常用函数

1、数值型函数

// 绝对值函数:返回x的绝对值
abs(x)
select abs(-32);

// 取模函数:返回x被y除后的余数
mod(x, y)
select mod(63, 8);

// 平方根函数:返回非负数x的二次方根
sqrt(x)
select sqrt(25);

// 符号函数:返回参数的符号,x的值为负、零和正时返回结果依次为-1、0、1
sign(x)
select sign(-6), sign(0), sign(6);

// 取整函数:向上取整,返回不小于x的最小整数值
ceil(x)
ceiling(x)
select ceil(-2.5), ceiling(2.5);

// 取整函数:向下取整,返回不大于x的最大整数值
floor(x)
select floor(-2.5), floor(2.5);

// 随机函数
//(1)rand(x)返回一个随机浮点值,范围在0到1之间
//(2)不带参数的rand()每次产生的随机数值是不同的
//(3)带参数的rand(x),当参数相同时,将产生相同的随机数,不同的x产生的随机数值不同
rand()
rand(x) 
select rand(), rand(), rand(10), rand(10);

// 四舍五入函数:返回数值x带有y为小数结果的数值(四舍五入)
// 返回最接近于参数x的数,其值保留小数点后y位,
// 若y为负值,则将保留x值到小数点左边y位
round(x)
round(x, y)
select round(1.67), round(1.38, 1), round(1.38, 0), round(222.38, -1), round(222.38, -2);

// 数值截取函数:返回数值x截取y位小数的结果(不四舍五入)
// 返回被舍去至小数点后y位的数字x
// 若y的值为0,则结果不带有小数点或不带有小数部分
// 若y设为负数,则截去x小数点左起第y位开始后面所有低位的值
truncate(x, y)
select truncate(1.31,1),truncate(1.99,1),truncate(1.99,0),truncate(19.99,-1);

/**三角函数**/
//圆周率函数
PI()
select PI();

//正弦函数:返回x的正弦值,其中x为弧度值
sin(x)
select sin(1), sin(0.5*PI());

//反正弦函数:返回x的反正弦值,若x不在-1到1的范围内,则返回NULL
asin(x)
select asin(0.84), asin(2);

//余弦函数:返回x的余弦值,其中x为弧度值
cos(x)
select cos(1), cos(0), cos(PI());

//反余弦函数:返回x的反余弦值,若x不在-1到1的范围内,则返回NULL
acos(x)
select acos(2), acos(1), acos(-1);

//正切函数:返回x的正切值,其中x为弧度值
tan(x)
select tan(1), tan(0);

//反正切函数:返回x的反正切值,正切值为x的值
atan(x)
select atan(1.55), atan(0);

//余切函数:返回x的余切值,其中x为弧度值
cot(x)
select cot(1);

2、字符串函数

//获取字符串字节数函数:返回字符串的字节长度
// utf8的编码字符,一个汉字三个字节,一个数字或字母一个字节
length(x)
select length('name'), length('数据库');

//获取字符串字符数函数:返回字符串的长度
char_length(str)
select char_length('name'), char_length('数据库');

//字母大写转换函数
upper(x)
ucase(x)
select upper('green'), ucase('green');

//字母小写转换函数
lower(x)
lcase(x)
select lower('GREEN'), lcase('GREEN');

//获取指定位置的子串
//返回指定index位置的字符串
elt(index, s, s, s…)
//截取str左边n个字符
left(s, n)
//截取str右边n个字符
right(s, n)
//从str的index位置截取len个字符
substring(s, index, len)
select elt(1, 'a', 'b'), left('MYSQL', 2), right('MYSQL', 3), substring('MYSQL', 2, 2);

//合并字符串函数:将多个字符合并成一个字符,任一参数为NULL,返回
concat(x)
select concat('MYSQL', '5.7');

//字符串去空函数
//去除字符串str左边的空格
ltrim(s)
//去除字符串str右边的空格
rtrim(s)
//去除字符串str两边的空格
trim(s)
select ltrim(' MY'), rtrim('MY '), trim(' MY ');

//字符串替换函数
// 使用s2从s1的index位置替换s1的len个元素
insert(s1, index, len, s2)
// 将s中的子串s1全部替换成s2
replace(s, s1, s2)
select insert('MYSQL', 1, 2, 'T'), replace('MYSQL', 'SQL', 'T');

//字符串取反函数
reverse(s)
select reverse('MYSQL');

//比较字符串大小函数:用于比较两个字符串的大小
//左大于右时返回1,左等于右时返回0,左小于于右时返回-1,
strcmp(s1, s2)
select  strcmp('b', 'a'), strcmp('a', 'a'), strcmp('a', 'b');

//字符串查找函数
// 返回字符串s1在s2中的位置
find_in_set(s1, s2)
// s2由一个类似列表的字符串变成了多个字符串,返回str在str1,str2…中的位置
field(s, s1, s2)
// 返回子串s1在字符串s2中的位置
locate(s1, s2):
position(s1 in s2)
// 返回子串s2在字符串s1中的位置
instr(s1,s2)
select  find_in_set('b', 'a,b,c'), field('a', 'a', 'b');

select  locate('a', 'ab'), position('a' in 'ab'), instr('ab', 'a');

3、日期与时间函数

// 获取当前日期时间
select now(), sysdate();

// 获取当前日期
select curdate(), current_date();

// 获取当前时间
select curtime(), current_time();

// 查询d对应的一周中的索引
// dayofweek(d):1表示周日,2标识周一.......7表示周六
select  dayofweek('2017-12-15');
// weekday(d):0表示周一,1标识周二.......6表示周日
select  weekday('2017-12-15');

// 查询d对应的一月中的第几天,范围1~31
dayofmonth(d)
select  dayofmonth('2017-12-15');

// 查询d对应的一年中的第几天,范围1~366
dayofyear(d)
select  dayofyear('2017-12-15');

// 从日期中选择出月份数:返回指定date对应的月份,范围1~12
month(d):返回对应的数字
select month('2017-12-15');
monthname(d):返回对应月份的英文名
select monthname('2017-12-15');

// 查询起始时间d1和d2的天数
datediff(d1, d2)
select datediff('2017-11-30', '2017-11-29') as col1;

// 执行日期的加运算
adddate(d, interval exprtype)
select adddate('2017-11-30 23:59:59', interval 1 second) as col1;

// 从日期中选择出周数
week(d)
select week('2017-12-15');

// 从日期中选择出年数
year(d)
select year('2017-12-15');

// 从时间中选择出小时数
hour(d)
select hour('2017-12-15 10:20:20');

// 从时间中选择出分钟数
minute(d)
select minute('2017-12-15 10:20:20');

4、date_format函数

date_format(date, formate)根据formate指定的格式显示date值,格式如下:

select date_format('2017-12-15 10:20:20', '%W %M %D %Y') as col1;

5、聚合函数

// count总数查询
select count(*)  from 表名
select count(*) from user;

// sum求和查询
select sum(列) from 表名
select sum(id) from user;

// avg平均值查询
select avg(列) from 表名
select avg(id) from user;

// max最大值查询
select max(列) from 表名
select max(id) from user;

// min最小值查询
select min(列) from 表名
select min(id) from user;