MySQL必知必会06：外键和连接-关联查询

<花开花落>

已于 2023-02-03 19:04:58 修改

阅读量5.7k

点赞数 3

分类专栏： MySQL 文章标签： mysql 数据库 database

于 2022-02-27 13:16:24 首次发布

本文链接：https://blog.csdn.net/qq_31362767/article/details/123162947

版权

MySQL 专栏收录该内容

18 篇文章 5 订阅

订阅专栏

阅读整理自《MySQL 必知必会》- 朱晓峰，详细内容请登录极客时间官网购买专栏。

文章目录

在实际的数据库应用开发过程中，经常需要把 2 个或 2 个以上的表进行关联，以获取需要的数据。这是因为，为了提高存取效率，会把不同业务模块的信息分别存放在不同的表里面。但是，从业务层面上看，需要完整全面的信息为经营决策提供数据支撑。

把分散在多个不同的表里的数据查询出来的操作，就是多表查询。

项目的进货模块，有这样 2 个数据表，分别是进货单头表（importhead）和进货单明细表（importdetails），每天都要对这两个表进行增删改查的操作。

listnumber (进货单号)	supplierid （供货商编号）	stocknumber （仓库编号）	importtype （进货类别）	totalquantity （总计数量）	totalvalue （总计金额）	recorder （录入人编号）	recordingdate （录入时间）
1234	1	1	1	30	1110	1	2020-12-20

进货单明细表记录了每次进货的商品明细信息。一条进货单头数据记录，对应多条进货商品的明细数据，也就是所谓的一对多的关系。具体信息如下表所示：

listnumber (进货单号)	itemnumber （商品编号）	quantity （进货数量）	importprice （进货价格）	importvalue （进货金额）
1234	1	10	89	890
1234	2	20	12	240

查询一次进货的所有相关数据，包括进货单的总体信息和进货商品的明细，就需要把 2 个表关联起来。

创建外键

假设有 2 个表，分别是表 A 和表 B，它们通过一个公共字段“id”发生关联关系，我们把这个关联关系叫做 R。如果“id”在表 A 中是主键，那么，表 A 就是这个关系 R 中的主表。相应的，表 B 就是这个关系中的从表，表 B 中的“id”，就是表 B 用来引用表 A 中数据的，叫外键。所以，外键就是从表中用来引用主表中数据的那个公共字段。

在这里插入图片描述

如图所示，在关联关系 R 中，公众字段（字段 A）是表 A 的主键，所以表 A 是主表，表 B 是从表；表 B 中的公共字段（字段 A）是外键。

在 MySQL 中，外键是通过外键约束来定义的。外键约束就是约束的一种，它必须在从表中定义，包括指明哪个是外键字段，以及外键字段所引用的主表中的主键字段是什么。MySQL 系统会根据外键约束的定义，监控对主表中数据的删除操作。如果发现要删除的主表记录，正在被从表中某条记录的外键字段所引用，MySQL 就会提示错误，从而确保了关联数据不会缺失。

外键约束可以在创建表的时候定义，也可以通过修改表来定义。

外键约束定义的语法结构：

[constraint <外键约束名称>] foreign key 字段名
references <主表名> 字段名

在创建表的时候定义外键约束：

create table 从表名
(
  字段名 类型,
  ...
-- 定义外键约束，指出外键字段和参照的主表字段
constraint 外键约束名
foreign key (字段名) references 主表名 (字段名)
)

修改表来定义外键约束：

alter table 从表名 add constraint 约束名 foreign key(字段名) references 主表名(字段名);

一般情况下，表与表的关联都是提前设计好了的，因此，会在创建表的时候就把外键约束定义好。不过，如果需要修改表的设计（比如添加新的字段，增加新的关联关系），但没有预先定义外键约束，那么，就要用修改表的方式来补充定义。

创建主表 demo.importhead：

CREATE TABLE demo.importhead (
    listnumber INT PRIMARY KEY,
    supplierid INT,
    stocknumber INT,
    importtype INT,
    importquantity DECIMAL(10 , 3 ),
    importvalue DECIMAL(10 , 2 ),
    recorder INT,
    recordingdate DATETIME
);

创建从表 demo.importdetails，并且给它定义外键约束：

CREATE TABLE demo.importdetails
(
  listnumber INT,
  itemnumber INT,
  quantity DECIMAL(10,3),
  importprice DECIMAL(10,2),
  importvalue DECIMAL(10,2),
  -- 定义外键约束，指出外键字段和参照的主表字段
  CONSTRAINT fk_importdetails_importhead
  FOREIGN KEY (listnumber) REFERENCES importhead (listnumber)
);

