函数
函数
参考
- https://dev.mysql.com/doc/refman/8.0/en/functions.html
1、简介
函数 是指一段可以直接被另一段程序调用的程序或代码。 也就意味着，这一段程序或代码在MySQL中已经给我们提供了，我们要做的就是在合适的业务场景调用对应的函数完成对应的业务需求即可。 那么，函数到底在哪儿使用呢？
我们先来看两个场景：
在企业的OA或其他的人力系统中，经常会提供的有这样一个功能，每一个员工登录上来之后都能够看到当前员工入职的天数。 而在数据库中，存储的都是入职日期，如 2000-11-12，那如果快速计算出天数呢？
在做报表这类的业务需求中,我们要展示出学员的分数等级分布。而在数据库中，存储的是学生的分数值，如98/75，如何快速判定分数的等级呢？
其实，上述的这一类的需求呢，我们通过MySQL中的函数都可以很方便的实现 。
MySQL中的函数主要分为以下四类： 字符串函数、数值函数、日期函数、流程函数。
2、字符串函数
MySQL中内置了很多字符串函数，常用的几个如下：
| 函数 | 功能 |
|---|---|
| CONCAT(S1,S2,...Sn) | 字符串拼接，将S1，S2，... Sn拼接成一个字符串 |
| LOWER(str) | 将字符串str全部转为小写 |
| UPPER(str) | 将字符串str全部转为大写 |
| LPAD(str,n,pad) | 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度 |
| RPAD(str,n,pad) | 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度 |
| TRIM(str) | 去掉字符串头部和尾部的空格 |
| SUBSTRING(str,start,len) | 返回从字符串str从start位置起的len个长度的字符串 |
演示如下：
2.1、concat : 字符串拼接
select concat('Hello' , ' MySQL');
2.2、lower : 全部转小写
select lower('Hello');
2.3、upper : 全部转大写
select upper('Hello');
2.4、lpad : 左填充
select lpad('01', 5, '-');
2.5、rpad : 右填充
select rpad('01', 5, '-');
2.6、trim : 去除空格
trim()函数用来过滤指定的字符串。
完整格式：TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
删除指定情况下str中的remstr。若没有指定情况，默认删除str前后空格。
经测试发现，trim()函数只能用来删除前后数据。
| 函数 | 功能 |
|---|---|
| BOTH | 删除指定的首尾字符 |
| LEADING | 删除指定的首字符 |
| TRAILING | 删除指定的尾字符 |
| LTRIM | 函数去除左空格 |
| RTRIM | 函数去除右空格 |
# BOTH 删除指定的首尾字符
SELECT TRIM(BOTH 'a' FROM 'aaababccaa');
# 输出：'babcc'
#LEADING 删除指定的首字符
SELECT TRIM(LEADING 'a' FROM 'aaababccaa');
# 输出：'babccaa'
#TRAILING删除指定的尾字符
SELECT TRIM(TRAILING 'a' FROM 'aaababccaa');
# 输出：'aaababcc'
#无条件，删除前后空格
SELECT TRIM(' aaababccaa ');
# 输出：'aaababccaa'
# MySQL中也可以使用LTRIM，RTRIM函数删除左或右空格
# LTRIM函数去除左空格
SELECT LTRIM(' aaababccaa');
# 输出：'aaababccaa'
# RTRIM函数去除右空格
SELECT RTRIM('aaababccaa ');
# 输出：'aaababccaa'
2.7、instr()
select trim(' Hello MySQL ');
格式一：
instr( string1, string2 ) / instr(源字符串, 目标字符串)格式二：
instr( string1, string2 [, start_position [, nth_appearance ] ] ) / instr(源字符串, 目标字符串, 起始位置, 匹配序号)
解析：
格式一：在
string1中查找string2，返回string2出现的第一个位置（index）。index是从1开始计算，如果没有找到就直接返回0，没有返回负数的情况。格式二：在string1中查找string2，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2。从1开始计算，如果没有找到就直接返回0，没有返回负数的情况。
select instr('helloworld', 'l')
from dual;
# --返回结果：3 默认第一次出现“l”的位置
select instr('helloworld', 'lo')
from dual;
# --返回结果：4 即：在“lo”中，“l”开始出现的位置
select instr('helloworld', 'wo')
from dual;
# --返回结果：6 即“w”开始出现的位置
2.8、trim()和instr()函数结合使用
在实际场景中，根据用户输入的数据进行条件查询。例如，根据姓名模糊查询，用户在搜索框中输入的时候前后可能会带上空格，这个时候我们就可以使用trim()函数去除用户输入数据的前后空格，然后使instr()函数检索该输入数据在数据库中对应字段是否存在。
select * from user where instr(user_name,trim(#{userName})) > 0;
select *
from tb_user tu
where instr(name, trim('Jin')) > 0;
2.9、substring : 截取子字符串
# 下标从1开始，不是从0
select substring('Hello MySQL',1,5);
案例:
由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。比如： 1号员工的工号应该为00001。
处理完毕后, 具体的数据为:
update emp set workno = lpad(workno, 5, '0');
3、数值函数
常见的数值函数如下：
| 函数 | 功能 |
|---|---|
| CEIL(x) | 向上取整 |
| FLOOR(x) | 向下取整 |
| MOD(x,y) | 返回x/y的模 |
| RAND() | 返回0~1内的随机数 |
| ROUND(x,y) | 求参数x的四舍五入的值，保留y位小数 |
演示如下：
3.1、ceil：向上取整
select ceil(1.1);/*输出结果：2*/
3.2、floor：向下取整
select floor(1.9);/*输出结果：1*/
3.3、mod：取模
select mod(7, 4);/*输出结果：3*/
3.4、rand：获取随机数
select rand();/*输出结果：0.1519974555373168*/
3.5、round：四舍五入
select round(2.344, 2);/*输出结果：2.34*/
3.6、六位随机数案例
通过数据库的函数，生成一个六位数的随机验证码。
思路： 获取随机数可以通过rand()函数，但是获取出来的随机数是在0-1之间的，所以可以在其基础上乘以1000000，然后舍弃小数部分，如果长度不足6位，补0
select lpad(round(rand() * 1000000, 0), 6, '0');/*输出结果：573382*/
4、日期函数
常见的日期函数如下：
| 函数 | 功能 |
|---|---|
| CURDATE() | 返回当前日期 |
| CURTIME() | 返回当前时间 |
| NOW() | 返回当前日期和时间 |
| YEAR(date) | 获取指定date的年份 |
| MONTH(date) | 获取指定date的月份 |
| DAY(date) | 获取指定date的日期 |
| DATE_ADD(date, INTERVAL expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
| DATEDIFF(date1,date2) | 返回起始时间date1 和 结束时间date2之间的天数 |
演示如下：
# A. curdate：当前日期
select curdate();
# B. curtime：当前时间
select curtime();
# C. now：当前日期和时间
select now();
# D. YEAR , MONTH , DAY：当前年、月、日
1 select YEAR(now());
2 select MONTH(now());
3 select DAY(now());
# E. date_add：增加指定的时间间隔
select date_add(now(), INTERVAL 70 YEAR );
# F. datediff：获取两个日期相差的天数
select datediff('2021-10-01', '2021-12-01');/*输出结果：-61*/
#查询所有员工的入职天数，并根据入职天数倒序排序。
select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by entrydays desc;
5、流程函数
| 函数 | 功能 |
|---|---|
| IF(value , t , f) | 如果value为true，则返回t，否则返回 f |
| IFNULL(value1 , value2) | 如果value1不为空，返回value1，否则返回value2 |
| CASE WHEN [ val1 ] THEN [res1] ... ELSE [ default ] END | 如果val1为true，返回res1，... 否则返回default默认值 |
| CASE [ expr ] WHEN [ val1 ] THEN [res1] ... ELSE [ default ] END | 如果expr的值等于val1，返回 res1，... 否则返回default默认值 |
演示如下：
# if
select if(false, 'Ok', 'Error');
# ifnull
select ifnull('Ok','Default');/*Ok*/
select ifnull('','Default');/* '' */
select ifnull(null,'Default');/*Default*/
# case when then else end
select name,( case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end ) as '工作地址' from emp;