常用MySQL函数汇总

一些经常会用到的mysql函数汇总，算是一个学习笔记吧，如果有错的地方欢迎指正
每个函数都带有简单的说明和示例，仅供参考
不知道为什么我mysql的UPcase Keywords不管用了，所以大小写有点混乱

数字函数
ABS(x)返回绝对值
select abs(-1);

AVG(expression)返回某个字段的平均值
select avg(balance)
from money;

GREATEST(expr1, expr2, expr3, …) 返回列表中的最大值
select greatest(1,2,3,4);

LEAST(expr1, expr2, expr3, …) 返回列表中的最小值
select least(1,2,3,4);

max()返回最大值
select max(balance)
from money;

min()返回最小值
select min(balance)
from money;

rand()返回0到1的随机浮点数
select rand();

round 四舍五入,第一个参数是数字，第二个参数是保留位数，默认为0，表示取整，若为负数，则在整数位上四舍五入
select round(3.14159);
select round(3.14159,1);
select round(103.14159,0);
select round(103.14159,-1);

通过嵌套round和rand函数可以获取指定范围内的随机数，例如：获取1到10之间的随机整数：
SELECT ROUND(RAND() * 10);

sum()求和
select sum(balance)
from money;

count()计数
select count(balance)
from money;
注意，max,min,count,avg,sum均为聚合函数，一般与group by 子句一起使用

ceil 向上取整，返回大于等于该参数的最小整数
select ceil(3.14159);

floor 向下取整，返回小于等于该参数的最大整数
SELECT floor(3.14159);

truncate 截断，第一个参数为原数字，第二个参数为保留位数
select truncate(3.14159,2);

mod 取模,即取余数，第一个参数为被除数，第二个参数为除数
select mod(10,3);

字符函数
length 获取参数值的字节个数,注意，一个英文字母标点以及数字是一个字节，一个汉字以及中文标点是3个字节
SELECT length('hello!1');
select length('数据库！');

concat 将多个字符串拼接，中间用逗号分隔
select concat('My','SQL');
select concat('数据库','查询');

upper 将参数所有字母转换为大写，数字及中文不报错，返回原参数值，但是无意义
select upper('mysql');

lower 将参数所有字母转换为小写
select lower('MYSQL');

substr
索引从指定位置开始，返回指定位置及其后指定字符长度的字符串；
第一个参数为需要索引的字符串,必选；第二个参数为指定索引开始位置，必选，若为负数，则表示从字符串末尾指定位置开始索引；
第三个参数为返回字符串的字符长度（包含索引开始位置），可选，若省略则返回开始位置及其后所有字符
select substr('mysql',1);
select substr('mysql',1,2);
select substr('mysql',-2,2);

instr 第一个参数与第二个参数均为字符串，返回第二个参数在第一个参数中第一次出现的位置，若未出现，返回0
select instr('mysql','sq');
select instr('mysql','python');
select instr('mysql','q',1,1);

trim 去除字符串两端的空格 ,ltrim去除左端空格，rtrim去除右端空格
select trim(' mysql ');
select rtrim(' mysql ');
select ltrim(' mysql ');

lpad
用指定字符串填充原字符串左边，其中第一个参数为原字符串，
第二个参数为填充后字符串整体长度，第三个参数为填充字符串，rpad同理
注意，若第二个参数小于原字符串长度，则该函数的实现功能变为截取字符串
select lpad('mysql',10,'');
select rpad('mysql',10,'');
select lpad(rpad('mysql',10,''),15,'');
select lpad('mysql',2,'*');

replace
将所有指定的字符串替换
第一个参数为原字符串，第二个参数为被替换的字符串，第三个参数为替换字符串
SELECT replace('mysql','sq','*');

FIND_IN_SET(s1,s2)返回在字符串s2中与s1匹配的字符串的位置,注意，s2需要被逗号分割
select find_in_set('c','a,b,c,d,e,f');

format()函数用于格式化一个数字，并以字符串的形式返回
第一个参数为需要被格式化的数字，第二个参数可选，表示保留的小数位数,可省略，省略即取整，为负数无效果，也是取整
select format(3.14,1);
select format(3.14);

INSERT(s1,x,len,s2)字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串
SELECT insert('@126.com',2,3,'qq');

LOCATE(s1,s)从字符串 s 中获取 s1 的开始位置
select locate('数','MySQL数据库');

日期函数

now
返回当前日期和时间
select now();

curdate
返回当前日期
select curdate();

curtime
返回当前时间，不包含日期
SELECT curtime();

date()从格式日期中返回日期
select date(now());

YEAR()从格式日期中返回年份
select year(now());

month()从格式日期中返回月份
select month(now());

day()从格式日期中返回日
select day(now());

time()从格式日期中返回时间
select time(now());

monthname,dayname
返回月份、日(是星期几)的英文名
select monthname(now());
select dayname(now());

datediff(d1,d2) 计算两个日期相隔的天数，d1-d2
SELECT datediff('2020-01-01','2019-01-01');

DATE_FORMAT按表达式的要求显示日期
select date_format(now(),'%Y年%m月%d日');

str_to_date
将字符串转换为指定格式的日期
select str_to_date('2020年1月1日','%Y年%m月%d日');