MySQL函数(超详细)

tag：「爆裂鼓手」

已于 2024-11-24 18:56:52 修改

阅读量1.8k

点赞数 28

分类专栏： MySQL 文章标签： mysql adb

于 2024-11-16 23:03:10 首次发布

本文链接：https://blog.csdn.net/weixin_44579176/article/details/143825205

版权

MySQL 专栏收录该内容

5 篇文章

订阅专栏

MySQL函数

1.简介

在MySQL中有很多内置函数，除了之前学习的聚合函数之外，还有很多其他内置函数：数值函数、字符串函数、时间日期函数、流程控制函数、加解密函数、开窗函数等。

2.如何查看MySQL函数的说明

2.1 使用官网文档：

https://dev.mysql.com/doc/refman/8.0/en/functions.html

2.2 使用命令

格式: HELP '函数名';

3. MySQL中常用的函数

3.1 数值函数

分类:
		1.小数位数处理
			ROUND、FORMAT、TRUNCATE、FLOOR、CEIL
		2.求余数、求幂、随机数
			MOD、POW、RAND

# =================================== SQL数学相关函数 ===================================

-- 1. 小数位数处理

-- ROUND(X, n)：对 X 进行四舍五入，保留 n 位小数，默认n为0
SELECT ROUND(1.6); #2
SELECT ROUND(1.333, 2);# 1.33
SELECT ROUND(2.689, 2);#2.69
SELECT ROUND(2.689, -1);# 0 注意!! 如果 n 为负数则会 从个位低位到高位将 n 位数字变为 0
SELECT ROUND(2222.689, -3);# 2000


-- FORMAT(X, n)：对 X 进行四舍五入，保留 n 位小数，以  ##,###,###.###格式显示
SELECT FORMAT(1001.6, 2); # 1,001.60
SELECT FORMAT(1123456.333, 2);# 1,123,456.333
SELECT FORMAT(1234567.6896, 4);# 1,234,567.6896
SELECT FORMAT(1234567.6896, 0);# 1,234,568
SELECT FORMAT(1234567.2896, 0);# 1,234,567
SELECT FORMAT(1234567.6896, -1);# 1,234,568 ==>负数跟 0 的作用是一样的


-- FLOOR(x)：向下取整 ==> Returns the largest integer value not greater than X.
SELECT FLOOR(-1.5); # -2
-- CEIL(X)：向上取整
-- ceiling(天花板)
SELECT CEIL(2.1);# 3


-- 2. 求余数、求幂、随机数

-- MOD(X, Y)：求 X 除以 Y 的余数 ==> Modulo operation. Returns the remainder of N divided by M.
SELECT MOD(5, 3); # 2
SELECT MOD(10, 2);# 0

-- POW(X, Y)：X的Y次方 ==> Returns the value of X raised to the power of Y. (X 的 Y次幂)
SELECT POW(2, 3); # 2^3 = 8
SELECT POW(10, 2); # 10 ^ 2 = 100
SELECT POW(10, -1);
# 10 ^ -1 = 0.1

-- RAND()：返回一个0-1之间的随机数，包含0，不包含1
SELECT RAND(); # 返回一个0-1之间的随机数
SELECT RAND(5);# 固定随机数 -->0.40613597483014313
-- 5是随机数种子，随机数种子一样，生成的随机数就固定不变


-- ROUND()
# 需求：计算 每个月的销量 占所有月份销量之和 的百分比(保留2位小数)
# 查询结果字段：
# month、sales、sum(所有月份销售之和)、ratio(每月销量占所有月份销量之和的百分比)
-- 数据准备
CREATE TABLE `tb_sales`
(
    `month` INT(2)  NOT NULL,
    `sales` INT(11) NOT NULL
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

INSERT INTO `tb_sales`
VALUES (1, 10),
       (2, 23),
       (3, 14),
       (4, 5),
       (5, 32),
       (6, 22),
       (7, 52),
       (8, 12),
       (9, 19),
       (10, 36),
       (11, 33),
       (12, 69);

-- 方法一 子查询 完成需求
SELECT *,
       (SELECT SUM(sales) FROM tb_sales)                                      AS sum,
       CONCAT(ROUND(sales / (SELECT SUM(sales) FROM tb_sales) * 100, 2), '%') AS ratio
FROM tb_sales;
# 涉及到 比率且要保留小数位的情况下 建议先 乘100 再舍入小数 ===> ROUND(0.3356)*100% = 0.33 * 100 = 33%
#                                                  ===> ROUND(0.3356 * 100%) = ROUND(33.56) = 33.56%
-- 方法二 开窗函数 完成需求
SELECT *,
       SUM(sales) OVER ()                                        AS sum,
       CONCAT(ROUND((sales / SUM(sales) OVER ()) * 100, 2), '%') AS ratio # ratio 比
FROM tb_sales;

3.2 字符串函数

字符串函数分类
    1)大小写转换
    2）字符串反转、拼接、局部替换
    3）字符串截取
    4）字符串长度及存储长度

-- 1. 大小写转换
-- 转换为小写
SELECT LOWER('Hello World!'); # hello world!
-- 转换为大写
SELECT UPPER('Hello World!'); # HELLO WORLD!

-- 2. 字符串反转、拼接、替换 ==> Returns the string str with the order of the characters reversed.
-- 字符串反转
SELECT REVERSE('123456');# 654321

