6. 表与表之间的关系_表关系-CSDN博客

1.原始表格

员工信息表
id	emp_name	emp_gender	dep_name	dep_desc
1	kid	male	A部门	play
2	qz	male	B部门	brick
3	ze	male	B部门	brick
4	db	male	B部门	brick
5	pp	male	C部门	eat

1.2在开发中表格缺点

1.表的重点不清晰。
2.存在很多相同的内容,浪费磁盘空间。
3.数据的扩展性极差. ↓
* 在开发中数据都是上万条起步的.例某部分换名字, 有1万个员工绑定改部分,需要一个个去替换.

2.拆分表格

将原来的表格进行拆分,重新建立的表重点清晰.

员工emp				部门dep
id	emp_name	emp_gender	id	dep_name	dep_desc
1	kid	male	1	A部门	play
2	qz	male	2	B部门	brick
3	ze	male	3	C部门	eat
4	db	male
5	pp	male

表拆分了,表与表之间的数据对应关系没有了,
需要加一个字段连接表与表之间的关系.
这个字段称为外键.

员工emp					部门dep
id	emp_name	emp_gender	dep_id	id	dep_name	dep_desc
1	kid	male	1	1	A部门	play
2	qz	male	2	2	B部门	brick
3	ze	male	2	3	C部门	eat
4	db	male	2
5	pp	male	3

这样就节省了空间,数据扩张性变强。

3.外键

foreign key 外键 用来建立表与表之间的关系。

在关联表中绑定被关联表的字段,建立联系.

foreign key(关联表中的外键字段)  references 被关联的表(被关联的表字段)

4.表之间的关系

表与表之间关系有四种：
1.一对多关系 (表关系没有 多对一的说法, 都是一对多)
2.多对多关系
3.一对一关系
4.没有关系

4.1确定表关系

判断表与表之间关系的时候,在不熟悉的情况下一定要按照换位思考分别在两张表的角度考虑,一定要两张表都考虑完全。

分析上例表的关系.
1.先站在员工表考虑：
	员工表里中 一个员工是否能对应多个部门？   不能！
	
2.再站在部门表考虑：
	部门表里中 一个部门是否能对应多个员工? 能！
	
结论：部门表单向的一对多员工表,那么两张表的关系就是‘一对多’,
    部分表为一, 员工表多.

5.一对多表关系

规则:
1. 一对多表关系,外键字段建立在多的一方。
2. 在创建表的时候一定要先创建被关联表。
3. 录数据的时候,先录被关联表。

5.1创建表

# 第一张部门表 
create table dep(
    dep_id int primary key auto_increment,  # 主键 自增
    dep_name varchar(16),
    dep_desc varchar(16)
  );

# 第二张员工表
create table emp(
    id int primary key auto_increment,
    name char(16),
    gender enum('male', 'femal', 'others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(dep_id)
    # 先把两张表的其他sql语句写出来,最后写外键关联
);

语句解释: foreign key(dep_id) references dep(dep_id) 
首先声明dep_id是一个外键的字段,其次说明这张表与dep的dep_id有关系。

5.2 表结构

# 查询部门表结构
desc dep;

+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| dep_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| dep_name | varchar(16) | YES  |     | NULL    |                |
| dep_desc | varchar(16) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

# 查询员工表结构  
desc emp;

                                                 # key 为 MUL 外键的标识
+--------+-------------------------------+------+-----+---------+----------------+
| Field  | Type                          | Null | Key | Default | Extra          |
+--------+-------------------------------+------+-----+---------+----------------+
| id     | int(11)                       | NO   | PRI | NULL    | auto_increment |
| name   | char(16)                      | YES  |     | NULL    |                |
| gender | enum('male','femal','others') | YES  |     | male    |                |
| dep_id | int(11)                       | YES  | MUL | NULL    |                |
+--------+-------------------------------+------+-----+---------+----------------+

5.3录数据

录数据的时候,先录被关联表。

# 为部门表录值
insert into dep(dep_name, dep_desc) values 
    ('A部门', 'play'), 
    ('B部门', 'brick'),
    ('C部门','eat');

# 为员工表录值
insert into emp(name, dep_id) values
    ('kid', 1),  # gender 使用默认值male
    ('qz', 2),
    ('ze', 2),
    ('db', 2),
    ('pp', 3);

5.4查看表数据

# 查看部门表数据
select * from dep;

+--------+----------+----------+
| dep_id | dep_name | dep_desc |
+--------+----------+----------+
|      1 | A部门    | play     |
|      2 | B部门    | brick    |
|      3 | C部门    | eat      |
+--------+----------+----------+

# 查看员工表数据
select * from emp;

+----+------+--------+--------+
| id | name | gender | dep_id |
+----+------+--------+--------+
|  1 | kid  | male   |      1 |
|  2 | qz   | male   |      2 |
|  3 | ze   | male   |      2 |
|  4 | db   | male   |      2 |
|  5 | pp   | male   |      3 |
+----+------+--------+--------+

5.5更新删除表

无法更改被关联表中被关联的字段信息.
删除表操作,只能先删关联表,才能删除被关联表.

# 更改员工的名字 kid --> qq    
update emp set name='qq' where name = 'kid';  
# 更改员工的部门信息 2 --> 1
update emp set dep_id=1 where name = 2;

# 更改部门的id 2 --> 200
update dep set dep_id=20 where dep_id =2;  # 报错

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails  (`db1`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`dep_id`))

