Mysql Getting Started Tutorial (vii) View _mysql

Related reading:

MySQL Introduction tutorial (v) creation, modification, and deletion of the table

A view is a virtual table that is derived from one or more tables. The view is like a window through which you can see the data specifically provided by the system.

1. Introduction to view

1.1 Meaning of views

A view is a virtual table that is derived from one or more tables, and can be defined from a view that already exists.
Only the definition of the view is stored in the database, and the data in the view is not stored in the original table. Once the data in the table changes, the data displayed in the view changes accordingly.
MySQL view does not support the function of input parameters, that is, poor interactivity, but for the change is not a great operation, the use of views can greatly simplify the user's operations.

1.2 Function of the view

The view acts like a filter, which can be summed up as

(1) What you see is what you need to make your operation simpler
(2) Through the view, users can only query and modify the specified data, thereby restricting the user's permissions, increase the security of the data
(3) The view can shield the effect of the changes of the original table structure, thus improving the logical independence of the table.

2. Create a View

In MySQL, creating a view is implemented through the Creat view statement, specifically

Where algorithm is the algorithm for view selection; The property manifest specifies nouns for each property in the view, the same as the properties of the query in the SELECT statement by default, and the SELECT statement is a complete query statement, Indicates that some of the records that meet the criteria are identified from a table and imported into the view; with CHECK option, to ensure that the view is in the right range when the view is updated, cascaded represents the criteria for all dependent views and tables when the view is updated, the default value, Local indicates that the view itself is defined when the view is updated, and that it is best to add with Check Option when using the CREATE view and to select the default cascaded parameter. This ensures that a new view derived from the view is updated to take into account the constraints of its parent view, which is more rigorous.

(1) Regarding the three options in algorithm, the undefined option indicates that MySQL will automatically select the algorithm to use, and the merge option means that statements that use the view are merged with the view definition, so that a part of the view definition replaces the corresponding part of the statement. The temptable option means that the results of the view are stored in a temporary table, and then the statement is executed using a temporary table.

(2) Note that before creating a view, you need to check for the permission to create view and the Select permissions for the columns that are involved in the query, which are stored in the user table under the MySQL database and can be applied to the SELECT statement query, specifically

Select Select_priv, Create_view_priv from Mysql.user where user= ' username '

Where the user name parameter indicates whether the user has drop permissions to query, and on my system, only the root user.

The following is a case of creating a view named Worker_view1 from the department table and the worker table

3. View View

View must have Show view permission, also stored in the user table.

The methods for viewing views include describe statements, Show table status statements, a view table CREATE VIEW statement, and query Information_schema database.

3.1 Describe statement to view simple information

The describe statement to view the view is the same as the statement that views the table, because the view is a special kind of table, basically in the form of

Describe view name;

3.2 Show Table Status statement view basic information

The basic form is

Show Table Status like ' view name ';

3.3 Show CREATE VIEW statement view details

The basic form is

Show create view view name;

3.4 View detailed information in the Views table

In MySQL, the definition of all views is in the view table under the INFORMATION_SCHEMA database, and querying this table allows you to view details of all views in the database.

The basic form is

SELECT * from Information_schema.views;

where * represents the information for all columns of the query.

Obviously use this statement to see is all the views, inconvenient to consult, general query details of the 3.3 method can be.

4, modify the view

When certain fields of a basic table change, it is necessary to modify the definition of the view to maintain consistency between the view and the base table.

4.1 Create or Replace view statement

This statement works well with views that can be modified in the presence of a view, and you can create a view without a view, with the syntax

In fact, feeling this statement is redefining a view and overwriting the original diagram. < yo "http://www.jb51.net/kf/ware/vc/" target= "_blank" class= "Keylink" >vcd4ncjxomybpzd0= "42-alter statement" >4.2 Alter statement

In retrospect, the ALTER statement can create indexes, modify the definition of the table, and modify the definition of the view.
The basic form is

5, update the view

Updating a view is the insertion, updating, and deletion of data from a table by view.
When you update a view, you can only update data within the scope of the permission.
In fact, the update of the view is finally implemented on the base table.

Note the following conditions do not update the view:

(1) The view contains sum (), COUNT (), Max (), and Min () functions

(2) The view contains Union,union All,distinct,group by and having etc keywords

(3) Constant view, that is, a field in a view that is often a type

(4) The Select in the view contains subqueries

(5) Views exported by non-updatable views

(6) When creating a view, algorithm selects the temptable type, which means that the system default temp table is not updatable

(7) There are columns with no default values on the table corresponding to the view, and the column is not included in the view. Because, when the view is updated, the record with no default value is inserted without a value and no null value is inserted, and the system does not allow this to occur

Because of the limitations of the view update, it is generally preferable to use the view as a virtual table for querying the data, rather than updating the data through the view, which can easily cause the update to fail.

6. Delete View

Deleting a view only deletes the definition of the view and does not delete the data. The drop VIEW statement can be used to delete a view, but the user must have the permissions of the drop, which is

Drop view [if exists] View name list [Restrict|cascade]

Where the if exists is used to judge the existence of the view, to be executed, and to separate the names of different views in the list of views by commas.

The above is a small set to introduce you to the MySQL introductory tutorial (vii) View, I hope to help you!