Operation of MySQL View

1 Features of the view:

• The columns of the view can come from different tables, which are the new relationships established in the abstract and logical sense of the table.

• A view is a table (a virtual table) that has a base table (a real table).

• View resumes and deletions do not affect the base table.

• Updates (additions, deletions, and modifications) to the contents of the view directly affect the base table.

• Adding and deleting data is not allowed when the view comes from more than one base table.

2 Creating a View

Basic syntax

create view view_nameAS 查询语句

Attention

• In the SQL statement naming specification, views are typically named in view_xxx or v_xxx styles.

• Create an attempt to use the "DROP view IF EXISTS view name;" The ungrateful statement prevents duplicate creation of the visual error.

3 View View View name

Starting with MySQL5.1, executing the show Tables statement displays both the table name and the view name in the database.

SHOW TABLES;

View detail Information

Basic syntax

SHOW TABLE STATUS [FROM db_name] [LIKE ‘pattern‘] 

Viewing View definition Information

Basic syntax

SHOW CREATE VIEW view_name

Viewing view design Information

Basic syntax

DESCRIBE | DESC view_name

4 Deleting a view

Basic syntax

DROP VIEW view_name [,view_name] ...

5 Modifying views

1. Way One:
   Delete the view first, and then reestablish the view.

2. Way two:
   Basic syntax

   CREATE OR REPLACE VIEW view_nameAS 查询语句

3. Way three: (Alter statement)
   Basic syntax

   ALTER VIEW view_name AS 查询语句

Operation of MySQL View