Mysql中關於視圖操作的詳解-mysql教程-PHP中文網

Mysql中關於視圖操作的詳解

黄舟

發布： 2017-09-06 15:08:30

原創

1440 人瀏覽過

视图的操作:

1.视图的创建:

create view view_name as 查询语句;

登入後複製

2.视图的查看:

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

登入後複製

3.使用视图(与查询表的语句相似);

select * from view_name (where 条件);

登入後複製

4.视图的修改:

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

登入後複製

5.视图的删除:

drop view view_name;

登入後複製

6.视图的分类:

(1).常量视图:

create view view_name as select 常量;

登入後複製

例子:

create view view_test1 as select 3.1415926;

登入後複製

查询:

select * from view_test1;

登入後複製

(2).聚合函数(SUM,MIN.,MAX,COUNT等)查询语句视图:

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

登入後複製

例子:

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

登入後複製

(3).排序功能的视图:

create view view_name as select 属性名 from table_name order by 属性名顺序;

其中顺序分为DESC 和 ASC ,前者降序,后者升序.

(4).表内连接查询语句:

create view view_name as select 属性 from table1_name,table2_name where 条件;

例子:

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;

登入後複製

其中的table_name 后的as的意思是起别名;

(5).表外连接(LEFT JOIN和RIGHT JOIN)查询语句:

create view view_name as select 属性名 from table1_name LEFT JOIN table2_name on 条件 where 条件;

左连接A,B表结果为A的全部记录和符合条件的B的记录

例子:

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).子查询相关查询语句:

create view view_name as select 属性名 from table1_name where 属性名 in (select 属性名 from table2_name);

例子:

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

登入後複製

(7).记录联合(UNION和UNION ALL):

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

登入後複製

查询的内容应该相同.

以上是Mysql中關於視圖操作的詳解的詳細內容。更多資訊請關注PHP中文網其他相關文章！