MySQL的函数

MySQL的函数

概述：

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

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

1. 聚合函数
2. 数学函数
3. 字符串函数
4. 日期函数
5. 控制流函数
6. 窗口函数

聚合函数——group_concat()

概述：

• 在MySQL中，聚合函数主要有：count，sum，min，max，avg，这些我以前说过，就不说了
• 我说另外一个函数:group_concat(），该函数用于实现行的合并
• group_concat()函数首先根据group by指定的列进行分组，并且用分隔符分隔，将同一个分组中的值连接起来，返回一个字符串结果
• 如果不分组，查询结果将展示在一行上

格式：

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

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

我们准备一些数据便于接下来的操作

create table emp(
    emp_id int primary key auto_increment comment '编号',
    emp_name char(20) not null default '' comment '姓名',
    salary decimal(10,2) not null default 0 comment '工资',
    department char(20) not null default '' comment '部门'
);
 
insert into emp(emp_name,salary,department) 
values('张晶晶',5000,'财务部'),('王飞飞',5800,'财务部'),('赵刚',6200,'财务部'),('刘小贝',5700,'人事部'),
('王大鹏',6700,'人事部'),('张小斐',5200,'人事部'),('刘云云',7500,'销售部'),('刘云鹏',7200,'销售部');

这里说一个小的知识点：

• 上述的COMMENT 是备注、注释的意思，写上COMMENT ‘注释信息后’ 之后，在建表信息里可以看到添加的备注信息
• COMMENT ‘注释信息’ 删除不会影响数据操作作只是没有字段注释说明，通常字段或列名都需要加注释，以方便自己和其他同事阅读数据库表字段信息。

操作：

• 目标：指定排序方式和分隔符

SELECT 
department,GROUP_CONCAT(emp_name ORDER BY salary DESC SEPARATOR ';')
FROM emp
GROUP BY department;

查询结果为

数学函数

字符串函数

日期函数

下方提到的相关日期格式类型可看 程序小达人 博主的该篇博客 链接: MySql时间日期函数及格式化详解

DATE_ADD(d，INTERVAL expr type) 中type 值可以是：

• MICROSECOND ——>微秒
• SECOND ——>秒
• MINUTE ——>分
• HOUR ——>时
• DAY ——>天
• WEEK ——>周
• MONTH ——>月
• QUARTER ——>季度
• YEAR ——>年
• DAY_SECOUND ——>在当前时间上加秒数
• DAY_MINUTE ——>在当前时间上加分数
• DAY_HOUR ——>在当前时间上加小时数
• YEAR_MONTH ——>在当前时间上加月数
• SECOND_MICROSECOND
• MINUTE_MICROSECOND
• MINUTE_SECOND
• HOUR_MICROSECOND
• HOUR_SECOND
• HOUR_MINUTE
• DAY_MICROSECOND
• DAY_SECOND
• DAY_MINUTE
• DAY_HOUR
• YEAR_MONTH

控制流函数

if逻辑判断语句

case when语句

格式：

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

解释：

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

操作

SELECT CASE 2+1
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
WHEN 5 THEN 5
ELSE 6
END AS result; -- 3

SELECT CASE 
WHEN 2+1=1 THEN 1
WHEN 2+1=2 THEN 2
WHEN 2+1=3 THEN 3
WHEN 2+1=4 THEN 4
WHEN 2+1=5 THEN 5
ELSE 6
END AS result; -- 3

熟练掌握case when 语句

准备数据

create table orders(
 oid int primary key, -- 订单id
 price double, -- 订单价格
 payType int -- 支付类型(1:微信支付 2:支付宝支付 3:银行卡支付 4：其他)
);
 
insert into orders values(1,1200,1);
insert into orders values(2,1000,2);
insert into orders values(3,200,3);
insert into orders values(4,3000,1);
insert into orders values(5,1500,2);

要求：查询orders表的信息，包含patType对应的支付类型

SELECT *,CASE payType
WHEN 1 THEN '微信支付'
WHEN 2 THEN '支付宝支付'
WHEN 3 THEN '银行卡支付'
ELSE '其他'
END AS readyPayType
FROM orders;

查询结果

窗口函数

概述：

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

聚合函数与窗口函数的区别可以通过模型图简单了解

二者模型图

窗口函数的分类

另外还有开窗聚合函数: SUM，AVG，MIN，MAX

基本语法

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

其中，window_function 是窗口函数的名称；expr 是参数，有些函数不需要参数；OVER子句包含三个选项：

• 分区（PARTITION BY） PARTITION BY选项用于将数据行拆分成多个分区（组），它的作用类似于GROUP BY分组。如果省略了 PARTITION BY，所有的数据作为一个组进行计算
• 排序（ORDER BY） OVER 子句中的ORDER BY选项用于指定分区内的排序方式，与 ORDER BY 子句的作用类似
• 以及窗口大小（frame_clause） frame_clause选项用于在当前分区内指定一个计算窗口，也就是一个与当前行相关的数据子集

窗口大小（frame_clause）

框架的计数单位分为两种：ROWS 和 RANGE，默认值为RANGE

• ROWS：以一行为一个单位
• RANGE(有排序)：以连续相同的值为一个单位
• RANGE(无排序)：以分区为单位

框架范围(frame_extent)

• CURRENT ROW ——>当前行
• UNBOUNDED PRECEDING ——>当前行上侧所有行
• UNBOUNDED FOLLOWING ——>当前行下侧所有行
• expr PRECEDING——>当前行上侧expr行(expr可以是数字，也可以是表达式)
• expr FOLLOWING——>当前行下侧expr行(expr可以是数字，也可以是表达式)

框架范围可由两种形式来定义：frame_start 和 frame_between，范围的定义基于基本单位

• frame_start：仅指定开始行(或区域)，则结束范围为默认值，即当前行(或区域)
• frame_between：指定开始行(或区域)与结束行(或区域)

序号函数

序号函数有三个：ROW_NUMBER()、RANK()、DENSE_RANK()，可以用来实现分组排序，并添加序号

格式：

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

数据准备

create table employee( 
   dname varchar(20), -- 部门名 
   eid varchar(20), 
   ename varchar(20), 
   hiredate date, -- 入职日期 
   salary double -- 薪资
); 
-- 添加数据
insert into employee values('研发部','1001','刘备','2021-11-01',3000);
insert into employee values('研发部','1002','关羽','2021-11-02',5000);
insert into employee values('研发部','1003','张飞','2021-11-03',7000);
insert into employee values('研发部','1004','赵云','2021-11-04',7000);
insert into employee values('研发部','1005','马超','2021-11-05',4000);
insert into employee values('研发部','1006','黄忠','2021-11-06',4000);
insert into employee values('销售部','1007','曹操','2021-11-01',2000);
insert into employee values('销售部','1008','许褚','2021-11-02',3000);
insert into employee values('销售部','1009','典韦','2021-11-03',5000);
insert into employee values('销售部','1010','张辽','2021-11-04',6000);
insert into employee values('销售部','1011','徐晃','2021-11-05',9000);
insert into employee values('销售部','1012','曹洪','2021-11-06',6000);

操作：

• 对每个部门的员工按照薪资排序，并给出排名，显示出排名，部门，姓名和薪水

SELECT dname,ename,salary,
row_number() over(
	PARTITION BY dname 
	ORDER BY salary 
) COUNT
FROM employee;

使用row_number() 排序为：正常排序，数据相同情况下排名依旧不相同

使用rank() 排序为：数据相同情况下排名相同

使用dense_rank() 排序为：数据相同情况下排名相同，但不会出现跳数字的情况，每个数字都保证存在

• 求出每个部门薪资排在前三名的员工

SELECT dname,ename,salary
FROM
(SELECT dname,ename,salary,
rank() over(
	PARTITION BY dname 
	ORDER BY salary  DESC
) COUNT
FROM employee) t
WHERE t.count<=3;

查询结果

• 对所有员工进行全局排序（不分组），不加partition by表示全局排序

SELECT dname,ename,salary,
rank() over(
	ORDER BY salary DESC
) COUNT
FROM employee;

查询结果

开窗聚合函数

概念

• 在窗口中每条记录动态地应用聚合函数【SUM()、AVG()、MAX()、MIN()、COUNT()】，可以动态计算在指定的窗口内的各种聚合函数值。
• 如果没有order by排序语句，默认把分组内的所有数据进行sum操作

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

查询结果如下，salary的和是截止到当行数据的salary和，但如果我们排序按照的字段有重复的情况，会将重复的数据都加上。

去除order by 语句

SELECT dname,ename,salary,
SUM(salary) over(
	PARTITION BY dname
) sum_money FROM employee;

查询结果如下，将所有的数据进行相加

与窗口大小（frame_clause）相结合的查询

SELECT  
dname,
ename,
salary,
SUM(salary) 
over(PARTITION BY dname 
ORDER BY hiredate  
ROWS BETWEEN unbounded preceding AND current ROW) AS c1 
-- 表示从当前行开始到上面所有salary数据的和
FROM employee;

select  
dname,
ename,
salary,
sum(salary) 
over(partition by dname order by hiredate   
rows between 3 preceding and current row) as c1 
-- 表示从当前行开始和当前行上的3条salary数据的和
from employee;

表示当前行加上上三行和下一行的数据表示

ROWS BETWEEN 3 preceding AND 1 following

表示当前行和当前行下的所有行的数据和

rows between current row and unbounded following

分布函数

CUME_DIST

• 用途：分组内小于、等于当前rank值的行数 / 分组内总行数
• 应用场景：查询小于等于当前薪资（salary）的比例
• 没有partition语句 所有的数据位于一组

SELECT  
 dname,
 ename,
 salary,
 cume_dist() over(PARTITION BY dname ORDER BY salary) AS count
FROM employee;

查询结果如下

我们以salary 进行排序，则该数据的计算：

• 在研发部中，第一行，小于3000的只有本身，所以count=1/6=0.166666...
• 第二行，小于等于4000的有3个人，所以count=3/6=0.5
• ...

PERCENT_RANK

• 用途：每行按照公式(rank-1) / (rows-1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数
• 应用场景：不常用

SELECT dname,ename,salary,
percent_rank() over(
	PARTITION BY dname
	ORDER BY salary 

) COUNT
FROM employee

查询结果为

我们以salary 进行排序，则该数据的计算：

• 在研发部中，第一行，count=(1-1)/(6-1)=0
• 第二行,所以count=(2-1)/(6-1)=0.2
• ...

由于这个使用的是rank()方式排序，rank()排序的特点为数据相同情况下排名相同，二第三行和第二行的salary数据相同，所以序号也相同，计算结果也相同

前后函数

• 用途：返回位于当前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值
• 应用场景：查询前1名同学的成绩和当前同学成绩的差值

lag的用法

lag() 的括号中可以填字段，行数和默认值

• 字段：表示以哪个字段进行输入
• 行数：表示填入前几行的数据
• 默认值：表示前几行无数据时，该行的值为null，但我们不想让他为null时，所填充的默认值

填充hiredate的前两行数据

SELECT dname,ename,hiredate,
lag(hiredate,2) over(
	PARTITION BY dname
	ORDER BY hiredate
) TIME FROM employee

查询结果

填入默认值 2001-1-1

SELECT dname,ename,hiredate,
lag(hiredate,2,'2001-1-1') over(
	PARTITION BY dname
	ORDER BY hiredate
) TIME FROM employee

查询结果

lead() 和lag() 用法一样，只不过lead() 是向下取数，也没必要再说了

头尾函数

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

-- 截止到当前，按照日期排序查询第1个入职和最后1个入职员工的薪资
SELECT
  dname,
  ename,
  hiredate,
  salary,
  first_value(salary) over(PARTITION BY dname ORDER BY hiredate) AS FIRST,
  last_value(salary) over(PARTITION BY dname ORDER BY  hiredate) AS LAST 
FROM  employee;

查询结果

其他函数

NTH_VALUE(expr,n)

• 用途：返回窗口中第n个expr的值。expr可以是表达式，也可以是列名
• 应用场景：截止到当前薪资，显示每个员工的薪资中排名第3的薪资

-- 截止到当前薪资，显示每个员工的薪资中排名第2或者第3的薪资
SELECT 
dname,ename,salary,
nth_value(salary,3) over(PARTITION BY dname ORDER BY salary) COUNT
FROM employee

查询结果

注意：我们选择第几个数据，就从第几个数据开始有值，如过我们选取的数据与前面的有重复的，则在重复的地方也会有值。

ntile()

• 用途：将分区中的有序数据分为n个等级，记录等级数
• 应用场景：将每个部门员工按照入职日期分成3组

-- 根据入职日期将每个部门的员工分成3组
SELECT 
  dname,
  ename,
  hiredate,
  salary,
ntile(3) over(PARTITION BY dname ORDER BY  hiredate  ) AS rn 
FROM employee;

查询结果

每组6个人，分成3组，刚好为11，22，33那么分成四组情况

总会保持一种平衡状态