《MySQL 基礎篇》八：約束和檢視

Author: ACatSmiling

Since: 2024-09-20

約束

常見的資料庫物件

物件	描述
表（TABLE）	表是儲存資料的邏輯單元，以行和列的形式存在，列就是欄位，行就是記錄
資料字典	即系統表，存放資料庫相關資訊的表。系統表的資料通常由資料庫系統維護，程式設計師通常不應該修改，只可檢視
約束（CONSTRAINT）	執行資料校驗的規則，用於保證資料完整性的規則
檢視（VIEW）	一個或者多個資料表裡的資料的邏輯顯示，檢視並不儲存資料
索引（INDEX）	用於提高查詢效能，相當於書的目錄
儲存過程（PROCEDURE）	用於完成一次完整的業務處理，沒有返回值，但可透過傳出引數將多個值傳給呼叫環境
儲存函式（FUNCTION）	用於完成一次特定的計算，具有一個返回值
觸發器（TRIGGER）	相當於一個事件監聽器，當資料庫發生特定事件後，觸發器被觸發，完成相應的處理

概述

為什麼需要約束

資料完整性（Data Integrity）是指資料的精確性（Accuracy）和可靠性（Reliability）。它是防止資料庫中存在不符合語義規定的資料和防止因錯誤資訊的輸入輸出造成無效操作或錯誤資訊而提出的。

為了保證資料的完整性，SQL 規範以約束的方式對錶資料進行額外的條件限制。從以下四個方面考慮：

• 實體完整性（Entity Integrity）：例如，同一個表中，不能存在兩條完全相同無法區分的記錄。
• 域完整性（Domain Integrity） ：例如：年齡範圍 0~120，性別範圍 "男/女"。
• 引用完整性（Referential Integrity）：例如：員工所在部門，在部門表中要能找到這個部門。
• 使用者自定義完整性（User-defined Integrity） ：例如：使用者名稱唯一、密碼不能為空等，本部門經理的工資不得高於本部門職工的平均工資的 5 倍。

什麼是約束

約束，constraint，是表級的強制規定。可以在建立表時規定約束（透過 CREATE TABLE 語句），或者在表建立之後透過 ALTER TABLE 語句規定
約束。

約束的分類

根據約束資料列的限制，約束可分為：

• 單列約束：每個約束只約束一列。
• 多列約束：每個約束可約束多列資料。

根據約束的作用範圍，約束可分為：

• 列級約束：只能作用在一個列上，跟在列的定義後面。
• 表級約束：可以作用在多個列上，不與列一起，而是單獨定義。

根據約束起的作用，約束可分為：

• NOT NULL：非空約束，規定某個欄位不能為空。
• UNIQUE：唯一約束，規定某個欄位在整個表中是唯一的。
• PRIMARY KEY：主鍵（非空且唯一）約束。
• FOREIGN KEY：外來鍵約束。
• CHECK：檢查約束。MySQL 不支援 CHECK 約束，但可以使用 CHECK 約束，只是沒有任何效果。
• DEFAULT：預設值約束。

檢視某個表已有的約束：

# information_schema 資料庫名（系統庫）
# table_constraints 表名稱（專門儲存各個表的約束）
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名稱';

mysql> SELECT * FROM information_schema.table_constraints WHERE table_name = 'employees';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def                | atguigudb         | emp_email_uk    | atguigudb    | employees  | UNIQUE          | YES      |
| def                | atguigudb         | emp_emp_id_pk   | atguigudb    | employees  | UNIQUE          | YES      |
| def                | atguigudb         | PRIMARY         | atguigudb    | employees  | PRIMARY KEY     | YES      |
| def                | atguigudb         | emp_dept_fk     | atguigudb    | employees  | FOREIGN KEY     | YES      |
| def                | atguigudb         | emp_job_fk      | atguigudb    | employees  | FOREIGN KEY     | YES      |
| def                | atguigudb         | emp_manager_fk  | atguigudb    | employees  | FOREIGN KEY     | YES      |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
6 rows in set (0.00 sec)

非空約束

作用：限定某個欄位/某列的值，不允許為空。

關鍵字：NOT NULL。

特點：

• 預設，所有的型別的值都可以是 NULL，包括 INT、FLOAT 等資料型別。
• 非空約束只能出現在表物件的列上，只能某個列單獨限定非空，不能組合非空。
• 一個表可以有很多列都分別限定了非空。
• 空字串 '' 不等於 NULL，0 也不等於 NULL。

新增非空約束：

# 建表時
CREATE TABLE 表名稱(
    欄位名 資料型別,
    欄位名 資料型別 NOT NULL,
    欄位名 資料型別 NOT NULL
);

# 建表後
ALTER TABLE 表名稱 MODIFY 欄位名 資料型別 NOT NULL;

刪除非空約束：

# 去掉 NOT NULL，相當於修改某個非註解欄位，該欄位允許為空
ALTER TABLE 表名稱 MODIFY 欄位名 資料型別 NULL;

# 去掉 not null，相當於修改某個非註解欄位，該欄位允許為空
ALTER TABLE 表名稱 MODIFY 欄位名 資料型別;

唯一性約束

作用：用來限制某個欄位/某列的值不能重複。

關鍵字：UNIQUE。

特點：

• 同一個表可以有多個唯一約束。
• 唯一約束可以是某一個列的值唯一，也可以多個列組合的值唯一。
• 唯一性約束允許列值為空，也允許出現多個 NULL 值。
• 在建立唯一約束的時候，如果不給唯一約束命名，就預設和列名相同。
• MySQL 會給唯一約束的列上預設建立一個唯一索引。

新增唯一約束：

# 建表時
CREATE TABLE 表名稱(
    欄位名 資料型別,
    欄位名 資料型別 UNIQUE,
    欄位名 資料型別 UNIQUE KEY,
    欄位名 資料型別
);

