視圖

視圖是虛擬的表，與包含數據的表不同，視圖只包含使用時動態檢索數據的查詢,主要是用于查詢。

為什么使用視圖

重用sql語句
簡化復雜的sql操作，在編寫查詢后，可以方便地重用它而不必知道他的基本查詢細節。
使用表的組成部分而不是整個表。
保護數據。可以給用戶授予表的特定部分的訪問權限而不是整個表的訪問權限。
更改數據格式和表示。視圖可返回與底層表的表示和格式不同的數據。

注意：

在視圖創建之后，可以用與表基本相同的方式利用它們。可以對視圖執行select操作，過濾和排序數據，將視圖聯結到其他視圖或表，甚至能添加和更新數據。
重要的是知道視圖僅僅是用來查看存儲在別處的數據的一種設施。視圖本身不包含數據，因此它們返回的數據時從其他表中檢索出來的。在添加和更改這些表中的數據時，視圖將返回改變過的數據。
因為視圖不包含數據，所以每次使用視圖時，都必須處理查詢執行時所需的任一檢索。如果你使用多個聯結和過濾創建了復雜的視圖或者嵌套了視圖，可能會發現性能下降得很厲害。因此，在部署使用了大量視圖的應用前，應該進行測試。

視圖的規則和限制

與表一樣，視圖必須唯一命名；
可以創建任意多的視圖；
為了創建視圖，必須具有足夠的訪問權限。這些限制通常由數據庫管理人員授予。
視圖可以嵌套，可以利用從其他視圖中檢索數據的查詢來構造一個視圖。
Order by 可以在視圖中使用，但如果從該視圖檢索數據select中也是含有order by，那么該視圖的order by 將被覆蓋。
視圖不能索引，也不能有關聯的觸發器或默認值
視圖可以和表一起使用

視圖的創建

利用create view 語句來進行創建視圖
使用show create view viewname；來查看創建視圖的語句
用drop view viewname 來刪除視圖
更新視圖可以先drop在create，也可以使用create or replace view。

視圖的更新

視圖是否可以更新，要視情況而定。

通常情況下視圖是可以更新的，可以對他們進行insert，update和delete。更新視圖就是更新其基表(視圖本身沒有數據)。如果你對視圖進行增加或者刪除行，實際上就是對基表進行增加或者刪除行。

但是，如果MySQL不能正確的確定更新的基表數據，則不允許更新(包括插入和刪除)，這就意味著視圖中如果存在以下操作則不能對視圖進行更新：(1)分組(使用group by 和 having )；(2)聯結；(3)子查詢；(4)并；(5)聚集函數;(6)dictinct;(7)導出(計算)列。

存儲過程

存儲過程就是為了以后的使用而保存的一條或者多條MySQL語句的集合。可將視為批文件，雖然他們的作用不僅限于批處理。

為什么使用儲存過程？

1.通過把處理封裝在容易使用的單元中，簡化復雜的操作；

2.由于不要求反復建立一系列處理步驟，保證了數據的完整性。如果所有開發人員和應用程序都使用同一(實驗和測試)存儲過程，則所使用的代碼都是相同的。這一點的延伸就是防止錯誤。需要執行的步驟越多，出錯的可能性就越大，防止錯誤保證了數據的一致性。

3.簡化對變動的管理，如果表名。列名或者業務邏輯等有變化，只需要更改存儲過程的代碼。使用它的人員甚至不需要知道這些變化。這一點延伸就是安全性，通過存儲過程限制對基數據的訪問減少了數據訛誤的機會。

4.提高性能。因為使用存儲過程比使用單獨的sql語句更快。

5.存在一些只能用在單個請求的MySQL元素和特性，存儲過程可以使用他們來編寫功能更強更靈活的代碼

綜上：

三個主要的好處：簡單、安全、高性能。

兩個缺陷：

1、存儲過程的編寫更為復雜，需要更高的技能更豐富的經驗。

2、可能沒有創建存儲過程的安全訪問權限。許多數據庫管理員限制存儲過程的創建權限，允許使用，不允許創建。

執行存儲過程

Call關鍵字：Call接受存儲過程的名字以及需要傳遞給他的任意參數。存儲過程可以顯示結果，也可以不顯示結果。

CREATE PROCEDURE productpricing()

??? BEGIN

??????? SELECT? AVG( prod_price)? as priceaverage FROM products;

