Node系列 · 数据库：函数和分组

SQL 函数是把数据"加工"成需要的结果的关键工具。本章按"数学 / 聚合 / 字符 / 日期"分类讲解内置函数，再讲自定义函数的创建与限制，最后回到 GROUP BY + HAVING 实战。

一、SQL 函数分类

类别	处理对象	典型函数
数学函数	数字	`ROUND` / `CEIL` / `FLOOR` / `ABS` / `MOD` / `RAND`
聚合函数	一组行	`COUNT` / `SUM` / `AVG` / `MAX` / `MIN`
字符函数	字符串	`CONCAT` / `SUBSTRING` / `LENGTH` / `UPPER` / `LOWER` / `TRIM` / `REPLACE`
日期函数	时间	`NOW` / `CURDATE` / `DATE_FORMAT` / `DATEDIFF` / `DATE_ADD`
条件函数	表达式	`IF` / `CASE` / `IFNULL` / `COALESCE`
类型转换	任意	`CAST` / `CONVERT`

二、数学函数

sql

SELECT
  ROUND(3.14159, 2),     -- 3.14       四舍五入，2 位小数
  CEIL(3.14),            -- 4          向上取整
  FLOOR(3.99),           -- 3          向下取整
  ABS(-10),              -- 10         绝对值
  MOD(10, 3),            -- 1          取模（10 % 3）
  RAND(),                -- 0.xxxxx    随机数 [0, 1)
  POW(2, 10),            -- 1024       幂
  SQRT(16);              -- 4          平方根

三、聚合函数

聚合函数上一章已讲过，补充几个常见用法：

sql

-- COUNT(*) vs COUNT(col) vs COUNT(DISTINCT col)
SELECT
  COUNT(*)              AS `总行数`,
  COUNT(`phone`)        AS `有手机号的行数`,
  COUNT(DISTINCT `class_id`) AS `班级去重数`
FROM `student`;

-- 聚合同时算多指标
SELECT
  COUNT(*)               AS `人数`,
  SUM(`score`)           AS `总分`,
  AVG(`score`)           AS `平均分`,
  MAX(`score`)           AS `最高分`,
  MIN(`score`)           AS `最低分`
FROM `score`
WHERE `subject` = '数学';

四、字符函数

sql

SELECT
  CONCAT('张', '三'),                      -- '张三'  拼接
  CONCAT_WS('-', '2024', '01', '15'),       -- '2024-01-15'  带分隔符
  SUBSTRING('Hello World', 1, 5),           -- 'Hello'  截取
  LENGTH('张三'),                           -- 6（UTF-8 占 3 字节 × 2）
  CHAR_LENGTH('张三'),                       -- 2  字符数
  UPPER('hello'),                           -- 'HELLO'
  LOWER('WORLD'),                           -- 'world'
  TRIM('  hello  '),                        -- 'hello'  去首尾空格
  REPLACE('hello world', 'world', 'Node'),  -- 'hello Node'
  LEFT('hello', 3),                         -- 'hel'    左取
  RIGHT('hello', 3);                        -- 'llo'    右取

WARNING

LENGTH() 返回字节数（UTF-8 中文 3 字节）；CHAR_LENGTH() 返回字符数。涉及中文长度的逻辑一定用 CHAR_LENGTH。

五、日期函数

sql

SELECT
  NOW(),                                  -- 当前时间（含时分秒）
  CURDATE(),                              -- 当前日期
  CURTIME(),                              -- 当前时间
  DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'),  -- '2024-08-15 14:30:00'
  DATE_FORMAT(NOW(), '%Y年%m月%d日'),       -- '2024年08月15日'
  DATEDIFF('2024-12-31', '2024-01-01'),     -- 335   日期差（天）
  DATE_ADD('2024-01-01', INTERVAL 30 DAY), -- '2024-01-31'  加 30 天
  DATE_SUB('2024-01-01', INTERVAL 1 MONTH), -- '2023-12-01' 减 1 月
  YEAR(NOW()), MONTH(NOW()), DAY(NOW()),
  UNIX_TIMESTAMP(NOW()),                   -- 1716310200  秒级时间戳
  FROM_UNIXTIME(1716310200);               -- 转回日期

常用日期格式符：

格式符	含义	示例
`%Y`	4 位年	2024
`%m`	2 位月	08
`%d`	2 位日	15
`%H`	24 小时	14
`%i`	分钟	30
`%s`	秒	00

六、条件函数

sql

-- IF：三目运算符
SELECT IF(`score` >= 60, '及格', '不及格') AS `result` FROM `score`;

-- IFNULL：处理 NULL
SELECT IFNULL(`phone`, '未填写') FROM `student`;

-- COALESCE：返回第一个非 NULL
SELECT COALESCE(`phone`, `email`, '无可用联系方式') FROM `student`;

-- CASE WHEN：复杂分支
SELECT
  `name`,
  CASE
    WHEN `score` >= 90 THEN '优秀'
    WHEN `score` >= 80 THEN '良好'
    WHEN `score` >= 60 THEN '及格'
    ELSE '不及格'
  END AS `等级`
