平均工资：
SQL> select sum(sal)/count(*) 一, avg(sal) 二 from emp; 一 二 ---------- ---------- 2073.21429 2073.21429
空值 组函数会自动滤空
SQL> select comm from emp; COMM ---------- 300 500 1400 0 COMM ---------- 已选择14行。
作为参照：
SQL> select count(*),count(comm) from emp; COUNT(*) COUNT(COMM) ---------- ----------- 14 4
可以在组函数中嵌套滤空函数，来屏蔽组函数的滤空功能：
SQL> select count(*),count(nvl(comm,0)) from emp; COUNT(*) COUNT(NVL(COMM,0)) ---------- ------------------ 14 14
查询平均工资大于2000的部门：
SQL> ed 已写入 file afiedt.buf 1 select deptno,avg(sal) 2 from emp 3 group by deptno 4* having avg(sal)>2000 SQL> / DEPTNO AVG(SAL) ---------- ---------- 20 2175 10 2916.66667
求10号部门的平均工资：
SQL> ed 已写入 file afiedt.buf 1 select deptno,avg(sal) 2 from emp 3 group by deptno 4* having deptno=10 SQL> / DEPTNO AVG(SAL) ---------- ---------- 10 2916.66667
此时改用 where：
SQL> ed 已写入 file afiedt.buf 1 select deptno,avg(sal) 2 from emp 3 group by deptno 4* where deptno=10 SQL> / where deptno=10 * 第 4 行出现错误: ORA-00933: SQL 命令未正确结束
内连接：
语法一：
select * from 表1 [inner] join 表2 on 表1.字段1=表2.字段1;
语法二：
select * from 表1,表2 where 表1.字段1=表2.字段1;
外连接：
1、左连接：
select * from 表1 left outer join 表2 on 表1.字段1=表2.字段1;
select * from 表1 left outer join 表2 where 表1.字段1=表2.字段1;
2、右连接：
select * from 表1 right outer join 表2 on 表1.字段1=表2.字段1;
select * from 表1 left outer join 表2 where 表1.字段1(+)=表2.字段1;
3、全外连接：
select * from 表1 full outer join 表2 on 表1.字段1=表2.字段1;
自连接：
举个例子：
自连接(selfjoin)是SQL语句中经常要用的连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。
示例：
在oracle的scott的schema中有一个表是emp。在emp中的每一个员工都有自己的mgr(经理)，并且每一个经理自身也是公司的员工，自身也有自己的经理。
但现在我们只有一张emp表。所以我们可以采用自连接。自连接的本意就是将一张表看成多张表来做连接。我们可以这样来写SQL语句:
SQL> select a.ename as 员工,b.ename as 上级 from emp a,emp b where a.mgr=b.empno; 员工 上级 -------------------- -------------------- FORD JONES SCOTT JONES JAMES BLAKE TURNER BLAKE MARTIN BLAKE WARD BLAKE ALLEN BLAKE MILLER CLARK ADAMS SCOTT CLARK KING BLAKE KING 员工 上级 -------------------- -------------------- JONES KING SMITH FORD 已选择13行。
交叉连接： 表与表之间做笛卡尔积查询！：
select * from 表1 cross join 表2; select * from 表1, 表2;
分页查询：
1、提取员工表前三行：
SQL> select rownum, emp.* from emp where rownum <4; ROWNUM EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- 1 7369 SMITH CLERK 7902 17-12月-80 800 20 2 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 3 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
2、提取员工表四行之后的：
错误！：
SQL> select rownum,emp.* from emp where rownum >3; 未选定行
正确：
SQL> select * from (select rownum r,emp.* from emp) re where re.r >3; R EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- 4 7566 JONES MANAGER 7839 02-4月 -81 2975 20 5 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 6 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 8 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 9 7839 KING PRESIDENT 17-11月-81 5000 10 10 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 11 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 12 7900 JAMES CLERK 7698 03-12月-81 950 30 13 7902 FORD ANALYST 7566 03-12月-81 3000 20 14 7934 MILLER CLERK 7782 23-1月 -82 1300 10 已选择11行。
提取工资排行前三的员工：
SQL> select rownum ,e.* from (select * from emp order by sal desc) e where rownum <4; ROWNUM EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- 1 7839 KING PRESIDENT 17-11月-81 5000 10 2 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 3 7902 FORD ANALYST 7566 03-12月-81 3000 20
提取6----10的记录：
1、扫描全表生成伪表，再进行提取分页(表数据多时效率极低)：
SQL> select * from (select rownum r, e.* from emp e ) er where er.r > 5 and er.r <11; R EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- 6 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 8 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 9 7839 KING PRESIDENT 17-11月-81 5000 10 10 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
2、提高效率后的写法，只扫描10行提取分页生成伪表：
SQL> select * from (select rownum r, e.* from emp e where rownum < 11) er where er.r > 5; R EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- 6 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 8 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 9 7839 KING PRESIDENT 17-11月-81 5000 10 10 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
排序加分页：
--a排序 select * from emp order by sal desc --b生成前10条伪表 select rownum , t1.* from (select * from emp order by sal desc ) t1 where rownum <11 --提取6到10 select * from (select rownum r , t1.* from (select * from emp order by sal desc ) t1 where rownum <11) t2 where t2.r >4
SQL> ed 已写入 file afiedt.buf 1 select * from 2 (select rownum r , t1.* from (select * from emp order by sal desc ) t1 3* where rownum <11) t2 where t2.r >5 4 ; R EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- 6 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 8 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 9 7934 MILLER CLERK 7782 23-1月 -82 1300 10 10 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
集合使用
1、并集：union
工资大于1500,或者是20部门下的员工：
SQL> select * from emp where sal > 1500 2 union 3 select * from emp where deptno =20 4 ; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7839 KING PRESIDENT 17-11月-81 5000 10 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7902 FORD ANALYST 7566 03-12月-81 3000 20 已选择9行。
2、交集：intersect
工资大于1500,并且是20部门下的员工
SQL> select * from emp where sal > 1500 2 intersect 3 select * from emp where deptno =20; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7902 FORD ANALYST 7566 03-12月-81 3000 20
3、差集：minus
1981年入职的普通员工（不包括经理，总裁）：
SQL> select * from emp where to_char(hiredate,'yyyy')='1981' 2 minus 3 select * from emp where job in ('MANAGER','PRESIDENT') 4 ; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7900 JAMES CLERK 7698 03-12月-81 950 30 7902 FORD ANALYST 7566 03-12月-81 3000 20 已选择6行。