子查询 - 《My SQL》

什么是子查询？子查询都可以出现在哪里？

select语句当中嵌套select语句，被嵌套的select语句是子查询。

子查询可以出现在哪里？

 select<br />            ..(select).<br />        from<br />            ..(select).<br />        where<br />            ..(select).<br />先执行子查询

where子句中使用子查询

案例：找出高于最低工资的员工
实现思路：
第一步：先找到最低工资
select min(sal) from emp;
         +----------+
         | min(sal) |
         +----------+
         |   800.00 |
         +----------+
第二步：找出比最低工资高的
select ename,sal from emp where sal > 800;
第三步：合并
select ename,sal from emp where sal > (select min(sal) from emp) order by sal;

from子句中的子查询

注意：from后面的子查询，可以将子查询的查询结果当做一张临时表（技巧）

案例：找出每个工作岗位的平均工资的薪资等级
第一步：找出每个岗位的平均工资（按照岗位来分组求平均值）
select job,avg(sal) avgsal from emp group by job;
            +-----------+-------------+
            | job       | avgsal         |
            +-----------+-------------+
            | CLERK     | 1037.500000 |
            | SALESMAN  | 1400.000000 |
            | MANAGER   | 2758.333333 |
            | ANALYST   | 3000.000000 |
            | PRESIDENT | 5000.000000 |
            +-----------+-------------+
第二步：将以上查询结果当做是一张真实的表j
select j.*,s.grade from (select job,avg(sal) avgsal from emp group by job) j join salgrade s on j.avgsal between losal and hisal; 
            +-----------+-------------+-------+
            | job       | avgsal         | grade |
            +-----------+-------------+-------+
            | CLERK     | 1037.500000 |     1 |
            | SALESMAN  | 1400.000000 |     2 |    
            | MANAGER   | 2758.333333 |     4 |
            | ANALYST   | 3000.000000 |     4 |
            | PRESIDENT | 5000.000000 |     5 |
            +-----------+-------------+-------+

select后面出现的子查询(这个内容不需要掌握，了解即可！！！！) ```java 案例：找出每个员工的部门名称，要求显示员工名，部门名 select e.ename,(select d.dname from dept d where e.deptno = d.deptno) 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 |
         +--------+------------+