7. 子查询

2.10SQL语句----》子查询

之前编写的所有查询严格来将都不算是复杂的查询，如果将所有查询进行内嵌的处理形式就会变成子查询，而且在所有的开发之中子查询一定熟练掌握。

2.10.1什么是子查询

所谓子查询是一个完整的查询语句中嵌入N个内部查询得到的查询结果，子查询本身没有固定的语法
把一个查询结果放在一个查询中使用就叫作子查询。（将一个查询语句作为一个结果供另一个语句使用）
嵌套在一个查询中的另一个查询就是子查询

2.10.2 子查询分类

独立子查询
子查询可以独立运行
相关子查询
子查询中引用父查询的结果

2.10.3子查询语法格式

例如1：select * from （select 列名1，列名2 from 表名）别名
例如2：SELECT * FROM 表名 WHERE 字段名+逻辑/关系/其他判断符+(
查询语句----》可以是之前所有的查询语句
);
1.SQL语句基本格式：
SELECT [DISTINCT] * | 列名1,[别名1]，列名2,[别名2]…| 计算过程|统计函数|分组字段
FROM 表名1【表别名1】，表名2【表别名2】.。。。。
【WHERE限定条件1，限定条件2.。。。。】
【GROUP BY分组条件1，分组条件2.。。。。】
【HAVING分组后的限定条件1，分组后的限定条件2.。。。。】
【ORDER BY 排序字段1 【ASC|DESC】，排序字段2 【ASC|DESC】
【UNION | UNION ALL | MINUS | intersect】

子查询语句格式：（）表示定义子查询
SELECT [DISTINCT] * | 列名1,[别名1]，列名2,[别名2]…| 计算过程|统计函数|分组字段（
SELECT [DISTINCT] * | 列名1,[别名1]，列名2,[别名2]…| 计算过程|统计函数|分组字段
FROM 表名1【表别名1】，表名2【表别名2】.。。。。
【WHERE限定条件1，限定条件2.。。。。】
【GROUP BY分组条件1，分组条件2.。。。。】
【HAVING分组后的限定条件1，分组后的限定条件2.。。。。】
【ORDER BY 排序字段1 【ASC|DESC】，排序字段2 【ASC|DESC】
）------》这个括号里的就是子查询
FROM 表名1【表别名1】，表名2【表别名2】.。。。。（
SELECT [DISTINCT] * | 列名1,[别名1]，列名2,[别名2]…| 计算过程|统计函数|分组字段
FROM 表名1【表别名1】，表名2【表别名2】.。。。。
【WHERE限定条件1，限定条件2.。。。。】
【GROUP BY分组条件1，分组条件2.。。。。】
【HAVING分组后的限定条件1，分组后的限定条件2.。。。。】
【ORDER BY 排序字段1 【ASC|DESC】，排序字段2 【ASC|DESC】
）------》这个括号里的就是子查询
【WHERE限定条件1，限定条件2.。。。。】（
SELECT [DISTINCT] * | 列名1,[别名1]，列名2,[别名2]…| 计算过程|统计函数|分组字段
FROM 表名1【表别名1】，表名2【表别名2】.。。。。
【WHERE限定条件1，限定条件2.。。。。】
【GROUP BY分组条件1，分组条件2.。。。。】
【HAVING分组后的限定条件1，分组后的限定条件2.。。。。】
【ORDER BY 排序字段1 【ASC|DESC】，排序字段2 【ASC|DESC】
）------》这个括号里的就是子查询
【GROUP BY分组条件1，分组条件2.。。。。】（
SELECT [DISTINCT] * | 列名1,[别名1]，列名2,[别名2]…| 计算过程|统计函数|分组字段
FROM 表名1【表别名1】，表名2【表别名2】.。。。。
【WHERE限定条件1，限定条件2.。。。。】
【GROUP BY分组条件1，分组条件2.。。。。】
【HAVING分组后的限定条件1，分组后的限定条件2.。。。。】
【ORDER BY 排序字段1 【ASC|DESC】，排序字段2 【ASC|DESC】
）------》这个括号里的就是子查询
【HAVING分组后的限定条件1，分组后的限定条件2.。。。。】（
SELECT [DISTINCT] * | 列名1,[别名1]，列名2,[别名2]…| 计算过程|统计函数|分组字段
FROM 表名1【表别名1】，表名2【表别名2】.。。。。
【WHERE限定条件1，限定条件2.。。。。】
【GROUP BY分组条件1，分组条件2.。。。。】
【HAVING分组后的限定条件1，分组后的限定条件2.。。。。】
【ORDER BY 排序字段1 【ASC|DESC】，排序字段2 【ASC|DESC】
）------》这个括号里的就是子查询
【ORDER BY 排序字段1 【ASC|DESC】，排序字段2 【ASC|DESC】（
这里面也可以加基础语法格式
）

