04.17開(kāi)課吧-數(shù)據(jù)分析歸檔it視頻學(xué)習(xí)網(wǎng)mysql day

1、變量Mysql本質(zhì)是一種編程語(yǔ)言，需要很多變量來(lái)保存數(shù)據(jù)。Mysql中很多的屬性控制都是通過(guò)mysql中固有的變量來(lái)實(shí)現(xiàn)的。系統(tǒng)變量系統(tǒng)內(nèi)部定義的變量，系統(tǒng)變量針對(duì)所有用戶(hù)（MySQL客戶(hù)端）有效。查看系統(tǒng)所有變量：show variables like pattern;Mysql允許用戶(hù)使用select查詢(xún)變量的數(shù)據(jù)值（系統(tǒng)變量）基本語(yǔ)法：select 變量名;修改系統(tǒng)變量：分為兩種修改方式1、局部修改（會(huì)話(huà)級(jí)別）：只針對(duì)當(dāng)前自己客戶(hù)端當(dāng)次連接有效基本語(yǔ)法：set 變量名 = 新值;2、全局修改：針對(duì)所有的客戶(hù)端，“所有時(shí)刻”都有效基本語(yǔ)法：set global 變量名 = 值; | set

2、 global.變量名 = 值;全局修改之后：所有連接的客戶(hù)端并沒(méi)發(fā)現(xiàn)改變？全局修改只針對(duì)新客戶(hù)端生效（正在連著的無(wú)效）注意：如果想要本次連接對(duì)應(yīng)的變量修改有效，那么不能使用全局修改，只能使用會(huì)話(huà)級(jí)別修改（set 變量名 = 值）;會(huì)話(huà)變量會(huì)話(huà)變量也稱(chēng)之為用戶(hù)變量，會(huì)話(huà)變量跟mysql客戶(hù)端是綁定的，設(shè)置的變量，只對(duì)當(dāng)前用戶(hù)使用的客戶(hù)端生效。定義用戶(hù)變量：set 變量名 = 值;在mysql中因?yàn)闆](méi)有比較符號(hào)=，所以是用=代替比較符號(hào)：有時(shí)候在賦值的時(shí)候，會(huì)報(bào)錯(cuò)：mysql為了避免系統(tǒng)分不清是賦值還是比較：特定增加一個(gè)變量的賦值符號(hào)： :=Set 變量名 := 值;Mysql是專(zhuān)門(mén)存儲(chǔ)數(shù)據(jù)的：

3、允許將數(shù)據(jù)從表中取出存儲(chǔ)到變量中：查詢(xún)得到的數(shù)據(jù)必須只能是一行數(shù)據(jù)（一個(gè)變量對(duì)應(yīng)一個(gè)字段值）：Mysql沒(méi)有數(shù)組。1、賦值且查看賦值過(guò)程：select 變量1 := 字段1，變量2 := 字段2 from 數(shù)據(jù)表 where 條件；錯(cuò)誤語(yǔ)法：就是因?yàn)槭褂?，系統(tǒng)會(huì)當(dāng)做比較符號(hào)來(lái)處理正確處理：使用:=2、只賦值，不看過(guò)程：select 字段1，字段2 from 數(shù)據(jù)源 where條件 into 變量1，變量2查看變量：select 變量名;局部變量作用范圍在begin到end語(yǔ)句塊之間。在該語(yǔ)句塊里設(shè)置的變量，declare語(yǔ)句專(zhuān)門(mén)用于定義局部變量。1、局部變量是使用declare關(guān)鍵字聲明2、

