MySQL_表关系、外键、修改表

概要

 

1 外键

2 几种表与表之间的关系

3 表关系操作总结

4 表SQL语句补充

 

详细

 

 

1 外键 foreign key

 

定义: 链接 表与表之间关系的字段.

作用: 让当前这个表的字段跟另外一张表的字段建立关系.

不理解请看: 2-1 图,其中 "部门编号"就是外键字段,在其后面的cmd中有实际创建演示,

 

2 几种表关系(4种)

 

2-1 一对多

 

例:员工表和部门表

• 为什么是一对多?
• 站在员工表角度: 一个员工只能对应一个部门;
• 站在部门角度: 一个部门可以对应多个员工;

 

 

说明:

• 外键创在哪: 创在"多的"哪个表
• 先创建无外键的表
• 再创建有外键的表(先写基本表语句,最后写关于外键的语句)

 

2-1.1

cmd演示

 

#1 创建无外键的部门表

 

 

2-1.2

若要对被关联的表做数据的"修改、删除"操作行吗?

 

如下: 不能.即本例中无法对部门表做修改或者删除操作

因为,你要删除或修改某部门信息,那么它所对应的员工表中的该部门的员工怎么办?

 

若要做到关联操作:

那么需把原来的外键语句这样写: 

 

看清楚,这是一句,为了看的清除,所以分三行写;

foreign key(author_id) references author(id)

on update cascade  # 级联更新

on delete cascade  # 级联删除

 

 

#2 创建员工表并通过外键与部门表绑定

 

 

2-2 多对多

 

例: 书籍表和作者表

• 为什么是多对多? 
• 站在书的角度,一本书可以有多个作者;
• 站在某个作者的角度,他可以有多本书;

 

cmd演示

• 注意: 需要创建第三个表来建两者之间的关系

#1 创建书籍表

#2 创建作者表

#3  创建关联两者的关系表

# 4 给作者表添加数据

 

 

2-3 一对一

 

例:作者表与作者详情表

• 为什么是一对一?
• 站在作者表角度考虑: 一个作者只能对应一个关于该作者的详细信息
• 站在作者详细信息角度考虑: 一个作者的详细信息也只能对应一个作者

 

 

 

cmd演示

 

外键建在哪?

• 外键字段建在任何一方都可以 但是推荐建在查询频率较高的表中

 

#1 创建"其他信息表"

 

#2 创建"主要信息表"

 

#3 创建"其他信息表"内容

其中电话号码因设置为int不够而报错,只能把位数改小,应设置为bigint

#4  创建"主要信息表"内容

注意: 加约束条件->detail_info_id中加入unique

其中,外键id=1有了后再建一个id=1的内容,报错,说明 detail_info_id中加入unique生效,且必须加,因为本次表关系要求是一对一,不加没法约束一对一的关系.

 

2-4 还有一种情况就是: 表之间没有关系

 

 

3 表关系操作总结

 

3-1 针对表操作

 

• 找表->判断关系->建外键->建sql表
• 先创建被关联表(没有外键字段的表)
• 在插入新数据的时候 应该先确保被关联表中有数据
• 在插入新数据的时候 外键字段只能填写被关联表中已经存在的数据

 

3-2  什么时候不用外键创建表关系

• 由于外键有实质性的诸多约束 当表特别多的时候外键的增多反而会增加耦合程度,
• 这时怎么办?
• 通过SQL语句层面 建立逻辑意义上的表关系
• 如: 操作员工表的sql执行完毕之后 立刻跟着执行操作部门的sql语句

 

4 修改表

 

4-1 修改表名

alter table 原表名 rename 新表名;

 

4-2 增加字段

#1 尾部追加

alter table 表名 add 字段名 数据类型 约束条件 ;

 

#2 添加到开头

alter table 表名 add 字段名 数据类型 约束条件 first;

 

#3 添加到某字段后

alter table 表名 add 字段名 数据类型 约束条件 after 字段;

 

4-3 删除字段

alter table 表名 drop 字段名;

 

4-4 修改字段

 

#1 修改字段类型

alter table 表名 modify 字段名 新字段类型 原或新的约束条件;

#2 修改字段名

alter table 表名 change 旧字段名 新字段名 原或新的字段类型 原或新的约束条件;