??? END;

創建名為productpricing的儲存過程。如果存儲過程中需要傳遞參數，則將他們在括號中列舉出來即可。括號必須有。BEGIN和END關鍵字用來限制存儲過程體。上述存儲過程體本身是一個簡單的select語句。注意這里只是創建存儲過程并沒有進行調用。

儲存過程的使用：

Call productpring()；

使用參數的存儲過程

一般存儲過程并不顯示結果，而是把結果返回給你指定的變量上。

變量：內存中一個特定的位置，用來臨時存儲數據。

MySQL> CREATE PROCEDURE prod(out pl decimal(8,2),out ph decimal(8,2),out pa decimal(8,2))
beginselect Min(prod_price) into pl from products;select MAx(prod_price) into ph from products;select avg(prod_price) into pa from products;end;call PROCEDURE(@pricelow,@pricehigh,@priceaverage);select @pricelow;select @pricehigh;select @pricelow,@pricehigh,@priceaverage;

解釋：

此存儲過程接受3個參數，pl存儲產品最低價，ph存儲產品最高價，pa存儲產品平均價。每個參數必須指定類型，使用的為十進制，關鍵字OUT 指出相應的參數用來從存儲過程傳出一個值(返回給調用者)。

MySQL支持in(傳遞給存儲過程)、out(從存儲過程傳出，這里所用)和inout(對存儲過程傳入和傳出)類型的參數。存儲過程的代碼位于begin和end語句內。他們是一系列select語句，用來檢索值。然后保存到相對應的變量(通過INTO關鍵字)。

存儲過程的參數允許的數據類型與表中使用的類型相同。注意記錄集是不被允許的類型，因此，不能通過一個參數返回多個行和列，這也是上面為什么要使用3個參數和3條select語句的原因。

調用：為調用此存儲過程，必須指定3個變量名。如上所示。3個參數是存儲過程保存結果的3個變量的名字。調用時，語句并不顯示任何數據，它返回以后可以顯示的變量(或在其他處理中使用)。

注意：所有的MySQL變量都是以@開頭。

CREATE PROCEDURE ordertotal(IN innumber int,OUT outtotal decimal(8,2))BEGINSELECT Sum(item_price * quantity) FROM orderitems WHERE order_num = innumber INTO outtotal;end??? //CALL ordertotal(20005,@total);select @total;? // 得到20005訂單的合計CALL ordertotal(20009,@total);select @total; //得到20009訂單的合計

帶有控制語句的存儲過程

??
CREATE PROCEDURE ordertotal(IN onumber INT,IN taxable BOOLEAN,OUT ototal DECIMAL(8,2))COMMENT 'Obtain order total, optionally adding tax'BEGIN-- declear variable for totalDECLARE total DECIMAL(8,2);-- declear tax percentageDECLARE taxrate INT DEFAULT 6;-- get the order totalSELECT Sum(item_price * quantity) FROM orderitems WHERE order_num = onumber INTO total;-- IS this taxable?IF taxable THEN-- yes ,so add taxrate to the totalSELECT total+(total/100*taxrate)INTO total;END IF;-- finally ,save to out variableSELECT total INTO ototal;END;

在存儲過程中我們使用了DECLARE語句，他們表示定義兩個局部變量，DECLARE要求指定變量名和數據類型。它也支持可選的默認值(taxrate默認6%)，因為后期我們還要判斷要不要增加稅，所以，我們把SELECT查詢的結果存儲到局部變量total中，然后在IF 和THEN的配合下，檢查taxable是否為真，然后在真的情況下，我們利用另一條SELECT語句增加營業稅到局部變量total中，然后我們再利用SELECT語句將total(增加稅或者不增加稅的結果)保存到總的ototal中。

COMMENT關鍵字上面的COMMENT是可以給出或者不給出，如果給出，將在SHOW PROCEDURE STATUS的結果中顯示。

觸發器

在某個表發生更改時自動處理某些語句，這就是觸發器。

觸發器是MySQL響應delete 、update 、insert 、位于begin 和end語句之間的一組語句而自動執行的一條MySQL語句。其他的語句不支持觸發器。

創建觸發器

在創建觸發器時，需要給出4條語句（規則）：

1.? 唯一的觸發器名；

2.? 觸發器關聯的表；

3.? 觸發器應該響應的活動；

