数据库(三) 表与表建立联系-外键

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

比如我们建立了一张个人信息表,把所有人的信息都存在这张表中

这张表中有老师和他们所属的部门,并且不同的部门工作也是不同的

所有数据存放在一张表中的弊端

　　1.组织结构不清晰

　　2.浪费存储空间

　　3.扩展性差

针对这个问题我们可以将他们分成两张表,一张专门记录个人信息,一张专门记录部门和部门工作

建立表的时候我们需要进行分析

　　1.站在个人信息表的角度:一个老师能否属于多个部门,不能!!!

　　2.站在部门表的角度:一个部门能否有多个老师,可以!!!

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

在表中是如何表示这种关系,需要找出一个两者中独一无二的东西并且把他们连接起来,我们在部门表中创建一个部门的id,在老师表中添加这个属性并指向部门id

如下表所示

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

2.外键(foreign key)

什么是外键

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

外键的作用

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

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

阻止执行
    从表插入新行，其外键值不是主表的主键值便阻止插入；
    从表修改外键值，新值不是主表的主键值便阻止修改；
    主表删除行，其主键值在从表里存在便阻止删除(要想删除，必须先删除从表    的相关行)；
    主表修改主键值，旧值在从表里存在便阻止修改(要想修改，必须先删除从表的相关行)。
级联执行
    主表删除行，连带从表的相关行一起删除；
    主表修改主键值，连带从表相关行的外键值一起修改。两种方法提供给用户选择。无论选取哪种方法，从表里都不会有多余行。从另一个角度理解，用拒绝同一事物在从表中的标志与主表不一致来实现与主表中的标志一致。

3.表与表关系之一对多

还是使用上面老师表和部门表的例子,两者是一对多的关系

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

# 先创建部门表dep
create table dep(
    id int primary key auto)increment,
    dep_name char(16),
    duty varchar(16)
);
# 再创建老师表teacher
create 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.修改和删除都需要考虑关联关系>>>同步更新和同步删除

# 把原表删除后新建两张表
# 还是需要先建被关联表dep
create table dep(
id int primary key auto_increment,
dep_name char(10),
duty char(10)
);

# 再创建关联表teacher
create 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