theme: smartblue
Nodejs 第三十七章 MySQL子查询和连表
- 在之前的各种案例中,我们是在一张表中进行增删改查的,但正常的开发中,是不会把所有的字段都定义在一张表里的。
- 因为如果所有字段都在一张表里的话,这跟所有代码都在一个文件里面有什么区别?
- 我们在第九章的时候就已经学到了模块化的思想了,这同样是能够借鉴到数据库的设计当中的。所以我们会根据不同的性质与功能的字段,将表分门别类,使其结构更加清晰
- 但在前端的代码中,我们可以通过ESM或者CommonJS两种导入导出规范,将各个文件链接为一个整体项目
- 那MySQL的表又该如何去进行将各种表联系起来呢?
- 而这就是我们今天的主题,子查询和连表
子查询
什么是子查询
在MySQL中,子查询(Subquery)是嵌套在另一个SQL查询中的查询。子查询可以用在各种SQL语句中,包括SELECT
、INSERT
、UPDATE
、和DELETE
语句之中,以及用在FROM
、WHERE
、和HAVING
子句中。子查询允许我们在一个查询内部执行另一个查询,让SQL查询具有更大的灵活性和复杂性。
- 简单的说,就是为了我们一开始所说的,将表与表之间
单方面
的联系起来。- 跟前端模块化导出导入是一样的,嵌套在主表中的子表是没办法逆向查询到主表的内容,因为我们的目的是让子表为主表服务(PS:主表和子表的概念是我为了形象化起的比喻,MySQL本身没有这个概念),而是子查询为主查询所利用
- 子查询像是一个被导入的模块,它为主查询(即使用它的模块)提供数据或功能,但主查询不能直接操作子查询的内部,并且子查询内部的数据处理对主查询是不可见的,只负责提供数据这一件事
- 而为什么用子查询,我们在一开头的介绍中也进行了阐述,践行模块化的思想,也是为了可读性、理解容易、维护方便等因素
子查询案例
- 我们先来理清思路(分两步):
- 首先需要有两张表,分别对应主查询(主表)和子查询(子表)
- 将这两张表建立起子查询关联
创建表
- 首先,开始我们的第一步,这里我们创建一张用户表和一张记录用户登录活动的子表
users
表作为主表,存储用户的基本信息;login_activities
表作为子表,通过外键与users
表的id
关联,记录用户的登录活动
主查询表
-- 创建主表 `users` CREATE TABLE `users` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `email` varchar(255), PRIMARY KEY (`id`) );
– 插入数据到
users
INSERT INTOusers
(name
,
VALUES
(‘迷你余’, ‘mini@gmail.com’),
(‘小余’, ‘small@gmail.com’),
(‘中余’, ‘medium@gmail.com’),
(‘大余’, ‘large@gmail.com’),
(‘超大余’, ‘xlarge@gmail.com’);
子查询表
- 在这里初始化数据的时候,通过查询user表(主查询表)获取到了
主键id
,我们将其填充进了login_activities
表中作为外键id
- 目的是为了接下来建立两个表的关联关系
- 批量操作的做法会让插入效率更高
-- 创建子表 `login_activities` CREATE TABLE `login_activities` ( `activity_id` int NOT NULL AUTO_INCREMENT, `user_id` int NOT NULL, `login_time` timestamp DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`activity_id`), FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) );
– 插入数据到
login_activities
– 假设每个用户都登录了至少一次
INSERT INTOlogin_activities
(user_id
)
SELECTid
FROMusers
;
建立查询关联
- 在这里我们要掌握一个概念
- 子查询中的括号:在SQL中,子查询通常需要用括号包围,以区分子查询和主查询的边界
- 假如我们想查询所有的有登录活动的用户的名字(也许用来筛选活跃用户和清僵尸粉是个不错的选择)
- 在如下的写法中,我们用到了
IN关键词
,IN
可以替代多个OR
条件。意思就是说IN(xxx,xxx,...)中的括号内只需满足其中一点即可。通常的用法是配合WHERE
进行筛选使用
- 在如下的写法中,我们用到了
SELECT `name` FROM `users`
WHERE `id` IN (
SELECT `user_id` FROM `login_activities`
);
-
这里我们需要分成两个部分来看:
- 这第一部分中,是主查询中的部分,用来查询name数据
SELECT `name` FROM `users`
-
第二部分就是我们的功能精髓了,在这个案例中,我们要查询到最近至少有登录过一次的用户
- 此时在第一部分中我们拿到了所有的用户名
- 而通过子查询(子表),我们拿到了在今年劳动节有登录过的用户名的名单
SELECT `name` FROM `users` WHERE `id` IN ( SELECT `user_id` FROM `login_activities` WHERE `login_time` >= '2024-05-01' );
-
通过上述的写法,我想大家应该明白了。子查询其实并不是一个具体的规范写法,而是一种思想体现。
- 我们将子查询拆分成了两个步骤,其实单独来看,不管是哪一个步骤,都是正常普通的操作(查询姓名,查询登录时间)
- 如果在我们前面全部都写在一个表的做法,是完全不需要这个概念的。正是因为结构清晰的模块化思想带来的诸多好处,才促使了表的功能分类。而子查询的概念则是基于此思想基石土壤得以发展的,所以我们才说他的一种思想体现。
- 就操作而言,确实是很普通的写法,具备价值的是背后所代表的思想,学习要理解其背后的设计哲学
-
我们不能够说:我用子查询就是因为这样才能达到我想要实现的功能。这样的理解是有问题的,因为这样做才能达到目的的原因是已经建立在将表分离的基础上了,那就表示你已经认可了结构化思想的好处(提升数据库查询的灵活性和效率),后面的子查询只不过是水到渠成的解决方式,思维延伸
连表查询
- 日常开发工作中的应用场景有很多,如果说我想要结合两张表的内容的话,直接单纯的使用
子查询
是不够的。- 我们就拿刚刚在子查询中的案例举例,我通过子查询拿到了五一后活跃的用户名。但我没看到具体的最近一次登录时间欸?
- 我如果此时想要在一张表里同时看到这两样内容(
用户名,登录时间
),也就是合并两表内容,依靠子查询自身是做不到的,因为用户名
和登录时间
这两样数据不在同一张表里,他们分别来自主查询表和子查询表
- 这时候我们的
连表查询
就该出马了,他可以解决这个问题,而连表查询又分为内连接
和外连接
以及交叉连接
。让我们来看看吧
内连接
什么是内连接
内连接返回两个表中匹配的行。如果在连接的列上存在匹配值,那么这些行就会出现在查询结果中。
内连接的使用
- 通过对内连接的简单介绍其实还是略显抽象的,我们来看下如何做的吧
SELECT * FROM `users`,`login_activities`
- 通过了初步的同时查询两个表,我们能看到两个表的内容全部结合了起来
- 但好像出了一点问题,总共的突然变成25条数据了,但我们只有5条才对
- 因为我们没有加上WHERE限制条件,两个表的数据没有一一对应起来了,而是5x5的进行结合,生成了25条不同的数据
- 而内连接就是在此基础上,在WHERE中通过
主键和外键
实现了数据一一对应的效果- 就操作而言,也还是朴实无华,就主外键相互对比一下。但还是那句话,思想才是关键
#users的主键id和login_activities的外键id对应
SELECT * FROM `users`,`login_activities` WHERE `users`.`id` = `login_activities`.`user_id`
- 目前看起来好像一切都很好,虽然内容是多了点,但只需要把命令中的
*通配符
换成我们想要的对应部分内容就行
SELECT `name`,`login_time` FROM `users`,`login_activities` WHERE `users`.`id` = `login_activities`.`user_id`
- 但其实内连接还是有一个问题,我们的数据并不是一直都可以完全匹配上的,比如说用户表有12条数据,而登录表可能只有8条数据
- 此时去进行内连接,就只会展现出他们共有的8条数据,剩下的4条数据就不会显示出来。那这样看来这个表可能就不够全面
- 我要是想要实现:所有用户名都显示出来,最近有登录的用户名显示时间,没有的就NULL值显示。内连接就无法做到这一点,它就像交集一样
外连接
什么是外连接
外连接返回至少一个表中的所有行,即使在另一个表中没有匹配。根据是左外连接、右外连接还是全外连接,其行为略有不同
- 而外连接又有左右之分,而左右外连接是有专门的写法的
左外连接
返回左表(table1
)的所有行,以及右表(table2
)中匹配的行。如果右表中没有匹配,这些行将以空值填充
- 写法:
LEFT JOIN [表名] ON [连接的条件]
FROM
后面引用的表的顺序也是由不同作用的- 第一个表叫做驱动表,以这个表为主
- 为什么是第一个表?因为它在
LEFT JOIN
的左边,左连接就是以左为主。如果是右外连接的话,右边就是驱动表
- 为了方便演示,我将登录表删掉一条信息
DELETE FROM `login_activities` WHERE `user_id` = 5
-
此时登录表就4条信息,用户表有5条,我们就以用户表为驱动表来进行。看在内连接中,另一个表中没有的数据,无法显示的情况是否还在
-
在之前我们筛选条件是使用
WHERE
关键词,在左右外连接中,是采用ON
关键词。这两者有什么不同?ON 关键词
- 用途:
ON
关键词主要用于连接查询中,指定连接条件,即定义如何匹配来自两个表的行。 - 上下文:
ON
是在JOIN
语句中使用,用来指明如何通过比较两个表中的列来连接行。
WHERE 关键词
- 用途:
WHERE
关键词用于指定过滤条件,即决定哪些行应该包括在查询结果中。 - 上下文:
WHERE
可以用在任何SELECT查询中,不仅限于连接查询。它是在数据连接后进行过滤的,因此可以在JOIN
之后使用WHERE
进一步限定结果。
- 用途:
# 在进行表的连接时,特别是在外连接中,使用ON来定义连接条件是非常重要的,因为这直接影响到哪些行会被包括在最终的连接结果中。ON条件确定了如何匹配两个表的数据,这是构建连接结果集的基础。
SELECT `name`,`login_time` FROM `users` LEFT JOIN `login_activities` ON `users`.`id` = `login_activities`.`user_id`
- 能够明显的看到,第五条数据也显示出来了。登录表中没有的数据就以
NULL空值
表示
右外连接
返回右表(
table2
)的所有行,以及左表(table1
)中匹配的行。如果左表中没有匹配,这些行将以空值填充
- 正如前面所说,如果是右外连接的话,右边就是驱动表
- 简单的把上面
左外连接
的LEFT改为RIGHT,其他内容不变,我们看得出来的结果如何
- 简单的把上面
SELECT `name`,`login_time` FROM `users` RIGHT JOIN `login_activities` ON `users`.`id` = `login_activities`.`user_id`
- 此时就只有4条数据,而不是5条数据了。可见此次确实是以
RIGHT JOIN
右侧的表为主导
总结
- 两种外连接方式,让我们有了两种选择,看要以哪个侧重表为主
- 理论来说,这两种方式其实已经涵盖绝大多数使用场景了,但我们知道,两个表之间其实是有三种选择
- 如上图,左外连接是A+B,而右外连接是B+C。通过图像,我们可以很轻易的发现,其实还有一种选项,就是A+B+C
- 其实是有方法的,叫做全外连接(Full Outer Join),返回两个表中所有行。如果某一侧没有匹配,那么该侧的行将以空值填充
- 但并非所有数据库系统都支持全外连接,所以我们这里没讲
这是一个从 https://juejin.cn/post/7368367426210578471 下的原始话题分离的讨论话题