什么是子查询?
当一个查询是另一个查询的条件时,这个查询称之为子查询(内层查询)
什么时候用?
当查询需求比较复杂,一次性查询无法得到结果,需要多次查询时,
例如:给出一个部门名称,需要获得该部门所有的员工信息
需要先确定部门的id,
然后才能通过id确定员工
解决问题的方式是把一个复杂的问题拆分为若干个简单的问题
如何使用?
首先明确子查询就是一个普通的查询,当一个查询需要作为子查询使用时,用括号包裹即可
案列:
准备数据:
create table emp (id int,name char(10),sex char,age int,dept_id int,job char(10),salary double);
insert into emp values
(1,"刘备","男",26,1,"总监",5800),
(2,"张飞","男",24,1,"员工",3000),
(3,"关羽","男",30,1,"员工",4000),
(4,"孙权","男",25,2,"总监",6000),
(5,"周瑜","男",22,2,"员工",5000),
(6,"小乔","女",31,2,"员工",4000),
(7,"曹操","男",19,3,"总监",10000),
(8,"司马懿","男",24,3,"员工",6000);
create table dept(id int primary key,name char(10));
insert into dept values(1,"市场"),(2,"行政"),(3,"财务");
需求:财务部有哪些:
数据在两张表中 可以使用链接查询
select emp.name from emp inner join dept on dept.id = emp.dept_id where dept .name = "财务";
子查询方式:
数据在两张表中,先查询那张?
emp? 不行 不知道部门名 查dept
第一步 需要知道财务部的id
select id from dept where name = "财务";
第二步 用查询的到的id作为判断条件查询emp
select name from emp where dept_id = 3;
3不能写死 是上一个查询的结果 所以直接写在后面 加上括号就变成了子查询
select name from emp where dept_id = (select id from dept where name = "财务");
in 关键字子查询
查询平均年龄大于25的部门名称
子查询方式:
平均年龄大于25的部门id有哪些?
先要求出每个部门的平年龄! 筛选出平均年龄大于25的部门id
拿着部门id 去查询部门表查询
select name from dept where id in (select dept_id from emp group by dept_id having avg(age) > 25);
多表查询方式:
先把数据拼接到一起 在加以筛选
select dept.name from emp inner join dept
on emp.dept_id = dept.id
group by dept.name
having avg(age) >25;
exists关键字子查询
exists 后跟子查询 子查询有结果是为True 没有结果时为False
为true时外层执行 为false外层不执行
select *from emp where exists (select *from emp where salary > 1000);
查看exists 的返回结果: 只有 0 和 1
select (exists (select *from emp where salary > 10000));
一个查询结果也是一个表 既然是表就能链接起来
综合练习:
查询每个部门工资最高的员工信息
先查询每个部门的最高工资
将查询结果与员工表联合起来
在加条件判断部门id相同并且 最高工资相同 则显示
select *from emp inner join
(select dept_id,max(salary) m from emp group by dept_id) t2
on emp.dept_id = t2.dept_id
where
emp.salary = t2.m;
面试题:
create table test(day_id date,result char(10));
insert test values("2018-10-01","success");
insert test values("2018-10-01","fail");
insert test values("2018-10-01","fail");
insert test values("2018-10-01","success");
insert test values("2018-10-02","success");
insert test values("2018-10-02","fail");
insert test values("2018-10-02","fail");
?
查询出以下结果:
day_id success fail
2018-10-01 2 2
2018-10-02 1 2
?
?
求出每天失败次数
select day_id,count(*) as fail from test group by day_id,result having result = "fail"
?
求出每天成功次数
select day_id,count(*) as success from test group by day_id,result having result = "success"
?
链接查询
select t1.day_id,success,fail from
(select day_id,count(*) as fail from test group by day_id,result having result = "fail") as t1
join
(select day_id,count(*) as success from test group by day_id,result having result = "success") as t2
on t1.day_id = t2.day_id;
原文：https://www.cnblogs.com/tangda/p/10560925.html