知識星球
儲存過程
為以後的使用而儲存的一條或多條mysql語句的集合。模糊上可將其視為批檔案。
它是函式。對，就相當於C語言中的函式、c++/java中的方法。可以呼叫，提高SQL執行效率。
一般情況下，普通許可權的使用者只有使用儲存過程的許可權，而沒有編寫的許可權，對，儲存過程也是為了使用者查詢方便（下面會說到“建立智慧場景”），同樣是面向使用者考慮的。
執行儲存過程：mysql稱儲存過程的執行為呼叫，因此mysql執行儲存過程的語句為CALL。CALL接收儲存過程的名字以及需要傳遞給他的任意引數。如下為某程式執行儲存過程：
CALL productpricing(@pricelow, @pricehigh, @priceaverage);
其中，返回產品的最低（pricelow）、最高（pricehigh）、和平均價格（priceaverage）。
建立儲存過程：直接上命令：
create procedure pro()
begin
select avg(prod_price) as priceaverage
from products;
end;
此儲存過程名為Pro，用create procedure pro()定義。如果儲存過程接收引數，它們將在（）中列舉出來。
其中，begin和end語句用來限定儲存過程體，過程體僅僅是一個select語句。
如果你使用的是mysql命令列實用程式，應注意：mysql命令列實用程式和mysql預設的一樣，將“;”視為分隔符，它們最終不會稱為儲存過程的成分，這會使SQL語句錯誤。解決辦法：在開頭加一行：delimiter // ——告訴命令列實用程式用//作為新的語句結束分隔符，然後將end處的;改為//，最後再加一行：delimiter ;——為回覆原來的語句分隔符
然後，如何使用？
CALL pro();
執行剛建立的儲存過程；（一般在這裡括號裡傳入變數）
為什麼Pro後面要有（）？——儲存過程實際上是一種函式。
刪除儲存過程
drop procedure pro;
請注意，沒有使用後面的（），只給出儲存過程名。
but，如果指定過程不存在，則上面語句將會錯誤。我們可以這樣使用：drop procedure if exists; （關於 if exists，後面會再見到，其在建立智慧儲存過程中應用廣泛）
使用引數：一般，儲存過程並不顯示結果，而是把結果返回給你指定的變數。
變數：記憶體中一個特定的位置，用來臨時儲存資料。
以下是Pro的修改版本：
create procedure pro(
out p1 decimal(8,2),
out p2 decimal(8,2),
out p3 decimal(8,2)
)
begin
select min(prod_price)
into p1
from products;
select max(prod_price)
into p2
from products;
select avg(prod_price)
into p3
from products;
end;
此過程接收三個引數：p1儲存產品最低價格，p2儲存產品最高價格，p3儲存產品平均價格。每個引數必須有指定的型別。關鍵字out指出相應的引數來從儲存過程中傳出一個值（返回給呼叫者）。
mysql支援IN（傳遞給儲存過程）、out（從儲存過程中傳出）、和INOUT（對儲存過程傳入和傳出）型別的引數
IN輸入引數:表示該引數的值必須在呼叫儲存過程時指定，在儲存過程中修改該引數的值不能被返回，為預設值
OUT輸出引數:該值可在儲存過程內部被改變，並可返回
INOUT輸入輸出引數:呼叫時指定，並且可被改變和返回
Ⅰ.IN引數例子
建立:
mysql > DELIMITER //
mysql > CREATE PROCEDURE demo_in_parameter(IN p_in int)
-> BEGIN
-> SELECT p_in;
-> SET p_in=2;
-> SELECT p_in;
-> END;
-> //
mysql > DELIMITER ;
執行結果:
mysql > SET @p_in=1;
mysql > CALL demo_in_parameter(@p_in);
+------+
| p_in |
+------+
| 1 |
+------+
+------+
| p_in |
+------+
| 2 |
+------+
mysql> SELECT @p_in;
+-------+
| @p_in |
+-------+
| 1 |
+-------+
以上可以看出，p_in雖然在儲存過程中被修改，但並不影響@p_id的值
Ⅱ.OUT引數例子
建立:
mysql > DELIMITER //
mysql > CREATE PROCEDURE demo_out_parameter(OUT p_out int)
-> BEGIN
-> SELECT p_out;
-> SET p_out=2;
-> SELECT p_out;
-> END;
-> //
mysql > DELIMITER ;
執行結果:
mysql > SET @p_out=1;
mysql > CALL sp_demo_out_parameter(@p_out);
+-------+
| p_out |
+-------+
| NULL |
+-------+
+-------+
| p_out |
+-------+
| 2 |
+-------+
mysql> SELECT @p_out;
+-------+
| p_out |
+-------+
| 2 |
+-------+
Ⅲ.INOUT引數例子
建立:
mysql > DELIMITER //
mysql > CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)
-> BEGIN
-> SELECT p_inout;
-> SET p_inout=2;
-> SELECT p_inout;
-> END;
-> //
mysql > DELIMITER ;
執行結果:
mysql > SET @p_inout=1;
mysql > CALL demo_inout_parameter(@p_inout) ;
+---------+
| p_inout |
+---------+
| 1 |
+---------+
+---------+
| p_inout |
+---------+
| 2 |
+---------+
mysql > SELECT @p_inout;
+----------+
| @p_inout |
+----------+
| 2 |
+----------+
如前所見，儲存過程是一系列select語句，用來檢索值，透過INTO關鍵字儲存到相應變數。
mysql中有全域性變數和區域性變數兩種：
1.區域性變數：以關鍵字DECLARE宣告，後跟變數名和變數型別，如：declare a int
也可以用關鍵字DEFAULT為變數指定預設值，如：declare a int default 10
2.全域性變數：mysql中的會話變數即為全域性變數，會話變數在整個過程中有效，其以字元“@”起始，如：
delimiter //
create procedure p2()
begin
set @t=1;
begin
set @t=2;
select @t;
end;
select @t;
end;
delimiter //
為了呼叫修改過的儲存過程，必須指定3個變數名，即：
CALL productpricing(@pricelow, @pricehigh, @priceaverage);
所有mysql變數都必須以@開始。
由於篇幅原因，具體事宜可關注我其他博文，在此不詳說。。。
當然，這其中最重要的就是，它支援各種條件語句，如：IF語句（首先判斷IF後條件是否為真，為真時執行後面THEN後的語句，為假則繼續判斷）、CASE語句（首先從WHEN後的value中查詢與CASE後的value相等的值，若找到則執行該分支的內容）、WHILE迴圈語句（首先判斷condition條件是否為真，為真時執行迴圈體）、LOOP迴圈語句（LOOP允許某特定於巨的重複執行，實現一個簡單的迴圈構造，退出迴圈用LEAVE語句）、REPEAT迴圈語句（先執行一次迴圈體，然後判斷condition條件是否為真，為真時退出迴圈）
示例：
1.LOOP
delimiter //
create procedure edxloop(out sum int)
begin
declare i int default 1;
declare s int default 0;
loop_lable:loop
set s=s+i;
set i=i+1;
if i>100 then
leave loop_lable;
end if;
end loop;
set sum=s;
end
delimiter //
call exloop(@s);
.................................
select @s;
儲存函式：和儲存過程一樣的套路，只是，create後面的procedure變成了function。如下：
delimiter //
create function name_of_student(std_id int)
return varchar(15)
begin
return(select name from syidentinfo where std=std_id);
end
delimiter //
兩個 return 缺一不可，第一個返回的是引數的型別，第二個是查詢結果。
呼叫儲存過程和儲存函式：1.呼叫儲存過程：前面提到過call，yes，透過前面的示例想必這一點完全不必多說，OK，To proceed to the next item!
2.呼叫儲存函式：直接select 函式名（引數）;即可
檢視儲存過程和儲存函式的定義：SHOW CREATE語句
show create procedure/function 名;
檢視儲存函式和儲存過程的狀態：如：show procedure status like ‘film_in_stock’; (檢視過程film_in_stock的資訊)
刪除儲存過程和儲存函式：drop procedure/function [IF EXISTS] 名;
觸發器
上面“儲存函式”中的“DELIMITER //”與建立觸發器時是一樣的，故而在此說說觸發器。
先說說觸發器吧，其實學過java的人都知道里面有一個可謂是“神往已久”的東西，叫“監視器”，常見如：滑鼠監視器，可以實現滑鼠操作；時間監視器、顏色監視器等。。。
而資料庫中的觸發器和這原理差不多：在執行某項“需要監視的操作”時設定觸發器，在執行時引發觸發器，讓系統完成既定的程式。
建立觸發器：
CREATE TRIGGER 觸發器名 BEFORE|AFTER 觸發事件 ON 表名 FOR EACH ROW 執行語句
before和after：指定觸發器執行的時間（在觸發時間之前還是之後）；
for each row：表示任何一條記錄上的操作滿足觸發事件都會觸發該觸發器；
執行語句：指觸發器被觸發後執行的程式
例如：建立一個名為timelog的表
create table timelog(
id int primary key auto_increment,
savetime varchar(50) not null
);
建立名為save_time的觸發器
delimiter //
create trigger save_time before insert
on studentinfo for each row
insert into timelog(savetime) values(now());
delimiter //
insert那一句放入的是“now（）”，所以講返回一條訊息：執行這條操作的時間！
這就是觸發器的作用。
上面是單一事件，若是多個事件怎麼辦？
在上面建立的timelog的基礎上，另外建立一個名為timeinfo的資料表
create table timeinfo(
id int primary key,
info varchar(50) not null
);
然後建立一個由DELETE觸發的多個語句的觸發器，如下：
delimiter //
create trigger delete_time_info after delete
on studentinfo for each row
begin
insert into timelog(savetime) values(now());
insert into timeinfo(info) values(now());
end
delimiter //
執行刪除操作的程式碼（觸發觸發器的命令）為：
DELETE FROM studentinfo where name='Chirs';
然後，用select語句分別檢視timelog資料表和timeinfo資料表，可看到返回結果。
檢視觸發器：用SHOW TRIGGERS;檢視觸發器基本資訊；
刪除觸發器：用 DROP TRIGGER 觸發器名稱;
來看一下觸發器的完整樣例吧：
一、建立MySQL例項資料表：
在mysql的預設的測試test資料庫下，建立兩個表t_a與t_b：
/*Table structure for table `t_a` */
DROP TABLE IF EXISTS `t_a`;
CREATE TABLE `t_a` (
`id` smallint(1) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) DEFAULT NULL,
`groupid` mediumint(8) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;
/*Data for the table `t_a` */
LOCK TABLES `t_a` WRITE;
UNLOCK TABLES;
/*Table structure for table `t_b` */
DROP TABLE IF EXISTS `t_b`;
CREATE TABLE `t_b` (
`id` smallint(1) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) DEFAULT NULL,
`groupid` mediumint(8) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=57 DEFAULT CHARSET=latin1;
/*Data for the table `t_b` */
LOCK TABLES `t_b` WRITE;
UNLOCK TABLES;
在t_a表上分建立一個CUD（增、改、刪）3個觸發器，將t_a的表資料與t_b同步實現CUD，注意建立觸發器每個表同類事件有且僅有一個對應觸發器，為什麼只能對一個觸發器，不解釋啦，看MYSQL的說明幫助文件吧。
MySQL事務
先說，mysql變數真是一個神奇的東西，不僅可以透過儲存過程和儲存函式模擬c/c++/java的函式（方法），mysql中還增加了對變數的檢視(select @變數)。
這和我們要說的事務有什麼關係呢？ 沒有。
在mysql中，事務由單獨單元的一個或多個SQL語句組成，在這個單元中，每個mysql語句相互依賴。
再先說一下，事務處理資料有什麼應用？
網購，使用者登入網站，瀏覽資訊，將喜歡的商品放入購物車，然後線上支付，當用戶付款完畢，通知商家發貨，注意，此時商家是沒有接到貨款的，當用戶收到貨物時，點選確認收貨，商家收到貨款，整個交易完成。試想當用戶選擇商品後，在發貨過程中選擇取消訂單，這是商家並沒有得到貨款將取消操作，如果不用事務處理，則使用者取消操作後，商家仍然給使用者發貨，會造成一些不愉快。故而在整個交易過程中，必須採取事務回滾操作。
簡單來說，事務就是給操作過程加一個“保險”。
提示：mysql事務只針對InnoDB和BDB型別表，而我們平時建立表時預設的是MyISAM型別表。
原子性：即事務的整體性和不可分割性，所有事務共進退。或者說，原子的執行是一個全部發生或全部失敗的整體過程。在一個原子操作中，如果事務的任何一個語句失敗，前面執行的語句都將被返回，一保證資料的整體性不被破壞。
一致性：基於mysql的日記處理機制，記錄資料庫的所有變化，為事務恢復提供跟蹤記錄。如：使用者A向用戶B轉賬5000元，使用者B卻發現自己賬戶只增加了3000元，是不是很桑心。。。
永續性：提交的事務即使mysql系統崩潰了仍然堅持執行。當一個事務完成，資料庫的日誌已經被更新時，永續性即可發揮其特有功效。而且，在mysql中，如果系統崩潰或者資料儲存介質被破壞，透過使用日誌，系統能夠恢復在重啟前進行的最後一次成功更新。
孤立性
預設情況下，InnoDB表的永續性最久。
事務的創立
過程有點麻煩：初始化事務、建立事務、應用select語句查詢資料是否被錄入、提交事務。
1.建立一個表：create table table_name(field-defintions) type=INNODB/BDB;
table_name是表名，field-defintions是表內欄位
若使用者希望將已經存在的表支援事務處理，可應用ALTER命令指定：alter table table_name type=INNODB/BDB;
2.初始化事務：start transaction;
3.建立事務：初始化成功後，向表中插入資料，如：
insert into connection(email,cellphone,qq,sid) values('mrsoft@mrsoft.com',123456789,8975645,3);
3.檢視：如：select * from connection where sid=3;
4.提交事務：在使用者沒有提交事務前，當其他使用者連線mysql伺服器時，用select查詢則不會顯示增加的事務。
提交事務命令：COMMIT
撤銷事務（事務回滾）：ROLLBACK命令。如果執行回滾操作，則在輸入start transaction命令後的所有SQL語句都將執行回滾操作。
如果使用者提交事務後，沒有提交事務，則事務預設為自動回滾狀態。
那事務回滾怎麼使用呢？我們需要設定一個savepoint變數，我稱之為：回滾標籤。需要回滾時，只需rollback到這個標籤的地方即可。如：
savepoint test;
...
...
rollback to savepoint test;
事務不支援巢狀使用，當用戶在未結束第一個事務而又重新開啟一個事務時，前一個事務會自動提交，同樣mysql命令中很多命令都會隱藏執行COMMIT命令；
MySQL行為
1.關於mysql自動提交：如果使用者不希望透過控制MySQL自動提交引數，可以更改提交模式，如：使用“SET AUTOCOMMIT=0;”命令關閉自動提交引數，此後，只有當用戶輸入COMMIT後，mysql才能將資料表中的資料提交到資料庫中。
我們可以透過檢視（select）“@@AUTOCOMMIT”變數來檢視當前自動提交狀態。
mysql孤立級
1.基於ANSI/ISO SQL規範，MySQL提供4中孤立級，分別是：SERIALIZABLE（序列化）、REPEATABLE READ（可重讀）、READ COMMITTED（提交後讀）、READ UNCOMMITTED（未提交讀）。
2.修改事務的孤立級：mysql預設的孤立級為“可重讀”，使用者可以透過命令：set global transaction isolation level 孤立級名;來修改級別；可 用select命令獲取當前事務的孤立級變數的值：select @@tx_isolation;
3.如果一般使用者想要修改事務的孤立級，必須首先獲取SUPER許可權。
表鎖定？——MyISAM中模擬InnoDB/BDB事務的東西
簡稱，偽事務。
用途：避免使用者操作資料庫過程中受到干擾。
設定表鎖定代替事務的基本步驟如下：
（1）為指定表新增鎖定：LOCK TABLES table_name lock_type,……
其中，lock_type是指鎖定型別，分為兩種：以讀方式（READ）和以寫方式（WRITE）
一.以讀方式：鎖定使用者其他方式操作（如：刪除，插入，更新）
以studentinfo表為例：lock table studentinfo read;
執行此步後，再向表中插入資料，會報錯！
二.以寫方式：設定使用者可以修改表中資料，但是除了自己以外其他會話中的使用者不能進行任何讀操作。
lock table studentinfo write;
此後，進行select * from studentinfo時，將不會顯示結果。