MySQL全面瓦解15：檢視

翁智華發表於2020-12-30

MySql

概述

很多時候，我們會有一些很複雜的資料庫操作，比如整合使用者的行為資料，那這些資料可能包含使用者的餐飲、生活日用、充值消費、交通出行、通訊物流、交通出行、醫療保健、住房物業、運動健康...

基於此，我們可能要去關聯很多的使用者行為資訊表，寫一大堆的合併、統計，以及條件過濾的指令碼，來輸出最終的二維表，這才是使用者真正需要的資料。如果把這些複雜的指令碼寫在業務程式中不是很合理，那有什麼辦法把

這些指令碼細節隱藏，只顯示只顯示簡潔的結果呢，那就要用到檢視了。

檢視是由資料庫中的一個表或多個表匯出的虛擬表，是一種虛擬存在的表，方便使用者對資料的操作。

概念

檢視是在mysql5之後出現的，是一種虛擬表，行和列的資料來自於定義檢視時使用的一些表中，檢視的資料是在使用檢視的時候動態生成的，檢視只儲存了sql的邏輯，不儲存查詢的結果。

使用場景

多個業務場景使用到相同的查詢結果，並且該查詢結果比較複雜的時候，我們可以使用檢視來隱藏複雜的實現細節。

檢視和表比較

	語法	實際中是否佔用物理空間	使用
檢視	create view	否，僅儲存sql的邏輯	增刪改查，實際上我們只使用查詢
表	create table	是，儲存了資料	增刪改查

檢視的作用

對其中所引用的基礎表來說，檢視的作用類似於篩選。定義檢視的篩選可以來自當前或其他資料庫的一個或多個表，或者其他檢視。通過檢視進行查詢沒有任何限制，通過它們進行資料修改時的限制也很少。檢視的作用歸納為如下幾點。

1、簡單性

看到的就是需要的。檢視不僅可以簡化使用者對資料的理解，也可以簡化他們的操作。那些被經常使用的查詢可以被定義為檢視，從而使得使用者不必為以後的操作每次指定查詢的條件。

經常需要聯合查詢的內容也可以指定為檢視，這樣就不需要每次都寫大量的聯表指令碼。

2、安全性

檢視的安全性可以防止未授權使用者檢視特定的行或列，使有許可權使用者只能看到表中特定行的方法，比如把只有某部分資料查詢檢視授權給某個使用者。

3、邏輯資料獨立性

檢視可以使應用程式和資料庫表在一定程度上獨立。如果沒有檢視，程式一定是建立在表上的。有了檢視之後，程式可以建立在檢視之上，從而程式與資料庫表被檢視分割開來。檢視可以在以下幾個方面使程式與資料獨立。

（1）如果應用建立在資料庫表上，當資料庫表發生變化時，可以在表上建立檢視，通過檢視遮蔽表的變化，從而使應用程式可以不動。

（2）如果應用建立在資料庫表上，當應用發生變化時，可以在表上建立檢視，通過檢視遮蔽應用的變化，從而使資料庫表不動。

（3）如果應用建立在檢視上，當資料庫表發生變化時，可以在表上修改檢視，通過檢視遮蔽表的變化，從而使應用程式可以不動。

（4）如果應用建立在檢視上，當應用發生變化時，可以在表上修改檢視，通過檢視遮蔽應用的變化，從而使資料庫可以不動。

資料準備

表名	描述
departments	部門表
employees	員工資訊表
jobs	職位資訊表
locations	位置表（部門表中會用到）
job_grades	薪資等級表

建立檢視

MySQL中，建立檢視是通過CREATE VIEW語句實現的。

語法

