mysql視圖學習筆記記錄

mysql5.1才有視圖功能，下面本文章來給各位介紹mysql視圖，創建，刪除，修改及創建視圖的一些注意事項，希望文章對各位有所幫助。

創建視圖

在MySQL中，創建視圖的完整語法如下：

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

其對應的語法變量信息如下：

[OR REPLACE]
    中括號中的OR REPLACE關鍵字是可選的。如果當前數據庫中已經存在指定名稱的視圖時，沒有該關鍵字，將會提示錯誤信息；如果使用了OR REPLACE關鍵字，則當前正在創建的視圖會覆蓋掉原來同名的視圖。
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    ALGORITHM子句是可選的，它表示使用何種算法來處理視圖。此外，它並不屬於標准SQL的一部分，而是MySQL對標准SQL進行的功能擴展。ALGORITHM可以設置三個值：MERGE、TEMPTABLE或UNDEFINED。如果沒有ALGORITHM子句，則默認值為UNDEFINED(未定義的)。
    對於MERGE，會將引用視圖的語句的文本與視圖定義合並起來，使得視圖定義的某一部分取代語句的對應部分。

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

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

    之所以提供TEMPTABLE選項，是因為TEMPTABLE在創建臨時表之後、並在完成語句處理之前，能夠釋放基表上的鎖定。與MERGE算法相比，鎖定釋放的速度更快，這樣，使用視圖的其他客戶端不會被屏蔽過長時間。

    此外，MERGE算法要求視圖中的行和基表中的行具有一對一的關系。如果視圖包含聚合函數(SUM(), MIN(), MAX(), COUNT()等)、DISTINCT、GROUP BY、HAVING、UNION或UNION ALL、沒有基表的引用文字值(例如：SELECT 'hello';)等結構中的任何一種，將失去一對一的關系，此時必須使用臨時表取而代之。
[(column_list)]
    (column_list)用於自定義視圖中各個字段的名稱。如果沒有該命令選項，那麼通過視圖查詢到的各個字段的名稱和視圖所使用到的數據表的字段名稱保持一致。下面是一個常見的用於創建視圖的SQL語句：

 代碼如下 復制代碼

    CREATE OR REPLACE VIEW v_user
    AS
    SELECT id, username FROM user;

    由於user表中的字段名稱為id和username，因此視圖v_user中的兩個字段名稱也分別默認為id和username。現在，我們將視圖v_user中的字段名稱分別自定義為uid和uname。

 代碼如下 復制代碼

    CREATE OR REPLACE VIEW v_user (uid, uname)
    AS
    SELECT id, username FROM user;

select_statement
    select_statement用於指定視圖的內容定義。簡而言之，這裡就是用戶自定義的一個SELECT語句。
[WITH [CASCADED | LOCAL] CHECK OPTION]
    該選項中的CASCADED為默認值，LOCAL CHECK OPTION用於在可更新視圖中防止插入或更新行。由於此選項一般不使用，因此不再贅述，具體信息請參考MySQL官方網站上的相關信息。

1.使用舉例
Eg. 本例創建一個產品表（product）和一個購買記錄表（purchase），再通過視圖purchase_detail查詢出購買的詳細信息。

 代碼如下 復制代碼

CREATE TABLE product

(

product_id INT NOT NULL,

name VARCHAR(50) NOT NULL,

price DOUBLE NOT NULL

);

INSERT INTO product VALUES(1, 'apple ', 5.5);

CREATE TABLE purchase

(

id INT NOT NULL,

product_id INT NOT NULL,

qty INT NOT NULL DEFAULT 0,

gen_time DATETIME NOT NULL

);

INSERT INTO purchase VALUES(1, 1, 10, NOW());

CREATE VIEW purchase_detail AS SELECT product.name as name, product .price as price, purchase.qty as qty, product .price * purchase.qty as total_value from product, purchase where product.product_id = purchase.product_id;

創建成功後，輸入：SELECT * FROM purchase_detail;

運行效果如下：

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

| name | price | qty | total_value |

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

| apple | 5.5 | 10 | 55 |

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

1 row in set (0.01 sec)

1.注意事項

創建視圖存在如下注意事項：

（1） 運行創建視圖的語句需要用戶具有創建視圖（CRATE VIEW）的權限，若加了[OR REPLACE]時，還需要用戶具有刪除視圖（DROP VIEW）的權限；

