MySQL中的连表查询

多表关系

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

• 一对多(多对一)
• 多对多
• 一对一

---

一对多(多对一)

• 场景：部门与员工的关系（一个部门下有多个员工）。

由于一个部门下，会关联多个员工。 而一个员工，是归属于某一个部门的 。那么此时，我们就需要在 emp 表中增加一个字段 dept_id 来标识这个员工属于哪一个部门，dept_id 关联的是 dept 的 id 。 如下所示：

上述的 emp 员工表的 dept_id 字段，关联的是 dept 部门表的 id 。部门表是一的一方，也称为父表，员工表是多的一方，称之为子表。

---

外键约束

• 现象：部门数据可以直接删除，然而还有部分员工归属于该部门下，此时就出现了数据的不完整、不一致问题 。
• 原因：目前上述的两张表，在数据库层面，并未建立关联，所以是无法保证数据的一致性和完整性的 。
• 解决方案：想解决上述的问题呢，我们就可以通过数据库中的 外键约束 来解决。

---

方案一：物理外键

• 概念：使用foreign key定义外键关联另外一张表。
• 缺点：
  • 影响增、删、改的效率（需要检查外键关系）。
  • 仅用于单节点数据库，不适用于分布式、集群场景。
  • 容易引发数据库的死锁问题，消耗性能。

-- 创建表时指定
create table 表名(
        字段名    数据类型,
        ...
        [constraint]   [外键名称]  foreign  key (外键字段名)   references   主表 (主表列名)        
);


-- 建完表后，添加外键
alter table  表名  add constraint  外键名称  foreign key(外键字段名) references 主表(主表列名);

---

方案二：逻辑外键(推荐)

• 概念：在业务层逻辑中，解决外键关联。
• 通过逻辑外键，就可以很方便的解决上述问题。

---

⭕在现在的企业开发中，很少会使用物理外键，都是使用逻辑外键。 甚至在一些数据库开发规范中，会明确指出==禁止使用物理外键 foreign key ==

---

一对一

在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

一对一关系表在实际开发中应用起来比较简单，通常是用来做单表的拆分，也就是将一张大表拆分成两张小表，将大表中的一些基础字段放在一张表当中，将其他的字段放在另外一张表当中，以此来提高数据的操作效率。

场景：人与身份证的关系

---

多对多

多对多的关系在开发中属于也比较常见的。比如：学生和老师的关系，一个学生可以有多个授课老师，一个授课老师也可以有多个学生。在比如：学生和课程的关系，一个学生可以选修多门课程，一个课程也可以供多个学生选修。

案例：学生与课程的关系

• 关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
• 实现关系：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

---

多表查询

基础语法

多表查询：查询时从多张表中获取所需数据

单表查询的SQL语句：select 字段列表 from 表名;

那么要执行多表查询，只需要使用逗号分隔多张表即可，如： select 字段列表 from 表1, 表2;

---

存在问题：笛卡尔乘积是指在数学中，两个集合(A集合和B集合)的所有组合情况。

在多表查询时，一定要避免出现笛卡尔乘积，只要加过合理的查询条件即可（关键）

SELECT * FROM 表1, 表2 WHERE 表1.关联字段 = 表2.关联字段; 

---

⭕扩展细节问题：

多表连接查询时，查询结果集中可能会出现同名的字段，无法区分属于哪个表，所以在多表查询时一定要使用 别名 来区分

示例：

---

分类

• 连接查询

  • 内连接：相当于查询A、B交集部分数据
  • 外连接
    • 左外连接：查询左表所有数据(包括两张表交集部分数据)
    • 右外连接：查询右表所有数据(包括两张表交集部分数据)

• 子查询

---

内连接查询：查询两表或多表中交集部分数据。

内连接从语法上可以分为：

隐式内连接语法：

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

显式内连接语法：(建议使用)

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

---

外连接查询：查询主表的全部数据，如果主表中的数据在从表中没有匹配的则显示为NULL

外连接分为两种：左外连接 和 右外连接。（本质没有区别）

左外连接语法：(一般使用交多)

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

左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。

右外连接语法：

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

右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

---

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

根据子查询结果的不同分为：

1. 标量子查询（子查询结果为单个值 [一行一列]）
2. 列子查询（子查询结果为一列，但可以是多行）
3. 行子查询（子查询结果为一行，但可以是多列）
4. 表子查询（子查询结果为多行多列[相当于子查询结果是一张表]）

子查询可以书写的位置：

1. where之后
2. from之后
3. select之后