神奇的 SQL 之谓词 → 难理解的 EXISTS

时间:2019-11-20 20:50:10
浏览:6

前言

　　开心一刻

我不你要要飞的更高，飞的更高，啊！

谓词

　　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
('2019010007001','李小龙',21,1),
('2019010007002','王祖贤',16,2),
('201901000100003','林青霞',17,2),
('201901000100004','李嘉欣',15,2),
('2019010009005','周润发',20,1),
('2019010009006','张国荣',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', '2019010007001', '010007', '影视7班'),
('2', '2019010007002', '010007', '影视7班'),
('3', '201901000100003', '010008', '影视8班'),
('4', '201901000100004', '010008', '影视8班'),
('5', '2019010009005', '010009', '影视9班'),
('6', '2019010009006', '010009', '影视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 查到的结果是一样的

　　　　　　用个图来概括下一般的谓词与 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
('201906100010','刘德华',55,1),
('201906100011','张家辉',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
('2019010007001','数学',1000),
('2019010007001','语文',1000),
('2019010007001','物理',1000),
('201901000100003','数学',1000),
('201901000100003','语文',95),
('2019010009006','数学',40),
('2019010009006','语文',90),
('201906100011','数学',1000);

SELECT * FROM tbl_student_score;

　　　　　　1、查询出“所有科目分数有的是 1000 分以上的学生”

　　　　　　　　2019010007001、201901000100003、201906100011 这两个多多多学生满足条件，亲们可不还能能将这 3 个学生查出来，三种 SQL 该怎么还能能写？亲们可不还能能转换下命题，将查询条件“所有科目分数有的是 1000 分以上” 转加进去它的双重否定 “没两个多多多多科目分数不满 1000 分”，你要用 NOT EXISTS 来表示转换后的命题

-- 没两个

多多多多科目分数不满 1000 分
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 < 1000    -- 分数不满1000 分的科目
);

　　　　　　2、查询出“数学分数在 1000 分以上（涵盖1000）且语文分数在 1000 分以上（涵盖）的学生”

　　　　　　　　结果应该是学号分别为 2019010007001、201901000100003 的学生。像原来的需求，亲们在实际业务中应该会总爱遇到，你要乍一看不可能会我我觉得不太像是全称量化的条件。不可能改成下面原来的说法，不可能亲们一下子就能明白它是全称量化的命题了。

"某个学生的所有行数据中，不可能

科目是数学，则分数在 1000 分以上；不可能

科目是语文，则分数在 1000 分以上。"

　　　　　　　　亲们再转加进去它双重否定：某个学生的所有行数据中，不可能科目是数学，则分数不低于 1000；不可能科目是语文，则分数不低于 1000 ；亲们还可不还能能按照如下顺序写出亲们你要的 SQL

-- 1、CASE 表达式，肯定
CASE WHEN subject = '数学' AND score >= 1000 THEN 1
        WHEN subject = '语文' AND score >= 1000 THEN 1
        ELSE 0 
END;

-- 2、CASE 表达式，单重否定（加进去去 NOT EXISTS才算双重）
CASE WHEN subject = '数学' AND score < 1000 THEN 1
        WHEN subject = '语文' AND score < 1000 THEN 1
    ELSE 0 
END;

-- 3、结果涵盖了 201906100011 的 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 < 1000 THEN 1
                        WHEN subject = '语文' AND score < 1000 THEN 1
                        ELSE 0 
                    END
);

-- 4、201906100011 越来越语文成绩，剔除掉
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 < 1000 THEN 1
                        WHEN subject = '语文' AND score < 1000 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进阶教程》

天天飞车新版本车队任务分为车队PK和什么 10月22日每日一题答案

这不够发烧，Ryzen Threadripper平台暂不支持NVMe RAID引导

前言

谓词

比较谓词

LIKE

BETWEEN

IS NULL 和 IS NOT NULL

IN

EXISTS

理论篇

实体的阶层

全称量化和地处量化

实践篇

查询表中“不”地处的数据

全称量化 ：习惯 “肯定 ⇔ 双重否定” 之间的转换

总结

参考

猜你喜欢

　　比较谓词

　　LIKE

　　BETWEEN

　　IS NULL 和 IS NOT NULL

　　IN

　　理论篇

　　　　实体的阶层

　　　　全称量化和地处量化

　　实践篇

　　　　查询表中“不”地处的数据

　　　　全称量化：习惯 “肯定 ⇔ 双重否定” 之间的转换