我的MYSQL學習心得（一）
我的MYSQL學習心得（二）
我的MYSQL學習心得（三）
我的MYSQL學習心得（四）
我的MYSQL學習心得（五）
這一節主要介紹MYSQL裡的函數，MYSQL裡的函數很多，我這裡主要介紹MYSQL裡有而SQLSERVER沒有的函數
數學函數
1、求余函數MOD(X,Y)
MOD(X,Y)返回x被y除後的余數，MOD()對於帶有小數部分的數值也起作用，他返回除法運算後的精確余數
MOD(,)
2、四捨五入函數TRUNCATE(X,Y)
TRUNCATE(X,Y)返回被捨去至小數點後y位的數字x。若y的值為0，則結果不帶有小數點或不帶有小數部分。
若y設為負數，則截去（歸零）x小數點左邊起第y位開始後面所有低位的值。
(,)
TRUNCATE(1.32,1)保留小數點後一位數字，返回值為1.3
TIPS：ROUND(X,Y)函數在截取值的時候會四捨五入，而TRUNCATE(x，y)直接截取值，並不進行四捨五入
字符串函數
計算字符串字符數的函數
1、CHAR_LENGTH(STR)返回值為字符串str所包含的字符個數。一個多字節字符算作一個單字符
CHAR_LENGTH()
2、合並字符串函數CONCAT_WS（x,s1,s2,......）
CONCAT_WS（x,s1,s2,......）,CONCAT_WS代表CONCAT with Separator，是CONCAT（）函數的特殊形式。
第一個參數x是其他參數的分隔符，分隔符的位置放在要連接的兩個字符串之間。分隔符可以是一個字符串，也可以是
其他參數。如果分隔符為NULL，則結果為NULL。函數會忽略任何分隔符參數後的NULL值。
CONCAT_WS(,,,),CONCAT_WS(,,,)
CONCAT_WS('-','1st','2nd','3rd')使用分隔符‘-’將3個字符串連接成一個字符串，結果為“1st-2nd-3rd”；
CONCAT_WS('-','1st',NULL,'3rd')因為第二個值為NULL，所以第二個值不會出現在結果裡
3、替換字符串的函數INSERT(S1,X,LEN,S2)
INSERT(S1,X,LEN,S2)函數跟SQLSERVER裡的STUFF()函數的功能是一樣的，這裡不作介紹了
4、填充字符串的函數LPAD(S1,LEN,S2)和RPAD(S1,LEN,S2)
LPAD(S1,LEN,S2)返回字符串s1，其左邊由字符串s2填補到len字符串長度。假如s1的長度大於len，則返回值被縮短至len字符。
LPAD（）函數和RPAD（）函數功能跟SQLSERVER裡的 REPLACE()相似，不過功能更加強大
LPAD(,,),LPAD(,,)
字符串“hello”長度大於4，不需要填充，因此LPAD('hello',4,'??')只返回被縮短的長度為4的子串
字符串“hello”長度小於10，LPAD('hello',10,'??')返回結果為“?????hello”，左側填充“？”，長度為10
5、RPAD(S1,LEN,S2)返回字符串s1，其右邊被字符串s2填補至len字符長度。假如字符串s1的長度大於len，則返回值被縮短到len字符長度
RPAD(,,)
字符串“hello”長度大於4，不需要填充，因此RPAD('hello',4,'?')只返回被縮短的長度為4的子串"hell"
6、刪除空格的函數TRIM(S)
TRIM(S)刪除字符串s兩側的空格
MYSQL這裡比SQLSERVER方便，SQLSERVER要刪除兩邊的空格，需要使用： (())
TRIM()
刪除指定字符串的函數TRIM(S1 FROM S)
7、TRIM(S1 FROM S)刪除字符串s中兩端所有的子字符串s1。s1為可選項，在未指定情況下，刪除空格
TRIM( )
'xyxboxyokxxyxy'兩端的重復字符串“xy”，而中間的“xy”並不刪除，結果為
xboxyokx
8、重復生成字符串的函數REPEAT(S,N)
這個函數跟SQLSERVER裡的REPLICATE()函數是一樣的，參數個數都是一樣的，這裡不作介紹了
9、比較字符串大小的函數STRCMP(S1,S2)
STRCMP(S1,S2)若所有的字符串均相同，則返回0；若根據當前分類次序，第一個參數小於第二個，則返回-1，其他情況返回1
STRCMP(,) ,STRCMP(,),STRCMP(,)
“txt”小於“txt2”，因此 STRCMP('txt','txt2') 返回結果為-1
STRCMP('txt2','txt')返回結果為1
“txt”與“txt”相等，因此STRCMP('txt','txt')返回結果為0
10、匹配子串開始位置的函數
LOCATE(STR1,STR)、POSITION(STR1 IN STR)、INSTR(STR,STR1)3個函數作用相同，返回子字符串str1在字符串str中的開始位置
這三個函數跟SQLSERVER裡的CHARINDEX()函數功能類似
LOCATE(,),POSITION( ) ,INSTR(,)
子字符串“ball”在字符串“football”中從第5個字母位置開始，因此3個函數返回結果都為5
11、返回指定位置的字符串的函數
ELT(N,字符串1,字符串2,字符串3,...,)，若N=1,則返回值為字符串1，若N=2，則返回值為字符串2，以此類推。
若N小於1或大於參數的數目，則返回值為NULL
ELT(,,,),ELT(,,)
由結果可以看到,ELT(3,'1st','2nd','3rd')返回第3個位置的字符串“3rd”；指定返回字符串位置超出參數個數，返回NULL
12、返回指定字符串位置的函數FIELD(S,S1,S2,...)
FIELD(S,S1,S2,...)返回字符串s在列表s1，s2，......中第一次出現的位置，在找不到s的情況下，返回值為0。
如果s為NULL，則返回值為0，原因是NULL不能同任何值進行同等比較。
FIELD(,,,,) ,,,,) col2
FIELD('hi','hihi','hey','hi','bas')函數中字符串hi出現在列表的第3個字符串位置，因此返回結果為3
FIELD('hi','hihi','lo','hilo','foo') 列表中沒有字符串hi，因此返回結果為0
13、返回子串位置的函數FIND_IN_SET(S1,S2)
FIND_IN_SET(S1,S2)返回字符串s1在字符串列表s2中出現的位置，字符串列表是一個由多個逗號
‘,’分開的字符串組成的列表。如果s1不在s2或s2為空字符串，則返回值為0。如果任意一個參數為NULL，則返回值為NULL。
這個函數在第一個參數包含一個逗號‘,’時將無法正常運行。
FIND_IN_SET(,)
雖然FIND_IN_SET(S1,S2)和FIELD(S,S1,S2,...)兩個函數格式不同，但作用類似，都可以返回指定字符串在字符串列表中的位置
14、選取字符串的函數MAKE_SET(X,S1,S2,...)
MAKE_SET(X,S1,S2,...)返回由x的二進制數指定的相應位的字符串組成的字符串，s1對應比特1，s2對應比特01以此類推。
s1，s2...中的NULL值不會被添加到結果中。
MAKE_SET(,,,) ,,,) col2
1的二進制值為0001，4的二進制值為0100，1與4進行異或操作之後的二進制值為0101，從右到左第一位和第三位為1。
MAKE_SET(1,'a','b','c')返回第一個字符串
MAKE_SET(1|4,'hello','nice','world') 返回從左端開始第一和第三個字符串組成的字符串
日期和時間函數
1、獲取當前日期的函數和獲取當前時間的函數
CURDATE()、CURRENT_DATE()、CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()
以上函數都是返回當前日期和時間值，MYSQL的函數數量的確比SQLSERVER多很多，SQLSERVER獲取當前時間和日期用的
函數是： ()
NOW()
返回UTC日期的函數和返回UTC時間的函數
MYSQL裡返回UTC日期和時間是分開的，而SQLSERVER裡是一起的
GETUTCDATE()
MYSQL
UTC_DATE(),UTC_TIME()
2、獲取月份的函數MONTHNAME(DATE)
MONTHNAME(DATE)函數返回日期date對應月份的英文全名
MONTHNAME()
返回8月份的英文
3、獲取季度、分鐘和秒鐘的函數
QUARTER(DATE)返回date對應的一年中的季度值，范圍是從1~4
使用QUARTER(DATE)函數返回指定日期對應的季度
QUARTER()
4月份在第二季度，所以返回2
4、MINUTE(TIME)返回time對應的分鐘數，范圍是從0~59
MINUTE()
返回10分鐘
5、SECOND(time) 返回time對應的秒數，范圍是從0~59
SECOND()
返回10秒
6、獲取日期的指定值的函數EXTRACT(type FROM date)
EXTRACT(type FROM date)這個函數跟SQLSERVER裡的DATEPART()函數是一樣的
獲取日期中的年份
SQLSERVER
(,)
MYSQL
EXTRACT( )
7、時間和秒鐘轉換的函數
TIME_TO_SEC(time)返回已轉化為秒的time參數，轉換公式為：小時*3600+分鐘*60+秒
TIME_TO_SEC()
SEC_TO_TIME(second)函數可以將秒轉換為小時、分鐘和秒數的second參數值
SEC_TO_TIME()
SEC_TO_TIME(second)函數和TIME_TO_SEC(time)函數互為反函數
8、計算日期和時間的函數
MYSQL裡計算日期和時間的函數比較多
增加日期：DATE_ADD(date,interval expr type)，ADDDATE(date,interval expr type)
減去日期：DATE_SUB(date,interval expr type)，SUBDATE(date,interval expr type)
增加時間：ADD_TIME(date,expr)
減去時間：SUBTIME(date,expr)
時間差：DATEDIFF()
日期和時間格式化：
DATE_FORMAT(date,format)
TIME_FORMAT(time,format)
返回日期時間字符串的顯示格式：GET_FORMAT(val_type,format_type)
相對來說，SQLSERVER在時間日期方面的計算就沒有那麼多函數了
基本上SQLSERVER利用下面兩個函數來通殺日期時間計算的場景
(),()
條件判斷函數
條件判斷函數也稱為流程控制函數，根據滿足的條件的不同，執行相應的流程。
MYSQL中進行條件判斷的函數有IF、IFNULL、CASE
雖然SQLSERVER裡也有IF和CASE，不過MYSQL裡的IF語句的語法跟SQLERVER有很大出入
1、IF(expr,v1,v2)函數
IF(expr,v1,v2)如果表達式expr是TRUE（expr<>0 and expr<>NULL），則IF()的返回值為v1；
否則返回值為v2。IF()的返回值為數字值或字符串值，具體情況視其所在語境而定
(,,)
1>2的結果為FALSE，IF(1>2,2,3)返回第二個表達式的值3。
TIPS：如果v1或v2中只有一個明確是NULL，則IF()函數的結果類型為非NULL表達式的結果類型。
2、IFNULL(V1,V2)函數
IFNULL(V1,V2)假如v1不為NULL，則IFNULL(V1,V2)的返回值為v1；否則其返回值為v2。
IFNULL()的返回值是數字或是字符串，具體情況視語境而定
IFNULL(,),IFNULL(,)
IFNULL(1,2)雖然第二個值也不為空，但返回結果依然是第一個值；
IFNULL(NULL,10)第一個值為空，因此返回“10”
注意：IFNULL(V1,V2)函數跟SQLSERVER裡的 () 函數不一樣
SQLSERVER裡的NULLIF函數
需要兩個參數,如果兩個指定的表達式等價，則返回null
例子：NULLIF(a,b)
說明：如果a和b是相等的，那麼返回NULL，如果不相等返回a
select NULLIF('eqeqweqwe','1') 結果是eqeqweqwe
select NULLIF(1,1) 結果是NULL
a和b的類型要一致
3、CASE函數
注意：一個CASE表達式的默認返回值類型是任何返回值的相容集合類型，但具體情況視其所在語境而定。
如果用字符串語境中，則返回結果為字符串。如果用在數字語境中，則返回結果為十進制、實數值或整數值
這個數據類型在拼接SQL語句的時候特別容易忽略，有時候會在拼接SQL語句的時候，case 後面的表達式或者when後面的表達式不一致報錯！
系統信息函數
1、獲取MYSQL版本號、連接數和數據庫名的函數
VERSION()返回指示MYSQL服務器版本的字符串。這個字符串使用utf8字符集
VERSION()
2、CONNECTION_ID()返回MYSQL服務器當前連接的次數，每個連接都有各自唯一的ID
查看當前用戶的連接數
CONNECTION_ID()
這裡返回1，返回值根據登錄的次數會有不同。
3、SHOW PROCESSLIST;
4、SHOW FULL PROCESSLIST;
processlist命令的輸出結果顯示了有哪些線程在運行，不僅可以查看當前所有的連接數，還可以查看當前的連接狀態
幫助識別出有問題的查詢語句等。
如果是root帳號，能看到所有用戶的當前連接。如果是其他普通帳號，則只能看到自己占用的連接。showprocesslist只能列出當前100條
如果想全部列出，可以使用SHOW FULL PROCESSLIST命令
SHOW PROCESSLIST
SHOW PROCESSLIST
show full processlist會看到連接使用的內存
show processlist
show full processlist
各個列的含義
（1）id列，用戶登錄mysql時，系統分配的“connection_id”
（2）user列，顯示當前用戶。如果不是root，這個命令就只顯示用戶權限范圍的sql語句
（3）host列，顯示這個語句是從哪個ip的哪個端口上發的，可以用來跟蹤出現問題語句的用戶
（4）db列，顯示這個進程目前連接的是哪個數據庫
（5）command列，顯示當前連接的執行的命令，一般取值為休眠（sleep），查詢（query），連接（connect）
（6）time列，顯示這個狀態持續的時間，單位是秒
（7）state列，顯示使用當前連接的sql語句的狀態，很重要的列，後續會有所有狀態的描述，state只是語句執行中的某一個狀態。一個sql語句，
以查詢為例
可能需要經過
copying to tmp table，
sorting result，
sending data
等狀態才可以完成
（8）info列，顯示這個sql語句，是判斷問題語句的一個重要依據。
5、DATABASE()和SCHEMA()函數返回使用utf8字符集的默認（當前）數據庫名
(),()
可以看到，兩個函數的作用相同
6、獲取用戶名的函數
USER()、CURRENT_USER()、CURRENT_USER、SYSTEM_USER()、SESSION_USER()
這幾個函數返回當前被MYSQL服務器驗證的用戶名和主機名組合。這個值符合確定當前登錄用戶
存取權限的MYSQL帳戶。一般情況下，這幾個函數的返回值是相同的。
(),(),()
返回結果指示了當前帳戶連接服務器的用戶名以及所連接的客戶主機，root為當前登錄的用戶名，localhost為登錄的主機名
7、獲取字符串的字符集和排序方式的函數
CHARSET(STR)返回字符串str自變量的字符集
CHARSET() ,CHARSET(( USING latin1)),CHARSET(VERSION())
CHARSET('abc')返回系統默認的字符集utf8；
CHARSET(CONVERT('abc' USING latin1))返回的字符集為latin1；
VERSION()返回的字符串使用utf8字符集，因此CHARSET返回結果為utf8
8、COLLATION(str)返回字符串str的字符排列方式
COLLATION(_latin2 ),COLLATION(( USING utf8))
可以看到，使用不同字符集時字符串的排列方式不同
9、獲取最後一個自動生成的ID值的函數
LAST_INSERT_ID()自動返回最後一個INSERT或UPDATE為AUTO_INCREMENT列設置的第一個發生的值
（1）一次插入一條記錄
首先創建表worker，其ID字段帶有AUTO_INCREMENT約束
AUTO_INCREMENT (
分別單獨向表worker插入2條記錄
worker (, worker (, worker
查看已經插入的數據可以發現，最後一條插入的記錄的ID字段值為2，使用LAST_INSERT_ID()查看最後自動生成的ID值
LAST_INSERT_ID()
可以看到，一次插入一條記錄時，返回值為最後一條插入記錄的ID值
（2）一次同時插入多條記錄
接下來，向表中插入多條記錄
worker (,),(,),(,)
查詢已經插入的記錄
worker
可以看到最後一條記錄的ID字段值為5，使用LAST_INSERT_ID()查看最後自動生成的ID值
LAST_INSERT_ID()
結果顯示，ID字段值不是5而是3，這是為什麽呢？
在向數據表插入一條記錄時，LAST_INSERT_ID()返回帶有AUTO_INCREMENT約束的字段最新生成的值2；繼續向表
中同時添加3條記錄，這時候因為當使用一條INSERT語句插入多個行時，LAST_INSERT_ID只返回插入的第一行數據
時產生的值，在這裡為第3條記錄。之所以這樣，是因為這使依靠其他服務器復制同樣的INSERT語句變得簡單
TIPS:LAST_INSERT_ID是與table無關的，如果向表a插入數據後，再向表b插入數據，LAST_INSERT_ID返回表b中的ID值
這裡跟SQLSERVER不一樣
使用下面腳本來測試，無論使用方式一還是方式二，當前的LAST_INSERT_ID都是最後一個值
. (,) ()
.
. ( N
.
. ( N
),(),( IDENT_CURRENT( .
加密函數
1、加密函數PASSWORD(STR)
PASSWORD(STR)從原文密碼str計算並返回加密後的密碼字符串，當參數為NULL時，返回NULL
PASSWORD()
MYSQL將PASSWORD函數加密後的密碼保存到用戶權限表中
TIPS:PASSWOR()函數在MYSQL服務器的鑒定系統中使用；不應將他用在個人應用程序中，PASSWORD（）函數加密是單向的（不可逆）
PASSWORD執行密碼加密與UNIX中密碼加密方式不同
2、加密函數MD5(str)
MD5(str)為字符串算出一個MD5 128比特校驗和。該值以32位十六進制數字的二進制字符串形式返回，若參數為NULL，則會返回NULL
MD5()
3、加密函數ENCODE(str,pswd_str)
ENCODE(str,pswd_str)使用pswd_str作為密碼，加密str。使用DECODE()解密結果，結果是一個和str長度相同的二進制字符串
ENCODE(,)
可以看到加密後的結果為亂碼
4、解密函數DECODE(crypt_str,pswd_str)
DECODE(crypt_str,pswd_str)使用pswd_str作為密碼，解密加密字符串crypt_str，crypt_str是由ENCODE()返回的字符串
DECODE(ENCODE(,),)
可以看到，解密出來的字符串
ENCODE()和DECODE互為反函數
其他函數
1、格式化函數FORMAT(x,n)
FORMAT(x,n)將數字x格式化，並以四捨五入的方式保留小數點後n位，結果以字符串的形式返回。
若n為0，則返回結果函數不含小數部分
FORMAT(,)
FORMAT(12332.123465,4)保留4位小數點值，並進行四捨五入，結果為12,332.1235
2、不同進制的數字轉換的函數
CONV(N,from_base,to_base)函數進行不同進制數間的轉換。
CONV(,,)
CONV('a',16,2)將十六進制的a轉換為二進制表示的數值。
3、IP地址與數字相互轉換的函數
INET_ATON(expr)給出一個作為字符串的網絡地址的點地址表示，返回一個代表該地址數值的整數。
地址可以是4或8比特地址
INET_NTOA(expr)給定一個數字網絡地址（4或8比特），返回作為字符串的該地址的點地址表示。
4、加鎖函數和解鎖函數
GET_LOCK(str,timeout)設法使用字符串str給定的名字得到一個鎖，超時為timeout秒。
RELEASE_LOCK(str)解開被GET_LOCK()獲取的，用字符串str所命名的鎖。
IS_FREE_LOCK(str)檢查名為str的鎖是否可以使用
IS_USED_LOCK(str)檢查名為str的鎖是否正在被使用
5、重復執行指定操作的函數
BENCHMARK(count,expr)函數重復count次執行表達式expr。他可以用於計算MYSQL處理表達式的速度。
結果值通常為0（0只是表示處理過程很快，並不是沒有花費時間）
另一個作用是他可以在MYSQL客戶端內部報告語句執行的時間。
首先，使用PASSWORD函數加密密碼
PASSWORD()
可以看到PASSWORD()函數執行花費的時間為0.00098秒
下面使用BENCHMARK函數重復執行PASSWORD操作500000次
BENCHMARK(,PASSWORD())
由此可以看出，使用BENCHMARK執行500000次的時間為0.49690秒，明顯比執行一次的時間延長了。
TIPS：BENCHMARK報告的時間是客戶端經過的時間，而不是在服務器端的CPU時間，每次執行後報告的時間並不一定是相同的。
6、改變字符集的函數
CONVERT(...using...)帶有USING的CONVERT()函數被用來在不同的字符集之間轉化數據。
CHARSET(),CHARSET(( USING latin1))
默認為utf8字符集，通過CONVERT()將字符串“string”的默認字符集改為latin1
7、改變數據類型的函數
CAST(x,AS type)和CONVERT(x,type)函數將一個類型的值轉換為另一個類型的值，可轉換的type有：
BINARY、CHAR(n)、DATE、TIME、DATETIME、DECIMAL、SIGNED、UNSIGNED
在SQLSERVER裡也是使用這兩個函數進行數據類型轉換的~
( ()),(,TIME)
可以看到， CAST(100 AS CHAR(2))將整數數據100轉換為帶有2個顯示寬度的字符串類型，結果為10
CONVERT('2013-8-9 12:12:12',TIME)將DATETIME類型的值，轉換為TIME類型值，結果為“12:12:12”
總結
這一節簡單介紹了MYSQL裡的一些函數，並且比較了與SQLSERVER的區別
TIPS:
1、MYSQL中，日期時間以字符串形式存儲在數據表中，因此可以使用字符串函數分別截取日期時間值的不同部分
2、修改默認的字符集，更改MYSQL默認的字符集，在Windows中，只需要修改my.ini，該文件在MYSQL安裝目錄下。
修改配置文件中的default-character-set和character-set-server參數值，將其改為想要的字符集名稱，如：
gbk、gb2312、latin1等，修改完之後，重啟MYSQL服務，即可生效。
如果不確定當前使用的字符集，可以使用下面的SQL語句來查看當前字符集進行對比
SHOW VARIABLES
如有不對的地方，歡迎大家拍磚o(∩_∩)o
