实战4:如何使用中间表
1. 前言
在外键一节中,我们介绍了外键的基本使用,并在末尾中给出了下面这句话:
外键
是体现数据表关系的核心点,但主流的外键方式却都是弱外键
。
不知道你是否会有些许疑惑,弱外键
是什么?强外键
又是什么?它与本节的中间表又有什么关系?
带着这些疑惑,我们一起来开始本小节的学习。
2. 弱外键与强外键
在外键一节中,我们介绍到外键可以通过如下的方式来创建:
FOREIGN KEY (user_id) REFERENCES imooc_user(id)
通过声明方式,会自主将两张表做外键关联,我们把这样的外键称为强外键
。强外键最大的特点就是层面,会维护外键关联的表。
但是也正是因为这个特性,强外键不够灵活,举个例子来说,当你某张表的数据时,如果另一张表有此表的外键,那么可能会被拒绝,当然你可以通过级联来同时另一张表中关联的数据。如下,我们新建两张存在外键关联的表:
DROP TABLE IF EXISTS imooc_user;CREATE TABLE imooc_user( id int PRIMARY KEY, username varchar(), age int);DROP TABLE IF EXISTS imooc_user_;CREATE TABLE imooc_user_( id int PRIMARY KEY, user_id int NOT NULL, int, FOREIGN KEY (user_id) REFERENCES imooc_user(id));INSERT INTO imooc_user(id,username,age) VALUES (,'pedro',);INSERT INTO imooc_user_(id,user_id,) VALUES (,,);
创建成功后,我们通过 Delete 来 pedro
:
DELETE FROM imooc_user WHERE id = ;
我们失败,并给出了如下信息:
(1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`imooc`.`imooc_user_`, CONSTRAINT `imooc_user__ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `imooc_user` (`id`))')
告诉我们 id
是 imooc_user_
表的外键,如果会破坏数据的完整性,因此拒绝了我们的操作。
我们改造一下外键约束,让它级联:
ALTER TABLE imooc_user_ DROP FOREIGN KEY imooc_user__ibfk_1;ALTER TABLE imooc_user_ ADD CONSTRAINT imooc_user__ibfk_1 FOREIGN KEY(user_id) REFERENCES imooc_user(id) ON DELETE CASCADE;
我们再次 pedro
:
DELETE FROM imooc_user WHERE id = ;
这次成功,且 imooc_user_
中的关联数据也被了。
强外键虽然能够保证数据的完整性(要么都有,要么都没有),但是弊端很明显,了一些数据后,与之关联的数据也都被了,不利于数据的维护,也不利于更改和迁移;再者,强外键会因为关联来同步检测和更新两张表,无疑会拉低整体的。因此目前大家普遍采用弱外键
的方式。
在 join 一节中,我们提到外键的最终落脚点是使用 Join 来连接数据,不过 连接并非只强外键,它其实也弱外键
,甚至无外键,只要连接的字段能够对应上,连接都是可行的。
那么什么是弱外键
了?答案其实很简单,强外键是层面上的外键,而弱外键是逻辑层面的上的外键。如下,我们新建两表:
CREATE TABLE imooc_user( id int PRIMARY KEY, username varchar(), age int);CREATE TABLE imooc_user_( id int PRIMARY KEY, user_id int NOT NULL, int);
在新建 imooc_user_ 表的 语句中,我们并未声明 user_id
是外键,但是在逻辑层面上我们认为它就是外键,在连接的时候知道其对应关系
就行了。
3. 中间表
聊完了外键,我们来介绍本节的重点 —— 中间表。先引入场景,有两张数据表,分别是 imooc_user
(表)和 imooc_class
(课程表),对于来说,他(她)可以购买多门课程,而对于课程来说,它也可以被多个购买。这样就产生了难题,与课程之间是典型的多对多
关系,因此我们需要另一张表(imooc_user_class
)来记录与课程之购买关系。
类似于 imooc_user_class
这样的关系表,我们称之为中间表
。对于它们三者,我们可以这样设计(省略诸多字段信息):
DROP TABLE IF EXISTS imooc_user;CREATE TABLE imooc_user( id int PRIMARY KEY, username varchar(), age int);DROP TABLE IF EXISTS imooc_class;CREATE TABLE imooc_class( id int PRIMARY KEY, name varchar(), description varchar());DROP TABLE IF EXISTS imooc_user_class;CREATE TABLE imooc_user_class( id int PRIMARY KEY, user_id int NOT NULL, class_id int NOT NULL);
从 imooc_user_class
表的结构上看,它的主体
其实就是一些外键的组合
。这也是中间表与外键的关系。
它们之关系如下图所示:
4. 实践
接下来,我们以实战的角度来看 imooc_user
(表)和 imooc_class
(课程表)以及关系表 imooc_user_class
。
首先,我们新增几条和课程记录:
INSERT INTO imooc_user(id,username,age) VALUES (,'pedro',),(,'tom',),(,'mary',);INSERT INTO imooc_class(id,name,description) VALUES (,'知多少', '一卷囊括天下事'),(,'回首又见Java','你蓦然回首时,我依然在灯火阑珊处'),(,'倚Python屠虫记', '看我这把Python大刀斩尽你无数爬虫');
接着,我们来模拟购买课程。
某一天,pedro
购买了 知多少
和回首又见Java
这两门课,有了中间表,我们无需改动主表,而是至中间表即可:
INSERT INTO imooc_user_class VALUES(,,), (,,);
第二天,mary
购买了 知多少
和倚Python屠虫记
两门课:
INSERT INTO imooc_user_class VALUES(,,), (,,);
现在,需要查看数据。首先,他想知道谁都购买了课程,由于购买记录都记载在了 imooc_user_class
表中,我们只需要它即可(人可能购买多门课程,所以需要 Distinct 去重):
SELECT DISTINCT user_id FROM imooc_user_class;
+---------+ | user_id | +---------+ | 1 | | 3 | +---------+
光有 user_id
可不行,我们需要知道,于是连接一下 imooc_user
即可:
SELECT DISTINCT user_id,username FROM imooc_user_class LEFT JOIN imooc_user ON imooc_user_class.user_id = imooc_user.id;
+---------+----------+ | user_id | username | +---------+----------+ | 1 | pedro | | 3 | mary | +---------+----------+
我们发现,pedro
和 mary
都购买了课程,这与上述一致。
还想知道,哪些课程被购买了:
SELECT DISTINCT class_id,name FROM imooc_user_class LEFT JOIN imooc_class ON imooc_user_class.class_id = imooc_class.id;
+----------+----------------+ | class_id | name | +----------+----------------+ | 1 | 知多少 | | 2 | 回首又见Java | | 3 | 倚Python屠虫记 | +----------+----------------+
不错,三门课都被购买了。更想知道 知多少
这门课被谁购买了:
SELECT DISTINCT user_id,username FROM imooc_user_class LEFT JOIN imooc_user ON imooc_user_class.user_id = imooc_user.id WHERE imooc_user_class.class_id = ;
+---------+----------+ | user_id | username | +---------+----------+ | 1 | pedro | | 3 | mary | +---------+----------+
不错,大家都买了这门课?。
可以看到,中间表的存在让数据的变得更为方便和有效了。当然你也可以选择不要中间表,而在两张主表中各自对方外键的方式来达到同样的,不过这样的方式显然不推荐。
5. 小结
业务开发中的很大一部分业务都是通过中间表
来实现的,请务必熟练掌握和理解它。
连接
操作是驱动中间表数据的核心操作,如果你还不够熟悉连接,可以再次阅读连接小节,并着手操练一番。