在之前的文章中已經總結了一部分,SQL語法知識點,本篇繼續。
8、事務處理
不能回退 SELECT 語句,回退 SELECT 語句也沒意義;也不能回退 CREATE 和 DROP 語句。
MySQL 預設是隱式提交,每執行一條語句就把這條語句當成一個事務然後進行提交。當出現 START TRANSACTION 語句時,會關閉隱式提交;當 COMMIT 或 ROLLBACK 語句執行後,事務會自動關閉,重新恢復隱式提交。
透過 set autocommit=0 可以取消自動提交,直到 set autocommit=1 才會提交;autocommit 標記是針對每個連線而不是針對伺服器的。
指令
START TRANSACTION - 指令用於標記事務的起始點。
SAVEPOINT - 指令用於建立保留點。
ROLLBACK TO - 指令用於回滾到指定的保留點;如果沒有設定保留點,則回退到 START TRANSACTION 語句處。
COMMIT - 提交事務。
—— 開始事務
START TRANSACTION;
—— 插入操作 A
INSERT INTO `user`
VALUES (1, ‘root1’, ‘root1’, ‘xxxx@163。com’);
—— 建立保留點 updateA
SAVEPOINT updateA;
—— 插入操作 B
INSERT INTO `user`
VALUES (2, ‘root2’, ‘root2’, ‘xxxx@163。com’);
—— 回滾到保留點 updateA
ROLLBACK TO updateA;
—— 提交事務,只有操作 A 生效
COMMIT;
9、許可權控制
GRANT 和 REVOKE 可在幾個層次上控制訪問許可權:
整個伺服器,使用 GRANT ALL 和 REVOKE ALL
整個資料庫,使用 ON database。*;
特定的表,使用 ON database。table;
特定的列;
特定的儲存過程。
新建立的賬戶沒有任何許可權。
賬戶用 username@host 的形式定義,
username@% 使用的是預設主機名。
MySQL 的賬戶資訊儲存在 mysql 這個資料庫中。USE mysql;
SELECT user FROM user;
1。 建立賬戶
CREATE USER myuser IDENTIFIED BY ‘mypassword’;
2。 修改賬戶名
UPDATE user SET user=‘newuser’ WHERE user=‘myuser’;
FLUSH PRIVILEGES;
3。 刪除賬戶
DROP USER myuser;
4。 檢視許可權
SHOW GRANTS FOR myuser;
5。 授予許可權
GRANT SELECT, INSERT ON *。* TO myuser;
6。 刪除許可權
REVOKE SELECT, INSERT ON *。* FROM myuser;
7。 更改密碼
SET PASSWORD FOR myuser = ‘mypass’;
10、儲存過程
儲存過程可以看成是對一系列 SQL 操作的批處理;
使用儲存過程的好處
程式碼封裝,保證了一定的安全性;
程式碼複用;
由於是預先編譯,因此具有很高的效能。
建立儲存過程
命令列中建立儲存過程需要自定義分隔符,因為 命令列是以 ; 為結束符,而儲存過程中也包含了分號,因此會錯誤把這部分分號當成是結束符,造成語法錯誤。
包含 in、out 和 inout 三種引數。
給變數賦值都需要用 select into 語句。
每次只能給一個變數賦值,不支援集合的操作。
1。 建立儲存過程
DROP PROCEDURE IF EXISTS `proc_adder`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_adder`(IN a int, IN b int, OUT sum int)
BEGIN
DECLARE c int;
if a is null then set a = 0;
end if;
if b is null then set b = 0;
end if;
set sum = a + b;
END
;;
DELIMITER ;
2。 使用儲存過程
set @b=5;
call proc_adder(2,@b,@s);
select @s as sum;
11、遊標
遊標(cursor)是一個儲存在 DBMS 伺服器上的資料庫查詢,它不是一條 SELECT 語句,而是被該語句檢索出來的結果集。
在儲存過程中使用遊標可以對一個結果集進行移動遍歷。
遊標主要用於互動式應用,其中使用者需要對資料集中的任意行進行瀏覽和修改。
使用遊標的四個步驟:
宣告遊標,這個過程沒有實際檢索出資料;
開啟遊標;
取出資料;
關閉遊標;
DELIMITER $
CREATE PROCEDURE getTotal()
BEGIN
DECLARE total INT;
—— 建立接收遊標資料的變數
DECLARE sid INT;
DECLARE sname VARCHAR(10);
—— 建立總數變數
DECLARE sage INT;
—— 建立結束標誌變數
DECLARE done INT DEFAULT false;
—— 建立遊標
DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age>30;
—— 指定遊標迴圈結束時的返回值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
SET total = 0;
OPEN cur;
FETCH cur INTO sid, sname, sage;
WHILE(NOT done)
DO
SET total = total + 1;
FETCH cur INTO sid, sname, sage;
END WHILE;
CLOSE cur;
SELECT total;
END $
DELIMITER ;
—— 呼叫儲存過程
call getTotal();
12、觸發器
觸發器是一種與表操作有關的資料庫物件,當觸發器所在表上出現指定事件時,將呼叫該物件,即表的操作事件觸發表上的觸發器的執行。
可以使用觸發器來進行審計跟蹤,把修改記錄到另外一張表中。
MySQL 不允許在觸發器中使用 CALL 語句,也就是不能呼叫儲存過程。
BEGIN 和 END
當觸發器的觸發條件滿足時,將會執行 BEGIN 和 END 之間的觸發器執行動作。
⏩ 注意:在 MySQL 中,分號 ; 是語句結束的識別符號,遇到分號表示該段語句已經結束,MySQL 可以開始執行了。因此,直譯器遇到觸發器執行動作中的分號後就開始執行,然後會報錯,因為沒有找到和 BEGIN 匹配的 END。
這時就會用到 DELIMITER 命令(DELIMITER 是定界符,分隔符的意思)。它是一條命令,不需要語句結束標識,語法為:DELIMITER new_delemiter。new_delemiter 可以設為 1 個或多個長度的符號,預設的是分號 ;,我們可以把它修改為其他符號,如 $ - DELIMITER $ 。在這之後的語句,以分號結束,直譯器不會有什麼反應,只有遇到了 $,才認為是語句結束。注意,使用完之後,我們還應該記得把它給修改回來。
NEW 和 OLD
MySQL 中定義了 NEW 和 OLD 關鍵字,用來表示觸發器的所在表中,觸發了觸發器的那一行資料。
在 INSERT 型觸發器中,NEW 用來表示將要(BEFORE)或已經(AFTER)插入的新資料;
在 UPDATE 型觸發器中,OLD 用來表示將要或已經被修改的原資料,NEW 用來表示將要或已經修改為的新資料;
在 DELETE 型觸發器中,OLD 用來表示將要或已經被刪除的原資料;
使用方法:NEW。columnName (columnName 為相應資料表某一列名)
1。 建立觸發器
⏩ 提示:為了理解觸發器的要點,有必要先了解一下建立觸發器的指令。
CREATE TRIGGER 指令用於建立觸發器。
語法:
CREATE TRIGGER trigger_name
trigger_time
trigger_event
ON table_name
FOR EACH ROW
BEGIN
trigger_statements
END;
說明:
trigger_name:觸發器名
trigger_time: 觸發器的觸發時機。取值為 BEFORE 或 AFTER。
trigger_event: 觸發器的監聽事件。取值為 INSERT、UPDATE 或 DELETE。
table_name: 觸發器的監聽目標。指定在哪張表上建立觸發器。
FOR EACH ROW: 行級監視,Mysql 固定寫法,其他 DBMS 不同。
trigger_statements: 觸發器執行動作。是一條或多條 SQL 語句的列表,列表內的每條語句都必須用分號 ; 來結尾。
示例:
DELIMITER $
CREATE TRIGGER `trigger_insert_user`
AFTER INSERT ON `user`
FOR EACH ROW
BEGIN
INSERT INTO `user_history`(user_id, operate_type, operate_time)
VALUES (NEW。id, ‘add a user’, now());
END $
DELIMITER ;
2。 檢視觸發器
SHOW TRIGGERS;
3。 刪除觸發器
DROP TRIGGER IF EXISTS trigger_insert_user;
學習IT相關內容,找“職座標線上”