4、局部變量declare語(yǔ)句出現(xiàn)的位置一定是在begin和end之間（beginend是在大型語(yǔ)句塊中使用：函數(shù)/存儲(chǔ)過(guò)程/觸發(fā)器）3、聲明語(yǔ)法：declare 變量名 數(shù)據(jù)類(lèi)型 屬性;流程結(jié)構(gòu)流程結(jié)構(gòu)：代碼的執(zhí)行順序If分支基本語(yǔ)法If在Mysql中有兩種基本用法1、用在select查詢(xún)當(dāng)中，當(dāng)做一種條件來(lái)進(jìn)行判斷基本語(yǔ)法：if(條件,為真結(jié)果,為假結(jié)果)2、用在復(fù)雜的語(yǔ)句塊中（函數(shù)/存儲(chǔ)過(guò)程/觸發(fā)器）基本語(yǔ)法If 條件表達(dá)式 then 滿(mǎn)足條件要執(zhí)行的語(yǔ)句;End if;復(fù)合語(yǔ)法復(fù)合語(yǔ)法：代碼的判斷存在兩面性，兩面都有對(duì)應(yīng)的代碼執(zhí)行?；菊Z(yǔ)法：If 條件表達(dá)式 then滿(mǎn)足條件要執(zhí)行的語(yǔ)句;

5、Else不滿(mǎn)足條件要執(zhí)行的語(yǔ)句;/如果還有其他分支（細(xì)分），可以在里面再使用ifIf 條件表達(dá)式 then/滿(mǎn)足要執(zhí)行的語(yǔ)句End if;End if;While循環(huán)基本語(yǔ)法循環(huán)體都是需要在大型代碼塊中使用基本語(yǔ)法While 條件 do要循環(huán)執(zhí)行的代碼;End while;結(jié)構(gòu)標(biāo)識(shí)符結(jié)構(gòu)標(biāo)識(shí)符：為某些特定的結(jié)構(gòu)進(jìn)行命名，然后為的是在某些地方使用名字基本語(yǔ)法標(biāo)識(shí)名字:While 條件 do循環(huán)體End while 標(biāo)識(shí)名字;標(biāo)識(shí)符的存在主要是為了循環(huán)體中使用循環(huán)控制。在mysql中沒(méi)有continue和break，有自己的關(guān)鍵字替代：Iterate：迭代，就是以下的代碼不執(zhí)行，重新開(kāi)始循環(huán)（co

6、ntinue）Leave：離開(kāi)，整個(gè)循環(huán)終止（break）標(biāo)識(shí)名字:While 條件 do If 條件判斷 then循環(huán)控制;Iterate/leave 標(biāo)識(shí)名字;End if;循環(huán)體End while 標(biāo)識(shí)名字;函數(shù)在mysql中，函數(shù)分為兩類(lèi)：系統(tǒng)函數(shù)（內(nèi)置函數(shù)）和自定義函數(shù)不管是內(nèi)置函數(shù)還是用戶(hù)自定義函數(shù)，都是使用select 函數(shù)名(參數(shù)列表);內(nèi)置函數(shù)字符串函數(shù)Char_length()：判斷字符串的字符數(shù)Length()：判斷字符串的字節(jié)數(shù)（與字符集）Concat()：連接字符串Instr()：判斷字符在目標(biāo)字符串中是否存在，存在返回其位置，不存在返回0Lcase()：全部小寫(xiě)Le

7、ft()：從左側(cè)開(kāi)始截取，直到指定位置（位置如果超過(guò)長(zhǎng)度，截取所有）Ltrim()：消除左邊對(duì)應(yīng)的空格Mid()：從中間指定位置開(kāi)始截取，如果不指定截取長(zhǎng)度，直接到最后時(shí)間函數(shù)Now()：返回當(dāng)前時(shí)間，日期 時(shí)間Curdate()：返回當(dāng)前日期Curtime()：返回當(dāng)前時(shí)間Datediff()：判斷兩個(gè)日期之間的天數(shù)差距，參數(shù)日期必須使用字符串格式（用引號(hào)）Date_add(日期,interval 時(shí)間數(shù)字 type)：進(jìn)行時(shí)間的增加Type:day/hour/minute/secondUnix_timestamp()：獲取時(shí)間戳From_unixtime()：將指定時(shí)間戳轉(zhuǎn)換成對(duì)應(yīng)的日期時(shí)

