本文介绍了 MySQL 中常用的字符串、日期和聚合函数。通过实际 SQL 示例和 Java 代码演示，展示了 LENGTH、SUBSTRING、DATE_FORMAT、COUNT 等函数的用法。内容涵盖数据清洗、时间计算及统计分析场景，帮助开发者高效处理数据库操作。
在数据库的世界里，函数就像是程序员的工具箱，为我们提供了强大的数据处理能力。无论是清洗数据、格式化输出、计算时间差，还是进行统计分析，MySQL 函数都能大显身手。今天，我们将一起深入探索 MySQL 中最常用的几类函数：、 和 。我们将通过丰富的示例和 Java 代码来演示如何在实际项目中运用这些函数。
字符串函数是处理文本数据的基础。在日常开发中，我们经常需要对字符串进行截取、替换、大小写转换、填充等操作。MySQL 提供了大量内置的字符串函数来简化这些任务。
LENGTH() 和 CHAR_LENGTH()LENGTH() 和 CHAR_LENGTH() 都用来计算字符串的长度，但它们的计算方式不同：
LENGTH(str)：计算字符串的字节长度。对于单字节字符集（如 Latin1），它等于字符数；对于多字节字符集（如 UTF8），一个字符可能占用多个字节。CHAR_LENGTH(str)：计算字符串的字符数，忽略字符编码的差异。-- 假设表 users 存储了用户名和描述信息
INSERT INTO users (name, description) VALUES ('Alice', 'Hello World'), ('张三', '你好世界');
SELECT name, description,
LENGTH(description) AS byte_length, -- 计算字节长度
CHAR_LENGTH(description) AS char_length -- 计算字符长度
FROM users;
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 批量插入包含英文和中文的测试数据。LENGTH() 和 CHAR_LENGTH() 计算 description 字段的长度，并在 Java 控制台打印结果。LENGTH() 返回的是字节数（每个中文字符通常占 3 个字节），而 CHAR_LENGTH() 返回的是字符数。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 个字符)
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();
}
}
products 表存储产品信息。LEFT, RIGHT, SUBSTRING 等函数提取特定部分的数据。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
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();
}
}
user_profiles 表存储用户信息。REPLACE 替换特定文本或脱敏敏感信息。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
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();
}
}
employees 表存储员工信息。UPPER, LOWER 及组合函数实现大小写转换。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
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();
}
}
user_input_logs 表用于记录用户输入。TRIM, LTRIM, RTRIM 去除空白字符。CONCAT()CONCAT(str1, str2, ...) 用于将多个字符串连接成一个字符串。如果任意一个参数为 NULL，则结果为 NULL。
SELECT CONCAT('Hello', ' ', 'World') AS result; -- 输出：Hello World
SELECT CONCAT_WS('-', '2023', '10', '15') AS date_string; -- 使用分隔符连接
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 和 CONCAT_WS 拼接地址信息。日期函数是处理时间相关数据的强大工具。在应用开发中，我们经常需要计算日期差、提取日期组件、格式化日期显示、处理时间戳等。
NOW(), CURDATE(), CURTIME()这些函数用于获取当前的日期、时间和时间戳。
NOW()：返回当前日期和时间。CURDATE()：返回当前日期。CURTIME()：返回当前时间。SELECT NOW() AS current_datetime;
SELECT CURDATE() AS current_date;
SELECT CURTIME() AS current_time;
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();
}
}
events 表存储事件信息。NOW(), CURDATE(), CURTIME() 获取当前时间信息。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;
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();
}
}
orders 表存储订单信息。YEAR, MONTH, DAY 等函数提取时间组件。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;
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();
}
}
tasks 表存储任务信息。DATE_ADD 计算任务的预计截止日期。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;
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();
}
}
project_milestones 表存储项目里程碑信息。DATEDIFF 计算实际完成日期与计划日期的天数差。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;
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();
}
}
blog_posts 表存储博客文章信息。DATE_FORMAT 输出不同的日期格式。聚合函数是对一组值进行计算并返回单一值的函数。它们常用于 GROUP BY 子句中进行统计分析。
COUNT()COUNT() 用于计算行数或非空值的数量。
COUNT(*)：计算所有行数。COUNT(column)：计算指定列中非 NULL 值的数量。COUNT(DISTINCT column)：计算指定列中不同非 NULL 值的数量。SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT department) FROM employees;
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();
}
}
sales 表存储销售记录。COUNT(*) 和 COUNT(sale_date) 对比统计结果。SUM()SUM() 用于计算数值列的总和。
SELECT SUM(price) FROM sales;
SELECT SUM(quantity * price) FROM sales;
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();
}
}
inventory 表存储库存信息。SUM 计算总库存数量和总价值。AVG()AVG() 用于计算数值列的平均值。
SELECT AVG(price) FROM sales;
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();
}
}
student_scores 表存储学生成绩信息。GROUP BY 和 AVG 计算各科目的平均分。MAX() 和 MIN()MAX() 和 MIN() 用于找出列中的最大值和最小值。
SELECT MAX(price) FROM sales;
SELECT MIN(price) FROM sales;
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();
}
}
employee_salaries 表存储员工薪资信息。MAX 和 MIN 找出最高和最低薪资。GROUP BY 结合使用聚合函数通常与 GROUP BY 子句一起使用，以对数据进行分组并计算每组的聚合值。
SELECT department, COUNT(*) FROM employees GROUP BY department;
SELECT department, AVG(salary) FROM employees GROUP BY department;
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();
}
}
transactions 表存储交易记录。GROUP BY 按客户分组，并计算交易次数和总金额。让我们结合前面学到的所有函数，进行一次综合实战——构建一个销售数据分析报告。
为一家零售公司生成一份销售报告，包含：
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');
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();
}
}
SUM 和 AVG 计算每个产品的总销售额和平均单价。SUM 计算每个区域的总销售额。SUM 计算每个季度的总销售额。通过今天的深入学习，我们掌握了 MySQL 中最常用的字符串函数、日期函数和聚合函数。这些函数不仅仅是简单的工具，更是解决实际业务问题的强大武器。从简单的字符串拼接、日期计算，到复杂的统计分析，函数的应用无处不在。
在实际项目中，熟练运用这些函数，不仅能提高开发效率，还能写出更加健壮和易维护的 SQL 查询语句。
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]
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]
graph LR
O[Aggregate Functions] --> P[COUNT]
O --> Q[SUM]
O --> R[AVG]
O --> S[MAX/MIN]
O --> T[GROUP BY]
graph TD
A[原始数据表] --> B[字符串函数处理]
A --> C[日期函数处理]
A --> D[聚合函数处理]
B --> E[数据处理层]
C --> E
D --> E
E --> F[格式化报表]
E --> G[统计图表]
E --> H[分析报告]
微信公众号「极客日志」，在微信中扫描左侧二维码关注。展示文案：极客日志 zeeklog
查找任何按下的键的javascript键代码、代码、位置和修饰符。 在线工具，Keycode 信息在线工具，online
JavaScript 字符串转义/反转义；Java 风格 \uXXXX（Native2Ascii）编码与解码。 在线工具，Escape 与 Native 编解码在线工具，online
使用 Prettier 在浏览器内格式化 JavaScript 或 HTML 片段。 在线工具，JavaScript / HTML 格式化在线工具，online
Terser 压缩、变量名混淆，或 javascript-obfuscator 高强度混淆（体积会增大）。 在线工具，JavaScript 压缩与混淆在线工具，online
在线格式化和美化您的 SQL 查询（它支持各种 SQL 方言）。 在线工具，SQL 美化和格式化在线工具，online
解析 INSERT 等受限 SQL，导出为 CSV、JSON、XML、YAML、HTML 表格（见页内语法说明）。 在线工具，SQL转CSV/JSON/XML在线工具，online