# 欄位列表中如果是一個欄位，表示該列的值唯一，如果是兩個或更多個欄位，那麼複合唯一，即多個欄位的組合是唯一的
CREATE TABLE 表名稱(
    欄位名 資料型別,
    欄位名 資料型別,
    欄位名 資料型別,
    [CONSTRAINT 約束名] UNIQUE KEY(欄位名)
);

# 建表後
# 方式一
ALTER TABLE 表名稱 ADD UNIQUE KEY(欄位列表);

# 方式二
ALTER TABLE 表名稱 MODIFY 欄位名 欄位型別 UNIQUE;

示例：

mysql> CREATE TABLE USER(
    ->     id INT NOT NULL,
    ->     NAME VARCHAR(25),
    ->     PASSWORD VARCHAR(16),
    ->     -- 使用表級約束語法，表示使用者名稱和密碼組合不能重複
    ->     CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD)
    -> );
Query OK, 0 rows affected (0.05 sec)

刪除唯一約束：

• 新增唯一性約束的列上也會自動建立唯一索引。
• 刪除唯一約束只能透過刪除唯一索引的方式刪除。
• 刪除時需要指定唯一索引名，唯一索引名就和唯一約束名一樣。
• 如果建立唯一約束時未指定名稱，如果是單列，就預設和列名相同；如果是組合列，那麼預設和 () 中排在第一個的列名相同。也可以自定義唯一性約束名。

# 檢視都有哪些約束
SELECT * FROM information_schema.table_constraints WHERE table_name = 表名; 

# 刪除約束
ALTER TABLE 表名 DROP INDEX 約束名;

關於複合唯一性約束：

CREATE TABLE 表名稱(
    欄位名 資料型別,
    欄位名 資料型別,
    欄位名 資料型別,
    UNIQUE KEY(欄位列表) # 欄位列表中寫的是多個欄位名，多個欄位名用逗號分隔，表示複合唯一約束，即多個欄位的組合是唯一的
);

主鍵約束

作用：用來唯一標識表中的一行記錄。

關鍵字：PRIMARY KEY。

特點：

• 主鍵約束相當於唯一約束 + 非空約束的組合，主鍵約束列不允許重複，也不允許出現空值。
• 一個表最多隻能有一個主鍵約束，建立主鍵約束可以在列級別建立，也可以在表級別上建立。
• 主鍵約束對應著表中的一列或者多列（複合主鍵）。
• 如果是多列組合的複合主鍵約束，那麼這些列都不允許為空值，並且組合的值不允許重複。MySQL 的主鍵名總是 PRIMARY，就算自己命名了主鍵約束名也沒用。
• 當建立主鍵約束時，系統預設會在所在的列或列組合上建立對應的主鍵索引（能夠根據主鍵查詢的，就根據主鍵查詢，效率更高）。如果刪除主鍵約束了，主鍵約束對應的索引就自動刪除了。
• 需要注意的一點是，不要修改主鍵欄位的值。因為主鍵是資料記錄的唯一標識，如果修改了主鍵的值，就有可能會破壞資料的完整性。

新增主鍵約束：

# 建表時指定主鍵約束
CREATE TABLE 表名稱(
    欄位名 資料型別 PRIMARY KEY, # 列級模式
    欄位名 資料型別,
    欄位名 資料型別
);
CREATE TABLE 表名稱(
    欄位名 資料型別,
    欄位名 資料型別,
    欄位名 資料型別,
    [CONSTRAINT 約束名] PRIMARY KEY(欄位名) # 表級模式
);

# 建表後增加主鍵約束
ALTER TABLE 表名稱 ADD PRIMARY KEY(欄位列表); # 欄位列表可以是一個欄位，也可以是多個欄位，如果是多個欄位的話，是複合主鍵

刪除主鍵約束：

ALTER TABLE 表名稱 DROP PRIMARY KEY;

說明：刪除主鍵約束，不需要指定主鍵名，因為一個表只有一個主鍵，刪除主鍵約束後，非空還存在。

關於複合主鍵約束：

CREATE TABLE 表名稱(
    欄位名 資料型別,
    欄位名 資料型別,
    欄位名 資料型別,
	PRIMARY KEY(欄位名1,欄位名2) # 表示欄位 1 和欄位 2 的組合是唯一的，也可以有更多個欄位
);

外來鍵約束

作用：限定某個表的某個欄位的引用完整性。

關鍵字：FOREIGN KEY。

• 主表（父表）：被引用的表，被參考的表。
• 從表（子表）：引用別人的表，參考別人的表。

特點：

• 從表的外來鍵列，必須引用/參考主表的主鍵或唯一約束的列，因為被依賴/被參考的值必須是唯一的。
• 在建立外來鍵約束時，如果不給外來鍵約束命名，預設名不是列名，而是自動產生一個外來鍵名（例如 student_ibfk_1），也可以指定外來鍵約束名。
• 若建立表時就指定外來鍵約束，需先建立主表，再建立從表。
• 刪表時，先刪從表（或先刪除外來鍵約束），再刪除主表。
• 當主表的記錄被從表參照時，主表的記錄將不允許刪除，如果要刪除資料，需要先刪除從表中依賴該記錄的資料，然後才可以刪除主表的資料。
• 在從表中指定外來鍵約束，一個表可以建立多個外來鍵約束。
• 從表的外來鍵列與主表被參照的列名字可以不相同，但是資料型別必須一樣，邏輯意義一致。如果型別不一樣，建立子表時，會出現錯誤。
• 當建立外來鍵約束時，系統預設會在所在的列上建立對應的普通索引，索引名是外來鍵的約束名。（根據外來鍵查詢效率很高）
• 刪除外來鍵約束後，必須手動刪除對應的索引。

新增外來鍵約束：

# 建表時
CREATE TABLE 主表名稱(
    欄位1 資料型別 PRIMARY KEY,
    欄位2 資料型別
);

