我的MYSQL學習心得（一）
我的MYSQL學習心得（二）
我的MYSQL學習心得（三）
我的MYSQL學習心得（四）
我的MYSQL學習心得（五）
我的MYSQL學習心得（六）
我的MYSQL學習心得（七）
我的MYSQL學習心得（八）
我的MYSQL學習心得（九）
我的MYSQL學習心得（十）
我的MYSQL學習心得（十二）
這一篇《我的MYSQL學習心得（十一）》將會講解MYSQL的視圖
使用視圖的理由是什麼？
1、安全性：一般是這樣做的:創建一個視圖，定義好該視圖所操作的數據。
之後將用戶權限與視圖綁定，這樣的方式是使用到了一個特性：grant語句可以針對視圖進行授予權限。
2、查詢性能提高
3、有靈活性的功能需求後，需要改動表的結構而導致工作量比較大，那麼可以使用虛擬表的形式達到少修改的效果。
這是在實際開發中比較有用的
4、復雜的查詢需求，可以進行問題分解，然後將創建多個視圖獲取數據。將視圖聯合起來就能得到需要的結果了。
創建視圖
創建視圖的語法
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
其中，CREATE：表示新建視圖；
REPLACE：表示替換已有視圖
ALGORITHM ：表示視圖選擇算法
view_name ：視圖名
column_list：屬性列
select_statement：表示select語句
[WITH [CASCADED | LOCAL] CHECK OPTION]參數表示視圖在更新時保證在視圖的權限范圍之內
可選的ALGORITHM子句是對標准SQL的MySQL擴展。
ALGORITHM可取三個值：MERGE、TEMPTABLE或UNDEFINED。
如果沒有ALGORITHM子句，默認算法是UNDEFINED（未定義的）。算法會影響MySQL處理視圖的方式。
對於MERGE，會將引用視圖的語句的文本與視圖定義合並起來，使得視圖定義的某一部分取代語句的對應部分。
對於TEMPTABLE，視圖的結果將被置於臨時表中，然後使用它執行語句。
對於UNDEFINED，MySQL自己選擇所要使用的算法。如果可能，它傾向於MERGE而不是TEMPTABLE，
這是因為MERGE通常更有效，而且如果使用了臨時表，視圖是不可更新的。
LOCAL和CASCADED為可選參數，決定了檢查測試的范圍，默認值為CASCADED。
腳本 視圖的數據來自於兩個表
CREATE TABLE student (stuno INT ,stuname NVARCHAR(60)) CREATE TABLE stuinfo (stuno INT ,class NVARCHAR(60),city NVARCHAR(60)) INSERT INTO student VALUES(1,'wanglin'),(2,'gaoli'),(3,'zhanghai') INSERT INTO stuinfo VALUES(1,'wuban','henan'),(2,'liuban','hebei'),(3,'qiban','shandong') -- 創建視圖 CREATE VIEW stu_class(id,NAME,glass) AS SELECT student.`stuno`,student.`stuname`,stuinfo.`class` FROM student ,stuinfo WHERE student.`stuno`=stuinfo.`stuno` SELECT * FROM stu_class
查看視圖
查看視圖必須要有SHOW VIEW權限
查看視圖的方法包括：DESCRIBE、SHOW TABLE STATUS、SHOW CREATE VIEW
DESCRIBE查看視圖基本信息
DESCRIBE 視圖名
DESCRIBE stu_class
結果顯示了視圖的字段定義、字段的數據類型、是否為空、是否為主/外鍵、默認值和額外信息
DESCRIBE一般都簡寫成DESC
SHOW TABLE STATUS語句查看查看視圖基本信息
查看視圖的信息可以通過SHOW TABLE STATUS的方法
SHOW TABLE STATUS LIKE 'stu_class'
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment --------- ------ ------- ---------- ------ -------------- ----------- --------------- ------------ --------- -------------- ----------- ----------- ---------- --------- -------- -------------- ------- stu_class (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) VIEW
COMMENT的值為VIEW說明該表為視圖，其他的信息為NULL說明這是一個虛表，如果是基表那麼會基表的信息，這是基表和視圖的區別
SHOW CREATE VIEW語句查看視圖詳細信息
SHOW CREATE VIEW stu_class
View Create View character_set_client collation_connection --------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------- -------------------- stu_class CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `stu_class` AS select `student`.`stuno` AS `id`,`student`.`stuname` AS `name`,`stuinfo`.`class` AS `class` from (`student` join `stuinfo`) where (`student`.`stuno` = `stuinfo`.`stuno`) utf8 utf8_general_ci
執行結果顯示視圖的名稱、創建視圖的語句等信息
在VIEWS表中查看視圖的詳細信息
在MYSQL中，INFORMATION_SCHEMA VIEWS表存儲了關於數據庫中的視圖的信息
通過對VIEWS表的查詢可以查看數據庫中所有視圖的詳細信息
SELECT * FROM `information_schema`.`VIEWS`
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ------------- ------------ ---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ ------------ -------------- ------------- -------------------- -------------------- def school stu_class select `school`.`student`.`stuno` AS `id`,`school`.`student`.`stuname` AS `name`,`school`.`stuinfo`.`class` AS `class` from `school`.`student` join `school`.`stuinfo` where (`school`.`student`.`stuno` = `school`.`stuinfo`.`stuno`) NONE YES root@localhost DEFINER utf8 utf8_general_ci
當前實例下只有一個視圖stu_class
修改視圖
修改視圖是指修改數據庫中存在的視圖，當基本表的某些字段發生變化時，可以通過修改視圖來保持與基本表的一致性。
MYSQL中通過CREATE OR REPLACE VIEW 語句和ALTER語句來修改視圖
語法如下：
ALTER OR REPLACE [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
該語句用於更改已有視圖的定義。其語法與CREATE VIEW類似。當視圖不存在時創建，存在時進行修改
修改視圖
DELIMITER $$ CREATE OR REPLACE VIEW `stu_class` AS SELECT `student`.`stuno` AS `id` FROM (`student` JOIN `stuinfo`) WHERE (`student`.`stuno` = `stuinfo`.`stuno`)$$ DELIMITER ;
通過DESC來查看更改之後的視圖定義
DESC stu_class
可以看到只查詢一個字段
ALTER語句修改視圖
ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
這裡關鍵字跟前面的一樣，這裡不做介紹
使用ALTER語句修改視圖 stu_class
ALTER VIEW stu_class AS SELECT stuno FROM student;
使用DESC查看
DESC stu_class
更新視圖
更新視圖是指通過視圖來插入、更新、刪除表數據，因為視圖是虛表，其中沒有數據。
通過視圖更新的時候都是轉到基表進行更新，如果對視圖增加或者刪除記錄，實際上是對基表增加或刪除記錄
我們先修改一下視圖定義
ALTER VIEW stu_class AS SELECT stuno,stuname FROM student;
查詢視圖數據
UPDATE
UPDATE stu_class SET stuname='xiaofang' WHERE stuno=2
查詢視圖數據
更新成功
INSERT
INSERT INTO stu_class VALUES(6,'haojie')
插入成功
DELETE
DELETE FROM stu_class WHERE stuno=1
刪除成功
當視圖中包含如下內容的時候，視圖的更新操作將不能被執行
（1）視圖中包含基本中被定義為非空的列
（2）定義視圖的SELECT語句後的字段列表中使用了數學表達式
（3）定義視圖的SELECT語句後的字段列表中使用聚合函數
（4）定義視圖的SELECT語句中使用了DISTINCT、UNION、TOP、GROUP BY 、HAVING子句
刪除視圖
刪除視圖使用DROP VIEW語法
DROP VIEW [IF EXISTS] view_name [, view_name] ... [RESTRICT | CASCADE]
DROP VIEW能夠刪除1個或多個視圖。必須在每個視圖上擁有DROP權限
可以使用關鍵字IF EXISTS來防止因不存在的視圖而出錯
刪除stu_class視圖
DROP VIEW IF EXISTS stu_class
如果名稱為 stu_class 的視圖存在則刪除
使用SHOW CREATE VIEW語句查看結果
SHOW CREATE VIEW stu_class
Query: -- update stu_class set stuname='xiaofang' where stuno=2; -- delete from stu_class where stuno=1 -- select * from stu_class; -- ... Error Code: 1146 Table 'school.stu_class' doesn't exist Execution Time : 0 sec Transfer Time : 0 sec Total Time : 0.004 sec ---------------------------------------------------
該視圖不存在，刪除成功
總結
SQLSERVER裡實際上跟MYSQL一樣，也是有信息架構視圖的
信息架構視圖 (Transact-SQL)
信息架構視圖是 SQL Server 提供的幾種獲取元數據的方法之一。
信息架構視圖提供獨立於系統表的內部 SQL Server 元數據視圖。
盡管已經對基礎系統表進行了重要的修改，信息架構視圖仍然可使應用程序正常工作。
SQL Server 中包含的信息架構視圖符合 ISO 標准中的信息架構定義。
信息架構視圖的數據是存放在系統數據庫Resource 數據庫裡面
mssqlsystemresource.mdf
利用INFORMATION_SCHEMA視圖來拼接 SQL 語句
如有不對的地方，歡迎大家拍磚o(∩_∩)o
