30分鐘搞定MySQL常用函數(shù)之字符串、日期、聚合函數(shù)(附實操代碼)
更新時間：2026年04月02日 10:14:39 作者：Jinkxs
MySQL內(nèi)置函數(shù)是數(shù)據(jù)庫操作中提升效率的重要工具,它們能在數(shù)據(jù)庫層面直接完成數(shù)據(jù)處理,減少應(yīng)用層與數(shù)據(jù)庫的交互成本,這篇文章主要介紹了MySQL常用函數(shù)之字符串、日期、聚合函數(shù)的相關(guān)資料,需要的朋友可以參考下
在數(shù)據(jù)庫的世界里，函數(shù)就像是程序員的工具箱，為我們提供了強大的數(shù)據(jù)處理能力。無論是清洗數(shù)據(jù)、格式化輸出、計算時間差，還是進(jìn)行統(tǒng)計分析，MySQL 函數(shù)都能大顯身手。今天，我們將一起深入探索 MySQL 中最常用的幾類函數(shù)：字符串函數(shù)、日期函數(shù) 和 聚合函數(shù)。我們將通過豐富的示例和 Java 代碼來演示如何在實際項目中運用這些函數(shù)，讓你在 30 分鐘內(nèi)輕松掌握它們！
一、字符串函數(shù)
字符串函數(shù)是處理文本數(shù)據(jù)的基礎(chǔ)。在日常開發(fā)中，我們經(jīng)常需要對字符串進(jìn)行截取、替換、大小寫轉(zhuǎn)換、填充等操作。MySQL 提供了大量內(nèi)置的字符串函數(shù)來簡化這些任務(wù)。
1. 字符串長度函數(shù)LENGTH()和CHAR_LENGTH()
LENGTH() 和 CHAR_LENGTH() 都用來計算字符串的長度，但它們的計算方式不同：
LENGTH(str)：計算字符串的字節(jié)長度。對于單字節(jié)字符集（如 Latin1），它等于字符數(shù)；對于多字節(jié)字符集（如 UTF8），一個字符可能占用多個字節(jié)。CHAR_LENGTH(str)：計算字符串的字符數(shù)，忽略字符編碼的差異。
示例：
-- 假設(shè)表 users 存儲了用戶名和描述信息
-- INSERT INTO users (name, description) VALUES ('Alice', 'Hello World'), ('張三', '你好世界');
SELECT
name,
description,
LENGTH(description) AS byte_length, -- 計算字節(jié)長度
CHAR_LENGTH(description) AS char_length -- 計算字符長度
FROM users;
-- 結(jié)果可能類似于：
-- name | description | byte_length | char_length
-- Alice | Hello World | 11 | 11
-- 張三 | 你好世界 | 12 | 4 (UTF8下中文字符占3字節(jié))Java 代碼示例：
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class StringFunctionExample {
private static final String DB_URL = "jdbc:mysql://localhost:3306/test_db?useSSL=false&serverTimezone=UTC";
private static final String DB_USER = "root"; // 替換為你的用戶名
private static final String DB_PASSWORD = "password"; // 替換為你的密碼
public static void demonstrateLengthFunctions() {
Connection conn = null;
try {
conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
// 創(chuàng)建示例表
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);
}
// 插入測試數(shù)據(jù)
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("? 測試數(shù)據(jù)已插入。");
}
// 查詢并展示 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=== 字符串長度函數(shù)演示 ===");
System.out.printf("%-10s %-15s %-15s %-15s%n", "姓名", "描述", "字節(jié)長度", "字符長度");
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("執(zhí)行字符串長度函數(shù)演示時發(fā)生錯誤: " + 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();
}
}代碼解釋
- 創(chuàng)建表：首先創(chuàng)建一個
sample_users表用于存儲示例數(shù)據(jù)。 - 插入數(shù)據(jù)：使用
PreparedStatement批量插入包含英文和中文的測試數(shù)據(jù)。 - 查詢與展示：執(zhí)行 SQL 查詢，同時使用
LENGTH()和CHAR_LENGTH()計算description字段的長度，并在 Java 控制臺打印結(jié)果。 - 輸出對比：對于中文字符，
LENGTH()返回的是字節(jié)數(shù)（每個中文字符通常占 3 個字節(jié)），而CHAR_LENGTH()返回的是字符數(shù)。
2. 字符串截取函數(shù)SUBSTRING()/SUBSTR()和LEFT()/RIGHT()
這些函數(shù)用于從字符串中提取一部分。
SUBSTRING(str, pos, len)：從str的位置pos開始，截取長度為len的子字符串。pos從 1 開始計數(shù)。LEFT(str, len)：從字符串左邊開始截取len個字符。RIGHT(str, len)：從字符串右邊開始截取len個字符。
示例：
-- 假設(shè)有一個產(chǎn)品名稱字段 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);
// 創(chuàng)建示例表
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);
}
// 插入測試數(shù)據(jù)
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("? 產(chǎn)品數(shù)據(jù)已插入。");
}
// 演示截取函數(shù)
String querySQL = """
SELECT
product_name,
product_code,
LEFT(product_name, 5) AS brand_name, -- 截取前5個字符作為品牌名
RIGHT(product_code, 3) AS last_three_digits, -- 截取產(chǎn)品代碼后3位
SUBSTRING(product_name, 1, 5) AS substring_brand, -- 同樣是前5個字符
SUBSTRING(product_code, 4, 3) AS middle_part_code -- 從位置4開始截取3個字符
FROM products
""";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(querySQL)) {
System.out.println("\n=== 字符串截取函數(shù)演示 ===");
System.out.printf("%-25s %-15s %-15s %-15s %-15s %-15s%n",
"產(chǎn)品名稱", "產(chǎ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("執(zhí)行字符串截取函數(shù)演示時發(fā)生錯誤: " + 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();
}
}代碼解釋
- 創(chuàng)建表：創(chuàng)建
products表存儲產(chǎn)品信息。 - 插入數(shù)據(jù)：插入三條包含產(chǎn)品名稱和代碼的測試數(shù)據(jù)。
- 查詢與演示：
- 使用
LEFT(product_name, 5)提取產(chǎn)品名稱的前 5 個字符作為品牌名。 - 使用
RIGHT(product_code, 3)提取產(chǎn)品代碼的后 3 個字符。 - 使用
SUBSTRING(product_name, 1, 5)和LEFT類似，但更靈活。 - 使用
SUBSTRING(product_code, 4, 3)從產(chǎn)品代碼的第 4 個字符開始截取 3 個字符。
- 使用
- 輸出結(jié)果：清晰地展示了不同截取函數(shù)的效果。
3. 字符串替換函數(shù)REPLACE()
REPLACE(str, from_str, to_str) 用于將字符串 str 中所有出現(xiàn)的 from_str 替換為 to_str。
示例：
-- SELECT REPLACE('Hello World', 'World', 'MySQL') AS result; -- 輸出: Hello MySQL
-- SELECT REPLACE('abcabcabc', 'bc', 'XY') AS result; -- 輸出: aXYaXYaXYJava 代碼示例：
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);
// 創(chuàng)建示例表
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);
}
// 插入測試數(shù)據(jù)
String insertSQL = "INSERT INTO user_profiles (full_name, email, bio) VALUES (?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
pstmt.setString(1, "張小明");
pstmt.setString(2, "zhang.xiaoming@example.com");
pstmt.setString(3, "我是張小明，熱愛技術(shù)。聯(lián)系方式：zhang.xiaoming@example.com");
pstmt.addBatch();
pstmt.setString(1, "李麗");
pstmt.setString(2, "li.li@company.com");
pstmt.setString(3, "李麗，軟件工程師。郵箱地址：li.li@company.com");
pstmt.addBatch();
pstmt.executeBatch();
System.out.println("? 用戶資料已插入。");
}
// 演示替換函數(shù)
String querySQL = """
SELECT
full_name,
email,
bio,
REPLACE(bio, '聯(lián)系方式', '聯(lián)系信息') AS modified_bio, -- 替換“聯(lián)系方式”為“聯(lián)系信息”
REPLACE(email, '@', '[AT]') AS masked_email -- 用 [AT] 替換 @ 符號（僅演示）
FROM user_profiles
""";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(querySQL)) {
System.out.println("\n=== 字符串替換函數(shù)演示 ===");
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("執(zhí)行字符串替換函數(shù)演示時發(fā)生錯誤: " + 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();
}
}代碼解釋
- 創(chuàng)建表：創(chuàng)建
user_profiles表存儲用戶信息。 - 插入數(shù)據(jù)：插入包含用戶姓名、郵箱和簡介的測試數(shù)據(jù)，其中簡介中包含了郵箱地址。
- 查詢與演示：
- 使用
REPLACE(bio, '聯(lián)系方式', '聯(lián)系信息')將簡介中的“聯(lián)系方式”替換為“聯(lián)系信息”。 - 使用
REPLACE(email, '@', '[AT]')將郵箱地址中的@符號替換為[AT]，用于演示（實際應(yīng)用中可能用于脫敏）。
- 使用
- 輸出結(jié)果：展示原始數(shù)據(jù)和替換后的效果。
4. 大小寫轉(zhuǎn)換函數(shù)UPPER()/UCASE()和LOWER()/LCASE()
這些函數(shù)用于將字符串轉(zhuǎn)換為大寫或小寫。
UPPER(str)/UCASE(str)：將字符串轉(zhuǎn)換為大寫。LOWER(str)/LCASE(str)：將字符串轉(zhuǎn)換為小寫。
示例：
-- SELECT UPPER('hello world') AS upper_result; -- 輸出: HELLO WORLD
-- SELECT LOWER('HELLO WORLD') AS lower_result; -- 輸出: hello world
-- SELECT UCASE('mysql') AS ucase_result; -- 輸出: MYSQL
-- SELECT LCASE('MYSQL') AS lcase_result; -- 輸出: mysqlJava 代碼示例：
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);
// 創(chuàng)建示例表
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);
}
// 插入測試數(shù)據(jù)
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("? 員工數(shù)據(jù)已插入。");
}
// 演示大小寫轉(zhuǎn)換
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 -- 簡單標(biāo)題格式
FROM employees
""";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(querySQL)) {
System.out.println("\n=== 大小寫轉(zhuǎn)換函數(shù)演示 ===");
System.out.printf("%-10s %-10s %-10s %-20s %-20s %-30s%n",
"名", "姓", "部門", "大寫名", "小寫姓", "標(biāo)題格式名");
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("執(zhí)行大小寫轉(zhuǎn)換函數(shù)演示時發(fā)生錯誤: " + 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();
}
}代碼解釋
- 創(chuàng)建表：創(chuàng)建
employees表存儲員工信息。 - 插入數(shù)據(jù)：插入包含員工名、姓和部門的測試數(shù)據(jù)，其中名字和姓氏大小寫不統(tǒng)一。
- 查詢與演示：
- 使用
UPPER(first_name)將名字轉(zhuǎn)換為大寫。 - 使用
LOWER(last_name)將姓氏轉(zhuǎn)換為小寫。 - 使用
CONCAT(UPPER(SUBSTRING(first_name, 1, 1)), LOWER(SUBSTRING(first_name, 2)))實現(xiàn)簡單的標(biāo)題格式（首字母大寫，其余小寫）。
- 使用
- 輸出結(jié)果：展示了原始數(shù)據(jù)和各種大小寫轉(zhuǎn)換后的效果。
5. 去除空格函數(shù)TRIM()/LTRIM()/RTRIM()
這些函數(shù)用于去除字符串兩端或特定一側(cè)的空白字符。
TRIM(str)：去除字符串兩端的空白字符。LTRIM(str)：去除字符串左側(cè)的空白字符。RTRIM(str)：去除字符串右側(cè)的空白字符。TRIM([leading|trailing|both] [remstr FROM] str)：更詳細(xì)的語法，可以指定去除哪一側(cè)的空白或指定要移除的字符。
示例：
-- SELECT TRIM(' Hello ') AS result; -- 輸出: Hello
-- SELECT LTRIM(' Hello') AS result; -- 輸出: Hello
-- SELECT RTRIM('Hello ') AS result; -- 輸出: Hello
-- SELECT TRIM('x' FROM 'xxxHelloxxx') AS result; -- 輸出: Hello (去除兩邊的 'x')
-- SELECT TRIM(LEADING 'x' FROM 'xxxHello') AS result; -- 輸出: Hello (只去除開頭的 'x')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);
// 創(chuàng)建示例表
String createTableSQL = """
CREATE TABLE IF NOT EXISTS user_input_logs (
id INT AUTO_INCREMENT PRIMARY KEY,
raw_data VARCHAR(255), -- 原始輸入
processed_data VARCHAR(255) -- 處理后的數(shù)據(jù)
)
""";
try (Statement stmt = conn.createStatement()) {
stmt.execute(createTableSQL);
}
// 插入測試數(shù)據(jù)（包含前后空格）
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("? 用戶輸入日志已插入。");
}
// 演示去空格函數(shù)
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,
TRIM('\r\n\t ' FROM raw_data) AS clean_data -- 移除多種空白字符
FROM user_input_logs
""";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(querySQL)) {
System.out.println("\n=== 去除空格函數(shù)演示 ===");
System.out.printf("%-25s %-25s %-25s %-25s %-25s%n",
"原始數(shù)據(jù)", "兩端去空格", "左去空格", "右去空格", "清理后數(shù)據(jù)");
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");
String cleanData = rs.getString("clean_data");
System.out.printf("%-25s %-25s %-25s %-25s %-25s%n",
rawData, trimmedData, leftTrimmedData, rightTrimmedData, cleanData);
}
}
} catch (SQLException e) {
System.err.println("執(zhí)行去空格函數(shù)演示時發(fā)生錯誤: " + 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();
}
}代碼解釋
- 創(chuàng)建表：創(chuàng)建
user_input_logs表用于記錄用戶輸入。 - 插入數(shù)據(jù)：插入包含前后空格、制表符、換行符的原始數(shù)據(jù)。
- 查詢與演示：
- 使用
TRIM(raw_data)去除兩端的空白字符。 - 使用
LTRIM(raw_data)去除左側(cè)空白。 - 使用
RTRIM(raw_data)去除右側(cè)空白。 - 使用
TRIM('\r\n\t ' FROM raw_data)去除指定的多種空白字符。
- 使用
- 輸出結(jié)果：展示了原始數(shù)據(jù)和各種去空格處理后的效果。
6. 字符串連接函數(shù)CONCAT()
CONCAT(str1, str2, ...) 用于將多個字符串連接成一個字符串。如果任意一個參數(shù)為 NULL，則結(jié)果為 NULL。
示例：
-- SELECT CONCAT('Hello', ' ', 'World') AS result; -- 輸出: Hello World
-- SELECT CONCAT('User:', 'John') AS result; -- 輸出: User:John
-- SELECT CONCAT('Name: ', first_name, ' ', last_name) AS full_name FROM users; -- 連接姓名字段
-- SELECT CONCAT_WS('-', '2023', '10', '15') AS date_string; -- 使用分隔符連接，CONCAT_WSJava 代碼示例：
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);
// 創(chuàng)建示例表
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);
}
// 插入測試數(shù)據(jù)
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("? 地址數(shù)據(jù)已插入。");
}
// 演示字符串連接
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=== 字符串連接函數(shù)演示 ===");
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("執(zhí)行字符串連接函數(shù)演示時發(fā)生錯誤: " + 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();
}
}代碼解釋
- 創(chuàng)建表：創(chuàng)建
addresses表存儲地址信息。 - 插入數(shù)據(jù)：插入包含街道、城市、州、郵編的測試數(shù)據(jù)。
- 查詢與演示：
- 使用
CONCAT(street, ', ', city, ', ', state, ' ', zip_code)將地址信息拼接成一個完整的地址字符串。 - 使用
CONCAT_WS(', ', street, city, state, zip_code)使用逗號作為分隔符拼接地址，CONCAT_WS是CONCAT的變體，專門用于帶分隔符的連接。
- 使用
- 輸出結(jié)果：展示了原始數(shù)據(jù)和拼接后的地址效果。
二、日期函數(shù)
日期函數(shù)是處理時間相關(guān)數(shù)據(jù)的強大工具。在應(yīng)用開發(fā)中，我們經(jīng)常需要計算日期差、提取日期組件、格式化日期顯示、處理時間戳等。MySQL 提供了豐富的日期函數(shù)來應(yīng)對這些需求。
1. 當(dāng)前日期和時間函數(shù)NOW(),CURDATE(),CURTIME()
這些函數(shù)用于獲取當(dāng)前的日期、時間和時間戳。
NOW()：返回當(dāng)前日期和時間（YYYY-MM-DD HH:MM:SS格式）。CURDATE()：返回當(dāng)前日期（YYYY-MM-DD格式）。CURTIME()：返回當(dāng)前時間（HH:MM:SS格式）。
示例：
-- SELECT NOW() AS current_datetime; -- 輸出: 2023-10-15 14:30:45 -- SELECT CURDATE() AS current_date; -- 輸出: 2023-10-15 -- SELECT CURTIME() AS current_time; -- 輸出: 14:30:45
Java 代碼示例：
import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
public class DateTimeFunctionExample {
private static final String DB_URL = "jdbc:mysql://localhost:3306/test_db?useSSL=false&serverTimezone=UTC";
private static final String DB_USER = "root"; // 替換為你的用戶名
private static final String DB_PASSWORD = "password"; // 替換為你的密碼
public static void demonstrateDateTimeFunctions() {
Connection conn = null;
try {
conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
// 創(chuàng)建示例表
String createTableSQL = """
CREATE TABLE IF NOT EXISTS events (
id INT AUTO_INCREMENT PRIMARY KEY,
event_name VARCHAR(255),
event_date DATE,
created_at DATETIME,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
""";
try (Statement stmt = conn.createStatement()) {
stmt.execute(createTableSQL);
}
// 插入測試數(shù)據(jù)
String insertSQL = "INSERT INTO events (event_name, event_date, created_at) VALUES (?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
pstmt.setString(1, "產(chǎn)品發(fā)布會");
pstmt.setDate(2, Date.valueOf("2023-11-20")); // 使用 java.sql.Date
pstmt.setTimestamp(3, Timestamp.valueOf("2023-10-15 10:00:00")); // 使用 java.sql.Timestamp
pstmt.addBatch();
pstmt.setString(1, "團(tuán)隊建設(shè)活動");
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("? 事件數(shù)據(jù)已插入。");
}
// 演示日期時間函數(shù)
String querySQL = """
SELECT
event_name,
event_date,
created_at,
NOW() AS db_now, -- 數(shù)據(jù)庫當(dāng)前時間
CURDATE() AS db_current_date, -- 數(shù)據(jù)庫當(dāng)前日期
CURTIME() AS db_current_time, -- 數(shù)據(jù)庫當(dāng)前時間
DATE(created_at) AS created_date_only, -- 從DATETIME中提取日期
TIME(created_at) AS created_time_only, -- 從DATETIME中提取時間
DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') AS formatted_created_at -- 格式化時間
FROM events
""";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(querySQL)) {
System.out.println("\n=== 日期時間函數(shù)演示 ===");
System.out.printf("%-20s %-12s %-20s %-20s %-12s %-12s %-12s %-12s %-20s%n",
"事件名稱", "事件日期", "創(chuàng)建時間", "數(shù)據(jù)庫當(dāng)前時間", "當(dāng)前日期", "當(dāng)前時間", "日期", "時間", "格式化時間");
while (rs.next()) {
String eventName = rs.getString("event_name");
Date eventDate = rs.getDate("event_date");
Timestamp createdAt = rs.getTimestamp("created_at");
Timestamp dbNow = rs.getTimestamp("db_now");
Date dbCurrentDate = rs.getDate("db_current_date");
Time dbCurrentTime = rs.getTime("db_current_time");
Date createdDateOnly = rs.getDate("created_date_only");
Time createdTimeOnly = rs.getTime("created_time_only");
String formattedCreatedAt = rs.getString("formatted_created_at");
System.out.printf("%-20s %-12s %-20s %-20s %-12s %-12s %-12s %-12s %-20s%n",
eventName, eventDate, createdAt, dbNow, dbCurrentDate, dbCurrentTime,
createdDateOnly, createdTimeOnly, formattedCreatedAt);
}
}
} catch (SQLException e) {
System.err.println("執(zhí)行日期時間函數(shù)演示時發(fā)生錯誤: " + 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();
}
}代碼解釋
- 創(chuàng)建表：創(chuàng)建
events表存儲事件信息，包含日期和時間字段。 - 插入數(shù)據(jù)：插入兩條事件記錄，包含事件名稱、日期和創(chuàng)建時間。
- 查詢與演示：
- 使用
NOW()獲取數(shù)據(jù)庫服務(wù)器的當(dāng)前時間。 - 使用
CURDATE()和CURTIME()獲取當(dāng)前日期和時間。 - 使用
DATE(created_at)從DATETIME字段中提取日期部分。 - 使用
TIME(created_at)從DATETIME字段中提取時間部分。 - 使用
DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s')格式化時間顯示。
- 使用
- 輸出結(jié)果：展示了原始數(shù)據(jù)和各種日期時間函數(shù)的處理結(jié)果。
2. 日期和時間組件提取函數(shù)YEAR(),MONTH(),DAY(),HOUR(),MINUTE(),SECOND()
這些函數(shù)用于從日期時間值中提取特定的組件。
示例：
-- SELECT YEAR('2023-10-15') AS year_value; -- 輸出: 2023
-- SELECT MONTH('2023-10-15') AS month_value; -- 輸出: 10
-- SELECT DAY('2023-10-15') AS day_value; -- 輸出: 15
-- SELECT HOUR('2023-10-15 14:30:45') AS hour_value; -- 輸出: 14
-- SELECT MINUTE('2023-10-15 14:30:45') AS minute_value; -- 輸出: 30
-- SELECT SECOND('2023-10-15 14:30:45') AS second_value; -- 輸出: 45Java 代碼示例：
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);
// 創(chuàng)建示例表
String createTableSQL = """
CREATE TABLE IF NOT EXISTS orders (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_name VARCHAR(100),
order_date DATETIME,
delivery_status ENUM('Pending', 'Shipped', 'Delivered')
)
""";
try (Statement stmt = conn.createStatement()) {
stmt.execute(createTableSQL);
}
// 插入測試數(shù)據(jù)
String insertSQL = "INSERT INTO orders (customer_name, order_date, delivery_status) VALUES (?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
pstmt.setString(1, "張三");
pstmt.setTimestamp(2, Timestamp.valueOf("2023-10-10 14:20:00"));
pstmt.setString(3, "Shipped");
pstmt.addBatch();
pstmt.setString(1, "李四");
pstmt.setTimestamp(2, Timestamp.valueOf("2023-10-12 09:15:30"));
pstmt.setString(3, "Pending");
pstmt.addBatch();
pstmt.setString(1, "王五");
pstmt.setTimestamp(2, Timestamp.valueOf("2023-10-14 16:45:10"));
pstmt.setString(3, "Delivered");
pstmt.addBatch();
pstmt.executeBatch();
System.out.println("? 訂單數(shù)據(jù)已插入。");
}
// 演示提取日期時間組件
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,
HOUR(order_date) AS order_hour,
MINUTE(order_date) AS order_minute,
SECOND(order_date) AS order_second,
CONCAT(YEAR(order_date), '-', LPAD(MONTH(order_date), 2, '0')) AS year_month -- 組合成 YYYY-MM 格式
FROM orders
""";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(querySQL)) {
System.out.println("\n=== 日期時間組件提取演示 ===");
System.out.printf("%-10s %-20s %-10s %-10s %-10s %-10s %-10s %-10s %-15s%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");
int orderHour = rs.getInt("order_hour");
int orderMinute = rs.getInt("order_minute");
int orderSecond = rs.getInt("order_second");
String yearMonth = rs.getString("year_month");
System.out.printf("%-10s %-20s %-10d %-10d %-10d %-10d %-10d %-10d %-15s%n",
customerName, orderDate, orderYear, orderMonth, orderDay,
orderHour, orderMinute, orderSecond, yearMonth);
}
}
} catch (SQLException e) {
System.err.println("執(zhí)行日期時間組件提取演示時發(fā)生錯誤: " + 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();
}
}代碼解釋
- 創(chuàng)建表：創(chuàng)建
orders表存儲訂單信息。 - 插入數(shù)據(jù)：插入三條包含客戶名和下單時間的訂單記錄。
- 查詢與演示：
- 使用
YEAR(order_date),MONTH(order_date),DAY(order_date)等函數(shù)提取年、月、日。 - 使用
HOUR(order_date),MINUTE(order_date),SECOND(order_date)提取時、分、秒。 - 使用
CONCAT(YEAR(order_date), '-', LPAD(MONTH(order_date), 2, '0'))將年份和月份組合成YYYY-MM格式（使用LPAD確保月份是兩位數(shù)）。
- 使用
- 輸出結(jié)果：展示了原始時間數(shù)據(jù)和各個組件的提取結(jié)果。
3. 日期計算函數(shù)DATE_ADD()/ADDDATE(),DATE_SUB()/SUBDATE()
這些函數(shù)用于對日期進(jìn)行加減運算。
DATE_ADD(date, INTERVAL value unit)或ADDDATE(date, INTERVAL value unit)：給日期加上一個時間間隔。DATE_SUB(date, INTERVAL value unit)或SUBDATE(date, INTERVAL value unit)：從日期中減去一個時間間隔。INTERVAL后面跟著值和單位（如DAY,MONTH,YEAR,HOUR,MINUTE,SECOND）。
示例：
-- SELECT DATE_ADD('2023-10-15', INTERVAL 1 DAY) AS next_day; -- 輸出: 2023-10-16
-- SELECT DATE_SUB('2023-10-15', INTERVAL 1 MONTH) AS last_month; -- 輸出: 2023-09-15
-- SELECT DATE_ADD('2023-10-15 14:30:45', INTERVAL 1 HOUR) AS plus_one_hour; -- 輸出: 2023-10-15 15:30:45
-- SELECT ADDDATE('2023-10-15', INTERVAL 7 DAY) AS next_week; -- 輸出: 2023-10-22
-- SELECT SUBDATE('2023-10-15', INTERVAL 1 YEAR) AS last_year; -- 輸出: 2022-10-15Java 代碼示例：
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);
// 創(chuàng)建示例表
String createTableSQL = """
CREATE TABLE IF NOT EXISTS tasks (
id INT AUTO_INCREMENT PRIMARY KEY,
task_name VARCHAR(255),
start_date DATE,
duration_days INT DEFAULT 1, -- 任務(wù)持續(xù)天數(shù)
due_date DATE,
reminder_date DATE
)
""";
try (Statement stmt = conn.createStatement()) {
stmt.execute(createTableSQL);
}
// 插入測試數(shù)據(jù)
String insertSQL = "INSERT INTO tasks (task_name, start_date, duration_days, due_date, reminder_date) VALUES (?, ?, ?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
pstmt.setString(1, "需求分析");
pstmt.setDate(2, Date.valueOf("2023-10-10")); // 開始日期
pstmt.setInt(3, 3); // 持續(xù)3天
pstmt.setDate(3, null); // 后續(xù)計算
pstmt.setDate(4, null); // 后續(xù)計算
pstmt.addBatch();
pstmt.setString(1, "系統(tǒng)設(shè)計");
pstmt.setDate(2, Date.valueOf("2023-10-15"));
pstmt.setInt(3, 5);
pstmt.setDate(3, null);
pstmt.setDate(4, null);
pstmt.addBatch();
pstmt.executeBatch();
System.out.println("? 任務(wù)數(shù)據(jù)已插入。");
}
// 演示日期計算
String querySQL = """
SELECT
task_name,
start_date,
duration_days,
DATE_ADD(start_date, INTERVAL duration_days DAY) AS calculated_due_date, -- 計算截止日期
DATE_SUB(DATE_ADD(start_date, INTERVAL duration_days DAY), INTERVAL 1 DAY) AS actual_due_date, -- 實際截止日期（比計算的少一天）
DATE_ADD(start_date, INTERVAL 1 WEEK) AS one_week_later, -- 一周后
DATE_SUB(start_date, INTERVAL 1 MONTH) AS one_month_ago, -- 一個月前
DATE_ADD(start_date, INTERVAL 1 YEAR) AS one_year_later -- 一年后
FROM tasks
""";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(querySQL)) {
System.out.println("\n=== 日期計算函數(shù)演示 ===");
System.out.printf("%-15s %-12s %-15s %-20s %-20s %-20s %-20s %-20s%n",
"任務(wù)名稱", "開始日期", "持續(xù)天數(shù)", "計算截止日期", "實際截止日期", "一周后", "一個月前", "一年后");
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");
Date actualDueDate = rs.getDate("actual_due_date");
Date oneWeekLater = rs.getDate("one_week_later");
Date oneMonthAgo = rs.getDate("one_month_ago");
Date oneYearLater = rs.getDate("one_year_later");
System.out.printf("%-15s %-12s %-15d %-20s %-20s %-20s %-20s %-20s%n",
taskName, startDate, durationDays, calculatedDueDate, actualDueDate,
oneWeekLater, oneMonthAgo, oneYearLater);
}
}
} catch (SQLException e) {
System.err.println("執(zhí)行日期計算函數(shù)演示時發(fā)生錯誤: " + 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();
}
}代碼解釋
- 創(chuàng)建表：創(chuàng)建
tasks表存儲任務(wù)信息。 - 插入數(shù)據(jù)：插入兩條任務(wù)記錄，包含任務(wù)名稱、開始日期和持續(xù)天數(shù)。
- 查詢與演示：
- 使用
DATE_ADD(start_date, INTERVAL duration_days DAY)計算任務(wù)的預(yù)計截止日期。 - 使用
DATE_SUB(calculated_due_date, INTERVAL 1 DAY)計算實際截止日期（比預(yù)計少一天）。 - 使用
DATE_ADD(start_date, INTERVAL 1 WEEK)計算一周后的時間。 - 使用
DATE_SUB(start_date, INTERVAL 1 MONTH)計算一個月前的時間。 - 使用
DATE_ADD(start_date, INTERVAL 1 YEAR)計算一年后的時間。
- 使用
- 輸出結(jié)果：展示了原始數(shù)據(jù)和各種日期計算的結(jié)果。
4. 日期差函數(shù)DATEDIFF()和TIMESTAMPDIFF()
這些函數(shù)用于計算兩個日期之間的差值。
DATEDIFF(date1, date2)：計算date2與date1之間的天數(shù)差。如果date2大于date1，返回正值；反之返回負(fù)值。TIMESTAMPDIFF(unit, datetime1, datetime2)：計算datetime2與datetime1之間的差值，單位由unit指定（如SECOND,MINUTE,HOUR,DAY,MONTH,YEAR）。
示例：
-- SELECT DATEDIFF('2023-10-20', '2023-10-15') AS diff_days; -- 輸出: 5
-- SELECT DATEDIFF('2023-10-15', '2023-10-20') AS diff_days; -- 輸出: -5
-- SELECT TIMESTAMPDIFF(HOUR, '2023-10-15 10:00:00', '2023-10-15 14:30:00') AS diff_hours; -- 輸出: 4
-- SELECT TIMESTAMPDIFF(MINUTE, '2023-10-15 10:00:00', '2023-10-15 10:45:30') AS diff_minutes; -- 輸出: 45Java 代碼示例：
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);
// 創(chuàng)建示例表
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,
status ENUM('Planned', 'Completed', 'Delayed')
)
""";
try (Statement stmt = conn.createStatement()) {
stmt.execute(createTableSQL);
}
// 插入測試數(shù)據(jù)
String insertSQL = "INSERT INTO project_milestones (milestone_name, planned_date, actual_date, status) 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.setString(4, "Completed");
pstmt.addBatch();
pstmt.setString(1, "原型設(shè)計");
pstmt.setDate(2, Date.valueOf("2023-10-15"));
pstmt.setDate(3, Date.valueOf("2023-10-20")); // 實際完成日期
pstmt.setString(4, "Delayed");
pstmt.addBatch();
pstmt.setString(1, "代碼實現(xiàn)");
pstmt.setDate(2, Date.valueOf("2023-10-25"));
pstmt.setDate(3, null); // 尚未完成
pstmt.setString(4, "Planned");
pstmt.addBatch();
pstmt.executeBatch();
System.out.println("? 項目里程碑?dāng)?shù)據(jù)已插入。");
}
// 演示日期差計算
String querySQL = """
SELECT
milestone_name,
planned_date,
actual_date,
DATEDIFF(actual_date, planned_date) AS days_difference, -- 計算實際日期與計劃日期的天數(shù)差
CASE
WHEN DATEDIFF(actual_date, planned_date) > 0 THEN '延遲'
WHEN DATEDIFF(actual_date, planned_date) = 0 THEN '按時'
ELSE '提前'
END AS status_description,
TIMESTAMPDIFF(DAY, planned_date, actual_date) AS diff_in_days, -- 使用 TIMESTAMPDIFF 計算天數(shù)差
TIMESTAMPDIFF(WEEK, planned_date, actual_date) AS diff_in_weeks, -- 計算周數(shù)差
TIMESTAMPDIFF(HOUR, planned_date, actual_date) AS diff_in_hours -- 計算小時差
FROM project_milestones
WHERE actual_date IS NOT NULL -- 只計算已完成的里程碑
""";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(querySQL)) {
System.out.println("\n=== 日期差函數(shù)演示 ===");
System.out.printf("%-15s %-12s %-12s %-15s %-20s %-15s %-15s %-15s%n",
"里程碑名稱", "計劃日期", "實際日期", "天數(shù)差", "狀態(tài)描述", "天數(shù)差(新)", "周數(shù)差", "小時差");
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");
String statusDescription = rs.getString("status_description");
long diffInDays = rs.getLong("diff_in_days");
long diffInWeeks = rs.getLong("diff_in_weeks");
long diffInHours = rs.getLong("diff_in_hours");
System.out.printf("%-15s %-12s %-12s %-15d %-20s %-15d %-15d %-15d%n",
milestoneName, plannedDate, actualDate, daysDifference, statusDescription,
diffInDays, diffInWeeks, diffInHours);
}
}
} catch (SQLException e) {
System.err.println("執(zhí)行日期差函數(shù)演示時發(fā)生錯誤: " + 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();
}
}代碼解釋
- 創(chuàng)建表：創(chuàng)建
project_milestones表存儲項目里程碑信息。 - 插入數(shù)據(jù)：插入三條里程碑記錄，包含名稱、計劃日期、實際日期和狀態(tài)。
- 查詢與演示：
- 使用
DATEDIFF(actual_date, planned_date)計算實際完成日期與計劃日期的天數(shù)差。 - 使用
CASE語句根據(jù)天數(shù)差判斷項目是“提前”、“按時”還是“延遲”。 - 使用
TIMESTAMPDIFF(DAY, planned_date, actual_date)重復(fù)計算天數(shù)差。 - 使用
TIMESTAMPDIFF(WEEK, planned_date, actual_date)計算周數(shù)差。 - 使用
TIMESTAMPDIFF(HOUR, planned_date, actual_date)計算小時差。
- 使用
- 輸出結(jié)果：展示了每個已完成里程碑的詳細(xì)日期差信息。
5. 日期格式化函數(shù)DATE_FORMAT()
DATE_FORMAT(date, format) 用于將日期或時間值按照指定的格式進(jìn)行格式化輸出。
示例：
-- SELECT DATE_FORMAT('2023-10-15', '%Y-%m-%d') AS formatted_date; -- 輸出: 2023-10-15
-- SELECT DATE_FORMAT('2023-10-15 14:30:45', '%Y/%m/%d %H:%i:%s') AS formatted_datetime; -- 輸出: 2023/10/15 14:30:45
-- SELECT DATE_FORMAT('2023-10-15', '%M %D, %Y') AS formatted_long_date; -- 輸出: October 15th, 2023
-- SELECT DATE_FORMAT('2023-10-15 14:30:45', '%W, %M %e, %Y at %h:%i %p') AS formatted_long_datetime; -- 輸出: Sunday, October 15, 2023 at 02:30 PMJava 代碼示例：
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);
// 創(chuàng)建示例表
String createTableSQL = """
CREATE TABLE IF NOT EXISTS blog_posts (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255),
publish_date DATETIME,
category VARCHAR(100)
)
""";
try (Statement stmt = conn.createStatement()) {
stmt.execute(createTableSQL);
}
// 插入測試數(shù)據(jù)
String insertSQL = "INSERT INTO blog_posts (title, publish_date, category) VALUES (?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
pstmt.setString(1, "MySQL 教程入門");
pstmt.setTimestamp(2, Timestamp.valueOf("2023-10-10 09:00:00"));
pstmt.setString(3, "Database");
pstmt.addBatch();
pstmt.setString(1, "Java 編程技巧");
pstmt.setTimestamp(2, Timestamp.valueOf("2023-10-12 16:30:00"));
pstmt.setString(3, "Programming");
pstmt.addBatch();
pstmt.setString(1, "前端開發(fā)指南");
pstmt.setTimestamp(2, Timestamp.valueOf("2023-10-15 11:15:00"));
pstmt.setString(3, "Web");
pstmt.addBatch();
pstmt.executeBatch();
System.out.println("? 博客文章數(shù)據(jù)已插入。");
}
// 演示日期格式化
String querySQL = """
SELECT
title,
publish_date,
DATE_FORMAT(publish_date, '%Y-%m-%d') AS simple_date, -- 簡單日期格式
DATE_FORMAT(publish_date, '%Y/%m/%d %H:%i:%s') AS full_datetime, -- 完整日期時間格式
DATE_FORMAT(publish_date, '%M %D, %Y') AS long_date, -- 長日期格式
DATE_FORMAT(publish_date, '%W, %M %e, %Y at %h:%i %p') AS formatted_post_date, -- 博客文章格式
DATE_FORMAT(publish_date, '%Y年%m月%d日') AS chinese_date_format -- 中文日期格式
FROM blog_posts
ORDER BY publish_date DESC
""";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(querySQL)) {
System.out.println("\n=== 日期格式化函數(shù)演示 ===");
System.out.printf("%-25s %-20s %-15s %-20s %-20s %-40s %-20s%n",
"文章標(biāo)題", "發(fā)布日期", "簡單日期", "完整時間", "長日期", "格式化發(fā)布日期", "中文日期");
while (rs.next()) {
String title = rs.getString("title");
Timestamp publishDate = rs.getTimestamp("publish_date");
String simpleDate = rs.getString("simple_date");
String fullDatetime = rs.getString("full_datetime");
String longDate = rs.getString("long_date");
String formattedPostDate = rs.getString("formatted_post_date");
String chineseDateFormat = rs.getString("chinese_date_format");
System.out.printf("%-25s %-20s %-15s %-20s %-20s %-40s %-20s%n",
title, publishDate, simpleDate, fullDatetime, longDate,
formattedPostDate, chineseDateFormat);
}
}
} catch (SQLException e) {
System.err.println("執(zhí)行日期格式化函數(shù)演示時發(fā)生錯誤: " + 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();
}
}代碼解釋
- 創(chuàng)建表：創(chuàng)建
blog_posts表存儲博客文章信息。 - 插入數(shù)據(jù)：插入三條包含文章標(biāo)題、發(fā)布日期和分類的博客文章記錄。
- 查詢與演示：
- 使用
DATE_FORMAT(publish_date, '%Y-%m-%d')輸出簡單的日期格式。 - 使用
DATE_FORMAT(publish_date, '%Y/%m/%d %H:%i:%s')輸出完整的日期時間格式。 - 使用
DATE_FORMAT(publish_date, '%M %D, %Y')輸出長日期格式（如 “October 15th, 2023”）。 - 使用
DATE_FORMAT(publish_date, '%W, %M %e, %Y at %h:%i %p')輸出適合博客文章的格式（如 “Sunday, October 15, 2023 at 02:30 PM”）。 - 使用
DATE_FORMAT(publish_date, '%Y年%m月%d日')輸出中文日期格式。
- 使用
- 輸出結(jié)果：展示了每篇文章的原始發(fā)布日期和各種格式化后的日期顯示效果。
三、聚合函數(shù)
聚合函數(shù)是對一組值進(jìn)行計算并返回單一值的函數(shù)。它們常用于 GROUP BY 子句中進(jìn)行統(tǒng)計分析。常見的聚合函數(shù)包括 COUNT(), SUM(), AVG(), MAX(), MIN() 等。
1. 計數(shù)函數(shù)COUNT()
COUNT() 用于計算行數(shù)或非空值的數(shù)量。
COUNT(*)：計算所有行數(shù)，包括NULL值。COUNT(column)：計算指定列中非NULL值的數(shù)量。COUNT(DISTINCT column)：計算指定列中不同非NULL值的數(shù)量。
示例：
-- SELECT COUNT(*) FROM users; -- 計算用戶總數(shù) -- SELECT COUNT(email) FROM users; -- 計算有郵箱的用戶數(shù) -- SELECT COUNT(DISTINCT department) FROM employees; -- 計算不同部門的數(shù)量
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);
// 創(chuàng)建示例表
String createTableSQL = """
CREATE TABLE IF NOT EXISTS sales (
id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(255),
quantity INT,
price DECIMAL(10, 2),
sale_date DATE,
region VARCHAR(50)
)
""";
try (Statement stmt = conn.createStatement()) {
stmt.execute(createTableSQL);
}
// 插入測試數(shù)據(jù)
String insertSQL = "INSERT INTO sales (product_name, quantity, price, sale_date, region) VALUES (?, ?, ?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
pstmt.setString(1, "iPhone 14");
pstmt.setInt(2, 2);
pstmt.setBigDecimal(3, new java.math.BigDecimal("999.99"));
pstmt.setDate(4, Date.valueOf("2023-10-10"));
pstmt.setString(5, "North");
pstmt.addBatch();
pstmt.setString(1, "Samsung Galaxy S23");
pstmt.setInt(2, 1);
pstmt.setBigDecimal(3, new java.math.BigDecimal("899.99"));
pstmt.setDate(4, Date.valueOf("2023-10-11"));
pstmt.setString(5, "South");
pstmt.addBatch();
pstmt.setString(1, "MacBook Pro");
pstmt.setInt(2, 1);
pstmt.setBigDecimal(3, new java.math.BigDecimal("1999.99"));
pstmt.setDate(4, Date.valueOf("2023-10-12"));
pstmt.setString(5, "North");
pstmt.addBatch();
pstmt.setString(1, "iPad Air");
pstmt.setInt(2, 3);
pstmt.setBigDecimal(3, new java.math.BigDecimal("599.99"));
pstmt.setDate(4, Date.valueOf("2023-10-13"));
pstmt.setString(5, "East");
pstmt.addBatch();
pstmt.setString(1, "Apple Watch");
pstmt.setInt(2, 2);
pstmt.setBigDecimal(3, new java.math.BigDecimal("399.99"));
pstmt.setDate(4, Date.valueOf("2023-10-14"));
pstmt.setString(5, "West");
pstmt.addBatch();
pstmt.setString(1, "AirPods Pro"); // 沒有銷售日期
pstmt.setInt(2, 1);
pstmt.setBigDecimal(3, new java.math.BigDecimal("249.99"));
pstmt.setDate(4, null);
pstmt.setString(5, "North");
pstmt.addBatch();
pstmt.executeBatch();
System.out.println("? 銷售數(shù)據(jù)已插入。");
}
// 演示計數(shù)函數(shù)
String querySQL = """
SELECT
COUNT(*) AS total_sales,
COUNT(sale_date) AS sales_with_date,
COUNT(DISTINCT region) AS unique_regions,
COUNT(DISTINCT product_name) AS unique_products_sold
FROM sales
""";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(querySQL)) {
System.out.println("\n=== 計數(shù)函數(shù)演示 ===");
while (rs.next()) {
long totalSales = rs.getLong("total_sales");
long salesWithDate = rs.getLong("sales_with_date");
long uniqueRegions = rs.getLong("unique_regions");
long uniqueProductsSold = rs.getLong("unique_products_sold");
System.out.println("總銷售額: " + totalSales);
System.out.println("有銷售日期的銷售額: " + salesWithDate);
System.out.println("不同區(qū)域數(shù)量: " + uniqueRegions);
System.out.println("不同產(chǎn)品數(shù)量: " + uniqueProductsSold);
}
}
} catch (SQLException e) {
System.err.println("執(zhí)行計數(shù)函數(shù)演示時發(fā)生錯誤: " + 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();
}
}代碼解釋
- 創(chuàng)建表：創(chuàng)建
sales表存儲銷售記錄。 - 插入數(shù)據(jù)：插入六條銷售記錄，包含產(chǎn)品名、數(shù)量、價格、銷售日期和區(qū)域。其中一條記錄的銷售日期為空。
- 查詢與演示：
- 使用
COUNT(*)計算所有銷售記錄的數(shù)量。 - 使用
COUNT(sale_date)計算具有銷售日期的記錄數(shù)量（即sale_date不為NULL的記錄數(shù)）。 - 使用
COUNT(DISTINCT region)計算不同區(qū)域的數(shù)量。 - 使用
COUNT(DISTINCT product_name)計算不同產(chǎn)品的數(shù)量。
- 使用
- 輸出結(jié)果：展示了各種計數(shù)的結(jié)果。
2. 求和函數(shù)SUM()
SUM() 用于計算數(shù)值列的總和。
示例：
-- SELECT SUM(price) FROM sales; -- 計算所有商品的總價 -- SELECT SUM(quantity * price) FROM sales; -- 計算所有商品的總銷售額 -- SELECT SUM(price) FROM sales WHERE region = 'North'; -- 計算北部區(qū)域的總銷售額
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);
// 創(chuàng)建示例表
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),
supplier VARCHAR(100)
)
""";
try (Statement stmt = conn.createStatement()) {
stmt.execute(createTableSQL);
}
// 插入測試數(shù)據(jù)
String insertSQL = "INSERT INTO inventory (item_name, stock_quantity, unit_price, supplier) VALUES (?, ?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
pstmt.setString(1, "筆記本電腦");
pstmt.setInt(2, 50);
pstmt.setBigDecimal(3, new java.math.BigDecimal("5999.99"));
pstmt.setString(4, "供應(yīng)商A");
pstmt.addBatch();
pstmt.setString(1, "臺式機");
pstmt.setInt(2, 30);
pstmt.setBigDecimal(3, new java.math.BigDecimal("3999.99"));
pstmt.setString(4, "供應(yīng)商B");
pstmt.addBatch();
pstmt.setString(1, "顯示器");
pstmt.setInt(2, 100);
pstmt.setBigDecimal(3, new java.math.BigDecimal("1299.99"));
pstmt.setString(4, "供應(yīng)商A");
pstmt.addBatch();
pstmt.setString(1, "鍵盤");
pstmt.setInt(2, 200);
pstmt.setBigDecimal(3, new java.math.BigDecimal("199.99"));
pstmt.setString(4, "供應(yīng)商C");
pstmt.addBatch();
pstmt.setString(1, "鼠標(biāo)");
pstmt.setInt(2, 150);
pstmt.setBigDecimal(3, new java.math.BigDecimal("99.99"));
pstmt.setString(4, "供應(yīng)商B");
pstmt.addBatch();
pstmt.executeBatch();
System.out.println("? 庫存數(shù)據(jù)已插入。");
}
// 演示求和函數(shù)
String querySQL = """
SELECT
SUM(stock_quantity) AS total_stock,
SUM(stock_quantity * unit_price) AS total_inventory_value,
SUM(CASE WHEN supplier = '供應(yīng)商A' THEN stock_quantity * unit_price ELSE 0 END) AS supplier_a_value,
SUM(CASE WHEN supplier = '供應(yīng)商B' THEN stock_quantity * unit_price ELSE 0 END) AS supplier_b_value
FROM inventory
""";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(querySQL)) {
System.out.println("\n=== 求和函數(shù)演示 ===");
while (rs.next()) {
long totalStock = rs.getLong("total_stock");
BigDecimal totalInventoryValue = rs.getBigDecimal("total_inventory_value");
BigDecimal supplierAValue = rs.getBigDecimal("supplier_a_value");
BigDecimal supplierBValue = rs.getBigDecimal("supplier_b_value");
System.out.println("總庫存數(shù)量: " + totalStock);
System.out.println("總庫存價值: ￥" + totalInventoryValue.setScale(2, java.math.RoundingMode.HALF_UP));
System.out.println("供應(yīng)商A庫存價值: ￥" + supplierAValue.setScale(2, java.math.RoundingMode.HALF_UP));
System.out.println("供應(yīng)商B庫存價值: ￥" + supplierBValue.setScale(2, java.math.RoundingMode.HALF_UP));
}
}
} catch (SQLException e) {
System.err.println("執(zhí)行求和函數(shù)演示時發(fā)生錯誤: " + 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();
}
}代碼解釋
- 創(chuàng)建表：創(chuàng)建
inventory表存儲庫存信息。 - 插入數(shù)據(jù)：插入五條包含商品名、庫存數(shù)量、單價和供應(yīng)商的庫存記錄。
- 查詢與演示：
- 使用
SUM(stock_quantity)計算所有商品的總庫存數(shù)量。 - 使用
SUM(stock_quantity * unit_price)計算所有商品的總庫存價值。 - 使用
SUM(CASE WHEN supplier = '供應(yīng)商A' THEN stock_quantity * unit_price ELSE 0 END)計算供應(yīng)商 A 的庫存價值。 - 使用
SUM(CASE WHEN supplier = '供應(yīng)商B' THEN stock_quantity * unit_price ELSE 0 END)計算供應(yīng)商 B 的庫存價值。
- 使用
- 輸出結(jié)果：展示了庫存總量、總價值以及按供應(yīng)商劃分的價值。
3. 平均值函數(shù)AVG()
AVG() 用于計算數(shù)值列的平均值。
示例：
-- SELECT AVG(price) FROM sales; -- 計算商品的平均價格 -- SELECT AVG(quantity) FROM sales; -- 計算銷售數(shù)量的平均值 -- SELECT AVG(price) FROM sales WHERE region = 'North'; -- 計算北部區(qū)域商品的平均價格
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);
// 創(chuàng)建示例表
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);
}
// 插入測試數(shù)據(jù)
String insertSQL = "INSERT INTO student_scores (student_name, subject, score) VALUES (?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
pstmt.setString(1, "張三");
pstmt.setString(2, "數(shù)學(xué)");
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.setString(1, "張三");
pstmt.setString(2, "物理");
pstmt.setBigDecimal(3, new java.math.BigDecimal("78.5"));
pstmt.addBatch();
pstmt.setString(1, "李四");
pstmt.setString(2, "數(shù)學(xué)");
pstmt.setBigDecimal(3, new java.math.BigDecimal("90.0"));
pstmt.addBatch();
pstmt.setString(1, "李四");
pstmt.setString(2, "英語");
pstmt.setBigDecimal(3, new java.math.BigDecimal("88.5"));
pstmt.addBatch();
pstmt.setString(1, "李四");
pstmt.setString(2, "物理");
pstmt.setBigDecimal(3, new java.math.BigDecimal("95.0"));
pstmt.addBatch();
pstmt.setString(1, "王五");
pstmt.setString(2, "數(shù)學(xué)");
pstmt.setBigDecimal(3, new java.math.BigDecimal("76.0"));
pstmt.addBatch();
pstmt.setString(1, "王五");
pstmt.setString(2, "英語");
pstmt.setBigDecimal(3, new java.math.BigDecimal("82.5"));
pstmt.addBatch();
pstmt.setString(1, "王五");
pstmt.setString(2, "物理");
pstmt.setBigDecimal(3, new java.math.BigDecimal("80.0"));
pstmt.addBatch();
pstmt.executeBatch();
System.out.println("? 學(xué)生成績數(shù)據(jù)已插入。");
}
// 演示平均值函數(shù)
String querySQL = """
SELECT
subject,
AVG(score) AS avg_score,
COUNT(*) AS student_count,
ROUND(AVG(score), 2) AS rounded_avg_score -- 四舍五入到兩位小數(shù)
FROM student_scores
GROUP BY subject
ORDER BY avg_score DESC
""";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(querySQL)) {
System.out.println("\n=== 平均值函數(shù)演示 ===");
System.out.printf("%-10s %-15s %-15s %-15s%n", "科目", "平均分", "學(xué)生人數(shù)", "四舍五入平均分");
while (rs.next()) {
String subject = rs.getString("subject");
BigDecimal avgScore = rs.getBigDecimal("avg_score");
long studentCount = rs.getLong("student_count");
BigDecimal roundedAvgScore = rs.getBigDecimal("rounded_avg_score");
System.out.printf("%-10s %-15s %-15d %-15s%n",
subject, avgScore, studentCount, roundedAvgScore);
}
}
} catch (SQLException e) {
System.err.println("執(zhí)行平均值函數(shù)演示時發(fā)生錯誤: " + 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();
}
}代碼解釋
- 創(chuàng)建表：創(chuàng)建
student_scores表存儲學(xué)生成績信息。 - 插入數(shù)據(jù)：插入九條成績記錄，每個學(xué)生三門科目的成績。
- 查詢與演示：
- 使用
GROUP BY subject按科目分組。 - 使用
AVG(score)計算每個科目的平均分。 - 使用
COUNT(*)計算每個科目的學(xué)生人數(shù)。 - 使用
ROUND(AVG(score), 2)將平均分四舍五入到兩位小數(shù)。
- 使用
- 輸出結(jié)果：展示了各科目的平均分、學(xué)生人數(shù)和四舍五入后的平均分，并按平均分降序排列。
4. 最大值和最小值函數(shù)MAX()和MIN()
MAX() 和 MIN() 用于找出列中的最大值和最小值。
示例：
-- SELECT MAX(price) FROM sales; -- 找出最貴的商品價格 -- SELECT MIN(price) FROM sales; -- 找出最便宜的商品價格 -- SELECT MAX(sale_date) FROM sales; -- 找出最新的銷售日期 -- SELECT MIN(sale_date) 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);
// 創(chuàng)建示例表
String createTableSQL = """
CREATE TABLE IF NOT EXISTS employee_salaries (
id INT AUTO_INCREMENT PRIMARY KEY,
employee_name VARCHAR(100),
department VARCHAR(50),
salary DECIMAL(10, 2),
hire_date DATE
)
""";
try (Statement stmt = conn.createStatement()) {
stmt.execute(createTableSQL);
}
// 插入測試數(shù)據(jù)
String insertSQL = "INSERT INTO employee_salaries (employee_name, department, salary, hire_date) VALUES (?, ?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
pstmt.setString(1, "張經(jīng)理");
pstmt.setString(2, "IT");
pstmt.setBigDecimal(3, new java.math.BigDecimal("15000.00"));
pstmt.setDate(4, Date.valueOf("2020-05-10"));
pstmt.addBatch();
pstmt.setString(1, "李主管");
pstmt.setString(2, "HR");
pstmt.setBigDecimal(3, new java.math.BigDecimal("12000.00"));
pstmt.setDate(4, Date.valueOf("2019-08-20"));
pstmt.addBatch();
pstmt.setString(1, "王專員");
pstmt.setString(2, "IT");
pstmt.setBigDecimal(3, new java.math.BigDecimal("8000.00"));
pstmt.setDate(4, Date.valueOf("2021-01-15"));
pstmt.addBatch();
pstmt.setString(1, "趙專員");
pstmt.setString(2, "Finance");
pstmt.setBigDecimal(3, new java.math.BigDecimal("9500.00"));
pstmt.setDate(4, Date.valueOf("2020-11-30"));
pstmt.addBatch();
pstmt.setString(1, "陳專員");
pstmt.setString(2, "Marketing");
pstmt.setBigDecimal(3, new java.math.BigDecimal("7500.00"));
pstmt.setDate(4, Date.valueOf("2022-03-10"));
pstmt.addBatch();
pstmt.setString(1, "孫主管");
pstmt.setString(2, "Finance");
pstmt.setBigDecimal(3, new java.math.BigDecimal("13000.00"));
pstmt.setDate(4, Date.valueOf("2018-07-05"));
pstmt.addBatch();
pstmt.executeBatch();
System.out.println("? 員工薪資數(shù)據(jù)已插入。");
}
// 演示最大值和最小值函數(shù)
String querySQL = """
SELECT
MAX(salary) AS highest_salary,
MIN(salary) AS lowest_salary,
MAX(hire_date) AS latest_hire_date,
MIN(hire_date) AS earliest_hire_date,
AVG(salary) AS average_salary,
(MAX(salary) - MIN(salary)) AS salary_range -- 薪資差距
FROM employee_salaries
""";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(querySQL)) {
System.out.println("\n=== 最大值和最小值函數(shù)演示 ===");
while (rs.next()) {
BigDecimal highestSalary = rs.getBigDecimal("highest_salary");
BigDecimal lowestSalary = rs.getBigDecimal("lowest_salary");
Date latestHireDate = rs.getDate("latest_hire_date");
Date earliestHireDate = rs.getDate("earliest_hire_date");
BigDecimal averageSalary = rs.getBigDecimal("average_salary");
BigDecimal salaryRange = rs.getBigDecimal("salary_range");
System.out.println("最高薪資: ￥" + highestSalary.setScale(2, java.math.RoundingMode.HALF_UP));
System.out.println("最低薪資: ￥" + lowestSalary.setScale(2, java.math.RoundingMode.HALF_UP));
System.out.println("最新入職日期: " + latestHireDate);
System.out.println("最早入職日期: " + earliestHireDate);
System.out.println("平均薪資: ￥" + averageSalary.setScale(2, java.math.RoundingMode.HALF_UP));
System.out.println("薪資差距: ￥" + salaryRange.setScale(2, java.math.RoundingMode.HALF_UP));
}
}
} catch (SQLException e) {
System.err.println("執(zhí)行最大值最小值函數(shù)演示時發(fā)生錯誤: " + 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();
}
}代碼解釋
- 創(chuàng)建表：創(chuàng)建
employee_salaries表存儲員工薪資信息。 - 插入數(shù)據(jù)：插入六條員工記錄，包含姓名、部門、薪資和入職日期。
- 查詢與演示：
- 使用
MAX(salary)找出最高薪資。 - 使用
MIN(salary)找出最低薪資。 - 使用
MAX(hire_date)找出最新的入職日期。 - 使用
MIN(hire_date)找出最早的入職日期。 - 使用
AVG(salary)計算平均薪資。 - 使用
(MAX(salary) - MIN(salary))計算薪資差距。
- 使用
- 輸出結(jié)果：展示了員工薪資和入職日期的統(tǒng)計信息。
5. 聚合函數(shù)與分組GROUP BY結(jié)合使用
聚合函數(shù)通常與 GROUP BY 子句一起使用，以對數(shù)據(jù)進(jìn)行分組并計算每組的聚合值。
示例：
-- SELECT department, COUNT(*) FROM employees GROUP BY department; -- 按部門統(tǒng)計員工數(shù)量 -- SELECT department, AVG(salary) FROM employees GROUP BY department; -- 按部門計算平均薪資 -- SELECT product_category, SUM(sales_amount) FROM sales GROUP BY product_category; -- 按產(chǎn)品類別統(tǒng)計銷售額
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);
// 創(chuàng)建示例表
String createTableSQL = """
CREATE TABLE IF NOT EXISTS transactions (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
customer_name VARCHAR(100),
transaction_type ENUM('Purchase', 'Refund'),
amount DECIMAL(10, 2),
transaction_date DATE,
category VARCHAR(50)
)
""";
try (Statement stmt = conn.createStatement()) {
stmt.execute(createTableSQL);
}
// 插入測試數(shù)據(jù)
String insertSQL = "INSERT INTO transactions (customer_id, customer_name, transaction_type, amount, transaction_date, category) VALUES (?, ?, ?, ?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
// 客戶 A
pstmt.setInt(1, 1);
pstmt.setString(2, "張三");
pstmt.setString(3, "Purchase");
pstmt.setBigDecimal(4, new java.math.BigDecimal("150.00"));
pstmt.setDate(5, Date.valueOf("2023-10-10"));
pstmt.setString(6, "Electronics");
pstmt.addBatch();
pstmt.setInt(1, 1);
pstmt.setString(2, "張三");
pstmt.setString(3, "Purchase");
pstmt.setBigDecimal(4, new java.math.BigDecimal("80.00"));
pstmt.setDate(5, Date.valueOf("2023-10-12"));
pstmt.setString(6, "Clothing");
pstmt.addBatch();
pstmt.setInt(1, 1);
pstmt.setString(2, "張三");
pstmt.setString(3, "Refund");
pstmt.setBigDecimal(4, new java.math.BigDecimal("50.00"));
pstmt.setDate(5, Date.valueOf("2023-10-15"));
pstmt.setString(6, "Electronics");
pstmt.addBatch();
// 客戶 B
pstmt.setInt(1, 2);
pstmt.setString(2, "李四");
pstmt.setString(3, "Purchase");
pstmt.setBigDecimal(4, new java.math.BigDecimal("200.00"));
pstmt.setDate(5, Date.valueOf("2023-10-11"));
pstmt.setString(6, "Books");
pstmt.addBatch();
pstmt.setInt(1, 2);
pstmt.setString(2, "李四");
pstmt.setString(3, "Purchase");
pstmt.setBigDecimal(4, new java.math.BigDecimal("120.00"));
pstmt.setDate(5, Date.valueOf("2023-10-13"));
pstmt.setString(6, "Electronics");
pstmt.addBatch();
pstmt.setInt(1, 2);
pstmt.setString(2, "李四");
pstmt.setString(3, "Purchase");
pstmt.setBigDecimal(4, new java.math.BigDecimal("30.00"));
pstmt.setDate(5, Date.valueOf("2023-10-14"));
pstmt.setString(6, "Food");
pstmt.addBatch();
// 客戶 C
pstmt.setInt(1, 3);
pstmt.setString(2, "王五");
pstmt.setString(3, "Purchase");
pstmt.setBigDecimal(4, new java.math.BigDecimal("100.00"));
pstmt.setDate(5, Date.valueOf("2023-10-16"));
pstmt.setString(6, "Clothing");
pstmt.addBatch();
pstmt.setInt(1, 3);
pstmt.setString(2, "王五");
pstmt.setString(3, "Purchase");
pstmt.setBigDecimal(4, new java.math.BigDecimal("90.00"));
pstmt.setDate(5, Date.valueOf("2023-10-17"));
pstmt.setString(6, "Books");
pstmt.addBatch();
pstmt.executeBatch();
System.out.println("? 交易數(shù)據(jù)已插入。");
}
// 演示分組聚合
String querySQL = """
SELECT
customer_name,
COUNT(*) AS transaction_count,
SUM(CASE WHEN transaction_type = 'Purchase' THEN amount ELSE 0 END) AS total_purchases,
SUM(CASE WHEN transaction_type = 'Refund' THEN amount ELSE 0 END) AS total_refunds,
SUM(amount) AS net_amount,
AVG(amount) AS average_transaction_amount,
MAX(transaction_date) AS last_transaction_date,
MIN(transaction_date) AS first_transaction_date
FROM transactions
GROUP BY customer_name
ORDER BY total_purchases DESC
""";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(querySQL)) {
System.out.println("\n=== 分組聚合函數(shù)演示 ===");
System.out.printf("%-10s %-15s %-15s %-15s %-15s %-15s %-20s %-20s%n",
"客戶名", "交易次數(shù)", "總支出", "總退款", "凈額", "平均交易額", "最近交易日期", "首次交易日期");
while (rs.next()) {
String customerName = rs.getString("customer_name");
long transactionCount = rs.getLong("transaction_count");
BigDecimal totalPurchases = rs.getBigDecimal("total_purchases");
BigDecimal totalRefunds = rs.getBigDecimal("total_refunds");
BigDecimal netAmount = rs.getBigDecimal("net_amount");
BigDecimal averageTransactionAmount = rs.getBigDecimal("average_transaction_amount");
Date lastTransactionDate = rs.getDate("last_transaction_date");
Date firstTransactionDate = rs.getDate("first_transaction_date");
System.out.printf("%-10s %-15d %-15s %-15s %-15s %-15s %-20s %-20s%n",
customerName, transactionCount,
totalPurchases.setScale(2, java.math.RoundingMode.HALF_UP),
totalRefunds.setScale(2, java.math.RoundingMode.HALF_UP),
netAmount.setScale(2, java.math.RoundingMode.HALF_UP),
averageTransactionAmount.setScale(2, java.math.RoundingMode.HALF_UP),
lastTransactionDate, firstTransactionDate);
}
}
} catch (SQLException e) {
System.err.println("執(zhí)行分組聚合函數(shù)演示時發(fā)生錯誤: " + 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();
}
}代碼解釋
- 創(chuàng)建表：創(chuàng)建
transactions表存儲交易記錄。 - 插入數(shù)據(jù)：插入九條交易記錄，包含客戶 ID、姓名、交易類型（購買或退款）、金額、日期和類別。
- 查詢與演示：
- 使用
GROUP BY customer_name按客戶分組。 - 使用
COUNT(*)計算每位客戶的交易次數(shù)。 - 使用
SUM(CASE WHEN transaction_type = 'Purchase' THEN amount ELSE 0 END)計算每位客戶的總支出。 - 使用
SUM(CASE WHEN transaction_type = 'Refund' THEN amount ELSE 0 END)計算每位客戶的總退款。 - 使用
SUM(amount)計算每位客戶的凈額（支出減去退款）。 - 使用
AVG(amount)計算每位客戶的平均交易額。 - 使用
MAX(transaction_date)和MIN(transaction_date)找出每位客戶的最近和首次交易日期。 - 使用
ORDER BY total_purchases DESC按總支出降序排列。
- 使用
- 輸出結(jié)果：展示了每位客戶的詳細(xì)交易統(tǒng)計信息。
四、綜合實戰(zhàn)：銷售數(shù)據(jù)分析
讓我們結(jié)合前面學(xué)到的所有函數(shù)，進(jìn)行一次綜合實戰(zhàn)——構(gòu)建一個銷售數(shù)據(jù)分析報告。
實戰(zhàn)目標(biāo)
為一家零售公司生成一份銷售報告，包含：
- 每個產(chǎn)品的銷售總額。
- 每個產(chǎn)品的平均銷售價格。
- 每個區(qū)域的銷售總額。
- 每個季度的銷售總額。
- 每個季度的增長率。
- 各個產(chǎn)品的銷售排名。
數(shù)據(jù)準(zhǔn)備
-- 創(chuàng)建銷售數(shù)據(jù)表
CREATE TABLE IF NOT EXISTS quarterly_sales (
id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(255),
quantity INT,
unit_price DECIMAL(10, 2),
sale_date DATE,
region VARCHAR(50),
quarter VARCHAR(10) -- 用于存儲季度信息，例如 'Q1_2023'
);
-- 插入示例數(shù)據(jù)
INSERT INTO quarterly_sales (product_name, quantity, unit_price, sale_date, region, quarter) VALUES
('iPhone 14', 2, 999.99, '2023-01-15', 'North', 'Q1_2023'),
('Samsung Galaxy S23', 1, 899.99, '2023-01-20', 'South', 'Q1_2023'),
('MacBook Pro', 1, 1999.99, '2023-02-10', 'North', 'Q1_2023'),
('iPad Air', 3, 599.99, '2023-02-25', 'East', 'Q1_2023'),
('Apple Watch', 2, 399.99, '2023-03-10', 'West', 'Q1_2023'),
('AirPods Pro', 1, 249.99, '2023-03-20', 'North', 'Q1_2023'),
('Galaxy Tab S8', 2, 799.99, '2023-04-05', 'South', 'Q2_2023'),
('Surface Laptop', 1, 1299.99, '2023-04-15', 'West', 'Q2_2023'),
('ThinkPad X1', 1, 1899.99, '2023-05-10', 'North', 'Q2_2023'),
('Pixel 7', 2, 799.99, '2023-05-20', 'East', 'Q2_2023'),
('Dell XPS', 1, 1599.99, '2023-06-05', 'West', 'Q2_2023'),
('MacBook Air', 3, 1199.99, '2023-06-15', 'North', 'Q2_2023'),
('iPhone 15', 1, 1099.99, '2023-07-10', 'South', 'Q3_2023'),
('Galaxy S24', 2, 999.99, '2023-07-20', 'North', 'Q3_2023'),
('Surface Pro', 1, 1399.99, '2023-08-05', 'West', 'Q3_2023'),
('ThinkPad P1', 1, 2499.99, '2023-08-15', 'East', 'Q3_2023'),
('iPad Pro', 2, 899.99, '2023-09-05', 'North', 'Q3_2023'),
('AirPods 2nd Gen', 3, 179.99, '2023-09-20', 'South', 'Q3_2023'),
('Galaxy Watch', 1, 299.99, '2023-10-05', 'West', 'Q4_2023'),
('Mac Studio', 1, 2999.99, '2023-10-10', 'North', 'Q4_2023'),
('Surface Book', 1, 1899.99, '2023-10-20', 'East', 'Q4_2023'),
('Dell Inspiron', 2, 899.99, '2023-10-25', 'South', 'Q4_2023');Java 代碼示例
import java.sql.*;
import java.math.BigDecimal;
public class SalesAnalysisExample {
private static final String DB_URL = "jdbc:mysql://localhost:3306/test_db?useSSL=false&serverTimezone=UTC";
private static final String DB_USER = "root"; // 替換為你的用戶名
private static final String DB_PASSWORD = "password"; // 替換為你的密碼
public static void generateSalesReport() {
Connection conn = null;
try {
conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
// 1. 每個產(chǎn)品的銷售總額和平均價格
String productSalesQuery = """
SELECT
product_name,
SUM(quantity * unit_price) AS total_sales,
AVG(unit_price) AS avg_price,
SUM(quantity) AS total_quantity_sold
FROM quarterly_sales
GROUP BY product_name
ORDER BY total_sales DESC
""";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(productSalesQuery)) {
System.out.println("\n=== 產(chǎn)品銷售總額與平均價格 ===");
System.out.printf("%-20s %-15s %-15s %-15s%n", "產(chǎn)品名", "總銷售額", "平均單價", "總銷量");
while (rs.next()) {
String productName = rs.getString("product_name");
BigDecimal totalSales = rs.getBigDecimal("total_sales");
BigDecimal avgPrice = rs.getBigDecimal("avg_price");
long totalQuantitySold = rs.getLong("total_quantity_sold");
System.out.printf("%-20s %-15s %-15s %-15d%n",
productName,
totalSales.setScale(2, java.math.RoundingMode.HALF_UP),
avgPrice.setScale(2, java.math.RoundingMode.HALF_UP),
totalQuantitySold);
}
}
// 2. 每個區(qū)域的銷售總額
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=== 區(qū)域銷售總額 ===");
System.out.printf("%-10s %-15s%n", "區(qū)域", "總銷售額");
while (rs.next()) {
String region = rs.getString("region");
BigDecimal totalSales = rs.getBigDecimal("total_sales");
System.out.printf("%-10s %-15s%n",
region,
totalSales.setScale(2, java.math.RoundingMode.HALF_UP));
}
}
// 3. 每個季度的銷售總額
String quarterSalesQuery = """
SELECT
quarter,
SUM(quantity * unit_price) AS total_sales
FROM quarterly_sales
GROUP BY quarter
ORDER BY quarter
""";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(quarterSalesQuery)) {
System.out.println("\n=== 季度銷售總額 ===");
System.out.printf("%-12s %-15s%n", "季度", "總銷售額");
while (rs.next()) {
String quarter = rs.getString("quarter");
BigDecimal totalSales = rs.getBigDecimal("total_sales");
System.out.printf("%-12s %-15s%n",
quarter,
totalSales.setScale(2, java.math.RoundingMode.HALF_UP));
}
}
// 4. 每個季度的增長率（使用 LAG 窗口函數(shù)）
// 注意：MySQL 8.0+ 支持窗口函數(shù)
String quarterGrowthQuery = """
WITH quarterly_totals AS (
SELECT
quarter,
SUM(quantity * unit_price) AS total_sales
FROM quarterly_sales
GROUP BY quarter
),
quarterly_growth AS (
SELECT
quarter,
total_sales,
LAG(total_sales, 1) OVER (ORDER BY quarter) AS previous_quarter_sales
FROM quarterly_totals
)
SELECT
quarter,
total_sales,
previous_quarter_sales,
CASE
WHEN previous_quarter_sales IS NOT NULL AND previous_quarter_sales > 0 THEN
ROUND(((total_sales - previous_quarter_sales) / previous_quarter_sales) * 100, 2)
ELSE NULL
END AS growth_percentage
FROM quarterly_growth
ORDER BY quarter
""";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(quarterGrowthQuery)) {
System.out.println("\n=== 季度銷售增長率 ===");
System.out.printf("%-12s %-15s %-15s %-15s%n", "季度", "總銷售額", "上季度銷售額", "增長率(%)");
while (rs.next()) {
String quarter = rs.getString("quarter");
BigDecimal totalSales = rs.getBigDecimal("total_sales");
BigDecimal previousQuarterSales = rs.getBigDecimal("previous_quarter_sales");
BigDecimal growthPercentage = rs.getBigDecimal("growth_percentage");
System.out.printf("%-12s %-15s %-15s %-15s%n",
quarter,
totalSales.setScale(2, java.math.RoundingMode.HALF_UP),
previousQuarterSales == null ? "N/A" : previousQuarterSales.setScale(2, java.math.RoundingMode.HALF_UP),
growthPercentage == null ? "N/A" : growthPercentage.toString() + "%");
}
}
// 5. 各個產(chǎn)品的銷售排名
String productRankingQuery = """
SELECT
product_name,
SUM(quantity * unit_price) AS total_sales,
ROW_NUMBER() OVER (ORDER BY SUM(quantity * unit_price) DESC) AS sales_rank
FROM quarterly_sales
GROUP BY product_name
ORDER BY total_sales DESC
""";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(productRankingQuery)) {
System.out.println("\n=== 產(chǎn)品銷售排名 ===");
System.out.printf("%-20s %-15s %-10s%n", "產(chǎn)品名", "總銷售額", "排名");
while (rs.next()) {
String productName = rs.getString("product_name");
BigDecimal totalSales = rs.getBigDecimal("total_sales");
long salesRank = rs.getLong("sales_rank");
System.out.printf("%-20s %-15s %-10d%n",
productName,
totalSales.setScale(2, java.math.RoundingMode.HALF_UP),
salesRank);
}
}
} catch (SQLException e) {
System.err.println("生成銷售報告時發(fā)生錯誤: " + 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();
}
}代碼解釋
- 連接數(shù)據(jù)庫：獲取數(shù)據(jù)庫連接。
- 產(chǎn)品銷售分析：
- 使用
SUM(quantity * unit_price)計算每個產(chǎn)品的總銷售額。 - 使用
AVG(unit_price)計算每個產(chǎn)品的平均單價。 - 使用
SUM(quantity)計算每個產(chǎn)品的總銷量。 - 使用
GROUP BY product_name按產(chǎn)品分組。 - 使用
ORDER BY total_sales DESC按銷售額降序排列。
- 使用
- 區(qū)域銷售分析：
- 使用
SUM(quantity * unit_price)計算每個區(qū)域的總銷售額。 - 使用
GROUP BY region按區(qū)域分組。 - 使用
ORDER BY total_sales DESC按銷售額降序排列。
- 使用
- 季度銷售分析：
- 使用
SUM(quantity * unit_price)計算每個季度的總銷售額。 - 使用
GROUP BY quarter按季度分組。 - 使用
ORDER BY quarter按季度升序排列。
- 使用
- 季度增長率分析：
- 使用
WITH子句創(chuàng)建一個臨時結(jié)果集quarterly_totals，計算每個季度的總銷售額。 - 使用
LAG窗口函數(shù)獲取上一季度的銷售額。 - 計算增長率：
(本期銷售額 - 上期銷售額) / 上期銷售額 * 100。 - 使用
CASE語句處理第一個季度沒有上期數(shù)據(jù)的情況。
- 使用
- 產(chǎn)品銷售排名：
- 使用
ROW_NUMBER() OVER (ORDER BY SUM(quantity * unit_price) DESC)為產(chǎn)品分配銷售額排名。 - 使用
GROUP BY product_name按產(chǎn)品分組。 - 使用
ORDER BY total_sales DESC按銷售額降序排列。
- 使用
- 輸出報告：在控制臺打印出所有分析結(jié)果。
總結(jié)：函數(shù)是數(shù)據(jù)庫操作的利器
通過今天的深入學(xué)習(xí)，我們掌握了 MySQL 中最常用的字符串函數(shù)、日期函數(shù)和聚合函數(shù)。這些函數(shù)不僅僅是簡單的工具，更是解決實際業(yè)務(wù)問題的強大武器。從簡單的字符串拼接、日期計算，到復(fù)雜的統(tǒng)計分析，函數(shù)的應(yīng)用無處不在。
- 字符串函數(shù)：幫助我們處理和轉(zhuǎn)換文本數(shù)據(jù)，確保數(shù)據(jù)格式統(tǒng)一、整潔。
- 日期函數(shù)：使我們能夠高效地處理時間相關(guān)的業(yè)務(wù)邏輯，如計算周期、格式化顯示等。
- 聚合函數(shù)：是數(shù)據(jù)分析的核心，讓我們能夠快速洞察數(shù)據(jù)背后的規(guī)律和趨勢。
在實際項目中，熟練運用這些函數(shù)，不僅能提高開發(fā)效率，還能寫出更加健壯和易維護(hù)的 SQL 查詢語句。希望這篇博客能為你在 MySQL 的道路上提供堅實的基礎(chǔ)和實用的指導(dǎo)！
?? 了解更多關(guān)于 MySQL 函數(shù)的信息，請參考以下官方文檔和權(quán)威資源：
- MySQL 8.0 Reference Manual - Functions and Operators
- MySQL 8.0 Reference Manual - String Functions
- MySQL 8.0 Reference Manual - Date and Time Functions
- MySQL 8.0 Reference Manual - Aggregate (GROUP BY) Functions
- W3Schools SQL Functions
- GeeksforGeeks - MySQL String Functions
- GeeksforGeeks - MySQL Date Functions
- GeeksforGeeks - MySQL Aggregate Functions
Mermaid 圖表：字符串函數(shù)關(guān)系圖
Mermaid 圖表：日期函數(shù)關(guān)系圖
Mermaid 圖表：聚合函數(shù)關(guān)系圖
Mermaid 圖表：函數(shù)綜合應(yīng)用示意圖
總結(jié)
到此這篇關(guān)于MySQL常用函數(shù)之字符串、日期、聚合函數(shù)的文章就介紹到這了,更多相關(guān)MySQL字符串、日期、聚合函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了MySQL Where 條件語句介紹和運算符小結(jié),本文同時還給出了一些用法示例,需要的朋友可以參考下2014-11-11
insert和select結(jié)合實現(xiàn)"插入某字段在數(shù)據(jù)庫中的最大值＋1"的方法
今天小編就為大家分享一篇關(guān)于insert和select結(jié)合實現(xiàn)"插入某字段在數(shù)據(jù)庫中的最大值＋1"的方法，小編覺得內(nèi)容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧2019-03-03
CentOS7環(huán)境安裝包部署并配置MySQL5.7教程
文章詳細(xì)介紹了卸載和重新安裝MySQL 5.7的步驟,包括卸載舊版本、查看和卸載相關(guān)服務(wù)、下載和解壓新版本安裝包、創(chuàng)建用戶和目錄、配置my.cnf、初始化數(shù)據(jù)庫、啟動和修改密碼等過程,同時,還解決了客戶端不支持問題以及配置遠(yuǎn)程登錄的步驟2026-02-02
教你如何讓spark?sql寫mysql的時候支持update操作
spark提供了一個枚舉類，用來支撐對接數(shù)據(jù)源的操作模式，本文重點給大家介紹如何讓spark?sql寫mysql的時候支持update操作，本文通過實例代碼給大家介紹的非常詳細(xì)，需要的朋友參考下吧2022-02-02
根據(jù)要求配置MySQL主從備份、讀寫分離，結(jié)合網(wǎng)上的文檔，對搭建的步驟和出現(xiàn)的問題以及解決的過程做了如下筆記2012-03-03
MySQL 橫向衍生表(Lateral Derived Tables)的實現(xiàn)
橫向衍生表適用于在需要通過子查詢獲取中間結(jié)果集的場景,相對于普通衍生表,橫向衍生表可以引用在其之前出現(xiàn)過的表名,本文就來介紹一下MySQL 橫向衍生表(Lateral Derived Tables)的實現(xiàn),感興趣的可以了解一下2025-06-06
我想很多朋友都不怎么會在mysql中使用float類型，特別是用到金錢時我們可能會用雙精度來做，我們知道m(xù)ysql的float類型是單精度浮點類型不小心就會導(dǎo)致數(shù)據(jù)誤差2012-11-11
這篇文章主要給大家介紹了關(guān)于如何查看MySQL數(shù)據(jù)文件存放路徑的相關(guān)資料,文中通過圖文以及代碼示例介紹的非常詳細(xì),對大家學(xué)習(xí)或者使用mysql具有一定的參考借鑒價值,需要的朋友可以參考下2023-08-08
這篇文章主要為大家詳細(xì)介紹了mysql 8.0.15 安裝配置圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-03-03
這篇文章主要介紹了MySql視圖與觸發(fā)器使用過程,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2022-12-12