六天帶你玩轉Mysql筆記–第六天

六天帶你玩轉Mysql筆記–第六天

1.
事務安全

1.1
事務操作

1.1.1手動事務操作流程

1.1.2
事務操作原理

1.1.3
回滾點

1.1.4
自動事務處理

1.1.5
事務特性

1.1.6
鎖機制

2.
變數

2.1
系統變數

2.1.1
檢視系統變數

2.2
修改系統變數

2.2
自定義變數

2.2.1
自定義變數

3
觸發器

3.1建立觸發器

3.2
檢視觸發器

3.3
使用觸發器

3.4
修改觸發器&刪除觸發器

3.5
觸發器記錄

4.
程式碼執行結構

4.1
分支結構

4.2
迴圈結構

5.
函式

5.1
系統函式

5.2
自定義函式

5.2.1
建立函式

5.2.2
檢視函式

5.2.3
修改函式&刪除函式

5.2.4
函式引數

5.2.5
作用域

6
儲存過程

6.1
建立過程

6.2
檢視過程

6.3
呼叫過程

6.4
修改過程&刪除過程

6.5
過程引數

 

1. 事務安全

(1)事務:一系列要發生的連續的操作。

(2)事務安全:一種保護連續操作同時滿足(實現)的一種機制。

(3)事務安全的意義:保證資料操作的完整性。

1.1
事務操作

事務操作分為兩種:自動事務(預設的),手動事務

1.1.1手動事務操作流程

(1)開啟時事務:告訴系統以下所有的操作(寫)不要直接寫入到資料表,先存放到事務日誌smart
transaction。


開啟事務

start transaction;

(2)進行事務操作:一系列操作

1)李四賬戶減少1000

2)張三賬戶增多1000


事務操作1:李四的賬戶減少1000

update my_account set money = money – 1000 where id = 2;


事務操作2:張三賬戶增加1000

update my_account set money = money 1000 where id = 1;

 

(3)關閉事務:選擇性的將日誌檔案中操作的結果儲存到資料表(同步)或者直接清除事務日誌(原來操作全部清空)

1)提交事務:同步資料表(操作成功):commit

 

2)回滾事務:直接清空日誌表(操作失敗):rollback

 

1.1.2
事務操作原理

事務開啟後,所有的操作都會臨時儲存到事務日誌,事務日誌只有得到commit命令才會同步到資料表,其他任何情況都會清空(rollback,斷電和斷開連線)。

 

 

1.1.3
回滾點

(1)定義:在某個成功的操作完成之後,後續的操作有可能成功有可能失敗,但是不管成功還是失敗,前面的操作都已經成功。可以再在當前成功的位置,設定一個點。可以供後續失敗操作返回到該位置,而不是返回所有操作,這個點稱之為回滾點。

(2)設定回滾點語法:savepoint
回滾點名字;

(3)回到回滾點語法:rollback
to 回滾點名字;

 

1.1.4
自動事務處理

(1)在mysql中,預設的都是自動事務處理,使用者操作完成後會立即同步到資料庫。

(2)自動事務:系統通過autocommit變數控制

Show variables like
‘autocommit’;

 

(3)關閉事務自動提交

Set autocommit = off/0;

(4)自動關閉後,需要手動來選擇處理:commit/rollback

注意:通常開啟自動事務。

 

1.1.5
事務特性

事務有四大特性:A C I D

1)Atomic:原子性,事務的整個操作就是一個整體,不可分割,要麼全部成功,要麼全部失敗。

2)Consistency:一致性,事務操作的前後,資料表中的資料沒有變化。

3)Isolation:隔離性,事務操作時互相隔離不受影響。

4)Durability:永續性,資料一旦提交,永久改變資料表資料。

 

1.1.6
鎖機制

(1)Innodb預設是行鎖,但是如果在事務操作的過程中,沒有使用到索引,那麼系統會自動全表檢索資料,自動升級為表鎖。

(2)行鎖:只有當前行被鎖住,別的使用者不能操作

(3)表鎖:整張表被鎖住

 

 

2. 變數

變數分為:系統變數和自定義變數

 

2.1
系統變數

系統定義的變數,大部分的時候使用者根本不需要使用系統變數,系統變數時用來控制伺服器的表現,如autocommit,auto_increment等

2.1.1
檢視系統變數

Show  variables; –
檢視所有系統變數

檢視具體變數值:任何一個有資料返回的內容都是有select檢視

Select @@系統變數名;

 

2.2
修改系統變數

(1)修改系統變數分為兩種方式:會話級別和全域性級別

(2)會話級別:臨時修改,當前客戶端當次連線有效

Set
變數名 =
值;

