Foreign key : 外键
一对多 关系介绍
定义了一张部门员工表
id |
name |
gender |
Dep_name |
Dep_desc |
1 |
Jason |
male |
教学部 |
教书育人 |
2 |
Egon |
male |
外交部 |
漂泊游荡 |
3 |
Tank |
male |
教学部 |
教书育人 |
4 |
Kevin |
male |
教学部 |
教书育人 |
5 |
Owen |
Female |
技术部 |
技术支持 |
把所有数据放到一张表中的弊端:
1、组织结构不清晰
2、数据冗余,浪费硬盘空间
3、扩展性极差
# 上述弊端产生的原因类似于把代码写在同一个py文件中
# 我们是用 解耦合思想 来解决python代码的冗余及扩展性问题的
# 分析表数据之间的关系:
多个员工对应一个部门
一个部门对应多个员工
# 那么就可以建立一个部门列表,将 [员工所在部门的字段] 指向 [部门列表]中的 [id字段]
可以将上表数据的对应关系拆分为:
id |
Dep_name |
Dep_desc |
1 |
教学部 |
教书育人 |
2 |
外交部 |
漂泊游荡 |
3 |
技术部 |
技术支持 |
id |
name |
gender |
Dep_id |
1 |
Jason |
male |
1 |
2 |
Egon |
male |
2 |
3 |
Tank |
male |
1 |
4 |
Kevin |
male |
1 |
5 |
Owen |
Female |
3 |
现在再来看员工与部门表,员工表里的Dep_id我可以随意更改(不需要先改部门,再改部门职能),但是应该有一个强制限制,限制Dep_id字段下的数据必须是部门列表中存在的id才合理
# 这样就引出了mysql中的 Foreign key 这个约束条件
外键的一对多关系
Foreogn key 语法
# 代码示例:
# 1、在创建表时,先建被关联的表dep,才能建关联表emp
create table dep(
id int primary key auto_increment,
dep_name char(10),
dep_comment char(60)
);
# 2、建立关联
create table emp (
id int primary key auto_increment,
name char(16),
gender enum('male','female') not null default 'male',
dep_id int,
foreign key(dep_id) references dep(id)
# foreign key(自身表中的字段) references 要绑定数据的表(表中字段)
);
# 绑定的表中的那个字段必须是其主键
# 3、在插入记录时,必须先插入被关联的表dep,才能插关联的表dep
insert into dep(dep_name,dep_comment) values
(教学部,教书育人),
(外交部,漂泊游荡),
(技术部,技术支持);
insert into emp(name,gender,dep_id) values
('Jason','male',1),
('Egon','male',2),
('Tank','male',1),
('Kevin','male',1),
('Owen','Female',3);
上方的代码中出现了其他的问题:
# 当我想修改emp里的dep_id或dep里面的id时返现都无法成功
# 当我想删除dep表的教学部的时候,也无法删除
# low版解决方法:
先删除教学部对应的所有的员工,再删除教学部
# 高端解决方法:
# 给外键字段新增功能:同步更新同步删除(级联删除级联更新)
受限于外键约束,导致操作数据变得非常复杂,能否有一张简单的方式,让我不需要考虑在操作目标表的时候还需要考虑关联表的情况,比如我删除部门,那么这个部门对应的员工就应该跟着立即清空
其实外键中自带 同步更新 | 同步删除 的功能
既 更新与删除主表 都会在子表中同步的 更新|删除
# 写法:代码示例
create table emp(
id int primary key auto_increment,
name char(16),
gender enum('male','female') not null default 'male',
dep_id int,
foreign key(dep_id) references dep(id)
# 写在foreign key语句之后
on update cascade # 同步更新
on delete cascade # 同步删除
);
# 这样子建立出来的主表:
删除部门后,对应的部门里的员工,在员工表里的数据也会被删除
更新部门后,对应员工表中标识部门的字段也会同步更新
外键的多对多关系
# 图书表 与 作者表 之间的关系
仍然站在两张表的角度
1、站在图书表:
一本书可不可以有多个作者?是可以的,那就是书 一对多 作者
2、站在作者表:
一个作者可不可以写多本书?是可以的,那就是作者 一对多 书
# 双方都能一条数据对一应对方的多条数据,这种关系就称为 多对多
建表时遇到了问题:
先创建谁都不合适,谁都是谁的主表,谁又都是谁的子表
这该如何解决?
可以建立第三张表,该表中有图书表中书的id 的字段,还有一个作者表作者id 的字段
代码示例:
# 建立作者表
create table author(
id int primary key auto_increment,
name char(16)
);
# 建立书籍表
create table book(
id int primary key auto_increment,
name char(16),
price int
);
# 建立连接两表的 第三个表
create table author2book( # 这种第三方表一般命名为 表1名+2+表2名
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 into author(name) values
('egon'),
('alex'),
('wxx');
insert into book(name,price) values
('python全栈',200),
('葵花宝典',800),
('九阴真经',500),
('九阳神功',100);
# 再创建 第三方表
insert into author2book(author_id,book_id) values
(1,3),
(1,4),
(2,2),
(2,4),
(3,1),
(3,2),
(3,3),
(3,4);
# 结果展示---------------
mysql> select * from author;
+----+------+
| id | name |
+----+------+
| 1 | egon |
| 2 | alex |
| 3 | wxx |
+----+------+
3 rows in set (0.00 sec)
mysql> select * from book;
+----+--------------+-------+
| id | name | price |
+----+--------------+-------+
| 1 | python全栈 | 200 |
| 2 | 葵花宝典 | 800 |
| 3 | 九阴真经 | 500 |
| 4 | 九阳神功 | 100 |
+----+--------------+-------+
4 rows in set (0.00 sec)
mysql> select * from author2book;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
| 1 | 1 | 3 |
| 2 | 1 | 4 |
| 3 | 2 | 2 |
| 4 | 2 | 4 |
| 5 | 3 | 1 |
| 6 | 3 | 2 |
| 7 | 3 | 3 |
| 8 | 3 | 4 |
+----+-----------+---------+
8 rows in set (0.00 sec)
外键的一对一关系
# 客户表和学生表(老男孩的客户与学生之间,报名之前都是客户,只有报了名的才能是学生)
# 左表的一条记录唯一对应右表的一条记录,反之也一样
create table customer(
id int primary key auto_increment,
name char(16) not null,
python char(16) not null,
);
create table student(
id int primary key auto_increment,
class_name char(16) not null,
customer_id int unique, # 与外键关联的字段一定要保证是unique的
foreign key(customer_id) references customer(id)
# 与外键关联的字段一定要保证是unique的
on update cascade
on delete cascade
);
总结:
三种外键关系都是用foreign key,区别在于如何使用以及其他条件限制即可做出三种关系
修改表
# 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 旧字段名 新字段名 新数据类型 [完整性约束条件…];
复制表
# 查询语句执行的结果也是一张表,可以看成虚拟表
# 复制表结构+记录 (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;