子查询
1. 案例 ：找出薪水比公司平均员工高的员工，要求显示员工名字和薪水
mysql> select ename, sal from emp where sal > (select avg(sal) from emp);
+--------+------+
| ename | sal |
+--------+------+
| JONES | 2975 |
| BLAKE | 2850 |
| CLARK | 2450 |
| SCOTT | 3000 |
| KING | 5000 |
| FORD | 3000 |
+--------+------+
2. 案例：找出每个部门的平均薪水，并显示平均薪水的等级
// from 后面子查询，把查询结果当作临时表
mysql> 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 |
+--------+-----------+-------+
| 20 | 2175.0000 | 4 |
| 30 | 1566.6667 | 3 |
| 10 | 2916.6667 | 4 |
+--------+-----------+-------+
3 rows in set (0.00 sec)