mysql-檢視、觸發器、事務、儲存過程、流程控制

摘要： 目錄 檢視 觸發器 事務 儲存過程 流程控制 一、檢視 檢視是由查詢結果構成的一張虛擬表，和真實的表一樣，帶有名稱的列和行資料 強調：檢視是永久儲存的，但是檢視儲存的不是資料，只是一條sql語句 檢視的特點： ...

目錄

1. 檢視
2. 觸發器
3. 事務
4. 儲存過程
5. 流程控制

一、檢視

檢視是由查詢結果構成的一張虛擬表，和真實的表一樣，帶有名稱的列和行資料

強調：檢視是永久儲存的，但是檢視儲存的不是資料，只是一條sql語句

檢視的特點：

1. 檢視的列可以來自不同的表，是表的抽象和邏輯意義上建立的新關係。 
2. 檢視是由基本表（實表）產生的表（虛表）。
3. 檢視的建立和刪除不影響基本表。 
4. 對檢視內容的更新（新增、刪除和修改）直接影響基本表。 
5. 當檢視來自多個基本表時，不允許新增和刪除資料。

優點：

1. 可以簡化查詢（多表查詢轉換為直接通過檢視查詢）
2. 可以進行許可權控制（把表的許可權封閉，開發對應的檢視許可權）

（一）、建立檢視

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;
呼叫過程 calladd_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