學習SQL【8】-謂詞和CASE表達式

05-19

學習SQL【8】-謂詞和CASE表達式

來自專欄 Python愛好者

謂詞

什麼是謂詞

謂詞就是返回值為真值的函數。對於通常的函數來說，返回值有可能是數字、字元串和日期等，但是謂詞的返回值全部是真值。這也是謂詞和函數的最大區別。

謂詞主要有以下幾種：

LIKE
BETWEEN
IS NULL、IS NOT NULL
IN
EXISTS

LIKE謂詞—字元串的部分一致查詢

截止目前，我們使用字元串作為查詢條件的例子使用的都是=。這裡的=只有在字元串完全一致時才為真。與之相反，LIKE謂詞更加模糊一些，當需要進行字元串的部分一致查詢時需要使用該謂詞。

部分一致大體可以分為前方一致、中間一致和後方一致三種類型。接下來就讓我們看一看具體示例吧。

首先，我們先創建一張用作示例的表：

--創建SampleLike表 CREATE TABLE SampleLike ( strcol VARCHAR(6) NOT NULL, PRIMARY KEY(strcol));

向表中插入數據

--插入數據 BEGIN TRANSACTION;BEGIN INSERT INTO SampleLike VALUES (abcddd);INSERT 0 1 INSERT INTO SampleLike VALUES (dddabc);INSERT 0 1 INSERT INTO SampleLike VALUES (abdddc);INSERT 0 1 INSERT INTO SampleLike VALUES (abcdd);INSERT 0 1 INSERT INTO SampleLike VALUES (ddabc);INSERT 0 1 INSERT INTO SampleLike VALUES (abddc);INSERT 0 1 COMMIT;COMMIT

確認一下我們創建的表的內容：

SELECT * FROM SampleLike;

執行結果：

strcol-------- abcddd dddabc abdddc abcdd ddabc abddc(6 行記錄)

前方一致查詢

使用Like進行前方一致查詢

SELECT * FROM SampleLike WHERE strcol LIKE ddd%;

執行結果：

strcol-------- dddabc(1 行記錄)

其中的%代表「0字元以上的任意字元」的特殊符號，上例表示「以ddd開頭的所有字元」。

中間一致查詢

使用LIKE進行中間一致查詢

SELECT * FROM SampleLike WHERE strcol LIKE %ddd%;

執行結果：

strcol-------- abcddd dddabc abdddc(3 行記錄)

在字元串的起始和結束位置加上%，就能取出「包含ddd的字元串」。

後方一致查詢

使用LIKE進行後方一致查詢

SELECT * FROM SampleLike WHERE strcol LIKE %ddd;

執行結果：

strcol-------- abcddd(1 行記錄)

此外，我們還可以使用_（下劃線）來代替%，與%不同的是，它代表「任意一個字元」，下面我們就來嘗試一下：

--使用LIKE和_（下劃線）進行後方一致查詢 SELECT * FROM SampleLike WHERE strcol LIKE abc__;

執行結果：

strcol-------- abcdd(1 行記錄)

再舉個例子：

--查詢abc+任意3個字元的字元串 SELECT * FROM SampleLike WHERE strcol LIKE abc___;

執行結果：

strcol-------- abcddd(1 行記錄)

BETWEEN謂詞—範圍查詢

使用BETWEEN可以進行範圍查詢。該謂詞與其他謂詞或者函數不同的是它使用了3個參數。

--獲取銷售單價為100~1000元的商品 SELECT product_name, sale_price FROM Product WHERE sale_price BETWEEN 100 AND 1000;

執行結果：

product_name | sale_price--------------+------------ T衫 | 1000 打孔器 | 500 叉子 | 500 擦菜板 | 880 圓珠筆 | 100(5 行記錄)

BETWEEN的特點就是結果會包含100和1000這兩個臨界值。如果不想讓結果包含臨界值，那就必須使用<和>。

--選取出銷售單價為101~999元的商品 SELECT product_name, sale_price FROM Product WHERE sale_price > 100 AND sale_price < 1000;

執行結果：

product_name | sale_price--------------+------------ 打孔器 | 500 叉子 | 500 擦菜板 | 880(3 行記錄)

IS NULL、IS NOT NULL—判斷是否為NULL

為了選取某些值為NULL的列的數據，不能使用=，而只能使用特定的謂詞IS NULL。

--選取出進貨單價為NULL的商品 SELECT product_name, purchase_price FROM Product WHERE purchase_price IS NULL;

執行結果：

product_name | purchase_price--------------+---------------- 叉子 | 圓珠筆 |(2 行記錄)

與之相反，如果選取NULL以外的數據，需要使用謂詞IS NOT NULL。

--選取出進貨單價不為NULL的商品 SELECT product_name, purchase_price FROM Product WHERE purchase_price IS NOT NULL;

執行結果：

