废话引言

我有一个梦想，有一天能跟上单大师敲sql的速度QAQ
本文数据库同数据库SQL语句课堂总结（1）
主要介绍子查询的相关语句

正文开始

子查询介绍
　　定义：

子查询允许把一个查询嵌套在另一个查询当中。

　　子查询，又叫内部查询，相对于内部查询，包含内部查询的就称为外部查询。

　　子查询可以包含普通select可以包括的任何子句，比如：distinct、 group by、order by、limit、join和union等；但是对应的外部查询必须是以下语句之一：select、insert、update、delete、set或 者do。

　　子查询的位置： 
　　　　select 中、from 后、where 中.group by 和order by 中无实用意义。

例题（相干子查询与不相干子查询）

?先来试试？
1.找出部门员工数>3的部门名称，部门编号

第一个的子查询是相干子查询
是不可以直接查到结果的，因为它需要dept.deptno 但是他不知道。
select count(*) from emp where emp.DEPTNO=DEPT.DEPTNO表示汇总得到的每个部门的员工数

第二个子查询是不相干子查询
子查询可以直接得到表
(select DEPTNO from emp
group by DEPTNO having count(*)>3)
意为将员工根据部门分类之后对于每个分组的分组属性进行筛选，再得到被保留的行的我们觉得有意义的属性，部门编号。
问题就转化为请查询部门标号是20或30的部门的信息

//代码1
select dname,deptno
from dept 
where (select count(*) from emp
	   where emp.DEPTNO=DEPT.DEPTNO)>3
//代码2
select dname ,deptno
from dept
where DEPTNO in
(select DEPTNO from emp
group by DEPTNO having  count(*)>3)

经过本题得到对相干子查询和不相干子查询的总结

判断是否相关，要看在子查询中是否使用了外层查询所列出的字段

相干 : 对于外部每一行 内部都执行一次
上述代码流程：先找到dept表，对dept表的每一行的deptno，都做括号内的运算，然后将运算结果与3进行比较，>3 where返回true，该行被保留。
重点：内部相干子查询需要外部给他一个数那就是dept.deptno,不然where无法判断

不相干: 首先执行子查询一次 得到结果集 把结果作为常量送到外层执行外层子查询
上述代码流程：先在emp表中，将员工根据部门编号分类之后，这样就不需要查询dept.deptno，再对于每个分组（此时分组就是一个部门）的分组属性（部门人数）进行筛选，再得到被保留的行的我们觉得有意义的属性，部门编号，问题就转化为请查询部门标号是20或30的部门的信息。
重点：内部不相干子查询不在意外部在干嘛，它做的就是利用自己调用的emp表返回人数>3的部门编号，他不需要外部给它什么信息，他自己就能行。

相干子查询和不相干子查询代表了两种思维方式
相干直接从最终目的入手，看这个部门人数是不是大于3？（这个条件自然被放在where后面）
不相干子查询倒着退，先把料备好（把20和30得到）

?再来一道
2.查询平均工资高于1800的部门工作的员工

相干的较难理解，解析如下
我们一行一行的看员工，第一行发现员工A，那么我该不该输出他的信息呢
于是我就问数据库啊，就和这个A一起干活的人，把他们的工资加起来再加A的能不能过1800啊
数据库表示让我先查查，那么对于A，我先按照A的部门号再遍历一遍emp表。先找人！把这些人汇总成表，在对这个得到的表汇总工资求平均，得到数字，就知道满不满足>1800的条件了！
显然，这个听起来就很麻烦，对于每一个员工都要遍历一遍emp表，即使A和B是一个部门的同事，数据库还是要重新算这个部门的平均工资。
重点：相干子查询始终要参照点外部的东西，你得给他A的部门号，他才能生成和这行（行代表一个人 假设是A）有关的部门信息表
注意表的重命名：重命名之后要对表明确 重命名之后相当于表的新的视图 或者说是对象的引用

相干

select emp.ENAME,emp.DEPTNO,emp.sal
from emp
where (select avg(sal) from emp t where t.DEPTNO=emp.DEPTNO)>1800

不相干

先找到部门，再去看员工，虽然我们也没有调用部门表，但是我们用员工的部门编号分组了鸭！就自然得到部门了鸭！

select emp.ENAME,emp.DEPTNO
from emp
where emp.deptno in
(select DEPTNO from emp 
group by deptno having avg(sal)>1800
)

