Getting started with MySQL is simple-learning notes-Chapter 4 View

8.1 view Overview

A view is a virtual table exported from a database table, view, or multiple tables. It facilitates user operations on data.

 

8.2 create a view

You must have the create view and select permissions.

Select select_priv, create_view_priv from mysql. userwhere user = 'root ';

 

Create [algorithm = {undefined | merge | temptable}]

View view name [(attribute list)]

As SELECT statement

[With [cascaded | Local] Check option];

 

The algorithm parameter indicates the algorithm selected by the view.

Undefined is not specified and is automatically selected

Merge combines view statements and view definitions so that a part of view definitions replaces the corresponding part of statements.

Temptable stores the view results to a temporary table and then uses the temporary table to execute the statement.

The local parameter indicates that the view must meet the conditions defined by the view during update;

The cascaded parameter indicates that all related views and tables must be met when the view is updated. The default value is.

When using the create view statement to create a view, it is best to add the with check option parameter and the cascaded parameter. In this way, after the new view is derived from the view, the constraints of its parent view must be considered when the new view is updated. This method is strict to ensure data security.

 

Create viewdepartment_view1

Select * from department;

 

Create viewdepartment_view2 (name, function, localtion)

Select d_name, function, address fromdepartment;

 

Createalgorithm = merge view worker_view1 (name, department, sex, age, address)

Select name, department. d_name, sex, 2009-birthday, address from worker, Department whereworker. d_id = Department. d_id

With local check option;

 

8.3 View

You must have the show view permission.

 

Describe | DESC view name;

Show table status like 'view name ';

Show create view name;

Select * From information_schema.views;

 

8.4 modify View

Create or replace | alter [algorithm = {undefined | merge | temptable}]

View view name [(attribute list)]

As SELECT statement

[With [cascaded | Local] Check option];

The syntax is basically the same as that of create view.

 

8.5 update View

Updating a view refers to inserting (insert), updating (update), and deleting (delete) data in tables through the view. The view is a virtual table with no data. When views are updated, they are all converted to basic tables for update. When updating a view, only data within the permission range can be updated. If the update is out of the range, it cannot be updated.

Principle: Do not update the view whenever possible

The syntax is the same as the update syntax.

 

Which views cannot be updated:

1. The view contains sum (), count (), and other focus functions.

2. A view contains keywords such as Union, Union all, distinct, groupby, and having.

3. Constant View

Createview view_now as select now ()

4. The view contains subqueries.

5. Views exported from non-updatable views

6. When creating a view, algorithm is of the temptable type.

7. A column with no default value exists in the table corresponding to the view, and the column is not included in the view.

8. With [cascaded | Local] Check option also determines whether the view can be updated.

The local parameter indicates that the view must meet the conditions defined by the view during update;

The cascaded parameter indicates that all related views and tables must be met when the view is updated. The default value is.

 

8.6 Delete A View

When deleting a view, you can only delete the view definition and do not delete data.

The user must have the drop permission.

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