什么是子查询？
select 语句中嵌套select语句，被嵌套的select的语句是子查询。
子查询都可以出现在哪里？
select
...(select).
from
...(select).
where
...(select).
where语句中使用子查询
案例：找出高出平均工资的员工信息。
select * from emp where sal > avg(sal); // 错误的写法，where后面不能直接使用分组函数
第一步：找出平均工资
mysql> select avg(sal) from emp;
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.03 sec)
第二步：where过滤
mysql> 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-07-13 | 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.13 sec)
第一步和第二步合并：
mysql> select * from emp where sal > (select avg(sal) from emp);
+-------+-------+-----------+------+------------+---------+------+--------+
| 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-07-13 | 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.03 sec)
from后面嵌套子查询
案例：找出每个部门的平均薪水的薪资等级；
第一步：找出每个部门平均薪水(按照部门编号分组，求sal的平均值)
mysql> 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.02 sec)
第二步：将以上查询结果当成临时表t,让t表和salgrade s表连接，条件：t.avgsal between s.losal and hisal
select
t.*,s.grade
from
join
salgrade s
on
t.avgsal between s.losal and s.hisal
真实sql
mysql> select
-> t.*,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 |
+--------+-------------+-------+
| 30 | 1566.666667 | 3 |
| 10 | 2916.666667 | 4 |
| 20 | 2175.000000 | 4 |
+--------+-------------+-------+
3 rows in set (0.23 sec)
案例：找出每个部门薪资等级的平均值。
第一步：找出每个员工的薪资等级
mysql> select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal
between s.losal and s.hisal;
+--------+---------+--------+-------+
| ename | sal | deptno | grade |
+--------+---------+--------+-------+
| SMITH | 800.00 | 20 | 1 |
| ALLEN | 1600.00 | 30 | 3 |
| WARD | 1250.00 | 30 | 2 |
| JONES | 2975.00 | 20 | 4 |
| MARTIN | 1250.00 | 30 | 2 |
| BLAKE | 2850.00 | 30 | 4 |
| CLARK | 2450.00 | 10 | 4 |
| SCOTT | 3000.00 | 20 | 4 |
| KING | 5000.00 | 10 | 5 |
| TURNER | 1500.00 | 30 | 3 |
| ADAMS | 1100.00 | 20 | 1 |
| JAMES | 950.00 | 30 | 1 |
| FORD | 3000.00 | 20 | 4 |
| MILLER | 1300.00 | 10 | 2 |
+--------+---------+--------+-------+
14 rows in set (0.00 sec)
第二步：基于以上的结果，继续按照 deptno 分组，求grade的平均值
mysql> select
-> e.deptno,avg(s.grade)
-> from
-> emp e
-> join
-> salgrade s
-> on
-> e.sal between s.losal and s.hisal
-> group by
-> e.deptno;
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
+--------+--------------+
3 rows in set (0.16 sec)
在select后面嵌套子查询。
案例：找出每个员工所在的部门名称，要求显示员工名和部门名。
第一步:找出每个员工所在的部门名称
mysql> select
-> e.ename,d.dname
-> from
-> emp e
-> join
-> dept d
-> where
-> e.deptno = d.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| CLARK | ACCOUNTING |
| KING | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH | RESEARCH |
| JONES | RESEARCH |
| SCOTT | RESEARCH |
| ADAMS | RESEARCH |
| FORD | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| MARTIN | SALES |
| BLAKE | SALES |
| TURNER | SALES |
| JAMES | SALES |
+--------+------------+
14 rows in set (0.00 sec)
方法2：
select
e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname
from
emp e;