[Turn]mysql View Learning Summary

Transferred from: http://www.cnblogs.com/wangtao_20/archive/2011/02/24/1964276.html

What is the rationale for using the view?
1. Security. This is typically done by creating a view that defines the data that the view operates on. The user rights are then bound to the view. Such a way is to use the

A feature: The GRANT statement can grant permissions on the view.
2. Improved query performance.

3. With the flexibility of functional requirements, the need to change the structure of the table resulting in a larger workload. You can use a virtual table in the form of a less modified effect.

This is useful in practical development.

Example: If, for some reason, A and B tables need to be merged together to form a new Table C. Both A and B will not exist at the end of the list. And because the original program was programmed

Writing SQL is based on a and B tables, which means rewriting a large number of SQL (to the C table to manipulate the data). And through the view you can do it without repairing

Change. Define two views first name or original table name A and B. A, B view complete the content from the C table.

Description: Using this workaround, the more detailed the view is, the better. Because there is no difference between using a view or using the syntax of a table. such as view name a
, then the query is still "select * from a".

4. Complex query requirements. You can perform a problem decomposition and then create multiple views to get the data. Combine the views together to get the results you need.



How the View works: When the view is invoked, the SQL in the view is executed and the data is taken. The contents of the view are not stored, but the data is derived when the view is referenced. This does not take up space, and because it is an instant reference, the contents of the view are always consistent with the contents of the real table.

What are the benefits of a view design? Save space, content is always consistent, then we do not need to maintain the content of the view, maintain the content of the real table, we can guarantee the integrity of the view.

Second, update the real table by updating the view implementation

See a lot of examples, update the view to update the real table. Reason, I understand this: The view does not save the content. Just reference data. Then, updating the view is actually manipulating the real table in a reference way.
With CHECK option: When an update is performed on a view, you need to check whether the updated value is still a condition that satisfies the definition of the views formula. The popular point is whether the updated results will still exist in the view. If the updated value is not in the view range, it is not allowed to update if you create a view without adding with CHECK option to update the data in the view, MySQL does not check for validity. Delete it and delete it. You will not see it in the view.


What is the actual meaning of using the validity check?


View Practice: Reorganize the requirements of a table
CREATE TABLE ' result ' (' Math_no ' INT () not NULL unsigned auto_increment PRIMARY KEY,
' Teamno ' INT (Ten) is not NULL,
' Playerno ' INT (Ten) is not NULL,
' WON ' VARCHAR () not NULL,
' LOST ' Varcahr (Ten) not NULL,
' Captain ' INT (Ten) not NULL COMMIT ' is the other name of Playerno ',
' Division ' VARCHAR (+) not NULL
Engine=myisam DEFAULT Charset=utf8 commit= ' re-set new table ' Auto_increment=1


Create a view for each table to save the data in:
CREATE VIEW Teams (teamno,playerno,division) as SELECT DISTINCT teamno,captain,division from result

Error: #1050-table ' teams ' already exists

Description, because the view is also a table, which is a virtual table. Cannot duplicate the existing table (view)

Next, delete the table teams, and then execute the code that created the view.


It's easier to understand the rules of use by seeing the view as something like a table. The following comparisons may make it easier for you to understand:

1. When using a view, it is the same as using the table syntax.
2. When creating a view, the name of the view if it is the same as the existing table, it will be an error, not allowed to create. A view is a special kind of table

3. When creating views, you can use CREATE VIEW teams (Teamno,playerno,division) to define the structure of the view chart.
4. In the phpMyAdmin. The view is listed in the table list on the left with the table. Only by the state "View:teams" on the right side can you know that the table is a view chart.



The internal management mechanism of the view in MySQL:

The records of the view are saved in a table called views in the INFORMATION_SCHEMA database. Information such as the definition code for a particular view and the database to which it belongs can see two working mechanisms for understanding the view:

Statement: SELECT * from teams

Summarize several knowledge points for the above statement
1. Confirmation is the process of the view: Teams can also be a table name. Because the table differs from the physical mechanism of the view. The view itself is not storing content. So, in the use of SQL
, how does MySQL know teams is a view or a table. It is because there is a routine that looks at the directory that is doing the job.

2.mysql two ways to work with views: alternative and materialized.
The replacement method is understood, and the view name is replaced directly with the view's formula. For the above view Teams,mysql is replaced with the formula for the view, and the view formulas are merged into select. The result is the following SQL statement:
SELECT * FROM (select DISTINCT teamno,captain,division from result). That is, the SQL statement that was last submitted to MySQL for processing.


Materialized, MySQL first gets the result of the view execution, which forms an intermediate result that exists in memory temporarily. After that, the outside SELECT statement is adjusted

These intermediate results (temporary tables) are used.


Seems to be to get results, formal differences, as if not to appreciate the difference in nature. What is the difference between the two ways?

Replace the view formula and treat it as a whole SQL. Materialized, processing the view results first, and then processing the outside query requirements.
The replacement method can be summarized as, first prepared, then executed.
The embodiment is summed up and understood as, separate processing.

Which way is good? I don't know. MySQL will decide for itself which method to use for processing. You can specify the way you use it when you define the view. Like this

Use:

CREATE algorithm=merge VIEW Teams as SELECT DISTINCT teamno,captain,division from result

Algorithm has three parameters: merge, temptable, UNDEFINED

Look at the MySQL manual, which mentions the different ways in which the substitution and materialization are applicable, as you can understand:
Because the data in the staging table is not updatable. Therefore, if the use parameter is temptable, the update cannot be made.
When your parameter definition is undefined (no algorithm parameter defined). MySQL is more inclined to choose Merge mode. Because it's more effective.

[Turn]mysql View Learning Summary