變量

Mysql本質是一種編程語言，需要很多變量來保存數(shù)據(jù)。Mysql中很多的屬性控制都是通過mysql中固有的變量來實現(xiàn)的。

系統(tǒng)變量

系統(tǒng)內部定義的變量，系統(tǒng)變量針對所有用戶（MySQL客戶端）有效。

查看系統(tǒng)所有變量：show variables [like ‘pattern’];

 

Mysql允許用戶使用select查詢變量的數(shù)據(jù)值（系統(tǒng)變量）

基本語法：select @@變量名;

 

修改系統(tǒng)變量：分為兩種修改方式

1、  局部修改（會話級別）：只針對當前自己客戶端當次連接有效

基本語法：set 變量名 = 新值;

2、  全局修改：針對所有的客戶端，“所有時刻”都有效

基本語法：set global 變量名 = 值; || set @@global.變量名 = 值;

全局修改之后：所有連接的客戶端并沒發(fā)現(xiàn)改變？全局修改只針對新客戶端生效（正在連著的無效）

 

 

注意：如果想要本次連接對應的變量修改有效，那么不能使用全局修改，只能使用會話級別修改（set 變量名 = 值）;

會話變量

會話變量也稱之為用戶變量，會話變量跟mysql客戶端是綁定的，設置的變量，只對當前用戶使用的客戶端生效。

 

定義用戶變量：set @變量名 = 值;

 

在mysql中因為沒有比較符號==，所以是用=代替比較符號：有時候在賦值的時候，會報錯：mysql為了避免系統(tǒng)分不清是賦值還是比較：特定增加一個變量的賦值符號：  :=

Set @變量名 := 值;

 

Mysql是專門存儲數(shù)據(jù)的：允許將數(shù)據(jù)從表中取出存儲到變量中：查詢得到的數(shù)據(jù)必須只能是一行數(shù)據(jù)（一個變量對應一個字段值）：Mysql沒有數(shù)組。

1、 賦值且查看賦值過程：select @變量1 := 字段1，@變量2 := 字段2 from 數(shù)據(jù)表 where 條件；

 

錯誤語法：就是因為使用=，系統(tǒng)會當做比較符號來處理

 

正確處理：使用:=

 

2、 只賦值，不看過程：select 字段1，字段2… from 數(shù)據(jù)源 where條件 into @變量1，@變量2…

 

查看變量：select @變量名;

局部變量

作用范圍在begin到end語句塊之間。在該語句塊里設置的變量，declare語句專門用于定義局部變量。

 

 

1、 局部變量是使用declare關鍵字聲明

2、 局部變量declare語句出現(xiàn)的位置一定是在begin和end之間（beginend是在大型語句塊中使用：函數(shù)/存儲過程/觸發(fā)器）

3、 聲明語法：declare 變量名 數(shù)據(jù)類型 [屬性];

流程結構

流程結構：代碼的執(zhí)行順序

If分支

基本語法

If在Mysql中有兩種基本用法

 

1、  用在select查詢當中，當做一種條件來進行判斷

基本語法：if(條件,為真結果,為假結果)

2、  用在復雜的語句塊中（函數(shù)/存儲過程/觸發(fā)器）

基本語法

 

If  條件表達式  then

         滿足條件要執(zhí)行的語句;

End  if;

復合語法

復合語法：代碼的判斷存在兩面性，兩面都有對應的代碼執(zhí)行。

基本語法：

 

If  條件表達式  then

         滿足條件要執(zhí)行的語句;

Else

         不滿足條件要執(zhí)行的語句;

//如果還有其他分支（細分），可以在里面再使用if

         If 條件表達式 then

                   //滿足要執(zhí)行的語句

         End if;

End  if;

While循環(huán)

基本語法

循環(huán)體都是需要在大型代碼塊中使用

基本語法

 

While 條件 do

         要循環(huán)執(zhí)行的代碼;

End while;

 

結構標識符

