MySQL (10) View

Objective

Before you introduce the query statement, feeling write is also good, like can go to see. What we are sharing today is the view in MySQL.

View: A view is a virtual table formed by the results of the query. Non-temporary table, as long as not delete the words will be stored on the disk, but there is no corresponding file . The use of views is the same as for normal tables.

first, what is a view

A view is a specific subset of database data. You can prevent all users from accessing database tables, and require users to manipulate data only through views , which can protect users and applications from certain database modifications .
The view is abstract and when used, extracts the data from the table and forms a virtual table . But there are a lot of restrictions on his operation.
The view is never going to disappear by itself unless you manually delete it .
Views can sometimes be helpful in improving efficiency. Temporary tables are almost not useful for performance and are resource users .
Views are typically stored with the database, and temporary tables are always in tempdb .
The view is suitable for multi-table connection browsing when it is not suitable for adding, deleting, changing, which can improve the efficiency of execution.

Ii. Overview of the View 　　 2.1. Differences between views and tables

The view is a compiled SQL statement, and the table is not .
The view has no actual physical record, and the table has .
The table is the content, and the view is the window.
The table uses only the physical space and the view does not occupy the physical space, the view is only the existence of the logical concept, the table can be modified in time , but the view can only be modified by the statements created .
A view is a way of looking at a data table, querying data from some fields in a datasheet, just a collection of some SQL statements. From a security standpoint, the view does not know the table structure without giving the user access to the data table.
A table is a table in global mode, a real table , a table with a local schema, and a virtual table .
The creation and deletion of views affects only the view itself, and does not affect the corresponding base table .

　 2.1. Connection of views and tables

A view is a table built on top of a base table whose structure (that is, defined columns) and content (that is, all data rows) come from the base table, which exists based on the existence of the base table .
A view can correspond to a base table, or it can correspond to more than one base table .
A view is an abstraction of a basic table and a new relationship that is established in a logical sense .

2.3. Type of view 　　

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.

Create [algorithm= algorithm] View v_name AS SELECT statement
Algorithm = merge/temptable/undifined (first two optional)

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.

When a view is referenced, the statement that references the view is merged with the statement that defines the view.

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.

When a view is referenced, a temporary table is established based on the view's creation statement.

3) UNDEFINED

When a view is referenced, a temporary table is established based on the view's creation statement.

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.

third, the basic use of the view

Environment:

　　　　

3.1. Create a View

 as Select statement;

　　　

　　Once you have created a view, you can view it by looking at all the data tables in the database:

　　　　

　　You can see that the view you just created has been put into the table collection of the database in the current database. Because a view is also a table, it is a virtual table.

3.2. Enquiry

　　　　The query for the view is the same as the query for the base table, because the view is also a data table, so you can query it like this

　　　　

　　 3.3. Delete View

Drop View v_name;

　　　

Delete view, no effect on base table

3.4. Modify the View

 as Select statement;

Results check:

3.5. Update View

1) Create a new view

　　　　　　

2) Update view

　　　　　

Summary: updates will cause the data in the base table to be updated accordingly

Iv. Advantages of using views 　　 4.1, can simplify the query

Check the top three highest salary departments:

　　　　　　

　 4.2, you can control the rights

The permissions of the table are closed, but the appropriate view view permission is opened, and only part of the data is open in the view.

4.3, large data can be used in the sub-table

For example, in general (no special Optimization) Table of more than 200w of the number of rows, the operation will be significantly slower, you can put a table of data, split into more than one table to store.
Using a view, multiple tables form a single view that looks like an entire table.

4.4. Easy to maintain and organize the database 　　　

A, B table synthesis C table, in order to make the original SQL, you can use the C table, a, B table structure of the same data of the same view created to continue using.

　　

MySQL (10) View