This article uses examples to illustrate the principles and usage of MySQL views. Share with you for your reference, the details are as follows:

In this article:

• What is a view
• Creating a View
• View View
• View Modification
• Deleting a view
• Data Operations on Views

Release date: 2018-04-13

What is a View:

• A view is a virtual table based on query results . The table from which the data comes is called the base table.
• The creation and deletion of views does not affect the base table.
• Insertion and modification operations of the view will affect the basic table.
• If the view is derived from multiple base tables, the base tables cannot be modified.
• Uses of views:
  • 1. The view is based on the query results, so that the view can hide some data information in the base table that should not be displayed to the user (for example, a developer needs a user information table, but should not show his user's password information.)
  • 2. A view is a virtual table that can store query information for easy operation.
  • ......

Create a view:

• Syntax: create view view name as select statement [with check option]; [select statement can query results from multiple tables: joint query, join query]
  • With check option will restrict insert and modify operations based on conditional statements such as where (for example, if the retrieved view data is male, it is not allowed to change male to female)

    create view man_info as select * from student where gender="male";

Replenish:

• There is another option for creating a view: view algorithm (not described here, you can search Baidu if you want to know)

Check out the view:

• A view is a virtual table. All table view statements can be used in the view.
  • View all views: show tables/views;
  • View the view structure: desc/describe/show columns from view name;
  • View the view creation statement: show create table/view view name;

View modification:

• Sometimes a view definition error may occur, so a view modification operation is performed.
• Syntax: alter view view name as new select statement;

create view user_view as select * from user;
alter view user_view as select username,money from user;

Replenish:

• Since a view is a virtual table, there is another way to modify a view: create or replace view view creation statement; [the old view will be overwritten by the new view]

Deleting a view:

• Syntax: drop view view name[,view name…];
• Example:

  drop view student_class,student_info;

Data operations of the view:

• If the view is derived from multiple base tables, the base tables cannot be modified. However, in theory, updates are allowed.

View data view:

• Syntax: select field list from view name; [It is consistent with the query operation of the basic table. 】

Data insertion into the view:

• When data comes from multiple base tables, the insert operation cannot be performed.
• Syntax: insert into view name values(); [It is consistent with the insert operation of the basic table. 】
• Note: The view structure comes from the base table, so it must accept the constraints of the base table. If a field is not allowed to be empty, but the view's insert operation does not assign a value, the insert will fail.

View data modification:

• Syntax: update view name set field name = value where condition; [It is consistent with the modification operation of the basic table. 】

Deleting data from a view:

• When data comes from multiple basic tables, the deletion operation cannot be performed.
• Syntax: delete from view name where condition; [It is consistent with the deletion operation of the basic table. 】

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.