MySQL 檢視的基礎操作（五）_Mysql

1.為什麼使用視圖： 
    為了提高複雜SQL語句的複用性和表操作的安全性（例如：工資欄位不想展示給所有能查看該查詢結果的人），MySQL提供了視圖特性。所謂視圖，本質上是一種虛擬表，其內容與真實的表相似，包含一系列帶有名稱的列和行資料。但是，視圖並不在資料庫中以儲存資料值的形式存在。行和列的資料來自訂視圖的查詢所引用基本表，並且在具體使用視圖時動態產生。 
視圖有如下特點;
      1. 視圖的列可以來自不同的表，是表的抽象和邏輯意義上建立的新關係。
      2. 視圖是由基本表（實表）產生的表（虛表）。
      3. 視圖的建立和刪除不影響基本表。
      4. 對視圖內容的更新（添加、刪除和修改）直接影響基本表。
      5. 當視圖來自多個基本表時，不允許添加和刪除資料。

2.建立視圖： 
在建立視圖時，首先要確保擁有CREATE VIEW的許可權，並且同時確保對建立視圖所引用的表也具有相應的許可權。 
2.1 建立視圖的文法形式： 
    雖然視圖可以被看成是一種虛擬表，但是其在物理上是不存在的，即資料庫管理系統沒有專門的位置為視圖儲存資料。根據視圖的概念發現其資料來源於查詢語句，因此建立視圖的文法為：

CREATE VIEW view_name AS 查詢語句
//說明：和建立表一樣，視圖名不能和表名、也不能和其他視圖名重名。視圖的功能實際就是封裝了複雜的查詢語句。

樣本：

use zhaojd_test;  //選擇一個自己建立的庫create table t_product( //建立表  id int primary key,  pname varchar(20),  price decimal(8,2)); insert into t_product values(1,'apple',6.5); //向表中插入資料insert into t_product values(2,'orange',3); //向表中插入資料create view view_product as select id,name from t_product; //建立視圖select * from view_product;

結果為：
+------+--------+
| id   | name   |
+------+--------+
| 1    | apple  |
| 2    | orange |
+------+--------+
//其實在建立視圖時實際代碼裡寫的是一個表查詢語句，只不過把這個查詢語句封裝起來重新起了一個名字，方便可以重複使用。
//再者，安全性方面可以隱藏一些不希望看到的欄位，比如這裡的價格欄位。
//注意：在SQL語句的命名規範中，視圖一般以view_xxx或者v_xxx的樣式來命名。視圖的查詢語句和表的查詢語句相同。

2.2 建立各種視圖： 
    由於視圖的功能實際上時封裝查詢語句，那麼是不是任何形式的查詢語句都可以封裝在視圖中呢？ 

2.2.1 封裝實現查詢常量語句的視圖（常量視圖）：

樣本：

mysql> create view view_test1 as select 3.1415926;Query OK, 0 rows affected (0.07 sec)mysql> select * from view_test1;+-----------+| 3.1415926 |+-----------+| 3.1415926 |+-----------+1 row in set (0.00 sec)

2.2.2 封裝使用彙總函式（SUM、MIN、MAX、COUNT等）查詢語句的視圖：

樣本：
首先準備需要用到的兩張表及其初始化資料;

CREATE TABLE t_group(  id INT PRIMARY KEY AUTO_INCREMENT,  NAME VARCHAR(20));CREATE TABLE t_student(  id INT PRIMARY KEY AUTO_INCREMENT,  NAME VARCHAR(20),  sex CHAR(1),  group_id INT,  FOREIGN KEY (group_id) REFERENCES t_group (id));//t_group表中插入資料INSERT INTO t_group (NAME) VALUES('group_1');INSERT INTO t_group (NAME) VALUES('group_2');INSERT INTO t_group (NAME) VALUES('group_3');INSERT INTO t_group (NAME) VALUES('group_4');INSERT INTO t_group (NAME) VALUES('group_5');//t_student表中插入資料INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_1','M',1);INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_2','M',1);INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_3','M',2);INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_4','W',2);INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_5','W',2);INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_6','W',2);INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_7','M',3);INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_8','W',4);INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_9','W',4);================================================================mysql> create view view_test2 as select count(name) from t_student;Query OK, 0 rows affected (0.71 sec)mysql> select * from view_test2;+-------------+| count(name) |+-------------+|      9 |+-------------+1 row in set (0.01 sec)

2.2.3 封裝了實現排序功能（ORDER BY）查詢語句的視圖：

樣本：