1 CREATE [OR REPLACE] [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
2 VIEW ViewName[(ColumnList)]
3 AS SELECT ...
4 [WITH [CASCADED|LOCAL] CHECK OPTION];

引數說明：

（1）ALGORITHM：可選項，表示檢視選擇的演算法。

（2）ViewName 檢視名：表示要建立的檢視名稱。

（3）ColumnList 屬性清單：可選項，指定檢視中各個屬性的名詞，預設情況下與SELECT語句中的查詢的屬性相同。

（4）SELECT語句：表示一個完整的查詢語句，將查詢記錄匯入檢視中。

（5）WITH CHECK OPTION：可選項，表示更新檢視時要保證在該檢視的許可權範圍之內。

檢視的使用步驟

（1）建立檢視

（2）對檢視執行查詢操作

對單表的操作

查詢出訂單表中，單筆訂單額度超過100元的資料，下面依次展示建立檢視和查詢檢視。

 1 mysql> select *  from t_order;
 2 +---------+-----+-------+--------+---------------------+------+
 3 | orderid | uid | uname | amount | time                | year |
 4 +---------+-----+-------+--------+---------------------+------+
 5 |      20 |   1 | brand | 91.23  | 2018-08-20 17:22:21 | 2018 |
 6 |      21 |   1 | brand | 87.54  | 2019-07-16 09:21:30 | 2019 |
 7 |      22 |   1 | brand | 166.88 | 2019-04-04 12:23:55 | 2019 |
 8 |      23 |   2 | helyn | 93.73  | 2019-09-15 10:11:11 | 2019 |
 9 |      24 |   2 | helyn | 102.32 | 2019-01-08 17:33:25 | 2019 |
10 |      25 |   2 | helyn | 106.06 | 2019-12-24 12:25:25 | 2019 |
11 |      26 |   2 | helyn | 73.42  | 2020-04-03 17:16:23 | 2020 |
12 |      27 |   3 | sol   | 55.55  | 2019-08-05 19:16:23 | 2019 |
13 |      28 |   3 | sol   | 69.96  | 2020-09-16 19:23:16 | 2020 |
14 |      29 |   4 | weng  | 199.99 | 2020-06-08 19:55:06 | 2020 |
15 +---------+-----+-------+--------+---------------------+------+
16 10 rows in set
17 
18 mysql> create view view1
19     -> as select * from t_order where amount >100;
20 Query OK, 0 rows affected
21 
22 mysql> select * from view1;
23 +---------+-----+-------+--------+---------------------+------+
24 | orderid | uid | uname | amount | time                | year |
25 +---------+-----+-------+--------+---------------------+------+
26 |      22 |   1 | brand | 166.88 | 2019-04-04 12:23:55 | 2019 |
27 |      24 |   2 | helyn | 102.32 | 2019-01-08 17:33:25 | 2019 |
28 |      25 |   2 | helyn | 106.06 | 2019-12-24 12:25:25 | 2019 |
29 |      29 |   4 | weng  | 199.99 | 2020-06-08 19:55:06 | 2020 |
30 +---------+-----+-------+--------+---------------------+------+
31 4 rows in set
32 
33 mysql>  select * from view1 where year = 2019;
34 +---------+-----+-------+--------+---------------------+------+
35 | orderid | uid | uname | amount | time                | year |
36 +---------+-----+-------+--------+---------------------+------+
37 |      22 |   1 | brand | 166.88 | 2019-04-04 12:23:55 | 2019 |
38 |      24 |   2 | helyn | 102.32 | 2019-01-08 17:33:25 | 2019 |
39 |      25 |   2 | helyn | 106.06 | 2019-12-24 12:25:25 | 2019 |
40 +---------+-----+-------+--------+---------------------+------+
41 3 rows in set

上面我們建立了一個檢視：view1，我們需要看某個條件下的資訊時，不用關心這個檢視內部是什麼樣的，只需要查詢檢視就可以了，sql簡單很多。

對多表的操作

有兩個表：學生、班級，資料基數如下，需要建立一個檢視，統計每個班級的人數和平均成績和總成績。

 1 mysql> select * from classes;
 2 +---------+-----------+
 3 | classid | classname |
 4 +---------+-----------+
 5 |       1 | 初三一班  |
 6 |       2 | 初三二班  |
 7 |       3 | 初三三班  |
 8 +---------+-----------+
 9 3 rows in set
10 
11 mysql> select * from students;
12 +-----------+-------------+-------+---------+
13 | studentid | studentname | score | classid |
14 +-----------+-------------+-------+---------+
15 |         1 | brand       | 97.5  |       1 |
16 |         2 | helen       | 96.5  |       1 |
17 |         3 | lyn         | 96    |       1 |
18 |         4 | sol         | 97    |       1 |
19 |         7 | b1          | 81    |       2 |
20 |         8 | b2          | 82    |       2 |
21 |        13 | c1          | 71    |       3 |
22 |        14 | c2          | 72.5  |       3 |
23 |        19 | lala        | 51    |       0 |
24 +-----------+-------------+-------+---------+
25 9 rows in set

 1 mysql> create view view2 as select a.classname as '班級名稱',count(b.studentid) as '總人數',sum(b.score) as '總分',avg(b.score) as '平均分'
 2 from classes a inner join students b on a.classid = b.classid 
 3 group by a.classid,a.classname;
 4 Query OK, 0 rows affected
 5 
 6 mysql> select * from view2;
 7 +----------+--------+--------+-----------+
 8 | 班級名稱 | 總人數 | 總分   | 平均分    |
 9 +----------+--------+--------+-----------+
10 | 初三一班 |      4 | 387.00 | 96.750000 |
11 | 初三二班 |      2 | 163.00 | 81.500000 |
12 | 初三三班 |      2 | 143.50 | 71.750000 |
13 +----------+--------+--------+-----------+
14 3 rows in set

查詢檢視結構

使用 desc viewname 語法檢視檢視的結構

 1 mysql> desc view2;
 2 +----------+---------------+------+-----+---------+-------+
 3 | Field    | Type          | Null | Key | Default | Extra |
 4 +----------+---------------+------+-----+---------+-------+
 5 | 班級名稱 | varchar(30)   | YES  |     | NULL    |       |
 6 | 總人數   | bigint(21)    | NO   |     | 0       |       |
 7 | 總分     | decimal(32,2) | YES  |     | NULL    |       |
 8 | 平均分   | decimal(14,6) | YES  |     | NULL    |       |
 9 +----------+---------------+------+-----+---------+-------+
10 4 rows in set

使用show create view語法檢視檢視建立資訊

1 mysql> show create view view2;
2 +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
3 | View  | Create View                                                                                                                                                                                                                                                                                                                                    | character_set_client | collation_connection |
4 +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
5 | view2 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view2` AS select `a`.`classname` AS `班級名稱`,count(`b`.`studentid`) AS `總人數`,sum(`b`.`score`) AS `總分`,avg(`b`.`score`) AS `平均分` from (`classes` `a` join `students` `b` on((`a`.`classid` = `b`.`classid`))) group by `a`.`classid`,`a`.`classname` | utf8                 | utf8_general_ci      |
6 +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
7 1 row in set

修改檢視

修改檢視是指修改資料庫中已存在的表的定義。當基本表的某些欄位發生改變時，可以通過修改檢視來保持檢視和基本表之間一致。MySQL中通過CREATE OR REPLACE VIEW語句和ALTER VIEW語句來修改檢視。

create or replace方式

當檢視存在時，修改，不存在時，建立新的檢視。

 1 mysql> create or replace view view2 as select a.classname as '班級名稱',count(b.studentid) as '總人數',sum(b.score) as '總分',avg(b.score) as '平均分'
 2 from classes a inner join students b on a.classid = b.classid 
 3 group by a.classid,a.classname;
 4 Query OK, 0 rows affected
 5 
 6 mysql> select * from view2;
 7 +----------+--------+--------+-----------+
 8 | 班級名稱 | 總人數 | 總分   | 平均分    |
 9 +----------+--------+--------+-----------+
10 | 初三一班 |      4 | 387.00 | 96.750000 |
11 | 初三二班 |      2 | 163.00 | 81.500000 |
12 | 初三三班 |      2 | 143.50 | 71.750000 |
13 +----------+--------+--------+-----------+
14 3 rows in set

alter方式

 1 mysql> alter view view2 as select a.classname as '班級名稱',count(b.studentid) as '總人數',sum(b.score) as '總分',avg(b.score) as '平均分'
 2 from classes a inner join students b on a.classid = b.classid 
 3 group by a.classid,a.classname;
 4 Query OK, 0 rows affected
 5 
 6 mysql> select * from view2;
 7 +----------+--------+--------+-----------+
 8 | 班級名稱 | 總人數 | 總分   | 平均分    |
 9 +----------+--------+--------+-----------+
10 | 初三一班 |      4 | 387.00 | 96.750000 |
11 | 初三二班 |      2 | 163.00 | 81.500000 |
12 | 初三三班 |      2 | 143.50 | 71.750000 |
13 +----------+--------+--------+-----------+
14 3 rows in set

刪除檢視

刪除檢視是指刪除資料庫中已存在的檢視。刪除檢視時，只能刪除檢視的定義，不會刪除資料。MySQL中，使用DROP VIEW語句來刪除檢視。但是，使用者必須擁有DROP許可權。

語法

1 DROP VIEW IF EXISTS vname1[,vname2][,vname3];

可以刪除多個檢視，多個檢視名稱使用都好隔開。

1 mysql> DROP VIEW IF EXISTS view1,view2;
2 Query OK, 0 rows affected

更新檢視資料

即更改檢視中的資料，而不是更改檢視中的sql邏輯。當對檢視進行更新後，也會對原始表的資料進行更新。

為了防止對原始表的資料產生更新，可以為檢視新增只讀許可權，只允許讀檢視，不允許對檢視進行更新。一般比較少這麼操作：

 1 mysql> create view view1 as select * from classes;
 2 Query OK, 0 rows affected
 3 
 4 mysql> select * from view1;
 5 +---------+-----------+
 6 | classid | classname |
 7 +---------+-----------+
 8 |       1 | 初三一班  |
 9 |       2 | 初三二班  |
10 |       3 | 初三三班  |
11 +---------+-----------+
12 3 rows in set
13 
14 mysql> insert into view1 values(4,'出三四班');
15 Query OK, 1 row affected
16 
17 mysql> update view1 set classname='初三四班' where classid=4;
18 Query OK, 1 row affected
19 Rows matched: 1  Changed: 1  Warnings: 0
20 
21 mysql> select * from view1;
22 +---------+-----------+
23 | classid | classname |
24 +---------+-----------+
25 |       1 | 初三一班  |
26 |       2 | 初三二班  |
27 |       3 | 初三三班  |
28 |       4 | 初三四班  |
29 +---------+-----------+
30 4 rows in set

檢視的更新我們一般不使用，為了保護檢視的某部分割槽間的資料不被修改，一般會使用WITH CHECK OPTION約束，甚至設定為只讀檢視。

with check option

檢視只操作它能夠查詢出來的資料，對於查詢不出的資料，即使基表中存在資料，也不可以通過檢視來操作。針對這方面的限制有 with check option 表示式進行操作。他對delete、insert、update分別有如下約束：

關鍵字型別	描述
update	有with check option，要保證update後，資料能被檢視查詢出來
delete	有無with check option都一樣
insert	有with check option，要保證insert後，資料要被檢視查詢出來

下面建立兩個同樣結果的檢視，一個帶with check option，一個不帶。

 1 mysql> create view v as select * from  t_order where amount>0;
 2 Query OK, 0 rows affected
 3  
 4 mysql> create view v_option as select * from  t_order where amount>0 with check option;
 5 Query OK, 0 rows affected
 6 
 7 mysql> select * from t_order;
 8 +---------+---------+---------+-------+
 9 | orderid | account | amount  | goods |
10 +---------+---------+---------+-------+
11 |       8 | brand   | 52.2    |     2 |
12 |       9 | hen     | 1752.02 |     7 |
13 |      10 | helyn   | 88.5    |     4 |
14 |      11 | sol     | 1007.9  |    11 |
15 |      12 | diny    | 12      |     1 |
16 |      13 | weng    | 52.2    |     5 |
17 |      14 | sally   | 99.71   |     9 |
18 |      15 | brand1  | -99.99  |     6 |
19 +---------+---------+---------+-------+
20 8 rows in set

原表有8條資料，因為條件限制，檢視無法讀取到最後一條資料，即只有前7條資料。那根據上面的規則，使用v_option檢視進行修改，修改他查詢範圍之外的資料應該是不允許的，我們來試試：

1 mysql> update v_option set amount=-8 where orderid=12;
2 1369 - CHECK OPTION failed 'test.v_option'

amout = -8，改完後資料會跑到檢視可查詢範圍之外，所以被限制了。使用不限制的檢視就可以了，如下：

 1 mysql> update v set amount=-8 where orderid=12;
 2 Query OK, 1 row affected
 3 Rows matched: 1  Changed: 1  Warnings: 0
 4 
 5 mysql> select * from t_order;
 6 +---------+---------+---------+-------+
 7 | orderid | account | amount  | goods |
 8 +---------+---------+---------+-------+
 9 |       8 | brand   | 52.2    |     2 |
10 |       9 | hen     | 1752.02 |     7 |
11 |      10 | helyn   | 88.5    |     4 |
12 |      11 | sol     | 1007.9  |    11 |
13 |      12 | diny    | -8      |     1 |
14 |      13 | weng    | 52.2    |     5 |
15 |      14 | sally   | 99.71   |     9 |
16 |      15 | brand1  | -99.99  |     6 |
17 +---------+---------+---------+-------+
18 8 rows in set

同理，delete、insert 也可以驗證下。

總結

瞭解檢視的用途及與表的區別。
掌握檢視的建立、使用、修改、刪除。
瞭解檢視的優點：簡單簡潔（隱藏細節，輸出結果）、安全性（使用者只能看到表中特定行的方法）、邏輯資料獨立性。

MySQL全面瓦解14：事務
2020-12-16
MySql
MySQL全面瓦解4：資料定義-DDL
2020-10-30
MySql
MySQL全面瓦解5：資料操作-DML
2020-11-01
MySql
MySQL全面瓦解28：分庫分表
2021-12-02
MySql
MySQL全面瓦解19：遊標相關
2021-01-21
MySql
MySQL全面瓦解18：自定義函式
2021-01-20
MySql函式
MySQL全面瓦解23：MySQL索引實現和使用
2021-02-25
MySql索引
MySQL全面瓦解6：查詢的基本操作
2020-11-06
MySql
MySQL全面瓦解：安裝部署與準備
2020-10-26
MySql
MySQL全面瓦解17：觸發器相關
2021-01-19
MySql觸發器
MySQL全面瓦解8：查詢的正則匹配
2020-11-10
MySql
MySQL全面瓦解16：儲存過程相關
2021-01-18
MySql儲存過程
MySQL全面瓦解13：系統函式相關
2020-11-28
MySql函式
MySQL全面瓦解7：查詢的過濾條件
2020-11-09
MySql
MySQL全面瓦解27：主從複製（原理 + 實踐）
2021-08-17
MySql
MySQL全面瓦解22：索引的介紹和原理分析
2021-02-18
MySql索引
MySQL全面瓦解26：程式碼評審中的MySQL（團隊使用）
2021-07-22
MySql
MySQL全面瓦解10：分組查詢和聚合函式
2020-11-16
MySql函式
MySQL全面瓦解9：查詢的排序、分頁相關
2020-11-13
MySql排序
MySQL全面瓦解3：資料型別介紹與分析
2020-10-28
MySql資料型別
MySQL全面瓦解2：常用命令和系統管理
2020-10-27
MySql
MySQL全面瓦解11：子查詢和組合查詢
2020-11-17
MySql
MySQL全面瓦解12：連線查詢的原理和應用
2020-11-19
MySql
MySQL全面瓦解29：使用Partition功能實現水平分割槽
2021-12-07
MySql
MySQL全面瓦解25：構建高效能索引（案例分析篇）
2021-03-09
MySql索引
MySQL全面瓦解20：可程式設計性之流程控制語句
2021-01-22
MySql程式設計
MYSQL 檢視
2018-10-24
MySql
MySQL View 檢視
2024-04-09
MySqlView
MySQL檢視介紹
2020-05-11
MySql
管理mysql的檢視
2021-09-09
MySql
MySQL 檢視簡介
2022-04-16
MySql
10_MySQL檢視
2020-12-22
MySql
MySQL入門系列：檢視
2019-03-18
MySql
mysql建立索引和檢視
2018-05-25
MySql索引
MySql 什麼是檢視
2020-09-27
MySql
MySQL之檢視學習
2019-06-18
MySql
MySQL筆記 13 檢視
2021-01-02
MySql筆記
【MySQL】檢視&子查詢
2020-12-20
MySql