• 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 ;

標籤：MySQL

上一篇：Mysql的讀寫分離中間件該怎么寫？聽我來說。

下一篇：Debezium的基本使用（以MySQL為例）