• MySQL視圖

一.視圖的概念

1.什麼是視圖：

SQL語句的執行結果是一張虛擬表 我們可以基於該表做其他操作
如果這張虛擬表需要頻繁使用 那麼為了方便可以將虛擬表保存起來 保存起來之後就稱之為"視圖"(本質就是一張虛擬表)

2.視圖的優點：

1）簡單：使用視圖的用戶完全不需要關心後面對應的表的結構、關聯條件和篩選條件，對用戶來說已經是過濾好的複合條件的結果集。

2）安全：使用視圖的用戶只能訪問他們被允許查詢的結果集，對錶的許可權管理並不能限制到某個行某個列，但是通過視圖就可以簡單的實現。

3）數據獨立：一旦視圖的結構確定了，可以屏蔽表結構變化對用戶的影響，源表增加列對視圖沒有影響；源表修改列名，則可以通過修改視圖來解決，不會造成對訪問者的影響。

總而言之，使用視圖的大部分情況是為了保障數據安全性，提高查詢效率。

3.創建視圖：

語法：

CREATE VIEW <視圖名> AS <SELECT語句>;

1、在硬碟中，視圖只有表結構文件，沒有表數據文件
2、視圖通常是用於查詢，儘量不要修改視圖中的數據

總結:視圖能儘量少用就儘量少用

• 觸發器

一.觸發器基本概念：

觸發器是一種特殊類型的存儲過程，它不同於存儲過程，主要是通過事件觸發而被執行的，即不是主動調用而執行的；而存儲過程則需要主動調用其名字執行

觸發器：trigger，是指事先為某張表綁定一段代碼，當表中的數據發生改變（增、刪、改）的時候，系統會自動觸發代碼並執行。

1.創建觸發器

創建語法：

delimiter 自定義結束符號
create trigger 觸發器名字 觸發時間 觸發事件 on 表名 for each row
begin
    -- 觸發器內容主體，sql語句
end
自定義的結束符合
delimiter ;
-- 觸發時間：before/after  表中數據發生改變前的狀態/表中數據發生改變後的狀態
-- 觸發事件：insert/update/delet

2.對觸發器的基礎操作

2.1查看全部觸發器:

show trigger;

2.2刪除觸發器

drop trigger 觸發器名字;

2.3觸發觸發器

觸發不是自動手動觸發的，而是在對應的事件發生後才會觸發。比如下麵創建的觸發器，只有在對錶進行數據操作的時候，觸發器才會執行

先創建兩張表：

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 $$  # 將mysql預設的結束符由;換成$$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
    if NEW.success = 'no' then  # 新記錄都會被MySQL封裝成NEW對象
        insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
    end if;
end $$
delimiter ;  # 結束之後記得再改回來，不然後面結束符就都是$$了

往表cmd中插入記錄，觸發觸發器

INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('kevin','0755','ls -l /etc',NOW(),'yes'),
    ('kevin','0755','cat /etc/passwd',NOW(),'no'),
    ('kevin','0755','useradd xxx',NOW(),'no'),
    ('kevin','0755','ps aux',NOW(),'yes');
查詢errlog表記錄結果：
+----+-----------------+---------------------+
| id   | err_cmd          | err_time               |
+----+-----------------+---------------------+
|  1 | cat /etc/passwd | 2022-08-19 15:31:13|
|  2 | useradd xxx      | 2022-08-19 15:31:13|
+----+-----------------+---------------------+

• 事務

一.事務的四大特征

二.開啟事務的步驟

# 1.創建一個表t1;
+--------+---------+
| uname  | balance |
+--------+---------+
| 王二      |      50   |
| 李四      |     150  |
+--------+---------+
# 2.開啟事務
commit;
# 3.編寫事務中的sql語句（insert、update、delete）這裡實現一下"王二給李五轉賬"的事務過程
update t1 set balance = 30 where uname = "王二";
update t1 set balance = 170 where uname = "李四";
# 4.提交事務
commit;
# 開啟事務檢測操作是否完整，不完整主動回滾到上一個狀態，如果完整就應該執行commit操作
rollback; #回滾事務:就是事務不執行，回滾到事務執行前的狀態

