Day03 03-MySQL函数大全与详解

news/2024/11/24 21:43:01/

文章目录

      • 第九章 MySQL的函数
        • 9.1 功能性函数
          • 9.1.1 IFNULL
          • 9.1.2 IF
          • 9.1.3 CASE
          • 9.1.4 行转列实现
          • 9.1.5 exists
        • 9.2 日期函数
        • 9.3 字符串函数
        • 9.4 数学函数
        • 9.5 使用示例
        • 9.6 自定义函数
          • 9.6.1 什么是自定义函数
          • 9.6.2 函数与存储过程的区别
          • 9.6.3 自定义函数实现
        • 9.7 窗口函数
          • 9.7.1 窗口函数的简介
          • 9.7.2 窗口函数案例
          • 9.7.3 窗口分组
          • 9.7.4 窗口数据排序
          • 9.7.5 排名函数

第九章 MySQL的函数

函数在编程中总是存在，存在于各种编程语言中，当然也包括SQL，作为一名当下或者未来优秀的SQL Boy，如果你不熟悉一些常用函数，严格来说，就不算优秀的SQL Boy了。所以，如果作为初学者，本章节很重要，因为后续的大数据很多数仓工具都提供类SQL，也任然有很多函数，但大致函数都很像，所以，前面的学好，后续的就信手拈来。

9.1 功能性函数

9.1.1 IFNULL

这个函数在之前的SQL中已经使用过了，函数有两个参数，形式为: IFNULL(field, value)

逻辑: 如果第一个参数field是NULL，则返回第二个参数value的值，否则就返回field本身的值

select ifnull(null, 10);		-- 查询结果是10
select ifnull(10, 20);			-- 查询结果是10
select ifnull(`id`, 0);			-- 如果id字段的值是NULL，就返回0，否则就返回id字段的值
9.1.2 IF

这个函数有点类似于Java中的三目运算符，函数有三个参数，形式为: IF(condition, value1, value2)

逻辑: 如果第一个参数condition条件成立，就返回value1的值，否则就返回value2的值。

select if(10 > 20, 10, 20);		-- 因为10 > 20不成立，因此返回20
select if(10 > 5, 10, 5);		-- 因为10 > 5成立，因此返回10
select if(`chinese` > `math`, `chinese`, `math`);	-- 如果chinese字段的值大于math字段的值，就返回chinese字段的值，否则就返回math字段的值
9.1.3 CASE

这个函数是MySQL中非常强大的一个函数，可以使用他来实现类似Java中的分支结构。通过模拟Java的if的实现和switch的实现，来实现一些简单的分支流程控制。case…when有两种写法:

  1. 类似Java的if

    -- 需求: 已知sc表中有一个字段名为`score`表示一个学生的成绩。通过这个成绩字段的不同范围，查询出不同的结果:
-- < 0 或者 > 100, 等级为"错误成绩"
-- [0, 60), 等级为"不及格"
-- [60, 80), 等级为"良"
-- [80, 100], 等级为"优"
select `sid`, `score`, case when `score` < 0 or `score` > 100 then '错误成绩'when `score` < 60 then '不及格'when `score` < 80 then '良'else '优'end as 'level'
from `sc`;

  2. 类似Java的switch

    -- 需求: 已知sc表中有一个字段名为`subject`表示成绩的科目。通过这个字段的不同的值，查询出不同的描述信息:
-- 如果是chinese，查询出"语文"
-- 如果是math，查询出"数学"
-- 如果是english，查询出"英语"
-- 其他的保持原样
select `subject`,case `subject`when 'chinese' then '语文'when 'math' then '数学'when 'english' then '英语'else `subject`end as '翻译'
from `sc`
9.1.4 行转列实现

  1. 数据准备

    snamesubjectscore
    张小三语文78
    张小三数学77
    张小三英语90
    张小三历史89
    张小三体育80
    李小四数学90
    李小四英语80
    李小四体育88
    李小四政治88
    李小四历史78
    王小五语文90
    王小五英语80
    王小五政治89
    王小五体育90
    -- 建表
