（一）基本介绍
select语句中可以嵌套select语句，被嵌套的select语句被称为子查询。
（二）应用场景
select ...(select ) from ...(select ) where ...(select)..
（三）具体实例
where
实例：找出比最低工资高的员工姓名和工资？？
mysql> select ename,sal from emp where sal>min(sal);
ERROR 1111 (HY000): Invalid use of group function
#错误原因：where子句中不能直接使用分组函数
#正确方法如下
#第一步：找出emp表中sal的最小值
mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
| 800.00 |
+----------+
1 row in set (0.00 sec)
#第二步：根据最小值来进行选择和输出符合条件的员工名字和薪水
mysql> select ename,sal from emp where sal>800;
+--------+---------+
| ename | sal |
+--------+---------+
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
13 rows in set (0.00 sec)
#方法二：通过子查询实现（先执行‘ 子查询 ’，然后再执行‘ 外查询 ’）
mysql> select ename,sal from emp where sal>(select min(sal) from emp);
+--------+---------+
| ename | sal |
+--------+---------+
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
13 rows in set (0.00 sec)
mysql>
from
实例：找出每个岗位的平均工资的薪资等级
分析：
第一步：根据岗位进行分组，并求出每个分组中的薪水平均值
select job,avg(sal) from emp group by job;
第二步：将上表（假设为 s）和 薪资等级表（s）进行连接，可得目标结果
select
t.* ,s.grade
from
( select job,avg(sal) as avgsal from emp group by job) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
#未起别名
mysql> select job,avg(sal) from emp group by job;
+-----------+-------------+
| job | avg(sal) |
+-----------+-------------+
| CLERK | 1037.500000 |
| SALESMAN | 1400.000000 |
| MANAGER | 2758.333333 |
| ANALYST | 3000.000000 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+
5 rows in set (0.00 sec)
#起别名
mysql> select job,avg(sal) as avgsal from emp group by job;
+-----------+-------------+
| job | avgsal |
+-----------+-------------+
| CLERK | 1037.500000 |
| SALESMAN | 1400.000000 |
| MANAGER | 2758.333333 |
| ANALYST | 3000.000000 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+
5 rows in set (0.00 sec)
mysql> select t.* ,s.grade from ( select job,avg(sal) as avgsal from emp group by job) t join salgrade s on t.avgsal between s.losal and s.hisal;
+-----------+-------------+-------+
| job | avgsal | grade |
+-----------+-------------+-------+
| CLERK | 1037.500000 | 1 |
| SALESMAN | 1400.000000 | 2 |
| MANAGER | 2758.333333 | 4 |
| ANALYST | 3000.000000 | 4 |
| PRESIDENT | 5000.000000 | 5 |
+-----------+-------------+-------+
5 rows in set (0.00 sec)
注意：from后面的子查询，可以将子查询的查询结果当做一张临时表。（技巧）
select
案例：找出每个员工的部门名称，要求显示员工名、部门名？？
mysql> select e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
错误情况：
mysql> select e.ename,(select dname from dept) as dname from emp e;
ERROR 1242 (21000): Subquery returns more than 1 row
#说明：dept中的记录个数是四个，并不是一个，所以说在匹配时候会出现错误
mysql> select dname from dept;
+------------+
| dname |
+------------+
| ACCOUNTING |
| RESEARCH |
| SALES |
| OPERATIONS |
+------------+
4 rows in set (0.00 sec)
说明：select后面的子查询只能返回一条结果，如果多于一条就会报错
