概述

在MySQL中，为了提高代码重用性和隐藏实现细节，MySQL提供了很多函数，函数可以理解为别人封装好的模板代码

分类

在MySQL中，函数非常多，主要可以分为以下几类：

• 聚合函数
• 数学函数
• 字符串函数
• 日期函数
• 控制流函数
• 窗口函数

聚合函数

在MySQL中聚合函数主要是：count、sum、min、max、avg…在前面的学习中都有学习过，还有一个特殊的函数，是没有学习过的 —— group_concat()，该函数用于实现行的合并，group_concat()函数首先根据 group by指定的列进行分组，并且用分隔符分隔，将同一组中的值连接起来，返回一个字符串结果
格式：

gropy_concat([distinct] 字段名 [order by 排序字段 asc/desc] [separator '分隔符']) 

说明：

• 使用distinct可以排除重复值
• 如果需要对结果中的值进行排序，可以使用order by子句
• separator是一个字符串值，默认为逗号

数学函数

函数名	描述	实例
abs(x)	返回x的绝对值	select abs(-1) – 返回1
ceil(x)	返回大于或等于x的最小整数	select ceil(1.5) – 返回2
floor(x)	返回小于或等于x的最大整数	select floor(1.5) – 返回1
greatest(expr1,expr2,expr3,…)	返回列表中的最大值	select greatest(3,12,34,8,25) – 返回34
least(expr1,expr2,expr3,…)	返回列表中的最小值	select least(3,12,34,8,25) – 返回3
max(expression)	返回expression中的最大值	select max(price) from product
min(expression)	返回expression中的最小值	select min(price) from product
mod(x,y)	返回x除以y以后的余数	select mod(5,2) – 返回1
pi()	返回圆周率	select pi() – 返回3.141593
power(x,y)	返回x的y次方	select power(2,3) – 返回8
rand()	返回0到1的随机数	select rand() – 0.93099315644334
round(x)	返回离x最近的整数（遵循四舍五入）	select round(1.23456) – 返回1
round(x,y)	返回指定位数的小数（遵循四舍五入）	select round(1.23456,3) – 返回1.235
truncate(x,y)	返回数值x保留到小数点后y位的值（与round最大的区别是不会进行四舍五入）	select truncate(1.23456,3) – 返回1.234

字符串函数

函数	描述	实例
char_length(s)	返回字符串s的字符数	select char_length(‘runoob’) – 返回6
character_length(s)	返回字符串s的字符数	select character_length(‘runoob’) – 返回6
concat(s1,s2,s3…)	将字符串s1,s2,s3…等多个字符串合并为一个字符串	select concat(‘hello’,‘world’) – 返回helloworld
concat_ws(x,s1,s2…)	同concat函数一样，但每个字符串之间会加上x，x可以是分隔符	select concat_ws(’,’,‘hello’,‘world’) – 返回hello,world
field(s,s1,s2…)	返回第一个字符串s在字符列表(s1,s2…)中的位置	select field(‘c’,‘a’,‘b’,‘c’) – 返回3
trim(x)	去除字符串s开始和结尾的空格	select trim(’ a ') – 返回a
mid(s,n,len)	从字符串s的n位置截取长度为len的子字符串，同substring(s,n,len)	select mid(‘runoob’,2,3) – 返回 uno
position(s1 in s)	从字符串s中获取s1的开始位置	select position(‘b’ in ‘abc’) – 返回2
replace(s,s1,s2)	将字符串s2替换字符串s中的字符串s1	select replace(‘abc’,‘a’,‘x’) – 返回xbc
reverse(s)	将字符串s的顺序反过来	select reverse(‘abc’) – 返回cba

…

控制流函数

函数	描述	实例
if(expr,v1,v2)	如果表达式expr成立，返回结果v1,反之返回结果v2	select if(1>0,‘正确’,‘错误’) – 返回正确
ifnull(v1,v2)	如果的值不为null，则返回v1,反之返回v2	select ifnull(null,‘helloworld’) – 返回helloworld
isnull(expression))	判断表达式是否为null	select isnull(null) – 返回1
nullif(expr1,expr2)	比较两个字符串，如果字符串expr1与expr2相等，返回null，否则返回expr1	select nullif(24,25) 返回24

case when语句

case expressionwhen condition1 then result1when condition2 then result2...when conditionN then resultNelse result
end

解释：
case表示函数开始，end表示函数结束，如果expression比较condition1成立，则返回result1，如果condition2成立，则返回result2，如果全部都不成立，则返回reslut，而当有一个expression比较condition成立，则后面的condition都不执行

窗口函数

MySQL8.0新增窗口函数，窗口函数又称开窗函数，与Oracle窗口函数类似，属于MySQL的一大特点
窗口函数相对于聚合函数来说，聚合函数是对一组数据计算后返回单个值（即分组），窗口函数一次只会处理一行数据，窗口函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数
语法结构：

window_function(expr) over(partition by ...order by ...frame_clause
)

解释：
window_function是窗口函数的名称，expr是参数，有些函数不需要参数，over子句包含三个选项：

• 分区（partition by）是用于将数据行拆分成多个分区（组），它的作用类似于group by分组，如果省略了partition by，所有的数据作为一个组进行计算
• 排序（order by）是用于指定分区内的排序方式，与order by子句的作用类似
• 窗口大小（frame_clause）是用于在当前分区内指定一个计算窗口，也就是一个与当前行相关的数据子集

#创建表employee
create table employee(dname varchar(20), -- 部门名eid varchar(20), ename varchar(20),hiredate date, -- 入职日期salary double -- 薪资
)

1 序号函数

序号函数有三个：row_number()、rank()、dense_rank()可以用来实现分组排序，并添加序号
格式：

row_number() | rank() | dense_rank() over(partition by ...order by ...
)

操作：

#对每个部门的员工按照薪资排序，并给出排名
select dname,ename,salary row_number() over (partition by dname order by salary desc) as rn from employee

注意： row_number()、rank()、dense_rank()使用起来区别在于添加的序号规则不同，其他用法均相同

2 窗口聚合函数

在窗口中每条记录动态的应用聚合函数（sum()、avg()、max()、min()、count()），可以动态计算在指定窗口内的各种聚合函数值

select dname,ename,salary,sum(salary) over(partition by dname order by hiredate) as pv1 from employee

注意： 使用窗口聚合函数时，over中加order by的话，默认显示结果为累加的效果，如果没有order by的话，默认把分组内的所有数据进行sum操作统一显示

3 分布函数

• cume_dist：分组内小于、等于当前rank值得行数/分组内总行数，常用于获取比例
• percent_rank：每行按照公式（rank-1）/（rows-1）进行计算，其中rank为rank()函数产生的序号，rows为当前窗口的记录总行数，这个函数不常用

4 前后函数

lag和lead
用途：返回位于当前行的前n行lag(exor,n)或后n行lead(expr,n)的expr的值
应用场景：查询前一名同学的成绩和当前同学成绩的差值

5 头尾函数

first_value和last_value
用途：返回第一个first_value(expr)或最后一个last_value(expr)expr的值
应用场景：截止到当前，按照日期排序查询第1个入职和最后一个入职员工的薪资