MySQL表关系--外键

时间:2021-12-22 06:58:55

一、外键前戏

如果我们把所有的信息都记录在一张表中会带来的问题:

  1.表的结构不清晰

  2.浪费磁盘空间

  3.表的扩展性极差

所以我们要把这种表拆成几张不同的表,分析表与表之间的关系。

确定表与表之间的关系,一定要换位思考(必须两方都考虑周全之后才能得出结论)

学生与班级表:

1.站在学生的角度看班级:能否多个学生在一个班级 (一个班级能否有多个学生)    可以!!!

2.站在班级的角度看学生:能否有多个班级对应一个学生 (一个学生能否在多个班级)     不可以!!!

结论:学生表和班级表之间仅仅只是单向的多对一,那么它们的关系就是“一对多”(无论是多对一还是一对多,都叫‘一对多’)

所以学生表多对一班级表,在学生表中创建一个字段(class_id)指向班级表的id字段。

如何让两种表有代码层面上真正的关联,就必须使用外键 (foreign key)

什么是外键?  让表与表有硬性层面上的关系。

二、一对多

foreign key

外键约束

  1.在创建表的时候,必须先创建被关联表

  2.插入数据的时候,也必须先插入被关联表的数据

有员工表和部门表,之间是一对多的关系,通常将关系字段称之为外键字段,外键建在多的一方。(这里外键建在员工表中)

建表:

先建部门表(被关联表)

#部门表
create table dep(
id int primary key auto_increment,
dep_name varchar(20),
dep_sec varchar(20)
);

MySQL表关系--外键

创建员工表    除了基本的员工字段,还有一个外键和部门表的id字段绑定关系

#员工表
create table emp(
id int primary key auto_increment,
emp_name varchar(10),
emp_gender enum('male','female','others') default 'male',
dep_id int,
foreign key(dep_id) references dep(id) #dep_id是外键字段并且和dep表id字段有关系
);

MySQL表关系--外键

插入数据:

部门表先插数据

insert into dep(dep_name,dep_desc) values('技术部','提供技术服务'),
('后勤部','后勤保障'),
('财务部','发工资');

MySQL表关系--外键

员工表插数据    这里需要注意的点是你插入的dep_id一定要是部门表中存在的,否则就会报错

insert into emp(emp_name,dep_id) values('jason',1),
('egon',2),
('tank',3);

MySQL表关系--外键

修改表数据:

外键虽然能够帮你强制建立关系,但是也会给表之间增加数据相关的约束。

1.在删除数据的时候,要先删除员工表的数据,才能删除部门表的数据。

因为员工表中有外键关联字段部门表,所以直接删部门表的数据会报错

2.可以使用级联更新和级联删除

在外键加上 on update cascade  on delete cascade

#员工表
create table emp(
id int primary key auto_increment,
emp_name varchar(10),
emp_gender enum('male','female','others') default 'male',
dep_id int,
foreign key(dep_id) references dep(id) #dep_id是外键字段并且和dep表id字段有关系
on update cascade
on delete cascade
);

update dep set id=200 where id = 3;

MySQL表关系--外键

delete from dep where id = 2;

MySQL表关系--外键

三、多对多

图书与作者表   换位思考

1.先站在图书表:多本书能否是有一个作者(一个作者能否写多本图书 )    可以!!!

2.站在作者表:多个作者能否合写一本书(一本书能否有多个作者)   可以!!!

如果双方都是可以,那么就是多对多

强调!!!  foreign key只是用来帮你建表关系的,不是某个关系特有的方法

多对多关系的建立,必须手动创建第三张表,用来专门记录两种表之间的关系

建表

先建两种普通的表,不需要设置外键

图书表

create table book(
id int primary key auto_increment,
title varchar(10),
price int
);

MySQL表关系--外键

作者表

create table author(
id int primary key auto_increment,
name varchar(20),
age int
);

MySQL表关系--外键

关联表    两个外键(一个book_id关联book表的id字段,一个author_id关联author表的id字段)

create table book2author(
id int primary key auto_increment,
book_id int,
foreign key(book_id) references book(id)
on update cascade
on delete cascade,
author_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade
);

MySQL表关系--外键

插入数据

图书表插入数据:

insert into book(title,price) values('西游记',50),('水浒传',80),('红楼梦',60);

MySQL表关系--外键

作者表插入数据:

insert into author(name,age) values('小红',30),('小兰',40),('小绿',50);

MySQL表关系--外键

关联表插入数据:

insert into book2author(book_id,author_id) values(1,1),(1,2),(2,1),(1,2),(3,1);

MySQL表关系--外键

四、一对一关系

一对一的场景,当你的表特别庞大的时候,你可以考虑拆分表

比如一张表里面有客户基本信息(名字,年龄),还有详细(爱好,身高,身份证号。。。)

外键字段建在任意一方都可以,但是推荐你建在查询频率高的一方

建表

用户表:因为用户表中含有外键所以应该先建用户详细表

create table user(
id int primary key auto_increment,
name varchar(10),
age int,
userdetail_id int unique, #唯一,一对一关系
foreign key(userdetail_id) references userdetail(id)
on update cascade
on delete cascade
);

MySQL表关系--外键

用户详细表

create table userdetail(
id int primary key auto_increment,
hobby varchar(20),
IDcard varchar(20)
);

MySQL表关系--外键

总结:

1.通常将关系字段称之为外键字段

一对多的外键字段,建在多的一方

多对多的外键字段,建在第三张表

一对一,外键字段建在任意一方都可以,推荐建在查询频率高的那边

2.判断表关系的最简单的方法:

图书与出版社

  一本书可不可以有多个出版社    不可以!!!

  一个出版社可不可以出多本书    可以!!!

  一对多的关系

图书与作者表

  一本书可不可以有多个作者    可以!!!

  一个作者可不可以出多本书    可以!!!

  多对多的关系

作者与作者详情

  一个作者可不可以有多个详情        不可以!!!

  一个作者详情可不可以有多个作者   不可以!!!

  要么两者是一对一,要么两者之间没有关系

了解知识点:

修改表
1.修改表的完整语句
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 旧字段名 新字段名 新数据类型 [完整性约束条件…]; 复制表 # 查询语句执行的结果也是一张表,可以看成虚拟表
# 复制表结构+记录 (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;