首页课程创意工坊价格

7 / 15

分组与聚合

数据操作

在前面的学习中，我们学习了SQL的基本查询和操作语法。但是在实际工作中，我们经常需要对数据进行各种处理和转换。这一部分我们将深入探讨如何在SQL中处理字符串、数值和时间数据。

与标准的SQL命令不同，数据处理主要依赖于各种内置函数来实现。虽然SQL标准定义了一些函数，但各个数据库厂商往往有自己的实现方式和扩展功能。

数据操作

不同数据库系统（MySQL、Oracle、SQL Server等）的函数可能有所差异，在实际项目中要根据使用的数据库选择合适的函数。

我们将从最常用的字符串数据开始学习，然后逐步深入到数值和时间数据的处理技巧。

字符串数据处理

字符串是数据库中最常见的数据类型之一。无论是存储用户姓名、地址信息，还是产品描述，我们都需要熟练掌握字符串的处理技巧。

字符串数据类型

在数据库中，我们主要使用三种字符串数据类型：

类型	说明	适用场景
CHAR	存储固定长度的字符串。不足时自动用空格补齐到指定长度。例如`CHAR(10)`存储"北京"时，实际为"北京 "（后补8个空格）。	字段长度固定，如身份证号、邮编等
VARCHAR	存储可变长度的字符串，只占用实际数据所需空间。	长度不固定的文本，如姓名、地址
TEXT	用于存储大量文本数据，可存储非常长的字符串。	文章内容、产品描述等大文本字段

让我们看一个实际的例子：

|
-- 创建一个包含不同字符串类型的表
CREATE TABLE 用户信息表 (
    用户编号 INT PRIMARY KEY,
    用户名 CHAR(10),        -- 固定长度用户名
    真实姓名 VARCHAR(50),    -- 可变长度姓名
    个人简介 TEXT            -- 大文本个人简介
);

字符串数据的生成

在SQL中，插入字符串数据时，最常用也最基础的做法是将字符串内容用一对单引号（'）括起来。这样数据库就能识别出哪些内容是字符串类型的数据。例如，如果你要插入名字为“小明”的用户，只需要写成 '小明'。无论是中文、英文还是其他字符，只要放在一对单引号之间，数据库都会把它当作一个完整的字符串来处理。这种方式适用于所有SQL语句中涉及字符串的地方，包括插入（INSERT）、查询（SELECT）、更新（UPDATE）等操作。

|
-- 向用户信息表插入数据
INSERT INTO 用户信息表 (用户编号, 用户名, 真实姓名, 个人简介) 
VALUES (1, '小明', '张小明', '热爱编程的大学生，主修计算机科学专业');

当字符串长度超过列定义的最大长度时，数据库会根据配置决定是截断字符串还是报错。现代数据库通常采用严格模式，会在数据过长时抛出错误：

|
-- 这个操作会失败，因为真实姓名超过了50个字符的限制
INSERT INTO 用户信息表 (真实姓名) 
VALUES ('这是一个非常非常非常非常非常非常非常长的姓名，肯定会超过五十个字符的限制');

处理字符串中的单引号

当字符串本身包含单引号时，我们需要进行转义处理。最常用的方法是用两个单引号代替一个单引号：

|
-- 正确处理包含单引号的字符串
INSERT INTO 用户信息表 (个人简介) 
VALUES ('我很喜欢''Hello World''这个经典例子');
 
-- 或者使用反斜杠转义（MySQL支持）
INSERT INTO 用户信息表 (个人简介) 
VALUES ('我很喜欢\'Hello World\'这个经典例子');

使用特殊字符

有时候，我们在存储或处理字符串时，可能需要包含一些特殊字符，比如重音符（é、ü）、表情符号、或者无法直接键入的ASCII控制字符等。这些字符在SQL语句中不方便直接输入，或者在不同的编码环境下容易出错。为了解决这个问题，数据库通常提供了专门的函数来生成这些特殊字符。例如，可以通过 CHAR() 函数根据字符的ASCII码或Unicode码动态生成对应的字符。这样不仅可以插入常见的英文字母，还能灵活地插入各种特殊符号、不可见字符，甚至是多字节字符。配合字符串拼接函数（如 CONCAT()），可以将这些特殊字符灵活地组合到需要的位置，实现更复杂的字符串处理需求。

|
-- 使用CHAR函数生成特殊字符（ASCII码）
SELECT CHAR(65, 66, 67) AS 字母;  -- 结果：'ABC'
 
-- 结合CONCAT函数使用特殊字符
SELECT CONCAT('你好', CHAR(65), '世界') AS 问候语;  -- 结果：'你好A世界'

字符串操作函数

熟练运用字符串处理函数是数据库数据分析与清洗的基础能力。以下通过实际示例介绍常用的字符串操作函数及其典型应用场景。

