Mysql常用函數匯總

Mysql常用函數匯總

一、 聚合函數

1.1 求和函數-----SUM()

求和函數SUM( )用於對數據求和，返回選取結果集中所有值的總和。
語法:SELECT SUM(column_name) FROM table_name
說明：SUM()函數只能作用於數值型數據，即列column_name中的數據必須是數值型的。

1.2 計數函數-----COUNT()

COUNT()函數用來計算表中記錄的個數或者列中值的個數，計算內容由SELECT語句指定。
使用COUNT函數時，必須指定一個列的名稱或者使用星號，星號表示計算一個表中的所有記錄。
兩種使用形式如下:
COUNT(*)，計算表中行的總數，即使表中行的數據為NULL，也被計入在內。
COUNT(column)，計算column列包含的行的數目，如果該列中某行數據為NULL，則該行不計入統計總數。
--使用COUNT(*)函數對表中的行數計數:
COUNT(*)函數將返回滿足SELECT語句的WHERE子句中的搜索條件的函數。
--使用COUNT( )函數對一列中的數據計數:
COUNT( )函數可用於對一列中的數據值計數。
與忽略了所有列的COUNT(*)函數不同，COUNT( )函數逐一檢查一列（或多列）中的值，並對那些值不是NULL的行計數。
--使用COUNT( )函數對多列中的數據計數:
COUNT( )函數不僅可用於對一列中的數據值計數，也可以對多列中的數據值計數。
如果對多列計數，則需要將要計數的多列通過連接符連接後，作為COUNT( )函數的參數。

1.3 最大/最小值函數—-----MAX()/MIN()

當需要了解一列中的最大值時，可以使用MAX()函數；
同樣，當需要了解一列中的最小值時，可以使用MIN()函數。語法如下。
SELECT MAX (column_name) / MIN (column_name) FROM table_name
說明：列column_name中的數據可以是數值、字符串或是日期時間數據類型。
MAX()/MIN()函數將返回與被傳遞的列同一數據類型的單一值。

1.4 均值函數-----AVG()

函數AVG()用於計算一列中數據值的平均值。
語法:SELECT AVG (column_name) FROM table_name
說明：AVG()函數的執行過程實際上是將一列中的值加起來，再將其和除以非NULL值的數目。
所以，與SUM( )函數一樣，AVG()函數只能作用於數值型數據，即列column_name中的數據必須是數值型的。

1.5 合並函數-----GROUP_CONCAT()

GROUP_CONCAT可以對分組後的列進行字符串的合並(拼接)。
語法:
GROUP_CONCAT (
　　　　　　　[DISTINCT] [,expr ...] [,col_name]
　　　　　　　[ ORDER BY {,col_name ...} [ASC | DESC] ]
　　　　　　　[SEPARATOR str_val]
　　　　　　　)
舉例:表如下

country|population|name
中國　　|1　　　　|a
美國　　|1　　　　|b
日本　　|5　　　　|a
歐洲　　|5　　　　|c
韓國　　|2　　　　|a
非洲　　|NULL　　|b
(FROM table_name忽略)
--SELECT GROUP_CONCAT(population) GROUP BY name
(result:1,5,2|1|5)
--SELECT GROUP_CONCAT(population ORDER BY population) GROUP BY name
(result:1,2,5|1|5)
--SELECT GROUP_CONCAT(population,'-',country) GROUP BY name
(result:1-中國,2-韓國,5-日本|1-美國|5-歐洲)
--SELECT GROUP_CONCAT(
　　　　　　　　(CASE country
　　　　　　　　　　　　WHEN '中國' THEN 'good'
　　　　　　　　　　　　ELSE 'bad'
　　　　　　　　　END)
　　　　　　　　,'-',population) GROUP BY name
(result:good-1,bad-2,bad-5|bad-1|bad-5)
--SELECT GROUP_CONCAT(population,SEPARATOR '-') GROUP BY name
(result:1-5-2|1|5)

 

1.6 聚合分析的重值處理

