MySQL Advanced Seven - Use of Views

1. Conditions for using views

If a certain query result appears very frequently, and this query result needs to be often used as a subquery, it will be more convenient to use the view.

2. Benefits of using views

a. Simplified query statements

b. Permission control can be performed

Close the permissions of the table, but open it accordingly View permissions, only some data columns are open in the view.

c. When a large data table is divided into tables, for example, if a table has 1 million pieces of data, the table can be divided into four views.

Calculate based on the remainder of the id

3. Create the view

create or replace view v_test as select * from user;

Add OR REPLACE to indicate that the statement can also replace the existing view

4. Call the view

select * from v_test;

5. Modify the view

alter view v_test as select * from user1;

6. Delete the view

drop view if exists v_test;

7. View view

show tables;

The view is placed in the views table under the information_schema database

8. View view definition

show table status from companys like 'v_test';

9. View algorithm - there are two execution algorithms

a. Merge: Merge execution method. Whenever executed, first merge the sql of our view The statement is mixed with the SQL statement of the external query view and finally executed.

b. Temptable: Temporary table mode. Whenever a query is made, the select statement used in the view generates a temporary table of results, and then the query is performed in the current temporary table.

The above is the content of MySQL Advanced Seven - the use of views. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!