超全整理，MySQL常用函数

1、数学函数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(pi)的值RAND()--returns0to1，可以通过提供参数（seed）使RAND()随机数生成器生成一个指定的值。ROUND(x,y)--返回参数x的四舍五入值，小数点后y位SIGN(x)--返回代表数字x符号的值SQRT(x)--返回数字的平方根TRUNCATE(x,y)--返回数字x截断到小数点后y位的结果2.聚合函数AVG(X)--返回指定列的平均值COUNT(X)--返回非NULL值的个数在指定列中MIN(X)--返回指定列的最小值MAX(X)--返回指定列的最大值SUM(X)--返回所有值的和ofthespecifiedcolumnGROUP_CONCAT(X)--返回属于一个组的列值的组合结果很有用3.字符串函数（20）ASCII(char)--返回该组的ASCII码值characterBIT_LENGTH(str)--返回字符串的位长CONCAT(s1,s2...,sn)--将s1,s2...,sn拼接成字符串CONCAT_WS(sep,s1,s2...,sn)--将s1,s2...,sn拼接成一个字符串，使用sepc字符分隔INSERT(str,x,y,instr)--从字符串str的第x个位置开始，将第y个字符长的子字符串替换为字符串instr，返回结果FIND_IN_SET(str,list)--分析逗号分隔的列表list，如果找到str，则返回str在列表中的位置LCASE(str)orLOWER(str)--返回将字符串str中所有字符改为小写的结果LEFT(str,x)--返回字符串str最左边的x个字符LENGTH(s)--返回字符串str中的字符个数LTRIM(str)--从字符串str中截掉前导空格POSITION(substr,str)--返回substringsubstrincharacters字符串str中第一次出现的位置QUOTE(str)--用反斜杠转义str中的单引号REPEAT(str,srchstr,rplcstr)--返回字符串str重复x次的结果REVERSE(str)--返回字符串str反转的结果RIGHT(str,x)--返回字符串str中最右边的x个字符RTRIM(str)--返回thestringstr末尾的空格STRCMP(s1,s2)--比较字符Strings1和s2TRIM(str)--去掉字符串开头和结尾的所有空格UCASE(str)orUPPER(str)--将字符串str中的所有字符返回为大写。4.日期时间函数CURDATE()或CURRENT_DATE()--返回当前日期CURTIME()或CURRENT_TIME()--返回当前时间DATE_ADD(date,INTERVALintkeyword)--返回日期加上间隔时间的结果int(int必须跟在关键字Forformatting)例如SELECTDATE_ADD(CURRENT_DATE,INTERVAL6MONTH);DATE_FORMAT(date,fmt)--根据指定的fmt格式格式化日期date值DATE_SUB(date,INTERVALintkeyword)--返回date加上间隔时间的结果int(int必须根据关键字进行格式化）例如SELECTDATE_SUB(CURRENT_DATE,INTERVAL6MONTH);DAYOFWEEK(date)--返回日期所代表的星期几(1~7)DAYOFMONTH(date)--返回日期为月份的第一天(1~31)DAYOFYEAR(date)--返回date为一年的第一天(1~366)DAYNAME(date)--返回日期的周名，如：SELECTDAYNAME(CURRENT_DATE);FROM_UNIXTIME(ts,fmt)--根据指定fmt格式(date)--返回date的月份值(1~12)MONTHNAME(date)--返回date的月份名称，如：SELECTMONTHNAME(CURRENT_DATE);NOW()--返回当前日期和timeQUARTER(date)--返回日期在一年（1~4）中的四分之一如SELECTQUARTER(CURRENT_DATE);WEEK(date)--返回日期date为一年中的第几周(0~53)YEAR(date)--返回日期date年份(1000~9999)例如获取当前系统时间SELECTFROM_UNIXTIME(UNIX_TIMESTAMP());SELECTEXTRACT(YEAR_MONTHFROMCURRENT_DATE);SELECTEXTRACT(DAY_SECONDFROMCURRENT_DATE);SELECTEXTRACT(HOUR_MINUTEFROMCURRENT_DATE);返回两个日期值之间的差（月）SELECTPERIOD_DIFF(200302,199802);Mysql计算年龄：SELECTDATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)),'%Y')+0ASageFROMemployee;这样，如果Brithday是未来日期，则计算结果为0。下面的SQL语句计算员工的绝对年龄，即Birthday是未来日期时，会得到一个负值。SELECTDATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(生日,'%Y')-(DATE_FORMAT(NOW(),'00-%m-%d')100,'true','false');IF()函数适用于只有两种可能结果的情况。然而，在现实世界中，我们可能会发现条件测试需要多个分支。在这种情况下，它与PHP和Perl语言的switch-case条件例程相同。CASE函数的格式有些复杂，通常如下：CASE[expressiontobeevaluated]WHEN[val1]THEN[result1]WHEN[val2]THEN[result2]WHEN[val3]THEN[result3]......valn]THEN[resultn]ELSE[defaultresult]END这里，第一个参数是要判断的值或表达式，后面是一系列的WHEN-THEN块，每个块的第一个参数指定要比较的值，如果为真,返回结果。所有WHEN-THEN块都将以ELSE块结束。当END结束所有外部CASE块时，如果前面的每个块都不匹配，则返回ELSE块指定的默认结果。如果没有指定ELSE块，并且WHEN-THEN比较都不为真，则MySQL将返回NULL。CASE函数还有另外一种语法，有时使用起来很方便，如下所示：条件测试它是否为真。例如：SELECTCASE'green'WHEN'red'THEN'stop'WHEN'green'THEN'go'END;SELECTCASE9WHEN1THEN'a'WHEN2THEN'b'ELSE'N/A'END;SELECTCASEWHEN(2+2)=4THEN'OK'WHEN(2+2)<>4THEN'notOK'ENDASSTATUS;SELECTName,IF((IsActive=1),'activated','notactivated')ASRESULTFROMUserLoginInfo;SELECTfname,lname,(math+sci+lit)AStotal,CASEWHEN(math+sci+lit)<50THEN'D'WHEN(math+sci+lit)BETWEEN50AND150THEN'C'WHEN(math+sci+lit)BETWEEN151AND250THEN'B'ELSE'A'ENDASgradeFROMmarks;SELECTIF(ENCRYPT('sue','ts')=upass,'allow','deny')ASLoginResultFROMusersWHEREuname='sue';7、格式化函数DATE_FORMAT(date,fmt)--根据字符串格式化日期值fmtFORMAT(x,y)--将x格式化为逗号分隔的数字序列，y为结果的小数位数INET_ATON(ip)--返回IP地址的数字表示INET_NTOA(num)--返回数字表示的IP地址TIME_FORMAT(time,fmt)--根据字符串fmt格式化时间值。最简单的是FORMAT()函数，它可以将大值格式化为逗号分隔的人类可读序列。例如，SELECTFORMAT(34234.34323432,3);SELECTDATE_FORMAT(NOW(),'%W,%D%M%Y%r');SELECTDATE_FORMAT(NOW(),'%Y-%m-%d');SELECTDATE_FORMAT(19990330,'%Y-%m-%d');SELECTDATE_FORMAT(NOW(),'%h:%i%p');SELECTINET_ATON('10.122.89.47');SELECTINET_NTOA(175790383);8.类型转换函数对于数据类型的转换，MySQL提供了CAST()函数，可以将一个值转换成指定的数据类型。类型有：BINARY,CHAR,DATE,TIME,DATETIME,SIGNED,UNSIGNED如SELECTCAST(NOW()ASSIGNEDINTEGER),CURDATE()+0;SELECT'f'=BINARY'F','f'=CAST('F'ASBINARY);9、系统信息函数DATABASE()--返回当前数据库名BENCHMARK(count,expr)--将表达式expr重复count次CONNECTION_ID()--返回当前客户连接IDFOUND_ROWS()--返回最后ASELECT查询检索总行数USER()或SYSTEM_USER()——返回当前登录用户名SELECTBENCHMARK(9999999,LOG(RAND()*PI()));--本例MySQL计算LOG(RAND()*PI())表达式9999999次。10.开窗函数MySQL8.0版本开始支持开窗函数ROW_NUMBER()OVER()--Sorting:1,2,3,4RANK()OVER()--Sorting:1,1,3,3,5DENSE_RANK()OVER()--排序：1,1,2,2,3,4NTILE()OVER()--将排序好的数据分成N组，记录层数LAG(expr,n)OVER()--返回当前行前N行expr的值LEAD(expr,n)OVER()--返回当前行后N行expr的值