MySQL 实战指南：四大核心函数（字符串/数值/日期/流程）详解与案例应用

MySQL学习记录 —- 函数

日期： 11.1

📖今日知识点 ——函数

1.什么是函数？

函数是指一段可以直接被另一段程序调用的程序或代码。

2.有哪些函数？

📖今日知识点 ——字符串函### MySQL中内置了很多字符串函数，常用的几个如下：

⚠️ 注意：MySQL 的 SUBSTRING(str,start,len) 中 start 是从 1 开始计数的，而 Java 的 substring(beginIndex,endIndex) 是从 0 开始计数的，两者并不一样。

例如 substring('Hello MySQL',1,5) 在 MySQL 中返回 Hello（第 1 个字符起取 5 个）；而 Java 中 "Hello MySQL".substring(0,5) 才返回 Hello，若写成 substring(1,5) 得到的则是 ello。

另外，MySQL 的第三个参数 len 表示截取长度，Java 的第二个参数 endIndex 表示结束下标（不含），语义也不同，迁移代码时尤其要注意。

|

💻练习代码

— concat 连接字符串
select concat('Hello','MySQL');

— lower 转为小写
select lower('Hello');

— upper 转为大写
select upper('Hello');

— lpad 填充左边
select lpad('01',5,'-');

— rpad 填充右边
select rpad('01',5,'-');

— trim 去掉字符串头部和尾部的空格
select trim(' Hello MySQL ');

— substring 截取字符串
select substring('Hello MySQL',1,5);

— 由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。比如：1号员工的工号应该为00001。
update emp set workno = lpad(workno,5,'0');

运行结果：

📖今日知识点 ——数值函数

MySQL 中内置了很多数值函数，常用的几个如下：

💻练习代码

— 数值函数
— ceil() 向上取整
select ceil(1.1); — 结果为2
— floor() 向下取整
select floor(1.9); — 结果为1
— MOD(x,y) 返回x除以y的余数
select mod(7,4); — 结果为3
— RAND() 返回0~1内的随机值，即[0,1)
select rand(); — 随机值–0.6933717817937692
— ROUND(x,y) 求参数x的四舍五入的值，保留y位小数
select round(1.234,2); — 结果为1.23

— 案例：通过数据库的函数，生成一个六位数的随机验证码
select lpad(round(rand()*1000000,0),6,'0');

运行结果：

📖今日知识点 ——日期函数

MySQL 中内置了很多日期函数，常用的几个如下：

💻练习代码

— 日期函数
— curdate() 返回当前日期,年月日
select curdate();

— curtime() 返回当前时间,时分秒
select curtime();

— now() 返回当前日期和时间,年月日时分秒
select now();

— year(date)获取指定date的年份
select year(now());

— month(date) 获取指定date的月份
select month(now());

— day(date) 获取指定date的日期
select day(now());

— date_add(date,interval expr type) 返回一个日期/时间值加上一个时间间隔expr后的时间值
select date_add(now(),interval 70 day);

— datediff(date1,date2) 返回起始时间date1和结束时间date2之间的天数
select datediff('2024-8-25',now());

— 案例：查询所有员工的入职天数，并根据入职天数倒序排序。
select name ,datediff(now(),emp.entrydate) as entrydates from emp order by entrydates desc;

运行结果：

📖今日知识点 ——流程函数

1.常见流程函数

是很常用的一类函数，可以在 SQL 语句中实现条件筛选，从而提高语句的效率。常用的几个如下：

💻练习代码

— 流程控制函数
— if
select if(1 > 0,'Yes','No'); — 结果为Yes

— ifnull
select ifnull('OK','Default'); — 结果为OK
— 这里的 '' 是一个空字符串，它并不是 NULL，是一个有效的非空值。
select ifnull('','Default'); — 结果为空字符串
select ifnull(null,'Default'); — 结果为Default

— case when then else end
— 需求：查询emp表的员工姓名和工作地址（北京/上海 —-> 一线城市，其他 —-> 二线城市）
select
name,
(case workaddress when '北京' then '一线城市' when '上海' then '一线城市'else '二线城市'end) as '工作地址'
from emp;

— 案例：统计班级各个学员的成绩，展示的规则如下：
— >= 85，展示优秀
— >= 60，展示及格
— 否则，展示不及格
— 新建表格
create table score(
id int comment 'ID',
name varchar(20) comment '姓名',
math int comment '数学成绩',
english int comment '英语成绩',
chinese int comment '语文成绩'
) comment '学生成绩表';

— 插入数据
insert into score(id, name, math, english, chinese) values
(1, 'Tom', 67, 88, 95),
(2, 'Rose', 23, 66, 90),
(3, 'Jack', 56, 98, 76);

select
id,
name,
(case when math >= 85 then '优秀' when math >= 60 then '及格' else '不及格' end) as '数学成绩',
(case when english >= 85 then '优秀' when english >= 60 then '及格' else '不及格' end) as '英语成绩',
(case when chinese >= 85 then '优秀' when chinese >= 60 then '及格' else '不及格' end) as '语文成绩'
from score;

运行结果：

📖今日知识点 ——函数小结

1.总结：

2.场景应用说明

DATEDIFF: 数据库表中，存储的是入职日期，如 2000-11-12，如何快速计算入职天数？？？（例如：加入公司天数 1495天）

CASE … WHEN …: 数据库表中，存储的是学生的分数值，如98、75，如何快速判定分数的等级呢？？？（例如：判定为“及格”、“优秀”等）