1.多行函数
- Sum avg max min count
- 组函数具有滤空的作用(添加nvl屏蔽该功能)
- 分组group by
- 多行分组
- 分组过滤
- where 和分组过滤的区别（having)
- 分组的增强(rollup)
break on deptno skip 2;
break on null;
SQL> -- 计算一下每一个月要发多少工资,不含奖金
SQL> select sum(sal) from emp; SUM(SAL)
----------
29025 SQL> select sal from emp; SAL
----------
800
1600
1250
2975
1250
2850
2450
3000
5000
1500
1100 SAL
----------
950
3000
1300 已选择14行。 SQL> -- 计算一下每年发的奖金的和
SQL> select sum(comm) from emp; SUM(COMM)
----------
2200 SQL> select comm from emp; COMM
---------- 300
500 1400 0 COMM
---------- 已选择14行。 SQL> -- 多行函数自动虑空
SQL> -- 计算一下公司的平均工资
SQL> select sum(sal)/count(*) from emp; SUM(SAL)/COUNT(*)
-----------------
2073.21429
SQL> select sum(sal)/count(sal) from emp; SUM(SAL)/COUNT(SAL)
-------------------
2073.21429 SQL> select avg(sal) from emp; AVG(SAL)
----------
2073.21429 SQL> -- 计算一下平均奖金
SQL> select sum(comm)/count(*) from emp; SUM(COMM)/COUNT(*)
------------------
157.142857 SQL> select sum(comm)/count(comm) from emp; SUM(COMM)/COUNT(COMM)
---------------------
550 错误方法 SQL> select avg(comm) from emp; AVG(COMM)
----------
550 错误方法 SQL> -- 多行函数的虑空并不是在所有的场合都适用,如果你不希望他的虑空起作用你可以采用函数的嵌套来屏蔽该功能
SQL> select sum(comm)/count(*),avg(nvl(comm,0)) from emp; SUM(COMM)/COUNT(*) AVG(NVL(COMM,0))
------------------ ----------------
157.142857 157.142857 SQL> -- 查询工资最高和最低的员工信息
SQL> select max(sal),min(sal) from emp; MAX(SAL) MIN(SAL)
---------- ----------
5000 800 SQL> select max(comm),min(comm) from emp; MAX(COMM) MIN(COMM)
---------- ----------
1400 0 SQL> -- 分组
SQL> -- 求每一个部门的工资总和 和平均工资
SQL> select deptno,sum(sal),avg(sal)
2 from emp
3 group by deptno; DEPTNO SUM(SAL) AVG(SAL)
---------- ---------- ----------
30 9400 1566.66667
20 10875 2175
10 8750 2916.66667 SQL> -- 统计部门的平均工资，部门号，岗位
SQL> select deptno,avg(sal),job
2 from emp
3 group by deptno,job; DEPTNO AVG(SAL) JOB
---------- ---------- ------------------
20 950 CLERK
30 1400 SALESMAN
20 2975 MANAGER
30 950 CLERK
10 5000 PRESIDENT
30 2850 MANAGER
10 1300 CLERK
10 2450 MANAGER
20 3000 ANALYST 已选择9行。 SQL> -- group by 后面必须要跟select后面没有在多行函数里面的字段
SQL> -- 分组函数的过滤
SQL> -- 统计部门号为20的部门下的所有职位的平均工资
SQL> select deptno,avg(sal),job
2 from emp
3 where deptno=20
4 group by deptno,job; DEPTNO AVG(SAL) JOB
---------- ---------- ------------------
20 950 CLERK
20 2975 MANAGER
20 3000 ANALYST SQL> select deptno,avga(sal),job
2 from emp
3 group by deptno,job
4 having deptno=20;
select deptno,avga(sal),job
*
第 1 行出现错误:
ORA-00904: "AVGA": 标识符无效 SQL> c /avga(sal)/avg(sal);
1* select deptno,avg(sal),job
SQL> / DEPTNO AVG(SAL) JOB
---------- ---------- ------------------
20 950 CLERK
20 2975 MANAGER
20 3000 ANALYST SQL> -- 统计平均工资大于2000的部门
SQL> select deptno,avg(sal)
2 from emp
3 where avg(sal) >2000
4 group by deptno,job;
where avg(sal) >2000
*
第 3 行出现错误:
ORA-00934: 此处不允许使用分组函数 SQL> select deptno,avg(sal)
2 from emp
3 group by deptno
4 having avg(sal)>2000; DEPTNO AVG(SAL)
---------- ----------
20 2175
10 2916.66667 SQL> --1. where和having都可以用来做条件的过滤操作,但是where后面不能跟分组函数,having后面可以跟分组函数
SQL> -- 2 尽量使用where 因为他的效率更高
SQL> select deptno,job,sum(sal)
2 from emp
3 group by rollup(deptno,job); DEPTNO JOB SUM(SAL)
---------- ------------------ ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600 DEPTNO JOB SUM(SAL)
---------- ------------------ ----------
30 9400
29025 已选择13行。 SQL> break on deptno skip 2;
SQL> / DEPTNO JOB SUM(SAL)
---------- ------------------ ----------
10 CLERK 1300
MANAGER 2450
PRESIDENT 5000
8750 20 CLERK 1900
ANALYST 6000
MANAGER 2975
10875 DEPTNO JOB SUM(SAL)
---------- ------------------ ---------- 30 CLERK 950
MANAGER 2850
SALESMAN 5600
9400 29025 已选择13行。 SQL> --break on deptno skip 2;去除deptno后相同的no,并空两行；
SQL> break on null;
SQL> / DEPTNO JOB SUM(SAL)
---------- ------------------ ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600 DEPTNO JOB SUM(SAL)
---------- ------------------ ----------
30 9400
29025 已选择13行。 SQL> break on deptno skip 3;
SQL> / DEPTNO JOB SUM(SAL)
---------- ------------------ ----------
10 CLERK 1300
MANAGER 2450
PRESIDENT 5000
8750 20 CLERK 1900
ANALYST 6000
MANAGER 2975
10875 DEPTNO JOB SUM(SAL)
---------- ------------------ ---------- 30 CLERK 950
MANAGER 2850
SALESMAN 5600
9400 29025 已选择13行。 SQL> select sum(sal) from emp; SUM(SAL)
----------
29025 SQL> select sum(comm) from emp; SUM(COMM)
----------
2200 SQL> select sum(sal)/count(*) from emp; SUM(SAL)/COUNT(*)
-----------------
2073.21429 SQL> select sum(sal)/count(sal) from emp; SUM(SAL)/COUNT(SAL)
-------------------
2073.21429 SQL> select avg(sal) from emp; AVG(SAL)
----------
2073.21429 SQL> select max(sal),min(sal) from emp; MAX(SAL) MIN(SAL)
---------- ----------
5000 800 SQL> select max(comm),min(comm) from emp; MAX(COMM) MIN(COMM)
---------- ----------
1400 0 SQL> select deptno,sum(sal),avg(sal)
2 from emp
3 group by deptno; DEPTNO SUM(SAL) AVG(SAL)
---------- ---------- ----------
30 9400 1566.66667 20 10875 2175 10 8750 2916.66667 SQL> select deptno,avg(sal),job
2 from emp
3 group by deptno,job; DEPTNO AVG(SAL) JOB
---------- ---------- ------------------
20 950 CLERK 30 1400 SALESMAN 20 2975 MANAGER DEPTNO AVG(SAL) JOB
---------- ---------- ------------------ 30 950 CLERK 10 5000 PRESIDENT 30 2850 MANAGER DEPTNO AVG(SAL) JOB
---------- ---------- ------------------ 10 1300 CLERK
2450 MANAGER 20 3000 ANALYST 已选择9行。 SQL> select deptno,avg(sal),job
2 from emp
3 where deptno=20
4 group by deptno,job; DEPTNO AVG(SAL) JOB
---------- ---------- ------------------
20 950 CLERK
2975 MANAGER
3000 ANALYST SQL> select deptno,avg(sal),job
2 from emp
3 group by deotno,job
4 having deptno=20;
group by deotno,job
*
第 3 行出现错误:
ORA-00904: "DEOTNO": 标识符无效 /*
c:用来改错（只能改一个单词）
*/
SQL> c /deotno/deptno;
3* group by deptno,job
SQL> / DEPTNO AVG(SAL) JOB
---------- ---------- ------------------
20 950 CLERK
2975 MANAGER
3000 ANALYST SQL> select deptno,avg(sal),job
2 from emp
3 group by deptno,job
4 having avg(sal)>2000; DEPTNO AVG(SAL) JOB
---------- ---------- ------------------
20 2975 MANAGER 10 5000 PRESIDENT 30 2850 MANAGER DEPTNO AVG(SAL) JOB
---------- ---------- ------------------ 10 2450 MANAGER 20 3000 ANALYST SQL> select deptno,job,sum(sal)
2 from emp
3 group by rollup(deptno,job); DEPTNO JOB SUM(SAL)
---------- ------------------ ----------
10 CLERK 1300
MANAGER 2450
PRESIDENT 5000
8750 20 CLERK 1900
ANALYST 6000
MANAGER 2975
10875 DEPTNO JOB SUM(SAL)
---------- ------------------ ---------- 30 CLERK 950
MANAGER 2850
SALESMAN 5600
9400 29025 已选择13行。 SQL> break on null;
SQL> / DEPTNO JOB SUM(SAL)
---------- ------------------ ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600 DEPTNO JOB SUM(SAL)
---------- ------------------ ----------
30 9400
29025 已选择13行。 SQL> break on deptno skip 2;
SQL> / DEPTNO JOB SUM(SAL)
---------- ------------------ ----------
10 CLERK 1300
MANAGER 2450
PRESIDENT 5000
8750 20 CLERK 1900
ANALYST 6000
MANAGER 2975
10875 DEPTNO JOB SUM(SAL)
---------- ------------------ ---------- 30 CLERK 950
MANAGER 2850
SALESMAN 5600
9400 29025 已选择13行。 SQL> spool off;
2.多表查询
1）笛卡尔集
2）等值连接
3）非等值连接
4）外连接
5）内连接
6）层次查询(只能有一张表 connect by prior empno = mgr start with empno = 7839)
SQL> -- 查询所有部门的信息和部门下的所有员工信息
SQL> select e.empno,e.ename,job,d.deptno,d.dname
2 from emp e,dept d
3 where e.deptno=d.deptno; EMPNO ENAME JOB DEPTNO DNAME
---------- ------------------------------ ------------------ ---------- ----------------------------
7369 SMITH CLERK 20 RESEARCH 7499 ALLEN SALESMAN 30 SALES
7521 WARD SALESMAN SALES 7566 JONES MANAGER 20 RESEARCH 7654 MARTIN SALESMAN 30 SALES EMPNO ENAME JOB DEPTNO DNAME
---------- ------------------------------ ------------------ ---------- ----------------------------
7698 BLAKE MANAGER 30 SALES 7782 CLARK MANAGER 10 ACCOUNTING 7788 SCOTT ANALYST 20 RESEARCH 7839 KING PRESIDENT 10 ACCOUNTING EMPNO ENAME JOB DEPTNO DNAME
---------- ------------------------------ ------------------ ---------- ---------------------------- 7844 TURNER SALESMAN 30 SALES 7876 ADAMS CLERK 20 RESEARCH 7900 JAMES CLERK 30 SALES 7902 FORD ANALYST 20 RESEARCH EMPNO ENAME JOB DEPTNO DNAME
---------- ------------------------------ ------------------ ---------- ---------------------------- 7934 MILLER CLERK 10 ACCOUNTING 已选择14行。 SQL> select * from tab; TNAME TABTYPE CLUSTERID
------------------------------------------------------------ -------------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE SQL> select *from salgrade; GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999 SQL> -- 查询员工信息和员工的工资级别
SQL> select e.empno,e.sal,s.grade,s.losal,s.hisal
2 from emp e,salgrade s
3 where e.sal between s.losal and s.hisal; EMPNO SAL GRADE LOSAL HISAL
---------- ---------- ---------- ---------- ----------
7369 800 1 700 1200
7900 950 1 700 1200
7876 1100 1 700 1200
7521 1250 2 1201 1400
7654 1250 2 1201 1400
7934 1300 2 1201 1400
7844 1500 3 1401 2000
7499 1600 3 1401 2000
7782 2450 4 2001 3000
7698 2850 4 2001 3000
7566 2975 4 2001 3000 EMPNO SAL GRADE LOSAL HISAL
---------- ---------- ---------- ---------- ----------
7788 3000 4 2001 3000
7902 3000 4 2001 3000
7839 5000 5 3001 9999 已选择14行。 SQL> -- 不等值连接
SQL> -- 外连接
SQL> -- 统计每一个部门的人数
SQL> -- 显示部门号 部门名称 部门人数
SQL> select e.deptno,d.dname,count(*)
2 from emp e,dept d
3 where e.deptno=d.deptno
4 group by e.deptno,d.dname; DEPTNO DNAME COUNT(*)
---------- ---------------------------- ----------
10 ACCOUNTING 3 20 RESEARCH 5 30 SALES 6 SQL> select * from dept; DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> /*
SQL>
SQL> 外连接:分为左外连接和右外连接
SQL> 左外连接: where e.deptno = d.deptno(+) 不成立的时候,无论右边是否有数据与之对应,左边的都要显示
SQL> where e.deptno = d.depton(+)
SQL> 右外连接: where e.deptno(+) = d.deptno 不成立的时候,无论左边表是否有数据与之对应,右边的都会显示
SQL> where e.deptno(+) = d.deptno;
SQL> */
SQL> select d.deptno,d.dname,count(e.empno)
2 from emp e,dept d
3 where e.deptno(+)=d.deptno
4 group by d.deptno,d.deptname;
group by d.deptno,d.deptname
*
第 4 行出现错误:
ORA-00904: "D"."DEPTNAME": 标识符无效 SQL> c /d.deptname/d.dname;
4* group by d.deptno,d.dname
SQL> / DEPTNO DNAME COUNT(E.EMPNO)
---------- ---------------------------- --------------
10 ACCOUNTING 3 40 OPERATIONS 0 20 RESEARCH 5 30 SALES 6 SQL> select d.deptno,d.dname,count(e.empno)
2 from emp e,dept d
3 where e.deptno=d.deptno(+)
4 group by d.deptno,d.dname; DEPTNO DNAME COUNT(E.EMPNO)
---------- ---------------------------- --------------
10 ACCOUNTING 3 20 RESEARCH 5 30 SALES 6 SQL> select e.ename 领导名字,e.empno 领导id,p.ename 员工名字,p.empno 员工id
2 from emp e,emp p
3 where e.ename=p.mgr;
where e.ename=p.mgr
*
第 3 行出现错误:
ORA-01722: 无效数字 SQL> c /e.ename/e.empno;
3* where e.empno=p.mgr
SQL> / 领导名字 领导ID 员工名字 员工ID
-------------------- ---------- -------------------- ----------
FORD 7902 SMITH 7369
BLAKE 7698 ALLEN 7499
BLAKE 7698 WARD 7521
KING 7839 JONES 7566
BLAKE 7698 MARTIN 7654
KING 7839 BLAKE 7698
KING 7839 CLARK 7782
JONES 7566 SCOTT 7788
BLAKE 7698 TURNER 7844
SCOTT 7788 ADAMS 7876
BLAKE 7698 JAMES 7900 领导名字 领导ID 员工名字 员工ID
-------------------- ---------- -------------------- ----------
JONES 7566 FORD 7902
CLARK 7782 MILLER 7934 已选择13行。 SQL> select count(*) from emp e,emp p
2 where e.empno=p.mgr; COUNT(*)
----------
13 SQL> select count(*) from emp e,emp p; COUNT(*)
----------
196 SQL> -- 内连接只能做数据量小的表,对于大表用层次查询
SQL> -- 层次查询
SQL> select ename 领导名字,empno 领导id
2 from emp
3 connect by prior empno=mgr
4 start with mgr is null; 领导名字 领导ID
-------------------- ----------
KING 7839
JONES 7566
SCOTT 7788
ADAMS 7876
FORD 7902
SMITH 7369
BLAKE 7698
ALLEN 7499
WARD 7521
MARTIN 7654
TURNER 7844 领导名字 领导ID
-------------------- ----------
JAMES 7900
CLARK 7782
MILLER 7934 已选择14行。