7. MySQL常用函数

字符串函数

-- 字符串函数

-- ASCII(S) 返回字符串S中的第一个字符的ASCII码值
-- CHAR_LENGTH(s) 返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同

sql

SELECT ascii("abc"),ascii("ABC"),CHAR_LENGTH("1234");

-- REPLACE(str, a, b) 用字符串b替换字符串str中所有出现的字符串a

sql

SELECT replace("Hello world!","world","MySQL");

-- LENGTH(列名)：算长度，判断乱码；

sql

SELECT LENGTH(sname) from student; -- utf8编码一个汉字占3个字节
SELECT LENGTH(idnum) from student;

-- lower 全部转小写

sql

select lower('Hello');

-- upper 全部转大写

sql

select upper('Hello');

-- lpad   左填充  用字符串pad对str的左边进行填充，达到n个字符串长度

sql

select lpad('01', 5, '-');

-- rpad  右填充, 用字符串pad对str的右边进行填充，达到n个字符串长度

sql

select rpad('01', 5, '-');

-- trim   去除空格 去掉字符串头部和尾部的空格

sql

select trim(' Hello  MySQL ');

-- substring  截取子字符串
-- SUBSTRING(str,start,len) 返回从字符串str从start位置起的len个长度的字符串

sql

select substring('Hello MySQL',1,5);

-- SUBSTRING_INDEX(str, delimiter, count) 按分隔符截取子字符串
/*
str	字符串	要截取的原始字符串（必填）
delimiter	字符串	分隔符（必填，区分大小写！例如 "," 和 "," 相同，但 ":" 和 ";" 不同）
count	整数	截取次数（必填）：
- 正数：从左到右截取，保留第 count 个分隔符左侧的所有内容
- 负数：从右到左截取，保留第 abs(count) 个分隔符右侧的所有内容
- 0：返回空字符串（无实际意义）
返回值  返回截取后的子字符串；若分隔符不存在于原始字符串中，直接返回整个 str。
*/
-- 正向截取（count 为正数） 从左到右查找分隔符，保留第 count 个分隔符左侧的内容。
-- 示例1：截取第1个","左侧的内容

sql

SELECT SUBSTRING_INDEX('a,b,c,d', ',', 1); -- 结果：'a'

-- 示例2：截取第2个","左侧的内容（包含前2个分隔符之间的部分）

sql

SELECT SUBSTRING_INDEX('a,b,c,d', ',', 2); -- 结果：'a,b'

-- 示例3：count 超过分隔符总数（返回整个字符串）

sql

SELECT SUBSTRING_INDEX('a,b,c,d', ',', 5); -- 结果：'a,b,c,d'（仅3个","，count=5超出）

-- 反向截取（count 为负数）从右到左查找分隔符，保留第 abs(count) 个分隔符右侧的内容。
-- 示例1：截取第1个","右侧的内容（从右数第1个）

sql

SELECT SUBSTRING_INDEX('a,b,c,d', ',', -1); -- 结果：'d'

-- 示例2：截取第2个","右侧的内容（从右数第2个）

sql

SELECT SUBSTRING_INDEX('a,b,c,d', ',', -2); -- 结果：'c,d'

-- 示例3：count 绝对值超过分隔符总数（返回整个字符串）

sql

SELECT SUBSTRING_INDEX('a,b,c,d', ',', -10); -- 结果：'a,b,c,d'

-- SUBSTRING_INDEX() 最强大的用法是组合使用，实现复杂的字符串提取
-- 场景 1：提取 URL 中的域名
-- 原始URL：https://www.mysql.com/docs/

sql

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('https://www.mysql.com/docs/', '//', -1), '/', 1) AS domain;

-- 先去掉"https://"，得到"www.mysql.com/docs/"
-- 再按"/"截取第1个左侧，得到域名
-- 结果：'www.mysql.com'

-- 场景 2：拆分 IP 地址的各段（如提取前 3 段、最后 1 段）
-- 原始IP：192.168.1.100

sql

