MySQL視圖的使用

概述：

視圖即是虛擬表，也稱為派生表，因為它們的內容都派生自其它表的查詢結果。雖然視圖看起來感覺和基本表一樣，但是它們不是基本表。基本表的內容是持久的，而視圖的內容是在使用過程中動態產生的。——摘自《SQLite權威指南》

使用視圖的優點：

1.可靠的安全性
2.查詢性能提高
3.有效應對靈活性的功能需求
4.輕松應對復雜的查詢需求

視圖的基本使用：

創建：

例如我們本身有一個這樣的基本表：

mysql> select * from students;
+------+----------------+-------+
| id | name | age |
+------+----------------+-------+
| 1 | bumblebee | 200 |
| 1 | king of monkey | 10000 |
+------+----------------+-------+

那麼就可以像這樣來創建一個視圖：

CREATE VIEW stu_view AS SELECT name FROM students;

Query OK, 0 rows affected (0.01 sec)

創建完一個視圖，可以通過查看數據庫中的全部數據表來查看：

mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| stu_view |
| students |
+-------------------+
2 rows in set (0.00 sec)

可以看到當前數據庫中已經把剛剛創建的視圖放進數據庫的表集合中了。因為視圖也是一種表，是虛擬表。

查詢：

視圖的查詢和基本表的查詢一樣，因為視圖也是一種數據表，所以你可以像這樣的來查詢它

mysql> select * from stu_view;
+----------------+
| name |
+----------------+
| bumblebee |
| king of monkey |
+----------------+

刪除：

DROP VIEW stu_view;

刪除之後可以再次查詢進行驗證：

mysql> select * from stu_view;
ERROR 1146 (42S02): Table 'student.stu_view' doesn't exist

接下來我們看看如果我們變動了原始的基本表，視圖會有什麼改變：

mysql> INSERT INTO students(id, name, age) VALUES (2, 'Zeus', 100000);
Query OK, 1 row affected (0.00 sec)

檢查基本表：

mysql> SELECT * FROM students;
+------+----------------+--------+
| id | name | age |
+------+----------------+--------+
| 1 | bumblebee | 200 |
| 1 | king of monkey | 10000 |
| 2 | Zeus | 100000 |
+------+----------------+--------+
3 rows in set (0.00 sec)

檢查視圖：

mysql> SELECT * FROM stu_view;
+----------------+
| name |
+----------------+
| bumblebee |
| king of monkey |
| Zeus |
+----------------+
3 rows in set (0.00 sec)

更新：

mysql> CREATE VIEW stu_view2 AS SELECT id, name FROM students;
Query OK, 0 rows affected (0.01 sec)

驗證：

mysql> select * from stu_view2;
+------+----------------+
| id | name |
+------+----------------+
| 1 | bumblebee |
| 1 | king of monkey |
| 2 | Zeus |
+------+----------------+
3 rows in set (0.00 sec)

更新視圖：

mysql> UPDATE stu_view2 SET name='Medusa' WHERE id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

檢查視圖更新結果：

mysql> SELECT * FROM stu_view2;
+------+----------------+
| id | name |
+------+----------------+
| 1 | bumblebee |
| 1 | king of monkey |
| 2 | Medusa |
+------+----------------+
3 rows in set (0.00 sec)

檢查基本表更新結果：

mysql> SELECT * FROM students;
+------+----------------+--------+
| id | name | age |
+------+----------------+--------+
| 1 | bumblebee | 200 |
| 1 | king of monkey | 10000 |
| 2 | Medusa | 100000 |
+------+----------------+--------+
3 rows in set (0.00 sec)

關聯多表的視圖：

以上都是基於單表的操作，接下來我們從兩張表中來做一些實戰。

我們額外創建一個info表作為輔助的數據表，如下：

mysql> select * from info;
+----+--------+---------------------------------+
| id | stu_id | info |
+----+--------+---------------------------------+
| 1 | 1 | A member of the deformed steel. |
| 2 | 2 | Hero in Chinese Mythology. |
| 3 | 3 | In Greek mythology the Gorgon. |
+----+--------+---------------------------------+
3 rows in set (0.00 sec)

我們創建一個連接了兩張基本表的視圖stu_view3

mysql> CREATE VIEW stu_view3 AS SELECT s.id, s.name, s.age, i.info FROM students s, info i WHERE i.stu_id=s.id;
Query OK, 0 rows affected (0.00 sec)

驗證過程：

mysql> select * from stu_view3;
+------+----------------+--------+---------------------------------+
| id | name | age | info |
+------+----------------+--------+---------------------------------+
| 1 | bumblebee | 200 | A member of the deformed steel. |
| 2 | king of monkey | 10000 | Hero in Chinese Mythology. |
| 3 | Medusa | 100000 | In Greek mythology the Gorgon. |
+------+----------------+--------+---------------------------------+
3 rows in set (0.00 sec)

對連接了兩張基本表的視圖stu_view3進行更新操作：

mysql> UPDATE stu_view3 SET age=800 WHERE id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

驗證視圖stu_view3：

mysql> select * from stu_view3;
+------+----------------+--------+---------------------------------+
| id | name | age | info |
+------+----------------+--------+---------------------------------+
| 1 | bumblebee | 800 | A member of the deformed steel. |
| 2 | king of monkey | 10000 | Hero in Chinese Mythology. |
| 3 | Medusa | 100000 | In Greek mythology the Gorgon. |
+------+----------------+--------+---------------------------------+
3 rows in set (0.00 sec)

驗證基本表：

mysql> select * from students;
+------+----------------+--------+
| id | name | age |
+------+----------------+--------+
| 1 | bumblebee | 800 |
| 2 | king of monkey | 10000 |
| 3 | Medusa | 100000 |
+------+----------------+--------+
3 rows in set (0.00 sec)

總結：

1.在使用視圖的時候，就是與使用表的語法一樣的
2.創建視圖的時候，該視圖的名字如果與已經存在表重名的話，那麼會報錯，不允許創建。視圖就是一種特殊的表