View in MySQL

First, create a view

Create [algorithm = view algorithm] View name as SELECT statement; --can be any query statement (normal query, connection query, union query, subquery)

There are three view algorithm options:

1. Undefined: The default setting, which indicates that no algorithm is specified, typically this option is automatically specified as merge.

2. temptable: Temporal table algorithm, the system first executes the view encapsulated SELECT statement, and then executes the SELECT statement outside the view, because to execute two query statements, so the efficiency is relatively low.

3. Merge: Merge algorithm, the system merges the view-encapsulated SELECT statement with the SELECT statement outside the view before executing the merged statement, which is relatively efficient because it only needs to be executed once.

　　Note: When you create a multi-table view, fields with the same name in multiple tables cause the creation to fail , either by using aliases or by querying only the different name segments.

　　CREATE VIEW my_v1 as SELECT * from My_student; --Create a single table view

　　CREATE VIEW my_v2 as Select c.name,c.room,s.* from My_class as C left join my　　_student as s on c.id = S.class; --Create multiple table views without querying the same name field ID

　　CREATE VIEW my_v3 as select c.id as c_id,c.name,c.room,s.* From My_class as C left joins My_student as s on c.id = S.class; --Query the same name field ID with an alias

Second, view view

All methods used to view the table are available for viewing views:

Show tables;

Show tables like ' pattern ';

Show create table view name; --You can also use the show CREATE view name

DESC view name;

Third, use the view

The view is used primarily for querying, and the view is queried as if it were a table.

Four, modify the view

ALTER view name as a new SELECT statement; --Changes to the view can only modify the statements he encapsulates

V. Delete a view

Drop view name;

View in MySQL