視圖的操作: 1.視圖的創建: create view view_name as 查詢語句; 2.視圖的查看: show tables;// 顯示所有的表和視圖 show create view view_name; desc view_name; show table status from ta ...
視圖的操作: 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; 查詢的內容應該相同.