SELECT
  SUBSTRING_INDEX('192.168.1.100', '.', 1) AS ip_segment1, -- 第1段：192
  SUBSTRING_INDEX(SUBSTRING_INDEX('192.168.1.100', '.', 2), '.', -1) AS ip_segment2, -- 第2段：168
  SUBSTRING_INDEX(SUBSTRING_INDEX('192.168.1.100', '.', -2), '.', 1) AS ip_segment3, -- 第3段：1
  SUBSTRING_INDEX('192.168.1.100', '.', -1) AS ip_segment4; -- 第4段：100

-- 场景 3：提取邮箱的用户名和域名
-- 原始邮箱：user123@example.com

sql

SELECT
  SUBSTRING_INDEX('user123@example.com', '@', 1) AS username, -- 用户名：user123
  SUBSTRING_INDEX('user123@example.com', '@', -1) AS email_domain; -- 域名：example.com

-- 场景 4：处理带分隔符的多值字段（如标签、分类）
假设表中有一个 tags 字段，存储格式为 "Java,MySQL,Spring"，需提取第 2 个标签：

sql

SELECT
  SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', 2), ',', -1) AS second_tag
FROM test_table
WHERE tags = 'Java,MySQL,Spring'; -- 结果：'MySQL'

-- 场景 5：截取文件路径中的文件名（含后缀）
-- 原始路径：/home/user/docs/report.pdf

sql

SELECT SUBSTRING_INDEX('/home/user/docs/report.pdf', '/', -1) AS filename; -- 结果：'report.pdf'

-- 场景 6：提取日期中的年 / 月 / 日（假设日期格式为 "2025-11-04"）

sql

SELECT
  SUBSTRING_INDEX('2025-11-04', '-', 1) AS year, -- 年：2025
  SUBSTRING_INDEX(SUBSTRING_INDEX('2025-11-04', '-', 2), '-', -1) AS month, -- 月：11
  SUBSTRING_INDEX('2025-11-04', '-', -1) AS day; -- 日：04

-- LEFT(str,n) 返回字符串str最左边的n个字符
-- RIGHT(str,n) 返回字符串str最右边的n个字符

sql

SELECT left("123456789",3),right("123456789",3);

-- REPEAT(str, n) 返回str重复n次的结果
-- SPACE(n) 返回n个空格 
-- concat  字符串拼接
-- CONCAT(S1,S2,...Sn)  ,将s1，s2，...sn拼接成一个字符串

sql

select concat('Hello' ,SPACE(5), 'MySQL');

# SELECT concat('Hello' ,REPEAT("-",3), 'MySQL');

-- 案例:  由于想将学生学号统一调整为5位数，目前不足5位数的全部在前面补0。比如： 1号学生的学号应该为0001。

sql

update student set sno = lpad(sno, 5, '0') WHERE sno='001';

-- GROUP_CONCAT() 是 MySQL 内置的聚合函数，核心作用是将分组（GROUP BY）后的同一组内的指定字段值拼接成一个字符串，替代传统的多行返回，实现 “多行转单行” 的效果，常用于简化分组后的数据展示、汇总场景。
/*
GROUP_CONCAT([DISTINCT] 字段名 
             [ORDER BY 排序字段 ASC/DESC] 
             [SEPARATOR '分隔符']);
*/
-- 例1：查询每个学生选的所有课程（多行转单行）
# 多行数据

sql

SELECT sname,cname from student
join sc on student.Sno=sc.Sno
join course on sc.Cno=course.Cno;

# 使用group_concat合并成一行数据

sql

SELECT student.sno,sname,GROUP_CONCAT(cname SEPARATOR ' & ') 选课情况 from student
join sc on student.Sno=sc.Sno
join course on sc.Cno=course.Cno
GROUP BY sname
ORDER BY student.sno;

# 使用 | 分隔数据,并按学号升序排列

sql

SELECT student.sno,sname,GROUP_CONCAT(cname ORDER BY cname desc  SEPARATOR ' | ') from student
join sc on student.Sno=sc.Sno
join course on sc.Cno=course.Cno
GROUP BY sname
ORDER BY student.sno;

-- 将学生的各科成绩合并成一行显示

sql

SELECT sname,GROUP_CONCAT(concat(cname,' ',score) SEPARATOR ' | ') 各科成绩 from student
join sc on student.Sno=sc.Sno
join course on sc.Cno=course.Cno
GROUP BY sname;

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-- 数值函数

