Foreign key : 外键
一对多 关系介绍
定义了一张部门员工表
| 1 | Jason | male | 教学部 | 教书育人 |
| 2 | Egon | male | 外交部 | 漂泊游荡 |
| 3 | Tank | male | 教学部 | 教书育人 |
| 4 | Kevin | male | 教学部 | 教书育人 |
| 5 | Owen | Female | 技术部 | 技术支持 |
把所有数据放到一张表中的弊端：
1、组织结构不清晰
2、数据冗余，浪费硬盘空间
3、扩展性极差
# 上述弊端产生的原因类似于把代码写在同一个py文件中
# 我们是用 解耦合思想 来解决python代码的冗余及扩展性问题的
# 分析表数据之间的关系：
多个员工对应一个部门
一个部门对应多个员工
# 那么就可以建立一个部门列表，将 [员工所在部门的字段] 指向 [部门列表]中的 [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;
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
