在 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 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;
-- 使用 `\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;
在指令加上 OR REPLACE
就可以修改了
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
就可以使用我們常見的 DROP 指令
DROP VIEW view_name
使用檢視表就像是有了一個簡單明瞭的窗口,讓我們能清晰、方便地看到底層複雜的資料表中的資料,而不需要關心裡面複雜的結構和數據