MySQL常用函数

1.算术函数

SELECT ABS(-1); #求绝对值
SELECT SQRT(100);#求平方根
SELECT CEIL(-2.3);#求大于2.3的最小整数
SELECT FLOOR(-2.3);#求小于2.3的最大整数

SELECT RAND(); #生成[0,1)的随机小数
SELECT RAND(100); #根据指定的数值是生成一个伪随机数

#四舍五入
SELECT ROUND(1.5);
SELECT ROUND(1.4);
SELECT ROUND(-1.5);
SELECT ROUND(-1.4);

#截断小数位
SELECT TRUNCATE(123.456,0);
SELECT TRUNCATE(123.456,1);
SELECT TRUNCATE(123.456,2);

#返回符号
SELECT SIGN(5),SIGN(0),SIGN(-5);

#幂运算
SELECT POW(2,3),POW(2.2,3.3),POW(2,-1)

格式化函数FORMAT(x,n)

FORMAT(x,n)函数可以将数字x进行格式化，将x保留到小数点后n位。这个过程需要进行四舍五入。例如FORMAT(2.356,2)返回的结果将会是2.36；FORMAT(2.353,2)返回的结果将会是2.35。下面使用FORMAT(x,n)函数来讲235.3456和235.3454进行格式化，都保留到小数点后3位。

SELECT FORMAT(235.3456,3),FORMAT(235.3454,3);

更多数学函数

2.字符串函数

#CHAR_LENGTH字符个数 LENGTH()字节数
SELECT CHAR_LENGTH(‘abc汉字’),LENGTH(‘abc汉字’);

#串联
SELECT CONCAT(‘abc’,‘123’)
SELECT CONCAT(‘abc’,NOW())
SELECT CONCAT_WS(’-’,‘南京’,‘马鞍山’,‘芜湖’)

SELECT INSERT(‘01234567’,2,1,‘ABCDE’) #从第2个字符位置共计1个替换成 ‘ABCDE’

#转换成小写
SELECT LOWER(‘BLACK’),LCASE(‘BLack’);
#转换成大写
SELECT UPPER(‘black’),UCASE(‘black’);

#补白
SELECT LPAD(‘black’,8,’+’);
SELECT RPAD(‘black’,8,’+’);

#去除空格
SELECT CONCAT(RTRIM(‘black ‘),TRIM(’ black ‘),LTRIM(’ black’));
SELECT TRIM(‘xy’ FROM ‘xyabcxy123xy’); #去除原字符串前后’xy’

#重复
SELECT REPEAT(‘mysql’,3)

#空格函数
SELECT CONCAT(’(’,SPACE(6),’)’)
#替换函数
SELECT REPLACE(‘xxx.mysql.com’,‘x’,‘w’)

#字符串比较函数

SELECT ‘aBC’>‘ABc’;#不区分大小写

‘0’,‘A’,'a’ASCII值为48,65,97
SELECT STRCMP(‘ABC’,‘1bc’);#前者大于后者返回1，等于返回0，小于返回-1，不区分大小写
SELECT STRCMP(‘0BC’,‘1bc’);//CMP compare
#求子串
SELECT SUBSTR(‘breakfast’,2,4) #mysql字符位置是1开始计数
SELECT SUBSTR(‘breakfast’,6) #从第6字符开始至结束
SELECT SUBSTR(‘breakfast’,-3,3) #负索引从倒数第一个开始

#求子串位置
SELECT LOCATE(‘ball’,‘football’);
SELECT POSITION(‘ball’ IN ‘football’);
SELECT INSTR(‘football’,‘ball’);
#逆序
SELECT REVERSE(‘ABC’)

#返回指定位置字符串
SELECT ELT(3,‘南京’,‘马鞍山’,‘芜湖’,‘铜陵’);
SELECT ELT(4,‘南京’,‘马鞍山’,‘芜湖’,‘铜陵’);
SELECT ELT(5,‘南京’,‘马鞍山’,‘芜湖’,‘铜陵’);
#返回第一参数位于序列中位置
SELECT FIELD(‘芜湖’,‘南京’,‘马鞍山’,‘芜湖’);
#返回字符串的位置
SELECT FIND_IN_SET(‘马鞍山’,‘南京,马鞍山,芜湖’);

3. 时间函数

DAYOFWEEK(date):返回日期date的星期索引(1=星期天，2=星期一, …7=星期六)。
select DAYOFWEEK(‘1998-02-03’);

select DAYOFWEEK(now());

WEEKDAY(date):返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。
select WEEKDAY(‘1997-01-18’);

DAYOFMONTH(date):返回date的月份中的日期，在1到31范围内。
select DAYOFMONTH(‘1997-01-18’);

DAYOFYEAR(date):返回date在一年中的日数, 在1到366范围内。
select DAYOFYEAR(‘1997-01-18’);

