MySQL全面瓦解15:檢視

翁智華發表於2020-12-30

概述

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

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

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

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

概念

檢視是在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 也可以驗證下。 

總結

  1. 瞭解檢視的用途及與表的區別。

  2. 掌握檢視的建立、使用、修改、刪除。

  3.  瞭解檢視的優點:簡單簡潔(隱藏細節,輸出結果)、安全性(使用者只能看到表中特定行的方法)、邏輯資料獨立性。

相關文章