-- 多个相同字符串拼接 ==> Returns a string consisting of the string str repeated count times.
SELECT REPEAT('象飞田', 3);# 象飞田象飞田象飞田

-- 字符串拼接 => CONCAT(字符串1,字符串2) 
SELECT CONCAT('马走日', '象飞田'); # 马走日象飞田
-- 比较常用的
SELECT CONCAT(10.15, '%');#10.15% 

-- 指定分隔符拼接字符串 => CONCAT_WS(分隔符,字符串1,字符串2)
SELECT CONCAT_WS('^_^', '马走日', '象飞田'); #马走日^_^象飞田
SELECT YEAR(CONCAT_WS('-', '2024', '11', '02')); # 2024-11-02
SELECT CONCAT_WS('-', 'yyyy', 'mm', 'dd');# 2024-11-02

-- 字符串替换 匹配大小写 => REPLACE(字符串,需要替换的字符串,替换值)
SELECT REPLACE('赢赢赢士角炮巡河车赢', '赢', ''); # 士角炮巡河车
SELECT REPLACE('赢赢赢士角炮巡河车赢', '赢', '输');# 输输输士角炮巡河车输
SELECT REPLACE('aAa', 'a', 'B');# BAB

-- 3. 字符串截取
-- SUBSTR(str, n, m)：从 str 字符串的第 n 个字符(注意：n不是下标,而是位置.从 1 开始) 往后截取 m 个字符，返回子串；m可省略，表示截取到末尾。
/* 
==>  For all forms of SUBSTRING(), the position of the first character in the string from which the substring is to be extracted is reckoned as 1.
*/
SELECT SUBSTR('五七炮屏风马', 3, 1); # 炮

SELECT SUBSTR('五七炮屏风马', 4); # 屏风马
SELECT SUBSTRING('五七炮屏风马', 4, 3);# 屏风马
-- SUBSTRING与SUBSTR相同
SELECT SUBSTRING('五七炮屏风马', -4); # 炮屏风马
SELECT SUBSTR('五七炮屏风马', -4);# 炮屏风马
-- 空串 返回 NULL ==> This function is multi-byte safe. If len is less than 1, the result is the empty string
SELECT SUBSTR('', -4); # 看不到效果
SELECT IF(SUBSTR('', -4), 'True', 'Null');# NULL

-- 从左或右截取n个字符  当 截取长度为负 或者 字符串为空时 返回 NULL
-- ==>Returns the leftmost len characters from the string str:str, or NULL if any argument is NULL.
SELECT LEFT('仙人指路，急进中兵', 4); # 仙人指路
SELECT LEFT('仙人指路，急进中兵', -4); # NULL
SELECT RIGHT('仙人指路，急进中兵', 4);# 急进中兵

-- 4. 字符串长度和存储长度
-- 注意!! CHAR_LENGTH(character length) ==> 返回的是字符长度 有几个字就是几个长度
-- LENGTH ==> 返回的是 以字节为单位的 字符串的存储长度 (一个中文占3个字节(长度为 3),一个英文占一个字节)
-- utf8编码格式一个中文占3个字节
SELECT CHAR_LENGTH('仙人指路，急进中兵');# 9
# 字符串索引是 从1开始的
SELECT LENGTH('仙人指路，急进中兵');# 27
SELECT LENGTH('haha,');# 5


