MySQL 常用函数实战：字符串、日期与聚合函数

代码解释

1. 创建表：首先创建一个 sample_users 表用于存储示例数据。
2. 插入数据：使用 PreparedStatement 批量插入包含英文和中文的测试数据。
3. 查询与展示：执行 SQL 查询，同时使用 LENGTH() 和 CHAR_LENGTH() 计算 description 字段的长度，并在 Java 控制台打印结果。
4. 输出对比：对于中文字符，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, RIGHT(product_code, 3) AS last_three_digits, SUBSTRING(product_name, 1, 5) AS substring_brand, SUBSTRING(product_code, 4, 3) AS middle_part_code 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();
    }
}

代码解释

1. 创建表：创建 products 表存储产品信息。
2. 插入数据：插入三条包含产品名称和代码的测试数据。
3. 查询与演示：使用 LEFT, RIGHT, SUBSTRING 等函数提取特定部分的数据。
4. 输出结果：清晰地展示了不同截取函数的效果。

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

REPLACE(str, from_str, to_str) 用于将字符串 str 中所有出现的 from_str 替换为 to_str。

示例：

SELECT REPLACE('Hello World', 'World', 'MySQL') AS result; -- 输出：Hello MySQL
SELECT REPLACE('abcabcabc', 'bc', 'XY') AS result; -- 输出：aXYaXYaXY

Java 代码示例：

import java.sql.*;

public class ReplaceExample {
    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 demonstrateReplaceFunction() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
            String createTableSQL = "CREATE TABLE IF NOT EXISTS user_profiles ( id INT AUTO_INCREMENT PRIMARY KEY, full_name VARCHAR(100), email VARCHAR(100), bio TEXT )";
            try (Statement stmt = conn.createStatement()) {
                stmt.execute(createTableSQL);
            }
            String insertSQL = "INSERT INTO user_profiles (full_name, email, bio) VALUES (?, ?, ?)";
            try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
                pstmt.setString(1, "张小明");
                pstmt.setString(2, "[email protected]");
                pstmt.setString(3, "我是张小明，热爱技术。联系方式：[email protected]");
                pstmt.addBatch();
                pstmt.setString(1, "李丽");
                pstmt.setString(2, "[email protected]");
                pstmt.setString(3, "李丽，软件工程师。邮箱地址：[email protected]");
                pstmt.addBatch();
                pstmt.executeBatch();
                System.out.println("✅ 用户资料已插入。");
            }
            String querySQL = "SELECT full_name, email, bio, REPLACE(bio, '联系方式', '联系信息') AS modified_bio, REPLACE(email, '@', '[AT]') AS masked_email FROM user_profiles";
            try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(querySQL)) {
                System.out.println("\n=== 字符串替换函数演示 ===");
                System.out.printf("%-10s %-25s %-50s %-50s %-25s%n", "姓名", "邮箱", "原始简介", "修改后简介", "屏蔽邮箱");
                while (rs.next()) {
                    String fullName = rs.getString("full_name");
                    String email = rs.getString("email");
                    String bio = rs.getString("bio");
                    String modifiedBio = rs.getString("modified_bio");
                    String maskedEmail = rs.getString("masked_email");
                    System.out.printf("%-10s %-25s %-50s %-50s %-25s%n", fullName, email, bio, modifiedBio, maskedEmail);
                }
            }
        } 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) {
        demonstrateReplaceFunction();
    }
}

代码解释

1. 创建表：创建 user_profiles 表存储用户信息。
2. 插入数据：插入包含用户姓名、邮箱和简介的测试数据。
3. 查询与演示：使用 REPLACE 替换特定文本或脱敏敏感信息。
4. 输出结果：展示原始数据和替换后的效果。

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

这些函数用于将字符串转换为大写或小写。

• UPPER(str) / UCASE(str)：将字符串转换为大写。
• LOWER(str) / LCASE(str)：将字符串转换为小写。

示例：

SELECT UPPER('hello world') AS upper_result; -- 输出：HELLO WORLD
SELECT LOWER('HELLO WORLD') AS lower_result; -- 输出：hello world

Java 代码示例：

import java.sql.*;

