mysql--View

Use the meaning of a view

The query on the table is the same as the query statement on the view. However, views are a more secure and flexible way.

　　Security : You can bind user rights to Views (grant statements can grant permissions on the view).

　　Flexible : You can support partial queries on large table C based on the creation of A and B views on table C. Instead of having to build a table A and B separately.

Create a View

　　A view is a reference to the original real table. We can manipulate the real table through the view. A view is a table, a virtual table! so there may be duplicate names when creating the view.

　　To see an example, create a table ' result ':

CREATE TABLE' Result ' (' math_no ')INT(Ten) not NULLunsigned auto_incrementPRIMARY KEY, ' Teamno 'INT(Ten) not NULL, ' Playerno 'INT(Ten) not NULL, ' WON 'VARCHAR(Ten) not NULL, ' LOST ' Varcahr (Ten) not NULL, ' Captain 'INT(Ten) not NULL COMMIT 'just another name for Playerno.', ' Division 'VARCHAR(Ten) not NULL) ENGINE=MYISAMDEFAULTCHARSET=Utf8COMMIT='new table for re-grouping'Auto_increment=1

Engine=myisam or InnoDB This is the storage engine for the specified database, the main differences are InnoDB support transactions, MYISAM not supported but faster.

Create a view on the table result

CREATE VIEW  as SELECT DISTINCT  from result

Update the real table by updating the view

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.

View Internal Management

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, for example:

Statement: SELECT * from teams

1. Replacement method: Select *from (select DISTINCT Teamno, captain, division from result) and then handed to MySQL for processing

2. materialization: MySQL Gets the result of the view execution first, the star gives an intermediate result in memory, and then the outside select invokes the intermediate result.

mysql--View