MySql的視圖

標籤：使用者名稱   字元   選擇   mysq   tab   undefined   code   ora   名稱   

視圖是從一個或多個表中匯出的表。是一種虛擬存在的表。視圖就像一個視窗，通過這個視窗可以看到系統專門提供的資料。這樣，使用者可以不用看到整個資料庫表中資料，而只關心對自己有用的資料。視圖可以使使用者的操作更方便，而且可以保障資料庫系統的安全性。

資料庫中只存放了視圖的定義，而並沒有存放視圖中的資料。這些資料存放在原來的表中，使用視圖查詢資料時，資料庫系統會從原來的表中取出對應的資料。因此，視圖中的資料是依賴於原來的表中的資料的。一旦表中的資料發生改變，顯示在視圖中的資料也會發生改變。

視圖是儲存在資料庫中的查詢的sql語句，它主要出於兩種原因：安全原因，視圖可以隱藏一些資料，例如：員工資訊表，可以用視圖只顯示姓名、工齡、地址，而不顯示社會保險號和工資數等，另一原因是可使複雜的查詢易於理解和使用。

視圖的作用

對其中所引用的基礎資料表來說，視圖的作用類似於篩選。定義視圖的篩選可以來自當前或其他資料庫的一個或多個表，或者其他視圖。通過視圖進行查詢沒有任何限制，通過它們進行資料修改時的限制也很少。下面將視圖的作用歸納為如下幾點：

1.簡單性

看到的就是需要的。視圖不僅可以簡化使用者對資料的理解，也可以簡化對資料的操作。那些被經常使用的查詢可以被定義為視圖，從而使得使用者不必為以後的每次操作指定全部的條件。

2.安全性

視圖的安全性可以防止未授權使用者查看特定的行或列，許可權使用者只能看到表中特定行的方法如下：

（1）在表中增加一個標誌使用者名稱的列；

（2）建立視圖，是使用者只能看到標有自己使用者名稱的行；

（3）把視圖授權給其他使用者。

3.邏輯資料獨立性

視圖可以使應用程式和資料庫表在一定程度上獨立。如果沒有視圖，程式一定是建立在表上的。有了視圖之後，程式可以建立在視圖之上，從而程式與資料庫表被視圖分割開來。視圖可以在以下幾個方面使程式與資料獨立：

（1）如果應用建立在資料庫表上，當資料庫表發生變化時，可以在表上建立視圖，通過視圖屏蔽表的變化，從而應用程式可以不動。

（2）如果應用建立在資料庫表上，當應用發生變化時，可以在表上建立視圖，通過視圖屏蔽應用的變化，從而使資料庫表不動。

（3）如果應用建立在視圖上，當資料庫表發生變化時，可以在表上修改視圖，通過視圖屏蔽表的變化，從而應用程式可以不動。

（4）如果應用建立在視圖上，當應用發生變化時，可以在表上修改視圖，通過視圖屏蔽應用的變化，從而資料庫可以不動。

建立視圖

建立視圖是指在已經存在的資料庫表上建立視圖。視圖可以建立在一張表中，也可以建立在多張表中。

建立視圖需要具有CREATE VIEW的許可權，同時應該具有查詢涉及的列的SELECT許可權。可以使用SELECT語句來查詢這些許可權資訊，查詢文法如下：

SELECT select_priv, create_view_priv FROM mysql.user WHERE user=‘root‘;

MySQL中，建立視圖是通過CREATE VIEW語句實現的。其文法如下：