（2） SELECT語句不能包含FROM子句中的子查詢；

（3） SELECT語句不能引用系統或用戶變量；

（4） SELECT語句不能引用預處理語句參數；

（5） 在存儲子程序內，定義不能引用子程序參數或局部變量；

（6） 在定義中引用的表或視圖必須存在。但是，創建了視圖後，能夠捨棄定義引用的表或視圖。要想檢查視圖定義是否存在這類問題，可使用CHECK TABLE語句；

（7） 在定義中不能引用TEMPORARY表，不能創建TEMPORARY視圖；

（8） 在視圖定義中命名的表必須已存在；

（9） 不能將觸發程序與視圖關聯在一起；

（10） 在視圖定義中允許使用ORDER BY，但是，如果從特定視圖進行了選擇，而該視圖使用了具有自己ORDER BY的語句，它將被忽略。

刪除視圖

在MySQL中刪除視圖的方法非常簡單，其詳細語法如下：

--刪除指定名稱的一個或多個視圖

 代碼如下 復制代碼 DROP VIEW [IF EXISTS]
 view_name [, view_name2]...

其中，關鍵字IF EXISTS用於防止因視圖不存在而提示出錯，此時，只有存在該視圖才會執行刪除操作。DROP VIEW語句可以一次性刪除多個視圖，只需要在多個視圖名稱之間以英文逗號隔開即可。如果多個視圖存在於不同的數據庫中，不數據當前數據庫的視圖名稱之前還必須加上db_name.前綴。

--刪除視圖v_user
DROP VIEW v_user;

1.使用舉例

Eg1. 刪除在前面的小節中創建的視圖purchase_detail：DROP VIEW purchase_detail;

Eg2. 刪除一個未知的視圖：DROP VIEW IF EXISTS test_view;

Eg3. 刪除多個視圖：DROP VIEW IF EXISTS test_view1, test_view2;

1.注意事項

必須對要刪除的一個或多個視圖擁有DROP VIEW的權限。

修改視圖

請參考創建視圖語法中的OR REPLACE關鍵字，只要具備該關鍵字的視圖創建語句就是修改視圖的SQL語句。
查看視圖

在MySQL中，show tables不僅可以用於查看當前數據庫中存在哪些數據表，同時也可以查看到當前數據庫中存在哪些視圖。

 代碼如下 復制代碼

--執行show tables
mysql> show tables;

--以下是輸出結果
+----------------+
| Tables_in_test |
+----------------+
| user           |
| v_user         |
+----------------+
2 rows in set (0.00 sec)

不過，僅僅使用show tables語句，在輸出結果中，我們根本無法區分到底哪些才是視圖哪些才是真實的數據表(當然，視圖的命名我們可以統一約定以”v_”開頭)。此時，我們需要使用命令show full tables，該命令可以列出額外的table_type列，如果對應輸出行上該列的值為”VIEW”，則表示這是一個視圖。

 代碼如下 復制代碼

--執行show full tables
mysql> show full tables;

--以下是輸出結果
+----------------+------------+
| Tables_in_test | Table_type |
+----------------+------------+
| user           | BASE TABLE |
| v_user         | VIEW       |
+----------------+------------+
2 rows in set (0.00 sec)

當我們通過上述命令找到了我們所需要的視圖之後，我們可以使用如下命令查看創建該視圖的詳細語句：

show create view view_name

例如，我們使用該命令查看創建視圖v_user的SQL語句：

 代碼如下 復制代碼

--由於該語句的輸出結果較為雜亂，因此使用G命令進行格式化輸出
mysql> show create view v_user G;

--以下是格式化的輸出結果
*************************** 1. row ***************************
                View: v_user
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_user` AS select `user`.`id` AS `uid`,`user`.`username`
AS `uname` from `user`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

1.使用舉例
Eg. 將上一小節中中創建的視purchase_detail進行修改，去掉qty列，語句如下：

 代碼如下 復制代碼

ALTER VIEW purchase_detail AS SELECT product.name as name, product .price as price, product .price * purchase.qty as total_value from product, purchase where product.product_id = purchase.product_id;

此時通過語句：select * from purchase_detail;對視圖進行查詢時，結果如下：

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

| name | price | total_value |

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

| apple | 5.5 | 55 |

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