PostgreSQL數據庫視圖及子查詢怎么使用

本篇內容介紹了“PostgreSQL數據庫視圖及子查詢怎么使用”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

視圖

表里面保存的是實際數據，視圖里面保存的是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數據庫視圖及子查詢怎么使用”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！