CREATE TABLE 從表名稱(
欄位1 資料型別 PRIMARY KEY,
欄位2 資料型別,
[CONSTRAINT <外來鍵約束名稱>] FOREIGN KEY (從表的某個欄位) REFERENCES 主表名 (被參考欄位)
);
# （從表的某個欄位）的資料型別必須與主表名（被參考欄位）的資料型別一致，邏輯意義也一樣
# （從表的某個欄位）的欄位名可以與主表名（被參考欄位）的欄位名一樣，也可以不一樣
-- FOREIGN KEY：在表級指定子表中的列
-- REFERENCES：標示在父表中的列

# 建表後
ALTER TABLE 從表名 ADD [CONSTRAINT 約束名] FOREIGN KEY (從表的欄位) REFERENCES 主表名 (被引用欄位) [ON UPDATE 約束等級][ON DELETE 約束等級];

一般情況下，表與表的關聯都是提前設計好了的，因此，會在建立表的時候就把外來鍵約束定義好。不過，如果需要修改表的設計（比如新增新的欄位，增加新的關聯關係），但沒有預先定義外來鍵約束，那麼，就要用修改表的方式來補充定義。

外來鍵約束關係是針對主表和從表雙方的：

• 新增了外來鍵約束後，主表的修改和刪除資料受約束。
• 新增了外來鍵約束後，從表的新增和修改資料受約束。
• 在從表上建立外來鍵，要求主表必須存在。
• 刪除主表時，要求從表從表先刪除，或將從表中外來鍵引用該主表的關係先刪除。

在 MySQL 裡，外來鍵約束是有成本的，需要消耗系統資源。對於大併發的 SQL 操作，有可能會不適合。比如大型網站的中央資料庫，可能會 因為外來鍵約束的系統開銷而變得非常慢 。所以，MySQL 允許不使用系統自帶的外來鍵約束，應在應用層面完成檢查資料一致性的邏輯。也就是說，即使不用外來鍵約束，也要想辦法透過應用層面的附加邏輯，來實現外來鍵約束的功能，確保資料的一致性。

約束等級：

• Cascade方式 ：在父表上 UPDATE/DELETE 記錄時，同步 UPDATE/DELETE 子表的匹配記錄。
• Set null方式 ：在父表上 UPDATE/DELETE 記錄時，將子表上匹配記錄的列設為 NULL，但是要注意子表的外來鍵列不能為 NULL。
• No action方式 ：如果子表中有匹配的記錄，則不允許對父表對應候選鍵進行 UPDATE/DELETE 操作。
• Restrict方式 ：同 No action， 都是立即檢查外來鍵約束。
• Set default方式 （在視覺化工具 SQLyog 中可能顯示空白）：父表有變更時，子表將外來鍵列設定成一個預設的值，但 Innodb 不能識別。
• 如果沒有指定等級，就相當於 Restrict 方式。
• 對於外來鍵約束，最好是採用ON UPDATE CASCADE ON DELETE RESTRICT的方式。

刪除外來鍵約束：

# 第一步，先檢視約束名和刪除外來鍵約束
SELECT * FROM information_schema.table_constraints WHERE table_name = 表名稱;# 檢視某個表的約束名

ALTER TABLE 從表名 DROP FOREIGN KEY 外來鍵約束名;

# 第二步，檢視索引名和刪除索引，注意，只能手動刪除
SHOW INDEX FROM 表名稱; # 檢視某個表的索引名

ALTER TABLE 從表名 DROP INDEX 索引名;

阿里開發規範：

【 強制 】不得使用外來鍵與級聯，一切外來鍵概念必須在應用層解決。

說明：（概念解釋）學生表中的 student_id 是主鍵，那麼成績表中的 student_id 則為外來鍵。如果更新學生表中的 student_id，同時觸發成績表中的 student_id 更新，即為級聯更新。外來鍵與級聯更新適用於單機低併發，不適合分散式、高併發叢集；級聯更新是強阻塞，存在資料庫更新風暴的風險；外來鍵影響資料庫的插入速度。

檢查約束

作用：檢查某個欄位的值是否符號某種要求，一般指的是值的範圍。

關鍵字：CHECK。

MySQL 5.7 可以使用 CHECK 約束，但 CHECK 約束對資料驗證沒有任何作用，新增資料時，沒有任何錯誤或警告，MySQL 8.0 中可以使用 CHECK 約束。

預設約束

作用：給某個欄位/某列指定預設值，一旦設定預設值，在插入資料時，如果此欄位沒有顯式賦值，則賦值為預設值。

關鍵字：DEFAULT。

新增預設約束：

# 建表時
CREATE TABLE 表名稱(
    欄位名 資料型別 PRIMARY KEY,
    欄位名 資料型別 UNIQUE KEY NOT NULL,
    欄位名 資料型別 UNIQUE KEY,
    欄位名 資料型別 NOT NULL DEFAULT 預設值
);

CREATE TABLE 表名稱(
    欄位名 資料型別 DEFAULT 預設值 ,
    欄位名 資料型別 NOT NULL DEFAULT 預設值,
    欄位名 資料型別 NOT NULL DEFAULT 預設值,
    PRIMARY KEY(欄位名),
    UNIQUE KEY(欄位名)
);

# 建表後
ALTER TABLE 表名稱 MODIFY 欄位名 資料型別 DEFAULT 預設值;

# 如果這個欄位原來有非空約束，那麼在加預設值約束時，還得保留非空約束，否則非空約束就被刪除了
# 同理，在給某個欄位加非空約束也一樣，如果這個欄位原來有預設值約束，你想保留，也要在 modify 語句中保留預設值約束，否則就刪除了
ALTER TABLE 表名稱 MODIFY 欄位名 資料型別 DEFAULT 預設值 NOT NULL;

說明：預設值約束一般不在唯一鍵和主鍵列上加。

刪除預設值約束：

