MySQL笔记之多表

2022-03-08 17:28:00

貌似很久不写博客了，趁着项目刚完结，记录一下这段时间了解到的一些认识，或许有些地方理解的还不够到位，看到的希望能不吝赐教。

外键

虽然要记录的是关于MySQL的一些内容，但是对于大多数的关系型数据库也是通用的。

概念

在百度百科中，有这样的一个词条： 外键

如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。外键又称作外关键字。

通俗来讲，外键就是从表用来关联主表的一个约束。外键是用来实现参照完整性的，不同的外键约束方式将可以使两张表紧密的结合起来，特别是修改或者删除的级联操作将使得日常的维护工作更加轻松。

外键主要用来保证数据的完整性和一致性。, 而且在MySQL中，由于引擎的关系，只有聚集索引InnoDB才支持外键，而非聚集索引MyISAM则不支持外键约束。所以接下来的内容是基于InnoDB的。

与索引的关系

可以认为，外键对于从表而言只是一个特殊的字段，一个约束关系。其功能就是实现两（多）张表的数据的完整性和一致性。

当创建完成一个外键的时候，数据库会默认创建索引。来提升两（多）表查询的效率。 关于更加低层的索引和外键的关系，这里暂且不过多叙述。

外键和索引的关系可以一起来看个小例子。

mysql> desc students;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(12)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(32) | NO   |     | NULL    |                |
| class_id | int(12)     | NO   | MUL | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> desc classes;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(12)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

很典型的多对一（一对多）关系，一个学生属于一个班级，一个班级有好多学生。所以班级表为主表， 学生表即为从表。

  • 索引信息：

MySQL笔记之多表

  • 外键信息：
    MySQL笔记之多表

写法

标准的SQL式外键的写法如下：

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]

而对于上面的reference_option有这么几个可选项（在MySQL中）：

  • RESTRICT: 当在主表（即外键的来源表）中更新、删除对应记录时，首先检查该记录是否有对应外键，如果有则不允许更新、删除。

  • CASCADE: 主表更新或者删除相关的记录，则会自动的更新或者删除从表中与之相匹配的数据。

  • SET NULL: 当从表中的外键字段没有被设置为NOT NULL的时候，当主表更新或者删除相关的记录时，从表中与之相匹配的记录会被设置为NULL。

  • NO ACTION: InnoDB拒绝删除或者更新主表。即如果存在从数据，不允许删除主数据。

需要注意的是：

NO ACTION和RESTRICT的区别：只有在及个别的情况下会导致区别，前者是在其他约束的动作之后执行，后者具有最高的优先权执行。

查询

理解了上面的创建过程，其实还是远远不够的。最好是来几个比较生动的小例子来加深一下记忆。关于单表的查询内容，相比大家都很熟悉了。这里就不过多叙述咯。

一对多（多对一）查询

在数据库内，比单表操作稍微难那么一点点的，应该就是两张表上的查询了。下面来举个简单的小例子。

mysql> select * from classes;
+----+----------+
| id | name     |
+----+----------+
|  1 | 软1414班 |
|  2 | 软1413班 |
|  3 | 软1415班 |
|  4 | 软1409班 |
+----+----------+
4 rows in set (0.00 sec)

mysql> select * from students;
+----+------------+----------+
| id | name       | class_id |
+----+------------+----------+
|  1 | 郭璞       |        1 |
|  2 | Gad GO     |        2 |
|  3 | 陈Sir      |        3 |
|  4 | MagicTiger |        4 |
+----+------------+----------+
4 rows in set (0.00 sec)

MySQL笔记之多表

可以看出，一对多（多对一）关系的查询操作也是很简单的。由于我们设置的外键策略为CASCADE，所以当主表数据更新或者删除的时候，从表的数据也会相应的更改。

比如删除班级编号为4的行记录，然后再次观察一下结果会怎样。

mysql> select * from students;
+----+--------+----------+
| id | name   | class_id |
+----+--------+----------+
|  1 | 郭璞   |        1 |
|  2 | Gad GO |        2 |
|  3 | 陈Sir  |        3 |
+----+--------+----------+
3 rows in set (0.00 sec)

班级编号为4的记录被删除后，对应到从表（students）中的MagicTiger记录也被删除了。

那么我们将外键策略更改为RESTRICT试试。同样，这次将主表（classes）编号为1的记录内容更改一下。
MySQL笔记之多表

根据错误提示，我们会发现，由于从表（students）有相关记录引用了主表（classes）的某条记录，根据外键约束原则，不能随意删除或者更改。

多对多查询

多对多的关系也比较普遍，比如对于一个博客系统而言。一篇文章可以附属多个标签，而一个标签下可以拥有很多文章。这就是典型的多对多关系了。

mysql> select * from posts;
+----+--------------------------+
| id | title                    |
+----+--------------------------+
|  1 | 张三，你快乐吗？         |
|  2 | 张三，编程能使你快乐吗？ |
|  3 | 张三，编程能使你快乐吗？ |
|  4 | 张三，编程能使你快乐吗？ |
+----+--------------------------+
4 rows in set (0.00 sec)