product_name | purchase_price--------------+---------------- T衫 | 500 打孔器 | 320 運動T衫 | 2800 菜刀 | 2800 高壓鍋 | 5000 擦菜板 | 790(6 行記錄)

IN謂詞—OR的簡便用法

通過OR指定多個進貨單價進行查詢：

SELECT product_name, purchase_price FROM Product WHERE purchase_price = 320 OR purchase_price = 500 OR purchase_price = 5000;

執行結果：

product_name | purchase_price--------------+---------------- T衫 | 500 打孔器 | 320 高壓鍋 | 5000(3 行記錄)

我們使用IN 謂詞來替換上述SQL語句：

--通過IN來指定多個進貨單價進行查詢 SELECT product_name, purchase_price FROM Product WHERE purchase_price IN (320, 500, 5000);

執行結果：

product_name | purchase_price--------------+---------------- T衫 | 500 打孔器 | 320 高壓鍋 | 5000(3 行記錄)

反之，否定形式可以使用NOT IN來實現：

--使用NOT IN 進行查詢時指多個排除的進貨單價進行查詢 SELECT product_name, purchase_price FROM Product W HERE purchase_price NOT IN (320, 500, 5000);

執行結果：

product_name | purchase_price--------------+---------------- 運動T衫 | 2800 菜刀 | 2800 擦菜板 | 790(3 行記錄)

注釋：使用IN 和NOT IN 時是無法取出NULL數據的，NULL終究是需要使用IS NULL和IS NOT NULL來進行判斷。

使用子查詢作為IN謂詞的參數

IN謂詞和子查詢

IN謂詞（NOT IN謂詞）具有其他謂詞所沒有的用法，那就是可以使用子查詢來作為其參數。子查詢在之前已經學過，就是SQL內部生成的表。

為了掌握更詳盡的使用方法，我們再創建一張新表：

--創建ShopProduct(商店商品)表的CREATE TABLE語句 CREATE TABLE ShopProduct ( shop_id CHAR(4) NOT NULL, shop_name VARCHAR(200) NOT NULL, product_id CHAR(4) NOT NULL, quantit INTEGER NOT NULL, PRIMARY KEY(shop_id, product_id));

向表ShopProduct中插入數據

--向表ShopProduct中插入數據的INSERT語句 BEGIN TRANSACTION;BEGIN INSERT INTO ShopProduct VALUES(000A, 成華區, 0001, 30);INSERT 0 1 INSERT INTO ShopProduct VALUES(000A, 成華區, 0002, 50);INSERT 0 1 INSERT INTO ShopProduct VALUES(000A, 成華區, 0003, 15);INSERT 0 1 INSERT INTO ShopProduct VALUES(000B, 金牛區, 0002, 30);INSERT 0 1 INSERT INTO ShopProduct VALUES(000B, 金牛區, 0003, 120);INSERT 0 1 INSERT INTO ShopProduct VALUES(000B, 金牛區, 0004, 20);INSERT 0 1 INSERT INTO ShopProduct VALUES(000B, 金牛區, 0006, 10);INSERT 0 1 INSERT INTO ShopProduct VALUES(000B, 金牛區, 0007, 40);INSERT 0 1 INSERT INTO ShopProduct VALUES(000C, 武侯區, 0003, 20);INSERT 0 1 INSERT INTO ShopProduct VALUES(000C, 武侯區, 0004, 50);INSERT 0 1 INSERT INTO ShopProduct VALUES(000C, 武侯區, 0006, 90);INSERT 0 1 INSERT INTO ShopProduct VALUES(000C, 武侯區, 0007, 70);INSERT 0 1 INSERT INTO ShopProduct VALUES(000D, 錦江區, 0001, 100);INSERT 0 1 COMMIT;COMMIT

確認創建的表的內容：

SELECT * FROM ShopProduct;

執行結果：

shop_id | shop_name | product_id | quantity---------+-----------+------------+---------- 000A | 成華區 | 0001 | 30 000A | 成華區 | 0002 | 50 000A | 成華區 | 0003 | 15 000B | 金牛區 | 0002 | 30 000B | 金牛區 | 0003 | 120 000B | 金牛區 | 0004 | 20 000B | 金牛區 | 0006 | 10 000B | 金牛區 | 0007 | 40 000C | 武侯區 | 0003 | 20 000C | 武侯區 | 0004 | 50 000C | 武侯區 | 0006 | 90 000C | 武侯區 | 0007 | 70 000D | 錦江區 | 0001 | 100(13 行記錄)

使用子查詢作為IN謂詞的參數：

--取得「在武侯區銷售的商品的銷售單價」 SELECT product_name, sale_price FROM Product WHERE product_id IN (SELECT product_id FROM ShopProduct WHERE shop_id = 000C);

執行結果：

product_name | sale_price--------------+------------ 運動T衫 | 4000 菜刀 | 3000 叉子 | 500 擦菜板 | 880(4 行記錄)