8、間格式數(shù)學(xué)函數(shù)Abs()：絕對(duì)值Ceiling()：向上取整Floor()：向下取整Pow()：求指數(shù)，誰(shuí)的多少次方Rand()：獲取一個(gè)隨機(jī)數(shù)（0-1之間）Round()：四舍五入函數(shù)其他函數(shù)Md5()：對(duì)數(shù)據(jù)進(jìn)行md5加密（mysql中的md5與其他任何地方的md5加密出來(lái)的內(nèi)容是完全相同的）Version()：獲取版本號(hào)Databse()：顯示當(dāng)前所在數(shù)據(jù)庫(kù)UUID()：生成一個(gè)唯一標(biāo)識(shí)符（自增長(zhǎng)）：自增長(zhǎng)是單表唯一，UUID是整庫(kù)（數(shù)據(jù)唯一同時(shí)空間唯一）自定義函數(shù)自定義函數(shù)：用戶(hù)自己定義的函數(shù)函數(shù)：實(shí)現(xiàn)某種功能的語(yǔ)句塊（由多條語(yǔ)句組成）1、函數(shù)內(nèi)部的每條指令都是一個(gè)獨(dú)立的個(gè)體：需要符合

9、語(yǔ)句定義規(guī)范：需要語(yǔ)句結(jié)束符分號(hào)；2、函數(shù)是一個(gè)整體，而且函數(shù)是在調(diào)用的時(shí)候才會(huì)被執(zhí)行，那么當(dāng)設(shè)計(jì)函數(shù)的時(shí)候，意味著整體不能被中斷；3、Mysql一旦見(jiàn)到語(yǔ)句結(jié)束符分號(hào)，就會(huì)自動(dòng)開(kāi)始執(zhí)行解決方案：在定義函數(shù)之前，嘗試修改臨時(shí)的語(yǔ)句結(jié)束符基本語(yǔ)法：delimiter修改臨時(shí)語(yǔ)句結(jié)束符：delimiter 新符號(hào)可以使用系統(tǒng)非內(nèi)置即可$中間為正常SQL指令：使用分號(hào)結(jié)束（系統(tǒng)不會(huì)執(zhí)行：不認(rèn)識(shí)分號(hào)）使用新符號(hào)結(jié)束修改回語(yǔ)句結(jié)束符：delimiter ;創(chuàng)建函數(shù)自定義函數(shù)包含幾個(gè)要素：function關(guān)鍵字，函數(shù)名，參數(shù)（形參和實(shí)參可選），確認(rèn)函數(shù)返回值類(lèi)型，函數(shù)體，返回值函數(shù)定義基本語(yǔ)法：修改語(yǔ)句結(jié)

10、束符Create function 函數(shù)名(形參) returns 返回值類(lèi)型Begin/函數(shù)體Return 返回值數(shù)據(jù);/數(shù)據(jù)必須與結(jié)構(gòu)中定義的返回值類(lèi)型一致End語(yǔ)句結(jié)束符修改語(yǔ)句結(jié)束符（改回來(lái)）并不是所有的函數(shù)都需要begin和end：如果函數(shù)體本身只有一條指令（return），那么可以省略begin和end形參：在mysql中需要為函數(shù)的形參指定數(shù)據(jù)類(lèi)型（形參本身可以有多個(gè)）基本語(yǔ)法：變量名 字段類(lèi)型查看函數(shù)1、可以通過(guò)查看function狀態(tài)，查看所有的函數(shù)Show function status like pattern;2、查看函數(shù)的創(chuàng)建語(yǔ)句：show create functi

