多表查询1 - 建立多表关系

• 多表关系
• • 一对多
  • 多对多
  • 一对一

多表关系

一对多

e.g.e.g.e.g. 两个表，一个 emp 员工表，一个dept 部门表；
emp 中一个员工对应一个部门，但是 dept 中一个部门可以对应多个员工；

如何维护一对多（多对一）的多表关系？

通过建立 外键 ；在 ”多“ 的一方建立外键，关联 “一“ 的一方的主键

一对多（多对一）多表关系案例：

# 创建多的一方
create table emp(id int auto_increment primary key comment 'ID',name varchar(50) not null comment '姓名',age int comment '年龄',job varchar(20) comment '职位',salary int comment '薪水',entrydate date comment '入职日期',managerid int comment '直属领导',dept_id int comment '部门ID'
) comment '员工表';# 创建一的一方
create table dept(id int auto_increment primary key comment 'ID',name varchar(50) not null comment '部门名称'
) comment '部门表';

# 插入数据 - 多的一方
insert into emp (name,age,job,salary,entrydate,managerid,dept_id) values ('老夏', 23, '总经理', 200000, '2022-01-10', 1,1),('老张', 24, 'CTO', 200000, '2023-01-01', 1, 2),('老徐', 23, 'CEO', 200000, '2020-01-01', 1, 3),('员工甲', 25, '产品研发', 200000, '2022-04-10', 1,2),('员工乙', 27, '产品测试', 200000, '2023-06-01', 1,2);# 插入数据 - 一的一方
insert into dept values (1, '研发部'),(2, '产品部'),(3, '董事办');

# 通过外键维护多表关系
alter table emp add constraint fk_emp_empt_id foreign key (dept_id) references dept(id);

多对多

e.g.e.g.e.g. 两个表，一个 student 学生表，一个 course 课程表；
student 表中一个学生可以选择多门课程，而 course 表中一个课程包含多名学生；

如何维护多对多的多表关系？

通过建立 中间表 ；中间表至少包含两个外键，一个关联 student 表的主键，另一个关联 course 表的主键；通过中间表，我们就可以维护学生与课程之间的关系。

多对多多表关系案例：

# 创建多表 student
create table student (id int auto_increment primary key comment '主键ID',name varchar(10) comment '姓名',no varchar(10) comment '学生号'
) comment '学生表';# 创建多表 course
create table course (id int auto_increment primary key comment '主键ID',name varchar(10) comment '课程名称'
) comment '课程表';

# 插入到多表 student 中
insert into student values (null, '老张', 'S220802'),(null, '老夏', 'S225832'),(null, '老徐', 'S220831');# 插入到多表 course 中
insert into course values (null, 'java'),(null, 'python'),(null, 'php'),(null, 'hadoop');

# 创建中间表 student_course
create table student_course (id int auto_increment primary key comment '主键',studentid int not null comment '学生id',courseid int not null comment '课程id',constraint fk_courseid foreign key (courseid) references course(id),constraint fk_studentid foreign key (studentid) references student(id)
) comment '学生课程中间表';

一对一

一对一关系多用于单表拆分，就是一个表忒长了，操作效率很低，那我就拆分成为多张表

如何维护一对一的多表关系？

通过在任意一方增加 外键 ，关联另一方的主键，并设置外键为唯一的（UNIQUE）

一对一多表关系案例：

# 建立表 tb_user
create table tb_user (id int auto_increment primary key comment '主键ID',name varchar(10) comment '姓名',age int comment '年龄',gender char(1) comment '性别',phone char(11) comment '手机号'
) comment '用户基本信息表';# 建立表 tb_user_edu
create table tb_user_edu (id int auto_increment primary key comment '主键ID',degree varchar(20) comment '学历',major varchar(20) comment '专业',userid int unique comment '用户ID',constraint fk_userid foreign key (userid) references tb_user(id)
) comment '用户教育信息表';

# 插入数据到表 tb_user
insert into tb_user values(null, '老张', 24, '男', '123456789'),(null, '老杜', 23, '男', '123232789'),(null, '老徐', 24, '男', '12111789'),(null, '老夏', 25, '男', '12346669');# 插入数据到表 tb_user_edu
insert into tb_user_edu values(null, '本科', '计算机科学与技术', 1),(null, '博士', '机器学习', 2),(null, '研究生', '人工智能', 3),(null, '本科', '计算机科学与技术', 4);