神奇的 SQL 之謂詞 → 難理解的 EXISTS

来源:https://www.cnblogs.com/youzhibing/archive/2019/09/23/11385136.html
-Advertisement-
Play Games

前言 開心一刻 我要飛的更高,飛的更高,啊! 謂詞 SQL 中的謂詞指的是:返回值是邏輯值的函數。我們知道函數的返回值有可能是數字、字元串或者日期等等,但謂詞的返回值全部是邏輯值(TRUE/FALSE/UNKNOW),謂詞是一種特殊的函數。關於邏輯值,可以查看:神奇的 SQL 之溫柔的陷阱 → 三值 ...


前言

  開心一刻

我要飛的更高,飛的更高,啊!

謂詞

  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
);
View Code

      用個圖來概括下一般的謂詞與 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進階教程》


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • ## SQL Server 命令式操作 - 首先 sql server 的基本操作分為三類 - CREATE (創建資料庫) - ALTER(修改資料庫) - DROP(刪除資料庫) **CREATE**創建資料庫 ```CREATE DATABASE TEST1 ##資料庫名稱 ON primar ...
  • 1.Playbook劇本小結 1.什麼是playbook,playbook翻譯過來就是“劇本”,那playbook組成如下 play: 定義的是主機的角色task: 定義的是具體執行的任務playbook: 由一個或多個play組成,一個play可以包含多個task任務 簡單理解為: 使用不同的模塊 ...
  • [TOC] 第十六章、淺識資料庫 資料庫配置 資料庫修改信息 用戶操作 表的修改 創建表的完整語法 資料庫表的引擎:驅動數據的方式 資料庫優化 資料庫的模式 mysql支持的數據類型 整型 浮點型 字元串:資料庫優化 char效率要高於varchar 時間 枚舉與集合 約束 ...
  • 經常需要查一些信息, 想寫視圖來返回數據以提高效率,但是用試視圖不能傳參,只好想到改存儲過程。記錄一下語法,方便以後做項目時候想不起來了用。 1:傳欄位返回datatable 2: 傳欄位回一串字元 3: 傳字元串返回datable 4:存儲過程調用存儲過程 --加半個小時(select datea ...
  • 從 MySQL 5.7.8 開始,MySQL 支持原生的 JSON 數據類型。 一. 創建json(不可以設置長度,可以是null,不能用有預設值) mysql> CREATE TABLE lnmp ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, ` ...
  • 建表語句: 報錯: 原因: 我這裡使用 api,method 來做 api 表的聯合主鍵,此時會根據這兩個欄位建立索引,charset 是 utf8 ,也就是一個字元3個位元組, 那麼總共索引的位元組為: 500*3+50*3 = 1650 個位元組,而mysql 要求的索引是 767 個位元組。 解決: ...
  • 一、操作資料庫1.1 創建資料庫1.2 查看資料庫1.3 修改資料庫1.4 刪除資料庫1.5 選擇資料庫二、操作表2.1 創建表2.2 查看表2.3 修改表2.4 刪除表三、操作表記錄CRUD3.1 INSERT3.2 UPDATE3.3 DELETE3.4 SELECT四、備份恢複數據庫五、多表設... ...
  • //從頭截取 update 表名 set 表列名 =SUBSTRING(表列名,1,目標位置數值) //!計數從1開始,從左往右 where 條件 //條件自己選擇,不加where條件會更新所有行,請特別註意 //截取中間部分 update 表名 set 表列名 =SUBSTRING(表列名,目標位 ...
一周排行
    -Advertisement-
    Play Games
  • Timer是什麼 Timer 是一種用於創建定期粒度行為的機制。 與標準的 .NET System.Threading.Timer 類相似,Orleans 的 Timer 允許在一段時間後執行特定的操作,或者在特定的時間間隔內重覆執行操作。 它在分散式系統中具有重要作用,特別是在處理需要周期性執行的 ...
  • 前言 相信很多做WPF開發的小伙伴都遇到過表格類的需求,雖然現有的Grid控制項也能實現,但是使用起來的體驗感並不好,比如要實現一個Excel中的表格效果,估計你能想到的第一個方法就是套Border控制項,用這種方法你需要控制每個Border的邊框,並且在一堆Bordr中找到Grid.Row,Grid. ...
  • .NET C#程式啟動閃退,目錄導致的問題 這是第2次踩這個坑了,很小的編程細節,容易忽略,所以寫個博客,分享給大家。 1.第一次坑:是windows 系統把程式運行成服務,找不到配置文件,原因是以服務運行它的工作目錄是在C:\Windows\System32 2.本次坑:WPF桌面程式通過註冊表設 ...
  • 在分散式系統中,數據的持久化是至關重要的一環。 Orleans 7 引入了強大的持久化功能,使得在分散式環境下管理數據變得更加輕鬆和可靠。 本文將介紹什麼是 Orleans 7 的持久化,如何設置它以及相應的代碼示例。 什麼是 Orleans 7 的持久化? Orleans 7 的持久化是指將 Or ...
  • 前言 .NET Feature Management 是一個用於管理應用程式功能的庫,它可以幫助開發人員在應用程式中輕鬆地添加、移除和管理功能。使用 Feature Management,開發人員可以根據不同用戶、環境或其他條件來動態地控制應用程式中的功能。這使得開發人員可以更靈活地管理應用程式的功 ...
  • 在 WPF 應用程式中,拖放操作是實現用戶交互的重要組成部分。通過拖放操作,用戶可以輕鬆地將數據從一個位置移動到另一個位置,或者將控制項從一個容器移動到另一個容器。然而,WPF 中預設的拖放操作可能並不是那麼好用。為瞭解決這個問題,我們可以自定義一個 Panel 來實現更簡單的拖拽操作。 自定義 Pa ...
  • 在實際使用中,由於涉及到不同編程語言之間互相調用,導致C++ 中的OpenCV與C#中的OpenCvSharp 圖像數據在不同編程語言之間難以有效傳遞。在本文中我們將結合OpenCvSharp源碼實現原理,探究兩種數據之間的通信方式。 ...
  • 一、前言 這是一篇搭建許可權管理系統的系列文章。 隨著網路的發展,信息安全對應任何企業來說都越發的重要,而本系列文章將和大家一起一步一步搭建一個全新的許可權管理系統。 說明:由於搭建一個全新的項目過於繁瑣,所有作者將挑選核心代碼和核心思路進行分享。 二、技術選擇 三、開始設計 1、自主搭建vue前端和. ...
  • Csharper中的表達式樹 這節課來瞭解一下表示式樹是什麼? 在C#中,表達式樹是一種數據結構,它可以表示一些代碼塊,如Lambda表達式或查詢表達式。表達式樹使你能夠查看和操作數據,就像你可以查看和操作代碼一樣。它們通常用於創建動態查詢和解析表達式。 一、認識表達式樹 為什麼要這樣說?它和委托有 ...
  • 在使用Django等框架來操作MySQL時,實際上底層還是通過Python來操作的,首先需要安裝一個驅動程式,在Python3中,驅動程式有多種選擇,比如有pymysql以及mysqlclient等。使用pip命令安裝mysqlclient失敗應如何解決? 安裝的python版本說明 機器同時安裝了 ...