Basic operations of MySQL views (5)_MySQL

1. Why use views:
In order to improve the reusability of complex SQL statements and the security of table operations (for example, the salary field does not want to be displayed to everyone who can view the query results), MySQL provides view features. The so-called view is essentially a virtual table whose content is similar to a real table and contains a series of named column and row data. However, views do not exist as stored data values ​​in the database. The row and column data come from the base table referenced by the query of the custom view and are dynamically generated when the view is used.
The view has the following characteristics;
​​ 1. The columns of the view can come from different tables, which is a new relationship established in the abstract and logical sense of the table.
​ ​ 2. A view is a table (virtual table) generated from a basic table (real table).
​ 3. The creation and deletion of views does not affect the basic tables.
​​ 4. Updates to view content (addition, deletion, and modification) directly affect the basic table.
5. When the view comes from multiple base tables, adding and deleting data is not allowed.

2. Create a view:
When creating a view, first make sure you have the CREATE VIEW permissions, and also make sure you have the corresponding permissions on the table referenced by the created view.
2.1 The syntax form of creating a view:
Although a view can be regarded as a virtual table, it does not exist physically, that is, the database management system does not have a special location to store data for the view. According to the concept of view, it is found that the data comes from the query statement, so the syntax for creating a view is:

CREATE VIEW view_name AS query statement
//Note: Just like creating a table, the view name cannot be the same as the table name or other view names. The function of the view is actually to encapsulate complex query statements.

Example:

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;

The result is:
+------+--------+
| id | name |
+------+--------+
| 1 | apple |
| 2 | orange |
+------+--------+
//In fact, when creating the view, what is actually written in the code is a table query statement, but the query statement is encapsulated and given a new name so that it can be reused.
//Furthermore, in terms of security, you can hide some fields you don't want to see, such as the price field here.
//Note: In the naming convention of SQL statements, views are generally named in the style of view_xxx or v_xxx. The query statement of the view is the same as the query statement of the table.

2.2 Create various views:
Since the function of the view actually encapsulates the query statement in real time, can any form of query statement be encapsulated in the view?

2.2.1 Encapsulate the view that implements query constant statements (constant view):

Example:

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 Encapsulate views that use aggregate functions (SUM, MIN, MAX, COUNT, etc.) query statements:

Example:
First prepare the two tables and their initialization data that need to be used;

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 Encapsulates the view that implements the sorting function (ORDER BY) query statement:

Example:

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 Encapsulates the view that implements the join query statement in the table:

Example: (Name of second group of students)

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 Encapsulates views that implement query statements for out-of-table joins (LEFT JOIN and RIGHT JOIN):

Example: (Second group of student names)

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 encapsulates the view that implements subquery related query statements:

Example:

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 Encapsulates the view that implements record union (UNION and UNION ALL) query statements:

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. View view:
3.1 SHOW TABLES statement to view view name:
When executing the SHOW TABLES statement, not only the name of the table can be displayed, but also the name of the view can be displayed.

Example:

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 statement to view view details:

Like the SHOW TABLES statement, the SHOW TABLE STATUS statement not only displays the detailed information of the table, but also displays the detailed information of the view.

The syntax is as follows:
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
//The parameter db_name is used to set the database. SHOW TABLES STATUS means that the detailed information of the tables and views of the set library will be displayed.
//Set the LIKE keyword to view detailed information about a specific table or view. For example: SHOW TABLE STATUS FROM zhaojd LIKE 't_group' G
Example:

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: 16384
Max_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: 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.3 SHOW CREATE VIEW语句查看视图定义信息：

语法为：
 SHOW CREATE VIEW viewname;
 示例：

 mysql> show create view view_test8 \G
*************************** 1. row ***************************
View: view_test8
Create 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: utf8
collation_connection: utf8_general_ci
1 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 changed
mysql> 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: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
1 row in set (0.01 sec)

4.删除视图： 
在删除视图时首先要确保拥有删除视图的权限。

语法为：
DROP VIEW view_name [,view_name] ......
//从语法可以看出，DROP VIEW一次可以删除多个视图
示例：

mysql> use zhaojd;
Database changed
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)
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. 视图来自于多个基本表时，不允许添加、删除数据。
    视图中的添加数据操作、删除数据操作、更新数据操作的语法同表完全相同。只是将表名换成视图名即可。

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。