Summary of mysql View Principle and Usage Examples

This paper summarizes the principle and usage of mysql view with examples. Share it for your reference, as follows:

1. What is a view

A view is a view in a computer database and is a virtual table whose contents are defined by a query. Like the real Table 1, the view contains 1 series of named column and row data. However, views do not exist in the database as stored sets of data values. Row and column data comes from the table referenced by the query that defines the view and is generated dynamically when the view is referenced. Simply put, a view is a table composed of its defined results;

Examples: class table class (ID, name), student table student (id, class_id, name);

When the data table structure is complex, but we only care about one part of the data, we can use views to define the data we care about

Create a view:

create view v_stu as select  c.name as c_name ,s.name as stu_name from student  s,class  c where c.id = s.class_id

Query View v_stu

select * from v_stu

c_name	stu_name
wu	1班
li	2班

2. Why use views

1. Security. 1 generally does this: Create a view and define the data that the view operates on. The user rights are then bound to the view. This way is to use the

One feature: grant statement can grant permissions on views.

2. Query performance is improved.

3. After flexible functional requirements, it is necessary to change the structure of the table, resulting in a large workload. Then you can use the form of virtual table to achieve the effect of less modification.

This is useful in actual development

Example: If, for some reason, the a and b tables need to be merged to form a new table, c. Finally, neither a table nor b table will exist. Because the original program is based on a table and b table query, which means that a large number of sql needs to be rewritten (changed to c table to operate data). And through the view, you can do it without modification. Define the two view names as well as the original table names a and b. The a, b views complete fetching the contents from the c table.

Note: Using this solution, the more detailed you know about the details of the view, the better. Because there is no syntax difference between using views and using tables. For example, the view name a, then the query is still " select * from a ".

4. Complex query requirements. Problem decomposition can be performed, and then multiple views will be created to obtain data. Combine the views to get the desired results.

The working mechanism of the view: When the view is called, the sql in the view will be executed to fetch the data. The contents of the view are not stored, but data is derived when the view is referenced. This does not take up space, because it is an immediate reference, the contents of the view are always identical to the contents of the real table. What are the benefits of designing the view like this? If space is saved and the content is always 1, then we don't need to maintain the content of the view and maintain the content of the real table to ensure the integrity of the view.

3. How the view is executed

There is no real data saved in the view, but only the data in the real table is referenced, and the referenced data has its definition decision. When we query the view, we actually take the data from the definition;

infomation_schema will have a database in MySQL, which is a database of mysql and stores some metadata of mysql database. The so-called metadata refers to the table information of mysql. View information, column information, and so on. Simply speaking, it is equivalent to one directory of mysql database. All views have storage descriptions in the views table; From here we can see the working mechanism of mysql;

select * from v_stu;

v_stu can also be a table name. Because the physical structure of the view and the table is different, the real data can be found in the table, and the view needs to find the definition to get the required data. How do we know that v_stu is a view? Because there is a directory viewing routine doing this.

View mechanism:

There are two mechanisms for view processing, replacement and materialization.

① Alternative: When operating the view, the view name is directly replaced by the view definition, and the result becomes select * from (select c. name as c_name, s. name as stu_name from student s, class c where c. id = s.class_id), which is submitted to mysql for execution;

② Formula: mysql first gets the result of view execution, which forms an intermediate result temporarily stored in memory. After that, the external select statement calls these intermediate results (temporary tables).

It seems that they all want to get results, and there are differences in form, but they don't seem to realize the essential differences. What is the difference between the two ways?

Replacement mode, after replacing the view formula, treat it as a whole sql. The specific way is to deal with the view results first, and then deal with the external query requirements.

Alternative methods can be summarized as: prepare first, then execute.

The concrete way is summarized and understood as separate treatment.

Which way is better? I don't know. mysql will decide for itself which way to process. You can also specify which way to use when defining your view.

Examples:

Syntax:

CREATE [ALGORITHM]={UNDEFINED|MERGE|TEMPTABLE}]
VIEW  View name  [( Attribute list )]
AS SELECT  Statement 
[WITH [CASCADED|LOCAL] CHECK OPTION];

ALGORITHM has three parameters: merge, TEMPTABLE and UNDEFINED

merge: Alternative processing mode, which can update the data in the real table;

TEMPTABLE: Used, because the data is stored in a temporary table, so can not be updated!

When your parameter definition is UNDEFINED (no ALGORITHM parameter is defined). mysql is more inclined to choose alternative methods. Because it is more effective.

Examples:

create ALGORITHM=merge view v_stu as select c.name as c_name ,s.name as stu_name from student s,class c where c.id = s.class_id
update v_stu set c_name = '' where c_name ='';

Successful execution

create ALGORITHM=TEMPTABLEview v_stu as select c.name as c_name ,s.name as stu_name from student s,class c where c.id = s.class_id

Execution failed and cannot be updated!

More readers interested in MySQL can check out the topics on this site: "MySQL Query Skills Encyclopedia", "MySQL Transaction Operation Skills Encyclopedia", "MySQL Stored Procedure Skills Encyclopedia", "MySQL Database Lock Skills Encyclopedia" and "MySQL Common Functions Encyclopedia"

I hope this article is helpful to everyone's MySQL database.