30分钟搞定MySQL常用函数之字符串、日期、聚合函数(附实操代码)

来源：这里教程网时间：2026-04-02 16:16:03 作者：

一、字符串函数1. 字符串长度函数LENGTH()和CHAR_LENGTH()示例：Java 代码示例：代码解释2. 字符串截取函数SUBSTRING()/SUBSTR()和LEFT()/RIGHT()示例：Java 代码示例：代码解释3. 字符串替换函数REPLACE()示例：Java 代码示例：代码解释4. 大小写转换函数UPPER()/UCASE()和LOWER()/LCASE()示例：Java 代码示例：代码解释5. 去除空格函数TRIM()/LTRIM()/RTRIM()示例：Java 代码示例：代码解释6. 字符串连接函数CONCAT()示例：Java 代码示例：代码解释二、日期函数1. 当前日期和时间函数NOW(),CURDATE(),CURTIME()示例：Java 代码示例：代码解释2. 日期和时间组件提取函数YEAR(),MONTH(),DAY(),HOUR(),MINUTE(),SECOND()示例：Java 代码示例：代码解释3. 日期计算函数DATE_ADD()/ADDDATE(),DATE_SUB()/SUBDATE()示例：Java 代码示例：代码解释4. 日期差函数DATEDIFF()和TIMESTAMPDIFF()示例：Java 代码示例：代码解释5. 日期格式化函数DATE_FORMAT()示例：Java 代码示例：代码解释三、聚合函数1. 计数函数COUNT()示例：Java 代码示例：代码解释2. 求和函数SUM()示例：Java 代码示例：代码解释3. 平均值函数AVG()示例：Java 代码示例：代码解释4. 最大值和最小值函数MAX()和MIN()示例：Java 代码示例：代码解释5. 聚合函数与分组GROUP BY结合使用示例：Java 代码示例：代码解释四、综合实战：销售数据分析实战目标数据准备Java 代码示例代码解释总结：函数是数据库操作的利器Mermaid 图表：字符串函数关系图 Mermaid 图表：日期函数关系图 Mermaid 图表：聚合函数关系图 Mermaid 图表：函数综合应用示意图 总结

在数据库的世界里，函数就像是程序员的工具箱，为我们提供了强大的数据处理能力。无论是清洗数据、格式化输出、计算时间差，还是进行统计分析，MySQL 函数都能大显身手。今天，我们将一起深入探索 MySQL 中最常用的几类函数：字符串函数、日期函数 和 聚合函数。我们将通过丰富的示例和 Java 代码来演示如何在实际项目中运用这些函数，让你在 30 分钟内轻松掌握它们！

一、字符串函数

字符串函数是处理文本数据的基础。在日常开发中，我们经常需要对字符串进行截取、替换、大小写转换、填充等操作。MySQL 提供了大量内置的字符串函数来简化这些任务。

1. 字符串长度函数LENGTH()和CHAR_LENGTH()

LENGTH() 和 CHAR_LENGTH() 都用来计算字符串的长度，但它们的计算方式不同：

LENGTH(str)：计算字符串的字节长度。对于单字节字符集（如 Latin1），它等于字符数；对于多字节字符集（如 UTF8），一个字符可能占用多个字节。CHAR_LENGTH(str)：计算字符串的字符数，忽略字符编码的差异。

示例：

Java 代码示例：

