《MySQL基础——内置函数》

一、日期函数：精准处理时间维度数据

日期函数主要用于获取、计算、调整日期和时间，在订单统计、日志分析、用户生日管理等场景中高频使用。以下是核心函数及实战案例：

1. 核心日期函数速查表

函数名称	描述	示例	结果
current_date()	获取当前日期（格式：YYYY-MM-DD）	select current_date();	2017-11-19
current_time()	获取当前时间（格式：HH:MM:SS）	select current_time();	13:51:21
current_timestamp()	获取当前时间戳（日期 + 时间）	select current_timestamp();	2017-11-19 13:51:48
now()	与current_timestamp()功能一致，更常用	select now();	2017-11-19 14:12:20
date(datetime)	提取datetime类型字段的日期部分	select date(‘2017-11-19 14:12:20’);	2017-11-19
date_add(date, interval 数值 单位)	给日期 / 时间添加指定时间间隔（单位：year/minute/second 等）	select date_add(‘2017-10-28’, interval 10 day);	2017-11-07
date_sub(date, interval 数值 单位)	从日期 / 时间中减去指定时间间隔	select date_sub(‘2017-10-01’, interval 2 day);	2017-09-29
datediff(date1, date2)	计算两个日期的差值（单位：天，结果 = date1-date2）	select datediff(‘2017-10-10’, ‘2016-09-01’);	404

2. 实战场景案例

案例 1：创建生日记录表并插入当前日期
需求：新建一张用户生日表，自动插入当前日期作为生日数据。

-- 1. 创建表
create table tmp(
    id int primary key auto_increment,  -- 自增主键
    birthday date  -- 生日字段（仅存日期）
);

-- 2. 插入当前日期
insert into tmp(birthday) values(current_date());

-- 3. 查看数据
select * from tmp;
-- 结果：
+----+------------+
| id | birthday   |
+----+------------+
|  1 | 2017-11-19 |
+----+------------+

案例 2：筛选 2 分钟内发布的留言
需求：新建留言表，插入数据后，查询 “2 分钟内发布的帖子”。

-- 1. 创建留言表
create table msg (
    id int primary key auto_increment,
    content varchar(30) not null,  -- 留言内容
    sendtime datetime  -- 发布时间（含时分秒）
);

-- 2. 插入2条留言（使用now()获取当前时间戳）
insert into msg(content,sendtime) values('hello1', now());
insert into msg(content,sendtime) values('hello2', now());

-- 3. 只显示留言内容和发布日期（隐藏时间）
select content, date(sendtime) as publish_date from msg;
-- 结果：
+---------+-------------+
| content | publish_date|
+---------+-------------+
| hello1  | 2017-11-19  |
| hello2  | 2017-11-19  |
+---------+-------------+

-- 4. 查询2分钟内发布的留言
-- 逻辑：给发布时间加2分钟，如果仍晚于当前时间，说明是2分钟内发布的
select * from msg where date_add(sendtime, interval 2 minute) > now();
--执行结果（假设当前时间在 2 分钟内）：
+----+---------+---------------------+
| id | content | sendtime            |
+----+---------+---------------------+
|  1 | hello1  | 2017-11-19 14:15:30 |
|  2 | hello2  | 2017-11-19 14:15:32 |
+----+---------+---------------------+

二、字符串函数：灵活处理文本数据

字符串函数用于对文本进行拼接、截取、替换、大小写转换等操作，常见于报表生成、数据格式化、模糊查询等场景。

1. 核心字符串函数速查表

函数名称	描述
charset(str)	返回字符串的字符集（如 utf8、gbk）
concat(str1, str2, …)	拼接多个字符串
instr(str, substr)	返回子串substr在str中首次出现的位置（无则返回 0，位置从 1 开始）
ucase(str) / upper(str)	将字符串转为大写
lcase(str) / lower(str)	将字符串转为小写
left(str, length)	从字符串左侧截取指定长度的字符
replace(str, search_str, replace_str)	用replace_str替换str中的search_str
length(str)	返回字符串的字节数（中文占多字节，与字符集相关）
substring(str, pos [, length])	从pos位置开始，截取str的length个字符（pos 从 1 开始）
trim(str) / ltrim(str) / rtrim(str)	去除字符串两端 / 左端 / 右端的空格