-- 示例：编写一个 SQL 查询来修复名字，使得只有第一个字符是大写的，其余都是小写的。
-- 查询结果字段：
--  user_id、name（修复后的名字） ==> 存到表 users_new 中
-- 建表
CREATE TABLE `users`
(
    `user_id` INT(11)     DEFAULT NULL,
    `name`    VARCHAR(40) DEFAULT NULL
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;
-- 插入数据
INSERT INTO `users`
VALUES (1, 'aLice'),
       (2, 'bOB');
-- 查询结果
-- user_id、name（修复后的名字） ==> 存到表 users_new 中
CREATE TABLE users_new AS
SELECT user_id,
       CONCAT(UPPER(LEFT(name, 1)), LOWER(SUBSTR(name, 2)))
FROM users;

3.3 时间日期函数

 时间日期函数分类
    1）获取当前时间的函数，比如当前的年月日或时间戳
    2）计算时间差的相关函数，比如两个日期之间相差多少天，一个日期90天后是几月几号
    3）获取年月日的函数，从一个时间中提取具体的年份、月份
    4）时间转换函数，比如将2021-10-05转换为时间戳

MySQL中的时间日期类型
1.DATETIME类型
格式‌：DATETIME类型的格式为YYYY-MM-DD HH:MM
‌范围‌：DATETIME的范围是’1000-01-01 00:00:00’到’9999-12-31 23:59:59’
‌精度‌：DATETIME的时间部分可以精确到秒。
‌用途‌：适用于需要同时存储日期和时间信息的场景，如记录事件发生的具体时间‌。

2.DATE类型
‌格式‌：DATE类型的格式为YYYY-MM-DD，只包含日期信息。
‌范围‌：DATE的范围是’1000-01-01’到’9999-12-31’
‌精度‌：DATE只存储日期，不包含时间信息‌。
‌用途‌：适用于只需要存储日期信息的场景，如记录生日或会议日期‌。

需要注意的是: 只有满足对应格式的数据才是DATETIME/DATE类型 => 比如:'2001-01' 就不是时间日期类型而是字符串类型了

# =================================== SQL时间日期函数 ===================================
-- 1. 获取当前时间
-- datetime ==>日期+时间    date ==> 日期     time ==> 时分秒
-- 获取当前datetime类型的时间
SELECT NOW(); # 2024-11-05 hh:mm:ss

-- 获取当前date类型的时间
SELECT CURRENT_DATE(); # 2024-11-05

-- 获取当前time类型的时间
SELECT CURRENT_TIME();# hh:mm:ss

-- 2. 计算时间差

-- 计算指定间隔的时间日期
-- DATE_ADD(时间,INTERVAL 数字 单位(年月日时分秒))
SELECT DATE_ADD('2024-9-27', INTERVAL 90 DAY); -- INTERVAL 数字 单位
SELECT DATE_ADD('2024-9-27', INTERVAL -90 DAY);

SELECT DATE_SUB('2024-9-27 ', INTERVAL 90 DAY);
-- 你给什么数据类型 它就返回什么数据类型
SELECT DATE_ADD(NOW(), INTERVAL 3 DAY); # yyyy--MM--dd HH:mm:ss
SELECT DATE_ADD(CURRENT_DATE(), INTERVAL 3 DAY); # yyyy--MM--dd
SELECT DATE_ADD(CURRENT_TIME(), INTERVAL 3 DAY); # HH:mm:ss
SELECT DATE_ADD(CURRENT_TIME(), INTERVAL 3 HOUR);
# HH:mm:ss

-- 示例：获取当前时间3个小时以前的时间
SELECT DATE_ADD(NOW(), INTERVAL -3 HOUR);


-- 计算两个时间日期之间的 天数差         前面 减去 后面
SELECT DATEDIFF('2024-03-22 09:00:00', '2024-03-20 07:00:00');# 2

-- 计算两个时间日期之间的时间差 (自定义时间单位) 后面 减去 前面     (按年比较的时候 不够 365天会显示0年)
SELECT TIMESTAMPDIFF(YEAR, '2024-03-22 09:00:00', '2025-05-20 07:00:00');
SELECT TIMESTAMPDIFF(YEAR, '2002-05-01', '2001-01-01');

-- 示例：使用TIMESTAMPDIFF函数计算当前时间距离1988年10月24日有多少天
SELECT TIMESTAMPDIFF(DAY, '1988-10-24', NOW());
SELECT TIMESTAMPDIFF(YEAR, '2023-03-22 09:00:00', NOW());

-- 3. 提取时间日期中的年月日

-- 获取当前日期中的年份
SELECT YEAR(NOW());# 2024

-- 获取2021-10-02 09:00中月份
SELECT MONTH('2024-10-02 09:00');# 10

-- 获取时间日期中的日
SELECT DAY('2024-10-02 09:00');# 02

-- 提取时间日期中的时分秒
-- 分别运行下面的SQL语句
SELECT HOUR('2024-10-02 09:01:30'); #9
SELECT HOUR('2024-10-02 12:01:30'); # 12 24小时制的

SELECT MINUTE('2024-10-02 09:01:30'); #1
SELECT MINUTE('2024-10-02 09:60:30'); # NULL

SELECT SECOND('2024-10-02 09:01:30');# 30
SELECT SECOND('2024-10-02 09:01:60');# NULL

-- 计算时间日期是星期几(0是周一，1是周二，依次类推)'
SELECT WEEKDAY('2024-10-18');


-- 4. 时间日期转换
-- 时间格式化(时间转字符串)
-- DATE_FORMAT(时间, '转换格式')
SELECT DATE_FORMAT('2024-11-02', '%Y-%m-%d %H:%i:%s'); #2024-11-02 00:00:00
SELECT DATE_FORMAT('2024-11-05', '%Y %M %D %W');# 2024 November 5th Tuesday
# 鼠标悬停函数上可以查 如 : %i Minutes, numeric (00..59) => 表示显示格式为 00-59 单位为分钟
SELECT DATE_FORMAT('2024-11-05', '%Y-%m-%d %H:%i'); #2024-11-05 00:00
SELECT DATE_FORMAT('2024-11-05 20:12', '%Y年%m月%d日 %H时%i分%s秒');#2024年11月05日 20时12分00秒

-- 字符串转时间
SELECT STR_TO_DATE('2024-10-20 16:01:45', '%Y-%m-%d %H:%i:%s'); # 2024-10-20 16:01:45
SELECT STR_TO_DATE('2024-10-20 16:01:45', '%Y-%m-%d');# 2024-10-20
SELECT STR_TO_DATE('2024-10', '%Y-%m');# null => 不是日期格式

-- 时间或字符串转时间戳
-- 时间戳（数字）：某个时间距离UTC时区的1970-01-01 00:00:00 过去了多久（通常以秒或毫秒为单位）。
SELECT UNIX_TIMESTAMP(NOW()); -- 以秒为单位


-- 时间戳转字符串
SELECT FROM_UNIXTIME(1729353600, '%Y-%m-%d %H:%i:%s');

SELECT FROM_UNIXTIME(0, '%Y-%m-%d %H:%i:%s');# 1970-01-01 08:00:00
-- 东八区

-- 获取指定日期那月的最后一天
SELECT LAST_DAY('2024-10-02 09:00');
SELECT LAST_DAY('2024-2-02 09:00');

-- 示例1：查询2020年的登录记录中，每个用户的最后一次登录时间。
-- 查询结果字段
--  user_id、last_stamp（2020年最后一次登录时间）
-- 数据准备
CREATE TABLE `logins`
(
    `user_id`    INT(11)  DEFAULT NULL,
    `time_stamp` DATETIME DEFAULT NULL
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;


INSERT INTO `logins`
VALUES (6, '2020-06-30 15:06:07'),
       (6, '2021-04-21 14:06:06'),
       (6, '2019-03-07 00:18:15'),
       (8, '2020-02-01 05:10:53'),
       (8, '2020-12-30 00:46:50'),
       (2, '2020-01-16 02:49:50'),
       (2, '2019-08-25 07:59:08'),
       (14, '2019-07-14 09:00:00'),
       (14, '2021-01-06 11:59:59');
-- 需求：查询在 2020 年登录过的所有用户的本年度 最后一次 登录时间。
SELECT user_id,
       MAX(time_stamp) AS last_time
FROM logins
WHERE YEAR(time_stamp) = 2020
GROUP BY user_id;

-- 需求：查询在 2020 年登录过的所有用户的 最后一次 登录时间。
SELECT l.user_id, MAX(time_stamp) latestLogin
FROM logins l
         JOIN (SELECT user_id FROM logins WHERE YEAR(time_stamp) = '2020') tmp
              ON tmp.user_id = l.user_id
GROUP BY l.user_id;


-- 示例2：查询温度比前一天（昨天）温度高的记录id
-- 查询结果字段：id
-- 数据准备
CREATE TABLE `weather`
(
    `id`          INT(11) DEFAULT NULL,
    `recordDate`  DATE    DEFAULT NULL,
    `temperature` INT(11) DEFAULT NULL
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

INSERT INTO `weather`
VALUES (1, '2015-01-01', 10),
       (2, '2015-01-02', 25),
       (3, '2015-01-03', 20),
       (4, '2015-01-04', 30);

-- 查询结果
SELECT a.id
FROM weather a
         JOIN weather b
              ON DATEDIFF(a.recorddate, b.recorddate) = 1
WHERE a.temperature > b.temperature;

3.4 两个条件判断语句 CASE-WHEN 和 IF

# =================================== CASE WHEN条件判断 ===================================
-- 01.CASE WHEN基本使用
-- 示例1
-- 需求：查询所有学生的成绩信息，并将学生的成绩分成5个等级，查询结果中需要有一个学生的成绩等级列，
-- 成绩等级如下：
-- 优秀：90分及以上
-- 良好：80-90，包含80
-- 中等：70-80，包含70
-- 及格：60-70，包含60
-- 不及格：60分以下
-- 查询结果字段：
--  name(姓名)、course(科目)、score(成绩)、grade(成绩等级)
-- 数据准备
CREATE TABLE `tb_score`
(
    `name`   VARCHAR(24)   NOT NULL,
    `course` VARCHAR(24)   NOT NULL,
    `score`  DECIMAL(5, 2) NOT NULL
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

INSERT INTO `tb_score`
VALUES ('张三', '语文', 81.00),
       ('张三', '数学', 75.00),
       ('李四', '语文', 76.00),
       ('李四', '数学', 90.00),
       ('王五', '语文', 81.00),
       ('王五', '数学', 100.00);
-- 查询结果
SELECT *,
       CASE
           WHEN score > 100 THEN '作弊'
           WHEN score >= 90 THEN '优秀'
           WHEN score >= 80 THEN '良好'
           WHEN score >= 70 THEN '中等'
           WHEN score >= 60 THEN '及格'
           ELSE '不及格'
           END AS 'grade'
FROM tb_score;



-- 分组条件计数
-- 示例1：统计不同科目中，成绩在90分以上(包含90)和90分以下的人数各有多少
-- 查询结果字段：
-- course(科目)、gte_90(该科目90分以上是学生人数)、lt_90(该科目90分以下的学生人数)

-- 示例1：统计不同科目中，成绩在90分以上(包含90)和90分以下的人数各有多少
# 方式1
SELECT course,
       COUNT(
               CASE WHEN score >= 90 THEN 1 ELSE NULL END
           ) AS gte_90,
       COUNT(
               CASE WHEN score < 90 THEN 1 ELSE NULL END
           ) AS lt_90
FROM tb_score
GROUP BY course;

--当然这题也可以用if来做 思想和行转列有点类似 =>下面会讲到
# 方式2 IF()
SELECT course,
       COUNT(IF(score >= 90, '优秀', NULL)) gte_90,
       COUNT(IF(score < 90, '优秀', NULL))  lt_90
FROM tb_score
GROUP BY course;



-- 02.IF判断语句
-- IF(条件, 值1, 值2)：条件成立，IF的结果就是值1，否则结果就是值2
-- 注意：CASE...END中只有两种条件选择时，才可以使用IF函数替代
-- 条件更新
-- 语法：UPDATE 表名 SET 列名=值 [WHERE 条件];

-- 示例1：实现一条update更新商品的价格，要求价格高于3000（包含）的降价10%，低于3000的上涨20%
-- 使用：product数据表
-- 数据准备
CREATE TABLE `product`
(
    `Product` VARCHAR(100)   DEFAULT NULL,
    `Price`   DECIMAL(10, 2) DEFAULT NULL
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;


INSERT INTO `product`
VALUES ('笔记本', 3050.00),
       ('手机', 2800.00),
       ('台式电脑', 2050.00);

UPDATE product
SET Price = IF(price > 3000, Price * 0.9, Price * 1.2);



-- 扩展：面试题
-- 示例2：多行变一行（行转列）
-- 注意：只要是多行变一行，首先要想到的就是分组聚合操作。
-- 使用：score数据表
CREATE TABLE `score`
(
    `学号` VARCHAR(24) DEFAULT NULL,
    `科目` VARCHAR(24) DEFAULT NULL,
    `成绩` INT(11)     DEFAULT NULL
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

INSERT INTO `score`
VALUES ('s001', '语文', 90),
       ('s001', '数学', 100),
       ('s001', '英语', 93),
       ('s002', '语文', 98),
       ('s002', '数学', 99),
       ('s002', '英语', 96);

-- 模板
CREATE TABLE score_multi_row
SELECT 学号,
       SUM(IF(科目 = '语文', 成绩, 0)) AS 语文,
       SUM(IF(科目 = '数学', 成绩, 0)) AS 数学,
       SUM(IF(科目 = '英语', 成绩, 0)) AS 英语
FROM score
GROUP BY 学号;


-- 示例3：一行变多行（列转行）
-- 使用：w_score数据表
CREATE TABLE `w_score`
(
    `学号` VARCHAR(24) DEFAULT NULL,
    `语文` BIGINT(11)  DEFAULT NULL,
    `数学` BIGINT(11)  DEFAULT NULL,
    `英语` BIGINT(11)  DEFAULT NULL
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

INSERT INTO `w_score`
VALUES ('s001', 90, 100, 93),
       ('s002', 98, 99, 96);

-- 刚开始看答案会晕,不妨请先理解这几条语句
SELECT '语文' FROM w_score; # 创建了一个字段 '语文',并且拿了这个字段的值进行作为该字段的记录填充了.. =>也就是说'语文'既是字段值也是字段名
SELECT '语文' AS 科目 FROM w_score; # 在上面的基础上给'语文'起了别名 =>科目
SELECT 学号 FROM w_score; 
SELECT 语文 AS 成绩 FROM w_score;

-- 查询
SELECT 学号,
       '语文' AS 科目,
       语文   AS 成绩
FROM w_score
UNION
SELECT 学号,
       '数学' AS 科目,
       语文   AS 成绩
FROM w_score
UNION
SELECT 学号,
       '英语' AS 科目,
       语文   AS 成绩
FROM w_score
ORDER BY 学号, FIELD(科目, '语文', '数学', '英语');
# FIELD(字段名,字符串1,字符串2.....) ==> 根据你给出的顺序进行排序

# =================================== 开窗函数 ===================================
# ----------------------------------- 1. 窗口函数简介 -----------------------------------

-- 窗口函数是 MySQL8.0 以后加入的功能，之前需要通过定义临时变量和大量的子查询才能完成的工作，使用窗口函数实现起来更加简洁高效

-- 示例1：针对 students 表的数据，计算每个同学的Score分数和整体平均分数的差值
-- 数据准备
CREATE TABLE `students`
(
    `ID`     INT(11)       NOT NULL AUTO_INCREMENT,
    `Name`   VARCHAR(24)   NOT NULL,
    `Gender` VARCHAR(8)    NOT NULL,
    `Score`  DECIMAL(5, 2) NOT NULL,
    PRIMARY KEY (`ID`)
);


INSERT INTO `students`
VALUES (1, 'smart', 'Male', 90.00),
       (2, 'linda', 'Female', 81.00),
       (3, 'lucy', 'Female', 83.00),
       (4, 'david', 'Male', 94.00),
       (5, 'Tom', 'Male', 92.00),
       (6, 'Jack', 'Male', 88.00);

-- 计算每个同学的Score分数和整体平均分数的差值
SELECT *,
       AVG(Score) OVER ()                   AS avg,
       ROUND(Score - AVG(Score) OVER (), 2) AS diff_avg
FROM students;



# ------------------------------------ 2. 窗口函数基础用法 -----------------------------------
-- 窗口函数的作用是在处理每行数据时，针对每一行关联的一组数据进行处理。

-- 基础语法：<window function> OVER(...)
-- <window function> 表示使用的窗口函数，窗口函数可以使用之前已经学过的聚合函数，比如COUNT()、SUM()、AVG()等，也可以是其他函数，比如 ranking 排序函数等，后面的课程中会介绍
-- OVER(...)的作用就是设置每行数据关联的窗口数据范围，OVER()时，每行关联的数据范围都是整张表的数据。

-- SQL示例

SELECT id,
       name,
       gender,
       score,
       -- OVER()：表示每行关联的窗口数据范围都是整张表的数据
       -- AVG(Score)：表示处理每行数据时，应用 AVG 对每行关联的窗口数据中的 Score 求平均
       AVG(score) OVER () AS `AVG_Score`
FROM students;



-- 典型应用场景1：计算每个值和整体平均值的差值

-- 示例1
# 需求：计算每个学生的 Score 分数和所有学生整体平均分的差值。
# 查询结果字段：
#   ID、Name、Gender、Score、AVG_Score(学生整体平均分)、difference(每位学生分数和整体平均分的差值)

SELECT *,
       AVG(score) OVER ()         AS avg_score,
       score - AVG(score) OVER () AS diff
FROM students;


-- 典型应用场景2：计算每个值占整体之和的占比

# 需求：计算每个学生的Score分数占所有学生分数之和的百分比
# 查询结果字段：
#   ID、Name、Gender、Score、sum(所有学生分数之和)、ratio(每位学生分数占所有学生分数之和的百分比)

SELECT *,
       SUM(score) OVER () AS sum_score,
       score / SUM(score) OVER () * 100
FROM students;



# ----------------------------------- 3. PARTITION BY分区 -----------------------------------

-- 基本语法：<window function> OVER(PARTITION BY 列名, ...)
-- PARTITION BY 列名, ...的作用是按照指定的列对整张表的数据进行分区
-- 分区之后，在处理每行数据时，<window function>是作用在该行数据关联的分区上，不再是整张表上

-- SQL 示例

SELECT id,
       name,
       gender,
       score,
       -- PARTITION BY Gender：按照性别对整张表的数据进行分区，此处会分成2个区
       -- AVG(Score)：处理每行数据时，应用 AVG 对该行关联分区数据中的 Score 求平均
       AVG(score) OVER (PARTITION BY gender) AS `Avg`
FROM students;

-- 应用示例

-- 示例1
-- 需求：计算每个学生的 Score 分数和同性别学生平均分的差值
-- 查询结果字段：
--  ID、Name、Gender、Score、Avg(同性别学生的平均分)、difference(每位学生分数和同性别学生平均分的差值)


SELECT *,
       AVG(score) OVER (PARTITION BY gender)         AS gender_score,
       score - AVG(score) OVER (PARTITION BY gender) AS diff
FROM students;


-- 示例2  参考tb_score表
-- 需求：计算每人各科分数与对应科目最高分的占比
-- 查询结果字段：
--  name、course、score、max(对应科目最高分数)、ratio(每人各科分数与对应科目最高分的占比)


SELECT *,
       MAX(score) OVER (PARTITION BY course)               AS max_score,
       score / MAX(score) OVER (PARTITION BY course) * 100 AS ratio
FROM tb_score;



-- 补充：PARTITION BY 和 GROUP BY的区别
-- 使用场景不同
-- PARTITION BY用在窗口函数中，结果是：一进一出
-- GROUP BY用在分组聚合中，结果是：多进一出

-- 分组聚合：统计每个科目的最高分
SELECT course,
       MAX(score) AS `max`
FROM tb_score
GROUP BY course;

-- 窗口函数：查询每行数据时，都对这行所属科目求了一个最高分
SELECT name,
       course,
       score,
       MAX(score) OVER (PARTITION BY course) AS `max`
FROM tb_score;


# ----------------------------------- 4. 排名函数 -----------------------------------

-- 基本语法：<ranking function> OVER (ORDER BY 列名, ...)
-- OVER() 中可以指定 ORDER BY 按照指定列对每一行关联的分区数据进行排序，然后使用排序函数对分区内的每行数据产生一个排名序号

-- SQL 示例
SELECT name,
       course,
       score,
       -- 此处 OVER() 中没有 PARTITION BY，所以整张表就是一个分区
       -- ORDER BY score DESC：按照 score 对每个分区内的数据降序排序
       -- RANK() 窗口函数的作用是对每个分区内的每一行产生一个排名序号
       RANK() OVER (ORDER BY score DESC) AS `rank`
FROM tb_score;

-- 排名函数
-- RANK()：产生的排名序号 ，有并列的情况出现时序号不连续  ==>1224
-- DENSE_RANK() ：产生的排序序号是连续的，有并列的情况出现时序号会重复 ==>1223
-- ROW_NUMBER() ：返回连续唯一的行号，排名序号不会重复 ==>1234

-- PARTITION BY和排序函数配合

-- 示例1：
-- 需求：按照不同科目，对学生的分数从高到低进行排名(要求：连续可重复)
-- 查询结果字段：
--  name、course、score、dense_rank(排名序号)

SELECT *,
       DENSE_RANK() OVER (PARTITION BY course ORDER BY score DESC) AS dr
FROM tb_score;




-- 典型应用：获取指定排名的数据
-- 示例2
-- 需求：获取每个科目，排名第二的学生信息
-- 查询结果字段：
--  name、course、score

SELECT *
FROM (SELECT *,
             DENSE_RANK() OVER (PARTITION BY course ORDER BY score DESC) AS rk
      FROM tb_score) tb_rk
WHERE tb_rk.rk = 2;


-- CTE(公用表表达式)
-- CTE(公用表表达式)：Common Table Expresssion，类似于子查询，相当于一张临时表，可以在 CTE 结果的基础上，进行进一步的查询操作。
-- 基础语法
/*
WITH tmp1 AS (
    -- 查询语句
), tmp2 AS (
    -- 查询语句
), tmp3 AS (
    -- 查询语句
)
SELECT
    字段名
FROM 表名;
*/

-- 示例1
-- 需求：获取每个科目，排名第二的学生信息
-- 查询结果字段：
--  name、course、score
WITH temp AS (SELECT *,
                     DENSE_RANK() OVER (PARTITION BY course ORDER BY score DESC) AS dr
              FROM tb_score)

SELECT *
FROM temp
WHERE dr = 2;



-- NTILE(X)窗口函数：将每个分区的数据均匀的分成X组，返回每行对应的组号

-- 示例1：将所有的拍卖信息按照浏览次数排序，并均匀分成4组，添加组号
-- 查询结果字段：
--  id、views(浏览次数)、quartile(分组序号)
-- 数据准备
CREATE TABLE `auction`
(
    `id`           INT(11) NOT NULL,
    `category_id`  INT(11)        DEFAULT NULL,
    `asking_price` DECIMAL(10, 2) DEFAULT NULL,
    `final_price`  DECIMAL(10, 2) DEFAULT NULL,
    `views`        INT(11)        DEFAULT NULL,
    `participants` INT(11)        DEFAULT NULL,
    `country`      VARCHAR(50)    DEFAULT NULL,
    `ended`        DATE           DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE
);


INSERT INTO `auction`
VALUES (1, 1, 190.07, 219.66, 93, 16, 'Spain', '2017-01-05'),
       (2, 4, 34.70, 54.70, 187, 31, 'Spain', '2017-01-05'),
       (3, 5, 124.85, 155.95, 237, 59, 'Spain', '2017-01-05'),
       (4, 3, 141.42, 205.73, 289, 58, 'Spain', '2017-01-06'),
       (5, 2, 31.11, 66.45, 165, 83, 'Spain', '2017-01-09'),
       (6, 3, 106.18, 125.07, 27, 3, 'Spain', '2017-01-06'),
       (7, 2, 124.83, 150.93, 266, 53, 'Spain', '2017-01-06'),
       (8, 1, 151.71, 164.54, 158, 53, 'Spain', '2017-01-08'),
       (9, 4, 51.44, 87.02, 235, 59, 'France', '2017-01-05'),
       (10, 2, 118.97, 178.21, 191, 38, 'France', '2017-01-05'),
       (11, 5, 38.50, 69.61, 44, 7, 'France', '2017-01-06'),
       (12, 4, 20.87, 35.57, 298, 37, 'France', '2017-01-08'),
       (13, 2, 56.45, 112.42, 267, 45, 'Germany', '2017-01-05'),
       (14, 3, 189.20, 242.16, 234, 33, 'Germany', '2017-01-06'),
       (15, 2, 43.15, 88.01, 92, 12, 'Germany', '2017-01-06'),
       (16, 5, 158.92, 179.18, 17, 2, 'Germany', '2017-01-06'),
       (17, 1, 64.55, 129.46, 155, 78, 'UK', '2017-01-05'),
       (18, 4, 196.07, 237.86, 63, 21, 'UK', '2017-01-05'),
       (19, 2, 171.26, 190.57, 194, 39, 'UK', '2017-01-06'),
       (20, 3, 157.81, 206.63, 218, 31, 'Italy', '2017-01-05'),
       (21, 2, 135.16, 197.43, 47, 12, 'Italy', '2017-01-07'),
       (22, 4, 172.98, 197.07, 297, 42, 'Italy', '2017-01-06'),
       (23, 5, 163.89, 218.99, 90, 18, 'Italy', '2017-01-09'),
       (24, 3, 115.76, 137.49, 136, 19, 'Italy', '2017-01-06'),
       (25, 3, 149.89, 208.09, 25, 3, 'Italy', '2017-01-07');


-- 查询
SELECT id,
       views,
       -- 按照浏览量降序，然后把分区数据划分为4组，产生每一行对应的组序号
       NTILE(4) OVER (ORDER BY views DESC) AS `quartile`
FROM auction;

-- 示例2：获取浏览次数前四份之一的拍卖信息。
-- 查询结果字段
--  id、views(浏览次数)
WITH temp AS (
    SELECT id,
           views,
           -- 按照浏览量降序，然后把分区数据划分为4组，产生每一行对应的组序号
           NTILE(4) OVER (ORDER BY views DESC) AS `quartile`
    FROM auction
)
SELECT id, views
FROM temp
WHERE quartile = 1;



-- LAG(字段, [N], [M])：返回分区中当前行前第N行的指定字段的内容，如果没有，默认返回M
-- 注意：M和N可以省略，N默认为1，M默认为NULL。
SELECT *,
       -- 等效于：LAG(sales) OVER(ORDER BY month) AS `next_sales`
       LAG(sales) OVER (ORDER BY month) AS `pre_sales`
FROM tb_sales;

-- LEAD(字段, [N], [M])：返回分区中当前行后第N行的指定字段的内容，如果没有，默认返回M
SELECT *,
       -- 等效于：LEAD(sales) OVER(ORDER BY month) AS `next_sales`
       LEAD(sales, 1, NULL) OVER (ORDER BY month) AS `next_sales`
FROM tb_sales;

-- 示例1：计算每个月销量和上个月销量的差值
-- month、sales(当前销量)、pre_sales(上月销量)、difference(当月销量和上月销量的差值）

-- 自关联方式

-- 开窗函数方式
SELECT *,
       LAG(sales, 1, NULL) OVER (ORDER BY month)         pre_sal,
       sales - LAG(sales, 1, NULL) OVER (ORDER BY month) diff
FROM tb_sales;


-- 示例2：计算每个月销量和下一个月销量的差值
-- month、sales(当前销量)、next_sales(下月销量)、difference(当月销量和下月销量的差值）

-- 自关联

-- 开窗函数方式
HELP 'LEAD';
SELECT *,
       LEAD(sales, 1, NULL) OVER (ORDER BY month)         next_sal,
       sales - LEAD(sales, 1, NULL) OVER (ORDER BY month) diff
FROM tb_sales;

完成上面的代码后一起来做一道牛客上的热门题目 #SQL280 实习广场投递简历分析(三),描述如下:

/*
在牛客实习广场有很多公司开放职位给同学们投递，同学投递完就会把简历信息存到数据库里。
现在有简历信息表(resume_info),表结构如下所示
*/

drop table if exists resume_info; # 保证建表成功
# 01-简历信息表
CREATE TABLE resume_info (
id int(4) NOT NULL,
job varchar(64) NOT NULL,
date date NOT NULL,
num int(11) NOT NULL,
PRIMARY KEY (id));
# 02- 数据
INSERT INTO resume_info VALUES
(1,'C++','2025-01-02',53),
(2,'Python','2025-01-02',23),
(3,'Java','2025-01-02',12),
(4,'C++','2025-01-03',54),
(5,'Python','2025-01-03',43),
(6,'Java','2025-01-03',41),
(7,'Java','2025-02-03',24),
(8,'C++','2025-02-03',23),
(9,'Python','2025-02-03',34),
(10,'Java','2025-02-04',42),
(11,'C++','2025-02-04',45),
(12,'Python','2025-02-04',59),
(13,'C++','2026-01-04',230),
(14,'Java','2026-01-04',764),
(15,'Python','2026-01-04',644),
(16,'C++','2026-01-06',240),
(17,'Java','2026-01-06',714),
(18,'Python','2026-01-06',624),
(19,'C++','2026-02-14',260),
(20,'Java','2026-02-14',721),
(21,'Python','2026-02-14',321),
(22,'C++','2026-02-24',134),
(23,'Java','2026-02-24',928),
(24,'Python','2026-02-24',525),
(25,'C++','2027-02-06',231);

id	job	date	num
1	C++	2025-01-02	53
2	Python	2025-01-02	23
3	Java	2025-01-02	12
4	C++	2025-01-03	54
5	Python	2025-01-03	43
6	Java	2025-01-03	41
7	Java	2025-02-03	24
8	C++	2025-02-03	23
9	Python	2025-02-03	34
10	Java	2025-02-04	42
11	C++	2025-02-04	45
12	Python	2025-02-04	59
13	C++	2026-01-04	230
14	Java	2026-01-04	764
15	Python	2026-01-04	644
16	C++	2026-01-06	240
17	Java	2026-01-06	714
18	Python	2026-01-06	624
19	C++	2026-01-04	260
20	Java	2026-02-14	721
21	Python	2026-02-14	321
22	C++	2026-02-14	134
23	Java	2026-02-24	928
24	Python	2026-02-24	525
25	C++	2027-02-06	231

第1行表示，在2025年1月2号，C++岗位收到了53封简历

…

最后1行表示，在2027年2月6号，C++岗位收到了231封简历

请你写出SQL语句查询在2025年投递简历的每个岗位，每一个月内收到简历的数目，和对应的2026年的同一个月同岗位，收到简历的数目，最后的结果先按first_year_mon月份降序，再按job降序排序显示，以上例子查询结果如下:

job	first_year_mon	first_year_cnt	second_year_mon	second_year_cnt
Python	2025-02	93	2026-02	846
Java	2025-02	66	2026-02	1649
C++	2025-02	68	2026-02	394
Python	2025-01	66	2026-01	1268
Java	2025-01	53	2026-01	1478
C++	2025-01	107	2026-01	470

# 方法一 分别获取 2025,2026年度各月份各岗位 收到简历的情况 => 合并
WITH firstyear AS(
SELECT  job,
        DATE_FORMAT(`date`,'%Y-%m') year_mon,
        SUM(num) year_cnt
FROM resume_info
WHERE YEAR(`date`) = '2025'
GROUP BY job,DATE_FORMAT(`date`,'%Y-%m')),
 secondyear AS(
     SELECT  job,
            DATE_FORMAT(`date`,'%Y-%m') year_mon,
            SUM(num) year_cnt
    FROM resume_info
    WHERE YEAR(`date`) = '2026'
    GROUP BY job,DATE_FORMAT(`date`,'%Y-%m'))
SELECT fy.job job, fy.year_mon first_year_mon, fy.year_cnt first_year_cnt,sy.year_mon second_year_mon,sy.year_cnt second_year_cnt
FROM firstyear fy
        JOIN secondyear sy ON
            fy.job = sy.job AND
           	RIGHT(fy.year_mon,2) = RIGHT(sy.year_mon,2) #注意这里不能用MONTH函数了前面讲过原因了
ORDER BY first_year_mon DESC ,fy.job DESC;

# 方法二 行转列 + CTE表达式
WITH first_year AS
(SELECT  job,
        MAX(IF(YEAR(`date`) = '2025',DATE_FORMAT(`date`,'%Y-%m'),NULL)) first_year_mon,
        SUM(IF(YEAR(`date`) = '2025',num,NULL)) first_year_cnt
FROM resume_info
GROUP BY job,DATE_FORMAT(`date`,'%Y-%m')
HAVING first_year_mon IS NOT NULL),
second_year AS
(SELECT  job,
        MAX(IF(YEAR(`date`) = '2026',DATE_FORMAT(`date`,'%Y-%m'),NULL)) second_year_mon,
        SUM(IF(YEAR(`date`) = '2026',num,NULL)) second_year_cnt
FROM resume_info
GROUP BY job,DATE_FORMAT(`date`,'%Y-%m')
HAVING second_year_cnt IS NOT NULL)
SELECT f.*,s.second_year_mon,s.second_year_cnt
FROM first_year f
    JOIN second_year s ON f.job = s.job AND RIGHT(f.first_year_mon,2) = RIGHT(s.second_year_mon,2)
ORDER BY first_year_mon DESC ,f.job DESC ;