实战4:如何使用中间表
1. 前言
在外键一节中,我们介绍了外键的基本使用,并在末尾中给出了下面这句话:
外键
是体现数据表关系的核心功能点,但主流的外键方式却都是弱外键
。
不知道你是否会有些许疑惑,弱外键
是什么?强外键
又是什么?它与本节的中间表又有什么关系?
带着这些疑惑,我们一起来开始本小节的学习。
2. 弱外键与强外键
2.1 强外键的缺点
在外键一节中,我们介绍到外键可以通过如下的方式来创建:
FOREIGN KEY (user_id) REFERENCES imooc_user(id)
通过声明方式,数据库会自主将两张表做外键关联,我们把这样的外键称为强外键
。强外键最大的特点就是数据库层面支持,数据库会自动维护外键关联的表。
但是也正是因为这个特性,强外键不够灵活,举个例子来说,当你删除某张表的数据时,如果另一张表有此表的外键,那么删除可能会被拒绝,当然你可以通过级联来同时删除另一张表中关联的数据。如下,我们新建两张存在外键关联的表:
DROP TABLE IF EXISTS imooc_user;
CREATE TABLE imooc_user
(
id int PRIMARY KEY,
username varchar(20),
age int
);
DROP TABLE IF EXISTS imooc_user_score;
CREATE TABLE imooc_user_score
(
id int PRIMARY KEY,
user_id int NOT NULL,
score int,
FOREIGN KEY (user_id) REFERENCES imooc_user(id)
);
INSERT INTO imooc_user(id,username,age) VALUES (1,'pedro',23);
INSERT INTO imooc_user_score(id,user_id,score) VALUES (1,1,9);
创建成功后,我们通过 Delete 来删除用户pedro
:
DELETE FROM imooc_user WHERE id = 1;
数据库提示我们删除失败,并给出了如下错误信息:
(1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`imooc`.`imooc_user_score`, CONSTRAINT `imooc_user_score_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `imooc_user` (`id`))')
数据库告诉我们id
是imooc_user_score
表的外键,如果删除会破坏数据的完整性,因此拒绝了我们的操作。
我们改造一下外键约束,让它支持级联删除:
ALTER TABLE imooc_user_score DROP FOREIGN KEY imooc_user_score_ibfk_1;
ALTER TABLE imooc_user_score ADD CONSTRAINT imooc_user_score_ibfk_1 FOREIGN KEY(user_id) REFERENCES imooc_user(id) ON DELETE CASCADE;
我们再次删除pedro
:
DELETE FROM imooc_user WHERE id = 1;
这次删除成功,且imooc_user_score
中的关联数据也被删除了。
强外键虽然能够保证数据的完整性(要么都有,要么都没有),但是弊端很明显,删除了一些数据后,与之关联的数据也都被删除了,不利于数据的维护,也不利于更改和迁移;再者,强外键会因为关联来同步检测和更新两张表,无疑会拉低数据库整体的性能。因此目前大家普遍采用弱外键
的方式。
2.2 什么是弱外键
在 join 一节中,我们提到外键的最终落脚点是使用 Join 来连接数据,不过 SQL 连接并非只支持强外键,它其实也支持弱外键
,甚至无外键,只要连接的字段能够对应上,连接都是可行的。
那么什么是弱外键
了?答案其实很简单,强外键是数据库层面上的外键,而弱外键是逻辑层面的上的外键。如下,我们新建两表:
CREATE TABLE imooc_user
(
id int PRIMARY KEY,
username varchar(20),
age int
);
CREATE TABLE imooc_user_score
(
id int PRIMARY KEY,
user_id int NOT NULL,
score int,
);
在新建 imooc_user_score 表的 SQL 语句中,我们并未声明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(20),
age int
);
DROP TABLE IF EXISTS imooc_class;
CREATE TABLE imooc_class
(
id int PRIMARY KEY,
name varchar(50),
description varchar(100)
);
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 (1,'pedro',23),(2,'tom',19),(3,'mary',22);
INSERT INTO imooc_class(id,name,description) VALUES
(1,'SQL知多少', '一卷囊括天下SQL事'),
(2,'回首又见Java','你蓦然回首时,我依然在灯火阑珊处'),
(3,'倚Python屠虫记', '看我这把Python大刀斩尽你无数爬虫');
4.1 使用弱外键
接着,我们来模拟用户购买课程。
某一天,pedro
购买了SQL知多少
和回首又见Java
这两门课,有了中间表,我们无需改动主表,而是添加记录至中间表即可:
INSERT INTO imooc_user_class VALUES(1,1,1), (2,1,2);
第二天,mary
购买了SQL知多少
和倚Python屠虫记
两门课:
INSERT INTO imooc_user_class VALUES(3,3,1), (4,3,3);
现在,管理员需要查看数据。首先,他想知道谁都购买了课程,由于购买记录都记载在了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 | SQL知多少 |
| 2 | 回首又见Java |
| 3 | 倚Python屠虫记 |
+----------+----------------+
不错,三门课都被购买了。管理员更想知道SQL知多少
这门课被谁购买了:
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 = 1;
+---------+----------+
| user_id | username |
+---------+----------+
| 1 | pedro |
| 3 | mary |
+---------+----------+
不错,大家都买了这门课
- 还没有人评论,欢迎说说您的想法!