MySQL database view

Day05 MySQL Database View

I. Overview of views:

A view is a virtual table, derived from one or more tables in a database, whose contents are defined by a query. Only the definition of the view exists in the database, but there is no related data, which is stored in the original table. Therefore, the view depends on the data in the original table, and the data in the view changes when the data in the table changes.

The role of the view:

1) simplicity,

2) security,

logical data independence,

II. Views:

1. Create a view:

create view name as select * from table name;

Notes:

1. Running the statement of creating view requires the user to have the permission to create view. If [or replace] is added, the user also needs to have the permission to delete view.

Select statements cannot contain subqueries in the from clause.

Select statements cannot refer to system or user variables.

Select statements cannot refer to preprocessed statement parameters.

The table or view referenced in the definition must exist.

Temporary tables cannot be referenced in definitions and temporary views cannot be created.

The table defined in the view must exist.

8, can not say trigger program and view associated together.

Order by is allowed in definition views, but if you select from a particular view that 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 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 name as select statement;

6. Update View

View update is actually data table update, update view refers to the view to insert, update, delete data in the table. Because a view is a virtual table in which there is no data.

update table name set view field1 ='a value', view field2 =' a value', where condition;

7. Delete View

drop view if exists View name; If exists: Yes Determine if the view exists, and delete if it exists. No, no, no.

III. Conclusion

This is the end of the lesson. At the same time, the basics of MySQL database are also learned. Next, we will enter the study of ascension.