错误1451（23000）：无法删除或更新父行：外键约束失败
（`db1`.`emp`，约束`emp_ibfk_1`外键（`dep_id`）引用`dep`（`dep_id`）

# 无法删除被关联表  
drop table dep;  # 报错
# ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

# 先删除关联表
drop table emp;

# 再删除被关联表
drop table dep;

6.级联

先删除被关联表的数据,再删除关联表的数据,操作繁琐。
做到数据之间有关系同步更新,同步删除。
在创建表的时候在绑定外键的后面设置级联更新与级联删除。

6.1级联设置

级联更新 on update cascade
级联删除 on delete cascade

# 部门表
create table dep(
    dep_id int primary key auto_increment,
    dep_name varchar(16),
    dep_desc varchar(16)
);

# 员工表
create table emp(
    id int primary key auto_increment,
    name char(16),
    gender enum('male', 'femal', 'others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(dep_id)
    on update cascade
    on delete cascade
);

6.2录数据

# 为部门表录值
insert into dep(dep_name, dep_desc) values 
    ('A部门', 'play'), 
    ('B部门', 'brick'),
    ('C部门', 'eat');

# 为员工表录值
insert into emp(name, dep_id) values
    ('kid', 1),  # gender 使用默认值male
    ('qz', 2),
    ('ze', 2),
    ('db', 2),
    ('pp', 3);

# 部门表内容
select * from dep;

+--------+----------+----------+
| dep_id | dep_name | dep_desc |
+--------+----------+----------+
|      1 | A部门    | play     |
|      2 | B部门    | brick    |
|      3 | C部门    | eat      |
+--------+----------+----------+

# 员工表内容
select * from emp;

+----+------+--------+--------+
| id | name | gender | dep_id |
+----+------+--------+--------+
|  1 | kid  | male   |      1 |
|  2 | qz   | male   |      2 |
|  3 | ze   | male   |      2 |
|  4 | db   | male   |      2 |
|  5 | pp   | male   |      3 |
+----+------+--------+--------+

6.3修改值

# 修改被关联表中被关联的字段值  id 2 --> 200
update dep set dep_id = 200 where dep_id =2;

# 查看修改后的效果
select * from dep;

+--------+----------+----------+
| dep_id | dep_name | dep_desc |
+--------+----------+----------+
|      1 | A部门    | play     |
|      3 | C部门    | eat      |
|    200 | B部门    | brick    |
+--------+----------+----------+

# 查看修改后的效果
select * from emp;

+----+------+--------+--------+
| id | name | gender | dep_id |
+----+------+--------+--------+
|  1 | kid  | male   |      1 |
|  2 | qz   | male   |    200 |
|  3 | ze   | male   |    200 |
|  4 | db   | male   |    200 |
|  5 | pp   | male   |      3 |
+----+------+--------+--------+

6.4删除值

# 删除 部门表  id=1 的数据
delete from dep where dep_id=1;

select * from emp;

+----+------+--------+--------+
| id | name | gender | dep_id |
+----+------+--------+--------+
|  2 | qz   | male   |    200 |
|  3 | ze   | male   |    200 |
|  4 | db   | male   |    200 |
|  5 | pp   | male   |      3 |
+----+------+--------+--------+

# 查看删除后的信息 对应部门id 为 1 的员工数据一起被删除
select * from dep;

+--------+----------+----------+
| dep_id | dep_name | dep_desc |
+--------+----------+----------+
|      3 | C部门    | eat      |
|    200 | B部门    | brick    |
+--------+----------+----------+

7.多对多表关系

book			author
id	title	ptice	id	name	age
1	Python 入门到放弃	2100	1	kid	18
2	C语言三剑客	1800	2	qz	19
3	Linux就该这么学	100
4	代码整洁之道	66

先站在书籍表的角度：
	一本书可不可以有多个作者, 可以！
在站在作者表的角度：
	一个作者可不可以写多本书, 可以！
结论：都是双向的一对多,那么表关系就是多对多。

book				author
id	title	ptice	author_id	id	name	age	book_id
1	Python 入门到放弃	2100	1,2	1	kid	18	1,2,4
2	C语言三剑客	1800	1,2	2	qz	19	1,2,3
3	Linux就该这么学	100	2
4	代码整洁之道	66	1

7.1相互关联

# 第一张表 书籍
create table book (
	id int primary key auto_increment,
	title varchar(32),
	pticr int,
	author_id int,  # 设置外键
	
	foreign key(author_id) references author(id)  # 外键关联 作者表的主键
	on update cascade
	on delete cascade
);

ERROR 1215 (HY000): Cannot add foreign key constraint
错误1215（HY000）：无法添加外键约束

# 第二张表 作者
create table author (
	id int primary key auto_increment,
	name varchar(32),
	age int,
	book_id int,  # 设置外键
    
	foreign key(book_id) references book(id)  # 外键关联 书籍 表的主键
	on update cascade
	on delete cascade
);

ERROR 1215 (HY000): Cannot add foreign key constraint
错误1215（HY000）：无法添加外键约束

相互关联,都需要对方先建立表。说明这个方式不可用.

7.2第三张表的引入

针对多对对字段：关系不能在两张表中创建外键,需要再单独开设一张表,专门用来存储两种表数据之间的关系。

book			author
id	title	ptice	id	name	age
1	Python 入门到放弃	2100	1	kid	18
2	C语言三剑客	1800	2	qz	19
3	Linux就该这么学	100
4	代码整洁之道	66

book2author	外键1	外键2
id	book_id	author_id
1	1	1
2	1	2
3	2	1
4	2	2
5	3	2
6	4	1

表与表之间没有直接的关系,通过第三张表来关联.
第三张表与书和作者都是一对多的关系。

7.3创建表

# 第一张表 书籍
create table book (
	id int primary key auto_increment,
	title varchar(32),
	ptice int);

# 第二张表 作者
create table author (
	id int primary key auto_increment,
	name varchar(32),
	age int);

# 第三张表 
create table book2author(

	id int primary key auto_increment,
	
	book_id int,  # 外键1
	foreign key(book_id) references book(id)  # 关联书籍表
	on update cascade
	on delete cascade,
	
	author_id int,  # 外键2
	foreign key(author_id) references author(id)  # 关联作者表
	on update cascade
	on delete cascade
	);

7.4第三张表的结构

# 查看第三张表的结构
 desc book2author;

                               # ↓  绑定两个外键
+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| book_id   | int(11) | YES  | MUL | NULL    |                |
| author_id | int(11) | YES  | MUL | NULL    |                |
+-----------+---------+------+-----+---------+----------------+

7.5录数据

insert into book(title, ptice) values 
	('Python 入门到放弃', 2100),
	('C语言三剑客',1800), 
	('Linux就该这么学', 100),
	('代码整洁之道', 66);

insert into author(name, age) values 
    ('kid', 18),
    ('qz', 19);

# 查看书籍表内容
select * from book;

+----+------------------------+-------+
| id | title                  | ptice |
+----+------------------------+-------+
|  1 | Python 入门到放弃      |  2100 |
|  2 | C语言三剑客            |  1800 |
|  3 | Linux就该这么学        |   100 |
|  4 | 代码整洁之道           |    66 |
+----+------------------------+-------+

# 查看作者表内容
select * from author;

+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | kid  |   18 |
|  2 | qz   |   19 |
+----+------+------+

# 第三张为表录值绑定关表之间的关系
insert into book2author(book_id, author_id) values 
    (1, 1), 
    (1, 2),
    (2, 1),
    (2, 2),
    (3,2),
    (4, 1);

# 第三张表信息
select * from book2author;

+----+---------+-----------+
| id | book_id | author_id |
+----+---------+-----------+
|  1 |       1 |         1 |
|  2 |       1 |         2 |
|  3 |       2 |         1 |
|  4 |       2 |         2 |
|  5 |       3 |         2 |
|  6 |       4 |         1 |
+----+---------+-----------+

7.6删除表数据

对表进行修改只能影响关联的表不会印象其他的表。

# 删除 书籍表中 id 为 1 所在行数据
delete from book where id=1;

select * from book;
select * from author;
select * from book2author;

+----+----------------------+-------+
| id | title                | ptice |
+----+----------------------+-------+
|  2 | C语言三剑客          |  1800 |
|  3 | Linux就该这么学      |   100 |
|  4 | 代码整洁之道         |    66 |
+----+----------------------+-------+

+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | kid  |   18 |
|  2 | qz   |   19 |
+----+------+------+

+----+---------+-----------+
| id | book_id | author_id |
+----+---------+-----------+
|  3 |       2 |         1 |
|  4 |       2 |         2 |
|  5 |       3 |         2 |
|  6 |       4 |         1 |
+----+---------+-----------+

将book表中数据全部删除,也不影响author表.

delete from book where id>1;

select * from book;
# Empty set (0.00 sec) 没有数据

select * from  book2author;
# Empty set (0.00 sec) 没有数据

# 没有直接的关联所有不会影响作者表的数据
select * from author;

+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | kid  |   18 |
|  2 | qz   |   19 |
+----+------+------+

8.一对一

如果一个表的字段特别多,每次查询又不需要用到所有的值,将表一分为二。

用户表
id	name	agr	addr	phone	hobby	email
1	kid	18	洪荒大世界	110	鸿蒙紫气	110@qq.com
2	qz	19	九天仙域	120	先天道体	120.@qq.cpm

用户表记录了 id name age addr phone hobby email.... 
拆分：
1.用户表：   id name age
2.用户详情： id addr ptone hobby email....

user			user_detail
id	name	agr	id	phone	addr	hobby	email
1	kid	18	1	110	洪荒大世界	鸿蒙紫气	110@qq.com
2	qz	19	2	120	九天仙域	先天道体	120.@qq.cpm

建立关系:

站在用户表：一个用户能对应对个用户详情吗？ 不能
站在详情表：一个用户详情是否属于多个用户？ 不能 

结论：单向的一对多,都不成立,那么这个时候两者之间的表关系就是一对一,或者没有关系。

站在用户表：一个用户能对应一个用户详情吗？ 能
站在详情表：一个用户详情是否属于一个用户？ 能
那么表与表之间就是一对一的关系.

一对一外键字段在任意一方都可以,但推荐建在查询频率高的表中。

user			唯一	user_detail
id	name	agr	user_detail_id	user_detail_id	phone	addr
1	kid	18	1	1	110	洪荒大世界
2	qz	19	2	2	120	九天仙域

8.1创建表

一对多和一对一的区别就是在于 一对一的唯一性.

# 创建用户表信息表
create table user_datail(
	user_detail_id int primary key auto_increment,
	phone int,
	addr varchar(16)
);

# 创建用户表
create table user(
    id int primary key auto_increment,
    name varchar(16),
    age int,
    user_detail_id int unique,
    foreign key(user_detail_id) references user_datail(user_detail_id)
    on update cascade
    on delete cascade
    );

8.2录数据

insert into user_datail(phone, addr) values 
    (110, '洪荒大世界'), 
    (120, '九天仙域');

insert into user(name, age, user_detail_id) values
    ('kid', 18, 1), 
    ('qz', 19, 2);

8.3查看表数据

select * from user;

+----+------+------+----------------+
| id | name | age  | user_detail_id |
+----+------+------+----------------+
|  1 | kid  |   18 |              1 |
|  2 | qz   |   19 |              2 |
+----+------+------+----------------+

select * from user_datail;

+----------------+-------+-----------------+
| user_detail_id | phone | addr            |
+----------------+-------+-----------------+
|              1 |   110 | 洪荒大世界      |
|              2 |   120 | 九天仙域        |
+----------------+-------+-----------------+

8.4删除表数据

# 级联删除
delete from user_datail where user_detail_id=1;

select * from user_datail;

+----------------+-------+--------------+
| user_detail_id | phone | addr         |
+----------------+-------+--------------+
|              2 |   120 | 九天仙域     |
+----------------+-------+--------------+

select * from user;

+----+------+------+----------------+
| id | name | age  | user_detail_id |
+----+------+------+----------------+
|  2 | qz   |   19 |              2 |
+----+------+------+----------------+