分组查询

count函数使用列名时会自动忽略空值

nvl函数可以防止count自动忽略空值，它的作用是当comm为空时返回0，因为0是非空，所以会进入统计总数。

单列分组

``````select deptno,avg(sal) from emp group by deptno order by deptno
``````

多列分组

``````select detpno,job,sum(sal) from emp group by deptno,job order by deptno
``````

过滤分组

having子句的使用

where与having的区别

• where子句中不能使用聚合函数，先过滤后分组
• having子句中可以使用聚合函数，先分组后过滤

``````select deptno,avg(sal) from emp where(avg(sal)) > 2000 group by deptno
``````

where子句中不能使用聚合函数，所以报错，改成having xxx子句即可。

``````select deptno,avg(sal) from emp group by deptno having(avg(sal)) > 2000
``````

在分组查询中使用order by

``````select deptno,avg(sal) from emp group by deptno order by avg(sal)
``````

``````select deptno,avg(sal) avgsal from emp group by deptno order by avgsal
``````

``````select deptno,avg(sal) from emp group by deptno order by 2
``````

``````select deptno,avg(sal) from emp group by deptno order by 2 desc
``````

分组函数的嵌套

``````select max(avg(sal)) from emp group by deptno
``````

group by语句增强

``````select deptno,job,sum(sal) from emp group by rollup(deptno,job)
``````

完善报表显示

ttitle col 15 '我的报表' col 35 sql.pno

break on deptno skip 1

多表查询

等值连接

``````select e.empno,e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno
``````

不等值连接

``````select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal
``````

外连接

``````select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数 from emp e,dept d where e.deptno=d.deptno group by d.deptno,d.dname
``````

``````select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数 from dept d left join emp e on d.deptno=e.deptno group by d.deptno,d.dname
``````

自连接

``````select e.ename 员工姓名,b.ename 老板姓名 from emp e, emp b where e.mgr=b.empno
``````

level是层次查询提供的伪列，需要显示使用才会查询这个伪列。

``````select level,empno,ename,sal,mgr from emp connect by prior empno=mgr start with mgr is null order by 1
``````

子查询

子查询语法中的小括号

``````select * from emp where sal > (select sal from emp where ename='FORD')
``````

可以使用子查询的位置

select，from，where，having

select位置的子查询只能是单行子查询，也就是只能返回一条结果

``````select empno,ename,sal,(select job from emp where empno='7839') job from emp
``````

having位置的子查询

``````select deptno,avg(sal) from emp group by deptno having avg(sal) > (select max(sal) from emp where deptno=30)
``````

from位置的子查询

``````select * from (select empno,ename,sal from emp)
``````

``````select * from (select empno,ename,sal,sal*12 annsal from emp)
``````

主查询和子查询可以不是同一张表

``````select * from emp where deptno=(select deptno from dept where dname='SALES')
``````

``````select e.* from emp e, dept d where e.deptno=d.deptno and d.dname='SALES'
``````

子查询的排序

rownum，行号，oracle自动为表分配的伪列。

• 行号永远按照默认的顺序生成
• 行号只能使用<,<=；不能使用>,>=
``````select rownum,empno,ename,sal from (select * from emp order by sal desc) where rownum<=3
``````

子查询执行顺序

``````select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal from emp e where sal > (select avg(sal) from emp where deptno=e.deptno)
``````

单行子查询和多行子查询

= 等于
> 大于
>= 大于等于
< 小于
<= 小于等于
<> 不等于

多行操作符：

in 等于列表中的任何一个
any 和子查询返回的任意一个值比较
all 和子查询返回的左右值比较

单行子查询示例1：

``````select * from emp where job=(select job from emp where empno=7566) and sal >（select sal from emp where empno=7782）
``````

单行子查询示例2：

``````select deptno,min(sal) from emp group by deptno having min(sal) > (select min(sal) from emp where deptno=20)
``````

多行子查询示例：

``````select * from emp where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING')
``````

``````select e.* from emp e, dept d where e.deptno=d.deptno and (d.dname='SALES' or d.dname='ACCOUNTING')
``````

子查询中的空值问题

a not in (10,20,null)

a != 10 and a != 20 and a != null, a != null 永远不成立，所以整个表达式永远返回false。

``````select * from emp where empno not in (select mgr from emp where mgr is not null)
``````