MySQL笔记之多表

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

外键

虽然要记录的是关于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 DELETEON 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语言的概述|学习笔记