神奇的 SQL 之谓词 → 难理解的 EXISTS
前言
开心一刻
我要飞的更高,飞的更高,啊!
谓词
sql 中的谓词指的是:返回值是逻辑值的函数。我们知道函数的返回值有可能是数字、字符串或者日期等等,但谓词的返回值全部是逻辑值(true/false/unknow),谓词是一种特殊的函数。关于逻辑值,可以查看:神奇的 sql 之温柔的陷阱 → 三值逻辑 与 null !
sql 中的谓词有很多,如 =、>、<、<> 等,我们来看看 sql 具体有哪些常用的谓词
比较谓词
创建表与初始化数据
-- 1、表创建并初始化数据 drop table if exists tbl_student; create table tbl_student ( id int(8) unsigned not null auto_increment comment '自增主键', sno varchar(12) not null comment '学号', name varchar(5) not null comment '姓名', age tinyint(3) not null comment '年龄', sex tinyint(1) not null comment '性别,1:男,2:女', primary key (id) ); insert into tbl_student(sno,name,age,sex) values ('20190607001','李小龙',21,1), ('20190607002','王祖贤',16,2), ('20190608003','林青霞',17,2), ('20190608004','李嘉欣',15,2), ('20190609005','周润发',20,1), ('20190609006','张国荣',18,1); drop table if exists tbl_student_class; create table tbl_student_class ( id int(8) unsigned not null auto_increment comment '自增主键', sno varchar(12) not null comment '学号', cno varchar(5) not null comment '班级号', cname varchar(20) not null comment '班级名', primary key (`id`) ) comment='学生班级表'; insert into tbl_student_class values ('1', '20190607001', '0607', '影视7班'), ('2', '20190607002', '0607', '影视7班'), ('3', '20190608003', '0608', '影视8班'), ('4', '20190608004', '0608', '影视8班'), ('5', '20190609005', '0609', '影视9班'), ('6', '20190609006', '0609', '影视9班'); select * from tbl_student; select * from tbl_student_class;
相信大家对 =、>、<、<>(!=)等比较运算符都非常熟悉,它们的正式名称就是比较谓词,使用示例如下
-- 比较谓词示例 select * from tbl_student where name = '王祖贤'; select * from tbl_student where age > 18; select * from tbl_student where age < 18; select * from tbl_student where age <> 18; select * from tbl_student where age <= 18;
like
当我们想用 sql 做一些简单的模糊查询时,都会用到 like 谓词,分为 前一致、中一致和后一致,使用示例如下
-- like谓词 select * from tbl_student where name like '李%'; -- 前一致 select * from tbl_student where name like '%青%'; -- 中一致 select * from tbl_student where name like '青%'; -- 后一致
如果name字段上建了索引,那么前一致会利用索引;而中一致、后一致会走全表扫描。
between
当我们想进行范围查询时,往往会用到 between 谓词,示例如下
-- between谓词 select * from tbl_student where age between 15 and 22; select * from tbl_student where age not between 15 and 22;
between 和它之后的第一个 and 组成一个范围条件;between 会包含临界值 15 和 22
select * from tbl_student where age between 15 and 22; -- 等价于 select * from tbl_student where age >= 15 and age <= 22;
若不想包含临界值,那就需要这么写了
select * from tbl_student where age > 15 and age < 22;
is null 和 is not null
null 的水很深,具体可看:神奇的 sql 之温柔的陷阱 → 三值逻辑 与 null !
in
有这样一个需求:查询出年龄等于 15、18以及20的学生,我们会用 or 来查
-- or select * from tbl_student where age = 15 or age = 18 or age = 20;
用 or 来查没问题,但是有一点不足,如果选取的对象越来越多,sql会变得越来越长,阅读性会越来越差。所以我们可以用 in 来代替
-- in select * from tbl_student where age in(15,18,20);
in 有一种其他谓词没有的使用方法:使用子查询作为其参数,这个在平时项目中也是用的非常多的,例如:查询出影视7班的学生信息
-- in实现,但不推荐 select * from tbl_student where sno in ( select sno from tbl_student_class where cname = '影视7班' ); -- 联表查,推荐 select ts.* from tbl_student_class tsc left join tbl_student ts on tsc.sno = ts.sno where tsc.cname = '影视7班';
很多情况下,in 是可以用联表查询来替换的
exists
exists也是 sql 谓词,但平时用的不多,不是说适用场景少,而是它不好驾驭,我们用不好它。它用法与其他谓词不一样,而且不好理解,另外很多情况下我们都用 in 来替代它了。
理论篇
在真正讲解 exsits 示例之前,我们先来了解下理论知识:实体的阶层 、全称量化与存在量化
实体的阶层
sql 严格区分阶层,不能跨阶层操作。就用我们常用的谓词来举例,同样是谓词,但是与 = 、between 等相比,exists 的用法还是大不相同的。概括来说,区别在于“谓词的参数可以取什么值”;“x = y”或 “x between y ” 等谓词可以取的参数是像 “21” 或者 “李小龙” 这样的单一值,我们称之为标量值,而 exists 可以取的参数究竟是什么呢?从下面这条 sql 语句来看,exists 的参数不像是单一值
select * from tbl_student ts where exists ( select * from tbl_student_class tsc where ts.sno = tsc.sno );
我们可以看出 exists 的参数是行数据的集合。之所以这么说,是因为无论子查询中选择什么样的列,对于 exists 来说都是一样的。在 exists 的子查询里, select 子句的列表可以有下面这三种写法。
1. 通配符:select * 2. 常量:select '1' 3. 列名:select tsc.id
也就是说如下 3 条 sql 查到的结果是一样的
-- select * select * from tbl_student ts where exists ( select * from tbl_student_class tsc where ts.sno = tsc.sno ); -- select 常量 select * from tbl_student ts where exists ( select 1 from tbl_student_class tsc where ts.sno = tsc.sno ); -- select 列名 select * from tbl_student ts where exists ( select tsc.sno from tbl_student_class tsc where ts.sno = tsc.sno );
用个图来概括下一般的谓词与 exists 的区别
从上图我们知道,exists 的特殊性在于输入值的阶数(输出值和其他谓词一样,都是逻辑值)。谓词逻辑中,根据输入值的阶数对谓词进行分类。= 或者 betweeen 等输入值为一行的谓词叫作“一阶谓词”,而像 exists 这样输入值为行的集合的谓词叫作 “二阶谓词”。关于 “阶” ,有兴趣的可以区看我的另一篇博客:神奇的 sql 之层级 → 为什么 group by 之后不能直接引用原表中的列
全称量化和存在量化
谓词逻辑中有量词(限量词、数量词)这类特殊的谓词。我们可以用它们来表达一些这样的命题:“所有的 x 都满足条件 p” 或者 “存在(至少一个)满足条件 p 的 x ”,前者称为“全称量词”,后者称为“存在量词”,分别记作 ∀(a的下倒)、∃(e的左倒)。
sql 中的 exists 谓词实现了谓词逻辑中的存在量词,然而遗憾的是, sql 却并没有实现全称量词。但是没有全称量词并不算是 sql 的致命缺陷,因为全称量词和存在量词只要定义了一个,另一个就可以被推导出来。具体可以参考下面这个等价改写的规则(德·摩根定律)。
∀ x p x = ¬ ∃ x ¬p(所有的 x 都满足条件 p =不存在不满足条件 p 的 x )
∃ x p x = ¬ ∀ x ¬px(存在 x 满足条件 p =并非所有的 x 都不满足条件 p)
因此在 sql 中,为了表达全称量化,需要将"所有的行都满足条件p" 这样的命题转换成 "不存在不满足条件 p 的行"
实践篇
上面的理论篇,大家看了以后可能还是有点晕,我们结合具体的实际案例来看看 exists 的妙用
查询表中“不”存在的数据
上面的 tbl_student中的学生都分配到了具体的班级,假设新来了两个学生(刘德华、张家辉),他们暂时还未被分配到班级,我们如何将他们查询出来(查询未被分配到班级的学生信息)。
-- 新来、未被分配到班级的学生 insert into tbl_student(sno,name,age,sex) values ('20190610010','刘德华',55,1), ('20190610011','张家辉',46,1);
我们最容易想到的 sql 肯定是下面这条
-- not in 实现 select * from tbl_student where sno not in(select sno from tbl_student_class);
其实用 not exists 也是可以实现的
-- not exists 实现 select * from tbl_student ts where not exists ( select * from tbl_student_class tsc where ts.sno = tsc.sno );
全称量化 :习惯 “肯定 ↔ 双重否定” 之间的转换
exists 谓词来表达全称量化,这是exists 的用法中很具有代表性的一个用法。但是需要我们打破常规思维,习惯从全称量化 “所有的行都××” 到其双重否定 “不××的行一行都不存在” 的转换。
假设我们有学生成绩表:tbl_student_score
-- 学生成绩表 drop table if exists tbl_student_score; create table tbl_student_score ( id int(8) unsigned not null auto_increment comment '自增主键', sno varchar(12) not null comment '学号', subject varchar(5) not null comment '课程', score tinyint(3) not null comment '分数', primary key (id) ); insert into tbl_student_score(sno,subject,score) values ('20190607001','数学',100), ('20190607001','语文',80), ('20190607001','物理',80), ('20190608003','数学',80), ('20190608003','语文',95), ('20190609006','数学',40), ('20190609006','语文',90), ('20190610011','数学',80); select * from tbl_student_score;
1、查询出“所有科目分数都在 50 分以上的学生”
20190607001、20190608003、20190610011 这三个学生满足条件,我们需要将这 3 个学生查出来,这个 sql 该如何写? 我们需要转换下命题,将查询条件“所有科目分数都在 50 分以上” 转换成它的双重否定 “没有一个科目分数不满 50 分”,然后用 not exists 来表示转换后的命题
-- 没有一个科目分数不满 50 分 select distinct sno from tbl_student_score tss1 where not exists -- 不存在满足以下条件的行 ( select * from tbl_student_score tss2 where tss2.sno = tss1.sno and tss2.score < 50 -- 分数不满50 分的科目 );
2、查询出“数学分数在 80 分以上(包含80)且语文分数在 50 分以上(包含)的学生”
结果应该是学号分别为 20190607001、20190608003 的学生。像这样的需求,我们在实际业务中应该会经常遇到,但是乍一看可能会觉得不太像是全称量化的条件。如果改成下面这样的说法,可能我们一下子就能明白它是全称量化的命题了。
"某个学生的所有行数据中,如果科目是数学,则分数在 80 分以上;如果科目是语文,则分数在 50 分以上。"
我们再转换成它双重否定:某个学生的所有行数据中,如果科目是数学,则分数不低于 80;如果科目是语文,则分数不低于 50 ;我们可以按照如下顺序写出我们想要的 sql
-- 1、case 表达式,肯定 case when subject = '数学' and score >= 80 then 1 when subject = '语文' and score >= 50 then 1 else 0 end; -- 2、case 表达式,单重否定(加上 not exists才算双重) case when subject = '数学' and score < 80 then 1 when subject = '语文' and score < 50 then 1 else 0 end; -- 3、结果包含了 20190610011 的 sql select distinct sno from tbl_student_score tss1 where subject in ('数学', '语文') and not exists ( select *from tbl_student_score tss2 where tss2.sno = tss1.sno and 1 = case when subject = '数学' and score < 80 then 1 when subject = '语文' and score < 50 then 1 else 0 end ); -- 4、20190610011 没有语文成绩,剔除掉 select sno from tbl_student_score tss1 where subject in ('数学', '语文') and not exists ( select * from tbl_student_score tss2 where tss2.sno = tss1.sno and 1 = case when subject = '数学' and score < 80 then 1 when subject = '语文' and score < 50 then 1 else 0 end ) group by sno having count(*) = 2; -- 必须两门科目都有分数
关于 exists 的案例有很多,这里就不再举例了,有兴趣的小伙伴可以看看:sql 中的 exists 到底做了什么?
如果大家想掌握 exists,希望大家多看看 exists 的案例,看多了你就会发现其中的通性:哪些场景适合用 exists。
总结
1、sql 中的谓词分两种:一阶谓词和二阶谓词(exists),区别主要在于接收的参数不同,一阶谓词接收的是 行,而二阶谓词接收的是 行的集合;
2、sql 中没有与全称量词相当的谓词,可以使用 not exists 代替;
3、exists 之所以难用(不是不好用,而是不会用),主要是全称量词的命题转换(肯定 ↔ 双重否定)比较难(楼主也懵!)。实际工作中往往会舍弃 exists,寻找它的替代方式,可能是 sql 的替代,也可能是业务方面的转换,所以说,exists 掌握不了没关系,当然,能掌握那是最好了;
参考
《sql基础教程》
《sql进阶教程》
下一篇: 老王说的
推荐阅读
-
神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程(二)
-
神奇的 SQL 之层级 → 为什么 GROUP BY 之后不能直接引用原表中的列
-
神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程(一)
-
神奇的 SQL 之谓词 → 难理解的 EXISTS
-
神奇的 SQL 之 ICP → 索引条件下推
-
神奇的 SQL 之温柔的陷阱 → 三值逻辑 与 NULL !
-
神奇的 SQL 之 MySQL 性能分析神器 → EXPLAIN,SQL 起飞的基石!
-
神奇的 SQL 之团结的力量 → JOIN
-
神奇的 SQL 之扑朔迷离 → ON 和 WHERE,好多细节!
-
神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程(二)