public class CaseConversionExample {
    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 demonstrateCaseConversion() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
            String createTableSQL = "CREATE TABLE IF NOT EXISTS employees ( id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), department VARCHAR(50) )";
            try (Statement stmt = conn.createStatement()) {
                stmt.execute(createTableSQL);
            }
            String insertSQL = "INSERT INTO employees (first_name, last_name, department) VALUES (?, ?, ?)";
            try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
                pstmt.setString(1, "john");
                pstmt.setString(2, "doe");
                pstmt.setString(3, "IT");
                pstmt.addBatch();
                pstmt.setString(1, "Jane");
                pstmt.setString(2, "SMITH");
                pstmt.setString(3, "HR");
                pstmt.addBatch();
                pstmt.setString(1, "michael");
                pstmt.setString(2, "Brown");
                pstmt.setString(3, "Finance");
                pstmt.addBatch();
                pstmt.executeBatch();
                System.out.println("✅ 员工数据已插入。");
            }
            String querySQL = "SELECT first_name, last_name, department, UPPER(first_name) AS upper_first_name, LOWER(last_name) AS lower_last_name, CONCAT(UPPER(SUBSTRING(first_name, 1, 1)), LOWER(SUBSTRING(first_name, 2))) AS title_case_first_name FROM employees";
            try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(querySQL)) {
                System.out.println("\n=== 大小写转换函数演示 ===");
                System.out.printf("%-10s %-10s %-10s %-20s %-20s %-30s%n", "名", "姓", "部门", "大写名", "小写姓", "标题格式名");
                while (rs.next()) {
                    String firstName = rs.getString("first_name");
                    String lastName = rs.getString("last_name");
                    String department = rs.getString("department");
                    String upperFirstName = rs.getString("upper_first_name");
                    String lowerLastName = rs.getString("lower_last_name");
                    String titleCaseFirstName = rs.getString("title_case_first_name");
                    System.out.printf("%-10s %-10s %-10s %-20s %-20s %-30s%n", firstName, lastName, department, upperFirstName, lowerLastName, titleCaseFirstName);
                }
            }
        } 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) {
        demonstrateCaseConversion();
    }
}

代码解释

1. 创建表：创建 employees 表存储员工信息。
2. 插入数据：插入包含员工名、姓和部门的测试数据。
3. 查询与演示：使用 UPPER, LOWER 及组合函数实现大小写转换。
4. 输出结果：展示了原始数据和各种大小写转换后的效果。

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

这些函数用于去除字符串两端或特定一侧的空白字符。

• TRIM(str)：去除字符串两端的空白字符。
• LTRIM(str)：去除字符串左侧的空白字符。
• RTRIM(str)：去除字符串右侧的空白字符。

示例：

SELECT TRIM(' Hello ') AS result; -- 输出：Hello
SELECT LTRIM(' Hello') AS result; -- 输出：Hello
SELECT RTRIM('Hello ') AS result; -- 输出：Hello

Java 代码示例：

import java.sql.*;

public class TrimExample {
    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 demonstrateTrimFunctions() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
            String createTableSQL = "CREATE TABLE IF NOT EXISTS user_input_logs ( id INT AUTO_INCREMENT PRIMARY KEY, raw_data VARCHAR(255), processed_data VARCHAR(255) )";
            try (Statement stmt = conn.createStatement()) {
                stmt.execute(createTableSQL);
            }
            String insertSQL = "INSERT INTO user_input_logs (raw_data, processed_data) VALUES (?, ?)";
            try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
                pstmt.setString(1, " John Doe ");
                pstmt.setString(2, "");
                pstmt.addBatch();
                pstmt.setString(1, "\t\tAlice Smith\n\n");
                pstmt.setString(2, "");
                pstmt.addBatch();
                pstmt.setString(1, " \r\nBob Johnson ");
                pstmt.setString(2, "");
                pstmt.addBatch();
                pstmt.executeBatch();
                System.out.println("✅ 用户输入日志已插入。");
            }
            String querySQL = "SELECT raw_data, TRIM(raw_data) AS trimmed_data, LTRIM(raw_data) AS left_trimmed_data, RTRIM(raw_data) AS right_trimmed_data FROM user_input_logs";
            try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(querySQL)) {
                System.out.println("\n=== 去除空格函数演示 ===");
                System.out.printf("%-25s %-25s %-25s %-25s%n", "原始数据", "两端去空格", "左去空格", "右去空格");
                while (rs.next()) {
                    String rawData = rs.getString("raw_data");
                    String trimmedData = rs.getString("trimmed_data");
                    String leftTrimmedData = rs.getString("left_trimmed_data");
                    String rightTrimmedData = rs.getString("right_trimmed_data");
                    System.out.printf("%-25s %-25s %-25s %-25s%n", rawData, trimmedData, leftTrimmedData, rightTrimmedData);
                }
            }
        } 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) {
        demonstrateTrimFunctions();
    }
}