-- ABS(x) 返回x的绝对值

-- SIGN(X) 返回X的符号。正数返回1，负数返回-1，0返回0

-- PI() 返回圆周率的值

-- CEIL(x)，CEILING(x) 返回大于或等于某个值的最小整数

-- FLOOR(x) 返回小于或等于某个值的最大整数

-- LEAST(e1,e2,e3…) 返回列表中的最小值

sql

SELECT least(3,2,1);

-- GREATEST(e1,e2,e3…) 返回列表中的最大值

sql

SELECT GREATEST(3,2,1);

-- MOD(x,y) 返回X除以Y后的余数

sql

SELECT ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.9),FLOOR(-43.23),MOD(12,5);

-- RAND() 返回0~1的随机值

-- RAND(x)返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数

sql

SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1);

-- ROUND(x) 返回一个对x的值进行四舍五入后，最接近于X的整数

-- ROUND(x,y) 返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位

-- TRUNCATE(x,y) 返回数字x截断为y位小数的结果

sql

SELECT round(3.567),round(3.567,2);

-- SQRT(x) 返回x的平方根。当X的值为负数时，返回NULL

sql

SELECT sqrt(16);

-- POW(x,y)，POWER(X,Y) 返回x的y次方

-- EXP(X) 返回e的X次方，其中e是一个常数，2.718281828459045

sql

SELECT pow(16,2),POWER(16,2),exp(3);

-- BIN(x) 返回x的二进制编码
-- HEX(x) 返回x的十六进制编码
-- OCT(x) 返回x的八进制编码
-- CONV(x,f1,f2) 返回f1进制数变成f2进制数

sql

select bin(20),hex(20),oct(20),conv(20,8,2);

-- 案例: 通过数据库的函数，生成一个六位数的随机验证码。

sql

select lpad(round(rand()*1000000 , 0), 6, '0');

-- --------------------------------------------------------------------------------------------------------

-- 字符转数值函数

-- 1、cast()函数
-- cast函数用于将值从一种数据类型转换为表达式中指定的另一种数据类型
-- 语法：cast(值 as 数据类型)
-- 在使用cast函数时，可用的数据类型包括：
-- date：日期类型 日期格式"YYYY-MM-DD"
-- time：时间类型 日期格式 "HH:MM:SS" 
-- datetime：日期时间类型 日期格式 "YYYY-MM-DD HH:MM:SS"
-- signed：有符号的int类型（有符号指的是正数负数）
-- unsigned:无符号的int类型
-- CHAR[(N)]:定长字符串类型,可选 N 是字符串的长度
-- DECIMAL(m,n):浮点型 m是数值位数，n是小数位数

sql

SELECT cast(100 as CHAR(5));
SELECT cast('-0012' as signed);
SELECT cast('-0012' as unsigned);
SELECT cast("3.56789" as decimal(5,2));
SELECT cast("2025-10-12" as datetime);

2、convert()函数
-- 格式：CONVERT(值,新数据类型)
-- 数据类型和上面相同

sql

SELECT convert('3.56789',DECIMAL(5,2));
SELECT convert('3.56789',signed);
select CONVERT('2025-10-12',datetime);

3、隐式转换

sql

select "0012"+1

-- 日期函数
-- curdate()  当前日期

sql

select curdate();
select year(curdate());

根据出生日期计算年龄

sql

select year(curdate())- year(sbirth) as 年龄,curdate(),sbirth from student;

-- curtime()  当前时间

sql

select curtime();

-- now()  当前日期和时间

sql

select now();

-- YEAR , MONTH , DAY  年、月、日

sql

select YEAR(now());
select YEAR(CURDATE());
select MONTH(now());
select DAY(now());

-- 找出10月1日过生的学生信息

sql

SELECT * from student where MONTH(sbirth)=1 and day(sbirth)=1;

-- date_add  增加指定的时间间隔
-- DATE_ADD(日期值, INTERVAL 间隔值 SECOND|MINUTE|HOUR|DAY|WEEK|MONT|QUARTER|YEAR)
-- 返回一个日期/时间值加上一个时间间隔expr后的时间值

sql

select date_add('2026-06-07', INTERVAL -100 );

