## 数据库表的基本概念与关系数据库通常包含多个表，每个表存储特定类型的信息。例如： * 学生表：存储学生信息。 * 老师表：存储老师信息。 * 班级表：存储班级信息。这些表通过各种关系连接，形成一个结构化的数据管理系统。主要的关系类型包括： * 一对多关系：如一个班级包含多名学生。 * 多对多关系：如一个老师可教授多个班级，一个班级也可由多名老师教授。 * 一对一关系：如一个用户对应一个身份证信息。 ## 外键与表关联外键是实现表之间关系的关键工具。通过外键，可以将两个表连接起来，实现数据的整合查询。示例：用户表与身份证表的一对一关系： * 用户表（`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 数据库，点击建表按钮：
![image.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/ac2ce7bfc8944cbab7c0a5a8b5ac008a~tplv-k3u1fbpfcp-jj-mark:0:0:0:0:q75.image#?w=1406\&h=794\&s=185749\&e=png\&b=f5f4f4)
分别添加 id、name 列：
![image.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/e2a6884771764797ab65af00097f7539~tplv-k3u1fbpfcp-jj-mark:0:0:0:0:q75.image#?w=1838\&h=978\&s=182321\&e=png\&b=f3f3f3)
id\_card 表：
![image.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/03c1ca3993da4a19bfcc597e4a476f8d~tplv-k3u1fbpfcp-jj-mark:0:0:0:0:q75.image#?w=1826\&h=960\&s=189295\&e=png\&b=f3f3f3)
指定外键 user\_id 关联 user 表的 id：
![image.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/22055da78e5a4f92b68c833ccb8242a4~tplv-k3u1fbpfcp-jj-mark:0:0:0:0:q75.image#?w=1834\&h=968\&s=135437\&e=png\&b=f8f8f8)
还要选择主表数据 update 或者 delete 的时候，从表怎么办，这里我们先用默认的：
![image.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/85683a53762b45daa1df64648e7064ab~tplv-k3u1fbpfcp-jj-mark:0:0:0:0:q75.image#?w=1798\&h=810\&s=85934\&e=png\&b=fafafa) ## 数据操作与查询插入数据后，可以通过多表关联查询来查看关联数据： ```sql -- 插入数据 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; ``` 关联查询到的结果：
![image.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/0bc2f7df8c7e45219c5e2b1347ed34ba~tplv-k3u1fbpfcp-jj-mark:0:0:0:0:q75.image#?w=506\&h=156\&s=34620\&e=png\&b=fdfdfd)
这就是多表关联查询，语法是 JOIN ON。 ## JOIN 类型解析 * INNER JOIN（默认）: 只返回两个表中能关联上的数据。 * LEFT JOIN: 返回左表（`FROM` 语句之后的表）的所有记录，即使右表中没有匹配的记录。 * RIGHT JOIN: 返回右表（`JOIN` 语句之后的表）的所有记录，即使左表中没有匹配的记录。例如，使用 `RIGHT JOIN` 的查询如下： ```sql -- 选择 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 表的级联方式：
![image.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/d3e3098f772948fb9097daef789e0f3c~tplv-k3u1fbpfcp-jj-mark:0:0:0:0:q75.image#?w=1756\&h=1054\&s=134305\&e=png\&b=f9f9f9)
删除条 `user` 数据：
![image.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/70fc4fffcfe049da883de4d8163d78a9~tplv-k3u1fbpfcp-jj-mark:0:0:0:0:q75.image#?w=784\&h=306\&s=59164\&e=png\&b=f0f0f0)
再执行上面的右查询代码：
![image.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/1e41b29ecabb4685ad6c43e3877c020b~tplv-k3u1fbpfcp-jj-mark:0:0:0:0:q75.image#?w=578\&h=152\&s=35110\&e=png\&b=fdfdfd) ## 外键约束与级联操作外键约束用于维护数据库表之间的完整性。常见的外键约束处理方式包括： * `RESTRICT` / `NO ACTION`:（通常是默认行为）只有当从表没有关联记录时，才允许删除或更新主表的记录。 * `CASCADE`: 如果主表（外键指向的表）的记录被删除或更新，从表（有外键的表）的相应记录也会被级联删除或更新。 * `SET NULL`: 如果主表的记录被删除或其关键字段被更新，从表的外键字段会被设置为 `null`。例如，如果设置外键约束为 `CASCADE`，并且更新了 `user` 表中某个 `id`，则 `id_card` 表中相应的 `user_id` 也会更新。如果删除了 `user` 表中的记录，所有关联的 `id_card` 记录也会被删除。注意：在外键关系中，主表是被外键引用的表，而从表是包含外键的表。因此，当主表中的记录发生变更时，从表中依赖于这些记录的外键字段会受到影响。 ## 一对多关系一对多关系是数据库中常见的数据结构，其中一个实体可以关联多个其他实体，但这些被关联的实体仅指向单一实体。例如： - **作者与文章**：一个作者可以写多篇文章，但每篇文章只能有一个作者。 - **订单与商品**：一个订单可以包含多个商品，但每个商品只属于一个订单。 - **部门与员工**：一个部门可以有多名员工，但每名员工只属于一个部门。 ### 实现方式在数据库中，通常通过在“多”的一方添加一个外键来实现一对多关系。
例如，员工表中会包含一个指向部门表的外键 `department_id`。 ```sql -- 创建一个名为 `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，表示不再属于任何部门。 ## 多对多关系多对多关系指两种实体相互间可以有多个关联。例如： - **文章与标签**：一篇文章可以有多个标签，同一个标签可以标记多篇文章。 - **学生与课程**：一个学生可以选修多门课程，一门课程可以被多个学生选修。 - **用户与角色**：一个用户可以拥有多个角色，一个角色可以包含多个用户。 ### 实现方式多对多关系通常通过一个中间表来实现，这个表存储两个实体之间的关联： ```sql -- 创建一个名为 `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 表中的数据会是这样的： ```sql +------------+--------+ | article_id | tag_id | +------------+--------+ | 1 | 1 | | 1 | 2 | | 2 | 1 | | 2 | 3 | +------------+--------+ ``` ## 查询与操作 ### 查询操作使用 `JOIN` 语句可以查询出部门及其所有员工，或文章及其所有标签： ```sql -- 查询部门及其员工 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` 选项实现的： ```sql DELETE FROM `article` WHERE `id` = 1; ```