PostgreSQL資料庫檢視及子查詢使用操作

2022-04-11 16:00:48

檢視

表裡面儲存的是實際資料，檢視裡面儲存的是SELECT語句(檢視本身不儲存資料)。

從檢視中讀取資料，此時檢視在內部執行SELECT語句，建立一張臨時表。

使用檢視的好處：其一，檢視不儲存資料，節省儲存裝置容量。其二，將頻繁使用的SELECT語句儲存成檢視，每次使用這些語句時候，不用重複書寫，只需呼叫檢視。其三，資料儲存到表中，要顯式的執行SQL更新語句才能更新資料，而檢視中的資料會隨著原表的變化自動更新。

建立檢視

格式：

CREATE VIEW 檢視名稱(<檢視列名1>,<檢視列名2>,...)
AS
<SELECT語句>

例子：

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

使用檢視

可見，如果使用檢視，不用每次都寫GROUP BY等一些語句從Product表中取資料。

並且，如果Product表中資料更新，檢視也自動更新。

這是因為，檢視就是儲存好的SELECT語句。

SELECT product_type, cnt_product
  FROM ProductSum;

多重檢視：以檢視為基礎建立檢視。但是這樣會降低SQL效能。

CREATE VIEW ProductSumA (product_type, cnt_product)
AS
SELECT product_type, cnt_product
  FROM ProductSum
 WHERE product_type = '辦公用品';

定義檢視時，不能用ORDER BY子句。因為檢視和表，資料行都沒有順序。

(PostgreSQL裡面，定義檢視時候可以用ORDER BY子句，有些DBMS不行)

檢視更新：

如果定義檢視的SELECT語句滿足一些條件，檢視可以被更新。

SELECT子句沒用DISTINCT、FROM子句只有一張表、沒用GROUP BY、沒用HAVING。

通過彙總得到的資料無法更新，這是因為檢視和表要同時更新。

如果給上面的ProductSum中新增(‘食物’,3)的資料，原表就需要增加三行種類為食物的資料，但是這些資料我們都不知道，因此沒法更新表中的資料。

可以更新下面這樣，不通過彙總得到的檢視。

CREATE VIEW ProductA (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
AS 
SELECT *
  FROM Product
 WHERE product_type = '辦公用品';

向檢視插入資料。

INSERT INTO ProductA VALUES ('0009', '鉛筆', '辦公用品', 95, 10, '2222-10-1');

此時可看到，檢視和表都更新了。

刪除檢視：

格式

DROP VIEW 檢視名稱(<檢視列名1>,<檢視列名2>,...)

例子

DROP VIEW ProductSum;

然後報錯

ERROR:  cannot drop view productsum because other objects depend on it
描述:  view productsuma depends on view productsum
提示:  Use DROP ... CASCADE to drop the dependent objects too.

這是因為前面以ProductSum為基礎，建立了一個ProductSumA檢視。

可以像下面這樣刪除ProductSum和與之關聯的檢視。

DROP VIEW ProductSum CASCADE;

子查詢

子查詢，相當於一次性檢視。

定義檢視ProductSum

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

子查詢：將定義檢視的SELECT語句直接用到FROM子句裡面。

AS ProductSum，ProductSum是子查詢的名稱。執行完外邊的SELECT語句，子查詢就消失了。

下面程式碼，執行順序，先是FROM子句裡面的SELECT語句，然後是外邊的SELECT語句。

SELECT product_type, cnt_product
  FROM (SELECT product_type, COUNT(*) AS cnt_product
          FROM Product
         GROUP BY product_type) AS ProductSum;

下面再次檢視ProductSum發現，ProductSum已經不存在了。由此看出，子查詢是一次性的，並不像檢視一樣儲存到硬碟裡面。

在子查詢的FROM子句裡面，可以繼續使用子查詢。

下面就是把ProductSum裡面cnt_product = 4的資料選出來了。

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;

標量子查詢scalar subquery，返回表中某一行某一列的值(單一值)的子查詢。

可以在WHERE子句中使用標量子查詢。

由於WHERE子句中無法使用聚合函數，像下面的語句就是錯誤的。

SELECT product_id, product_name, sale_price
  FROM Product
 WHERE sale_price > AVG(sale_price);

可以通過下面這樣去實現。

SELECT product_id, product_name, sale_price FROM Product WHERE sale_price > (SELECT AVG(sale_price) FROM Product);SELECT product_id, product_name, sale_price
  FROM Product
 WHERE sale_price > (SELECT AVG(sale_price)
                         FROM Product);

在任何使用單一值的地方，都可以使用標量子查詢。

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

SELECT product_id, product_name, sale_price, (SELECT AVG(sale_price) FROM Product) AS avg_price FROM Product;SELECT product_id, 
       product_name, 
       sale_price,
       (SELECT AVG(sale_price)
          FROM Product) AS avg_price
  FROM Product;

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

不同商品種類的平均銷售單價與全部商品的銷售單價相比。

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

標量子查詢不能返回多行結果，如果返回多行結果，那就是一個普通的子查詢，不能用到需要單一輸入值的地方了。

關聯子查詢

現在要選取各個商品種類裡面，高於該商品種類平均銷售價的商品。

按照商品種類計算平均價格：

SELECT AVG(sale_price)
  FROM Product
 GROUP BY product_type;

因為有三種商品，上面這個查詢返回三個結果。

那麼就不能用下面這種方法了。因為子查詢不是標量子查詢，不能在WHERE子句裡面用。

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

在細分的組內進行比較的時候，用到關聯子查詢。

在子查詢裡面新增了一個WHERE子句。目的是在同一商品種類中對各商品銷售單價和平均單價比較。

由於比較物件是同一個Product表，所以用了P1、P2兩個別名。

使用關聯子查詢，用<表名>.<列名>形式，限定product_type，對平均單價比較。

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

而且，不加GROUP BY，也能得到相同結果：

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

以上就是PostgreSQL資料庫檢視及子查詢使用操作的詳細內容，更多關於PostgreSQL資料庫的檢視子查詢的資料請關注it145.com其它相關文章！