mysql数据库：分表、多表关联、外键约束、级联操作

本文目录：

一、分表、外键、级联、一对多

二、多对多

三、一对一

 

 

一、分表、外键、级联、一对多

分表

将部门数据与员工数据放到同一张表中会造成
    数据重复
    结构混乱
    扩展维护性差
    需要分表


create table dept(id int primary key auto_increment,name char(20),job char(20));
create table emp(id int primary key auto_increment,name char(20),gendr char,age int,salary float,d_id int);

 

外键

以上代码 可以建立关联关系 但是这个关系是逻辑上的 不实际存在
需要为他建立物理上的关联
通过外键 约束

create table 表名(字段名 类型(长度),foreign key(外键的字段名称) references 对方表名(对方主键名));

使用外键时 必须分清 主从关系

外键的第一种约束
先建主表
再建从表
create table dept(id int primary key auto_increment,name char(20),job char(20));
create table emp(id int primary key auto_increment,name char(20),d_id int,foreign key(d_id) references dept(id));

外键的第二个约束
先插入主表
再插入从表

外键的第三个约束
删除记录时
先删除从表记录
再删主表记录

外键的第四个约束
从表更新外键时 必须保证外键是存在的

外键的第五个约束
更新主表的id时
必须先删除从表关联的数据
或者把关联数据 关联其他的主表id

外键的第六个约束
删除主表时 要先删除从表

 

级联操作

有了这几个约束后 主表和从表中的数据必然是完整的
相应的收到外键约束  主表的删除  更新操作受到限制
很多情况下 我们就是要删除一个部门 然而需要至少两条sql语句   麻烦

可以使用级联
create table emp(
id int primary key auto_increment,
name char(20),
d_id int,
foreign key(d_id) references dept(id)
on delete cascade
on update cascade
);

on delete cascade 当主表删除记录时 从表相关联的记录同步删除
on update cascade 当主表id更新时 从表相关联的记录同步更新
注意是单向的  主表变化是 级联操作从表   从表的变化不会级联到主表

一对多

一个场景中对应多个对象，想想生活中有哪些例子：一个年级可以有多个班级，古代一个官人可以有多个老婆，一条线可以有多辆公交车等等；

为了区分一对多和多对一，统一口径都叫一对多；即多个班级对应一个班级；

刚才的ehr库中的dept和emp表很好的说明一多的关系，即一个部门有多名员工，多个员工属于一个部门，但仔细想有没有这种情况存在，一个员工身兼数职，有啊！

但这种情况在公司里是少数，那我们举一个简单的公交系统案例：

 

 

 车辆表中的线路ID外键关联线路表中的id

 

二、多对多

老师和学生
一个老师可以教多个学生
一个学生可以被多个老师教
老师表和学生表 是多对多的关系
需要一个中间表 专门存储关联关系

create table teacher(id int primary key auto_increment,name char(15));
create table student(id int primary key auto_increment,name char(15));

 

 

#中间表
create table tsr(
id int primary key auto_increment,
t_id int,s_id int,
foreign key(t_id) references teacher(id),
foreign key(s_id) references student(id)
);

 

现在老师和学生 都是主表  关系表是从表
先插入老师和学生数据
insert into teacher values
(1,"水月先生"),
(2,"黄承彦");

insert into student values
(1,"诸葛亮"),
(2,"司马懿");

(3,"陆逊");

# 插入对应关系
insert into tsr values
(null,1,1),
(null,1,2),
(null,2,1);

(null,2,3);

 

 

 

如何知道  1号老师教过哪些学生

 

或者

如何知道1号被哪些老师教过

 

三、一对一

夫妻关系确实是一对一 但是在表中字段完全相同 没有必要分表

强行举例

客户 和 学生
一个客户只能产生一个学生
一个学生只能对应一个客户
这样的关系是一对一
使用外键来关联  但是需要给外键加上唯一约束
客户和学生有主从关系 需要先建立客户 再建学生

create table customer(c_id int primary key auto_increment,
name char(20),phonenum char(11),addr char(20));
create table student1(s_id int primary key auto_increment,
name char(20),
class char(11),
number char(20),
housenum char(20),c_id int UNIQUE,
foreign key(c_id) references customer(c_id)
);

一对一的另一种使用场景

当一个表这种字段太多 而常用字段不多时  可以采取垂直分表的方式来提高效率
原有的person表

拆分为
st_info
姓名 性别  年龄

details_info
身份证 地址 名族 身高 体重 血型 学历 政治面貌 联系方式

也有另一种提升效率的方式 水平分表
当一个表中数据记录太多时  效率会降低  可以采取水平分表  字段完全相同
stu  中有十万数据
    stu1 五万
    stu2 五万