快捷導(dǎo)航

PostgreSQL數(shù)據(jù)庫視圖及子查詢使用操作

更新時(shí)間：2022年04月11日 11:34:40 作者：_jym

這篇文章主要為大家介紹了PostgreSQL數(shù)據(jù)庫視圖及子查詢的使用操作，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步早日升職加薪

視圖

表里面保存的是實(shí)際數(shù)據(jù)，視圖里面保存的是SELECT語句(視圖本身不存儲(chǔ)數(shù)據(jù))。

從視圖中讀取數(shù)據(jù)，此時(shí)視圖在內(nèi)部執(zhí)行SELECT語句，創(chuàng)建一張臨時(shí)表。

使用視圖的好處：其一，視圖不保存數(shù)據(jù)，節(jié)省存儲(chǔ)設(shè)備容量。其二，將頻繁使用的SELECT語句保存成視圖，每次使用這些語句時(shí)候，不用重復(fù)書寫，只需調(diào)用視圖。其三，數(shù)據(jù)保存到表中，要顯式的執(zhí)行SQL更新語句才能更新數(shù)據(jù)，而視圖中的數(shù)據(jù)會(huì)隨著原表的變化自動(dòng)更新。

創(chuàng)建視圖

格式：

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表中取數(shù)據(jù)。

并且，如果Product表中數(shù)據(jù)更新，視圖也自動(dòng)更新。

這是因?yàn)椋晥D就是保存好的SELECT語句。

SELECT product_type, cnt_product
  FROM ProductSum;

在這里插入圖片描述

多重視圖：以視圖為基礎(chǔ)創(chuàng)建視圖。但是這樣會(huì)降低SQL性能。

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

在這里插入圖片描述

定義視圖時(shí)，不能用ORDER BY子句。因?yàn)橐晥D和表，數(shù)據(jù)行都沒有順序。

(PostgreSQL里面，定義視圖時(shí)候可以用ORDER BY子句，有些DBMS不行)

視圖更新：

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

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

通過匯總得到的數(shù)據(jù)無法更新，這是因?yàn)橐晥D和表要同時(shí)更新。

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

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

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

向視圖插入數(shù)據(jù)。

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

此時(shí)可看到，視圖和表都更新了。

在這里插入圖片描述

刪除視圖：

格式

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

例子

DROP VIEW ProductSum;

然后報(bào)錯(cuò)

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.

這是因?yàn)榍懊嬉訮roductSum為基礎(chǔ)，創(chuàng)建了一個(gè)ProductSumA視圖。

可以像下面這樣刪除ProductSum和與之關(guān)聯(lián)的視圖。

DROP VIEW ProductSum CASCADE;

子查詢

子查詢，相當(dāng)于一次性視圖。

定義視圖ProductSum

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

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

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

下面代碼，執(zhí)行順序，先是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發(fā)現(xiàn)，ProductSum已經(jīng)不存在了。由此看出，子查詢是一次性的，并不像視圖一樣保存到硬盤里面。

在這里插入圖片描述

在子查詢的FROM子句里面，可以繼續(xù)使用子查詢。

下面就是把ProductSum里面cnt_product = 4的數(shù)據(jù)選出來了。

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;

在這里插入圖片描述

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

可以在WHERE子句中使用標(biāo)量子查詢。

由于WHERE子句中無法使用聚合函數(shù)，像下面的語句就是錯(cuò)誤的。

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

可以通過下面這樣去實(shí)現(xiàn)。

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

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

在SELECT子句中使用標(biāo)量子查詢：

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子句中使用標(biāo)量子查詢：

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

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

標(biāo)量子查詢不能返回多行結(jié)果，如果返回多行結(jié)果，那就是一個(gè)普通的子查詢，不能用到需要單一輸入值的地方了。

關(guān)聯(lián)子查詢

現(xiàn)在要選取各個(gè)商品種類里面，高于該商品種類平均銷售價(jià)的商品。

按照商品種類計(jì)算平均價(jià)格：

SELECT AVG(sale_price)
  FROM Product
 GROUP BY product_type;

因?yàn)橛腥N商品，上面這個(gè)查詢返回三個(gè)結(jié)果。

在這里插入圖片描述

那么就不能用下面這種方法了。因?yàn)樽硬樵儾皇菢?biāo)量子查詢，不能在WHERE子句里面用。

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

在細(xì)分的組內(nèi)進(jìn)行比較的時(shí)候，用到關(guān)聯(lián)子查詢。

在子查詢里面添加了一個(gè)WHERE子句。目的是在同一商品種類中對(duì)各商品銷售單價(jià)和平均單價(jià)比較。

由于比較對(duì)象是同一個(gè)Product表，所以用了P1、P2兩個(gè)別名。

使用關(guān)聯(lián)子查詢，用<表名>.<列名>形式，限定product_type，對(duì)平均單價(jià)比較。

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，也能得到相同結(jié)果：

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數(shù)據(jù)庫視圖及子查詢使用操作的詳細(xì)內(nèi)容，更多關(guān)于PostgreSQL數(shù)據(jù)庫的視圖子查詢的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: