視圖
一、視圖的概述
1. 含義：是從資料庫中一個或多個表中導出的虛拟表
2. 作用：①簡單化 ②安全性 ③邏輯資料獨立性
3. 注意：一個表可以由多個視圖；
二、視圖的建立
1. 總的文法形式
CREATE
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
介紹
REPLACE：表示替換已經建立的視圖
ALGORITHM：表示視圖選擇的算法
view_name ：視圖名
column_list：屬性清單
select_statement：select語句
WITH [CASCADED | LOCAL] CHECK OPTION：表示視圖在更新時保證在視圖的權限範圍内
CASCADED：預設值，表示更新視圖時要滿足所有相關的視圖和表的條件
LOCAL：表示更新視圖時滿足該視圖本身定義的條件即可。
UNDEFINED：表示MySQL自動選擇算法
MERGE：表示将使用視圖語句與視圖定義合并起來，使得視圖定義的某一部分取代語句對應的部分 TEMPTABLE：表示将視圖的結果存入臨時表，然後用臨時表來執行語句
2. 在單表上建立視圖
文法：
CREATE VIEW 視圖名 [(視圖字段，……，字段n)] AS select 表字段名，……，表字段名n from 資料表；
CREATE or replace VIEW 視圖名 [(視圖字段，……，字段n)] AS select 表字段名，……，表字段名n from 資料表；
語句2，如果視圖沒有建立，則建立；如果視圖已建立，則修改視圖。
執行個體：
【例】
mysql>create view view_e(a,b,c,d,e,g,h) as select gkbh,yhm,mm,xm,xb,csrq,sfzh from guke;
【附，顯示】
select * from view_e;
+-------+----------+----------+------+------+------------+--------------------+
| a | b | c | d | e | g | h |
+-------+----------+----------+------+------+------------+--------------------+
| 00001 | aaaaaaaa | 12345678 | 李明 | 男 | 1990-05-06 | 111111111111111111 |
| 00002 | bbbbbbbb | 12345678 | 李逵 | 男 | 1988-05-06 | 111111111111111111 |
| 00003 | cccccccc | 12345678 | 西施 | 女 | 1990-05-06 | 111111111111111111 |
+-------+----------+----------+------+------+------------+--------------------+
(2) 注意：預設情況下建立的視圖和基本表的字段是一樣的，也可以通過指定視圖字段的名稱來建立視圖。
3. 在多表上建立視圖
文法：
CREATE VIEW 視圖名 [(視圖字段，……，字段n)]
AS A表表名.表字段名,……，A表表名.表字段名n，
B表表名.表字段名,……，B表表名.表字段名
from 資料A表，資料B表
where 連接配接條件；
執行個體：
【例】
mysql> create view view_a(訂單編号,商品編号,顧客編号,訂單日期)
as select dingdanxiangqing.ddbh,dingdanxiangqing.spbh,
dingdan.gkbh,dingdan.ddrq
from dingdan,dingdanxiangqing
where dingdanxiangqing.ddbh=dingdan.ddbh;
【附，效果】
mysql> select * from view_a;
+----------+----------+----------+---------------------+
| 訂單編号 | 商品編号 | 顧客編号 | 訂單日期 |
+----------+----------+----------+---------------------+
| 1 | 00000001 | 00001 | 2016-01-01 00:00:00 |
| 2 | 00000003 | 00003 | 2016-02-10 00:00:00 |
| 1 | 00000004 | 00001 | 2016-01-01 00:00:00 |
+----------+----------+----------+---------------------+
三、視圖的檢視
文法：
desc 視圖表
執行個體：
【例】
mysql> desc view_a;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| 訂單編号 | int(11) | NO | | 0 | |
| 商品編号 | char(8) | NO | | | |
| 顧客編号 | char(5) | NO | | NULL | |
| 訂單日期 | datetime | NO | | NULL | |
+----------+----------+------+-----+---------+-------+
作用：顯示視圖的字段定義、字段的資料類型、是否為空、是否是主鍵\外鍵、預設值和額外資訊
文法
show table status like'視圖表名’\G;
執行個體
mysql> show table status like'view_a';
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+
| view_a | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+
-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+
Data_free |Auto_increment | Create_time | Update_time | Check_time | Collation |Checksum | Create_options |
-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+
NULL | NULL |NULL | NULL | NULL | NULL | NULL | NULL |
-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+
---------+
Comment |
---------+
VIEW |
---------+
或者mysql>show table status like'view_a'\G;
*************************** 1. row ***************************
Name: view_a
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW
作用：顯示視圖的存儲引擎，建立時間
文法
show create view 視圖名
執行個體
【例】
mysql> show create view view_a\G;
*************************** 1. row ***************************
View: view_a
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_a` AS select `dingdanxiangqing`.`ddbh` AS `訂單編号`,`dingdanxiangqing`.`spbh` AS `商品編号`,`dingdan`.`gkbh` AS `顧客編号`,`dingdan`.`ddrq` AS `訂單日期` from (`dingdan` join `dingdanxiangqing`) where (`dingdanxiangqing`.`ddbh` = `dingdan`.`ddbh`)
character_set_client: gbk
collation_connection: gbk_chinese_ci
作用：顯示視圖的名稱和建立語句
文法
select * from information_schema.views\G;
執行個體
mysql> select * from information_schema.views\G;
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: webshop
TABLE_NAME: view_a
VIEW_DEFINITION: select `webshop`.`dingdanxiangqing`.`ddbh` AS `訂單編号`,`webshop`.`dingdanxiangqing`.`spbh` AS `商品編号`,`webshop`.`dingdan`.`gkbh` AS `顧客編号`,`webshop`.`dingdan`.`ddrq` AS `訂單日期` from `webshop`.`dingdan` join `webshop`.`dingdanxiangqing` where (`webshop`.`dingdanxiangqing`.`ddbh` = `webshop`.`dingdan`.`ddbh`)
CHECK_OPTION: NONE
IS_UPDATABLE: YES
DEFINER: [email protected]
SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: gbk
COLLATION_CONNECTION: gbk_chinese_ci
作用：顯示所有的資料表
四、視圖的修改
①CREATE語句
總的文法
CREATE
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
簡化版
CREATE or replace view 視圖名 as select 表字段 from 表名
注意：該文法與建立文法完全一樣，當視圖不存在時，此文法是建立視圖；視圖存在時，此文法是修改視圖；
②ALTER文法
總的文法：
ALTER
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
簡化版：
ALTER view 視圖名 as select 表字段 from 表名
五、視圖的更新
注意：
①因為視圖是虛拟表是以，是以對視圖的操作，實際上是對基本表的操作；
②對其中的一個視圖更新後，其基本表也更新了，進而與這個基本表相關的視圖表的内容也更新。
③不可以更新的視圖
1.包含以下關鍵字的sql語句：聚合函數(sum、min、max、count)、distinct、group by 、having、union 或者uinon all
2.常量視圖
3.select 中包含子查詢
4.join
5.from一個不可更新的試圖
6.where字句的子查詢引用了from字句中的表
(1)更新視圖
文法：
update 視圖名 set 更新資料；
執行個體：
原資料表：
mysql> select * from guke;
+-------+------+------------+
| gkbh | xm | csrq |
+-------+------+------------+
| 00001 | 李明 | 1990-05-06 |
| 00002 | 李逵 | 1988-05-06 |
| 00003 | 西施 | 1990-05-06 |
+-------+------+------------+
原視圖：
mysql> select * from view_c;
+----------+------+------------+
| 顧客編号 | 姓名 | 出生日期 |
+----------+------+------------+
| 00001 | 李明 | 1990-05-06 |
| 00002 | 李逵 | 1988-05-06 |
| 00003 | 西施 | 1990-05-06 |
+----------+------+------------+
改後：
視圖：
mysql> update view_c set 姓名='哈哈在笑' where 顧客編号='00002';
mysql> select * from view_c;
+----------+----------+------------+
| 顧客編号 | 姓名 | 出生日期 |
+----------+----------+------------+
| 00001 | 哈哈一笑 | 1990-05-06 |
| 00002 | 哈哈在笑 | 1988-05-06 |
| 00003 | 哈哈一笑 | 1990-05-06 |
+----------+----------+------------+
基本表：
mysql> select gkbh,xm,csrq from guke;
+-------+----------+------------+
| gkbh | xm | csrq |
+-------+----------+------------+
| 00001 | 哈哈一笑 | 1990-05-06 |
| 00002 | 哈哈在笑 | 1988-05-06 |
| 00003 | 哈哈一笑 | 1990-05-06 |
+-------+----------+------------+
注意：基本表已經改變
(2)插入記錄
文法：
insert into 資料表名 values(插入的資料，……，插入的資料n);
六、視圖的删除
1. 總的文法
DROP VIEW [IF EXISTS]
view_name [, view_name] ...
[RESTRICT | CASCADE]