Set @@變數名
= 值;

(3)全域性級別:一次修改,永久生效(對所有客戶端都生效)

Set global
變數名;

注:如果其他客戶端當前已經連線上伺服器,那麼當次修改無效,要重新登入才會生效。

2.2
自定義變數

2.2.1
自定義變數

(1)系統為了區分系統變數,規定使用者自定義變數必須使用一個@符號

Set @變數名
=/:= 值;

(2)自定義變數檢視

Select @變數名;

(3)在mysql中,”=”會預設的當做比較符號處理,mysql為了區分比較和賦值的概念增加了一個賦值符號:”:=”

(4)Mysql允許資料表中獲取資料,然後賦值給變數

方案1:邊賦值,邊檢視結果

程式碼:

select @name
= name,name from my_student;

 

程式碼:

select @name := name,name from my_student;

select @name;

 

方案2:只有賦值不看結果(1)要求很嚴格(2)資料記錄最多隻允許一條(3)Mysql不支援陣列

Select
欄位列表 from
表名 into
變數列表;

程式碼:

select name,age from my_student where id =2 into @name,@age;

效果:

 

(5)所有自定義的變數都是會話級別:當前客戶端當次連線有效

(6)所有自定義變數不區分資料庫(使用者級別)

3
觸發器

(1)觸發器(trigger):事先為了某張表繫結好一段程式碼,當表中的某些內容發生改變的時候(增刪改),系統會自動觸發程式碼執行。

(2)觸發器:事情型別,觸發時間,觸發物件

1)事件型別:增刪改,三種型別(insert,delete和update)

2)觸發時間:前後(before和after)

3)觸發物件:表中的每一條記錄(行)

(3)一張表中只能擁有一種觸發時間的一種型別的觸發器(不重複),即一張表最多能有6個觸發器。

 

3.1建立觸發器

(1)在mysql高階結構中,沒有大括號,都是對應的字元符號代替

(2)觸發器基本語法


臨時修改語句結束符

Delimiter
自定義符號 –後續程式碼中只有碰到自定義符號才算結束

Create trigger
觸發器名字 觸發時間 事件型別 on
表 for each row

Begin
 —
代表左大括號:開始


裡面就是觸發器的內容,每行內容都必須使用語句結束符:分號

End
   –代表右大括號:結束

自定義符號  —
語句結束符


將臨時修改修正過來

Delimiter ;

程式碼:


觸發器:訂單生成一個,商品庫存減少一個

delimiter $$

create trigger after_order after insert on my_order for each row

begin

 —
觸發器開始內容

 update my_goods set inv = inv-1 where id = 2;

 end

 $$

 delimiter ;

 

 

3.2
檢視觸發器

(1)檢視所有觸發器:show triggers [like
‘pattern’];

 

(2)可以檢視觸發器建立語句

Show create trigger
觸發器名字;

 

(3)所有的觸發器都會儲存一張表:information_schema
triggers;

 

3.3
使用觸發器

觸發器:不需要手動呼叫,而是當某種情況發生時回自動觸發。

 

3.4
修改觸發器&刪除觸發器

Drop trigger
觸發器名;

 

3.5
觸發器記錄

(1)觸發器記錄:不管觸發器是否觸發了,只要當某種操作準備執行,系統就會將“當前要操作的記錄的當前狀態”和“即將執行之後的新狀態”分別保留下來,供觸發器使用。其中,要操作的當前狀態儲存到old中,操作之後的可能形態儲存給new。

(2)Old代表的是舊記錄,new代表的是新記錄

刪除的時候是沒有new的;

插入的時候是沒有old的;

(3)Old和new都是代表記錄本身:任何一條記錄除了有資料,還有欄位名字。

(4)使用方式:old
欄位名/ new
欄位名;


觸發器:訂單生成,商品庫存減少

delimiter $$

create trigger after_order after insert on my_order for each row

begin

 —
觸發器開始內容

 update my_goods set inv = inv-new.g_number where
id = new.g_id;

 end

 $$

 delimiter ;

 

4. 程式碼執行結構

程式碼執行三種結構:順序結構,分支結構和迴圈結構

 

4.1
分支結構

(1)分子結構:事先準備多個程式碼塊,按照條件選擇性執行某段程式碼塊

(2)If分支基本語法:

If
條件判斷 then

   —
滿足條件執行的程式碼

Else


不滿足條件執行的程式碼

End if

 

4.2
迴圈結構

(1)迴圈結構:某段程式碼在指定條件執行重複迴圈

(2)基本語法(while迴圈,沒有for迴圈)

While
條件判斷 do

 —
滿足條件要執行的迴圈程式碼

 —
