在实际的数据库应用开发过程中，经常需要把 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 就会提示错误，从而确保了关联数据不会缺失。

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

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

修改表来定义外键约束：

1	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。

使用 sql 语句确认：

mysql> 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）和外连接（OUTERJOIN）。

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

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

销售表（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, a,b顺序颠倒了，还是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。

先插入单头数据：

1
2
3

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 阻止了数据不一致的情况出现。

1
2

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.字段名称);

Channing's Blog

MySQL必知必会 06丨外键和连接：如何做关联查询？

创建外键约束

内外连接

关联查询的误区

小结