目录
简单概述
在MySQL中，多表查询是指在一个查询中同时涉及多个表的操作。它可以帮助我们从多个表中检索相关数据，并将它们结合在一起进行分析和展示。
1.多表之间的关系
1.1 一对多/多对一
典型的案例就像我上一篇中所写的：MySQL基础篇-约束-CSDN博客
外键约束的案例
也就是利用外键约束来将表之间建立联系
1.2 多对多
案例：学生与课程之间的关系
关系：一个学生可以选修多门课程，一个课程可以供多名学生选择
建立关系：这时候就要建立一张中间表，中间表中至少包含二个外键，分别关联二张表的主键
创建对应的表
create table student(
id int auto_increment primary key ,
name varchar(10),
no varchar(10)
) comment '学生表';
insert into student values (null,'zs','2001010202'),(null,'ls','2001010202'),(null,'kk','2001010202'),(null,'ww','2001010202');
create table course(
id int auto_increment primary key ,
name varchar(10)
) comment '课程表';
insert into course values (null,'Java'),(null,'PHP'),(null,'C++');创建关系表
create table student_course(
id int auto_increment primary key ,
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 '学生课程中间表';此时学生表和课程表已经与其中的第三张表建立了联系
1.3 一对一
案例：用户与用户详情的关系
关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率。
这是一张用户的详情表
此时我们需要将用户的基本信息和受教育信息拆分出来，就可以使用这种一对一的关系来管理用户数据
在其中的一张表中使用约束外键将二者联系起来，这样就可以方便的管理了。
2. 多表查询-内连接
内连接返回的数据
使用内连接查询返回的数据必须要有强制条件那就是建立连接，二张表之间有外键约束建立了连接的数据才会返回。
2.1 隐式内连接
语法结构：
select * from 表名,表名 where 条件;这里有二张表，他们之间通过 ：dept_id 这个约束外键来建立了联系
现在要查询其中建立了联系且对应的数据
select spm.name,dept.name from spm,dept where spm.dept_id = dept.id;结果： 这样就可以查找到员工对应的部门了
扩展：通过上面的sql，可以看到where条件以及前面需要查询的字段，其中都需要涉及到
表名.某个字段，所以可以另起别名来简化
select s.name,d.name from spm s,dept d where s.dept_id = d.id;这个sql的运行结果跟上面的结果是一样的
2.2 显式内连接
语法：
select * from 表名1 INNER JOIN 表名2 ON 条件;
跟上面一样的需求，求出对应的员工的部门
select e.name,d.name from spm e inner join dept d on e.dept_id = d.id;2.3 内连接小结
显式内连接和隐式内连接的区别在于二点
一：语法不同，这个显而易见
二：sql代码可读性
总的来说，虽然两种方式都可以执行内连接操作，但显式内连接更为推荐，因为它具有更好的可读性和可维护性，能够清晰地展示查询中的连接关系，特别是在处理复杂查询时。而隐式内连接虽然有效，但容易让查询变得混乱和难以理解。因此，建议优先选择显式内连接的方式来编写SQL查询。
3.多表查询-外连接
外连接返回的数据
外连接和内连接返回的数据基本差不多，但是外连接返回的数据包括没有匹配上的数据，比如绑定的外键是NULL值，外连接也可以查询的到。
3.1 左外连接
返回的数据：
- 返回左表中的所有数据行，不论是否在右表中有匹配。
- 如果有匹配，将返回右表中匹配的数据行。
- 如果没有匹配，右表的列将包含NULL值。
- 左表中没有匹配的数据行仍然会包含在结果中。
语法：
select 字段 from 表名1 left join 表名2 on 条件;例如现在要查询所有的员工信息以及他们的部门信息
select s.*,d.name from spm s left join dept d on d.id = s.dept_id结果，可以即使是null，依然能够返回
3.2 右外连接
返回的数据：
- 返回右表中的所有数据行，不论是否在左表中有匹配。
- 如果有匹配，将返回左表中匹配的数据行。
- 如果没有匹配，左表的列将包含NULL值。
- 右表中没有匹配的数据行仍然会包含在结果中。
语法：
select 字段 from 表名1 right join 表名2 on 条件;没错，它跟左外连接只相差了一个单词
案例：现在我们要查询部门表的所有信息以及对应的员工信息
select d.*,s.* from spm s right join dept d on d.id = s.dept_id;结果，没有建立连接的则会返回null
4.多表查询-自连接
语法：
select 字段 from 表名1 join 表名2 on 条件;4.1 应用
自连接顾名思义就是自己连接自己，在一张表中通过某个字段来将其中的数据建立联系。
例如这种情况：
通过managerid字段，来查询到对应员工的上级
自连接的常见的应用场景：
-
组织结构：自连接可用于表示组织结构，例如公司的部门和子部门之间的层次关系。在一个表中存储部门信息，使用自连接可以轻松地查找部门的上级部门或子部门。
-
朋友关系：在社交网络或朋友关系管理系统中，自连接可用于查找用户之间的朋友关系。通过在同一用户表中存储用户信息，并使用自连接来建立用户之间的联系，可以轻松地查找用户的朋友或关注者。
-
评论和回复：在博客、论坛或社交媒体应用中，自连接可用于构建评论和回复的层次结构。每个评论可以与其父评论相关联，形成回复链。
-
员工管理：在员工管理系统中，自连接可用于表示员工与其直接上级的关系。这对于构建组织结构图以及查询员工的管理链非常有用。
-
产品和分类：自连接可以用于表示产品与其父产品或分类之间的关系。这在创建产品目录或层次结构时非常有用。
-
文件系统：自连接可以用于表示文件系统中的文件和文件夹之间的关系。每个文件夹可以包含文件和子文件夹，形成层次结构。
-
图形数据库：自连接是图形数据库中的常见操作，用于表示图形中节点之间的关系，例如社交网络图、地理信息系统等。
自连接在处理具有层次结构或复杂关系的数据时还是挺有作用的。它允许在同一表中查找相关数据，简化了数据检索和操作。在设计数据库模型时，考虑数据的层次结构和关系时，自连接是很有用的。
案例1：查询员工及其对应上级
select a.name ,b.name 上级 from spm a,spm b where a.managerid = b.id;结果
案例2：查询员工及其对应上级，没有上级的也要查询出来
这个时候需要查询没有关联的数据，要用到外连接
select a.name ,b.name 上级 from spm a left join spm b on a.managerid = b.id;结果
5.多表查询-联合查询
它用于合并两个或多个具有相同列结构的查询结果集，生成一个包含所有结果的单个结果集。联合查询通常用于将多个查询结果组合成一个单一的结果集，以便在应用程序中进行更方便的处理和显示。
案例：查询员工表中age<30 和 salary < 5000 的员工
select * from spm where age < 30
union all
select * from spm where salary < 5000;结果
可以看到游侠出现了2次，因为并没有做去重处理
select * from spm where age < 30
union
select * from spm where salary < 5000;将 all 去掉就可以达到去重的效果了
6.子查询
6.1 标量子查询
标量子查询的返回结果只包含单个值（数字，字符串，日期），而不是一组行或多列值。
常见的操作符：=, <>, >, >= , <, <=
案例：查询 “研发部” 的所有员工信息
select * from spm where dept_id = (select id from dept where name = '研发部')
其中括号内的sql返回结果为单一值
6.2 列子查询
列子查询返回的结果是一列（可以是多行）。
常用的操作符：IN （包含）, NOT IN , ANY （任一个）, SOME , ALL（所有的）
案例:查询研发部和财务部的所有员工信息
select * from spm where dept_id in (select id from dept where name = '研发部' or name = '财务部')
6.3 行子查询
行子查询返回的结果是一行（可以是多列）。
常见操作符：= ，<> , IN , NOT IN
案例：查询与 ‘游侠’ 的薪资和上级相同的员工信息
select * from spm where (salary,managerid) = (select salary,managerid from spm where name = '游侠') and name != '游侠';结果
7.小结
多表关系
- 一对多:在多的一方设置外键,关联一的一方的主键
- 多对多:建立中间表，中间表包含两个外键，关联两张表的主键
- 一对一:用于表结构拆分，在其中任何一方设置外键(UNIQUE)，关联另一方的主键
多表查询
- 内连接
- 隐式 where
- 显式 inner join ... on ...
- 外连接
- 左外 left join .... on ...
- 右外 right join ... on ...
- 自连接
- 子查询
5818
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
