MySQL 常用函数与经典转换

整理数值、字符串、日期、条件函数和行列转换案例。

一、MYSQL常用内置函数

1.数值函数

1.1 小数相关(5)

round ——四舍五入,取整，只看小数点后一位

select round(12.8);#13
select round(12.45)#12

format——格式化，保留几位小数（四舍五入）

select format(12.46,1)#12.5
select format(12.54,1)#12.5

truncate——截取数据，直接截取指定位数

select truncate(12.46,1)#12.4
select truncate(12.54,1)#12.5

ceil—— 向上取整

select ceil(12.4)#13
select ceil(-12.4)#12

floor——向下取整

select floor(12.4)#12
select floor(-12.4)#13

1.2整数相关

mod—— 取余数

select mod(12,4)#0
select mod (13,4)#1

pow——求幂

select pow(2,3)#8
select pow(2,10)#1024

rand——随机数

select rand() #取0-1的随机数，每次执行结果不同
select rand（1） #只取一次随机数

2.字符串函数

2.1大小写转换

select upper('ax') #AX 转化为大写
select lower('AX'))#ax 转化为小写

2.2反转、替换、重复、连接

#反转
select reverse('abc')#cba

#替换
select replace('内容'，'被替换的部分','替换成’）
select replace（'12345','23','45')# 14545

#重复
 select repeat('内容','重复次数')
 select repeat('12',5)#1212121212
               
#连接
 select concat('内容'，'内容')；
 select concat('12','34')#1234
 
 select concat_ws('连接符','内容'，’内容‘) 
 select concat_ws('+','16','8')#16+8

2.3字符串截取

#substring——从第几位开始截取
select substring('123456',3)#3456

#left——从左边截取几位
select left('123456',3)#123

#right——从右边截取几位
select right('123456',3)#456

2.4读取字符串长度

select length ('1234') #4

3.时间日期函数

3.1当前时间

#now
select now();#当前时间 年月日 时分秒
#current date
select current_date ();#当前年月日
#current_time ();#当前时分秒

3.2计算时间差函数

#计算两个时间的差
select datediff（时间1，时间2）#单位 day
select dateiff(2025-6-1,2024-6-1)#365

#后推时间
select date_add（时间，interval后推时间）
select date_add(2025-6-1,interval 25day) #2026-6-26

#前滚时间
select date_sub(时间，interval 前滚时间)
select date_sub(2026-6-1,interval 30day) #2026-5-1

3.3时间转换

3.3.1 时间与字符串转换

# 时间转化为字符串
#%Y-2026; %M-MAY; %D-15th; %y-26;%m-05;%d-15;%h %i %s
select date_format(2026-5-15,'%Y %M %D')

#将字符串转化为时间
select str_to_date('2026 05 15',%y %m %d) #后面的符合和前面的字段要对应上

3.3.2 时间和时间戳的转换

# 时间转换为时间戳
select unix_teamstemp();

#将时间戳转化为时间
select form_unixtime();

4.条件判断

4.1 双分支条件 if

select if(表达式，表达式为真输出的值，表达式为假输出的值)；

4.2 多分支条件

select case 
       when 表达式1 then 值1
       when 表达式2 then 值2
       when 表达式3 then 值3
       else 值4
 end

二、经典例题

1.多行转多列

#方法一 用if语句将不需要的数据变为0
select '学号' ，max（if('科目'='语文'，成绩，0)） 
               max（if('科目'='数学'，成绩，0)）
               max（if('科目'='英语'，成绩，0)） 
from score group by '学号'；

#方法二；直接拼表，然后筛选
select s1.xuehao,s1.chengji,s2.chengji,s3.chengji from day06_db.score s1
join day06_db.score s2 on s1.xuehao=s2.xuehao
join day06_db.score s3 on s1.xuehao=s3.xuehao
where s1.kemu='语文' and s2.kemu='数学'and s3.kemu='英语';

2.多列转多行

#先凑数据 再拼接表 再排序
select xuehao,'语文' as '科目',yuwen '成绩' from area.score_h
union
select xuehao,'数学' as '科目',shuxue '成绩' from area.score_h
union
select xuehao,'英语' as '科目', yingyu '成绩' from area.score_h
order by xuehao;