3.找到没有员工的部门

exist函数
–输入集合 --输出true/false
–集合非空返回true --用于检查输入集合是否存在元素

思路：对于每个部门，我看他对应的员工集合是否为空。
为空就返回它，因此要加not
对于每个部门（表里的每一行）检查他是否能使where条件为真
这个效率还是蛮高的，只要找到一个员工他就知道不为空了，就检查部门表的下一行。
一般来说用了exist都是相关子查询，但是不绝对。
是否相关核心在于子查询的判断条件是否使用了外层所提供的表

select dept.DEPTNO ,DEPT.DNAME from dept
where not exists(select * from emp where emp.DEPTNO=DEPT.deptno)

4.找出比三十号部门任一员工工资高的员工

观察题目：我们觉得这是一个值（该员工）和一个集合（30号部门员工）的工资比较
任一 比min 大
子查询返回的是列表集合
–只有当我们知道只返回一个值的时候才可以直接sal>(select sal from emp where DEPTNO=30)
将值和集合的比较转化为值和值
any 表示任一 内部连接是or

select EMP.ENAME,EMP.SAL from emp
where sal>any(select sal from emp where DEPTNO=30)

5.找出比三十号部门任意员工工资高的员工

任意 比max 大
all 表示任意 内部连接是and
比集合中某些（或者某个）大就ok —>some

select EMP.ENAME,EMP.SAL from emp
where sal>all(select sal from emp where DEPTNO=30)

总结：任何关系运算输入输出都是表，而一个查询结果也是表
故能够放表的地方都可以放一个查询

ok，我们再来看一个问题

6.查询平均工资高于1800的部门工作的员工姓名 员工工资 和这个部门的平均工资

区别在于：当我们只差前两项的时候，我们只需要emp表就ok了，但是所在部门的平均工资信息实际是要从emp表的汇总表中推算出来的，但是这个汇总表并不在from的数据源中，但是子查询其实是可以得到这个信息的

//上一版代码
select emp.ENAME,emp.SAL from emp
where (select avg(sal) from  emp t
	   where t.DEPTNO=emp.DEPTNO)>1800
//升级版
//将子查询的表重命名，在员工信息上利用部门编号一致，将部门平均工资数加到emp表后
//t是子查询的表的别名
select ename,sal,t.avgsal
from emp join(select deptno,avg(sal)avgsal from emp
			group by deptno having avg(sal)>1500) t
on emp.DEPTNO=t.deptno
--为了增强可读性
--with：把一个不相干自查询结果赋予一个名字，在接下来的查询中直接使用
with t as ( select deptno,avg(sal)avgsal from emp
			group by deptno having avg(sal)>1500)
--with:学术名称 被称为临时视图
--连接也是一种过滤
--先用过滤构建一个表，再用连接条件。
--去重：distinct，除此之外，还可以用结果集union其本身来去重
select ename,sal,t.avgsal
from emp join t on emp.DEPTNO=t.deptno

7.找到在员工所在部门平均工资高于1500的 同时该员工职位的平均工资大于1500的员工

with t as ( select deptno,avg(sal)deptnoavgsal from emp
			group by deptno having avg(sal)>1500),
	 s as ( select job,avg(sal)jobavgsal from emp
			group by job having avg(sal)>1500)

select ename,sal,t.deptnoavgsal,s.jobavgsal
from emp
join t on t.deptno=emp.deptno
join s on s.job=emp.JOB

上述为一种不相干子查询
因为无论是t还是s都是独立的表
下面代码都可以运行
一定要有两个子查询，考虑部门不考虑职位，考虑职位不考虑部门。

with s as ( select job,avg(sal)avgsal from emp
			group by JOB having avg(sal)>1500)
select * from s
with t as ( select deptno,avg(sal)avgsal from emp
			group by deptno having avg(sal)>1500)
select * from t

8.要所有员工的姓名 工资 部门平均工资 职位平均工资（相干子查询）

select必须返回一行一列一个值 --原子值
这里的子查询为标量子查询（返回一行一列一个值 --原子值）
可以转化为一个值

select ename,sal
,(select avg(sal) from emp where emp.DEPTNO=t.DEPTNO)deptavgsal
,(select avg(sal) from emp where emp.job =t.job )jobavgsal
from emp t