mysql> create view view_test3 as select name from t_student order by id desc;Query OK, 0 rows affected (0.06 sec)mysql> select * from view_test3;+-------+| name |+-------+| zjd_9 || zjd_8 || zjd_7 || zjd_6 || zjd_5 || zjd_4 || zjd_3 || zjd_2 || zjd_1 |+-------+9 rows in set (0.00 sec)

2.2.4 封裝了實現表內串連查詢語句的視圖：

樣本：（第二組學生的姓名）

mysql> create view view_test5 as select s.name from t_student s,t_group g where s.group_id=g.id and g.id=2;Query OK, 0 rows affected (0.07 sec)mysql> select * from view_test5;+-------+| name |+-------+| zjd_3 || zjd_4 || zjd_5 || zjd_6 |+-------+4 rows in set (0.00 sec)

2.2.5 封裝了實現表外串連（LEFT JOIN和RIGHT JOIN）查詢語句的視圖：

樣本：（第二組學生姓名）

mysql> create view view_test6 as select s.name from t_student s left join t_group g on s.group_id=g.id where g.id=2;Query OK, 0 rows affected (0.09 sec)mysql> select * from view_test6;+-------+| name |+-------+| zjd_3 || zjd_4 || zjd_5 || zjd_6 |+-------+4 rows in set (0.01 sec)

2.2.6 封裝了實現子查詢相關查詢語句的視圖：

樣本：

mysql> create view view_test7 as select s.name from t_student s where s.id in(select id from t_group);Query OK, 0 rows affected (0.08 sec)mysql> select * from view_test7;+-------+| name |+-------+| zjd_1 || zjd_2 || zjd_3 || zjd_4 || zjd_5 |+-------+5 rows in set (0.00 sec)

2.2.7 封裝了實現記錄聯合（UNION和UNION ALL）查詢語句的視圖：

mysql> create view view_test8 as select id,name from t_student union all select id,name from t_group;Query OK, 0 rows affected (0.08 sec)mysql> select * from view_test8;+----+---------+| id | name  |+----+---------+| 1 | zjd_1  || 2 | zjd_2  || 3 | zjd_3  || 4 | zjd_4  || 5 | zjd_5  || 6 | zjd_6  || 7 | zjd_7  || 8 | zjd_8  || 9 | zjd_9  || 1 | group_1 || 2 | group_2 || 3 | group_3 || 4 | group_4 || 5 | group_5 |+----+---------+14 rows in set (0.01 sec)

3.查看視圖： 
3.1 SHOW TABLES語句查看視圖名： 
執行SHOW TABLES 語句時不僅可以顯示表的名字，同時也是顯示出視圖的名字。

樣本：

mysql> show tables;+------------------+| Tables_in_zhaojd |+------------------+| t_group     || t_product    || t_student    || v_product    || view_test1    || view_test2    || view_test3    || view_test4    || view_test5    || view_test6    || view_test8    |+------------------+11 rows in set (0.00 sec)

 3.2 SHOW TABLE STATUS語句查看視圖詳細資料： 

和SHOW TABLES語句一樣，SHOW TABLE STATUS語句不僅會顯示表的詳細資料，同時也會顯示視圖的詳細資料。

文法如下：
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
//參數db_name用來設定資料庫，SHOW TABLES STATUS表示將顯示所設定庫的表和視圖的詳細資料。
//設定LIKE關鍵字，可以查看某一個具體表或視圖的詳細資料。例如： SHOW TABLE STATUS FROM zhaojd LIKE 't_group' \G
樣本：

mysql> show table status from zhaojd \G*************************** 1. row ***************************      Name: t_group     Engine: InnoDB    Version: 10   Row_format: Compact      Rows: 5 Avg_row_length: 3276  Data_length: 16384Max_data_length: 0  Index_length: 0   Data_free: 7340032 Auto_increment: 6  Create_time: 2016-08-19 16:26:06  Update_time: NULL   Check_time: NULL   Collation: utf8_general_ci    Checksum: NULL Create_options:    Comment:=============================================================       Name: view_test8     Engine: NULL    Version: NULL   Row_format: NULL      Rows: NULL Avg_row_length: NULL  Data_length: NULLMax_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

3.3 SHOW CREATE VIEW語句查看視圖定義資訊：

