MySQL之外键
一:MySQL外键
- 引入(比喻)
1.1.定义一张员工表
id name age dep_name dep_desc
1 jason 18 外交部 搞外交
2 kevin 28 教学部 教书育人
3 tony 38 教学部 教书育人
4 oscar 48 安保部 保家卫国
5 jackson 58 财务部 发工资
2.上述表不合理之处
1.表内部数据混乱(可忽略)
2.反复的录入重复数据(可忽略)
3.修改数据太过繁琐 浪费磁盘空间(可忽略)
4.极大地影响了操作数据的效率
3.将上述表拆分成两张表
id name age
1 jason 18
2 kevin 28
3 tony 38
4 oscar 48
5 jackson 58
id dep_name dep_desc
1 外交部 搞外交
2 教学部 教书育人
3 安保部 保家卫国
4 财务部 发工资
- 拆分完之后解决了上述四个缺陷
4.外键
用来记录表与表之间的关系
dep_id
| id | name | age | dep_id |
|---|---|---|---|
| 1 | jason | 18 | 1 |
| 2 | jevin | 28 | 2 |
| 3 | tony | 38 | 2 |
| 4 | oscar | 48 | 3 |
| 5 | jackson | 58 | 4 |
二:如何查找表关系
1.查找表的关系
1.多对一
2.多对多
3.一对一
4.没有关系
2.多对一：(示例图)
3.查找表关系:换位思考
书籍表与出版社表
1.先站在书籍表
问:一本书能够对应多个出版社
答:不可以
2.再站在出版社表
问:一个出版社能否对应多本书
答:可以
结论:一个可以一个不可以 表关系为"多对一"
那么外键字段建在"多"的一方
- 针对具有表关系的SQL建议先写普通字段 最后再考虑外键字段
三:多对一(外键)实战
1.创建表(书籍关联表)
create table book(
id int primary key auto_increment,
title varchar(32),
price int,
pub_id int,
foreign key(pub_id) references publish(id)
);
foreign key(关联表) references publish(被关联表)
作用:
book的pud_id字段 跟publish的id字段是(外键)关联的
注意:
(pud_id里面的字段永远只能写publish里面出现过的值)
创建表(出版社被关联表)
create table publish(
id int primary key auto_increment,
name varchar(32)
);
- 必须先创建被关联表
2.添加写入数据
- 在写入数据的时候也需要先写入被关联表
被关联数据(写入数据):
insert into publish(name) values('北方出版社'),('东方出版社');
关联数据(写入数据):
insert into book(title,price,pub_id) values('linux入门',666666,1),('python入门',88888888,1);
四:外键约束
1.外键约束问题
1.在创建表的时候需要先创建被关联表(没有外键的表)
详解:
因为没有被关联表的话 无法识别到被关联表的时候就会导致报错
2.在写入数据的时候也需要先写入被关联表(没有外键的表)
详解:
在存储数据的时候，没有被关联表的外键字段，关联表也无法关联，会导致报错
3.被关联表里面的数据无法直接删除和修改关联字段的操作
详解:
如果被关联表被删除了，那么关联表的外键字段就不知道指向谁了，会导致数据混乱
2.如何删除或修改被关联表
如果要修改或者删除被关联表的字段的话，那么关联表的字段也要同样被修改或删除才合理
该方法提供处理 被关联字段的删除与修改
关键字(级联更新 级联删除):
on update cascade
on delete cascade
五:级联更新 级联删除
1.创建表必须先创建(被关联表)
关联表
create table emp(
id int primary key auto_increment,
name varchar(32),
age int,
dep_id int,
foreign key(dep_id) references dep(id)
on update cascade
on delete cascade
);
注意:
on updata cascade与on delete cascade不要加逗号因同属于一条命令
被关联表
create table dep(
id int primary key auto_increment,
name varchar(32)
);
2.存储数据(先写入被关联表)
关联表
insert into emp(name,age,dep_id) values('jason',18,1),('kevin',19,2),('jack',29,3),('tom',30,2);
被关联表
insert into dep(name) values('外交部'),('保安部'),('财务部');
六:多对多
1.以书籍表与作者表为例
1.先站在书籍表的基础之上
问:一本书能否对应多个作者
答:可以
2.在站在作者表的基础之上
问:一个作者能否对应多本书
答:可以
结论:两个都可以那么表关系就是"多对多"
外键字段建在第三张关系表中
book_id : 永远只能出现书籍表的id
author_id : 永远只能出现作者表的id
2.错误的创建方式(创建多对多)
create table book1(
id int primary key auto_increment,
title varchar(32),
author_id int,
foreign key(author_id) references author1(id)
on update cascade
on delete cascade
);
create table author1(
id int primary key auto_increment,
name varchar(32),
book_id int,
foreign key(book_id) references book1(id)
on update cascade
on delete cascade
);
3.原因:
以上为错误的方式:
两张表都是关联表，无法进行关联，所以报错
4.解决多对多无法创建问题(第三方关联表)
被关联表(书籍表)
create table book1(
id int primary key auto_increment,
title varchar(32)
);
被关联表(作者表)
create table author1(
id int primary key auto_increment,
name varchar(32)
);
第三方关联表(相当于中转站)
create table book2author(
id int primary key auto_increment,
author_id int,
foreign key(author_id) references author1(id)
on update cascade
on delete cascade,
book_id int,
foreign key(book_id) references book1(id)
on update cascade
on delete cascade
);
5.存储数据(写入表内数据)
书籍表
insert into book1(title) values('python入门'),('java入门'),('linux入门');
作者表
insert into author1(name) values('ojbk'),(jason);
第三方关联表
insert into book2author(book_id,author_id) values(1,1),(2,1);
insert into book2author(book_id,author_id) calues(2,2),(2,3)
- 总结:多对多的关系，需要建立在第三张表上进行
七:表关系之一对一和没有关系
1.以用户表与用户详情表为例
1.先站在用户表的基础之上
问:一个用户能否对应多个用户详情
答:不可以
2.在站在用户详情表基础之上
问:一个用户详情能否对应多个用户
答:不可以
结论:换位思考之后两边都不可以 那么表关系有两种
1.没有关系(用膝盖都能判断出来)
2.一对一关系
2.外键字段建在哪里?
理论上建在任何一方都可以但是推荐建在查询频率较高的表中
3.创建一对一表关系(案例)
用户表(关联表)
create table user(
id int primary key auto_increment,
name varchar(32),
detail_id int unique,
foreign key(detail_id) references user_detail(id)
on update cascade
on delete cascade
);
注意:
detail_id int unique : 一对一关系，不能出现重复外键
注意:
先创建被关联表
用户详细表(被关联表)
create table user_detail(
id int primary key auto_increment,
addr varchar(32),
phone bigint
);
4.存储数据(先写入被关联表)
存入(用户详细表)
insert into user_detail(addr,phone) values('安徽',123),('江苏',110);
存入(用户表)
insert into user(name,detail_id) values('objk',1);
5.总结
一对一关系，只需要添加上unique，不能重复外键，因为是一对一关系。
