MySQL 多表查询

多表关系

一对多（多对一）

比如部门与员工的关系，一个部门对应多个员工，一个员工对应一个部门

在多的一方建立外键，指向一的一方的主键

多对多

比如学生与课程的关系，一个学生可以选修多门课程，一个课程有多个学生可以选择

建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

一对一

比如用户与用户详情的关系，多用于单标拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率

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

多表查询概述

多表查询指从多张表中查询数据

笛卡尔积：笛卡尔乘积是指在数学中，两个集合A集合 和 B集合的所有组合情况（在多表查询时，需要消除无效的笛卡尔积）

多表查询分类

• 连接查询
  • 内连接：相当于查询 A、B 交集部分数据
  • 外连接：
    • 左外连接：查询左表所有数据，以及两张表交集部分数据
    • 右外连接：查询右表所有数据，以及两张表交集部分数据
  • 自连接：当前表与自身的连接查询，自连接必须使用表别名
• 子查询

内连接

内连接查询的是两张表的交集的部分

隐式内连接

SELECT 字段列表 FROM 表1, 表2 WHERE 条件...;

显式内连接

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ...;

外连接

左外连接

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;

右外连接

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;

自连接

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

对于自连接查询，可以是内连接查询，也可以是外连接查询，在自连接查询中，必须要为表起别名，另外自连接也可以使用左右连接

联合查询

把多次查询的结果合并，形成一个新的查询结果集

SELECT 字段列表 FROM 表A ...;
UNION [ALL]
SELECT 字段列表 FROM 表B ...;

• 联合查询的多张表的列数和字段类型需要保持一致
• UNION ALL 会将全部的数据直接合并，但是 UNION 会进行去重

子查询

嵌套 SELECT 语句称为 嵌套查询，也称 子查询

SELECT * FROM t1 WHERE column = (SELECT column1 FROM t2);

子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等）

常用操作符：=, <>, >, >=, <, <=

列子查询

子查询返回的结果是一列

常用操作符：IN, NOT IN, ANY, SOME, ALL

操作符	描述
IN	在指定的集合范围之内，多选一
NOT IN	不在指定的集合范围之内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与 ANY 等同，使用 SOME 的地方都可以使用 ANY
ALL	子查询返回列表的所有值都必须满足

行子查询

子查询返回的结果是一行

常用操作符：=, <>, IN, NOT IN

表子查询

子查询返回的结果是多行多列

常用操作符：IN