create table `sc` (`sname` varchar(20),`subject` varchar(20),`score` int
);
-- 插入数据
insert into `sc` values ('张小三', '语文', 78),('张小三', '数学', 77),('张小三', '英语', 90),('张小三', '历史', 89),('张小三', '体育', 80),('李小四', '数学', 90),('李小四', '英语', 80),('李小四', '体育', 88),('李小四', '政治', 88),('李小四', '历史', 78),('王小五', '语文', 90),('王小五', '英语', 80),('王小五', '政治', 89),('王小五', '体育', 90);

  2. 需求实现1

    姓名语文数学英语历史政治体育
    张小三78779089080
    李小四09080788888
    王小五9008008990
    -- if实现版本
select `sname` as '姓名',sum(if(`subject` = '语文', `score`, 0)) as '语文',sum(if(`subject` = '数学', `score`, 0)) as '数学',sum(if(`subject` = '英语', `score`, 0)) as '英语',sum(if(`subject` = '历史', `score`, 0)) as '历史',sum(if(`subject` = '政治', `score`, 0)) as '政治',sum(if(`subject` = '体育', `score`, 0)) as '体育'
from `sc` group by `sname`;-- case实现版本
select `sname` as '姓名',sum(case `subject` when '语文' then `score` else 0 end) as '语文',sum(case `subject` when '数学' then `score` else 0 end) as '数学',sum(case `subject` when '英语' then `score` else 0 end) as '英语',sum(case `subject` when '历史' then `score` else 0 end) as '历史',sum(case `subject` when '政治' then `score` else 0 end) as '政治',sum(case `subject` when '体育' then `score` else 0 end) as '体育'
from `sc` group by `sname`;

  3. 需求实现2

    姓名语文数学英语历史政治体育总成绩
    张小三78779089080414
    李小四09080788888424
    王小五9008008990349
    -- if实现版本
select `sname` as '姓名',sum(if(`subject` = '语文', `score`, 0)) as '语文',sum(if(`subject` = '数学', `score`, 0)) as '数学',sum(if(`subject` = '英语', `score`, 0)) as '英语',sum(if(`subject` = '历史', `score`, 0)) as '历史',sum(if(`subject` = '政治', `score`, 0)) as '政治',sum(if(`subject` = '体育', `score`, 0)) as '体育',sum(`score`) as '总成绩'
from `sc` group by `sname`;-- case实现版本
select `sname` as '姓名',sum(case `subject` when '语文' then `score` else 0 end) as '语文',sum(case `subject` when '数学' then `score` else 0 end) as '数学',sum(case `subject` when '英语' then `score` else 0 end) as '英语',sum(case `subject` when '历史' then `score` else 0 end) as '历史',sum(case `subject` when '政治' then `score` else 0 end) as '政治',sum(case `subject` when '体育' then `score` else 0 end) as '体育',sum(`score`) as '总成绩'
from `sc` group by `sname`;

  4. 需求实现3

    姓名语文数学英语历史政治体育总成绩
    张小三78779089080414
    李小四09080788888424
    王小五9008008990349
    总成绩1681672501671772581187
    -- if实现版本
select `sname` as '姓名',sum(if(`subject` = '语文', `score`, 0)) as '语文',sum(if(`subject` = '数学', `score`, 0)) as '数学',sum(if(`subject` = '英语', `score`, 0)) as '英语',sum(if(`subject` = '历史', `score`, 0)) as '历史',sum(if(`subject` = '政治', `score`, 0)) as '政治',sum(if(`subject` = '体育', `score`, 0)) as '体育',sum(`score`) as '总成绩'
from `sc` group by `sname`
union
select '总成绩' as '姓名',sum(if(`subject` = '语文', `score`, 0)) as '语文',sum(if(`subject` = '数学', `score`, 0)) as '数学',sum(if(`subject` = '英语', `score`, 0)) as '英语',sum(if(`subject` = '历史', `score`, 0)) as '历史',sum(if(`subject` = '政治', `score`, 0)) as '政治',sum(if(`subject` = '体育', `score`, 0)) as '体育',sum(`score`) as '总成绩'
from `sc`;-- case实现版本
select `sname` as '姓名',sum(case `subject` when '语文' then `score` else 0 end) as '语文',sum(case `subject` when '数学' then `score` else 0 end) as '数学',sum(case `subject` when '英语' then `score` else 0 end) as '英语',sum(case `subject` when '历史' then `score` else 0 end) as '历史',sum(case `subject` when '政治' then `score` else 0 end) as '政治',sum(case `subject` when '体育' then `score` else 0 end) as '体育',sum(`score`) as '总成绩'
from `sc` group by `sname`
union
select '总成绩' as '姓名',sum(case `subject` when '语文' then `score` else 0 end) as '语文',sum(case `subject` when '数学' then `score` else 0 end) as '数学',sum(case `subject` when '英语' then `score` else 0 end) as '英语',sum(case `subject` when '历史' then `score` else 0 end) as '历史',sum(case `subject` when '政治' then `score` else 0 end) as '政治',sum(case `subject` when '体育' then `score` else 0 end) as '体育',sum(`score`) as '总成绩'
from `sc`;
9.1.5 exists

