資料庫檢視

資料庫設計

Lis Sulmont

Curriculum Manager

資料庫檢視

在資料庫中，view（檢視） 是對資料執行並儲存的查詢結果集，使用者可像查詢一般持久性資料表物件一樣查詢它（維基百科）。

不屬於實體綱要的虛擬資料表

儲存的是查詢，不是資料
從資料表彙整資料
可像一般資料表一樣查詢
免重複輸入常用查詢或更改綱要

¹ https://en.wikipedia.org/wiki/View_(SQL)

建立檢視（語法）

CREATE VIEW view_name AS

SELECT col1, col2 
FROM table_name 
WHERE condition;

建立檢視（範例）

雪花綱要中的書籍維度

目標：回傳 science fiction 類型的書名與作者

建立檢視（範例）

CREATE VIEW scifi_books AS

SELECT title,  author, genre
FROM dim_book_sf
JOIN dim_genre_sf ON dim_genre_sf.genre_id = dim_book_sf.genre_id
JOIN dim_author_sf ON dim_author_sf.author_id = dim_book_sf.author_id
WHERE dim_genre_sf.genre = 'science fiction';

查詢檢視（範例）

SELECT * FROM scifi_books

| title                         | author            | genre           |
|-------------------------------|-------------------|-----------------|
| The Naked Sun                 | Isaac Asimov      | science fiction |
| The Robots of Dawn            | Isaac Asimov      | science fiction |
| The Time Machine              | H.G. Wells        | science fiction |
| The Invisible Man             | H.G. Wells        | science fiction |
| The War of the Worlds         | H.G. Wells        | science fiction |
| Wild Seed (Patternmaster, #1) | Octavia E. Butler | science fiction |
| ...                           | ...               | ...             |

幕後發生了什麼

SELECT * FROM scifi_books

SELECT * FROM 
(SELECT title,  author, genre
FROM dim_book_sf
JOIN dim_genre_sf ON dim_genre_sf.genre_id = dim_book_sf.genre_id
JOIN dim_author_sf ON dim_author_sf.author_id = dim_book_sf.author_id
WHERE dim_genre_sf.genre = 'science fiction');

檢視清單

（在 PostgreSQL）

SELECT * FROM INFORMATION_SCHEMA.views;

包含系統檢視

SELECT * FROM information_schema.views
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

排除系統檢視

檢視的好處

不佔儲存空間
一種存取控制
- 隱藏敏感欄位並限制使用者可見範圍
遮蔽查詢複雜度
- 對高度正規化綱要很實用

$$ Pitchfork 評論資料庫的綱要

¹ https://www.kaggle.com/nolanbconaway/pitchfork-data

一起來練習吧！

資料庫設計