文法為：
 SHOW CREATE VIEW viewname;
 樣本：

 mysql> show create view view_test8 \G*************************** 1. row ***************************View: view_test8Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test8` AS select `t_student`.`id` AS `id`,`t_student`.`NAME` AS `name` from `t_student` union all select `t_group`.`id` AS `id`,`t_group`.`NAME` AS `name` from `t_group`character_set_client: utf8collation_connection: utf8_general_ci1 row in set (0.00 sec)//根據執行結果可以發現，SHOW CREATE VIEW語句返回兩個欄位，分別為表示視圖名的View欄位和關於視圖定義的Create view欄位。

3.4 DESCRIBE | DESC 語句查看視圖定義資訊：

文法為：
 DESCRIBE | DESC viewname;
樣本：

mysql> desc view_test8;+-------+-------------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id  | int(11)   | NO  |   | 0    |    || name | varchar(20) | YES |   | NULL  |    |+-------+-------------+------+-----+---------+-------+2 rows in set (0.02 sec) 

3.5 通過系統資料表查看視圖資訊： 
當MySQL安裝成功後，會自動建立系統資料庫infomation_schema。在該資料庫中存在一個包含視圖資訊的表格，可以通過查看錶格views來查看所有視圖的相關資訊。

樣本：

mysql> use information_schema;Database changedmysql> select * from views where table_name='view_test8' \G*************************** 1. row ***************************    TABLE_CATALOG: def    TABLE_SCHEMA: zhaojd     TABLE_NAME: view_test8   VIEW_DEFINITION: select `zhaojd`.`t_student`.`id`AS`id`,`zhaojd`.`t_student`.`NAME` AS `name` from `zhaojd`.`t_student` union all select `zhaojd`.`t_group`.`id` AS`id`,`zhaojd`.`t_group`.`NAME` AS `name` from `zhaojd`.`t_group`    CHECK_OPTION: NONE    IS_UPDATABLE: NO       DEFINER: root@localhost    SECURITY_TYPE: DEFINERCHARACTER_SET_CLIENT: utf8COLLATION_CONNECTION: utf8_general_ci1 row in set (0.01 sec)

4.刪除視圖： 
在刪除視圖時首先要確保擁有刪除視圖的許可權。

文法為：
DROP VIEW view_name [,view_name] ......
//從文法可以看出，DROP VIEW一次可以刪除多個視圖
樣本：

mysql> use zhaojd;Database changedmysql> show tables;+------------------+| Tables_in_zhaojd |+------------------+| t_group     || t_product    || t_student    || v_product    || view_test1    || view_test2    || view_test3    || view_test4    || view_test5    || view_test6    || view_test8    |+------------------+11 rows in set (0.00 sec)mysql> drop view view_test1, view_test2;Query OK, 0 rows affected (0.01 sec)mysql> show tables;+------------------+| Tables_in_zhaojd |+------------------+| t_group     || t_product    || t_student    || v_product    || view_test3    || view_test4    || view_test5    || view_test6    || view_test8    |+------------------+9 rows in set (0.00 sec)

5.修改視圖： 
5.1 CREATE OR REPLACE VIEW語句修改視圖： 
對於已經建立好的表，尤其是已經有大量資料的表，通過先刪除，然後再按照新的表定義重建立表的方式來修改表，需要做很多額外的工作，例如資料的重載等。可是對於視圖來說，由於是“虛表”，並沒有儲存資料，所以完全可以通過該方式來修改視圖。

實現思路就是：先刪除同名的視圖，然後再根據新的需求建立新的視圖即可。

DROP VIEW view_name; CREATE VIEW view_name as 查詢語句;

但是如果每次修改視圖，都是先刪除視圖，然後再次建立一個同名的視圖，則顯得非常麻煩。於是MySQL提供了更方便的實現替換的建立視圖的文法，完整文法為：

CREATE OR REPLACE VIEW view_name as 查詢語句；

 5.2 ALTER語句修改視圖：

文法為：

ALTER VIEW view_name as 查詢語句;

6.利用視圖操作基本表： 
再MySQL中可以通常視圖檢索基本表資料，這是視圖最基本的應用，除此之後還可以通過視圖修改基本表中的資料。 
6.1檢索（查詢）資料： 
    通過視圖查詢資料，與通過表進行查詢完全相同，只不過通過視圖查詢表更安全，更簡單實用。只需要把表名換成視圖名即可。 
6.2利用視圖操作基本表資料： 
    由於視圖是“虛表”，所以對視圖資料進行的更新操作，實際上是對其基本表資料進行的更新操作。在具體更新視圖資料時，需要注意以下兩點;
    1. 對視圖資料進行添加、刪除直接影響基本表。
    2. 視圖來自於多個基本表時，不允許添加、刪除資料。
    視圖中的添加資料操作、刪除資料操作、更新資料操作的文法同表完全相同。只是將表名換成視圖名即可。

以上就是本文的全部內容，希望對大家的學習有所協助，也希望大家多多支援雲棲社區。