顾名思义，就是判断数据是否存在的！exists的作用为判断一个表中的数据，是否在另外的一张表中能够查询到与之对应的数据

效率要比连接查询和子查询高！

-- 案例1: 查询有员工的部门
select distinct dept.* from dept left join emp on dept.deptno = emp.deptno where empno is not null;select * from dept where exists(select 1 from emp where dept.deptno = emp.deptno
);-- 案例2: 查询没有员工的部门
select distinct dept.* from dept left join emp on dept.deptno = emp.deptno where empno is null;select * from dept where not exists(select 1 from emp where dept.deptno = emp.deptno
);-- 案例3: 查询有部门的员工
select * from emp where exists (select 1 from dept where dept.deptno = emp.deptno
);-- 案例4: 查询没有部门的员工信息
select * from emp where not exists (select 1 from dept where dept.deptno = emp.deptno
);-- 案例5: 查询有下属的员工信息
select * from emp A where exists (select 1 from emp B where B.mgr = A.empno
);-- 案例6: 查询有领导的员工信息
select * from emp A where exists (select 1 from emp B where A.mgr = B.empno
);

9.2 日期函数

函数描述
ADDTIME (date2 ,time_interval )将time_interval加到date2
CURRENT_DATE ( )当前日期
CURRENT_TIME ( )当前时间
CURRENT_TIMESTAMP ( )当前时间戳
DATE (datetime )返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type )在date2中加上日期或时间
DATE_SUB (date2 , INTERVAL d_value d_type )在date2上减去一个时间
DATEDIFF (date1 ,date2 )两个日期差
NOW ()当前时间
YEAR|Month|Day(datetime )年月日

9.3 字符串函数

函数描述
CHARSET(str)返回字串字符集
CONCAT (string2 [… ])连接字串
INSTR (string ,substring )返回substring在string中出现的位置,没有返0
UCASE (string2 )转换成大写
LCASE (string2 )转换成小写
LEFT (string2 ,length )从string2中的左边起取length个字符
LENGTH (string )string长度
REPLACE (str ,search_str ,replace_str )在str中用replace_str替换search_str
STRCMP (string1 ,string2 )逐字符比较两字串大小,
SUBSTRING (str , position [,length ])从str的position开始,取length个字符
LTRIM (string2 ) RTRIM (string2 ) trim去除前端空格或后端空格

9.4 数学函数

函数描述
ABS (number2 )绝对值
BIN (decimal_number )十进制转二进制
CEILING (number2 )向上取整
CONV(number2,from_base,to_base)进制转换
FLOOR (number2 )向下取整
FORMAT (number,decimal_places )保留小数位数
HEX (DecimalNumber )转十六进制
LEAST (number , number2 […])求最小值
MOD (numerator ,denominator )求余
RAND([seed])RAND([seed])

9.5 使用示例

