Oracle数据库基础SQL语句(三)—高级查询（子查询）

Oracle数据库基础SQL语句(三)—高级查询（子查询）

找出比SCOTT工资高的员工信息
问题分析：

SELECT * 
from EMP
WHERE SAL>(SELECT SAL
           FROM EMP
					 WHERE ENAME='SCOTT');

子查询需要注意的十个问题

1. 子查询语法中的小括号

必需要写小括号，不写就会报错

2. 子查询的书写风格

该换行的换行，该缩进的缩进

3. Oracle可以使用子查询的位置：where select having from

1. WHERE：

SELECT * 
from EMP
WHERE SAL>(SELECT SAL
         FROM EMP
  				 WHERE ENAME='SCOTT');

2. SELECT

注：在select中使用子查询时，子查询只能是单行查询（即返回一条数据）

SELECT ENAME,JOB,mgr,(SELECT JOB FROM EMP WHERE EMPNO='7839') 第四列
FROM EMP;

3. HAVING

SELECT DEPTNO,AVG(SAL)
from EMP
GROUP BY DEPTNO
HAVING AVG(SAL)>(SELECT MAX(SAL)      //此处不能用where，因为where中不能包含组函数
                 FROM EMP
				 WHERE DEPTNO=30);

4. FROM

把子查询的结果看成一张表，在Oracle数据库中很常用

SELECT * 
FROM (SELECT DEPTNO,sal FROM EMP);

4. 不可以使用子查询的位置:GROUP BY

语法规定，不可以用，group by处不能出现子查询语句

5. from后面的子查询

from后面跟的一般是一张表名，而子查询的结果也可以看成一张表

SELECT *
FROM (SELECT DEPTNO,sal,sal*12 年薪 from EMP);

6. 主查询和子查询不是同一张表

实例：查询部门名称是“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'//最少有n-1个连接条件，此处就用了两个

思考：既然都可以实现，那哪个比较好呢？
理论上来说多表好，只是理论上，因为子查询有两个from，但多表查询涉及到笛卡尔集的问题，所以具体情况，具体分析

7. 一般子查询中不使用排序，打包在Top-N分析问题中，必须实用排序

思考：什么是Top-N问题？
示例：找到工资表中工资最高的前三位
首先先介绍下Oracle中的*伪列：*rownum

SELECT rownum,ENAME,EMPNO,SAL from EMP;

有了行号我们的问题就变得简单了起来，只需要子查询的结果进行降序排序即可
注：行号永远按照默认的顺序生成（下面这条语句虽然进行了排序，但行号并没有改变）
为什么默认排序，涉及到Oracle数据库的默认表问题

SELECT rownum,ENAME,EMPNO,SAL from EMP ORDER BY SAL DESC;

我们工资的降序排序是没有问题的，子查询登场，让行号默认成此排序就好了

SELECT rownum,ENAME,EMPNO,SAL from (SELECT *
																		from EMP
																		ORDER BY SAL DESC)																		
																		WHERE ROWNUM <=3;

注：rownum只能使用<,<=不能使用>,>=(Oracle的分页查询文中涉及)

8. 一般先执行子查询，在执行主查询，但相关子查询例外

什么是相关子查询？
示例：找到员工表中薪水大于本部门平均薪资的员工

select ENAME,EMPNO,SAL,(SELECT AVG(SAL)from EMP WHERE DEPTNO = e.DEPTNO) as avgsal
//此处的子查询查的就是员工部门的平均工资，所以复制过来起个别名得到这一列即可
from EMP e
//设置参数e 传递给这上下两个子查询
where SAL>(SELECT AVG(SAL)from EMP WHERE DEPTNO = e.DEPTNO);

9. 单行子查询只能使用单行操作符，多行子查询只能使用多行操作符

单行子查询：子查询只返回一条记录

多行子查询：两行或多行数据

SELECT * 
from EMP
//此处都是单行操作符，进行是单行子查询
WHERE JOB=(SELECT JOB from EMP where EMPNO=7566) AND
			SAL>(SELECT SAL from EMP WHERE EMPNO=7782);

下面的语句中=是但行操作符，而子查询的是每个部门的最低工资，不止一条数据，所以爆错

多行操作符IN
示例：查询部门是SALES和ACCOUNTING的员工信息

SELECT * 
FROM EMP
WHERE DEPTNO in (SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES'or DNAME = 'ACCOUNTING');

思考：此处是否可以用多表查询来做呢？答案是可以的

SELECT *
from emp e,DEPT d
WHERE e.DEPTNO=d.DEPTNO and(d.DNAME = 'SALES' OR d.DNAME = 'ACCOUNTING');

多行操作符ANY
示例：查询工资比30号部门任意一个员工高的员工信息

SELECT *
from emp
WHERE sal>any(SELECT SAL FROM EMP WHERE DEPTNO=30);

思考：大于任意一个值，不就是大于最大值吗，可以把多行查询变成单行子查询

SELECT *
from emp
WHERE sal>(SELECT MIN(SAL) FROM EMP WHERE DEPTNO=30);

多行操作符ALL
示例：查询工资比30号部门所有员工高的员工信息（跟上边不一样，注意仔细观看）

SELECT *
from emp
WHERE sal>ALL(SELECT SAL FROM EMP WHERE DEPTNO=30);

同样的，我们把此问题用单行子查询来解答

SELECT *
from emp
WHERE sal>(SELECT MAX(SAL) FROM EMP WHERE DEPTNO=30);

注：在实际问题中，最大还是最小值，具体问题具体分析

10. 子查询的空值问题

多行子查询的空值问题


判断空值不能用等号，所以a!=null永远为假
正确写法，加个不为空的条件

SELECT * 
from EMP
WHERE EMPNO not in (SELECT MGR FROM EMP WHERE mgr is not NULL);