代码解释

1. 创建表：创建 user_input_logs 表用于记录用户输入。
2. 插入数据：插入包含前后空格、制表符、换行符的原始数据。
3. 查询与演示：使用 TRIM, LTRIM, RTRIM 去除空白字符。
4. 输出结果：展示了原始数据和各种去空格处理后的效果。

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

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

示例：

SELECT CONCAT('Hello', ' ', 'World') AS result; -- 输出：Hello World
SELECT CONCAT_WS('-', '2023', '10', '15') AS date_string; -- 使用分隔符连接

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();
    }
}

代码解释

1. 创建表：创建 addresses 表存储地址信息。
2. 插入数据：插入包含街道、城市、州、邮编的测试数据。
3. 查询与演示：使用 CONCAT 和 CONCAT_WS 拼接地址信息。
4. 输出结果：展示了原始数据和拼接后的地址效果。

二、日期函数

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

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

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

• NOW()：返回当前日期和时间。
• CURDATE()：返回当前日期。
• CURTIME()：返回当前时间。

示例：

SELECT NOW() AS current_datetime;
SELECT CURDATE() AS current_date;
SELECT CURTIME() AS current_time;

Java 代码示例：

import java.sql.*;

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 )";
            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"));
                pstmt.setTimestamp(3, Timestamp.valueOf("2023-10-15 10:00:00"));
                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 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%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");
                    System.out.printf("%-20s %-12s %-20s %-20s %-12s %-12s%n", eventName, eventDate, createdAt, dbNow, dbCurrentDate, dbCurrentTime);
                }
            }
        } 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();
    }
}

代码解释

1. 创建表：创建 events 表存储事件信息。
2. 插入数据：插入两条事件记录。
3. 查询与演示：使用 NOW(), CURDATE(), CURTIME() 获取当前时间信息。
4. 输出结果：展示了原始数据和当前系统时间。

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

这些函数用于从日期时间值中提取特定的组件。

示例：

SELECT YEAR('2023-10-15') AS year_value;
SELECT MONTH('2023-10-15') AS month_value;
SELECT DAY('2023-10-15') AS day_value;

Java 代码示例：

import java.sql.*;

public class ExtractDateTimeComponentsExample {
    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 demonstrateExtractDateTimeComponents() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
            String createTableSQL = "CREATE TABLE IF NOT EXISTS orders ( id INT AUTO_INCREMENT PRIMARY KEY, customer_name VARCHAR(100), order_date DATETIME )";
            try (Statement stmt = conn.createStatement()) {
                stmt.execute(createTableSQL);
            }
            String insertSQL = "INSERT INTO orders (customer_name, order_date) VALUES (?, ?)";
            try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
                pstmt.setString(1, "张三");
                pstmt.setTimestamp(2, Timestamp.valueOf("2023-10-10 14:20:00"));
                pstmt.addBatch();
                pstmt.setString(1, "李四");
                pstmt.setTimestamp(2, Timestamp.valueOf("2023-10-12 09:15:30"));
                pstmt.addBatch();
                pstmt.executeBatch();
                System.out.println("✅ 订单数据已插入。");
            }
            String querySQL = "SELECT customer_name, order_date, YEAR(order_date) AS order_year, MONTH(order_date) AS order_month, DAY(order_date) AS order_day FROM orders";
            try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(querySQL)) {
                System.out.println("\n=== 日期时间组件提取演示 ===");
                System.out.printf("%-10s %-20s %-10s %-10s %-10s%n", "客户名", "下单时间", "年", "月", "日");
                while (rs.next()) {
                    String customerName = rs.getString("customer_name");
                    Timestamp orderDate = rs.getTimestamp("order_date");
                    int orderYear = rs.getInt("order_year");
                    int orderMonth = rs.getInt("order_month");
                    int orderDay = rs.getInt("order_day");
                    System.out.printf("%-10s %-20s %-10d %-10d %-10d%n", customerName, orderDate, orderYear, orderMonth, orderDay);
                }
            }
        } 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) {
        demonstrateExtractDateTimeComponents();
    }
}