2.10.4查询结果的种类

1.查询结果----符合要求的数据
1）多行多列
2）多行单列
3）单行单列
4）单行多列
Select * from emp；-----多行多列的查询结果

Select job from emp；------多行单列的查询结果

Select job from emp where ename=‘SMITH’; ------单行单列的查询结果

SELECT * FROM DEPT where LOC=‘NEW YORK’; ------单行多列的查询结果

2.对于子查询而言，至于出现在哪个子句中，并没有明确的结论
1）WHERE 子句中，当子查询返回单行单列/单行多列/多行单列的情况下，一般会出现在where子句中
2）Having子句中，子查询返回单行单列，并且需要统计操作的时候，一般会会在having子句中
3）FROM子句，子查询返回多行多列数据（表）可以在FROM子句中出现，按照表的形式处理（最麻烦的）
4）SELECT子句，子查询返回单行单列，一般不建议在select中使用子查询
最常用的是在WHERE和FROM 子句中使用子查询
在使用子查询执行，要分析子查询的出现结果的格式，比如查询结果为
多行多列-----FROM子句
若某个查询的结果为多行多列，则可以出现在其他查询中的from子句后，作为子查询使用
多行单列------where子句
单行单列-----where子句
单行多列-----WHERE子句
若某个查询结果为上述三种情况，则可以出现在其他查询中的where子句后，作为子查询使用
单行单列-----having子句

2.10.5为什么会有子查询

主要是为了解决查询的性能问题，通过子查询可以编写性能更高的查询方式提升数据的操作质量。
2.10.6WHERE子句使用子查询
WHERE子句的主要作用：是限制数据表中数据行的显示，是作为数据的筛选使用。
1 Where 子句使用子查询—单行单列
1.例如：
1）查询出公司工资最低的雇员信息
查询出公司最低工资
SQL> SELECT MIN(sal)
2 FROM emp;

转化需求----》查询出最低工资人员的所有信息
SQL> SELECT *
2 FROM emp
3 WHERE sal=(
4 SELECT MIN(sal)
5 FROM emp
6 );

2）查询高于公司平均工资的所有雇员的信息
查询出公司所有人员的平均工资
SQL> SELECT AVG(SAL)
2 FROM emp;

转化需求----》查询出现高于平均工资的所有人员的信息
SQL> SELECT *
2 FROM emp
3 WHERE sal>(
4 SELECT AVG(sal)
5 FROM emp
6 );

2Where 子句使用子查询—单行多列
单行多列的子查询放入where子句时，可以将查询字段用（）括起来使用
1）查询出与scott工作相同，工资相同的雇员信息
A)先查询出scott的工资+工作
SQL> SELECT job,sal
2 FROM emp
3 WHERE ename=‘SCOTT’;

B）查询出所有工作为ANALYST，工资为3000的所有人员信息
SQL> SELECT *
2 FROM emp
3 WHERE job=‘ANALYST’ AND sal=3000;
表示scott工作工资相同的人是ford，因此就是查询ford的个人信息。

转化需求查询出现其他人员工和scott工资和工作一样的员工，但是不包括scott
SQL> SELECT *
2 FROM emp
3 WHERE (job,sal)=(
4 SELECT job,sal
5 FROM emp
6 WHERE ename=‘SCOTT’
7 )
8 AND ename!=‘SCOTT’;

3 Where 子句使用子查询—多行单列(范围)
多行单列的子查询在where中用，
一旦子查询返回多行单列的数据，实际上所返回的内容就属于一个数据的范围了，那么对应范围的查询在子查询中定义有三种形式： IN ANY ALL
1.查询返回多行单列的数据范围的三种形式：
1）IN 和 NOT IN ----》不连续的范围
IN操作：表示在范围内，这一点与之前，讲解的限定查询中的IN是完全相同的
A. 询公司中薪资等于MANAGER 薪资的雇员信息
SQL> SELECT sal
FROM emp
WHERE job=‘MANAGER’;
查询经理的工资

转发为
SELECT *
FROM emp
WHERE sal IN (
SELECT sal
FROM emp
WHERE job=‘MANAGER’
);

B.查询公司雇员薪资不在MANAGER 薪资里的的雇员信息
先查询工资manager职位的人的薪资，然后以manager的薪资作为范围，然后查询不是这个薪资范围内的雇员信息
SQL> SELECT *
2 FROM emp
3 WHERE sal NOT IN (
4 SELECT SAL
5 FROM EMP
6 WHERE JOB =‘MANAGER’
7 );

