函数

分组函数

⚡️ 分组函数：

• 对多行数据进行计算，返回一个结果值。
• 常用于 GROUP BY 或统计分析。
• 作用：对一组数据进行汇总统计。

函数	作用
COUNT()	统计行数
SUM()	计算总和
AVG()	计算平均值
MAX()	求最大值
MIN()	求最小值

使用示例：

-- 建表示例
CREATE TABLE sales (
    id INT,
    product VARCHAR(20),
    amount DECIMAL(10,2)
);

INSERT INTO sales VALUES
(1, '手机', 2000),
(2, '电脑', 5000),
(3, '手机', 3000),
(4, '平板', 2500);

-- 示例1: 求总销售额
SELECT SUM(amount) AS total_sales FROM sales;

-- 示例2: 求平均销售额
SELECT AVG(amount) AS avg_sales FROM sales;

-- 示例3: 按产品分组，统计销售数量 & 总额
SELECT product, COUNT(*) AS sales_count, SUM(amount) AS total
FROM sales
GROUP BY product;

-- 查询平均年龄，聚合函数（分组函数）把多条数组聚合成一条数据
select avg(employee.empage) as "平均年龄" from employee;

-- 统计求和
select sum(employee.empscore) as "绩效总和" from employee;

-- 统计最大的年龄
select max(employee.empage) from employee;

-- 统计最小的年龄
select min(employee.empage) from employee;

-- 统计记录数
-- 统计员工表中总共有多少条记录数，以下两个作用相同
select count(*) as "总记录数" from employee;
select count(1) as "总记录数" from employee;

单行函数

• 作用于每一行，返回一个结果。
• 不改变数据的行数。
• 常用于查询结果的格式化、数据处理。

数值函数

函数	作用
ABS(x)	绝对值
ROUND(x, d)	四舍五入到 d 位
FLOOR(x)	向下取整
CEIL(x)	向上取整
MOD(x,y)	取余

-- 求绝对值
select abs(-123);

-- 四舍五入
select round(3.3);

-- 向下取整
select floor(3.3);

-- 向上取整
select ceil(3.3);

-- 取余
select mod(10,3);

字符串函数

函数	作用
LENGTH(str)	返回字符串长度（字符数）
CONCAT(a,b,...)	拼接字符串
UPPER(str) / LOWER(str)	大小写转换
TRIM(str)	去掉前后空格
SUBSTRING(str,pos,len)	截取字符串
REPLACE(str, from_str, to_str)	替换字符串

🔖 使用示例：

-- 字符串长度
select length('Hello World');

-- 拼接
select concat('Hello',' ','SQL');

-- 转大写
select upper('hello');

-- 去掉前后空格
select trim('   SQL   ');

-- 截取字符串
select substring('abcdef', 2, 3);

-- 替换
select replace('hello world','world','SQL');

日期函数

函数	作用
NOW()	返回当前日期时间
CURDATE() / CURTIME()	当前日期 / 时间
YEAR(date)、MONTH(date)、DAY(date)	提取年、月、日
DATE_ADD(date, INTERVAL n unit)	日期加减
DATEDIFF(d1,d2)	日期差（天数）

🔖 使用示例：

-- 当前日期时间
select now();

-- 只取日期
select curdate();

-- 提取年、月、日
select year(now()), month(now()), day(now());

-- 日期加减
select date_add('2025-08-19', interval 7 day);

-- 日期差
select datediff('2025-08-19','2025-08-01');

系统信息函数

函数	作用
USER()	当前用户
DATABASE()	当前数据库
VERSION()	数据库版本
LAST_INSERT_ID()	最近一次插入的自增 ID

🔖 使用示例：

-- 当前用户
select user();

-- 当前数据库
select database();

-- MySQL 版本
select version();

-- 最近一次插入 ID
insert into test(name) values('abc');
select last_insert_id();

条件判断函数

函数	作用
IF(expr, v1, v2)	条件成立返回 v1，否则 v2
IFNULL(v1, v2)	v1 为 NULL 时返回 v2
CASE WHEN ... THEN ... ELSE ... END	多条件判断

🔖 使用示例：

-- IF 判断
select if(10>5, '大于', '小于');

-- IFNULL
select ifnull(null, '默认值');

-- CASE，使用方式1：条件是一个区间
select 
  case 
    when score >= 90 then '优秀'
    when score >= 60 then '及格'
    else '不及格'
  end as result
from exam;


-- CASE，使用方式2：条件是常量
select 
  case 
    when 100 then '满分'
    when 60 then '及格'
    else '不及格'
  end as result
from exam;

其他函数

函数	作用
UUID()	生成唯一标识符
MD5(str)	计算 MD5 值
RAND()	生成随机数

🔖 使用示例：

-- UUID
select uuid();

-- MD5
select md5('123456');

-- 随机数
select rand();

窗口函数

什么是窗口函数

• 不是浏览器窗口的意思，而是数学/数据库里的“窗口（window）”概念。
• 在数据库里，窗口 = 当前行 + 它周围的一组行，就像透过一扇“滑动的窗口”去看数据子集。

📖 举例说明： 假设有一张学生成绩表（按分数排序）：

学生	分数
张三	95
李四	90
王五	85
赵六	80
钱七	75

如果我们定义窗口：

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

