深入 mysql，掌握一对一、一对多、多对多表设计、查询及级联操作

数据库表的基本概念与关系

数据库通常包含多个表，每个表存储特定类型的信息。例如：

• 学生表：存储学生信息。
• 老师表：存储老师信息。
• 班级表：存储班级信息。

这些表通过各种关系连接，形成一个结构化的数据管理系统。主要的关系类型包括：

• 一对多关系：如一个班级包含多名学生。
• 多对多关系：如一个老师可教授多个班级，一个班级也可由多名老师教授。
• 一对一关系：如一个用户对应一个身份证信息。

外键与表关联

外键是实现表之间关系的关键工具。通过外键，可以将两个表连接起来，实现数据的整合查询。

示例：用户表与身份证表的一对一关系：

• 用户表（user）：
  • id：主键，自动递增。
  • name：用户名，字符串类型。
• 身份证表（id_card）：
  • id：主键，自动递增。
  • card_name：身份证号，字符串类型。
  • user_id：外键，引用用户表的id。

sql

CREATE TABLE `user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `id_card` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `card_name` VARCHAR(45) NOT NULL,
  `user_id` INT,
  PRIMARY KEY (`id`),
  INDEX `card_id_idx` (`user_id`),
  CONSTRAINT `user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
);

 

我们也可以选择可视化方式场景，选择 hello-mysql 数据库，点击建表按钮：
 

分别添加 id、name 列：
 

id_card 表：
 

指定外键 user_id 关联 user 表的 id：
 

还要选择主表数据 update 或者 delete 的时候，从表怎么办，这里我们先用默认的：

数据操作与查询

插入数据后，可以通过多表关联查询来查看关联数据：

-- 插入数据
INSERT INTO `user` (`name`)
	VALUES
		('张三'),
		('李四'),
		('王五');
INSERT INTO id_card (card_name, user_id) 
  VALUES
  ('110101199001011234',1),
	('310101199002022345',2),
	('440101199003033456',3);

-- 多表关联查询
-- 选择 user 表的 id 和 name 字段，以及 id_card 表的 id 和 card_name 字段
SELECT user.id, name, id_card.id AS card_id, card_name 
-- 从 user 表开始查询
FROM user
-- 与 id_card 表进行内连接，连接条件是 user 表的 id 字段等于 id_card 表的 user_id 字段
JOIN id_card ON user.id = id_card.user_id;

这就是多表关联查询，语法是 JOIN ON。

JOIN 类型解析

• INNER JOIN（默认）: 只返回两个表中能关联上的数据。
• LEFT JOIN: 返回左表（FROM 语句之后的表）的所有记录，即使右表中没有匹配的记录。
• RIGHT JOIN: 返回右表（JOIN 语句之后的表）的所有记录，即使左表中没有匹配的记录。

例如，使用 RIGHT JOIN 的查询如下：

-- 选择 user 表的 id 和 name 字段，以及 id_card 表的 id 和 card_name 字段
SELECT user.id, name, id_card.id AS card_id, card_name
-- 从 user 表开始查询
FROM user
-- 通过 RIGHT JOIN 右连接 id_card 表，连接条件是 user 表的 id 字段等于 id_card 表的 user_id 字段
RIGHT JOIN id_card ON user.id = id_card.user_id;

此查询将返回所有 id_card 的数据，对于没有关联的 user 数据，其对应字段会显示为 null。
我们更新下 id_card 表的级联方式：
 

删除条 user 数据：
 

再执行上面的右查询代码：

外键约束与级联操作

外键约束用于维护数据库表之间的完整性。常见的外键约束处理方式包括：

• RESTRICT / NO ACTION:（通常是默认行为） 只有当从表没有关联记录时，才允许删除或更新主表的记录。
• CASCADE: 如果主表（外键指向的表）的记录被删除或更新，从表（有外键的表）的相应记录也会被级联删除或更新。
• SET NULL: 如果主表的记录被删除或其关键字段被更新，从表的外键字段会被设置为 null。

例如，如果设置外键约束为 CASCADE，并且更新了 user 表中某个 id，则 id_card 表中相应的 user_id 也会更新。如果删除了 user 表中的记录，所有关联的 id_card 记录也会被删除。