获取字符串长度

在数据库中，我们经常需要知道某个字符串有多少个字符，比如判断用户输入是否超出限制、统计文本长度等。SQL 提供了专门的函数来获取字符串的长度。最常用的是 LENGTH() 函数，它会返回字符串所占的字节数（对于英文字符和数字，每个字符占1个字节；对于中文等多字节字符，每个字符可能占2~3个字节，具体取决于数据库的编码方式）。如果你想获取字符的个数（而不是字节数），可以使用 CHAR_LENGTH() 或 LENGTHB()（不同数据库有不同的函数名）。

例如，LENGTH('abc') 返回 3，而 LENGTH('小明') 在UTF-8编码下会返回 6，因为每个汉字占3个字节。如果你只关心字符数量，可以用 CHAR_LENGTH('小明')，结果是 2。

|
-- 创建测试数据
INSERT INTO 用户信息表 VALUES 
(2, '小红', '李小红', '这是一段测试文本');
 
-- 获取各列的字符串长度
SELECT 
    真实姓名,
    LENGTH(真实姓名) AS 姓名长度,
    LENGTH(个人简介) AS 简介长度
FROM 用户信息表 
WHERE 用户编号 = 2;

查找子字符串位置

有时候我们需要判断某个子字符串（比如关键词、特定字符）在一段文本中的具体位置。SQL 提供了 POSITION() 函数（有些数据库也支持 INSTR()），可以用来查找某个子字符串在目标字符串中首次出现的位置。

POSITION(substring IN string) 的用法是：查找 substring 在 string 中第一次出现的位置，返回值是一个整数，表示子字符串的起始位置（注意：SQL 中位置从 1 开始计数，而不是 0）。如果没有找到，返回 0。

如果 个人简介 字段的内容是“这是一段测试文本”，那么 POSITION('测试' IN 个人简介) 的结果是 5，因为“测试”在第 5 个字符处开始。这个函数常用于数据清洗、文本分析、查找敏感词等场景。例如，可以用它判断用户输入中是否包含某些非法词汇，或者提取特定内容前后的文本。

|
-- 查找"测试"在个人简介中的位置
SELECT 
    个人简介,
    POSITION('测试' IN 个人简介) AS 测试位置
FROM 用户信息表 
WHERE 用户编号 = 2;

与大多数编程语言不同的是，在SQL中，字符串位置从1开始计数，不是从0开始。如果找不到子字符串，POSITION函数返回0。

字符串连接

在SQL中，字符串拼接（连接）是非常常见的需求，比如将多个字段合并成一句完整的话、生成动态标签、格式化输出等。最常用的字符串连接函数是 CONCAT。它可以接受两个或多个参数，把它们首尾相连，生成一个新的字符串。每个参数可以是字段、常量、表达式等，CONCAT 会自动将它们拼接在一起。如果有任何一个参数为 NULL，那么结果也是 NULL（在MySQL中如此，部分数据库可能会自动将 NULL 视为空字符串）。

例如，假设我们有用户的姓名和简介两个字段，可以用 CONCAT 把它们合成一句完整的介绍。你也可以拼接常量字符串，实现灵活的格式化输出。下面是具体用法：

|
-- 生成完整的用户介绍
SELECT 
    CONCAT('用户姓名：', 真实姓名, '，个人简介：', 个人简介) AS 完整介绍
FROM 用户信息表;

提取子字符串

SUBSTRING 函数用于从一个字符串中提取指定位置开始、指定长度的一段子字符串。它的基本语法是 SUBSTRING(字符串, 起始位置, 长度)，其中“起始位置”表示从第几个字符开始（SQL中从1开始计数），“长度”表示要提取多少个字符。如果只写起始位置而不写长度，则会从起始位置一直提取到字符串末尾。这个函数常用于拆分姓名、截取固定格式的数据、提取关键信息等场景。例如，可以用它提取姓名的姓氏（第1个字符）和名字（从第2个字符开始）。

|
-- 提取姓名的姓氏（假设姓氏是第一个字符）
SELECT 
    真实姓名,
    -- 提取真实姓名的姓氏（第1个字符）
    SUBSTRING(真实姓名, 1, 1) AS 姓氏,
    -- 提取真实姓名的名字（从第2个字符开始）
    SUBSTRING(真实姓名, 2) AS 名字
FROM 用户信息表;

字符串替换

REPLACE 函数用于将字符串中的某一部分内容替换为新的内容。它的基本语法是 REPLACE(原字符串, 被替换内容, 新内容)。当你希望把某个字段或文本中的指定子串全部替换成其他内容时，可以使用这个函数。例如，如果你想把“个人简介”字段中的所有“测试”字样替换为“示例”，就可以这样写。需要注意的是，REPLACE 会替换所有匹配的子串，而不仅仅是第一个出现的。

