MySQL常用函数整理

2024-07-17

本文介绍MySQL中经常用到的函数，包括数学函数、字符串函数、日期时间函数等等。

MySQL函数

1、数学函数

（1）abs(x)：返回x的绝对值

-- 返回-2的绝对值2
select abs(-2);

（2）sqrt(x):返回x的平方根

-- 返回16的平方根4
select sqrt(16);
-- -16没有平方根，返回null
select sqrt(-16);

（3）round(x[,d])：返回x的四舍五入值，保留d位小数

d的默认值为0.

-- 查询emp表中收入最高的员工工资是平均工资的多少倍,保留两位小数
select round(max(sal)/avg(sal),2) 倍数 from emp;

常用数学函数整理如下：

函数名	描述
abs(x)	返回x的绝对值
ceiling(x)	返回大于或等于x的最小整数(进一取整)
exp(x)	返回e的x次方
floor(x)	返回小于或等于x的最大整数(去掉小数取整)
greatest(exprl, expr2, expr3,…)	返回列表中的最大值
least(exprl, expr2, expr3, …)	返回列表中的最小值
log(base, x)	返回以 base 为底的对数
mod(x,y)	返回x除以y以后的余数
pi()	返回圆周率
pow(x,y)	返回x的y次方
rand()	返回0~1的随机数
round(x[,d])	返回x的四舍五入值,保留d位小数(d的默认值为0)
sign(x)	返回x的符号值，x是负数、0、正数时分别返回-1、0和1
sqrt(x)	返回x的平方根
truncate(x,y)	返回数值x保留到小数点后y位的值(与round函数最大的区别是不会进行四舍五入)

2、字符串函数

（1）concat(s1,s2,…):字符串拼接

-- 将3个字符串合并为一个长字符串，返回'CDA数据分析'
select concat('CDA','数据','分析');
-- 参数中包含空值，返回null
select concat('CDA',null,'分析');
-- 参数中包含空白值不影响结果，返回'CDA分析'
select concat('CDA','','分析');

（2）substring(s,start[,length]):字符串截取

-- 从字符串第四位开始，截取长度为2个字符的字符串，返回'数据'
select substring('CDA数据分析',4,2)；
-- 从字符串第4位开始，截取到字符串结束，返回'数据分析'
select substring('CDA数据分析',4);

（3）replace(s,s1,s2):将字符串s中的s1用s2替换

-- 将emp表中job字段中的manager替换为leader
select job,replace(job,'manager','leader') from emp;

常用字符串函数整理如下：

函数名	描述
ascii(s)	返回字符串s的第一个字符的ASCII码
bin(n)	返回值为n的二进制的字符串表示
bit_length(s)	返回值为二进制的字符串s的长度
concat(s1,s2,…)	把字符串s1、s2等多个字符串合并为一个长字符串
concat_ws(x, s1,s2,,..)	同concat(s1,s2,…) 函数，但是每个字符串之间用x间隔，x可以是分隔符
field(s,s1,s2,…)	返回第一个字符串s在字符串列表(s1,s2,…) 中的位置
find_in_set(s1,s2)	返回在字符串s2中与s1匹配的字符串的位置
insert(str,x,y,instr)	将字符串str从第x位置开始，y个字符长的子串替换为字符串instr,返回结果
instr(str,substr)	返回子字符串substr在文本字符串str中第一次出现的位置
locate(s1,s)	从字符串s中获取s1的开始位置
left(s,n)	返回字符串s的前n个字符
length(s)	返回字符串s的字符长度
lower(s)/lcase(s)	将字符串s的所有字母变成小写字母
ltrim(s)	去掉字符串s开头的空格
mid(s,n,len)	从字符串s的第n位置截取长度为len的子字符串，同substring(s,n,len)
position(s1,s)	返回字符串 s1 在字符串s中第一次出现的位置
quote(s)	用反斜线转义s中的单引号
repeat(s,n)	将字符串s重复n次
replace(s,s1,s2)	将字符串s2替代字符串s中的字符串s1
reverse(s)	将字符串 s的顺序反过来
right(s,n)	返回字符串s的后n个字符
rtrim(s)	去掉字符串s结尾的空格
stremp(s1,s2)	比较字符串s1和s2，若s1与s2相等，则返回0;若s1>s2，则返回1;若 s1<s2，则返回-1
substring(s, start[, length])	从字符串 s 的 start位置截取长度为length的子字符串
trim(s)	去掉字符串s开始和结尾处的空格
upper(s)/ ucase(s)	将字符串s的所有字母变成大写字母

3、日期时间函数

（1）year(date)、month(date)、day(date)：返回指定日期的年、月、日。

--获取日期中的年月日(日期时间、字符串或者数值均可被识别)
select year('2020-01-30') 年份,month('20200130') 月份， day(20200130) 日;

（2）date_format(date,format)：根据format字符串格式化date值。

-- 按照日月年的格式返回日期
select date_format('20-01-30 12:00:00','%d-%m-%y') 日月年;

（3）date_add(date,interval num type)：对日期时间进行加法运算，date为datetime或date型的起始时间，interval为固定关键词，num为需要增加的时间间隔，type 用来指明 num的对应单位。

-- 计算 2020年1月1日2个月后的日期
select date_add("2020-01-01",interval 2 month);