代码解释

1. 创建表：创建 orders 表存储订单信息。
2. 插入数据：插入三条订单记录。
3. 查询与演示：使用 YEAR, MONTH, DAY 等函数提取时间组件。
4. 输出结果：展示了原始时间数据和各个组件的提取结果。

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

这些函数用于对日期进行加减运算。

• DATE_ADD(date, INTERVAL value unit)：给日期加上一个时间间隔。
• DATE_SUB(date, INTERVAL value unit)：从日期中减去一个时间间隔。

示例：

SELECT DATE_ADD('2023-10-15', INTERVAL 1 DAY) AS next_day;
SELECT DATE_SUB('2023-10-15', INTERVAL 1 MONTH) AS last_month;

Java 代码示例：

import java.sql.*;

public class DateCalculationExample {
    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 demonstrateDateCalculations() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
            String createTableSQL = "CREATE TABLE IF NOT EXISTS tasks ( id INT AUTO_INCREMENT PRIMARY KEY, task_name VARCHAR(255), start_date DATE, duration_days INT )";
            try (Statement stmt = conn.createStatement()) {
                stmt.execute(createTableSQL);
            }
            String insertSQL = "INSERT INTO tasks (task_name, start_date, duration_days) VALUES (?, ?, ?)";
            try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
                pstmt.setString(1, "需求分析");
                pstmt.setDate(2, Date.valueOf("2023-10-10"));
                pstmt.setInt(3, 3);
                pstmt.addBatch();
                pstmt.setString(1, "系统设计");
                pstmt.setDate(2, Date.valueOf("2023-10-15"));
                pstmt.setInt(3, 5);
                pstmt.addBatch();
                pstmt.executeBatch();
                System.out.println("✅ 任务数据已插入。");
            }
            String querySQL = "SELECT task_name, start_date, duration_days, DATE_ADD(start_date, INTERVAL duration_days DAY) AS calculated_due_date FROM tasks";
            try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(querySQL)) {
                System.out.println("\n=== 日期计算函数演示 ===");
                System.out.printf("%-15s %-12s %-15s %-20s%n", "任务名称", "开始日期", "持续天数", "计算截止日期");
                while (rs.next()) {
                    String taskName = rs.getString("task_name");
                    Date startDate = rs.getDate("start_date");
                    int durationDays = rs.getInt("duration_days");
                    Date calculatedDueDate = rs.getDate("calculated_due_date");
                    System.out.printf("%-15s %-12s %-15d %-20s%n", taskName, startDate, durationDays, calculatedDueDate);
                }
            }
        } 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) {
        demonstrateDateCalculations();
    }
}

代码解释

1. 创建表：创建 tasks 表存储任务信息。
2. 插入数据：插入两条任务记录。
3. 查询与演示：使用 DATE_ADD 计算任务的预计截止日期。
4. 输出结果：展示了原始数据和各种日期计算的结果。

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

这些函数用于计算两个日期之间的差值。

• DATEDIFF(date1, date2)：计算 date2 与 date1 之间的天数差。
• TIMESTAMPDIFF(unit, datetime1, datetime2)：计算 datetime2 与 datetime1 之间的差值，单位由 unit 指定。

示例：

SELECT DATEDIFF('2023-10-20', '2023-10-15') AS diff_days;
SELECT TIMESTAMPDIFF(HOUR, '2023-10-15 10:00:00', '2023-10-15 14:30:00') AS diff_hours;

Java 代码示例：

import java.sql.*;

