Mysql視圖，mysql視圖創建

Mysql視圖，mysql視圖創建

1.簡介

　　視圖是由查詢結果形成的一張虛擬表。

2.使用場景

（1）如果某個查詢結果出現的非常頻繁，也就是，要經常拿這個查詢結果來做子查詢。

（2）需要隱藏某些數據而展現其他數據時。

3.創建視圖

create view 視圖名 as select 語句;

4.更新視圖

update 視圖名 set 語句;

5.修改視圖

 alter view 視圖名 as select 語句;

6.查看視圖

6.1 使用desc語句

desc 視圖名; 

6.2 show table status語句查詢視圖基本信息

show table status like '視圖名';

6.3 show create view語句查看視圖信息

show create view 視圖名;

6.4 在view表中查看視圖的詳細信息

select * from 視圖名;

7.視圖的優點

7.1 簡化查詢語句

比如：有一張商品表，我們經常要查每個欄目下商品的平均價格

  select cat_id,avg(shop_price) from goods gropy by cat_id;

這時候我們就可以創建一張視圖：

  create view avgPrice as select cat_id,avg(shop_price) from goods gropy by cat_id;

創建完，以後我們要查每個欄目的平均價格時，只要這麼寫

  select * from avgPrice;

就可以了。

7.2 可以進行權限控制

把表的權限封閉，但是開放相應的視圖權限，視圖裡只開放部分數據列，比如我們的goods商品表，我們不想讓別人看到我們的銷售價格，這時候我們就可以把查看商品表的權限封閉，創建一張視圖：

  create view showGoods as select goods_id,goods_name from goods;

不出現銷售價格列就可以了。

7.3 大數據分表時可以用到

比如表的行數據超過200萬行時，速度就會變慢，可以把一張表的數據拆成4張表來存放News表

  newsid  1,2,3,4...

  news1,news2,news3,news4表

把一張表的數據分散到4張表裡，分散的方法有很多，最常用的是id取模來計算 

  id%4+1=[1,2,3,4]

  ...

還可以用視圖，把四張表形成一張視圖

  create view news as select * from news1 union select * from news2 union ...

8.視圖與表的關系

視圖是表的查詢結果，自然表的數據變了，會影響視圖的結果。

9.視圖改變對表的影響

（1）視圖的增刪改也會影響表；

（2）但視圖並不總是能增刪改的；

（3）視圖的數據與表的數據一一對應時可以修改；

（4）對於視圖的insert還應注意：視圖必須包含表中沒有默認值的列。

10.視圖的algorithm(運算規則)

 algorithm = merge/temptable/undefined

10.1 merge

merge:當引用視圖時，引用視圖的語句與定義視圖的語句合並。意味著視圖只是一個規則，語句規則，當查詢視圖時，把查詢視圖的語句比如：where...那些與創建時的語句where子句等合並，分析，形成一條select語句。

舉個列子：

我們先創建一張視圖查詢所有商品價格大於3000的商品

 create view g2 as select goods_id,goods_name,shop_price from goods where shop_price > 3000;

然後我們再查詢視圖的時候，再加上一個where條件<5000

 select * from g2 where shop_price < 5000;

這時候它就會把兩條語句合並分析最終形成這樣一條select語句

 select goods_id,goods_name,shop_price from goods where shop_price > 3000 and shop_price < 5000;

10.2 temptable

temptable:是根據創建語句瞬間創建一張臨時表，然後查詢視圖的語句從該臨時表查數據。

10.3 merge和temptable區別

merge 和 temptalbe 有一個顯著的區別，merge最終去查的還是goods表，而temptable去查的是虛擬表。

舉個例子：我們要得到每個欄目下最貴的商品

首先我們創建一張視圖查出每個欄目的商品按價格降序排序

create view lmj as select cat_id,goods_id,goods_name,shop_price from goods order by cat_id,shop_price desc;

這時候我們在查詢這張視圖的時候再對cat_id進行分組是不是就能得到我們想要的結果呢？

select * from lmj group by cat_id;

答案是不能的，因為它把我們的創建視圖的語句和查詢視圖的語句合並成

select cat_id,goods_id,goods_name,shop_price from goods group by cat_id order by cat_id,shop_price desc;

而如果我們在創建視圖的時候指定了它的運算規則為：temptable

create algorithm=temptable view lmj as select cat_id,goods_id,goods_name,shop_price from goods order by cat_id,shop_price desc;

然後我們再查詢視圖：

select * frm lmj group by cat_id;

就能得到我們想要的結果了。它會先把

select cat_id,goods_id,goods_name,shop_price from goods order by cat_id,shop_price desc;

這句sql語句取到的結果放到一張臨時表，然後我們再從這張臨時表查自然能得到我們想要的結果了，而不是合並了再去查。

undefined:未定義，自動，讓系統幫你選。