MySQL database view

DAY05 MySQL Database view

First, view overview:

A view is a virtual table that is a table from one or more tables in a database that is defined by a query. There is only a definition of the view in the database, and there is no related data, and the data is stored in the original table. Therefore, the view is dependent on the data in the original table, the data in the table changes, and the data in the view changes.

The role of the view:

1, with Simple,

2, with security,

3. Logical data Independence,

Second, view:

1. Create a view:

CREATE VIEW name as SELECT * from table name;

Precautions :

1. The statement that runs the CREATE view requires the user to have the CREATE VIEW permission, and if "orreplace" is added, the user has permission to delete the view.

2. The SELECT statement cannot contain subqueries from the FROM clause.

3. The SELECT statement cannot refer to a system or user variable.

4. The SELECT statement cannot reference a preprocessing statement parameter.

5. The table or view referenced in the definition must exist.

6. The temporary table cannot be referenced in the definition and the temporary view cannot be created.

7. The table defined in the view must exist.

8. The trigger program and the view cannot be associated together.

9. The order by is allowed in the definition view, but if a selection is made from a specific view, and the view uses a statement with its own order by, it is ignored.

2. View the view structure

Describe view name; or desc view name;

3. View Data

Show table status like ' View name ';

4. View the detailed definition of the view

Show Table view name;

5. Modify the View

Create or Replace view view name as SELECT statement;

6. Update view

The update of a view is actually an update to the data table, and the update view refers to inserting, updating, and deleting the data in the table through the viewport. Because the view is a virtual table, where there is no data.

Update table name set view Field 1 = ' Some value ', view field 2 = ' Some value ' where condition;

7. Delete View

Drop view if exists name; If exists: is to determine if the view exists and to perform the deletion if it exists. No, not done.

Iii. concluding remarks

Learn the knowledge of the view here. At the same time the basic knowledge of MySQL database is also finished. Then we will go into the study of Ascension.

This article from "Lonely One Night" blog, reproduced please contact the author!

MySQL database view