MONTH(date):返回date的月份，范围1到12。
select MONTH(‘1997-01-18’);

DAYNAME(date):返回date的星期名字。
select DAYNAME(“1997-01-18”);

MONTHNAME(DATE) :返回date的月份名字。
SELECT MONTHNAME(“1997-01-18”);

QUARTER(DATE):返回date一年中的季度，范围1到4。
SELECT QUARTER(‘1997-01-18’);

WEEK(DATE,FIRST):对于星期天是一周的第一天的地方，有一个单个参数，
返回date的周数，范围在0到52。2个参数形式WEEK()允许你指定星期是否开始于星期天或星期一。
如果第二个参数是0，星期从星期天开始，如果第二个参数是1，从星期一开始。
SELECT WEEK(‘2009-02-20’),WEEK(‘2009-02-20’,0),WEEK(‘2009-02-20’,1);

YEAR(date):返回date的年份，范围在1000到9999。
select YEAR(‘98-02-03’);

HOUR(TIME):返回time的小时，范围是0到23。
SELECT HOUR(‘10:05:03’);

MINUTE(time):返回time的分钟，范围是0到59。
select MINUTE(‘98-02-03 10:05:03’);

SECOND(time):回来time的秒数，范围是0到59。
select SECOND(‘10:05:03’);

DATE_ADD(date,INTERVAL expr type) ,进行日期增加的操作，可以精确到秒
DATE_SUB(date,INTERVAL expr type) ，进行日期减少的操作，可以精确到秒
SELECT “1997-12-31 23:59:59” + INTERVAL 1 SECOND;

SELECT INTERVAL 1 DAY + “1997-12-31”;

SELECT “1998-01-01” - INTERVAL 1 SECOND;

SELECT DATE_ADD(“1997-12-31 23:59:59”, INTERVAL 1 SECOND);

SELECT DATE_ADD(“1997-12-31 23:59:59”, INTERVAL “1:1” MINUTE_SECOND);

SELECT DATE_SUB(“1998-01-01 00:00:00”, INTERVAL “1 1:1:1” DAY_SECOND);

SELECT DATE_SUB(“1998-01-02”, INTERVAL 31 DAY);

CURRENT_DATE:以‘YYYY-MM-DD’或YYYYMMDD格式返回今天日期值，取决于函数在一个字符串还是数字上下文被使用。
select CURDATE();

CURRENT_TIME:以‘HH:MM:SS’或HHMMSS格式返回当前时间值
select CURTIME();

NOW():以‘YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS格式返回当前的日期和时间
SELECT NOW();

4.控制流程函数

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
在第一个方案的返回结果中， value=compare-value。而第二个方案的返回结果是第一种情况的真实结果。如果没有匹配的结果值，则返回结果为ELSE后的结果，如果没有ELSE 部分，则返回值为 NULL。
SELECT CASE 11 WHEN 1 THEN ‘one’
WHEN 2 THEN ‘two’ ELSE ‘more’ END ;

SELECT CASE WHEN 1>0 THEN ‘true’ ELSE ‘false’ END;

SELECT CASE BINARY ‘b’
WHEN ‘a’ THEN 1 WHEN ‘b’ THEN 2 END;

IF(expr1,expr2,expr3)
如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。
SELECT
IF(1 > 2, 2, 3),
IF(1 < 2, 'yes ', ‘no’),
IF(STRCMP(‘test’, ‘test1’), ‘no’, ‘yes’) ;

Strcmp(str1,str2):如果str1>str2返回1,str1=str2反回0，str1<str2返回-1)
STRCMP(expr1,expr2)

如果字符串相同，STRCMP()返回0，如果第一参数根据当前的排序次序小于第二个，返回-1，否则返回1。
SELECT STRCMP(‘text’, ‘text2’),STRCMP(‘text2’, ‘text’),STRCMP(‘text’, ‘text’);

5.系统信息

获取MySQL版本号、连接数、数据库名的函数
SELECT VERSION();

CONNECTION_ID()函数返回服务器的连接数，也就是到现在为止MySQL服务的连接次数；
SELECT CONNECTION_ID();

DATABASE()和SCHEMA()返回当前数据库名。
SELECT DATABASE(),SCHEMA();

获取用户名的函数

USER()、SYSTEM_USER()、SESSION_USER()、CURRENT_USER()和CURRENT_USER这几个函数可以返回当前用户的名称。
SELECT USER(),SYSTEM_USER(),SESSION_USER(),CURRENT_USER(),CURRENT_USER();

获取字符串的字符集和排序方式的函数

CHARSET(str)函数返回字符串str的字符集，一般情况这个字符集就是系统的默认字符集；COLLATION(str)函数返回字符串str的字符排列方式。

SELECT CHARSET(‘avc’);