数据库（三）表与表建立联系-外键

1.理解表与表之间建⽴关系的必要性

⽐如我们建⽴了⼀张个⼈信息表,把所有⼈的信息都存在这张表中

这张表中有⽼师和他们所属的部门,并且不同的部门⼯作也是不同的所有数据存放在⼀张表中的弊端　　1.组织结构不清晰　　2.浪费存储空间　　3.扩展性差

针对这个问题我们可以将他们分成两张表,⼀张专门记录个⼈信息,⼀张专门记录部门和部门⼯作建⽴表的时候我们需要进⾏分析

　　1.站在个⼈信息表的⾓度:⼀个⽼师能否属于多个部门,不能　　2.站在部门表的⾓度:⼀个部门能否有多个⽼师,可以

那这两张表的关系就是⽼师表多对⼀部门表,也可以这么说,部门表⼀对多⽼师表

在表中是如何表⽰这种关系,需要找出⼀个两者中独⼀⽆⼆的东西并且把他们连接起来,我们在部门表中创建⼀个部门的id,在⽼师表中添加这个属性并指向部门id如下表所⽰

这样我们就可以把两者联系起来了,但是⽼师的dep_id我们还是可以随意的修改,应该有⼀个,他们只能和部门表的id相同才对

2.外键(foreign key)

什么是外键

　　如果公共关键字在⼀个关系中是主关键字，那么这个公共关键字被称为另⼀个关系的外键。由此可见，外键表⽰了两个关系之间的相关联系。以另⼀个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。外键⼜称作外关键字。外键的作⽤

　　保持数据⼀致性，完整性，主要⽬的是控制存储在外键表中的数据。 使两张表形成关联，外键只能引⽤外表中的列的值或使⽤空值。外键拥有能使两张表阻⽌执⾏和级联执⾏的特点

所以要把两张有关联的表联系起来需要⽤到外键(foreign key)

阻⽌执⾏

从表插⼊新⾏，其外键值不是主表的主键值便阻⽌插⼊； 从表修改外键值，新值不是主表的主键值便阻⽌修改；

主表删除⾏，其主键值在从表⾥存在便阻⽌删除(要想删除，必须先删除从表 的相关⾏)； 主表修改主键值，旧值在从表⾥存在便阻⽌修改(要想修改，必须先删除从表的相关⾏)。级联执⾏

主表删除⾏，连带从表的相关⾏⼀起删除；

主表修改主键值，连带从表相关⾏的外键值⼀起修改。两种⽅法提供给⽤户选择。⽆论选取哪种⽅法，从表⾥都不会有多余⾏。从另⼀个⾓度理解，⽤拒绝同⼀事物在从表中的标志与主表不⼀致来实现与主表中的标志⼀致。

3.表与表关系之⼀对多

还是使⽤上⾯⽼师表和部门表的例⼦,两者是⼀对多的关系

1.在创建表的时候,我们需要先建被关联的表dep,才能建关联表teacher

# 先创建部门表depcreate table dep(

id int primary key auto)increment, dep_name char(16), duty varchar(16));

# 再创建⽼师表teachercreate table teacher(

id int primary key auto_increment,

gender enum('male','female') not null default 'male', dep_id int,

foreign key(dep_id) references dep(id));

2.在插⼊记录时,必须先插被关联的表dep,才能插关联表teacher

# 先插⼊被关联表的记录

insert dep(dep_name,duty) values('教学部','教书育⼈'),('德育部','培养品德'),('体育部','强⾝健体');

# 再插⼊关联表的记录

insert teacher(name,dep_id) values('sxc',1),('zzp',2),('zzj',3),('lzx',1),('yzy',1);

在我们创建完成之后,发现修改或者删除teacher表中的dep_id或者dep表中的id都⽆法成功,删除dep表中的数据也⽆法成功

这是因为dep表中的数据还跟teacher表中的数据相关联,我们想要操作数据,⽐如:需要先删除教学部对应所有的员⼯,再删除教学部

这样操作数据变得⾮常的复杂,能否有⼀种简单的⽅式,不需要考虑关联表的情况,⽐如我删除⼀个部门,那么这个部门的所有员⼯都跟着被删除3.修改和删除都需要考虑关联关系>>>同步更新和同步删除

# 把原表删除后新建两张表# 还是需要先建被关联表depcreate table dep(

id int primary key auto_increment,dep_name char(10),duty char(10));

# 再创建关联表teachercreate table teacher(

id int primary key auto_increment,name char(10),gender enum('male','female') not null default 'male',dep_id int,foreign key(dep_id) references dep(id)on update cascade on delete cascade);

# 先插⼊被关联表的记录

insert dep(dep_name,duty) values('教学部','教书育⼈'),('德育部','培养品德'),('体育部','强⾝健体');

# 再插⼊关联表的记录

insert teacher(name,dep_id) values('sxc',1),('zzp',2),('zzj',3),('lzx',1),('yzy',1);

这样我们删除⼀个部门之后这个部门所有的⼈都会跟着被删除更新部门后,对应⽼师表中的部门字段也会跟着改变