結構標識符：為某些特定的結構進行命名，然后為的是在某些地方使用名字

 

基本語法

 

標識名字:While 條件 do

循環(huán)體

End while [標識名字];

 

標識符的存在主要是為了循環(huán)體中使用循環(huán)控制。在mysql中沒有continue和break，有自己的關鍵字替代：

Iterate：迭代，就是以下的代碼不執(zhí)行，重新開始循環(huán)（continue）

Leave：離開，整個循環(huán)終止（break）

 

標識名字:While 條件 do

          If 條件判斷 then

                   循環(huán)控制;

                   Iterate/leave 標識名字;

         End if;

循環(huán)體

End while [標識名字];

 

函數(shù)

在mysql中，函數(shù)分為兩類：系統(tǒng)函數(shù)（內置函數(shù)）和自定義函數(shù)

不管是內置函數(shù)還是用戶自定義函數(shù)，都是使用select 函數(shù)名(參數(shù)列表);

內置函數(shù)

字符串函數(shù)

Char_length()：判斷字符串的字符數(shù)

Length()：判斷字符串的字節(jié)數(shù)（與字符集）

Concat()：連接字符串

Instr()：判斷字符在目標字符串中是否存在，存在返回其位置，不存在返回0

Lcase()：全部小寫

Left()：從左側開始截取，直到指定位置（位置如果超過長度，截取所有）

Ltrim()：消除左邊對應的空格

Mid()：從中間指定位置開始截取，如果不指定截取長度，直接到最后

時間函數(shù)

Now()：返回當前時間，日期 時間

Curdate()：返回當前日期

Curtime()：返回當前時間

Datediff()：判斷兩個日期之間的天數(shù)差距，參數(shù)日期必須使用字符串格式（用引號）

Date_add(日期,interval 時間數(shù)字 type)：進行時間的增加

         Type:day/hour/minute/second

 

Unix_timestamp()：獲取時間戳

From_unixtime()：將指定時間戳轉換成對應的日期時間格式

 

數(shù)學函數(shù)

Abs()：絕對值

Ceiling()：向上取整

Floor()：向下取整

Pow()：求指數(shù)，誰的多少次方

Rand()：獲取一個隨機數(shù)（0-1之間）

Round()：四舍五入函數(shù)

其他函數(shù)

Md5()：對數(shù)據(jù)進行md5加密（mysql中的md5與其他任何地方的md5加密出來的內容是完全相同的）

Version()：獲取版本號

Databse()：顯示當前所在數(shù)據(jù)庫

UUID()：生成一個唯一標識符（自增長）：自增長是單表唯一，UUID是整庫（數(shù)據(jù)唯一同時空間唯一）

自定義函數(shù)

自定義函數(shù)：用戶自己定義的函數(shù)

函數(shù)：實現(xiàn)某種功能的語句塊（由多條語句組成）

 

1、  函數(shù)內部的每條指令都是一個獨立的個體：需要符合語句定義規(guī)范：需要語句結束符分號；

2、  函數(shù)是一個整體，而且函數(shù)是在調用的時候才會被執(zhí)行，那么當設計函數(shù)的時候，意味著整體不能被中斷；

3、  Mysql一旦見到語句結束符分號，就會自動開始執(zhí)行

 

解決方案：在定義函數(shù)之前，嘗試修改臨時的語句結束符

基本語法：delimiter

修改臨時語句結束符：delimiter 新符號[可以使用系統(tǒng)非內置即可$$]

中間為正常SQL指令：使用分號結束（系統(tǒng)不會執(zhí)行：不認識分號）

使用新符號結束

修改回語句結束符：delimiter ;

創(chuàng)建函數(shù)

自定義函數(shù)包含幾個要素：function關鍵字，函數(shù)名，參數(shù)（形參和實參[可選]），確認函數(shù)返回值類型，函數(shù)體，返回值

 

函數(shù)定義基本語法：

修改語句結束符

Create function 函數(shù)名(形參) returns 返回值類型

