数据库视图

数据库设计

Lis Sulmont

Curriculum Manager

数据库视图

在数据库中，视图是对数据的存储查询所得到的结果集，用户可像查询持久化的集合对象一样查询它（维基百科）

不属于物理模式的虚拟表

存储的是查询而非数据
数据来自表中聚合
可像常规表一样查询
无需重复输入常用查询或更改模式

¹ 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

Passons à la pratique !

数据库设计