在创建表的同时定义了一个名字叫 fk_importdetails_importhead 的外键约束，同时声明，这个外键约束的字段 listnumber 引用的是表 importhead 里面的字段 listnumber。

确认外键约束创建成功了：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-V7XBpTgW-1645938818413)(…/mdpic/06外键和连接-关联查询2.png)]

使用 sql 语句确认：

select constraint_name, table_name, column_name, referenced_table_name, referenced_column_name
from information_schema.key_column_usage
where constraint_name = 'fk_importdetails_importhead';

+-----------------------------+---------------+-------------+-----------------------+------------------------+
| CONSTRAINT_NAME             | TABLE_NAME    | COLUMN_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+-----------------------------+---------------+-------------+-----------------------+------------------------+
| fk_importdetails_importhead | importdetails | listnumber  | importhead            | listnumber             |
+-----------------------------+---------------+-------------+-----------------------+------------------------+

通过查询可以看到，外键约束所在的表是“importdetails”，外键字段是“listnumber”，参照的主表是“importhead”，参照的主表字段是“listnumber”。

连接

在 MySQL 中，有 2 种类型的连接，分别是 **内连接（INNER JOIN）**和 外连接（OUTER JOIN）。

内连接表示查询结果只返回符合连接条件的记录，这种连接方式比较常用；
外连接则不同，表示查询结果返回某一个表中的所有记录，以及另一个表中满足连接条件的记录。

实际举例：项目中有会员销售的需求，所以流水表中的数据记录，既包括非会员的普通销售，又包括会员销售。区别是，会员销售的数据记录包括会员编号，而在非会员销售的数据记录中，会员编号为空。

销售表（demo.trans) ，假设业务字段 cardno 是会员信息表的主键：

transactionnumber (流水单号)	itemnumber （商品编号）	price （价格）	quantity （数量）	cardno （会员卡号）	transdate （交易时间）
1	1	89	1	10000001	2020-12-01
2	2	12	1	NULL	2020-12-02

会员信息表（demo.membermaster）：

carno (会员卡号)	membername （会员名称）	memberphone （会员电话）	memberpid （会员身份证号）	sex （会员行性别）
10000001	张三	13698765432	475145197001012356	女

表 demo.trans 中的字段 cardno 是这个关联关系中的外键。

通过内连接（公共字段 cardno 把两个表关联到了一起），查询所有会员销售的流水记录：

mysql> select a.transactionno, a.itemnumber, a.quantity, a.price, a.transdate, b.membername
    -> from demo.trans as a
    -> join demo.membermaster as b on (a.cardno = b.cardno);
+---------------+------------+----------+-------+---------------------+------------+
| transactionno | itemnumber | quantity | price | transdate           | membername |
+---------------+------------+----------+-------+---------------------+------------+
|             1 |          1 |    1.000 | 89.00 | 2020-12-01 00:00:00 | 张三       |
+---------------+------------+----------+-------+---------------------+------------+

关键字 JOIN 与关键字 ON 配对使用。

跟内连接只返回符合连接条件的记录不同的是，外连接还可以返回表中的所有记录，它包括两类，分别是左连接和右连接。

左连接，一般简写成 left join，返回左边表中的所有记录，以及右表中符合连接条件的记录
右连接，一般简写成 right join，返回右边表中的所有记录，以及左表中符合连接条件的记录

查询全部流水信息的时候，就会用到外连接：

mysql> select a.transactionno, a.itemnumber, a.quantity, a.price, a.transdate, b.membername
    -> from demo.trans as a
    -> left join demo.membermaster as b -- left join, 以demo.trans为主
    -> on (a.cardno = b.cardno);
+---------------+------------+----------+-------+---------------------+------------+
| transactionno | itemnumber | quantity | price | transdate           | membername |
+---------------+------------+----------+-------+---------------------+------------+
|             1 |          1 |    1.000 | 89.00 | 2020-12-01 00:00:00 | 张三       |
+---------------+------------+----------+-------+---------------------+------------+

mysql> select a.transactionno, a.itemnumber, a.quantity, a.price, a.transdate, b.membername
    -> from demo.membermaster as b
    -> right join demo.trans as a  -- right join, 为demo.trans为主
    -> on (a.cardno = b.cardno);
+---------------+------------+----------+-------+---------------------+------------+
| transactionno | itemnumber | quantity | price | transdate           | membername |
+---------------+------------+----------+-------+---------------------+------------+
|             1 |          1 |    1.000 | 89.00 | 2020-12-01 00:00:00 | 张三       |
+---------------+------------+----------+-------+---------------------+------------+

