【MySQL基础】MySQL多表操作详解

序号系列文章4【MySQL基础】MySQL表的七大约束5【MySQL基础】字符集与校对集详解6【MySQL基础】MySQL单表操作详解7【MySQL基础】运算符及相关函数详解

文章目录

前言

大家好，我是小杨！前面我为大家介绍了MySQL中的单表操作，那么今天我将详细的为大家介绍MySQL中的多表相关知识，希望大家能够从中收获多多！

MySQL多表操作

1，多表关系

1.1，一对一

概念：实体集A至少和实体集B中的一个实体有联系，反之亦是，则称实体集A和实体集B具有一对一联系，记为1：1。

实例：假设一个部门只能有一个负责人，每个负责人只能负责一个部分，则部门与负责人两个实体之间就是一对一关系。

关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以此提升操作效率。

实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)。

1.2，一对多

概念：实体集A中的每个实体与实体集B中的任意多个实体有联系，而实体B中每个实体集最多与实体集A的一个实体有联系，则称之为实体集A与实体集B具有一对多联系，记为1：n。

实例：一个员工只能属于一个部门，而一个部门却可能有多个员工，则部门实体集与员工实体集之间是一对多关系。

实现：在多的一方建立外键，指向一的一方的主键，即对员工表设置外键，对部门表设置主键。

1.3，多对多

概念：实体集A中的每个实体与实体集B中的任意多个(含0个或多个)实体有联系，而实体集B中的每个实体与实体集A中的任意多个实体有联系，则称实体集A与实体集B具有多对多联系，记为n：n。

实例：一个学生可以选多门课程，而每个课程可以被多个学生选，则学生与课程两个实体之间是多对多关系。

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方的主键，即根据学生表和课程表建立中间表，中间表关联两表。

实际效果图：

2，多表查询

2.1，交叉查询

在MySQL中，交叉查询是一种结合两张或多张表中所有记录的查询方式。它通常使用CROSS JOIN关键字来实现。

交叉查询会返回两个表中所有记录的笛卡尔积，这就意味着每一行记录在第一个表中都将与在第二个表中的所有记录匹配。

例如，如果表A有3条记录，3个字段和表B有4条记录，2个字段，那么交叉查询会返回12条记录。每条记录5个字段。

SELECT 查询字段 FROM 表1名称 CROSS JOIN 表2名称;

为了更好理解交叉查询，现对部门表和职工表进行交叉查询：

操作说明：emp职工表的每条记录都会与dept部门表中的所有记录进行匹配，结果集是两张表的记录数相乘。

因此需要注意：使用交叉查询时需要特别小心，因为如果两个表中含有大量记录，结果集可能会非常大，导致性能问题。

扩展：笛卡尔积的示意图

笛卡尔积是指在第一个表中的每条记录都将与在第二个表中的所有记录相匹配。

2.2，内连接

内连接在MySQL中是一种常见的连接查询，它会根据匹配条件返回第一个表和第二个表所有匹配成功的记录。

内连接的语法可分为两种: 隐式内连接、显式内连接。

1，隐式内连接

隐式内连接，也叫等值连接，是通过在查询中使用WHERE子句来指定连接条件来实现的。这种方式的语法是：

SELECT 字段列表 FROM 表1名称,表2名称 WHERE 条件;

为了更好地理解隐式内连接，实例：查询每个员工的信息及所在部门信息

操作说明：因总裁小美的dept_id为NULL值，故无法在dept中找到相对应的部门，其余员工均能找到对应的部门，若有员工的dept_id在dept表中找不到时，也不会将其信息打印出来，只会将相互匹配(即emp.dept_id=dept.id)的数据信息打印出来。

为了更好地查看每个员工的基本信息和所在部门，根据指定字段进行查询：

2，显性内连接

显式内连接，也叫非等值连接，是通过在查询中使用JOIN关键字和ON子句来指定连接条件来实现的。这种方式的语法是：

SELECT 字段列表 FROM 表1名称 [INNER] JOIN 表2名称 ON 匹配条件;

为了更好地理解显式内连接，实例：查询每个员工的信息及所在部门信息

3，隐式内连接和显式内连接的对比

从上述对比中，显式内连接这种写法可以清晰地表明该查询需要连接哪两个表，以及连接条件是什么，而隐式内连接表明的不太明显。

因此，在日常使用中建议使用显式内连接的方式，因为它可以清晰地表明连接操作和连接条件，更易于理解和维护。

2.3，外连接

外连接可分为两种，分别是：左外连接 和 右外连接。

1，左外连接

左外连接用于返回连接关键字（LEFT JOIN）左表中所有的记录，以及右表中符合连接条件的记录。

当左表的某行记录在右表中没有匹配的记录时，右表中相关的记录将会被设为空值NULL。

SELECT 查询字段 FROM 表1名称 LEFT [OUTER] JOIN 表2名称 ON 匹配条件;

关键字左边的表1称为左表，也称之为主表；右边的表2称为右表，也称之为从表；OUTER在查询时可省略。

为了更好地理解左外连接，实例：查询emp表的所有数据, 和对应的部门信息

