10_MySQL檢視

檢視簡介

1. 檢視是什麼？
一張虛表，和真實表一樣，檢視包含一系列帶有名稱的行和列資料。檢視是從一個或多個表中匯出來的，我們也可以通過insert，update，delete來操作檢視。當通過檢視看到資料被修改時，相對應的原表的資料也會變化。同時原表發變化，則這種變化也可以自動反映到檢視中。

2. 檢視的優點
（1）簡單化：看到的就是需要的。檢視不僅可以簡化使用者對資料的理解，也可以簡化操作，經常被使用的查詢可以製作成一個檢視
（2）安全性：通過檢視使用者只能查詢和修改所能見到的資料，資料庫中其他的資料即看不見也取不到。資料庫授權命令可以讓每個使用者對資料庫的檢索限制到特定的資料庫物件上，但不能授權到資料庫特定的行，列上。
（3）邏輯資料獨立性：檢視可以幫助使用者遮蔽真實表結構變化帶來的影響。

建立檢視

Create [or replace替換] [algroithm檢視選擇的演算法={undefined|merge |temptable}] view 檢視名
[(column_list)] as select_statement [with[cascaded|local]check option]

[algroithm檢視選擇的演算法={undefined|merge |temptable}] Undefined：不常用。 merge ：表示將使用
的檢視語句與檢視定義合併起來，使檢視定義的某一部分取代語句對應的部分 temptable ：表示將檢視的結果存入臨時表，然後用臨時表來執行語句

with[cascaded|local] Cascaded ：預設為cascaded，表示更新檢視時，滿足所有相關檢視和表的條件
Local ：表示更新檢視時，滿足該檢視本身定義的條件即可

1.建立單表檢視