-- datediff  获取两个日期相差的天数,前面日期 - 后面日期
-- DATEDIFF(date1,date2) 返回起始时间date1 和 结束时间date2之间的天数

-- 计算同学们入学天数

sql

select datediff('2023-8-20',curdate());

/*
-- DATE_FORMAT 用于将日期或日期时间值格式化为指定的字符串格式。
-- DATE_FORMAT(date, 'format') 
-- date：一个合法的日期或日期时间表达式。
-- format：一个字符串，指定了日期或日期时间的格式。
以下是一些常用的格式说明符：

%Y：四位数的年份（例如，2024）  常用
%y：两位数的年份（例如，24）    常用
%m：两位数的月份（01 到 12）    常用
%c：月份（1 到 12）
%d：两位数的日（01 到 31）      常用
%e：日（1 到 31）
%H：两位数的小时（00 到 23）
%k：小时（0 到 23）
%i：两位数的分钟（00 到 59）
%s：两位数的秒（00 到 59）
%p：AM 或 PM
%r：时间，12 小时（hh:mm:ss AM 或 PM）
%T：时间，24 小时（hh:mm:ss）
%f：微秒
%W：星期的全名（例如，Sunday）
%a：星期的缩写（例如，Sun）
%b：月份的缩写（例如，Jan）
%M：月份的全名（例如，January）
%D：带有前导零的天数（0th, 1st, 2nd, 3rd, ...）
%j：一年中的天数（001 到 366）
*/
#将出生日期转换为字符串的形式

sql

SELECT DATE_FORMAT(Sbirth,'%Y%m%d') from student;

-- 案例: 查询所有老师的入职天数，并根据入职天数倒序排序。

sql

SELECT DATEDIFF(CURDATE(),entrydate) ed from teacher ORDER BY ed desc;
select tname, datediff(curdate(), entrydate) as 'entrydays' from teacher order by entrydays desc;

-- 根据学生的出生日期计算学生的年龄

sql

select sname,year(now())-year(sbirth) as 年龄 from student;  -- 只是年减年,不准确

/*
TIMESTAMPDIFF 函数在 MySQL 中用于计算两个日期或日期时间值之间的差异，并返回以指定单位表示的结果。这个函数非常有用于需要计算两个时间点之间相隔的天数、小时数、分钟数等场景。
TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2) 
unit：一个表示时间差的单位的字符串。常用的单位有 SECOND（秒）、MINUTE（分钟）、HOUR（小时）、DAY（天）、WEEK（周）、MONTH（月）、QUARTER（季度）和 YEAR（年）。
*/
select Sname,TIMESTAMPDIFF(year, sbirth, CURDATE()) AS age from student; -- 准确计算年龄

sql

UPDATE student set sage=TIMESTAMPDIFF(YEAR, sbirth, CURDATE());

-- STR_TO_DATE 将字符串转换成日期格式
/*
STR_TO_DATE(date_string, format_mask) 
date_string：要转换的日期字符串。
format_mask：一个字符串，指定了 date_string 的格式。这个格式应该与 date_string 的实际格式相匹配。
format_mask 可以包含与 DATE_FORMAT 函数相同的格式说明符，用于指定日期字符串的格式。例如：

%Y：四位数的年份
%m：两位数的月份
%d：两位数的日
%H：两位数的小时（24小时制）
%i：两位数的分钟
%s：两位数的秒

*/


-- 实例：从身份证号中提取出生日期

sql

SELECT 
    SUBSTRING(idnum,7,8) as 日期字符串 , -- 提取 出生日期 字符串
    STR_TO_DATE(SUBSTRING(idnum,7,8),'%Y%m%d') AS 提取的日期, -- 将 字符串 转换为 日期类型
	 sbirth
FROM
    student;

/*
时间戳(timestamp)，通常是一个字符序列，唯一地标识某一刻的时间。数字时间戳技术是数字签名技术一种变种的应用。
广泛应用的是Unix时间戳(Unix timestamp)：或称Unix时间(Unix time)、POSIX时间(POSIX time)，是一种时间表示方式，定义为从格林威治时间1970年01月01日00时00分00秒起至现在的总秒数。Unix时间戳不仅被使用在Unix 系统、类Unix系统中，也在许多其他操作系统中被广泛采用。   
*/
-- UNIX_TIMESTAMP() 以UNIX时间戳的形式返回当前时间。

