Mysql----淺入淺出之視圖、預存程序、觸發器，mysql----淺出

Mysql----淺入淺出之視圖、預存程序、觸發器，mysql----淺出

一、視圖 VIEW

視圖是虛擬表，本身不儲存任何資料。只有運行時，才包含動態檢索出來的資料。

eg：SELECT sid, name, sex, s_num FROM  student, school WHERE sid = 2 AND student.sid = scholl.sid ;

這個簡單的查詢涉及到兩個表。所以任何需要這個資料的人都必須熟悉兩個表以及之間的關係。想檢索其他學生資訊，就必須修改WHERE子句。如果可以把整個查詢封裝成一個虛擬表studentinfo，那麼就可以這樣得到資料：

eg：SELECT sid, name, sex, s_num FROM studentinfo WHERE sid = 2;

使用視圖可以重用sql語句。對於使用的人可以不必知道細節。對未經處理資料也有保護作用。

視圖也有一些限制，比如不能索引，不能有關聯的觸發器。名字必須唯一。

建立視圖：

eg：CREATE VIEW studentinfo AS SELECT sid name, sex, s_num FROM student, school WHERE student.sid = school.sid;

視圖也可以更新，但是只在特定的情況下。如果視圖有這些定義，則不能更新:分組、連接、子查詢、並、聚集合函式DISTINCT、計算資料行。

二、預存程序

當需要處理的商務邏輯很複雜的時候，可以一條條的寫sql語句，而且需要考慮到所有的需要處理的細節、資料完整性。可以建立預存程序來代替。它就像一個批處理，包含預先定義好的一條或多條sql語句的集合。但它的作用可不僅限於此。

建立：

eg：CREAT PROCEDURE prostudent() 

BEGIN

SELECT Max(score) AS scoremax FROM student;

END;

刪除：

eg：DROP PROCEDURE prostudent；

使用參數：

eg：CREATE PROCEDURE prostudent(OUT scorehigh DECIMAL(8, 2), OUT scorelow DECIMAL(8, 2))

BEGIN

SELECT Max(score) INTO scorehigh FROM student;

SELECT Min(score) INTO scorelow FROM student;

END;

執行：

eg：CALL prostudent(@scorelow, @scorehigh);

SELECT @scorehigh, @scorelow;

執行了名為prostudent的預存程序，並返回了最高成績和最低成績。

此外，預存程序還可以寫注釋、定義臨時變數、IN傳入參數、流程式控制制語句。

SHOW CREATE PROCEDURE ****; 可以查看建立預存程序的語句。

SHOW PROCEDURE STATUS;可以查看這個預存程序是何時、由誰建立的。

三、遊標

SELECT語句返回的是一個結果集，可能是滿足條件的多個航。那我們想對這個結果集的每一行進行一些處理時，或者在第一行、最後一行、前一行等特殊要求時，怎麼辦呢？這裡就用到裡遊標。mysql中的遊標只能用於預存程序，這是與其他資料庫的不同。

使用遊標需要先定義DECLARE *** CURSOR FOR、然後開啟OPEN ***、使用、關閉CLOSE ***。遊標的生命週期只在預存程序中，也就是如果你不關閉它，當預存程序END時，會自動關閉。

遊標開啟後，可以用FETCH取出一行，並在內部指向下一行的位置，當再次FETCH的時候，將取出下一行。

舉個例子：現在要將所有sid大於3的學生的成績加和。

eg:DELIMITER //

CREATE PROCEDURE sumofscore(OUT sum INT)

BEGIN

DECLARE done BOOLEEAN DEFAULT 0;

DELCARE tmp INT;

DECLARE s INT DEFAULT 0;

DECLARE yb CURSOR FOR SELECT score FROM student WHERE sid > 3;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

OPEN yb;

REPEAT

FETCH yb INTO tmp;

SET s = s + tmp;

UNTIL done END REPEAT;

