本文共 4489 字，大约阅读时间需要 14 分钟。

MySQL 函数指南：分类与实用案例

MySQL 是一款功能强大的数据库管理系统，其内置的函数种类繁多，涵盖了从简单到复杂的多种操作需求。本文将详细介绍 MySQL 的函数分类及其实际应用场景。

函数分类

MySQL 函数主要可以分为以下几类：

聚合函数

聚合函数是数据库开发中常用的工具，用于对多个数据行进行操作。GROUP_CONCAT() 是其中一个常用的聚合函数，用于将同一组数据中的多个值连接成一个字符串。

GROUP_CONCAT() 使用示例

语法：

GROUP_CONCAT([DISTINCT] 字段名 [ORDER BY 排序字段 ASC/DESC] [SEPARATOR '分隔符'])

说明：

• DISTINCT 可用于去除重复值。
• ORDER BY 用于对结果进行排序。
• SEPARATOR 用于指定分隔符，默认为逗号。

示例：

-- 将所有员工的名字合并成一行SELECT GROUP_CONCAT(emp_name) FROM emp;-- 指定分隔符为分号SELECT department, GROUP_CONCAT(emp_name separator ';') FROM emp GROUP BY department;-- 排序方式和分隔符结合使用SELECT department, GROUP_CONCAT(emp_name order by salary desc separator ';') FROM emp GROUP BY department;

数学函数

数学函数用于基本的算术运算，例如加、减、乘、除等。这些函数在数据转换和计算中非常有用。

示例

-- 计算两个数的和SELECT 5000 + 5800 AS sum_result;-- 计算两个数的差SELECT 5800 - 5000 AS difference_result;-- 计算两个数的乘积SELECT 5000 * 5800 AS product_result;-- 计算两个数的商SELECT 5800 / 5000 AS average_result;

字符串函数

字符串函数是处理文本数据的重要工具，常见的包括 CONCAT()、SUBSTRING()、LOWER() 等。

常用函数

• CONCAT()：连接两个或多个字符串
• SUBSTRING()：提取字符串的子串
• LOWER()：将字符串转换为小写
• UPPER()：将字符串转换为大写

示例

-- 将两个字符串连接起来SELECT CONCAT('张晶晶', '王飞飞') AS full_name;-- 提取字符串的前几位SELECT SUBSTRING('张晶晶', 1, 3) AS first_three_chars;-- 转换字符串为小写SELECT LOWER('张晶晶') AS lower_name;-- 转换字符串为大写SELECT UPPER('财务部') AS upper_department;

日期函数

日期函数用于处理日期时间数据，常见的包括 CURDATE()、DATE_FORMAT()、NOW() 等。

常用函数

• CURDATE()：获取当前日期
• DATE_FORMAT()：格式化日期
• NOW()：获取当前时间戳

示例

-- 获取当前日期SELECT CURDATE() AS today_date;-- 格式化日期为 'YYYY-MM-DD' 格式SELECT DATE_FORMAT('2023-10-10', '%Y-%m-%d') AS formatted_date;-- 获取当前时间戳SELECT NOW() AS current_timestamp;

控制流函数

控制流函数模拟了编程语言中的逻辑控制，常用于条件判断和循环。

常用函数

• IF：条件判断
• CASE：多条件判断
• LOOP：循环

示例

-- 条件判断IF salary > 5000, THEN '高薪员', ELSE '低薪员' END) AS salary_level;-- 多条件判断SELECT     CASE         WHEN payType = 1 THEN '微信支付'        WHEN payType = 2 THEN '支付宝支付'        WHEN payType = 3 THEN '银行卡支付'        ELSE '其他支付方式'    END AS payTypeStrFROM orders;

窗口函数

窗口函数是 MySQL 8.0 中的一大特点，用于在数据集中动态计算窗口内的数据。

常用函数

• ROW_NUMBER()：添加序号
• RANK()：排名排序
• SUM() OVER()：窗口聚合函数

示例

-- 添加序号SELECT dname, ename, salary, ROW_NUMBER() OVER(PARTITION BY dname ORDER BY salary DESC) AS rn FROM employee;-- 排名排序SELECT dname, ename, salary, RANK() OVER(PARTITION BY dname ORDER BY salary DESC) AS rn FROM employee;-- 窗口聚合函数SELECT dname, ename, salary, SUM(salary) OVER(PARTITION BY dname ORDER BY hiredate) AS pv1 FROM employee;

序号函数

序号函数用于添加序号，常用于排序和排名。

常用函数

• ROW_NUMBER()：按序号排序
• RANK()：按排名排序
• DENSE_RANK()：按密集排名排序

示例

-- 添加序号SELECT dname, ename, salary, ROW_NUMBER() OVER(PARTITION BY dname ORDER BY salary DESC) AS rn FROM employee;-- 添加排名SELECT dname, ename, salary, RANK() OVER(PARTITION BY dname ORDER BY salary DESC) AS rn FROM employee;-- 添加密集排名SELECT dname, ename, salary, DENSE_RANK() OVER(PARTITION BY dname ORDER BY salary DESC) AS rn FROM employee;

开窗聚合函数

窗口聚合函数用于在窗口中动态计算聚合函数值，不改变行数。

常用函数

• SUM()：窗口内求和
• AVG()：窗口内求平均值
• MAX()：窗口内求最大值
• MIN()：窗口内求最小值

示例

-- 窗口内求和SELECT dname, ename, salary, SUM(salary) OVER(PARTITION BY dname ORDER BY hiredate) AS pv1 FROM employee;-- 窗口内求最大值SELECT dname, ename, salary, MAX(salary) OVER(PARTITION BY dname ORDER BY hiredate) AS max_salary FROM employee;

分布函数

分布函数用于按比例分布，常用于统计比例。

常用函数

• CUME_DIST()：按比例分布
• PERCENT_RANK()：按百分比分布

示例

-- 按比例分布SELECT dname, ename, salary, CUME_DIST() OVER(PARTITION BY dname ORDER BY salary) AS rn1, CUME_DIST() OVER(PARTITION BY dname ORDER BY salary) AS rn2 FROM employee;-- 按百分比分布SELECT dname, ename, salary, PERCENT_RANK() OVER(PARTITION BY dname ORDER BY salary) AS rn FROM employee;

前后函数

前后函数用于获取窗口内的前后数据。

常用函数

• LAG()：获取前数据
• LEAD()：获取后数据
• FIRST_VALUE()：获取第一数据
• LAST_VALUE()：获取最后数据

示例

-- 获取前数据SELECT dname, ename, hiredate, salary, LAG(hiredate, 1, '2000-01-01') OVER(PARTITION BY dname ORDER BY hiredate) AS last_1_time FROM employee;-- 获取后数据SELECT dname, ename, hiredate, salary, LEAD(hiredate, 1, '2000-01-01') OVER(PARTITION BY dname ORDER BY hiredate) AS next_1_time FROM employee;

其他函数

NTILE() 和 NTH_VALUE()

用于窗口内定位特定数据点。

示例

-- 获取窗口内第n个值SELECT dname, ename, salary, NTILE(3) OVER(PARTITION BY dname ORDER BY salary) AS rn FROM employee;-- 获取窗口内第n个值SELECT dname, ename, salary, NTILE(3) OVER(PARTITION BY dname ORDER BY salary) AS second_score FROM employee;