2）ANY
A )ANY操作，ANY操作一共有三种使用形式：
A）=ANY-----》等同于使用IN的使用方法----》等于范围内任何一个值
B）>ANY----》大于范围内的任何一个值-----》大于范围内的最小值
例如： （2450，2975，2850）
C）<ANY----》小于范围内的任何一个值------》小于范围内的最大值
B)例如
a询公司中薪资等于MANAGER 薪资的雇员信息

SELECT *
FROM emp
WHERE sal =ANY (
SELECT sal
FROM emp
WHERE job=‘MANAGER’
);
=ANY的作用和IN的作用一样

b询公司中薪资大于MANAGER 薪资的雇员信息
SELECT *
FROM emp
WHERE sal>ANY (
SELECT sal
FROM emp
WHERE job=‘MANAGER’
);
>ANY表示大于范围内的任何一个值，因为经理的工资范围为（2450，2975，2850）
而2975又大于2450和2850所以会被查询到，2850同理，但2450就不大于范围内的任何一个值因此这个就不会被查询到

c询公司中薪资小于MANAGER 薪资的雇员信息
SELECT *
FROM emp
WHERE sal<ANY (
SELECT sal
FROM emp
WHERE job=‘MANAGER’
);
<ANY表示小于范围内的任何一个值，经理的工资范围为（2450，2975，2850）
而2450小于2975和2850所以会查询到，2850同理，而2975没有小于范围内的任何一个数，因此不会被查询到

3）ALL
A )ALL操作，ALL操作一共有2种使用形式：
a）>ALL----》比范围内所有的值都大----》大于最大值
b）<ALL----》比范围内所有的值都小-----》小于最小值
B)例如
a询公司中薪资大于MANAGER 薪资的雇员信息
SELECT *
FROM emp
WHERE sal>ALL (
SELECT sal
FROM emp
WHERE job=‘MANAGER’
);

b询公司中薪资小于MANAGER 薪资的雇员信息
SELECT *
FROM emp
WHERE sal<ALL (
SELECT sal
FROM emp
WHERE job=‘MANAGER’
);

2.总结:where子句多行单列子查询时的关系运算

1. IN 和NOT IN
   ----->指定字段值等于子查询中的某一个值.就可以被查询到
2. =ANY ANY
   =ANY和IN效果相同
   ----->指定字段值等于子查询中的某一个值.就可以被查询到
   >ANY
   ----->指定字段值大于子查询结果中最小值.就可以被查询到
   <ANY
   ----->指定字段值小于子查询结果中的最大值.就可以被查询到

3. ALL <ALL
   >ALL
   ----->指定字段值大于子查询结果中的最大值.就可以被查询到
   <ALL
   ----->指定字段值小于子查询结果中的最小值.就可以被查询到

2.10.5HAVING子句使用子查询

如果要在HAVING子句中使用子查询，一般而言都表示子查询返回的是单行单列，并且需要使用到分组统计的时候，HAVING出现的前提是GROUP BY 分组统计
Having子句是对分组后的数据进行过滤的子句，也可以在having子句中使用子查询，这个就是having子查询
1.例如
1）查询出平均薪资高于所有员工平均工资的职位的名称、以及职位的人数、这些高于平均工资的职位的平均工资
a）先查询出现所有员工平均工资
SQL> select avg(Sal) from emp;

2)以所有员工平均工资作为分组后过滤条件
SQL> SELECT job,COUNT(ename),AVG(sal)
2 FROM emp
3 GROUP BY job
4 HAVING AVG(sal)>(
5 SELECT AVG(sal)
6 FROM emp
7 );

2）查询出平均薪资最高的职位名称和该职位的平均工资
a）先分组后查询出现组的最高的平均薪资
SQL> SELECT MAX(AVG(sal))
2 FROM EMP
3 GROUP BY job;
为什么不能在select后没加job字段，因为按照job分组会出现多个组，此时只求平均值会出现每个组对应的平均值，如果在求平均值的函数上求最大值的平均值，只能显示一个数据值，而一个值无法对应多个分组，所有不能添加job字段。

转化为
SQL> SELECT job,AVG(sal)
2 FROM emp
3 GROUP BY job
4 HAVING AVG(sal)=(
5 SELECT MAX(AVG(sal))
6 FROM emp
7 GROUP BY job
8 );

3）查询出薪资排名第三的雇员的信息（不考虑并列薪资）
先查看最高薪资
SQL> SELECT MAX(sal)
2 FROM emp;

SQL> SELECT *
2 FROM emp
3 WHERE sal=(
4 SELECT MAX(SAL)
5 FROM EMP
6 WHERE SAL<(
7 SELECT MAX(SAL)
8 FROM EMP
9 WHERE SAL<(
10 SELECT MAX(SAL)
11 FROM EMP)));

