視圖的操作:

1.視圖的創(chuàng)建:

create view view_name as 查詢語句;

2.視圖的查看:

show tables;// 顯示所有的表和視圖
show create view view_name;
desc view_name;
show table status from table_name like 'view_name';

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).聚合函數(shù)(SUM,MIN.,MAX,COUNT等)查詢語句視圖:

create view view_name as select 函數(shù)(屬性名) 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).表內(nèi)連接查詢語句:

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表結(jié)果為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).子查詢相關(guān)查詢語句:

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).記錄聯(lián)合(UNION和UNION ALL):

create view view_name as 查詢語句1 UNION ALL 查詢語句2;

查詢的內(nèi)容應(yīng)該相同.

以上就是Mysql中關(guān)于視圖操作的詳解的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章！