iT邦幫忙

第 11 屆 iT 邦幫忙鐵人賽

DAY 24
1

前面介紹了 新建資料庫、資料表,插入資料,資料型態、匯入匯出方法,也介紹很多天的GIS 接下來來介紹一些不一樣的,不再是資料面的部份。

資料庫的 view ,之前有請教一個前輩,專門管資料庫的,他說他都用 VIEW 給別人查詢而已!第一次聽到蠻酷的,而且說 VIEW 查詢速度也不慢,它會向下去延伸。但小弟我技術還淺,我先來介紹怎麼建立資料庫的 view ,邁向大師第一步 哈哈哈/images/emoticon/emoticon07.gif (還早)

開始

有時候常用到的查詢,可能要 JOIN 很多張資料表,如何讓 SQL 簡化呢?可以嘗試看看 VIEW 這個功能。對於安全性限制,以及前面所述減少查詢語法的複雜度,有一定程度的幫助。

CREATE OR REPLACE VIEW adoption_dog_gov AS
    SELECT  subid, 
            bodytype, 
            colour, 
            age
    FROM adoption_gov_animals
    WHERE kind = '狗'
    ORDER BY subid;

如上所述 CREATE OR REPLACE VIEW 關鍵字,建立一個 VIEW 其中有 OR REPLACE 關鍵字表示如果已經有相同 VIEW 直接取代。這個View 的樣子就像 AS 關鍵字後面的查詢SELECT 語法一樣,產生這張虛擬的表。

實際操作

  1. 打開 pgAdmin 在 animal 資料庫上方點選右鍵 Tools -> Query Tool

  2. 貼上上方SQL語法(前提是你有做這系列前面的範例)

  3. 並點選 animal 資料庫 右鍵 Refresh。

  4. 依序展開樹狀清單 Schemas -> public -> Views 就可以看到剛剛新建的 adoption_dog_gov View 了喔!

https://ithelp.ithome.com.tw/upload/images/20191010/20105865Y7lNYD5Dqa.png

對 View 的查詢

查詢 View 的資料,就跟一般查詢資料表一樣。

SELECT *
FROM adoption_dog_gov; --這是 view 

https://ithelp.ithome.com.tw/upload/images/20191010/201058656djaA0EqcV.png

如上圖所示,只會出現剛剛在建立View 時的查詢條件,有點像預先存好SQL這是快捷的方式!你也可以而外加上限制條件,如下所示!

SELECT *
FROM adoption_dog_gov --這是 view 
LIMIT 6;

找出六筆 adoption_dog_gov 的資料,就只會顯示剛剛建立 View 的四個欄位,以及只顯示狗的資料並且依造subid正向排序,這樣就是 View 簡化查詢的好處。

https://ithelp.ithome.com.tw/upload/images/20191010/20105865v8rrIIJRxw.png

用原資料表查詢要這樣寫,就是剛剛建立 View 後面那一串查詢語法,並且附加上LIMIT只顯示六個內容。

-- 用原資料表查詢
SELECT  subid, 
        bodytype, 
        colour, 
        age, 
FROM adoption_gov_animals --這是資料表
WHERE kind = '狗'
ORDER BY subid
LIMIT 6;

--用 View查詢
SELECT *
FROM adoption_dog_gov --這是 view 
LIMIT 6;

-- 兩個是相同效果

如果常常用到不訪試試看把一些相同的部分寫成View!上面的範例可能比較簡單一點,如果較複雜的SQL語法會非常幫助喔!

刪除 VIEW

DROP VIEW adoption_dog_gov;

插入、更新、刪除VIEW 裡的資料

如果你的 View 是查詢單一資料表,沒有 JOIN 其他資料表,並且未使用到 GROUP BYDISTINCT 或其他子查詢,就可以對View執行插入資料、更新以及刪除的動作。

但是如果我們已經有資料表了,為什麼還要再用view 來插入、更新、刪除呢?

之前有提到 一對一 關聯式架構,可能因為一些原因把比較不相干的內容,或是較隱私的內容,分到另外一個資料表,交由資料庫管理權限去限制查詢。

如果用 View 也可以達到 這樣的效果,可以多一層View 給可以看的人看部分資料!原始的資料表還是保有其他欄位資料。

參考來源

官方文件:https://www.postgresql.org/docs/current/sql-createview.html


上一篇
PostGIS 分析空間資料 (5) - 資料空間的查詢 尋找你的鄰居
下一篇
PostgreSQL 內建/自訂 Function
系列文
後端前進PostgreSQL30

尚未有邦友留言

立即登入留言