mysql关联关系

5.2操作关联表

⚠️重点内容可直接往下翻看案例操作

5.2.1 关联关系

在开发中，需要根据实体的内容设计数据表，实体间会有各种关联关系，所以，根据实体设计的数据表之间，也存在着各种关联关系。 
MySQL中，数据表的关联关系分为三种。

1、多对1

多对一是数据表中，最常见的一种关系 
比如，员工和部门之间的关系，一个部门可以有多个员工，但是，一个员工从属于一个部门，部门表中的一行在员工表中，可以有许多匹配行，但是，员工表中的一行，在部门表中只能有一个匹配行。

建立外键，在多对一的表关系中，应该将外键建在多的一方，否则，会造成数据冗余。

2、 多对多

多对多也是数据表中的一种关系 
比如，学生与课程之间的关系，一个学生可以选择多门课程，一门课程可以供多个学生选择，学生表中的一行，在课程表中可以有许多匹配行，课程表中的一行，在学生表中也有许多匹配行。

3、 一对一

一对一关系，在实际生活中比较常见 
比如，人与身份证之间，就是一对一的关系，一个人对应一张身份证，一张身份证只能匹配一个人。那么一对一的关系如何建立外键？首先，要分清主从关系，从表需要主表的存在才有意义，身份证需要人的存在，才有意义，因此，人为主表，身份证为从表，要在身份证中建立外键。

注意，这种方法关系在数据库中并不常见，因为，这种方式存储的信息通常会放在一个表中。 
在实际开发中，一对一关联关系，可以应用于以下几个方面 
1、分割具有很多列的表 
2、由于安全原因，而隔离表的一部分 
3、保存临时的数据，并且，可以毫不费力的通过删除该表，而删除这些数据。

5.2.2 添加数据

在开发中，最常见的关联关系就是多对一关系。接下来，为表student和表grade中添加外键约束来建立两个表的关联关系。具体语句如下：

#为表student和表grade中添加外键约束来建立两个表的关联关系

alter table student add constraint FK_ID foreign key(gid) REFERENCES grade (id);

#语句执行成功后，查看外键约束是否成功添加

SHOW CREATE TABLE student;

从查询结果可以看出，student表的外键约束已经成功添加。此时表student和表grade之间是多对一的关系。因为外键列只能插入参考列存在的值，所以如果要为两个表添加数据，就需要先为主表grade添加数据。具体语句如下：

#添加数据

INSERT INTO grade(id,name)VALUES(1,'软件一班');

INSERT INTO grade(id,name)VALUES(2,'软件二班');

在上述语句中，添加的主键id为1,2，由于student表的外键与grade表的主键关联，因此在为student表添加数据时，gid的值只能是1或者2，不能使用其他值，具体语句如下：

INSERT INTO student(sid,sname,gid)VALUES(1,'王红',1);

INSERT INTO student(sid,sname,gid)VALUES(2,'李强',1);

INSERT INTO student(sid,sname,gid)VALUES(3,'赵四',2);

INSERT INTO student(sid,sname,gid)VALUES(4,'郝娟',2);

上述语句执行成功后，两个表之间的数据就具有关联性。假如要查询软件一班有哪些学生，首先需要查询软件一班的id，然后根据这个id在student表中查询该班级有哪些学生，具体语句如下：

（1）在grade表中查询出班级名称为“软件一班”的id。

SELECT id FROM grade WHERE name='软件一班';

（2）在student表中，查询gid=1的学生，即为软件一班的学生。

SELECT sname FROM student WHERE gid=1;

5.2.3 删除数据

前面小节中讲解了如何为关联表添加数据，在某些情况下还需要删除关联表中的数据。

1、第一种：修改表数据：

外键虽然能够帮你强制建立表关系，但是也会给表之间增加数据相关的约束；改也不好改，删也不好删。删除的时候可以先删除把绑定关系表的数据，然后再删除被绑定关系表的数据。现在绑定关系表是student，被绑定关系表是grade；如果需要将班级表grade中1班解散，删除语句为：

（1） 将软件一班的所有学生全部删除

delete from student where sname='王红';

delete from student where sname='李强';

上述执行成功后，可以使用SELECT语句查询。

SELECT * FROM student where gid=1;

从上述语句可以看出，student表中已经没有任何学生记录。

（2） 在grade表中，将软件一班删除

delete from grade where id=1;

查询一下

SELECT * FROM grade;

但是这样修改太麻烦，当你要修改表中的某一条数据时，还要考虑先改哪张表再改哪张表。

2、第二种修改方式：

级联更新级联删除：就是你在建外键的时候可以告诉它，之后你在动外键数据的时候我们两者是同步的，

你怎么动我怎么动，你删一条数据，我也跟着删除一条数据；你变我也跟着你变。

级联更新级联删除建表：

create table student1

(sid int primary key,

sname varchar(20),

gid int not null,

foreign key(gid) references grade(id)

on UPDATE CASCADE #级联更新

on DELETE CASCADE #级联删除

);

INSERT INTO student1(sid,sname,gid)VALUES(1,'王红',1);

INSERT INTO student1(sid,sname,gid)VALUES(2,'李强',1);

INSERT INTO student1(sid,sname,gid)VALUES(3,'赵四',2);

INSERT INTO student1(sid,sname,gid)VALUES(4,'郝娟',2);

（1）在grade表中，将软件一班删除（级联删除外键表中相应数据）

delete from grade where id=1;

可以直接删除

Select * from student1;

（2）在grade表中，将软件二班编码修改为5

Update grade

set id=5

Where id=2;

Select * from student1;