4）查询出所有高于公司平均工资的职位名称，职位人数以及平均工资
A查询出公司的平均工资
SQL> SELECT AVG(sal)
2 from emp
A查询出各个职位的平均工资----》所有个部门平均工资
SQL> SELECT AVG(sal),JOB
2 from emp
GROUP BY JOB;
C.所有个部门平均工资高于公司的平均工资
SQL> SELECT AVG(sal),JOB
2 from emp
GROUP BY JOB
HAVING AVG(SAL)>(
SELECT AVG(sal)
from emp
);

2.10.6 FROM子句使用子查询—别名

1.什么是from子查询
FROM子句的数据来源不仅仅可以是数据表，还可以是一个查询的
结果（一般是多行多列类型的数据）,把一个查询的结果作为主查询的数据来源，这就是FROM子查询。
FROM子句在开发之中其中最主要的作用是确定要使用的数据表，所谓数据表就是行和列的集合，如果子查询返回的是多行多列的数据，就需要使用from子句查询
2.注意----子查询定义别名
1.规范的做法就需要为子查询定义别名。
1.FROM子查询的结果作为主查询的数据来源，子查询的结果相当于一张临时数据表。
3.如果要使用子查询的字段则应该使用“别名.字段名”的方式进行引用。
3例如
1）查询出平均工资高于2000的职位名称以及该职位的平均工资
先查询出每个职位的平均工资
SQL> select job jobtest, avg(sal) saltest
2 from emp emptest
3 group by job;

在临时表中查询平均工资高于2000的职位名称以及该职位的平均工资，将这个临时表emptest作为查询数据源的依据

2）查询各部门的编号，名称，位置，部门人数，平均工资
A）查询各部门的编号，名称，位置
SELECT deptno,dname,loc
FROM dept;
B）查询各部门人数，平均工资
SELECT deptno,COUNT(empno),AVG(sal)
FROM emp
GROUP BY deptno;
C）查询各部门的编号，名称，位置，部门人数，平均工资
SELECT d.deptno,d.dname,d.loc,test.ctest, test.atest----表别名.列别名
FROM dept d,(
SELECT deptno,COUNT(empno) ctest,AVG(SAL) atest
FROM emp
GROUP BY deptno
) test----》别名
WHERE d.deptno=test.deptno(+);
在使用from子查询是，需要给子查询起别名，
分析：子查询
使用是在 from后面跟上一个查询语句嵌套，此时数据量是由化的，dept表由4行数据， 而经过分组查询后，emp的数据从原来的14行运行减少到3行运行，此时笛卡尔积是4*3=12行数据，总的数据量就是14+12=26行，对应多表查询缩减一半的数据量，

分析：多表查询
select d.deptno,d.dname,d.loc,count(dname),avg(sal)
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.dname,d.deptno,d.loc;
使用多表查询也可以出现想要的查询结果，但是数据量不同，emp表有14行数据，dept表有4行数据，使用多表查询就会出现笛卡儿积就是56行数据量，

D）对于同样的一种查询操作，有了两种实现方案，有什么区别？
------》多字段分组（多表查询），子查询模式。
区别：若使用多表查询，需要处理的数据量：
Emp14行dept4行===总数据量56行
若在FROM子句中引入子查询，然后进行多表查询
子查询的数据量14行-----》emp
生成临时的表数据量3行-----》test
而另一个表的数据量为4行----》dept
子查询的总的数据量为=emp+depttest=14+12=26行
4.结论
使用FROM子句编写的子查询其性能一定会更高，所以子查询的出现可以有效的解决多表查询的性能问题。在实际进行项目开发的过程中，到处都在使用子查询和子查询嵌套。
将多表查询替换为子查询最高效的一种方式。

2.10.7EXISTS运算

EXISTS（存在的意思）运算是一种根据子查询是否有数据的形式来判断条件是否成立的运算符
用于判断子查询有无结果的，但是不在乎结果是什么，有结果返回真，没结果返回假
1.例如
1）子查询返回数据则表示判断条件成立
A查询部门编号的名称
SQL> SELECT dname
2 FROM dept;
有数据
SQL> SELECT *
2 FROM dept
3 WHERE EXISTS(
4 SELECT dname
5 FROM dept
6 );

SQL> select mgr
2 from emp
3 where ename=‘KING’;

2）子查询不返回数据则表示判断条件不成立
SQL> SELECT dname
2 FROM dept
3 WHERE 1=2;
没有数据

SQL> SELECT *
2 FROM dept
3 WHERE EXISTS(
4 SELECT dname
5 FROM dept
6 WHERE 1=2
7 );

EXISTS最大的特征，只是简单判定是否有数据返回，而并不关系具体的返回数据是什么。在复杂查询中常用
2.面试题
请解释in与EXISTS的区别
In需要明确的进行数据判断，也就是要以子查询中返回的数据内容作为范围进行判断。
Exists不需要参考具体的返回内容，只要依靠是否有数据返回来判断条件是否成立。