四、MySQL多表查询

多表关系

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

1. 一对一

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

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

2. 一对多

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

3. 多对多

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

多表查询概述

笛卡尔积：笛卡尔乘积是指在数学中，两个集合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 条件;

自连接

1. 当前表与自身的连接查询，自连接必须使用表别名
2. 自连接查询，可以是内连接查询，也可以是外连接查询

SELECT 字段列表 FROM 表1 别名1 JOIN 表1 别名2 ON 条件;

联合查询

关键字：UNION(会去重)、UNION ALL(可能会出现重复)

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

SELECT 字段列表 FROM 表1 WHERE 条件
UNION [ALL]
SELECT 字段列表 FROM 表2 WHERE 条件
# 字段列表需要一致

子查询

SQL语句嵌套SELECT语句，称为嵌套查询，又称子查询

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

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

• 根据子查询结果不同，分为：
  1. 标量子查询（子查询结果为单个值）
  2. 列子查询（子查询结果为一列）
  3. 行子查询（子查询结果为一行）
  4. 表子查询（子查询结果为多行多列）
• 根据子查询位置，分为：WHERE 之后、FROM 之后、SELECT 之后

标量子查询

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

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

列子查询

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

常用的操作符：IN、NOT IN、ANY、SOME、ALL,需与 < = >等比较符使用

行子查询

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

常用的操作符：=、<>、IN、NOT IN

# 原多条件查询方式
SELECT * FROM t1 WHERE column1 = v1 and column2 = v2;
# 另一种多条件查询方式
SELECT * FROM t1 WHERE (column1, column2) = (v1, v2);

表子查询

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

常用的操作符：IN

将查询出来的表子查询当做一张表来第二次查询 FROM (表子查询) ‘别名’ WHERE 条件;