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

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二、数学函数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 平方根三、聚合函数聚合函数上一章已讲过补充几个常见用法-- 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 数学;四、字符函数SELECT CONCAT(张, 三), -- 张三 拼接 CONCAT_WS(-, 2024, 01, 15), -- 2024-01-15 带分隔符 SUBSTRING(Hello World, 1, 5), -- Hello 截取 LENGTH(张三), -- 6UTF-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 右取::: warningLENGTH()返回字节数UTF-8 中文 3 字节CHAR_LENGTH()返回字符数。涉及中文长度的逻辑一定用CHAR_LENGTH。:::五、日期函数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); -- 转回日期常用日期格式符格式符含义示例%Y4 位年2024%m2 位月08%d2 位日15%H24 小时14%i分钟30%s秒00六、条件函数-- 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 写自定义函数UDF7.1 创建函数DELIMITER $$ CREATE FUNCTION calculate_age(birthday DATE) RETURNS INT DETERMINISTIC BEGIN RETURN TIMESTAMPDIFF(YEAR, birthday, CURDATE()); END$$ DELIMITER ;参数说明子句含义RETURNS INT返回类型DETERMINISTIC确定性函数输入相同输出相同用于查询优化BEGIN ... END函数体多条语句用分号必须改 delimiter7.2 使用函数SELECT name, birthday, calculate_age(birthday) AS age FROM student;7.3 删除函数DROPFUNCTIONcalculate_age;::: warningMySQL 自定义函数有严格限制不能引用表只能用 SELECT INTO 赋值局部变量不能修改数据库只能返回单一标量值复杂逻辑应该放在应用层Node / Java 代码:::八、GROUP BY 高级用法8.1 多列分组-- 每个班级每科的平均分 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 汇总行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 高级用法-- 找出每个班级数学成绩前 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不能用SELECTscore*2ASdouble_scoreFROMscoreWHEREdouble_score100;-- ❌ 报错unknown column double_score实际执行顺序FROMWHERE行过滤GROUP BY分组HAVING组过滤SELECT计算列ORDER BY排序LIMIT分页WHERE在SELECT之前执行——所以 WHERE 不能用 SELECT 起的别名。::: tipHAVING 能用 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