public class DateDifferenceExample {
    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 demonstrateDateDifferences() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
            String createTableSQL = "CREATE TABLE IF NOT EXISTS project_milestones ( id INT AUTO_INCREMENT PRIMARY KEY, milestone_name VARCHAR(255), planned_date DATE, actual_date DATE )";
            try (Statement stmt = conn.createStatement()) {
                stmt.execute(createTableSQL);
            }
            String insertSQL = "INSERT INTO project_milestones (milestone_name, planned_date, actual_date) VALUES (?, ?, ?)";
            try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
                pstmt.setString(1, "需求评审");
                pstmt.setDate(2, Date.valueOf("2023-10-10"));
                pstmt.setDate(3, Date.valueOf("2023-10-12"));
                pstmt.addBatch();
                pstmt.setString(1, "原型设计");
                pstmt.setDate(2, Date.valueOf("2023-10-15"));
                pstmt.setDate(3, Date.valueOf("2023-10-20"));
                pstmt.addBatch();
                pstmt.executeBatch();
                System.out.println("✅ 项目里程碑数据已插入。");
            }
            String querySQL = "SELECT milestone_name, planned_date, actual_date, DATEDIFF(actual_date, planned_date) AS days_difference FROM project_milestones WHERE actual_date IS NOT NULL";
            try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(querySQL)) {
                System.out.println("\n=== 日期差函数演示 ===");
                System.out.printf("%-15s %-12s %-12s %-15s%n", "里程碑名称", "计划日期", "实际日期", "天数差");
                while (rs.next()) {
                    String milestoneName = rs.getString("milestone_name");
                    Date plannedDate = rs.getDate("planned_date");
                    Date actualDate = rs.getDate("actual_date");
                    int daysDifference = rs.getInt("days_difference");
                    System.out.printf("%-15s %-12s %-12s %-15d%n", milestoneName, plannedDate, actualDate, daysDifference);
                }
            }
        } 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) {
        demonstrateDateDifferences();
    }
}

代码解释

1. 创建表：创建 project_milestones 表存储项目里程碑信息。
2. 插入数据：插入三条里程碑记录。
3. 查询与演示：使用 DATEDIFF 计算实际完成日期与计划日期的天数差。
4. 输出结果：展示了每个已完成里程碑的详细日期差信息。

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

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

示例：

SELECT DATE_FORMAT('2023-10-15', '%Y-%m-%d') AS formatted_date;
SELECT DATE_FORMAT('2023-10-15 14:30:45', '%Y/%m/%d %H:%i:%s') AS formatted_datetime;

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 )";
            try (Statement stmt = conn.createStatement()) {
                stmt.execute(createTableSQL);
            }
            String insertSQL = "INSERT INTO blog_posts (title, publish_date) VALUES (?, ?)";
            try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
                pstmt.setString(1, "MySQL 教程入门");
                pstmt.setTimestamp(2, Timestamp.valueOf("2023-10-10 09:00:00"));
                pstmt.addBatch();
                pstmt.setString(1, "Java 编程技巧");
                pstmt.setTimestamp(2, Timestamp.valueOf("2023-10-12 16:30:00"));
                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 FROM blog_posts";
            try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(querySQL)) {
                System.out.println("\n=== 日期格式化函数演示 ===");
                System.out.printf("%-25s %-20s %-15s %-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");
                    System.out.printf("%-25s %-20s %-15s %-20s%n", title, publishDate, simpleDate, fullDatetime);
                }
            }
        } 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();
    }
}

代码解释

1. 创建表：创建 blog_posts 表存储博客文章信息。
2. 插入数据：插入三条博客文章记录。
3. 查询与演示：使用 DATE_FORMAT 输出不同的日期格式。
4. 输出结果：展示了每篇文章的原始发布日期和各种格式化后的日期显示效果。

三、聚合函数

聚合函数是对一组值进行计算并返回单一值的函数。它们常用于 GROUP BY 子句中进行统计分析。

1. 计数函数 COUNT()

COUNT() 用于计算行数或非空值的数量。

• COUNT(*)：计算所有行数。
• COUNT(column)：计算指定列中非 NULL 值的数量。
• COUNT(DISTINCT column)：计算指定列中不同非 NULL 值的数量。

示例：

SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT department) FROM employees;

Java 代码示例：

import java.sql.*;