4.表与表关系之多对多

我们建⽴⼀个图书表和作者表,思考两者的关系

站在两张表的⾓度进⾏分析

　　1.站在图书表:⼀本书可不可以有多个作者,可以　　2.站在作者表:⼀个作者可不可以写多本书,可以

那他们两者对于对⽅都是多对⼀的关系,那我们称这种关系为多对多

在创建⼀对多关联表时,必须得先创建被关联表(没有外键的),才能创建关联表(有外键的),在多对多关联表中,两者就都需要使⽤外键关联,这样先创建谁都不合适我们必须创建第三张表,并且该表需要拥有两个字段分别代表这两个表中的id

# 因为需要创建第三张表关联两张表,所以两张表没有创建的先后顺序create table book(

id int primary key auto_increment, name char(16), price int);

create table author(

id int primary key auto_increment, name char(10));

# 为两张表插⼊数据

insert book(name,price) values('book1',100),('book2',466),('book3',55),('book4',211);

insert author(name) values('sxc'),('zzj'),('zzp');

# 插⼊第三张表,将另两张表的id作为外键create table author2book(

id int primary key auto_increment, author_id int, book_id int,

foreign key(author_id) references author(id) on update cascade on delete cascade,

foreign key(book_id) references book(id) on update cascade on delete cascade);

# 为第三张表插⼊对应的记录

insert author2book(author_id,book_id) values(1,1),(1,4),(2,1),(2,3),(3,2),(3,3);

注意:两张关系表虽然有关联,但不是直接关联的,⽽是和第三张表关联,所以在删除表中数据的时候,另外⼀张表是没有影响的,只有第三张我们⾃⼰定义的表是级联更新,级联删除的

5.表与表关系之⼀对⼀

我们建⽴⼀个学⽣表和信息表,思考两者的关系

还是站在两张表的⾓度进⾏分析

　　1.⼀个学⽣可不可以有多个详细信息,不可以　　2.⼀个详细信息能否属于多个学⽣,不可以那么这两者互相都不是多对⼀的关系,这有两种情况　　1.这两者没有关联　　2.两者是⼀对⼀的关系

在创建⼀对⼀的关联表时,添加的外键可以放在两者任意⼀张表中,但是我们⼀般都放在⽐较常⽤的那⼀⽅

# 先创建没有外键的表create table stu_info(

id int primary key auto_increment, stu_id int unique, phone int);

# 再创建另⼀个表create table student(

id int primary key auto_increment, name varchar(16), age int,

stu_info_id int unique,

foreign key(stu_info_id) references stu_info(id) on update cascade on delete cascade);

注意:创建外键时,⼀定要表明该键是唯⼀的,即unique,这样才能表⽰⼀对⼀的关系

# 先在⽆外键的表中插⼊记录

insert stu_info(stu_id,phone) values(101,110),(102,120),(103,130);

# 然后在有外键的表中插⼊记录

insert student(name,age,stu_info_id) values('jason',18,1),('egon',26,2),('tank',24,3);

5.如何找出两表之间的关系

分析步骤：

#1、先站在左表的⾓度去找

是否左表的多条记录可以对应右表的⼀条记录，如果是，则证明左表的⼀个字段foreign key 右表⼀个字段（通常是id）#2、再站在右表的⾓度去找

是否右表的多条记录可以对应左表的⼀条记录，如果是，则证明右表的⼀个字段foreign key 左表⼀个字段（通常是id）#3、总结：#多对⼀：

如果只有步骤1成⽴，则是左表多对⼀右表如果只有步骤2成⽴，则是右表多对⼀左表

#多对多

如果步骤1和2同时成⽴，则证明这两张表时⼀个双向的多对⼀，即多对多,需要定义⼀个这两张表的关系表来专门存放⼆者的关系

#⼀对⼀:

如果1和2都不成⽴，⽽是左表的⼀条记录唯⼀对应右表的⼀条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可

6.修改表

# mysql对⼤⼩写不敏感语法：

1. 修改表名

ALTER TABLE 表名

RENAME 新表名;2. 增加字段

ALTER TABLE 表名

ADD 字段名 数据类型 [完整性约束条件…], ADD 字段名 数据类型 [完整性约束条件…]; ALTER TABLE 表名

ADD 字段名 数据类型 [完整性约束条件…] FIRST; ALTER TABLE 表名

ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名; 3. 删除字段

ALTER TABLE 表名

DROP 字段名;

4. 修改字段 # modify只能改字段数据类型完整约束，不能改字段名，但是change可以！ ALTER TABLE 表名

MODIFY 字段名 数据类型 [完整性约束条件…]; ALTER TABLE 表名

CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…]; ALTER TABLE 表名

CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

7.复制表

# 查询语句执⾏的结果也是⼀张表，可以看成虚拟表# 复制表结构＋记录 （key不会复制: 主键、外键和索引）create table new_service select * from service;

# 只复制表结构

select * from service where 1=2; //条件为假，查不到任何记录create table new1_service select * from service where 1=2; create table t4 like employees;

36