【MySQL数据库基本操作06--函数】

最新推荐文章于 2024-07-25 18:46:54 发布
最新推荐文章于 2024-07-25 18:46:54 发布
阅读量358 收藏
点赞数
分类专栏： MySQL 文章标签： mysql 数据库 database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_49892528/article/details/122149922
版权

MySQL的函数

1. 聚合函数

count\sum\min\max\avg

重点：group_concat()

-- 将所有员工的名字合并成一行 
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 separator ';') from emp group by department;

2. 数学函数

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

select ceil(1.1);--2
select floor(1.1); --1
select greatest(1,2,3);--3   # max返回字段的最大值，greatest返回列表的最大值
select least(1,2,3); --1
select mod(5/2);  --1
select pow(3,2);  --9
select floor(rand() * 100);
select round(3.568); --4
select round(3.568,2); --3.57
select truncate(3.568,2); --3.56

3. 字符串函数

#1 获取字符串字符个数
select char_length('hello');-- 5 length返回字节数
#2 concat字符串合并
select concat('hello','world'); -- helloworld
#3 指定分隔符进行合并
select concat_ws('-','hello','world'); -- hello-world
#4 返回第一个字符串 s 在字符串列表(s1,s2...)中第一次出现的位置
select field('a','a','b','c'); -- 1
#5 去除左边\右边\两端空格
select ltrim('   abc'),rtrim('abc   '),trim('  abc   ');
#6 从字符串 s 的 n 位置截取长度为 len 的子字符串，同 SUBSTRING(s,n,len)
select mid('helloworld',2,3); # ell
# 从字符串 s 中获取 s1 的开始位置,POSITION(s1 IN s)
select position('a' in 'nation'); #2
# 替换
select replace('abc','a','x'); # xbc
# 翻转
select reverse('abc'); #'cba'
# 返回字符串后n个字符
select right('hello',2); # lo
# 字符串比较
select strcmp('hello','world');  #-1,w>h
# 字符串截取
select substr('hello',2,3) #'ell'
# upper/lower 转换大小写
select upper('hello'); 'HELLO'

4. 日期函数

select unix_timestamp();
select unix_timestamp(2021-12-25 16:32:06);
# 将毫秒值时间戳转为指定格式日期
SELECT FROM_UNIXTIME(1598079966,'%Y-%m-%d %H:%i:%s'); 
# 获取当前年月日
select curdate();
# 获取当年时分秒
select curtime();
# 获取当前日期和时间
SELECT CURRENT_TIMESTAMP();
# 从日期或日期时间表达式中提取日期值
SELECT DATE(current_timestamp());    
# 计算日期 d1->d2 之间相隔的天数
SELECT DATEDIFF('2001-01-01','2001-02-02')
# 计算时间差值(秒级)
SELECT TIMEDIFF("13:10:11", "13:10:10");
# 日期格式化
select date_format('2021-1-1 1:1:1','%Y-%m-%d %H:%i:%s');
# 字符串转为日期
select str_to_date('2017-10-10','%Y-%m-%d');
# 将日期进行减法(date_sub)、加法(date_add);type值也可以多种
select date_sub('2021-10-01',interval 2 day);
# 从日期中获取小时
select extract(hour from '2021-12-25 20:01:10');
# 返回给给定日期的那一月份的最后一天
select last_day('2021-12-25');
# 获取指定年和天数的日期
select makedate('2021',100);
# 返回第几季节
select quarter('2021-5-20');

5. 控制流函数

5.1 if

# if(expr,v1,v2)
SELECT IF(1 > 0,'正确','错误'); 
# ifnull 如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。
SELECT IFNULL(null,'Hello Word')
# isnull 判断表达式是否为 NULL
SELECT ISNULL(NULL);
# nullif 比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1
select nullif()

5.2 case when

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

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

6. 窗口函数

​ MySQL 8.0 新增窗口函数,窗口函数又被称为开窗函数

在这里插入图片描述

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

6.1 序号函数

序号函数有三个：

ROW_NUMBER()、RANK()、DENSE_RANK()，可以用来实现分组排序，并添加序号。

不加partition by表示全局排序

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

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;

在这里插入图片描述

6.2 开窗聚合函数

select  
 dname,hiredate,
 ename,
 salary,
 sum(salary) over(partition by dname order by hiredate  rows between unbounded preceding and current row),
 sum(salary) over(partition by dname) as pv3
from employee;  -- 如果没有order  by排序语句  默认把分组内的所有数据进行sum操作

在这里插入图片描述

6.3 分布函数

