Oracle高级查询

高级查询在数据库的开发过程中应用广泛，从分组查询、多表查询和子查询三个方面介绍Oracle的高级查询。

分组查询

分组查询是按照一定的规则进行分组，分组以后数据会聚合，需要使用聚合函数，但是使用聚合函数不一定要分组，分组的关键字是group by。

常用的聚合函数有：最大值max()，最小值min()，平均值avg()，总和sum()，统计个数count()

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

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

group by子查询

在select 列表中所有未包含在聚合函数中的列都应该包含在group by子句中。

单列分组

求每个部门的平均工资，显示部门号，部门的平均工资。

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子句中可以使用聚合函数，先分组后过滤

注意：从SQL优化的角度上看，尽量使用where，因为where使得分组记录数大大降低，从而提高效率。

求平均工资大于2000的部门

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

还可以按列的序号排序，平均工资是第2列

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

降序排列加上desc即可

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

分组函数的嵌套

求部门平均工资的最大值

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

group by语句增强

主要用在group by语句报表功能

每个部门，安装不同职位，求工资总和，部门小结，总结。

可以使用rollup函数

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

再设置一下显示格式，break on deptno表示相同的部门号只显示一个，skip 1表示不同的部门号之间空1行。

完善报表显示

增加标题，页码等

ttitle col 15 ‘我的报表’ col 35 sql.pno

设置标题，空15列显示我的报表，然后空35列显示页码

col deptno heading 部门号

col job heading 职位

col sum(sal) heading 工资总额

以上3行设置列标题

break on deptno skip 1

设置显示格式，相同的部门号只显示一个，不同部门号之间空1行

将这些设置保存到一个sql文件（注意要改成ANSI编码，否则会出现乱码并且设置无效），然后通过get命令读取执行。再次执行查询语句，得到如下报表。如果出现了多页，为了显示美观，可以设置一页显示更多的行，比如设置每页显示100行：set pagesize 100

多表查询

上面的例子都是从单个表中查询数据，下面开始讲解从多个表中查询数据。

为了避免笛卡尔集，可以在where加入有效的连接条件，在实际允许环境下，应避免使用笛卡尔全集。

等值连接

实例：查询员工信息，要求显示：员工号，姓名，月薪，部门名称

需要查询员工表和部门表，通过部门号进行等值连接查询，where xxx=xxx

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

不等值连接

示例：查询员工信息，要求显示：员工号，姓名，月薪，薪水级别

需要查询员工表和薪水等级表，通过薪水等级上下限进行不等值连接查询。where xxx between xxx and xxx，注意：小值在between前面，大值在between后面

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

外连接一般通过join来实现，一张图看懂SQL的各种join用法。

使用join语句重新实现示例功能

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

这种方式会产生笛卡尔集，不适合大表的查询，可以使用层次查询来解决。connect by xxx start with xxx

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

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

子查询

子查询语法中的小括号

必须要有小括号，书写风格要清晰如下图所示：

示例：查询比FORD工资高的员工

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位置的子查询

示例：查找部门平均工资大于30号部门最大工资的部门号及其平均工资

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)

增加1列年薪，使用sal*12得到年薪

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

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

示例：查询部门名称是SALES的员工信息

使用子查询的方式：

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'

子查询的排序

一般不在子查询中，使用排序；但在Top-N分析问题中，必须对子查询排序

示例：找到员工表中工资最高的前三名，如下格式：

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)

单行子查询和多行子查询

单行子查询返回一个结果，只能使用单行操作符；

多行子查询返回多个结果，只能使用多行操作符。

单行操作符：

多行操作符：

单行子查询示例1：

查询员工信息，要求：

职位与7566员工一样，薪水大于7782员工的薪水

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

单行子查询示例2：

查询最低工资大于20号部门最低工资的部门号和部门的最低工资

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

多行子查询示例：

查询部门名称是SALES和ACCOUNTING的员工信息

使用多行子查询的方式：

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')

子查询中的空值问题

查询不是老板的员工

注意：当子查询中包含null值时，不要使用not in。

a not in (10,20,null)

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

可以在子查询中把null值过滤掉再使用not in。

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