MySQL View usage Summary

Views View Usage Summary:

A view can be thought of as a "virtual table" because it returns a result set format similar to the format of the dataset returned by the Entity data table, and the view is referenced in the same way as the data table. Each time the query uses a view, the DBMS dynamically generates the logical merge required for the view result set into the result set logic generated from the base table data. (Technical discussion Group: 276592700 (new))

L What is a view?

Features of the L view?

Type of L view?

The use of the L view?

First, what is a view

A view is a virtual table that is exported from one or several data tables or views and acts like a filter on a data table and must be made up of a SELECT statement in an SQL statement. When you define a view, you simply store the definition of the view in the database, do not save the view's data until the user uses the view to query the data and return the operation, and when the data needs to be obtained from different servers, the view can be used to organize and return the same structure data.

Second, the characteristics of the view

The main features of the view are as follows:

1. Simplify the operation of data

Users can define frequently used connections, federated queries, and select queries as views, so that each time a call is called, simply call the view. In addition, views can hide complex relationships between table tables.

2, can be used as a security mechanism

Views can be used to make security mechanisms. Users can set the view so that specific users can only view or modify data within their permissions, and other databases or tables cannot operate. The security of a view prevents unauthorized users from viewing or manipulating specific columns or rows, establishing a view by setting a user's flag in your table so that users can only view or manipulate columns or rows that flag their own labels, ensuring data security.

3. Merging and splitting data

Sometimes, because the amount of data in a table is too large to split the table, which causes the structure of the table to change, causing the user's application to be affected, we can use the view to mask the logical relationship between the entity tables to build the original table relationships required by the application.

4. Pour and export the data

In the actual project, we often use views to organize and import and export operations, very convenient.

Iii. Types of views

Because of different databases, such as MySQL, SQL Server, Oracle, and DB2, they differ in the creation and type of views, especially on types, so here we use MySQL to illustrate the summary, and the features of other data will continue to be updated later.

The types of views in MySQL are divided into:

1. MERGE

Merges the SQL statements of the view with the SQL statements that refer to the view, and finally executes them together.

2, TempTable

The result set of the view is stored in a temporary table and is manipulated from the staging table each time it is executed.

3, UNDEFINED

The default view type, the DBMS prefers to choose merge rather than necessarily, because the merge is more efficient and, more importantly, the temporary table view cannot be updated.

Therefore, it is recommended to use the merge algorithm type view.

Properties of the View

A view is a reference to a number of basic tables, a virtual table, query execution results, not store specific data (basic table data has changed, the view will change);

Can be the same as the basic table, the increase and deletion check operation (PS: Add and delete to change the conditions of the operation);

The role of the view

Convenient operation, especially query operation, reduce complex SQL statements, enhance readability;

More secure, database authorization commands cannot be qualified to specific rows and specific columns, but you can restrict permissions to row and column levels by reasonably creating views

Use occasions

Permission control, you do not want users to access some of the table contains sensitive information columns, such as salary ...

The key information comes from a number of complex related tables, can create a view to extract the information we need to simplify the operation;

MySQL View usage Summary