介绍
在本实验中，我们将探索在 MySQL 中处理多个表的基础知识。理解表之间的关系以及如何从多个表中组合数据对于构建实际应用程序至关重要。我们将学习表的关系、不同类型的 JOIN 操作，以及如何使用外键约束来维护数据完整性。通过本实验，你将获得创建相关表、跨表查询数据以及管理表之间关系的实践经验。
理解表关系
在这一步中，我们将探索表关系的概念，并检查我们的表是如何连接的。理解表关系是使用关系型数据库的基础。
首先，连接到 MySQL：
sudo mysql -u root
连接成功后，选择我们的数据库：
USE bookstore;
让我们检查表的结构及其关系：
SHOW CREATE TABLE books;
你将看到包含外键约束的输出：
| books | CREATE TABLE `books` (
`book_id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`author_id` int(11) DEFAULT NULL,
`publisher_id` int(11) DEFAULT NULL,
`publication_year` int(11) DEFAULT NULL,
`price` decimal(10,2) NOT NULL,
PRIMARY KEY (`book_id`),
KEY `author_id` (`author_id`),
KEY `publisher_id` (`publisher_id`),
CONSTRAINT `books_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `authors` (`author_id`),
CONSTRAINT `books_ibfk_2` FOREIGN KEY (`publisher_id`) REFERENCES `publishers` (`publisher_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
让我们分解数据库中的关系：
每本书有一个作者（多对一关系）
books表中的author_id引用了authors表中的author_id。这意味着books表中的author_id必须存在于authors表中。- 多本书可以有同一个作者。
每本书有一个出版商（多对一关系）
books表中的publisher_id引用了publishers表中的publisher_id。- 多本书可以有同一个出版商。
为了看到这些关系的实际效果，让我们尝试一个简单的查询，看看每个作者写了多少本书：
SELECT author_id, COUNT(*) as book_count
FROM books
GROUP BY author_id;
输出将如下所示：
+-----------+------------+
| author_id | book_count |
+-----------+------------+
| 1 | 2 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
+-----------+------------+
这告诉我们，author_id 为 1 的作者有 2 本书，而 author_id 为 2、3 和 4 的作者各有 1 本书。这说明了多对一关系：多个书籍记录可以指向一个作者记录。
基本 INNER JOIN 操作
在这一步中，我们将学习 INNER JOIN，这是一种关键的连接操作。INNER JOIN 只返回两个表中匹配的行。可以将其想象为维恩图，其中 INNER JOIN 给出两个表的交集。如果一个表中的记录在另一个表中没有对应的匹配项，则该记录将从结果中排除。
让我们从一个简单的 INNER JOIN 开始，获取书籍标题及其作者的名字：
SELECT
books.title,
authors.first_name,
authors.last_name
FROM books
INNER JOIN authors ON books.author_id = authors.author_id;
这个查询：
- 从
books表开始（FROM books）。 - 将其与
authors表连接（INNER JOIN authors）。 - 使用
ON books.author_id = authors.author_id指定连接条件。这是数据库知道books表中的哪些行与authors表中的哪些行匹配的方式：它查找两个表中author_id值相等的记录。 - 选择我们想要查看的列：
books.title、authors.first_name和authors.last_name。
你应该会看到如下输出：
+----------------------------+------------+-----------+
| title | first_name | last_name |
+----------------------------+------------+-----------+
| The MySQL Guide | John | Smith |
| Data Design Patterns | Emma | Wilson |
| Database Fundamentals | Michael | Brown |
| SQL for Beginners | Sarah | Johnson |
| Advanced Database Concepts | John | Smith |
+----------------------------+------------+-----------+
在 SELECT 语句中，你会看到我们使用了 books.title、authors.first_name 和 authors.last_name。这明确告诉 MySQL 每列来自哪个表。如果列名在多个表中是唯一的，你可以省略表前缀（例如，只使用 title）。然而，为了避免歧义，尤其是在多个连接的情况下，最好始终指定表前缀。
我们还可以与 publishers 表进行连接：
SELECT
books.title,
publishers.name as publisher_name,
books.publication_year,
books.price
FROM books
INNER JOIN publishers ON books.publisher_id = publishers.publisher_id;
在这种情况下，publishers.name as publisher_name 意味着我们从 publishers 表中选择 name 列，并在输出中将其重命名为 publisher_name。这使得列的含义更加清晰。
+----------------------------+--------------------+------------------+-------+
| title | publisher_name | publication_year | price |
+----------------------------+--------------------+------------------+-------+
| The MySQL Guide | Tech Books Pro | 2023 | 45.99 |
| Data Design Patterns | Tech Books Pro | 2022 | 39.99 |
| Database Fundamentals | Database Press | 2021 | 29.99 |
| SQL for Beginners | Database Press | 2023 | 34.99 |
| Advanced Database Concepts | Query Publications | 2023 | 54.99 |
+----------------------------+--------------------+------------------+-------+
LEFT JOIN 操作
在这一步中，我们将探索 LEFT JOIN 操作。LEFT JOIN（有时称为 LEFT OUTER JOIN）返回“左”表（FROM 子句中提到的第一个表）中的所有记录以及“右”表中的匹配记录。与 INNER JOIN 的关键区别在于，即使右表中没有匹配项，左表中的记录仍会包含在结果中，右表中没有匹配项的地方会用 NULL 值填充。
让我们添加一个尚未出版任何书籍的作者：
INSERT INTO authors (first_name, last_name, email)
VALUES ('David', 'Clark', 'david.clark@email.com');
现在，让我们使用 LEFT JOIN 查看所有作者，包括那些尚未出版书籍的作者：
SELECT
authors.first_name,
authors.last_name,
COUNT(books.book_id) as book_count
FROM authors
LEFT JOIN books ON authors.author_id = books.author_id
GROUP BY authors.author_id;
你应该会看到如下输出：
+------------+-----------+------------+
| first_name | last_name | book_count |
+------------+-----------+------------+
| John | Smith | 2 |
| Emma | Wilson | 1 |
| Michael | Brown | 1 |
| Sarah | Johnson | 1 |
| David | Clark | 0 |
+------------+-----------+------------+
请注意，David Clark 出现在结果中，书籍数量为 0，尽管他尚未出版任何书籍。这是因为 LEFT JOIN 包含了 authors 表中的所有行，而对于 David，books 表中没有匹配的书籍，因此 COUNT(books.book_id) 的结果是 0，而不是 NULL。这说明了关键的区别：LEFT JOIN 保证左表中的所有行都会出现在结果中，而 INNER JOIN 只包含两个表中匹配的行。如果有书籍，计数将是一个整数，否则由于 COUNT() 的作用，计数将为 0。
多表连接
在这一步中，我们将学习如何连接两个以上的表。当你需要从多个相关表中组合数据以获取更全面的数据视图时，通常会用到这种操作。
让我们创建一个查询，将三个表中的信息组合在一起：
SELECT
b.title,
CONCAT(a.first_name, ' ', a.last_name) as author_name,
p.name as publisher_name,
b.publication_year,
b.price
FROM books b
INNER JOIN authors a ON b.author_id = a.author_id
INNER JOIN publishers p ON b.publisher_id = p.publisher_id
ORDER BY b.title;
让我们分解这个查询的作用：
- **
FROM books b**：这表示我们从books表开始，并为其指定别名b。使用别名（b、a、p）是一种良好的实践，可以使查询更短且更易读。 - **
INNER JOIN authors a ON b.author_id = a.author_id**：这将books表与authors表基于author_id进行连接。只有当两个表中都存在匹配的author_id时，才会返回行。a是authors表的别名。 - **
INNER JOIN publishers p ON b.publisher_id = p.publisher_id**：这将前一个连接的结果与publishers表基于publisher_id进行连接。只有当两个表中都存在匹配的publisher_id时，才会返回行。p是publishers表的别名。 - **
SELECT b.title, CONCAT(a.first_name, ' ', a.last_name) as author_name, p.name as publisher_name, b.publication_year, b.price**：我们从books表中选择标题，使用CONCAT将作者的名字和姓氏组合成author_name列，使用出版商的名称列并将其重命名为publisher_name，以及从各自的表中选择出版年份和价格。 - **
ORDER BY b.title**：这将输出按书籍标题升序排序。
+----------------------------+---------------+--------------------+------------------+-------+
| title | author_name | publisher_name | publication_year | price |
+----------------------------+---------------+--------------------+------------------+-------+
| Advanced Database Concepts | John Smith | Query Publications | 2023 | 54.99 |
| Data Design Patterns | Emma Wilson | Tech Books Pro | 2022 | 39.99 |
| Database Fundamentals | Michael Brown | Database Press | 2021 | 29.99 |
| SQL for Beginners | Sarah Johnson | Database Press | 2023 | 34.99 |
| The MySQL Guide | John Smith | Tech Books Pro | 2023 | 45.99 |
+----------------------------+---------------+--------------------+------------------+-------+
这个查询展示了如何将不同表中的数据组合在一起，以获取更完整的信息。每个书籍条目都与作者和出版商信息相关联。
让我们尝试另一个示例，显示书籍及其作者，包括没有书籍的作者，并在可用时显示出版商信息：
SELECT
CONCAT(a.first_name, ' ', a.last_name) as author_name,
b.title,
p.name as publisher_name
FROM authors a
LEFT JOIN books b ON a.author_id = b.author_id
LEFT JOIN publishers p ON b.publisher_id = p.publisher_id
ORDER BY author_name;
在这个示例中，我们使用了 LEFT JOIN。这将确保显示所有作者，无论他们是否出版了书籍。对于没有书籍的作者，标题和出版商名称将显示为 NULL。
+---------------+----------------------------+--------------------+
| author_name | title | publisher_name |
+---------------+----------------------------+--------------------+
| David Clark | NULL | NULL |
| Emma Wilson | Data Design Patterns | Tech Books Pro |
| John Smith | The MySQL Guide | Tech Books Pro |
| John Smith | Advanced Database Concepts | Query Publications |
| Michael Brown | Database Fundamentals | Database Press |
| Sarah Johnson | SQL for Beginners | Database Press |
+---------------+----------------------------+--------------------+
使用外键约束
在这最后一步中，我们将探索外键约束如何帮助维护相关表之间的数据完整性。外键约束通过防止创建孤立记录或不一致数据的操作，确保表之间的关系保持有效。
让我们通过一些示例来理解外键约束的工作原理：
首先，尝试添加一本带有无效 author_id 的书籍：
-- 由于外键约束，此操作将失败
INSERT INTO books (title, author_id, publisher_id, publication_year, price)
VALUES ('Failed Book', 999, 1, 2023, 29.99);
你会看到一个错误消息，因为 author_id 999 在 authors 表中不存在：
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bookstore`.`books`, CONSTRAINT `books_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `authors` (`author_id`))
此错误消息表明，books 表中 author_id 列上的外键约束阻止我们添加引用 authors 表中不存在的 author_id 的书籍。
同样，我们不能直接删除已出版书籍的作者，除非先处理他们的书籍：
-- 由于外键约束，此操作将失败
DELETE FROM authors WHERE author_id = 1;
尝试直接删除 author_id = 1 的作者会导致外键错误，因为有书籍引用了该 author_id。这是 MySQL 确保数据一致性的方式。
要安全地删除作者及其书籍，我们需要：
- 先删除书籍，再删除作者。这确保我们不会留下孤立记录。
- 使用
CASCADE DELETE（我们将在高级实验中探讨）。
让我们看看如何通过先删除书籍来正确删除书籍及其作者：
-- 首先，删除该作者的书籍
DELETE FROM books WHERE author_id = 1;
-- 现在我们可以安全地删除作者
DELETE FROM authors WHERE author_id = 1;
通过先删除书籍，你移除了外键引用，从而允许删除作者而不违反约束。
总结
在本实验中，我们涵盖了在 MySQL 中处理多个表的基本内容：
- 理解表关系以及如何使用主键和外键实现这些关系。
- 使用
INNER JOIN组合多个表中的匹配记录，展示如何使用前缀和别名选择相关列。 - 使用
LEFT JOIN包含一个表中的所有记录，即使它们在另一个表中没有匹配记录，并理解如何解释NULL值。 - 使用多个连接组合来自多个表的数据，展示如何一次性从三个表中获取数据。
- 使用外键约束维护数据完整性，防止孤立记录，并演示 MySQL 如何管理表之间的关系。
这些技能构成了有效使用关系型数据库的基础。理解如何跨多个表组合和关联数据对于构建健壮的数据库应用程序至关重要。
