MySQL学习四-＜基础篇＞-函数

字符串函数

# 字符串拼接
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 ;