# 刪除預設值約束，也不保留非空約束
ALTER TABLE 表名稱 MODIFY 欄位名 資料型別;

# 刪除預設值約束，保留非空約束
ALTER TABLE 表名稱 MODIFY 欄位名 資料型別 NOT NULL; 

自增約束

作用：設定某個欄位的值自增。

關鍵字：AUTO_INCREMENT。

特點：

• 一個表最多隻能有一個自增長列。
• 當需要產生唯一識別符號或順序值時，可設定自增長。
• 自增長列約束的列必須是鍵列（主鍵列，唯一鍵列）。
• 自增約束的列的資料型別必須是整數型別。
• 如果自增列指定了 0 和 NULL，會在當前最大值的基礎上自增；如果自增列手動指定了具體值，直接賦值為具體值。

新增自增約束：

# 建表時
CREATE TABLE 表名稱(
    欄位名 資料型別 PRIMARY KEY AUTO_INCREMENT,
    欄位名 資料型別 UNIQUE KEY NOT NULL,
    欄位名 資料型別 UNIQUE KEY,
    欄位名 資料型別 NOT NULL DEFAULT 預設值
);

CREATE TABLE 表名稱(
    欄位名 資料型別 DEFAULT 預設值 ,
    欄位名 資料型別 UNIQUE KEY AUTO_INCREMENT,
    欄位名 資料型別 NOT NULL DEFAULT 預設值,
    PRIMARY KEY(欄位名)
);

# 建表後
ALTER TABLE 表名稱 MODIFY 欄位名 資料型別 AUTO_INCREMENT;

刪除自增約束：

# ALTER TABLE 表名稱 MODIFY 欄位名 資料型別 AUTO_INCREMENT; # 給這個欄位增加自增約束
ALTER TABLE 表名稱 MODIFY 欄位名 資料型別; # 去掉 AUTO_INCREMENT，相當於刪除

MySQL 8.0 新特性 - 自增變數的持久化：

在 MySQL 8.0 之前，自增主鍵 AUTO_INCREMENT 的值如果大於 max(primary key) + 1，此時，如果刪除最大的資料，然後重慶 MySQL 後，會重置 AUTO_INCREMENT=max(primary key)+1，再次新增資料時，主鍵會等於重啟前被刪除的資料的主鍵值，這種現象在某些情況下會導致業務主鍵衝突或者其他難以發現的問題。

在 MySQL 5.7 系統中，對於自增主鍵的分配規則，是由 InnoDB 資料字典內部一個計數器來決定的，而該計數器只在記憶體中維護，並不會持久化到磁碟中，當資料庫重啟時，該計數器會被初始化。

MySQL 8.0 將自增主鍵的計數器持久化到重做日誌中。每次計數器發生改變，都會將其寫入重做日誌中。如果資料庫重啟，InnoDB 會根據重做日誌中的資訊來初始化計數器的記憶體值。

檢視

概述

檢視，VIEW，一方面可以幫我們使用表的一部分而不是所有的表，另一方面也可以針對不同的使用者制定不同的查詢檢視。比如，針對一個公司的銷售人員，我們只想給他看部分資料，而某些特殊的資料，比如採購的價格，則不會提供給他。再比如，人員薪酬是個敏感的欄位，那麼只給某個級別以上的人員開放，其他人的查詢檢視中則不提供這個欄位。

檢視的理解

• 檢視是一種虛擬表，本身是不具有資料的，佔用很少的記憶體空間，它是 SQL 中的一個重要概念。

• 檢視建立在已有表的基礎上，檢視賴以建立的這些表稱為基表。

• 檢視的建立和刪除隻影響檢視本身，不影響對應的基表。但是當對檢視中的資料進行增加、刪除和修改操作時，資料表中的資料會相應地發生變化，反之亦然。

  • 在資料庫中，檢視不會儲存資料，資料真正儲存在資料表中。當對檢視中的資料進行增加、刪除和修改操作時，資料表中的資料會相應地發生變化；反之亦然。

• 向檢視提供資料內容的語句為 SELECT 語句，可以將檢視理解為儲存起來的 SELECT 語句。

• 檢視，是向使用者提供基表資料的另一種表現形式。通常情況下，小型專案的資料庫可以不使用檢視，但是在大型專案中，以及資料表比較複雜的情況下，檢視的價值就凸顯出來了，它可以幫助我們把經常查詢的結果集放到虛擬表中，提升使用效率。

建立檢視

在 CREATE VIEW 語句中嵌入子查詢：

CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 檢視名稱 [(欄位列表)]
AS 查詢語句
[WITH [CASCADED|LOCAL] CHECK OPTION];

# 簡化
CREATE VIEW 檢視名稱
AS 查詢語句;

建立單表檢視

mysql> CREATE VIEW empvu80
    -> AS
    -> SELECT employee_id, last_name, salary
    -> FROM employees
    -> WHERE department_id = 80;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM empvu80;
+-------------+------------+----------+
| employee_id | last_name  | salary   |
+-------------+------------+----------+
|         145 | Russell    | 14000.00 |
|         146 | Partners   | 13500.00 |
|         147 | Errazuriz  | 12000.00 |
|         148 | Cambrault  | 11000.00 |
|         149 | Zlotkey    | 10500.00 |
|         150 | Tucker     | 10000.00 |
|         151 | Bernstein  |  9500.00 |
|         152 | Hall       |  9000.00 |
|         176 | Taylor     |  8600.00 |
|         177 | Livingston |  8400.00 |
|         179 | Johnson    |  6200.00 |
+-------------+------------+----------+
34 rows in set (0.00 sec)

mysql> CREATE VIEW emp_year_salary (ename, year_salary)
    -> AS
    -> SELECT last_name, salary * 12 * (1 + IFNULL(commission_pct, 0))
    -> FROM employees;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM emp_year_salary;
