目錄
- 視圖
- 觸發器
- 事務
- 存儲過程
- 流程控制
一、視圖
視圖是由查詢結果構成的一張虛拟表，和真實的表一樣，帶有名稱的列和行資料
強調：視圖是永久存儲的，但是視圖存儲的不是資料，隻是一條sql語句
視圖的特點：
- 視圖的列可以來自不同的表，是表的抽象和邏輯意義上建立的新關系。
- 視圖是由基本表（實表）産生的表（虛表）。
- 視圖的建立和删除不影響基本表。
- 對視圖内容的更新（添加、删除和修改）直接影響基本表。
- 當視圖來自多個基本表時，不允許添加和删除資料。
優點：
- 可以簡化查詢（多表查詢轉換為直接通過視圖查詢）
- 可以進行權限控制（把表的權限封閉，開發對應的視圖權限）
（一）、建立視圖
create view 視圖名稱 as sql 查詢語句 例子：CREATE view test_view as SELECT * from test; （二）、查詢視圖
select * from 視圖名 [where 條件] （三）、修改視圖
alter view 視圖名稱 AS SQL語句； 例子：ALTER view test_view as SELECT * from test_view WHERE salary>10000 （四）、删除視圖
drop view 視圖名稱;
例子：drop view test_view 二、觸發器
觸發器可以監視使用者對表的增、删、改操作，并觸發某種操作（沒有查），自動執行，無法直接調用。
建立觸發器文法的四要素：
1.監視地點（table）
2.監視事件（insert/update/delete）
3.觸發時間（before/after）
4.觸發事件（insert/update/delete）
（一）、建立觸發器
# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# 插入後
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 删除後
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新後
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END 文法
#準備表
CREATE TABLE cmd (
id INT PRIMARY KEY auto_increment,
USER CHAR (32),
priv CHAR (10),
cmd CHAR (64),
sub_time datetime, #送出時間
success enum ('yes', 'no') #0代表執行失敗
);
CREATE TABLE errlog (
id INT PRIMARY KEY auto_increment,
err_cmd CHAR (64),
err_time datetime
);
#建立觸發器
delimiter //
CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW
BEGIN
IF NEW.success = 'no' THEN #等值判斷隻有一個等号
INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必須加分号
END IF ; #必須加分号
END//
delimiter ;
#往表cmd中插入記錄，觸發觸發器，根據IF的條件決定是否插入錯誤日志
INSERT INTO cmd (
USER,
priv,
cmd,
sub_time,
success
)
VALUES
('A','0755','ls -l /etc',NOW(),'yes'),
('A','0755','cat /etc/passwd',NOW(),'no'),
('A','0755','useradd xxx',NOW(),'no'),
('A','0755','ps aux',NOW(),'yes');
#查詢錯誤日志，發現有兩條
mysql> select * from errlog;
+----+-----------------+---------------------+
| id | err_cmd | err_time |
+----+-----------------+---------------------+
| 1 | cat /etc/passwd | 2018-09-18 20:18:48 |
| 2 | useradd xxx | 2018-09-18 20:18:48 |
+----+-----------------+---------------------+
2 rows in set (0.00 sec) 案例
強調：NEW表示即将插入的資料行，OLD表示即将删除的資料行
（二）、檢視觸發器
show triggers （三）、删除觸發器
drop trigger 觸發器的名稱 三、事務
事務用于将某些操作的多個SQL作為原子性操作，意思就是，事務是一組sql語句集合。
一旦有某一個出現錯誤，即可復原到原來的狀态，進而保證資料庫資料完整性。在事務内的語句, 要麼全部執行成功, 要麼全部執行失敗。
（一）、事務的特性
事務具有以下四個特性（ACID）
1.原子性：事務是一個整體，不可分割，包含在其中的sql操作要麼全部成功，要麼全部失敗復原，不可能隻執行其中一部分操作。
2.一緻性：當事務執行後 所有的資料都是完整的(外鍵限制 非空限制)。
3.持久性：一旦事務送出，資料永久儲存在資料庫中
4.隔離性：事務之間互相隔離，一個事務的執行不影響其他事務的執行
SQL标準定義了4類隔離級别，包括了一些具體規則，用來限定事務内外的哪些改變是可見的，哪些是不可見的。低級别的隔離級一般支援更高的并發處理，并擁有更低的系統開銷。
（二）、事務的隔離級别
1.READ UNCOMMITED（未送出讀）：所有事務都可以看到其他未送出事務的執行結果。很少用于實際應用，因為它的性能不比其他級别好多少
2.READ COMMITED（送出讀）：大部分資料庫預設級别，不包括mysql。一個事務從開始到送出之前, 所做的任何修改對其他事務都是不可見的。
3.REPEATABLE READ（可重複讀）：mysql預設級别，解決了髒讀的問題. 該級别保證了在同一個事務中多次讀取同樣記錄的結果時一緻的. 無法解決幻讀問題
4.SERIALIZABLE（可串行化）：是最高的隔離級别，強制事務排序，使之不可能互相沖突，進而解決幻讀問題
髒讀： 一個事物 讀到了 另一個事務未送出的資料 查詢 之前要保證 所有的更新都已經完成。
不可重複讀：在一個事務的兩次查詢之中資料不一緻，這可能是兩次查詢過程中間插入了一個事務更新的原有的資料。
幻讀：指的是當某個事務在讀取某個範圍内的記錄時, 另外一個事務又在該範圍内插入了新的記錄, 當之前的事務再次讀取該範圍的記錄時, 會産生幻行(Phantom Row).
（三）、事務操作
start transaction; 開啟一個事物
commit 送出事物
rollback 復原事務 注：mysql預設開啟自動送出事務，pymysql預設是不自動送出，需手動commit
四、存儲過程
存儲過程包含了一系列可執行的sql語句的集合，類似于函數(方法)。
使用存儲過程的優點：
#1. 用于替代程式寫的SQL語句，實作程式與sql解耦
#2. 基于網絡傳輸，傳别名的資料量小，而直接傳sql資料量大 缺點：不友善擴充
（一）、使用存儲過程
建立文法:
create procedure 過程的名稱 ({in,out,inout} 資料類型 參數名稱)
begin
具體的sql代碼
end
參數前面需要指定參數的作用
in 表示該參數用于傳入資料
out 用于傳回資料
inout 即可傳入 也可傳回
參數類型是 mysql中的資料類型
調用文法：
call 存儲過程()
案例:建立一個存儲過程 作用是将兩個整數相加
create procedure add_p (in a int,in b int)
begin
select a + b;
end
//
調用 call add_p(1,2)
案例:建立一個存儲過程 作用是将兩個整數相加 将結果儲存在變量中
定義一個變量
set @su = 100;
create procedure add_p2 (in a int,in b int,out su int)
begin
set su = a + b;
end
//
定義變量 set @su = 100;
調用過程 call add_p2(10,20,@su);
注意 在存儲過程中 需要使用分号來結束一行 但是分号有特殊含義
得将原始的結束符 修改為其他符号
delimiter // 結束符更換為//
delimiter; 案列
在存儲過程中 需要使用分号來結束一行 但是分号有特殊含義
得将原始的結束符 修改為其他符号
delimiter // 結束符更換為//
delimiter; create procedure show_p (in a int)
begin
if a = 1 then
select "壹";
elseif a = 2 then
select "貳";
else
select "other";
end if;
end // 使用存儲過程 完成 輸入 一個 數字 1或2 顯示 壹 或 貳
（二）、删除存儲過程
drop procedure proc_name; 五、流程控制
（一）、條件語句
delimiter //
CREATE PROCEDURE proc_if ()
BEGIN
declare i int default 0;
if i = 1 THEN
SELECT 1;
ELSEIF i = 2 THEN
SELECT 2;
ELSE
SELECT 7;
END IF;
END //
delimiter ; if
（二）、循環語句
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
SELECT
num ;
SET num = num + 1 ;
END WHILE ;
END //
delimiter ; while
delimiter //
CREATE PROCEDURE proc_repeat ()
BEGIN
DECLARE i INT ;
SET i = 0 ;
repeat
select i;
set i = i + 1;
until i >= 5
end repeat;
END //
delimiter ; repeat
BEGIN
declare i int default 0;
loop_label: loop
set i=i+1;
if i<8 then
iterate loop_label;
end if;
if i>=10 then
leave loop_label;
end if;
select i;
end loop loop_label;
END loop
焚膏油以繼晷，恒兀兀以窮年。