CLOSE yb;

SELECT s INTO sum;

END

//

DELIMITER ;

DELIMITER 重定義mysql的結束符。02000是資料未找到的錯誤碼，利用它來判斷是否遍曆完所有資料。

四、觸發器

在某些需求下，想要某些語句在特定事件發生時，自動執行，那麼就用到了觸發器。mysql觸發器只能響應delete、insert和update語句。

建立觸發器：

eg：CRESTE TRIGGER newstudent AFTER INSERT ON student FOR EACH ROW SELECT new.sid INTO @s;

觸發器名字是newstudent，INSERT指定響應事件是插入操作。AFTER/BEFORE指定是在事件執行前還是事件執行後觸發。FOR EACH ROW指定對插入的每一行都進行操作，所以每插入一行，就把這行的sid傳給變數s。每個表每個事件只允許有一個觸發器，所以每個表最多有6個觸發器。一個觸發器也只能響應一個事件。

刪除觸發器：

eg：DROP TRIGGER newstudent;

使用觸發器：

在INSERTZHONG ,可引用一個名為NEW的虛擬表，訪問被插入的行。在BEFROE INSERT中，也可以引用NEW，甚至可以更新資料，以改變插入資料的內容。

DELETE觸發器中，可以引用名為OLD的虛擬表，訪問被刪除的行。

mysql中的預存程序、觸發器、視圖的用法

建立預存程序

Create procedure、Create function

下面是它們的格式：
Create proceduresp_Name ([proc_parameter ])
routine_body

這裡的參數類型可以是 IN OUT INOUTT ，意思和單詞的意思是一樣的，IN 表示是傳進來的參數，
OUT 是表示傳出去的參數，INOUT 是表示傳進來但最終傳回的參數。
Create functionsp_Name ([func_parameter ])
Returns type

Routine_body
Returns type 指定了返回的類型，這裡給定的類型與傳回值的類型要是一樣的，否則會報錯。

下面給出兩個簡單的例子來說明：

1、 顯示 Mysql 目前的版本

執行結果
mysql> use welefen;
Database changed
mysql> delimiter // #定義//作為結束標記符號
mysql> create procedure getversion(out param1 varchar(50)) #param1為傳出參數
-> begin
-> select version() into param1; #將版本的資訊賦值給 param1
-> end
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> call getversion(@a); #調用getversion()這個預存程序
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
-> //
+--------------------------+
| @a |
+--------------------------+
| 5.1.14-beta-community-nt |
+--------------------------+
1 row in set (0.00 sec)

2、 顯示”hello world”

執行結果
mysql> delimiter //
mysql> create function display(w varchar(20)) returns varchar(50)
-> begin
-> return concat('hello ‘,w);
-> end
......餘下全文>>
 
sql視圖 預存程序 觸發器各自的優點是什？

視圖的優點：

提高資料安全性，可以不讓使用者看到表中的某個欄位。比如password，你只給他們執行視圖的許可權，不給執行表的許可權，他們就無法查看全部資料。

還有可以建立一個視圖，內容包括兩個表，更新的時候只需要指定ID，而不用管它來自哪個表，對應表中的資料就會自動更新。

預存程序的優點：

包括視圖的所有優點，還可以讓不懂資料庫的人也能也用資料庫，還有就是方便程式計設，比如我負責前景程式設計，你負責寫存程，我不用管你是怎麼寫，最後只接調用，我們分工明確，我也不需要懂你所懂的，這為用不懂語言和不同專業的人在一起合作提供了良好的平台。提高開發效率。

觸發器的優點：

保證資料的正確性和邏輯，比如訂單表中新增一條資料，對應在庫存表中會減少一個產品一樣。
還有保證資料的安全性，比如當使用者刪除表A，我們可以判斷他是否為Admin組的使用者，如果不是，就會給出錯誤的提示，並將交易回復。

我知道的就這麼多了。。。嘿嘿。