FROM `score`;

CASE WHEN 比 IF 更强大——支持多分支和范围判断，是 SQL 里写业务规则最常用的工具。

七、自定义函数

MySQL 允许用 SQL 写自定义函数（UDF）：

7.1 创建函数

sql

DELIMITER $$

CREATE FUNCTION `calculate_age`(`birthday` DATE)
RETURNS INT
DETERMINISTIC
BEGIN
  RETURN TIMESTAMPDIFF(YEAR, `birthday`, CURDATE());
END$$

DELIMITER ;

参数说明：

子句	含义
`RETURNS INT`	返回类型
`DETERMINISTIC`	确定性函数（输入相同输出相同），用于查询优化
`BEGIN ... END`	函数体（多条语句用分号，必须改 delimiter）

7.2 使用函数

sql

SELECT `name`, `birthday`, calculate_age(`birthday`) AS `age`
FROM `student`;

7.3 删除函数

sql

DROP FUNCTION `calculate_age`;

WARNING

MySQL 自定义函数有严格限制：

不能引用表（只能用 SELECT INTO 赋值局部变量）
不能修改数据库
只能返回单一标量值
复杂逻辑应该放在应用层（Node / Java 代码）

八、GROUP BY 高级用法

8.1 多列分组

sql

-- 每个班级每科的平均分
SELECT `class_id`, `subject`, AVG(`score`) AS `avg_score`
FROM `student` s
JOIN `score` sc ON sc.student_id = s.id
GROUP BY `class_id`, `subject`
ORDER BY `class_id`, `subject`;

8.2 WITH ROLLUP 汇总行

sql

SELECT `class_id`, `subject`, SUM(`score`) AS `total`
FROM `score`
GROUP BY `class_id`, `subject` WITH ROLLUP;

WITH ROLLUP 在分组结果末尾追加一行汇总。class_id = NULL 那行就是全部总和。

8.3 HAVING 高级用法

sql

-- 找出每个班级数学成绩前 3 的学生
SELECT `class_id`, `name`, `score`
FROM (
  SELECT
    s.`class_id`, s.`name`, sc.`score`,
    ROW_NUMBER() OVER (PARTITION BY s.`class_id` ORDER BY sc.`score` DESC) AS `rank`
  FROM `student` s
  JOIN `score` sc ON sc.student_id = s.id
  WHERE sc.`subject` = '数学'
) AS t
WHERE t.`rank` <= 3;

MySQL 8.0+ 支持窗口函数（ROW_NUMBER / RANK / DENSE_RANK / LAG / LEAD），能在不破坏行结构的前提下做排序和聚合。

九、SQL 执行顺序

理解 SQL 执行顺序，能解释为什么 SELECT 里给字段起的别名 WHERE 不能用：

sql

SELECT `score` * 2 AS `double_score`
FROM `score`
WHERE `double_score` > 100;  -- ❌ 报错：unknown column 'double_score'

实际执行顺序：

WHERE 在 SELECT 之前执行——所以 WHERE 不能用 SELECT 起的别名。

TIP

HAVING 能用 SELECT 别名——因为 HAVING 在 SELECT 之后执行。但这依赖具体数据库实现，不推荐这种写法（可读性差）。改用子查询或 WITH 子句更清晰。

十、最佳实践

场景	推荐
数学计算	用 `ROUND` / `CEIL` / `FLOOR` 而非应用层处理
字符串拼接	`CONCAT_WS`（带分隔符版本）
中文长度	`CHAR_LENGTH` 而非 `LENGTH`
日期格式化	`DATE_FORMAT`，格式符固定
NULL 处理	`IFNULL` / `COALESCE`
业务规则	`CASE WHEN` 比 `IF` 链更可读
自定义函数	谨慎用；复杂逻辑放应用层
SQL 顺序	别名只在 ORDER BY / HAVING 可用（不推荐）

十一、小结

函数分类：数学 / 聚合 / 字符 / 日期 / 条件 / 类型转换
聚合函数上一章已讲；本节补充 COUNT 三种形式和 SUM/AVG/MAX/MIN
中文长度用 CHAR_LENGTH（字符数）而非 LENGTH（字节数）
日期处理用 DATE_FORMAT / DATEDIFF / DATE_ADD，避免应用层解析
条件分支 CASE WHEN 比 IF 链更强大
自定义函数有严格限制，复杂逻辑放应用层
SQL 执行顺序：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Node系列 · 数据库：函数和分组 ​

一、SQL 函数分类 ​

二、数学函数 ​

三、聚合函数 ​

四、字符函数 ​

五、日期函数 ​

六、条件函数 ​

七、自定义函数 ​

7.1 创建函数 ​

7.2 使用函数 ​

7.3 删除函数 ​

八、GROUP BY 高级用法 ​

8.1 多列分组 ​

8.2 WITH ROLLUP 汇总行 ​

8.3 HAVING 高级用法 ​

九、SQL 执行顺序 ​

十、最佳实践 ​

十一、小结 ​