字符串函数
# 字符串拼接 select concat('hello ','mysql'); # 字符串转为小写 select lower("Hello"); # 字符串转为大写 select upper("Hello"); # 左填充 select lpad('01',5,'-'); # 右填充 select rpad('01',5,'-'); # 去掉前后空格 select trim(" wer "); #返回字符串str从start位置起len个长度的字符串 select substring("hello word!",1,3);
数值函数
#向上取整 select ceil(1.2); #向下取整 select floor(1.2); #返回x/y的模 select mod(8,3); #返回0-1内的随机数 select rand(); #求参数x的四舍5入的值,保留位小数 select round(2.336,2);
日期函数
# 当前日期 select curdate(); # 当前时间 select curtime(); # 返回当前日期和时间 select now(); # 指定日期年 select year(now()); # 返回一个日期时间值加上一个时间间隔之后的时间值 select date_add(now(), INTERVAL 70 DAY); # 返回两个日期之间的天数 select datediff(curdate(),'2022-07-05');
流程函数
# if select u.id,u.username,u.password,u.email,if(u.id>10,'user','Vip') as root from user as u where u.password = '123'; # ifNULl select u.id,u.username,u.password,u.email,IFNULL(u.email,'没有填') as detail from user as u where u.password = '123'; # case when select u.id,u.username,u.password,u.email,case when u.email then u.email else '无' end from user as u where u.password = '123'; # select u.id,u.username,u.password,u.email,case u.password when 123 then '测试' else 'default' end from user as u ;