public class CountFunctionExample {
    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 demonstrateCountFunction() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
            String createTableSQL = "CREATE TABLE IF NOT EXISTS sales ( id INT AUTO_INCREMENT PRIMARY KEY, product_name VARCHAR(255), quantity INT, sale_date DATE )";
            try (Statement stmt = conn.createStatement()) {
                stmt.execute(createTableSQL);
            }
            String insertSQL = "INSERT INTO sales (product_name, quantity, sale_date) VALUES (?, ?, ?)";
            try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
                pstmt.setString(1, "iPhone 14");
                pstmt.setInt(2, 2);
                pstmt.setDate(3, Date.valueOf("2023-10-10"));
                pstmt.addBatch();
                pstmt.setString(1, "AirPods Pro");
                pstmt.setInt(2, 1);
                pstmt.setDate(3, null);
                pstmt.addBatch();
                pstmt.executeBatch();
                System.out.println("✅ 销售数据已插入。");
            }
            String querySQL = "SELECT COUNT(*) AS total_sales, COUNT(sale_date) AS sales_with_date FROM sales";
            try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(querySQL)) {
                System.out.println("\n=== 计数函数演示 ===");
                while (rs.next()) {
                    long totalSales = rs.getLong("total_sales");
                    long salesWithDate = rs.getLong("sales_with_date");
                    System.out.println("总销售额：" + totalSales);
                    System.out.println("有销售日期的销售额：" + salesWithDate);
                }
            }
        } 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) {
        demonstrateCountFunction();
    }
}

代码解释

1. 创建表：创建 sales 表存储销售记录。
2. 插入数据：插入六条销售记录，其中一条销售日期为空。
3. 查询与演示：使用 COUNT(*) 和 COUNT(sale_date) 对比统计结果。
4. 输出结果：展示了各种计数的结果。

2. 求和函数 SUM()

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

示例：

SELECT SUM(price) FROM sales;
SELECT SUM(quantity * price) FROM sales;

Java 代码示例：

import java.sql.*;

public class SumFunctionExample {
    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 demonstrateSumFunction() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
            String createTableSQL = "CREATE TABLE IF NOT EXISTS inventory ( id INT AUTO_INCREMENT PRIMARY KEY, item_name VARCHAR(255), stock_quantity INT, unit_price DECIMAL(10, 2) )";
            try (Statement stmt = conn.createStatement()) {
                stmt.execute(createTableSQL);
            }
            String insertSQL = "INSERT INTO inventory (item_name, stock_quantity, unit_price) VALUES (?, ?, ?)";
            try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
                pstmt.setString(1, "笔记本电脑");
                pstmt.setInt(2, 50);
                pstmt.setBigDecimal(3, new java.math.BigDecimal("5999.99"));
                pstmt.addBatch();
                pstmt.setString(1, "台式机");
                pstmt.setInt(2, 30);
                pstmt.setBigDecimal(3, new java.math.BigDecimal("3999.99"));
                pstmt.addBatch();
                pstmt.executeBatch();
                System.out.println("✅ 库存数据已插入。");
            }
            String querySQL = "SELECT SUM(stock_quantity) AS total_stock, SUM(stock_quantity * unit_price) AS total_inventory_value FROM inventory";
            try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(querySQL)) {
                System.out.println("\n=== 求和函数演示 ===");
                while (rs.next()) {
                    long totalStock = rs.getLong("total_stock");
                    java.math.BigDecimal totalInventoryValue = rs.getBigDecimal("total_inventory_value");
                    System.out.println("总库存数量：" + totalStock);
                    System.out.println("总库存价值：¥" + totalInventoryValue.setScale(2, java.math.RoundingMode.HALF_UP));
                }
            }
        } 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) {
        demonstrateSumFunction();
    }
}

代码解释

1. 创建表：创建 inventory 表存储库存信息。
2. 插入数据：插入五条库存记录。
3. 查询与演示：使用 SUM 计算总库存数量和总价值。
4. 输出结果：展示了库存总量及价值。

3. 平均值函数 AVG()

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

示例：

SELECT AVG(price) FROM sales;

Java 代码示例：

import java.sql.*;

public class AvgFunctionExample {
    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 demonstrateAvgFunction() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
            String createTableSQL = "CREATE TABLE IF NOT EXISTS student_scores ( id INT AUTO_INCREMENT PRIMARY KEY, student_name VARCHAR(100), subject VARCHAR(50), score DECIMAL(5, 2) )";
            try (Statement stmt = conn.createStatement()) {
                stmt.execute(createTableSQL);
            }
            String insertSQL = "INSERT INTO student_scores (student_name, subject, score) VALUES (?, ?, ?)";
            try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
                pstmt.setString(1, "张三");
                pstmt.setString(2, "数学");
                pstmt.setBigDecimal(3, new java.math.BigDecimal("85.5"));
                pstmt.addBatch();
                pstmt.setString(1, "张三");
                pstmt.setString(2, "英语");
                pstmt.setBigDecimal(3, new java.math.BigDecimal("92.0"));
                pstmt.addBatch();
                pstmt.executeBatch();
                System.out.println("✅ 学生成绩数据已插入。");
            }
            String querySQL = "SELECT subject, AVG(score) AS avg_score FROM student_scores GROUP BY subject";
            try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(querySQL)) {
                System.out.println("\n=== 平均值函数演示 ===");
                System.out.printf("%-10s %-15s%n", "科目", "平均分");
                while (rs.next()) {
                    String subject = rs.getString("subject");
                    BigDecimal avgScore = rs.getBigDecimal("avg_score");
                    System.out.printf("%-10s %-15s%n", subject, avgScore);
                }
            }
        } 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) {
        demonstrateAvgFunction();
    }
}