+-------------+-------------+
| ename       | year_salary |
+-------------+-------------+
| King        |   288000.00 |
| Kochhar     |   204000.00 |
| De Haan     |   204000.00 |
| Hunold      |   108000.00 |
| Ernst       |    72000.00 |
| Hartstein   |   156000.00 |
| Fay         |    72000.00 |
| Mavris      |    78000.00 |
| Baer        |   120000.00 |
| Higgins     |   144000.00 |
| Gietz       |    99600.00 |
+-------------+-------------+
107 rows in set (0.00 sec)
    
mysql> CREATE VIEW salvu50
    -> AS
    -> SELECT employee_id ID_NUMBER, last_name NAME, salary * 12 ANN_SALARY
    -> FROM employees
    -> WHERE department_id = 50;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM salvu50;
+-----------+-------------+------------+
| ID_NUMBER | NAME        | ANN_SALARY |
+-----------+-------------+------------+
|       120 | Weiss       |   96000.00 |
|       121 | Fripp       |   98400.00 |
|       122 | Kaufling    |   94800.00 |
|       123 | Vollman     |   78000.00 |
|       124 | Mourgos     |   69600.00 |
|       125 | Nayer       |   38400.00 |
|       126 | Mikkilineni |   32400.00 |
|       193 | Everett     |   46800.00 |
|       194 | McCain      |   38400.00 |
|       195 | Jones       |   33600.00 |
|       196 | Walsh       |   37200.00 |
|       197 | Feeney      |   36000.00 |
|       198 | OConnell    |   31200.00 |
|       199 | Grant       |   31200.00 |
+-----------+-------------+------------+
45 rows in set (0.00 sec)

說明 1：實際上就是在 SQL 查詢語句的基礎上封裝了檢視 VIEW，這樣就會基於 SQL 語句的結果集形成一張虛擬表。

說明 2：在建立檢視時，沒有在檢視名後面指定欄位列表，則檢視中欄位列表預設和 SELECT 語句中的欄位列表一致。如果 SELECT 語句中給欄位取了別名，那麼檢視中的欄位名和別名相同。

建立多表聯合檢視

mysql> CREATE VIEW empview
    -> AS
    -> SELECT e.employee_id emp_id, e.last_name, d.department_name
    -> FROM employees e, departments d
    -> WHERE e.department_id = d.department_id;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM empview;
+--------+-------------+------------------+
| emp_id | last_name   | department_name  |
+--------+-------------+------------------+
|    200 | Whalen      | Administration   |
|    201 | Hartstein   | Marketing        |
|    202 | Fay         | Marketing        |
|    114 | Raphaely    | Purchasing       |
|    160 | Doran       | Sales            |
|    161 | Sewall      | Sales            |
|    113 | Popp        | Finance          |
|    205 | Higgins     | Accounting       |
|    206 | Gietz       | Accounting       |
+--------+-------------+------------------+
106 rows in set (0.00 sec)

# 左外連線，比上面的內連線資料多了一條
mysql> CREATE VIEW empview2
    -> AS
    -> SELECT e.employee_id emp_id, e.last_name, d.department_name
    -> FROM employees e LEFT JOIN departments d
    -> ON e.department_id = d.department_id;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM empview2;
+--------+-------------+------------------+
| emp_id | last_name   | department_name  |
+--------+-------------+------------------+
|    100 | King        | Executive        |
|    101 | Kochhar     | Executive        |
|    102 | De Haan     | Executive        |
|    164 | Marvins     | Sales            |
|    172 | Bates       | Sales            |
|    173 | Kumar       | Sales            |
|    174 | Abel        | Sales            |
|    175 | Hutton      | Sales            |
|    176 | Taylor      | Sales            |
|    177 | Livingston  | Sales            |
|    178 | Grant       | NULL             |
|    204 | Baer        | Public Relations |
|    205 | Higgins     | Accounting       |
|    206 | Gietz       | Accounting       |
+--------+-------------+------------------+
107 rows in set (0.00 sec)

mysql> CREATE VIEW emp_dept
	-> AS
	-> SELECT e.last_name, e.department_name
	-> FROM employees e LEFT JOIN departments d
	-> ON e.department_id = d.department_id;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM emp_dept;
+-------------+------------------+
| last_name   | department_name  |
+-------------+------------------+
| King        | Executive        |
| Kochhar     | Executive        |
| De Haan     | Executive        |
| Hunold      | IT               |
| Livingston  | Sales            |
| Grant       | NULL             |
| Johnson     | Sales            |
| Taylor      | Shipping         |
| Mavris      | Human Resources  |
| Baer        | Public Relations |
| Higgins     | Accounting       |
| Gietz       | Accounting       |
+-------------+------------------+
107 rows in set (0.00 sec)

mysql> CREATE VIEW dept_sum_vu(name, minsal, maxsal, avgsal)
    -> AS
    -> SELECT d.department_name, MIN(e.salary), MAX(e.salary), AVG(e.salary)
    -> FROM employees e, departments d
    -> WHERE e.department_id = d.department_id
    -> GROUP BY d.department_name;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM dept_sum_vu;
+------------------+----------+----------+--------------+
| name             | minsal   | maxsal   | avgsal       |
+------------------+----------+----------+--------------+
| Executive        | 17000.00 | 24000.00 | 19333.333333 |
| IT               |  4200.00 |  9000.00 |  5760.000000 |
| Finance          |  6900.00 | 12000.00 |  8600.000000 |
| Purchasing       |  2500.00 | 11000.00 |  4150.000000 |
| Shipping         |  2100.00 |  8200.00 |  3475.555556 |
| Sales            |  6100.00 | 14000.00 |  8955.882353 |
| Administration   |  4400.00 |  4400.00 |  4400.000000 |
| Marketing        |  6000.00 | 13000.00 |  9500.000000 |
| Human Resources  |  6500.00 |  6500.00 |  6500.000000 |
| Public Relations | 10000.00 | 10000.00 | 10000.000000 |
| Accounting       |  8300.00 | 12000.00 | 10150.000000 |
+------------------+----------+----------+--------------+
11 rows in set (0.00 sec)

