🔥博客主页： 【小扳_-CSDN博客】
❤感谢大家点赞👍收藏⭐评论✍
文章目录
1.0 多表设计概述
1.1 多表设计 - 一对多
1.2 多表设计 - 一对一
1.3 多表设计 - 多对多
2.0 多表查询概述
2.1 多表查询 - 内连接
2.2 多表查询 - 外连接
2.3 多表查询 - 子查询
1.0 多表设计概述
多表设计是指在数据库中将数据分散存储在多个表中的设计方法。这种设计方法通常用于将数据按照不同的实体或属性进行划分，以便更好地组织和管理数据。
在多表设计中，不同的表之间通常会通过外键来建立关联关系，从而实现数据之间的引用和关联。这种设计方法有助于减少数据冗余、提高数据的一致性和完整性，并且可以更好地支持数据的查询和分析。
总的来说，为了数据在表中更好的管理，将数据拆分到不同的表中。而表与表之间通过外键来建立联系。
多表设计的类型主要分为：一对多、一对一、多对多。
1.1 多表设计 - 一对多
在数据库设计中，一对多关系指的是一个实体在另一个实体中有多个关联记录的关系。通常使用外键来实现一对多关系。假设我们有两个实体 A 和 B ，A 实体可以有多个关联的 B 实体记录，而B实体只能关联一个 A 实体记录。
外键语法：
-- 创建表时指定 create table 表名( 字段名 数据结构, ... [constraint] [外键名] foreign key(外键字段名) references 主表(字段名) ); -- 建完表后，添加外键 alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(字段名);
举个例子，部门与员工的关系，一个部门可以有多个员工，而一个员工只能属于一个部门，这就构成了一对多的关系，为了建立部门与员工的关系，则在员工表中添加外键即可。
代码如下：
create table department( id tinyint unsigned primary key comment '序号', name varchar(10) not null comment '部门名称', last_time datetime not null comment '最后的操作时间' )comment '部门表'; create table employee( id tinyint unsigned primary key comment 'id号', name varchar(10) not null comment '名字', department_id tinyint unsigned comment '部门号', last_time datetime not null comment '最后的操作时间' )comment '员工表'; -- 添加外键约束 alter table employee add foreign key (department_id) references department(id); -- 添加部门表中的数据 insert into department values (1,'学工部',now()),(2,'教研部',now()),(3,'教学部',now()),(4,'后勤部',now()); -- 添加员工表中的数据 insert into employee values (1,'张三',1,now()),(2,'李四',2,now()),(3,'王五',2,now()),(4,'赵六',4,now());部门表：
员工表：
这两个表已经建立了物理联系，通过外键来建立物理上的联系是为了保证数据的一致性和完整性。
比如，现在要删除部门表中的 '教研部' 数据，代码如下：
delete from department where id = 2;执行结果如下：
由于添加了外键联系，为了确保数据的一致性和完整性，所以影响该操作失败。
而对与删除员工表中的数据则可以删除成功，代码如下：
delete from employee where name = '王五';执行结果如下：
物理外键：
使用 foreign key 定义外键关联另外一张表。但是会影响增、删、改的效率(因为需要检查外键关系)、仅用于单节点数据库，不适用与分布式、集群场景、容易引发数据库的死锁问题、消耗性能。所以我们一般建立表与表之间的逻辑外键联系，而不建立物理外键联系。
关于在一对多关系中在哪一个表中添加外键：
简单粗暴的说，在一对多中，代表多的表需要添加外键，一个员工表与一个部门表，显然员工表是代表多的一方，部门表代表少的一方。因为一个部门有很多员工，而一个员工只能属于一个部门。
1.2 多表设计 - 一对一
在数据库中，一对一关系是指两个实体之间存在一种一对一的关联关系。这种关系通常通过在两个表之间共享一个相同的主键来实现。
在任意一方加入外键，关联另外一个的主键，并且设置外键为唯一的 unique 。
举个例子，用户与身份证信息的关系，一对一关系，用于单表的拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率。
代码如下：
create table user( id tinyint unsigned primary key comment 'id号', name varchar(10) not null comment '名字', phone varchar(11) comment '电话号码', degree varchar(10) comment '学历', birthday date comment '出生日期' )comment '用户表'; create table user_id_card( id_card varchar(18) primary key comment '身份证号码', issued varchar(10) not null, fk_id tinyint unsigned, constraint fk foreign key (fk_id) references user(id) )comment '用户id表'; -- 添加数据 insert into user values (1,'鹰王','18812340001','初中','1960-11-06'); insert into user values (2,'辐王','18812340002','高中','1961-11-06'),(3,'龙王','18812340003','高中','1962-11-06'); insert into user_id_card values (100000000010000011,'朝阳',1),(100000000010000022,'西阳',2),(100000000010000033,'东阳',3);用户ID表：
用户表：
通过物理外键已经建立好了两个表的联系了。
关于在一对一关系中在哪一个表中添加外键：
在一对一中，任意一个表中都可以添加外键，任选一个表即可。
1.3 多表设计 - 多对多
在多表设计中，多对多关系通常需要使用一个中间表来实现。这种中间表包含两个外键，分别指向参与关系的两个表。这样就可以实现多对多关系的表示。
举个例子，学生与课程的关系，一个学生可以选修多门课程，一门课程也可以供多个学生选择。
代码如下：
create table student( id tinyint primary key comment 'id号', name varchar(10) not null comment '名字', no varchar(20) comment '学号' )comment '学生表'; create table course( id tinyint primary key comment 'id号', name varchar(10) not null unique comment '课程名' )comment '课程表'; create table course_student( id tinyint primary key comment 'id号', student_id tinyint comment '外键id号', course_id tinyint comment '外键id号', constraint fk_s foreign key (student_id) references student(id), constraint fk_c foreign key (course_id) references course(id) )comment '学生与课程的中间表'; -- 添加数据 insert into student values (1,'张三',2002350101),(2,'李四',2002350102),(3,'王五',2002350103); insert into course values (1,'Java'),(2,'PHP'),(3,'MySQL'); insert into course_student values (1,1,1),(2,1,2),(3,1,3),(4,2,1),(5,2,3),(6,3,2);课程表：
中间表：
学生表：
小结：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。
2.0 多表查询概述
多表查询是指在数据库中同时查询多个表的数据的操作。通过多表查询，可以根据不同表之间的关联关系，将数据连接起来。
多表查询的方式：内连接、外连接、子查询。
2.1 多表查询 - 内连接
内连接是一种多表查询的方式，用于检索两个或多个表中满足连接条件的数据。内连接只返回满足连接条件的行，即两个表中的数据必须在连接条件下匹配才会被检索出来。
内连接语法：
-- 隐式内连接 select 字段列表 from 表1,表2 where 条件 ...; -- 显示内连接 select 字段列表 from 表1 [inner] join 表2 on 链接条件 ...;
显示内连接更推荐使用，因为它提供了更清晰的语法结构和更好的可读性。隐式内连接虽然在一些情况可以简化语句，但是容易造成混淆和错误，不易维护。但是两者的效果是一样的。
2.2 多表查询 - 外连接
在数据库中，多表查询可以通过外连接来实现。外连接是一种连接操作，用于检索两个或多个表中的数据，即使其中一个表中的数据在另一个表中没有匹配项也可以检索出来。在外连接中，常见的类型有左外连接、右外连接。左外连接会返回左表中的所有数据，即使右表中没有匹配项；右外连接会返回右表中的所有数据，即使左表中没有匹配项。
外连接语法：
-- 左外链接 select 字段列表 from 表1 left [outer] join 表2 on 连接条件 ...; -- 右外链接 select 字段列表 from 表1 right [outer] join 表2 on 连接条件 ...;
左外链接与右外链接可以相互转换的，一般习惯用左外链接方式来查询多表。
2.3 多表查询 - 子查询
在数据库中，多表查询是指从多个表中检索数据的操作。而子查询是指在一个查询中嵌套另一个查询的操作。所以子查询也称为嵌套查询。
可以具体分为：
标量子查询：子查询返回的结果为单个值。
列子查询：子查询返回的结果为一列。
行子查询：子查询返回的结果为一行。
表子查询：子查询返回的结果为多行多列。
返回的多行多列就是一个表，常作为临时表，常用的操作符：in 。