Begin

         //函數(shù)體

         Return 返回值數(shù)據(jù);       //數(shù)據(jù)必須與結構中定義的返回值類型一致

End

語句結束符

修改語句結束符（改回來）

 

并不是所有的函數(shù)都需要begin和end：如果函數(shù)體本身只有一條指令（return），那么可以省略begin和end

 

形參：在mysql中需要為函數(shù)的形參指定數(shù)據(jù)類型（形參本身可以有多個）

基本語法：變量名 字段類型

查看函數(shù)

1、  可以通過查看function狀態(tài)，查看所有的函數(shù)

Show function status [like ‘pattern’];

2、  查看函數(shù)的創(chuàng)建語句：show create function 函數(shù)名字;

調用函數(shù)

自定義函數(shù)的調用與內置函數(shù)的調用是一樣的：select 函數(shù)名(實參列表)；

 

刪除函數(shù)

刪除函數(shù)：drop function 函數(shù)名;

 

 

注意事項

1、  自定義函數(shù)是屬于用戶級別的：只有當前客戶端對應的數(shù)據(jù)庫中可以使用

2、  可以在不同的數(shù)據(jù)庫下看到對應的函數(shù)，但是不可以調用

3、  自定義函數(shù)：通常是為了將多行代碼集合到一起解決一個重復性的問題

4、  函數(shù)因為必須規(guī)范返回值：那么在函數(shù)內部不能使用select指令：select一旦執(zhí)行就會得到一個結果（result set）：select 字段 into @變量;（唯一可用）

函數(shù)流程結構案例

 

需求：從1開始，直到用戶傳入的對應的值為止，自動求和：凡是5的倍數(shù)都不要。

 

設計：

1、  創(chuàng)建函數(shù)

2、  需要一個形參：確定要累加到什么位置

3、  需要定義一個變量來保存對應的結果：set @變量名;

使用局部變量來操作：此結果是在函數(shù)內部使用

Declare 變量名 類型 [= 默認值];

4、  內部需要一個循環(huán)來實現(xiàn)迭代累加

5、  循環(huán)內部需要進行條件判斷控制：5的倍數(shù)

6、  函數(shù)必須有返回值

 

定義函數(shù)結構完成

 

調用函數(shù)：select 函數(shù)名(實參);

變量作用域

變量作用域：變量能夠使用的區(qū)域范圍

局部作用域

使用declare關鍵字聲明（在結構體內：函數(shù)/存儲過程/觸發(fā)器），而且只能在結構體內部使用

 

1、  declare關鍵字聲明的變量沒有任何符號修飾，就是普通字符串，如果在外部訪問該變量，系統(tǒng)會自動認為是字段

會話作用域

用戶定義的，使用@符號定義的變量，使用set關鍵字

 

會話作用域：在當前用戶當次連接有效，只要在本連接之中，任何地方都可以使用（可以在結構內部，也可以跨庫）

 

會話變量可以在函數(shù)內部使用

 

會話變量可以跨庫

全局作用域

所有的客戶端所有的連接都有效：需要使用全局符號來定義

Set global 變量名 = 值;

Set @@global.變量名 = 值;

 

通常，在SQL編程的時候，不會使用自定義變量來控制全局。一般都是定義會話變量或者在結構中使用局部變量來解決問題。

存儲過程

存儲過程概念

存儲過程（Stored Procedure）是在大型數(shù)據(jù)庫系統(tǒng)中，一組為了完成特定功能的SQL 語句集，存儲在數(shù)據(jù)庫中，經(jīng)過第一次編譯后再次調用不需要再次編譯（效率比較高），用戶通過指定存儲過程的名字并給出參數(shù)（如果該存儲過程帶有參數(shù)）來執(zhí)行它。存儲過程是數(shù)據(jù)庫中的一個重要對象（針對SQL編程而言）。

 

存儲過程：簡稱過程

與函數(shù)的區(qū)別

相同點

