前言
举例子，是最简单有效的学习方法。本系列文章以一个贯穿始终的场景，结合多个实例讲解MySQL的基本用法。
❔ 为什么要写这个系列？
- 模仿是最好的老师，实践是检验成果的方法。
- 本系列以实操样例和应用场景为核心，将MySQL基本用法贯穿其中，达到学以致用的效果。
❔ 为什么要学习MySQL？
- MySQL是最常用的数据库之一，具有简单易用的特点，适合初学者学习
- 数据库的基本用法有相通之处，学会MySQL可以为学习其他数据库打下良好基础
❔ 跟别的入门教材有什么不同？
- 以一个贯穿始终的应用场景为主线，渐进地讲解用法
- 难度适中，既有基础方法，也有值得注意的关键细节
❔ 本系列文章不包含哪些内容？
该系列文章的目标是，将最核心的知识，以最直接的方式，呈现给初学者。因此不包含：
- MySQL安装方法
- MySQL系统管理方法，例如备份、恢复、导入导出等
- 高级主题，例如数据库监控、数据库调优和SQL优化
回顾
上篇文章👉《【跟着例子学MySQL】数据更新 – 更新,删除和导入导出》 讲了如何更新和导出数据。
这篇开始讲解多表关联的相关内容。
多表关联
到目前为止，我们的例子只涉及到一个“products”表。一个实用的数据库包含许多相关的表。
产品有供应商。如果每个产品都有一个供应商，并且每个供应商只提供一个产品（称为一对一关系），我们可以简单地将供应商的数据（名称、地址、电话号码）添加到products表中。假设每个产品都有一个供应商，并且一个供应商可能提供零个或多个产品（称为一对多关系）。将供应商的数据放入产品表中会导致数据的重复。这是因为一个供应商可能会提供许多产品，因此，同一个供应商的数据会出现在许多行中。这不仅浪费了存储，而且很容易导致不一致（因为所有重复的数据必须同时更新）。如果一个产品有许多供应商，每个供应商可以提供多对多的关系，情况就更加复杂了。
一对多关系
假设每个产品都有一个供应商，并且每个供应商提供一个或多个产品。我们可以创建一个名为suppliers的表来存储供应商的数据（例如，姓名、地址和电话号码）。我们创建一个具有唯一值的列supplierID来标识每个供应商。我们设置supplierID作为表供应商的主键（以确保唯一性和方便快速搜索）。
为了将供应商表与产品表联系起来，我们在products表中添加了一个新的列——supplierID。然后，我们将products表中的supplierID列设置为外键引用，以确保所谓的引用完整性。
suppliers表定义如下：
| supplierID | name | phone |
|---|---|---|
| 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) | supplierID INT (Foreign Key) |
|---|---|---|---|---|---|
| 1001 | PEN | Pen Red | 5000 | 1.23 | 501 |
| 1002 | PEN | Pen Blue | 8000 | 1.25 | 501 |
| 1003 | PEN | Pen Black | 2000 | 1.25 | 501 |
| 1004 | PEC | Pencil 2B | 10000 | 0.48 | 502 |
我们需要首先创建suppliers表，因为products表引用了suppliers表。suppliers表被称为父表，而products表在此关系中被称为子表。
mysql> USE southwind;
mysql> DROP TABLE IF EXISTS suppliers;
mysql> CREATE TABLE suppliers (
supplierID INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(30) NOT NULL DEFAULT '',
phone CHAR(8) NOT NULL DEFAULT '',
PRIMARY KEY (supplierID));
mysql> DESCRIBE suppliers;
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| supplierID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | | |
| phone | char(8) | NO | | | |
+------------+------------------+------+-----+---------+----------------+
mysql> INSERT INTO suppliers VALUE
(501, 'ABC Traders', '88881111'),
(502, 'XYZ Company', '88882222'),
(503, 'QQ Corp', '88883333');
mysql> SELECT * FROM suppliers;
+------------+-------------+----------+
| supplierID | name | phone |
+------------+-------------+----------+
| 501 | ABC Traders | 88881111 |
| 502 | XYZ Company | 88882222 |
| 503 | QQ Corp | 88883333 |
+------------+-------------+----------+
ALTER TABLE
我们将使用ALTER TABLE命令，以添加一个新的supplierID到products表中，而不是删除和重新创建products表。
mysql> ALTER TABLE products
ADD COLUMN supplierID INT UNSIGNED NOT NULL;
Query OK, 4 rows affected (0.13 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> DESCRIBE products;
+-------------+------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+------------+----------------+
| productID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| productCode | char(3) | NO | | | |
| name | varchar(30) | NO | | | |
| quantity | int(10) unsigned | NO | | 0 | |
| price | decimal(10,2) | NO | | 9999999.99 | |
| supplierID | int(10) unsigned | NO | | NULL | |
+-------------+------------------+------+-----+------------+----------------+
接下来，我们将在products表的supplierID列上添加一个外键约束，以确保products表中的每个supplierID总是引用suppliers表中的一个有效的supplierID——这被称为参照完整性。
在添加外键之前，我们需要将products表中现有记录的supplierID设置为suppliers表中有效的supplierID（如supplierID=501）。
-- 将“products”表中现有记录的supplierID设置为有效的supplierID
mysql> UPDATE products SET supplierID = 501;
-- Add a foreign key constrain
mysql> ALTER TABLE products
ADD FOREIGN KEY (supplierID) REFERENCES suppliers (supplierID);
mysql> DESCRIBE products;
+-------------+------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+------------+----------------+
......
| supplierID | int(10) unsigned | NO | MUL | | |
+-------------+------------------+------+-----+------------+----------------+
mysql> UPDATE products SET supplierID = 502 WHERE productID = 2004;
-- 选择有效的productID
mysql> SELECT * FROM products;
+-----------+-------------+-----------+----------+-------+------------+
| productID | productCode | name | quantity | price | supplierID |
+-----------+-------------+-----------+----------+-------+------------+
| 2001 | PEC | Pencil 3B | 500 | 0.52 | 501 |
| 2002 | PEC | Pencil 4B | 200 | 0.62 | 501 |
| 2003 | PEC | Pencil 5B | 100 | 0.73 | 501 |
| 2004 | PEC | Pencil 6B | 500 | 0.47 | 502 |
+-----------+-------------+-----------+----------+-------+------------+
带JOIN的SELECT语句
SELECT命令可以用来查询和连接两个相关表中的数据。例如，要列出产品名称（在产品表中）和供应商名称（在供应商表中），我们可以通过两个常用的供应商ID列连接这两个表：
mysql> SELECT products.name, price, suppliers.name
FROM products
JOIN suppliers ON products.supplierID = suppliers.supplierID
WHERE price < 0.6;
+-----------+-------+-------------+
| name | price | name |
+-----------+-------+-------------+
| Pencil 3B | 0.52 | ABC Traders |
| Pencil 6B | 0.47 | XYZ Company |
+-----------+-------+-------------+
-- 需要使用products.name和suppliers.name。名称来区分这两个“names”
mysql> SELECT products.name, price, suppliers.name
FROM products, suppliers
WHERE products.supplierID = suppliers.supplierID
AND price < 0.6;
+-----------+-------+-------------+
| name | price | name |
+-----------+-------+-------------+
| Pencil 3B | 0.52 | ABC Traders |
| Pencil 6B | 0.47 | XYZ Company |
+-----------+-------+-------------+
In the above query result, two of the columns have the same heading "name". We could create aliases for headings.
-- 使用别名作为列名以进行显示
mysql> SELECT products.name AS `Product Name`, price, suppliers.name AS `Supplier Name`
FROM products
JOIN suppliers ON products.supplierID = suppliers.supplierID
WHERE price < 0.6;
+--------------+-------+---------------+
| Product Name | price | Supplier Name |
+--------------+-------+---------------+
| Pencil 3B | 0.52 | ABC Traders |
| Pencil 6B | 0.47 | XYZ Company |
+--------------+-------+---------------+
-- 对表名也可以使用别名
mysql> SELECT p.name AS `Product Name`, p.price, s.name AS `Supplier Name`
FROM products AS p
JOIN suppliers AS s ON p.supplierID = s.supplierID
WHERE p.price < 0.6;
未完待续
下一篇我们接着介绍多对多关系。
如果喜欢这篇文章，请不要忘记关注🧡、点赞👍和收藏📔哦！
2893
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
