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 various 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, just need to store such "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 a view is required, the view takes data from the function relation "area = length * width" to table 1 and table 2 to obtain the desired result. In fact, the view does not store the area of each rectangle.

The above table design is not rigorous only 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", respectively. "Mathematical Achievement", "Language achievement". "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 the changes in the table will definitely affect the view.

Does the change of view change the table?

That's not necessarily. Suppose that the view and the table are in a corresponding, that is, f (x, y) can be reversed X, Y (similar to inverse function relationship ).

Such a case. Changes to the view can affect the existing table.

For the average score view, the average score can not be obtained from the results of each section, so they are not one by one corresponding relationship, such a situation MySQL does not agree to change the view .

Views are implemented in three ways

Merge: when referencing a view. Merges the conditions of the reference 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. Red Dash is the query statement that creates the view

Today, students who require an average of three high levels can refer directly to the view to query

Assumptions 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 they were 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 decide whether to use merge or temptable.

The role of the view

(1) Convenient operation: Suppose to be used frequently to a temporary table, such as the above average split view. Assuming no view is defined, each time the average is required to divide the temporary table. Then go to the temporary table to query. It is very convenient to have a view. Directly querying the view is possible.

(2) Security considerations: Assume that two companies cooperate, need to open up some user information, but do not want to publish some privacy or confidential information (such as user password). Ability to generate views that do not include the password field to each other. The other side directly uses the view to meet the requirements.

MySQL---View