MySQL Learning notes-View Insights

A view is called a virtual table, and view is the result of a SQL query. It is easy to understand that view is the shadow or mirror of a table.

The effect of two view:

(1) Permission control: When you allow users to query only certain columns in a table, you can use the view to open a few columns and play the role of permission control.

(2) Simple query statement: When the query statement is quite complex, you can use the embedded query as a view, and then query, it seems easy to understand.

The relationship between the three table and the corresponding view

(1) When you modify the data in the table, the data in the view naturally changes.

(2) If each row in the view corresponds to one by one of the data in the physical table, modifying the view data affects the data in the table, and if not, the statement that modifies the view will have an error and cannot be executed. For example, a view has each student's name and the corresponding average score, and when the average score is modified, it cannot be mapped to the table because there are multiple accounts for that student, and there is no way to confirm which row of data has changed.

Four-View Query

(1) Simple query formed view, if the view query, will build view statement + View statement =>> merged into physical table statement, this algorithm is called merge (merge).

(2) If the view statement is more complex, it is difficult to merge the view statement, MySQL will first execute the view creation statement, the returned result set form the temporary table in memory, and then query according to the query view statement.

MySQL Learning notes-View Insights