数据库之表关系，一对多、多对多、一对一（外键，级联更新级联删除）

如何让两种表有代码层面上真正的关联 就必须使用外键
什么是外键?
让表与表有硬性层面上的关系

一对多(Foreign Key)
foreign key
外键约束
1.在创建表的时候 必须先创建被关联表
2.插入数据的时候 也必须先插入被关联表的数据

#部门表
id dep_name dep_sesc
1 技术部 技术支持
2 管理部 公司经营管理
3 销售部 负责产品的销售和后期的客户关系维护
4 咨询部 公司产品介绍

#先创建被关联表
create table dep(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(128)
);

#再创建关联表
create table emp(
id int primary key auto_increment,
emp_name varchar(16),
emp_gender enum('male','female','others') default 'male',
dep_id int,
foreign key(dep_id) references dep(id)
);

#先插入被关联表的数据

insert into dep(dep_name,dep_desc) values('技术部','技术支持'),
('管理部','公司经营管理'),
('销售部','负责产品的销售和后期的客户关系维护'),
('咨询部','公司产品介绍');

#再插入关联表的数据

insert into emp(emp_name,dep_id) values('张三',1),('李四',2),('王五',3),('小红',4),('小丽',4),('小明'，2);

改
update dep set id=5 where id=2; #不能更改
'''
mysql> update dep set id=5 where id=2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db3`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))

'''

update emp set id=100 where id=2; #可以更改
'''
mysql> update emp set id=100 where id=2;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
'''

