这篇博客给大家介绍 SQL 中的常用函数,包括字符函数、数学函数、日期函数,还有一些流程控制函数。
先做个函数的汇总:
字符函数
concat 拼接
substr 截取子串
upper 转换成大写
lower 转换成小写
trim 去前后指定的空格和字符
ltrim 去左边空格
rtrim 去右边空格
replace 替换
lpad 左填充
rpad 右填充
instr 返回子串第一次出现的索引
length 获取字节个数
数学函数
round 四舍五入
rand 随机数
floor 向下取整
ceil 向上取整
mod 取余
truncate 截断
日期函数
now 当前系统日期+时间
curdate 当前系统日期
curtime 当前系统时间
str_to_date 将字符转换成日期
date_format 将日期转换成字符
流程控制函数
if 处理双分支
case语句 处理多分支
情况1:处理等值判断
情况2:处理条件判断
其他函数
version 版本
database 当前库
user 当前连接用户
下面挑几个函数的案例让大家对函数有一个直观的了解。
字符函数
select length("张三丰hahaha")
> 15
substr(str, pos, len) # 索引从 1 开始
select substr("李莫愁爱上陆展元", 7)
> "陆展元"
insert(strs, str) # 在strs中找str,找到返回起始索引,找不到返回0
select insert("杨不悔爱上殷六侠","殷六侠")
> 7
select trim("a" from "aaa张aaa三丰aaa")
> "张aaa三丰"
select lpad('殷素素', 10, '*')
> '*******殷素素'
select replace('张无忌爱上了周芷若', '周芷若', '赵敏')
> '张无忌爱上了赵敏'
数学函数
select round(1.65)
> 2
select round(1.567, 2)
> 1.57
select ceil(1.00)
> 1
select ceil(-1.02)
> -1
select floor(9.99)
> 9
select floor(-9.99)
> -10
select truncate(1.65, 1)
> 1.6
select mod(10, 3)
> 1
select mod(10, -3)
> 1
关于 MOD 函数,这里有一个技巧: M O D ( a , b ) = a − a / b ∗ b MOD(a, b) = a - a / b * b MOD(a,b)=a−a/b∗b
日期函数
案例: 查询有奖金的员工名和入职日期。
select last_name, date_format(hire_date, '%m月%d日%y年') as '入职日期'
from employees
where commission_pct is not null;
案例:查询入职日期为 1992-4-3 的员工信息。
select * from employees
where hiredate = str_to_date('4-3 1992', '%c-%d %Y)
流程控制函数
以下两种 case 函数,第一个case后面加了要判断的字段或表达式, when 后面是常量。第二个case后没有添加字段或表达式,when 后面是条件判断。
select salary, department_id
case department_id
when 30 then salary * 1.1
when 40 then salary * 1.2
else salary
end as '新工资'
from employees;
select salary
case
when salary > 20000 then 'A'
when salary > 15000 then 'B'
else 'D'
end as '工资级别'
from employees;