MySQL数值函数实战：10个案例掌握核心用法（基于emp表）

发布于：2025-07-12 ⋅ 阅读:(20) ⋅ 点赞:(0)

MySQL数值函数实战：10个案例掌握核心用法（基于emp表）

引言

在数据处理和分析中，数值函数 是进行数学计算、统计分析的基础工具。无论是计算平均值、处理小数精度，还是生成随机数，掌握数值函数都能大幅提升SQL编程效率。本文结合 emp 员工表，通过 10个实战案例 带你掌握MySQL数值函数的核心用法！

一、基础数值函数速览

先快速回顾常用数值函数的语法和作用：

函数名 作用说明 基础示例（可直接运行）
ROUND(num,d) 四舍五入到指定小数位 SELECT ROUND(3.14159, 2); → 3.14
CEIL(num) 向上取整（取大于等于该数的最小整数） SELECT CEIL(3.2); → 4
FLOOR(num) 向下取整（取小于等于该数的最大整数） SELECT FLOOR(3.9); → 3
ABS(num) 返回绝对值 SELECT ABS(-5); → 5
MOD(num, divisor) 返回除法余数 SELECT MOD(10, 3); → 1
POWER(num, power) 返回幂运算结果 SELECT POWER(2, 3); → 8
TRUNCATE(num, d) 截断小数位数（不四舍五入） SELECT TRUNCATE(3.99, 0); → 3
SUM(num) 返回总和 SELECT SUM(1, 2, 3); → 6
MAX(num)/MIN(num) 返回最大值/最小值 SELECT MAX(10, 5, 8); → 10
RAND() 返回0-1之间的随机数 SELECT RAND(); → 0.7834...

二、基于emp表的实战练习（10个核心案例）

emp 表结构：

DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp`  (
  `id` int(11) NULL DEFAULT NULL COMMENT '编号',
  `workno` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '工号',
  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `gender` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',
  `age` tinyint(3) UNSIGNED NULL DEFAULT NULL COMMENT '年龄',
  `idcard` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '身份证号',
  `workaddress` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '工作地址',
  `entrydate` date NULL DEFAULT NULL COMMENT '入职时间'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '员工表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES (1, '1', '柳岩', '女', 20, '123456789012345678', '北京', '2000-01-01');
INSERT INTO `emp` VALUES (2, '2', '张无忌', '男', 18, '123456789012345678', '北京', '2005-09-01');
INSERT INTO `emp` VALUES (3, '3', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01');
INSERT INTO `emp` VALUES (4, '4', '赵六', '女', 38, '123456757123845670', '北京', '2009-12-01');
INSERT INTO `emp` VALUES (5, '5', '小昭', '女', 16, '123456789012345678', '上海', '2007-07-01');
INSERT INTO `emp` VALUES (6, '6', '杨逍', '男', 28, '12345678912345678', '北京', '2006-01-01');
INSERT INTO `emp` VALUES (7, '7', '常威', '男', 40, '123456789712345670', '北京', '2005-05-01');
INSERT INTO `emp` VALUES (8, '8', '黛绮丝', '女', 35, '1234561517123645670', '北京', '2010-08-01');
INSERT INTO `emp` VALUES (9, '9', '周芷若', '女', 48, '123456789012345678', '北京', '2013-04-01');
INSERT INTO `emp` VALUES (10, '10', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01');
INSERT INTO `emp` VALUES (11, '11', '张士诚', '男', 55, '123456789712345670', '江苏', '2013-05-01');
INSERT INTO `emp` VALUES (12, '12', '常遇春', '男', 82, '123444675715245678', '北京', '2004-02-01');
INSERT INTO `emp` VALUES (13, '13', '张三丰', '男', 58, '123456789012345678', '江苏', '2002-09-11');
INSERT INTO `emp` VALUES (14, '14', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01');
INSERT INTO `emp` VALUES (15, '15', '胡青牛', '男', 70, '12345676971234567X', '西安', '2018-04-01');
INSERT INTO `emp` VALUES (16, '16', '周芷若', '女', 18, NULL, '北京', '2012-06-01');

SET FOREIGN_KEY_CHECKS = 1;
案例1：计算平均年龄（保留1位小数）

需求：统计所有员工的平均年龄，结果保留1位小数。

SELECT ROUND(AVG(age), 1) AS 平均年龄 FROM emp;

关键点：

  • AVG(age) 计算原始平均值（可能多位小数）。
  • ROUND(..., 1) 将结果四舍五入到1位小数。
案例2：年龄向上取整到10的倍数

需求：将每个员工的年龄向上取整到最近的10的倍数（如28→30，38→40）。

SELECT name, age, CEIL(age / 10) * 10 AS 取整后年龄 FROM emp;

关键点：

  • age / 10 先将年龄缩小10倍（如28→2.8）。
  • CEIL(2.8) 向上取整为3，再乘以10得到30。
案例3：年龄向下取整到10的倍数

需求：将每个员工的年龄向下取整到最近的10的倍数（如28→20，38→30）。

SELECT name, age, FLOOR(age / 10) * 10 AS 取整后年龄 FROM emp;

关键点：

  • 与 CEIL 相反，FLOOR 向下取整（如2.8→2，再乘以10得到20）。
案例4：年龄与平均年龄的绝对差

需求：计算每个员工年龄与平均年龄的差值的绝对值。

SELECT 
  name, 
  age,
  ABS(age - (SELECT AVG(age) FROM emp)) AS 年龄差 
FROM emp;

关键点：

  • 子查询 (SELECT AVG(age) FROM emp) 先计算全局平均年龄。
  • ABS() 确保差值为正数（如年龄20与平均38.5的差为18.5）。
案例5：年龄尾数分布统计

需求：统计员工年龄对10取余的结果（如尾数0、1、2…），并分组计数。

SELECT 
  MOD(age, 10) AS 年龄尾数,  -- 对10取余，得到0-9的尾数
  COUNT(*) AS 人数 
FROM emp 
GROUP BY 年龄尾数 
ORDER BY 年龄尾数;

关键点：

  • MOD(age, 10) 返回年龄除以10的余数（如38→8，20→0）。
  • GROUP BY 按尾数分组，统计每组人数。
案例6：计算年龄的平方值

需求：计算每个员工年龄的平方（如20岁→400，28岁→784）。

SELECT name, age, POWER(age, 2) AS 年龄平方 FROM emp;

关键点：

  • POWER(age, 2) 等价于 age * age，计算平方值。
案例7：截断平均年龄到整数

需求：计算平均年龄，直接截断小数部分（对比四舍五入的差异）。

SELECT 
  AVG(age) AS 原始平均年龄,  -- 保留小数的平均值
  TRUNCATE(AVG(age), 0) AS 截断后平均年龄  -- 截断小数，保留整数
FROM emp;

关键点：

  • 假设平均年龄为38.6，TRUNCATE(..., 0) 直接保留38（不进位）。
  • 对比 ROUND(38.6, 0) 会四舍五入为39。
案例8：统计年龄总和

需求：计算所有员工的年龄总和。

SELECT SUM(age) AS 年龄总和 FROM emp;

关键点：

  • SUM(age) 累加所有员工的年龄值。
案例9：最大与最小年龄的差值

需求：计算公司员工的最大年龄与最小年龄的差值。

SELECT 
  MAX(age) AS 最大年龄, 
  MIN(age) AS 最小年龄, 
  MAX(age) - MIN(age) AS 年龄差值 
FROM emp;

关键点：

  • MAX(age) 和 MIN(age) 分别获取最大和最小年龄。
  • 差值反映年龄范围（如82-16=66）。
案例10：生成随机绩效加分（0-100）

需求：为每个员工生成一个0-100的随机整数（模拟绩效加分）。

SELECT name, ROUND(RAND() * 100) AS 随机绩效加分 FROM emp;

关键点：

  • RAND() 生成0-1之间的随机数（如0.7834）。
  • 乘以100后范围变为0-100，ROUND() 取整为整数。

三、进阶拓展思路

  1. 结合条件筛选：
    仅处理特定部门的员工年龄：

    SELECT ROUND(AVG(age), 1) 
FROM emp 
WHERE dept_id = 1; -- 仅技术部

  2. 函数嵌套使用：
    先计算年龄平方，再取平方根还原：

    SELECT age, SQRT(POWER(age, 2)) AS 还原年龄 FROM emp;

  3. 与聚合函数结合：
    统计不同年龄段的人数分布：

    SELECT 
  FLOOR(age / 10) * 10 AS 年龄段, 
  COUNT(*) AS 人数 
FROM emp 
GROUP BY 年龄段;

总结

数值函数是MySQL数据处理的基础工具，通过 取整、绝对值、幂运算、随机数 等操作，可高效处理数值型数据。结合实际业务表（如 emp 员工表）练习，更易理解函数在真实场景的价值。

拓展建议：

  • 尝试处理更复杂的业务场景（如计算销售额增长率、库存周转率）；
  • 结合 WHERE 条件和分组统计，深化函数应用。

欢迎留言交流你的SQL练习心得！ 🚀

本文同步发布于CSDN，原创内容，转载请注明出处。
关注作者，获取更多MySQL实战技巧！

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布