前言
举例子,是最简单有效的学习方法。本系列文章以一个贯穿始终的场景,结合多个实例讲解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 |
+--------------+---------------+
未完待续
下一篇我们接着介绍一对一关系。
如果喜欢这篇文章,请不要忘记关注🧡、点赞👍和收藏📔哦!