MySQL之多表操作

第一步：创表

# 建表
create table dep(
	id int primary key auto_increment,
    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
('xiao','male',18,200),
('quan','female',78,200),
('zheng','male',58,201),
('zhang','male',48,202),
('xu','female',18,203),
('li','female',18,203),
('chen','male',18,203);

联表查询

笛卡尔积：

笛卡尔积为两个集合(两张表)中的每条数据进行两两组合的结果。

select * from dep,emp; # 结果叫笛卡尔积

在多表查询时会产生笛卡尔积，要通过添加条件消除笛卡尔积。

select * from emp,dep where emp.dep_id = dep.id; 

通过上面添加条件进行查询可以去除笛卡尔积现象，这种查询也叫隐式内连接查询

联表查询的分类

• inner join 内连接
• left join 左连接
• right join 右连接
• union join 全连接

1. inner join 内连接
select * from emp inner join dep on emp.dep_id = dep.id;
# 内连接特点是只拼接两张表中共有的数据部分

2. left join 左连接
select * from emp left join dep on emp.dep_id = dep.id;
# 左连接特点是左表所有的数据都展示出来，没有对应的项就用NULL

3. right join 右连接
select * from emp right join dep on emp.dep_id = dep.id;
# 右连接特点是右表所有的数据都展示出来，没有对应的项就用NULL

4. union join 全连接
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;
# 全连接特点是左右表所有的数据都展示出来，没有对应的项就用NULL

子查询

思路：分步解决问题
将一个查询语句的结果当做另一个查询语句的调节去用

# 查询部门是技术或者人力资源的员工信息
1. 先获取部门的id号
2. 再去员工表里面筛选出对应的员工
select id from dep where name='技术' or name='人力资源';
select name from emp where dep_id in (200,201);
组合：
select name from emp where dep_id in (select id from dep where name='技术' or name='人力资源');

总结：

表的查询结果可以作为其他表的查询条件，

也可以通过起别名的方式把它作为一张虚拟表跟其他表关联。

多表查询就两种方式

1、先拼接表再查询

2、子查询，一步一步来

知识点补充

# 查询平均年龄在25岁以上的部门名称
"""
只要是多表查询，就有两种思路
	联表和子查询
"""
# 联表操作
1. 先拿到部门和员工表，拼接之后的结果
2. 分析语义，得出需要再进行分组
select dep.name from emp inner join dep
	on emp.dep_id = dep.id
	group by dep.name
	having avg(age) > 25;
"""涉及到多表操作的时候一定要加上表的前缀"""

# 子查询
select name from dep where id in
	(select dep_id from emp group by dep_id 
 		having avg(age) > 25);

# 关键字exists(了解)
只返回布尔值 True False
返回True的时候外层查询语句执行
返回False的时候外层查询语句不再执行
select * from emp where exists(select id from emp where id > 3);

select * from emp where exists(select id from emp where id > 30);