Basic operation of MySQL View (v) _mysql

1. Why Use Views:
To improve the reusability of complex SQL statements and the security of table operations (for example, the Payroll field does not want to be shown to anyone who can view the query's results), MySQL provides view features. A view, which is essentially a virtual table, is similar in content to a real table, containing a series of named columns and row data. However, the view does not exist in the database in the form of a stored data value. Row and column data comes from the base table referenced by the query that defines the view, and is dynamically generated when the view is used specifically.
Views have the following characteristics;
1. The columns of the view can come from different tables, which are the new relationships established in the abstract and logical sense of the table.
2. A view is a table (a virtual table) produced by a basic table (a real table).
3. The establishment and deletion of the view does not affect the base table.
4. Updates to the content of the view (add, delete, and modify) directly affect the base table.
5. Adding and deleting data is not allowed when the view is from multiple base tables.

2. Create a view:
When you create a view, you first want to ensure that you have permission to create view, and also that you have appropriate permissions for the tables that are referenced by creating the view.
2.1 Syntax form to create a view:
Although a view can be viewed as a virtual table, it does not physically exist, that is, the database management system does not have a dedicated location to store data for the view. Depending on the concept of the view, its data is derived from the query statement, so the syntax for creating the view is:

CREATE VIEW view_name as query statement
//Note: The view name cannot be the same as the table name, nor the name of the other view, as you create the table. The function of a view is actually to encapsulate complex query statements.

Example:

Use Zhaojd_test;  Select a library created by yourself CREATE TABLE
t_product (//CREATE TABLE
  ID int primary KEY,
  pname varchar, price
  decimal (8,2)
); 
INSERT into t_product values (1, ' Apple ', 6.5); Inserting data into a table insert into
t_product values (2, ' Orange ', 3);//Insert data to table
CREATE view view_product as select Id,name from t _product; Create a View
select * from View_product;

The results are:
+------+--------+
| ID | name |
+------+--------+
| 1 | Apple |
| 2 | Orange |
+------+--------+
//In fact, the actual code in the creation of the view is a table query statement, but the query to encapsulate a new name, easy to reuse.
Furthermore, the security aspect can hide some fields that you don't want to see, such as the Price field here.
Note: In the naming convention for SQL statements, views are generally named in view_xxx or v_xxx styles. The query statement for the view is the same as the query statement for the table.

2.2 Create various views:
Since the view's functionality actually encapsulates the query statement, is it possible that any form of query statement can be encapsulated in the view?

2.2.1 Encapsulates 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 Encapsulates a view that uses aggregate functions (SUM, MIN, MAX, Count, and so on) to query statements:

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

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 (), Sex CHAR (1), group_id INT, FOREIGN
KEY (group_id) REFERENCES T_group (id));
Insert data into the T_group table 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 ');
Insert data into the T_student table 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 a view that implements the sort 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 a view that implements a query statement for a join in a table:

Example: (second group of Students ' names)

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 a view that implements query statements for Outer 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 a 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 a view that implements the record Union (Union and Union All) query statement:

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 | z Jd_5  |
| 6 | zjd_6  |
| 7 | zjd_7  |
| 8 | zjd_8  | |
9 | zjd_9  |
| 1 | gro up_1 |
| 2 | group_2 |
| 3 | Group_3 |
| 4 | Group_4 |
| 5 | Group_5 |
+----+---------+
rows in Set (0.01 sec)

3. View View:
3.1 Show Tables statement view name:
When you execute the show tables statement, you can display not only the name of the table, but also the name of the view.

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    |
+------------------+
rows in Set (0.00 sec)

3.2 Show TABLE status statement view View details:

As with the show tables statement, the shows table status statement not only displays the details of the table, but also displays the details of the view.

The syntax is as follows:
Show TABLE STATUS [from db_name] [like ' pattern ']
//Parameter db_name is used to set up the database, Show tables status represents the details of the tables and views of the set library.
Set the LIKE keyword to view the details of 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_len gth:3276 data_length:16384 max_data_length:0 index_length:0 data_free:7340032 auto_increment:6 : 2016-08-19 16:26:06 update_time:null check_time:null collation:utf8_general_ci checksum:null 
    Ions: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 The Show CREATE view statement to view the view definition information:

The syntax is:
Show CREATE VIEW viewname;
Example:

 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 ' and ' 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)
//According to the execution results, the show CREATE view statement returns two fields, respectively, the View field that represents the name of the views and the CREATE view field for the view definition.

3.4 DESCRIBE | DESC statement to view the view definition information:

The syntax is:
DESCRIBE | DESC viewname;
Example:

mysql> desc VIEW_TEST8;
+-------+-------------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID  | int (one)   | NO  |   | 0    |    |
| name | varchar (20) | YES |   | NULL  |    |
+-------+-------------+------+-----+---------+-------+
2 rows in Set (0.02 sec) 

3.5 View view information through system tables:
when the MySQL installation succeeds, the system database Infomation_schema is created automatically. There is a table in the database that contains view information, and you can view the information about all views by viewing the form views.

Example:

mysql> use Information_schema;
The Database changed
mysql> select * from the 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. Delete view:
When you delete a view, you first make sure you have permission to delete the view.

The syntax is:
DROP VIEW view_name [, view_name] ...
//From the syntax you can see that drop view can delete multiple views at once
Example:

 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 |
+------------------+ 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. Modify the View:
5.1 The CREATE OR REPLACE View statement to modify the view:
for tables that have already been created, especially those that already have a large amount of data, you need to do a lot of extra work, such as overloading the data, by deleting the table and then modifying it in a new table definition. For a view, however, because it is a "virtual table" and does not store data, it is entirely possible to modify the view in this way.

The idea is to delete a view with the same name before creating a new view based on the new requirements.

DROP VIEW view_name; 
CREATE VIEW view_name as query statement;

However, if you modify the view each time, it is cumbersome to delete the view first and then create a view with the same name again. So MySQL provides a more convenient implementation of the replacement of the syntax to create the view, the complete syntax is:

CREATE OR REPLACE VIEW view_name as query statement;

5.2 Alter statement modifies a view:

The syntax is:

ALTER VIEW view_name as query statement;

6. Use the View Operation Basic table:
in MySQL, you can usually retrieve the basic table data in the view, which is the most basic application of the view, and you can then modify the data in the base table through the view.
6.1 Retrieving (querying) data:
querying data through a view is exactly the same as querying through a table, except that it is more secure and more simple and practical through a view lookup table. Just change the table name to the view name.
6.2 Using the view to manipulate basic table data:
Because the view is a "virtual table," The update operation on the views data is actually an update of its basic table data. When you update the view data, you need to pay attention to the following two points;
1. Adding and deleting view data directly affects the basic table.
2. Adding and deleting data is not allowed when the view comes from multiple base tables.
The syntax for adding data operations, deleting data operations, and updating data operations in the view is identical to the table. Just replace the table name with the view name.

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.