多表查询

多表查询

表创建

# 建表
create table dep(
id int,
name varchar(20)
);

create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum("male","female") not null default "male",
age int,
dep_id int
);

# 插入数据
insert into dep values
(200,"技术"),
(201,"人力资源"),
(202,"销售"),
(203,"运营");

insert into emp(name,sex,age,dep_id) values
("jason","male",18,200),
("egon","female",48,201),
("kevin","male",38,201),
("nick","female",28,202),
("owen","male",18,200),
("jerry","female",18,204)
;
# 当初为什么我们要分表，就是为了方便管理，在硬盘上确实有多张表，但是到了内存中我们应该把他们再拼成一张表进行查询才合理

笛卡尔积查询

是两张表相乘的结果，若左边有m条，右边有m条，查询结果为m*n条，往往包含大量错误数据。

select * from emp,dep;  # 左表一条记录与右表所有记录都对应一遍

# 查询员工及所在部门的信息
select * from emp,dep where emp.dep_id = dep.id;
# 查询部门为技术部的员工及部门信息
select * from emp,dep where emp.dep_id = dep.id and dep.name = "技术";

内连接查询(inner join)

查询两张表都有匹配关系的记录

select * from emp inner join dep on emp.dep_id = dep.id;
select * from emp inner join dep on emp.dep_id = dep.id
							where dep.name = "技术";  # 在多表查询中要筛选的是两边的关系，on用于过滤关联关系。而where单独做条件过滤，这样sql看起来可以更清晰明确，当然where依然可以代替on，inner可以省略

左连接查询(left join)

以左边的表为基准全部显示出来，右表中仅匹配成功的记录。

select * from emp left join dep on emp.dep_id = dep.id;

右连接查询(right join)

以右边的表为基准全部显示出来，左表中仅匹配成功的记录

select * from emp right join dep on emp.dep_id = dep.id;

全外连接查询(union)

无论是否匹配成功，两边表中的记录都要全部显示。mysql中可以使用合并查询结果，在所有语句最后写分号

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;

union只能用于字段数量相同的两个表，会自动去除重复的记录

union all 则保留所有记录

子查询

将一个表的查询结果作为另一个查询语句的条件(内层)

in 关键字子查询

# 1.查询部门是技术或则人力资源的员工信息
"""
先获取技术部和人力资源部的id号，再去员工表里面根据前面的id筛选出符合要求的员工信息
"""
select * from emp where dep_id in (select id from dep where name = "技术" or name = "人力资源");

# 2.每个部门最新入职的员工  思路：先查每个部门最新入职的员工，再按部门对应上联表查询
select t1.id,t1.name,t1.hire_date,t1.post,t1.* 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
;
"""
记住一个规律，表的查询结果可以作为其他表的查询条件，也可以通过起别名的方式把它作为一张虚拟表去跟其他表做关联查询
"""
select * from emp inner join dep on emp.dep_id = dep.id

exists 关键字子查询

exists后跟子查询，子查询有结果是为True，外层执行，没有结果时为False，外层不执行。

select * from emp where exists (select * from emp where salary > 1000);