|
-- 将个人简介中的"测试"替换为"示例"
SELECT 
    个人简介,
    REPLACE(个人简介, '测试', '示例') AS 修改后简介
FROM 用户信息表;

字符串比较

除了使用等号（=）进行精确匹配外，SQL 还提供了 LIKE 操作符用于灵活的字符串模式匹配。LIKE 允许你通过通配符来查找符合特定模式的字符串，非常适合模糊搜索。例如：

% 表示任意数量（包括0个）的任意字符。
_ 表示任意单个字符。

通过组合这些通配符，你可以实现“包含”、“以某字符开头”或“以某字符结尾”等多种模糊查询需求：

|
-- 查找姓名中包含"小"字的用户
SELECT * FROM 用户信息表 
WHERE 真实姓名 LIKE '%小%';
 
-- 查找以"张"开头的用户
SELECT * FROM 用户信息表 
WHERE 真实姓名 LIKE '张%';

让我们通过一个综合例子来展示这些函数的实际应用：

|
-- 创建一个产品信息表来演示字符串处理
CREATE TABLE 产品信息表 (
    产品ID INT PRIMARY KEY,
    产品名称 VARCHAR(100),
    产品描述 TEXT,
    价格 DECIMAL(10,2)
);
 
-- 插入测试数据
INSERT INTO 产品信息表 VALUES 
(1, 'iPhone 15 Pro', '最新款苹果手机，配备A17芯片', 8999.00

|
+---------------------------+-----------------------------------+--------------+
| 产品标题                  | 简短描述                          | 名称长度     |
+---------------------------+-----------------------------------+--------------+
| iPhone 15 Pro - ¥8999.00  | 最新款苹果手机，配备...           |           13 |
| AirPods Pro - ¥1999.00    | 主动降噪无线耳机...               |           11 |
+---------------------------+-----------------------------------+--------------+

数值数据处理

数值型数据在SQL中具有高度的可操作性。我们不仅可以直接存储和检索数值，还能利用SQL提供的标准算术运算符（+、-、*、/）及丰富的数学函数对其进行复杂的计算与分析。这为数据统计、财务分析等场景提供了强有力的支持。

基本算术运算

在SQL中，数值型字段不仅可以直接存储和检索，还能像数学表达式一样进行各种算术运算，包括加法（+）、减法（-）、乘法（*）、除法（/）等。例如，我们可以在SELECT语句中直接对数字进行计算，或者对表中的字段进行运算，得到新的结果列。这些算术操作可以帮助我们快速完成如总价计算、折扣处理、数据统计等常见业务需求。下面是一些常见的算术运算示例：

|
-- 基本的算术运算示例
SELECT 
    (25 * 8) AS 乘法结果,
    (100 - 25) AS 减法结果,
    (200 / 8) AS 除法结果,
    (17 + 8) AS 加法结果;

|
+--------------+--------------+--------------+--------------+
| 乘法结果     | 减法结果     | 除法结果     | 加法结果     |
+--------------+--------------+--------------+--------------+
|          200 |           75 |      25.0000 |           25 |
+--------------+--------------+--------------+--------------+

在处理产品价格时，我们经常需要进行计算：

|
-- 计算产品折扣价格
SELECT 
    产品名称,
    价格 AS 原价,
    ROUND(价格 * 0.8, 2) AS 八折价格,
    ROUND(价格 * 0.9, 2) AS 九折价格
FROM 产品信息表;

数学函数

SQL支持多种数学函数，能够满足复杂的数据计算与分析需求，广泛应用于金融、统计等专业场景。

取模运算

MOD函数（取模函数）用于计算两个数相除后的余数。其基本语法为 MOD(a, b)，表示用a除以b后所得的余数。例如，MOD(7, 3) 的结果是1，因为7除以3等于2余1。这个函数在实际业务中非常常见，比如判断某个编号是否为偶数（MOD(编号, 2) = 0），分组、分页、循环分配任务等场景都离不开取模运算。通过MOD函数，我们可以灵活地对数据进行分组、筛选和分类处理。

|
-- 判断产品ID是否为偶数
SELECT 
    产品ID,
    产品名称,
    MOD(产品ID, 2) AS 余数,
    CASE WHEN MOD(产品ID, 2) = 0 THEN '偶数ID' ELSE '奇数ID' END AS ID类型
FROM 产品信息表;

幂运算

POW函数可以计算幂运算，在计算复利、增长率等场景中很有用：

|
-- 计算投资复利（假设年利率5%，投资3年）
SELECT 
    10000 AS 本金,
    POW(1.05, 3) AS 利率因子,
    ROUND(10000 * POW(1.05, 3), 2) AS 三年后金额;

数值精度控制

在金融数据处理或高精度计算等专业场景中，数值精度的控制至关重要。

四舍五入

ROUND函数是最常用的精度控制函数。它的作用是对数值进行“四舍五入”处理，可以指定保留的小数位数。例如，ROUND(价格, 2) 表示将价格四舍五入保留两位小数。如果不指定第二个参数，则默认四舍五入到整数位。该函数常用于财务报表、价格结算等需要精确控制小数位数的场景，能够有效避免因小数误差带来的计算问题。

|
-- 不同精度的四舍五入
SELECT 
    价格,
    ROUND(价格) AS 整数价格,
    ROUND(价格, 1) AS 一位小数,
    ROUND(价格 * 1.15, 2) AS 含税价格
FROM 产品信息表;

向上和向下取整

CEIL和FLOOR是SQL中常用的取整函数，用于处理需要整数结果的场景。

CEIL（Ceiling）函数：用于向上取整，即返回大于或等于给定数值的最小整数。例如，CEIL(2.3) 的结果是3，CEIL(-2.3) 的结果是-2。无论小数部分是多少，只要不是整数，都会进一位。常用于计算最少需要多少个单位（如最少需要多少个箱子、工人等）。
FLOOR函数：用于向下取整，即返回小于或等于给定数值的最大整数。例如，FLOOR(2.7) 的结果是2，FLOOR(-2.7) 的结果是-3。无论小数部分是多少，都会舍去小数部分，结果向下靠近负无穷。常用于统计完整的单位数（如完整的千元数、完整的天数等）。

这两个函数在实际业务中非常实用，比如计算分批次数、分页、金额拆分等场景，可以帮助我们精确控制数据的分组和分配方式。

|
-- 计算需要的包装箱数量（向上取整）
SELECT 
    产品名称,
    价格,
    CEIL(价格 / 1000) AS 需要千元大钞数量,
    FLOOR(价格 / 1000) AS 完整千元数
FROM 产品信息表;

截断小数

TRUNCATE函数可以直接截断小数位，不进行四舍五入：

|
-- 比较截断和四舍五入的差别
SELECT 
    价格,
    TRUNCATE(价格, 1) AS 截断结果,
    ROUND(价格, 1) AS 四舍五入结果
FROM 产品信息表;

符号和绝对值处理

在金融、会计等专业领域的数据处理中，经常需要判断数值的正负或获取其绝对值，此时可以借助符号函数（如ABS、SIGN）实现对数据符号和绝对值的精确控制：

|
-- 创建一个账户余额表来演示
CREATE TABLE 账户余额表 (
    账户ID INT PRIMARY KEY,
    账户名称 VARCHAR(50),
    余额 DECIMAL(10,2)
);
 
INSERT INTO 账户余额表 VALUES 
(1, '张三的储蓄账户', 5000.00),
(2, '李四的信用卡', -

|
 
+-----------------------+----------+--------------+--------+--------------+
| 账户名称              | 余额     | 绝对金额     | 符号   | 账户状态     |
+-----------------------+----------+--------------+--------+--------------+
| 张三的储蓄账户        |  5000.00 |      5000.00 |      1 | 正余额       |
| 李四的信用卡          | -1200.50 |      1200.50 |     -1 | 负余额       |
| 王五的投资账户        |     0.00

时间数据处理

时间数据在数据库中属于较为复杂的数据类型，涉及日期、时间、时区等多方面内容。正确、高效地处理时间数据是开发高质量应用系统的基础能力之一，也是实际业务中经常遇到的重要问题。

时间数据类型

在数据库中，主要有三种时间相关的数据类型：

数据类型	格式	说明
DATE	YYYY-MM-DD	只存储日期，如 2024-03-15
TIME	HH:MM:SS	只存储时间，如 14:30:00
DATETIME	YYYY-MM-DD HH:MM:SS	存储完整的日期和时间

时区问题

在处理时间数据时，时区是一个重要考虑因素。全球被划分为24个时区，不同地区的时间可能相差几小时。

现代应用通常使用UTC（协调世界时）作为标准时间存储格式，然后根据用户所在地区转换为本地时间显示。

|
-- 查看当前数据库的时区设置
SELECT @@global.time_zone AS 全局时区, @@session.time_zone AS 会话时区;
 
-- 获取当前时间（不同格式）
SELECT 
    CURRENT_DATE() AS 当前日期,
    CURRENT_TIME() AS 当前时间,
    CURRENT_TIMESTAMP() AS 当前时间戳;

|
+--------------+--------------+---------------------+
| 当前日期     | 当前时间     | 当前时间戳          |
+--------------+--------------+---------------------+
| 2025-09-25   | 11:05:03     | 2025-09-25 11:05:03 |
+--------------+--------------+---------------------+

时间数据的生成

直接使用字符串

|
-- 创建员工考勤表
CREATE TABLE 员工考勤表 (
    考勤ID INT PRIMARY KEY AUTO_INCREMENT,
    员工姓名 VARCHAR(50),
    上班时间 DATETIME,
    下班时间 DATETIME,
    考勤日期 DATE
);
 
-- 使用字符串插入时间数据
INSERT INTO 员工考勤表 (员工姓名, 上班时间, 下班时间, 考勤日期) VALUES 
('张小明', '2024-03-15 09:00:00', '2024-03-15 18:00:00', '2024-03-15'),

使用CAST函数转换

在实际开发中，我们经常会遇到各种格式的时间字符串，比如只有日期、只有时间，或者日期和时间的组合。这些字符串如果直接存入数据库，可能无法参与时间计算或排序。此时，可以使用CAST函数将这些“非标准”时间字符串转换为数据库支持的标准时间数据类型（如DATE、TIME、DATETIME）：

|
-- 转换不同格式的时间字符串
SELECT 
    CAST('2024-03-15' AS DATE) AS 标准日期,
    CAST('14:30:00' AS TIME) AS 标准时间,
    CAST('2024-03-15 14:30:00' AS DATETIME) AS 标准时间戳;

时间计算

时间计算在时间数据管理与分析中占据核心地位，是实现数据统计、趋势分析和业务逻辑推断的基础。

时间加减

DATE_ADD 和 DATE_SUB 是 MySQL 中常用的时间函数，用于对日期或时间字段进行加法或减法操作。

DATE_ADD(日期, INTERVAL 数量单位)：在指定日期的基础上增加一定的时间间隔（如天、月、年、小时等），返回新的日期或时间。例如，DATE_ADD('2024-03-15', INTERVAL 7 DAY) 表示在 2024-03-15 的基础上加 7 天，结果为 2024-03-22。
DATE_SUB(日期, INTERVAL 数量单位)：在指定日期的基础上减少一定的时间间隔，返回新的日期或时间。例如，DATE_SUB('2024-03-15', INTERVAL 1 MONTH) 表示在 2024-03-15 的基础上减去 1 个月，结果为 2024-02-15。

这两个函数常用于考勤、统计、报表等场景，比如计算某个日期之后/之前的时间点，或者推算未来/过去的日期。

|
-- 计算员工工作时间和未来日期
SELECT 
    员工姓名,
    上班时间,
    下班时间,
    TIMEDIFF(下班时间, 上班时间) AS 工作时长,
    DATE_ADD(考勤日期, INTERVAL 7 DAY) AS 一周后日期,
    DATE_ADD(上班时间, INTERVAL 30 MINUTE) AS 迟到截止时间
FROM 员工考勤表;

计算时间差

DATEDIFF函数是MySQL中用于计算两个日期之间相差天数的常用函数。它的基本语法是：DATEDIFF(日期1, 日期2)，返回值为“日期1 - 日期2”之间的天数（即日期1比日期2多多少天）。如果日期1晚于日期2，结果为正数；如果日期1早于日期2，结果为负数。

这个函数非常适合用于以下场景：

统计项目的总工期（如项目的开始日期和结束日期之间的天数）
计算员工的出勤天数或请假天数
分析某个事件距离现在还有多少天，或者已经过去了多少天
判断某个任务是否超期，以及超期了多少天

使用时，只需将需要比较的两个日期作为参数传入，函数会自动计算它们之间的天数差。例如，DATEDIFF('2024-03-31', '2024-01-01') 的结果是90，表示这两个日期相差90天。下面通过实际的SQL例子来演示它的用法：

|
-- 计算项目进度
CREATE TABLE 项目进度表 (
    项目ID INT PRIMARY KEY,
    项目名称 VARCHAR(100),
    开始日期 DATE,
    计划结束日期 DATE,
    实际结束日期 DATE
);
 
INSERT INTO 项目进度表 VALUES 
(1, '电商网站开发', '2024-01-01', '2024-03-31', NULL),
(

|
+--------------------+--------------+--------------------+--------------------+-----------------+-------------------------+
| 项目名称           | 开始日期     | 计划结束日期       | 计划工期天数       | 已进行天数      | 项目状态                |
+--------------------+--------------+--------------------+--------------------+-----------------+-------------------------+
| 电商网站开发       | 2024-01-01   | 2024-03-31         |                 90 |             633 | 已超期543天             |
| 移动APP开发        | 2024-02-01   | 2024-04-30         |                 89 |

时间格式化和提取

提取时间组件

EXTRACT 函数可以从日期或时间类型的字段中提取出你关心的具体时间部分，比如年份（YEAR）、月份（MONTH）、日期（DAY）、小时（HOUR）、分钟（MINUTE）等。这样做的好处是可以方便地对时间数据进行分组、筛选或统计分析。例如，如果你想统计每个月的销售额，就可以用 EXTRACT(MONTH FROM 订单日期) 把订单日期中的月份单独提取出来。 EXTRACT 的基本语法是：EXTRACT(时间单位 FROM 日期字段)，其中“时间单位”可以是 YEAR、MONTH、DAY、HOUR、MINUTE、SECOND 等。这样你就可以灵活地获取时间字段中的任意组成部分，满足各种业务分析需求。

|
-- 分析员工考勤模式
SELECT 
    员工姓名,
    考勤日期,
    EXTRACT(YEAR FROM 考勤日期) AS 年份,
    EXTRACT(MONTH FROM 考勤日期) AS 月份,
    EXTRACT(DAY FROM 考勤日期) AS 日期,
    DAYNAME(考勤日期) AS 星期几,
    EXTRACT(HOUR FROM 上班时间) AS 上班小时
FROM 员工考勤表;

月末日期处理

LAST_DAY 函数的作用是返回指定日期所在月份的最后一天，非常适合用于财务结算、月度报表等需要确定每月截止日期的场景。例如，如果你想知道本月或上个月的月末日期，可以用 LAST_DAY 函数直接获取，无需手动计算天数。其基本用法是：LAST_DAY(日期字段或表达式)，返回值为该日期所在月份的最后一天（类型为 DATE）。这样可以方便地进行月度数据统计、对账、生成月末报表等操作。

|
-- 计算月末报表日期
SELECT 
    CURRENT_DATE() AS 今天,
    LAST_DAY(CURRENT_DATE()) AS 本月最后一天,
    LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)) AS 上月最后一天;

让我们通过一个完整的例子来展示时间数据处理的综合应用：

|
-- 创建订单表来演示时间数据的实际应用
CREATE TABLE 订单表 (
    订单ID INT PRIMARY KEY AUTO_INCREMENT,
    客户姓名 VARCHAR(50),
    下单时间 DATETIME,
    发货时间 DATETIME,
    期望收货时间 DATETIME,
    实际收货时间 DATETIME,
    订单金额 DECIMAL(10,2)
);
 
-- 插入测试数据
INSERT INTO 订单表 (客户姓名, 下单时间, 发货时间, 期望收货时间, 实际收货时间, 订单金额) VALUES

|
+----------+--------------+--------------+---------------------+----------------------------+-----------------+
| 订单ID   | 客户姓名     | 订单金额     | 下单时间            | 发货状态                   | 收货状态        |
+----------+--------------+--------------+---------------------+----------------------------+-----------------+
|        1 | 王小明       |       299.00 | 2024-03-10 10:30:00 | 已发货（5小时后）          | 按时收货        |
|        2 | 李小华       |      1588.00 | 2024-03-11 14:20:00

数据类型转换

在实际的数据库管理与数据处理过程中，类型转换是常见且重要的操作。例如，需要将字符串类型的数据转换为数值型，或将数值型转换为日期型等。SQL语言为此提供了丰富且高效的类型转换函数，以满足复杂的数据处理需求。

CAST函数

CAST函数是SQL标准中定义的类型转换函数，几乎所有数据库都支持。它的语法简单直观：

|
-- CAST函数的基本语法演示
SELECT 
    CAST('12345' AS SIGNED INTEGER) AS 字符串转整数,
    CAST(123.456 AS DECIMAL(5,2)) AS 浮点数转定点数,
    CAST('2024-03-15' AS DATE) AS 字符串转日期,
    CAST(CURRENT_TIMESTAMP() AS DATE) AS

|
+--------------------+-----------------------+--------------------+--------------------+
| 字符串转整数       | 浮点数转定点数        | 字符串转日期       | 时间戳转日期       |
+--------------------+-----------------------+--------------------+--------------------+
|              12345 |                123.46 | 2024-03-15         | 2025-09-25         |
+--------------------+-----------------------+--------------------+--------------------+

字符串到数字的转换

在实际业务数据处理中，字符串与数值之间的类型转换是最为常见且基础的数据清洗操作之一，尤其是在数据导入、统计分析等场景中尤为重要，我们看一下下面的例子：

|
-- 创建一个包含文本数字的表
CREATE TABLE 销售数据表 (
    记录ID INT PRIMARY KEY AUTO_INCREMENT,
    产品名称 VARCHAR(50),
    销售数量_文本 VARCHAR(10),  -- 存储为文本的数量
    单价_文本 VARCHAR(10)       -- 存储为文本的价格
);
 
INSERT INTO 销售数据表 (产品名称, 销售数量_文本, 单价_文本) VALUES 
('笔记本电脑', '15', '5999.99'),
(

|
+-----------------+--------------+---------+--------------+
| 产品名称        | 销售数量     | 单价    | 总销售额     |
+-----------------+--------------+---------+--------------+
| 笔记本电脑      |           15 | 5999.99 |     89999.85 |
| 鼠标            |          120 |   89.50 |     10740.00 |
| 键盘            |           45 |  299.00 |     13455.00 |
+-----------------+--------------+---------+--------------+

当字符串包含非数字字符时，CAST函数会尽可能转换前面的数字部分，其余部分会被忽略。建议在转换前先验证数据的有效性。

处理转换失败的情况

在实际的数据处理过程中，并非所有字符串都能够被成功地转换为数值类型：

|
-- 演示部分转换和转换失败的情况
SELECT 
    '123abc' AS 原始字符串,
    CAST('123abc' AS UNSIGNED INTEGER) AS 转换结果,  -- 结果：123
    '999999999999999999999' AS 超大数字,
    CAST('999999999999999999999' AS UNSIGNED INTEGER) AS 转换结果2;  -- 可能溢出

数据清理和标准化

在实际的数据处理与集成项目中，常常需要应对来自多种数据源的异构数据格式，这对数据清洗与标准化提出了更高的要求：

|
-- 创建原始导入数据表
CREATE TABLE 客户导入数据 (
    客户ID VARCHAR(20),
    客户姓名 VARCHAR(50),
    注册日期 VARCHAR(20),
    消费金额 VARCHAR(15),
    是否VIP VARCHAR(5)
);
 
-- 插入混合格式的数据
INSERT INTO 客户导入数据 VALUES 
('C001',

|
+--------------+--------------+--------------------+--------------------+-----------+
| 客户编号     | 客户姓名     | 标准注册日期       | 标准消费金额       | VIP标志   |
+--------------+--------------+--------------------+--------------------+-----------+
|            1 | 张三         | 2024-03-15         |            1299.50 |         1 |
|            2 | 李四         | 2024-03-16         |            2500.00 |         0 |
|            3 |

报表和分析

在报表生成和数据分析过程中，数据类型转换是确保数据准确性和可用性的关键步骤。通过对数值、日期、字符串等不同类型的数据进行规范化和转换，可以实现数据的统一展示、便于后续的统计分析和业务决策。例如，将原始数据中的金额、日期等字段转换为标准格式，有助于生成规范的报表和进行精确的分析计算。

|
-- 创建月度业绩表
CREATE TABLE 月度业绩表 (
    年份 INT,
    月份 INT,
    销售额 DECIMAL(12,2),
    目标额 DECIMAL(12,2)
);
 
INSERT INTO 月度业绩表 VALUES 
(2024, 1, 125000.00, 120000.

|
+--------------+-----------------+-----------------+-----------+---------------+
| 报告期间     | 实际销售额      | 目标销售额      | 完成率    | 达标状态      |
+--------------+-----------------+-----------------+-----------+---------------+
| 2024年1月    | ¥ 125,000.00    | ¥ 120,000.00    | 4.2%      | ✅ 达标        |
| 2024年2月    | ¥ 98,000.00     | ¥ 110,000.00    | -10.9%    | ❌ 未达标

高级转换技巧

条件转换

在SQL中，CASE语句可用于实现基于条件的复杂数据转换逻辑。通过结合CASE语句，可以根据不同的数据特征或业务规则，对字段值进行灵活的格式化、标准化或分类处理，从而满足多样化的数据清洗和报表需求。例如，针对不同格式的输入数据，CASE语句能够动态选择合适的转换方式，实现高效且可维护的数据处理流程：

|
-- 处理多种格式的电话号码
CREATE TABLE 联系人表 (
    姓名 VARCHAR(50),
    电话号码 VARCHAR(20)
);
 
INSERT INTO 联系人表 VALUES 
('张小明', '138-1234-5678'),
('李小红', '13812345678'),
('王小华', '+86 138 1234 5678'),
('赵小强', '(138) 1234-5678');

|
+-----------+-------------------+---------------+
| 姓名      | 原始格式          | 标准格式      |
+-----------+-------------------+---------------+
| 张小明    | 138-1234-5678     | 13812345678   |
| 李小红    | 13812345678       | 13812345678   |
| 王小华    | +86 138 1234 5678 | 8613812345678 |
| 赵小强    | (138) 1234-5678

性能考虑

频繁的类型转换可能影响查询性能，特别是在大数据量的情况下。在设计数据库表结构时，应该选择合适的数据类型来避免频繁的类型转换。如果经常需要进行数值计算，就不要将数字存储为字符串类型。

|
-- 避免在WHERE子句中进行类型转换
-- 不好的做法：
-- SELECT * FROM 销售数据表 WHERE CAST(销售数量_文本 AS INTEGER) > 50;
 
-- 更好的做法：先转换数据类型，或者在应用层面处理
SELECT * FROM 销售数据表 WHERE 销售数量_文本 > '50';

通过掌握这些数据生成、转换和操作的技巧，我们就能够在SQL中灵活处理各种数据格式，为复杂的业务需求提供强有力的数据支持。无论是字符串处理、数值计算，还是时间操作，这些函数都是日常数据库开发中不可或缺的工具。

小练习

1. 在SQL中，如何正确表示字符串数据？

2. 关于CONCAT函数的功能，以下哪个说法是正确的？

3. 关于ROUND函数的功能，以下哪个说法是正确的？

4. 关于DATE_ADD和DATE_SUB函数的区别，以下哪个说法是正确的？

5. 关于CAST函数的功能，以下哪个说法是正确的？

6. 字符串处理综合应用

假设你有一个用户表，包含以下字段和数据：

用户表：

用户ID	用户名	邮箱	手机号
1	zhangsan	zhangsan@example.com	138-1234-5678
2	lisi	lisi@test.com	13987654321
3	wangwu	wangwu@demo.org	+86 150 1234 5678

请编写SQL查询，完成以下任务：

将用户名转换为首字母大写的形式（如 zhangsan → Zhangsan）
提取邮箱的域名部分（@符号后面的部分）
统一手机号格式，去除所有非数字字符，只保留数字

答案：

|
SELECT 
    用户ID,
    用户名,
    CONCAT(
        UPPER(SUBSTRING(用户名, 1, 1)),
        LOWER(SUBSTRING(用户名, 2))
    ) AS 格式化用户名,
    邮箱,
    SUBSTRING(邮箱, LOCATE('@', 邮箱) + 1) AS 邮箱域名,
    手机号,
    REPLACE(REPLACE

7. 数值计算和时间处理

假设你有一个订单表，包含以下字段和数据：

订单表：

订单ID	订单金额	折扣率	下单时间	发货时间
1	299.99	0.1	2024-03-10 10:30:00	2024-03-10 16:00:00
2	1588.50	0.15	2024-03-11 14:20:00	2024-03-12 09:15:00
3	799.00	0.05	2024-03-12 09:45:00	NULL

请编写SQL查询，计算以下内容：

每个订单的折扣后金额（原价 × (1 - 折扣率)），保留两位小数
每个订单从下单到发货的小时数（如果未发货则显示"未发货"）
每个订单的折扣后金额向上取整到百位（如 269.99 → 300）

答案：

|
SELECT 
    订单ID,
    订单金额,
    折扣率,
    ROUND(订单金额 * (1 - 折扣率), 2) AS 折扣后金额,
    CASE 
        WHEN 发货时间 IS NULL THEN '未发货'
        ELSE CAST(TIMESTAMPDIFF(HOUR, 下单时间, 发货时间) AS CHAR)
    END AS 发货时长_小时,
    CEIL(ROUND(订单金额 *

8. 数据类型转换和数据清理

假设你从外部系统导入了一批数据，格式不太规范。现在有一个产品导入表：

产品导入表：

产品ID	产品名称	价格_文本	库存_文本	上架日期_文本	是否热销_文本
P001	iPhone 15	5,999.99	150	2024/03/15	true
P002	MacBook	8999	85	2024-03-16	1
P003	AirPods	1,299.50	200	03/17/2024	false

请编写SQL查询，完成以下数据清理和转换任务：

将价格_文本转换为数值类型（需要去除千位分隔符逗号）
将库存_文本转换为整数类型
将上架日期_文本统一转换为标准日期格式（YYYY-MM-DD）
将是否热销_文本转换为布尔值（true/1 → 1，false/0 → 0）

答案：

|
SELECT 
    产品ID,
    产品名称,
    -- 去除逗号后转换为数值
    CAST(REPLACE(价格_文本, ',', '') AS DECIMAL(10,2)) AS 价格,
    -- 直接转换为整数
    CAST(库存_文本 AS UNSIGNED INTEGER) AS 库存,
    -- 统一日期格式
    CASE 
        WHEN 上架日期_文本 LIKE '%/%' THEN

用户ID	格式化用户名	邮箱域名	标准化手机号
1	Zhangsan	example.com	13812345678
2	Lisi	test.com	13987654321
3	Wangwu	demo.org	15012345678

订单ID	折扣后金额	发货时长_小时	向上取整到百位
1	269.99	5	300
2	1350.23	18	1400
3	759.05	未发货	800

产品ID	价格	库存	上架日期	是否热销
P001	5999.99	150	2024-03-15	1
P002	8999.00	85	2024-03-16	1
P003	1299.50	200	2024-03-17	0

数据操作 | 自在学