Basic operation of MySQL view (v)

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 show to all people who can view the results of the query), MySQL provides the view features. A view is essentially a virtual table whose content is similar to a real table, containing a series of column and row data with names. However, views do not exist in the database as stored data values. Row and column data is derived from the base table referenced by the query that defines the view, and is generated dynamically when the view is used specifically.
The view has 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) that is produced by a base table (a real table).
3. The creation and deletion of views does not affect the base table.
4. Updates (additions, deletions, and modifications) to the contents of the view directly affect the base table.
5. Adding and deleting data is not allowed when the view is from more than one base table.

2. Create a view:
When you create a view, you first make sure that you have permission to create view, and that you also have the appropriate permissions for the table that the view is referenced in.
2.1 Create a syntax form for a view:
Although a view can be viewed as a virtual table, it is physically nonexistent, that is, the database management system does not have a dedicated location to store data for the view. Based on the concept of a view, it finds that its data originates from a query statement, so the syntax for creating a view is:

CREATE VIEW view_name as query statement
Description: As with creating a table, the view name cannot be the same as the table name or the name of the other view. The function of a view is actually to encapsulate a complex query statement.

Example:

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

The result is:
+------+--------+
| ID | name |
+------+--------+
| 1 | Apple |
| 2 | Orange |
+------+--------+
In fact, when creating a view, the actual code is written in a table query statement, but the query statement package to re-name, easy to reuse.
Furthermore, security can hide some fields that you do not want to see, such as the Price field here.
Note: In the naming conventions of SQL statements, views are typically 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 Creating various views:
Since the functionality of the view actually encapsulates the query statement, is it possible that any form of query statements can be encapsulated in the view?

2.2.1 Encapsulates a view that implements a query constant statement (a 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)

The 2.2.2 package uses the aggregate function (SUM, MIN, MAX, Count, and so on) to query the view of the statement:

Example:
First, prepare the two tables 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//t_group table inserts 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//t_student table inserts 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 a 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 query statements for in-table joins:

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 a view that implements a query statement for an out-of-table connection (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 joins 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 a 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 | 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 the 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    |+------------------+11 rows In Set (0.00 sec)

3.2 Show TABLE status statement view View details:

Like the show tables statement, the Show Table status statement shows not only the details of the table, but also the details of the view.

The syntax is as follows:
SHOW TABLE STATUS [from db_name] [like ' pattern ']
The parameter db_name is used to set up the database, and show TABLES status indicates the details of the tables and views of the set library are displayed.
Set the LIKE keyword to view detailed information for a specific table or view. 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: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 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_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)//depending on the execution results, the SHOW CREATE VIEW statement returns two fields, respectively, the View field that represents the views name and the CREATE for the view definition The View field.

3.4 DESCRIBE | The DESC statement views the view definition information:

The syntax is:
DESCRIBE | DESC viewname;
Example:

Mysql> desc view_test8;+-------+-------------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| ID  | int   | NO  |   | 0    | |    | name | varchar (20) | YES |   | NULL  |    

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

Example:

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: [email protected]    SECURITY_TYPE:DEFINERCHARACTER_SET_CLIENT:utf8COLLATION_ Connection:utf8_general_ci1 row in Set (0.01 sec)

4. Delete the view:
When you delete a view, first make sure that you have permission to delete the view.

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

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. Modify the View:
5.1 The CREATE OR REPLACE View statement modifies the view:
For tables that have already been created, especially those that already have a large amount of data, it is necessary to do a lot of extra work, such as overloading the data, by first deleting and then re-building the table in the new table definition. However, for a view, 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;

But if you modify the view every time, it is cumbersome to delete the view first and then create a view with the same name again. MySQL provides a more convenient syntax for creating a view that implements replacement, with the complete syntax:

CREATE OR REPLACE VIEW view_name as query statement;

5.2 Alter statement to modify the view:

The syntax is:

ALTER VIEW view_name as query statement;

6. Use the view to manipulate the base table:
In MySQL, you can normally retrieve basic table data from a view, which is the most basic application of the view, and in addition to that, you can modify the data in the base table through the view.
6.1 Search (query) data:
Querying data through a view is exactly the same as querying through a table, except that querying a table through a view is safer and more simple and practical. Just change the table name to the view name.
6.2 Using views to manipulate basic table data:
Because the view is a "virtual table," The update operation on the image data is actually an update to its underlying table data. The following two points need to be noted when updating view data specifically;
1. Adding and deleting view data directly affects the base table.
2. When the view comes from more than one base table, the data is not allowed to be added or deleted.
The syntax for adding data operations, deleting data operations, and updating data operations in a view is exactly the same as the table. Just replace the table name with the view name.

The above is the whole content of this article, I hope that everyone's learning has helped, but also hope that we support phpstudy.

Basic operation of MySQL view (v)