mysql< View >

View View Overview

A view is a table that is exported from one or several basic tables and can operate as a basic table. Only view definitions are stored in the view, not the data corresponding to the view.

If the data for the base table changes, the data that is queried from the view also changes. So you can think of a view as a virtual table.

Advantages

Simplifying query statements

In daily development we can define frequently used queries as views, allowing users to avoid a large number of repetitive operations.

Security

With view users can only query and modify the data they see, other data in the database is neither visible nor accessible.

Logical Data Independence

Views can help users mask the impact of changes in the real-world table structure.

View Management Create a syntax format for a view

Create view< View name >[(< list; ...)] as subquery [with CHECK OPTION]

A subquery can appear with any SELECT statement, but the order by and distinct are usually not allowed in the query statement

Create a view on a single table Create a view on multiple tables View View

Use the Describe statement to view field information for a view

DESCRIBE view name;

or desc view name;

Use the show TABLE status statement to view basic information about a view

SHOW TABLE STATUS like ' view name '

You can use the show CREATE VIEW statement to view not only the definition statement when creating the view, but also the character encoding of the view

SHOW CREATE view name;

Modify a View

Because views are virtual tables that do not actually store data, updating the operation of the view ultimately translates to an update operation on the base table. To prevent data updates on a view, you can add a with CHECK option clause when you define the view when you are manipulating basic table data that is not part of the views scope.

We found that the data in the table changed. This is not safe because views are mostly browsed to the user and do not need to be modified. So we usually add the WITH CHECK option clause at the end of the SQL statement so that the user cannot modify it at will.

1. Modify the view using the Create OR REPLACE view statement

CREATE [OR REPLACE][algorithm = {UNDEFINED | MERGE | TempTable}]

VIEW view_name [(column_list)]

As Select_statement

[with[cascaded | LOCAL] CHECK OPTION]

2. Modify the view with the ALTER statement

ALTER [algorithm = {UNDEFINED | MERGE | TempTable}]

VIEW view_name [(column_list)]

As Select_statement

[with[cascaded | LOCAL] CHECK OPTION]

Update View

When the view contains the following content, the update operation for the view cannot be performed:

The view does not contain columns that are defined as non-empty in the base table.

A mathematical expression is used in the field list after the SELECT statement that defines the view.

The aggregate function is used in the field list after the SELECT statement that defines the view.

The Distinct,union,top,group by or HAVING clause is used in the SELECT statement that defines the view.

Update a view with the UPDATE statement

Updating views with DELETE statements

Updating views with INSERT statements

Delete a view

Only the definition of the view can be deleted and the data will not be deleted.

Drop View < view name >

Application case-The application of the view

mysql< View >