CREATE [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW視圖名 [（屬性清單）]

AS SELECT語句 WITH [CASCADED|LOCAL] CHECK OPTION;

ALGORITHM是選擇性參數，表示視圖選擇的演算法；

“視圖名”參數表示要建立的視圖名稱；

“屬性清單”是選擇性參數，指定視圖中各個屬性的名詞，預設情況下與SELECT語句中查詢的屬性相同；

SELECT語句參數是一個完整的查詢語句，表示從某個表中查出某些滿足條件的記錄，將這些記錄匯入視圖中；

WITH CHECK OPTION是選擇性參數，表示更新視圖時要保證在該視圖的許可權範圍之內。

CREATE ALGORITHM=MERGE VIEWbook_view1（a_sort, a_talk, a_books, a_name）AS SELECT sort, talk, books, tb_user.nameFROM tb_book, tb_user WHERE tb_book.id=tb_user.idWITH LOCAL CHECK OPTION；

建立視圖時需要注意以下幾點：

（1）運行建立視圖的語句需要使用者具有建立視圖（create view）的許可權，若添加了[or replace]時，還需要使用者具有刪除視圖（drop view）的許可權；

（2）select語句不能包含from子句中的子查詢；

（3）select語句不能引用系統或使用者變數；

（4）select語句不能引用預先處理語句參數；

（5）在儲存子程式內，定義不能引用子程式參數或局部變數；

（6）在定義中引用的表或視圖必須存在。但是建立了視圖後，能夠捨棄定義引用的表或視圖。要想檢查視圖定義是否存在這類問題，可使用check table語句；

（7）在定義中不能引用temporary表，不能建立temporary視圖；

（8）在視圖定義中命名的表必須已存在；

（9）不能將觸發程式與視圖關聯在一起；

（10）在視圖定義中允許使用order by，但是，如果從特定視圖進行了選擇，而該視圖使用了具有自己order by的語句，它將被忽略。

查看視圖

查看視圖是指查看資料庫中已存在的視圖。查看視圖必須要有SHOW VIEW的許可權。查看視圖的方法主要包括DESCRIBE語句、SHOW TABLE STATUS語句、SHOW CREATE VIEW語句等。select * from information_schema.views;

1.DESCRIBE語句

DESCRIBE可以縮寫成DESC, DESC語句的格式如下：

DESCRIBE 視圖名；

2.SHOW TABLE STATUS語句

在MYSQL中，可以使用SHOW TABLE STATUS語句查看視圖的資訊。其文法格式如下：

SHOW TABLE STATUS LIKE ‘視圖名‘；

“LIKE”表示後面匹配的是字串；

“視圖名”參數指要查看的視圖名稱，需要用單引號定義。

3.SHOW CREATE VIEW語句

在MYSQL中，SHOW CREATE VIEW語句可以查看視圖的詳細定義。其文法格式如下：

SHOW CREATE VIEW 視圖名

修改視圖

修改視圖是指修改資料庫中已存在的表的定義。當基本表的某些欄位發生改變時，可以通過修改視圖來保持視圖和基本表之間一致。MySQL中通過CREATE OR REPLACE VIEW語句和ALTER語句來修改視圖。

1.CREATE OR REPLACE VIEW

在MYSQL中，CREATE OR REPLACE VIEW語句可以用來修改視圖。該語句的使用非常靈活。在視圖已經存在的情況下，對視圖進行修改；視圖不存在時，可以建立視圖。CREATE OR REPLACE VIEW語句的文法如下：

CREATE OR REPLACE [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]

VIEW視圖 [（屬性清單）] AS SELECT語句

WITH [CASCADED|LOCAL] CHECK OPTION;

2.ALTER

ALTER VIEW語句改變了視圖的定義，包括被索引檢視表，但不影響所依賴的預存程序或觸發器。該語句與CREATE VIEW語句有著同樣的限制，如果刪除並重建了一個視圖，就必須重新為它分配許可權。

alter view語句的文法如下：

alter view [algorithm={merge|temptable|undefined}]

view view_name [（column_list）]

as select_statement with [cascaded|local] check option; 

ps:在建立視圖時，在使用了WITH CHECK OPTION, WITH ENCRYPTION, WITH SCHEMABING或VIEW_METADATA選項時，如果想保留這些選項提供的功能，必須在ALTER VIEW語句中將它們包括進去。

更新視圖

對視圖的更新其實就是對錶的更新，更新視圖是指通過視圖來插入（INSERT）、更新（UPDATE）和刪除（DELETE）表中的資料。因為視圖是一個虛擬表，其中沒有資料。通過視圖更新時，都是轉換到基本表來更新。更新視圖時，只能更新許可權範圍內的資料。超出了範圍，就不能更新。

1.更新視圖

與表的更新使用類似

2.更新視圖的限制

並不是所有的視圖都可以更新，以下幾種情況是不能更新視圖的：

（1）視圖中包含COUNT（）、SUM（）、MAX（）和MIN（）等函數。例如：

CREATE VIEW book_view1（a_sort, a_book）

AS SELECT sort, books, COUNT（name）FROM tb_book；

（2）視圖中包含UNION、UNION ALL、DISTINCT、GROUP BY和HAVIG等關鍵字。例如：

CREATE VIEW book_view1（a_sort, a_book）

AS SELECT sort, books, FROM tb_book GROUP BY id；

（3）常量視圖。例如：

CREATE VIEW book_view1

AS SELECT ‘Aric‘ as a_book；

（4）視圖中的SELECT中包含子查詢。例如：

CREATE VIEW book_view1（a_sort）

AS SELECT（SELECT name FROM tb_book）；

（5）由不可更新的視圖匯出的視圖。例如：

CREATE VIEW book_view1

AS SELECT*FROM book_view2；

（6）建立視圖時，ALGORITHM為TEMPTABLE類型。例如：

CREATE ALGORITHM=TEMPTABLE

VIEW book_view1

AS SELECT*FROM tb_book；

（7）視圖對應的表上存在沒有預設值的列，而且該列沒有包含在視圖裡。例如，表中包含的name欄位沒有預設值，但是視圖中不包括該欄位。那麼這個視圖是不能更新的。因為，在更新視圖時，這個沒有預設值的記錄將沒有值插入，也沒有NULL值插入。資料庫系統是不會允許這樣的情況出現的，其會阻止這個視圖更新。

上面的幾種情況其實就是一種情況，規則就是，視圖的資料和基本表的資料不一樣了。

ps:視圖中雖然可以更新資料，但是有很多的限制。一般情況下，最好將視圖作為查詢資料的虛擬表，而不要通過視圖更新資料。因為，使用視圖更新資料時，如果沒有全面考慮在視圖中更新資料的限制，可能會造成資料更新失敗。

刪除視圖

刪除視圖是指刪除資料庫中已存在的視圖。刪除視圖時，只能刪除視圖的定義，不會刪除資料。MySQL中，可以使用DROP VIEW語句來刪除視圖，但是，使用者必須擁有DROP許可權。

DROP VIEW語句的文法如下：

DROP VIEW IF EXISTS ＜視圖名＞ [RESTRICT|CASCADE]

IF EXISTS參數指判斷視圖是否存在，如果存在則執行，不存在則不執行；

“視圖名列表”參數表示要刪除的視圖的名稱和列表，各個視圖名稱之間用逗號隔開。

該語句從資料字典中刪除指定的視圖定義；如果該視圖匯出了其他視圖，則使用CASCADE串聯刪除，或者先顯式刪除匯出的視圖，再刪除該視圖；刪除基表時，由該基表匯出的所有視圖定義都必須顯式刪除。

 

MySql的視圖