學習SQL【6】-複雜查詢

05-12

到目前為止，我們學習了表的創建、查詢和更新等資料庫的基本操作方法。現在我們將會在這些基本方法的基礎上，學習一些實際應用的方法。

一：視圖

1：視圖和表

表中存儲的是實際數據，而視圖中保存的是從表中獲取數據所使用的SELECT語句。從SQL的角度來看，視圖和表是一樣的，只是視圖並不存儲數據，而是存儲SELECT語句。

視圖的優點：

視圖的優點大體上有兩點。

● 第一點是由於視圖無需保存數據，因此可以節省存儲設備的容量。

● 第二點是可以將頻繁使用的SELECT語句保存成視圖，這樣就不用每次重新書寫了。

所以應該將經常使用的SELECT語句做成視圖。

2：創建視圖的方法

創建視圖需要使用CREATE VIEW語句，其語法如下：

CREATE VIEW 視圖名稱（<視圖列名1>, <視圖列名2>,...） AS <SELECT語句>

注釋：

SELECT語句需要書寫在AS關鍵字之後，SELECT語句中列的排列順序和視圖中列的排列順序相同。

接下來，我們仍然使用最開始創建的Product表為基礎進行下面的演示。Product表的結構和內容如下所示：

product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+------------- 0001 | T衫 | 衣服 | 1000 | 500 | 2017-09-20 0002 | 打孔器 | 辦公用品 | 500 | 320 | 2017-09-11 0003 | 運動T衫 | 衣服 | 4000 | 2800 | 0004 | 菜刀 | 廚房用具 | 3000 | 2800 | 2017-09-20 0005 | 高壓鍋 | 廚房用具 | 6800 | 5000 | 2017-01-15 0006 | 叉子 | 廚房用具 | 500 | | 2017-09-20 0007 | 擦菜板 | 廚房用具 | 880 | 790 | 2016-04-28 0008 | 圓珠筆 | 辦公用品 | 100 | | 2017-11-11(8 行記錄)

下面就讓我們試著創建視圖吧：

--ProductSum視圖 CREATE VIEW ProductSum (product_type, cnt_product) AS SELECT product_type, COUNT(*) FROM Product GROUP BY product_type;

這樣我們就創建了一個名為ProductSum的視圖。

視圖和表一樣，可以書寫在SELECT語句的FROM子句中。

--使用視圖 SELECT product_type, cnt_product FROM ProductSum;

執行結果：

product_type | cnt_product--------------+------------- 衣服 | 2 辦公用品 | 2 廚房用具 | 4(3 行記錄)

使用視圖的查詢的步驟：

① 首先執行定義視圖的SELECT語句。

② 根據得到的結果，再執行在FROM子句中使用視圖的SELECT語句。

還可以創建多重視圖，即是在視圖的基礎上再創建視圖。

--視圖ProductSumJimCREATE VIEW ProductSumJim (product_type, cnt_product) ASSELECT product_type, cnt_product FROM ProductSum WHERE product_type = 辦公用品;

確認創建好的視圖:

SELECT * FROM ProductSumJim;

執行結果：

product_type | cnt_product--------------+------------- 辦公用品 | 2(1 行記錄)

注釋：

● 對大多數DBMS來說，多重視圖會降低SQL性能，所以我們應該避免使用多重視圖。

● 定義視圖不要使用ORDER BY子句。

● 視圖和表需要同時進行更新，因此通過匯總得到的視圖無法進行更新。

不是通過匯總得到的視圖就可以進行更新：

CREATE VIEW ProductJim (product_id, product_name, product_type, sale_price, purchase_price, regist_date) ASSELECT * FROM Product WHERE product_type = 辦公用品;

向視圖中添加數據行:

INSERT INTO ProductJim VALUES (0009, 印章, 辦公用品, 95, 10, 2017-11-30);

確認數據是否已經添加到視圖中:

SELECT * FROM ProductJim;

執行結果：

product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+------------- 0002 | 打孔器 | 辦公用品 | 500 | 320 | 2017-09-11 0008 | 圓珠筆 | 辦公用品 | 100 | | 2017-11-11 0009 | 印章 | 辦公用品 | 95 | 10 | 2017-11-30(3 行記錄)

確認數據是否添加到原表中:

SELECT * FROM Product;

執行結果：

3：刪除視圖

刪除視圖需要使用DROP VIEW語句

例如，刪除視圖ProductSum：

DROP VIEW ProductSum;

但是在PostgreSQL中，由於視圖ProductSum存在關聯視圖ProductSumJim,因此會發生如下錯誤：

錯誤: 無法刪除視圖 productsum 因為有其它對象倚賴它描述: 視圖 productsumjim 倚賴於視圖 productsum提示: 使用 DROP .. CASCADE 把倚賴對象一併刪除.

這時可以使用CASCADE選項來刪除關聯視圖：

DROP VIEW ProductSum CASCADE;

我們再次將Product表恢復到初始狀態（8行），因此我們要刪掉剛才添加進的第九行：

DELETE FROM Product WHERE product_id = 0009;

二：子查詢

1：子查詢與視圖

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

子查詢的特點：將用來定義視圖的SELECT語句直接用於FROM子句中。

例如：

--在FROM子句中直接書寫定義視圖的SELECT語句 SELECT product_type, cnt_product FROM (SELECT product_type, COUNT(*) AS cnt_product FROM Product GROUP BY product_type) AS ProductSum;

兩種方法得到的結果完全相同。

product_type | cnt_product--------------+------------- 衣服 | 2 辦公用品 | 2 廚房用具 | 4(3 行記錄)

注釋：子查詢作為內層查詢會首先執行。

增加子查詢的層數：

由於子查詢的層數原則上沒有限制，因此可以在子查詢的FROM子句中再繼續使用子查詢語句。

--增加子查詢的嵌套層數 SELECT product_type, cnt_product FROM (SELECT * FROM ( SELECT product_type, COUNT(*) AS cnt_product FROM Product GROUP BY product_type) AS ProductSum WHERE cnt_product = 4) AS ProductSum2;

執行結果：

product_type | cnt_product--------------+------------- 廚房用具 | 4(1 行記錄)

但是隨著子查詢的層數增加，SQL語句會變得愈發地難以讀懂，所以應該避免使用多層嵌套的子查詢語句。

2：子查詢的名稱

原則上子查詢必須設定名稱。為子查詢設定名稱時需要使用關鍵字AS。

3：標量子查詢

標量就是單一的意思，而標量子查詢則有一個特殊的限制，那就是必須而且只能返回1行1列的結果。

也就是說標量子查詢是返回單一值的子查詢。

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

比如，我們需要查出銷售單價高於平均銷售單價的商品：

先計算出平均銷售單價：

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

執行結果：

avg----------------------- 2097.5000000000000000(1 行記錄)

然後完整的SQL代碼如下所示：

--選取出銷售單價高於全部商品的平均單價的商品 SELECT product_id, product_name, sale_price FROM Product WHERE sale_price > (SELECT AVG(sale_price) FROM Product);

執行結果：

product_id | product_name | sale_price------------+--------------+------------ 0003 | 運動T衫 | 4000 0004 | 菜刀 | 3000 0005 | 高壓鍋 | 6800(3 行記錄)

4：標量子查詢的書寫位置

能夠使用常數或者列名的地方，無論是SELECT語句、GROUP BY 子句、HAVING子句，還是ORDER BY 子句。幾乎所有的地方都可以使用。

例如：

--在SELECT子句中使用標量子查詢 SELECT product_id, product_name, sale_price, (SELECT AVG(sale_price) FROM Product) AS avg_price FROM Product;

執行結果：

product_id | product_name | sale_price | avg_price------------+--------------+------------+----------------------- 0001 | T衫 | 1000 | 2097.5000000000000000 0002 | 打孔器 | 500 | 2097.5000000000000000 0003 | 運動T衫 | 4000 | 2097.5000000000000000 0004 | 菜刀 | 3000 | 2097.5000000000000000 0005 | 高壓鍋 | 6800 | 2097.5000000000000000 0006 | 叉子 | 500 | 2097.5000000000000000 0007 | 擦菜板 | 880 | 2097.5000000000000000 0008 | 圓珠筆 | 100 | 2097.5000000000000000(8 行記錄)

在HAVING子句中使用標量子查詢:

SELECT product_type, AVG(sale_price) FROM Product GROUP BY product_type HAVING AVG(sale_price) > (SELECT AVG(sale_price) FROM Product);

執行結果：

product_type | avg--------------+----------------------- 衣服 | 2500.0000000000000000 廚房用具 | 2795.0000000000000000(2 行記錄)

三：關聯子查詢

1：普通子查詢與關聯子查詢的區別

按此前所學，使用子查詢就能選出銷售單價高於全部商品平均銷售單價的商品，這次我們稍微更改一下需求，選取出各種商品中高於該類商品平均銷售單價的商品。

如果我們使用標量子查詢的方法就會發生錯誤：

SELECT product_type, product_name, sale_price FROM Product WHERE sale_price > (SELECT AVG(sale_price) FROM Product GROUP BY product_type);

發生錯誤的原因就是該子查詢會返回3行結果，並不是標量子查詢，而在WHERE子句中使用子查詢時，必須是標量子查詢。

這個時候就可以使用關聯子查詢來解決上述問題。

--通過關聯子查詢按照商品種類對平均銷售單價進行比較 SELECT product_type, product_name, sale_price FROM Product AS P1 WHERE sale_price > (SELECT AVG(sale_price) FROM Product AS P2 WHERE P1.product_type = P2.product_type GROUP BY product_type);

執行結果：

product_type | product_name | sale_price--------------+--------------+------------ 辦公用品 | 打孔器 | 500 衣服 | 運動T衫 | 4000 廚房用具 | 菜刀 | 3000 廚房用具 | 高壓鍋 | 6800(4 行記錄)

這裡的關鍵就是在子查詢中添加WHERE子句的條件。該條件的意思就是，在同一商品種類中對各個商品的銷售單價和平均單價進行比較。

因此，在細分的組內進行比較時，需要使用關聯子查詢。

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

換個角度來看，其實關聯子查詢也和GROUP BY子句一樣，可以對集合進行切分。

3：關聯條件一定要寫在子查詢內

關聯名稱就是像P1，P2這樣作為表的別名的名稱，它存在一個有效的範圍，也就是它的作用域。具體來說，子查詢內部設定的關聯名稱，只能在該子查詢內部使用。

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