本文目錄：1.創建、修改視圖2.視圖演算法merge、temptable3.刪除、查看視圖信息4.檢查無效視圖視圖是表表達式的一種，所以它也是虛擬表。對視圖操作的時候會通過語句動態的從表中臨時獲取數據。 1.創建、修改視圖當使用or replace時，如果視圖存在則此語句相當於alter view ...

本文目錄：
1.創建、修改視圖
2.關於視圖中的order by
3.視圖演算法merge、temptable
4.刪除、查看視圖信息
5.檢查無效視圖

視圖是表表達式的一種，所以它也是虛擬表。對視圖操作的時候會通過語句動態的從表中臨時獲取數據。

1.創建、修改視圖

CREATE [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW [IF NOT EXISTS] view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

ALTER 
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

當使用or replace時，如果視圖存在則此語句相當於alter view，如果視圖不存在，則等價於create view。

關於algorithm，後文詳細說明。

with [local|cascaded] check option：它的對象是可更新視圖(即merge演算法的視圖)。對於可更新視圖，可給定WITH CHECK OPTION子句來防止插入或更新非法記錄，除非作用在行上的select_statement中的WHERE子句為"true"。其中local表示只要滿足本視圖的篩選條件即可插入或更新，cascaded表示必須滿足所有視圖的篩選條件才可插入或更新。預設是with cascaded check option。

例如，下麵的語句定義了3個視圖，其中後兩個視圖是以第一個視圖作為基表創建的。在向view2和view3插入記錄的時候，如果記錄中欄位a=10：由於view2預設使用的是cascaded選項，a=10不滿足view1的條件，所以插入失敗；而view3使用的是local選項，只需滿足view3的條件即可，所以a=10滿足條件,即可以成功插入。

create view view1 as select * from t where a<10;
create view view2 as select * from view1 where a>5;
create view view3 as select * from view1 where a>5 with local check option;

MySQL/MariaDB中視圖創建後，列的定義是"已固化"狀態。也就是說，如果視圖定義語句中的select語句中使用了星號"*"表示所有列，在創建視圖的時候會轉化為對應的列名存儲在視圖定義語句中，所以如果基表中新增了列將不會被視圖的SQL語句檢索到。

例如：

create or replace view v_city
as
select * from world.city where id>200;

查看視圖的定義語句：可以看到，select語句中的星號是替換為了對應的列名來表示的。

mysql> mysql> show create view v_city\G
*************************** 1. row ***************************
                View: v_city
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`192.168.100.%` SQL SECURITY DEFINER VIEW `v_city` AS select `city`.`ID` AS `ID`,`city`.`Name` AS `Name`,`city`.`CountryCode` AS `CountryCode`,`city`.`District` AS `District`,`city`.`Population` AS `Population` from `city` where (`city`.`ID` > 200)
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

在MySQL/MariaDB中視圖定義語句中的select部分中，from後面不能是子查詢。在這一點上MySQL/MariaDB和其他類型的資料庫有些不一樣。如果在某種條件下，視圖的定義語句from字句正好需要的是子查詢，可以將這個子查詢先定義成視圖，再將視圖放在from字句中。更新視圖時，實際上是轉到對應的基表上進行更新。

2.關於視圖中的order by子句

按照標準SQL的規則，在視圖定義語句的select語句中不允許出現order by子句，除非使用了TOP(limit)，但這時候的ORDER BY只是為top挑選滿足數量的行。因為視圖是表表達式的一種，既然是表表達式，它是一種表，儘管是虛擬表。而表是不允許有序的(在關係引擎看來表總是無序的，在優化器看來表可以有序)。在SQL Server中，如果在視圖定義語句中使用了order by但卻沒有使用top子句，則直接報錯。

但在MySQL/MairaDB中的視圖定義語句中允許使用order by(又是違反標準的行為)。它認為視圖中的order by會在引用視圖時直接作用於基表。如果在引用視圖時也使用了order by子句，則視圖引用語句中的order by覆蓋視圖定義語句中的order by。例如：

CREATE OR REPLACE VIEW my_view AS SELECT * FROM t ORDER BY id DESC ;
SELECT * FROM my_view ORDER BY id ASC;

3.視圖演算法merge、temptable

algorithm={undefined|merge|temptable}是視圖選擇演算法。視圖的演算法會影響MySQL/MariaDB處理視圖的方式：

merge會將引用視圖的語句與視圖定義語句合併起來，使得視圖定義的某一部分取代語句的對應部分。例如在引用視圖時會將視圖名替換成基表名，將查詢涉及的列替換成基表中的列名等。
temptable將視圖的結果放入臨時表中，然後使用該表的數據執行對應語句操作。
undefined是讓MySQL/MariaDB自己選擇merge還是temptable，它更傾向於merge。這是未指定algorithm時的預設值。

例如，以下是merge的一個特殊例子，很能說明merge演算法：

MariaDB [test]> create or replace table t
                (id int auto_increment,
                 name char(20),
                 age int,
                 primary key(id));
MariaDB [test]> insert into t(name,age) values
     ('chenyi',21),
     ('huanger',22),
     ('zhangsan',23),
     ('lisi',24),
     ('wangwu',25),
    ('zhaoliu',26);
MariaDB [test]> select * from t;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | chenyi   |   21 |
|  2 | huanger  |   22 |
|  3 | zhangsan |   23 |
|  4 | lisi     |   24 |
|  5 | wangwu   |   25 |
|  6 | zhaoliu  |   26 |
+----+----------+------+ 
# 創建一個id<5的視圖my_view
MariaDB [test]> create or replace algorithm=merge view my_view(vf1,vf2) as 
                select id,name from t where age<24;
MariaDB [test]> select * from my_view;
+-----+----------+
| vf1 | vf2      |
+-----+----------+
|   1 | chenyi   |
|   2 | huanger  |
|   3 | zhangsan |
+-----+----------+

返回的結果是3行記錄。

由於是merge演算法的視圖，在引用視圖(此處是查詢操作)的時候，會將視圖中的各項替換為基表t中的各項。包括：

"*"號替換為vf1和vf2，它們又替換為t表中的id和name。
from子句中的my_view替換為表t。
加上視圖定義語句中的where子句。

因此，select * from my_view;在執行的時候，會轉換為下麵的查詢語句：

select id,name from t where age<24;

如果查詢my_view的時候，使用下麵的語句：

MariaDB [test]> select * from my_view where vf1<2; 
+-----+--------+
| vf1 | vf2    |
+-----+--------+
|   1 | chenyi |
+-----+--------+

在執行的時候，該語句將替換為下麵的語句：

select id,name from t where id<2 and age<24;

只有使用merge演算法的時候，視圖才是可更新視圖，因為temptable演算法操作的是填充到臨時表中的數據，無法結合基表進行數據更新。

因為merge演算法結合了基表，因此它有一些限制，出現了以下情況時不能使用merge演算法：

HAVING
LIMIT
GROUP BY
DISTINCT
UNION
UNION ALL
使用了聚合函數，如MAX(), MIN(), SUM() or COUNT()
在select列表中有子查詢
沒有基表，因為可能引用的是純值，例如create view va as select 2。

之所以有以上限制，是因為使用了它們之後，視圖的結構和基表的機構不一致，無法和基表一一對應，也就無法作為可更新視圖。

4.刪除、查看視圖

可以一次性刪除多個視圖。

DROP VIEW [IF EXISTS] view_name [, view_name] ...

MySQL/MariaDB中不存在show view status語句。可以使用show table status表和視圖的狀態信息，使用show tables顯示出資料庫中的表和視圖。

SHOW TABLE STATUS LIKE  'v_city';

查看視圖定義語句：

show create view view_name;

還可以從information_schema.views表中查看相關信息，但是要註意的是，在views表中視圖名所在的欄位稱為table_name而不是view_name。如下：

select * from information_schema.views where table_name='view_name';

5.檢查無效視圖

在創建視圖的時候，要求它的基表已存在，否則會報錯。但是在視圖創建成功後，視圖的基表可能會刪除掉，或者更新基表中的引用欄位。這時視圖就已經是無效視圖。

如何檢測這些無效視圖？

可以先在information.schema中查找出有哪些視圖，然後再使用check table語句檢測。

例如：

check table my_view,my_view2

以下是無效視圖檢查結果：

MariaDB [test]> check table my_view\G
*************************** 1. row ***************************
   Table: test.my_view
      Op: check
Msg_type: Error
Msg_text: Table 'test.t' doesn't exist
*************************** 2. row ***************************
   Table: test.my_view
      Op: check
Msg_type: Error
Msg_text: View 'test.my_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
*************************** 3. row ***************************
   Table: test.my_view
      Op: check
Msg_type: error
Msg_text: Corrupt
3 rows in set (0.000 sec)

回到Linux系列文章大綱：http://www.cnblogs.com/f-ck-need-u/p/7048359.html
回到網站架構系列文章大綱：http://www.cnblogs.com/f-ck-need-u/p/7576137.html
回到資料庫系列文章大綱：http://www.cnblogs.com/f-ck-need-u/p/7586194.html
轉載請註明出處：http://www.cnblogs.com/f-ck-need-u/p/8870908.html

註：若您覺得這篇文章還不錯請點擊右下角推薦，您的支持能激發作者更大的寫作熱情，非常感謝！

MySQL/MariaDB視圖

1.創建、修改視圖

2.關於視圖中的order by子句

3.視圖演算法merge、temptable

4.刪除、查看視圖

5.檢查無效視圖