Detailed explanation of view operations in Mysql

View operations:

1. View creation:

create view view_name as 查询语句;

2. View view:

show tables;// 显示所有的表和视图
show create view view_name;
desc view_name;
show table status from table_name like 'view_name';

3. Use views (similar to query table statements) );

select * from view_name (where 条件);

4. View modification:

alter view view_name as 查询语句;
create or replace view view_name as 查询语句;

5. View deletion:

drop view view_name;

6. View classification:

(1) .Constant view:

create view view_name as select 常量;

Example:

create view view_test1 as select 3.1415926;

Query:

select * from view_test1;

(2).Aggregation function (SUM, MIN., MAX, COUNT, etc.) query statement view:

create view view_name as select 函数(属性名) from table_name;

Example:

create view view test2 as select count(name) from t_student;

(3). Sorting function view:

create view view_name as select attribute name from table_name order by attribute name order;

The order is divided into DESC and ASC, the former is in descending order and the latter is in ascending order.

(4).Intra-table connection query statement:

create view view_name as select attribute from table1_name,table2_name where Conditions;

Example:

create view view_testt4 as select s.name from t_student as s,t_group as g where s.group_id = g.id and g.id = 2;

The as after table_name means an alias;

(5). External table connection (LEFT JOIN and RIGHT JOIN) query statement:

create view view_name as select attribute name from table1_name LEFT JOIN table2_name on condition where condition;

Left join A, B table results are all records of A and those that meet the conditions B's record

Example:

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). Subquery related query statement:

create view view_name as select attribute name from table1_name where attribute name in (select attribute name from table2_name);

Example:

create vieww view_test6 as select s.name from t_student as s where s.group_id in (select id from t_group);

(7).Record union (UNION and UNION ALL):

create view view_name as 查询语句1 UNION ALL 查询语句2;

The content of the query should be the same.

The above is the detailed content of Detailed explanation of view operations in Mysql. For more information, please follow other related articles on the PHP Chinese website!