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

  • 时间:
  • 浏览:0
  • 来源:5分时时彩官网_5分时时彩怎么玩_5分时时彩平台哪个好

前言

  开心一刻

我须要飞的更高,飞的更高,啊!

谓词

  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
('201901007001','李小龙',21,1),
('201901007002','王祖贤',16,2),
('201901001003','林青霞',17,2),
('201901001004','李嘉欣',15,2),
('201901009005','周润发',20,1),
('201901009006','张国荣',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', '201901007001', '01007', '影视7班'),
('2', '201901007002', '01007', '影视7班'),
('3', '201901001003', '01008', '影视8班'),
('4', '201901001004', '01008', '影视8班'),
('5', '201901009005', '01009', '影视9班'),
('6', '201901009006', '01009', '影视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
('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
('201901007001','数学',100),
('201901007001','语文',100),
('201901007001','物理',100),
('201901001003','数学',100),
('201901001003','语文',95),
('201901009006','数学',40),
('201901009006','语文',90),
('20190610011','数学',100);

SELECT * FROM tbl_student_score;

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

        201901007001、201901001003、20190610011 这名 八个学生满足条件,亲戚亲戚我们歌词 歌词 我们歌词 歌词 我们歌词 歌词 须要将这 3 个学生查出来,这名 SQL 该怎么才能 才能 写? 亲戚亲戚我们歌词 歌词 我们歌词 歌词 我们歌词 歌词 须要转换下命题,将查询条件“所有科目分数都有 100 分以上” 转加进它的双重否定 “这样八个科目分数不满 100 分”,或多或少 用 NOT EXISTS 来表示转换后的命题

-- 这样八个科目分数不满 100 分
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 < 100    -- 分数不满100 分的科目
);

      2、查询出“数学分数在 100 分以上(涵盖100)且语文分数在 100 分以上(涵盖)的学生”

        结果应该是学号分别为 201901007001、201901001003 的学生。像从前的需求,亲戚亲戚我们歌词 歌词 我们歌词 歌词 我们歌词 歌词 在实际业务中应该会老会 遇到,或多或少 乍一看原应会其实不太像是全称量化的条件。原应改成下面从前的说法,原应亲戚亲戚我们歌词 歌词 我们歌词 歌词 我们歌词 歌词 一下子就能明白它是全称量化的命题了。

"某个学生的所有行数据中,原应科目是数学,则分数在 100 分以上;原应科目是语文,则分数在 100 分以上。"

        亲戚亲戚我们歌词 歌词 我们歌词 歌词 我们歌词 歌词 再转加进它双重否定:某个学生的所有行数据中,原应科目是数学,则分数不低于 100;原应科目是语文,则分数不低于 100 ;亲戚亲戚我们歌词 歌词 我们歌词 歌词 我们歌词 歌词 可不不不 按照如下顺序写出亲戚亲戚我们歌词 歌词 我们歌词 歌词 我们歌词 歌词 你要的 SQL

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

-- 2、CASE 表达式,单重否定(加进 NOT EXISTS才算双重)
CASE WHEN subject = '数学' AND score < 100 THEN 1
        WHEN subject = '语文' AND score < 100 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 < 100 THEN 1
                        WHEN subject = '语文' AND score < 100 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 < 100 THEN 1
                        WHEN subject = '语文' AND score < 100 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进阶教程》