Mysql --- View

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

Syntax:

Create view name as select statement;

Why does view store a function relationship?

When a view is referenced, it searches for related fields based on the tables related to the view creation and calculates the result based on the relationship. Therefore, the view does not need to store data, but only needs to store this "function relationship ".

For example, table 1 is a table that records the rectangular length.

Length (cm)

10

For example, table 2 records the rectangular width.

Width (cm)

20

Create a view to calculate the rectangular area

Area

Length * width

When you need to reference a view, the view will calculate the data in Table 1 and Table 2 based on the Function Relationship "Area = long * wide. In fact, the view does not store the area of each rectangle. The above table is not strictly designed to illustrate the problem.

Does a 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) will definitely change. X and y are equivalent to creating a view table, and f (x, y) is equivalent to a view.

Create a student renewal table

Each field represents "student ID", "Mathematical score", "Chinese score", and "English score ".

Now we create a view to calculate the average score of each student.

Make changes to the table to see if the view will change together.

Let's look at the view content.

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

Does a view change the table?

This is not necessarily the case. If a view corresponds to a table, that is, f (x, y) can reverse obtain x and y (similar to the inverse function relationship ). In this case, changes to the view will affect the original table.

For the view that calculates the average score, the average score cannot get the scores of each subject, so they are not a one-to-one correspondence. In this case, MysqlViews cannot be changed..

Three methods are available for view implementation.

Merge: When a view is referenced, the referenced conditions are merged with the conditions when the view is created. Create a new query statement to query each table.

Taking the previous average view as an example, the query statement for creating the view is marked with a red line.

Currently, students with the top three average scores can directly reference the view for query.

If algorithm is set to merge

The actual operation is to merge the two query conditions, which is equivalent

Temptable:When referencing a view, you first query each table based on the conditions for creating the view, generate a temporary table for the result, and then query the temporary table.

Undefined:Database undefined by default,The database determines whether to use merge or temptable based on the situation.

Role of a view

(1) convenient operation: If you need to use a temporary table frequently, such as the average view above. If no view is defined, you must divide the temporary table evenly each time before querying the temporary table. With a view, you can directly query the view.

(2) security considerations: if the two companies cooperate, they need to open some user information of both parties, but do not want to publish information related to privacy or confidentiality (such as user passwords ), you can generate a view that does not contain the password field to the other party. The other party can directly use the view to meet the requirements.