数据表的关联关系 |
您所在的位置：网站首页 › 数据表关联模型是什么 › 数据表的关联关系 |
数据表的关联关系
|
1.关联关系介绍
MySQL是⼀个关系型数据库，不仅可以存储数据，还可以维护数据与数据之间的关系——通过在数据表中添加字段建⽴外键约束
班级 — 学⽣ （⼀对多） ⼀个班级包含多个学⽣ 学⽣ — 班级 （多对⼀） 多个学⽣可以属于同⼀个班级 图书 — 分类 商品 ---- 商品类别 ⽅案： 在多的⼀端添加外键 ，与⼀的⼀端主键进⾏关联 学⽣ — 课程 ⼀个学⽣可以选择多⻔课、⼀⻔课程也可以由多个学⽣选择 会员 — 社团 ⼀个会员可以参加多个社团、⼀个社团也可以招纳多个会员 ⽅法：额外创建⼀张关系表来维护多对多关联——在关系表中定义两个外键，分别与两个数 据表的主键进⾏关联 外键约束——将⼀个列添加外键约束与另⼀张表的主键(唯⼀列)进⾏关联之后，这个外键 约束的列添加的数据必须要在关联的主键字段中存在 案例学生表与班级表 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 | +----------+----------+------------+---------+------+ |
数据与数据之间的 关联关系 分为四种： ⼀对⼀关联 ⼀对多关联 多对⼀关联 多对多关联
【本文地址】
今日新闻 |
推荐新闻 |