【跟着例子学MySQL】多表关联 -- 多对多关系

---

前言

举例子，是最简单有效的学习方法。本系列文章以一个贯穿始终的场景，结合多个实例讲解MySQL的基本用法。

❔ 为什么要写这个系列？

• 模仿是最好的老师，实践是检验成果的方法。
• 本系列以实操样例和应用场景为核心，将MySQL基本用法贯穿其中，达到学以致用的效果。

❔ 为什么要学习MySQL？

• MySQL是最常用的数据库之一，具有简单易用的特点，适合初学者学习
• 数据库的基本用法有相通之处，学会MySQL可以为学习其他数据库打下良好基础

❔ 跟别的入门教材有什么不同？

• 以一个贯穿始终的应用场景为主线，渐进地讲解用法
• 难度适中，既有基础方法，也有值得注意的关键细节

❔ 本系列文章不包含哪些内容？

该系列文章的目标是，将最核心的知识，以最直接的方式，呈现给初学者。因此不包含：

• MySQL安装方法
• MySQL系统管理方法，例如备份、恢复、导入导出等
• 高级主题，例如数据库监控、数据库调优和SQL优化

---

回顾

上篇文章👉《【跟着例子学MySQL】多表关联 – 一对多关系》 讲了一对多的多表关联。这篇接着讲解多对多的多表关联。

---

多对多关系

假设一个产品有很多供应商；一个供应商以多对多关系提供许多产品。你不能在产品表中包含supplierID，因为无法确定供应商的数量，从而无法确定supplierID所需的列数。同样地，你不能在供应商表中包含productID，因为你不能确定产品的数量。

要解决此问题，你需要创建一个新表，称为连接表（或连接表），称为 products_suppliers表，其结构如下所示：

productID INT (Foreign Key)	supplierID INT (Foreign Key)
2001	501
2002	501
2003	501
2004	502
2001	503

suppliers表如下所示：

supplierID INT	name VARCHAR(30)	phone CHAR(8)
501	ABC Traders	88881111
502	XYZ Company	88882222
503	QQ Corp	88883333

products表如下所示：

productID INT	productCode CHAR(3)	name VARCHAR(30)	quantity INT	price DECIMAL(10,2)
2001	PEC	Pencil 3B	500	0.52
2002	PEC	Pencil 4B	200	0.62
2003	PEC	Pencil 5B	100	0.73
2004	PEC	Pencil 6B	500	0.47

让我们创建 products_suppliers表。表的主键由两列组成： productID和supplierID，因为它们的组合唯一地标识每行。定义此主键是为了确保唯一性。此外，还定义了两个外键，以将约束设置为两个父表。

mysql> CREATE TABLE products_suppliers (
        productID INT UNSIGNED NOT NULL,
        supplierID INT UNSIGNED NOT NULL,
           -- 与父表相同的数据类型
        PRIMARY KEY (productID, supplierID),
        FOREIGN KEY (productID) REFERENCES products (productID),
        FOREIGN KEY (supplierID) REFERENCES suppliers (supplierID)
      );
mysql> DESCRIBE products_suppliers;
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| productID  | int(10) unsigned | NO   | PRI | NULL    |       |
| supplierID | int(10) unsigned | NO   | PRI | NULL    |       |
+------------+------------------+------+-----+---------+-------+
mysql> INSERT INTO products_suppliers VALUES (2001, 501), (2002, 501),
(2003, 501), (2004, 502), (2001, 503);
-- 外键列（子表的列）中的值必须匹配它们引用的列中的（父表）的有效值
mysql> SELECT * FROM products_suppliers;
+-----------+------------+
| productID | supplierID |
+-----------+------------+
| 2001      | 501        |
| 2002      | 501        |
| 2003      | 501        |
| 2004      | 502        |
| 2001      | 503        |
+-----------+------------+

接下来，从products表中删除supplierID列。

在删除此列之前，你需要删除在此列上构建的外键。要删除MySQL中的一个健，你需要知道由系统生成的约束名称。要查找约束名称，请执行SHOW CREATE TABLE products，并在CONSTRAINT constraint_name FOREIGN KEY ....子句中注意外键的约束名称。然后，你可以使用"ALTER TABLE products DROP FOREIGN KEY constraint_name来删除外键。

mysql> SHOW CREATE TABLE products \G
Create Table: CREATE TABLE `products` (
    `productID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `productCode` char(3) NOT NULL DEFAULT '',
    `name` varchar(30) NOT NULL DEFAULT '',
    `quantity` int(10) unsigned NOT NULL DEFAULT '0',
    `price` decimal(7,2) NOT NULL DEFAULT '99999.99',
    `supplierID` int(10) unsigned NOT NULL DEFAULT '501',
    PRIMARY KEY (`productID`),
    KEY `supplierID` (`supplierID`),
    CONSTRAINT `products_ibfk_1` FOREIGN KEY (`supplierID`)
    REFERENCES `suppliers` (`supplierID`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1006 DEFAULT CHARSET=latin1
mysql> ALTER TABLE products DROP FOREIGN KEY products_ibfk_1;
mysql> SHOW CREATE TABLE products \G

现在，我们可以删除列冗余的supplierID列。

mysql> ALTER TABLE products DROP supplierID;
mysql> DESC products;

---

查询

类似地，我们可以使用SELECT with JOIN来查询这3个表中的数据，例如:

mysql> SELECT products.name AS `Product Name`, price, suppliers.name AS `Supplier Name`
    FROM products_suppliers
    JOIN products ON products_suppliers.productID = products.productID
    JOIN suppliers ON products_suppliers.supplierID = suppliers.supplierID
WHERE price < 0.6;
+--------------+-------+---------------+
| Product Name | price | Supplier Name |
+--------------+-------+---------------+
| Pencil 3B    | 0.52  | ABC Traders   |
| Pencil 3B    | 0.52  | QQ Corp       |
| Pencil 6B    | 0.47  | XYZ Company   |
+--------------+-------+---------------+
-- 也可以为表名定义别名
mysql> SELECT p.name AS `Product Name`, s.name AS `Supplier Name`
       FROM products_suppliers AS ps
       JOIN products AS p ON ps.productID = p.productID
       JOIN suppliers AS s ON ps.supplierID = s.supplierID
       WHERE p.name = 'Pencil 3B';
+--------------+---------------+
| Product Name | Supplier Name |
+--------------+---------------+
| Pencil 3B    | ABC Traders   |
| Pencil 3B    | QQ Corp       |
+--------------+---------------+
-- 使用WHERE子句加入（遗留的，不推荐）
mysql> SELECT p.name AS `Product Name`, s.name AS `Supplier Name`
       FROM products AS p, products_suppliers AS ps, suppliers AS s
       WHERE p.productID = ps.productID
         AND ps.supplierID = s.supplierID
         AND s.name = 'ABC Traders';
+--------------+---------------+
| Product Name | Supplier Name |
+--------------+---------------+
| Pencil 3B    | ABC Traders   |
| Pencil 4B    | ABC Traders   |
| Pencil 5B    | ABC Traders   |
+--------------+---------------+

---

未完待续

下一篇我们接着介绍一对一关系。

---

如果喜欢这篇文章，请不要忘记关注🧡、点赞👍和收藏📔哦！