視圖的操作:
1.視圖的創建:
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).聚合函數(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;
查詢的內容應該相同.
?
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
