数据库学习-MySQL函数

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

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

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

聚合函数

在MySQL中，聚合函数主要有：count，sum，min，max，avg，这些聚合函数之前都学过。这里学习另外一个函数：group_concat()，该函数用户实现行的合并。

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

格式：

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

说明：

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

create database mydb4;
use mydb4;

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,'财务部'),('张小斐',5200,'人事部'),('刘云云',7500,'销售部'),('刘云鹏',7200,'销售部'),('刘云鹏',7200,'销售部');

-- 将所有员工的名字合成一行
select group_concat(emp_name) from emp;
-- 指定分隔符合并
select group_concat(emp_name separator ';') from emp;
-- 指定排序方式和分隔符
select department,group_concat(emp_name separator ';') from emp group by department;
select department,group_concat(emp_name order by salary desc separator ';') from emp group by department;

---

数字函数

 

 

---

字符串函数

---

日期函数

 

---

控制流函数

if逻辑判断语句

select *,if(score>=85,'优秀','及格') flag from score;

case when语句

select
	case 5
		when 1 then '你好'
		when 2 then 'hello'
		when 5 then '正确'
		else 
			'其他'
	end;

select
    *,
    case payType
        when 1 then '微信支付'
        when 2 then '支付宝支付'
        when 3 then '银行卡支付'
        else
            '其他支付方式'
    end as payTypeStr
from orders;

select
    *,
    case
        when payType=1 then '微信支付'
        when payType=2 then '支付宝支付'
        when payType=3 then '银行卡支付'
        else
            '其他支付方式'
    end as payTypeStr
from orders;

---

窗口函数

        MySQL8.0新增窗口函数，窗口函数又被称为开窗函数，与Oracle窗口函数类似，属于MySQL的一大特点。

        非聚合窗口函数是相对于聚合函数来说的。聚合函数是对一组数据计算后返回单个值（即分组），非聚合函数一次只会处理一行数据。窗口聚合函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数。 

 语法结构

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选项用于在当前分区内指定一个计算窗口，也就是一个与当前行相关的数据子集

序号函数

        序号函数有三个：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 desc) as rn1,
 rank() over(partition by dname order by salary desc) as rn2,
 dense_rank() over(partition by dname order by salary desc) as rn3
from employee;

求出每个部门薪资排在前三名的员工-分组求TOPN

select * from
(
 select
  dname,
  ename,
  salary,
  dense_rank() over(partition by dname order by salary desc) as rn
 from employee
)t
where t.rn<=3;

 

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

select
  dname,
  ename,
  salary,
  dense_rank() over(order by salary desc) as rn
from employee

 开窗聚合函数-sum，avg，min，max

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

select
	dname,
	ename,
	hiredate,
	salary,
	sum(salary) over(partition by dname order by hiredate) as c1
from employee;-- 从第一行开始加到当前行
select
	dname,
	ename,
	hiredate,
	salary,
	sum(salary) over(partition by dname) as c1
from employee;-- 如果没有order by排序语句，默认把分组内的所有数据进行sum操作

select
	dname,
	ename,
	salary,
	sum(salary) over(partition by dname order by hiredate rows between 3 preceding and current row) as c1
from employee;-- 从向上3行加到当前行

select
	dname,
	ename,
	salary,
	sum(salary) over(partition by dname order by hiredate rows between 3 preceding and 1 following) as c1
from employee;-- 从向上3行加到向后1行，包括当前行

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

分布函数-cume_dist和percent_rank()

cume_dist

用途：分组内小于、等于当前rank值的行数/分组内总行数
应用场景：查询小于等于当前薪资（salary）的比例

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;

percent_rank

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

select 
 dname,
 ename,
 salary,
 rank() over(partition by dname order by salary desc) as rn,
 percent_rank() over(partition by dname order by salary desc) as rn2
from employee;

前后函数-lag和lead

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

select
	dname,
	ename,
	salary,
	hiredate,
	lag(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as time1,
	lag(hiredate,2) over(partition by dname order by hiredate) as time2
from employee;

select
	dname,
	ename,
	salary,
	hiredate,
	lead(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as time1,
	lead(hiredate,2) over(partition by dname order by hiredate) as time2
from employee;

 

 头尾函数-first_values和last_value

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

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;

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

nth_value

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

select
	dname,
	ename, 
	salary,
	hiredate,
	nth_value(salary,2) over(partition by dname order by hiredate) as second_salary,
	nth_value(salary,3) over(partition by dname order by hiredate) as third_salary
from employee;

 

 ntile

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

select
	dname,
	ename, 
	salary,
	hiredate,
	ntile(3) over(partition by dname order by hiredate) as nt
from employee;