mysql> create table t1(quantity int, price int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(3,50);
Query OK, 1 row affected (0.00 sec)

mysql> create view view_t1 
    -> as select quantity,price,quantity*price
    -> from t1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from view_t1;
+----------+-------+----------------+
| quantity | price | quantity*price |
+----------+-------+----------------+
|        3 |    50 |            150 |
+----------+-------+----------------+
1 row in set (0.00 sec)

//加上別名顯示

mysql> create view view_t2(qty,price,total)
    -> as select quantity,price,quantity*price
    -> from t1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from view_t2;
+------+-------+-------+
| qty  | price | total |
+------+-------+-------+
|    3 |    50 |   150 |
+------+-------+-------+
1 row in set (0.00 sec)

2.建立多表檢視

mysql> create table student
    -> (
    -> s_id int primary key,
    -> s_name char(22),
    -> s_age int,
    -> s_sex char(22)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table stu_info
    -> (
    -> s_id int,
    -> class char(22),
    -> addr varchar(22)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into stu_info(s_id,class,addr)
    -> values
    -> (1,'erban','anhui'),
    -> (2,'sanban','chongqing'),
    -> (3,'yiban','shandong');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into student(s_id,s_name)
    -> values
    -> (1,'zhangsan'),
    -> (2,'lisi'),
    -> (3,'wangwu');

mysql> create view stu_class(id,name,class) 
    -> as select student.s_id,student.s_name,stu_info.class
    -> from student,stu_info 
    -> where student.s_id = stu_info.s_id;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from stu_class;
+----+----------+--------+
| id | name     | class  |
+----+----------+--------+
|  1 | zhangsan | erban  |
|  2 | lisi     | sanban |
|  3 | wangwu   | yiban  |
+----+----------+--------+
3 rows in set (0.00 sec)

檢視檢視

1.檢視檢視的基本資訊

語法：Show table status like ‘檢視名’\G;

mysql> show table status\G

mysql> show table status like 'view_t2'\G

2.檢視檢視的詳細資訊

語法：show create view 檢視名\G

mysql> show create view view_t1\G
*************************** 1. row ***************************
                View: view_t1
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_t1` AS select `t1`.`quantity` AS `quantity`,`t1`.`price` AS `price`,(`t1`.`quantity` * `t1`.`price`) AS `quantity*price` from `t1`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

在mysql中，information schema 資料庫下的views表中儲存了所有檢視的定義。通過對views表的查詢，可以檢視資料庫中所有檢視的詳細資訊。

mysql> use information_schema 
mysql> select * from views where TABLE_NAME = 'view_t1'\G
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: qin
          TABLE_NAME: view_t1
     VIEW_DEFINITION: select `qin`.`t1`.`quantity` AS `quantity`,`qin`.`t1`.`price` AS `price`,(`qin`.`t1`.`quantity` * `qin`.`t1`.`price`) AS `quantity*price` from `qin`.`t1`
        CHECK_OPTION: NONE
        IS_UPDATABLE: YES
             DEFINER: root@localhost
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
1 row in set (0.00 sec)

修改檢視

語法：Create or replace view

mysql> create or replace view view_t1 as select * from t1;
Query OK, 0 rows affected (0.00 sec)

mysql> desc view_t1;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| quantity | int(11) | YES  |     | NULL    |       |
| price    | int(11) | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

//alter修改檢視

mysql> desc view_t2;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| qty   | int(11)    | YES  |     | NULL    |       |
| price | int(11)    | YES  |     | NULL    |       |
| total | bigint(21) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter view view_t2 as select quantity from t1;
Query OK, 0 rows affected (0.00 sec)

mysql> desc view_t2;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| quantity | int(11) | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

更新檢視

update
//檢視原資料

mysql> select * from t1;
+----------+-------+
| quantity | price |
+----------+-------+
|        3 |    50 |
+----------+-------+
1 row in set (0.00 sec)

mysql> select * from view_t2;
+----------+
| quantity |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

//修改後再次檢視

mysql> update view_t2 set quantity=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from view_t2;
+----------+
| quantity |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

mysql> select * from t1;
+----------+-------+
| quantity | price |
+----------+-------+
|        5 |    50 |
+----------+-------+
1 row in set (0.00 sec)

//原表插入資料檢視表也會跟著改變

mysql> insert into t1 values(3,5);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----------+-------+
| quantity | price |
+----------+-------+
|        5 |    50 |
|        3 |     5 |
+----------+-------+
2 rows in set (0.00 sec)

mysql> select * from view_t2;
+----------+
| quantity |
+----------+
|        5 |
|        3 |
+----------+
2 rows in set (0.00 sec)

//刪除檢視表欄位，原表也會跟著刪除
相反刪除原表欄位，檢視表也會跟著刪除

mysql> select * from view_t1;
+----------+-------+
| quantity | price |
+----------+-------+
|        5 |    50 |
|        3 |     5 |
+----------+-------+
2 rows in set (0.00 sec)

mysql> delete from view_t1 where price=5;
Query OK, 1 row affected (0.00 sec)

mysql> select * from view_t1;
+----------+-------+
| quantity | price |
+----------+-------+
|        5 |    50 |
+----------+-------+
1 row in set (0.00 sec)

mysql> select * from t1;
+----------+-------+
| quantity | price |
+----------+-------+
|        5 |    50 |
+----------+-------+
1 row in set (0.00 sec)

檢視存在以下情況時，更新操作無法執行
（1）檢視中不包含原表中被定義為非空的列
（2） 在定義檢視的select語句後的欄位列表中使用了數學表示式
（3）在定義檢視的select 語句後欄位列表中使用了聚合函式時不接受更新操作
（4）select中，使用了union \top \group by 或having 無法接受

刪除檢視

語法 Drop view [IF EXISTS]檢視名1，檢視名2 ……

mysql> drop view if exists stu_class;
Query OK, 0 rows affected (0.00 sec)

擴充套件

MySQL中檢視和表的區別以及聯絡是什麼？

1.兩者的區別
（1）檢視是已經編譯好的SQL語句，是基於SQL語句的結果集的視覺化的表。而表不是。
（2）檢視沒有實際的物理記錄，而表有。
（3）表是內容，檢視視窗
（4）表和檢視雖然都佔用物理空間，但是檢視只是邏輯概念存在，而表可以及時對資料進行修改，但是檢視只能用建立語句來修改 。
（5）檢視是檢視資料表的一種方法，可以查詢資料表中某些欄位構成的資料，只是一些SQL 語句的集合。從安全形度來說，檢視可以防止使用者接觸資料表，因而知道表結構 。
（6）表屬於全域性模式中的表，是實表。而檢視屬於區域性模式的表，是虛表
（7）檢視的建立和刪除隻影響檢視本身，而不影響對應表的基本表。

2.兩者的聯絡
檢視是在基本表之上建立的表，它的結構和內容都來自於基本表，它依賴基本表存在而存在。一個檢視可以對應一個基本表，也可以對應多個基本表。檢視是基本的抽象和邏輯意義上建立的關係。