MySQL常用函數整理

點擊關註上方「SQL資料庫開發」，

設為「置頂或星標」，第一時間送達乾貨

之前已經整理過了SQL Server的一些常用函數，有需要的也可以一起看一下

SQL Server常用函數整理

今天繼續分享MySQL的一些常用函數。

1、數學函數

ABS(x)         BIN(x)         CEILING(x)     EXP(x)         FLOOR(x)       GREATEST(x1,x2,...,xn)                LEAST(x1,x2,...,xn)                   LN(x)           LOG(x,y)        MOD(x,y)        PI()            RAND()          ROUND(x,y)      SIGN(x)         SQRT(x)         TRUNCATE(x,y)   
（提示：可以左右滑動代碼）
2、聚合函數
AVG(X)            COUNT(X)          MIN(X)            MAX(X)            SUM(X)            GROUP_CONCAT(X)   
3、字符串函數
ASCII(char)        BIT_LENGTH(str)    CONCAT(s1,s2...,sn)                    CONCAT_WS(sep,s1,s2...,sn)                   INSERT(str,x,y,instr)                    FIND_IN_SET(str,list)                   LCASE(str)或LOWER(str)                    LEFT(str,x)        LENGTH(s)          LTRIM(str)         POSITION(substr,str)                    QUOTE(str)         REPEAT(str,srchstr,rplcstr)                   REVERSE(str)       RIGHT(str,x)       RTRIM(str)         STRCMP(s1,s2)      TRIM(str)          UCASE(str)或UPPER(str)                    
4、日期和時間函數
CURDATE()或CURRENT_DATE()                    CURTIME()或CURRENT_TIME()                    DATE_ADD(date,INTERVAL int keyword)                   例如SELECT DATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);
DATE_FORMAT(date,fmt)                     DATE_SUB(date,INTERVAL int keyword)                   例如SELECT DATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);
DAYOFWEEK(date)    DAYOFMONTH(date)   DAYOFYEAR(date)    DAYNAME(date)      FROM_UNIXTIME(ts,fmt)                     HOUR(time)         MINUTE(time)       MONTH(date)        MONTHNAME(date)    NOW()              QUARTER(date)      例如SELECT QUARTER(CURRENT_DATE);
WEEK(date)         YEAR(date)         例如，獲取當前系統時間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
5、加密函數
AES_ENCRYPT(str,key)                     AES_DECRYPT(str,key)                     DECODE(str,key)    ENCRYPT(str,salt)  ENCODE(str,key)    MD5()              PASSWORD(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') ;
6、控制流函數
CASE WHEN [test1] THEN [result1]...ELSE [default] END                     CASE [test] WHEN [val1] THEN [result]...ELSE [default] END                      IF(test,t,f)        IFNULL(arg1,arg2)   NULLIF(arg1,arg2)   
這些函數的第一個是IFNULL()，它有兩個參數，並且對第一個參數進行判斷。如果第一個參數不是NULL，函數就會向調用者返回第一個參數；如果是NULL,將返回第二個參數。例如SELECT IFNULL(1,2), IFNULL(NULL,10),IFNULL(4*NULL,'false');
NULLIF()函數將會檢驗提供的兩個參數是否相等，如果相等，則返回NULL，如果不相等，就返回第一個參數。例如SELECT  NULLIF(1,1), NULLIF('A','B'), NULLIF(2+3,4+1);
MySQL的IF()函數也可以建立一個簡單的條件測試，這個函數有三個參數，第一個是要被判斷的表達式，如果表達式為真，IF()將會返回第二個參數，如果為假，IF()將會返回第三個參數。例如SELECT IF(1<10,2,3),IF(56>100,'true','false');IF()函數在只有兩種可能結果時才適合使用。然而，在現實世界中，我們可能發現在條件測試中會需要多個分支。在這種情況下，它和PHP及Perl語言的switch-case條件例程一樣。
CASE函數的格式有些複雜，通常如下所示：CASE [expression to be evaluated]WHEN [val 1] THEN [result 1]WHEN [val 2] THEN [result 2]WHEN [val 3] THEN [result 3].WHEN [val n] THEN [result n]ELSE [default result]END這裡，第一個參數是要被判斷的值或表達式，接下來的是一系列的WHEN-THEN塊，每一塊的第一個參數指定要比較的值，如果為真，就返回結果。所有的WHEN-THEN塊將以ELSE塊結束，當END結束了所有外部的CASE塊時，如果前面的每一個塊都不匹配就會返回ELSE塊指定的默認結果。如果沒有指定ELSE塊，而且所有的WHEN-THEN比較都不是真，MySQL將會返回NULL。CASE函數還有另外一種句法，有時使用起來非常方便，如下：CASEWHEN [conditional test 1] THEN [result 1]WHEN [conditional test 2] THEN [result 2]ELSE [default result]END這種條件下，返回的結果取決於相應的條件測試是否為真。例如：SELECT  CASE 'green'      WHEN 'red' THEN 'stop'      WHEN 'green' THEN 'go' END;
SELECT CASE  9 WHEN 1 THEN 'a'WHEN 2 THEN 'b' ELSE 'N/A' END;
SELECT CASE WHEN (2+2)=4 THEN 'OK' WHEN (2+2)<>4 THEN 'not OK' END AS STATUS;
SELECT Name,IF((IsActive = 1),'已激活','未激活') AS RESULT FROM UserLoginInfo;
SELECT fname,lname,(math+sci+lit) AS total,CASE WHEN (math+sci+lit) < 50 THEN 'D'     WHEN (math+sci+lit) BETWEEN 50 AND 150 THEN 'C'     WHEN (math+sci+lit) BETWEEN 151 AND 250 THEN 'B'ELSE 'A' END AS grade FROM marks ;
SELECT IF(ENCRYPT('sue','ts')=upass,'allow','deny') AS LoginResultFROM users WHERE uname = 'sue';
7、格式化函數
DATE_FORMAT(date,fmt)                    FORMAT(x,y)       INET_ATON(ip)     INET_NTOA(num)    TIME_FORMAT(time,fmt)                     其中最簡單的是FORMAT()函數，它可以把大的數值格式化為以逗號間隔的易讀的序列。例如SELECT FORMAT(34234.34323432,3) ;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) ;
8、類型轉化函數
為了進行數據類型轉化，MySQL提供了CAST()函數，它可以把一個值轉化為指定的數據類型。類型有：BINARY,CHAR,DATE,TIME,DATETIME,SIGNED,UNSIGNED例如SELECT CAST(NOW() AS SIGNED INTEGER),CURDATE()+0;SELECT 'f'=BINARY 'F','f'=CAST('F' AS BINARY);
9、系統信息函數
DATABASE()         --返回當前資料庫名BENCHMARK(count,expr)                      --將表達式expr重複運行count次CONNECTION_ID()    --返回當前客戶的連接IDFOUND_ROWS()       --返回最後一個SELECT查詢進行檢索的總行數USER()或SYSTEM_USER()                     --返回當前登陸用戶名VERSION()          --返回MySQL伺服器的版本例如SELECT DATABASE(),VERSION(),USER();SELECTBENCHMARK(9999999,LOG(RAND()*PI()));--該例中,MySQL計算LOG(RAND()*PI())表達式9999999次。