意思是 当前行 + 前一行 + 后一行 就是一个“窗口”。

• 当窗口落在 李四 (90) 时，这个窗口包含的就是：张三(95), 李四(90), 王五(85)。
• 当窗口滑到 王五 (85) 时，窗口包含：李四(90), 王五(85), 赵六(80)。

这个“移动的可见区域”就是 窗口。 窗口函数就是在这个 小范围（window） 内，对每一行进行计算。

🔎 为什么不用“分组函数”这个名字？

因为和 GROUP BY 聚合函数不一样：

• GROUP BY 聚合：缩小行数，一个组只返回一行。
• 窗口函数：不缩小行数，而是在每一行上套一个“窗口”，在窗口范围里做计算，然后结果再对应到这一行。

📝 直观比喻

• GROUP BY → 像是把班级同学分组，统计每组的平均分，只保留一条结果。
• 窗口函数 → 像是给每个学生一个小望远镜（窗口），望远镜范围内的同学分数拿来做运算，但结果还是贴在该学生这行。

作用

• 对每一行进行分析，并且能在分区（Partition） 和 排序（Order） 的基础上返回聚合或排名结果。
• 与普通聚合函数不同：窗口函数不会把多行压缩成一行，而是“在行上再计算”。

函数	作用
ROW_NUMBER()	行号
RANK()	排名（有并列，跳过名次）
DENSE_RANK()	排名（有并列，不跳过名次）
SUM()/AVG()/COUNT() OVER()	窗口聚合
RANK() OVER(...)	给结果集中的每一行分配排名

基本语法

函数名() OVER (
    PARTITION BY 列名 # 类似group by
    ORDER BY 列名 # 排序
    [ROWS | RANGE BETWEEN ...] # 滑动窗口
)

函数名() → 窗口函数，如 ROW_NUMBER(), SUM()

OVER() → 指定窗口（分析范围）

• PARTITION BY → 分组（类似 GROUP BY，但不合并行）
• ORDER BY → 窗口内排序
• ROWS / RANGE → 定义“滑动窗口”范围（可选）

常见函数

函数	说明
ROW_NUMBER()	连续行号（不管是否并列）
RANK()	排名（有并列，排名会跳过）
DENSE_RANK()	排名（有并列，但不会跳过排名）
NTILE(N)	把结果集分成 N 份，标记每行属于第几份
LEAD(expr, n, default)	取当前行往后第 n 行的值
LAG(expr, n, default)	取当前行往前第 n 行的值
FIRST_VALUE(expr)	窗口中第一个值
LAST_VALUE(expr)	窗口中最后一个值
聚合函数 SUM()/AVG()/COUNT()/MAX()/MIN()	在窗口内进行累积或局部聚合

🔖 使用示例：

行号与排名

SELECT name, score,
       ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
       RANK()       OVER (ORDER BY score DESC) AS rank_num,
       DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_num
FROM student;

分区内排名

SELECT class_id, name, score,
       RANK() OVER (PARTITION BY class_id ORDER BY score DESC) AS class_rank
FROM student;

滑动窗口聚合

SELECT name, score,
       SUM(score) OVER (ORDER BY score ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM student;

使用规则 & 注意事项

1. 必须带 OVER() 子句，否则和普通聚合函数没有区别。
2. 不减少行数，结果集的行数与原始数据相同。
3. PARTITION BY 类似于 GROUP BY，但不会合并数据，只是分区后在每个分区内重新计算。
4. ORDER BY 决定计算顺序，对排名类函数尤其重要。
5. 窗口范围 (ROWS/RANGE)：
   • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW → 从起始行到当前行
   • ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING → 前一行、当前行、后一行
   • RANGE 更关注“值范围”，ROWS 关注“行位置”。
6. 性能上比子查询、变量更直观，但大数据量下仍需注意索引和排序开销。

加密函数

函数	作用
MD5(str)	返回字符串的 MD5 128 位哈希值（32位16进制字符串）
SHA(str)	返回字符串的 SHA1 哈希值
SHA2(str, len)	使用 SHA2 算法加密，len=224/256/384/512
PASSWORD(str)	返回 MySQL 内部使用的加密串（不推荐用户自行使用）
ENCODE(str, key)	使用 key 对 str 进行加密
DECODE(str, key)	使用 key 对加密串进行解密
AES_ENCRYPT(str, key)	使用 AES 算法加密
AES_DECRYPT(str, key)	使用 AES 算法解密

🔖 使用示例：

-- 使用 MD5 加密
select md5('123456') as md5_pwd;

-- 使用 SHA1 加密
select sha('hello world') as sha1_val;

-- 使用 SHA2 加密（256位），生成的长度为 256/4 = 64位的字符串
select sha2('hello world', 256) as sha2_val;

-- 使用 AES 加密和解密
select 
  aes_encrypt('secret_data', 'mykey') as encrypted,
  aes_decrypt(aes_encrypt('secret_data', 'mykey'), 'mykey') as decrypted;

-- 使用 ENCODE/DECODE
select 
  encode('testdata','mykey') as encoded,
  decode(encode('testdata','mykey'),'mykey') as decoded;

总结：

• 分组函数：对多行做统计（SUM、COUNT、AVG…）。
• 单行函数：对单行做处理（ABS、SUBSTRING、NOW…）。