mysql> select * from tags;
+----+----------+
| id | name     |
+----+----------+
|  1 | 心情随笔 |
|  2 | 心得体会 |
|  3 | 感悟     |
|  4 | 心情随笔 |
|  5 | 心得体会 |
|  6 | 感悟     |
|  7 | 心情随笔 |
|  8 | 心得体会 |
|  9 | 感悟     |
+----+----------+
9 rows in set (0.00 sec)

mysql> select * from posts_tags;
+----+---------+--------+
| id | post_id | tag_id |
+----+---------+--------+
|  1 |       1 |      1 |
|  2 |       1 |      2 |
|  3 |       1 |      3 |
+----+---------+--------+
3 rows in set (0.00 sec)

然后我们来个多对多关系的查询。

mysql> SELECT
    -> p.title '文章标题',
    -> t. NAME '标签'
    -> FROM
    -> posts AS p,
    -> tags AS t,
    -> posts_tags AS pt
    -> WHERE
    -> p.id = pt.post_id
    -> AND t.id = pt.tag_id;
+------------------+----------+
| 文章标题         | 标签     |
+------------------+----------+
| 张三，你快乐吗？ | 心情随笔 |
| 张三，你快乐吗？ | 心得体会 |
| 张三，你快乐吗？ | 感悟     |
+------------------+----------+
3 rows in set (0.00 sec)

这样就能把多对多的两张表之间的数据关联起来了。核心在于，把表示关系的第三张表当做是查询的纽带。

插入

刚才的数据其实是我使用SQL语句插入的，为了更好的记录这些知识点，就把插入数据的步骤留在了后面。

多对一（一对多）

对于多对一（一对多）这样的表，插入数据的情况比较简单，只需要注意好外键带来的约束关系即可。还是拿本次这个学生-班级表来说，一个新来的学生势必要分配给一个班级的（这里不考虑一个班级只有一个学生的情况）。也就是说，一定会先有班级，然后才能分配学生到底在哪个班级。

  • 正常插入一个有班级的学生。
mysql> select * from students;
+----+--------+----------+
| id | name   | class_id |
+----+--------+----------+
|  1 | 郭璞   |        1 |
|  2 | Gad GO |        2 |
|  3 | 陈Sir  |        3 |
+----+--------+----------+
3 rows in set (0.01 sec)

mysql> insert into students(name, class_id) values('小薇', 1);
Query OK, 1 row affected (0.08 sec)

mysql> select * from students;
+----+--------+----------+
| id | name   | class_id |
+----+--------+----------+
|  1 | 郭璞   |        1 |
|  2 | Gad GO |        2 |
|  3 | 陈Sir  |        3 |
|  4 | 小薇   |        1 |
+----+--------+----------+
4 rows in set (0.00 sec)
  • 当班级编号不存在时，由于外键约束，插入会失败。
mysql> select * from classes;
+----+----------+
| id | name     |
+----+----------+
|  1 | 软1411班 |
|  2 | 软1413班 |
|  3 | 软1415班 |
+----+----------+
3 rows in set (0.01 sec)

mysql> insert into students(name, class_id) values('宋仲基', 7);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`relations`.`students`, CONSTRAINT `fk_student_class` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))
mysql>

因此，在对一个数据库进行关系设计的时候，考虑清楚外键是否有必要还是挺有用的。

多对多插入

刚才记录了关于多对多关系查询的例子。而且从外键上也只能看到ON DELETE 和 ON UPDATE 这俩选项，也许你会疑惑， 为什么没有INSERT啊？答案是数据库其实并没有那么的智能，它没办法自动的分辨出你的这篇文章会被打上什么标签， 也不会分辨出你这个标签下会挂上多少个文章。这些工作是需要我们手动（或者说根据程序指定）的方式来添加的。

还是拿本次的博客系统中：博客–标签来举例。一篇文章被发表的时候会根据用户的选择打上一些个标签，我们需要把文章插入到数据库的同时，将标签与文章的关系放到关系表中。如下：

START TRANSACTION;

INSERT INTO posts_tags (post_id, tag_id) SELECT
    p.id,
    t.id
FROM
    posts p,
    tags t
WHERE
    p.id = (
        SELECT
            p.id
        FROM
            p
        WHERE
            title = "张三，你快乐吗？"
    )
AND t.id IN (
    SELECT
        t.id
    FROM
        t
    WHERE
        NAME IN (
            '心情随笔',
            '心得体会',
            '感悟'
        )
);

COMMIT;

这样就可以将博客和标签之间的关系持久化到第三张关系表中了。

mysql> select * from posts_tags;
+----+---------+--------+
| id | post_id | tag_id |
+----+---------+--------+
|  1 |       1 |      1 |
|  2 |       1 |      2 |
|  3 |       1 |      3 |
+----+---------+--------+
3 rows in set (0.01 sec)

一般而言，使用事务会更加安全一些，当然了使用触发器也是可以的，但是触发器不能保证语句被正确执行。而采用事务就可以原子性的实现。

上面的SQL语句看起来仍然不够优雅，更好的替代方式是编写一个存储过程，接收参数，然后在事务中实现这个功能。

总结

回顾一下，感觉写的还是很浅显。由此可见，关系型数据库学问多着哩。O(∩_∩)O哈哈~

上一篇：gdb调试运行程序带参数（调用动态链接库），debug过程记录


下一篇：SQL语言的概述|学习笔记