一.一对一关系概述:
例如:一位用户只能有一张身份证,一张身份证也只能对应一位用户
如果用户基本信息查询频率比用户身份信息查询频率高,为了提高效率,可拆分为两张表:
此时如何体现一对一的关系呢?其实一对一的关系可看成特殊的一对多的关系,一对多的关系只需要在多的一方添加外键即可,因此可以通过外键来体现一对一的关系,只需要在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(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);