注意：在外键关系中，主表是被外键引用的表，而从表是包含外键的表。因此，当主表中的记录发生变更时，从表中依赖于这些记录的外键字段会受到影响。

一对多关系

一对多关系是数据库中常见的数据结构，其中一个实体可以关联多个其他实体，但这些被关联的实体仅指向单一实体。例如：

• 作者与文章：一个作者可以写多篇文章，但每篇文章只能有一个作者。
• 订单与商品：一个订单可以包含多个商品，但每个商品只属于一个订单。
• 部门与员工：一个部门可以有多名员工，但每名员工只属于一个部门。

实现方式

在数据库中，通常通过在“多”的一方添加一个外键来实现一对多关系。
例如，员工表中会包含一个指向部门表的外键 department_id。

-- 创建一个名为 `department` 的表，用于存储部门信息
CREATE TABLE `department` (
    `id` INT NOT NULL AUTO_INCREMENT,  -- 定义一个名为 `id` 的列，类型为 INT，不允许为空，自动增长
    `name` VARCHAR(45) NOT NULL,       -- 定义一个名为 `name` 的列，类型为 VARCHAR(45)，不允许为空
    PRIMARY KEY (`id`)                 -- 将 `id` 列设置为主键
);

-- 创建一个名为 `employee` 的表，用于存储员工信息
CREATE TABLE `employee` (
    `id` INT NOT NULL AUTO_INCREMENT,  -- 定义一个名为 `id` 的列，类型为 INT，不允许为空，自动增长
    `name` VARCHAR(45) NOT NULL,       -- 定义一个名为 `name` 的列，类型为 VARCHAR(45)，不允许为空
    `department_id` INT,               -- 定义一个名为 `department_id` 的列，类型为 INT，用于存储员工所属部门的 ID
    FOREIGN KEY (`department_id`) REFERENCES `department`(`id`) 
        ON DELETE SET NULL,            -- 设置外键，`department_id` 引用 `department` 表的 `id`，当部门被删除时，相关员工的 `department_id` 设置为 NULL
    PRIMARY KEY (`id`)                 -- 将 `id` 列设置为主键
);

创建了两个表：

1. 部门表 (department)：
   • id：部门的唯一标识符，自动增长，作为主键。
   • name：部门的名称，存储类型为长度最大为 45 的字符串。
2. 员工表 (employee)：
   • id：员工的唯一标识符，自动增长，作为主键。
   • name：员工的姓名，存储类型为长度最大为 45 的字符串。
   • department_id：外键，指向部门表的 id，表示员工所属的部门。如果所属部门被删除，员工记录中的 department_id 将被设置为 NULL，表示不再属于任何部门。

多对多关系

多对多关系指两种实体相互间可以有多个关联。例如：

• 文章与标签：一篇文章可以有多个标签，同一个标签可以标记多篇文章。
• 学生与课程：一个学生可以选修多门课程，一门课程可以被多个学生选修。
• 用户与角色：一个用户可以拥有多个角色，一个角色可以包含多个用户。

实现方式

多对多关系通常通过一个中间表来实现，这个表存储两个实体之间的关联：

-- 创建一个名为 `article` 的表，用于存储文章信息
CREATE TABLE `article` (
    `id` INT NOT NULL AUTO_INCREMENT,  -- 定义一个名为 `id` 的列，类型为 INT，不允许为空，自动增长，用于唯一标识每篇文章
    `title` VARCHAR(50) NOT NULL,      -- 定义一个名为 `title` 的列，类型为 VARCHAR(50)，不允许为空，用于存储文章的标题
    `content` TEXT NOT NULL,           -- 定义一个名为 `content` 的列，类型为 TEXT，不允许为空，用于存储文章的内容
    PRIMARY KEY (`id`)                 -- 将 `id` 列设置为主键
);

-- 创建一个名为 `tag` 的表，用于存储标签信息
CREATE TABLE `tag` (
    `id` INT NOT NULL AUTO_INCREMENT,  -- 定义一个名为 `id` 的列，类型为 INT，不允许为空，自动增长，用于唯一标识每个标签
    `name` VARCHAR(50) NOT NULL,       -- 定义一个名为 `name` 的列，类型为 VARCHAR(50)，不允许为空，用于存储标签的名称
    PRIMARY KEY (`id`)                 -- 将 `id` 列设置为主键
);

