16661 字
83 分钟
02：mysql之常用函数
2026-06-03
数据库
运维
/
数据库

[TOC]

02：mysql之常用函数#

今日工作任务路线图

flowchart LR
    subgraph BottomRow[常用函数]
        direction LR
        A[字符串函数] --> B[日期函数] --> C[聚集函数] --> D[数学计算] --> E[if函数] --> F[case函数]
    end

一、工作场景#

在电商公司的运营部门，为了评估商品销售情况，需要对数据库中的销售数据进行分析。数据库使用 MySQL 存储订单信息。运营人员需要统计不同商品类别的总销售额、平均销售单价和最高销售单价。 他们使用 MySQL 的聚合函数，SUM() 计算每个商品类别的总销售额，AVG() 求平均销售单价，MAX() 获取最高销售单价。为了筛选出近期一个月内的有效订单，使用 DATE_SUB() 函数确定时间范围。通过 GROUP BY 对商品类别分组，将不同类别的数据区分开。最后，利用这些统计结果，运营人员制定针对性的营销策略，以提高销售额和利润。

二、为什么学mysql的函数#

学习 MySQL 函数是提升数据库操作能力和数据处理效率的关键。在日常数据处理中，我们需要对数据进行各种计算、转换和筛选，而 MySQL 函数可以帮助我们轻松完成这些任务。

例如，在统计销售数据时，我们可以使用聚合函数计算总销售额、平均价格等；在处理日期数据时，日期和时间函数能让我们方便地进行日期计算和格式化。掌握 MySQL 函数还能优化查询性能，通过合理运用函数，减少不必要的数据传输和处理，提高数据库的响应速度。此外，函数的使用还能使 SQL 语句更加简洁和易读，提升我们的工作效率和代码质量。

三、字符串函数#

当前任务环节

flowchart LR
    subgraph BottomRow[常用函数]
        direction LR
        A[字符串函数] --> B[日期函数] --> C[聚集函数] --> D[数学计算] --> E[if函数] --> F[case函数]
    end


    classDef highlight stroke:#f00,stroke-width:2px;
    class A highlight;

3.1、返回字符串长度#

LENGTH(str) 函数#

项目说明
函数名称LENGTH【冷斯】 (在某些数据库中也称为 LEN)
功能说明返回字符串的长度。注意：在大多数数据库中，此函数返回的是字符串的字节数，而非字符数，这在处理多字节字符集（如UTF-8）时至关重要。
语法LENGTH【冷斯】(string)
参数string: 需要计算长度的字符串。可以是字符串字面量（如'ABC'）、字段名或变量。
返回值一个整数值，表示字符串的字节数。如果参数为 NULL，则返回 NULL。
示例1. SELECT【涩莱克特】 LENGTH【冷斯】('Hello'); -> 5 (5个英文字符，在UTF-8中每个占1字节)
2. SELECT【涩莱克特】 LENGTH【冷斯】('你好'); -> 6 (2个中文字符，在UTF-8中每个占3字节，共6字节)
3. SELECT【涩莱克特】 LENGTH(column_name) from【弗乱】 table_name; -> 返回表中该字段各值的字节长度
注意要点1. 字节 vs. 字符:
- 这是最关键的区别。如果需要获取字符数（通常更符合直觉），应使用 CHAR_LENGTH【查_冷斯】() 或 CHARACTER_LENGTH【冷斯】() 函数。
2. 数据库差异:
- MySQL, MariaDB: LENGTH【冷斯】() 返回字节数，CHAR_LENGTH【查_冷斯】() 返回字符数。
- SQL Server: 使用 LEN() 返回字符数（但会去掉尾部空格），使用 DATALENGTH【得塔-冷斯】() 返回字节数。
- Oracle: 使用 LENGTHB() 返回字节数，LENGTH【冷斯】() 返回字符数。
- PostgreSQL: LENGTH() 返回字符数，使用 OCTET_LENGTH() 返回字节数。
- SQLite: LENGTH() 返回字符数。
3. 空字符串: LENGTH('') 返回 0。
4. NULL 值: LENGTH(NULL) 返回 NULL。
使用建议在不确定数据库字符集或需要处理多语言文本时，强烈建议使用 CHAR_LENGTH【查_冷斯】】() 来获取字符数，以避免因字符编码不同导致的意外结果。LENGTH【冷斯】() 更适用于计算存储空间等需要字节精度的场景。

LENGTH vs. CHAR_LENGTH#

函数测量单位UTF-8 示例 LENGTH【冷斯】('你好')适用场景
LENGTH【冷斯】(str)字节6 (2字符 * 3字节/字符)计算存储占用、网络传输大小
CHAR_LENGTH【查_冷斯】(str)字符2计算字符串的实际字符数量、显示长度、业务逻辑

LENGTH(str)#

--LENGTH【冷斯】(str)             返字符串长度，以字节为单位
mysql> select【涩莱克特】 name from【弗乱】 moershi.user where【威尔】 name = "root" ;
+------+
| name |
+------+
| root |
+------+
1 row in set (0.00 sec)
mysql> select【涩莱克特】 name,length【冷斯】(name) as 字节个数 from moershi.user where【威尔】 name = "root" ;
+------+--------------+
| name | 字节个数      |
+------+--------------+
| root |            4  |
+------+--------------+
1 row in set (0.00 sec)


--一个汉字3个字节
mysql> select【涩莱克特】 name,length【冷斯】(name) from【弗乱】 moershi.employees【ing普洛伊s】 where【威尔】 employee【ing普洛伊】_id = 3 ;
+-----------+--------------+
| name      | length【冷斯】(name) |
+-----------+--------------+
| 李玉英    |            9 |
+-----------+--------------+

CHAR_LENGTH(str)#

-- CHAR_LENGTH【查_冷斯】(str)        返回字符串长度，以字符为单位
mysql> select【涩莱克特】 name from【弗乱】 moershi.employees【ing普洛伊s】 where【威尔】 employee【ing普洛伊】_id = 3 ;
+-----------+
| name      |
+-----------+
| 李玉英    |
+-----------+
1 row in set (0.00 sec)


mysql> select【涩莱克特】 name,char_length【查_冷斯】(name) from【弗乱】 moershi.employees【ing普洛伊s】 where【威尔】 employee【ing普洛伊】_id = 3 ;
+-----------+-----------------------------+
| name      | char_length【查_冷斯】(name) |
+-----------+-----------------------------+
| 李玉英     |                 3           |
+-----------+-----------------------------+
1 row in set (0.00 sec)

3.2、将字符串转换为大写#

函数概览#

UPPER【阿普厄】(str) 和 UCASE【U kèi 斯】(str) 函数的功能完全相同，都是将字符串 str 中的所有字母字符转换为大写形式。它们互为同义词，区别仅在于函数名称，以适应不同用户的习惯。