如果在SELECT語句中使用了子查詢，那麼即使數據發生了變更，還可以繼續使用同樣的SELECT語句。像這樣能夠應對數據變更的程序，稱為『易維護程序』。

NOT IN 和子查詢

使用子查詢作為NOT IN 的參數：

SELECT product_name, sale_price FROM Product WHERE product_id NOT IN (SELECT product_id FROM ShopProduct WHERE shop_id = 000A);

執行結果：

product_name | sale_price--------------+------------ 菜刀 | 3000 高壓鍋 | 6800 叉子 | 500 擦菜板 | 880 圓珠筆 | 100(5 行記錄)

EXISTS謂詞

EXISTS謂詞的使用方法

一言以蔽之，謂詞的作用就是「判斷是否存在某種滿足條件的記錄」。如果存在這樣的記錄就返回真（TRUE），如果不存在這樣的記錄就返回假（FALSE）。EXISTS（存在）謂詞的主語是「記錄」。

--使用EXISTS選取出「武侯區在售商品的銷售單價」 SELECT product_name, sale_price FROM Product AS P WHERE EXISTS (SELECT * FROM ShopProduct AS SP WHERE SP.shop_id = 000C AND SP.product_id = p.product_id);

執行結果：

product_name | sale_price--------------+------------ 運動T衫 | 4000 菜刀 | 3000 叉子 | 500 擦菜板 | 880(4 行記錄)

注釋：

通常指定關聯子查詢作為EXISTS的參數。
作為EXISTS參數的子查詢中通常使用SELECT *。

使用NOT EXISTS替換NOT IN

就像EXISTS可以替換IN一樣，NOT IN 也可以用NOT EXISTS 來替換。

--使用NOT EXISTS 讀取出「成華區店在售之外的商品的銷售單價」 SELECT product_name, sale_price FROM Product AS P WHERE NOT EXISTS (SELECT * FROM ShopProduct AS SP WHERE SP.shop_id = 000A AND SP.product_id = p.product_id);

執行結果：

product_name | sale_price--------------+------------ 菜刀 | 3000 高壓鍋 | 6800 叉子 | 500 擦菜板 | 880 圓珠筆 | 100(5 行記錄)

CASE表達式

什麼是CASE表達式

CASE表達式是一種進行運算的功能，它是SQL中最重要的功能之一。CASE表達式是在區分情況下使用，這種情況的區分在編程中通常叫做條件（分支）。類似於C語言中的if……else….語句。

CASE表達式的語法

CASE表達式的語法分為簡單CASE表達式和搜索CASE表達式兩種。但是搜索CASE表達式包含了簡單CASE表達式的全部功能，所以我們學習搜索CASE表達式的語法就可以了。

--搜索CASE表達式CASE WHEN <求值表達式> THEN <表達式> WHEN <求值表達式> THEN <表達式> WHEN <求值表達式> THEN <表達式>..... ELSE <表達式>END

CASE表達式會從最初的WHEN子句中的「 <求值表達式> 」進行求值運算。所謂求值，就是要調查該表達式的真值是什麼，如果結果為真（TRUE），那麼就返回THEN子句中的表達式，CASE表達式的執行到此為止。如果結果不為真，那麼就跳轉到下一條的WHEN子句的求值之中。如果知道最後的WHEN子句為止返回結果都不為真，那麼就會返回ELSE中的表達式，執行結束。

CASE表達式的使用方法

咱們用一個例子說明：

--通過CASE表達式將A~C的字元串加入到商品種類中 SELECT product_name, CASE WHEN product_type = 衣服 THEN A: || product_type WHEN product_type = 辦公用品 THEN B: || product_type WHEN product_type = 廚房用具 THEN C: || product_type ELSE NULL END AS abs_product_type FROM Product;

執行結果：

注釋：

雖然CASE表達式中的ELSE子句可以省略，但是最好不要省略。
CASE表達式中的END不能省略。

CASE表達式可以實現行列互換

使用GRUOP BY無法實現行列轉換：

SELECT product_type, SUM(sale_price) AS sum_price FROM Product GROUP BY product_type;

執行結果：

product_type | sum_price--------------+----------- 衣服 | 5000 辦公用品 | 600 廚房用具 | 11180(3 行記錄)

但是使用CASE表達式可以實現行列轉換

--對照商品種類計算出的銷售單價合計值進行行列轉換 SELECT SUM(CASE WHEN product_type = 衣服 THEN sale_price ELSE 0 END) AS sum_price_clothes, SUM(CASE WHEN product_type = 廚房用具 THEN sale_price ELSE 0 END) AS sum_price_kitchen, SUM(CASE WHEN product_type = 辦公用品 THEN sale_price ELSE 0 END) AS sum_price_office FROM Product;

執行結果：

sum_price_clothes | sum_price_kitchen | sum_price_office-------------------+-------------------+------------------ 5000 | 11180 | 600(1 行記錄)

今天的學習到此結束。加油！

每天學習一點點，每天進步一點點。