MySQL視圖詳解

視圖就是一個存在于數據庫中的虛擬表。

視圖本身沒有數據，只是通過執行相應的select語句完成獲得相應的數據。

目錄

創建視圖：

刪除視圖：

修改視圖：

視圖縮減業務邏輯

視圖的執行算法：

視圖的更新：

不可更新的視圖：

關于視圖的可插入性：insert

With check option的用法：

WITH LOCAL/cascade CHECK OPTION的用法：

 

 

創建視圖：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

    VIEW view_name [(column_list)]

    AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

 

mysql> create view v_emp as select empno,ename,job from emp;

Query OK, 0 rows affected (0.00 sec)

刪除視圖：

Drop view view_name;

修改視圖：

mysql> alter view v_emp as select empno,ename,job,deptno from emp;

Query OK, 0 rows affected (0.01 sec)

修改視圖結構，即修改所使用的字段的名稱（可以隱含基表的字段名稱）：

mysql> alter view v_emp(v1,v2,v3,v4) as select empno,ename,job,deptno from emp;

Query OK, 0 rows affected (0.00 sec)

 

 

視圖縮減業務邏輯

視圖用來隱藏復雜的業務邏輯，從join連接查詢產生一個view。先使用 視圖完成一定的邏輯，再在視圖的基礎上完成另外的邏輯。

通常，視圖完成的邏輯都是相對比較基礎的邏輯。

 

視圖的執行算法：

存在兩種執行算法：

1、  Merge：合并的執行方式，每當執行的時候，先將我們視圖的sql語句與外部查詢視圖的sql語句，混合在一起，最終執行；

2、  Temptable：臨時表模式，每當查詢的時候，將視圖所使用的select語句生成一個結果的臨時表，再在當前的臨時表內進行查詢。

指的是一個視圖是在什么時候執行，依據哪些方式執行；

對于MERGE，會將引用視圖的語句的文本與視圖定義合并起來，使得視圖定義的某一部分取代語句的對應部分。

 

對于TEMPTABLE，視圖的結果將被置于臨時表中，然后使用它執行語句。

 

對于UNDEFINED，MySQL將選擇所要使用的算法。如果可能，它傾向于MERGE而不是TEMPTABLE，這是因為MERGE通常更有效，而且如果使用了臨時表，視圖是不可更新

 

當用戶創建視圖時，mysql默認使用一種undefine的處理算法，就是會自動在合并和臨時表內進行選擇。

 

注意：

1、  盡量使用視圖完成讀操作

2、  如果使用視圖，則需要注意，對視圖的修改，也是對基表的修改，會即時生效；

3、  刪除視圖時，不會銷毀實體表內的數據

4、  如果大家做的是外部接口，一個數據庫多個應用，針對每一個應用，采用不同的視圖接口。

 

視圖的更新：

mysql> select * from dept;

+--------+-------+-----------+

| deptno | dname | loc       |

+--------+-------+-----------+

|     10 | bsc   | puyang    |

|     11 | bts   | xuchang   |

|     12 | 0521  | zhengzhou |

+--------+-------+-----------+

3 rows in set (0.01 sec)

 

mysql> create view v_dept as select deptno,dname from dept;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from v_dept;

+--------+-------+

| deptno | dname |

+--------+-------+

|     10 | bsc   |

|     11 | bts   |

|     12 | 0521  |

+--------+-------+

3 rows in set (0.23 sec)

 

mysql> update v_dept set dname='MSC' where deptno=12;（更新視圖）