11、on 函數(shù)名字;調(diào)用函數(shù)自定義函數(shù)的調(diào)用與內(nèi)置函數(shù)的調(diào)用是一樣的：select 函數(shù)名(實(shí)參列表)；刪除函數(shù)刪除函數(shù)：drop function 函數(shù)名;注意事項(xiàng)1、自定義函數(shù)是屬于用戶(hù)級(jí)別的：只有當(dāng)前客戶(hù)端對(duì)應(yīng)的數(shù)據(jù)庫(kù)中可以使用2、可以在不同的數(shù)據(jù)庫(kù)下看到對(duì)應(yīng)的函數(shù)，但是不可以調(diào)用3、自定義函數(shù)：通常是為了將多行代碼集合到一起解決一個(gè)重復(fù)性的問(wèn)題4、函數(shù)因?yàn)楸仨氁?guī)范返回值：那么在函數(shù)內(nèi)部不能使用select指令：select一旦執(zhí)行就會(huì)得到一個(gè)結(jié)果（result set）：select 字段 into 變量;（唯一可用）函數(shù)流程結(jié)構(gòu)案例需求：從1開(kāi)始，直到用戶(hù)傳入的對(duì)應(yīng)的值為止，自動(dòng)求和：凡

12、是5的倍數(shù)都不要。設(shè)計(jì)：1、創(chuàng)建函數(shù)2、需要一個(gè)形參：確定要累加到什么位置3、需要定義一個(gè)變量來(lái)保存對(duì)應(yīng)的結(jié)果：set 變量名;使用局部變量來(lái)操作：此結(jié)果是在函數(shù)內(nèi)部使用Declare 變量名 類(lèi)型 = 默認(rèn)值;4、內(nèi)部需要一個(gè)循環(huán)來(lái)實(shí)現(xiàn)迭代累加5、循環(huán)內(nèi)部需要進(jìn)行條件判斷控制：5的倍數(shù)6、函數(shù)必須有返回值定義函數(shù)結(jié)構(gòu)完成調(diào)用函數(shù)：select 函數(shù)名(實(shí)參);變量作用域變量作用域：變量能夠使用的區(qū)域范圍局部作用域使用declare關(guān)鍵字聲明（在結(jié)構(gòu)體內(nèi)：函數(shù)/存儲(chǔ)過(guò)程/觸發(fā)器），而且只能在結(jié)構(gòu)體內(nèi)部使用1、declare關(guān)鍵字聲明的變量沒(méi)有任何符號(hào)修飾，就是普通字符串，如果在外部訪(fǎng)問(wèn)該變量，

13、系統(tǒng)會(huì)自動(dòng)認(rèn)為是字段會(huì)話(huà)作用域用戶(hù)定義的，使用符號(hào)定義的變量，使用set關(guān)鍵字會(huì)話(huà)作用域：在當(dāng)前用戶(hù)當(dāng)次連接有效，只要在本連接之中，任何地方都可以使用（可以在結(jié)構(gòu)內(nèi)部，也可以跨庫(kù)）會(huì)話(huà)變量可以在函數(shù)內(nèi)部使用會(huì)話(huà)變量可以跨庫(kù)全局作用域所有的客戶(hù)端所有的連接都有效：需要使用全局符號(hào)來(lái)定義Set global 變量名 = 值;Set global.變量名 = 值;通常，在SQL編程的時(shí)候，不會(huì)使用自定義變量來(lái)控制全局。一般都是定義會(huì)話(huà)變量或者在結(jié)構(gòu)中使用局部變量來(lái)解決問(wèn)題。存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程概念存儲(chǔ)過(guò)程（Stored Procedure）是在大型數(shù)據(jù)庫(kù)系統(tǒng)中，一組為了完成特定功能的SQL 語(yǔ)句集，存儲(chǔ)

