MySQL学习三：常见函数

一、单行函数

1.1 字符函数

1.1.1 length

作用：获取参数值的字节个数（utf-8一个汉字代表3个字节，gbk为2个字节）

select length('小明笑了hahaha')

1.1.2 concat

作用：拼接字符串

select concat(last_name,'_',first_name) 姓名 from employees

1.1.3 upper 和 lower

作用：upper 将字符全部变为大写，lower 将字符全部变为小写

select upper('john') as 大写姓名, lower('JOHN') as 小写姓名

1.1.4 substr

作用1：截取从指定索引处后面所有字符
作用2：截取从指定索引处指定字符长度的字符
注意：SQL 和 MySQL 语言中，索引从 1 开始

select substr('小明在草地上踢足球', 6) as output1, 
substr('小明在草地上踢足球', 2, 5) as output2

1.1.5 instr

作用：返回子符串第一次出现的索引，如果找不到返回 0

select instr('小明高兴地吃着棒棒糖', '棒棒糖')  as output1,
instr('小明高兴地吃着棒棒糖', '天空') as output2

1.1.6 trim

作用：去除字符串前后空格，或去除字符串前后指定字符

select length(trim('    小明    ')) as output1, 
trim('a' from 'aaaaaaaa小aaa明aaaaaa') as output2,
trim('aa' from 'aaaaaaa小aaa明aaaaaa') as output3

1.1.7 lpad 和 rpad

作用：lpad 用指定的字符实现左填充指定长度，rpad 用指定的字符实现右填充指定长度

select lpad('小明',4,'*') as output1,
rpad('小明',6,'*') as output2

1.1.8 replace

作用：用其它字符替换字符串中的所有原有字符

select replace('小明在草地上踢足球', '踢足球', '野餐') as output1,
replace('踢足球小明踢足球在草地上踢足球', '踢足球', '天空') as output2

1.2 数学函数

1.2.1 round

作用：四舍五入，可根据需要保留小数位数

select round(2.65) as output1,
round(-1.55) as output2,
round(1.56789,3) as output3

1.2.2 ceil 和 floor

作用：ceil 向上取整（返回 >= 该数字的最小整数），floor 向下取整（返回<=该参数的最大整数）

select ceil(2.47) as output1,
ceil(-4.3) as output2,
floor(9.99) as output3,
floor(-6.99) as output4

1.2.3 truncate

作用：截断数字的小数位数

select truncate(1.697,2) as output1,
truncate(-1.468,1) as output2

1.2.4 mod

作用：取余

select mod(10, 3) as output

1.3 日期函数

1.3.1 curdate

作用：返回当前的系统日期，不包含时间

select curdate() as 当前日期

1.3.2 获取指定的部分（年、月、日等）

select year('2003-08-27') as 年份,
month('2015-11-16') as 月份,
day('2020-05-23') as 日

1.3.3 获取任意一个月份中有多少天

select day(last_day('2008-02-01')) as days

1.3.4 str_to_date

作用：将日期格式的字符转换成指定格式的日期

# 查询入职日期为1992年4月3日的员工信息
select * from employees 
where hiredate = str_to_date('4-3 1992','%c-%d %Y')

1.3.5 date_format

作用：将日期转换成指定格式的字符

#查询有奖金的员工名和入职日期(xx月/xx日 xx年)
select last_name, date_format(hiredate,'%m月/%d日 %y年') 入职日期
from employees
where commission_pct is not null

1.4 流程控制函数

1.4.1 if 函数

select last_name,commission_pct, 
if(commission_pct is null, '没奖金，呵呵', '有奖金，嘻嘻') 备注
from employees

1.4.2 case 函数

语法一：
case 要判断的字段或表达式
when 常量1 then 要显示的值 1 或语句 1;
when 常量2 then 要显示的值 2 或语句 2;
。。。。。。
else 要显示的值 n 或语句 n;
end as 别名

# 部门号=30，显示的工资为1.1倍; 部门号=40，显示的工资为1.2倍
# 部门号=50，显示的工资为1.3倍; 其他部门，显示的工资为原工资
select salary 原始工资, department_id,
case department_id
	when 30 then salary * 1.1
	when 40 then salary * 1.2
	when 50 then salary * 1.3
	else salary
	end as 新工资
from employees

语法二：
case
when 条件1 then 要显示的值 1 或语句 1
when 条件2 then 要显示的值 2 或语句 2
。。。。。。
else 要显示的值 n 或语句 n
end as 别名

# 如果工资>20000，显示A级别; 如果工资>15000，显示B级别
# 如果工资>10000，显示C级别; 否则，显示D级别
select salary,
case
	when salary>20000 then 'A'
	when salary>15000 then 'B'
	when salary>10000 then 'C'
	else 'D'
	end as 工资级别
from employees

二、分组函数 (聚合函数)

2.1 分类

sum 求和，avg 平均值，max 最大值，min 最小值，count 计算个数

2.2 特点

① sum、avg 一般用于处理数值型，max、min、count 可以处理任何类型
② 以上分组函数都忽略 null 值
③ 可以和 distinct 搭配，实现去重的运算
④ 分组函数一同查询的字段，要求是 group by 后的字段

2.3 count 函数详细介绍

一般使用 count(*) 统计个数

select count(salary) as output1, 
count(*) as output2,
count(1) as output3
from employees