Basic MySQL view operations (5): Basic mysql view operations

Basic MySQL view operations (5): Basic mysql view operations

1. Why View: 
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 anyone who can view the query results), MySQL provides the view feature. A view is essentially a virtual table. Its content is similar to a real table and contains a series of columns and row data with names. However, a view does not exist in the form of storing data values in the database. The data of rows and columns comes from the basic table referenced by the query in the definition view, and is dynamically generated when the view is used.
The view has the following features;
1. View columns can come from different tables and are new relationships established in the abstract and logical sense of tables.
2. A view is a table (virtual table) generated by a basic table (real table ).
3. Creating and deleting a view does not affect the basic table.
4. Updating (adding, deleting, and modifying) View content directly affects basic tables.
5. When a view comes from multiple basic tables, data cannot be added or deleted.

2. Create a view:
When creating a VIEW, you must first ensure that you have the create view permission and have the corresponding permissions for the table referenced by the created VIEW.
2.1 syntax form for creating 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. According to the concept of view, it is found that its data comes from the query statement. Therefore, the syntax for creating a view is:

Create view view_name AS query statement
// Description: Like creating a table, the view name cannot be the same as the table name or the name of another view. The view function encapsulates complex query statements.

Example:

Use zhaojd_test; // select a self-created database, create table t_product (// create table 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) into the table ); // insert data into the table: create view view_product as select id, name from t_product; // create view select * from view_product;

Result:
+ ------ + -------- +
| Id | name |
+ ------ + -------- +
| 1 | apple |
| 2 | orange |
+ ------ + -------- +
// In fact, when creating a view, a table query statement is written in the actual code, but the query statement is encapsulated and a new name is created, which is convenient for reuse.
// In addition, some fields that you do not want to see can be hidden in terms of security, such as the price field here.
// Note: In the naming conventions of SQL statements, views are generally named in view_xxx or v_xxx. View query statements are the same as Table query statements.

2.2 create various views:
Because the view function encapsulates query statements, can any form of query statements be encapsulated in the view?

2.2.1 encapsulate the view for querying 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 the view of query statements Using Aggregate functions (SUM, MIN, MAX, COUNT, etc:

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 (20), sex CHAR (1 ), group_id INT, foreign key (group_id) REFERENCES t_group (id); // INSERT data INTO t_group (NAME) VALUES ('group _ 1') in the t_group table '); 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 into t_student (NAME, sex, group_id) INTO the t_student table) 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 for implementing the 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 for implementing the join query statement in the table:

Example: (name of the 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 the view for implementing the query statements for outer table JOIN (left join and right join:

Example: (Second Group Student name)

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 for 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 for implementing the 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 the View:
3.1 show tables statement view Name:
When executing the show tables statement, you can not only display the table name, but also the view name.

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 displays not only the TABLE details, but also the View Details.

Syntax:
Show table status [FROM db_name] [LIKE 'pattern']
// The db_name parameter is used to set the database. show tables status indicates that the detailed information about the TABLES and views of the set database is displayed.
// Set the LIKE keyword to view the details of 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 show create view statement:

Syntax:
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_clie Nt: utf8collation_connection: utf8_general_ci1 row in set (0.00 sec) // according to the execution results, we can find that the show create view statement returns two fields, they are the View Field indicating the view name and the Create View field about the view definition.

3.4 DESCRIBE | DESC statement:

Syntax:
DESCRIBE | DESC viewname;
Example:

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 view information through the system table:
After MySQL is successfully installed, the system database infomation_schema is automatically created. A table containing view information exists in the Database. You can view information of all views by viewing 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: root@localhost    SECURITY_TYPE: DEFINERCHARACTER_SET_CLIENT: utf8COLLATION_CONNECTION: utf8_general_ci1 row in set (0.01 sec)

4. Delete A View:
Before deleting a view, make sure that you have the permission to delete the view.

Syntax:
Drop view view_name [, view_name] ......
// The syntax shows that multiple views can be deleted at a time by dropping VIEW.
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 create or replace view statement modify VIEW:
For tables that have already been created, especially tables that already have a large amount of data, you can modify the table by deleting the table first and then re-creating the table according to the new table definition, you need to do a lot of extra work, such as data overloading. However, because the view is a "virtual table" and data is not stored, you can modify the view in this way.

The idea is to delete a view with the same name first, and then create a new view based on the new requirements.

Drop view view_name; create view view_name as query statement;

However, if you delete a view and create a view with the same name each time you modify the view, it is very troublesome. Therefore, MySQL provides a more convenient syntax for creating views. The complete syntax is:

Create or replace view view_name as query statement;

5.2 ALTER statement modification View:

Syntax:

Alter view view_name as query statement;

6. Operate the basic table using the View:
In MySQL, basic table data can be retrieved by views, which is the most basic application of views. In addition, data in basic tables can be modified by views.
6.1 search (query) data:
Querying data through views is exactly the same as querying through tables, but querying tables through views is safer and more practical. You only need to replace the table name with the view name.
6.2 operate basic table data using views:
Because the view is a "virtual table", the update operation on the View data is actually an update operation on its basic table data. When updating View data, pay attention to the following two points;
1. adding or deleting View data directly affects the basic table.
2. When a view comes from multiple basic tables, data cannot be added or deleted.
The syntax for adding, deleting, and updating data operations in a view is identical to that in a table. Replace the table name with the view name.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.