本文講述MySql5.x中最常用的一些函數(shù)，包括控制流程函數(shù)、字符串函數(shù)、數(shù)值函數(shù)、日期和時(shí)間函數(shù)，對(duì)不常用的函數(shù)略去不講，以便開(kāi)發(fā)人員查閱。

一．             控制流程函數(shù)

1．CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

該語(yǔ)句用于在不同情況時(shí)，執(zhí)行不同的語(yǔ)句，實(shí)例如下：

Eg1. SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;

輸出：true

Eg2. SELECT CASE BINARY 'B' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;

       輸出：NULL

2．IF(expr1,expr2,expr3)

       如果expr1返回true，則該表達(dá)式返回expr2，否則返回expr3。

Eg1. SELECT IF(1<2,'yes ','no');

       輸出：'yes '

Eg2. SELECT IF(STRCMP('test','test1'),'no','yes');

       上例中STRCMP(expr1, expr2)這個(gè)函數(shù)在expr1小于expr2時(shí)，返回-1，相等時(shí)返回0，其余的情況返回1，在上例中返回-1，因此上例的輸出為：'yes'

3．IFNULL(expr1,expr2)

       該函數(shù)在expr1為NULL時(shí)，返回expr2，否則，返回expr1。

Eg1. SELECT IFNULL(1,0);

       輸出：1

Eg2. SELECT IFNULL(NULL,10);

       輸出：10

4．NULLIF(expr1,expr2)

       如果expr1=expr2，則返回NULL，否則，返回expr1。

Eg1. SELECT NULLIF(1,1);

       輸出：NULL

Eg2. SELECT NULLIF(3,4);

       輸出：3

二．             字符串函數(shù)

1.       ASCII(str)

返回值為字符串str 的最左字符的數(shù)值。假如str為空字符串，則返回值為 0 。假如str 為NULL，則返回值為 NULL。 ASCII()用于帶有從 0到255的數(shù)值的字符。

Eg1. SELECT ASCII('2');

       輸出：50

Eg2. SELECT ASCII('de');

       輸出：100

2.       CONCAT(str1,str2,...) 

返回結(jié)果為連接參數(shù)產(chǎn)生的字符串。如有任何一個(gè)參數(shù)為NULL ，則返回值為 NULL。或許有一個(gè)或多個(gè)參數(shù)。

Eg1. SELECT CONCAT('Hello', ',', '阿蜜果');

       輸出：'Hello, 阿蜜果'

Eg2. SELECT CONCAT('Hello', NULL, '阿蜜果');

       輸出：NULL

3.       CONCAT_WS(separator,str1,str2,...)

CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。   第一個(gè)參數(shù)是其它參數(shù)的分隔符。分隔符的位置放在要連接的兩個(gè)字符串之間。分隔符可以是一個(gè)字符串，也可以是其它參數(shù)。如果分隔符為 NULL，則結(jié)果為 NULL。函數(shù)會(huì)忽略任何分隔符參數(shù)后的NULL值。

Eg1. SELECT CONCAT_WS(',', 'Hello', '阿蜜果');

       輸出：'Hello, 阿蜜果'

Eg. SELECT CONCAT_WS(',', 'Hello', NULL, '阿蜜果');

       輸出：'Hello, 阿蜜果'

4.       INSERT(str,pos,len,newstr)

返回字符串 str, 其子字符串起始于 pos位置和長(zhǎng)期被字符串 newstr取代的len字符。 如果pos超過(guò)字符串長(zhǎng)度，則返回值為原始字符串。 假如len的長(zhǎng)度大于其它字符串的長(zhǎng)度，則從位置pos開(kāi)始替換。若任何一個(gè)參數(shù)為null，則返回值為NULL。

Eg1. SELECT INSERT('hello,Amigo', 7, 5, 'Locus');

       輸出：hello,Locus

5.       INTER(str, substr)

返回字符串 str中子字符串的第一個(gè)出現(xiàn)位置。

Eg1. SELECT INSTR('hello,Amigo', 'Amigo');

       輸出：7

6.       LENGTH(str)

返回值為字符串str的長(zhǎng)度，單位為字節(jié)。一個(gè)多字節(jié)字符算作多字節(jié)。

Eg1. SELECT LENGTH('Amigo');

       輸出：5

7.       LOWER(str)

返回字符串 str以及所有根據(jù)最新的字符集映射表變?yōu)樾?xiě)字母的字符。

Eg1. SELECT LOWER('AMIGO');

       UPPER(str)函數(shù)的用法與此類(lèi)似，在此不再贅述。不過(guò)它是將字符串轉(zhuǎn)換為大寫(xiě)的。

       輸出：amigo

