03_SQL 中的字符、数学、日期函数

这篇博客给大家介绍 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 函数，这里有一个技巧：$MOD(a,b)=a-a/b\ast 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;