Operation of MySQL View

First, create the syntax form of the view

CREATE VIEW view_name

As query statement;

Working with views

SELECT * from View_name;

Second, create a variety of views

1. Encapsulates the view that implements the query constant statement, the so-called constant view

CREATE VIEW View_test1

As SELECT 3.1415926;

2. Encapsulating a view of query statements using aggregate functions (Sun,min,max,count, etc.)

CREATE VIEW View_test2

As SELECT COUNT (name) from T_student;

3. Encapsulates a view that implements a sort function (order by) query statement

CREATE VIEW View_test3

As SELECT name from T_student ORDER by ID DESC;

4. Encapsulates a view that implements in-table join query statements

CREATE VIEW View_test4

As SELECT S.name

From T_student as s,t_group as G

WHERE S.group_id=g.id and g.id=2;

5. Encapsulates a view that implements an out-of-table connection (left Join,right JOIN) query statement

CREATE VIEW VIEW_TEST5

As SELECT S.name

From T_student as S left JOIN T_group as G on s.group_id=g.id

WHERE g.id=2;

6. Encapsulates a view that implements query statements related to subqueries

CREATE VIEW View_test6

As SELECT S.name

From T_student as S

WHERE s.gruop_id in (SELECT ID from t_group);

7. Encapsulates a view that implements a record federation (union,union all) query statement

CREATE VIEW View_test7

As SELECT Id,name from T_student

UNION All

SELECT id,name from T_group;

Third, view view

1.SHOW TABLES;

You can view the table name and view the view

2.SHOW TABLE STATUS;

View detailed information for tables and views

SHOW TABLE STATUS

From view like "View _selectproduct" \g

Viewing view _selectproduct Views in a view database

SHOW CREATE VIEW view_name;

To view the definition information for a view

3.describe| DESC view_name;

Viewing view design Information

4. Viewing view information through system tables

Use INFORMATION_SCHEMA;

SELECT * from views WHERE table_name= ' view_selectproduct ' \g

Iv. Deleting views

DROP VIEW view_name1,view_name2;

You can delete 1 or more views at a time

V. Modify the View

1. Delete the view first, and then recreate the view

2.CREATE OR REPLACE VIEW _name

As query statement;

3. ALTER VIEW view_name

As query statement;

Vi. manipulating basic tables with views

The operation of adding, deleting and changing view data directly affects the basic table;

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

View_product is a view that queries all fields

1. Adding Data operations

INSERT into View_product VALUES (one, ' PEAR4 ', 12.3);

2. Delete Data operations

DELETE from view_product WHERE name= ' Apple ';

3. Update Data operations

UPDATE view_product set price=3.5 WHERE name= ' pear ';

Operation of MySQL View