7.多表关系和连接查询

7. 多表关系和连接查询

7.1 一对多

1.案例：部门与员工的关系

一个部门对应多个员工，一个员工对应一个部门

2.实现：在多的一方建立外键，指向一的一方的主键。

7.2 多对多

1.案例：学生与课程的关系

一个学生可以选修多门课程，一门课程也可以供多个学生选择。

2.实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。

7.3 一对一

1.案例：用户于用户详情的关系

一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另外一张表中，以提升效率。

2.实现：在任意一方加入外键，关联另外一方的主键，并设置外键为唯一的（unique）。

7.4 多表关系分类

1.连接查询

• 内连接：相当于查询A、B交集部分数据

• 外连接：

  左外连接：查询左表所有数据，以及两张表交集部分数据。

  右外连接：查询右表所有数据，以及两张表交集部分数据。

• 自连接：当前表与自身的连接查询，自连接必须使用表别名。

• 子查询

7.5 连接查询-内连接

1.隐式内连接

select 字段列表 from 表1,表2 where 条件;

#查询每一个员工的姓名及其所在部门的名称
select emy_name,dept_name
from tb_emy e,tb_dept d
where e.dept_id = d.id

2.显示内连接

select 字段列表 from 表1 [inner] join 表2 on 连接条件;

#查询每一个员工的姓名及其所在部门的名称
select emy_name,dept_name
from tb_emy e
join tb_dept d
on e.dept_id = d.id

7.6 连接查询-外连接

1.左外连接：查询表1所有数据，以及两张表交集部分数据。

select 字段列表 from 表1 left [outer] join 表2 on 条件;

#查询员工表的所有数据和员工所在的部门名称
#左查询
select e.*,d.dept_name
from tb_emy e 
left join tb_dept d 
on e.dept_id = d.id

#结果：员工表中没有部门的员工会显示在表中，没有员工的部门不会显示在表中（员工表为表1，部门表为表2）。

2.右外连接：查询表2所有数据，以及两张表交集部分数据。

select 字段列表 from 表1 right [outer] join 表2 on 条件;

#查询员工表的所有数据和员工所在的部门名称
#右查询
select e.*,d.dept_name
from tb_emy e
right join tb_dept d
on e.dept_id = d.id

#结果：部门表中没有员工的部门会显示在表中，没有部门的员工不会显示在表中（员工表为表1，部门表为表2）。

7.7 连接查询-自连接

1.自连接查询(可以是内连接查询也可以是外连接查询)

select 字段列表 from 表A 别名A join 表B 别名B on 条件;

2.内连接查询

#查询员工表中员工名字及其对应的领导名字
select a.emy_name '员工',b.emy_name '领导'
from tb_emy a
inner join tb_emy b
on a.mar_id = b.id

3.外连接查询

#查询员工表中员工名字及其对应的领导名字
select a.emy_name '员工',b.emy_name '领导'
from tb_emy a
left join tb_emy b
on a.mar_id = b.id

7.8 多表查询-联合查询

1.对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

select 字段列表 from 表A where 条件
union [all]
select 字段列表 from 表A where 条件;

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

union all 会将全部的数据直接合并在一起，union会对合并之后的数据去重。

7.9 多表查询-子查询

1 介绍

1.概念：SQL语句中嵌套select语句，称为嵌套查询，又称为子查询。

#语法
select * from t1 where column1 = (select column1 from t2);

子查询外部的语句可以是insert / update / delete / select 的任何一个。

2.根据子查询的不同，分为：

• 标量子查询（子查询结果为单值）

• 列子查询（子查询结果为一列）

• 行子查询（子查询结果为一行）

• 表子查询（子查询结果为多行多列）

2 标量子查询

1.子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询成为标量子查询。

2.常用操作符： = <> > >= < <=

#查询部门为稻妻的所有员工信息
## 1.查询出部门名称为稻妻的id
## 2.在员工表中查询dept_id 等于 id的员工信息
select *
from tb_emy
where dept_id = (
				select id
				from tb_dept
				where dept_name = '稻妻'
)

3 列子查询

1.子查询返回的结果是一列（可以是多行）,这种子查询称为列子查询。

2.常用的操作符：in 、 not in 、 any 、 some 、all

#查询部门为稻妻和璃月的所有员工信息
## 1.查询出部门名称为稻妻和璃月的id
## 2.在员工表中查询dept_id 等于 id的员工信息
select *
from tb_emy
where dept_id in (
				select id
				from tb_dept
				where dept_name = '稻妻' or dept_name = '璃月'
)

4 行子查询

1.子查询返回的结果是一行（可以是多列），这种子查询称为行子查询

2.常用的操作符：= 、 <> 、 in 、 not in

#查询与 “刻晴” 的薪资及直属领导相同的员工信息
select *
from tb_emy
where (emy_money,mar_id) in (
						select emy_money,mar_id
						from tb_emy
						where emy_name = '刻晴'
)

5 表子查询

1.子查询返回的结果是多行多列，这种子查询称为表子查询

2.常用的操作符：in

#查询与 “甘雨” ，“刻晴” 的部门id和薪资相同的员工的信息
select *
from tb_emy
where (emy_money,dept_id) in (
							select emy_money,dept_id
							from tb_emy
							where emy_name = '刻晴' or emy_name = '甘雨'
)