基於檢視建立檢視

當建立好一張檢視之後，還可以在它的基礎上繼續建立檢視。

舉例：聯合 emp_dept 檢視和 emp_year_salary 檢視查詢員工姓名、部門名稱、年薪資訊，建立 emp_dept_ysalary 檢視。

mysql> CREATE VIEW emp_dept_ysalary
    -> AS
    -> SELECT emp_dept.last_name, emp_dept.department_name, emp_year_salary.year_salary
    -> FROM emp_dept INNER JOIN emp_year_salary
    -> ON emp_dept.last_name = emp_year_salary.last_name;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM emp_dept_ysalary;
+-------------+------------------+-------------+
| last_name   | department_name  | year_salary |
+-------------+------------------+-------------+
| King        | Sales            |   288000.00 |
| King        | Executive        |   288000.00 |
| Kochhar     | Executive        |   204000.00 |
| De Haan     | Executive        |   204000.00 |
| Hunold      | IT               |   108000.00 |
| Ernst       | IT               |    72000.00 |
| Grant       | NULL             |    31200.00 |
| Whalen      | Administration   |    52800.00 |
| Hartstein   | Marketing        |   156000.00 |
| Fay         | Marketing        |    72000.00 |
| Mavris      | Human Resources  |    78000.00 |
| Baer        | Public Relations |   120000.00 |
| Higgins     | Accounting       |   144000.00 |
| Gietz       | Accounting       |    99600.00 |
+-------------+------------------+-------------+
117 rows in set (0.01 sec)

利用檢視對資料進行格式化

開發中，經常需要輸出某個格式的內容，比如想輸出員工姓名和對應的部門名，對應格式為 emp_name(department_name)，就可以使用檢視來完成資料格式化的操作：

mysql> CREATE VIEW emp_depart
	-> AS
	-> SELECT CONCAT(e.last_name, '(', d.department_name, ')') AS emp_dept
	-> FROM employees e JOIN departments d
	-> WHERE e.department_id = d.department_id;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM emp_depart;
+-------------------------+
| emp_dept                |
+-------------------------+
| Whalen(Administration)  |
| Hartstein(Marketing)    |
| Fay(Marketing)          |
| Raphaely(Purchasing)    |
| Khoo(Purchasing)        |
| Urman(Finance)          |
| Popp(Finance)           |
| Higgins(Accounting)     |
| Gietz(Accounting)       |
+-------------------------+
106 rows in set (0.00 sec)

示例

不同庫的資料連表檢視：

-- 作業票檢視

-- 對應欄位：
    -- 企業標識, 作業票id, 作業任務id, 作業票編號, 作業名稱, 作業內容, 作業具體地點, 作業位置名稱, 作業單位名稱, 屬地單位, 作業型別, 作業等級, 作業狀態, 實際開始時間, 實際結束時間, 作業票票樣地址, 是否關聯JSA, 資料來源(0統建, 1自建)

DROP VIEW IF EXISTS enterprise_self.view_enterprise_self_work_ticket;
CREATE VIEW enterprise_self.view_enterprise_self_work_ticket
(company_identity, work_ticket_id, work_task_id, work_ticket_num, work_name, work_content, work_site, work_position_name, work_unit_name, territorial_unit_name, work_type, work_level, work_status, actual_start_time, actual_end_time, ticket_url, if_jsa, source_type)
AS
(
    /*
	 * 統建系統
	 */
    SELECT ticket.tenant, ticket.id, ticket.work_task, ticket.num, ticket.work_name, ticket.work_content, ticket.work_site, ticket.work_position_name, ticket.work_unit_name, ticket.territorial_unit_name, ticket.work_type_code, ticket.work_level, ticket.work_status, ticket.actual_start_time, ticket.actual_end_time, ticket.ticket_url, (CASE WHEN (task.safety_analysis != 0) THEN '關聯' ELSE '未關聯' END) AS if_jsa, 0 FROM hse_alratk.work_ticket ticket LEFT JOIN hse_alratk.work_task task ON ticket.work_task = task.id WHERE ticket.is_deleted = 0 AND ticket.work_status IN (1, 2, 3, 4)
	/*
	 * 自建系統
	 */
    UNION ALL
    SELECT ticket.credit_code, ticket.work_ticket_id, ticket.work_task_id, ticket.work_ticket_code, ticket.work_name, ticket.work_content, '', ticket.work_position_name, ticket.work_unit_name, ticket.department_name, (CASE ticket.work_type_code WHEN 'blinding_pipeline_operation' THEN 'blinding-pipeline_operation' ELSE work_type_code END) AS work_type_code, (CASE ticket.work_level_code WHEN 'super' THEN '特級' WHEN 'first' THEN '一級' WHEN 'second' THEN '二級' WHEN 'third' THEN '三級' WHEN 'forth' THEN '四級' ELSE '-') AS work_level_code, 3, ticket.plan_start_time, ticket.plan_end_time, ticket.work_ticket_file_url, (CASE WHEN (task.jsa_id != '' AND task.jsa_id IS NOT NULL) THEN '關聯' ELSE '未關聯' END) AS if_jsa, 1 FROM data_sync.work_ticket ticket LEFT JOIN data_sync.work_task task ON ticket.work_task_id = task.work_task_id WHERE ticket.deleted = 0
);

對應 ddl：

-- 作業票檢視

-- 對應欄位：
    -- 企業標識, 作業票id, 作業任務id, 作業票編號, 作業名稱, 作業內容, 作業具體地點, 作業位置名稱, 作業單位名稱, 屬地單位, 作業型別, 作業等級, 作業狀態, 計劃開始時間, 計劃結束時間, 實際開始時間, 實際結束時間, 作業票票樣地址, 是否關聯安全分析, 位置點所屬劃分割槽域id, 座標, 位置名稱, 資料上報時間(自建系統), 資料來源(0統建, 1自建)

