How to create a view in MySQL database

This article mainly describes how to use the CREATE VIEW statement to create a view in MySQL.

Syntax:

CREATE VIEW <视图名> AS <SELECT语句>

• : Specify the name of the view. The name must be unique in the database and cannot have the same name as another table or view.

• : Specify the SELECT statement to create a view, which can be used to query multiple base tables or source views.

There are the following restrictions on the specification of the SELECT statement in the created view:

1. Users except those who have CREATE VIEW In addition to permissions, it also has relevant permissions on the base tables and other views involved in the operation.

2. SELECT statement cannot reference system or user variables.
   

3. SELECT statement cannot contain subqueries in the FROM clause.

4. SELECT statement cannot reference prepared statement parameters.

The table or view referenced in the view definition must exist. However, after you create the view, you can delete the table or view that the definition references. You can use the CHECK TABLE statement to check whether the view definition has such problems.
The ORDER BY statement is allowed in the view definition, but if you select from a specific view, and the view uses its own ORDER BY statement, the ## in the view definition #ORDER BY will be ignored. The
TEMPORARY table (temporary table) cannot be referenced in the view definition, and the TEMPORARY view cannot be created.
WITH CHECK OPTION means that when modifying the view, check whether the inserted data meets the conditions set by WHERE .

Create a view named

view_menu on the b_menu table. The entered SQL statement and execution results are as follows.

Create view SQL statement:

create view view_menu
as select *from b_menu;

View view SQL statement:

select * from view_menu;

By default, the created view has the same fields as the basic table. You can also create a view by specifying the name of the view field. (Mainly because all the data is used when creating the view).

Recommended:

mysql tutorial

The above is the detailed content of How to create a view in MySQL database. For more information, please follow other related articles on the PHP Chinese website!