Shulou(Shulou.com)05/31 Report--

This article will explain in detail what the view syntax in mysql has, and the editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

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

View syntax in mysql:

An overview of the view:

A View is a virtual table. The view does not actually exist in the database, and the row and column data is used to customize the table used in the query for the view, and is generated dynamically when the view is used. Generally speaking, a view is the result set returned after the execution of a SELECT statement. So when we create the view, the main task is to create this SQL query statement.

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

Simplicity: users who use views do not need to care about the structure, association conditions, and filter conditions of the corresponding tables at all, and are already the result set of filtered composite conditions for users.

Security: users using views can only access the result set that they are allowed to query, and the permission management of the table cannot be limited to a row or column, but it can be easily implemented through the view.

Data independence: once the structure of the view is determined, you can shield the impact of changes in the table structure on the user, and adding columns in the source table has no effect on the view: if the source table modifies the column name, it can be solved by modifying the view without affecting the visitors.

Syntax of the view

Create a view

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

WITH [CASCADED | LOCAL] CHECK OPTION determines whether the data is allowed to be updated so that the record no longer meets the conditions of the view.

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

CASCADED: all conditions for all views of this view must be met before they can be updated. CASCADED is the default.

Create views and query views:

Update the view:

Find that the view has been updated and query the original table:

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

Modify the view syntax:

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

View the view:

When you use the SHOW TABLES command, it displays not only the name of the table, but also the name of the view:

Use the SHOW CREATE VIEW + view name command to query the statements executed when the view is created:

Delete the view:

Syntax:

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

Delete the view:

This is the end of this article on "what are the view grammars in mysql?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.