2. 实战场景案例

案例 1：格式化学生成绩展示
需求：将学生表（student）中的 “姓名、语文、数学成绩” 按 “XXX 的语文是 XXX 分，数学是 XXX 分” 格式显示。

select concat(name, '的语文是', chinese, '分，数学是', math, '分') as '成绩详情' 
from student;
-- 结果示例：
+-------------------------------+
| 成绩详情                      |
+-------------------------------+
| 张三的语文是90分，数学是85分  |
| 李四的语文是88分，数学是92分  |
+-------------------------------+

案例 2：处理员工姓名格式
需求：1. 截取员工表（EMP）中姓名的第 2-3 个字符；2. 将姓名首字母转为小写，其余不变。

-- 1. 截取第2-3个字符
select substring(ename, 2, 2) as sub_name, ename as full_name from EMP;
-- 示例结果：
+----------+-----------+
| sub_name | full_name |
+----------+-----------+
| MI       | SMITH     |
| LL       | ALLEN     |
+----------+-----------+

-- 2. 首字母小写，其余不变
-- 逻辑：截取首字符转小写 + 截取剩余字符拼接
select concat(lcase(substring(ename, 1, 1)), substring(ename, 2)) as lower_name 
from EMP;
-- 示例结果：
+-----------+
| lower_name|
+-----------+
| sMITH     |
| aLLEN     |
+-----------+

案例 3：替换字符串中的特定内容
需求：将 EMP 表中姓名包含 “S” 的部分，替换为 “上海”。

select replace(ename, 'S', '上海') as new_name, ename as old_name from EMP;
-- 示例结果：若ename为"SMITH"，new_name为"上海MITH"

三、数学函数：高效完成数值计算

数学函数用于处理数值类型数据的计算，如取整、进制转换、随机数生成等，适用于数据统计、数值格式化等场景。

1. 核心数学函数速查表

函数名称	描述	示例	结果
abs(number)	计算绝对值	select abs(-100.2);	100.2
bin(decimal)	将十进制数转为二进制	select bin(10);	1010
hex(decimal)	将十进制数转为十六进制	select hex(10);	A
conv(number, from_base, to_base)	任意进制转换（如十进制转八进制）	select conv(10, 10, 8);	12
ceiling(number)	向上取整（返回大于等于该数的最小整数）	select ceiling(23.04);	24
floor(number)	向下取整（返回小于等于该数的最大整数）	select floor(23.7);	23
format(number, decimals)	格式化数值，保留指定小数位（四舍五入）	select format(12.3456, 2);	12.35
rand()	生成 0.0（含）到 1.0（不含）的随机浮点数	select rand();	0.657892…
mod(number, denominator)	取模（求余数）	select mod(10, 3);	1

四、其他实用函数：获取元信息与数据安全

这类函数虽不局限于特定数据类型，但在数据库管理、数据安全等场景中不可或缺。

1. 核心函数速查表

函数名称	描述	示例	结果
user()	查询当前登录 MySQL 的用户	select user();	root@localhost
database()	显示当前正在使用的数据库	select database();	test_db
md5(str)	对字符串进行 MD5 加密（生成 32 位摘要，不可逆）	select md5(‘admin’);	21232f297a57a5a743894a0e4a801fc3
password(str)	MySQL 专用用户密码加密（生成 41 位哈希值）	select password(‘root’);	*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
ifnull(val1, val2)	若val1为 NULL，返回val2；否则返回val1	select ifnull(null, ‘123’);	123
		select ifnull(‘abc’, ‘123’);	abc

五、实战 OJ

SQL250 查找字符串中逗号出现的次数