### 1\. 子查询的概念
##### 1.1 子查询的概念和理解
某些查询逻辑中，需要引入另一个查询做为条件或者数据来源进行辅助。
比如：查询与‘TOM’在同一部门工作的其他员工
```
select * from emp where deptno = TOM的deptno and
ename <> 'TOM';
```
TOM的deptno
```
select deptno from emp where ename = 'TOM';
```
将两个独立的查询合并起来
```
select * from emp
where deptno = (select deptno from emp
where ename = 'TOM')
and ename <> 'TOM';
```
整个外部这部分SQL语句被称为外部查询（主查询），括号内查询TOM部门号的这个SQL语句被称为子查询
##### 1.2 子查询使用位置
子查询可以使用在很多子句中
* from子句中，from子句中的子查询可以理解为是一张临时的“表”（视图）
* where子句中
* having子句中
通常主要使用在where和from中
### 2\. 简单子查询（单行子查询）
**单行子查询的查询结果是一行一列的数据**
可以使用常规的> ,<, >=, <=, =, !=进行子查询数据的比较
可以将子查询理解为就是一个简单的数据
示例1：查询比TOM月薪高的员工信息
```
select * from emp where sal > (select
sal from emp where ename = 'TOM')
```
示例2：查询与LEE是同一职位同一部门的其他员工信息
```
select *
from emp
where job = (select job from emp where
ename = 'LEE')
and deptno = (select deptno from emp where
ename = 'LEE')
and ename <> 'LEE'
```
**子查询中可以嵌套其他的子查询，层数限制在32层内**
示例3：查询月薪高于AMY所在部门平均月薪的员工信息
```
select *
from emp
where sal > (select AVG(sal) from emp
where deptno = (select deptno from emp where ename = 'AMY'))
```
### 3\. 多行子查询
##### 3.1 多行子查询
**多行子查询返回的是多行一列的数据**
当子查询返回多行记录时，使用=这类的比较运算符无法执行
当有多条记录返回时，使用IN来进行比较，相当于等于子查询中某个值即可
示例4：查询是经理的员工信息（=）
```
select *
from emp
where empno in (select distinct mgr from
emp where mgr is not null)
```
示例5：查询不是经理的员工信息（!=）
```
select *
from emp
where empno not in (select distinct mgr
from emp where mgr is not null)
```
**not in中的数据不能有null值，如果有null值，相当于执行了=null操作，不能筛选出任何数据**
##### 3.2 ANY（SOME）与ALL
主要使用在多行子查询中进行>或<与操作时
ANY（SOME）：任何一个
ALL：所有
> ANY 比最小的大
> ALL 比最大的大
> < ANY 比最大的小
> < ALL 比最小的小
示例6：查询比10部门所有人月薪都要高的员工信息
```
select *
from emp
where sal > ALL(select sal from emp
where deptno = 10)
```
也可以使用
```
select *
from emp
where sal > (select MAX(sal) from emp
where deptno = 10)
```
使用ANY和ALL执行效率要高于分组函数
##### 3.3 from子句中的子查询
将子查询运用在from中，相当于一张“表”
**必须为作为“表”的子查询起“表别名”（示例7中的t）**
示例7：查询部门编号，部门名称，部门loc，部门人数
```
select
d.deptno,d.dname,d.loc,IFNULL(t.empnum,0)
from dept d left join
(select deptno, COUNT(empno) as empnum
from emp
where deptno is not null
group by deptno) t
on(d.deptno = t.deptno)
```
**在MySQL中,update语句分组函数的结果不能作为子查询的返回结果**
```
update emp set sal = sal + 200
where sal < (select avg(sal) from emp);
```
MySQL中认为更新和子查询不能同时进行。
解决办法：将子查询再次进行嵌套，制作成From中的子查询
```
update emp set sal = sal + 200
where sal < (select asal from(select
avg(sal) from emp) t);
```
### 4\. 相关子查询（难点）
前3节的子查询都属于**独立子查询**，子查询可以独立执行
相关子查询：子查询不可独立执行，必须依赖外部查询
##### 4.1 常规的相关子查询
示例8：查询比自己所在部门平均月薪高的员工信息
常规写法
```
select * from emp e
join (select deptno, AVG(sal) as asal
from emp
group by deptno) t
on(e.deptno = t.deptno)
where e.sal > t.asal
```
相关子查询写法
```
select * from emp e1
where e1.sal > (select AVG(sal) from emp
e2 where e2.deptno = e1.deptno)
```
**相关子查询的特点：外部查询执行一行数据，子查询执行一次**
##### 4.2 EXIST关键字
子查询用于验证外部查询的一行记录是否符合子查询的条件
示例9：查询是经理的员工的员工信息
```
select *
from emp e1
where exists (select empno from emp e2
where e2.mgr = e1.empno)
```
如果想表述不存在的逻辑，使用NOT EXISTS
C���P