前面介紹的5種聚合函數，可以作用於所選列中的所有數據（不管列中的數據是否有重置），
也可以只對列中的非重值進行處理，即把重復的值只取一次進行聚合分析。
當然，對於MAX()/MIN()函數來講，重值處理意義不大。
可以使用ALL關鍵字指明對所選列中的所有數據進行處理，
使用DISTINCT關鍵字指明對所選列中的非重值數據進行處理。
以AVG()函數為例，語法如下。
SELECT AVG ([ALL/DISTINCT] column_name) FROM table_name
說明：[ALL/DISTINCT]在缺省狀態下，默認是ALL關鍵字，
即不管是否有重值，處理所有數據。其他聚合函數的用法與此相同。

二、 數學函數

ABS(x) 返回x的絕對值
BIN(x) 返回x的二進制（OCT返回八進制，HEX返回十六進制）
CEILING(x) 返回大於x的最小整數值
EXP(x) 返回值e（自然對數的底）的x次方
FLOOR(x) 返回小於x的最大整數值
GREATEST(x1,x2,...,xn)返回集合中最大的值
LEAST(x1,x2,...,xn) 返回集合中最小的值
LN(x) 返回x的自然對數
LOG(x,y)返回x的以y為底的對數
MOD(x,y) 返回x/y的模（余數）
PI()返回pi的值（圓周率）
RAND()返回０到１內的隨機值,可以通過提供一個參數(種子)使RAND()隨機數生成器生成一個指定的值。
ROUND(x,y)返回參數x的四捨五入的有y位小數的值
SIGN(x) 返回代表數字x的符號的值
SQRT(x) 返回一個數的平方根
TRUNCATE(x,y) 返回數字x截短為y位小數的結果

三、 字符串函數

ASCII(char)返回字符的ASCII碼值
BIT_LENGTH(str)返回字符串的比特長度
CONCAT(s1,s2...,sn)將s1,s2...,sn連接成字符串
CONCAT_WS(sep,s1,s2...,sn)將s1,s2...,sn連接成字符串，並用sep字符間隔
INSERT(str,x,y,instr) 將字符串str從第x位置開始，y個字符長的子串替換為字符串instr，返回結果
FIND_IN_SET(str,list)分析逗號分隔的list列表，如果發現str，返回str在list中的位置
LCASE(str)或LOWER(str) 返回將字符串str中所有字符改變為小寫後的結果
LEFT(str,x)返回字符串str中最左邊的x個字符
LENGTH(s)返回字符串str中的字符數
LTRIM(str) 從字符串str中切掉開頭的空格
POSITION(substr,str) 返回子串substr在字符串str中第一次出現的位置
QUOTE(str) 用反斜槓轉義str中的單引號
REPEAT(str,srchstr,rplcstr)返回字符串str重復x次的結果
REVERSE(str) 返回顛倒字符串str的結果
RIGHT(str,x) 返回字符串str中最右邊的x個字符
RTRIM(str) 返回字符串str尾部的空格
STRCMP(s1,s2)比較字符串s1和s2
TRIM(str)去除字符串首部和尾部的所有空格
UCASE(str)或UPPER(str) 返回將字符串str中所有字符轉變為大寫後的結果

四、日期和時間函數

4.1 獲取當前系統時間

--CURDATE()或CURRENT_DATE() 返回當前的日期,例如'2015-07-27'
--CURTIME()或CURRENT_TIME() 返回當前的時間,例如'09:36:23'
--NOW()或CURRENT_TIMESTAMP()或SYSDATE() 返回當前日期時間,例如'2015-07-27 09:37:11'

--UNIX_TIMESTAMP(date)
如果沒有參數調用，返回一個Unix時間戳記(從'1970-01-01 00:00:00'GMT開始的秒數)。
如果UNIX_TIMESTAMP()用一個date參數被調用，它返回從'1970-01-01 00:00:00' GMT開始的秒數值。
date可以是一個DATE字符串、一個DATETIME字符串、一個TIMESTAMP或以YYMMDD或YYYYMMDD格式的本地時間的一個數字。
例子:SELECT UNIX_TIMESTAMP();//結果1437965279
例子:SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00'); //結果875996580
--FROM_UNIXTIME(unix_timestamp)
以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式返回unix_timestamp參數所表示的值，
取決於函數是在一個字符串還是或數字上下文中被使用。
例子:SELECT FROM_UNIXTIME(875996580);//'1997-10-04 22:23:00'
例子:select FROM_UNIXTIME(875996580) + 0;//19971004222300
--FROM_UNIXTIME(unix_timestamp,format)
返回表示 Unix 時間標記的一個字符串，根據format字符串格式化。
format可以包含與DATE_FORMAT()函數列出的條目同樣的修飾符。具體參考下面format表.
例子:SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y %D %M %h:%i:%s');//'2015 27th July 10:53:29'