14、在數(shù)據(jù)庫(kù)中，經(jīng)過(guò)第一次編譯后再次調(diào)用不需要再次編譯（效率比較高），用戶(hù)通過(guò)指定存儲(chǔ)過(guò)程的名字并給出參數(shù)（如果該存儲(chǔ)過(guò)程帶有參數(shù)）來(lái)執(zhí)行它。存儲(chǔ)過(guò)程是數(shù)據(jù)庫(kù)中的一個(gè)重要對(duì)象（針對(duì)SQL編程而言）。存儲(chǔ)過(guò)程：簡(jiǎn)稱(chēng)過(guò)程與函數(shù)的區(qū)別相同點(diǎn)1、存儲(chǔ)過(guò)程和函數(shù)目的都是為了可重復(fù)地執(zhí)行操作數(shù)據(jù)庫(kù)的sql語(yǔ)句的集合。2、存儲(chǔ)過(guò)程函數(shù)都是一次編譯，后續(xù)執(zhí)行不同點(diǎn)1、標(biāo)識(shí)符不同。函數(shù)的標(biāo)識(shí)符為FUNCTION，過(guò)程為：PROCEDURE。2、函數(shù)中有返回值，且必須返回，而過(guò)程沒(méi)有返回值。3、過(guò)程無(wú)返回值類(lèi)型，不能將結(jié)果直接賦值給變量；函數(shù)有返回值類(lèi)型，調(diào)用時(shí)，除在select中，必須將返回值賦給變量。4、函數(shù)可以

15、在select語(yǔ)句中直接使用，而過(guò)程不能：函數(shù)是使用select調(diào)用，過(guò)程不是。存儲(chǔ)過(guò)程操作創(chuàng)建過(guò)程基本語(yǔ)法Create procedure 過(guò)程名字(參數(shù)列表)Begin過(guò)程體End結(jié)束符如果過(guò)程體中只有一條指令，那么可以省略begin和end過(guò)程基本上也可以完成函數(shù)對(duì)應(yīng)的所有功能查看過(guò)程查看過(guò)程與查看函數(shù)完全一樣：除了關(guān)鍵字查看全部存儲(chǔ)過(guò)程：show procedure status like pattern;查看過(guò)程創(chuàng)建語(yǔ)句：show create procedure 過(guò)程名字;調(diào)用過(guò)程過(guò)程：沒(méi)有返回值，select不可能調(diào)用調(diào)用過(guò)程有專(zhuān)門(mén)的語(yǔ)法：call 過(guò)程名(實(shí)參列表);刪除過(guò)程基

16、本語(yǔ)法：drop procedure 過(guò)程名字;存儲(chǔ)過(guò)程的形參類(lèi)型存儲(chǔ)過(guò)程也允許提供參數(shù)（形參和實(shí)參）：存儲(chǔ)的參數(shù)也和函數(shù)一樣，需要指定其類(lèi)型。但是存儲(chǔ)過(guò)程對(duì)參數(shù)還有額外的要求：自己的參數(shù)分類(lèi)In表示參數(shù)從外部傳入到里面使用（過(guò)程內(nèi)部使用）：可以是直接數(shù)據(jù)也可以是保存數(shù)據(jù)的變量Out表示參數(shù)是從過(guò)程里面把數(shù)據(jù)保存到變量中，交給外部使用：傳入的必須是變量如果說(shuō)傳入的out變量本身在外部有數(shù)據(jù)，那么在進(jìn)入過(guò)程之后，第一件事就是被清空，設(shè)為NULLInout數(shù)據(jù)可以從外部傳入到過(guò)程內(nèi)部使用，同時(shí)內(nèi)部操作之后，又會(huì)將數(shù)據(jù)返還給外部。參數(shù)使用級(jí)別語(yǔ)法（形參）過(guò)程類(lèi)型 變量名 數(shù)據(jù)類(lèi)型; /in int_