8.       LPAD(str, len, padstr)

返回字符串 str, 其左邊由字符串padstr 填補(bǔ)到len字符長(zhǎng)度。假如str 的長(zhǎng)度大于len, 則返回值被縮短至len字符。

Eg1. SELECT LPAD('hi',4,'!!');

       輸出：'!!hi'                      

Eg2. SELECT LPAD('hi',1,'!!');

       輸出：'h'

       RPAD(…)函數(shù)用法與此類(lèi)似，在此不再贅述。

9.       REPEAT(str,count)

返回一個(gè)由重復(fù)的字符串str 組成的字符串，字符串str的數(shù)目等于count。 若 count <= 0,則返回一個(gè)空字符串。若str或count 為 NULL，則返回NULL。

Eg1. SELECT REPEAT('Amigo', 2);

       輸出：'AmigoAmigo'

10.   SUBSTRING(str,pos), SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)

       不帶有len 參數(shù)的格式從字符串str返回一個(gè)子字符串，起始于位置 pos。帶有len參數(shù)的格式從字符串str返回一個(gè)長(zhǎng)度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式為標(biāo)準(zhǔn) SQL 語(yǔ)法。也可能對(duì)pos使用一個(gè)負(fù)值。假若這樣，則子字符串的位置起始于字符串結(jié)尾的pos 字符，而不是字符串的開(kāi)頭位置。在以下格式的函數(shù)中可以對(duì)pos 使用一個(gè)負(fù)值。

Eg1. SELECT SUBSTRING('hello,Amigo',6);       

       輸出：Amigo

Eg2. SELECT SUBSTRING('hello,Amigo', 6, 4);

       輸出：Amig

三．             數(shù)值函數(shù)

1.       ABS(X)

返回X的絕對(duì)值。

Eg1. SELECT ABS(-5);

       輸出：5

2.       FLOOR(X)

返回不大于X的最大整數(shù)值 。

Eg1. SELECT FLOOR(2.56);

       輸出：2

Eg2. SELECT FLOOR(-2.56)

       輸出：-3

3.       MOD(N,M) , N % M N MOD M

模操作。返回N 被 M除后的余數(shù)。

Eg1. SELECT MOD(237, 10);

       輸出：7

Eg2. SELECT 237 % 10;

       輸出：7

Eg3. SELECT 237 MOD 10;

       輸出：7

4.       POW(X, Y)，POWER(X, Y)

返回X的Y乘方的結(jié)果值。

Eg1. SELECT POW(2, 3);

       輸出：8

Eg2. SELECT POWER(2, 4)

       輸出：16

5.       RAND()，RAND(N)

返回一個(gè)隨機(jī)浮點(diǎn)值 v，范圍在 0 到1 之間 (即, 其范圍為 0 ≤ v ≤ 1.0)。若已指定一個(gè)整數(shù)參數(shù) N，則它被用作種子值，用來(lái)產(chǎn)生重復(fù)序列。

Eg1.  SELECT RAND(20);

       輸出：0.15888261251047

Eg2. SELECT RAND();

       輸出：0.9233482386203

若要在i ≤ R ≤ j 這個(gè)范圍得到一個(gè)隨機(jī)整數(shù)R ，需要用到表達(dá)式 FLOOR(i + RAND() * (j – i + 1))。例如， 若要在7 到 12 的范圍（包括7和12）內(nèi)得到一個(gè)隨機(jī)整數(shù), 可使用以下語(yǔ)句：

SELECT FLOOR(7 + (RAND() * 6));

6.       ROUND(X)，ROUND(X,D)

返回參數(shù)X, 其值接近于最近似的整數(shù)。在有兩個(gè)參數(shù)的情況下，返回 X ，其值保留到小數(shù)點(diǎn)后D位，而第D位的保留方式為四舍五入。若要接保留X值小數(shù)點(diǎn)左邊的D 位，可將 D 設(shè)為負(fù)值。

Eg1. SELECT ROUND(-1.23);

       輸出：-1

Eg2. SELECT ROUND(2.56);

       輸出：3

7.       SQRT(X)

      返回非負(fù)數(shù)X的二次方根，如X為NULL時(shí)，返回NULL。

Eg1. SELECT SQRT(4);

      輸出：2

Eg2. SELECT SQRT(-4);

      輸出：NULL

四．             日期和時(shí)間函數(shù)

