MySQL 檢視簡介

低吟不作語發表於2022-04-16
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,...]

相關文章