1、  存儲過程和函數(shù)目的都是為了可重復地執(zhí)行操作數(shù)據(jù)庫的sql語句的集合。

2、  存儲過程函數(shù)都是一次編譯，后續(xù)執(zhí)行

不同點

1、標識符不同。函數(shù)的標識符為FUNCTION，過程為：PROCEDURE。

2、函數(shù)中有返回值，且必須返回，而過程沒有返回值。

3、過程無返回值類型，不能將結果直接賦值給變量；函數(shù)有返回值類型，調用時，除在select中，必須將返回值賦給變量。

4、函數(shù)可以在select語句中直接使用，而過程不能：函數(shù)是使用select調用，過程不是。

 

 

存儲過程操作

創(chuàng)建過程

基本語法

Create procedure 過程名字([參數(shù)列表])

Begin

         過程體

End

結束符

 

如果過程體中只有一條指令，那么可以省略begin和end

 

過程基本上也可以完成函數(shù)對應的所有功能

查看過程

查看過程與查看函數(shù)完全一樣：除了關鍵字

 

查看全部存儲過程：show procedure status [like ‘pattern’];

 

查看過程創(chuàng)建語句：show create procedure 過程名字;

調用過程

過程：沒有返回值，select不可能調用

 

調用過程有專門的語法：call 過程名([實參列表]);

刪除過程

基本語法：drop procedure 過程名字;

存儲過程的形參類型

存儲過程也允許提供參數(shù)（形參和實參）：存儲的參數(shù)也和函數(shù)一樣，需要指定其類型。

 

但是存儲過程對參數(shù)還有額外的要求：自己的參數(shù)分類

In

表示參數(shù)從外部傳入到里面使用（過程內部使用）：可以是直接數(shù)據(jù)也可以是保存數(shù)據(jù)的變量

Out

表示參數(shù)是從過程里面把數(shù)據(jù)保存到變量中，交給外部使用：傳入的必須是變量

如果說傳入的out變量本身在外部有數(shù)據(jù)，那么在進入過程之后，第一件事就是被清空，設為NULL

Inout

數(shù)據(jù)可以從外部傳入到過程內部使用，同時內部操作之后，又會將數(shù)據(jù)返還給外部。

 

 

參數(shù)使用級別語法（形參）

過程類型  變量名  數(shù)據(jù)類型; //in int_1 int

 

分析結果：out類型的數(shù)據(jù)會被清空，其他正常

 

在執(zhí)行過程之后，再次查看會話變量（外部）

 

觸發(fā)器

觸發(fā)器概念

   基本概念

觸發(fā)器是一種特殊類型的存儲過程，它不同于我們前面介紹過的存儲過程。觸發(fā)器主要是通過事件進行觸發(fā)而被執(zhí)行的，而存儲過程可以通過存儲過程名字而被直接調用。

 

觸發(fā)器：trigger，是一種非常接近于js中的事件的知識。提前給某張表的所有記錄（行）綁定一段代碼，如果改行的操作滿足條件（觸發(fā)），這段提前準備好的代碼就會自動執(zhí)行。

   作用

1、可在寫入數(shù)據(jù)表前，強制檢驗或轉換數(shù)據(jù)。（保證數(shù)據(jù)安全）

2、觸發(fā)器發(fā)生錯誤時，異動的結果會被撤銷。（如果觸發(fā)器執(zhí)行錯誤，那么前面用戶已經(jīng)執(zhí)行成功的操作也會被撤銷：事務安全）

3、部分數(shù)據(jù)庫管理系統(tǒng)可以針對數(shù)據(jù)定義語言（DDL）使用觸發(fā)器，稱為DDL觸發(fā)器。

4、可依照特定的情況，替換異動的指令 (INSTEAD OF)。（mysql不支持）

   觸發(fā)器優(yōu)缺點

優(yōu)點