1. ADDDATE(date,INTERVAL expr type)，ADDDATE(expr,days)

       當(dāng)被第二個(gè)參數(shù)的INTERVAL格式激活后， ADDDATE()就是DATE_ADD()的同義詞。相關(guān)函數(shù)SUBDATE()則是DATE_SUB()的同義詞。

Eg1. SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);

       輸出：'1998-02-02'

Eg2. SELECT ADDDATE('1998-01-02', INTERVAL 31 DAY);

       輸出：'1998-02-02'

2. CURDATE()

       將當(dāng)前日期按照'YYYY-MM-DD' 或YYYYMMDD 格式的值返回，具體格式根據(jù)函數(shù)用在字符串或是數(shù)字語(yǔ)境中而定。

Eg1. SELECT CURDATE();

       輸出：'2008-01-01'

Eg2. SELECT CURDATE() + 0;

       輸出：20080101

3. CURTIME()

       將當(dāng)前時(shí)間以'HH:MM:SS'或 HHMMSS 的格式返回， 具體格式根據(jù)函數(shù)用在字符串或是數(shù)字語(yǔ)境中而定。 

Eg1. SELECT CURTIME();

       輸出：'15:15:26'

4. DATE(expr)

       提取日期或時(shí)間日期表達(dá)式expr中的日期部分。

Eg1. SELECT DATE('2007-12-31 23:59:59');

       輸出：'2007-12-31'

5. DATEDIFF(expr, expr2)

       DATEDIFF() 返回起始時(shí)間 expr和結(jié)束時(shí)間expr2之間的天數(shù)。Expr和expr2為日期或 date-and-time 表達(dá)式。計(jì)算中只用到這些值的日期部分。

Eg1. SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');

       輸出：1

Eg2. SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');

       輸出：-31

6. DAYOFMONTH(date)

       返回date 對(duì)應(yīng)的該月日期，范圍是從 1到31。

Eg1. SELECT DAYOFMONTH('2008-01-05');

       輸出：5

7. MONTH (time)

       返回date對(duì)應(yīng)的月份，范圍時(shí)從 1 到 12。

Eg1. SELECT MONTH('2007-01-05');

輸出：1

8. HOUR(time)

       返回time 對(duì)應(yīng)的小時(shí)數(shù)。對(duì)于日時(shí)值的返回值范圍是從 0 到 23 。

Eg1. SELECT HOUR('11:47:1');

9. MINUTE(time)

       返回 time 對(duì)應(yīng)的分鐘數(shù),范圍是從 0 到 59。

Eg1. SELECT MINUTE('2007-01-05 11:51:23);

       輸出：51

10. SECOND(time)

       返回time對(duì)應(yīng)的秒數(shù), 范圍是從 0到59。

Eg1. SELECT SECOND('11:56:43);

       輸出：43

11. LAST_DAY(date)

       獲取一個(gè)日期或日期時(shí)間值，返回該月最后一天對(duì)應(yīng)的值。若參數(shù)無(wú)效，則返回NULL。

Eg1. SELECT LAST_DAY('2008-02-01');

       輸出：29

12. NOW()

       返回當(dāng)前日期和時(shí)間值，其格式為 'YYYY-MM-DD HH:MM:SS' 或YYYYMMDDHHMMSS ， 具體格式取決于該函數(shù)是否用在字符串中或數(shù)字語(yǔ)境中。

Eg1. SELECT NOW();

       輸出：'2007-01-05 11:54:30'

13. SUBDATE(date,INTERVAL expr type) ，SUBDATE(expr,days)

       當(dāng)被第二個(gè)參數(shù)的 INTERVAL型式調(diào)用時(shí), SUBDATE()和DATE_SUB()的意義相同。對(duì)于有關(guān)INTERVAL參數(shù)的信息， 見(jiàn)有關(guān) DATE_ADD()的討論。

Eg1. SELECT SUBDATE('2008-01-05', INTERVAL 31 DAY);

       輸出：2007-12-05

14. SYSDATE()

       返回當(dāng)前日期和時(shí)間值，格式為'YYYY-MM-DD HH:MM:SS' 或YYYYMMDDHHMMSS， 具體格式根據(jù)函數(shù)是否用在字符串或數(shù)字語(yǔ)境而定。

在一個(gè)存儲(chǔ)程序或觸發(fā)器中, SYSDATE()返回其執(zhí)行的時(shí)間, 而非存儲(chǔ)成都或觸發(fā)語(yǔ)句開(kāi)始執(zhí)行的時(shí)間。這個(gè)NOW()的運(yùn)作有所不同。

Eg1. SELECT SYSDATE();

       輸出：2008-01-05 12:01:41