MySQL的函数以及相关案例与练习

1.MySQL的主要函数

1.1 聚合函数

group_concat()函数的使用

CREATE DATABASE bianbian4;
USE bianbian4;
CREATE TABLE emp (
	emp_id INT PRIMARY KEY auto_increment COMMENT '编号',
	emp_name VARCHAR ( 20 ) NOT NULL DEFAULT '' COMMENT '姓名',
	salary DECIMAL ( 10, 2 ) NOT NULL DEFAULT 0 COMMENT '工资',
	department VARCHAR ( 20 ) NOT NULL DEFAULT '' COMMENT '部门' 
);
INSERT INTO emp ( emp_name, salary, department )
VALUES
	( '张晶晶', 5000, '财务部' ),
	( '王飞飞', 5800, '财务部' ),
	( '赵刚', 6200, '财务部' ),
	( '刘小贝', 5700, '人事部' ),
	( '王大鹏', 6700, '人事部' ),
	( '张小裴', 5200, '人事部' ),
	( '刘云云', 7500, '销售部' ),
	( '刘云鹏', 7200, '销售部' ),
	( '刘云鹏', 7800, '销售部' );
	
-- 将所有员工的名字合并成一行
SELECT GROUP_CONCAT(emp_name) FROM emp;
-- 指定分隔符合并
SELECT GROUP_CONCAT(emp_name SEPARATOR ';') FROM emp;
-- 指定排序方式和分隔符
SELECT department,GROUP_CONCAT(emp_name ORDER BY salary desc SEPARATOR ';') FROM emp GROUP BY department;

1.2 数学函数

-- 数学函数
SELECT abs(-10);
SELECT abs(10);
SELECT abs(表达式或者字段） FROM 表;
-- ceil 向上取证;
select ceil(1.1); -- 2
select ceil(1.0); -- 1
-- 向下取整
select floor(1.1); -- 1
select floor(1.9); -- 1
-- 取最大值
select greatest(1,2); -- 2
-- 最小值
select least(1,2,3); -- 1

1.3 字符串函数

1.4 日期函数

1.5 控制流函数

1.5.1 if逻辑判断语句

判断状态时，可以使用IF函数进行判断：

1.5.2 case when函数

case when 相当于 C语言的switch()语句

1.6 窗口函数

窗口函数分类：

窗口函数语法结构：

1.6.1 序号函数

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

use bianbian4;
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),
('研发部','1002','关羽','2021-11-02',5000),
('研发部','1003','张飞','2021-11-03',7000),
('研发部','1004','赵云','2021-11-04',7000),
('研发部','1005','马超','2021-11-05',4000),
('研发部','1006','黄忠','2021-11-06',4000),

('销售部','1001','曹操','2021-11-01',2000),
('销售部','1002','许褚','2021-11-02',3000),
('销售部','1003','典韦','2021-11-03',5000),
('销售部','1004','张辽','2021-11-04',6000),
('销售部','1005','徐晃','2021-11-05',9000),
('销售部','1006','曹洪','2021-11-06',6000);
-- 对每个部门的员工按照薪资排序，并给出排名
select dname,ename,salary, 
row_number() over (partition by dname order by salary desc) as row_rank,
rank() over (partition by dname order by salary desc) as rk,
dense_rank() over (partition by dname order by salary desc) as dense_rk
from employee;

-- 求出每个部门薪资排在前三的员工，-分组求topN
select * 
from
(
select dname,ename,salary, 
dense_rank() over (partition by dname order by salary desc) as dense_rk
from employee
) as t
where t.dense_rk<=3;
-- 对所有员工进行全局排序
select dname,ename,salary, 
dense_rank() over (order by salary desc) as dense_rk
from employee

row_number()：不考虑相不相同，顺序的数字不变，1，2，3
rank()：考虑相同的情况，跳数字
dense_rank()：考虑相同情况， 不跳数字
如下图：

1.6.2 开窗聚合函数

-- 按照分组日期累加
select 
 dname,
 ename,
 hiredate,
 salary, 
 sum(salary) over (partition by dname order by hiredate ) as c1
