常用函數 · mysql從入門到精通

[TOC] ## 點擊查看官方文檔： ## 數學函數點擊進入詳細參考 ### 其他 BIN(x) //數字轉成二進制（OCT返回八進制，HEX返回十六進制） PI() //輸出圓周率 SIGN(x) 返回代表數字x的符號的值 SQRT(x) 返回一個數的平方根 POW(6,2) select POW(6,2) //返回6的2次方 ABS(x) 返回x的絕對值 EXP(x) 返回值e（自然對數的底）的x次方 LN(x) 返回x的自然對數 LOG(x,y)返回x的以y為底的對數 ### 取模/隨機數/四舍五入... MOD(x,y) //取x和y的模 RAND() //輸出0-1之間的隨機數 ROUND(x,y) //參數x先四舍五入，在返回y位小數的值 TRUNCATE(x,y) //保留y位小數（與ROUND最大的區別是不會進行四舍五入） ### 最大最小相關 CEILING(x) //返回不小X的最小整數值，返回值轉為一個BIGINT；正數：向上取整，負數：向下取整 FLOOR(x) //返回不大于X的最大整數值，返回值轉為一個BIGINT；正數：向下取整，負數：向下取整 GREATEST(x1,x2,...,xn) //找每行n列中最大的值 LEAST(x1,x2,...,xn) //找集合中最小的值 ### 角度相關 RADIANS(x) //將角度轉換為弧度 SELECT RADIANS(180) DEGREES(x) //將弧度轉換為角度 SELECT DEGREES(3.1415926535898) ## 聚合函數(常用于GROUP BY從句的SELECT查詢中) AVG(col)返回指定列的平均值 COUNT(col)返回指定列中非NULL值的個數 count(*) //將返回表格中所有存在的行的總數包括值為null的行， count(列名) //將返回表格中不包括null以外的所有行的總數(有默認值的列也會被計入） count(1) //1代表你這個查詢的表里的第一個字段這里用1，也是為了方便，當然如果數據量較大的話，也可以提高速度，因為寫count(*)的話會所有列掃描，這里用1的話或者用字段名的話，只掃描你寫的那個列。不包括NULL列。 MIN(col)返回指定列的最小值 MAX(col)返回指定列的最大值 SUM(col)返回指定列的所有值之和 GROUP_CONCAT(col) 返回由屬于一組的列值連接組合而成的結果 ## 字符串函數 ### 替換 INSERT(str,x,y,instr) //替換字符串。將str第x位置開始，y個字符長的，子串替換為字符串instr。x的位置從1開始 REPLACE(s,s1,s2) // 將字符串s2替代字符串s中的字符串s1 SELECT REPLACE('abc','a','x') //輸出xbc ### 填充 LPAD(s1,len,s2) //字符串s2來填充s1的開始處，使字符串長度達到len SELECT LPAD('abc',5,'x') //會輸出xxabc，雖然只輸入了一個x，但系統會根據長度5，而自動補全 RPAD(s1,len,s2) //字符串s2來填充s1的結尾處，使字符串的長度達到len SELECT RPAD('abc',5,'x') //會輸出xxabc，雖然只輸入了一個x，但系統會根據長度5，而自動補全 ### 查找 FIND_IN_SET(str,list) //查找字符串。list的格式是以逗號分隔的，如果匹配上str，返回str在list中的位置。返回的位置最小是1 POSITION(substr,str) //首次出現的位置。返回子串substr在字符串str中第一次出現的位置 LOCATE(substr,str), LOCATE(substr,str,pos) 第一個語法返回substr在字符串str 的第一個出現的位置。第二個語法返回子符串 substr 在字符串str，從pos處開始的第一次出現的位置。如果substr 不在str 中，則返回值為0 。 ### 截取 SUBSTRING(s,n,len) //獲取從字符串s中的第n個位置開始長度為len的字符串。注意：從1開始 SELECT SUBSTRING('abc',1,2) //輸出ab MID() //和SUBSTRING一樣 LEFT(str,x) //從左邊截取。返回字符串str中最左邊的x個字符 RIGHT(str,x) //從右邊截取。返回字符串str中最右邊的x個字符 LTRIM(str) 從字符串str中切掉開頭的空格 RTRIM(str) 返回字符串str尾部的空格 TRIM(str)去除字符串首部和尾部的所有空格 SELECT TRIM('@' FROM '@@abc@@') //輸出abc ### 拼接 CONCAT(s1,s2...,sn) //將s1,s2...,sn連接成字符串。如果有任何一個參數為null，則返回值為nul CONCAT_WS(sep,s1,s2...,sn) //和CONCAT一樣。只是多了個指定分隔符的功能，并用sep字符間隔 ### 計算 BIT_LENGTH(str)返回字符串的比特長度 CHAR_LENGTH(s) //計算中文的長度 LENGTH(s) //計算英文字符數 ### 其他 ASCII(char)返回字符的ASCII碼值 LCASE(str) 或 LOWER(str) //將字符串轉換成小寫 UPPER() //轉換成大寫 field() //以字段中的值排序 QUOTE(str) //用反斜杠轉義str中的單引號 REVERSE(str) //倒序輸出。反轉字符串str的結果 REPEAT(str,srchstr,rplcstr) //重復輸出字符串。返回字符串str重復x次的結果 STRCMP(s1,s2)比較字符串s1和s2 SELECT STRCMP('text', 'text2'); //第2個參數大，返回-1 SELECT STRCMP('text2', 'text'); //第1個參數大，返回1 SELECT STRCMP('text', 'text'); //相等返回0 ELT(n,s1,s2,...) //返回第n個字符串 SELECT ELT(2,'a','b','c') //輸出b。注意2、3、4參數都是字符串 ## 日期和時間函數 FROM_UNIXTIME(ts,fmt) //格式化時間戳ts，根據指定的fmt格式 SELECT FROM_UNIXTIME(1320981071) //輸出2011-11-11 11:11:11 ### 計算相關 DATEDIFF(d1,d2) //計算日期2個日期之間相差的天數 SELECT DATEDIFF('2018-07-09','2018-07-11') //-2;因為第是第1個參數減第2個參數 SELECT DATEDIFF('2018-07-11','2018-07-09') //2; 第1個參數大，就是正值，否則是負值 ADDDATE(d,n) //計算其實日期d加上n天的日期 select ADDDATE('2018-07-10',16) //輸出2018-07-26；加了16天，默認是天 SELECT ADDDATE('2011-11-11 11:11:11', INTERVAL 5 MINUTE) //輸出2011-11-11 11:16:11 (TYPE的取值與上面那個列出來的函數類似)；加了5分鐘 SUBDATE(d,n) /日期d減去n天后的日期 SELECT SUBDATE('2011-11-11 11:11:11', 1) //2011-11-10 11:11:11 (默認是天) SUBDATE(d,INTERVAL expr type) //日期d減去一個時間段后的日期 SELECT SUBDATE('2011-11-11 11:11:11', INTERVAL 5 MINUTE) //2011-11-11 11:06:11 (TYPE的取值與上面那個列出來的函數類似）；減去5分鐘 ADDTIME(t,n) //時間t加上n秒的時間（上面是天數和分鐘，這里是秒） SELECT ADDTIME('2011-11-11 11:11:11', 5) //2011-11-11 11:11:16 ；加了6秒 SUBTIME(t,n) //時間t減去n秒的時間 SELECT SUBTIME('2011-11-11 11:11:11', 5) //2011-11-11 11:11:06 (秒)；減了6秒 DATE_ADD(date,INTERVAL int keyword) //返回日期date加上間隔時間int的結果(int必須按照關鍵字進行格式化) SELECT DATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH); //當前日期+6個月 DATE_SUB(date,INTERVAL int keyword) //返回日期date減去間隔時間int的結果(int必須按照關鍵字進行格式化) SELECT DATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH); //當前日期-6個月 DATE_FORMAT(字段,'%Y') //截取時間，格式化時間計算本月的第一天和最后一天 select DATE_ADD(curdate(),interval -day(curdate())+1 day) as 開始時間; //本月的開始時間 select last_day(curdate()) as 結束時間; //本月的最后一天 ### 當前時間相關 CURDATE()或CURRENT_DATE() //返回當前的日期。格式為：2018-07-10 CURTIME()或CURRENT_TIME() //返回當前的時間。格式為：00:01:57 UNIX_TIMESTAMP() //當前時間戳 SELECT UNIX_TIMESTAMP() //輸出當前時間戳 SELECT UNIX_TIMESTAMP('2011-11-11 11:11:11') //輸出固定時間日期的時間戳 NOW() 返回當前的日期和時間 SELECT NOW() ;//2017-04-25 19:44:24 是年/月/星期，的第幾天 DAYOFWEEK(date) 返回date所代表的一星期中的第幾天(1~7) DAYOFMONTH(date) 返回date是一個月的第幾天(1~31) DAYOFYEAR(date) 返回date是一年的第幾天(1~366) 年月日/時分秒/星期/周/季度... SECOND(t) //返回t中的秒鐘值 SELECT SECOND('1:2:3') //輸出 TIME_TO_SEC(t) //將時間t轉換為秒 SELECT TIME_TO_SEC('1:12:00') //輸出4320 SEC_TO_TIME(s) //將秒轉換為時分秒的格式 SELECT SEC_TO_TIME(4320) //輸出01:12:00 HOUR(time) 返回time的小時值(0~23) MINUTE(time) 返回time的分鐘值(0~59) MONTH(date) 返回date的月份值(1~12) MONTHNAME(date) 返回date的英文月份名 YEAR(date) 返回日期date的年份(1000~9999) DAYNAME(date) //返回date的英文星期名 QUARTER(date) 返回date在一年中的季度(1~4) WEEK(date) //返回日期date為一年中第幾周(0~53) select WEEKOFYEAR('2016-2-2'); //5 單純輸出第幾周(0-53周) select yearweek ('2016-2-2'); //201605 年+第幾周一些示例：獲取當前系統時間：SELECT FROM_UNIXTIME(UNIX_TIMESTAMP()); SELECT EXTRACT(YEAR_MONTH FROM CURRENT_DATE); SELECT EXTRACT(DAY_SECOND FROM CURRENT_DATE); SELECT EXTRACT(HOUR_MINUTE FROM CURRENT_DATE); SELECT PERIOD_DIFF(200302,199802); //計算兩個日期值之間的差值(月數) //在Mysql中計算年齡： SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)),'%Y')+0 AS age FROM employee; //如果Brithday是未來的年月日的話，計算結果為0。 //下面的SQL語句計算員工的絕對年齡，即當Birthday是未來的日期時，將得到負值。 SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') -(DATE_FORMAT(NOW(), '00-%m-%d') <DATE_FORMAT(birthday, '00-%m-%d')) AS age from employee //在Mysql中計算年齡： SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)),'%Y')+0 AS age FROM employee; //如果Brithday是未來的年月日的話，計算結果為0。 //下面的SQL語句計算員工的絕對年齡，即當Birthday是未來的日期時，將得到負值。 SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') -(DATE_FORMAT(NOW(), '00-%m-%d') <DATE_FORMAT(birthday, '00-%m-%d')) AS age from employee ## 加密函數 AES_ENCRYPT(str,key) 返回用密鑰key對字符串str利用高級加密標準算法加密后的結果，調用AES_ENCRYPT的結果是一個二進制字符串，以BLOB類型存儲 AES_DECRYPT(str,key) 返回用密鑰key對字符串str利用高級加密標準算法解密后的結果 ENCODE(str,key) 使用key作為密鑰加密字符串str，調用ENCODE()的結果是一個二進制字符串，它以BLOB類型存儲 DECODE(str,key) 使用key作為密鑰解密加密字符串str SELECT ENCODE('123','xxoo') //輸出;vx SELECT DECODE(';vx','xxoo') //輸出123 ENCRYPT(str,salt) 使用UNIXcrypt()函數，用關鍵詞salt(一個可以惟一確定口令的字符串，就像鑰匙一樣)加密字符串str MD5() 計算字符串str的MD5校驗和 PASSWORD(str) 返回字符串str的加密版本，這個加密過程是不可逆轉的，和UNIX密碼加密過程使用不同的算法。 SHA() 計算字符串str的安全散列算法(SHA)校驗和示例： SELECT ENCRYPT('root','salt'); SELECT ENCODE('xufeng','key'); SELECT DECODE(ENCODE('xufeng','key'),'key');#加解密放在一起 SELECT AES_ENCRYPT('root','key'); SELECT AES_DECRYPT(AES_ENCRYPT('root','key'),'key'); SELECT MD5('123456'); SELECT SHA('123456'); ## 控制流函數 MySQL有4個函數是用來進行條件操作的，這些函數可以實現SQL的條件邏輯，允許開發者將一些應用程序業務邏輯轉換到數據庫后臺。 ### MySQL控制流函數：第1種：IF(test,t,f) 如果test是真，返回t；否則返回f SELECT IF(1>0,'true','false'); // 返回true //if判斷不好使，如果字段的時分在between的范圍內，那么會輸出1，因為第2個參數是true SELECT nextFollowupTime, DATE_FORMAT(nextFollowupTime,'%Y-%m-%d'), IF (DATE_FORMAT(nextFollowupTime,'%H:%i') between '00:01' and '23:59' ,true,DATE_FORMAT(nextFollowupTime, '%H:%i')) FROM customer_folowup LIMIT 100 第2種：IFNULL(arg1,arg2) 如果arg1不是null，返回arg1，否則返回arg2 SELECT IFNULL(NULL, 1 ) ; // 返回 1 SELECT IFNULL(0 , 1 ) ; // 返回 0 第3種：NULLIF(arg1,arg2) 如果arg1=arg2，返回NULL，否則返回arg1（第2個參數，永遠不會返回） SELECT NULLIF(1,1),NULLIF('A','B'),NULLIF(2+3,4+1); 第4種：條件判斷中會需要多個分支。MySQL提供了CASE函數，它和PHP及Perl語言的switch-case條件一樣。 CASE函數的格式有些復雜，通常如下所示： CASE [expression to be evaluated] //要被判斷的值或表達式 //接下來的是一系列的WHEN-THEN塊，每一塊的第一個參數指定要比較的值，如果為真，就返回結果 WHEN [val 1] THEN [result 1] //相當于val 1和case的值，做比較，如果為true。返回result 1 WHEN [val 2] THEN [result 2] WHEN [val 3] THEN [result 3] ...... WHEN [val n] THEN [result n] ELSE [default result] //所有的WHEN-THEN塊將以ELSE塊結束 //當END結束了所有外部的CASE塊時，如果WHEN-THEN塊的每一個塊都不匹配,就會返回ELSE塊指定的默認結果。 //如果沒有指定ELSE塊，而且所有的WHEN-THEN比較都不是真，MySQL將會返回NULL END 指定要比較的值，如果為真，就返回結果 WHEN [val 1] THEN [result 1] //相當于val 1和case的值，做比較，如果為true。返回result 1 WHEN [val 2] THEN [result 2] WHEN [val 3] THEN [result 3] ...... WHEN [val n] THEN [result n] ELSE [default result] //所有的WHEN-THEN塊將以ELSE塊結束 //當END結束了所有外部的CASE塊時，如果WHEN-THEN塊的每一個塊都不匹配,就會返回ELSE塊指定的默認結果。 //如果沒有指定ELSE塊，而且所有的WHEN-THEN比較都不是真，MySQL將會返回NULL END 案例： SELECT id, CASE followupType //case開始 WHEN 'CALL' THEN '打電話' //如果followupType字段的值 == CALL 顯示打電話 WHEN 'APPOINTMENT' THEN '預約上門' WHEN 'SMS' THEN '短信' WHEN 'OTHER' THEN '其他' WHEN 'INTERVIEW' THEN '面談' ELSE '啦啦啦' END //end 相當于是結束case FROM customer_folowup ## 其他函數 ### 格式化函數 DATE_FORMAT(date,fmt) 依照字符串fmt格式化日期date值 TIME_FORMAT(time,fmt) 依照字符串fmt格式化時間time值 FORMAT(x,y) //把大的數值格式化為以逗號間隔的易讀的序列。把x格式化為以逗號隔開的數字序列，y是結果的小數位數 SELECT FORMAT(3.1415926,3) //輸出3.142；取小數點后3位 ### 不同進制的數字進行轉換 ASCII(s) 返回字符串s的第一個字符的ASCII碼； BIN（x）返回x的二進制編碼； HEX(x) 返回x的十六進制編碼； OCT(x) 返回x的八進制編碼； CONV(x,f1,f2) 返回f1進制數變成f2進制數； ### IP地址與數字相互轉換的函數 INET_ATON(ip) 返回IP地址的數字表示 INET_NTOA(num) 返回數字所代表的IP地址 SELECT INET_ATON('192.168.0.1') //輸出 3232235521 SELECT INET_NTOA(3232235521) //輸出 192.168.0.1 ### 加鎖解鎖 GET_LOCK(name,time)函數定義一個名稱為nam、持續時間長度為time秒的鎖。如果鎖定成功，則返回1；如果嘗試超時，則返回0；如果遇到錯誤，返回NULL。 RELEASE_LOCK(name)函數解除名稱為name的鎖。如果解鎖成功，則返回1；如果嘗試超時，返回0了如果解鎖失敗，返回NULL； IS_FREE_LOCK(name)函數判斷是否已使用名為name的鎖定。如果使用，返回0，否則，返回1； SELECT GET_LOCK('MySQL',10) //持續鎖定10秒，輸出1 SELECT RELEASE_LOCK('MySQL') //解鎖成功，輸出1 SELECT IS_FREE_LOCK('MySQL') //是否存在，不存在輸出1 ### 重復指定執行操作的函數　　BENCHMARK(count.expr)函數將表達式expr重復執行count此，然后返回執行時間。該函數可以用來判斷MySQL處理表達式的速度。 SELECT BENCHMARK(10000,NOW()) //輸出0 返回系統時間1萬 ## 轉換數據類型 CAST(x AS type) CONVERT(x,type) 　　這兩個函數只對BINARY、CHAR、DATE、DATETIME、TIME、SIGNED INTEGER、UNSIGNED INTEGER。 SELECT CAST('123' AS UNSIGNED INTEGER) + 1 //輸出124 SELECT '123' + 1 //輸出124 其實MySQL能默認轉換（字符串+整數） SELECT CAST(NOW() AS DATE) //輸出2014-12-18 CONVERT(10/6,SIGNED INTEGER) #10/6的值，轉化為int。值不精確會自動向上取整，改成浮點數也不行 //計算年齡 SELECT 雇員ID,姓名,職務,cast(strftime('%Y','now') as int)-cast(strftime('%Y',出生日期) as int)+1 as 年齡 FROM 雇員示例： SELECT DATE_FORMAT(NOW(),'%W,%D %M %Y %r'); SELECT DATE_FORMAT(NOW(),'%Y-%m-%d'); SELECT DATE_FORMAT(19990330,'%Y-%m-%d'); SELECT DATE_FORMAT(NOW(),'%h:%i %p'); SELECT INET_ATON('10.122.89.47'); SELECT INET_NTOA(175790383); ## 系統信息函數 DATABASE() 返回當前數據庫名 BENCHMARK(count,expr) 將表達式expr重復運行count次 CONNECTION_ID() 返回當前客戶的連接ID FOUND_ROWS() 返回最后一個SELECT查詢進行檢索的總行數 USER()或SYSTEM_USER() 返回當前登陸用戶名 VERSION() 返回MySQL服務器的版本 CHARSET(str) 返回字符串str的字符集 SELECT CHARSET('ABC') //輸出utf-8；查看字符集 SELECT CHARSET(CONVERT('ABC' USING gbk)) //輸出gbk；設置字符集 COLLATION(str)返回字符串str的字符排列方式 LAST_INSERT_ID()返回最近生成的AUTO_INCREMENT值示例： SELECT DATABASE(),VERSION(),USER(); SELECTBENCHMARK(9999999,LOG(RAND()*PI()));#該例中,MySQL計算LOG(RAND()*PI())表達式9999999次。 ### 其他 coalesce -- 某個字段是null，用其他值替換Null; 字段值不是null的該函數失效 COALESCE(x, y) 等同于 (CASE WHEN x IS NOT NULL THEN x ELSE y END)