我要评论
在数据库的世界里，函数就像是程序员的工具箱，为我们提供了强大的数据处理能力。无论是清洗数据、格式化输出、计算时间差，还是进行统计分析，mysql 函数都能大显身手。今天，我们将一起深入探索 mysql 中最常用的几类函数：字符串函数、日期函数 和 聚合函数。我们将通过丰富的示例和 java 代码来演示如何在实际项目中运用这些函数，让你在 30 分钟内轻松掌握它们！
一、字符串函数
字符串函数是处理文本数据的基础。在日常开发中，我们经常需要对字符串进行截取、替换、大小写转换、填充等操作。mysql 提供了大量内置的字符串函数来简化这些任务。
1. 字符串长度函数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;
-- 结果可能类似于：
-- name | description | byte_length | char_length
-- alice | hello world | 11 | 11
-- 张三 | 你好世界 | 12 | 4 (utf8下中文字符占3字节)java 代码示例：
import java.sql.*;
import java.util.arraylist;
import java.util.list;
public class stringfunctionexample {
private static final string db_url = "jdbc:mysql://localhost:3306/test_db?usessl=false&servertimezone=utc";
private static final string db_user = "root"; // 替换为你的用户名
private static final string db_password = "password"; // 替换为你的密码
public static void demonstratelengthfunctions() {
connection conn = null;
try {
conn = drivermanager.getconnection(db_url, db_user, db_password);
// 创建示例表
string createtablesql = """
create table if not exists sample_users (
id int auto_increment primary key,
name varchar(100),
description text
)
""";
try (statement stmt = conn.createstatement()) {
stmt.execute(createtablesql);
}
// 插入测试数据
string insertsql = "insert into sample_users (name, description) values (?, ?)";
try (preparedstatement pstmt = conn.preparestatement(insertsql)) {
pstmt.setstring(1, "alice");
pstmt.setstring(2, "hello world");
pstmt.addbatch();
pstmt.setstring(1, "张三");
pstmt.setstring(2, "你好世界");
pstmt.addbatch();
pstmt.executebatch();
system.out.println("✅ 测试数据已插入。");
}
// 查询并展示 length 和 char_length
string querysql = """
select
name,
description,
length(description) as byte_length,
char_length(description) as char_length
from sample_users
""";
try (statement stmt = conn.createstatement();
resultset rs = stmt.executequery(querysql)) {
system.out.println("\n=== 字符串长度函数演示 ===");
system.out.printf("%-10s %-15s %-15s %-15s%n", "姓名", "描述", "字节长度", "字符长度");
while (rs.next()) {
string name = rs.getstring("name");
string description = rs.getstring("description");
int bytelen = rs.getint("byte_length");
int charlen = rs.getint("char_length");
system.out.printf("%-10s %-15s %-15d %-15d%n", name, description, bytelen, charlen);
}
}
} catch (sqlexception e) {
system.err.println("执行字符串长度函数演示时发生错误: " + e.getmessage());
e.printstacktrace();
} finally {
try {
if (conn != null && !conn.isclosed()) {
conn.close();
}
} catch (sqlexception e) {
e.printstacktrace();
}
}
}
public static void main(string[] args) {
demonstratelengthfunctions();
}
}代码解释
- 创建表：首先创建一个
sample_users表用于存储示例数据。 - 插入数据：使用
preparedstatement批量插入包含英文和中文的测试数据。 - 查询与展示：执行 sql 查询，同时使用
length()和char_length()计算description字段的长度，并在 java 控制台打印结果。 - 输出对比：对于中文字符，
length()返回的是字节数（每个中文字符通常占 3 个字节），而char_length()返回的是字符数。
2. 字符串截取函数substring()/substr()和left()/right()
这些函数用于从字符串中提取一部分。
substring(str, pos, len)：从str的位置pos开始，截取长度为len的子字符串。pos从 1 开始计数。left(str, len)：从字符串左边开始截取len个字符。right(str, len)：从字符串右边开始截取len个字符。
示例：
-- 假设有一个产品名称字段 product_name
-- select substring('mysql database', 1, 5) as result; -- 输出: mysql
-- select left('mysql database', 5) as result; -- 输出: mysql
-- select right('mysql database', 7) as result; -- 输出: database
-- select substring('mysql database', 7) as result; -- 输出: database (从位置7开始到末尾)
-- select substring('mysql database', 7, 4) as result; -- 输出: dat (从位置7开始，截取4个字符)java 代码示例：
import java.sql.*;
public class substringexample {
private static final string db_url = "jdbc:mysql://localhost:3306/test_db?usessl=false&servertimezone=utc";
private static final string db_user = "root"; // 替换为你的用户名
private static final string db_password = "password"; // 替换为你的密码
public static void demonstratesubstringfunctions() {
connection conn = null;
try {
conn = drivermanager.getconnection(db_url, db_user, db_password);
// 创建示例表
string createtablesql = """
create table if not exists products (
id int auto_increment primary key,
product_name varchar(255),
product_code varchar(50)
)
""";
try (statement stmt = conn.createstatement()) {
stmt.execute(createtablesql);
}
// 插入测试数据
string insertsql = "insert into products (product_name, product_code) values (?, ?)";
try (preparedstatement pstmt = conn.preparestatement(insertsql)) {
pstmt.setstring(1, "iphone 14 pro max");
pstmt.setstring(2, "ip14pm001");
pstmt.addbatch();
pstmt.setstring(1, "samsung galaxy s23 ultra");
pstmt.setstring(2, "sgs23u002");
pstmt.addbatch();
pstmt.setstring(1, "macbook air m2");
pstmt.setstring(2, "ma2m003");
pstmt.addbatch();
pstmt.executebatch();
system.out.println("✅ 产品数据已插入。");
}
// 演示截取函数
string querysql = """
select
product_name,
product_code,
left(product_name, 5) as brand_name, -- 截取前5个字符作为品牌名
right(product_code, 3) as last_three_digits, -- 截取产品代码后3位
substring(product_name, 1, 5) as substring_brand, -- 同样是前5个字符
substring(product_code, 4, 3) as middle_part_code -- 从位置4开始截取3个字符
from products
""";
try (statement stmt = conn.createstatement();
resultset rs = stmt.executequery(querysql)) {
system.out.println("\n=== 字符串截取函数演示 ===");
system.out.printf("%-25s %-15s %-15s %-15s %-15s %-15s%n",
"产品名称", "产品代码", "品牌名", "后三位", "截取品牌", "中间代码");
while (rs.next()) {
string productname = rs.getstring("product_name");
string productcode = rs.getstring("product_code");
string brandname = rs.getstring("brand_name");
string lastthreedigits = rs.getstring("last_three_digits");
string substringbrand = rs.getstring("substring_brand");
string middlepartcode = rs.getstring("middle_part_code");
system.out.printf("%-25s %-15s %-15s %-15s %-15s %-15s%n",
productname, productcode, brandname, lastthreedigits, substringbrand, middlepartcode);
}
}
} catch (sqlexception e) {
system.err.println("执行字符串截取函数演示时发生错误: " + e.getmessage());
e.printstacktrace();
} finally {
try {
if (conn != null && !conn.isclosed()) {
conn.close();
}
} catch (sqlexception e) {
e.printstacktrace();
}
}
}
public static void main(string[] args) {
demonstratesubstringfunctions();
}
}代码解释
- 创建表：创建
products表存储产品信息。 - 插入数据：插入三条包含产品名称和代码的测试数据。
- 查询与演示：
- 使用
left(product_name, 5)提取产品名称的前 5 个字符作为品牌名。 - 使用
right(product_code, 3)提取产品代码的后 3 个字符。 - 使用
substring(product_name, 1, 5)和left类似，但更灵活。 - 使用
substring(product_code, 4, 3)从产品代码的第 4 个字符开始截取 3 个字符。
- 使用
- 输出结果：清晰地展示了不同截取函数的效果。
3. 字符串替换函数replace()
replace(str, from_str, to_str) 用于将字符串 str 中所有出现的 from_str 替换为 to_str。
示例：
-- select replace('hello world', 'world', 'mysql') as result; -- 输出: hello mysql
-- select replace('abcabcabc', 'bc', 'xy') as result; -- 输出: 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);
// 创建示例表
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, "zhang.xiaoming@example.com");
pstmt.setstring(3, "我是张小明，热爱技术。联系方式：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("✅ 用户资料已插入。");
}
// 演示替换函数
string querysql = """
select
full_name,
email,
bio,
replace(bio, '联系方式', '联系信息') as modified_bio, -- 替换“联系方式”为“联系信息”
replace(email, '@', '[at]') as masked_email -- 用 [at] 替换 @ 符号（仅演示）
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(bio, '联系方式', '联系信息')将简介中的“联系方式”替换为“联系信息”。 - 使用
replace(email, '@', '[at]')将邮箱地址中的@符号替换为[at]，用于演示（实际应用中可能用于脱敏）。
- 使用
- 输出结果：展示原始数据和替换后的效果。
4. 大小写转换函数upper()/ucase()和lower()/lcase()
这些函数用于将字符串转换为大写或小写。
upper(str)/ucase(str)：将字符串转换为大写。lower(str)/lcase(str)：将字符串转换为小写。
示例：
-- select upper('hello world') as upper_result; -- 输出: hello world
-- select lower('hello world') as lower_result; -- 输出: hello world
-- 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);
// 创建示例表
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(first_name)将名字转换为大写。 - 使用
lower(last_name)将姓氏转换为小写。 - 使用
concat(upper(substring(first_name, 1, 1)), lower(substring(first_name, 2)))实现简单的标题格式（首字母大写，其余小写）。
- 使用
- 输出结果：展示了原始数据和各种大小写转换后的效果。
5. 去除空格函数trim()/ltrim()/rtrim()
这些函数用于去除字符串两端或特定一侧的空白字符。
trim(str)：去除字符串两端的空白字符。ltrim(str)：去除字符串左侧的空白字符。rtrim(str)：去除字符串右侧的空白字符。trim([leading|trailing|both] [remstr from] str)：更详细的语法，可以指定去除哪一侧的空白或指定要移除的字符。
示例：
-- 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);
// 创建示例表
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,
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=== 去除空格函数演示 ===");
system.out.printf("%-25s %-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");
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("执行去空格函数演示时发生错误: " + 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(raw_data)去除两端的空白字符。 - 使用
ltrim(raw_data)去除左侧空白。 - 使用
rtrim(raw_data)去除右侧空白。 - 使用
trim('\r\n\t ' from raw_data)去除指定的多种空白字符。
- 使用
- 输出结果：展示了原始数据和各种去空格处理后的效果。
6. 字符串连接函数concat()
concat(str1, str2, ...) 用于将多个字符串连接成一个字符串。如果任意一个参数为 null，则结果为 null。
示例：
-- select concat('hello', ' ', 'world') as result; -- 输出: hello world
-- select concat('user:', 'john') as result; -- 输出: user:john
-- select concat('name: ', first_name, ' ', last_name) as full_name from users; -- 连接姓名字段
-- select concat_ws('-', '2023', '10', '15') as date_string; -- 使用分隔符连接，concat_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);
// 创建示例表
string createtablesql = """
create table if not exists addresses (
id int auto_increment primary key,
street varchar(255),
city varchar(100),
state varchar(100),
zip_code varchar(20)
)
""";
try (statement stmt = conn.createstatement()) {
stmt.execute(createtablesql);
}
// 插入测试数据
string insertsql = "insert into addresses (street, city, state, zip_code) values (?, ?, ?, ?)";
try (preparedstatement pstmt = conn.preparestatement(insertsql)) {
pstmt.setstring(1, "123 main street");
pstmt.setstring(2, "new york");
pstmt.setstring(3, "ny");
pstmt.setstring(4, "10001");
pstmt.addbatch();
pstmt.setstring(1, "456 oak avenue");
pstmt.setstring(2, "los angeles");
pstmt.setstring(3, "ca");
pstmt.setstring(4, "90210");
pstmt.addbatch();
pstmt.executebatch();
system.out.println("✅ 地址数据已插入。");
}
// 演示字符串连接
string querysql = """
select
street,
city,
state,
zip_code,
concat(street, ', ', city, ', ', state, ' ', zip_code) as full_address,
concat_ws(', ', street, city, state, zip_code) as formatted_address -- 使用分隔符
from addresses
""";
try (statement stmt = conn.createstatement();
resultset rs = stmt.executequery(querysql)) {
system.out.println("\n=== 字符串连接函数演示 ===");
system.out.printf("%-25s %-20s %-10s %-10s %-50s %-50s%n",
"街道", "城市", "州", "邮编", "完整地址", "格式化地址");
while (rs.next()) {
string street = rs.getstring("street");
string city = rs.getstring("city");
string state = rs.getstring("state");
string zipcode = rs.getstring("zip_code");
string fulladdress = rs.getstring("full_address");
string formattedaddress = rs.getstring("formatted_address");
system.out.printf("%-25s %-20s %-10s %-10s %-50s %-50s%n",
street, city, state, zipcode, fulladdress, formattedaddress);
}
}
} catch (sqlexception e) {
system.err.println("执行字符串连接函数演示时发生错误: " + e.getmessage());
e.printstacktrace();
} finally {
try {
if (conn != null && !conn.isclosed()) {
conn.close();
}
} catch (sqlexception e) {
e.printstacktrace();
}
}
}
public static void main(string[] args) {
demonstrateconcatfunction();
}
}代码解释
- 创建表：创建
addresses表存储地址信息。 - 插入数据：插入包含街道、城市、州、邮编的测试数据。
- 查询与演示：
- 使用
concat(street, ', ', city, ', ', state, ' ', zip_code)将地址信息拼接成一个完整的地址字符串。 - 使用
concat_ws(', ', street, city, state, zip_code)使用逗号作为分隔符拼接地址，concat_ws是concat的变体，专门用于带分隔符的连接。
- 使用
- 输出结果：展示了原始数据和拼接后的地址效果。
二、日期函数
日期函数是处理时间相关数据的强大工具。在应用开发中，我们经常需要计算日期差、提取日期组件、格式化日期显示、处理时间戳等。mysql 提供了丰富的日期函数来应对这些需求。
1. 当前日期和时间函数now(),curdate(),curtime()
这些函数用于获取当前的日期、时间和时间戳。
now()：返回当前日期和时间（yyyy-mm-dd hh:mm:ss格式）。curdate()：返回当前日期（yyyy-mm-dd格式）。curtime()：返回当前时间（hh:mm:ss格式）。
示例：
-- select now() as current_datetime; -- 输出: 2023-10-15 14:30:45 -- select curdate() as current_date; -- 输出: 2023-10-15 -- select curtime() as current_time; -- 输出: 14:30:45
java 代码示例：
import java.sql.*;
import java.time.localdatetime;
import java.time.format.datetimeformatter;
public class datetimefunctionexample {
private static final string db_url = "jdbc:mysql://localhost:3306/test_db?usessl=false&servertimezone=utc";
private static final string db_user = "root"; // 替换为你的用户名
private static final string db_password = "password"; // 替换为你的密码
public static void demonstratedatetimefunctions() {
connection conn = null;
try {
conn = drivermanager.getconnection(db_url, db_user, db_password);
// 创建示例表
string createtablesql = """
create table if not exists events (
id int auto_increment primary key,
event_name varchar(255),
event_date date,
created_at datetime,
updated_at timestamp default current_timestamp on update current_timestamp
)
""";
try (statement stmt = conn.createstatement()) {
stmt.execute(createtablesql);
}
// 插入测试数据
string insertsql = "insert into events (event_name, event_date, created_at) values (?, ?, ?)";
try (preparedstatement pstmt = conn.preparestatement(insertsql)) {
pstmt.setstring(1, "产品发布会");
pstmt.setdate(2, date.valueof("2023-11-20")); // 使用 java.sql.date
pstmt.settimestamp(3, timestamp.valueof("2023-10-15 10:00:00")); // 使用 java.sql.timestamp
pstmt.addbatch();
pstmt.setstring(1, "团队建设活动");
pstmt.setdate(2, date.valueof("2023-12-05"));
pstmt.settimestamp(3, timestamp.valueof("2023-10-15 11:30:00"));
pstmt.addbatch();
pstmt.executebatch();
system.out.println("✅ 事件数据已插入。");
}
// 演示日期时间函数
string querysql = """
select
event_name,
event_date,
created_at,
now() as db_now, -- 数据库当前时间
curdate() as db_current_date, -- 数据库当前日期
curtime() as db_current_time, -- 数据库当前时间
date(created_at) as created_date_only, -- 从datetime中提取日期
time(created_at) as created_time_only, -- 从datetime中提取时间
date_format(created_at, '%y-%m-%d %h:%i:%s') as formatted_created_at -- 格式化时间
from events
""";
try (statement stmt = conn.createstatement();
resultset rs = stmt.executequery(querysql)) {
system.out.println("\n=== 日期时间函数演示 ===");
system.out.printf("%-20s %-12s %-20s %-20s %-12s %-12s %-12s %-12s %-20s%n",
"事件名称", "事件日期", "创建时间", "数据库当前时间", "当前日期", "当前时间", "日期", "时间", "格式化时间");
while (rs.next()) {
string eventname = rs.getstring("event_name");
date eventdate = rs.getdate("event_date");
timestamp createdat = rs.gettimestamp("created_at");
timestamp dbnow = rs.gettimestamp("db_now");
date dbcurrentdate = rs.getdate("db_current_date");
time dbcurrenttime = rs.gettime("db_current_time");
date createddateonly = rs.getdate("created_date_only");
time createdtimeonly = rs.gettime("created_time_only");
string formattedcreatedat = rs.getstring("formatted_created_at");
system.out.printf("%-20s %-12s %-20s %-20s %-12s %-12s %-12s %-12s %-20s%n",
eventname, eventdate, createdat, dbnow, dbcurrentdate, dbcurrenttime,
createddateonly, createdtimeonly, formattedcreatedat);
}
}
} catch (sqlexception e) {
system.err.println("执行日期时间函数演示时发生错误: " + e.getmessage());
e.printstacktrace();
} finally {
try {
if (conn != null && !conn.isclosed()) {
conn.close();
}
} catch (sqlexception e) {
e.printstacktrace();
}
}
}
public static void main(string[] args) {
demonstratedatetimefunctions();
}
}代码解释
- 创建表：创建
events表存储事件信息，包含日期和时间字段。 - 插入数据：插入两条事件记录，包含事件名称、日期和创建时间。
- 查询与演示：
- 使用
now()获取数据库服务器的当前时间。 - 使用
curdate()和curtime()获取当前日期和时间。 - 使用
date(created_at)从datetime字段中提取日期部分。 - 使用
time(created_at)从datetime字段中提取时间部分。 - 使用
date_format(created_at, '%y-%m-%d %h:%i:%s')格式化时间显示。
- 使用
- 输出结果：展示了原始数据和各种日期时间函数的处理结果。
2. 日期和时间组件提取函数year(),month(),day(),hour(),minute(),second()
这些函数用于从日期时间值中提取特定的组件。
示例：
-- 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);
// 创建示例表
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);
}
// 插入测试数据
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("✅ 订单数据已插入。");
}
// 演示提取日期时间组件
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("执行日期时间组件提取演示时发生错误: " + 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(order_date),month(order_date),day(order_date)等函数提取年、月、日。 - 使用
hour(order_date),minute(order_date),second(order_date)提取时、分、秒。 - 使用
concat(year(order_date), '-', lpad(month(order_date), 2, '0'))将年份和月份组合成yyyy-mm格式（使用lpad确保月份是两位数）。
- 使用
- 输出结果：展示了原始时间数据和各个组件的提取结果。
3. 日期计算函数date_add()/adddate(),date_sub()/subdate()
这些函数用于对日期进行加减运算。
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);
// 创建示例表
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, -- 任务持续天数
due_date date,
reminder_date date
)
""";
try (statement stmt = conn.createstatement()) {
stmt.execute(createtablesql);
}
// 插入测试数据
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); // 持续3天
pstmt.setdate(3, null); // 后续计算
pstmt.setdate(4, null); // 后续计算
pstmt.addbatch();
pstmt.setstring(1, "系统设计");
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("✅ 任务数据已插入。");
}
// 演示日期计算
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=== 日期计算函数演示 ===");
system.out.printf("%-15s %-12s %-15s %-20s %-20s %-20s %-20s %-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");
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("执行日期计算函数演示时发生错误: " + 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(start_date, interval duration_days day)计算任务的预计截止日期。 - 使用
date_sub(calculated_due_date, interval 1 day)计算实际截止日期（比预计少一天）。 - 使用
date_add(start_date, interval 1 week)计算一周后的时间。 - 使用
date_sub(start_date, interval 1 month)计算一个月前的时间。 - 使用
date_add(start_date, interval 1 year)计算一年后的时间。
- 使用
- 输出结果：展示了原始数据和各种日期计算的结果。
4. 日期差函数datediff()和timestampdiff()
这些函数用于计算两个日期之间的差值。
datediff(date1, date2)：计算date2与date1之间的天数差。如果date2大于date1，返回正值；反之返回负值。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);
// 创建示例表
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);
}
// 插入测试数据
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, "原型设计");
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, "代码实现");
pstmt.setdate(2, date.valueof("2023-10-25"));
pstmt.setdate(3, null); // 尚未完成
pstmt.setstring(4, "planned");
pstmt.addbatch();
pstmt.executebatch();
system.out.println("✅ 项目里程碑数据已插入。");
}
// 演示日期差计算
string querysql = """
select
milestone_name,
planned_date,
actual_date,
datediff(actual_date, planned_date) as days_difference, -- 计算实际日期与计划日期的天数差
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 计算天数差
timestampdiff(week, planned_date, actual_date) as diff_in_weeks, -- 计算周数差
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=== 日期差函数演示 ===");
system.out.printf("%-15s %-12s %-12s %-15s %-20s %-15s %-15s %-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");
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("执行日期差函数演示时发生错误: " + 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(actual_date, planned_date)计算实际完成日期与计划日期的天数差。 - 使用
case语句根据天数差判断项目是“提前”、“按时”还是“延迟”。 - 使用
timestampdiff(day, planned_date, actual_date)重复计算天数差。 - 使用
timestampdiff(week, planned_date, actual_date)计算周数差。 - 使用
timestampdiff(hour, planned_date, actual_date)计算小时差。
- 使用
- 输出结果：展示了每个已完成里程碑的详细日期差信息。
5. 日期格式化函数date_format()
date_format(date, format) 用于将日期或时间值按照指定的格式进行格式化输出。
示例：
-- select date_format('2023-10-15', '%y-%m-%d') as formatted_date; -- 输出: 2023-10-15
-- select date_format('2023-10-15 14:30:45', '%y/%m/%d %h:%i:%s') as formatted_datetime; -- 输出: 2023/10/15 14:30:45
-- select date_format('2023-10-15', '%m %d, %y') as formatted_long_date; -- 输出: october 15th, 2023
-- select date_format('2023-10-15 14:30:45', '%w, %m %e, %y at %h:%i %p') as formatted_long_datetime; -- 输出: sunday, october 15, 2023 at 02:30 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);
// 创建示例表
string createtablesql = """
create table if not exists blog_posts (
id int auto_increment primary key,
title varchar(255),
publish_date datetime,
category varchar(100)
)
""";
try (statement stmt = conn.createstatement()) {
stmt.execute(createtablesql);
}
// 插入测试数据
string insertsql = "insert into blog_posts (title, publish_date, category) values (?, ?, ?)";
try (preparedstatement pstmt = conn.preparestatement(insertsql)) {
pstmt.setstring(1, "mysql 教程入门");
pstmt.settimestamp(2, timestamp.valueof("2023-10-10 09:00:00"));
pstmt.setstring(3, "database");
pstmt.addbatch();
pstmt.setstring(1, "java 编程技巧");
pstmt.settimestamp(2, timestamp.valueof("2023-10-12 16:30:00"));
pstmt.setstring(3, "programming");
pstmt.addbatch();
pstmt.setstring(1, "前端开发指南");
pstmt.settimestamp(2, timestamp.valueof("2023-10-15 11:15:00"));
pstmt.setstring(3, "web");
pstmt.addbatch();
pstmt.executebatch();
system.out.println("✅ 博客文章数据已插入。");
}
// 演示日期格式化
string querysql = """
select
title,
publish_date,
date_format(publish_date, '%y-%m-%d') as simple_date, -- 简单日期格式
date_format(publish_date, '%y/%m/%d %h:%i:%s') as full_datetime, -- 完整日期时间格式
date_format(publish_date, '%m %d, %y') as long_date, -- 长日期格式
date_format(publish_date, '%w, %m %e, %y at %h:%i %p') as formatted_post_date, -- 博客文章格式
date_format(publish_date, '%y年%m月%d日') as chinese_date_format -- 中文日期格式
from blog_posts
order by publish_date desc
""";
try (statement stmt = conn.createstatement();
resultset rs = stmt.executequery(querysql)) {
system.out.println("\n=== 日期格式化函数演示 ===");
system.out.printf("%-25s %-20s %-15s %-20s %-20s %-40s %-20s%n",
"文章标题", "发布日期", "简单日期", "完整时间", "长日期", "格式化发布日期", "中文日期");
while (rs.next()) {
string title = rs.getstring("title");
timestamp publishdate = rs.gettimestamp("publish_date");
string simpledate = rs.getstring("simple_date");
string fulldatetime = rs.getstring("full_datetime");
string longdate = rs.getstring("long_date");
string formattedpostdate = rs.getstring("formatted_post_date");
string chinesedateformat = rs.getstring("chinese_date_format");
system.out.printf("%-25s %-20s %-15s %-20s %-20s %-40s %-20s%n",
title, publishdate, simpledate, fulldatetime, longdate,
formattedpostdate, chinesedateformat);
}
}
} catch (sqlexception e) {
system.err.println("执行日期格式化函数演示时发生错误: " + e.getmessage());
e.printstacktrace();
} finally {
try {
if (conn != null && !conn.isclosed()) {
conn.close();
}
} catch (sqlexception e) {
e.printstacktrace();
}
}
}
public static void main(string[] args) {
demonstratedateformatting();
}
}代码解释
- 创建表：创建
blog_posts表存储博客文章信息。 - 插入数据：插入三条包含文章标题、发布日期和分类的博客文章记录。
- 查询与演示：
- 使用
date_format(publish_date, '%y-%m-%d')输出简单的日期格式。 - 使用
date_format(publish_date, '%y/%m/%d %h:%i:%s')输出完整的日期时间格式。 - 使用
date_format(publish_date, '%m %d, %y')输出长日期格式（如 “october 15th, 2023”）。 - 使用
date_format(publish_date, '%w, %m %e, %y at %h:%i %p')输出适合博客文章的格式（如 “sunday, october 15, 2023 at 02:30 pm”）。 - 使用
date_format(publish_date, '%y年%m月%d日')输出中文日期格式。
- 使用
- 输出结果：展示了每篇文章的原始发布日期和各种格式化后的日期显示效果。
三、聚合函数
聚合函数是对一组值进行计算并返回单一值的函数。它们常用于 group by 子句中进行统计分析。常见的聚合函数包括 count(), sum(), avg(), max(), min() 等。
1. 计数函数count()
count() 用于计算行数或非空值的数量。
count(*)：计算所有行数，包括null值。count(column)：计算指定列中非null值的数量。count(distinct column)：计算指定列中不同非null值的数量。
示例：
-- select count(*) from users; -- 计算用户总数 -- select count(email) from users; -- 计算有邮箱的用户数 -- select count(distinct department) from employees; -- 计算不同部门的数量
java 代码示例：
import java.sql.*;
public class countfunctionexample {
private static final string db_url = "jdbc:mysql://localhost:3306/test_db?usessl=false&servertimezone=utc";
private static final string db_user = "root"; // 替换为你的用户名
private static final string db_password = "password"; // 替换为你的密码
public static void demonstratecountfunction() {
connection conn = null;
try {
conn = drivermanager.getconnection(db_url, db_user, db_password);
// 创建示例表
string createtablesql = """
create table if not exists sales (
id int auto_increment primary key,
product_name varchar(255),
quantity int,
price decimal(10, 2),
sale_date date,
region varchar(50)
)
""";
try (statement stmt = conn.createstatement()) {
stmt.execute(createtablesql);
}
// 插入测试数据
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("✅ 销售数据已插入。");
}
// 演示计数函数
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=== 计数函数演示 ===");
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("不同区域数量: " + uniqueregions);
system.out.println("不同产品数量: " + uniqueproductssold);
}
}
} 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)计算具有销售日期的记录数量（即sale_date不为null的记录数）。 - 使用
count(distinct region)计算不同区域的数量。 - 使用
count(distinct product_name)计算不同产品的数量。
- 使用
- 输出结果：展示了各种计数的结果。
2. 求和函数sum()
sum() 用于计算数值列的总和。
示例：
-- select sum(price) from sales; -- 计算所有商品的总价 -- select sum(quantity * price) from sales; -- 计算所有商品的总销售额 -- select sum(price) from sales where region = 'north'; -- 计算北部区域的总销售额
java 代码示例：
import java.sql.*;
public class sumfunctionexample {
private static final string db_url = "jdbc:mysql://localhost:3306/test_db?usessl=false&servertimezone=utc";
private static final string db_user = "root"; // 替换为你的用户名
private static final string db_password = "password"; // 替换为你的密码
public static void demonstratesumfunction() {
connection conn = null;
try {
conn = drivermanager.getconnection(db_url, db_user, db_password);
// 创建示例表
string createtablesql = """
create table if not exists inventory (
id int auto_increment primary key,
item_name varchar(255),
stock_quantity int,
unit_price decimal(10, 2),
supplier varchar(100)
)
""";
try (statement stmt = conn.createstatement()) {
stmt.execute(createtablesql);
}
// 插入测试数据
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, "供应商a");
pstmt.addbatch();
pstmt.setstring(1, "台式机");
pstmt.setint(2, 30);
pstmt.setbigdecimal(3, new java.math.bigdecimal("3999.99"));
pstmt.setstring(4, "供应商b");
pstmt.addbatch();
pstmt.setstring(1, "显示器");
pstmt.setint(2, 100);
pstmt.setbigdecimal(3, new java.math.bigdecimal("1299.99"));
pstmt.setstring(4, "供应商a");
pstmt.addbatch();
pstmt.setstring(1, "键盘");
pstmt.setint(2, 200);
pstmt.setbigdecimal(3, new java.math.bigdecimal("199.99"));
pstmt.setstring(4, "供应商c");
pstmt.addbatch();
pstmt.setstring(1, "鼠标");
pstmt.setint(2, 150);
pstmt.setbigdecimal(3, new java.math.bigdecimal("99.99"));
pstmt.setstring(4, "供应商b");
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,
sum(case when supplier = '供应商a' then stock_quantity * unit_price else 0 end) as supplier_a_value,
sum(case when supplier = '供应商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=== 求和函数演示 ===");
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("总库存数量: " + totalstock);
system.out.println("总库存价值: ¥" + totalinventoryvalue.setscale(2, java.math.roundingmode.half_up));
system.out.println("供应商a库存价值: ¥" + supplieravalue.setscale(2, java.math.roundingmode.half_up));
system.out.println("供应商b库存价值: ¥" + supplierbvalue.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(stock_quantity)计算所有商品的总库存数量。 - 使用
sum(stock_quantity * unit_price)计算所有商品的总库存价值。 - 使用
sum(case when supplier = '供应商a' then stock_quantity * unit_price else 0 end)计算供应商 a 的库存价值。 - 使用
sum(case when supplier = '供应商b' then stock_quantity * unit_price else 0 end)计算供应商 b 的库存价值。
- 使用
- 输出结果：展示了库存总量、总价值以及按供应商划分的价值。
3. 平均值函数avg()
avg() 用于计算数值列的平均值。
示例：
-- select avg(price) from sales; -- 计算商品的平均价格 -- select avg(quantity) from sales; -- 计算销售数量的平均值 -- select avg(price) from sales where region = 'north'; -- 计算北部区域商品的平均价格
java 代码示例：
import java.sql.*;
public class avgfunctionexample {
private static final string db_url = "jdbc:mysql://localhost:3306/test_db?usessl=false&servertimezone=utc";
private static final string db_user = "root"; // 替换为你的用户名
private static final string db_password = "password"; // 替换为你的密码
public static void demonstrateavgfunction() {
connection conn = null;
try {
conn = drivermanager.getconnection(db_url, db_user, db_password);
// 创建示例表
string createtablesql = """
create table if not exists student_scores (
id int auto_increment primary key,
student_name varchar(100),
subject varchar(50),
score decimal(5, 2)
)
""";
try (statement stmt = conn.createstatement()) {
stmt.execute(createtablesql);
}
// 插入测试数据
string insertsql = "insert into student_scores (student_name, subject, score) values (?, ?, ?)";
try (preparedstatement pstmt = conn.preparestatement(insertsql)) {
pstmt.setstring(1, "张三");
pstmt.setstring(2, "数学");
pstmt.setbigdecimal(3, new java.math.bigdecimal("85.5"));
pstmt.addbatch();
pstmt.setstring(1, "张三");
pstmt.setstring(2, "英语");
pstmt.setbigdecimal(3, new java.math.bigdecimal("92.0"));
pstmt.addbatch();
pstmt.setstring(1, "张三");
pstmt.setstring(2, "物理");
pstmt.setbigdecimal(3, new java.math.bigdecimal("78.5"));
pstmt.addbatch();
pstmt.setstring(1, "李四");
pstmt.setstring(2, "数学");
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, "数学");
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("✅ 学生成绩数据已插入。");
}
// 演示平均值函数
string querysql = """
select
subject,
avg(score) as avg_score,
count(*) as student_count,
round(avg(score), 2) as rounded_avg_score -- 四舍五入到两位小数
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=== 平均值函数演示 ===");
system.out.printf("%-10s %-15s %-15s %-15s%n", "科目", "平均分", "学生人数", "四舍五入平均分");
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("执行平均值函数演示时发生错误: " + 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 subject按科目分组。 - 使用
avg(score)计算每个科目的平均分。 - 使用
count(*)计算每个科目的学生人数。 - 使用
round(avg(score), 2)将平均分四舍五入到两位小数。
- 使用
- 输出结果：展示了各科目的平均分、学生人数和四舍五入后的平均分，并按平均分降序排列。
4. 最大值和最小值函数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);
// 创建示例表
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);
}
// 插入测试数据
string insertsql = "insert into employee_salaries (employee_name, department, salary, hire_date) values (?, ?, ?, ?)";
try (preparedstatement pstmt = conn.preparestatement(insertsql)) {
pstmt.setstring(1, "张经理");
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("✅ 员工薪资数据已插入。");
}
// 演示最大值和最小值函数
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=== 最大值和最小值函数演示 ===");
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("执行最大值最小值函数演示时发生错误: " + 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(salary)找出最高薪资。 - 使用
min(salary)找出最低薪资。 - 使用
max(hire_date)找出最新的入职日期。 - 使用
min(hire_date)找出最早的入职日期。 - 使用
avg(salary)计算平均薪资。 - 使用
(max(salary) - min(salary))计算薪资差距。
- 使用
- 输出结果：展示了员工薪资和入职日期的统计信息。
5. 聚合函数与分组group by结合使用
聚合函数通常与 group by 子句一起使用，以对数据进行分组并计算每组的聚合值。
示例：
-- select department, count(*) from employees group by department; -- 按部门统计员工数量 -- select department, avg(salary) from employees group by department; -- 按部门计算平均薪资 -- select product_category, sum(sales_amount) from sales group by product_category; -- 按产品类别统计销售额
java 代码示例：
import java.sql.*;
public class groupbyaggregationexample {
private static final string db_url = "jdbc:mysql://localhost:3306/test_db?usessl=false&servertimezone=utc";
private static final string db_user = "root"; // 替换为你的用户名
private static final string db_password = "password"; // 替换为你的密码
public static void demonstrategroupbyaggregation() {
connection conn = null;
try {
conn = drivermanager.getconnection(db_url, db_user, db_password);
// 创建示例表
string createtablesql = """
create table if not exists transactions (
id int auto_increment primary key,
customer_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);
}
// 插入测试数据
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("✅ 交易数据已插入。");
}
// 演示分组聚合
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=== 分组聚合函数演示 ===");
system.out.printf("%-10s %-15s %-15s %-15s %-15s %-15s %-20s %-20s%n",
"客户名", "交易次数", "总支出", "总退款", "净额", "平均交易额", "最近交易日期", "首次交易日期");
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("执行分组聚合函数演示时发生错误: " + 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表存储交易记录。 - 插入数据：插入九条交易记录，包含客户 id、姓名、交易类型（购买或退款）、金额、日期和类别。
- 查询与演示：
- 使用
group by customer_name按客户分组。 - 使用
count(*)计算每位客户的交易次数。 - 使用
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按总支出降序排列。
- 使用
- 输出结果：展示了每位客户的详细交易统计信息。
四、综合实战：销售数据分析
让我们结合前面学到的所有函数，进行一次综合实战——构建一个销售数据分析报告。
实战目标
为一家零售公司生成一份销售报告，包含：
- 每个产品的销售总额。
- 每个产品的平均销售价格。
- 每个区域的销售总额。
- 每个季度的销售总额。
- 每个季度的增长率。
- 各个产品的销售排名。
数据准备
-- 创建销售数据表
create table if not exists quarterly_sales (
id int auto_increment primary key,
product_name varchar(255),
quantity int,
unit_price decimal(10, 2),
sale_date date,
region varchar(50),
quarter varchar(10) -- 用于存储季度信息，例如 'q1_2023'
);
-- 插入示例数据
insert into quarterly_sales (product_name, quantity, unit_price, sale_date, region, quarter) values
('iphone 14', 2, 999.99, '2023-01-15', 'north', 'q1_2023'),
('samsung galaxy s23', 1, 899.99, '2023-01-20', 'south', 'q1_2023'),
('macbook pro', 1, 1999.99, '2023-02-10', 'north', 'q1_2023'),
('ipad air', 3, 599.99, '2023-02-25', 'east', 'q1_2023'),
('apple watch', 2, 399.99, '2023-03-10', 'west', 'q1_2023'),
('airpods pro', 1, 249.99, '2023-03-20', 'north', 'q1_2023'),
('galaxy tab s8', 2, 799.99, '2023-04-05', 'south', 'q2_2023'),
('surface laptop', 1, 1299.99, '2023-04-15', 'west', 'q2_2023'),
('thinkpad x1', 1, 1899.99, '2023-05-10', 'north', 'q2_2023'),
('pixel 7', 2, 799.99, '2023-05-20', 'east', 'q2_2023'),
('dell xps', 1, 1599.99, '2023-06-05', 'west', 'q2_2023'),
('macbook air', 3, 1199.99, '2023-06-15', 'north', 'q2_2023'),
('iphone 15', 1, 1099.99, '2023-07-10', 'south', 'q3_2023'),
('galaxy s24', 2, 999.99, '2023-07-20', 'north', 'q3_2023'),
('surface pro', 1, 1399.99, '2023-08-05', 'west', 'q3_2023'),
('thinkpad p1', 1, 2499.99, '2023-08-15', 'east', 'q3_2023'),
('ipad pro', 2, 899.99, '2023-09-05', 'north', 'q3_2023'),
('airpods 2nd gen', 3, 179.99, '2023-09-20', 'south', 'q3_2023'),
('galaxy watch', 1, 299.99, '2023-10-05', 'west', 'q4_2023'),
('mac studio', 1, 2999.99, '2023-10-10', 'north', 'q4_2023'),
('surface book', 1, 1899.99, '2023-10-20', 'east', 'q4_2023'),
('dell inspiron', 2, 899.99, '2023-10-25', 'south', 'q4_2023');java 代码示例
import java.sql.*;
import java.math.bigdecimal;
public class salesanalysisexample {
private static final string db_url = "jdbc:mysql://localhost:3306/test_db?usessl=false&servertimezone=utc";
private static final string db_user = "root"; // 替换为你的用户名
private static final string db_password = "password"; // 替换为你的密码
public static void generatesalesreport() {
connection conn = null;
try {
conn = drivermanager.getconnection(db_url, db_user, db_password);
// 1. 每个产品的销售总额和平均价格
string productsalesquery = """
select
product_name,
sum(quantity * unit_price) as total_sales,
avg(unit_price) as avg_price,
sum(quantity) as total_quantity_sold
from quarterly_sales
group by product_name
order by total_sales desc
""";
try (statement stmt = conn.createstatement();
resultset rs = stmt.executequery(productsalesquery)) {
system.out.println("\n=== 产品销售总额与平均价格 ===");
system.out.printf("%-20s %-15s %-15s %-15s%n", "产品名", "总销售额", "平均单价", "总销量");
while (rs.next()) {
string productname = rs.getstring("product_name");
bigdecimal totalsales = rs.getbigdecimal("total_sales");
bigdecimal avgprice = rs.getbigdecimal("avg_price");
long totalquantitysold = rs.getlong("total_quantity_sold");
system.out.printf("%-20s %-15s %-15s %-15d%n",
productname,
totalsales.setscale(2, java.math.roundingmode.half_up),
avgprice.setscale(2, java.math.roundingmode.half_up),
totalquantitysold);
}
}
// 2. 每个区域的销售总额
string regionsalesquery = """
select
region,
sum(quantity * unit_price) as total_sales
from quarterly_sales
group by region
order by total_sales desc
""";
try (statement stmt = conn.createstatement();
resultset rs = stmt.executequery(regionsalesquery)) {
system.out.println("\n=== 区域销售总额 ===");
system.out.printf("%-10s %-15s%n", "区域", "总销售额");
while (rs.next()) {
string region = rs.getstring("region");
bigdecimal totalsales = rs.getbigdecimal("total_sales");
system.out.printf("%-10s %-15s%n",
region,
totalsales.setscale(2, java.math.roundingmode.half_up));
}
}
// 3. 每个季度的销售总额
string quartersalesquery = """
select
quarter,
sum(quantity * unit_price) as total_sales
from quarterly_sales
group by quarter
order by quarter
""";
try (statement stmt = conn.createstatement();
resultset rs = stmt.executequery(quartersalesquery)) {
system.out.println("\n=== 季度销售总额 ===");
system.out.printf("%-12s %-15s%n", "季度", "总销售额");
while (rs.next()) {
string quarter = rs.getstring("quarter");
bigdecimal totalsales = rs.getbigdecimal("total_sales");
system.out.printf("%-12s %-15s%n",
quarter,
totalsales.setscale(2, java.math.roundingmode.half_up));
}
}
// 4. 每个季度的增长率（使用 lag 窗口函数）
// 注意：mysql 8.0+ 支持窗口函数
string quartergrowthquery = """
with quarterly_totals as (
select
quarter,
sum(quantity * unit_price) as total_sales
from quarterly_sales
group by quarter
),
quarterly_growth as (
select
quarter,
total_sales,
lag(total_sales, 1) over (order by quarter) as previous_quarter_sales
from quarterly_totals
)
select
quarter,
total_sales,
previous_quarter_sales,
case
when previous_quarter_sales is not null and previous_quarter_sales > 0 then
round(((total_sales - previous_quarter_sales) / previous_quarter_sales) * 100, 2)
else null
end as growth_percentage
from quarterly_growth
order by quarter
""";
try (statement stmt = conn.createstatement();
resultset rs = stmt.executequery(quartergrowthquery)) {
system.out.println("\n=== 季度销售增长率 ===");
system.out.printf("%-12s %-15s %-15s %-15s%n", "季度", "总销售额", "上季度销售额", "增长率(%)");
while (rs.next()) {
string quarter = rs.getstring("quarter");
bigdecimal totalsales = rs.getbigdecimal("total_sales");
bigdecimal previousquartersales = rs.getbigdecimal("previous_quarter_sales");
bigdecimal growthpercentage = rs.getbigdecimal("growth_percentage");
system.out.printf("%-12s %-15s %-15s %-15s%n",
quarter,
totalsales.setscale(2, java.math.roundingmode.half_up),
previousquartersales == null ? "n/a" : previousquartersales.setscale(2, java.math.roundingmode.half_up),
growthpercentage == null ? "n/a" : growthpercentage.tostring() + "%");
}
}
// 5. 各个产品的销售排名
string productrankingquery = """
select
product_name,
sum(quantity * unit_price) as total_sales,
row_number() over (order by sum(quantity * unit_price) desc) as sales_rank
from quarterly_sales
group by product_name
order by total_sales desc
""";
try (statement stmt = conn.createstatement();
resultset rs = stmt.executequery(productrankingquery)) {
system.out.println("\n=== 产品销售排名 ===");
system.out.printf("%-20s %-15s %-10s%n", "产品名", "总销售额", "排名");
while (rs.next()) {
string productname = rs.getstring("product_name");
bigdecimal totalsales = rs.getbigdecimal("total_sales");
long salesrank = rs.getlong("sales_rank");
system.out.printf("%-20s %-15s %-10d%n",
productname,
totalsales.setscale(2, java.math.roundingmode.half_up),
salesrank);
}
}
} catch (sqlexception e) {
system.err.println("生成销售报告时发生错误: " + e.getmessage());
e.printstacktrace();
} finally {
try {
if (conn != null && !conn.isclosed()) {
conn.close();
}
} catch (sqlexception e) {
e.printstacktrace();
}
}
}
public static void main(string[] args) {
generatesalesreport();
}
}代码解释
- 连接数据库：获取数据库连接。
- 产品销售分析：
- 使用
sum(quantity * unit_price)计算每个产品的总销售额。 - 使用
avg(unit_price)计算每个产品的平均单价。 - 使用
sum(quantity)计算每个产品的总销量。 - 使用
group by product_name按产品分组。 - 使用
order by total_sales desc按销售额降序排列。
- 使用
- 区域销售分析：
- 使用
sum(quantity * unit_price)计算每个区域的总销售额。 - 使用
group by region按区域分组。 - 使用
order by total_sales desc按销售额降序排列。
- 使用
- 季度销售分析：
- 使用
sum(quantity * unit_price)计算每个季度的总销售额。 - 使用
group by quarter按季度分组。 - 使用
order by quarter按季度升序排列。
- 使用
- 季度增长率分析：
- 使用
with子句创建一个临时结果集quarterly_totals，计算每个季度的总销售额。 - 使用
lag窗口函数获取上一季度的销售额。 - 计算增长率：
(本期销售额 - 上期销售额) / 上期销售额 * 100。 - 使用
case语句处理第一个季度没有上期数据的情况。
- 使用
- 产品销售排名：
- 使用
row_number() over (order by sum(quantity * unit_price) desc)为产品分配销售额排名。 - 使用
group by product_name按产品分组。 - 使用
order by total_sales desc按销售额降序排列。
- 使用
- 输出报告：在控制台打印出所有分析结果。
总结：函数是数据库操作的利器
通过今天的深入学习，我们掌握了 mysql 中最常用的字符串函数、日期函数和聚合函数。这些函数不仅仅是简单的工具，更是解决实际业务问题的强大武器。从简单的字符串拼接、日期计算，到复杂的统计分析，函数的应用无处不在。
- 字符串函数：帮助我们处理和转换文本数据，确保数据格式统一、整洁。
- 日期函数：使我们能够高效地处理时间相关的业务逻辑，如计算周期、格式化显示等。
- 聚合函数：是数据分析的核心，让我们能够快速洞察数据背后的规律和趋势。
在实际项目中，熟练运用这些函数，不仅能提高开发效率，还能写出更加健壮和易维护的 sql 查询语句。希望这篇博客能为你在 mysql 的道路上提供坚实的基础和实用的指导！
🌐 了解更多关于 mysql 函数的信息，请参考以下官方文档和权威资源：
- mysql 8.0 reference manual - functions and operators
- mysql 8.0 reference manual - string functions
- mysql 8.0 reference manual - date and time functions
- mysql 8.0 reference manual - aggregate (group by) functions
- w3schools sql functions
- geeksforgeeks - mysql string functions
- geeksforgeeks - mysql date functions
- geeksforgeeks - mysql aggregate functions
mermaid 图表：字符串函数关系图
mermaid 图表：日期函数关系图
mermaid 图表：聚合函数关系图
mermaid 图表：函数综合应用示意图
总结
到此这篇关于mysql常用函数之字符串、日期、聚合函数的文章就介绍到这了,更多相关mysql字符串、日期、聚合函数内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论