MySQL---View

A view is a virtual table that does not actually store data and stores a "functional relationship."

Grammar:

CREATE VIEW name as SELECT statement;

Why does a view store a function relationship?

When referencing a view, it looks for related fields based on the tables that relate to creating the view, and the results are calculated according to this relationship. So the view does not need to store the data, only need to store this "function relationship".

Table 1 is a table that records the length of a rectangle

Length (cm)

10

Table 2 is a table that records the width of a rectangle

Width (cm)

20

Now create a view to calculate the rectangular area

Area

Length * Width

when referencing a view, the view takes the data from the function relation "area = length * width" to table 1 and table 2 to get the desired result. The view does not actually store the area of each rectangle. The above table design is not rigorous just to illustrate the problem.

Does changing the table change the view?

Because it is a function relation f (x, y), when x or Y has a change, the value of f (x, Y) is bound to change. X and Y correspond to the tables that create the view, andf (x, Y) corresponds to the view.

Create a Student score table

Each field represents "Student ID", "Math Score", "Language score", "English score".

Now create a view of average scores for each student

Make changes to the table to see if the view will change along with it.

And look at the contents of the view

The view does change, so changes to the table will definitely affect the view.

Does the change of view change the table?

This is not necessarily true, if the view and table are in a corresponding, that is, f (x, y) can be reversed X, Y (similar to inverse function relationship ). In this case, the view changes affect the existing table.

For the view of average achievement, it is impossible for the average score to get the results of each section, so they are not one by one correspondence, in which case MySQL is not allowed to change the view .

Views are implemented in three ways

Merge: When a view is referenced, the criteria that are referenced are merged with the conditions when the view was created. Make a new query statement to query the tables.

Take the previous average view as an example, the red Dash is the query statement that creates the view

Students who now require an average of three heights can refer to the view directly to query

if algorithm set to Merge

The actual operation is to combine these two query conditions with the equivalent

temptable: when referencing a view, the tables are queried according to the conditions in which the view was created, the results are generated as temporary tables, and the temporary tables are queried.

undefined: database default undefined , It is up to the database to use merge or temptable depending on the situation.

The role of the view

(1) Convenient operation: If you want to use a temporary table frequently, such as the above average split view. If you do not define a view, you need to calculate the average temporary table each time, and then query the temporary table. It is convenient to have a view, and it is possible to query the view directly.

(2) Security considerations: If two companies cooperate , you need to open up some user information for both parties, but do not want to publish some information that involves privacy or confidentiality (such as a user password), you can generate a view that does not contain a password field to each other. The other side directly uses the view to meet the requirements.