程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 我的MYSQL學習心得(十一)

我的MYSQL學習心得(十一)

編輯:MySQL綜合教程

我的MYSQL學習心得(十一)

我的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 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved