MySQL系列：檢視基本操作（3）

libingql發表於2017-02-26
MySql

1. 檢視簡介

1.1 檢視定義

　　檢視是一種虛擬的表，是從資料庫中一個或多個表中匯出來的表。

　　檢視可以從已存在的檢視的基礎上定義。

　　資料庫中只存放檢視的定義，並沒有存放檢視中的資料，資料存放在原來的表中。

　　檢視中的資料依賴於原來的表中的資料，表中的資料發生變化，顯示在檢視中的資料也會改變。

1.2 檢視作用

　　（1）增加資料安全性

　　通過檢視，使用者只能查詢和修改指定的資料。

　　資料庫授權命令可以限制使用者的操作許可權，但不能限制到特定行和列上。使用檢視可以將使用者的許可權限制到特定的行和列上。

　　（2）提高表的邏輯獨立性

　　檢視可以遮蔽原有表結構變化帶來的影響。

　　原有的表結構增加列和刪除未被引用的列，對檢視不造成影響。

2. 建立檢視

2.1 建立檢視語法

　　CREATE VIEW語法形式：

CERATE [ALGORITHM = { UNDEFINED | MERGE | TEMPTABLE }]
    VIEW view_name [(column_name, .. ,column_name)]
    AS SELECT statement
    [WITH [CASCADED | LOCAL] CHECK OPTION];

　　其中：

　　ALGORITHM：檢視選擇的演算法，包括UNDEFINED,MERGE及TEMPTABLE。

　　　　UNDEFINED：MySQL將自動選擇所需要的演算法；

　　　　MERGE：將使用檢視的語句與檢視定義合併起來，使得檢視定義的某一部分取代語句的對應部分；

　　　　TEMPTABLE：將檢視的結果存入臨時表，使用臨時表執行語句。

　　CASCADED：表示更新檢視時，要滿足所有相關檢視和表的條件，該引數為預設值。

　　LOCAL：表示更新檢視時，要滿足該檢視本身的定義的條件即可。

　　使用CREATE VIEW語句建立檢視時，最好加上WITH CASCADED CHECK OPTION引數。

　　建立檢視時，需要有CREATE VIEW許可權。同時具有查詢涉及列的SELECT許可權。

　　在mysql資料庫的user表中儲存使用者許可權，使用SELECT語句查詢：

mysql> SELECT Select_priv,Create_view_priv FROM mysql.user WHERE user='root';
+-------------+------------------+
| Select_priv | Create_view_priv |
+-------------+------------------+
| Y           | Y                |
+-------------+------------------+

2.2 建立檢視示例

mysql> CREATE VIEW vproduct
    -> AS
    -> SELECT ProductID,ProductName FROM product;
mysql> CREATE VIEW vproduct(ProductID,ProductName)
    -> AS
    -> SELECT ProductID,ProductName FROM product;

3. 檢視檢視

　　檢視檢視是指檢視資料庫中已存在的檢視的定義，檢視檢視要有SHOW VIEW許可權。

　　檢視檢視的方法包括：DESCRIBE、SHOW TABLE STATUS、SHOW CREATE VIEW及查詢information_schema資料庫下的views表。

3.1 DESCRIBE語句檢視檢視基本資訊

　　語法格式：

DESCRIBE view_name

　　示例：

mysql> DESC vproduct;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| ProductID   | int(11)     | NO   |     | NULL    |       |
| ProductName | varchar(50) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

3.2 SHOW TABLE STATUS語句檢視檢視基本資訊

　　語法格式：

SHOW TABLE STATUS LIKE 'view_name'

　　示例：

mysql> SHOW TABLE STATUS LIKE 'vproduct';
mysql> show table status like 'vproduct'\G
*************************** 1. row ***************************
           Name: vproduct
         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
1 row in set (0.00 sec)

　　執行結果顯示，Comment值為VIEW，說明該表為檢視。儲存引擎、資料長度等資訊都顯示為NULL，說明檢視是虛擬表。

3.3 SHOW CREATE VIEW語句檢視檢視詳細資訊

　　語法格式：

SHOW CREATE VIEW view_name

　　示例：

mysql> SHOW CREATE VIEW vproduct\G
*************************** 1. row ***************************
                View: vproduct
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vproduct` AS select `product`.`ProductID` AS `ProductID`,`product`.`ProductName` AS `ProductName` from `product`
character_set_client: gbk
collation_connection: gbk_chinese_ci
1 row in set (0.11 sec)

3.4 在views表中檢視檢視詳細資訊

mysql> SELECT * FROM information_schema.views WHERE TABLE_NAME='vproduct'\G
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: portal
          TABLE_NAME: vproduct
     VIEW_DEFINITION: select `portal`.`product`.`ProductID` AS `ProductID`,`portal`.`product`.`ProductName` AS `ProductName` from `portal`.`product`
        CHECK_OPTION: NONE
        IS_UPDATABLE: YES
             DEFINER: root@localhost
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: gbk
COLLATION_CONNECTION: gbk_chinese_ci
1 row in set (0.00 sec)

4. 修改檢視

　　MySQL中通過CREATE OR REPLACE VIEW語句和ALTER語句來修改檢視。

4.1 CREATE OR REPLACE VIEW語句修改檢視

　　CREATE OR REPLACE VIEW語句可以用來修改檢視，在檢視已存在的情況下，修改檢視；檢視不存在時，建立檢視。

　　CREATE OR REPLACE VIEW語法格式：

CERATE OR REPLACE [ALGORITHM = { UNDEFINED | MERGE | TEMPTABLE }]
    VIEW view_name [(column_name, .. ,column_name)]
    AS SELECT statement
    [WITH [CASCADED | LOCAL] CHECK OPTION];

　　示例：

mysql> CREATE OR REPLACE VIEW vproduct
    -> AS
    -> SELECT ProductID,ProductName FROM product;

4.2 ALTER語句修改檢視

　　ALTER VIEW語法格式：

ALTER [ALGORITHM = { UNDEFINED | MERGE | TEMPTABLE }]
    VIEW view_name [(column_name, .. ,column_name)]
    AS SELECT statement
    [WITH [CASCADED | LOCAL] CHECK OPTION];

　　示例：

mysql> ALTER VIEW vproduct
    -> AS
    -> SELECT ProductID,ProductName FROM product
    -> WITH CASCADED CHECK OPTION;

5. 刪除檢視

　　刪除檢視是指刪除資料庫中已存在的檢視。刪除檢視時，只能刪除檢視的定義，不會刪除資料。

　　語法形式：

DROP VIEW [IF EXISTS] view_name,[view_name] [RESTRICT | CASCADE]

　　其中，IF EXISTS判斷檢視存在，若存在則執行，不存在則不執行。刪除多個檢視之間使用逗號隔開。

　　示例：

mysql> DROP VIEW IF EXISTS vproduct;

相關文章