MYSQL中的SQL语句----子查询

什么是子查询

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

子查询可以出现在哪里？

select

        ....(select)

from

        ....(select)

where

        ....(select)

1. 在from后嵌套子查询

例：找出每个部门平均薪水的薪资等级 ，在from后面嵌套select语句

第一步：找出每个部门的平均薪资
select deptno,avg(sal) from emp group by deptno;
第二步：把第一步的查询结果作为新的表与salgrade表做连接查询
select
    t.*, s.grade
from
    t
join
    salgrade s
on
    t.avgsal between s.losal and s.hisal;



mysql> select
    -> t.*,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 |
+--------+-------------+-------+
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
|     20 | 2175.000000 |     4 |
+--------+-------------+-------+

2. 在select后面嵌套子查询

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

select 
    e.ename,e.deptno,d.dname
from 
    emp e
join
    dept d
on
    e.deptno=d.deptno;

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