What is the view syntax in mysql

View syntax in mysql: 1. Create a view, the code is [CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]]; 2. Modify the view, the code is [ALTER [ALGORITHM] .

The operating environment of this tutorial: Windows 7 system, mysql version 8.0.22, DELL G3 computer. This method is suitable for all brands of computers.

Related free learning recommendations: mysql video tutorial

View syntax in mysql:

Overview of views:

View (View) is a virtual table. The view does not actually exist in the database. The row and column data come from the tables used in the query of the custom view and are dynamically generated when the view is used. In layman's terms, a view is the result set returned after a SELECT statement is executed. So when we create the view, the main work falls on creating this SQL query statement.

The advantages of views over ordinary tables mainly include the following.

• Simple: Users who use the view do not need to care about the structure, association conditions and filtering conditions of the subsequent corresponding tables. For users, it is already a result set of filtered compound conditions.

• Security: Users using views can only access the result sets they are allowed to query. Permission management of the table cannot be limited to a certain row or column, but it can be done through the view. Simple implementation.

• Data independence: Once the structure of the view is determined, the impact of changes in the table structure on users can be shielded. Adding columns to the source table has no impact on the view: modifying the column name of the source table can be done by Modify the view to solve it without affecting visitors.

View syntax

Create view

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

WITH [CASCADED | LOCAL] CHECK OPTION determines whether to allow data to be updated Make the record no longer meet the conditions of the view.

• LOCAL: As long as the conditions of this view are met, it can be updated.

• CASCADED: All conditions for all views of this view must be met before it can be updated. CASCADED is the default value.

Create views and query views:

Update views:

It is found that the view has been updated. Query the original table:

It is found that the original table has also been changed. It can be seen that modifications to the view will affect the basic table. ( Whether the view can be updated depends on the setting of WITH [CASCADED | LOCAL] CHECK OPTION). Although the view can be updated, updating is not recommended because the view is used to simplify queries.

Modify the view syntax:

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

View the view:

When using the SHOW TABLES command, not only the name of the table is displayed, but also the view is displayed. Name:

Use the SHOW CREATE VIEW view name command to query the statements executed when creating the view:

Delete view:

Syntax:

DROP VIEW [IF EXISTS] view_name [, view_name] ...[RESTRICT | CASCADE]

Delete view:

The above is the detailed content of What is the view syntax in mysql. For more information, please follow other related articles on the PHP Chinese website!