iT邦幫忙

2023 iThome 鐵人賽

DAY 15
0
自我挑戰組

Hello SQL 初次見面你好系列 第 15

Day 15 SQL 檢視表(VIEW)

  • 分享至 

  • xImage
  •  

在 SQL 中,「檢視表」或「視圖」是一種虛擬資料表,它基於 SQL 查詢的結果
檢視表本身不存儲數據,它只是 保存 了創建它的 SQL 查詢
當你查詢檢視表時,系統會執行保存的 SQL 查詢並返回結果

每次執行查詢檢視表時都會獲取最新的資料表的資料

  • 我們建立今天會用到的資料表,寫入一些資料
-- CREATE departments table
CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name CHARACTER VARYING
;

INSERT INTO departments (name) VALUES ('業務'), ('人資'), ('開發');
-- CREATE employees table

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name CHARACTER VARYING,
    department_id BIGINT REFERENCES departments(id) 
);

INSERT INTO employees (name, department_id)
VALUES ('james', 1), ('john', 2), ('jenny', 3) ........

建立完資料就讓我們開始吧

CREATE VIEW (建立檢視表)

語法:

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

案例 1: 假設我們需要使用 VIEW 去知道目前所有的員工人數

CREATE VIEW employee_count AS
SELECT COUNT(id) AS employee_count
FROM employees

CREATE VIEW

那我們要如何使用剛做出來的 VIEW 呢,可以像下面這樣

SELECT * FROM employee_count;


 employee_count
----------------
            101
(1 row)

案例 2: 假設我們有一個員工資料表 employees 和部門資料表 departments,我們想創建一個檢視表,以簡化查找每個部門的員工數量的查詢

CREATE VIEW department_employee_count AS
SELECT d.name AS department_name, COUNT(e.id) AS employee_count
FROM departments d
JOIN employees e ON e.department_id = d.id
GROUP BY d.name;

一樣使用

SELECT * FROM department_employee_count;

在 postgresql 你可以這樣做

-- 使用 `\dv` 指令,查看所有的 view

demo_db=# \dv
                 List of relations
 Schema |           Name            | Type | Owner
--------+---------------------------+------+-------
 public | department_employee_count | view | chan
 public | employee_count            | view | chan
(2 rows)

-- 使用 `\d+ view_name` 指令 ,可以看到這個 view 的詳細資料

demo_db=# \d+ department_employee_count

                   View "public.department_employee_count"

     Column      |       Type        | Collation | Nullable | Default | Storage  
-----------------+-------------------+-----------+----------+---------+---------
 department_name | character varying |           |          |         | extended
 employee_count  | bigint            |           |          |         | plain   
 
 
View definition:
 SELECT d.name AS department_name,
    count(e.id) AS employee_count
   FROM departments d
     JOIN employees e ON e.department_id = d.id
  GROUP BY d.name;

更新或是修改 VIEW

在指令加上 OR REPLACE 就可以修改了

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

刪除 VIEW

就可以使用我們常見的 DROP 指令

DROP VIEW view_name

為什麼要使用 SQL 檢視表?

主要原因:

  1. 簡單性和便利性: 檢視表能把複雜的查詢藏起來,所以我們只需用簡單的 SELECT 語句就能拿到想要的數據。
  2. 保護數據: 通過檢視表,我們能控制大家看到哪些列和行,這樣敏感信息就不會暴露出去。
  3. 維護性: 即便資料結構變了,只要檢視表不變,我們就不用改應用程式的代碼。
  4. 避免重複工作: 把常用的查詢寫成檢視表,以後就可以一直用。

好處:

  1. 查詢變得直觀: 檢視表會把複雜的查詢和多資料表的聯接藏起來,所以查詢看起來會簡單很多。
  2. 保護隱私: 用檢視表,我們能控制大家看到的數據,敏感信息就能得到保護。
  3. 易於維護: 如果底層的資料表結構改了,只要更新檢視表就好,不用動用到這個資料表的每一條 SQL 語句。

壞處:

  1. 性能問題: 因為每次查詢檢視表都會重新計算,所以有時候會比較慢。
  2. 更新限制: 有的檢視表不能更新,特別是那些包含聚合、分組或聯接多資料表的檢視表。

使用檢視表就像是有了一個簡單明瞭的窗口,讓我們能清晰、方便地看到底層複雜的資料表中的資料,而不需要關心裡面複雜的結構和數據


上一篇
Day 14 什麼是 SQL 觸發器 (Triggers)
下一篇
Day 16 在 PostgreSQL 中使用 序列(sequence)
系列文
Hello SQL 初次見面你好30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言