1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
<code> </code><code>/*視圖*/</code><code>--視圖不占用實體空間</code>
<code>/*表和視圖共享資料庫中相同的名稱空間，是以，資料庫不能包含具有相同名稱的表和視圖*/</code>
<code>/*視圖縮減業務邏輯 http://blog.itpub.net/28194062/viewspace-772902/</code>
<code>視圖用來隐藏複雜的業務邏輯，從</code><code>join</code><code>連接配接查詢産生一個</code><code>view</code><code>。先使用 視圖完成一定的邏輯，再在視圖的基礎上完成另外的邏輯。</code>
<code>通常，視圖完成的邏輯都是相對比較基礎的邏輯。</code>
<code> </code><code>注意：</code>
<code>1、 盡量使用視圖完成讀操作</code>
<code>2、 如果使用視圖，則需要注意，對視圖的修改，也是對基表的修改，會即時生效；</code>
<code>3、 删除視圖時，不會銷毀實體表内的資料</code>
<code>4、 如果大家做的是外部接口，一個資料庫多個應用，針對每一個應用，采用不同的視圖接口。</code>
<code>5. 視圖支援嵌套，也就是說可以利用其他視圖檢索出來的資料建立新的視圖</code>
<code>5. 在視圖中可以使用 OREDR </code><code>BY</code><code>，但是如果視圖内已經使用該排序子句，則視圖的</code><code>ORDER</code> <code>BY</code> <code>将覆寫前面的 </code><code>ORDER</code> <code>BY</code><code>。</code>
<code>6. 視圖不能索引，也不能關聯觸發器或預設值。</code>
<code>7 視圖可以和表同時使用</code>
<code>*/</code>
<code>--檢查使用者是否擁有select權限和create_view權限</code>
<code>select</code> <code>select_priv,create_view_priv </code><code>from</code> <code>mysql.</code><code>user</code> <code>where</code> <code>user</code> <code>=</code><code>'guest'</code>
<code>--建立普通視圖</code>
<code>create</code> <code>view</code> <code>department_view1 </code><code>as</code> <code>select</code> <code>* </code><code>from</code> <code>department </code><code>--從department中選取資料直接定義到視圖department_view1中</code>
<code>--建立普通視圖并自定義字段名</code>
<code>create</code> <code>view</code> <code>department_view2(</code><code>name</code><code>,</code><code>function</code><code>,location) </code><code>--建立視圖department_view2包含name,function,location字段</code>
<code>as</code> <code>select</code> <code>d_name,</code><code>function</code><code>,address </code><code>from</code> <code>department </code><code>--從department表中分别select三個字段的資料到視圖的對應三個字段中</code>
<code>/*視圖的執行算法：</code>
<code>存在兩種執行算法：</code>
<code>1、 Merge：合并的執行方式，每當執行的時候，先将我們視圖的sql語句與外部查詢視圖的sql語句，混合在一起，最終執行；</code>
<code>2、 Temptable：臨時表模式，每當查詢的時候，将視圖所使用的</code><code>select</code><code>語句生成一個結果的臨時表，再在目前的臨時表内進行查詢。</code>
<code>指的是一個視圖是在什麼時候執行，依據哪些方式執行；</code>
<code>對于MERGE，會将引用視圖的語句的文本與視圖定義合并起來，使得視圖定義的某一部分取代語句的對應部分。(現用現查？)</code>
<code> </code>
<code>對于TEMPTABLE，視圖的結果将被置于臨時表中，然後使用它執行語句。(臨時表無法随時更新資料？)</code>
<code>對于UNDEFINED，MySQL将選擇所要使用的算法。如果可能，它傾向于MERGE而不是TEMPTABLE，這是因為MERGE通常更有效，而且如果使用了臨時表，視圖是不可更新</code>
<code>當使用者建立視圖時，mysql預設使用一種undefine的處理算法，就是會自動在合并和臨時表内進行選擇。</code>
<code> </code><code>*/</code>
<code> </code><code>--補坑</code>
<code>CREATE</code> <code>TABLE</code> <code>worker ( </code>
<code>id </code><code>INT</code><code>(4) </code><code>NOT</code> <code>NULL</code> <code>UNIQUE</code> <code>PRIMARY</code> <code>KEY</code> <code>AUTO_INCREMENT,</code>
<code>num </code><code>INT</code><code>(10) </code><code>NOT</code> <code>NULL</code> <code>UNIQUE</code> <code>,</code>
<code>d_id </code><code>INT</code><code>(4) ,</code>
<code>name</code> <code>VARCHAR</code><code>(20) </code><code>NOT</code> <code>NULL</code> <code>,</code>
<code>sex </code><code>VARCHAR</code><code>(4) </code><code>NOT</code> <code>NULL</code> <code>,</code>
<code>birthday </code><code>DATE</code> <code>,</code>
<code>address </code><code>VARCHAR</code><code>(50) ,</code>
<code>CONSTRAINT</code> <code>worker_fk </code><code>FOREIGN</code> <code>KEY</code> <code>(d_id)</code>
<code>REFERENCES</code> <code>department (d_id)</code>
<code>);</code><code>--隻找到表定義語句，沒有找到表中資料，看來沒法實驗了</code>
<code> </code>
<code>--在多表上建立視圖</code>
<code>create</code> <code>algorithm = merge </code><code>view</code> <code>-- 建立合并執行算法的視圖，現用現查？等會試試看</code>
<code>worker_view1(</code><code>name</code><code>,department,sex,age,address) </code><code>--視圖名為worker_view1，包含name,department,sex,age,address字段</code>
<code>as</code> <code>--少了as一直報錯</code>
<code>select</code> <code>name</code><code>,department,d_name,sex,2009-birthday,address </code><code>--選取name,department等列，并将計算2009-birthday的值作為字段</code>
<code>from</code> <code>worker,department </code><code>where</code> <code>worker.d_id=department.d_id</code><code>--報錯沒有worker表，補坑</code>
<code>with</code> <code>local</code> <code>check</code> <code>option</code><code>;</code><code>--local參數是更新視圖時隻考慮此視圖的限制，不考慮資料來源或父視圖的限制，cascaded更新時考慮滿足所有相關視圖和表的條件(預設)</code>
<code>--檢視視圖</code>
<code>--檢視視圖定義</code>
<code>describe worker_view1 </code>
<code>--檢視視圖基本資訊</code>
<code>show </code><code>table</code> <code>status </code><code>like</code> <code>'worker_view1'</code> <code>--comment項為view，說明是視圖，engine等其他項為空，說明是虛拟表</code>
<code>--檢視視圖詳細資訊</code>
<code>show </code><code>create</code> <code>view</code> <code>worker_view1 </code>
<code>--輸出如下</code>
<a href="http://s4.51cto.com/wyfs02/M02/80/18/wKiom1c3FJmCN1mFAACgbUmVSfg191.png" target="_blank"></a>
<code>CREATE</code> <code>ALGORITHM=MERGE </code>
<code>DEFINER=`root`@`localhost` </code><code>--與表定義語句不同的是，這裡出現了被誰定義（建立）的資訊</code>
<code>SQL SECURITY DEFINER </code><code>VIEW</code>
<code>`worker_view1` </code>
<code>AS</code>
<code> </code><code>select</code> <code>`worker`.`</code><code>name</code><code>` </code><code>AS</code> <code>`</code><code>name</code><code>`,</code>
<code> </code><code>`department`.`d_name` </code><code>AS</code> <code>`department`,</code>
<code> </code><code>`worker`.`sex` </code><code>AS</code> <code>`sex`,</code>
<code> </code><code>(2009 - `worker`.`birthday`) </code><code>AS</code> <code>`age`,</code>
<code> </code><code>`worker`.`address` </code><code>AS</code> <code>`address` </code>
<code> </code><code>from</code> <code>(`worker` </code><code>join</code> <code>`department`) </code>
<code> </code><code>where</code> <code>(`worker`.`d_id` = `department`.`d_id`) </code>
<code> </code><code>WITH</code> <code>LOCAL</code> <code>CHECK</code> <code>OPTION</code>
<code>---修改視圖</code>
<code>--使用create or replace view語句，||不僅可以修改已經存在的視圖，也可以建立新視圖，優選create or replace方式</code>
<code>describe department_view1</code><code>--檢視原來的定義</code>
<code>create</code> <code>or</code> <code>replace</code> <code>algorithm=temptable </code><code>view</code> <code>department_view1(department,</code><code>function</code><code>,location)</code>
<code>as</code> <code>select</code> <code>d_name,</code><code>function</code><code>,address </code>
<code>from</code> <code>department</code>
<code>describe department_view1</code><code>--檢視現在的定義,隻剩三個字段了</code>
<code>--使用alter語句||隻可以修改已經存在的語句</code>
<code>alter</code> <code>view</code> <code>department_view2(department,</code><code>name</code><code>,sex,location)</code>
<code>as</code> <code>select</code> <code>d_name,</code><code>name</code><code>,worker.sex,department.address</code>
<code>from</code> <code>department,worker </code><code>where</code> <code>department.d_id=worker.d_id</code>
<code>with</code> <code>check</code> <code>option</code>
<code>---更新視圖</code>
<code> </code><code>--是指通過視圖來Insert，update，delete表中的資料--→視圖是一個虛拟表--→沒有資料--→轉換到源資料的表中進行更新</code>
<code> </code><code>create</code> <code>view</code> <code>department_view3(</code><code>name</code><code>,</code><code>function</code><code>,address)</code>
<code> </code><code>as</code> <code>select</code> <code>d_name,</code><code>function</code><code>,address </code><code>from</code> <code>department </code><code>where</code> <code>d_id=1001;</code>
<code> </code><code>--向視圖中更新一條記錄</code>
<code> </code><code>update</code> <code>department_view3 </code><code>set</code> <code>name</code><code>=</code><code>'科研部'</code><code>,</code><code>function</code><code>=</code><code>'新産品研發'</code><code>,address=</code><code>'3号樓5層'</code>
<code> </code><code>select</code> <code>* </code><code>from</code> <code>department </code><code>--源表中id=1001的資料也被修改</code>
<code> </code><code>--對視圖的更新最後都是實作在基本表上的</code>
<code> </code><code>--但除了一下幾種情況</code>
<code> </code><code>--1，視圖中包含sum()、count()、max()、min()等統計函數,如</code>
<code> </code><code>create</code> <code>view</code> <code>worker_view4(</code><code>name</code><code>,sex,total)</code>
<code> </code><code>as</code> <code>select</code> <code>name</code><code>,sex,</code><code>count</code><code>(</code><code>name</code><code>) </code><code>from</code> <code>worker</code>
<code> </code><code>--2,視圖中包含union(合并select結果時去除重複值),union all（不去除）,distinct,group by,having等關鍵字</code>
<code> </code><code>create</code> <code>view</code> <code>worker_view4(</code><code>name</code><code>,sex,address)</code>
<code> </code><code>as</code> <code>select</code> <code>name</code><code>,sex,homeaddress </code><code>from</code> <code>worker</code>
<code> </code><code>group</code> <code>by</code> <code>d_id</code>
<code> </code><code>--3,常量視圖</code>
<code> </code><code>create</code> <code>view</code> <code>worker_view6</code>
<code> </code><code>as</code> <code>select</code> <code>'Aric'</code> <code>as</code> <code>name</code>
<code> </code><code>--4,視圖的select中包含子查詢</code>
<code> </code><code>create</code> <code>view</code> <code>worker_view7</code>
<code> </code><code>as</code> <code>select</code><code>(</code><code>select</code> <code>name</code> <code>from</code> <code>worker)</code><code>--為啥沒有from子句也行？--實驗結果：無法從此視圖中查詢資料，報錯，正确寫法應該是下面的實驗</code>
<code> </code><code>--檢視下view建立語句</code>
<code> </code><code>show </code><code>create</code> <code>view</code> <code>worker_view7 </code><code>--還是不明白</code>
<code> </code><code>--實驗</code>
<code> </code><code>use sakila</code>
<code> </code><code>create</code> <code>view</code> <code>worker_view8 </code><code>as</code>
<code> </code><code>select</code><code>* </code><code>from</code> <code>(</code><code>select</code> <code>last_name </code><code>from</code> <code>customer ) a</code>
<code> </code><code>select</code> <code>* </code><code>from</code> <code>worker_view8</code>
<code> </code><code>--5,由不可更新視圖創造出的視圖同樣不可更新，algorithm=temptable，臨時表算法的視圖也同樣不可更新</code>
<code> </code><code>--6,with local check option選項的視圖更新時隻需考慮本視圖的限制不需考慮上級視圖的限制</code>
<code> </code><code>--7,with cascaded check option選項的視圖更新時需考慮本視圖與上級所有視圖的限制</code>
<code> </code><code>---删除視圖</code>
<code> </code><code>drop</code> <code>view</code> <code>if exists worker_view1 </code><code>--删除列舉出來的視圖，如果列舉的視圖存在就删除</code>
<code> </code><code>drop</code> <code>view</code> <code>if exists worker_view1,worker_view2 </code><code>--可以一次删除多個視圖，存在幾個就删除幾個</code>
本文轉自 angry_frog 51CTO部落格，原文連結:http://blog.51cto.com/l0vesql/1773387
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)