1.关联关系介绍
MySQL是⼀个关系型数据库，不仅可以存储数据，还可以维护数据与数据之间的关系——通过在数据表中添加字段建⽴外键约束 数据与数据之间的 关联关系 分为四种： ⼀对⼀关联 ⼀对多关联 多对⼀关联 多对多关联
2. 一对一关联
一对多与多对一
班级 — 学⽣ （⼀对多） ⼀个班级包含多个学⽣ 学⽣ — 班级 （多对⼀） 多个学⽣可以属于同⼀个班级 图书 — 分类 商品 ---- 商品类别
⽅案： 在多的⼀端添加外键 ，与⼀的⼀端主键进⾏关联
多对多关联
学⽣ — 课程 ⼀个学⽣可以选择多⻔课、⼀⻔课程也可以由多个学⽣选择 会员 — 社团 ⼀个会员可以参加多个社团、⼀个社团也可以招纳多个会员
⽅法：额外创建⼀张关系表来维护多对多关联——在关系表中定义两个外键，分别与两个数 据表的主键进⾏关联
外键约束
外键约束——将⼀个列添加外键约束与另⼀张表的主键(唯⼀列)进⾏关联之后，这个外键 约束的列添加的数据必须要在关联的主键字段中存在
案例
学生表与班级表 1.先创建班级表
create table classes(
class_id int primary key auto_increment,
class_name varchar(40) not null unique,
class_remark varchar(200)
);
创建学⽣表（在学⽣表中添加外键与班级表的主键进⾏关联）
# 【⽅式⼀】在创建表的时候，定义cid字段，并添加外键约束
# 由于cid 列 要与classes表的class_id进⾏关联，因此cid字段类型和⻓度要与
class_id⼀致
create table students(
stu_num char(8) primary key,
stu_name varchar(20) not null,
stu_gender char(2) not null,
stu_age int not null,
cid int,
constraint FK_STUDENTS_CLASSES foreign key(cid) references
classes(class_id)
);
#【⽅式⼆】先创建表，再添加外键约束
create table students(
stu_num char(8) primary key,
stu_name varchar(20) not null,
stu_gender char(2) not null,
stu_age int not null,
cid int
);
# 在创建表之后，为cid添加外键约束
alter table students add constraint FK_STUDENTS_CLASSES foreign
key(cid) references classes(class_id);
# 删除外键约束
alter table students drop foreign key FK_STUDENTS_CLASSES;
3.向班级表添加班级信息
insert into classes(class_name,class_remark)
values('Java2104','...');
insert into classes(class_name,class_remark)
values('Java2105','...');
insert into classes(class_name,class_remark)
values('Java2106','...');
insert into classes(class_name,class_remark)
values('Python2106','...');
select * from classes;
+----------+------------+--------------+
| class_id | class_name | class_remark |
+----------+------------+--------------+
| 1 | Java2104 | ... |
| 2 | Java2105 | ... |
| 3 | Java2106 | ... |
| 4 | Python2106 | ... |
+----------+------------+--------------+
向学⽣表中添加学⽣信息
insert into students(stu_num,stu_name,stu_gender,stu_age,cid)
values('20210102','李斯','⼥',20, 4 );
# 添加学⽣时，设置给cid外键列的值必须在其关联的主表classes的classs_id列存在
insert into students(stu_num,stu_name,stu_gender,stu_age,cid)
values('20210103','王五','男',20, 6 );
6.外键约束-级联
当学⽣表中存在学⽣信息关联班级表的某条记录时，就不能对班级表的这条记录进⾏修 改ID和删除操作，如下：
mysql> select * from classes;
+----------+------------+--------------+
| class_id | class_name | class_remark |
+----------+------------+--------------+
| 1 | Java2104 | ... | # 班级表中class_id=1的班级信息
被学⽣表中的记录关联了
| 2 | Java2105 | ... | # 我们就不能修改Java2104的
class_id,并且不能删除
| 3 | Java2106 | ... |
| 4 | Python2106 | ... |
+----------+------------+--------------+
mysql> select * from students;
+----------+----------+------------+---------+------+
| stu_num | stu_name | stu_gender | stu_age | cid |
+----------+----------+------------+---------+------+
| 20210101 | 张三 | 男 | 18 | 1 |
| 20210102 | 李四 | 男 | 18 | 1 |
| 20210103 | 王五 | 男 | 18 | 1 |
| 20210104 | 赵柳 | ⼥ | 18 | 2 |
+----------+----------+------------+---------+------+
mysql> update classes set class_id=5 where class_name='Java2104';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key
constraint fails (`db_test2`.`students`, CONSTRAINT
`FK_STUDENTS_CLASSES` FOREIGN KEY (`cid`) REFERENCES `classes`
(`class_id`))
mysql> delete from classes where class_id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key
constraint fails (`db_test2`.`students`, CONSTRAINT
`FK_STUDENTS_CLASSES` FOREIGN KEY (`cid`) REFERENCES `classes`
(`class_id`))
如果⼀定要修改Java2104 的班级ID，该如何实现呢 ？ 将引⽤Java2104班级id的学⽣记录中的cid修改为 NULL 在修改班级信息表中Java2104记录的 class_id 将学⽣表中cid设置为NULL的记录的cid重新修改为 Java2104这个班级的新的id
1update students set cid=NULL where cid=1; # 结果如下：
+----------+----------+------------+---------+------+
| stu_num | stu_name | stu_gender | stu_age | cid |
+----------+----------+------------+---------+------+
| 20210101 | 张三 | 男 | 18 | NULL |
| 20210102 | 李四 | 男 | 18 | NULL |
| 20210103 | 王五 | 男 | 18 | NULL |
| 20210104 | 赵柳 | ⼥ | 18 | 2 |
+----------+----------+------------+---------+------+
2update classes set class_id=5 where class_name='Java2104'; # 结果如下
+----------+------------+--------------+
| class_id | class_name | class_remark |
+----------+------------+--------------+
| 2 | Java2105 | ... |
| 3 | Java2106 | ... |
| 4 | Python2106 | ... |
| 5 | Java2104 | ... |
+----------+------------+--------------+
3update students set cid=5 where cid IS NULL; # 结果如下
+----------+----------+------------+---------+------+
| stu_num | stu_name | stu_gender | stu_age | cid |
+----------+----------+------------+---------+------+
| 20210101 | 张三 | 男 | 18 | 5 |
| 20210102 | 李四 | 男 | 18 | 5 |
| 20210103 | 王五 | 男 | 18 | 5 |
| 20210104 | 赵柳 | ⼥ | 18 | 2 |
我们可以使⽤级联操作来实现：
在添加外键时，设置级联修改 和 级联删除
# 删除原有的外键
alter table students drop foreign key FK_STUDENTS_CLASSES;
# 重新添加外键，并设置级联修改和级联删除
alter table students add constraint FK_STUDENTS_CLASSES foreign
key(cid) references classes(class_id) ON UPDATE CASCADE ON DELETE
CASCADE;
测试级联修改：
# 班级信息
+----------+------------+--------------+
| class_id | class_name | class_remark |
+----------+------------+--------------+
| 2 | Java2105 | ... |
| 3 | Java2106 | ... |
| 4 | Python2106 | ... |
| 5 | Java2104 | ... |
+----------+------------+--------------+
# 学⽣信息
+----------+----------+------------+---------+------+
| stu_num | stu_name | stu_gender | stu_age | cid |
+----------+----------+------------+---------+------+
| 20210101 | 张三 | 男 | 18 | 5 |
| 20210102 | 李四 | 男 | 18 | 5 |
| 20210103 | 王五 | 男 | 18 | 5 |
| 20210104 | 赵柳 | ⼥ | 18 | 2 |
+----------+----------+------------+---------+------+
# 直接修改Java2104的class_id,关联Java2104这个班级的学⽣记录的cid也会同步修
改
update classes set class_id=1 where class_name='Java2104';
# 班级信息
+----------+------------+--------------+
| class_id | class_name | class_remark
+----------+------------+--------------+
| 2 | Java2105 | ... |
| 3 | Java2106 | ... |
| 4 | Python2106 | ... |
| 1 | Java2104 | ... |
+----------+------------+--------------+
# 学⽣信息
+----------+----------+------------+---------+------+
| stu_num | stu_name | stu_gender | stu_age | cid |
+----------+----------+------------+---------+------+
| 20210101 | 张三 | 男 | 18 | 1 |
| 20210102 | 李四 | 男 | 18 | 1 |
| 20210103 | 王五 | 男 | 18 | 1 |
| 20210104 | 赵柳 | ⼥ | 18 | 2 |
+----------+----------+------------+---------+------+
3.测试级联删除
# 删除class_id=1的班级信息，学⽣表引⽤此班级信息的记录也会被同步删除
delete from classes where class_id=1;
+----------+------------+--------------+
| class_id | class_name | class_remark |
+----------+------------+--------------+
| 2 | Java2105 | ... |
| 3 | Java2106 | ... |
| 4 | Python2106 | ... |
+----------+------------+--------------+
+----------+----------+------------+---------+------+
| stu_num | stu_name | stu_gender | stu_age | cid |
+----------+----------+------------+---------+------+
| 20210104 | 赵柳 | ⼥ | 18 | 2 |
+----------+----------+------------+---------+------+