-- enterprise_data_lake2.view_enterprise_self_work_ticket source
DROP VIEW IF EXISTS enterprise_data_lake2.view_enterprise_self_work_ticket;
CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `enterprise_data_lake2`.`view_enterprise_self_work_ticket`
AS
-- 統建系統
SELECT
    `ticket`.`tenant` AS `company_identity`,
    `ticket`.`id` AS `work_ticket_id`,
    `ticket`.`work_task` AS `work_task_id`,
    `ticket`.`num` AS `work_ticket_num`,
    `ticket`.`work_name` AS `work_name`,
    `ticket`.`work_content` AS `work_content`,
    `ticket`.`work_site` AS `work_site`,
    `ticket`.`work_position_name` AS `work_position_name`,
    `ticket`.`work_unit_name` AS `work_unit_name`,
    `ticket`.`territorial_unit_name` AS `territorial_unit_name`,
    `ticket`.`work_type_code` AS `work_type`,
    `ticket`.`work_level` AS `work_level`,
    `ticket`.`work_status` AS `work_status`,
    `ticket`.`plan_start_time` AS `plan_start_time`,
    `ticket`.`plan_end_time` AS `plan_end_time`,
    `ticket`.`actual_start_time` AS `actual_start_time`,
    `ticket`.`actual_end_time` AS `actual_end_time`,
    `ticket`.`ticket_url` AS `ticket_url`,
    (CASE
        WHEN (`task`.`safety_analysis` <> 0) THEN '關聯'
        ELSE '未關聯'
    END) AS `if_safety_analysis`,
    `position`.`region_id` AS `region_id`,
    `position`.`coordinate` AS `coordinate`,
    `position`.`position_name` AS `position_name`,
    NULL AS `report_create_date`,
    0 AS `source_type`
FROM
(`enterprise_data_lake2`.`work_ticket` `ticket`
    LEFT JOIN `enterprise_data_lake2`.`work_task` `task` ON ((`ticket`.`work_task` = `task`.`id`) AND (`ticket`.`tenant` = `task`.`tenant`))
    INNER JOIN `enterprise_data_lake2`.`mm_position` `position` ON ((`ticket`.`work_position` = `position`.`id`) AND (`ticket`.`tenant` = `position`.`tenant`))
)
WHERE
((`ticket`.`is_deleted` = 0) AND (`ticket`.`work_status` IN (1, 2, 3, 4)))
UNION ALL
-- 自建系統
SELECT
    `ticket`.`credit_code` AS `company_identity`,
    `ticket`.`work_ticket_id` AS `work_ticket_id`,
    `ticket`.`work_task_id` AS `work_task_id`,
    `ticket`.`work_ticket_code` AS `work_ticket_num`,
    `ticket`.`work_name` AS `work_name`,
    `ticket`.`work_content` AS `work_content`,
    '' AS `work_site`,
    `ticket`.`work_position_name` AS `work_position_name`,
    `ticket`.`work_unit_name` AS `work_unit_name`,
    `ticket`.`department_name` AS `territorial_unit_name`,
    (CASE
        `ticket`.`work_type_code` WHEN 'blinding_pipeline_operation' THEN 'blinding-pipeline_operation'
        ELSE `ticket`.`work_type_code`
    END) AS `work_type`,
    (CASE
        `ticket`.`work_level_code` WHEN 'super' THEN '特級'
        WHEN 'first' THEN '一級'
        WHEN 'second' THEN '二級'
        WHEN 'third' THEN '三級'
        WHEN 'forth' THEN '四級'
        ELSE '-'
    END) AS `work_level`,
    3 AS `work_status`,
    `ticket`.`plan_start_time` AS `plan_start_time`,
    `ticket`.`plan_end_time` AS `plan_end_time`,
    `ticket`.`plan_start_time` AS `actual_start_time`,
    `ticket`.`plan_end_time` AS `actual_end_time`,
    `ticket`.`work_ticket_file_url` AS `ticket_url`,
    (CASE
        WHEN ((`task`.`jsa_id` <> '')
            AND (`task`.`jsa_id` IS NOT NULL)) THEN '關聯'
        ELSE '未關聯'
    END) AS `if_safety_analysis`,
    NULL AS `region_id`,
    NULL AS `coordinate`,
    NULL AS `position_name`,
    `ticket`.`report_create_date` AS `report_create_date`,
    1 AS `source_type`
FROM
    (`data_sync`.`work_ticket` `ticket`
LEFT JOIN `data_sync`.`work_task` `task` ON
    ((`ticket`.`work_task_id` = `task`.`work_task_id`)))
WHERE
    (`ticket`.`deleted` = 0);

檢視檢視

# 語法一：檢視資料庫的表物件，檢視物件
mysql> SHOW TABLES;
+---------------------+
| Tables_in_atguigudb |
+---------------------+
| countries           |
| departments         |
| dept_sum_vu         |
| emp                 |
| emp_depart          |
| emp_dept            |
| emp_dept_ysalary    |
| emp_details_view    |
| emp_year_salary     |
| employees           |
| empview             |
| empview2            |
| empvu80             |
| job_grades          |
| job_history         |
| jobs                |
| locations           |
| order               |
| regions             |
| salvu50             |
+---------------------+
20 rows in set (0.00 sec)

# 語法二：檢視檢視的結構
# DESC/DESCRIBE 檢視名稱;
mysql> DESC emp_depart;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| emp_dept | varchar(57) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

# 語法三：檢視檢視的屬性資訊
# 檢視檢視資訊（顯示資料表的儲存引擎、版本、資料行數和資料大小等）
# 執行結果顯示，註釋 Comment 為 VIEW，說明該表為檢視，其他的資訊為 NULL，說明這是一個虛表
mysql> SHOW TABLE STATUS LIKE 'emp_depart';
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+
| Name       | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+
| emp_depart | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | 2023-05-09 16:45:51 | NULL        | NULL       | NULL      |     NULL | NULL           | VIEW    |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+
1 row in set (0.00 sec)