-- mysql常用的函数
SELECT ADDTIME('2007-12-30 21:50:50', '1:1:1') 
SELECT ADDTIME(NOW(),'1:1:1')
SELECT CURRENT_DATE()
SELECT CURRENT_TIME()
SELECT CURRENT_TIMESTAMP()
SELECT DATE(NOW())-- 获取当前时间的日期部分SELECT DATE_ADD('2016-6-6' ,INTERVAL  -1 DAY)
SELECT DATEDIFF('2016-6-8','2016-6-10')  -- 前面的时间-后面的时间的差值
SELECT NOW();SELECT YEAR|MONTH|DAY(DATETIME )-- 不能这样做
SELECT YEAR(NOW())-- 单独获取年
SELECT MONTH(NOW())-- 单独获取月
SELECT DAY(NOW())-- 单独获取日-- 对字符串的操作
SELECT CHARSET('hello') -- 当前的编码格式
SELECT CONCAT('he','lo') -- 合并字符串
SELECT *,CONCAT(ename,job) FROM emp;
SELECT INSTR('hello','e')-- 当在原字符串中找不到子字符串时,会返回0
SELECT LEFT('hello',2)-- -- 不能这样做
SELECT REPLACE('hello','h','wo')
SELECT STRCMP('hello','heloo') -- 比较两个字符串   前面大返回1   后面大返回-1  相等返回0
SELECT LTRIM('       hello') -- 去除左边的空格
SELECT RTRIM('hello     ') -- 去除右边的空格
SELECT MOD(34,0); -- 返回null

9.6 自定义函数

9.6.1 什么是自定义函数

在上方我们已经学习了很多的函数了，但是虽然系统给我们提供的函数再怎么多，依然不能满足我们所有的需求。有时候对于一些较为复杂的逻辑，我们就需要用自定义的函数来实现。

其实自定义函数与Java中的类似，都需要指定函数的名字、函数的参数、函数的返回值和函数需要实现的逻辑功能，只是在语法上有所区别。Java的函数用Java代码来书写，MySQL的函数与后面要学习的存储过程类似，都是一组SQL集。

9.6.2 函数与存储过程的区别

  • 函数可以return值，存储过程不能直接return，但是有输出参数可以输出多个返回值；

  • 函数可以嵌入到sql语句中使用，而存储过程不能；

  • 函数一般用于实现较简单的有针对性的功能（如求绝对值、返回当前时间等），存储过程用于实现复杂的功能（如复杂的业务逻辑功能）；

  • 函数的关键字是function, 存储过程是:procedure

9.6.3 自定义函数实现

  1. 定义一个函数，返回一个数字的绝对值

    -- 如果之前存在这个函数，先将其删除
drop function if exists `my_abs`;-- 自定义分隔符,这里定义的分隔符是;; 定义好后,只有遇到;;才会结束
delimiter ;;-- 创建函数，num为形参，returns为返回值
create function `my_abs`(`num` int) returns int
deterministic
deterministic
-- 函数开始
beginif `num` >= 0 thenreturn `num`;elsereturn -`num`;end if;
-- 函数结束
end
;;-- 将分隔符重新定义为;
delimiter ;

  2. 定义一个函数，通过成绩的不同区间，返回对应的级别: 不及格、良、优、错误成绩

    -- 如果之前存在这个函数，先将其删除
drop function if exists `get_level_info`;-- 自定义分隔符
delimiter ;;-- 创建函数
create function `get_level_info`(`score` int) returns varchar(20) charset utf8
deterministic
beginif `score` < 0 or `score` > 100 thenreturn '错误成绩';elseif `score` < 60 thenreturn '不及格';elseif `score` < 80 thenreturn '良';elsereturn '优'end if;
end
;;delimiter ;

  3. 定义一个函数，提取一个字符串中的中文部分

    -- 如果之前存在这个函数，先将其删除
drop function if exists `get_name`;
-- 自定义分隔符
delimiter ;;
-- 创建函数
create function `get_name`(`name` varchar(50) charset utf8) returns varchar(50) charset utf8
deterministic
begindeclare i int default 0;declare l int default char_length(`name`);declare tmp varchar(50) default '';while i <= l doif mid(`name`, i, 1) regexp '[\\u4e00-\\u9fa5]' thenset tmp = concat(tmp, mid(`name`, i, 1));end if;set i = i + 1;end while;return tmp;
end
;;delimiter ;

9.7 窗口函数

MySQL在8.0版本之后，支持了窗口函数。

9.7.1 窗口函数的简介

在了解窗口函数之前，我们先看这个需求应该怎么实现: 查询每一个员工的信息，及其部门的平均薪资。