17、1 int分析結(jié)果：out類(lèi)型的數(shù)據(jù)會(huì)被清空，其他正常在執(zhí)行過(guò)程之后，再次查看會(huì)話(huà)變量（外部）觸發(fā)器觸發(fā)器概念基本概念觸發(fā)器是一種特殊類(lèi)型的存儲(chǔ)過(guò)程，它不同于我們前面介紹過(guò)的存儲(chǔ)過(guò)程。觸發(fā)器主要是通過(guò)事件進(jìn)行觸發(fā)而被執(zhí)行的，而存儲(chǔ)過(guò)程可以通過(guò)存儲(chǔ)過(guò)程名字而被直接調(diào)用。觸發(fā)器：trigger，是一種非常接近于js中的事件的知識(shí)。提前給某張表的所有記錄（行）綁定一段代碼，如果改行的操作滿(mǎn)足條件（觸發(fā)），這段提前準(zhǔn)備好的代碼就會(huì)自動(dòng)執(zhí)行。作用1、可在寫(xiě)入數(shù)據(jù)表前，強(qiáng)制檢驗(yàn)或轉(zhuǎn)換數(shù)據(jù)。（保證數(shù)據(jù)安全）2、觸發(fā)器發(fā)生錯(cuò)誤時(shí)，異動(dòng)的結(jié)果會(huì)被撤銷(xiāo)。（如果觸發(fā)器執(zhí)行錯(cuò)誤，那么前面用戶(hù)已經(jīng)執(zhí)行成功的操作也會(huì)被撤

18、銷(xiāo)：事務(wù)安全）3、部分?jǐn)?shù)據(jù)庫(kù)管理系統(tǒng)可以針對(duì)數(shù)據(jù)定義語(yǔ)言（DDL）使用觸發(fā)器，稱(chēng)為DDL觸發(fā)器。4、可依照特定的情況，替換異動(dòng)的指令 (INSTEAD OF)。（mysql不支持）觸發(fā)器優(yōu)缺點(diǎn)優(yōu)點(diǎn)1、觸發(fā)器可通過(guò)數(shù)據(jù)庫(kù)中的相關(guān)表實(shí)現(xiàn)級(jí)聯(lián)更改。（如果某張表的數(shù)據(jù)改變，可以利用觸發(fā)器來(lái)實(shí)現(xiàn)其他表的無(wú)痕操作用戶(hù)不知道）2、保證數(shù)據(jù)安全：進(jìn)行安全校驗(yàn)缺點(diǎn)1、對(duì)觸發(fā)器過(guò)分的依賴(lài)，勢(shì)必影響數(shù)據(jù)庫(kù)的結(jié)構(gòu)，同時(shí)增加了維護(hù)的復(fù)雜 HYPERLINK /view/644590.htm t _blank 程度。2、造成數(shù)據(jù)在程序?qū)用娌豢煽?。（PHP層）觸發(fā)器基本語(yǔ)法創(chuàng)建觸發(fā)器基本語(yǔ)法Create trigger 觸

19、發(fā)器名字 觸發(fā)時(shí)機(jī) 觸發(fā)事件 on 表 for each rowBeginEnd觸發(fā)對(duì)象：on 表 for each row，觸發(fā)器綁定實(shí)質(zhì)是表中的所有行，因此當(dāng)每一行發(fā)生指定的改變的時(shí)候，就會(huì)觸發(fā)觸發(fā)器。觸發(fā)時(shí)機(jī)觸發(fā)時(shí)機(jī)：每張表中對(duì)應(yīng)的行都會(huì)有不同的狀態(tài)，當(dāng)SQL指令發(fā)生的時(shí)候，都會(huì)令行中數(shù)據(jù)發(fā)生改變，每一行總會(huì)有兩種狀態(tài)：數(shù)據(jù)操作前和操作后Before：在表中數(shù)據(jù)發(fā)生改變前的狀態(tài)After：在表中數(shù)據(jù)已經(jīng)發(fā)生改變后的狀態(tài)觸發(fā)事件觸發(fā)事件：mysql中觸發(fā)器針對(duì)的目標(biāo)是數(shù)據(jù)發(fā)生改變，對(duì)應(yīng)的操作只有寫(xiě)操作（增刪改）Insert：插入操作Update：更新操作Delete：刪除操作注意事項(xiàng)一張表中，每一個(gè)觸發(fā)時(shí)機(jī)綁定的觸發(fā)事件對(duì)應(yīng)的觸發(fā)器類(lèi)型只能有一個(gè)：一張表中只能有一個(gè)對(duì)應(yīng)after insert觸發(fā)器因此，一張表中最多的觸發(fā)器只能有6個(gè)：before insert，before update，b