【MySQL数据库】MySQL聚合函数、时间函数、日期函数、窗口函数等函数的使用

小小程序员 • 2023-01-02 • 云技术社区 • 293 阅读

首尾函数first_value() last_value()

云数据库 https://cloud.tencent.com/product/cdb?from=10680

前言

    MySQL数据库中提供了很丰富的函数，比如我们常用的聚合函数，日期及字符串处理函数等。SELECT语句及其条件表达式都可以使用这些函数，函数可以帮助用户更加方便的处理表中的数据，使MySQL数据库的功能更加强大。本篇文章主要为大家介绍几类常用函数的用法。本期我们将介绍MySQL函数，帮助你更好使用MySQL。

MySQL函数

聚合函数

在MySQL中，聚合函数主要由：count,sum,min,max,avg,这些聚合函数我们之前都学过，不再重复。这里我们学习另外一个函数:group_concat(），该函数用户实现行的合并。

group_concat()函数首先根据group by指定的列进行分组，并且用分隔符分隔，将同一个分组中的值连接起来，返回一个字符串结果。 说明:

使用distinct可以排除重复值；
如果需要对结果中的值进行排序，可以使用orderby子句；　　
separator是一个字符串值，默认为逗号。例子 --将所有员工的*名字合并成一行

--格式
group_concat([distinct]字段名 [orderby 排序字段asc/desc] [separator'分隔符']) 

--默认分隔符合并
select group_concat(emp_name) from emp; 

--指定分隔符合并
select department,group_concat(emp_name separator ';' )
from emp group by department;

数学函数

数学函数主要用来\\*\\*处理数值数据\\*\\*，主要的数学函数有：绝对值函数、三角函数(包括正弦函数、余弦函数、正切函数、余切函数等)、对数函数、随机数函数等。\\*\\*在有错误产生时，数学函数将会返回空值NULL。\\*\\*

字符串函数

MySQL字符串常用函数有：

LOWER，将字符串参数值转换为全小写字母后返回
UPPER，将字符串参数值转换为全大写字母后返回；
CONCAT，将多个字符串参数首尾相连后返回；
SUBSTR，从源字符串str中指定位置pos开始取。

下面列举了MySQL较为全面的字符串函数，大家可以收藏起来，需要时再看即可。

编辑

日期函数

    日期和时间函数主要用来**处理日期和时间值**，一般的日期函数除了使用**DATE类型**的参数外，也可以使用**DATESTAMP类型**或者**TIMESTAMP类型**的参数，但是会忽略这些值的时间部分。相同的，以TIME类型值为参数的函数，可以接受TIMESTAMP类型的参数，但是会忽略日期部分。许多日期函数可以同时接收数和字符串这两种参数。

下面列举了MySQL较为全面的字符串函数，大家可以收藏起来，需要时再看即可。

控制流函数

1.控制流函数也称作“条件判断函数”，其根据满足的条件不同，执行相应的流程。
2.MySQL 中常见的控制流函数有 IF、IFNULL、case When、case test when（这里主要是case when）。

CASE expression
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
   ...
    WHEN conditionN THEN resultN
    ELSE result
END

/*
解释：
CASE 表示函数开始，END 表示函数结束。
如果 condition1 成立，则返回 result1, 
如果 condition2 成立，则返回 result2，
当全部不成立则返回 result，而当有一个成立之后，
后面的就不执行了。
*/

--例1
select case 100 
           when 50 then 'tom' 
           when 100 then 'mary'else 'tim'
end ;


--例2
select case 
           when 1=2 then 'tom'
           when 2=2 then 'mary' 
           else'tim'
end ;

窗口函数

window_function (expr ) OVER ( PARTITION BY ... ORDER BY ... frame_clause )

window_function是窗口函数的名称；
expr是参数，有些函数不需要参数；
OVER子句包含三个选项： 分区（PARTITION BY） PARTITION BY选项用于将数据行拆分成多个分区（组），它的作用类似于GROUPBY分组。如果省略了 PARTITION BY，所有的数据作为一个组进行计算排序（ORDER BY）

序号函数

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

OVER 子句中的ORDERBY选项用于指定分区内的排序方式，与 ORDER BY 子句的作用类似以及窗口大小（frame_clause）。 frame_clause选项用于在当前分区内指定一个计算窗口，也就是一个与当前行相关的数据子集。

开窗聚合函数- SUM,AVG,MIN,MAX

sum(salary) over(partition by dname order by hiredate) as pv1

前后函数 lag lead

用途：返回位于当前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值

lag(hiredate,1,'2000-01-01') over
(partition by dname order by hiredate) as last_1_time, 

lag(hiredate,2) over(partition by dname order by hiredate) 
as last_2_time

lead(hiredate,1,'2000-01-01') over
(partition by dname order by hiredate) aslast_1_time,

lead(hiredate,2) over(partition by dname order by hiredate)
as last_2_time