《SQL基礎教程》筆記（3）

《SQL基礎教程》 讀書筆記

一些常見概念的對比和區分

1.DISTINCT使用

-- 取出資料表中的非重複資料
SELECT DISTINCT shohin_id 
    FROM Shohin;

-- 計算去除重複資料後的資料行數
SELECT COUNT(DISTINCT shohin_bunrui)
    FROM Shohin;

2.指定升序或降序

SELECT shohin_id, shohin_mei, shohin_tanka
    FROM Shohin
ORDER BY hanbai_tanka DESC;

3.別名

• GROUP BY不可以使用別名
• ORDER BY可以使用別名

4.聚合函式

只有SELECT和HAVING子句，以及ORDER BY子句中能夠使用聚合函式

5.GROUP BY 和 ORDER BY

• 使用GROUP BY子句時，SELECT子句中不能出現聚合鍵之外的列。
• 使用ORDER BY子句時，SELECT子句之外的列也可以使用。

6.插入預設值

CREATE TABLE ShohinIns
( shohin_id CHAR(4) NOT NULL,
  hanbai_tanka INTEGER DEFAULT 0, -- 銷售單價的預設值設定為0；
  PRIMARY KEY (shohin_id)
);

-- 通過顯示方法插入預設值(在VALUES子句中指定DEFAULT關鍵字)
INSERT INTO ShohinIns (shohin_id, hanbai_tanka)
VALUES('0007',DEFAULT);

-- 通過隱式方法插入預設值（不使用DEFAULT關鍵字，只要在列清單和VALUES中省略設定了預設值的列就可以了）
INSERT INTO ShohinIns (shohin_id)
VALUES('007');

注：更推薦第一種方法，更加一目瞭然地知道hanbai_tanka列使用了預設值，SQL語句地含義也更加任意理解。

省略INSERT語句中的列名，就會自動設定為該列的預設值（沒有預設值會設定為NULL）。

7.從其他表中複製資料

插入資料的方法，除了使用VALUES子句指定具體的資料之外，還可以從其他表中複製資料。

執行INSERT…SELECT語句時，可以在表之間進行資料傳遞，可以用於資料備份時候使用。

INSERT INTO ShohinCopy (shohin_id, shohin_mei, shohin_bunrui)
SELECT shohin_id, shobin_mei, shobin_bunrui
    FROM Shobin;

8.檢視VIEW

檢視包含“不能使用ORDER BY”和“可對其進行有限制的更新”兩項限制。

刪除檢視需要使用DROP VIEW (<檢視列名1>,<檢視列名2>,...)語句。

檢視的使用：

CREATE VIEW ShohinSum(shohin_bunrui, cnt_shohin) --檢視的列名
AS
SELECT shohin_bunrui, COUNT(*)
    FROM Shohin
GROUP BY shohin_bunrui;

-- 檢視的使用
SELECT shohin_bunrui, cnt_shohin
    FROM ShohinSum;

shohin_bunrui   cnt_shohin
衣服              2
辦公用品            2
廚房用具            4

檢視的限制1-定義檢視時不能使用ORDER BY子句

檢視的限制2-對檢視進行更新

如果定義檢視的SELECT語句能夠滿足某些條件，那麼這個檢視就可以被更新。例如：

• SELECT子句中未使用DISTINCT
• FROM子句中只有一張表
• 未使用GROUP BY子句
• 未使用HAVING子句

9.子查詢

子查詢就是一次性的檢視（SELECT語句）。與檢視不同，子查詢在SELECT語句執行完畢之後就會消失。

由於子查詢需要命名，因此需要根據處理內容來指定恰當的名稱。

標量子查詢就是隻能返回一行一列的子查詢。(是子查詢返回一行一列，最終的查詢並不一定。)

-- 直接在FROM子句中使用定義檢視的SELECT語句
SELECT shohin_bunrui, cnt_shohin
    FROM ( SELECT shohin_bunrui, COUNT(*) AS cnt_shohin
            FROM Shohin
            GROUP BY shohin_bunrui
    ) AS ShohinSum;

子查詢就是將用來定義檢視的SELECT語句直接用於FROM子句當中。雖然“AS ShohinSum”就是子查詢的名稱，但由於該名稱是一次性的，因此不會像檢視那樣儲存在介質（硬碟）之中，而是在SELECT語句執行之後就消失了。子查詢（subquery）就是”次級(sub)”的”查詢（query）”。

標量子查詢：就是返回單一值得子查詢

SELECT shohin_id, shohinmei, hanbai_tanka
    FROM Shohin
WHERE hanbai_tanka > (SELECT AVG(hanbai_tanka)
                      FROM Shohin);

