MySQL getting started tutorial (7) view, mysql getting started tutorial View

MySQL getting started tutorial (7) view, mysql getting started tutorial View

Related reading:

MySQL getting started tutorial (5) create, modify, and delete tables

A view is a virtual table exported from one or more tables. A view is like a window in which you can view the data provided by the system.

1. View Overview

1.1 view meaning

A view is a virtual table exported from one or more tables. It can also be defined based on an existing view.
The database only stores view definitions, but does not Store View data. The data is still stored in the original table. Once the data in the table changes, the data displayed in the view also changes accordingly.
The MySQL view does not support the input parameter function, that is, the interactive performance is poor, but the changes are not very large. Using the view can greatly simplify user operations.

1.2 role of views

A view is similar to a filter.

(1) What you see is what you need to simplify the operation
(2) Through views, users can only query and modify specified data, thus limiting user permissions and increasing data security.
(3) views can avoid the impact of changes to 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

Algorithm is the Algorithm selected for the view. The attribute list specifies the nouns of each attribute in the view. By default, the attributes are the same as those queried in the Select statement; A Select statement is a complete query statement that identifies certain records that meet the conditions from a table and imports these records into the view; with Check Option indicates that the update view must be within the permission range of the view. CASCADED indicates that all related views and table conditions must be met when the view is updated. This is the default value, local indicates that the view must satisfy its definition when updating the view. Note that it is best to add With Check Option when creating the view and select the default CASCADED parameter. This ensures that the constraints of the parent view can be considered after the new view derived from the view is updated, which is more rigorous.

(1) For the three options in Algorithm, the Undefined option indicates that MySQL will automatically select the Algorithm to be used, and the Merge option indicates that view statements and view definitions are merged, make a part of the view definition replace the corresponding part of the statement. The Temptable option indicates that the view result is saved to the temporary table and then the statement is executed using the temporary table.

(2) Before creating a view, you must check whether you have the Create view permission and Select permission for the columns involved in the query, these permissions are stored in the user table in the MySQL database and can be queried using the select statement.

Select select_priv, create_view_priv from mysql. user where user = 'username'

The user name parameter indicates whether the user to be queried has the Drop permission. In my system, only the root user is allowed.

The following example shows how to create a view named worker_view1 from the department table and worker table.

3. view the view

To view a view, you must have the show view permission and save it to the user table.

View viewing methods include describe statements, show table status statements, show create view statements, and views tables in the information_schema database.

3.1 view simple information using describe statements

View describe statements are the same as view statements, because view is a special table, the basic form is

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 name;

3.4 View details in the views table

In MySQL, all views are defined in the views table in the information_schema database. query this table to view the details of all views in the database.

The basic form is

select * from information_schema.views;

* Indicates querying information of all columns.

Obviously, this statement is used to view all views, which is inconvenient to query. Generally, you can use the 3.3 method to query details.

4. Modify the view

When some fields in the basic table change, it is necessary to modify the view definition to maintain consistency between the view and the basic table.

4.1 Create or replace view statement

This statement is easy to use. You can modify a view when a view exists. If no view exists, you can create a view. The syntax is

In fact, the statement re-defines a view and overwrites the original graph. <� "Http://www.bkjia.com/kf/ware/vc/" target = "_ blank" class = "keylink"> vcD4NCjxoMyBpZD0 = "42-alter statement"> 4.2 Alter statement

Previously, the alter statement not only creates an index, but also modifies the table definition. In this case, you can also modify the view definition.
The basic form is

5. Update the view

Update a view is used to insert, update, and delete data in a table.
When updating a view, only data within the permission range can be updated.
In fact, the last update of the view is implemented on the basic table.

Note that the view cannot be updated in the following situations:

(1) The view contains functions such as sum (), count (), max (), and min ().

(2) The view contains keywords such as union, union all, distinct, group by, and having.

(3) constant view, that is, the constant type of a field in the view

(4) select in the view contains subqueries

(5) views exported from non-updatable views

(6) When creating a view, algorithm selects the temptable type, that is, the default temporary table cannot be updated.

(7) There is a column with no default value on the table corresponding to the view, and the column is not included in the view. This is because, when updating a view, this record without default values will be inserted with no values or NULL values. This situation is not allowed by the system.

There are many restrictions on update of views. Generally, it is best to use a view as a virtual table for data query, instead of updating data through a view, which may easily cause update failure.

6. delete a view

Deleting a view only deletes the view definition and does not delete data. The DROP View statement can be used to delete a View. However, you must have the drop permission.

Drop view [if exists] view name list [restrict | cascade]

If exists is used to determine whether a view exists. if exists is used to determine whether a view exists, view names are separated by commas.

The above is the MySQL getting started tutorial (7), which I hope to help you!

Articles you may be interested in:

• ASP. NET and MySQL Databases
• How to solve the error of MySQL view 1,349th
• Usage of MySQL notes View
• Application Analysis Based on mysql transactions, views, stored procedures, and triggers
• Sample Code for implementing oracle materialized view using mysql triggers
• Usage of visual charts in MySQL and tips on multi-Table INNER JOIN