子查询
也是实现多表关联查询的。在子查询中可以包含in any all等
关键字,也可以包含比较运算符等等。
子查询灵活多变的。
子查询经常出现如下两个位置
(1)where子句中的子查询
1.1 查询结果为单行单列的子查询
例子:查询员工表中工资比turner还要高的所有员工
step1:先查询turner员工的工资是多少
select sal from t_employee
where ename='turner';
查询结果是1500,是单行单列的一种结果
step2:再查询工资高于turner,也就是高于1500的员工
select empno,ename,sal
from t_employee
where sal>1500;
将step1和step2的查询语句合并 – where子句的子查询格式
select empno,ename,sal
from t_employee
where sal>(select sal from t_employee
where ename='turner');
1.2 查询结果为单行多列的子查询
将xiaohong的工资改成800
update t_employee set sal=800 where empno=7999;
例子:查询员工表中工资和职位与smith一样的员工信息
step1:先查询smith的工资和职位是多少
select sal,job from t_employee
where ename='smith';
查询结果是800,clerk,是单行多列的结果
step2:在查询其他员工工资是800,职位是clerk的员工
select empno,ename,job,sal
from t_employee
where sal=800 and job='clerk';
将step1和step2的查询语句合并
select empno,ename,job,sal
from t_employee
where (sal,job)=(select sal,job from t_employee where ename='smith');
1.3 查询结果是多行单列的子查询
1.3.1使用带关键字in的子查询
例子:查询员工表中的员工,这些员工的部门号必须都在部门表中。
step1:先查询部门表中都有哪些部门号
select deptno from t_dept;
10
20
30
40
查询结果是多行单列的。
step2:在查询员工表中员工的部门编号为10 20 30 40
select ename,deptno
from t_employee
where deptno=10 or deptno=20 or
deptno=30 or deptno=40;
select ename,deptno
from t_employee
where deptno in(10,20,30,40);
将step1和step2查询语句合并
select ename,deptno
from t_employee
where deptno in(select deptno from t_dept);
in和=any的功能一样
select ename,deptno
from t_employee
where deptno=any(select deptno from t_dept);
1.3.2 使用带关键字any的子查询
①=any 功能和关键字in一样的
②>any 比子查询查询的结果中最小的还要大
③<any 比子查询查询的结果中最大的还要小
例子:查询员工表中员工,这些员工的工资不低于职位为manager的员工的工资。
step1:先查询职位为manager的员工的工资都是多少
select sal from t_employee
where job='manager';
2975.00
2850.00
2450.00
多行单列的查询结果
step2:在查询员工的工资不低于2975,2850,2450
select ename,sal
from t_employee
where sal>any(select sal from t_employee where job='manager');
1.3.3 使用带关键字all的子查询
①>all 比子查询查询的结果最大的还要大
②<all 比子查询查询的结果最小的还要小
例子:查询员工表中的员工,这些员工的工资高于职位为manager的工资
step1:先查询职位为manager的工资都有哪些
select sal from t_employee
where job='manager';
step2:在查询员工,工资要高于职位为manager
select ename,sal
from t_employee
where sal>all(select sal from t_employee where job='manager');
(2)from子句中的子查询
特点:当子查询的查询结果是多行多列的时候,该子查询语句一般会在主查询from的后面。相当于是把子查询的结果当成了一张临时表来处理。
例子:查询员工表中各部门的部门号,部门名称,部门所在地,员工的人数,员工的平均工资。
方法1:使用内连接查询的方式
select d.deptno,d.dname,d.loc,count(e.ename),avg(e.sal),group_concat(e.ename)
from t_employee e inner join t_dept d
on e.deptno=d.deptno
group by e.deptno;
方法2:使用子查询的查询方法
step1:先查询员工表中各部门的员工数量和平均工资
select deptno dno,count(ename) num,avg(sal) avgsal
from t_employee
group by deptno;
查询结果是一个多行多列的结果
dno num avgsal
10 3 2916.66667
20 5 2175.00000
30 6 1566.66667
50 1 800.00000
以上结果看成是一张临时表 msg
step2:再将部门表去连接临时表msg进行多表关联查询
分析:表 t_dept d 部门表
msg 临时表
关联条件 d.deptno=msg.dno
select d.deptno,d.dname,d.loc,num,avg,sal
from t_dept d inner join (select deptno dno,count(ename) num,avg(sal) avg,sal
from t_employee
group by deptno msg
on d.deptno=msg.dno;