1、把所有数据都存放于一张表的弊端
1、表的组织结构复杂不清晰
2、浪费空间
3、扩展性极差
2、寻找表与表之间的关系的套路
举例：emp表 dep表
步骤一：
part1：
1、先站在左表emp的角度
2、去找左表emp的多条记录能否对应右表dep的一条记录
3、翻译2的意义：
左表emp的多条记录==》多个员工
右表dep的一条记录==》一个部门
最终翻译结果：多个员工是否可以属于一个部门？
如果是则需要进行part2的流程
part2：
1、站在右表dep的角度
2、去找右表dep的多条记录能否对应左表emp的一条记录
3、翻译2的意义：
右表dep的多条记录==》多个部门
左表emp的一条记录==》一个员工
最终翻译结果：多个部门是否可以包含同一个员工
如果不可以，则可以确定emp与dep的关系只一个单向的多对一
如何实现？
在emp表中新增一个dep_id字段，该字段指向dep表的id字段
# foreign key会带来什么样的效果？
#1、约束1：在创建表时，先建被关联的表dep，才能建关联表emp
create table dep(
id int primary key auto_increment,
dep_name char(10),
dep_comment char(60)
);
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)
);
#2、约束2：在插入记录时，必须先插被关联的表dep，才能插关联表emp
insert into dep(dep_name,dep_comment) values
('sb教学部','sb辅导学生学习，教授python课程'),
('外交部','老男孩上海校区驻张江形象大使'),
('nb技术部','nb技术能力有限部门');
insert into emp(name,gender,dep_id) values
('alex','male',1),
('egon','male',2),
('lxx','male',1),
('wxx','male',1),
('wenzhou','female',3);
#3、约束3：更新与删除都需要考虑到关联与被关联的关系
解决方案：
1、先删除关联表emp，再删除被关联表dep，准备重建
mysql> drop table emp;
Query OK, 0 rows affected (0.11 sec)
mysql> drop table dep;
Query OK, 0 rows affected (0.04 sec)
2、重建：新增功能，同步更新，同步删除
create table dep(
id int primary key auto_increment,
dep_name char(10),
dep_comment char(60)
);
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)
on update cascade
on delete cascade
);
insert into dep(dep_name,dep_comment) values
('sb教学部','sb辅导学生学习，教授python课程'),
('外交部','老男孩上海校区驻张江形象大使'),
('nb技术部','nb技术能力有限部门');
insert into emp(name,gender,dep_id) values
('alex','male',1),
('egon','male',2),
('lxx','male',1),
('wxx','male',1),
('wenzhou','female',3);
# 同步删除
mysql> select * from dep;
+----+------------------+------------------------------------------------------------------------------------------+
| id | dep_name | dep_comment |
+----+------------------+------------------------------------------------------------------------------------------+
| 1 | sb教学部 | sb辅导学生学习，教授python课程 |
| 2 | 外交部 | 老男孩上海校区驻张江形象大使 |
| 3 | nb技术部 | nb技术能力有限部门 |
+----+------------------+------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> select * from emp;
+----+------------------+--------+--------+
| id | name | gender | dep_id |
+----+------------------+--------+--------+
| 1 | alex | male | 1 |
| 2 | egon | male | 2 |
| 3 | lxx | male | 1 |
| 4 | wxx | male | 1 |
| 5 | wenzhou | female | 3 |
+----+------------------+--------+--------+
5 rows in set (0.00 sec)
mysql> delete from dep where id=1;
Query OK, 1 row affected (0.02 sec)
mysql> select * from dep;
+----+------------------+------------------------------------------------------------------------------------------+
| id | dep_name | dep_comment |
+----+------------------+------------------------------------------------------------------------------------------+
| 2 | 外交部 | 老男孩上海校区驻张江形象大使 |
| 3 | nb技术部 | nb技术能力有限部门 |
+----+------------------+------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select * from emp;
+----+------------------+--------+--------+
| id | name | gender | dep_id |
+----+------------------+--------+--------+
| 2 | egon | male | 2 |
| 5 | wenzhou | female | 3 |
+----+------------------+--------+--------+
2 rows in set (0.00 sec)
#同步更新
mysql> select * from emp;
+----+------------------+--------+--------+
| id | name | gender | dep_id |
+----+------------------+--------+--------+
| 2 | egon | male | 2 |
| 5 | wenzhou | female | 3 |
+----+------------------+--------+--------+
2 rows in set (0.00 sec)
mysql> update dep set id=200 where id =2;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dep;
+-----+------------------+------------------------------------------------------------------------------------------+
| id | dep_name | dep_comment |
+-----+------------------+------------------------------------------------------------------------------------------+
| 3 | nb技术部 | nb技术能力有限部门 |
| 200 | 外交部 | 老男孩上海校区驻张江形象大使 |
+-----+------------------+------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select * from emp;
+----+------------------+--------+--------+
| id | name | gender | dep_id |
+----+------------------+--------+--------+
| 2 | egon | male | 200 |
| 5 | wenzhou | female | 3 |
+----+------------------+--------+--------+
2 rows in set (0.00 sec)
需求 见图1
以上数据 存在
1.数据冗余
2.效率低下
3.扩展性差
通过分表可以解决上述问题
新的问题? 不清楚员工是那个部门
解决的办法 在员工表中添加字段保存部门的编号
此时两个表之间就产生了关系
员工属于 部门 部门包含员工
可以称为主从关系
部门表叫主表 员工表叫从表
虽然有了关系 但是两个表之间还是没有任何物理联系,插一个不存在的部门也没问题
mysql提供了 foreign key 专门用于为表和表之间 建立物理关联
思考 表里存储的是一条条的记录
两个表之间能产生的关系有哪些?
现有 A B两张表
1.多对一
2.一对一
3.多对多
在查找表之间的关系时 要分别站在 不同表去思考
1. 从员工出发 员工对于部门来说 时 多个员工对应一个部门
2. 从部门出发 一个部门对应多个员工
如果两个得到的关系不同 则认为 这种多对一关系是单向
先创建部门表
create table dept(id int primary key auto_increment,name char(10),manager char(10));
在创建员工表
create table emp(id int primary key auto_increment,name char(10),dept_id int,foreign key(dept_id) references dept(id));
需求: 设计 学员表 和 班级表 五分钟练习
两个表多对一的关系 通过外键来进行关联
外键加在谁身上? 加到从表上
create table class(id int primary key auto_increment,name char(10));
create table student(id int primary key auto_increment,name char(10),c_id int,foreign key(c_id) references class(id));
总结: 外键的作用 表与表之间建立联系
添加外键约束时: 产生的限制
被关联的表需要先被创建
部门数据(主表)应该先插入 员工数据(从表)后插入
在删除部门数据前(主表)前 要保证该部门的员工数据都删除了
在更新部门编号前 要先保证没有员工关联到这个部门
简单的说 外键指的是 另一张的主键
外键加上以后 主表中的数据 删除 和更新时 都受到限制
解决的方案是为 外键 添加 级联操作
级联操作
指的是就是同步更新和删除
语法:在创建外键时 在后面添加 on update cascade 同步更新
on delete cascade 同步删除
实例:
create table class(id int primary key auto_increment,name char(10));
create table student(
id int primary key auto_increment,
name char(10),
c_id int,
foreign key(c_id) references class(id)
on update cascade
on delete cascade
);
insert into class value(null,"python3期");
insert into student value(null,"罗傲宇",1);
对主表的id进行更新
以及删除某条主表记录 来验证效果
多对多关系的处理:
建立一个第三方表 专门存储两个表之间的关系
这个关系表 应该有两个字段 分别关联学生的id 和老师的id
为了保证 数据的合法性 完整性 给这两个字段都添加外键约束
实例: 见图2
create table teacher (id int primary key auto_increment,name char(10));
create table student (id int primary key auto_increment,name char(10));
create table t_s (t_id int,
s_id int,
foreign key(t_id) references teacher(id),
foreign key(s_id) references student(id)
);
为了避免重复无用的关系数据 关系表加上关联的主键约束
create table t_s (t_id int,
s_id int,
foreign key(t_id) references teacher(id),
foreign key(s_id) references student(id),
primary key(t_id,s_id)
);
insert into student value(null,"lxx");
insert into teacher value(null,"exx");
insert into t_s value(1,1);
