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

  • 时间:
  • 浏览:0
  • 来源:大发快3_快3手机app下载_大发快3手机app下载

前言

  开心一刻

我都里能飞的更高,飞的更高,啊!

谓词

  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
('2019060
7001','李小龙',21,1),
('2019060
7002','王祖贤',16,2),
('2019060
60

03','林青霞',17,2),
('2019060
60

04','李嘉欣',15,2),
('2019060
9005','周润发',20,1),
('2019060
9006','张国荣',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', '2019060
7001', '060
7', '影视7班'),
('2', '2019060
7002', '060
7', '影视7班'),
('3', '2019060
60

03', '060
8', '影视8班'),
('4', '2019060
60

04', '060
8', '影视8班'),
('5', '2019060
9005', '060
9', '影视9班'),
('6', '2019060
9006', '060
9', '影视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  和它以前的第有2个 AND 组成有2个范围条件;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

    有原先有2个需求:查询出年龄等于 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” 由于 “存在(离米 有2个)满足条件 P 的 x ”,前者称为“全称量词”,后者称为“存在量词”,分别记作 ∀(A的下倒)、∃(E的左倒)。

      SQL 中的 EXISTS 谓词实现了谓词逻辑中的存在量词,然而遗憾的是, SQL 却并这麼 实现全称量词。为社 让 这麼 全称量词太多是否 SQL 的致命缺乏,由于 全称量词和存在量词假使 定义了有2个,原先就都里能被推导出来。具体都里能参考下面某些等价改写的规则(德·摩根定律)。

∀ x P x = ¬ ∃ x ¬P(所有的 x 都满足条件 P =不存在不满足条件 P 的 x )

∃ x P x = ¬ ∀ x ¬Px(存在 x 满足条件 P =太多所有的 x 全是满足条件 P)

      为社 让 在 SQL 中,为了表达全称量化,都里能将"所有的行都满足条件P" 原先的命题转打上去 "不存在不满足条件 P 的行"

  实践篇

    中间的理论篇,大伙看得人以前由于 还是特别晕,大伙结合具体的实际案例来看看 EXISTS 的妙用

    查询表中“不”存在的数据

      中间的 tbl_student中的学生都分配到了具体的班级,假设新来了有2个学生(刘德华、张家辉),大伙暂时还未被分配到班级,大伙要怎样将大伙查询出来(查询未被分配到班级的学生信息)。

-- 新来、未被分配到班级的学生
INSERT INTO tbl_student(sno,name,age,sex) VALUES
('20190660

10','刘德华',55,1),
('20190660

11','张家辉',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 的用法中很具有代表性的有2个用法。为社 让 都里能大伙打破常规思维,习惯从全称量化 “所有的行都××” 到其双重否定 “不××的行一行全是存在” 的转换。

      假设大伙有学生成绩表: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
('2019060
7001','数学',60

),
('2019060
7001','语文',60

),
('2019060
7001','物理',60

),
('2019060
60

03','数学',60

),
('2019060
60

03','语文',95),
('2019060
9006','数学',40),
('2019060
9006','语文',90),
('20190660

11','数学',60

);

SELECT * FROM tbl_student_score;

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

        2019060 7001、2019060 60 03、20190660 11 这有2个学生满足条件,大伙都里能将这 3 个学生查出来,某些 SQL 该要怎样写? 大伙都里能转换下命题,将查询条件“所有科目分数全是 60 分以上” 转打上去它的双重否定 “这麼 有2个科目分数不满 60 分”,为社 让 用 NOT EXISTS 来表示转换后的命题

-- 这麼

有2个科目分数不满 60

 分
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 < 60

    -- 分数不满60

 分的科目
);

      2、查询出“数学分数在 60 分以上(包含60 )且语文分数在 60 分以上(包含)的学生”

        结果应该是学号分别为 2019060 7001、2019060 60 03 的学生。像原先的需求,大伙在实际业务中应该会无缘无故 遇到,为社 让 乍一看由于 会我我觉得不太像是全称量化的条件。由于 改成下面原先的说法,由于 大伙一下子就能明白它是全称量化的命题了。

"某个学生的所有行数据中,由于

科目是数学,则分数在 60

 分以上;由于

科目是语文,则分数在 60

 分以上。"

        大伙再转打上去它双重否定:某个学生的所有行数据中,由于 科目是数学,则分数不低于 60 ;由于 科目是语文,则分数不低于 60 ;大伙都里能按照如下顺序写出大伙你都里能的 SQL

-- 1、CASE 表达式,肯定
CASE WHEN subject = '数学' AND score >= 60

 THEN 1
        WHEN subject = '语文' AND score >= 60

 THEN 1
        ELSE 0 
END;

-- 2、CASE 表达式,单重否定(打上去 NOT EXISTS才算双重)
CASE WHEN subject = '数学' AND score < 60

 THEN 1
        WHEN subject = '语文' AND score < 60

 THEN 1
    ELSE 0 
END;

-- 3、结果包含了 20190660

11 的 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 < 60

 THEN 1
                        WHEN subject = '语文' AND score < 60

 THEN 1
                        ELSE 0 
                    END
);

-- 4、20190660

11 这麼

语文成绩,剔除掉
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 < 60

 THEN 1
                        WHEN subject = '语文' AND score < 60

 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进阶教程》

猜你喜欢

A. Goljahanpoor数据,A. Goljahanpoor新闻,A. Goljahanpoor视频,A. Goljahanpoor身价

A.GoljahanpoorA.Goljahanpoor俱乐部:TPV谭柏利国籍:芬兰身高:CM位置:中场年龄:24岁体重:KG号码:9号生日:1995-06-07惯用脚:赛季

2020-01-29

多家平台成立共享配送联盟,非诚信配送员将上黑名单

IT之家1月11日消息 昨日,美团外卖、UU跑腿、闪送、邻趣、快服务等多家平台签署成立共享配送联盟。配送员能都可不可不还里能根据人及意愿,承接任一肯能多个平台的订单配送工作,称

2020-01-29

暴徒变身“顾客” 声大夹恶 前线保安成磨心被欺凌

【大公报讯】已持续近两天的连串暴力衝突,多个商场变成暴徒的战场,商场前线保安惨成磨心。香港护卫及物业管理从业员总会早前否认,指暴力衝突事件令前线保安员陷入两难,除了有意味着因妨

2020-01-29

谷歌CEO发内部备忘录,支持员工周四举行步行抗议

北京时间10月31日晚间消息,CNBC今日援引知情人士的消息称,针对谷歌员工准备在本周四举行步行抗议的计划,谷歌CEO桑达尔·皮查伊(SundarPichai)在一份内内外部邮

2020-01-29

摩拜单车免扫码解锁功能上线:再也不用担心二维码被刮啦

IT之家7月23日消息 据今日36氪消息,摩拜单车正式上线了“免扫码解锁”功能。据介绍,用户假如有一天将手机靠近车锁,无需扫码即可用蓝牙解锁单车。摩拜单车App与微信小线程池池

2020-01-29