8、子查询

什么是子查询

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

子查询可以出现的位置

select
　　可以出现子查询
from
　　可以出现子查询
where
　　可以出现子查询

 

 

一 where 后面使用子查询

找出高于平均工资的员工；

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

 

 

二 from后面使用子查询

找出每个部门平均薪水的工资等级；

//1 先找出每个部门的平均薪水 用到了group by分组

select avg(sal) as '平均薪水',deptno as '部门编号' from emp group by deptno;

+--------------+----
| 平均薪水 | 部门编号
+--------------+----
| 2916.6667 | 10 |
| 2175.0000 | 20 |
| 1566.6667 | 30 |
+--------------+--

工资等级表
mysql> select * from salgrade;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

完整SQL语句：这段SQL的重点是：把查询结果模拟一张表，给里面的分组函数起别名，方便后续使用

select a.*,s.grade from (select deptno,avg(sal) as avgsal from emp group by deptno) as a join salgrade as s on a.avgsal between s.losal and s.hisal;
　　　　　　　　　　　　　　　　　　        把查询结果模拟为一张表 起别名a                                                             连接条件      

 

三 select后面使用子查询

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

员工表
select ename,deptno from empl;
+--------+--------+
| ename  | deptno |
+--------+--------+
| SMITH  |     20 |
| ALLEN  |     30 |
| WARD   |     30 |
| JONES  |     20 |
| MARTIN |     30 |
| BLAKE  |     30 |
| CLARK  |     10 |
| SCOTT  |     20 |
| KING   |     10 |
| TURNER |     30 |
| ADAMS  |     20 |
| JAMES  |     30 |
| FORD   |     20 |
| MILLER |     10 |
+--------+--------+

部门表
mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

 

1） 常用写法：内连接（等值连接）

SELECT e.ename,d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno;

 

//2 select 嵌套子查询( 木有理解了)

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