代码解释

1. 创建表：创建 student_scores 表存储学生成绩信息。
2. 插入数据：插入多条成绩记录。
3. 查询与演示：使用 GROUP BY 和 AVG 计算各科目的平均分。
4. 输出结果：展示了各科目的平均分。

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

MAX() 和 MIN() 用于找出列中的最大值和最小值。

示例：

SELECT MAX(price) FROM sales;
SELECT MIN(price) FROM sales;

Java 代码示例：

import java.sql.*;

public class MaxMinFunctionExample {
    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 demonstrateMaxMinFunctions() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
            String createTableSQL = "CREATE TABLE IF NOT EXISTS employee_salaries ( id INT AUTO_INCREMENT PRIMARY KEY, employee_name VARCHAR(100), salary DECIMAL(10, 2) )";
            try (Statement stmt = conn.createStatement()) {
                stmt.execute(createTableSQL);
            }
            String insertSQL = "INSERT INTO employee_salaries (employee_name, salary) VALUES (?, ?)";
            try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
                pstmt.setString(1, "张经理");
                pstmt.setBigDecimal(2, new java.math.BigDecimal("15000.00"));
                pstmt.addBatch();
                pstmt.setString(1, "陈专员");
                pstmt.setBigDecimal(2, new java.math.BigDecimal("7500.00"));
                pstmt.addBatch();
                pstmt.executeBatch();
                System.out.println("✅ 员工薪资数据已插入。");
            }
            String querySQL = "SELECT MAX(salary) AS highest_salary, MIN(salary) AS lowest_salary FROM employee_salaries";
            try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(querySQL)) {
                System.out.println("\n=== 最大值和最小值函数演示 ===");
                while (rs.next()) {
                    java.math.BigDecimal highestSalary = rs.getBigDecimal("highest_salary");
                    java.math.BigDecimal lowestSalary = rs.getBigDecimal("lowest_salary");
                    System.out.println("最高薪资：¥" + highestSalary.setScale(2, java.math.RoundingMode.HALF_UP));
                    System.out.println("最低薪资：¥" + lowestSalary.setScale(2, java.math.RoundingMode.HALF_UP));
                }
            }
        } 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) {
        demonstrateMaxMinFunctions();
    }
}

代码解释

1. 创建表：创建 employee_salaries 表存储员工薪资信息。
2. 插入数据：插入六条员工记录。
3. 查询与演示：使用 MAX 和 MIN 找出最高和最低薪资。
4. 输出结果：展示了员工薪资的统计信息。

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

聚合函数通常与 GROUP BY 子句一起使用，以对数据进行分组并计算每组的聚合值。

示例：

SELECT department, COUNT(*) FROM employees GROUP BY department;
SELECT department, AVG(salary) FROM employees GROUP BY department;

Java 代码示例：

import java.sql.*;