操作说明：对职员表emp(主表)和部门表dept(从表)进行左外连接，emp表中的每条记录都会与dept表中的记录进行匹配，当左表的某行记录在右表中没有匹配的记录时，右表中相关的记录将会被设为空值NULL。

2，右外连接

右外连接用于返回连接关键字（RIGHT JOIN）右表（主表）中所有的记录，以及左表（从表）中符合连接条件的记录。

当右表的某行记录在左表中没有匹配的记录时，左表中相关的记录将会被设为空值NULL。

SELECT 查询字段 FROM 表1名称 RIGHT [OUTER] JOIN 表2名称 ON 匹配条件;

关键字左边的表1称为左表，也称之为从表；右边的表2称为右表，也称之为主表；OUTER在查询时可省略。

为了更好地理解右外连接，实例：查询dept表的所有数据, 和对应的员工信息

操作说明：对职员表emp(从表)和部门表dept(主表)进行右外连接，dept表中的每条记录都会与emp表中的记录进行匹配，当右表的某行记录在左表中没有匹配的记录时，左表中相关的记录将会被设为空值NULL。

2.4，自连接

自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。

SELECT 字段列表 FROM 表1名称 表1别名 JOIN 表2名称 表2别名 ON 条件 ... ;

为了更好地理解自连接，实例：查询员工及其所属领导的部分信息

操作说明：在自连接查询时，根据职员表emp中的id字段值与managerid字段值进行查询，符合条件的记录就打印出来，不符合条件的记录就不打印；在自连接时，对操作表进行取别名操作，因为是相同的表进行连接查询，若不取别名，无法分辨和进行查询。

为了更好地查看查询的员工及其所属领导的信息，根据指定字段进行查询

注意事项：在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段。

2.5，联合查询

联合查询是多表查询的一种方式，在保证多个SELECT语句的查询字段数相同的情况下，合并多个查询的结果。

SELECT 字段列表 FROM 表名称1 ...
UNION [ ALL | DISTINCT ]
SELECT 字段列表 FROM 表名称2 ...

在上述语法中，UNION是实现联合查询的关键字，ALL和DISTINCT是联合查询的选项。

ALL表示保留所有的查询记录；DISTINCT是默认值，表示去除完全重复的记录。

为了更好地理解联合查询，实例：查询薪资低于 8000 和 高于15000的员工信息

操作说明：先分别进行查询薪资高于15000和薪资低于8000的员工信息，再通过联合查询的方式进行查询，对比查询结果。

联合查询特殊情况：查询的字段数相同但字段不同

操作说明：先查询薪资高于15000的员工id，name，job，salary这些信息，再查询低于8000的员工的id，name，job，salary这些信息，然后再通过联合查询进行查询，最终查询结果只保留第一个SELECT语句对应的字段名称，且第二个查询的字段虽与第一个查询的字段不匹配，但也会根据字段出现顺序对结果进行合并。

结论：SELECT查询的字段个数必须相同，且联合查询的结果只保留第一个SELECT语句对应的字段名称。

即使UNION后的SELECT查询的字段与第一个SELECT查询的字段表达含义或数据类型不同，也仅会根据字段出现顺序对结果进行合并。

若要对联合查询的记录进行排序等操作，需要用圆括号包括每一个SELECT语句，在SELECT语句内或者在联合查询的最后添加ORDER BY语句。若想要排序生效，必须在ORDER BY后添加LIMIT限定联合查询排序的数量，通常推荐使用大于表记录数的任意值。

3，子查询

3.1，子查询概述

1，子查询

子查询指的是在一个SQL语句A（SELECT、INSERT、UPDATE等）中嵌入一个查询语句B，作为执行的条件或者是查询的数据源（代替FROM后的数据表），这里的查询语句B就是子查询语句，它是一条完整的SELECT语句，能够独立的执行。

其实对子查询的理解，可以简单理解归纳为以下两点：

1，SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

2， 子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。

2，子查询的执行流程

在含有子查询的语句中，子查询必须书写在圆括号( )内。

1，SQL语句首先会执行子查询中的语句。

2， 然后再将返回的结果作为外层SQL语句的过滤条件。

3，当遇到同一个SQL语句中含有多层子查询时，执行顺序是从最里层的子查询开始执行。

3，子查询分类

子查询的划分方式有多种，最常见的是以功能和位置进行划分。

1，按子查询的功能可以将其分为标量子查询、列子查询、行子查询和表子查询。

2，按子查询出现的位置可以将其分为WHERE子查询和FROM子查询。

标量子查询、列子查询和行子查询都属于WHERE子查询，而表子查询属于FROM子查询。

3.2，标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。

常用的操作符：= 、<> 、> 、 >= 、 < 、<=

SELECT 字段列表 FROM 表名称 WHERE 条件判断  {=|<>}
(SELECT 字段名 FROM 数据源 [WHERE] [GROUP BY] [HAVING] [ORDER BY] [LIMIT]);

为了更好地理解标量子查询，实例：查询 “研发部” 的所有员工信息

1，基本查询，分两步进行

2，标量子查询

操作说明：标量子查询是将基本查询的两步操作合并在一起，将基本查询的第1步查询结果作为判断条件进行第2步查询。

