二、 子查询
(一个问题一个问题解决)
把一个查询语句用括号括起来,当做另外一条查询语句的条件去用,称为子查询
select name from emp where dep_id = (select id from dep where name="技术"); #子查询
select emp.name from emp inner join dep on emp.dep_id = dep.id where dep.name="技术"; #链表
#查询平均年龄在25岁以上的部门名
select name from dep where id in (select dep_id from emp group by dep_id having avg(age) > 25); #子查询
select dep.name from emp inner join dep on emp.dep_id = dep.id group by dep.name having avg(age) > 25; #链表
#查看不足2人的部门名(子查询得到的是有人的部门id)
select * from emp where exists (select id from dep where id > 3); #exists用法,当()返回True时,外层查询语句将进行查询;当返回值为False时,外层查询语句不进行查询(empty set)
#查询每个部门最新入职的那位员工
select t1.id,t1.name,t1.post,t1.hire_date,t2.post,t2.max_date from emp as t1 inner join (select post,max(hi