数据库之多表查询

1、内连接：把两张表有对应关系的记录连接成一张虚拟表
select * from emp inner join dep on emp.dep_id = dep.id;

#应用：
select * from emp,dep where emp.dep_id = dep.id and dep.name = "技术"; # 不要用where做连表的活

select * from emp inner join dep on emp.dep_id = dep.id
where dep.name = "技术"
;

2、左连接：在内连接的基础上，保留左边没有对应关系的记录
select * from emp left join dep on emp.dep_id = dep.id;

3、右连接：在内连接的基础上，保留右边没有对应关系的记录
select * from emp right join dep on emp.dep_id = dep.id;

4、全连接：在内连接的基础上，保留左、右边没有对应关系的记录
select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;

 

#补充：多表连接可以不断地与虚拟表连接

查找各部门最高工资
select t1.* from emp as t1
inner join
(select post,max(salary) as ms from emp group by post) as t2
on t1.post = t2.post
where t1.salary = t2.ms

 

二，子查询
#1：子查询是将一个查询语句嵌套在另一个查询语句中。
#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4：还可以包含比较运算符：= 、 !=、> 、<等

子查询：把一个查询语句用括号括起来，当做另一条查询语句的条件去用，称为子查询
select emp.name from emp inner join dep on emp.dep_id=dep.id where dep.name='技术'

select name from emp where dep_id=
(select id from dep where 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）;
查询每一个部门最新入职的那个员工
select t1.id,t1.name,t1.post,t1.hire_date,t2.post,t2.max_date from emp as t1 inner join (select post,max(hire_date) as max_date from emp group by post) as t2
on t1.post=t2.post
where t1.hire_date=t2.max_date
;

init.sql文件

练习