神奇的 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
更多相關文章
  • 資料庫個人筆記（1）-- 基礎篇
    ## SQL Server 命令式操作 - 首先 sql server 的基本操作分為三類 - CREATE (創建資料庫) - ALTER（修改資料庫） - DROP（刪除資料庫） **CREATE**創建資料庫 ```CREATE DATABASE TEST1 ##資料庫名稱 ON primar ...
  • Playbook劇本小結
    1.Playbook劇本小結 1.什麼是playbook，playbook翻譯過來就是“劇本”，那playbook組成如下 play: 定義的是主機的角色task: 定義的是具體執行的任務playbook: 由一個或多個play組成，一個play可以包含多個task任務 簡單理解為: 使用不同的模塊 ...
  • 第十六章、淺識資料庫
    [TOC] 第十六章、淺識資料庫 資料庫配置 資料庫修改信息 用戶操作 表的修改 創建表的完整語法 資料庫表的引擎：驅動數據的方式 資料庫優化 資料庫的模式 mysql支持的數據類型 整型 浮點型 字元串：資料庫優化 char效率要高於varchar 時間 枚舉與集合 約束 ...
  • sqlserver存儲過程里傳欄位、傳字元串，並返回DataTable、字元串,存儲過程調用存儲過程。
    經常需要查一些信息， 想寫視圖來返回數據以提高效率，但是用試視圖不能傳參，只好想到改存儲過程。記錄一下語法，方便以後做項目時候想不起來了用。 1：傳欄位返回datatable 2： 傳欄位回一串字元 3: 傳字元串返回datable 4：存儲過程調用存儲過程 --加半個小時(select datea ...
  • mysql json欄位
    從 MySQL 5.7.8 開始，MySQL 支持原生的 JSON 數據類型。 一. 創建json(不可以設置長度,可以是null,不能用有預設值) mysql> CREATE TABLE lnmp ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, ` ...
  • MySQL:Specified key was too long; max key length is 767 bytes.
    建表語句： 報錯： 原因： 我這裡使用 api,method 來做 api 表的聯合主鍵，此時會根據這兩個欄位建立索引，charset 是 utf8 ，也就是一個字元3個位元組， 那麼總共索引的位元組為： 500*3+50*3 = 1650 個位元組，而mysql 要求的索引是 767 個位元組。 解決： ...
  • 【MySQL】SQL語句基礎
    一、操作資料庫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四、備份恢複數據庫五、多表設... ...
  • SQL使用UPDATE和SUBSTRING截取字元串方法,從頭截取到某個位置，截取中間片段，字元串中間截取到末尾或刪除前面的字元串
    //從頭截取 update 表名 set 表列名 =SUBSTRING(表列名,1,目標位置數值) //！計數從1開始，從左往右 where 條件 //條件自己選擇，不加where條件會更新所有行，請特別註意 //截取中間部分 update 表名 set 表列名 =SUBSTRING(表列名，目標位 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類