SELECT AVG(hanbai_tanka)
    FROM Shohin;
-- 上面就是標量子查詢，SELECT語句的查詢結果是單一的值。

標量子查詢的返回值可以用在=或者<>這樣需要單一值的比較運算子之中。這也是標量子查詢的優勢所在。

標量子查詢的書寫位置並不僅僅侷限於WHERE子句中，通常任何可以使用單一值得位置都可以使用。也就是說，能夠使用常數或者列名的地方，無論是SELECT子句、GROUP BY子句、HAVING子句，還是ORDER BY子句，幾乎所有的地方都可以使用。

-- SELECT子句中使用標量子查詢
SELECT shohin_id,
       shohin_mei,
       hanbai_tanka,
       (SELECT AVG(hanbai_tanka)
           FROM Shohin) AS avg_tanka
    FROM Shohin;

-- HAVING 子句中使用標量子查詢
SELECT shohin——bunrui, AVG(hanbai_tanka)
    FROM Shohin
    GROUP BY shohin_bunrui
HAVING AVG(hanbai_tanka) > (SELECT AVG(hanbai_tanka)
                               FROM Shohin);

在WHERE子句中使用標量子查詢：

“查詢出銷售單價高於平均銷售單價得商品。”

SELECT shohin_id,shohinmei,hanbai_tanka
    FROM Shohin
WHERE hanbai_tanka > AVG(hanbai_tanka)

雖然這樣得SELECT語句看上去能夠滿足我們的要求，但是由於在WHERE子句中不能使用聚合函式，因此這樣的SELECT語句是錯誤的。

-- 計算平均銷售單價的標量子查詢
SELECT AVG(hanbai_tanka)
    FROM Shohin;

-- 選取出銷售單價（hanbai_tanka）高於全部商品的平均單價的商品
SELECTR shohin_id,shohin_mei,shohin_tanka
    FROM Shohin
WHERE hanbai_tanka > (SELECT AVG(hanbai_tanka)　FROM Shohin)

10.關聯子查詢

• 關聯子查詢會在細分的組內比較時使用
• 關聯子查詢和GROUP BY子句一樣，也可以對錶中的資料進行切分
• 關聯子查詢的結合條件如果未出現在子查詢之中就會發生錯誤

按此前所學，使用子查詢就能選取出銷售單價高於全部商品平均銷售單價的物品。但是，我們改變下條件，選取出各商品分類中高於該分類平均銷售單價的商品。

我們並不是要以全部商品為基礎，而是要以細分的組為基礎。對組內商品的平均價格和各商品的銷售單價進行比較。

-- 按照商品分類計算平均價格
SELECT AVG(hanbai_tank)
    FROM Shohin
GROUP BY shohin_bunrui;

-- 通過關聯子查詢按照商品種類對平均銷售單價進行比較
SELECT shohin_id, shohin_mei, hanbai_tanka
    FROM Shohin AS S1
WHERE hanbai_tanka > (
    SELECT AVG(hanbai_tanka)
        FROM Shohin AS S2
    WHERE S1.shohin_bunrui = S2.shohin_bunrui
    GROUP BY shohin_bunrui
);

這樣我們就能選取出辦公用品、衣服和廚房用具三類商品中高於該類商品平均銷售單價的商品了。這裡起關鍵作用的就是在子查詢中新增的WHERE子句的條件。該條件的意思就是，在同一商品種類中對各商品的銷售單價和平均單價進行比較。

關聯子查詢也是用來對集合進行切分的：

關聯子查詢執行時，DBMS內部的執行結果如下圖所示：

11.CASE表示式

-- 搜尋CASE表示式
CASE
    WHEN <判斷表示式> THEN <表示式> 
    WHEN <判斷表示式> THEN <表示式>
    WHEN <判斷表示式> THEN <表示式>
    ...
    ELSE <表示式>
END

-- 通過CASE表示式將A-C的字串加入到商品分類當中
SELECT shohin_mei,
    CASE WHEN shohin_bunrui = '衣服'
        THEN 'A:' || shohin_bunrui
        WHEN shohin_bunrui = '辦公用品'
        THEN 'B:' || shohin_bunrui
        WHEN shohin_bunrui = '廚房用具'
        THEN 'C:' || shohin_bunrui
        ELSE NULL
    END AS abc_shohin_bunrui
FROM Shohin;

shohin_mei  abc_shohin_bunrui
T恤衫     A:衣服
打孔器     B:辦公用品
運動T恤        A:衣服
菜刀          C:廚房用具
高壓鍋     C:廚房用具
叉子          C:廚房用具
擦菜板     C:廚房用具
圓珠筆     B:辦公用品