MySql基礎知識之視圖的特點、創建、操作等

1.視圖簡介

為瞭提高復雜SQL語句的復用性和表操作的安全性,MySql資料庫管理系統提供瞭視圖特性。所謂視圖,本質上是一種虛擬表,其內容與真實的表相似,包含一系列帶有名稱的列和行數據。但是,視圖並不在資料庫中以存儲數據值的形式存在。行和列數據來自設定視圖的查詢所引用基本表,並且在具體引用時動態生成。

視圖使程式員隻關心感興趣的某些特定數據和他們所負責的特定任務。這樣我們隻能看到視圖中所設定的數據,而不是視圖所引用表中的全部數據,從而提高資料庫中數據的安全性。

視圖有以下幾個特點:

(1)視圖的列可以來自不同的表,是表的抽象和在邏輯意義上建立的新關系。

(2)視圖是由基本表(實表)產生的表(虛表)。

(3)視圖的建立和刪除不影響基本表。

(4)對視圖內容的更新(添加,刪除和修改)直接影響基本表。

(5)當視圖來自多個基本表時,不允許添加和刪除數據。

視圖的操作包括創建視圖,查看視圖,刪除視圖和修改視圖。

2. 創建視圖

具體講解之前,請執行以下兩條語句,創建一個名為t_product的表並插入四條數據

mysql> create table t_product(id INT,name VARCHAR(20),price FLOAT);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t_product values(1,'apple',6.5),(2,'banana',4.5),(3,'orange',1.5),(4,'pear',2.5);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc t_product;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| price | float       | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from t_product;
+------+--------+-------+
| id   | name   | price |
+------+--------+-------+
|    1 | apple  |   6.5 |
|    2 | banana |   4.5 |
|    3 | orange |   1.5 |
|    4 | pear   |   2.5 |
+------+--------+-------+
4 rows in set (0.00 sec)

2.1 創建視圖的語法形式

從視圖的概念可以發現其數據來源於查詢語句,因此創建視圖的語法為:

create view view_name as 查詢語句;

示例:創建出隱藏價格字段price的視圖view_selectproduct:

mysql> create view view_selectproduct as select id,name from t_product;
Query OK, 0 rows affected (0.02 sec)

視圖的查詢就像查詢表一樣簡單:

mysql> select * from view_selectproduct;
+------+--------+
| id   | name   |
+------+--------+
|    1 | apple  |
|    2 | banana |
|    3 | orange |
|    4 | pear   |
+------+--------+
4 rows in set (0.01 sec)

2.2 創建各種視圖

2.2.1 封裝實現查詢語句的視圖,即所謂的常量視圖

具體語句如下:

create view view_test1 as select 3.1415926;

2.2.2 封裝使用聚合函數查詢語句的視圖

聚合函數包括但不限於SUM,MIN,MAX,COUNT,具體語句如下:

create view view_test2 as select COUNT(name) from t_product;

2.2.3 封裝瞭實現排序功能(ORDER BY)查詢語句的視圖

具體語句如下:

create view view_test3 as select name from t_product order by id desc;

*desc表示降序,asc表示升序。

2.2.4 封裝瞭實現表內連接查詢語句的視圖

具體語句如下:

create view view_test4 as select s.name from t_student as s ,t_group as g where s.group_id=g.id and g.id=2; 

其中,t_student,t_group分別時學生表和分組表,學生表中有一個字段為_id,分組表中有一個字段為id,且都為INT型。

2.2.5 封裝瞭實現表外連接的查詢語句視圖

具體語句如下:

create view view_test5 as select s.name from t_student as s left join t_group as g on s.group_id=g.id where g.id=2;

 

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

具體語句如下:

create view view_test6 as select s.name from t_student as s where s.group_id in (select id from t_group);

2.2.7 封裝瞭實現記錄聯合(UNION和UNION ALL)查詢語句的視圖

具體語句如下:

create view view_test7 as select id, name from t_student union all select id,name from t_group;

2.3 查看視圖

2.3.1 查看視圖名:

具體語句:

show tables;

以上語句不僅會顯示當前資料庫中的數據包名,還會顯示視圖名。

2.3.2 語句查詢視圖的詳細信息

show table status from table_name \G

上述語句會列出當前資料庫中表和視圖的詳細信息,如下所示:

*************************** 2. row ***************************
           Name: t_product
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 4
 Avg_row_length: 4096
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2018-05-13 21:42:08
    Update_time: 2018-05-13 21:43:48
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 3. row ***************************
           Name: view_selectproduct
         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