# 語法四：檢視檢視的詳細定義資訊
# SHOW CREATE VIEW 檢視名稱;
mysql> SHOW CREATE VIEW emp_depart;
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View       | Create View                                                                                                                                                                                                                                                        | character_set_client | collation_connection |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| emp_depart | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `emp_depart` AS select concat(`e`.`last_name`,'(',`d`.`department_name`,')') AS `emp_dept` from (`employees` `e` join `departments` `d`) where (`e`.`department_id` = `d`.`department_id`) | utf8mb3              | utf8_general_ci      |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

更新檢視的資料

一般情況

MySQL 支援使用 INSERT、UPDATE 和 DELETE 語句對檢視中的資料進行插入、更新和刪除操作。當檢視中的資料發生變化時，資料表中的資料也會發生變化，反之亦然。

不可更新的檢視

要使檢視可更新，檢視中的行和底層基本表中的行之間必須存在一對一的關係。另外當檢視定義出現如下情況時，檢視不支援更新操作：

• 在定義檢視的時候指定了ALGORITHM = TEMPTABLE，檢視將不支援 INSERT 和 DELETE 操作；
• 檢視中不包含基表中所有被定義為非空又未指定預設值的列，檢視將不支援 INSERT 操作；
• 在定義檢視的 SELECT 語句中使用了 JOIN 聯合查詢 ，檢視將不支援 INSERT 和 DELETE 操作；
• 在定義檢視的 SELECT 語句後的欄位列表中使用了數學表示式或子查詢，檢視將不支援 INSERT，也不支援 UPDATE 使用了數學表示式、子查詢的欄位值；
• 在定義檢視的 SELECT 語句後的欄位列表中使用DISTINCT、聚合函式、GROUP BY、HAVING、UNION等，檢視將不支援 INSERT、UPDATE、DELETE；
• 在定義檢視的 SELECT 語句中包含了子查詢，而子查詢中引用了FROM後面的表，檢視將不支援 INSERT、UPDATE、DELETE；
• 檢視定義基於一個不可更新檢視；
• 常量檢視。

雖然可以更新檢視資料，但總的來說，檢視作為虛擬表，主要用於方便查詢，不建議更新檢視的資料。對檢視資料的更改，都是透過對實際資料表裡資料的操作來完成的。

修改和刪除檢視

修改檢視

方式 1：使用CREATE OR REPLACE VIEW子句修改檢視。

CREATE OR REPLACE VIEW empvu80(id_number, name, sal, department_id)
AS
SELECT employee_id, first_name || ' ' || last_name, salary, department_id
FROM employees
WHERE department_id = 80;

方式 2：使用ALTER VIEW子句修改檢視。

ALTER VIEW 檢視名稱
AS
查詢語句

刪除檢視

刪除檢視只是刪除檢視的定義，並不會刪除基表的資料。

語法：

DROP VIEW IF EXISTS 檢視名稱;

DROP VIEW IF EXISTS 檢視名稱1, 檢視名稱2, 檢視名稱3, ...;

說明：基於檢視 a、b 建立了新的檢視 c，如果將檢視 a 或者檢視 b 刪除，會導致檢視 c 的查詢失敗。這樣的檢視 c 需要手動刪除或修改，否則影響使用。

總結

檢視優點

操作簡單：

• 將經常使用的查詢操作定義為檢視，可以使開發人員不需要關心檢視對應的資料表的結構、表與表之間的關聯關係，也不需要關心資料表之間的業務邏輯和查詢條件，而只需要簡單地操作檢視即可，極大簡化了開發人員對資料庫的操作。

減少資料冗餘：

• 檢視跟實際資料表不一樣，它儲存的是查詢語句。所以，在使用的時候，需要透過定義檢視的查詢語句來獲取結果集。而檢視本身不儲存資料，不佔用資料儲存的資源，減少了資料冗餘。

資料安全：

• MySQL 將使用者對資料的訪問限制在某些資料的結果集上，而這些資料的結果集可以使用檢視來實現，使用者不必直接查詢或運算元據表。這也可以理解為檢視具有隔離性。檢視相當於在使用者和實際的資料表之間加了一層虛擬表。

• 同時，MySQL 可以根據許可權將使用者對資料的訪問限制在某些檢視上，使用者不需要查詢資料表，可以直接透過檢視獲取資料表中的資訊。這在一定程度上保障了資料表中資料的安全性。

適應靈活多變的需求：

• 當業務系統的需求發生變化後，如果需要改動資料表的結構，則工作量相對較大，可以使用檢視來減少改動的工作量。這種方式在實際工作中使用得比較多。

能夠分解複雜的查詢邏輯：

• 資料庫中如果存在複雜的查詢邏輯，則可以將問題進行分解，建立多個檢視獲取資料，再將建立的多個檢視結合起來，完成複雜的查詢邏輯。

檢視缺點

如果在實際資料表的基礎上建立了檢視，那麼，如果實際資料表的結構變更了，就需要及時對相關的檢視進行相應的維護。特別是巢狀的檢視（就是在檢視的基礎上建立檢視），會導致維護變得比較複雜， 可讀性不好 ，容易變成系統的潛在隱患。因為建立檢視的 SQL 查詢可能會對欄位重新命名，也可能包含複雜的邏輯，這些都會增加維護的成本。

實際專案中，如果檢視過多，會導致資料庫維護成本的問題。所以，在建立檢視的時候，需要結合實際專案需求，綜合考慮檢視的優點和不足，這樣才能正確使用檢視，使系統整體達到最優。

原文連結

https://github.com/ACatSmiling/zero-to-zero/blob/main/RelationalDatabase/mysql.md