-- 实现方式1: 通过子查询实现
select A.*, B.avg_sal
from emp A join (select deptno,avg(ifnull(sal,0)) avg_sal from emp group by deptno) B on A.deptno = B.deptno-- 实现方式2: 通过子查询实现
select emp.*, (select avg(ifnull(sal,0)) from emp B where B.deptno = A.deptno)
from emp A;

通过这个查询可以看到，想要进行明细的查询和聚合统计的查询，需要进行两次查询操作，比较麻烦，而且查询效率也不高。那么，能不能简化这样的流程呢？窗口函数就应运而生了！

那么，窗口函数是什么呢？

  • 窗口函数又名开窗函数，属于分析函数的一种。
  • 是一种用于解决复杂报表统计需求的函数。
  • 窗口函数常用于计算基于组的某种值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。简单的说窗口函数对每条详细记录开一个窗口,进行聚合统计的查询
  • 开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。
  • 窗口函数一般不单独使用
  • 窗口函数内也可以分组和排序

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CUbUcSPn-1684987337773)(https://shawn-qianfeng.oss-cn-beijing.aliyuncs.com/img/image-20220117234130132.png)]

9.7.2 窗口函数案例

使用over()函数，为查询到的每一行数据开一个数据窗口。窗口内的数据为本次查询到的所有的数据。

一般是对窗口内的数据进行聚合统计的操作。

-- 需求: 查询每一个员工的基本信息，及所有的员工的数量
select *, count(*) over() from `emp`;-- 需求: 查询每一个员工的基本信息，及所有员工的工资和
select *, sum(`sal`) over() from `emp`;-- 需求: 查询每一个员工的基本信息，及所有员工的平均奖金
select *, avg(ifnull(`comm`, 0)) over() from `emp`;-- 需求: 查询每一个员工的基本信息，及所有员工的平均工龄
select *, avg(round(datediff(now(), `hiredate`)/365, 1)) over() from `emp`;
9.7.3 窗口分组

通过指定的字段，对数据进行分组，此时窗口内的数据为指定分组的数据。

例如: count(*) over(partition by `deptno`), 此时将数据通过deptno字段进行了分组，窗口内的数据是与本行的deptno相同的数据。

-- 需求: 查询每一个员工的基本信息，及其部门的人数
select *, count(*) over(partition by `deptno`) from `emp`;-- 需求: 查询每一个员工的基本信息，及其部门的最高工资
select *, max(`sal`) over(partition by `deptno`) from `emp`;-- 需求: 查询每一个员工的基本信息，及其部门的平均薪资
select *, avg(ifnull(`sal`, 0)) over(partition by `deptno`) from `emp`;-- 需求: 查询每一个员工的基本信息，及部门的平均工龄
select *, avg(round(datediff(now(), `hiredate`)/365, 1)) over(partition by `deptno`) from `emp`;-- 需求: 查询每一个员工的基本信息，及当前岗位的人数
select *, count(*) over(partition by `job`) from `emp`;
9.7.4 窗口数据排序

使用order by子句，可以实现窗口内的数据按照指定的条件进行升序或者降序的排列。此时需要注意的是: 窗口内的数据会逐行递增

select *, sum(`sal`) over(partition by `deptno` order by `sal` desc) from `emp`;
9.7.5 排名函数

有了窗口函数，就可以对数据进行排名次了。常见的排名函数有三种: row_number()、rank()、dense_rank();

  • row_number: 对每一行的数据进行编号，不会出现相同的名次
  • rank: 常见的排名函数，会出现并列的名次，同时会出现名次跳跃的情况
  • dense_rank: 常见的排名函数，会出现并列的名次，不会出现名次跳跃的情况
scorerow_numberrankdense_rank
100111
99222
99322
98443
98543
97664
-- 查询每一个员工的基本信息，及员工在自己部门内的工资排名
select *, rank() over(partition by `deptno` order by `sal` desc) from `emp`;-- 查询每一个员工的基本信息，及员工在所有人中的工资排名
select *, dense_rank() over(order by `sal` desc) from `emp`;-- 查询每一个员工的基本信息，及员工在同一个工种内的工资排名
select *, rank() over(partition by `job` order by `sal` desc) from `emp`;-- 查询每一个员工的基本信息，及员工在所有人中的工龄排名
select *, rank() over(order by `hiredate`) from `emp`;-- 查询每一个员工的基本信息，及员工在当前部门中的工龄排名
select *, rank() over(partition by `deptno` order by `hiredate`) from `emp`;

http://www.ppmy.cn/news/82566.html

相关文章

燃料电池系统控制技术的演进

燃料电池系统控制技术的演进

背景 随着环保意识的不断提高&#xff0c;对清洁能源的需求也日益增加。燃料电池作为一种高效、低污染的清洁能源&#xff0c;受到了广泛关注和重视。与传统燃烧发电相比&#xff0c;燃料电池具有高效、无污染、静音等优点&#xff0c;可以成为未来清洁能源的主要来源之一。 …
阅读更多...
【实用工具 - vscode】实现Linux服务器的远程操控

【实用工具 - vscode】实现Linux服务器的远程操控

&#x1f31f;hello&#xff0c;各位读者大大们你们好呀&#x1f31f; &#x1f36d;&#x1f36d;系列专栏&#xff1a;【计算机工具】 ✒️✒️本篇内容&#xff1a;vscode的下载安装&#xff0c;常见插件及详细安装方法&#xff0c;插件停用及卸载&#xff0c;vscode快捷键 …
阅读更多...
Java 中Spring Security 是什么？如何使用

Java 中Spring Security 是什么？如何使用

简介 Spring Security是一款基于Spring框架的安全框架&#xff0c;它提供了一系列的功能和API&#xff0c;用于保护Web应用程序和REST API的安全性。Spring Security可以提供身份验证、授权、加密和防止攻击等功能。它是Spring框架的一部分&#xff0c;可以与Spring框架无缝集…
阅读更多...
Python带你实现批量自动点赞小程序

Python带你实现批量自动点赞小程序

前言 大家早好、午好、晚好吖 ❤ ~欢迎光临本文章 所用知识点: 动态数据抓包 requests发送请求 json数据解析 开发环境: python 3.8 运行代码 pycharm 2022.3 辅助敲代码 requests 请求模块 &#xff0c;第三方&#xff0c;需安装 win R 输入cmd 输入安装命令 pip inst…
阅读更多...
Java基础---＞并发部分（3）【JUC、AQS】

Java基础---＞并发部分（3）【JUC、AQS】

文章目录 AQS&#xff08;AbstractQueuedSynchronizer&#xff09;AQS实现原理AQS操作重点方法 Java并发容器JUC&#xff08;java.util.concurrent&#xff09;ConcurrentHashMapCopyOnWriteArrayList AQS&#xff08;AbstractQueuedSynchronizer&#xff09; AbstractQueuedSy…
阅读更多...
【Python共享文件】——Python快速搭建HTTP web服务实现文件共享并公网远程访问

【Python共享文件】——Python快速搭建HTTP web服务实现文件共享并公网远程访问

文章目录 1. 前言2. 视频教程3. 本地文件服务器搭建3.1 python的安装和设置3.2 cpolar的安装和注册 4. 本地文件服务器的发布4.1 Cpolar云端设置4.2 Cpolar本地设置 5. 公网访问测试6. 结语 1. 前言 数据共享作为和连接作为互联网的基础应用&#xff0c;不仅在商业和办公场景有…
阅读更多...
SpringBoot操作Mongodb

SpringBoot操作Mongodb

SpringBoot操作Mongodb 引言 MongoDB 特点 1、MongoDB 是一个面向文档存储的数据库&#xff0c;操作起来比较简单和容易。 2、你可以在MongoDB记录中设置任何属性的索引(如&#xff1a;FirstName“Sameer”,Address“8 Gandhi Road”)来实现更快的排序。 3、你可以通过本地…
阅读更多...
DAPP开发（一）——web3.0和智能合约

DAPP开发（一）——web3.0和智能合约

web1.0~3.0&#xff0c;很多年前&#xff0c;互联网没有那么发达&#xff0c;刚出来的时候&#xff0c;仅仅只能在网上浏览观看信息&#xff0c;这就是1.0&#xff0c;后来出现了很多的软件&#xff0c;人们不仅可以浏览还可以发表自己的看法&#xff0c;手机app百花齐放&#…
阅读更多...
最新文章

Copyright @ 2022~2023