MySQL的函数

目录

一.分类

聚合函数

概述

格式

操作

 数学函数

操作1

操作2

操作3

字符串函数

操作1

 操作2

操作3

操作4

日期函数

操作1

 操作2

操作3

控制流函数

if逻辑判断语句

case when 语句

 窗口函数

介绍

 分类

序号函数

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

分布函数- CUME_DIST和PERCENT_RANK

前后函数-LAG和LEAD

 头尾函数-FIRST_VALUE和LAST_VALUE

 其他函数-NTH_VALUE(expr, n)、NTILE(n)

---

 

一.分类

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

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

聚合函数

概述

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

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

格式

说明

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

操作

 

 

 

 数学函数

操作1

--求绝对值
select abs( -10);
select abs (10);
select abs(表示式或者字段) from 表;
--向上取整 
select ceil(1.1); -- 2
select ceil(1.0); -- 1
--向下取整
select floor(1.1); -- 1
select floor(1.9); -- 1
--取列表最大值
select greatest(1,2,3);

操作2

--取模
select mod (5,2);-- 1
--取x的y次方
select power(2,3); -- 8

操作3

--将小数的四舍五入取整
select round(3.5415);-- 3
--将小数的四舍五入取指定位数小数
select round(3.5415,3);-- 3.542

use mydb2 ;
select category_id, round(avg(price),2) from product group by category_id;

字符串函数

操作1

--字符串函数
-- 1:获取字符串字符个数
selectchar_length( ' hello ' ) ; -- 5
select char_length( '你好吗'); -- 3
-- length取长度，返回的单位是字节
select length( ' hello ' ); -- 5
select length('你好吗'); -- 9
-- 2:字符串合并
select concat( ' hello' , 'world');
select concat(c1,c2) from table_name;
-- 2:指定分隔符进行字符串合并
select concat_ws( '-', ' hello' , 'world' ); I
-3:返回字符串在列表中的位置
select field( 'aaa' , 'aaa' , ' bbb ' , 'ccc' ); -- 1
select field( 'bbb', 'aaa' , ' bbb' , 'ccc '); -- 2

 操作2

-- 4:去除字符串空格
select ltrim('   aaaa'); -- 去除左边空格
select rtrim('aaaa   '); -- 去除右边空格
select trim('  aaaa  '); -- 去除两端空格
-- 5:字符串截取
select mid("helloworld" ,2,3); -- 从第二个字符开始截取，截取长度为3
-- 6:获取字符串A在字符串中出现的位置
select position( 'abc' in 'habcelloabcworld ' );
-- 7:字符串替换
select replace( 'aaahelloaaaworld' , 'aaa' , 'bbb') ;
-- 8:字符串翻转
select reverse( ' hello ' );

操作3

-- 9:返回字符串的后几个字符
select right( 'hello',3); --返回最后三个字符
-- 10:字符串比较
select strcmp( ' hello' , ' world ' );
-- 11:字符串截取
select substr( ' hello',2,3); --从第二个字符开始截取，截取三个字符

操作4

-- 11:字符串截取
select substr( ' hello',2,3); --从第二个字符开始截取，截取三个字符
select substring( 'hello',2,3); --从第二个字符开始截取，截取三个字符
-- 12:将小写转大写
select ucase( "helloworld" );
select upper( "helloworld" );
-- 13:将大写转为小写
select lcase( "hellowor1d");
select lower( "helloworld" );

日期函数

操作1

--日期函数
-- 1:获取时间戳（毫秒值)
select unix_timestamp( );
-- 2:将一个日期字符串转为毫秒值
select unix_timestamp( ' 2021-12-21 08:08:08');
-- 3:将时间戳毫秒值转为指定格式的日期
select from_unixtime(1640045288,'%Y-%m-%d %H:%i:%s ')

 操作2

 

 

操作3

 

 

 

控制流函数

if逻辑判断语句

 

case when 语句

 

 窗口函数

介绍

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

 分类

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

语法结构

 其中，window_function 是窗口函数的名称, expr是参数，有些函数不需要参数;OVER子句包含三个选项:
1.分区(PARTITION BY)
PARTITION BY选项用于将数据行拆分成多个分区(组)，它的作用类似于GROUP BY分组。如果省略了PARTTION BY，所有的数据作为一个组进行计算
2.排序(ORDER BY)
OVER子句中的ORDER BY选项用于指定分区内的排序方式，与ORDER BY子句的作用类似以及
3.窗口大小(frame_clause) 。
frame_clause选项用于在当前分区内指定一个计算窗口，也就是一个与当前行相关的数据子集。

序号函数

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

格式

 操作

create database test2;
use test2 ;
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',4900),
							( '销售部','1007','曹操','2021-11-01',2000),
							( '销售部','1008','许褚','2021-11-02',3000),
							( '销售部','10091','典韦','2021-11-03',5000),
							('销售部','1010','张辽','2021-11-04',6000),
							( '销售部','1011','徐晃','2021-11-05',9000),
							('销售部','1012','曹洪','2021-11-06',6000);

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

select  *, rank () over (partition by dname  order by salary desc ) as pm from employee e ;
select  *, dense_rank  () over (partition by dname  order by salary desc ) as pm from employee e ;

输出的结果依次是

row_number:

rank:

dense_rank:

 

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

概念
在窗口中每条记录动态地应用聚合函数(SUM()、AVG()、MAX()、MIN()、CcOUNT())，可以动态计算在指定的窗口内的各种聚合函数值。

 操作

 

 8000=3000+5000
15000=3000+5000+7000
……

 

 

 

 

 

分布函数- CUME_DIST和PERCENT_RANK

介绍-CUME_DIST

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

操作

 

 

介绍-PERCENT_RANK

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

操作

 

 

前后函数-LAG和LEAD

介绍

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

操作

 1是指把上一行的输出，后面的是默认值

 

 

 头尾函数-FIRST_VALUE和LAST_VALUE

介绍

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

操作

 

 其他函数-NTH_VALUE(expr, n)、NTILE(n)

介绍-NTH_VALUE(expr,n)

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

操作

 

 介绍-NTILE

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

操作