CUME_DIST:

• 用途：分组内小于、等于当前rank值的行数 / 分组内总行数

• 应用场景：查询小于等于当前薪资（salary）的比例

SELECT
	dname,
	ename,
	salary,
	round( cume_dist() over ( ORDER BY salary ), 2 ) AS rn1,-- 没有partition语句 所有的数据位于一组
	round( cume_dist() over ( PARTITION BY dname ORDER BY salary ), 2 ) AS rn2 
FROM
	employee;

PERCENT_RANK:

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

•应用场景：不常用

7. 前后函数

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

•应用场景：查询前1名同学的成绩和当前同学成绩的差值

SELECT
	dname,
	ename,
	hiredate,
	salary,
	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 
FROM
	employee;
/*
last_1_time: 指定了往上第1行的值，default为'2000-01-01'  
                         第一行，往上1行为null,因此取默认值 '2000-01-01'
                         第二行，往上1行值为第一行值，2021-11-01 
                         第三行，往上1行值为第二行值，2021-11-02 
last_2_time: 指定了往上第2行的值，为指定默认值
                         第一行，往上2行为null
                         第二行，往上2行为null
                         第四行，往上2行为第二行值，2021-11-01 
                         第七行，往上2行为第五行值，2021-11-02 
*/
-- lead的用法
select 
 dname,
 ename,
 hiredate,
 salary,
 lead(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as last_1_time,
 lead(hiredate,2) over(partition by dname order by hiredate) as last_2_time 
from employee;

8. 头尾函数

•用途：返回第一个（FIRST_VALUE(expr)）或最后一个（LAST_VALUE(expr)）expr的值

•应用场景：截止到当前，按照日期排序查询第1个入职和最后1个入职员工的薪资

-- 注意,  如果不指定ORDER BY，则进行排序混乱，会出现错误的结果
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;

在这里插入图片描述

9. 其他函数

​ NTH_VALUE(expr,n)

•用途：返回窗口中第n个expr的值。expr可以是表达式，也可以是列名

•应用场景：截止到当前薪资，显示每个员工的薪资中排名第2或者第3的薪资

-- 查询每个部门截止目前薪资排在第二和第三的员工信息
select 
  dname,
  ename,
  hiredate,
  salary,
  nth_value(salary,2) over(partition by dname order by hiredate) as second_score,
  nth_value(salary,3) over(partition by dname order by hiredate) as third_score
from employee

在这里插入图片描述

​ NTILE(n)

•用途：将分区中的有序数据分为n个等级，记录等级数

•应用场景：将每个部门员工按照入职日期分成3组

-- 根据入职日期将每个部门的员工分成3组
select 
  dname,
  ename,
  hiredate,
  salary,
ntile(3) over(partition by dname order by  hiredate  ) as rn 
from employee;

在这里插入图片描述

sayhito_h
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
动力节点MySQL数据库入门视频教程-040-分组函数
12-20
动力节点的杜老师讲述的mysql教程，详细讲解了MySQL的相关知识，包括MySQL概述，MySQL应用环境，MySQL系统特性，MySQL初学基础，MySQL管理工具，如何安装MySQL及MySQL新特性，通过观看可掌握MySQL全套知识。
动力节点MySQL数据库入门视频教程-038-分组函数
12-20
动力节点的杜老师讲述的mysql教程，详细讲解了MySQL的相关知识，包括MySQL概述，MySQL应用环境，MySQL系统特性，MySQL初学基础，MySQL管理工具，如何安装MySQL及MySQL新特性，通过观看可掌握MySQL全套知识。
MySQL8新特性：窗口函数（精讲）
weixin_52533007的博客
07-09 1626
本博主将用CSDN记录软件开发求学之路上亲身所得与所学的心得与知识，有兴趣的小伙伴可以关注博主！也许一个人独行，可以走的很快，但是一群人结伴而行，才能走的更远！
Mysql 窗口函数
热门推荐
菜菜的大数据开发の路
03-04 3万+
一, MySQl 8.0 窗口函数 窗口函数适用场景: 对分组统计结果中的每一条记录进行计算的场景下, 使用窗口函数更好; 可以跟Hive的对比着看: 点我, 特么的花了一晚上整理, 没想到跟Hive 的基本一致, 还不因为好久没复习博客了, 淦 注意: mysql 因为没有array数据结构, 无法像Hive一样 行列进行转换; 1.1 窗口函数分类 MySQL从8.0版本开始支持窗口函数。窗口函数的作用类似于在查询中对数据进行分组，不同的是，分组操作会把分组的结果聚合成一条记录，而窗口函数是
MySQL 8 中的窗口函数详解
m0_54219225的博客
07-25 923
窗口函数是一种在结果集中进行计算的函数，可以对数据集的特定“窗口”内的行进行操作，而不需要将数据聚合成单个的输出行。与普通的聚合函数不同，窗口函数允许我们保留原始行，因此在进行数据分析时具有更大的灵活性。在 MySQL 8 中，窗口函数被广泛使用，能够带来更加复杂的数据分析能力，支持对数据的排序、分组和计算。MySQL 8 中的窗口函数为数据分析提供了强大的新工具，允许我们在与原始数据保持行的独立性时进行复杂的计算和分析。
一文带你快速上手MySQL8窗口函数，实现更高效的数据处理
热爱编程、热爱生活、热爱探索
10-13 692
花费30分钟的时间阅读本文，你将快速了解了解窗口函数的相关概念，以及如何使用，了解传统的聚合函数和窗口函数的区别。文中内容是博主阅读其它博主的博客、MySQL官方文档进行整理总结了，如果你觉得本文对你有所帮助，还请你不吝惜赐赞，你的支持将是我持续更新的动力
MYSQL8.0窗口函数
cristianoxm的博客
10-03 1361
Mysql8.0的窗口函数。不同的是，。比如在MYSQL的最新版本MYSQL8已经。但是在就旧版本中还不支持这些函数，只能自己实现。实现方法主要。
MySQL8新特性：窗口函数（最新）（内含使用场景和例子）
weixin_44512005的博客
06-12 1188
本文将介绍MySQL8的新特性——窗口函数，从概念、语法、示例各方面详细解析窗口函数，以及解析面试中窗口函数常考的题目和知识点
动力节点MySQL数据库入门视频教程-031-单行处理函数
12-20
动力节点的杜老师讲述的mysql教程，详细讲解了MySQL的相关知识，包括MySQL概述，MySQL应用环境，MySQL系统特性，MySQL初学基础，MySQL管理工具，如何安装MySQL及MySQL新特性，通过观看可掌握MySQL全套知识。
动力节点MySQL数据库入门视频教程-030-单行处理函数
12-17
动力节点的杜老师讲述的mysql教程，详细讲解了MySQL的相关知识，包括MySQL概述，MySQL应用环境，MySQL系统特性，MySQL初学基础，MySQL管理工具，如何安装MySQL及MySQL新特性，通过观看可掌握MySQL全套知识。
mysql数据库国产化改造-达梦
最新发布
08-27
文档包含了mysql数据库国产化改造实践过程中的一些总结的经验。包括表结构调整，关键词冲突，函数冲突等。
MySQL8中的11个窗口函数
倔强禅师的博客
06-16 534
MySQL8窗口函数总结及简单实用
MySQL8窗口函数
mmqgirlfriend的博客
04-04 245
窗口限定一个范围，它可以理解为满足某些条件的记录集合，窗口函数也就是在窗口范围内执行的函数。
MySQL 8.0 新特性 —— 窗口函数 详解
lx104921的博客
05-14 955
窗口函数介于单行函数和分组函数之间。与分组函数相比，窗口函数对记录进行分组聚合之后，将聚合结果置于每一条记录之中。窗口函数根据窗口是否可变可以分为静态窗口函数和动态窗口函数。
MySQL8中的开窗函数
Gan_1314的博客
07-05 4546
MySQL从8.0版本开始支持窗口函数了，窗口函数又名开窗函数，属于分析函数的一种。用于解决复杂报表统计需求的功能强大的函数。窗口函数用于计算基于组（GROUP BY）的某种聚合值，它和聚合函数的不同之处是：窗口函数可以在分组之后的返回多行结果，而聚合函数对于每个组只返回一行。开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。..................
MySQL 8窗口函数详解：高效数据处理的必备技能
todoitbo的博客
05-27 3561
本文将详细介绍MySQL 8中的窗口函数。窗口函数是SQL中强大的数据分析工具，能够帮助我们进行复杂的统计和分析操作。本文将介绍窗口函数的定义、基本语法和常用类型，并通过实例展示如何在实际应用中使用窗口函数进行数据分析。读者将学习到如何利用窗口函数提升查询效率，简化复杂的数据处理任务。
Mysql8之窗口函数
j218255的博客
08-06 81
文章内容是关于Mysql8的窗口函数的使用,内容会结合我做到的相关题目或者结合相关业务的分析来进行写作,可能并不全但后续会进行补充.
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值