MySQL 視圖簡介

概述

資料庫中關於數據的查詢有時非常複雜，例如表連接、子查詢等，這種查詢編寫難度大，很容易出錯。另外，在具體操作表時，有時候要求只能操作部分欄位。

為了提高複雜 SQL 語句的復用性和表的操作的安全性，MySQL 提供了視圖特性。所謂視圖，本質上是一種虛擬表，同樣包含一系列帶有名稱的列和行數據。行和列的數據來自自定義視圖的查詢所引用的基本表，並在具體引用視圖時動態生成

視圖的特點如下：

  • 視圖的列可以來自不同的表，是表的抽象和邏輯意義上建立的新關係
  • 視圖是由基本表（實表）產生的表（虛表）
  • 視圖的建立和刪除不影響基本表
  • 對視圖內容的更新（增刪改）直接影響基本表
  • 當視圖來自多個基本表，不允許添加和刪除數據

創建視圖

1. 創建視圖的語法形式

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]  
VIEW view_name [column_list]  
AS SELECT statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
  • CREATE 表示創建新的視圖，REPLACE 表示替換已經創建的視圖
  • ALGORITHM 表示視圖選擇的演算法，取值有三種：
    • UNDEFINED：MySQL 自動選擇演算法
    • MERGE：將使用的視圖語句與視圖定義合併起來，使得視圖定義的某一部分取代語句對應的部分
    • TEMPLATE：表示將視圖的結果放入臨時表，然後用臨時表來執行語句
  • view_name 表示視圖的名稱
  • column_list 為屬性列
  • SELECT statement 表示 SELECT 語句
  • 參數 [WITH [CASCADED | LOCAL] CHECK OPTION] 表示視圖在更新時保證在視圖的許可權範圍內，CASCADED 與 LOCAL 為可選參數：
    • CASCADED 為默認值，表示更新視圖時要滿足所有相關視圖和表的條件
    • LOCAL 表示更新視圖時滿足該視圖本身定義的條件即可

該語句要求具有針對視圖的 CREATE VIEW 許可權，以及針對由 SELECT 語句選擇的每一列上的某些許可權。對於在 SELECT 語句中其他地方使用的列，必須具有 SELECT 許可權，如果還有 OR REPLACE 子句，就必須在視圖上具有 DROP 許可權

2. 在單表創建視圖

示例：在資料庫 company 中，由員工表 t_employee 創建出隱藏工資欄位 salary 的視圖 view_selectemployee

CREATE VIEW view_selectemployee AS
SELECT id,name,gender,age,deptno FROM t_employee;

查詢視圖

SELECT * FROM view_selectemployee;

3. 在多表創建視圖

示例：在資料庫 company 中，由部門表 t_dept 和員工表 t_employee 創建一個名為 view_dept_employee 的視圖

CREATE ALGORITHM=MERGE VIEW
view_dept_employee(name,dept,gender,age,loc)
AScSELECT iname,t_dept.deptname,gender,age,t_dept.location
FROM t_employee, t_dept WHERE t_employee.deptno = t_dept.deptno
WITH LOCAL CHECK OPTION;

查看視圖

使用 DESCRIBE | DESC 語句查看視圖基本資訊，因為視圖也是一張表，只不過比較特殊

DESCRIBE | DESC viewname

在 MySQL 中，所有視圖的定義都存在資料庫 information_schema 中的表 views 中，查詢 views 表可以得到資料庫中所有視圖的詳細資訊

SELECT * FROM information_schema.views
WHERE table_name = 'viewname' \G

修改視圖

修改視圖是指修改資料庫中存在的視圖，當基本表的某些欄位發生變化時，可以通過修改視圖來保持與基本表的一致

在 MySQL 中，CREATE OR REPLACE VIEW 語句可以用來修改視圖。當視圖已經存在，對視圖進行修改，否則創建視圖

CREATE OR REPLACE [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]  
VIEW view_name [column_list]  
AS SELECT statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

也可以使用 ALTER 語句修改視圖，其語法中的關鍵字和參數都與創建視圖是一樣的

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]  
VIEW view_name [column_list]  
AS SELECT statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

更新視圖

更新視圖是指通過視圖來插入、更新和刪除表中的數據。可以使用 SQL 語句更新視圖，達到更新基本表數據。反過來，更新基本表的數據，也會更新視圖的數據

但實際上，並不是所有視圖都可以更新，以下幾種情況是不能更新視圖的：

  • 視圖中包含 SUM()、COUNT()、MAX() 和 MIN() 函數
  • 視圖中包含 UNION、UNION ALL、DISTINCT、GROUP BY 和 HAVING 等關鍵字
  • 常量視圖，例如：CREATE VIEW view_1 AS SELECT 'Rebecca' AS name;
  • 包含子查詢的視圖
  • 由不可更新的視圖導出的視圖
  • 創建視圖時，ALGORITHM 為 TEMPTABLE 類型
  • 視圖對應的列存在沒有默認值的列，而且該列沒有包含在視圖裡
  • 設置了 [WITH [CASCADED | LOCAL] CHECK OPTION] 參數，需要符合對應條件才能更新

刪除視圖

刪除視圖是指刪除資料庫中已存在的視圖，不會刪除數據

在 MySQL 中，可以使用 DROP VIEW 語句來刪除視圖，但是用戶必須擁有 DROP 許可權

DROP VIEW viewname [viewname1,viewname2,...]

Tags:

VirMach 便宜 VPS

QNews

QNews