（4）datediff(d1,d2)：计算结束日d1和起始日d2之间间隔的天数。curdate():返回当前日期。

-- 计算员工的工龄(雇佣日期与当前日期的差)
select ename,floor(datediff(curdate(),hiredate)/365) as 工龄 from emp;

常用日期时间函数整理如下：

函数名	描述
adddate(date,interval num type)/date_add(date,interval num type)	计算起始日期 date加上长度为 num、单位为type的时间长度的日期
curdate()/current_date()	返回当前日期
curtime()/current_time()	返回当前时间
current_timestamp()/localtimestamp()	返回当前日期和时间
date()	从日期、日期时间表达式中提取日期值，或将字符串型数据转为日期型数据
datediff(d1,d2)	计算结束日d1和起始日d2之间间隔的天数
date_format(date,format)	根据format字符串格式化date的值
date_sub(date,interval num type)/subdate(date,interval num type)	计算起始日期date减去长度为num、单位为type的时间长度的日期
day(date)	返回日期date的日期部分
dayname(date)	返回日期date的星期名
dayofmonth(date)	计算日期date是本月的第几天(1-31)
dayofweek(date)	返回日期date所代表的一星期中的第几天(1-7)，1代表星期日，2代表星期一，以此类推
dayofyear(date)	计算日期date是本年的第几天
from_days(n)	计算从0000年1月1日开始n天后的日期
from unixtime(ts,f)	根据指定的f格式，格式化UNIX时间戳ts
hour(t)	返回t中的小时值(0-23)
last_day(date)	返回给定日期的那一月份的最后一天
maketime(hour, minute, second)	3个参数分别为小时、分钟、秒，将其组合为完整时间
minute(t)	返回(中的分钟值(0-59)
monthname(date)	返回日期 date中的月份名称
month(date)	返回日期date中的月份值(1-12)返回当前日期和时间
quarter(date)	返回日期date是第几季节(1-4)
second(t)	返回t中的秒钟值(0-59)
sec_to_ time(s)	将以秒为单位的时间s转换为时分秒的格式
str_to_date(string,format_mask)	将字符串转换为日期
time()	提取传入参数的时间值
time_format(t,f)	根据指定的f格式，格式化时间t
time_to_sec(t)	将时间t转换为秒
timediff(t1, t2)	计算t1和t2的时间差值
to_days(date)	计算日期date距离0000年1月1日的天数
unix_timestamp([date])	返回一个UNIX 时间戳(从1970-01-01 00:00:00开始的秒数，date默认值为当前时间)
weck(date)/ weekofyear(date)	计算日期 date是本年的第几周(0-53)
weekday(date)	计算日期 date 是星期几，0表示星期一，1表示星期二
year(date)	返回日期date的年份(1000-9999)

4、分组合并函数

-- 查询各部门员工姓名，默认逗号分割
select deptno, group_concat(ename order by ename separator '/') 员工姓名列表 from emp group by deptno;

5、逻辑函数

（1）空值函数

-- 查询每位员工的实发工资（基本工资+奖金）
select ename,sal+ifnull(comm,0) 实发工资 from emp;

（2）if函数

-- 查询员工的工资级别，3000及以上为高，1500及一下为地，其余为中
select ename,sal,if(sal>=3000,'高',if(sal<=1500,'低','中')) 工资级别 from emp;

（3）case逻辑表达式

-- 查询员工的工资级别，3000及以上为高，1500及一下为地，其余为中
select ename,sal,
    case when sal>=3000 then '高'
    when sal<=1500 then '低'
    else '中' end 工资级别 from emp;

6、开窗函数

对比单条记录与该记录所处分组之间的差别。

（1）over函数

-- 查询每位员工与公司所有员工的平均工资之间的情况
select *,avg(sal) over() 平均工资 from emp;

（2）partition by子句

-- 查询每位员工与所属部门平均工资之间的情况
select *,avg(sal) over(partition by deptno) 平均工资 from emp;

（3）order by子句

-- 按入职日期查询各部门的累计工资
select *,sum(sal) over(partition bydeptno order by hiredate) 累计工资 from emp;

（4）序号函数

-- 查询各部门员工的工资排名
select *,
row_number() over(order by sal desc) 排名1，
dense_rank() over(order by sal desc) 排名2,
rank() over(order by sal desc) 排名3
from emp;

MySQL中常用的开窗函数：

函数名	描述
cume_dist()	计算一组值中一个值的累计分布
dense_rank()	根据 order by 子句为分区中的每一行分配一个等级。它将相同的等级分配给具有相等值的行。如果两行或更多行具有相同的排名,排名值序列中将没有间隙
first_value()	返回相对于窗口框架第一行的指定表达式的值
lag()	返回分区中当前行之前的第N行的值。如果不存在前一行，则返回null
last_value()	返回相对于窗口框架最后一行的指定表达式的值
lead()	返回分区中当前行之后的第N行的值。如果不存在后续行，则返回null
nth_value()	从窗口框架的第N行返回参数的值
ntile()	将每个窗口分区的行分配到指定数量的排名组中
percent_rank()	计算分区或结果集中行的百分数等级
rank()	与dense_rank()函数相似，不同之处在于当两行或更多行具有相同的等级时，等级值序列中存在间隙
row_number()	为分区中的每一行分配一个顺序整数