SQL CREATE VIEW 語句

在本教程中,你將學習如何使用 SQL 建立、更新和刪除檢視。

建立檢視以簡化表訪問

檢視是一個虛擬表,其定義儲存在資料庫中。但是,與表不同,檢視實際上不包含任何資料。相反,它提供了一種在資料庫中儲存常用複雜查詢的方法。但是,你可以使用 SQL SELECT 語句中的檢視來訪問資料,就像使用普通表或基表一樣。

通過允許使用者通過檢視訪問資料,而不是讓他們直接訪問整個基表,檢視也可以用作安全機制。

語法

使用該 CREATE VIEW 語句建立檢視。

CREATE VIEW view_name AS select_statement ; 

為了清楚地理解這一點,讓我們看看以下 employeesdepartments 表。

Table: employees

+--------+--------------+------------+---------+
| emp_id | emp_name     | hire_date  | dept_id |
+--------+--------------+------------+---------+
|      1 | Ethan Hunt   | 2001-05-01 |       4 |
|      2 | Tony Montana | 2002-07-15 |       1 |
|      3 | Sarah Connor | 2005-10-18 |       5 |
|      4 | Rick Deckard | 2007-01-03 |       3 |
|      5 | Martin Blank | 2008-06-24 |    NULL |
+--------+--------------+------------+---------+

Table: departments

+---------+------------------+
| dept_id | dept_name        |
+---------+------------------+
|       1 | Administration   |
|       2 | Customer Service |
|       3 | Finance          |
|       4 | Human Resources  |
|       5 | Sales            |
+---------+------------------+

假設你要檢索員工的 ID 和名稱及其部門名稱,則需要執行左連線操作,如下所示:

SELECT t1.emp_id, t1.emp_name, t2.dept_name
FROM employees AS t1 LEFT JOIN departments AS t2
ON t1.dept_id = t2.dept_id;

執行上述查詢後,你將獲得如下輸出:

+--------+--------------+-----------------+
| emp_id | emp_name     | dept_name       |
+--------+--------------+-----------------+
|      1 | Ethan Hunt   | Human Resources |
|      2 | Tony Montana | Administration  |
|      3 | Sarah Connor | Sales           |
|      4 | Rick Deckard | Finance         |
|      5 | Martin Blank | NULL            |
+--------+--------------+-----------------+

但是,只要你想要訪問此記錄,就需要再次鍵入整個查詢。如果你經常進行這樣的操作,它會變得非常不方便和煩人。

在這種情況下,你可以建立一個檢視以使查詢結果更易於訪問,如下所示:

CREATE VIEW emp_dept_view AS
SELECT t1.emp_id, t1.emp_name, t2.dept_name
FROM employees AS t1 LEFT JOIN departments AS t2
ON t1.dept_id = t2.dept_id;

現在,你可以使用檢視 emp_dept_view 訪問相同的記錄,如下所示:

SELECT * FROM emp_dept_view;

因為,你可以看到你可以節省多少時間和精力。

提示: 檢視始終顯示最新資料! 每次查詢檢視時,資料庫引擎都會執行與檢視關聯的 SQL 查詢並重新建立資料。

注意: 在 MySQL 中,你還可以在檢視定義中指定 ORDER BY 子句。但是,在 SQL Sever 中,檢視定義不能包含 ORDER BY 子句,除非在 SELECT 語句的選擇列表中也有一個 TOP 子句。

替換現有檢視

在 MySQL 中,如果要更新或替換現有檢視,可以刪除該檢視並建立新檢視,或者只使用 OR REPLACE 語句中的子句 CREATE VIEW ,如下所示:

CREATE OR REPLACE VIEW view_name AS select_statement ; 

注意:OR REPLACE 語句中使用該 CREATE VIEW 子句時,如果檢視不存在,它將建立一個新檢視,否則將替換現有檢視。

以下 SQL 語句將通過向其新增新列 salary 來替換或更改現有檢視 emp_dept_view 的定義

-- Syntax for MySQL Database 
CREATE OR REPLACE VIEW emp_dept_view AS
SELECT t1.emp_id, t1.emp_name, t1.salary, t2.dept_name
FROM employees AS t1 LEFT JOIN departments AS t2
ON t1.dept_id = t2.dept_id;

更新檢視後,如果執行以下語句:

SELECT * FROM emp_dept_view ORDER BY emp_id;

你將在結果輸出中看到另一個列 salary,如下所示:

+--------+--------------+--------+-----------------+
| emp_id | emp_name     | salary | dept_name       |
+--------+--------------+--------+-----------------+
|      1 | Ethan Hunt   |   5000 | Human Resources |
|      2 | Tony Montana |   6500 | Administration  |
|      3 | Sarah Connor |   8000 | Sales           |
|      4 | Rick Deckard |   7200 | Finance         |
|      5 | Martin Blank |   5600 | NULL            |
+--------+--------------+--------+-----------------+

注意: SQL Server 不支援 OR REPLACE 子句,因此要替換檢視,你只需刪除該檢視並重新建立一個新檢視。

通過檢視更新資料

從理論上講,你也可以在檢視中除了執行 SELECT 外,還執行 INSERTUPDATE 以及 DELETE 操作。但是,並非所有檢視都是可更新的,即能夠修改基礎源表的資料。可更新性有一些限制。

通常,如果檢視包含以下任何內容,則檢視不可更新:

  • DISTINCTGROUP BYHAVING 子句。
  • 聚合函式,例如 AVG()COUNT()SUM()MIN()MAX() 等等。
  • UNIONUNION ALLCROSSJOINEXCEPTINTERSECT 運算子。
  • WHERE 子句中的子查詢,它引用在 FROM 子句中的表。

如果檢視滿足這些條件,則可以使用該檢視修改源表。

以下語句將更新 emp_id 等於 1 的員工的 salary

UPDATE emp_dept_view SET salary = '6000' 
WHERE emp_id = 1;

注意: 對於可插入性,檢視必須包含基表中沒有預設值的所有列。類似地,對於可更新性,檢視中的每個可更新列必須對應於源表中的可更新列。

刪除檢視

同樣,如果你不再需要檢視,則可以使用 DROP VIEW 語句將其從資料庫中刪除,如以下語法所示:

DROP VIEW view_name ; 

以下命令將從資料庫中刪除檢視 emp_dept_view

DROP VIEW emp_dept_view;