MySQL-子查询

什么是子查询

• select语句中嵌套select语句，被嵌套的select语句称为子查询

• 子查询都可以出现在哪里？

select
	..（select）
from
	..（select）
where
	..（select）

where子句中出现子查询

• 案例：找出比最低工资高的员工姓名和工资？
  

select 
	ename,sal 
from 
	emp 
where
	sal > (select min(sal) from emp);
	//where子句中不得直接使用分组函数，但可以间接通过子查询使用

实现思路：
1.查询最低工资是多少

select min(sal) from emp;

+----------+
| min(sal) |
+----------+
|   800.00 |
+----------+
1 row in set (0.00 sec)

2.找出大于最低工资的员工名和工资

select ename,sal from emp where sal > 800;

+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
13 rows in set (0.00 sec)

3.合并

select ename,sal from emp where sal > (select min(sal) from emp);

from中出现的子查询

• from 后面的子查询，可以将子查询的查询结果当做一张临时表。

• 案例：找出每个岗位的平均工资的薪资等级。
  思路：
  第一步：找出每个岗位的平均工资（按照岗位分组求平均值）

select job,avg(sal) from emp group by job;

+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| CLERK     | 1037.500000 |
| SALESMAN  | 1400.000000 |
| MANAGER   | 2758.333333 |
| ANALYST   | 3000.000000 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+
5 rows in set (0.00 sec)

第二步：把上面查询出来的数据看成一张表Tab,再和工资等级表连接

select * from salgrade;
//临时表Tab
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

第三步：合并

select 
	Tab.*,s.garde
from
	(select job,avg(sal) as avgsal from emp group by job) as Tab
join 
	salgrade s
on
	Tab.avgsal between s.losal and s.hisal;
	
+-----------+-------------+-------+
| job       | avgsal      | grade |
+-----------+-------------+-------+
| CLERK     | 1037.500000 |     1 |
| SALESMAN  | 1400.000000 |     2 |
| MANAGER   | 2758.333333 |     4 |
| ANALYST   | 3000.000000 |     4 |
| PRESIDENT | 5000.000000 |     5 |
+-----------+-------------+-------+
5 rows in set (0.00 sec)

//字段名和表名都可以取别名，as也可以省略
//avg(sal中avg是一个关键字，直接写在on中会被认为是一个函数，所以取个别名。

select后的子查询（了解）

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

//一般写法
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;

+--------+------------+
| 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.00 sec)

//子查询写法
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.00 sec)

select e.ename,e.deptno,(select dname from dept) as danme from emp e;

ERROR 1242 (21000): Subquery returns more than 1 row

注意：对于select后面子查询来说，这个子查询只能一次返回一条结果，多余一条就报错。