--SEC_TO_TIME(seconds)
返回seconds參數，變換成小時、分鐘和秒，值以'HH:MM:SS'或HHMMSS格式化，
取決於函數是在一個字符串還是在數字上下文中被使用。
例子:SELECT SEC_TO_TIME(2378);//'00:39:38'
例子:SELECT SEC_TO_TIME(2378) + 0; //3938
--TIME_TO_SEC(time)
返回time參數，轉換成秒。
例子:SELECT TIME_TO_SEC('22:23:00');//80580v
例子:SELECT TIME_TO_SEC('00:39:38'); //2378

4.2 日期的時間間隔函數

(4.2.1) 增加(減少)幾年幾月幾天幾時幾分幾秒
--ADDDATE或DATE_ADD(date,INTERVAL expr type)
--SUBDATE或DATE_SUB(date,INTERVAL expr type)
--EXTRACT(type FROM date)函數從日期中返回“type”間隔
expr是指定加到開始日期或從開始日期減去的間隔值一個表達式，expr是一個字符串；
它可以以一個“-”開始表示負間隔。type是一個關鍵詞，指明表達式應該如何被解釋。

SECOND 秒 SECONDS
MINUTE 分鐘 MINUTES
HOUR 時間 HOURS
DAY 天 DAYS
MONTH 月 MONTHS
YEAR 年 YEARS
MINUTE_SECOND 分鐘和秒 "MINUTES:SECONDS"
HOUR_MINUTE 小時和分鐘 "HOURS:MINUTES"
DAY_HOUR 天和小時 "DAYS HOURS"
YEAR_MONTH 年和月 "YEARS-MONTHS"
HOUR_SECOND 小時, 分鐘， "HOURS:MINUTES:SECONDS"
DAY_MINUTE 天, 小時, 分鐘 "DAYS HOURS:MINUTES"
DAY_SECOND 天, 小時, 分鐘, 秒 "DAYS HOURS:MINUTES:SECONDS"
舉例:
(1)SELECT DATE_ADD(NOW(),INTERVAL 60 SECOND);//間隔60秒
(2)SELECT DATE_ADD(NOW(),INTERVAL "2:20" MINUTE_SECOND);//間隔2分鐘60秒
(3)SELECT DATE_SUB("1998-01-01 00:00:00",INTERVAL "-1 1 1" DAY_SECOND);
//間隔一小時一分一秒,天數為空 默認取0.expr前可加"-"
(4)SELECT EXTRACT(HOUR_SECOND FROM NOW());//結果102111,表示10點21分11秒.

 

--PERIOD_ADD(P,N)
增加N個月到階段P（以格式YYMM或YYYYMM)。以格式YYYYMM返回值。注意階段參數P不是日期值。
例子:SELECT PERIOD_ADD(9801,2);//結果199803
--PERIOD_DIFF(P1,P2)
返回在時期P1和P2之間月數，P1和P2應該以格式YYMM或YYYYMM。注意，時期參數P1和P2不是日期值。
例子:SELECT PERIOD_DIFF(9802,199703);//結果11

(4.2.2) 日期轉換
--TO_DAYS(date) 給出一個日期date，返回一個天數(從0年的天數)。
例子:SELECT TO_DAYS(950501);//結果728779
例子:SELECT TO_DAYS('1997-10-07'); //結果729669
--FROM_DAYS(N)
給出一個天數N，返回一個DATE值。
例子:SELECT FROM_DAYS(366);//結果0001-01-01

4.3 日期格式化(轉換)

--DATE_FORMAT(date,fmt) 依照指定的fmt格式格式化日期date值.
--TIME_FORMAT(time,format)處理包含小時、分鐘和秒的那些格式修飾符。其他修飾符產生一個NULL值或0。