import java.sql.*; import java.util.ArrayList; import java.util.List; public class StringFunctionExample { private static final String DB_URL = "jdbc:mysql://localhost:3306/test_db?useSSL=false&serverTimezone=UTC"; private static final String DB_USER = "root"; // 替换为你的用户名 private static final String DB_PASSWORD = "password"; // 替换为你的密码 public static void demonstrateLengthFunctions() { Connection conn = null; try { conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD); // 创建示例表 String createTableSQL = """ CREATE TABLE IF NOT EXISTS sample_users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), description TEXT ) """; try (Statement stmt = conn.createStatement()) { stmt.execute(createTableSQL); } // 插入测试数据 String insertSQL = "INSERT INTO sample_users (name, description) VALUES (?, ?)"; try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) { pstmt.setString(1, "Alice"); pstmt.setString(2, "Hello World"); pstmt.addBatch(); pstmt.setString(1, "张三"); pstmt.setString(2, "你好世界"); pstmt.addBatch(); pstmt.executeBatch(); System.out.println("✅ 测试数据已插入。"); } // 查询并展示 LENGTH 和 CHAR_LENGTH String querySQL = """ SELECT name, description, LENGTH(description) AS byte_length, CHAR_LENGTH(description) AS char_length FROM sample_users """; try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(querySQL)) { System.out.println("\n=== 字符串长度函数演示 ==="); System.out.printf("%-10s %-15s %-15s %-15s%n", "姓名", "描述", "字节长度", "字符长度"); while (rs.next()) { String name = rs.getString("name"); String description = rs.getString("description"); int byteLen = rs.getInt("byte_length"); int charLen = rs.getInt("char_length"); System.out.printf("%-10s %-15s %-15d %-15d%n", name, description, byteLen, charLen); } } } catch (SQLException e) { System.err.println("执行字符串长度函数演示时发生错误: " + e.getMessage()); e.printStackTrace(); } finally { try { if (conn != null && !conn.isClosed()) { conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } } public static void main(String[] args) { demonstrateLengthFunctions(); } }

代码解释

创建表：首先创建一个 sample_users 表用于存储示例数据。

插入数据：使用 PreparedStatement 批量插入包含英文和中文的测试数据。

查询与展示：执行 SQL 查询，同时使用 LENGTH() 和 CHAR_LENGTH() 计算 description 字段的长度，并在 Java 控制台打印结果。

输出对比：对于中文字符，LENGTH() 返回的是字节数（每个中文字符通常占 3 个字节），而 CHAR_LENGTH() 返回的是字符数。

2. 字符串截取函数SUBSTRING()/SUBSTR()和LEFT()/RIGHT()

这些函数用于从字符串中提取一部分。

SUBSTRING(str, pos, len)：从 str 的位置 pos 开始，截取长度为 len 的子字符串。pos 从 1 开始计数。LEFT(str, len)：从字符串左边开始截取 len 个字符。RIGHT(str, len)：从字符串右边开始截取 len 个字符。

示例：

-- 假设有一个产品名称字段 product_name -- SELECT SUBSTRING('MySQL Database', 1, 5) AS result; -- 输出: MySQL -- SELECT LEFT('MySQL Database', 5) AS result; -- 输出: MySQL -- SELECT RIGHT('MySQL Database', 7) AS result; -- 输出: Database -- SELECT SUBSTRING('MySQL Database', 7) AS result; -- 输出: Database (从位置7开始到末尾) -- SELECT SUBSTRING('MySQL Database', 7, 4) AS result; -- 输出: Dat (从位置7开始，截取4个字符)

Java 代码示例：

import java.sql.*; public class SubstringExample { private static final String DB_URL = "jdbc:mysql://localhost:3306/test_db?useSSL=false&serverTimezone=UTC"; private static final String DB_USER = "root"; // 替换为你的用户名 private static final String DB_PASSWORD = "password"; // 替换为你的密码 public static void demonstrateSubstringFunctions() { Connection conn = null; try { conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD); // 创建示例表 String createTableSQL = """ CREATE TABLE IF NOT EXISTS products ( id INT AUTO_INCREMENT PRIMARY KEY, product_name VARCHAR(255), product_code VARCHAR(50) ) """; try (Statement stmt = conn.createStatement()) { stmt.execute(createTableSQL); } // 插入测试数据 String insertSQL = "INSERT INTO products (product_name, product_code) VALUES (?, ?)"; try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) { pstmt.setString(1, "iPhone 14 Pro Max"); pstmt.setString(2, "IP14PM001"); pstmt.addBatch(); pstmt.setString(1, "Samsung Galaxy S23 Ultra"); pstmt.setString(2, "SGS23U002"); pstmt.addBatch(); pstmt.setString(1, "MacBook Air M2"); pstmt.setString(2, "MA2M003"); pstmt.addBatch(); pstmt.executeBatch(); System.out.println("✅ 产品数据已插入。"); } // 演示截取函数 String querySQL = """ SELECT product_name, product_code, LEFT(product_name, 5) AS brand_name, -- 截取前5个字符作为品牌名 RIGHT(product_code, 3) AS last_three_digits, -- 截取产品代码后3位 SUBSTRING(product_name, 1, 5) AS substring_brand, -- 同样是前5个字符 SUBSTRING(product_code, 4, 3) AS middle_part_code -- 从位置4开始截取3个字符 FROM products """; try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(querySQL)) { System.out.println("\n=== 字符串截取函数演示 ==="); System.out.printf("%-25s %-15s %-15s %-15s %-15s %-15s%n", "产品名称", "产品代码", "品牌名", "后三位", "截取品牌", "中间代码"); while (rs.next()) { String productName = rs.getString("product_name"); String productCode = rs.getString("product_code"); String brandName = rs.getString("brand_name"); String lastThreeDigits = rs.getString("last_three_digits"); String substringBrand = rs.getString("substring_brand"); String middlePartCode = rs.getString("middle_part_code"); System.out.printf("%-25s %-15s %-15s %-15s %-15s %-15s%n", productName, productCode, brandName, lastThreeDigits, substringBrand, middlePartCode); } } } catch (SQLException e) { System.err.println("执行字符串截取函数演示时发生错误: " + e.getMessage()); e.printStackTrace(); } finally { try { if (conn != null && !conn.isClosed()) { conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } } public static void main(String[] args) { demonstrateSubstringFunctions(); } }

代码解释

创建表：创建 products 表存储产品信息。

插入数据：插入三条包含产品名称和代码的测试数据。

查询与演示：使用 LEFT(product_name, 5) 提取产品名称的前 5 个字符作为品牌名。使用 RIGHT(product_code, 3) 提取产品代码的后 3 个字符。使用 SUBSTRING(product_name, 1, 5) 和 LEFT 类似，但更灵活。使用 SUBSTRING(product_code, 4, 3) 从产品代码的第 4 个字符开始截取 3 个字符。

输出结果：清晰地展示了不同截取函数的效果。

CONCAT(str1, str2, ...) 用于将多个字符串连接成一个字符串。如果任意一个参数为 NULL，则结果为 NULL。

示例：

-- SELECT CONCAT('Hello', ' ', 'World') AS result; -- 输出: Hello World -- SELECT CONCAT('User:', 'John') AS result; -- 输出: User:John -- SELECT CONCAT('Name: ', first_name, ' ', last_name) AS full_name FROM users; -- 连接姓名字段 -- SELECT CONCAT_WS('-', '2023', '10', '15') AS date_string; -- 使用分隔符连接，CONCAT_WS

Java 代码示例：

import java.sql.*; public class ConcatExample { private static final String DB_URL = "jdbc:mysql://localhost:3306/test_db?useSSL=false&serverTimezone=UTC"; private static final String DB_USER = "root"; // 替换为你的用户名 private static final String DB_PASSWORD = "password"; // 替换为你的密码 public static void demonstrateConcatFunction() { Connection conn = null; try { conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD); // 创建示例表 String createTableSQL = """ CREATE TABLE IF NOT EXISTS addresses ( id INT AUTO_INCREMENT PRIMARY KEY, street VARCHAR(255), city VARCHAR(100), state VARCHAR(100), zip_code VARCHAR(20) ) """; try (Statement stmt = conn.createStatement()) { stmt.execute(createTableSQL); } // 插入测试数据 String insertSQL = "INSERT INTO addresses (street, city, state, zip_code) VALUES (?, ?, ?, ?)"; try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) { pstmt.setString(1, "123 Main Street"); pstmt.setString(2, "New York"); pstmt.setString(3, "NY"); pstmt.setString(4, "10001"); pstmt.addBatch(); pstmt.setString(1, "456 Oak Avenue"); pstmt.setString(2, "Los Angeles"); pstmt.setString(3, "CA"); pstmt.setString(4, "90210"); pstmt.addBatch(); pstmt.executeBatch(); System.out.println("✅ 地址数据已插入。"); } // 演示字符串连接 String querySQL = """ SELECT street, city, state, zip_code, CONCAT(street, ', ', city, ', ', state, ' ', zip_code) AS full_address, CONCAT_WS(', ', street, city, state, zip_code) AS formatted_address -- 使用分隔符 FROM addresses """; try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(querySQL)) { System.out.println("\n=== 字符串连接函数演示 ==="); System.out.printf("%-25s %-20s %-10s %-10s %-50s %-50s%n", "街道", "城市", "州", "邮编", "完整地址", "格式化地址"); while (rs.next()) { String street = rs.getString("street"); String city = rs.getString("city"); String state = rs.getString("state"); String zipCode = rs.getString("zip_code"); String fullAddress = rs.getString("full_address"); String formattedAddress = rs.getString("formatted_address"); System.out.printf("%-25s %-20s %-10s %-10s %-50s %-50s%n", street, city, state, zipCode, fullAddress, formattedAddress); } } } catch (SQLException e) { System.err.println("执行字符串连接函数演示时发生错误: " + e.getMessage()); e.printStackTrace(); } finally { try { if (conn != null && !conn.isClosed()) { conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } } public static void main(String[] args) { demonstrateConcatFunction(); } }

代码解释

创建表：创建 addresses 表存储地址信息。

插入数据：插入包含街道、城市、州、邮编的测试数据。

查询与演示：使用 CONCAT(street, ', ', city, ', ', state, ' ', zip_code) 将地址信息拼接成一个完整的地址字符串。使用 CONCAT_WS(', ', street, city, state, zip_code) 使用逗号作为分隔符拼接地址，CONCAT_WS 是 CONCAT 的变体，专门用于带分隔符的连接。

输出结果：展示了原始数据和拼接后的地址效果。

二、日期函数

日期函数是处理时间相关数据的强大工具。在应用开发中，我们经常需要计算日期差、提取日期组件、格式化日期显示、处理时间戳等。MySQL 提供了丰富的日期函数来应对这些需求。

1. 当前日期和时间函数NOW(),CURDATE(),CURTIME()

这些函数用于获取当前的日期、时间和时间戳。

NOW()：返回当前日期和时间（YYYY-MM-DD HH:MM:SS 格式）。CURDATE()：返回当前日期（YYYY-MM-DD 格式）。CURTIME()：返回当前时间（HH:MM:SS 格式）。

示例：

-- SELECT NOW() AS current_datetime; -- 输出: 2023-10-15 14:30:45 -- SELECT CURDATE() AS current_date; -- 输出: 2023-10-15 -- SELECT CURTIME() AS current_time; -- 输出: 14:30:45

Java 代码示例：

import java.sql.*; import java.time.LocalDateTime; import java.time.format.DateTimeFormatter; public class DateTimeFunctionExample { private static final String DB_URL = "jdbc:mysql://localhost:3306/test_db?useSSL=false&serverTimezone=UTC"; private static final String DB_USER = "root"; // 替换为你的用户名 private static final String DB_PASSWORD = "password"; // 替换为你的密码 public static void demonstrateDateTimeFunctions() { Connection conn = null; try { conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD); // 创建示例表 String createTableSQL = """ CREATE TABLE IF NOT EXISTS events ( id INT AUTO_INCREMENT PRIMARY KEY, event_name VARCHAR(255), event_date DATE, created_at DATETIME, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) """; try (Statement stmt = conn.createStatement()) { stmt.execute(createTableSQL); } // 插入测试数据 String insertSQL = "INSERT INTO events (event_name, event_date, created_at) VALUES (?, ?, ?)"; try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) { pstmt.setString(1, "产品发布会"); pstmt.setDate(2, Date.valueOf("2023-11-20")); // 使用 java.sql.Date pstmt.setTimestamp(3, Timestamp.valueOf("2023-10-15 10:00:00")); // 使用 java.sql.Timestamp pstmt.addBatch(); pstmt.setString(1, "团队建设活动"); pstmt.setDate(2, Date.valueOf("2023-12-05")); pstmt.setTimestamp(3, Timestamp.valueOf("2023-10-15 11:30:00")); pstmt.addBatch(); pstmt.executeBatch(); System.out.println("✅ 事件数据已插入。"); } // 演示日期时间函数 String querySQL = """ SELECT event_name, event_date, created_at, NOW() AS db_now, -- 数据库当前时间 CURDATE() AS db_current_date, -- 数据库当前日期 CURTIME() AS db_current_time, -- 数据库当前时间 DATE(created_at) AS created_date_only, -- 从DATETIME中提取日期 TIME(created_at) AS created_time_only, -- 从DATETIME中提取时间 DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') AS formatted_created_at -- 格式化时间 FROM events """; try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(querySQL)) { System.out.println("\n=== 日期时间函数演示 ==="); System.out.printf("%-20s %-12s %-20s %-20s %-12s %-12s %-12s %-12s %-20s%n", "事件名称", "事件日期", "创建时间", "数据库当前时间", "当前日期", "当前时间", "日期", "时间", "格式化时间"); while (rs.next()) { String eventName = rs.getString("event_name"); Date eventDate = rs.getDate("event_date"); Timestamp createdAt = rs.getTimestamp("created_at"); Timestamp dbNow = rs.getTimestamp("db_now"); Date dbCurrentDate = rs.getDate("db_current_date"); Time dbCurrentTime = rs.getTime("db_current_time"); Date createdDateOnly = rs.getDate("created_date_only"); Time createdTimeOnly = rs.getTime("created_time_only"); String formattedCreatedAt = rs.getString("formatted_created_at"); System.out.printf("%-20s %-12s %-20s %-20s %-12s %-12s %-12s %-12s %-20s%n", eventName, eventDate, createdAt, dbNow, dbCurrentDate, dbCurrentTime, createdDateOnly, createdTimeOnly, formattedCreatedAt); } } } catch (SQLException e) { System.err.println("执行日期时间函数演示时发生错误: " + e.getMessage()); e.printStackTrace(); } finally { try { if (conn != null && !conn.isClosed()) { conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } } public static void main(String[] args) { demonstrateDateTimeFunctions(); } }

代码解释

创建表：创建 events 表存储事件信息，包含日期和时间字段。

插入数据：插入两条事件记录，包含事件名称、日期和创建时间。

查询与演示：使用 NOW() 获取数据库服务器的当前时间。使用 CURDATE() 和 CURTIME() 获取当前日期和时间。使用 DATE(created_at) 从 DATETIME 字段中提取日期部分。使用 TIME(created_at) 从 DATETIME 字段中提取时间部分。使用 DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') 格式化时间显示。

输出结果：展示了原始数据和各种日期时间函数的处理结果。

DATE_FORMAT(date, format) 用于将日期或时间值按照指定的格式进行格式化输出。

示例：

-- SELECT DATE_FORMAT('2023-10-15', '%Y-%m-%d') AS formatted_date; -- 输出: 2023-10-15 -- SELECT DATE_FORMAT('2023-10-15 14:30:45', '%Y/%m/%d %H:%i:%s') AS formatted_datetime; -- 输出: 2023/10/15 14:30:45 -- SELECT DATE_FORMAT('2023-10-15', '%M %D, %Y') AS formatted_long_date; -- 输出: October 15th, 2023 -- SELECT DATE_FORMAT('2023-10-15 14:30:45', '%W, %M %e, %Y at %h:%i %p') AS formatted_long_datetime; -- 输出: Sunday, October 15, 2023 at 02:30 PM

Java 代码示例：

import java.sql.*; public class DateFormattingExample { private static final String DB_URL = "jdbc:mysql://localhost:3306/test_db?useSSL=false&serverTimezone=UTC"; private static final String DB_USER = "root"; // 替换为你的用户名 private static final String DB_PASSWORD = "password"; // 替换为你的密码 public static void demonstrateDateFormatting() { Connection conn = null; try { conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD); // 创建示例表 String createTableSQL = """ CREATE TABLE IF NOT EXISTS blog_posts ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255), publish_date DATETIME, category VARCHAR(100) ) """; try (Statement stmt = conn.createStatement()) { stmt.execute(createTableSQL); } // 插入测试数据 String insertSQL = "INSERT INTO blog_posts (title, publish_date, category) VALUES (?, ?, ?)"; try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) { pstmt.setString(1, "MySQL 教程入门"); pstmt.setTimestamp(2, Timestamp.valueOf("2023-10-10 09:00:00")); pstmt.setString(3, "Database"); pstmt.addBatch(); pstmt.setString(1, "Java 编程技巧"); pstmt.setTimestamp(2, Timestamp.valueOf("2023-10-12 16:30:00")); pstmt.setString(3, "Programming"); pstmt.addBatch(); pstmt.setString(1, "前端开发指南"); pstmt.setTimestamp(2, Timestamp.valueOf("2023-10-15 11:15:00")); pstmt.setString(3, "Web"); pstmt.addBatch(); pstmt.executeBatch(); System.out.println("✅ 博客文章数据已插入。"); } // 演示日期格式化 String querySQL = """ SELECT title, publish_date, DATE_FORMAT(publish_date, '%Y-%m-%d') AS simple_date, -- 简单日期格式 DATE_FORMAT(publish_date, '%Y/%m/%d %H:%i:%s') AS full_datetime, -- 完整日期时间格式 DATE_FORMAT(publish_date, '%M %D, %Y') AS long_date, -- 长日期格式 DATE_FORMAT(publish_date, '%W, %M %e, %Y at %h:%i %p') AS formatted_post_date, -- 博客文章格式 DATE_FORMAT(publish_date, '%Y年%m月%d日') AS chinese_date_format -- 中文日期格式 FROM blog_posts ORDER BY publish_date DESC """; try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(querySQL)) { System.out.println("\n=== 日期格式化函数演示 ==="); System.out.printf("%-25s %-20s %-15s %-20s %-20s %-40s %-20s%n", "文章标题", "发布日期", "简单日期", "完整时间", "长日期", "格式化发布日期", "中文日期"); while (rs.next()) { String title = rs.getString("title"); Timestamp publishDate = rs.getTimestamp("publish_date"); String simpleDate = rs.getString("simple_date"); String fullDatetime = rs.getString("full_datetime"); String longDate = rs.getString("long_date"); String formattedPostDate = rs.getString("formatted_post_date"); String chineseDateFormat = rs.getString("chinese_date_format"); System.out.printf("%-25s %-20s %-15s %-20s %-20s %-40s %-20s%n", title, publishDate, simpleDate, fullDatetime, longDate, formattedPostDate, chineseDateFormat); } } } catch (SQLException e) { System.err.println("执行日期格式化函数演示时发生错误: " + e.getMessage()); e.printStackTrace(); } finally { try { if (conn != null && !conn.isClosed()) { conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } } public static void main(String[] args) { demonstrateDateFormatting(); } }

代码解释

创建表：创建 blog_posts 表存储博客文章信息。

插入数据：插入三条包含文章标题、发布日期和分类的博客文章记录。

查询与演示：使用 DATE_FORMAT(publish_date, '%Y-%m-%d') 输出简单的日期格式。使用 DATE_FORMAT(publish_date, '%Y/%m/%d %H:%i:%s') 输出完整的日期时间格式。使用 DATE_FORMAT(publish_date, '%M %D, %Y') 输出长日期格式（如 “October 15th, 2023”）。使用 DATE_FORMAT(publish_date, '%W, %M %e, %Y at %h:%i %p') 输出适合博客文章的格式（如 “Sunday, October 15, 2023 at 02:30 PM”）。使用 DATE_FORMAT(publish_date, '%Y年%m月%d日') 输出中文日期格式。

输出结果：展示了每篇文章的原始发布日期和各种格式化后的日期显示效果。

三、聚合函数

聚合函数是对一组值进行计算并返回单一值的函数。它们常用于 GROUP BY 子句中进行统计分析。常见的聚合函数包括 COUNT(), SUM(), AVG(), MAX(), MIN() 等。

1. 计数函数COUNT()

四、综合实战：销售数据分析

让我们结合前面学到的所有函数，进行一次综合实战——构建一个销售数据分析报告。

实战目标

为一家零售公司生成一份销售报告，包含：

每个产品的销售总额。

每个产品的平均销售价格。

每个区域的销售总额。

每个季度的销售总额。

每个季度的增长率。

各个产品的销售排名。

数据准备

-- 创建销售数据表 CREATE TABLE IF NOT EXISTS quarterly_sales ( id INT AUTO_INCREMENT PRIMARY KEY, product_name VARCHAR(255), quantity INT, unit_price DECIMAL(10, 2), sale_date DATE, region VARCHAR(50), quarter VARCHAR(10) -- 用于存储季度信息，例如 'Q1_2023' ); -- 插入示例数据 INSERT INTO quarterly_sales (product_name, quantity, unit_price, sale_date, region, quarter) VALUES ('iPhone 14', 2, 999.99, '2023-01-15', 'North', 'Q1_2023'), ('Samsung Galaxy S23', 1, 899.99, '2023-01-20', 'South', 'Q1_2023'), ('MacBook Pro', 1, 1999.99, '2023-02-10', 'North', 'Q1_2023'), ('iPad Air', 3, 599.99, '2023-02-25', 'East', 'Q1_2023'), ('Apple Watch', 2, 399.99, '2023-03-10', 'West', 'Q1_2023'), ('AirPods Pro', 1, 249.99, '2023-03-20', 'North', 'Q1_2023'), ('Galaxy Tab S8', 2, 799.99, '2023-04-05', 'South', 'Q2_2023'), ('Surface Laptop', 1, 1299.99, '2023-04-15', 'West', 'Q2_2023'), ('ThinkPad X1', 1, 1899.99, '2023-05-10', 'North', 'Q2_2023'), ('Pixel 7', 2, 799.99, '2023-05-20', 'East', 'Q2_2023'), ('Dell XPS', 1, 1599.99, '2023-06-05', 'West', 'Q2_2023'), ('MacBook Air', 3, 1199.99, '2023-06-15', 'North', 'Q2_2023'), ('iPhone 15', 1, 1099.99, '2023-07-10', 'South', 'Q3_2023'), ('Galaxy S24', 2, 999.99, '2023-07-20', 'North', 'Q3_2023'), ('Surface Pro', 1, 1399.99, '2023-08-05', 'West', 'Q3_2023'), ('ThinkPad P1', 1, 2499.99, '2023-08-15', 'East', 'Q3_2023'), ('iPad Pro', 2, 899.99, '2023-09-05', 'North', 'Q3_2023'), ('AirPods 2nd Gen', 3, 179.99, '2023-09-20', 'South', 'Q3_2023'), ('Galaxy Watch', 1, 299.99, '2023-10-05', 'West', 'Q4_2023'), ('Mac Studio', 1, 2999.99, '2023-10-10', 'North', 'Q4_2023'), ('Surface Book', 1, 1899.99, '2023-10-20', 'East', 'Q4_2023'), ('Dell Inspiron', 2, 899.99, '2023-10-25', 'South', 'Q4_2023');

Java 代码示例

import java.sql.*; import java.math.BigDecimal; public class SalesAnalysisExample { private static final String DB_URL = "jdbc:mysql://localhost:3306/test_db?useSSL=false&serverTimezone=UTC"; private static final String DB_USER = "root"; // 替换为你的用户名 private static final String DB_PASSWORD = "password"; // 替换为你的密码 public static void generateSalesReport() { Connection conn = null; try { conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD); // 1. 每个产品的销售总额和平均价格 String productSalesQuery = """ SELECT product_name, SUM(quantity * unit_price) AS total_sales, AVG(unit_price) AS avg_price, SUM(quantity) AS total_quantity_sold FROM quarterly_sales GROUP BY product_name ORDER BY total_sales DESC """; try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(productSalesQuery)) { System.out.println("\n=== 产品销售总额与平均价格 ==="); System.out.printf("%-20s %-15s %-15s %-15s%n", "产品名", "总销售额", "平均单价", "总销量"); while (rs.next()) { String productName = rs.getString("product_name"); BigDecimal totalSales = rs.getBigDecimal("total_sales"); BigDecimal avgPrice = rs.getBigDecimal("avg_price"); long totalQuantitySold = rs.getLong("total_quantity_sold"); System.out.printf("%-20s %-15s %-15s %-15d%n", productName, totalSales.setScale(2, java.math.RoundingMode.HALF_UP), avgPrice.setScale(2, java.math.RoundingMode.HALF_UP), totalQuantitySold); } } // 2. 每个区域的销售总额 String regionSalesQuery = """ SELECT region, SUM(quantity * unit_price) AS total_sales FROM quarterly_sales GROUP BY region ORDER BY total_sales DESC """; try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(regionSalesQuery)) { System.out.println("\n=== 区域销售总额 ==="); System.out.printf("%-10s %-15s%n", "区域", "总销售额"); while (rs.next()) { String region = rs.getString("region"); BigDecimal totalSales = rs.getBigDecimal("total_sales"); System.out.printf("%-10s %-15s%n", region, totalSales.setScale(2, java.math.RoundingMode.HALF_UP)); } } // 3. 每个季度的销售总额 String quarterSalesQuery = """ SELECT quarter, SUM(quantity * unit_price) AS total_sales FROM quarterly_sales GROUP BY quarter ORDER BY quarter """; try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(quarterSalesQuery)) { System.out.println("\n=== 季度销售总额 ==="); System.out.printf("%-12s %-15s%n", "季度", "总销售额"); while (rs.next()) { String quarter = rs.getString("quarter"); BigDecimal totalSales = rs.getBigDecimal("total_sales"); System.out.printf("%-12s %-15s%n", quarter, totalSales.setScale(2, java.math.RoundingMode.HALF_UP)); } } // 4. 每个季度的增长率（使用 LAG 窗口函数） // 注意：MySQL 8.0+ 支持窗口函数 String quarterGrowthQuery = """ WITH quarterly_totals AS ( SELECT quarter, SUM(quantity * unit_price) AS total_sales FROM quarterly_sales GROUP BY quarter ), quarterly_growth AS ( SELECT quarter, total_sales, LAG(total_sales, 1) OVER (ORDER BY quarter) AS previous_quarter_sales FROM quarterly_totals ) SELECT quarter, total_sales, previous_quarter_sales, CASE WHEN previous_quarter_sales IS NOT NULL AND previous_quarter_sales > 0 THEN ROUND(((total_sales - previous_quarter_sales) / previous_quarter_sales) * 100, 2) ELSE NULL END AS growth_percentage FROM quarterly_growth ORDER BY quarter """; try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(quarterGrowthQuery)) { System.out.println("\n=== 季度销售增长率 ==="); System.out.printf("%-12s %-15s %-15s %-15s%n", "季度", "总销售额", "上季度销售额", "增长率(%)"); while (rs.next()) { String quarter = rs.getString("quarter"); BigDecimal totalSales = rs.getBigDecimal("total_sales"); BigDecimal previousQuarterSales = rs.getBigDecimal("previous_quarter_sales"); BigDecimal growthPercentage = rs.getBigDecimal("growth_percentage"); System.out.printf("%-12s %-15s %-15s %-15s%n", quarter, totalSales.setScale(2, java.math.RoundingMode.HALF_UP), previousQuarterSales == null ? "N/A" : previousQuarterSales.setScale(2, java.math.RoundingMode.HALF_UP), growthPercentage == null ? "N/A" : growthPercentage.toString() + "%"); } } // 5. 各个产品的销售排名 String productRankingQuery = """ SELECT product_name, SUM(quantity * unit_price) AS total_sales, ROW_NUMBER() OVER (ORDER BY SUM(quantity * unit_price) DESC) AS sales_rank FROM quarterly_sales GROUP BY product_name ORDER BY total_sales DESC """; try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(productRankingQuery)) { System.out.println("\n=== 产品销售排名 ==="); System.out.printf("%-20s %-15s %-10s%n", "产品名", "总销售额", "排名"); while (rs.next()) { String productName = rs.getString("product_name"); BigDecimal totalSales = rs.getBigDecimal("total_sales"); long salesRank = rs.getLong("sales_rank"); System.out.printf("%-20s %-15s %-10d%n", productName, totalSales.setScale(2, java.math.RoundingMode.HALF_UP), salesRank); } } } catch (SQLException e) { System.err.println("生成销售报告时发生错误: " + e.getMessage()); e.printStackTrace(); } finally { try { if (conn != null && !conn.isClosed()) { conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } } public static void main(String[] args) { generateSalesReport(); } }

代码解释

连接数据库：获取数据库连接。

产品销售分析：使用 SUM(quantity * unit_price) 计算每个产品的总销售额。使用 AVG(unit_price) 计算每个产品的平均单价。使用 SUM(quantity) 计算每个产品的总销量。使用 GROUP BY product_name 按产品分组。使用 ORDER BY total_sales DESC 按销售额降序排列。

区域销售分析：使用 SUM(quantity * unit_price) 计算每个区域的总销售额。使用 GROUP BY region 按区域分组。使用 ORDER BY total_sales DESC 按销售额降序排列。

季度销售分析：使用 SUM(quantity * unit_price) 计算每个季度的总销售额。使用 GROUP BY quarter 按季度分组。使用 ORDER BY quarter 按季度升序排列。

季度增长率分析：使用 WITH 子句创建一个临时结果集 quarterly_totals，计算每个季度的总销售额。使用 LAG 窗口函数获取上一季度的销售额。计算增长率：(本期销售额 - 上期销售额) / 上期销售额 * 100。使用 CASE 语句处理第一个季度没有上期数据的情况。

产品销售排名：使用 ROW_NUMBER() OVER (ORDER BY SUM(quantity * unit_price) DESC) 为产品分配销售额排名。使用 GROUP BY product_name 按产品分组。使用 ORDER BY total_sales DESC 按销售额降序排列。

输出报告：在控制台打印出所有分析结果。

总结：函数是数据库操作的利器

通过今天的深入学习，我们掌握了 MySQL 中最常用的字符串函数、日期函数和聚合函数。这些函数不仅仅是简单的工具，更是解决实际业务问题的强大武器。从简单的字符串拼接、日期计算，到复杂的统计分析，函数的应用无处不在。

字符串函数：帮助我们处理和转换文本数据，确保数据格式统一、整洁。日期函数：使我们能够高效地处理时间相关的业务逻辑，如计算周期、格式化显示等。聚合函数：是数据分析的核心，让我们能够快速洞察数据背后的规律和趋势。

在实际项目中，熟练运用这些函数，不仅能提高开发效率，还能写出更加健壮和易维护的 SQL 查询语句。希望这篇博客能为你在 MySQL 的道路上提供坚实的基础和实用的指导！

???? 了解更多关于 MySQL 函数的信息，请参考以下官方文档和权威资源：

MySQL 8.0 Reference Manual - Functions and Operators

MySQL 8.0 Reference Manual - String Functions

MySQL 8.0 Reference Manual - Date and Time Functions

MySQL 8.0 Reference Manual - Aggregate (GROUP BY) Functions

W3Schools SQL Functions

GeeksforGeeks - MySQL String Functions

GeeksforGeeks - MySQL Date Functions

GeeksforGeeks - MySQL Aggregate Functions

Mermaid 图表：字符串函数关系图

Mermaid 图表：日期函数关系图

Mermaid 图表：聚合函数关系图

Mermaid 图表：函数综合应用示意图

总结

到此这篇关于MySQL常用函数之字符串、日期、聚合函数的文章就介绍到这了,

编辑推荐：

返回列表

一、字符串函数

1. 字符串长度函数LENGTH()和CHAR_LENGTH()

示例：

Java 代码示例：

代码解释

2. 字符串截取函数SUBSTRING()/SUBSTR()和LEFT()/RIGHT()

示例：

Java 代码示例：

代码解释

3. 字符串替换函数REPLACE()

示例：

Java 代码示例：

代码解释

4. 大小写转换函数UPPER()/UCASE()和LOWER()/LCASE()

示例：

Java 代码示例：

代码解释

5. 去除空格函数TRIM()/LTRIM()/RTRIM()

示例：

Java 代码示例：

代码解释

6. 字符串连接函数CONCAT()

示例：

Java 代码示例：

代码解释

二、日期函数

1. 当前日期和时间函数NOW(),CURDATE(),CURTIME()

示例：

Java 代码示例：

代码解释

2. 日期和时间组件提取函数YEAR(),MONTH(),DAY(),HOUR(),MINUTE(),SECOND()

示例：

Java 代码示例：

代码解释

3. 日期计算函数DATE_ADD()/ADDDATE(),DATE_SUB()/SUBDATE()

示例：

Java 代码示例：

代码解释

4. 日期差函数DATEDIFF()和TIMESTAMPDIFF()

示例：

Java 代码示例：

代码解释

5. 日期格式化函数DATE_FORMAT()

示例：

Java 代码示例：

代码解释

三、聚合函数

1. 计数函数COUNT()

示例：

Java 代码示例：

代码解释

2. 求和函数SUM()

示例：

Java 代码示例：

代码解释

3. 平均值函数AVG()

示例：

Java 代码示例：

代码解释

4. 最大值和最小值函数MAX()和MIN()

示例：

Java 代码示例：

代码解释

5. 聚合函数与分组GROUP BY结合使用

示例：

Java 代码示例：

代码解释

四、综合实战：销售数据分析

实战目标

数据准备

Java 代码示例

代码解释

总结：函数是数据库操作的利器

Mermaid 图表：字符串函数关系图

Mermaid 图表：日期函数关系图

Mermaid 图表：聚合函数关系图

Mermaid 图表：函数综合应用示意图

总结

编辑推荐：

相关推荐