4 MySQL View

Directory:

1. Overview of views

1.1 Why a view is introduced

1.2 What is a view

1.3 Benefits of the view

1.4 Categories of views

2. Creation and deletion of views

3. Experiments

1.View Overview1.1Why introduce views[1]

Question: If it is because of some need,atable andbtables need to be merged together to form a new tableC. Finallyatable andbNone of the tables will exist. And since the original program was writtenSQLthe distinction is based onatable andbtable, which means that you need to rewrite a large number ofSQL (Change toCtable to manipulate the data).

Solution: Define two view names or the original table names a and b. a,b View complete the content from the C table. You can do this without modification through the view.

1.2What is a view[2]

A view is a table that is presented from one or more tables and is a virtual existence. A view is like a window through which you can see the data that is specifically provided by the system. This allows users to take care of data that is useful to them without seeing the data in the entire database. The database contains only the definition of the view, not the data in the view, which is stored in the original table. When querying data using a view, the database system extracts the corresponding data from the original table. The data in the view depends on the data in the original table, and the data displayed in the view changes as soon as the data in the table changes.

1.3Benefits of the view[3]

(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. The way to do this is to use a feature: The grant statement can grant permissions on the view.
(2) improved query performance.
(3) responding to 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.

(4) flexible response to changes in requirements.
With the flexibility of functional requirements, it is necessary 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. For example, the following question case.

1.4Classification of Views[4]

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 a part of the view definition supersedes the corresponding part of the statement.

(2) TempTable, the result of the view will be placed in the staging table and then executed with the statement.

(3) UNDEFINED,MySQL will select the algorithm 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.

2.creation and deletion of views[5]

MySQL creates a view with the CREATE VIEW implementation syntax:

Create [algorithm={undifined|merge|temptable}] View View_name [(properties)] as SELECT * [WITH [cascaded| LOCAL] Check option];

Analytical:

Algorithm is an optional parameter that represents the algorithm of the view selection,undefined represents the MySQL automatic selection algorithm, themerge represents the use of a view's statement to merge with the view definition, which replaces the corresponding part of the statement with the definition of the view. temptable represents the structure of a view saved to a staging table, and then executes the statement using a temporary table.

View_name represents the name of the view to be created.

Properties is an optional parameter that specifies a noun for each property in the view, which is the same as the query in select .

Select is a complete query statement that queries from a table for certain records that meet the criteria and imports them into the view.

With CHECK option is an optional parameter that indicates that the update view is to be guaranteed within the view's permission range.

See the experiment section for changes to the view.

3.Experiment

(1) Create a table, insert data

Create TableBook (IDint(Ten) not NULLAuto_increment,namevarchar( +) not NULL, authorvarchar( +) not NULL, Pressvarchar( +) not NULL, Priceint(4),Primary Key(ID));Insert  intoBook (Name,author,press,price)Values("Mysql_guider", "Xiao Ming", "whu-Press "," About");Insert  intoBook (Name,author,press,price)Values("Python_guider", "Xiao Ming", "whu-Press "," -");Insert  intoBook (Name,author,press,price)Values("Ws_guider", "Xiao Ming", "whu-Press ","101");

View Code

(2) Create a view

CREATE View Book_view As select name, author from book;

Create success!

(3) View view

Desc Book_view;

(4) querying with views

SELECT * from Book_view;

(5) inserting data using a view

INSERT into Book_view values ("Lol_guider", "Xiao Yang");

Insert Success!

SELECT * from Book_view;

SELECT * from book;

(6) using views to change data

Update Book_view set author= "Xiao Ma" where name= "Ws_guider";

The change was successful!

SELECT * from book;

(7) changing the view

Alter VIEW Book_view AS select name, author, price from book with cascaded check option;

The change was successful!

Desc Book_view;

(8) Delete a view

Drop view if exists book_view;

Run successfully!

Reference:

[1] http://www.cnblogs.com/wangtao_20/archive/2011/02/24/1964276.html

[2] Http://www.jb51.net/article/36363.htm

[3] Http://www.cnblogs.com/lyhabc/p/3801527.html

[4] Http://blog.51yip.com/mysql/1062.html

[5] Http://www.cnblogs.com/kiwi/archive/2012/11/17/2775228.html

4 MySQL View