项目说明
函数名称UPPER【阿普厄】(str), UCASE【U kèi 斯】(str)
功能描述将字符串 str 中的所有字母字符转换为大写形式。非字母字符（如数字、空格、特殊符号）保持不变。
通用语法UPPER【阿普厄】(string【斯jǘn】)
UCASE【U kèi 斯】(string【斯jǘn】)
参数string【斯jǘn】: 需要转换的字符串。可以是字符串字面量（如 'abc'）、字段名或变量。
返回值一个新的字符串，其中所有小写字母已被转换为大写字母。如果参数为 NULL，则返回 NULL`。

基本用法

mysql> SELECT【涩莱克特】 UPPER【阿普厄】('Hello World!') AS UpperText;
-- 结果: 'HELLO WORLD!'


mysql> SELECT【涩莱克特】 UCASE【U kèi 斯】('sql is fun') AS UpperText;
-- 结果: 'SQL IS FUN'

总结与对比#

特性UPPER【阿普厄】(str)UCASE【U kèi 斯】(str)
功能将字符串转换为大写将字符串转换为大写
标准性SQL 标准非标准，但广泛实现
通用性极高，所有SQL数据库都支持很高，但并非所有数据库都支持（如Oracle不支持UCASE【U kèi 斯】）
推荐度⭐️⭐️⭐️⭐️⭐️ (首选)⭐️⭐️⭐️⭐️
-- UPPER【阿普厄】(str)和UCASE【U kèi 斯】(str)  将字符串中的字母全部转换成大写
mysql> select【涩莱克特】 name from【弗乱】  moershi.user where【威尔】 uid <= 3 ;
+--------+
| name   |
+--------+
| root   |
| bin    |
| daemon |
| adm    |
+--------+
4 rows in set (0.00 sec)


mysql> select【涩莱克特】 upper【阿普厄】(name) from【弗乱】  moershi.user where【威尔】 uid <= 3 ;
+-------------+
| upper【阿普厄】(name) |
+-------------+
| ROOT        |
| BIN         |
| DAEMON      |
| ADM         |
+-------------+
4 rows in set (0.00 sec)


mysql> select【涩莱克特】 ucase【U kèi 斯】(name) from【弗乱】  moershi.user where【威尔】 uid <= 3 ;
+-------------+
| ucase【U kèi 斯】(name) |
+-------------+
| ROOT        |
| BIN         |
| DAEMON      |
| ADM         |
+-------------+
4 rows in set (0.00 sec)

3.3、将字符串转换成小写#

好的，这是关于 SQL 中 LOWER【喽厄】() 和 LCASE【L kèi 斯】() 函数的详细说明。

函数概览#

LOWER【喽厄】(str) 和 LCASE【L kèi 斯】(str) 函数的功能完全相同，都是将字符串 str 中的所有字母字符转换为小写形式。它们互为同义词，区别仅在于函数名称。

项目说明
函数名称LOWER【喽厄】(str), LCASE【L kèi 斯】(str)
功能描述将字符串 str 中的所有字母字符转换为小写形式。非字母字符（如数字、空格、特殊符号）保持不变。
通用语法LOWER【喽厄】(string【斯jǘn】)
LCASE【L kèi 斯】(string【斯jǘn】)
参数string【斯jǘn】: 需要转换的字符串。可以是字符串字面量（如 'ABC'）、字段名或变量。
返回值一个新的字符串，其中所有大写字母已被转换为小写字母。如果参数为 NULL，则返回 NULL。

基本用法与示例#

1. 直接转换文本字符串#
mysql> SELECT LOWER【喽厄】('HELLO World! 123') AS LowerText;
+------------------+
| LowerText        |
+------------------+
| hello world! 123 |
+------------------+
1 row in set (0.00 sec)


-- 结果: 'hello world! 123'


mysql> SELECT LCASE【L kèi 斯】('SQL Tutorial') AS LowerText;
+--------------+
| LowerText    |
+--------------+
| sql tutorial |
+--------------+
1 row in set (0.00 sec)


-- 结果: 'sql tutorial'
3. 在 WHERE 子句中实现不区分大小写的比较或搜索#

这是该函数最常用和实用的场景之一。

-- 查找类别为 'electronics' 的所有产品，不区分大小写
mysql> SELECT * FROM products WHERE LOWER【喽厄】(category) = 'electronics';


-- 在模糊查询中实现不区分大小写
mysql> SELECT * FROM users WHERE LOWER【喽厄】(email) LIKE '%@gmail.com';
4. 与 UPPER/UCASE 结合使用进行数据标准化#
-- 将用户输入的用户名统一格式化为小写后存储，确保唯一性
mysql> UPDATE【阿普dei 特】 users SET username = LOWER【喽厄】('SomeUserName') WHERE id = 100;

总结与对比#

特性LOWER【喽厄】(str)LCASE【L kèi 斯】(str)
功能将字符串转换为小写将字符串转换为小写
标准性SQL 标准非标准，但广泛实现
通用性极高，所有主流SQL数据库都支持很高，但并非所有数据库都支持（如 Oracle 不支持 LCASE【L kèi 斯】）
推荐度⭐️⭐️⭐️⭐️⭐️ (首选)⭐️⭐️⭐️⭐️
核心结论与最佳实践#
  1. 功能完全相同：LOWER【喽厄】() 和 LCASE【L kèi 斯】() 的功能完全一致，选择哪一个通常取决于个人习惯。
  2. 首选 LOWER【喽厄】()：LOWER【喽厄】() 是 SQL 标准中定义的函数，因此具有最广泛的数据库支持（包括 Oracle）。为了代码的最大兼容性、可读性和可移植性，应优先使用 LOWER【喽厄】()。
  3. 主要应用场景：
    • 数据比较：在 WHERE 子句中进行不区分大小写的比较。
    • 数据搜索：实现不区分大小写的搜索 (LIKE)。
    • 数据标准化：在存储或显示前将文本统一为小写格式。
  4. 注意 NULL：如果输入参数为 NULL，输出结果也为 NULL。
-- LOWER【喽厄】(str)和LCASE【L kèi 斯】(str)    将str中的字母全部转换成小写
mysql> select【涩莱克特】 lower【喽厄】("ABCD") ;
+---------------+
| lower【喽厄】("ABCD") |
+---------------+
| abcd          |
+---------------+
1 row in set (0.00 sec)


mysql> select【涩莱克特】 lcase【L kèi 斯】("ABCD") ;
+---------------+
| lcase【L kèi 斯】("ABCD") |
+---------------+
| abcd          |
+---------------+
1 row in set (0.00 sec)
mysql>

3.4、从字符串中提取子串#

substr【杀斯妥】

函数概览#

SUBSTR【杀斯妥】() 函数用于从字符串中提取子串。它是 SQL 中最重要和常用的字符串函数之一。

项目说明
函数名称SUBSTR【杀斯妥】(string【斯jǘn】, start, length)
别名SUBSTRING【杀斯jǘn】() (功能完全相同，SUBSTR【杀斯妥】 更常见)
功能描述从原字符串中返回一个从指定位置开始、具有指定长度的子字符串。
通用语法SUBSTR【杀斯妥】(string【斯jǘn】, start [, length])
参数1. string【斯jǘn】：必需的。要提取子串的源字符串。
2. start：必需的。开始位置。
重要：起始位置通常为 1，而不是 0。
3. length：可选的。要提取的字符数。如果省略，则返回从 start 开始到字符串结尾的所有字符。
返回值提取出的子字符串。如果任何参数为 NULL，则返回 NULL。

基本用法与示例#

1. 指定起始位置和长度#

这是最常用的形式。

mysql>  SELECT SUBSTR【杀斯妥】('SQL Tutorial', 1, 3) AS ExtractString;
+---------------+
| ExtractString |
+---------------+
| SQL           |
+---------------+
1 row in set (0.02 sec)
-- 结果: 'SQL' (从第1个字符开始，取3个字符)


mysql> SELECT SUBSTR【杀斯妥】('SQL Tutorial', 5, 4) AS ExtractString;
+---------------+
| ExtractString |
+---------------+
| Tuto          |
+---------------+
1 row in set (0.00 sec)
-- 结果: 'Tuto' (从第5个字符('T')开始，取4个字符)
2. 省略长度参数（提取到末尾）#

如果省略第三个参数，函数将返回从起始位置到字符串末尾的所有字符。

mysql>  SELECT SUBSTR【杀斯妥】('SQL Tutorial', 5) AS ExtractString;
+---------------+
| ExtractString |
+---------------+
| Tutorial      |
+---------------+
1 row in set (0.00 sec)
-- 结果: 'Tutorial' (从第5个字符开始，直到结束)
3. 使用负起始位置（从末尾开始计数）#

这是一个非常实用的功能。许多数据库（如 MySQL、PostgreSQL、SQLite）支持使用负数作为 start 参数，表示从字符串末尾开始向前计数。

-- 提取最后 3 个字符
mysql> SELECT SUBSTR【杀斯妥】('SQL Tutorial', -3) AS ExtractString;
+---------------+
| ExtractString |
+---------------+
| ial           |
+---------------+
1 row in set (0.00 sec)
-- 结果: 'ial'


-- 从倒数第 6 个字符开始，提取 4 个字符
mysql> SELECT SUBSTR【杀斯妥】('SQL Tutorial', -6, 4) AS ExtractString;
+---------------+
| ExtractString |
+---------------+
| tori          |
+---------------+
1 row in set (0.00 sec)
-- 结果: 'ori' (从倒数第6位('o')开始，取4个字符。但后面只剩3个，所以只返回'ori')

注意：SQL Server 不支持负起始位置，需配合 LEN() 函数计算。

4. 在查询中处理表字段数据#
-- 提取每个人的姓氏（假设从第6个字符开始）
mysql> SELECT full_name, SUBSTR【杀斯妥】(full_name, 6) AS last_name FROM employees【ing普洛伊s】;

总结与对比：SUBSTR【杀斯妥】 vs. SUBSTRING【杀斯jǘn】#

特性SUBSTR【杀斯妥】(…)SUBSTRING【杀斯jǘn】(…)
功能从字符串中提取子串从字符串中提取子串
普及度非常普遍，是许多数据库中的主要函数名也很常见，是 SQL 标准名称
语法SUBSTR【杀斯妥】(str, start, length)SUBSTRING【杀斯jǘn】(str FROM start FOR length) (标准语法) 或 SUBSTRING(str, start, length)
数据库支持MySQL, Oracle, SQLite, PostgreSQL, MariaDBSQL Server, PostgreSQL, MySQL (也支持SUBSTR)
推荐度⭐️⭐️⭐️⭐️⭐️ (通用性极佳)⭐️⭐️⭐️⭐️ (是标准，但需注意数据库方言)

不同数据库的注意事项#

数据库主要函数负起始位置备注
MySQL, MariaDBSUBSTR【杀斯妥】() 或 SUBSTRING【杀斯jǘn】()支持两者完全同义。
OracleSUBSTR【杀斯妥】()支持不支持 SUBSTRING【杀斯jǘn】。
SQL ServerSUBSTRING【杀斯jǘn】()不支持需用 LEN(string【斯jǘn】) + 1 - n 来计算从末尾开始的位置。
PostgreSQLSUBSTRING【杀斯jǘn】() (标准) 或 SUBSTR【杀斯妥】()支持两者都可用。
SQLiteSUBSTR【杀斯妥】()支持

核心结论与最佳实践#

  1. 功能核心：SUBSTR【杀斯妥】(str, start, length) 用于精确提取字符串的特定部分。
  2. 起始索引：牢记起始位置通常是 1，这是与许多编程语言（如 Python、Java）从 0 开始索引的最大区别。
  3. 负索引：负起始位置是一个非常有用的特性，可以轻松地从字符串末尾开始操作，但要注意 SQL Server 不支持。
  4. 兼容性建议：
    • 如果你主要在 MySQL、Oracle、SQLite 中工作，可以放心使用 SUBSTR【杀斯妥】()。
    • 如果你在 SQL Server 中工作，则必须使用 SUBSTRING【杀斯jǘn】()。
    • 为了编写跨数据库兼容的 SQL，了解这些差异至关重要。在不确定时，查看数据库的官方文档是最好的方法。
  5. 常见用途：提取区号、获取文件扩展名、隐藏部分敏感信息（如 SUBSTR【杀斯妥】(phone_number, -4) 显示手机尾号）、解析固定格式的代码等。
--SUBSTR【杀斯妥】(s, start,end)     从s的start位置开始取出到end长度的子串
mysql> select【涩莱克特】 name from【弗乱】  moershi.employees【ing普洛伊s】 where【威尔】 employee【ing普洛伊】_id <= 3 ;
+-----------+
| name      |
+-----------+
| 梁伟      |
| 郭岩      |
| 李玉英    |
+-----------+
3 rows in set (0.00 sec)
--不是输出员工的姓 只输出名字（只输出第2、3个字符）
mysql> select【涩莱克特】 substr(name,2,3) from【弗乱】  moershi.employees【ing普洛伊s】 where【威尔】 employee【ing普洛伊】_id <= 3 ;
+------------------+
| substr(name,2,3) |
+------------------+
| 伟               |
| 岩               |
| 玉英             |
+------------------+
3 rows in set (0.00 sec)

3.5、在一个字符串中查找另一个子字符串#

函数概览#

INSTR() 函数用于在一个字符串中查找另一个子字符串，并返回其首次出现的位置。如果未找到，则返回 0。它是一个非常重要的字符串查找函数。

项目说明
函数名称INSTR(string【斯jǘn】, substring【杀斯jǘn】)
别名LOCATE() (在MySQL中), CHARINDEX() (在SQL Server中)
功能描述返回子字符串 substring【杀斯jǘn】 在字符串 string【斯jǘn】 中第一次出现的起始位置。
通用语法INSTR(string【斯jǘn】, substring【杀斯jǘn】)
扩展语法某些数据库（如Oracle、MySQL）支持可选参数来指定起始搜索位置和出现次数：INSTR(string【斯jǘn】, substring【杀斯jǘn】 [, start [, occurrence]])
参数1. string【斯jǘn】：必需的。被搜索的源字符串。
2. substring【杀斯jǘn】：必需的。要查找的子字符串。
3. start：可选的。开始搜索的位置，默认为 1。
4. occurrence：可选的。指定要查找第几次出现的位置，默认为 1。
返回值一个整数值，表示子串首次出现的起始位置。
位置从 1 开始计数。
如果未找到子串，则返回 0。
如果任何参数为 NULL，则返回 NULL。

基本用法与示例#

为了演示，我们使用字符串 'Hello World! Welcome to the World of SQL.' 作为示例源字符串。

1. 基本查找：查找子串首次出现的位置#
mysql> SELECT INSTR('Hello World!', 'World') AS Position;
+----------+
| Position |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)


-- 结果: 7 (因为 'W' 是源字符串的第7个字符)
-- 分解: H e l l o [空格] W o r l d !
-- 位置: 1 2 3 4 5  6    7 8 9 10 11 12
2. 查找不存在的子串#
mysql> SELECT INSTR('Hello World!', 'SQL') AS Position;
+----------+
| Position |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
-- 结果: 0 (因为 'SQL' 没有出现)
3. 在查询中处理表字段数据#
-- 找到所有产品代码中连字符 '-' 的位置
mysql> SELECT product_code, INSTR(product_code, '-') AS hyphen_position FROM products;
4. 与其他函数组合使用（常与 SUBSTR 搭配）#

一个非常强大的组合是先用 INSTR 定位，再用 SUBSTR 截取。

-- 提取产品代码中连字符之后的部分（型号）
mysql> SELECT product_code,SUBSTR(product_code, INSTR(product_code, '-') + 1) AS model_number FROM products;

结果：

product_codemodel_number
LAPTOP-001001
MOUSE-002002
KEYBOARD-003003

逻辑：先找到 - 的位置 (N)，然后从 N+1 的位置开始截取到末尾。

总结与对比：INSTR vs. 其他类似函数#

特性INSTR(string【斯jǘn】, substring【杀斯jǘn】)LOCATE (MySQL)CHARINDEX (SQL Server)POSITION (标准SQL)
语法INSTR(str, substr)LOCATE(substr, str [, start])CHARINDEX(substr, str [, start])POSITION(substr IN str)
参数顺序(字符串, 子串)(子串, 字符串)(子串, 字符串)(子串 IN 字符串)
起始位置可选第三参数 start可选第三参数 start可选第三参数 start不支持
出现次数可选第四参数 occurrence (Oracle/MySQL)不支持不支持不支持
返回值位置 (从1开始) / 0位置 (从1开始) / 0位置 (从1开始) / 0位置 (从1开始) / 0

不同数据库的注意事项与语法#

数据库主要函数语法示例备注
OracleINSTRINSTR('abc', 'b')支持扩展参数 (start, occurrence)
MySQLINSTR 或 LOCATEINSTR('abc', 'b') 或 LOCATE('b', 'abc')INSTR 支持扩展参数，LOCATE 支持 start
SQL ServerCHARINDEXCHARINDEX('b', 'abc')不支持 occurrence 参数
PostgreSQLSTRPOS 或 POSITIONSTRPOS('abc', 'b') 或 POSITION('b' IN 'abc')POSITION 是标准SQL语法
SQLiteINSTRINSTR('abc', 'b')只支持两个基本参数

核心结论与最佳实践#

  1. 功能核心：INSTR 用于定位，而不是提取。它返回的是数字位置。
  2. 黄金搭档：INSTR 常与 SUBSTR 组合使用，实现“先定位，后截取”的强大功能。
  3. 参数顺序陷阱：不同数据库的参数顺序可能不同（特别是 INSTR 和 CHARINDEX/LOCATE）。这是最大的混淆点，使用时务必注意。
  4. 兼容性建议：
    • 如果你主要在 Oracle、SQLite 中工作，使用 INSTR。
    • 如果你在 MySQL 中工作，INSTR 和 LOCATE 都可以，但注意参数顺序。
    • 如果你在 SQL Server 中工作，必须使用 CHARINDEX。
    • 为了编写符合SQL标准的代码，可以使用 POSITION(substr IN str)，但功能可能最基础。
  5. 常见用途：查找特定字符或关键字的位置、解析结构化字符串（如URL、代码）、验证字符串中是否包含某部分内容（结果>0即表示包含）。
-- INSTR(str,str1)        返回str1参数，在str参数内的位置
mysql>  select【涩莱克特】 name from【弗乱】  moershi.user where【威尔】 uid <= 3 ;
+--------+
| name   |
+--------+
| root   |
| bin    |
| daemon |
| adm    |
+--------+
4 rows in set (0.00 sec)


--查找名字里有a及出现的位置
mysql> select【涩莱克特】 name,instr(name,"a") from【弗乱】  moershi.user where【威尔】 uid <= 3 ;
+--------+-----------------+
| name   | instr(name,"a") |
+--------+-----------------+
| root   |               0 |
| bin    |               0 |
| daemon |               2 |
| adm    |               1 |
+--------+-----------------+
4 rows in set (0.00 sec)


-- 查找名字里有英字及出现的位置
mysql> select【涩莱克特】 name , instr(name,"英") from【弗乱】  moershi.employees【ing普洛伊s】;
+-----------+-------------------+
| name      | instr(name,"英")  |
+-----------+-------------------+
| 梁伟      |                 0 |
| 郭岩      |                 0 |
| 李玉英    |                 3 |
| 张健      |                 0 |
| 郑静      |                 0 |
| 牛建军    |                 0 |
| 刘斌      |                 0 |
| 汪云      |                 0 |
| 张建平    |                 0 |
| 郭娟      |                 0 |
| 郭兰英    |                 3 |
| 王英      |                 2 |

3.6、移除字符串开头和/或结尾处的指定字符#

函数概览#

TRIM【垂姆】() 函数用于移除字符串开头和/或结尾处的指定字符（默认为空格）。它是数据清洗和标准化中最重要、最常用的函数之一。

项目说明
函数名称TRIM【垂姆】(...)
功能描述从字符串的两端（开头和结尾）移除指定的前缀和后缀字符（默认为空格）。
标准语法TRIM【垂姆】([removal_spec] [remove_char] FROM string【斯jǘn】)
简化语法TRIM【垂姆】(string【斯jǘn】)
参数1. string【斯jǘn】：必需的。要处理的源字符串。
2. removal_spec：可选的。指定移除位置：
- LEADING：只移除开头的字符。
- TRAILING：只移除结尾的字符。
- BOTH：移除开头和结尾的字符（默认行为）。
3. remove_char：可选的。指定要移除的字符，默认为空格 ' '。
返回值一个新的字符串，已移除指定位置上的指定字符。如果参数为 NULL，则返回 NULL。

基本用法与示例#

基本语法​说明#
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)


> BOTH​​：移除字符串​​开头和结尾​​的字符（默认选项）。
> ​​LEADING​​：仅移除字符串​​开头​​的字符。
> ​​TRAILING​​：仅移除字符串​​结尾​​的字符。
> ​​remstr​​：要移除的字符（或字符串）。如果省略，​​默认移除空格​​。
> ​​str​​：要处理的原始字符串。
1. 基本用法：移除首尾空格（最常见用途）#

这是最常用的情况，因此几乎所有数据库都支持最简形式。

mysql> SELECT TRIM【垂姆】('   Hello World   ') AS TrimmedString;
+---------------+
| TrimmedString |
+---------------+
| Hello World   |
+---------------+
1 row in set (0.00 sec)
-- 结果: 'Hello World' (首尾空格被移除，中间空格保留)


-- 等效于标准写法的默认行为
mysql> SELECT TRIM【垂姆】(BOTH ' ' FROM '   Hello World   ') AS TrimmedString;
+---------------+
| TrimmedString |
+---------------+
| Hello World   |
+---------------+
1 row in set (0.00 sec)
-- 结果: 'Hello World'
2. 指定移除位置 (LEADING / TRAILING / BOTH)#
-- 只移除开头的空格 (LTRIM 的功能)
mysql> SELECT TRIM【垂姆】(LEADING FROM '   Hello World   ') AS LeadingTrimmed;
+----------------+
| LeadingTrimmed |
+----------------+
| Hello World    |
+----------------+
1 row in set (0.00 sec)
-- 结果: 'Hello World   '


-- 只移除结尾的空格 (RTRIM 的功能)
mysql> SELECT TRIM【垂姆】(TRAILING FROM '   Hello World   ') AS TrailingTrimmed;
+-----------------+
| TrailingTrimmed |
+-----------------+
|    Hello World  |
+-----------------+
1 row in set (0.00 sec)
-- 结果: '   Hello World'


-- 明确指定移除两端 (BOTH 是默认值，可省略)
mysql> SELECT TRIM【垂姆】(BOTH FROM '   Hello World   ') AS BothTrimmed;
+-------------+
| BothTrimmed |
+-------------+
| Hello World |
+-------------+
1 row in set (0.00 sec)
-- 结果: 'Hello World'
3. 移除指定字符（不仅仅是空格）#

这是一个非常强大的功能，可以移除任何指定的字符。

-- 移除首尾的 'x'
mysql> SELECT TRIM【垂姆】('x' FROM 'xxHello Worldxx') AS TrimmedString;
+---------------+
| TrimmedString |
+---------------+
| Hello World   |
+---------------+
1 row in set (0.00 sec)
-- 结果: 'Hello World'


-- 移除首尾的连字符 '-'
mysql> SELECT TRIM【垂姆】('-' FROM '--SQL-') AS TrimmedString;
+---------------+
| TrimmedString |
+---------------+
| SQL           |
+---------------+
1 row in set (0.00 sec)
-- 结果: 'SQL'


-- 组合使用：只移除开头的 '0'
mysql> SELECT TRIM【垂姆】(LEADING '0' FROM '0001234500') AS TrimmedString;
+---------------+
| TrimmedString |
+---------------+
| 1234500       |
+---------------+
1 row in set (0.00 sec)
-- 结果: '1234500' (开头的0被移除，结尾的0保留)


-- 组合使用：只移除结尾的逗号 ','
mysql> SELECT TRIM【垂姆】(TRAILING ',' FROM 'value1,value2,value3,,,') AS TrimmedString;
+----------------------+
| TrimmedString        |
+----------------------+
| value1,value2,value3 |
+----------------------+
1 row in set (0.00 sec)
-- 结果: 'value1,value2,value3'
4. 在查询中处理表字段数据（数据清洗）#

假设有一个 users 表，由于数据导入问题，name 字段可能包含多余空格：

idname
1’ John Doe ‘
2’Alice ’
-- 清理并显示姓名
mysql> SELECT id, name, TRIM【垂姆】(name) AS cleaned_name FROM users;

结果：

idnamecleaned_name
1’ John Doe ''John Doe’
2’Alice ''Alice’
-- 在 WHERE 子句中使用，确保比较的准确性
mysql> SELECT * FROM products WHERE TRIM【垂姆】(product_code) = 'ABC123';
-- 即使数据库中存的是 ' ABC123 ' 也能匹配

总结与对比：TRIM【垂姆】 vs. LTRIM/RTRIM#

特性TRIM【垂姆】()LTRIM() / RTRIM()
功能可灵活移除首、尾或两端的指定字符LTRIM() 只移除开头的空格/字符
RTRIM() 只移除结尾的空格/字符
灵活性高。通过参数配置功能低。功能单一，每个函数只做一件事
符合标准是 ANSI SQL 标准不是标准，但被广泛实现
移除指定字符支持。可移除任意指定字符在多数数据库中仅支持空格，或语法有所不同
推荐度⭐️⭐️⭐️⭐️⭐️ (首选)⭐️⭐️⭐️ (用于简单场景或兼容旧代码)
TRIM【垂姆】() 与 LTRIM()/RTRIM() 的等效操作#
-- 移除开头空格：
mysql> TRIM【垂姆】(LEADING FROM '  abc  ') -- 结果: 'abc  '
mysql> LTRIM('  abc  ')             -- 结果: 'abc  '


-- 移除结尾空格：
mysql> TRIM【垂姆】(TRAILING FROM '  abc  ') -- 结果: '  abc'
mysql> RTRIM('  abc  ')              -- 结果: '  abc'


-- 移除两端空格：
mysql> TRIM【垂姆】('  abc  ')               -- 结果: 'abc'
-- 等效组合：
mysql> LTRIM(RTRIM('  abc  '))       -- 结果: 'abc'
不同数据库的注意事项#
数据库TRIM【垂姆】() 支持程度备注
MySQL, MariaDB完全支持支持标准语法和简化语法
PostgreSQL完全支持支持标准语法和简化语法
SQL Server支持 (2017及以上版本)2017版本之前需使用 LTRIM(RTRIM())
Oracle完全支持支持标准语法和简化语法
SQLite支持支持简化语法 TRIM【垂姆】(string【斯jǘn】)，但从3.38版本开始支持标准语法

核心结论与最佳实践#

  1. 功能核心：TRIM【垂姆】() 用于清理字符串边界的无关字符，最常用于移除空格。
  2. 现代首选：对于新的项目，应优先使用功能更强大、更符合标准的 TRIM【垂姆】() 函数，而不是 LTRIM() 和 RTRIM()。
  3. 数据清洗：在数据导入、数据验证和查询比较前，使用 TRIM【垂姆】() 清理用户输入或外部数据至关重要，可以避免因首尾空格导致的匹配失败。
  4. 灵活运用：记住它可以移除任何指定字符（如 0, -, ,），而不仅仅是空格，这在处理固定格式的代码、数字字符串时非常有用。
  5. 兼容性：虽然是最新标准，但已在主流数据库的最新版本中得到良好支持。对于旧版SQL Server等环境，需回退到 LTRIM(RTRIM()) 组合。
--TRIM【垂姆】(s)            返回字符串s删除了两边空格之后的字符串
mysql> select【涩莱克特】 trim【垂姆】("  ABC  ");
+-----------------+
| trim【垂姆】("  ABC  ") |
+-----------------+
| ABC             |
+-----------------+
1 row in set (0.00 sec)
mysql>

四、日期函数#

当前任务环节

flowchart LR
    subgraph BottomRow[常用函数]
        direction LR
        A[字符串函数] --> B[日期函数] --> C[聚集函数] --> D[数学计算] --> E[if函数] --> F[case函数]
    end


    classDef highlight stroke:#f00,stroke-width:2px;
    class B highlight;

获取系统或指定日期与时间

函数说明
curtime【科泰姆】()获取时间
curdate【科dei 特】()获取日期
now【闹】()获取日期和时间
year【耶尔】()获取年
month【mèn斯】()获取月
day【dèi】()获取日
week【wèi kē】()获取一年中的第几周
date【dei 特】()获取日期
weekday【wèi kē dèi】()获取一周中的周几
time【泰姆】()获取时间
hour【ào é】()获取小时
minute【密 ní 特】 ()获取分钟
second【塞耿特】()获取秒
quarter【扩特】()获取一年中第几季度
monthname【mòn斯-内姆】()获取月份名称
dayname【dei 内姆】()获取日期对应的星期名
dayofyear【dèi诶 符 耶尔】()获取一年中第几天
dayofmonth【dèi诶 符 mèn斯】()获取一月中第几天

命令操作如下所示：

mysql> select【涩莱克特】 curtime【科泰姆】(); //获取系统时间
+-----------+
| curtime【科泰姆】() |
+-----------+
| 17:42:20  |
+-----------+
1 row in set (0.00 sec)


mysql> select【涩莱克特】 curdate【科dei 特】();//获取系统日期
+------------+
| curdate【科dei 特】()  |
+------------+
| 2023-05-24 |
+------------+
1 row in set (0.00 sec)


mysql> select【涩莱克特】 now【闹】() ;//获取系统日期+时间
+---------------------+
| now【闹】()               |
+---------------------+
| 2023-05-24 17:42:29 |
+---------------------+
1 row in set (0.00 sec)


mysql> select【涩莱克特】 year【耶尔】(now【闹】()) ; //获取系统当前年
+-------------+
| year【耶尔】(now【闹】()) |
+-------------+
|        2023 |
+-------------+
1 row in set (0.00 sec)


mysql> select【涩莱克特】 month【mèn斯】(now【闹】()) ; //获取系统当前月
+--------------+
| month【mèn斯】(now【闹】()) |
+--------------+
|            5 |
+--------------+
1 row in set (0.00 sec)


mysql> select【涩莱克特】 day【dèi】(now【闹】()) ; //获取系统当前日
+------------+
| day【dèi】(now【闹】()) |
+------------+
|         24 |
+------------+
1 row in set (0.00 sec)


mysql> select【涩莱克特】 hour【ào é】(now【闹】()) ; //获取系统当前小时
+-------------+
| hour【ào é】(now【闹】()) |
+-------------+
|          17 |
+-------------+
1 row in set (0.00 sec)


mysql> select【涩莱克特】 minute【密 ní 特】 (now【闹】()) ; //获取系统当分钟
+---------------+
| minute【密 ní 特】 (now【闹】()) |
+---------------+
|            46 |
+---------------+
1 row in set (0.00 sec)


mysql> select【涩莱克特】 second【塞耿特】(now【闹】()) ; //获取系统当前秒
+---------------+
| second【塞耿特】(now【闹】()) |
+---------------+
|            34 |
+---------------+
1 row in set (0.00 sec)


mysql> select【涩莱克特】 time【泰姆】(now【闹】()) ;//获取当前系统时间
+-------------+
| time【泰姆】(now【闹】()) |
+-------------+
| 17:47:36    |
+-------------+
1 row in set (0.00 sec)


mysql> select【涩莱克特】 date【dei 特】(now【闹】()) ; //获取当前系统日期
+-------------+
| date【dei 特】(now【闹】()) |
+-------------+
| 2023-05-24  |
+-------------+
1 row in set (0.00 sec)


mysql> select【涩莱克特】 curdate【科dei 特】();//获取当前系统日志
+------------+
| curdate【科dei 特】()  |
+------------+
| 2023-05-24 |
+------------+
1 row in set (0.00 sec)


mysql> select【涩莱克特】 dayofmonth【dèi诶 符 mèn斯】(curdate【科dei 特】());//获取一个月的第几天
+-----------------------+
| dayofmonth【dèi诶 符 mèn斯】(curdate【科dei 特】()) |
+-----------------------+
|                    24 |
+-----------------------+
1 row in set (0.00 sec)


mysql> select【涩莱克特】 dayofyear【dèi诶 符 耶尔】(curdate【科dei 特】());//获取一年中的第几天
+----------------------+
| dayofyear【dèi诶 符 耶尔】(curdate【科dei 特】()) |
+----------------------+
|                  144 |
+----------------------+
1 row in set (0.00 sec)


mysql>
mysql> select【涩莱克特】 monthname【mòn斯-内姆】(curdate【科dei 特】());//获取月份名
+----------------------+
| monthname【mòn斯-内姆】(curdate【科dei 特】()) |
+----------------------+
| May                  |
+----------------------+
1 row in set (0.00 sec)


mysql> select【涩莱克特】 dayname【dei 内姆】(curdate【科dei 特】());//获取星期名
+--------------------+
| dayname【dei 内姆】(curdate【科dei 特】()) |
+--------------------+
| Wednesday          |
+--------------------+
1 row in set (0.00 sec)


mysql> select【涩莱克特】 quarter【扩特】(curdate【科dei 特】());//获取一年中的第几季度
+--------------------+
| quarter【扩特】(curdate【科dei 特】()) |
+--------------------+
|                  2 |
+--------------------+
1 row in set (0.00 sec)


mysql> select【涩莱克特】 week【wèi kē】(now【闹】());//一年中的第几周
+-------------+
| week【wèi kē】(now【闹】()) |
+-------------+
|          21 |
+-------------+
1 row in set (0.00 sec)


mysql> select【涩莱克特】 weekday【wèi kē dèi】(now【闹】())；//一周中的周几
+----------------+
| weekday【wèi kē dèi】(now【闹】()) |
+----------------+
|              2 |
+----------------+
1 row in set (0.00 sec)

五、聚集函数#

当前任务环节

flowchart LR
    subgraph BottomRow[常用函数]
        direction LR
        A[字符串函数] --> B[日期函数] --> C[聚集函数] --> D[数学计算] --> E[if函数] --> F[case函数]
    end


    classDef highlight stroke:#f00,stroke-width:2px;
    class C highlight;

对数值类型表头下的数据做统计

命令操作如下所示：

输出3号员工2018每个月的基本工资

mysql> select【涩莱克特】 basic【贝斯克】 from【弗乱】 moershi.salary【撒拉瑞】 where【威尔】 employee【ing普洛伊】_id=3 and year【耶尔】(date【dei 特】)=2018;
+-------+
| basic【贝斯克】 |
+-------+
|  9261 |
|  9261 |
|  9261 |
|  9261 |
|  9261 |
|  9261 |
|  9261 |
|  9261 |
|  9261 |
|  9261 |
|  9261 |
|  9724 |
+-------+
12 rows in set (0.00 sec)

sum(表头名) 求和

mysql> select【涩莱克特】 sum(basic【贝斯克】) from【弗乱】 moershi.salary【撒拉瑞】 where【威尔】 employee【ing普洛伊】_id=3 and year【耶尔】(date【dei 特】)=2018;
+------------+
| sum(basic【贝斯克】) |
+------------+
|     111595 |
+------------+
1 row in set (0.00 sec)

avg(表头名) 计算平均值

mysql> select【涩莱克特】 avg(basic【贝斯克】) from【弗乱】 moershi.salary【撒拉瑞】 where【威尔】 employee【ing普洛伊】_id=3 and year【耶尔】(date【dei 特】)=2018;
+------------+
| avg(basic【贝斯克】) |
+------------+
|  9299.5833 |
+------------+
1 row in set (0.00 sec)

min(表头名) 获取最小值

mysql> select【涩莱克特】 min(basic【贝斯克】) from【弗乱】 moershi.salary【撒拉瑞】 where【威尔】 employee【ing普洛伊】_id=3 and year【耶尔】(date【dei 特】)=2018;
+------------+
| min(basic【贝斯克】) |
+------------+
|       9261 |
+------------+
1 row in set (0.00 sec)

max(表头名) 获取最大值

mysql> select【涩莱克特】 max(basic【贝斯克】) from【弗乱】 moershi.salary【撒拉瑞】 where【威尔】 employee【ing普洛伊】_id=3 and year【耶尔】(date【dei 特】)=2018;
+------------+
| max(basic【贝斯克】) |
+------------+
|       9724 |
+------------+
1 row in set (0.00 sec)

count【康特】(表头名) 统计表头值个数

//输出3号员工2018年奖金小于3000的奖金
mysql> select【涩莱克特】 bonus【波诺斯】 from【弗乱】 moershi.salary【撒拉瑞】 where【威尔】 employee【ing普洛伊】_id=3 and year【耶尔】(date【dei 特】)=2018 and bonus【波诺斯】<3000;
+-------+
| bonus【波诺斯】 |
+-------+
|  1000 |
|  1000 |
|  1000 |
+-------+
3 rows in set (0.00 sec)
//统计3号员工2018年奖金小于3000的次数
mysql> select【涩莱克特】 count【康特】(bonus【波诺斯】) from【弗乱】 moershi.salary【撒拉瑞】 where【威尔】 employee【ing普洛伊】_id=3 and year【耶尔】(date【dei 特】)=2018 and bonus【波诺斯】<3000;
+--------------+
| count【康特】(bonus【波诺斯】) |
+--------------+
|            3 |
+--------------+
1 row in set (0.01 sec)

六、数学计算#

当前任务环节

flowchart LR
    subgraph BottomRow[常用函数]
        direction LR
        A[字符串函数] --> B[日期函数] --> C[聚集函数] --> D[数学计算] --> E[if函数] --> F[case函数]
    end


    classDef highlight stroke:#f00,stroke-width:2px;
    class D highlight;

对行中的列做计算

符号用途例子
+加法uid + gid
-减法uid - gid
*乘法uid * gid
/除法uid / gid
%取余数（求模）uid % gid
()提高优先级(uid + gid) / 2

命令操作如下所示：

输出8号员工2019年1月10 工资总和

mysql> select【涩莱克特】 employee【ing普洛伊】_id,date【dei 特】,basic【贝斯克】 +  bonus【波诺斯】  as 总工资 from【弗乱】 moershi.salary【撒拉瑞】 where【威尔】employee【ing普洛伊】_id = 8 and date【dei 特】=20190110;
+-------------------------+------------+-----------------+
| employee【ing普洛伊】_id | date【dei 特】       |     总工资       |
+-------------------------+------------+-----------------+
|           8             | 2019-01-10 |          24093  |
+-------------------------+------------+-----------------+

输出8号员工的名字和年龄

mysql> select【涩莱克特】 name, 当前年份 - year【耶尔】(birth_date【dei 特】) as 年龄 from【弗乱】 moershi.employees【ing普洛伊s】  where【威尔】employee【ing普洛伊】_id = 8 ;


-- mysql> select name, year【耶尔】(now【闹】()) - year【耶尔】(birth_date【dei 特】) as 年龄 from moershi.employees where employee_id=8;
-- year【耶尔】(now【闹】())  获取当前年份
+--------+--------+
| name   | 年龄   |
+--------+--------+
| 汪云   |     29 |
+--------+--------+

查看8号员工2019年1月10 基本工资翻3倍的 值

mysql> select【涩莱克特】 employee【ing普洛伊】_id , basic【贝斯克】 , basic【贝斯克】 * 3  as 工资翻三倍  from【弗乱】 moershi.salary【撒拉瑞】 where【威尔】 employee【ing普洛伊】_id=8  and date【dei 特】=20190110;


-- mysql> select employee_id,basic,basic * 3 as 工资翻三倍 from moershi.salary where employee_id=8 and date=20190110;
+-------------+-------+-----------------+
| employee【ing普洛伊】_id | basic【贝斯克】 | 工资翻三倍      |
+-------------+-------+-----------------+
|           8 | 23093 |           69279 |
+-------------+-------+-----------------+
1 row in set (0.00 sec)

查看8号员工2019年1月10的平均工资

mysql> select【涩莱克特】 employee【ing普洛伊】_id ,  (basic【贝斯克】+bonus【波诺斯】)/2 as 平均工资  from【弗乱】 moershi.salary【撒拉瑞】 where【威尔】  employee【ing普洛伊】_id=8  and date【dei 特】=20190110 ;


--mysql> select employee_id,(basic + bonus)/2 as 平均工资 from moershi.salary where employee_id=8 and date=20190110;
+-------------+--------------+
| employee【ing普洛伊】_id | 平均工资     |
+-------------+--------------+
|           8 |   12046.5000 |
+-------------+--------------+
1 row in set (0.01 sec)

输出员工编号1-10之间偶数员工编号及对应的员工名

mysql> select【涩莱克特】 employee【ing普洛伊】_id , name  from【弗乱】  moershi.employees【ing普洛伊s】 where【威尔】 employee【ing普洛伊】_id  between【B豚ing】 1 and 10  and  employee【ing普洛伊】_id % 2  =  0   ;


-- mysql> select employee_id,name from moershi.employees where employee_id between 1 and 10 and employee_id % 2 = 0;
+-------------+-----------+
| employee【ing普洛伊】_id | name      |
+-------------+-----------+
|           2 | 郭岩      |
|           4 | 张健      |
|           6 | 牛建军    |
|           8 | 汪云      |
|          10 | 郭娟      |
+-------------+-----------+
5 rows in set (0.00 sec)

七、if函数#

当前任务环节

flowchart LR
    subgraph BottomRow[常用函数]
        direction LR
        A[字符串函数] --> B[日期函数] --> C[聚集函数] --> D[数学计算] --> E[if函数] --> F[case函数]
    end


    classDef highlight stroke:#f00,stroke-width:2px;
    class E highlight;

if(条件,v1,v2) 如果条件是TRUE则返回v1，否则返回v2

ifnull(v1,v2) 如果v1不为NULL，则返回v1，否则返回v2

特性IF 函数IFNULL 函数
主要用途通用的条件判断，根据条件真假返回不同值专门处理 NULL 值
参数数量三个：IF(条件, 值_if_true, 值_if_false)两个：IFNULL(表达式, 替换值)
核心逻辑条件为真（非0且非NULL）返回第二个参数，否则返回第三个参数表达式不为NULL时返回其自身，否则返回替换值
适用场景两种可能性的选择，如分类、状态转换字段可能为NULL时提供默认值，防止计算错误

💡 认识 IF 函数#

IF 函数就像一个简单的“如果…那么…否则…”判断器。它的基本语法是：

IF(condition, value_if_true, value_if_false)
  • condition：要判断的条件表达式。
  • value_if_true：当条件为真（TRUE，即非0且非NULL）时返回的值。
  • value_if_false：当条件为假（FALSE 或 NULL）时返回的值。

使用示例：

-- 根据年龄判断是否成年
SELECT name, IF(age >= 18, '成年', '未成年') AS age_group FROM users;


-- 在商品查询中显示折扣信息
SELECT product_name, price, IF(price > 100, '高价商品', '普通商品') AS price_category FROM products;


-- 注意：直接判断浮点数时，MySQL会尝试将其转为整数，这可能不是你想要的结果
SELECT IF(0.1, 1, 0); -- 返回 0，因为0.1被转为整数0后判断为假
SELECT IF(0.1 <> 0, 1, 0); -- 返回 1，这是正确的比较方式

演示if（） 语句的执行过程

mysql> select【涩莱克特】  if(1 = 2 , "a","b");
+---------------------+
| if(1 = 2 , "a","b") |
+---------------------+
| b                   |
+---------------------+
1 row in set (0.00 sec)
mysql> select【涩莱克特】  if( 1 = 1 , "a","b");
+---------------------+
| if(1 = 1 , "a","b") |
+---------------------+
| a                   |
+---------------------+
1 row in set (0.00 sec)
mysql>

🔄 掌握 IFNULL 函数#

IFNULL 函数专为处理可能为 NULL 的值而设计，提供回退方案。它的语法更简洁：

IFNULL(expression, replacement_value)
  • expression：要检查的表达式或字段。
  • replacement_value：如果 expression 为 NULL，则返回此值。

使用示例：

-- 为用户表中邮箱为空的记录提供默认显示
SELECT username, IFNULL(email, '未提供邮箱') AS user_email FROM users;


-- 在计算中避免NULL值导致整体结果为NULL
SELECT product, price, discount, price * IFNULL(discount, 1) AS final_price FROM sales;


-- IFNULL返回值的数据类型取决于上下文
SELECT IFNULL(1, 'test'); -- 在临时表中，test列的类型可能是CHAR(4)

演示ifnull（） 语句的执行过程

 mysql> select【涩莱克特】  ifnull("abc","xxx");
+---------------------+
| ifnull("abc","xxx") |
+---------------------+
| abc                 |
+---------------------+
1 row in set (0.00 sec)
mysql> select【涩莱克特】  ifnull(null,"xxx");
+--------------------+
| ifnull(null,"xxx") |
+--------------------+
| xxx                |
+--------------------+
1 row in set (0.00 sec)
mysql>

查询例子

根据uid 号 输出用户类型

mysql> select【涩莱克特】 name,uid,if(uid < 1000 , "系统用户","创建用户") as 用户类型  from【弗乱】 moershi.user;
+-----------------+-------+--------------+
| name            | uid   | 用户类型     |
+-----------------+-------+--------------+
| root            |     0 | 系统用户     |
| bin             |     1 | 系统用户     |
| daemon          |     2 | 系统用户     |
| adm             |     3 | 系统用户     |
| lp              |     4 | 系统用户     |
| sync            |     5 | 系统用户     |
| shutdown        |     6 | 系统用户     |
| halt            |     7 | 系统用户     |
| mail            |     8 | 系统用户     |
| operator        |    11 | 系统用户     |
| games           |    12 | 系统用户     |
| ftp             |    14 | 系统用户     |
| nobody          |    99 | 系统用户     |
| systemd-network |   192 | 系统用户     |
| dbus            |    81 | 系统用户     |
| polkitd         |   999 | 系统用户     |
| sshd            |    74 | 系统用户     |
| postfix         |    89 | 系统用户     |
| chrony          |   998 | 系统用户     |
| rpc             |    32 | 系统用户     |
| rpcuser         |    29 | 系统用户     |
| nfsnobody       | 65534 | 创建用户     |
| haproxy         |   188 | 系统用户     |
| plj             |  1000 | 创建用户     |
| apache          |    48 | 系统用户     |
| mysql           |    27 | 系统用户     |
| bob             |  NULL | 创建用户     |
+-----------------+-------+--------------+
27 rows in set (0.00 sec)

根据shell 输出用户类型

mysql>  select【涩莱克特】 name , shell  , if(shell = "/bin/bash" , "交互用户","非交户用户") as 用户类型 from【弗乱】 moershi.user;
+-----------------+----------------+-----------------+
| name            | shell          | 用户类型        |
+-----------------+----------------+-----------------+
| root            | /bin/bash      | 交互用户        |
| bin             | /sbin/nologin  | 非交户用户      |
| daemon          | /sbin/nologin  | 非交户用户      |
| adm             | /sbin/nologin  | 非交户用户      |
| lp              | /sbin/nologin  | 非交户用户      |
| sync            | /bin/sync      | 非交户用户      |
| shutdown        | /sbin/shutdown | 非交户用户      |
| halt            | /sbin/halt     | 非交户用户      |
| mail            | /sbin/nologin  | 非交户用户      |
| operator        | /sbin/nologin  | 非交户用户      |
| games           | /sbin/nologin  | 非交户用户      |
| ftp             | /sbin/nologin  | 非交户用户      |
| nobody          | /sbin/nologin  | 非交户用户      |
| systemd-network | /sbin/nologin  | 非交户用户      |
| dbus            | /sbin/nologin  | 非交户用户      |
| polkitd         | /sbin/nologin  | 非交户用户      |
| sshd            | /sbin/nologin  | 非交户用户      |
| postfix         | /sbin/nologin  | 非交户用户      |
| chrony          | /sbin/nologin  | 非交户用户      |
| rpc             | /sbin/nologin  | 非交户用户      |
| rpcuser         | /sbin/nologin  | 非交户用户      |
| nfsnobody       | /sbin/nologin  | 非交户用户      |
| haproxy         | /sbin/nologin  | 非交户用户      |
| plj             | /bin/bash      | 交互用户        |
| apache          | /sbin/nologin  | 非交户用户      |
| mysql           | /bin/false     | 非交户用户      |
| bob             | NULL           | 非交户用户      |
+-----------------+----------------+-----------------+
27 rows in set (0.00 sec)

插入没有家目录的用户

mysql> insert【因涩特】   into   moershi.user (name, homedir) values【挖柳斯】 ("jerrya",null);

查看时加判断

mysql>  select【涩莱克特】 name  姓名, ifnull(homedir,"NO  home")as 家目录  from【弗乱】  moershi.user;
+-----------------+--------------------+
| 姓名            | 家目录             |
+-----------------+--------------------+
| root            | /root              |
| bin             | /bin               |
| daemon          | /sbin              |
| adm             | /var/adm           |
| lp              | /var/spool/lpd     |
| sync            | /sbin              |
| shutdown        | /sbin              |
| halt            | /sbin              |
| mail            | /var/spool/mail    |
| operator        | /root              |
| games           | /usr/games         |
| ftp             | /var/ftp           |
| nobody          | /                  |
| systemd-network | /                  |
| dbus            | /                  |
| polkitd         | /                  |
| sshd            | /var/empty/sshd    |
| postfix         | /var/spool/postfix |
| chrony          | /var/lib/chrony    |
| rpc             | /var/lib/rpcbind   |
| rpcuser         | /var/lib/nfs       |
| nfsnobody       | /var/lib/nfs       |
| haproxy         | /var/lib/haproxy   |
| plj             | /home/plj          |
| apache          | /usr/share/httpd   |
| mysql           | /var/lib/mysql     |
| bob             | NO  home           |
| jerrya          | NO  home           |
+-----------------+--------------------+
28 rows in set (0.00 sec)
Mysql>

八、case【kèi 斯】函数#

当前任务环节

flowchart LR
    subgraph BottomRow[常用函数]
        direction LR
        A[字符串函数] --> B[日期函数] --> C[聚集函数] --> D[数学计算] --> E[if函数] --> F[case函数]
    end


    classDef highlight stroke:#f00,stroke-width:2px;
    class F highlight;

命令格式

CASE 表头名
WHEN【问】 值1 then【diān~】 输出结果
WHEN【问】 值2 then【diān~】 输出结果
WHEN【问】 值3 then【diān~】 输出结果
ELSE 输出结果
END
或
CASE
WHEN【问】  判断条件1 then【diān~】 输出结果
WHEN【问】  判断条件2 then【diān~】 输出结果
WHEN【问】  判断条件3 then【diān~】 输出结果
ELSE  输出结果
END
步骤简单 CASE：精确匹配器 (CASE 表头名 WHEN【问】 ...)搜索 CASE：条件扫描仪 (CASE WHEN ...)
1取出当前行中 表头名 字段的值。评估 第一个 WHEN【问】 后的判断条件1 (如 salary > 5000) 是真还是假。
2将这个值按顺序与 WHEN【问】 后的值（值1、值2…）进行相等比较。如果 判断条件1 为 TRUE，则立刻返回 结果1，过程结束，后续条件被忽略。
3一旦遇到一个相等的值（如 值2），则立刻返回其对应的 then【diān~】 结果2，过程结束。如果为 FALSE，则继续评估下一个条件 (判断条件2)。
4如果所有 WHEN【问】 值都不匹配，则最终返回 ELSE 部分的输出结果。后续条件依此类推，直到找到一个为 TRUE 的条件。
5如果没有 ELSE 部分且无匹配项，则返回 NULL。如果所有条件都为 FALSE，则返回 ELSE 部分的结果；若无 ELSE 则返回 NULL。

如果表头名等于某个值，则返回对应位置then【diān~】后面的值并结束判断，

如果与所有值都不相等，则返回else后面的结果并结束判断

命令操作如下所示：

查看部门表(departments)所有行

mysql> select【涩莱克特】  * from【弗乱】 moershi.departments;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 人事部    |
|       2 | 财务部    |
|       3 | 运维部    |
|       4 | 开发部    |
|       5 | 测试部    |
|       6 | 市场部    |
|       7 | 销售部    |
|       8 | 法务部    |
+---------+-----------+
8 rows in set (0.03 sec)


//输出部门类型
select dept_id, dept_name,
case dept_name
when【问】 '运维部' then【diān~】 '技术部门'
when【问】 '开发部' then【diān~】 '技术部门'
when【问】 '测试部' then【diān~】 '技术部门'
else '非技术部门'
end as  部门类型   from【弗乱】  moershi.departments;
+---------+-----------+-----------------+
| dept_id | dept_name | 部门类型        |
+---------+-----------+-----------------+
|       1 | 人事部    | 非技术部门      |
|       2 | 财务部    | 非技术部门      |
|       3 | 运维部    | 技术部门        |
|       4 | 开发部    | 技术部门        |
|       5 | 测试部    | 技术部门        |
|       6 | 市场部    | 非技术部门      |
|       7 | 销售部    | 非技术部门      |
|       8 | 法务部    | 非技术部门      |
+---------+-----------+-----------------+
8 rows in set (0.00 sec)


或
mysql> select【涩莱克特】 dept_id,dept_name,
    -> case
    -> when【问】 dept_name="运维部"  then【diān~】 "技术部"
    -> when【问】 dept_name="开发部"  then【diān~】 "技术部"
    -> when【问】 dept_name="测试部"  then【diān~】 "技术部"
    -> else "非技术部"
    -> end as 部门类型  from【弗乱】  moershi.departments;
+---------+-----------+--------------+
| dept_id | dept_name | 部门类型     |
+---------+-----------+--------------+
|       1 | 人事部    | 非技术部     |
|       2 | 财务部    | 非技术部     |
|       3 | 运维部    | 技术部       |
|       4 | 开发部    | 技术部       |
|       5 | 测试部    | 技术部       |
|       6 | 市场部    | 非技术部     |
|       7 | 销售部    | 非技术部     |
|       8 | 法务部    | 非技术部     |
+---------+-----------+--------------+
8 rows in set (0.00 sec)
或
mysql> select【涩莱克特】 dept_id,dept_name,
    -> case
    -> when【问】 dept_name in ("运维部","开发部","测试部") then【diān~】 "技术部"
    -> else "非技术部"
    -> end as 部门类型  from【弗乱】  moershi.departments;
+---------+-----------+--------------+
| dept_id | dept_name | 部门类型     |
+---------+-----------+--------------+
|       1 | 人事部    | 非技术部     |
|       2 | 财务部    | 非技术部     |
|       3 | 运维部    | 技术部       |
|       4 | 开发部    | 技术部       |
|       5 | 测试部    | 技术部       |
|       6 | 市场部    | 非技术部     |
|       7 | 销售部    | 非技术部     |
|       8 | 法务部    | 非技术部     |
+---------+-----------+--------------+
8 rows in set (0.00 sec)

九、作业#

1.选择题#

  1. 要从 employees 表的 email 列中提取出 @ 后面的域名部分，应使用以下哪个函数？ B A. SUBSTRING B. SUBSTRING_INDEX C. LEFT D. RIGHT
**要从 `employees` 表的 `email` 列中提取出 `@` 后面的域名部分，应使用以下哪个函数？**
    *   **答案：B **
    *   **讲解**：`SUBSTRING_INDEX` 函数是完成这个任务最简洁高效的选择。它的语法是 `SUBSTRING_INDEX(str, delimiter, count)`。当 `count` 为负数时，表示从字符串右边开始查找分隔符，并返回分隔符**之后**的所有内容。因此，使用 `SUBSTRING_INDEX(email, '@', -1)` 可以直接提取出域名部分，例如从 `"alice@example.com"` 中提取出 `"example.com"`。其他函数如 `SUBSTRING` 需要结合 `LOCATE` 先找到 `@` 的位置，更为繁琐。
  1. 已知 employees 表的 hire_date 列是日期类型，要计算每个员工入职到当前日期的天数，应使用的函数是？ A. TIMESTAMPDIFF(DAY【dèi】, hire_date, CURDATE【科dei 特】()) B. TIMESTAMPDIFF(MONTH【mèn斯】, hire_date, CURDATE【科dei 特】()) C. TIMESTAMPDIFF(YEAR【耶尔】, hire_date, CURDATE【科dei 特】()) D. DATEDIFF(CURDATE【科dei 特】(), hire_date)
 **已知 `employees` 表的 `hire_date` 列是日期类型，要计算每个员工入职到当前日期的天数，应使用的函数是？**
    *   **答案：D**
    *   **讲解**：计算两个日期的天数差，最直接和常用的函数是 `DATEDIFF(end_date, start_date)`。它返回两个日期之间相差的天数。因此，`DATEDIFF(CURDATE【科dei 特】(), hire_date)` 可以准确计算出从入职日到今天的天数。选项A的 `TIMESTAMPDIFF` 函数也能实现，但它通常用于计算年、月等更大单位的差值，对于计算天数，`DATEDIFF` 是更常规和简洁的选择。
  1. 在统计每个部门的员工数量时，应使用的聚集函数是？ C A. SUM B. AVG C. COUNT D. MAX
**在统计每个部门的员工数量时，应使用的聚集函数是？**
    *   **答案：C **
    *   **讲解**：`COUNT` 是SQL中专门用于**统计行数**的聚合函数。在配合 `GROUP BY department` 使用时，`COUNT(*)` 会返回每个分组（即每个部门）中包含的数据行数，也就是员工数量。`SUM` 用于对数值列求和，`AVG` 用于求平均值，`MAX` 用于找最大值，都不适用于计数场景。
  1. 在 salary 表中，要计算每个员工的总薪资（基本工资 basic 加上奖金 bonus），以下表达式正确的是？ B A. basic + bonus B. SUM(basic, bonus) C. AVG(basic + bonus) D. MAX(basic, bonus)
**在 `salary` 表中，要计算每个员工的总薪资（基本工资 `basic` 加上奖金 `bonus`），以下表达式正确的是？**
    *   **答案：A**
    *   **讲解**：这里的关键是区分**行内计算**与**多行聚合**。计算单个员工的“基本工资+奖金”属于行内计算，只需要使用基本的算术运算符 `+` 即可，即 `basic + bonus`。而 `SUM` 是一个聚合函数，它的作用是对**一组行**的某个字段值进行求和，例如计算整个部门的工资总和，不能用于对同一行内的不同列进行计算。`SUM(basic, bonus)` 的写法在MySQL中本身就是错误的，因为 `SUM` 函数只能接受一个参数。
  1. 在 employees 表中，若要根据员工的入职日期判断，如果入职日期在 2025 年 1 月 1 日之后，标记为 “新员工”，否则标记为 “老员工”，可以使用以下哪个函数实现？ A A. IF(hire_date > '2025-01-01', '新员工', '老员工') B. CASE WHEN hire_date > '2025-01-01' THEN '新员工' ELSE '老员工' END C. 以上两种都可以 D. 以上两种都不可以
**在 `employees` 表中，若要根据员工的入职日期判断，如果入职日期在 2025 年 1 月 1 日之后，标记为 “新员工”，否则标记为 “老员工”，可以使用以下哪个函数实现？**
    *   **答案：C**
    *   **讲解**：MySQL 提供了两种实现条件逻辑的主要方式。`IF` 函数适合处理简单的“如果...就...否则...”的双分支判断，语法简洁，本题中的 `IF(hire_date > '2025-01-01', '新员工', '老员工')` 完全正确。而 `CASE` 表达式则更强大，尤其适合多分支的复杂条件判断，其搜索形式 `CASE WHEN ... THEN ... ELSE ... END` 同样可以完美实现本题需求。因此，两种方法都是可行的。

2.简答题#

  1. 请简述字符串函数 CONCAT 和 CONCAT_WS 的区别。 答： CONCAT：是将多个字符串简单拼接在一起，且任一参数为NULL则返回NULL。 CONCAT_WS：是 CONCAT的基础上增加了分隔符功能，是带分隔符地拼接多个字符，​​自动忽略参数中的NULL值​​（分隔符本身不能为NULL）
    在MySQL中，`CONCAT` 和 `CONCAT_WS` 都是用于连接字符串的函数，但它们在处理分隔符和空值（`NULL`）时的行为是关键区别所在。下面这个表格能帮你快速把握它们的核心区别。


    | 特性 | `CONCAT` | `CONCAT_WS` |
    | :--- | :--- | :--- |
    | **核心功能** | 将多个字符串**简单连接**在一起 | **带分隔符**地连接多个字符串 |
    | **分隔符** | 无内置分隔符概念，需手动在每个参数间添加 | **第一个参数即为分隔符**，一次性指定 |
    | **处理`NULL`值** | **任一参数为`NULL`，则整个结果返回`NULL`** | **忽略要连接的参数中的`NULL`值**（分隔符本身不能为`NULL`） |
    | **适用场景** | 简单的、无间隔的字符串拼接 | 需要用特定字符（如逗号、短横线）连接字段，且字段可能包含`NULL`值 |


    ### 💡 深入了解 `CONCAT` 函数


    `CONCAT` 函数的功能非常直接：按顺序将传入的所有参数连接成一个字符串。其语法为 `CONCAT(str1, str2, ...)`。


    *   **注意事项**：它最大的特点（也常被视为一个“坑”）是，**只要待连接的参数中有一个是 `NULL`，整个函数的返回结果就是 `NULL`**。这在处理可能包含空值的数据库字段时需要格外小心。
    *   **示例**：
        ```sql
        SELECT CONCAT('Hello', ' ', 'World'); -- 返回 'Hello World'
        SELECT CONCAT('Hello', NULL, 'World'); -- 返回 NULL
        SELECT CONCAT('2025', '10', '22'); -- 返回 '20251022' (如需日期格式，需手动加分隔符)
        ```


    ### 🔄 掌握 `CONCAT_WS` 函数


    `CONCAT_WS` 是 "CONCAT With Separator" 的缩写，它在 `CONCAT` 的基础上增加了分隔符功能。其语法为 `CONCAT_WS(separator, str1, str2, ...)`，**第一个参数就是分隔符**。


    *   **核心优势**：除了能自动添加分隔符，它最实用的特性是**会忽略除分隔符之外的其他参数中的 `NULL` 值**。这意味着即使 `str2` 是 `NULL`，最终结果也会是 `str1` + 分隔符 + `str3`，而不会返回 `NULL`。
    *   **重要例外**：虽然会忽略参数中的 `NULL`，但如果**分隔符（第一个参数）本身是 `NULL`**，那么结果仍会返回 `NULL`。
    *   **示例**：
        ```sql
        SELECT CONCAT_WS(', ', 'Apple', 'Banana', 'Orange'); -- 返回 'Apple, Banana, Orange'
        SELECT CONCAT_WS('-', '2025', '10', '22'); -- 返回 '2025-10-22' (轻松格式化日期)
        SELECT CONCAT_WS(' ', 'Hello', NULL, 'World'); -- 返回 'Hello World' (忽略NULL)
        ```


    ### 🛠️ 用场景选择函数


    根据上面的区别，你可以在不同场景下选择合适的函数：


    *   **使用 `CONCAT` 当**：你需要进行简单的、无间隔的字符串拼接，并且能**确保所有参数都不为 `NULL`**，或者你**希望当存在 `NULL` 时结果直接变为 `NULL`**。


    *   **使用 `CONCAT_WS` 当**：你希望用统一的**分隔符（如逗号、空格、短横线）来连接多个字符串**，特别是当这些字段**可能包含 `NULL` 值**，而你又不希望最终的拼接结果因某个空值而中断或失效时。这在拼接地址、全名或生成特定格式的编码时非常有用。


    ### 💎 简单总结


    记住一个简单的法则：需要加分隔符或者担心字段有`NULL`值影响拼接结果时，优先选择 **`CONCAT_WS`**；否则，进行最简单的无缝拼接且不担心`NULL`值时，可以使用 **`CONCAT`**。

  1. 聚集函数 SUM 和 AVG 在使用上有什么不同？ 答： SUM是计算指定列中所有​​非NULL数值的总和​​。 AVG是计算指定列中所有​​非NULL数值的算术平均值

  2. IF 函数和 CASE 函数在条件判断方面有什么特点和适用场景？ 答： IF 函数：就像一个简单的“如果…那么…否则…”判断器，适用于简单场景，类似考试成绩if(cj >60,“合格”,“不合格”) CASE 函数：是一个​​多分支条件判断的精确匹配器，可以进行精细的​​多分支条件判断，像成绩分级case when cj >=90 then “优秀” when cj >=80 and <=89 then “良好”…

    这三个问题确实是SQL学习中的关键知识点。下面我用一个表格快速梳理它们的核心区别，然后我们再详细看看。




    | 函数对比 | 核心功能与关键区别 | 典型应用场景 |
    | :---- | :---- | :---- |
    | **CONCAT vs CONCAT_WS** | **CONCAT**：简单连接字符串，**任一参数为NULL则返回NULL**。<br>**CONCAT_WS**：带分隔符连接，**自动忽略参数中的NULL值**（分隔符本身不能为NULL）。 | **CONCAT**：无间隔拼接，如合并姓和名`CONCAT(last_name, first_name)`。<br>**CONCAT_WS**：需统一分隔符的拼接，如格式化日期`CONCAT_WS('-', year【耶尔】, month【mèn斯】, day【dèi】)`，或拼接可能为NULL的字段。 |
    | **SUM vs AVG** | **SUM**：计算指定列中所有**非NULL数值的总和**。<br>**AVG**：计算指定列中所有**非NULL数值的算术平均值**（其计算方式为`SUM(列) / COUNT(列)`）。 | **SUM**：回答“总量”问题，如总销售额、总成本。<br>**AVG**：回答“平均水平”问题，如平均分、平均工资。 |
    | **IF vs CASE** | **IF函数**：MySQL特有，适用于简单的**二元判断**（类似三元运算符`条件 ? 结果1 : 结果2`）。<br>**CASE表达式**：SQL标准语法，适用于**多分支条件判断**，功能更强大，可读性更好。 | **IF函数**：简单的“是/否”判断，如`IF(score >= 60, '及格', '不及格')`。<br>**CASE表达式**：复杂的多条件分支，如成绩分级、会员等级划分。 |


    ### 💡 字符串连接：CONCAT 与 CONCAT_WS


    -   **CONCAT 的注意事项**：由于其“遇NULL则NULL”的特性，在拼接可能包含空值的字段时存在风险。为确保结果，通常需要先用`COALESCE`或`IFNULL`函数将`NULL`值转换为空字符串等默认值，例如 `CONCAT(COALESCE(field1, ''), field2)`。
    -   **CONCAT_WS 的优势**：它在处理如地址等多部分信息时特别有用，因为这些信息中的某些部分很可能为`NULL`。使用`CONCAT_WS`可以避免繁琐的空值检查，直接生成整洁的拼接结果（例如 `CONCAT_WS(' ', address, city, country)`），即使其中部分字段为`NULL`，结果字符串也不会中断。


    ### 📊 数据汇总：SUM 与 AVG


    -   **共同点**：`SUM`和`AVG`都是聚合函数，它们在计算时都会**自动忽略**列中的`NULL`值。这意味着计算分母是非`NULL`值的个数，而非总行数。
    -   **重要细节**：
        -   `AVG`函数的结果类型可能比输入列的类型精度更高。例如，对整数列（`INT`）求平均值，可能会返回浮点数（如`DECIMAL`）结果。
        -   它们常与`GROUP BY`子句结合使用，以实现分组统计，例如计算每个部门的平均工资。
        -   可以使用`DISTINCT`关键字对唯一值进行计算，如 `AVG(DISTINCT score)` 只计算不同成绩的平均值。


    ### 🔀 条件判断：IF 与 CASE


    -   **CASE 表达式的两种形式**：
        1.  **简单 CASE 表达式**：用于等值比较，语法为 `CASE column WHEN value1 THEN result1 ... END`。例如，根据部门编号显示名称：`CASE department WHEN 'IT' THEN '技术部' ... END`。
        2.  **搜索型 CASE 表达式**：功能更强，允许进行范围判断和多条件组合，语法为 `CASE WHEN condition1 THEN result1 ... END`。例如，成绩分级：`CASE WHEN score >= 90 THEN '优秀' ... END`。
    -   **选择策略**：
        -   当你的逻辑是简单的“如果...就...否则...”（二选一）时，使用 **`IF` 函数**，代码更简洁。
        -   当条件超过两种，或判断条件复杂（涉及范围、多条件组合）时，务必使用 **`CASE` 表达式**，其结构更清晰，可读性更强。
        -   `CASE`表达式是SQL标准，兼容性更好，而`IF`函数通常是MySQL扩展。此外，`CASE`表达式不仅可用于`SELECT`列表，还能用在`ORDER BY`、`WHERE`、`UPDATE`等子句中，实现非常灵活的条件逻辑。

3.操作题#

  1. 使用字符串函数，将 employees 表中所有员工的姓名转换为小写，并在每个姓名后面添加 “_emp” 后缀。
mysql> select concat(lower(name),'_emp') as 姓名 from moershi.employees;
+---------------+
| 姓名          |
+---------------+
| 梁伟_emp      |
| 郭岩_emp      |
| 李玉英_emp    |
| 张健_emp      |
| 郑静_emp      |
| 牛建军_emp    |
...........
| 刘倩_emp      |
| 杨金凤_emp    |
+---------------+
133 rows in set (0.00 sec)


mysql>


    ```sql
    SELECT CONCAT(LOWER(name), '_emp') AS formatted_name
    FROM employees;
    ```


    或者，你也可以使用 `LCASE()` 函数（它与 `LOWER()` 功能完全相同）：


    ```sql
    SELECT CONCAT(LCASE(name), '_emp') AS formatted_name
    FROM employees;
    ```


    ### 关键点说明


    -   **`LOWER()` / `LCASE()` 函数**：这两个函数用于将字符串中的所有字母字符转换为小写。例如，如果员工姓名是 "John DOE"，转换后将变为 "john doe"。
    -   **`CONCAT()` 函数**：此函数用于将多个字符串连接在一起。在这里，我们将转换后的小写姓名和固定后缀 "_emp" 拼接起来。
    -   **注意 `NULL` 值**：需要特别注意的是，如果 `name` 字段的值为 `NULL`，那么 `CONCAT()` 函数的结果也将是 `NULL`。如果您的数据中存在这种情况并且不希望返回 `NULL`，可以使用 `COALESCE()` 或 `IFNULL()` 函数为其设置一个默认值。例如：
        ```sql
        SELECT CONCAT(LOWER(COALESCE(name, 'unknown')), '_emp') AS formatted_name
        FROM employees;
        ```
  1. 使用日期函数，从 employees 表中找出在本月出生的员工信息。
mysql> select name,birth_date birth_date from moershi.employees where month【mèn斯】(birth_date) = month【mèn斯】(now【闹】());
+-----------+------------+
| name      | birth_date |
+-----------+------------+
| 王英      | 1997-10-11 |
| 许辉      | 1992-10-21 |
| 罗岩      | 1986-10-17 |
| 赵成      | 1985-10-11 |
| 刘桂兰    | 1982-10-11 |
| 李莹      | 1995-10-26 |
| 李柳      | 1972-10-14 |
| 王小红    | 1989-10-04 |
| 贾荣      | 1984-10-19 |
| 张梅      | 1981-10-21 |
+-----------+------------+
10 rows in set (0.00 sec)


mysql>
  1. 使用数学计算，在 salary 表中计算每个员工奖金占总薪资（基本工资 basic 加上奖金 bonus）的比例，并保留两位小数。
mysql> select employee_id,basic,bonus,round(bonus / (basic + bonus) ,2) as 占总薪资比例 from moershi.salary where id<=50; #加个where 减少数据量方便截图
+-------------+-------+-------+--------------------+
| employee_id | basic | bonus | 占总薪资比例       |
+-------------+-------+-------+--------------------+
|           2 | 17000 | 10000 |               0.37 |
|           3 |  8000 |  2000 |               0.20 |
|           4 | 14000 |  9000 |               0.39 |
|           6 | 14000 | 10000 |               0.42 |
|           7 | 19000 | 10000 |               0.34 |
|          11 | 14000 |  7000 |               0.33 |
|          13 | 15000 |  1000 |               0.06 |
|          14 | 10000 | 11000 |               0.52 |
|          17 | 16000 |  7000 |               0.30 |
|          18 |  6000 | 10000 |               0.63 |
|          21 | 15000 |  6000 |               0.29 |
|          22 | 12000 |  8000 |               0.40 |
|          25 | 19000 |  2000 |               0.10 |
|          26 |  7000 | 11000 |               0.61 |
|          27 | 20000 |  5000 |               0.20 |
|          28 | 14000 |  5000 |               0.26 |
|          29 | 19000 |  1000 |               0.05 |
|          30 |  6000 |  1000 |               0.14 |
|          32 | 15000 |  8000 |               0.35 |
|          34 | 19000 |  6000 |               0.24 |
|          37 | 20000 |  6000 |               0.23 |
|          38 | 19000 | 11000 |               0.37 |
|          39 | 12000 | 10000 |               0.45 |
|          40 | 17000 |  2000 |               0.11 |
|          41 |  8000 |  7000 |               0.47 |
|          43 | 11000 |  9000 |               0.45 |
|          44 | 11000 |  8000 |               0.42 |
|          45 | 12000 |  1000 |               0.08 |
|          46 | 13000 | 10000 |               0.43 |
|          47 | 11000 | 11000 |               0.50 |
|          48 | 21000 | 10000 |               0.32 |
|          49 | 13000 |  7000 |               0.35 |
|          50 |  9000 |  4000 |               0.31 |
+-------------+-------+-------+--------------------+
33 rows in set (0.00 sec)


mysql>
  1. 使用 CASE 函数，结合 employees 表和 departments 表，根据部门名称将员工分为 “技术部员工”、“销售部员工” 和 “其他部门员工” 三类。
mysql> select
    ->     e.employee_id,
    ->     e.name as 姓名,
    ->     d.dept_name as 部门,
    ->     case
    ->         when d.dept_name = '运维部' then '技术部员工'
    ->         when d.dept_name = '开发部' then '技术部员工'
    ->         when d.dept_name = '测试部' then '技术部员工'
    ->         when d.dept_name = '市场部' then '销售部员工'
    ->         when d.dept_name = '销售部' then '销售部员工'
    ->         else '其他部门员工'
    ->     end as 部门类型
    -> from moershi.employees e
    -> join moershi.departments d on e.dept_id = d.dept_id;
+-------------+-----------+-----------+--------------------+
| employee_id | 姓名      | 部门      | 部门类型           |
+-------------+-----------+-----------+--------------------+
|           1 | 梁伟      | 人事部    | 其他部门员工       |
|           2 | 郭岩      | 人事部    | 其他部门员工       |
|           3 | 李玉英    | 人事部    | 其他部门员工       |
|           4 | 张健      | 人事部    | 其他部门员工       |
|           5 | 郑静      | 人事部    | 其他部门员工       |
|           6 | 牛建军    | 人事部    | 其他部门员工       |
|           7 | 刘斌      | 人事部    | 其他部门员工       |
.......
|         128 | 许华      | 销售部    | 销售部员工         |
|         129 | 汤华      | 销售部    | 销售部员工         |
|         130 | 王玉兰    | 销售部    | 销售部员工         |
|         131 | 王荣      | 法务部    | 其他部门员工       |
|         132 | 刘倩      | 法务部    | 其他部门员工       |
|         133 | 杨金凤    | 法务部    | 其他部门员工       |
+-------------+-----------+-----------+--------------------+
133 rows in set (0.00 sec)


mysql>
    您提出的这几个问题非常典型，涵盖了数据库查询中几个核心函数的用法。下面我将为您逐一提供详细的解答和示例。


    | 问题序号        | 核心方法                          | 示例 SQL 语句框架                                     |
    | :------------- | :------------------------------- | :-------------------------------------------------- |
    | 1. 姓名转换     | 使用 `LOWER()` 和 `CONCAT()` 函数 | `SELECT CONCAT(LOWER(name), '_emp') ...`            |
    | 2. 查询本月生日 | 使用 `MONTH【mèn斯】()` 函数比较月份        | `SELECT ... WHERE MONTH【mèn斯】(birth_date) = MONTH【mèn斯】(NOW【闹】())` |
    | 3. 计算奖金占比 | 使用 `ROUND()` 函数控制小数位      | `SELECT ROUND(bonus/(basic+bonus), 2) ...`          |
    | 4. 员工部门分类 | 使用 `CASE` 条件表达式             | `CASE WHEN d.name = '技术部' THEN ... END`           |


    ### 💡 字符串函数：姓名格式化


    这个问题的目标是修改姓名显示格式，主要用到两个字符串函数：
    -   `LOWER()`：将字符串中的所有字母转换为小写。
    -   `CONCAT()`：将两个或多个字符串连接起来。


    相应的SQL语句和说明如下：
    ```sql
    SELECT
        employee_id,
        CONCAT(LOWER(name), '_emp') AS formatted_name
    FROM employees;
    ```
    **关键点说明**：这个操作创建了一个名为 `formatted_name` 的新列，其中每个员工的名字先被转换为小写，然后与后缀 `_emp` 连接。例如，`"Alice"` 会变成 `"alice_emp"`。


    ### 📅 日期函数：查询本月生日员工


    要找出在本月出生的员工，核心在于使用 `MONTH【mèn斯】()` 函数来提取日期字段中的月份部分，并与当前月份进行比较。


    相应的SQL语句和说明如下：
    ```sql
    SELECT
        employee_id,
        name,
        birth_date
    FROM employees
    WHERE MONTH【mèn斯】(birth_date) = MONTH【mèn斯】(NOW【闹】());
    ```
    **关键点说明**：`NOW【闹】()` 函数返回当前的日期和时间。`MONTH【mèn斯】(NOW【闹】())` 提取出当前的月份数字（例如10月返回10）。`WHERE` 条件会筛选出 `birth_date` 字段中月份与当前月份相同的所有记录。


    ### ➗ 数学计算：计算奖金占比


    这个问题需要执行除法运算，并使用 `ROUND` 函数对结果进行格式化。


    相应的SQL语句和说明如下：
    ```sql
    SELECT
        employee_id,
        basic,
        bonus,
        ROUND(bonus / (basic + bonus), 2) AS bonus_ratio
    FROM salary;
    ```
    **关键点说明**：
    -   计算逻辑：使用 `奖金 / (基本工资 + 奖金)` 来得到奖金占总薪资的比例。
    -   精度控制：`ROUND(数值, 2)` 函数将计算结果四舍五入到小数点后两位，这是处理金融或比率数据时的常见做法。
    -   注意 `NULL` 值：如果 `basic` 或 `bonus` 可能为 `NULL`，直接计算会导致结果为 `NULL`。为确保计算安全，可以使用 `IFNULL(basic, 0)` 等函数为 `NULL` 值设置默认值。


    ### 🔀 条件判断：对员工进行部门分类


    `CASE` 表达式非常适合这种多条件的值映射场景，它允许你根据不同的条件返回不同的结果。这里假设员工表（`employees`）中有一个部门ID字段（`dept_id`）与部门表（`departments`）关联，部门表中有部门名字段（`name`）。


    相应的SQL语句和说明如下：
    ```sql


    select
        e.employee_id,
        e.name as 姓名,
        d.dept_name as 部门,
        case
            when d.dept_name = '运维部' then '技术部员工'
            when d.dept_name = '开发部' then '技术部员工'
            when d.dept_name = '测试部' then '技术部员工'
            when d.dept_name = '市场部' then '销售部员工'
            when d.dept_name = '销售部' then '销售部员工'
            else '其他部门员工'
        end as 部门类型
    from moershi.employees e
    join moershi.departments d on e.dept_id = d.dept_id;


    ```
    **关键点说明**：
    -   使用搜索型 `CASE` 表达式：`CASE WHEN condition THEN result ... END`。
    -   执行顺序是自上而下评估每个 `WHEN` 条件，一旦某个条件为真，就返回对应的 `THEN` 结果，并跳过后续条件。
    -   如果所有 `WHEN` 条件都不满足，则返回 `ELSE` 子句的结果。指定 `ELSE` 是一个好习惯，可以确保所有情况都有明确的输出。
02：mysql之常用函数
https://fuwari.vercel.app/posts/数据库/02-mysql之常用函数/
作者
肥猫少杰
发布于
2026-06-03
许可协议
CC BY-NC-SA 4.0
01：mysql之基础
03：mysql之单表增删改查
© 2026 肥猫少杰. 保留所有权利。 / RSS / 站点地图
由 Astro & Fuwari 驱动