4.? 觸發器何時執行(處理之前或者之后)

Create trigger 語句創建觸發器

CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added' INTO @info;

CREATE TRIGGER用來創建名為newproduct的新觸發器。觸發器可以在一個操作發生前或者發生后執行，這里AFTER INSERT 是指此觸發器在INSERT語句成功執行后執行。這個觸發器還指定FOR EACH ROW ，因此代碼對每個插入行都會執行。文本Product added 將對每個插入的行顯示一次。

注意：

1、觸發器只有表才支持，視圖，臨時表都不支持觸發器。

2、觸發器是按照每個表每個事件每次地定義，每個表每個事件每次只允許一個觸發器，因此，每個表最多支持六個觸發器(insert，update，delete的before 和after)。

3、單一觸發器不能與多個事件或多個表關聯，所以，你需要一個對insert和update 操作執行的觸發器，則應該定義兩個觸發器。

4、觸發器失敗：如果before 觸發器失敗，則MySQL將不執行請求的操作，此外，如果before觸發器或者語句本身失敗，MySQL則將不執行after觸發器。

觸發器類別

INSERT觸發器

是在insert語句執行之前或者執行之后被執行的觸發器。

1、在insert觸發器代碼中，可引入一個名為new的虛擬表，訪問被插入的行；

2、在before insert觸發器中，new中的值也可以被更新(允許更改被插入的值)；

3、對于auto_increment列，new在insert執行之前包含0，在insert執行之后包含新的自動生成值

CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;

創建一個名為neworder的觸發器，按照AFTER INSERT ON orders 執行。在插入一個新訂單到orders表時，MySQL生成一個新的訂單號并保存到order_num中。觸發器從NEW.order_num取得這個值并返回它。此觸發器必須按照AFTER INSERT執行，因為在BEFORE INSERT語句執行之前，新order_num還沒有生成。對于orders的每次插入使用這個觸發器總是返回新的訂單號。

DELETE觸發器

Delete觸發器在delete語句執行之前或者之后執行。

1、在delete觸發器的代碼內,可以引用一個名為OLD的虛擬表，用來訪問被刪除的行。

2、OLD中的值全為只讀，不能更新。

CREATE TRIGGER deleteorder BEFORE DELETE ON orders FOR EACH ROWBEGININSERT INTO archive_orders(order_num,order_date,cust_id) values (OLD.order_num,OLD.order_date,OLD.cust_id);END;----------------------------------------------------------------CREATE TABLE archive_orders(order_num int(11) NOT NULL AUTO_INCREMENT,order_date datetime NOT NULL,cust_id int(11) NOT NULL,PRIMARY KEY (order_num),KEY fk_orders1_customers1 (cust_id),CONSTRAINT fk_orders1_customers1 FOREIGN KEY (cust_id) REFERENCES customers(cust_id)) ENGINE=InnoDB AUTO_INCREMENT=20011 DEFAULT CHARSET=utf8

在任意訂單被刪除前將執行此觸發器，它使用一條INSERT 語句將OLD中的值(要被刪除的訂單) 保存到一個名為archive_orders的存檔表中(為實際使用這個例子，我們需要用與orders相同的列創建一個名為archive_orders的表)

使用BEFORE DELETE觸發器的優點(相對于AFTER DELETE觸發器來說)為，如果由于某種原因，訂單不能存檔，delete本身將被放棄。

我們在這個觸發器使用了BEGIN和END語句標記觸發器體。這在此例子中并不是必須的，只是為了說明使用BEGIN END 塊的好處是觸發器能夠容納多條SQL 語句(在BEGIN END塊中一條挨著一條)。

UPDATE觸發器

在update語句執行之前或者之后執行

1、在update觸發器的代碼內,可以引用一個名為OLD的虛擬表，用來訪問以前(UPDATE語句之前)的值，引用一個名為NEW的虛擬表訪問新更新的值。

2、在BEFORE UPDATE觸發器中，NEW中的值可能也被用于更新(允許更改將要用于UPDATE語句中的值)

3、OLD中的值全為只讀，不能更新。

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state = Upper(NEW.vemd_state);

保證州名縮寫總是大寫(不管UPFATE語句中是否給出了大寫)，每次更新一行時，NEW.vend_state中的值(將用來更新表行的值)都用Upper(NEW.vend_state)替換。