MySQL View learning tutorials and examples

A, what view

View is an interface for storing data, or it can be said to be a virtual table. This data can be from one or several basic tables (or views) of data. It can also be data that the user has defined from. In fact, the view does not store data, the data is placed in the basic table, the basic table in the data changes, the view of the data changes.

Two, what does the view use

1, the view can make the query very clear
if you're looking for data that is stored in three relational tables, you'll need to write a federated query when you view the data. In another way, I put the data of the joint query into the view, so the query is not more convenient, pass phpMyAdmin look is not more clear.

2, protect important data from the database, and give different people different data
If you ask someone to help you develop a system, but you want to expose the real table, this time the view is not the best choice.

Three, type of view

The view of MySQL has three types: MERGE, temptable, UNDEFINED. If there is no algorithm clause, the default algorithm is undefined (undefined). The algorithm affects how MySQL handles views. The

1,merge merges the text of the statement referencing the view with the view definition, so that a portion of the view definition replaces the corresponding part of the statement.
2,temptable, the result of the view is placed in a temporary table and then used to execute the statement. The
3,undefined,mysql will select the algorithm you want to use. If possible, it tends to merge rather than temptable, because the merge is usually more efficient, and if you use a temporary table, the view is not updatable.

Four, add view

1, add rule

The code is as follows	Copy Code
CREATE [OR REPLACE] [algorithm = {UNDEFINED | MERGE | TempTable}] VIEW view_name [(column_list)] As Select_statement [With [cascaded | Local] CHECK OPTION

2, Example

The code is as follows	Copy Code
mysql> use test; Database changed mysql> Create algorithm = MERGE View AAA as SELECT * from user;//creates a view Query OK, 0 rows Affected (0.00 sec)

As we said above, there is no real data in the view, what is his storage in the Data Warehouse? Look underneath.

The code is as follows

Copy Code

[root@blackghost test]# cat/usr/local/mysql/data/test/aaa.frm  type=view  query=select ' test '. ' user '. ' id ' as ' id ', ' Test '. ' user '. ' name ' as ' name ', ' Test '. ' user '. ' Sex ' as ' sex ' from ' test '. ' User '   md5= 04d5ab2cc3ffcf3376a5e9c946f858ab  updatable=1  algorithm=2  definer_user=  definer_host=  suid=2  with_check_option=0  revision=1  timestamp=2010-10-20 19:59:34  create-version=1  Source=select * from user  client_cs_name=utf8  Conne ction_cl_name=utf8_general_ci  View_body_utf8=select ' test '. ' user '. ' id ' as ' id ', ' test '. ' user '. ' Name ' as ' Name ', ' Test '. ' user '. ' Sex ' as ' sex ' from ' test '. ' User '

Created a view that he did not. MyD,. Myi These two files, one is to hold the data, one to store the index. Here it can be explained that the data is in the basic table.

Five, modify the view

1, modify the rules

The code is as follows	Copy Code
ALTER [algorithm = {UNDEFINED | MERGE | TempTable}] VIEW view_name [(column_list)] As Select_statement [With [cascaded | Local] CHECK OPTION

2, Example

The code is as follows	Copy Code
Mysql> ALTER VIEW AAA AS SELECT * from user where ID <> any -> (select u_id from comment); Query OK, 0 rows Affected (0.00 sec)

Six, view, delete view

The code is as follows

Copy Code

Mysql> SELECT * from AAA; Viewing view data +----+------+-----+ | ID | name | sex | +----+------+-----+ | 3 |   Tank | 0 | | 4 |   Tank | 0 | +----+------+-----+ 2 rows in Set (0.00 sec) Mysql> Show CREATE View Aaag; View View 1. Row *************************** View:aaa Create view:create algorithm=merge definer= ' @ ' SQL security definer View ' AAA ' as select ' user '. ' id ' as ' id ', ' user '. ' NA Me ' as ' name ', ' user '. ' Sex ' as ' sex ' from ' user ' where ' user '. ' ID ' <> any (select ' comment '. ' u_id ' as ' u_id ' from ' Co Mment ') Character_set_client:utf8 Collation_connection:utf8_general_ci 1 row in Set (0.00 sec) mysql> drop View AAA; Delete View Query OK, 0 rows Affected (0.00 sec)