-- 创建一个名为 `article_tag` 的中间表，用于存储文章和标签之间的多对多关系
CREATE TABLE `article_tag` (
    `article_id` INT NOT NULL,         -- 定义一个名为 `article_id` 的列，类型为 INT，不允许为空，用于引用文章表的 `id`
    `tag_id` INT NOT NULL,             -- 定义一个名为 `tag_id` 的列，类型为 INT，不允许为空，用于引用标签表的 `id`
    PRIMARY KEY (`article_id`, `tag_id`),  -- 将 `article_id` 和 `tag_id` 组合设置为主键，确保每对文章和标签的关系是唯一的
    FOREIGN KEY (`article_id`) REFERENCES `article`(`id`) ON DELETE CASCADE,  -- 设置外键，当引用的文章被删除时，相关的关联记录也会被自动删除
    FOREIGN KEY (`tag_id`) REFERENCES `tag`(`id`) ON DELETE CASCADE  -- 设置外键，当引用的标签被删除时，相关的关联记录也会被自动删除
);

1. 文章表 (**article**)：
   • id：文章的唯一标识符，自动增长，作为主键。
   • title：文章的标题，存储类型为长度最大为 50 的字符串。
   • content：文章的内容，存储类型为 TEXT，可以存储较长的文本。
2. 标签表 (**tag**)：
   • id：标签的唯一标识符，自动增长，作为主键。
   • name：标签的名称，存储类型为长度最大为 50 的字符串。
3. 文章标签关联表 (**article_tag**)：
   • article_id 和 tag_id：用于存储文章和标签之间的关系。每个字段分别引用 article 表和 tag 表的 id。
   • 通过设置复合主键（article_id, tag_id），确保每对文章和标签的关联是唯一的。
   • 外键约束带有 ON DELETE CASCADE 选项，意味着当删除文章或标签时，所有相关的关联记录也会自动被删除，这有助于维护数据库的完整性和一致性。

假设有以下文章和标签：

• 文章1: ID = 1
• 文章2: ID = 2
• 标签A: ID = 1
• 标签B: ID = 2
• 标签C: ID = 3

文章到标签的关系如下：

• 文章1 关联标签A 和 标签B
• 文章2 关联标签A 和 标签C

那么 article_tag 表中的数据会是这样的：

+------------+--------+
| article_id | tag_id |
+------------+--------+
| 1          | 1      |
| 1          | 2      |
| 2          | 1      |
| 2          | 3      |
+------------+--------+

查询与操作

查询操作

使用 JOIN 语句可以查询出部门及其所有员工，或文章及其所有标签：

-- 查询部门及其员工
SELECT * 
FROM `department`  -- 从“department”表中选择数据，该表存储部门信息
JOIN `employee`    -- 通过 JOIN 操作与“employee”表连接
ON `department`.`id` = `employee`.`department_id`  -- 连接条件是部门表的 id 与员工表的部门 id 相匹配
WHERE `department`.`id` = 5;  -- 仅查询 id 为 5 的部门及其员工

-- 查询文章及其标签
SELECT `t`.`name` AS 标签名, `a`.`title` AS 文章标题
FROM `article` `a`  -- 从“article”表中选择数据，该表存储文章信息，并使用别名 `a`
JOIN `article_tag` `at` ON `a`.`id` = `at`.`article_id`  -- 通过 JOIN 操作连接“article_tag”表，使用别名 `at`，连接条件是文章 ID 匹配
JOIN `tag` `t` ON `t`.`id` = `at`.`tag_id`  -- 再通过 JOIN 操作连接“tag”表，使用别名 `t`，连接条件是标签 ID 匹配
WHERE `a`.`id` = 1;  -- 仅查询 id 为 1 的文章及其对应的标签

删除操作

删除一篇文章时，与之关联的标签关系也会被自动删除，这是通过设置外键的 ON DELETE CASCADE 选项实现的：

DELETE FROM `article` WHERE `id` = 1;