sql

SELECT UNIX_TIMESTAMP();

-- UNIX_TIMESTAMP(date) 将时间date以UNIX时间戳的形式返回。

sql

SELECT UNIX_TIMESTAMP("2025-1-1");

-- FROM_UNIXTIME(timestamp) 将UNIX时间戳的时间转换为普通格式的时间

sql

SELECT FROM_UNIXTIME(1735660800);

-- 流程控制函数
-- if

sql

select if(FALSE, 'Ok', 'Error');

-- ifnull

sql

select ifnull('Ok','Default');
select ifnull('','Default');
select ifnull(null,'Default');

-- case when then else end
-- 需求: 查询student表中的学生姓名和籍贯 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)

sql

select     sname,sbirthplace,
    ( case sbirthplace 
		when '北京' then '一线城市' 
		when '上海' then '一线城市' 
		else '二线城市' end ) as '籍贯'
from student;

-- 案例: 统计班级各个学生的成绩，展示的规则如下：
-- >= 85，展示优秀
-- >= 60，展示及格
-- 否则，展示不及格

sql

select
    sno,score,
    (case when score >= 85 then '优秀' 
		when score>=75 then '良好'
		when score >=60 then '及格' 
		else '不及格' end ) '评语'
from sc;

-- 实例：根据出生日期，生成身份证号
/*
身份证号的18位数分别代表以下含义：‌1

‌第1、2位‌：代表所在省份的代码。
‌第3、4位‌：代表所在城市的代码（地级市、盟、自治州）。
‌第5、6位‌：代表所在区县的代码（县、县级市、区）。
‌第7-14位‌：代表出生年月日，其中7-10位是年份，11-12位是月份，13-14位是日期。
‌第15、16位‌：代表所在地派出所的代码。
‌第17位‌：代表性别，奇数为男性，偶数为女性。
‌第18位‌：是校验码，由号码编制单位按统一的公式计算出来。校验码可以是0-9的数字，也可以是X（代表10）。如果用10做尾号，身份证号码就变成了19位，为保持身份证号为18位标准，用X来代替10。
*/

sql

SELECT ssex,Sbirth,
    CONCAT
		(
        Rpad(round(rand()*1000000 , 0), 6, '0'), -- 假设的地址码 前6位
        DATE_FORMAT(sbirth, '%Y%m%d'), -- 7-14位是出生日期 
        LPAD(FLOOR(RAND() * 100), 2, '0'), -- 假设的顺序码
        (case when ssex = '男' then FLOOR(RAND()*4+1)*2-1 -- -- 男性为奇数
		when ssex= '女' then FLOOR(RAND()*5)*2 -- 女性为偶数
		end ), 
		(case when round(RAND()*10,0)=10 then 'X' -- 随机生成最后一位，如果是10则为X
	  else FLOOR(RAND()*10) end)	-- round()有可能生成10,floor不会生成10	
		) AS 身份证号
FROM   student;
UPDATE student SET
idnum=CONCAT
		(
        lpad(round(rand()*1000000 , 0), 6, '0'), -- 假设的地址码 前6位
        DATE_FORMAT(sbirth, '%Y%m%d'), -- 7-14位是出生日期 
        LPAD(FLOOR(RAND() * 100), 2, '0'), -- 假设的顺序码
        (case when ssex = '男' then FLOOR(RAND()*4+1)*2-1 -- -- 男性为奇数
		when ssex= '女' then FLOOR(RAND()*5)*2 -- 女性为偶数
		end ), 
		(case when round(RAND()*10,0)=10 then 'X' -- 随机生成最后一位，如果是10则为X
	  else FLOOR(RAND()*10) end)	-- round()有可能生成10,floor不会生成10	
		) ;
SELECT FLOOR(RAND() * 5) * 2 AS random_even_number;#得到一个偶数
SELECT FLOOR(RAND() * 4+1) * 2 - 1  AS random_even_number; #避免出现rand()出现0，然后整个表达式结果为-1

←

上一节 6. 排序与分页

下一节 8. 多表查询（连接）

→