1、  觸發(fā)器可通過數(shù)據(jù)庫中的相關表實現(xiàn)級聯(lián)更改。（如果某張表的數(shù)據(jù)改變，可以利用觸發(fā)器來實現(xiàn)其他表的無痕操作[用戶不知道]）

2、  保證數(shù)據(jù)安全：進行安全校驗

 

缺點

1、  對觸發(fā)器過分的依賴，勢必影響數(shù)據(jù)庫的結構，同時增加了維護的復雜程度。

2、  造成數(shù)據(jù)在程序層面不可控。（PHP層）

觸發(fā)器基本語法

創(chuàng)建觸發(fā)器

基本語法

Create trigger 觸發(fā)器名字 觸發(fā)時機 觸發(fā)事件 on 表 for each row

Begin

 

End

 

觸發(fā)對象：on 表 for each row，觸發(fā)器綁定實質是表中的所有行，因此當每一行發(fā)生指定的改變的時候，就會觸發(fā)觸發(fā)器。

觸發(fā)時機

觸發(fā)時機：每張表中對應的行都會有不同的狀態(tài)，當SQL指令發(fā)生的時候，都會令行中數(shù)據(jù)發(fā)生改變，每一行總會有兩種狀態(tài)：數(shù)據(jù)操作前和操作后

 

Before：在表中數(shù)據(jù)發(fā)生改變前的狀態(tài)

After：在表中數(shù)據(jù)已經(jīng)發(fā)生改變后的狀態(tài)

觸發(fā)事件

觸發(fā)事件：mysql中觸發(fā)器針對的目標是數(shù)據(jù)發(fā)生改變，對應的操作只有寫操作（增刪改）

 

Insert：插入操作

Update：更新操作

Delete：刪除操作

注意事項

一張表中，每一個觸發(fā)時機綁定的觸發(fā)事件對應的觸發(fā)器類型只能有一個：一張表中只能有一個對應after insert觸發(fā)器

 

因此，一張表中最多的觸發(fā)器只能有6個：before insert，before update，before delete，after insert，after update，after delete

 

需求：有兩張表，一張是商品表，一張是訂單表（保留商品ID），每次訂單生成，商品表中對應的庫存就應該發(fā)生變化。

 

1、  創(chuàng)建兩張表：商品表和訂單表

2、  創(chuàng)建觸發(fā)器：如果訂單表發(fā)生數(shù)據(jù)插入，對應的商品就應該減少庫存

Create trigger 名字  after insert on my_orders for each row

查看觸發(fā)器

1、  查看全部觸發(fā)器

Show triggers;

 

2、  查看觸發(fā)器的創(chuàng)建語句

Show create trigger 觸發(fā)器名字;

 

觸發(fā)觸發(fā)器

想辦法讓觸發(fā)器執(zhí)行：讓觸發(fā)器指定的表中，對應的時機發(fā)生對應的操作即可。

1、  表為my_orders

2、  在插入之后

3、  插入操作

刪除觸發(fā)器

基本語法：drop trigger 觸發(fā)器名字;

 

觸發(fā)器應用

      記錄關鍵字：new、old

觸發(fā)器針對的是數(shù)據(jù)表中的每條記錄（每行），每行在數(shù)據(jù)操作前后都有一個對應的狀態(tài)，觸發(fā)器在執(zhí)行之前就將對應的狀態(tài)獲取到了，將沒有操作之前的狀態(tài)（數(shù)據(jù)）都保存到old關鍵字中，而操作后的狀態(tài)都放到new中。

 

在觸發(fā)器中，可以通過old和new來獲取綁定表中對應的記錄數(shù)據(jù)。

基本語法：關鍵字.字段名

 

Old和new并不是所有觸發(fā)器都有：

Insert：插入前全為空，沒有old

Delete：清空數(shù)據(jù)，沒有new

      商品自動扣除庫存

 

驗證結果

 

如果庫存數(shù)量沒有商品訂單多怎么辦？

操作目標：訂單表，操作時機：下單前；操作事件：插入

 

結果驗證