MySQL 常用函数

一、基本概念

1. 调用方式：select 函数名(实参列表) from 表
2. 重点学习内容：函数名称、函数实现的功能

二、分类

1. 单行函数：如 ifnull、length、concat，传入一个值返回一个值，详细点说就是你在查询过程中在 select 后面调用了单行函数，在里面传入了某个字段，那么查询结果的显示就会是该字段在经过了单行函数处理后的结果。
2. 分组函数：count、max、min、avg、sum，传入一组值返回一组值。

这里我们重点学习单行函数，下面介绍的都是单行函数。

三、字符函数

函数	作用
length	用于获取参数值的字节个数(与 MySQL 设置的字符集有关)
concat	拼接字符串
upper、lower	将传入的单词变为大写、小写
substr	截取传入字符串的字串
instr	返回子串在母串中第一次出现的索引
trim	去处字符串前后空格
lpad、rpad	左、右填充字符串到指定长度
replace	替换字符串中指定子串为新的子串

1. length

   select ename as '姓名', length(ename) as '姓名字节个数' from emp;
   

2. concat

   # 将 people 表中的 姓和名连接起来，中间用'_'间隔
   select concat(last_name, '_', first_name) as '姓名' from people;
   

3. upper、lower

   # 将姓用大写字母展示，名用小写字母展示
   select concat(upper(last_name), '_', lower(first_name)) as '姓名' from people;
   

4. substr

   # (字符串, 起始索引, 长度)，注意 SQL 中的起始索引都是从 1 开始的
   # (字符串, 起始索引)
   select substr('莫莫喜欢吃冰淇淋', 6, 3); # 从索引 6 开始，3 个字符
   select substr('莫莫喜欢吃冰淇淋', 6); # 从索引 6 开始直至最后
   

5. 综合案例

   # 姓名中首字符大写，其余字符小写，然后用 '_' 拼接显示出来
   select concat(substr(last_name, 1, 1), '_', substr(concat(last_name, first_name), 2)) from people;
   

6. instr

   # 返回子串 '喜欢'，在母串 '莫莫喜欢吃冰淇淋'，第一次出现的索引值，如果找不到则返回 0
   select instr('莫莫喜欢吃冰淇淋', '喜欢'); # 返回 3
   

7. trim

   # 去掉字符串前后的空格
   select trim('     速冻莫莫 ');
   
   # 去掉字符串前后的 'a'
   select trim('a' from 'aaaaaaaa速冻莫莫aaaaaaaaaaa' );
   

8. lpad、rpad

   # 用 (指定字符) 左填充 (指定字符串)到 (指定长度)
   select lpad('莫莫', 5, '*'); # '***莫莫';
   
   # 用 (指定字符) 右填充 (指定字符串) 到 (指定长度)
   select rpad('莫莫', 5, '*'); # '莫莫***';
   

9. replace

   # 将 (指定字符串) 中的 (指定子串) 替换为 (目标子串)
   select replace('莫莫喜欢吃冰淇淋', '冰淇淋', '榴莲'); # '莫莫喜欢吃榴莲'
   

四、数学函数

函数	作用
round	四舍五入为整数
ceil、floor	向上、向下取整
truncate	强制保留指定位数小数
mod	取余数

1. round

   # 四舍五入为整数
   select round(3.656); # 4
   
   # 四舍五入小数点后保留 2 位
   select round(3.656, 2); # 3.66
   

2. ceil、floor

   # 向上取整
   select ceil(1.002); # 2
   
   # 向下取整
   select floor(0.9); # 0
   

3. truncate

   # 强制保留指定数位小数
   select truncate(11.3729, 2); # 11.37
   

4. mod

   # 取余数
   select mod(10, 3); # 输出1
   

五、日期函数

函数	作用
now	返回当前系统日期 + 时间
curdate	返回当前系统日期
curtime	返回当前系统时间
year、month、day …	获取当前日期数据类型数据的年、月、日…

1. now

   # 返回当前系统日期 + 时间
   select now(); # 2021-01-04 14:47:35
   

2. curdate

   # 返回当前系统日期
   select curdate(); # 2021-01-04
   

3. curtime

   # 返回当前系统时间
   select curtime(); # 14:47:35
   

4. year、month、day、hour、minute、second

   # 获取日期数据的年
   select year('2019-01-01 10:12:32'); # 2019
   
   # 获取日期数据的月
   select month('2019-01-01 10:12:32'); # 1
   
   # 获取日期数据的日
   select month('2019-01-01 10:12:32'); # 1
   
   # 获取日期数据的时
   select hour('2019-01-01 10:12:32'); # 10
   
   # 获取日期数据的分
   select minute('2019-01-01 10:12:32'); # 12
   
   # 获取日期数据的秒
   select second('2019-01-01 10:12:32'); # 32
   

六、流程控制函数

1. if

   # 等价于 3 > 1 ? '3大于1' : '3不大于1'
   select if(3 > 1, '3大于1', '3不大于1');
   

2. case

   # 用法一：相当于 swicth
   case 要判断的字段或表达式
   when 常量1 then 要显示的值1或语句1
   when 常量2 then 要显示的值2或语句2
   ...
   else 要显示的值n或语句n;
   end
   
   /*
   	案例：查询员工的工资，要求：
   		部门号=10，显示工资为1.1倍
   		部门号=20，显示工资为1.2倍
   		部门号=30，显示工资为1.3倍
   		其他部门，显示工资为原工资
   */
   select salary oldsal, deptno,
   case deptno
   when 10 then oldsal * 1.1
   when 20 then oldsal * 1.2
   when 30 then oldsal * 1.3
   else oldsal
   end as newsal
   from emp;
   
   # 用法二：相当于 if...else if...else
   case
   when 条件1 then 要显示的值1或语句1
   when 条件2 then 要显示的值2或语句2
   ...
   else 要显示的值n或语句n
   end
   /*
   	案例：查询员工的工资情况
   		如果工资 > 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 emp;