多表设计 - 一对一多对多

一.一对一关系概述：

例如：一位用户只能有一张身份证，一张身份证也只能对应一位用户

如果用户基本信息查询频率比用户身份信息查询频率高，为了提高效率，可拆分为两张表：

此时如何体现一对一的关系呢？其实一对一的关系可看成特殊的一对多的关系，一对多的关系只需要在多的一方添加外键即可，因此可以通过外键来体现一对一的关系，只需要在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)，这样的话一行数据就只会对应另外一张表的一行数据：UNIQUE是唯一约束

---

二.一对一关系代码演示：

1.准备工作：

a.用户基本信息表：

代码：

-- 一对一: 用户 与 身份证
create table tb_user
(id     int unsigned primary key auto_increment comment 'ID',name   varchar(10)      not null comment '姓名',gender tinyint unsigned not null comment '性别, 1 男  2 女',phone  char(11) comment '手机号',degree varchar(10) comment '学历'
) comment '用户信息表';
​
insert into tb_user
values (1, '白眉鹰王', 1, '18812340001', '初中'),(2, '青翼蝠王', 1, '18812340002', '大专'),(3, '金毛狮王', 1, '18812340003', '初中'),(4, '紫衫龙王', 2, '18812340004', '硕士');

b.用户身份信息表：

代码：

create table tb_user_card
(id           int unsigned primary key auto_increment comment 'ID',nationality  varchar(10)  not null comment '民族',birthday     date         not null comment '生日',idcard       char(18)     not null comment '身份证号',issued       varchar(20)  not null comment '签发机关',expire_begin date         not null comment '有效期限-开始',expire_end   date comment '有效期限-结束',user_id      int unsigned not null unique comment '用户ID',constraint fk_user_id foreign key (user_id) references tb_user (id)
) comment '用户信息表';
​
insert into tb_user_card
values (1, '汉', '1960-11-06', '100000100000100001', '朝阳区公安局', '2000-06-10', null, 1),(2, '汉', '1971-11-06', '100000100000100002', '静安区公安局', '2005-06-10', '2025-06-10', 2),(3, '汉', '1963-11-06', '100000100000100003', '昌平区公安局', '2006-06-10', null, 3),(4, '回', '1980-11-06', '100000100000100004', '海淀区公安局', '2008-06-10', '2028-06-10', 4);

2.此时字段user_id是唯一的，代表了一个用户只有一个身份证，在用户基本信息表中，也就一个身份证只对应了一位用户；

---

三.多对多关系概述：

比如一个学生可以由多位教师代课，一位代课老师可以有多位学生；

再比如学生与课程的关系：

多对多关系中通过简单的外键关系较为复杂，因为一个字段中只能填入一个数据，无法表示出多个数据

插入第三张中间表，比如查找id为1的学生选了哪些课程，只需要到中间表即学生课程关系表中找字段studentid为1的对应的课程字段courseid对应了哪些课程。查看课程被哪些学生选了同理。

---

四.多对多关系代码演示：

1.准备工作：

a.学生表：

代码：

--  多对多: 学生 与 课程
create table tb_student
(id   int auto_increment primary key comment '主键ID',name varchar(10) comment '姓名',no   varchar(10) comment '学号'
) comment '学生表';
​
insert into tb_student(name, no)
values ('黛绮丝', '2000100101'),('谢逊', '2000100102'),('殷天正', '2000100103'),('韦一笑', '2000100104');

b.课程表：

代码：

create table tb_course
(id   int auto_increment primary key comment '主键ID',name varchar(10) comment '课程名称'
) comment '课程表';
​
insert into tb_course (name)
values ('Java'),('PHP'),('MySQL'),('Hadoop');

c.中间表：学生课程关系表

create table tb_student_course
(id         int auto_increment comment '主键' primary key,student_id int not null comment '学生ID',course_id  int not null comment '课程ID',constraint fk_courseid foreign key (course_id) references tb_course (id),constraint fk_studentid foreign key (student_id) references tb_student (id)
) comment '学生课程中间表';
​
insert into tb_student_course(student_id, course_id)
values (1, 1),(1, 2),(1, 3),(2, 2),(2, 3),(3, 4);

2.如果表与表建立了物理外键，那么可以通过可视化图形工具进行查看表之间的关系：

---