子查询

一.概述

在查询语句中嵌套查询语句的过程

特点：
1.子查询语句为一个独立的查询语句(多数情况下)
2.可以嵌套多个子查询语句
3.可以在多个子句中加子查询语句

示例：
1.查询和SCOTT同部门的员工信息
–表： EMP 列：* WHERE条件 :部门和SCOTT相同
SELECT * FROM EMP WHERE DEPTNO=20;
–查询scott的部门
SELECT DEPTNO FROM EMP WHERE ENAME =‘SCOTT’;
–一句代码实现 子查询语句
SELECT * FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME =‘SCOTT’);

二.做题步骤

示例
查询员工表中工资大于SMITH所在部门的平均工资的员工信息

法一：‘SMITH’->ENAME->部门->平均薪资->工资->员工信息 --类似于如此的过程，用子查询

1:根据已知的SMITH求出它的部门
SELECT DEPTNO FROM EMP WHERE ENAME=‘SMITH’;

2:根据查询出的部门求部门的平均薪资

SELECT AVG(SAL) FROM EMP WHERE DEPTNO=20;

3:根据平均薪资过滤工资列的信息, 进而查出所有的信息

SELECT * FROM EMP WHERE SAL>2312.5;

4:整合 自下而上将各步骤的代码整合到一起

SELECT *
FROM EMP
WHERE SAL >
(SELECT AVG(SAL)
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = ‘SMITH’));

5:审视一下代码： 列的比较有无错误 连接符使用有无错误

SELECT *
FROM EMP
WHERE SAL >
(SELECT AVG(SAL)
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = ‘SMITH’));

查询员工表中工资大于SMITH所在部门的平均工资的员工信息

法二：
1:直接将架构写出 分析各个关键字的内容
SELECT * FROM EMP WHERE SAL>(SMITH所在部门的平均工资);

2:将结构中不确定的值用变量1代替
SELECT * FROM EMP WHERE SAL>(变量1);

3：将代码中的变量1用代码描述，若含有不确定的值依然用变量代替
–SMITH所在部门的平均工资
SELECT AVG(SAL) FROM EMP WHERE DEPTNO=(变量2);

4:重复第三步，直到代码中不含有变量为止
–SMITH所在部门
SELECT DEPTNO FROM EMP WHERE ENAME=‘SMITH’;

5:整合 自上而下整合
SELECT *
FROM EMP
WHERE SAL >
(SELECT AVG(SAL)
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = ‘SMITH’));

6:审视一下代码： 列的比较有无错误 连接符使用有无错误
SELECT *
FROM EMP
WHERE SAL >
(SELECT AVG(SAL)
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = ‘SMITH’));

三.各个关键字的分析

结构：
SELECT COLUMN1,… .
FROM TABLE_NAME
WHERE CONDITION
GROUP BY COLUMN1,…
HAVING CONDITION
ORDER BY COLUMN1 …;

1.根据数据在表中展现形式（不包含空值）：

SELECT * FROM EMP;

单行单列 ：SELECT EMPNO FROM EMP WHERE SAL=800;
单行多列 ：SELECT EMPNO,DEPTNO FROM EMP WHERE SAL=800;
多行单列 ：SELECT EMPNO FROM EMP ;
多行多列 ：SELECT EMPNO,DEPTNO FROM EMP ;

2.SELECT +子查询语句

单行单列
SELECT (SELECT EMPNO FROM EMP WHERE SAL=800) FROM EMP;–相当于常量，不等价于常量

单行多列
SELECT (SELECT EMPNO,DEPTNO FROM EMP WHERE SAL=800) FROM EMP;–报错 值过多

多行单列
SELECT (SELECT EMPNO FROM EMP ) FROM EMP;–报错 单行子查询返回多个行

多行多列
SELECT (SELECT EMPNO,DEPTNO FROM EMP ) FROM EMP;–报错 值过多

–参考表的关系范式(第一，第二，第三范式)理解

对应关系：1对1 1对多 多对1 多对多

EMPNO: ENAME=1:1
JOB : EMPNO =1:多
EMPNO : JOB =多：1
HIREDATE : DEPTNO = 多：多

3.FROM + 子查询语句

—单行单列：
SELECT * FROM (SELECT ENAME FROM EMP WHERE SAL=800);—可以执行
—单行多列
SELECT * FROM (SELECT ENAME，HIREDATE FROM EMP WHERE SAL=800);—可以执行
—多行多列
SELECT * FROM (SELECT ENAME，HIREDATE FROM EMP );—可以执行
—多行单列
SELECT * FROM (SELECT ENAME FROM EMP );—可以执行

思考：为什么from四种均可？

源于 FROM +数据源(表名、集合)，所以from四种均可。

SELECT * FROM (SELECT * FROM EMP3);–从空表中查询数据为空

4.WHERE +子查询语句

单行单列
SELECT * FROM EMP WHERE EMPNO = (SELECT EMPNO FROM EMP WHERE SAL=800);–可以执行

