什么是子查询： select语句中嵌套着select语句，被嵌套的语句就是子查询。
它们可以出现在
select
..(select)from
..(select)where
..(select)123456
例子：
1. 在where中嵌入
找出高于平均薪资员工的信息
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-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 |
+-------+-------+-----------+------+------------+---------+------+--------+*/1234567891011
2. from后面嵌套子查询
找出每个部门平均薪水的薪资等级
第一步：找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;+--------+-------------+| deptno | avgsal |+--------+-------------+| 10 | 2916.666667 || 20 | 2175.000000 || 30 | 1566.666667 |+--------+-------------+12345678
第二部：把上表作为一个临时的表d，然后和等级的那个表连接
select d.deptno,s.grade
from (select deptno,avg(sal) as avgsal from emp group by deptno) d
join salgrade s
on d.avgsal between s.losal and s.hisal;+--------+-------+| deptno | grade |+--------+-------+| 10 | 4 || 20 | 4 || 30 | 3 |+--------+-------+123456789101112
- 在select后面嵌套子查询
找出每个员工所在的部门名称，要求显示员工名和部门名
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;//这是使用的内连接