變更迴圈條件

End while;

(3)迴圈控制:在迴圈內部進行迴圈判斷和控制

Mysql中沒有對應continue和break,但是有替代品

Iterate:迭代,型別continue:後面的程式碼不執行,迴圈重新來

Leave:離開,類似break

(4)使用方式:iterate/leave
迴圈名字


定義迴圈名字

迴圈名字:while
條件 do


迴圈體


迴圈控制

Leave/iterate
迴圈名字;

End while;

 

5. 函式

將一段程式碼塊封裝到一個結構中,在需要執行程式碼塊的時候,呼叫結構執行即可(程式碼複用)

 

5.1
系統函式

(1)系統定義好的函式,直接呼叫即可。

(2)任何函式都有返回值,因此函式的呼叫時通過select呼叫。

(3)Mysql中,字串的基本操作單位(最常見的是字元)

1)Substring
字串擷取

set @cn = ‘世界你好’;

set @en = ‘hello world’;


字串擷取

select substring(@cn,1,1);

select substring(@en,1,1);

 

2)Char_length:字元長度

Length:位元組長度

select char_length(@cn),char_length(@en),length(@cn),length(@en);

 

3)Instr:判斷字串是否在某個具體的字串中存在,存在返回位置

 

4)Lpad:左填充,將字串按照某個指定的填充方式,填充到指定長度(字元)

select lpad(@cn,20,’歡迎’),lpad(@en,20,’hello’);

 

5)Insert:替換,找到指定長度的字串,替換成目標字串

6)Strcmp:compare,字串比較

 

5.2
自定義函式

函式要素:函式名,引數列表(形參和實參),返回值,函式體(作用域)

 

5.2.1
建立函式

(1)建立語法:

Create function
函式名([引數列表]) returns
資料型別 –規定返回資料型別

Begin


函式體


返回值:return型別(指定資料型別)

End

(2)自定義函式與系統函式呼叫方式是一樣:select
函式名(實參列表)

 

5.2.2
檢視函式

檢視所有函式:show function status [like
‘patren’];

檢視函式的建立語句:show create function
函式名;

 

5.2.3
修改函式&刪除函式

(1)函式只能先刪除後新增,不能修改。

Drop function
函式名;

 

5.2.4
函式引數

(1)引數分為兩種:定義時的引數叫做形參,呼叫時的引數叫實參(實參可以是數值也可以變數)。形參:要求必須指定資料型別。

Function
函式名(形參名字 欄位型別)returns
資料型別

(2)在函式內部使用@定義的變數在函式外部也可以訪問。

 

 

5.2.5
作用域

(1)Mysql中的作用域與js中的作用域完全一樣

(2)全域性變數可以在任何地方使用;區域性變數只能在函式內部使用。

(3)全域性變數:使用set關鍵字定義,使用@符號標誌

(4)區域性變數:使用declare關鍵字宣告,沒有@符號;所有的區域性變數的宣告,必須在函式體的開始之前定義。

delimiter @@

create function display3(int_1 int) returns int

 

begin

     —
定義區域性變數

declare res int default 0;

declare num int default 1;


迴圈判斷

while num <= int_1 do

if num%5!=0 then


相加

set
res = res num;

end if;


改變迴圈變數

set num = num 1;

end while;

return res;

end

@@

delimiter ;

 

 

6
儲存過程

儲存過程簡稱過程(procedure),是一種用來處理資料的方式。

儲存過程是一種沒有返回值的函式。

6.1
建立過程

Create procedure
過程名字([引數列表])

Begin


過程體

End

6.2
檢視過程

檢視所有過程:show procedure status [like
‘patern’];

6.3
呼叫過程

Call
過程名;

6.4
修改過程&刪除過程

過程只能先刪除,後新增

Drop procedure
過程名;

6.5
過程引數

(1)函式的引數需要資料型別指定,過程比函式更嚴格。過程有自己的型別限定:三種型別

1)In:資料只是從外部傳入給內部使用(值傳遞,變數或數值)

2)Out:只允許過程內部使用(不用外部資料),給外部使用的只能是變數(引用傳遞:外部的資料會被先清空再進入到內部)

3)Inout:外部可以在內部使用,內部修改液可以給外部使用(典型的引用傳遞:只能傳變數)

(2)基本使用

Create procedure
過程名(in
形參名字 資料型別,out
形參名字 資料型別,inout
形參名字 資料型別)

 

(3)呼叫:out和inout傳入的必須是變數

(4)儲存過程對於變數的操作(返回)是滯後的:是在儲存過程呼叫結束的時候,才會重新將內部修改的值(傳入的變數)賦值給外部傳入的全域性變數、