Query OK, 1 row affected (0.03 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 

mysql> select * from v_dept;

+--------+-------+

| deptno | dname |

+--------+-------+

|     10 | bsc   |

|     11 | bts   |

|     12 | MSC   |

+--------+-------+

3 rows in set (0.00 sec)

 

mysql> select * from dept;（基表對應的數據也更新）

+--------+-------+-----------+

| deptno | dname | loc       |

+--------+-------+-----------+

|     10 | bsc   | puyang    |

|     11 | bts   | xuchang   |

|     12 | MSC   | zhengzhou |

+--------+-------+-----------+

3 rows in set (0.00 sec)

刪除視圖的數據：

mysql> delete from v_dept where deptno=10;

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from v_dept;

+--------+-------+

| deptno | dname |

+--------+-------+

|     11 | bts   |

|     12 | MSC   |

+--------+-------+

2 rows in set (0.00 sec)

 

mysql> select * from dept;（基表對應的記錄也被刪除了）

+--------+-------+-----------+

| deptno | dname | loc       |

+--------+-------+-----------+

|     11 | bts   | xuchang   |

|     12 | MSC   | zhengzhou |

+--------+-------+-----------+

2 rows in set (0.00 sec)

 

 

不可更新的視圖：

某些視圖是可更新的。也就是說，可以在諸如UPDATE、DELETE或INSERT等語句中使用它們，以更新基表的內容。對于可更新的視圖，在視圖中的行和基表中的行之間必須具有一對一的關系。還有一些特定的其他結構，這類結構會使得視圖不可更新。更具體地講，如果視圖包含下述結構中的任何一種，那么它就是不可更新的：

 

·         聚合函數（SUM(), MIN(), MAX(), COUNT()等）。

 

·         DISTINCT

 

·         GROUP BY

 

·         HAVING

 

·         UNION或UNION ALL

 

·         位于選擇列表中的子查詢

 

·         Join

 

·         FROM子句中的不可更新視圖

 

·         WHERE子句中的子查詢，引用FROM子句中的表。

 

·         僅引用文字值（在該情況下，沒有要更新的基本表）。

 

·         ALGORITHM = TEMPTABLE（使用臨時表總會使視圖成為不可更新的）。

 

創建不可更新的視圖：（使用臨時表的算法）

mysql> create algorithm=temptable view v_dept2 as select * from dept;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show create view v_dept2\G

*************************** 1. row ***************************

                View: v_dept2

         Create View: CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_dept2` AS select `dept`.`deptno` AS `deptno`,`dept`.`dname` AS `dname`,`dept`.`loc` AS `loc` from `dept`

character_set_client: gbk

collation_connection: gbk_chinese_ci

1 row in set (0.00 sec)

 

嘗試更新視圖，報錯，（一定程度上保證了基表數據的安全性）

mysql> update v_dept2 set loc='shanghai' where deptno=10;

ERROR 1288 (HY000): The target table v_dept2 of the UPDATE is not updatable

 

關于視圖的可插入性：insert

如果視圖滿足關于視圖列的下述額外要求，可更新的視圖也是可插入的：

·         不得有重復的視圖列名稱。

·         視圖必須包含沒有默認值的基表中的所有列。

·         視圖列必須是簡單的列引用而不是導出列。導出列不是簡單的列引用，而是從表達式導出的。下面給出了一些導出列示例：

·                3.14159

·                col1 + 3

·                UPPER(col2)

·                col3 / col4

·                (subquery)

混合了簡單列引用和導出列的視圖是不可插入的，但是，如果僅更新非導出列，視圖是可更新的。

 

更改視圖的算法：（原來是temptable，改為merge，從而視圖變成可以更新了）

mysql> alter ALGORITHM=merge view v_dept2 as select * from dept;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from v_Dept2;

+--------+-------+-----------+--------+

| deptno | dname | loc       | amount |

+--------+-------+-----------+--------+

|     11 | bts   | xuchang   |      0 |

|     12 | MSC   | zhengzhou |      0 |

+--------+-------+-----------+--------+

2 rows in set (0.00 sec)

 

由于該視圖沒有導出列，故可以插入數據：

mysql> insert into v_dept2 values(13,'SW','shanghai',100);

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from dept;（基表數據也被插入）

+--------+-------+-----------+--------+

| deptno | dname | loc       | amount |

+--------+-------+-----------+--------+

|     11 | bts   | xuchang   |      0 |

|     12 | MSC   | zhengzhou |      0 |

|     13 | SW    | shanghai  |    100 |

+--------+-------+-----------+--------+

3 rows in set (0.02 sec)

 

mysql> select * from v_dept2;（視圖數據插入）

+--------+-------+-----------+--------+

| deptno | dname | loc       | amount |

+--------+-------+-----------+--------+

|     11 | bts   | xuchang   |      0 |

|     12 | MSC   | zhengzhou |      0 |

|     13 | SW    | shanghai  |    100 |

+--------+-------+-----------+--------+

3 rows in set (0.00 sec)

 

更改視圖含有導出列，則通過視圖不能插入數據：

mysql> alter view v_dept2 as select *,amount/2 from dept;（amount/2是導出列）

Query OK, 0 rows affected (0.01 sec)

 

mysql> select * from v_dept2;

+--------+-------+-----------+--------+----------+

| deptno | dname | loc       | amount | amount/2 |

+--------+-------+-----------+--------+----------+

|     11 | bts   | xuchang   |     90 |  45.0000 |

|     12 | MSC   | zhengzhou |     80 |  40.0000 |

|     13 | SW    | shanghai  |    100 |  50.0000 |

+--------+-------+-----------+--------+----------+

3 rows in set (0.02 sec)

 插入一條數據是不允許的：

mysql> insert into v_dept2(deptno,dname,loc,amount) values(14,'HW','puyang',110);

ERROR 1471 (HY000): The target table v_dept2 of the INSERT is not insertable-into

 

但是如果簡單更新非導出列是可以的：

mysql> update v_dept2 set amount=110 where deptno=13;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 

mysql> select * from dept;（基表數據更新成功）

+--------+-------+-----------+--------+

| deptno | dname | loc       | amount |

+--------+-------+-----------+--------+

|     11 | bts   | xuchang   |     90 |

|     12 | MSC   | zhengzhou |     80 |

|     13 | SW    | shanghai  |    110 |

+--------+-------+-----------+--------+

3 rows in set (0.01 sec)

 

mysql> select * from v_dept2;（視圖數據更新成功）

+--------+-------+-----------+--------+----------+

| deptno | dname | loc       | amount | amount/2 |

+--------+-------+-----------+--------+----------+

|     11 | bts   | xuchang   |     90 |  45.0000 |

|     12 | MSC   | zhengzhou |     80 |  40.0000 |

|     13 | SW    | shanghai  |    110 |  55.0000 |

+--------+-------+-----------+--------+----------+

3 rows in set (0.00 sec)

 

 

With check option的用法：

（with check option對于沒有where條件的視圖不起作用的）

mysql> alter view v_dept2 as select * from dept where dname='SW' with check option;

Query OK, 0 rows affected (0.00 sec)（只限于插入dname是SW的記錄）

 

mysql> insert into v_dept2 values(15,'SW','beijing',20);（插入成功）

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from dept;

+--------+-------+-----------+--------+

| deptno | dname | loc       | amount |

+--------+-------+-----------+--------+

|     11 | bts   | xuchang   |     90 |

|     12 | MSC   | zhengzhou |     80 |

|     13 | SW    | shanghai  |     60 |

|     14 | SW    | nanjing   |     65 |

|     15 | SW    | beijing   |     20 |

+--------+-------+-----------+--------+

5 rows in set (0.00 sec)

 

mysql> insert into v_dept2 values(15,'BSC','nanjing',65);（插入失敗）

ERROR 1369 (HY000): CHECK OPTION failed 'temp.v_dept2'

 

mysql> delete from v_dept2 where deptno=15;（有沒有with check option，不影響刪除操作）

Query OK, 1 row affected (0.00 sec)

mysql> update v_dept2 set dname='HW' where deptno=13;（更新成非SW的 失敗）

ERROR 1369 (HY000): CHECK OPTION failed 'temp.v_dept2'

對于with check option用法，總結如下：

通過有with check option選項的視圖操作基表(只是面對單表，對連接多表的視圖正在尋找答案)，有以下結論： 插入后的數據，通過視圖能夠查詢出來就符合WITH CHECK OPTION 否則就不符合；

首先視圖只操作它可以查詢出來的數據，對于它查詢不出的數據，即使基表有，也不可以通過視圖來操作。

1.對于update,有with check option，要保證update后，數據要被視圖查詢出來

2.對于delete,有無with check option都一樣

4.對于insert,有with check option，要保證insert后，數據要被視圖查詢出來

對于沒有where 子句的視圖，使用with check option是多余的

 

WITH LOCAL/cascade CHECK OPTION的用法：

在關于可更新視圖的WITH CHECK OPTION子句中，當視圖是根據另一個視圖定義的時，LOCAL和CASCADED關鍵字決定了檢查測試的范圍。LOCAL關鍵字對CHECK OPTION進行了限制，使其僅作用在定義的視圖上，CASCADED會對將進行評估的基表進行檢查。如果未給定任一關鍵字，默認值為CASCADED。

mysql> select * from test;

+----+------+

| id | aa   |

+----+------+

|  1 |   12 |

|  2 |    4 |

|  3 |   44 |

|  4 |   25 |

|  5 |   26 |

|  6 |    8 |

|  7 |   15 |

+----+------+

7 rows in set (0.00 sec)

 

mysql> create view v1 as select * from test where aa<40 with check option;（視圖v1）

Query OK, 0 rows affected (0.02 sec)

 

mysql> select * from v1;

+----+------+

| id | aa   |

+----+------+

|  1 |   12 |

|  2 |    4 |

|  4 |   25 |

|  5 |   26 |

|  6 |    8 |

|  7 |   15 |

+----+------+

6 rows in set (0.00 sec)

 

mysql> create view v2 as select * from v1 where aa>10 with local check option;

Query OK, 0 rows affected (0.00 sec)

 

mysql> create view v3 as select * from v1 where aa>10 with cascaded check option;

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into v2 values(null,50);（通過視圖v2插入50，只檢查插入的數據是否滿足v2

的條件aa>10,成功插入）

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into v3 values(null,50);（通過視圖v3插入50，不僅檢查是否滿足V3的條件aa>10，還要檢查是否滿足v1的條件aa<40）插入失敗

ERROR 1369 (HY000): CHECK OPTION failed 'temp.v3'

但是，雖然通過視圖v2插入成功，v2中并沒有50這條數據，test基表中有50這條數據，直接插入到基表了

mysql> select * from v2;

+----+------+

| id | aa   |

+----+------+

|  1 |   12 |

|  4 |   25 |

|  5 |   26 |

|  7 |   15 |

+----+------+

4 rows in set (0.00 sec)

 

mysql> select * from test;

+----+------+

| id | aa   |

+----+------+

|  1 |   12 |

|  2 |    4 |

|  3 |   44 |

|  4 |   25 |

|  5 |   26 |

|  6 |    8 |

|  7 |   15 |

|  8 |   50 |

+----+------+

8 rows in set (0.00 sec)