跳至主要內容

MySQL基础 - 多表查询

友人大约 5 分钟

MySQL基础 - 多表查询

多表关系

提示

在正式学习数据库的多表查询之前，我们需要了解到需要多表查询的场景，以及有哪些多表查询的情况，也就是数据表之间的关系。

一对一

一对一关系是我们比较常见的关系，比如数据表A中的一条值对应数据表B中的一条值，这种情况经常用于多表的拆分。例如将最常用的字段抽取出来，使得提高查询的效率。

实现

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

一对多

一对多的关系也可以看作多对一，就看你是从那个视角去看待这段关系。例如，一个部门下有多个员工，但是一个员工只能属于一个部门，这种关系就是一对多关系。

实现

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

多对多

这种关系也是我们经常遇到的一种关系，如学生和课程之间的关系，一个学生可以选择多门课程，而一门课程中也可以有多个学生，这就是一种多对多的关系.

实现

这种关系的实现相对来说较为复杂，需要我们建立一张中间表，这张中间表的字段最少包含了两个外键，关联了两方多的主键，从而确保多对多的关系。

多表查询

多表查询是指从多张数据表中查询数据。然而在多表查询之前，我们首先需要了解一个简单的概念：笛卡尔积。

笛卡尔积

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

例如，我们在连接查询的时候，不指定条件列表，则查询出来的数据是两张表的笛卡尔积组合。即按行将两张表排列组合，一共有 表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 条件列表 ...;

注意

  • 自连接是不区分左连接和右连接的，因为两张表的是完全相同的，无论将哪张表放在左边，其结果都是相同的。
  • 自连接可以是内连接，也可以是外连接查询。

联合查询

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

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

注意

  • 对于联合查询而言，多张表的列数必须保持一致，字段类型也需要保持一致。
  • union all 会讲全部的数据直接合并在一起，而 union 会将数据合并后去重。

子查询

子查询又称为嵌套查询，即在SQL语句中嵌套使用SELECT语句，一次SELECT的返回集又称为了另外一个SELECT的数据集。

SELECT * FROM table1 WHERE column1 = (
    SELECT column1 FROM table2
);

提示

子查询的外部可以是INSERT、DELETE、UPDATE、SELECT操作中的任意一个。

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

  • 标量子查询（子查询的结果为单个值）
  • 列子查询（子查询的结果为一列）
  • 行子查询（子查询的结果为一行）
  • 表子查询（子查询的结果为多行多列）

标量子查询

标量子查询指的是子查询的返回的结果是一行一列的一个值，如数字、字符串、日期等简单的形式。

常用的操作符号：=、!=、>、>=、 < 、<=

列子查询

列子查询指的是子查询的返回结果是一列，可以是多行的数据集。

常用的操作符： IN、NOT IN、ANY、SOME、ALL

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

行子查询

行子查询指的是子查询返回的结果是一行，可以是多列的数据集。

常用的操作符：=、!= 、IN、NOT IN

表子查询

表子查询指的是子查询返回的结果是多行多列的数据集。

常用的操作符：IN