3 rows in set (0.00 sec)

也可以使用show table查看指定視圖的詳細信息,具體語句如下:

show table status from database_name like 'view_name';

例如顯示視圖view_selectproduct的詳細信息:

mysql> show table status from canvas like 'view_selectproduct' \G
*************************** 1. row ***************************
           Name: view_selectproduct
         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)

2.3.3 show create view語句查看視圖設定信息

如果像查看視圖的設定信息,可以使用如下語句:

show create view view_name;

例如,查看視圖view_selectproduct視圖的設定信息:

mysql> show create view view_selectproduct \G
*************************** 1. row ***************************
                View: view_selectproduct
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_selectproduct` AS select `t_product`.`id` AS `id`,`t_product`.`name` AS `name` from `t_product`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

2.3.4 DESCRIBE|DESC語句查看視圖設計信息

如果想要查看視圖的設計信息,可以使用如下語句:

describe|desc view_name;

例如查看視圖view_selectproduct的設計信息:

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

2.4 刪除視圖

刪除視圖的語法形式如下:

drop view view_name1,view_name2...view_namen;

可以看出執行刪除操作可以同時刪除多個視圖,示例略。

2.5 修改視圖

2.5.1 CREATE OR REPLACE VIEW 語句修改視圖

MySql提供瞭可以實現替換的創建視圖語法,具體創建語法如下:

create or replace view view_name as 查詢語句;

示例,重新創建view_selectproduct視圖:

mysql> create or replace view view_selectproduct as select name from t_product;
Query OK, 0 rows affected (0.02 sec)

mysql> desc view_selectproduct;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

2.5.2 ALTER修改語句

和修改表一樣,alter語句也可以修改視圖,具體語法如下:

alter view view_name as 查詢語句;

例如,使用alter語句修改視圖view_selectproduct:

mysql> alter view view_selectproduct as select name,price from t_product;
Query OK, 0 rows affected (0.02 sec)

mysql> desc view_selectproduct;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| price | float       | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

2.6 利用視圖操作基本表

2.6.1 檢索(查詢)數據

通過視圖查詢數據與通過表查詢數據一樣,隻不過通過視圖查詢比表更安全,更簡單實用。在具體使用時隻需要把表名換成視圖名即可。

具體語法如下:

select * from view_name;

示例略。

2.6.2 利用視圖操作基本表數據

對於視圖的操作不僅限於查詢,還可以對視圖進行更新(增加,刪除和更新)數據操作。由於視圖時“虛表”,所以對視圖數據進行的更新操作,實際是對其基本表數據進行更新操作。在具體更新視圖數據時,需要註意以下兩點:

(1)對視圖數據進行添加,刪除會直接影響基本表。

(2)視圖來自於多個基本表時,不允許添加和刪除數據。

2.6.2.1 添加數據操作

具體語法形式如下:

insert into view_name(屬性名1,屬性名2...)values(數據1,數據2...);
或者
insert into view_name values(數據1,數據2...);

示例略。

2.6.2.2 刪除數據

語法形式如下:

delete from view_name where 條件語句;

示例略。

2.6.2.3 更新數據

語法形式如下:

update view_name set 新的屬性值 where 條件語句;

示例:通過更新view_selectproduct中apple的售價為10.5:

mysql> select * from view_selectproduct;
+--------+-------+
| name   | price |
+--------+-------+
| apple  |   6.5 |
| banana |   4.5 |
| orange |   1.5 |
| pear   |   2.5 |
+--------+-------+
4 rows in set (0.00 sec)

mysql> select * from t_product;
+------+--------+-------+
| id   | name   | price |
+------+--------+-------+
|    1 | apple  |   6.5 |
|    2 | banana |   4.5 |
|    3 | orange |   1.5 |
|    4 | pear   |   2.5 |
+------+--------+-------+
4 rows in set (0.00 sec)

mysql> update view_selectproduct set price=10.5 where name='apple';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from view_selectproduct;
+--------+-------+
| name   | price |
+--------+-------+
| apple  |  10.5 |
| banana |   4.5 |
| orange |   1.5 |
| pear   |   2.5 |
+--------+-------+
4 rows in set (0.00 sec)

mysql> select * from t_product;
+------+--------+-------+
| id   | name   | price |
+------+--------+-------+
|    1 | apple  |  10.5 |
|    2 | banana |   4.5 |
|    3 | orange |   1.5 |
|    4 | pear   |   2.5 |
+------+--------+-------+
4 rows in set (0.00 sec)

You May Also Like