主要内容：

本文主要讲解子查询以及案列讲解

子查询：

首先咱们先弄明白什么是子查询：select 查询语句中嵌套 select 语句，被嵌套的 select 语句是子查询。、

什么是子查询弄明白了，那它都可以出现在哪里呢？

select

... ... (select)

from

... ... (select)

where

... ... (select)

也就是说 select 子查询可以出现在以上任何位置。

案例：

• where 子句中嵌套子查询

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

emp 表中的信息都是月工资，首先我们要求出平均工资 avg(sal)，然后用 where sal > avg(sal)。如果使用在子查询，我们可以把这两个步骤写在同一个查询语句中。

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

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-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.02 sec)

小贴士：子查询都要用小括号括起来

mysql> select avg(sal) from emp;

+-------------+

| avg(sal) |

+-------------+

| 2073.214286 |

+-------------+

1 row in set (0.00 sec)

• from 后面嵌套子查询

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

想要找出薪水等级，要和 salgrade 表进行连接查询。再看题目，找出的是每个部门的平均薪水，要按照部门进行分组吧，分完组之后通过子查询进行计算每一组的平均薪资。

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

mysql> select t.*,s.grade from (select deptno,avg(sal) 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.11 sec)

• 在 select 后面嵌套子查询

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

通过自查询，把部门名查询出来

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

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.04 sec)