1.20.MySQL-多表设计 - 一对一&多对多

一.一对一关系概述：

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

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

此时如何体现一对一的关系呢？其实一对一的关系可看成特殊的一对多的关系，一对多的关系只需要在多的一方添加外键即可，因此可以通过外键来体现一对一的关系，只需要在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(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.如果表与表建立了物理外键，那么可以通过可视化图形工具进行查看表之间的关系：

---