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

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

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

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

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

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

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

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

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

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

10、束符Create function 函數(shù)名(形參) returns 返回值類型Begin/函數(shù)體Return 返回值數(shù)據(jù);/數(shù)據(jù)必須與結(jié)構(gòu)中定義的返回值類型一致End語句結(jié)束符修改語句結(jié)束符（改回來）并不是所有的函數(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 functi

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

12、是5的倍數(shù)都不要。設(shè)計：1、創(chuàng)建函數(shù)2、需要一個形參：確定要累加到什么位置3、需要定義一個變量來保存對應(yīng)的結(jié)果：set 變量名;使用局部變量來操作：此結(jié)果是在函數(shù)內(nèi)部使用Declare 變量名 類型 = 默認(rèn)值;4、內(nèi)部需要一個循環(huán)來實(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ù)/存儲過程/觸發(fā)器），而且只能在結(jié)構(gòu)體內(nèi)部使用1、declare關(guān)鍵字聲明的變量沒有任何符號修飾，就是普通字符串，如果在外部訪問該變量，

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

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

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

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

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

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

19、發(fā)器名字 觸發(fā)時機(jī) 觸發(fā)事件 on 表 for each rowBeginEnd觸發(fā)對象：on 表 for each row，觸發(fā)器綁定實(shí)質(zhì)是表中的所有行，因此當(dāng)每一行發(fā)生指定的改變的時候，就會觸發(fā)觸發(fā)器。觸發(fā)時機(jī)觸發(fā)時機(jī)：每張表中對應(yīng)的行都會有不同的狀態(tài)，當(dāng)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ā)器針對的目標(biāo)是數(shù)據(jù)發(fā)生改變，對應(yīng)的操作只有寫操作（增刪改）Insert：插入操作Update：更新操作Delete：刪除操作注意事項一張表中，每一個觸發(fā)時機(jī)綁定的觸發(fā)事件對應(yīng)的觸發(fā)器類型只能有一個：一張表中只能有一個對應(yīng)after insert觸發(fā)器因此，一張表中最多的觸發(fā)器只能有6個：before insert，before update，b