from employee

-- 如果没有order by 将分组内的数据全部相加
select 
 dname,
 ename,
 hiredate,
 salary, 
 sum(salary) over (partition by dname  ) as c1
from employee

实现区间统计，以sum为例子，数据是上面创建的数据：

-- 指定范围进行相加
-- 起始行： unbounded preceding 到当前的行 current row
select 
 dname,
 ename,
 hiredate,
 salary, 
 sum(salary) over (partition by dname order by hiredate rows between unbounded preceding and current row) as c1
from employee

-- 当前行的上面三行
select 
 dname,
 ename,
 hiredate,
 salary, 
 sum(salary) over (partition by dname order by hiredate rows between 3 preceding and current row) as c1
from employee

-- 向上三行+当前行+下面一行
select 
 dname,
 ename,
 hiredate,
 salary, 
 sum(salary) over (partition by dname order by hiredate rows between 3 preceding and 1 following) as c1
from employee

-- 当前行到最后
select 
 dname,
 ename,
 hiredate,
 salary, 
 sum(salary) over (partition by dname order by hiredate rows between current row and unbounded following) as c1
from employee

1.6.3 分布函数

cume_dist()的介绍：
用途：分组内小于、等于当前rank值的行数/分组内总行数
应用场景：查询小于等于当前薪资的比列

cume_dist()的实例：

use bianbian4;
select
 dname,
 ename,
 salary,
 cume_dist() over(order by salary) as rn1,
 cume_dist() over(partition by dname order by salary) as rn2
from employee;
/*
不加partition by 是全部行数：
rn1 表示整个工资比当前数小于等于的数所占比例
3/12 = 0.25   (3000)
5/12 = 0.4166666666666667  (4000)

有partition by,看本组：
rn2表示含义案例：
1 / 6 = 0.16666666666666666   (3000,在研发组中）
*/

cume_dist()的结果：

percent_rank的介绍：

percent_rank代码：

select
 dname,
 ename,
 salary,
 rank() over(partition by dname order by salary desc) as rn1,
 percent_rank() over(partition by dname order by salary desc) as rn2
from employee;
/*
rn2:
第一行：（1-1）/ (6-1) = 0
第二行：（1-1）/ (6-1) = 0
第三行：（3-1）/ (6-1) = 0.4
*/

percent_rank()结果：

1.6.4 前后函数 LAG和LEAD

select
 dname,
 ename,
 salary,
 hiredate,
 -- 将上一行的，放在当前的后面，如果没有默认'2000-01-01'
 lag(hiredate,1,'2000-01-01') over(partition by dname order by hiredate ) as rn1,
 -- 将上两行的值，放在当前的后面，如果没有默认null
 lag(hiredate,2) over(partition by dname order by hiredate ) as rn2
from employee;

1.6.5 头尾函数first_value和last_value

first_value()和last_value()案例：

select
 dname,
 ename,
 salary,
 hiredate,
 -- 到目前为止的第一个
 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;

1.6.6 其他函数nth_value(expr,n)、ntile(n)

nth_value(expr,n)案例：

select
 dname,
 ename,
 salary,
 hiredate,
 -- 到目前为止的排第1行的薪资
 nth_value(salary,1) over(partition by dname order by hiredate ) as first_salary,
 -- 到目前为止的排第2行的薪资
 nth_value(salary,2) over(partition by dname order by hiredate ) as second_salary
from employee;

ntile案例：

select
 dname,
 ename,
 salary,
 hiredate,
 -- 分成3组
 ntile(3) over(partition by dname order by hiredate ) as nt
from employee;
-- 取出每一个部门第一组员工
select * 
from
(
select
 dname,
 ename,
 salary,
 hiredate,
 -- 分成3组
 ntile(3) over(partition by dname order by hiredate ) as nt
from employee
) t
where t.nt =1;

参考

https://www.bilibili.com/video/BV1iF411z7Pu?p=99&spm_id_from=pageDriver