单行多列
SELECT * FROM EMP WHERE (EMPNO,SAL) = (SELECT EMPNO,SAL FROM EMP WHERE SAL=800 );

多行单列
SELECT * FROM EMP WHERE EMPNO = (SELECT EMPNO FROM EMP ); --子查询返回多个行

SELECT * FROM EMP WHERE EMPNO IN(SELECT EMPNO FROM EMP );
SELECT * FROM EMP WHERE EMPNO =ANY(SELECT EMPNO FROM EMP );

多行多列

SELECT * FROM EMP WHERE (EMPNO,SAL) = (SELECT EMPNO,SAL FROM EMP )–单行子查询返回多个行

SELECT * FROM EMP WHERE (EMPNO,SAL)IN(SELECT EMPNO,SAL FROM EMP );

5.GROUP BY

SELECT DEPTNO FROM EMP GROUP BY (SELECT EMPNO FROM EMP WHERE SAL=800);
–分组后不允许出现子查询表达式

6.HAVING +子查询语句

单行单列
SELECT EMPNO FROM EMP GROUP BY EMPNO HAVING EMPNO =(SELECT EMPNO FROM EMP WHERE SAL=800);

单行多列

SELECT EMPNO, DEPTNO
FROM EMP
GROUP BY EMPNO, DEPTNO
HAVING (EMPNO, DEPTNO) = (SELECT EMPNO, DEPTNO FROM EMP WHERE SAL = 800);

多行单列

SELECT EMPNO FROM EMP GROUP BY EMPNO HAVING EMPNO =(SELECT EMPNO FROM EMP)–子查询返回多个行

SELECT EMPNO FROM EMP GROUP BY EMPNO HAVING EMPNO IN(SELECT EMPNO FROM EMP);

多行多列

SELECT EMPNO, DEPTNO
FROM EMP
GROUP BY EMPNO, DEPTNO
HAVING(EMPNO, DEPTNO)IN (SELECT EMPNO, DEPTNO FROM EMP) ;

7.ORDER BY +子查询语句

单行单列
SELECT * FROM EMP ORDER BY (SELECT EMPNO FROM EMP WHERE SAL=800);–可以执行 不排序

单行多列

SELECT * FROM EMP ORDER BY (SELECT EMPNO,EANME FROM EMP WHERE SAL=800)–值过多

多行单列

SELECT * FROM EMP ORDER BY (SELECT EMPNO FROM EMP )–单行子查询返回多个行

多行多列

SELECT * FROM EMP ORDER BY (SELECT EMPNO,EANME FROM EMP )–值过多

8.小结

1).SELECT :单行单列
2).FROM : 单行单列 单行多列 多行单列 多行多列
3).WHERE : 单行单列 单行多列 多行单列 多行多列
4).GROUP BY:不允许出现子查询语句
5).HAVING : 单行单列 单行多列 多行单列 多行多列
6).ORDER BY : 单行单列
7).SELECT和ORDER BY一致
8).FROM HAVING WHERE 一致:
WHERE和HAVING由于其后添加条件，总有一个条件使代码执行所以均可

四.相关非相关子查询

A.非相关子查询：
主查询语句和子查询语句没有联系，子查询语句能够独立运行

–子查询主要应用于WHERE 、HAVING 后，联合查询中FROM后

示例：

1.查询员工表中部门和SCOTT相等且工资大于20号部门的最大工资的员工信息

SELECT *
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = ‘SCOTT’)
AND SAL > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 20);

2.查询工资降序排列在前三名的员工信息(ROWNUM)

利用ROWNUM去除序号在前三名的员工信息
在添加序号之前工资已经有序(ORDER BY)

先将工资排序，在添加ROWNUM
SELECT * FROM EMP ORDER BY SAL DESC
添加ROWNUM

SELECT E.*,ROWNUM FROM (SELECT * FROM EMP ORDER BY SAL DESC) E WHERE ROWNUM<4;

3.查询emp表中20号部门的员工编号，员工姓名，工资以及20号部门的员工人数，并以员工人数显示

SELECT EMPNO,
ENAME,
SAL,
(SELECT COUNT(*) FROM EMP WHERE DEPTNO = 20) 员工人数
FROM EMP
WHERE DEPTNO = 20;
–利用相关子查询查询各个部门的员工人数

4.查询各个部门大的员工人数

–法一：
SELECT COUNT(*) FROM EMP GROUP BY DEPTNO;–纵向显示
–法二：
SELECT SUM(CASE WHEN DEPTNO=10 THEN 1 ELSE 0 END) “10号部门人数” ,
SUM(CASE WHEN DEPTNO=20 THEN 1 ELSE 0 END) “20号部门人数” ,
SUM(CASE WHEN DEPTNO=30 THEN 1 ELSE 0 END) “30号部门人数”
FROM EMP;–横向显示

SELECT SUM(CASE WHEN DEPTNO=10 THEN 1 END) “10号部门人数” ,
SUM(CASE WHEN DEPTNO=20 THEN 1 END) “20号部门人数” ,
SUM(CASE WHEN DEPTNO=30 THEN 1 END) “30号部门人数”
FROM EMP;–横向显示