delete dep from where id=1; #,在没有先删除对应员工表数据时，被关联的部门不能删除
####
mysql> delete from dep where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db3`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))
mysql> update emp set id=2 where id=100;
#####

外键虽然能够帮你强制建立表关系 但是也会给表之间增加数据相关的约束
1.删除数据的时候 先删员工表的数据 再删部门表的数据
delete from emp where id=2;
delete from emp where id=6;
'''
mysql> delete from emp where id=2;
Query OK, 1 row affected (0.05 sec)

mysql> delete from emp where id=6;
Query OK, 1 row affected (0.06 sec)

mysql> select * from emp;
+----+----------+------------+--------+
| id | emp_name | emp_gender | dep_id |
+----+----------+------------+--------+
| 1 | 张三 | male | 1 |
| 3 | 王五 | male | 3 |
| 4 | 小红 | male | 4 |
| 5 | 小丽 | male | 4 |
+----+----------+------------+--------+
'''
===============================================

delete from dep where id=2;

'''
mysql> delete from dep where id=2;
Query OK, 1 row affected (0.06 sec)

mysql> select * from dep;
+----+-----------+-----------------------------------------------------+
| id | dep_name | dep_desc |
+----+-----------+-----------------------------------------------------+
| 1 | 技术部 | 技术支持 |
| 3 | 销售部 | 负责产品的销售和后期的客户关系维护 |
| 4 | 咨询部 | 公司产品介绍 |
+----+-----------+-----------------------------------------------------+
3 rows in set (0.00 sec)
'''

级联更新级联删除
删除部门后，对应的部门里面的员工表数据对应删除
更新部门后，对应员工表中的标示部门的字段同步更新

create table dep1(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(128)
);

create table emp1(
id int primary key auto_increment,
emp_name varchar(32),
emp_gender enum('male','female','others') default 'male',
dep_id int,
foreign key(dep_id) references dep1(id)
on update cascade
on delete cascade
);

insert into dep1(dep_name,dep_desc) values ('技术部','技术支持'),
('管理部','公司经营管理'),
('销售部','负责产品的销售和后期的客户关系维护'),
('咨询部','公司产品介绍');
'''
mysql> select * from dep1;
+----+-----------+-----------------------------------------------------+
| id | dep_name | dep_desc |
+----+-----------+-----------------------------------------------------+
| 1 | 技术部 | 技术支持 |
| 2 | 管理部 | 公司经营管理 |
| 3 | 销售部 | 负责产品的销售和后期的客户关系维护 |
| 4 | 咨询部 | 公司产品介绍 |
+----+-----------+-----------------------------------------------------+
4 rows in set (0.00 sec)
'''

insert into emp1(emp_name,dep_id) values('张三',1),('李四',2),
('王五',3),('小红',4),('小丽',4),('小明',2);
'''
mysql> select * from emp1;
+----+----------+------------+--------+
| id | emp_name | emp_gender | dep_id |
+----+----------+------------+--------+
| 1 | 张三 | male | 1 |
| 2 | 李四 | male | 2 |
| 3 | 王五 | male | 3 |
| 4 | 小红 | male | 4 |
| 5 | 小丽 | male | 4 |
| 6 | 小明 | male | 2 |
+----+----------+------------+--------+
6 rows in set (0.00 sec)
'''

update dep1 set id=200 where id=1;
'''
mysql> select * from dep1;
+-----+-----------+------------------------------------
| id | dep_name | dep_desc
+-----+-----------+------------------------------------
| 2 | 管理部 | 公司经营管理
| 3 | 销售部 | 负责产品的销售和后期的客户关系维护
| 4 | 咨询部 | 公司产品介绍
| 200 | 技术部 | 技术支持
+-----+-----------+------------------------------------
4 rows in set (0.00 sec)

mysql> select * from emp1;
+----+----------+------------+--------+
| id | emp_name | emp_gender | dep_id |
+----+----------+------------+--------+
| 1 | 张三 | male | 200 |
| 2 | 李四 | male | 2 |
| 3 | 王五 | male | 3 |
| 4 | 小红 | male | 4 |
| 5 | 小丽 | male | 4 |
| 6 | 小明 | male | 2 |
+----+----------+------------+--------+
'''

delete from dep1 where id=2;
# 删除id=2 管理部消失，关联的员工表中李四和小明两条信息也自动被清除了。
'''
mysql> select * from dep1;
+-----+-----------+-------------------------------------
| id | dep_name | dep_desc
+-----+-----------+-------------------------------------
| 3 | 销售部 | 负责产品的销售和后期的客户关系维护
| 4 | 咨询部 | 公司产品介绍
| 200 | 技术部 | 技术支持
+-----+-----------+-------------------------------------
3 rows in set (0.00 sec)

mysql> select * from emp1;
+----+----------+------------+--------+
| id | emp_name | emp_gender | dep_id |
+----+----------+------------+--------+
| 1 | 张三 | male | 200 |
| 3 | 王五 | male | 3 |
| 4 | 小红 | male | 4 |
| 5 | 小丽 | male | 4 |
+----+----------+------------+--------+
4 rows in set (0.00 sec)

'''

多对多

# 图书表与作者表之间的关系
"""
仍然站在两张表的角度：
1.站在图书表：一本书可不可以有多个作者，可以！那就是书多对一作者
2.站在作者表：一个作者可不可以写多本书，可以！那就是作者多对一书
双方都能一条数据对应对方多条记录，这种关系就是多对多！
"""
# 先来想如何创建表？图书表需要有一个外键关联作者，作者也需要有一个外键字段关联图书。问题来了，先创建谁都不合适！如何解决？
# 建立第三张表，该表中有一个字段fk左表的id，还有一个字段是fk右表的id
多对多关系的建立 必须手动创建第三张表 用来专门记录两种表之间的关系

先建两种普通的表 不需要设置外键
create table book(
id int primary key auto_increment,
b_name varchar(16),
prince double(255,30)
);

create table author(
id int primary key auto_increment,
name varchar(16),
age tinyint unsigned,
gender enum('male','female','others') default 'female'
);

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

insert into author(name,age) values('A',12),('B',13),('C',14),('D',289);
#查看作者信息

mysql> select * from author;
+----+------+------+--------+
| id | name | age | gender |
+----+------+------+--------+
| 1 | A | 12 | female |
| 2 | B | 13 | female |
| 3 | C | 14 | female |
| 4 | D | 255 | female |
+----+------+------+--------+

insert into book(b_name,prince) values('家',28.99),('风筝',36.99),
('偷影子的人',32.99),('菊与刀',18.888888888);

#查看书籍信息
mysql> select * from book;
+----+-----------------+-----------------------------------+
| id | b_name | prince |
+----+-----------------+-----------------------------------+
| 1 | 家 | 28.990000000000000000000000000000 |
| 2 | 风筝 | 36.990000000000000000000000000000 |
| 3 | 偷影子的人 | 32.990000000000000000000000000000 |
| 4 | 菊与刀 | 18.888888888000000000000000000000 |
+----+-----------------+-----------------------------------+

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

#查看作者与书籍一一对应关系表
mysql> select * from authorbook;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
| 9 | 1 | 4 |
| 10 | 1 | 2 |
| 11 | 2 | 3 |
| 12 | 1 | 3 |
| 13 | 3 | 2 |
| 14 | 2 | 1 |
| 15 | 3 | 1 |
| 16 | 4 | 1 |
+----+-----------+---------+

#改 作者（author）字段id
update author set id=200 where id=2;

mysql> update author set id=200 where id=2;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from author;
+-----+------+------+--------+
| id | name | age | gender |
+-----+------+------+--------+
| 1 | A | 12 | female |
| 3 | C | 14 | female |
| 4 | D | 255 | female |
| 200 | B | 13 | female |
+-----+------+------+--------+

#查看作者与书籍一一对应关系表 z只要是author_id=2的字段都被改成author_id=200
mysql> select * from authorbook;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
| 9 | 1 | 4 |
| 10 | 1 | 2 |
| 11 | 200 | 3 |
| 12 | 1 | 3 |
| 13 | 3 | 2 |
| 14 | 200 | 1 |
| 15 | 3 | 1 |
| 16 | 4 | 1 |
+----+-----------+---------+

#改 书籍（book）字段id
update book set id=238 where id=3

mysql> update book set id=238 where id=3;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from book;
+-----+-----------------+-----------------------------------+
| id | b_name | prince |
+-----+-----------------+-----------------------------------+
| 1 | 家 | 28.990000000000000000000000000000 |
| 2 | 风筝 | 36.990000000000000000000000000000 |
| 4 | 菊与刀 | 18.888888888000000000000000000000 |
| 238 | 偷影子的人 | 32.990000000000000000000000000000 |
+-----+-----------------+-----------------------------------+

mysql> select * from book;
+-----+-----------------+-----------------------------------+
| id | b_name | prince |
+-----+-----------------+-----------------------------------+
| 1 | 家 | 28.990000000000000000000000000000 |
| 2 | 风筝 | 36.990000000000000000000000000000 |
| 4 | 菊与刀 | 18.888888888000000000000000000000 |
| 238 | 偷影子的人 | 32.990000000000000000000000000000 |
+-----+-----------------+-----------------------------------+
4 rows in set (0.00 sec)

mysql> select * from authorbook;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
| 9 | 1 | 4 |
| 10 | 1 | 2 |
| 11 | 200 | 238 |
| 12 | 1 | 238 |
| 13 | 3 | 2 |
| 14 | 200 | 1 |
| 15 | 3 | 1 |
| 16 | 4 | 1 |
+----+-----------+---------+

#删除字段author_id=200

mysql> delete from author where id=200;
Query OK, 1 row affected (0.06 sec)

mysql> select * from authorbook;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
| 9 | 1 | 4 |
| 10 | 1 | 2 |
| 12 | 1 | 238 |
| 13 | 3 | 2 |
| 15 | 3 | 1 |
| 16 | 4 | 1 |
+----+-----------+---------+

#删除字段bookid=238
mysql> delete from book where id=238;
Query OK, 1 row affected (0.06 sec)

mysql> select * from authorbook;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
| 9 | 1 | 4 |
| 10 | 1 | 2 |
| 13 | 3 | 2 |
| 15 | 3 | 1 |
| 16 | 4 | 1 |
+----+-----------+---------+

还可以这样删除=====>
mysql> delete from authorbook where id=9;
Query OK, 0 rows affected (0.00 sec)
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
| 10 | 1 | 2 |
| 13 | 3 | 2 |
| 15 | 3 | 1 |
| 16 | 4 | 1 |
+----+-----------+---------+

然后查看 mysql> select * from book; mysql> select * from author;

 

一对一
一对一表关系
1.一对一的场景 当你的表特别庞大的时候 你可以考虑拆分表
2.联想老男孩的客户和学生

create table userdetail(
id int primary key auto_increment,
addr varchar(128) unique,
phone int not null,
qq varchar(11) not null);

create table user(
id int primary key auto_increment,
name varchar(16) not null,
age int ,
userdetail_id int unique,
foreign key(userdetail_id) references userdetail(id)
on update cascade
on delete cascade
);

insert into userdetail(addr,phone,qq) values('安徽',13568462190,'1148596457'),('河南',18891452563,'1854894445'),('山东',17750504669,'1132101165');

insert into user(name,age,userdetail_id) values('zhang',19,1),('li',23,2),('huang',22,3);