管理檢視

資料庫設計

Lis Sulmont

Curriculum Manager

建立更複雜的檢視

彙總： SUM(), AVG(), COUNT(), MIN(), MAX(), GROUP BY 等
連接： INNER JOIN, LEFT JOIN. RIGHT JOIN, FULL JOIN
條件： WHERE, HAVING, UNIQUE, NOT NULL, AND, OR,>,< 等

授與與收回檢視的存取權

GRANT privilege(s) 或 REVOKE privilege(s)

ON object

TO role 或 FROM role

權限： SELECT, INSERT, UPDATE, DELETE 等
物件： table、view、schema 等
角色： 資料庫使用者或其群組

授與與收回範例

GRANT UPDATE ON ratings TO PUBLIC;

REVOKE INSERT ON films FROM db_user;

更新檢視

UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';

並非所有檢視都可更新

檢視只由一個資料表組成
不使用視窗或彙總函式

¹ https://www.postgresql.org/docs/9.5/sql-update.html

將資料插入檢視

INSERT INTO films (code, title, did, date_prod, kind)
    VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');

並非所有檢視都可插入

¹ https://www.postgresql.org/docs/9.5/sql-insert.html

將資料插入檢視

INSERT INTO films (code, title, did, date_prod, kind)
    VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');

並非所有檢視都可插入

重點：避免透過檢視修改資料

¹ https://www.postgresql.org/docs/9.5/sql-insert.html

刪除檢視

DROP VIEW view_name [ CASCADE | RESTRICT ];

RESTRICT（預設）：若有相依於該檢視的物件，則回傳錯誤
CASCADE：同時刪除該檢視與所有相依物件

重新定義檢視

CREATE OR REPLACE VIEW view_name AS new_query

若已存在名為 view_name 的檢視，會被取代
new_query 必須產生與舊查詢相同的欄位名稱、順序與資料型別
欄位輸出內容可不同
可在最後新增欄位

若無法符合以上條件，請先刪除舊檢視再新建

¹ https://www.postgresql.org/docs/9.2/sql-createview.html

變更檢視

ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression
ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name DROP DEFAULT
ALTER VIEW [ IF EXISTS ] name OWNER TO new_owner
ALTER VIEW [ IF EXISTS ] name RENAME TO new_name
ALTER VIEW [ IF EXISTS ] name SET SCHEMA new_schema
ALTER VIEW [ IF EXISTS ] name SET ( view_option_name [= view_option_value] [, ... ] )
ALTER VIEW [ IF EXISTS ] name RESET ( view_option_name [, ... ] )

¹ https://www.postgresql.org/docs/9.2/sql-alterview.html

一起來練習吧！

資料庫設計