　　%M 月名字(January……December)
　　%W 星期名字(Sunday……Saturday)
　　%D 有英語前綴的月份的日期(1st, 2nd, 3rd, 等等。）
　　%Y 年, 數字, 4 位
　　%y 年, 數字, 2 位
　　%a 縮寫的星期名字(Sun……Sat)
　　%d 月份中的天數, 數字(00……31)
　　%e 月份中的天數, 數字(0……31)
　　%m 月, 數字(01……12)
　　%c 月, 數字(1……12)
　　%b 縮寫的月份名字(Jan……Dec)
　　%j 一年中的天數(001……366)
　　%H 小時(00……23)
　　%k 小時(0……23)
　　%h 小時(01……12)
　　%I 小時(01……12)
　　%l 小時(1……12)
　　%i 分鐘, 數字(00……59)
　　%r 時間,12 小時(hh:mm:ss [AP]M)
　　%T 時間,24 小時(hh:mm:ss)
　　%S 秒(00……59)
　　%s 秒(00……59)
　　%p AM或PM
　　%w 一個星期中的天數(0=Sunday ……6=Saturday ）
　　%U 星期(0……52), 這裡星期天是星期的第一天
　　%u 星期(0……52), 這裡星期一是星期的第一天
　　%% 一個文字“%”。

 

4.4 提取日期

--DAYOFYEAR(date) 返回date是一年的第幾天(1~366)
例子:SELECT DAYOFYEAR('2015-07-27');//208

--DAYOFMONTH(date)或DAY() 返回date是一個月的第幾天(1~31)
例子:SELECT DAYOFMONTH('2015-07-27');//27

--DAYOFWEEK(date) 返回date所代表的一星期中的第幾天(1~7)
例子:SELECT DAYOFWEEK('2015-07-27');//2星期一為第二天.

--WEEKDAY(date) 返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。
例子:SELECT WEEKDAY('2015-07-27 10:09:08');//0表示星期一

--DAYNAME(date) 返回date的星期名.
例子:SELECT DAYNAME('2015-07-27 10:09:08');//Monday

--MONTHNAME(date) 返回date的月份名.
例子:SELECT MONTHNAME('2015-07-27 10:09:08');//July

--LAST_DAY(date )
函數使用說明：獲取一個日期或日期時間值，返回該月最後一天對應的值。
若參數無效，則返回 NULL 。
例子:SELECT LAST_DAY('2015-02-1 10:09:08');//2015-02-28

--YEAR(date) 返回日期date的年份(1000~9999)
例子:SELECT YEAR('2015-07-27 10:09:08');//2015

--QUARTER(date) 返回date在一年中的季度(1~4)
例子:SELECT QUARTER('2015-07-27 10:09:08');//3

--MONTH(date) 返回date的月份值(1~12)
例子:SELECT MONTH('2015-07-27 10:09:08');//7

--WEEK(date) 返回日期date為一年中第幾周(0~52)
例子:SELECT WEEK('2015-07-27 10:09:08');//30
--WEEK(date,first)
對於星期天是一周的第一天的地方，有一個單個參數，返回date的周數，
范圍在0到52。2個參數形式WEEK()允許你指定星期是否開始於星期天或星期一。
如果第二個參數是0，星期從星期天開始，如果第二個參數是1，從星期一開始。
例子:SELECT WEEK('2015-07-27 10:09:08',1);//31

--HOUR(time) 返回time的小時值(0~23)
例子:SELECT HOUR('2015-07-27 10:09:08');//10

--MINUTE(time) 返回time的分鐘值(0~59)
例子:SELECT MINUTE('2015-07-27 10:09:08');//9

--SECOND(time) 返回time的秒數，范圍是0到59。
例子:SELECT SECOND('2015-07-27 10:09:08');//8

五、控制流函數

CASE WHEN[test1] THEN [result1]...ELSE [default] END如果testN是真，則返回resultN，否則返回default
CASE [test] WHEN[val1] THEN [result]...ELSE [default]END 如果test和valN相等，則返回resultN，否則返回default
IF(test,t,f) 如果test是真，返回t；否則返回f
IFNULL(arg1,arg2) 如果arg1不是空，返回arg1，否則返回arg2

NULLIF(arg1,arg2) 如果arg1=arg2返回NULL；否則返回arg1