总结：标量子查询的流程为：先将子查询的结果集与指定条件进行比较，然后根据比较结果完成相对应的操作。

3.3，列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。

常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL

SELECT 字段列表 FROM 表名称 WHERE 条件判断 {IN|NOT IN}
(SELECT 字段名 FROM 数据源 [WHERE] [GROUP BY] [HAVING] [ORDER BY] [LIMIT]);

为了更好地理解列子查询，实例：查询 “财务部” 和 “市场部” 的所有员工信息

1，基本查询，分两步进行：

2，列子查询：

操作说明：列子查询是将基本查询的两步操作合并在一起，将基本查询的第1步查询的结果集作为判断条件进行第2步查询。

总结：列子查询的流程为先判断指定的条件是否在子查询语句返回的结果集中，然后根据比较结果完成相关需求的操作。

3.4，行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。

常用的操作符：= 、<> 、IN 、NOT IN

SELECT 字段列表 FROM 表名称WHERE (指定字段名1,指定字段名2,…) = 
(SELECT字段名1,字段名2,… FROM 数据源 [WHERE] [GROUP BY] [HAVING] [ORDER BY] [LIMIT]);

1，行子查询返回的一条记录与指定的条件比较，比较的运算符通常使用

=

。

2，子查询的结果必须全部与指定的字段相等才满足WHERE指定的条件。

为了更好地理解行子查询，实例：查询与 “小代” 的部门及直属领导相同的员工信息 ;

1，基本查询，分两步进行：

2，行子查询

总结：行子查询的流程为先判断指定的条件是否在子查询返回的结果集中或对其比较，然后根据比较结果完成相关需求的操作。

3.5，表子查询

子查询返回的结果用于FROM数据源，它是一个符合二维表结构的数据，可以是一行一列、一列多行、一行多列或多行多列。这种子查询称为表子查询。

SELECT 字段列表 FROM (SELECT语句) [AS] 别名
[WHERE] [GROUP BY] [HAVING] [ORDER BY] [LIMIT];

为了更好地理解表子查询，实例：查询与 “小代”，“小花” 的部门及直属领导相同的员工信息

1，基本查询，分两步进行：

2，表子查询

总结：表子查询的流程为：先将子查询的结果集作为数据源，作为表使用，然后再对该数据源根据需求进行相对应的操作。

注意：FROM后的数据源都是表名，但当数据源是子查询时，必须为其设置别名，同时也是为了将查询结果作为一个表使用时，可以进行条件判断、分组、排序以及限量等操作。

4，子查询关键字

关键字说明IN在指定的集合范围之内，多选一NOT IN不在指定的集合范围之内EXISTS判断查询子句是否有记录ANY子查询返回列表中，有任意一个满足即可SOME与ANY等同，使用SOME的地方都可以使用ANYALL子查询返回列表的所有值都必须满足
1，带ANY、SOME、ALL关键字的子查询，不能使用

<=>

比较运算符。

2，若子查询结果与条件匹配时有NULL，那么此条记录不参与匹配。

4.1，EXISTS关键字

在SQL语句中使用带EXISTS关键的子查询时，表示判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。

WHERE EXISTS(子查询语句);

NOT EXISTS判断子查询的结果，当没有返回结果时则返回1，否则返回0。

WHERE NOT EXISTS(子查询语句);

为了更好地理解EXISTS关键字，实例：在存在薪资高于12000的情况下，查询薪资高于15000的员工信息

4.2，ANY|SOME关键字

在SQL语句中使用带ANY关键的子查询时，表示给定的判断条件，只要符合ANY子查询结果中的任意一个，就返回1，否则返回0。

WHERE 表达式 比较运算符 ANY(子查询语句);

WHERE 表达式 比较运算符 SOME(子查询语句);

为了更好地理解ANY|SOME关键字，实例：查询比研发部其中任意一人工资高的员工信息

小知识：SOME关键字添加原因

MySQL在设计时添加SOME的原因在于，英文语法中虽然SOME和ANY的语法含义相同，但是NOT ANY和NOT SOME的含义区别很大。

前者NOT ANY表示一点也不，相当于NOT ALL，而后者NOT SOME仅用于否定部分内容。

因此，为了便于以英文为母语的开发者理解，设计出了带SOME和ANY关键字的子查询。

4.3，ALL关键字

在SQL语句中使用带ALL关键字的子查询时，表示给定的判断条件，只有全部符合ALL子查询的结果时，才返回1，否则返回0。

WHERE 表达式 比较运算符 ALL(子查询语句);

为了更好地理解ALL关键字，实例：查询比研发部所有人工资都高的员工信息

结语

这就是本期博客的全部内容啦，想必大家已经对MySQL中的多表操作的相关知识有了全新地认识和理解吧，如果有什么其他的问题无法自己解决，可以在评论区留言哦！

最后，如果你觉得这篇文章写的还不错的话或者有所收获的话，麻烦小伙伴们动动你们的小手，给个三连呗（点赞👍，评论✍，收藏📖），多多支持一下！各位的支持是我最大的动力，后期不断更新优质的内容来帮助大家，一起进步。那我们下期见！