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