public class GroupByAggregationExample {
    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 demonstrateGroupByAggregation() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
            String createTableSQL = "CREATE TABLE IF NOT EXISTS transactions ( id INT AUTO_INCREMENT PRIMARY KEY, customer_name VARCHAR(100), amount DECIMAL(10, 2) )";
            try (Statement stmt = conn.createStatement()) {
                stmt.execute(createTableSQL);
            }
            String insertSQL = "INSERT INTO transactions (customer_name, amount) VALUES (?, ?)";
            try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
                pstmt.setString(1, "张三");
                pstmt.setBigDecimal(2, new java.math.BigDecimal("150.00"));
                pstmt.addBatch();
                pstmt.setString(1, "张三");
                pstmt.setBigDecimal(2, new java.math.BigDecimal("80.00"));
                pstmt.addBatch();
                pstmt.setString(1, "李四");
                pstmt.setBigDecimal(2, new java.math.BigDecimal("200.00"));
                pstmt.addBatch();
                pstmt.executeBatch();
                System.out.println("✅ 交易数据已插入。");
            }
            String querySQL = "SELECT customer_name, COUNT(*) AS transaction_count, SUM(amount) AS total_amount FROM transactions GROUP BY customer_name";
            try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(querySQL)) {
                System.out.println("\n=== 分组聚合函数演示 ===");
                System.out.printf("%-10s %-15s %-15s%n", "客户名", "交易次数", "总金额");
                while (rs.next()) {
                    String customerName = rs.getString("customer_name");
                    long transactionCount = rs.getLong("transaction_count");
                    java.math.BigDecimal totalAmount = rs.getBigDecimal("total_amount");
                    System.out.printf("%-10s %-15d %-15s%n", customerName, transactionCount, totalAmount.setScale(2, java.math.RoundingMode.HALF_UP));
                }
            }
        } 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) {
        demonstrateGroupByAggregation();
    }
}

代码解释

1. 创建表：创建 transactions 表存储交易记录。
2. 插入数据：插入多条交易记录。
3. 查询与演示：使用 GROUP BY 按客户分组，并计算交易次数和总金额。
4. 输出结果：展示了每位客户的详细交易统计信息。

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

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

实战目标

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

1. 每个产品的销售总额。
2. 每个产品的平均销售价格。
3. 每个区域的销售总额。
4. 每个季度的销售总额。

数据准备

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)
);

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'),
('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'),
('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'),
('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 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%n", "产品名", "总销售额", "平均单价");
                while (rs.next()) {
                    String productName = rs.getString("product_name");
                    BigDecimal totalSales = rs.getBigDecimal("total_sales");
                    BigDecimal avgPrice = rs.getBigDecimal("avg_price");
                    System.out.printf("%-20s %-15s %-15s%n", productName, totalSales.setScale(2, BigDecimal.ROUND_HALF_UP), avgPrice.setScale(2, BigDecimal.ROUND_HALF_UP));
                }
            }
            // 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, BigDecimal.ROUND_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, BigDecimal.ROUND_HALF_UP));
                }
            }
        } 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();
    }
}

代码解释

1. 连接数据库：获取数据库连接。
2. 产品销售分析：使用 SUM 和 AVG 计算每个产品的总销售额和平均单价。
3. 区域销售分析：使用 SUM 计算每个区域的总销售额。
4. 季度销售分析：使用 SUM 计算每个季度的总销售额。
5. 输出报告：在控制台打印出所有分析结果。

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

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

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

在实际项目中，熟练运用这些函数，不仅能提高开发效率，还能写出更加健壮和易维护的 SQL 查询语句。

参考资源

1. MySQL 8.0 Reference Manual - Functions and Operators
2. MySQL 8.0 Reference Manual - String Functions
3. MySQL 8.0 Reference Manual - Date and Time Functions
4. MySQL 8.0 Reference Manual - Aggregate (GROUP BY) Functions

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

graph LR
A[String Functions] --> B[LENGTH/CHAR_LENGTH]
A --> C[SUBSTRING/SUBSTR]
A --> D[LEFT/RIGHT]
A --> E[REPLACE]
A --> F[UPPER/LOWER]
A --> G[TRIM]
A --> H[CONCAT]

Mermaid 图表：日期函数关系图

graph LR
I[Date & Time Functions] --> J[NOW/CURDATE/CURTIME]
I --> K[YEAR/MONTH/DAY]
I --> L[DATE_ADD/DATE_SUB]
I --> M[DATEDIFF/TIMESTAMPDIFF]
I --> N[DATE_FORMAT]

Mermaid 图表：聚合函数关系图

graph LR
O[Aggregate Functions] --> P[COUNT]
O --> Q[SUM]
O --> R[AVG]
O --> S[MAX/MIN]
O --> T[GROUP BY]

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

graph TD
A[原始数据表] --> B[字符串函数处理]
A --> C[日期函数处理]
A --> D[聚合函数处理]
B --> E[数据处理层]
C --> E
D --> E
E --> F[格式化报表]
E --> G[统计图表]
E --> H[分析报告]