【MySQL】十八、where，from，select后面嵌套子查询

1. 什么是子查询？子查询都可以出现在哪里？

select 语句当中嵌套select语句，被嵌套的select语句时子查询。

子查询可以出现在哪里？

select
	...(select)...
from
	...(select)...
where
	...(select)...

2. where子句中嵌套子查询

案例：找出高于平均薪资的员工信息。

select * from emp where sal > avg(sal); //错误

以上是一种错误的写法，牢记：where后面不能直接使用分组函数

正确写法：

第一步：找出平均薪资；

select avg(sal)  from emp;

+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+

第二步：where过滤

select * from emp where sal > 2073.214286 ;

+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+
6 rows in set (0.00 sec)

第一步和二合并步（在where子句中使用了子查询）：

select * from emp where sal > (select avg(sal) from emp);

查询结果同上。

3. from子句中嵌套子查询

案例1： 找出每个部门平均薪水的薪资等级

第一步：计算每个部门的平均工资（按照部门编号分组）：

select deptno, avg(sal)  as avgsal  from emp group by deptno;

+--------+-------------+
| deptno | avgsal	   |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

第二步：对每个部门的平均工资进行分级：

我们可以将上面查询出的结果当作一张临时新表t，与表salgrade进行连接查询，条件为
t.avgsal between s.losal and s.hisal

select 
	t.deptno, t.avgsal, s.grade
from 
	(select deptno, avg(sal) as avgsal from emp group by deptno) t 
join 
	salgrade s 
on 
	t.avgsal between s.losal and s.hisal;

+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     10 | 2916.666667 |     4 |
|     20 | 2175.000000 |     4 |
|     30 | 1566.666667 |     3 |
+--------+-------------+-------+
3 rows in set (0.00 sec)

案例2： 找出每个部门平均的薪资等级。

第一步：求出每个部门每个员工的薪水等级

select 
	e.deptno, e.ename, e.sal, s.grade  
from 
	emp e 
join 
	salgrade s 
on 
	e.sal between losal and hisal;

+--------+--------+---------+-------+
| deptno | ename  | sal     | grade |
+--------+--------+---------+-------+
|     20 | SMITH  |  800.00 |     1 |
|     30 | ALLEN  | 1600.00 |     3 |
|     30 | WARD   | 1250.00 |     2 |
|     20 | JONES  | 2975.00 |     4 |
|     30 | MARTIN | 1250.00 |     2 |
|     30 | BLAKE  | 2850.00 |     4 |
|     10 | CLARK  | 2450.00 |     4 |
|     20 | SCOTT  | 3000.00 |     4 |
|     10 | KING   | 5000.00 |     5 |
|     30 | TURNER | 1500.00 |     3 |
|     20 | ADAMS  | 1100.00 |     1 |
|     30 | JAMES  |  950.00 |     1 |
|     20 | FORD   | 3000.00 |     4 |
|     10 | MILLER | 1300.00 |     2 |
+--------+--------+---------+-------+
14 rows in set (0.00 sec)

第二步：对上述结果看做一张新表t，对每个部门薪资等级的平均值（对部门进行分组求均值）；

select 
	t.deptno, avg(t.grade) 
from
	(select e.deptno, e.ename, s.grade  from emp e join salgrade s on e.sal between losal and hisal) t
group by
	t.deptno;

+--------+--------------+
| deptno | avg(t.grade) |
+--------+--------------+
|     10 |       3.6667 |
|     20 |       2.8000 |
|     30 |       2.5000 |
+--------+--------------+
3 rows in set (0.00 sec)

但是，这里实际上没必要把新表看做一张临时表，这里其实可以直接写：

select 
	e.deptno, avg(s.grade) 
from 
	emp e 
join 
	salgrade s 
on 
	e.sal between losal and hisal
group by
	deptno;

查询结果与上述一致：

+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
|     10 |       3.6667 |
|     20 |       2.8000 |
|     30 |       2.5000 |
+--------+--------------+

4. 在select后面嵌入子查询

案例： 找出每个员工所在的部门名称，要求显示员工名和部门名；

方法一：使用内连接的方式：

（1）第一步： 找出每个员工所在的部门编号：

select ename, deptno from emp;

（2）第二步： 关联dept表：

select e.ename, e.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;

查询结果：

+--------+--------+------------+
| ename  | deptno | dname      |
+--------+--------+------------+
| SMITH  |     20 | RESEARCH   |
| ALLEN  |     30 | SALES      |
| WARD   |     30 | SALES      |
| JONES  |     20 | RESEARCH   |
| MARTIN |     30 | SALES      |
| BLAKE  |     30 | SALES      |
| CLARK  |     10 | ACCOUNTING |
| SCOTT  |     20 | RESEARCH   |
| KING   |     10 | ACCOUNTING |
| TURNER |     30 | SALES      |
| ADAMS  |     20 | RESEARCH   |
| JAMES  |     30 | SALES      |
| FORD   |     20 | RESEARCH   |
| MILLER |     10 | ACCOUNTING |
+--------+--------+------------+

方法二：在select后面嵌入子查询

第一步：找出部门名：
select deptno, dname from dept;
第二步：嵌套查询

select
	e.ename, e.deptno, (select dname from dept d where d.deptno = e.deptno) as dname 
from 
	emp e;

查询结果与方法一结果一致。