什么是子查询
- 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后面子查询来说,这个子查询只能一次返回一条结果,多余一条就报错。