MySQL view (view)

One, what is a view

A view is an interface that holds 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 the user's own defined data. 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.

Second, what is the use of the view

1, the view can make the query become very clear

If the data you are looking for is stored in three relational tables, you will have to write a federated query when you view the data. In other ways, I put the data of the Union query into the view, so it is not more convenient to query, phpMyAdmin see is not more clear.

2, protect the database of important data, to different people to see different data

If you let others help you develop a system, but you want to expose the real table, this time the view is not the best choice.

Third, the type of the view

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

1,merge, the text of the statement referencing the view is merged with the view definition so that some part of the view definition supersedes the corresponding part of the statement.

2,temptable, the result of the view is placed in the staging table and then used to execute the statement.

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 a temporary table is used, the view is not updatable.

Four, add view

1, adding rules

1Reate[OR REPLACE] [algorithm = {UNDEFINED | MERGE | TempTable}]  2   3  VIEWView_name[(column_list)]  4   5   asselect_statement6   7  [With [cascaded | LOCAL] CHECK OPTION]

2, example

MySQL> use test;   Database changed  mysql>Create  = View  as Select *  from user; // Create a View   0 rows affected (0.00 sec)  

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

1 [[email protected] test]# Cat/Usr/Local/Mysql/Data/Test/aaa.frm2TYPE=VIEW  3Query=Select' Test '. 'User'. ' ID ' as' id ', ' test '. 'User'. ' Name ' as' Name ', ' Test '. 'User'. ' Sex ' as' Sex ' from' Test '. 'User`  4Md5=04d5ab2cc3ffcf3376a5e9c946f858ab5Updatable=1  6Algorithm=2  7Definer_user=  8Definer_host=  9Suid=2  TenWith_check_option=0   OneRevision=1   A timestamp= .-Ten- -  +: -: the   - Create-Version=1   -Source=Select *  from User   theClient_cs_name=UTF8 -Connection_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`

Create the view that he did. MYD,. Myi These two files, one for storing data, and one for storing the index. Here you can explain that the data exists in the basic table.

Five, modifying the view

1, modify the rules

 1  alter  [ algorithm = {UNDEFINED | MERGE | temptable}    2  3  view  view_name [  (column_list)    4  5  as   select_statement  6  7  [ with [cascaded | LOCAL    check  option ] 

2, example

1 mysql>alterviewasselect* from Userwhere<>any  2   -(Select   from comment);   3 0 rows affected (0.00 sec)

Six, view, delete view

1Mysql> Select *  fromaaa//Viewing View data2 +----+------+-----+  3 |Id|Name|Sex|  4 +----+------+-----+  5 |  3 |Tank|   0 |  6 |  4 |Tank|   0 |  7 +----+------+-----+  8 2Rowsinch Set(0.00sec)9   TenMysql>ShowCreate ViewAaa\g;//View View One *************************** 1. Row***************************   A  View: AAA -  Create View:CREATEAlgorithm=MERGE Definer=' @ ' SQL SECURITY definerVIEW' AAA ' as Select`User'. ' ID ' as' ID ', 'User'. ' Name ' as' Name ', 'User'. ' Sex ' as' Sex ' from`User`where`User'. ' ID '<>  any(Select' comment '. ' u_id ' as' u_id ' from' comment ') - Character_set_client:utf8 the Collation_connection:utf8_general_ci - 1Rowinch Set(0.00sec) -    -Mysql> Drop Viewaaa//Delete a view +Query OK,0Rows Affected (0.00sec) -

MySQL view (view)