三.拓展知識點

MySQL提供兩種事務型存儲引擎InnoDB和NDB cluster及第三方XtraDB、PBXT

事務處理中有幾個關鍵辭彙會反覆出現
                                      事務(transaction)
                                      回退(rollback)
                                      提交(commit)
                                      保留點(savepoint)
為了支持回退部分事務處理，必須能在事務處理塊中合適的位置放置占位符，這樣如果需要回退可以回退到某個占位符(保留點)
創建占位符可以使用savepoint：
                                     savepoint sp01;
回退到占位符地址：
                                     rollback to sp01;
保留點在執行rollback或者commit之後自動釋放

四.事務併發時出現的問題

因為某一刻不可能總只有一個事務在運行，可能出現A在操作t1表中的數據，B也同樣在操作t1表，那麼就會出現併發問題，

對於同時運行的多個事務，當這些事務訪問資料庫中相同的數據時，如果沒有採用必要的隔離機制，就會發生以下各種併發問題。

五.事務的隔離級別

在SQL標準中定義了四種隔離級別，每一種級別都規定了一個事務中所做的修改
InnoDB支持所有隔離級別

設置事務的隔離級別： set transaction isolation level 級別；

設置全局的隔離級別：set global transaction isolation level 級別；

• MVCC多版本併發控制

MVCC只能在read committed(提交讀)、repeatable read(可重覆讀)兩種隔離級別下工作，其他兩個不相容(read uncommitted:總是讀取最新 serializable:所有的行都加鎖)

InnoDB的MVCC通過在每行記錄後面保存兩個隱藏的列來實現MVCC
一個列保存了行的創建時間
一個列保存了行的過期時間(或刪除時間) # 本質是系統版本號
每開始一個新的事務版本號都會自動遞增，事務開始時刻的系統版本號會作為事務的版本號用來和查詢到的每行記錄版本號進行比較

MVCC解決的問題
前提資料庫併發場景有三種，分別為∶
1)、讀讀∶ 不存在任何問題，也不需要併發控制
2)、讀寫∶有線程安全問題，可能會造成事務隔離性問題，可能遇到臟讀、幻讀、不可重覆讀
3)、寫寫∶ 有線程安全問題，可能存在更新丟失問題
MVCC是一種用來解決讀寫衝突的無鎖併發控制，也就是為事務分配單項增長的時間戳，為每個修改保存一個版本，版本與事務時間戳關聯，讀操作只讀該事務開始前的資料庫的快照。
因此，MVCC可以為資料庫解決以下問題∶
1）、在併發讀寫資料庫時，可以做到在讀操作時不用阻塞寫操作，寫操作也不用阻塞讀操作，提高了資料庫併發讀寫的性能。
2）、解決臟讀、幻讀、不可重覆讀等事務隔離問題，但是不能解決更新丟失問題

• 儲存過程

類似於python中的自定義函數

delimiter 臨時結束符
create procedure 名字(參數,參數)
begin
    sql語句;
end 臨時結束符
delimiter ;

delimiter $$
create procedure p1(
    in m int,  # in表示這個參數必須只能是傳入不能被返回出去
    in n int,  
    out res int  # out表示這個參數可以被返回出去，還有一個inout表示即可以傳入也可以被返回出去
)
begin
    select tname from teacher where tid > m and tid < n;
    set res=0;  # 用來標誌存儲過程是否執行
end $$
delimiter ;


# 針對res需要先提前定義
set @res=10;  定義
select @res;  查看
call p1(1,5,@res)  調用
select @res  查看

"""
查看存儲過程具體信息
    show create procedure pro1;
查看所有存儲過程
    show procedure status;
刪除存儲過程
    drop procedure pro1;
"""

• 內置函數

一.字元串函數

二.日期函數

三.數字函數

• 流程式控制制

一.分支結構之IF

# if條件語句
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 ;

二.迴圈結構之WHILE

# while迴圈
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 ;