查询的误区

在 MySQL 中，外键约束不是关联查询的必要条件。

很多人往往在设计表的时候，觉得只要连接查询就可以搞定一切了，外键约束太麻烦，没有必要。如果这么想，就进入了一个误区。

举例：假设一次进货数据是这样的：供货商编号是 1，进货仓库编号是 1。进货的商品编号是 1234，进货数量是 1，进货价格是 10，进货金额是 10。

先插入单头数据：

insert into demo.importhead
(listnumber, supplierid, stocknumber)
values (1234, 1, 1);

mysql> select * from demo.importhead;
+------------+------------+-------------+------------+----------------+-------------+----------+---------------+
| listnumber | supplierid | stocknumber | importtype | importquantity | importvalue | recorder | recordingdate |
+------------+------------+-------------+------------+----------------+-------------+----------+---------------+
|       1234 |          1 |           1 |          1 |           NULL |        NULL |     NULL | NULL          |
+------------+------------+-------------+------------+----------------+-------------+----------+---------------+
1 row in set (0.00 sec)

向进货单明细表中插入进货明细数据：

insert into demo.importdetails
(listnumber,itemnumber,quantity,importprice,importvalue)
VALUES
(1234,1,1,10,10);

mysql> select * from demo.importdetails;
+------------+------------+----------+-------------+-------------+
| listnumber | itemnumber | quantity | importprice | importvalue |
+------------+------------+----------+-------------+-------------+
|       1234 |          1 |    1.000 |       10.00 |       10.00 |
+------------+------------+----------+-------------+-------------+
1 row in set (0.00 sec)

此时，如果删除进货单头表的数据，就会出现只有明细、没有单头的数据缺失情况，MySQL 会提示错误，因为数据删除违反了外键约束。MySQL 阻止了数据不一致的情况出现。

mysql> delete from demo.importhead where listnumber = 1234;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`demo`.`importdetails`, CONSTRAINT `fk_importdetails_importhead` FOREIGN KEY (`listnumber`) REFERENCES `importhead` (`listnumber`))

这里，插入数据的顺序：为什么要先插入进货单头表的数据，再插入进货单明细表的数据呢？是因为，如果先插入数据到从表，也就是进货单明细表，会导致 MySQL 找不到参照的主表信息，会提示错误，因为添加数据违反了外键约束。

当然，按照信息系统的操作逻辑，生成一张进货单的时候，一定是先生成单头，再插入明细。同样，删除一张进货单的时候，一定是先删除明细，再删除单头。

然而，既然把进货数据拆成了 2 个表，这就决定了无论是数据添加，还是数据删除，都不能通过一条 SQL 语句实现。实际工作中，什么突发情况都是有可能发生的。你认为一定会完成的操作，完全有可能只执行了一部分。

因此，虽然你不用外键约束，也可以进行关联查询，但是有了它，MySQL 系统才会保护你的数据，避免出现误删的情况，从而提高系统整体的可靠性。

为什么在 MySQL 里，没有外键约束也可以进行关联查询呢？

原因是外键约束是有成本的，需要消耗系统资源。对于大并发的 SQL 操作，有可能会不适合。比如大型网站的中央数据库，可能会因为外键约束的系统开销而变得非常慢。所以，MySQL 允许你不使用系统自带的外键约束，在应用层面完成检查数据一致性的逻辑。也就是说，即使你不用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。

小结

外键约束，可以帮助我们确定从表中的外键字段与主表中的主键字段之间的引用关系，还可以确保从表中数据所引用的主表数据不会被删除，从而保证了 2 个表中数据的一致性。

连接可以帮助我们对 2 个相关的表进行连接查询，从 2 个表中获取需要的信息。

左连接表示连接以左边的表为主，结果集中要包括左边表中的所有记录；
右连接表示连接以右边的表为主，结果集中要包括右边表中的所有记录。

常用 SQL 语句：

-- 定义外键约束：
create table 从表名
(
字段 字段类型
....
constraint 外键约束名称
foreign key (字段名) references 主表名 (字段名称)
);

alter table 从表名 add constraint 约束名 foreign key(字段名) references 主表名(字段名);

-- 连接查询
select 字段名
from 表名 as a
join 表名 as b
on (a.字段名称=b.字段名称);
 
select 字段名
from 表名 as a
left join 表名 as b
on (a.字段名称 = b.字段名称);
 
select 字段名
from 表名 as a
right join 表名 as b
on (a.字段名称=b.字段名称);