MySQL常用日期格式转换函数、字符串函数、聚合函数详

目录

1. 日期格式转换函数
   • DATE_FORMAT()
   • STR_TO_DATE()
   • CURDATE() 与 NOW()
   • DATE_ADD() 与 DATE_SUB()
   • DATEDIFF() 与 TIMESTAMPDIFF()
2. 字符串函数
   • CONCAT()
   • SUBSTRING()
   • REPLACE()
   • LENGTH() 与 CHAR_LENGTH()
   • UPPER() 与 LOWER()
3. 聚合函数
   • COUNT()
   • SUM()
   • AVG()
   • MAX() 与 MIN()
   • GROUP_CONCAT()
4. 常见场景与示例
5. 总结

---

1. 日期格式转换函数

MySQL 提供了多种日期相关的函数，可以方便地对日期进行格式化、计算时间差以及加减时间。以下是常用的日期格式转换函数。

1.1 DATE_FORMAT()

DATE_FORMAT() 函数用于将日期格式化为指定的字符串格式。常见的日期格式化规则包括：

• %Y: 4位的年份（例如：2024）
• %m: 2位的月份（01-12）
• %d: 2位的日期（01-31）
• %H: 2位的小时（00-23）
• %i: 2位的分钟（00-59）
• %s: 2位的秒（00-59）

示例：

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS formatted_date;

结果：

formatted_date
-------------------
2024-09-13 14:35:21

1.2 STR_TO_DATE()

STR_TO_DATE() 将字符串解析为日期格式，通常用于从字符串中提取日期。

示例：

SELECT STR_TO_DATE('2024-09-13 14:35:21', '%Y-%m-%d %H:%i:%s') AS date_value;

结果：

date_value
-------------------
2024-09-13 14:35:21

1.3 CURDATE() 与 NOW()

• CURDATE() 返回当前日期（不包含时间部分）。
• NOW() 返回当前的日期和时间。

SELECT CURDATE() AS current_date, NOW() AS current_datetime;

结果：

current_date    | current_datetime
----------------|-------------------
2024-09-13      | 2024-09-13 14:35:21

1.4 DATE_ADD() 与 DATE_SUB()

• DATE_ADD() 用于在日期上增加指定的时间间隔。
• DATE_SUB() 用于在日期上减去指定的时间间隔。

SELECT DATE_ADD(CURDATE(), INTERVAL 7 DAY) AS next_week;
SELECT DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AS last_month;

结果：

next_week       | last_month
----------------|------------
2024-09-20      | 2024-08-13

1.5 DATEDIFF() 与 TIMESTAMPDIFF()

• DATEDIFF() 计算两个日期之间的天数差。
• TIMESTAMPDIFF() 可计算任意单位的时间差（秒、分钟、小时、天等）。

SELECT DATEDIFF('2024-09-20', '2024-09-13') AS days_diff;
SELECT TIMESTAMPDIFF(HOUR, '2024-09-13 08:00:00', NOW()) AS hours_diff;

结果：

days_diff   | hours_diff
------------|------------
7           | 6

---

2. 字符串函数

字符串操作是数据库查询中常见的需求。MySQL 提供了丰富的字符串处理函数，用于连接、替换、截取和转换字符串。

2.1 CONCAT()

CONCAT() 函数用于将多个字符串连接成一个字符串。

SELECT CONCAT('Hello, ', 'World!') AS greeting;

结果：

greeting
-----------
Hello, World!

2.2 SUBSTRING()

SUBSTRING() 函数用于截取字符串的子串。可以指定起始位置和长度。

SELECT SUBSTRING('abcdefg', 2, 3) AS sub_string;

结果：

sub_string
-----------
bcd

2.3 REPLACE()

REPLACE() 函数用于将字符串中的指定子串替换为新的字符串。

SELECT REPLACE('Hello World', 'World', 'MySQL') AS replaced_string;

结果：

replaced_string
----------------
Hello MySQL

2.4 LENGTH() 与 CHAR_LENGTH()

• LENGTH() 返回字符串的字节长度。
• CHAR_LENGTH() 返回字符串的字符数（与字符编码相关）。

SELECT LENGTH('Hello') AS byte_length, CHAR_LENGTH('Hello') AS char_length;

结果：

byte_length | char_length
------------|------------
5           | 5

2.5 UPPER() 与 LOWER()

UPPER() 将字符串转换为大写，LOWER() 将字符串转换为小写。

SELECT UPPER('hello') AS upper_case, LOWER('HELLO') AS lower_case;

结果：

upper_case | lower_case
-----------|------------
HELLO      | hello

---

3. 聚合函数

聚合函数用于对多行记录进行计算，常用于统计分析和报表生成。

3.1 COUNT()

COUNT() 用于计算查询结果的行数。

SELECT COUNT(*) AS total_rows FROM users;

结果：

total_rows
-----------
100

3.2 SUM()

SUM() 用于计算某列数值的总和。

SELECT SUM(salary) AS total_salary FROM employees;

结果：

total_salary
-------------
250000

3.3 AVG()

AVG() 用于计算某列数值的平均值。

SELECT AVG(salary) AS average_salary FROM employees;

结果：

average_salary
---------------
50000

3.4 MAX() 与 MIN()

• MAX() 返回某列的最大值。
• MIN() 返回某列的最小值。

SELECT MAX(salary) AS highest_salary, MIN(salary) AS lowest_salary FROM employees;

结果：

highest_salary | lowest_salary
---------------|---------------
100000         | 20000

3.5 GROUP_CONCAT()

GROUP_CONCAT() 用于将一组值连接成一个字符串，并以指定的分隔符分隔。

SELECT GROUP_CONCAT(username SEPARATOR ', ') AS user_list FROM users;

结果：

user_list
------------
alice, bob, charlie

---

4. 常见场景与示例

4.1 日期范围查询

假设你需要查询过去一个月内的订单：

SELECT * FROM orders
WHERE order_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND CURDATE();

4.2 统计用户数量

统计每个部门的员工数量：

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

4.3 合并用户姓名

假设你有一张用户表，想要将所有用户名拼接成一个字符串：

SELECT GROUP_CONCAT(username SEPARATOR ', ') AS all_usernames
FROM users;

---

5. 总结

本文详细介绍了 MySQL 中常用的日期格式转换函数、字符串函数和聚合函数。通过合理使用这些函数，你可以在实际开发中简化许多复杂的逻辑操作，提升查询的效率。无论是处理时间、操作字符串，还是进行数据聚合，MySQL 都提供了丰富且强大的内置函数，帮助你更好地管理和操作数据。

掌握这些函数并结合实际场景应用，将大大提高你在数据库操作中的效率和灵活性。希望这篇文章能帮助你在实际开发中更好地利用 MySQL 的强大功能。