B.相关子查询：
主查询语句和子查询语句有联系，子查询语句不能够独立运行

EXISTS和相关子查询联合使用

示例：

1.EXISTS的使用

SELECT * FROM EMP WHERE EXISTS (SELECT 1 FROM EMP WHERE DEPTNO=20);
SELECT * FROM EMP WHERE EXISTS (SELECT 1 FROM EMP WHERE DEPTNO IS NULL);

SELECT * FROM EMP WHERE NOT EXISTS (SELECT 1 FROM EMP WHERE DEPTNO=20);
SELECT * FROM EMP WHERE NOT EXISTS (SELECT 1 FROM EMP WHERE DEPTNO IS NULL);

2.EXISTS和相关子查询联合使用，来过滤查询结果中的数据

查询工资大于2000的员工所在部门的员工信息
–不相关子查询 通过编写条件过滤主查询语句中的数据

SELECT * FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM EMP WHERE SAL>2000);

相关子查询 通过子查询语句过滤主查询语句的数据
查询工资大于2000的员工所在部门的部门内员工信息
工资大于2000的员工所在部门->员工信息

SELECT *
FROM EMP E
WHERE EXISTS (SELECT DEPTNO
FROM EMP
WHERE SAL > 2000
AND E.DEPTNO = DEPTNO);

3.数据过滤过程

SELECT *
FROM EMP E
WHERE EXISTS (SELECT DEPTNO
FROM EMP
WHERE SAL > 2000
AND E.DEPTNO = DEPTNO);

4.相关子查询的做题步骤

1.要明确比较的列/联系外表和子查询内表的列
2.按照结构把题目中描述的大体用代码写出
3.把比较的列引入子查询中（在子查询条件后直接加AND/OR让内外两表的关系列=/>/<）
4.将两个条件联系以后，根据EISTS独有的规则判断该条数据符不符合子查询的条件，有无结果返回
决定该条数据是否返回到结果集中

C.练习

查询在纽约工作的员工的编号，姓名，以及职位
–不相关
SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = ‘NEW YORK’);

–相关
SELECT EMPNO, ENAME, JOB
FROM EMP E
WHERE EXISTS (SELECT 1 FROM DEPT WHERE LOC = ‘NEW YORK’ AND E.DEPTNO=DEPTNO);

查询工资大于公司平均工资的员工的姓名及职位

–不相关

SELECT ENAME,JOB FROM EMP WHERE SAL>(SELECT AVG(SAL) FROM EMP);

–相关
SELECT ENAME,JOB FROM EMP E WHERE EXISTS(SELECT AVG(SAL) FROM EMP HAVING E.SAL>AVG(SAL));
–having直接作为条件使用 ，表中全部数据为一组

查询工资大于该部门平均薪资的员工信息

–相关
SELECT * FROM EMP WHERE SAL>(SELECT AVG(SAL) FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM EMP));–大于公司平均薪资
SELECT * FROM EMP E WHERE SAL>(SELECT AVG(SAL) FROM EMP WHERE E.DEPTNO=DEPTNO );

查询emp表的全部信息及各部门的平均工资，并将平均薪资展示在emp表后以部门平均薪资显示

SELECT E.*,(SELECT AVG(SAL) FROM EMP WHERE E.DEPTNO=DEPTNO)部门平均薪资 FROM EMP E;

SELECT E.*,
ROUND((SELECT AVG(SAL)
FROM EMP
WHERE DEPTNO = E.DEPTNO),
2) 部门平均薪资
FROM EMP E;

注意：

1.明确在哪个位置添加两个表的关联条件！！！！！！
2.非相关子查询可以用在多个位置,相关子查询一般用在WHERE或HAVING子句中作为条件，两者可以发生转换。
3.相关子查询之所以存在，是因为在很多情况下使用相关子查询效率比较高。！！！
4.当子查询中的数据量比较大时，用EXISTS，当子查询中的数据量比较小时用 IN

五.总结

SELECT : 单行单列
FROM :单行单列 单行多列 多行多列 多行单列
WHERE :单行单列 单行多列 多行多列 多行单列
GROUP BY：不允许
HAVING：单行单列 单行多列 多行多列 多行单列
ORDER BY ：单行单列 不排序

2.子查询做题步骤 ！！！

–1):分清主次，主查询语句和子查询语句
–2):将子查询语句完整的用代码描述出来
–3):将主查询和子查询语句合并到一起，并确保无其他细节错误

3.EXISTS相关子查询步骤 ！！！

–1):分清主次，主查询语句和子查询语句，明确主次联系的连接条件
–2):将主查询语句和子查询语句通过EXISTS连接
–3):将连接条件写到EXISTS后子查询中，声明表中的列

4.相关子查询数据过滤过程

将主查询表中的数据分条来看，一条一条处理，各条数据之间互不影响的
利用EXISTS的特性将主表中的值引入到子查询条件中，看子查询是否有数据返回，决定该条数据
是否返回到结果集中;

–子查询用法灵活多变，具体情况具体分析