第12章：MySQL 常用函数（提升效率）

MySQL 提供了丰富的内置函数，这些函数可以帮助我们更方便地处理数据。本章将介绍一些最常用的 MySQL 函数，包括字符串函数、日期时间函数和其他常用函数。

12.1 字符串函数（常用）

字符串函数用于处理字符串类型的数据。

12.1.1 CONCAT() - 拼接字符串

语法： CONCAT(str1, str2, ...)

作用： 将多个字符串拼接成一个字符串

示例：

sql

-- 拼接姓名和班级
SELECT CONCAT(name, ' - ', class_id) AS 学生信息 FROM students;

-- 拼接多个字符串
SELECT CONCAT('Hello', ' ', 'World', '!') AS 问候语;

-- 处理 NULL 值（会返回 NULL）
SELECT CONCAT('Name: ', NULL) AS 结果;  -- 返回 NULL

-- 使用 CONCAT_WS（带分隔符）
SELECT CONCAT_WS(', ', '张三', '18', '一班') AS 学生信息;

12.1.2 LENGTH() - 获取字符串长度

语法： LENGTH(str)

作用： 返回字符串的长度（字节数）

示例：

sql

-- 获取姓名长度
SELECT name, LENGTH(name) AS 姓名长度 FROM students;

-- 注意：中文字符可能占用多个字节
SELECT LENGTH('你好') AS 长度;  -- UTF-8 下返回 6

-- 使用 CHAR_LENGTH 获取字符数
SELECT CHAR_LENGTH('你好') AS 字符数;  -- 返回 2

12.1.3 SUBSTR() - 截取字符串

语法： SUBSTR(str, start, length) 或 SUBSTRING(str, start, length)

作用： 从指定位置开始截取字符串

示例：

sql

-- 从第1个字符开始截取3个字符
SELECT SUBSTR('Hello World', 1, 5) AS 结果;  -- 返回 'Hello'

-- 从第7个字符开始截取到末尾
SELECT SUBSTR('Hello World', 7) AS 结果;  -- 返回 'World'

-- 截取姓名的前2个字符
SELECT name, SUBSTR(name, 1, 2) AS 姓名缩写 FROM students;

12.1.4 UPPER()/LOWER() - 转换大小写

语法： UPPER(str) / LOWER(str)

作用： 将字符串转换为大写/小写

示例：

sql

-- 转换为大写
SELECT UPPER('hello') AS 大写;  -- 返回 'HELLO'

-- 转换为小写
SELECT LOWER('WORLD') AS 小写;  -- 返回 'world'

-- 统一姓名格式
SELECT UPPER(name) AS 大写姓名 FROM students;

12.1.5 其他常用字符串函数

函数	语法	作用	示例
`TRIM()`	`TRIM(str)`	去除首尾空格	`TRIM(' hello ')` → 'hello'
`LTRIM()`	`LTRIM(str)`	去除左侧空格	`LTRIM(' hello')` → 'hello'
`RTRIM()`	`RTRIM(str)`	去除右侧空格	`RTRIM('hello ')` → 'hello'
`REPLACE()`	`REPLACE(str, old, new)`	替换字符串	`REPLACE('hello', 'l', 'x')` → 'hexxo'
`LEFT()`	`LEFT(str, length)`	左侧截取	`LEFT('hello', 2)` → 'he'
`RIGHT()`	`RIGHT(str, length)`	右侧截取	`RIGHT('hello', 2)` → 'lo'
`INSTR()`	`INSTR(str, substr)`	查找子串位置	`INSTR('hello', 'l')` → 3

12.2 日期时间函数（常用）

日期时间函数用于处理日期和时间类型的数据。

12.2.1 NOW() - 获取当前日期时间

语法： NOW()

作用： 返回当前的日期和时间

示例：

sql

-- 获取当前时间
SELECT NOW() AS 当前时间;

-- 插入当前时间
INSERT INTO orders (order_date) VALUES (NOW());

-- 使用 CURRENT_TIMESTAMP
SELECT CURRENT_TIMESTAMP AS 当前时间;

12.2.2 DATE() - 获取日期部分

语法： DATE(expr)

作用： 从日期时间表达式中提取日期部分

示例：

sql

-- 提取日期部分
SELECT DATE(NOW()) AS 当前日期;

-- 从时间戳提取日期
SELECT DATE('2023-01-01 12:34:56') AS 日期;  -- 返回 '2023-01-01'

12.2.3 YEAR()/MONTH()/DAY() - 获取年、月、日

语法： YEAR(date) / MONTH(date) / DAY(date)

作用： 从日期中提取年、月、日

示例：

sql

-- 提取年、月、日
SELECT 
    YEAR(NOW()) AS 年,
    MONTH(NOW()) AS 月,
    DAY(NOW()) AS 日;

-- 统计每年的订单数
SELECT YEAR(order_date) AS 年份, COUNT(*) AS 订单数
FROM orders
GROUP BY YEAR(order_date);

12.2.4 DATEDIFF() - 计算两个日期差值

语法： DATEDIFF(date1, date2)

作用： 计算两个日期之间的天数差

示例：

sql

-- 计算日期差
SELECT DATEDIFF('2023-12-31', '2023-01-01') AS 天数差;  -- 返回 364

-- 计算订单距今的天数
SELECT order_id, DATEDIFF(NOW(), order_date) AS 订单天数
FROM orders;

12.2.5 其他常用日期时间函数

函数	语法	作用	示例
`TIME()`	`TIME(expr)`	提取时间部分	`TIME('2023-01-01 12:34:56')` → '12:34:56'
`HOUR()`	`HOUR(time)`	提取小时	`HOUR('12:34:56')` → 12
`MINUTE()`	`MINUTE(time)`	提取分钟	`MINUTE('12:34:56')` → 34
`SECOND()`	`SECOND(time)`	提取秒	`SECOND('12:34:56')` → 56
`DATE_ADD()`	`DATE_ADD(date, INTERVAL expr unit)`	日期加法	`DATE_ADD(NOW(), INTERVAL 7 DAY)`
`DATE_SUB()`	`DATE_SUB(date, INTERVAL expr unit)`	日期减法	`DATE_SUB(NOW(), INTERVAL 1 MONTH)`
`CURDATE()`	`CURDATE()`	当前日期	`CURDATE()` → '2023-01-01'
`CURTIME()`	`CURTIME()`	当前时间	`CURTIME()` → '12:34:56'

12.3 其他常用函数

12.3.1 IF() - 条件判断

语法： IF(condition, value1, value2)

作用： 如果条件为真，返回 value1，否则返回 value2

示例：

sql

-- 根据成绩判断是否及格
SELECT 
    name, 
    score, 
    IF(score >= 60, '及格', '不及格') AS 状态
FROM students;

-- 条件表达式
SELECT 
    name, 
    age, 
    IF(age >= 18, '成年人', '未成年人') AS 类别
FROM students;

12.3.2 ROUND() - 四舍五入

语法： ROUND(number, decimals)

作用： 将数字四舍五入到指定小数位

示例：

sql

-- 四舍五入到整数
SELECT ROUND(85.67) AS 结果;  -- 返回 86

-- 四舍五入到2位小数
SELECT ROUND(85.6789, 2) AS 结果;  -- 返回 85.68

-- 计算平均成绩并四舍五入
SELECT ROUND(AVG(score), 2) AS 平均成绩 FROM students;

12.3.3 数学函数

函数	语法	作用	示例
`ABS()`	`ABS(number)`	绝对值	`ABS(-10)` → 10
`CEIL()`	`CEIL(number)`	向上取整	`CEIL(85.1)` → 86
`FLOOR()`	`FLOOR(number)`	向下取整	`FLOOR(85.9)` → 85
`MOD()`	`MOD(a, b)`	取模	`MOD(10, 3)` → 1
`POW()`	`POW(base, exp)`	幂运算	`POW(2, 3)` → 8
`SQRT()`	`SQRT(number)`	平方根	`SQRT(9)` → 3

12.3.4 聚合函数

函数	语法	作用	示例
`COUNT()`	`COUNT(expr)`	统计记录数	`COUNT(*)`
`SUM()`	`SUM(expr)`	求和	`SUM(score)`
`AVG()`	`AVG(expr)`	求平均值	`AVG(score)`
`MAX()`	`MAX(expr)`	求最大值	`MAX(score)`
`MIN()`	`MIN(expr)`	求最小值	`MIN(score)`

12.4 实操案例

案例1：使用字符串函数处理数据

场景： 处理用户姓名和邮箱

sql

-- 1. 拼接用户信息
SELECT CONCAT(name, ' <', email, '>') AS 联系信息 FROM users;

-- 2. 统一邮箱格式（转换为小写）
UPDATE users SET email = LOWER(email);

-- 3. 提取邮箱域名
SELECT 
    email, 
    SUBSTR(email, INSTR(email, '@') + 1) AS 域名
FROM users;

-- 4. 统计姓名长度分布
SELECT 
    LENGTH(name) AS 姓名长度, 
    COUNT(*) AS 人数
FROM users
GROUP BY LENGTH(name);

案例2：使用日期时间函数处理订单

场景： 分析订单数据

sql

-- 1. 统计每天的订单数
SELECT 
    DATE(order_date) AS 日期, 
    COUNT(*) AS 订单数
FROM orders
GROUP BY DATE(order_date)
ORDER BY 日期;

-- 2. 统计本月订单
SELECT COUNT(*) AS 本月订单数
FROM orders
WHERE MONTH(order_date) = MONTH(NOW()) 
  AND YEAR(order_date) = YEAR(NOW());

-- 3. 计算订单年龄（天数）
SELECT 
    order_id, 
    order_date, 
    DATEDIFF(NOW(), order_date) AS 订单年龄
FROM orders
ORDER BY 订单年龄 DESC;

-- 4. 预测交货日期（7天后）
SELECT 
    order_id, 
    order_date, 
    DATE_ADD(order_date, INTERVAL 7 DAY) AS 交货日期
FROM orders;

案例3：使用条件函数处理状态

场景： 学生成绩等级划分

sql

-- 划分成绩等级
SELECT 
    name, 
    score, 
    IF(score >= 90, '优秀',
       IF(score >= 80, '良好',
          IF(score >= 60, '及格', '不及格'))) AS 等级
FROM students;

-- 或者使用 CASE 语句
SELECT 
    name, 
    score, 
    CASE 
        WHEN score >= 90 THEN '优秀'
        WHEN score >= 80 THEN '良好'
        WHEN score >= 60 THEN '及格'
        ELSE '不及格'
    END AS 等级
FROM students;

12.5 新手易错点

❌ 错误1：字符串函数参数错误

sql

-- 错误：CONCAT 函数参数顺序错误
SELECT CONCAT('Hello', name) AS 结果 FROM students;

-- 正确：确保参数顺序正确
SELECT CONCAT('Hello ', name) AS 结果 FROM students;

❌ 错误2：日期函数使用不当

sql

-- 错误：日期格式不正确
SELECT DATEDIFF('2023/01/01', '2023-01-01');

-- 正确：使用标准日期格式
SELECT DATEDIFF('2023-01-02', '2023-01-01');

❌ 错误3：函数嵌套错误

sql

-- 错误：函数嵌套顺序错误
SELECT CONCAT('Name: ', UPPER(name)) FROM students;

-- 正确：函数嵌套是允许的
SELECT CONCAT('Name: ', UPPER(name)) FROM students;

❌ 错误4：数据类型不匹配

sql

-- 错误：对非数字类型使用数学函数
SELECT ROUND(name) FROM students;

-- 正确：确保数据类型匹配
SELECT ROUND(score) FROM students;

12.6 本章小结

函数类别	函数名	作用	示例
字符串函数	`CONCAT()`	拼接字符串	`CONCAT('a', 'b')`
	`LENGTH()`	字符串长度	`LENGTH('hello')`
	`SUBSTR()`	截取字符串	`SUBSTR('hello', 1, 3)`
	`UPPER()/LOWER()`	大小写转换	`UPPER('hello')`
	`TRIM()`	去除空格	`TRIM(' hello ')`
日期时间函数	`NOW()`	当前日期时间	`NOW()`
	`DATE()`	提取日期	`DATE(NOW())`
	`YEAR()/MONTH()/DAY()`	提取年/月/日	`YEAR(NOW())`
	`DATEDIFF()`	日期差	`DATEDIFF('2023-01-02', '2023-01-01')`
	`DATE_ADD()`	日期加法	`DATE_ADD(NOW(), INTERVAL 7 DAY)`
其他函数	`IF()`	条件判断	`IF(score >= 60, '及格', '不及格')`
	`ROUND()`	四舍五入	`ROUND(85.67, 1)`
	`ABS()`	绝对值	`ABS(-10)`
	`MOD()`	取模	`MOD(10, 3)`

最佳实践：

熟练掌握常用函数，提高 SQL 编写效率
注意函数的参数类型和返回值
合理使用函数嵌套，简化复杂逻辑
测试函数结果，确保符合预期

12.7 课后练习

使用 CONCAT 函数拼接学生姓名和班级
使用 SUBSTR 函数提取邮箱的域名部分
使用 DATE 函数统计每天的订单数量
使用 IF 函数根据成绩判断学生是否及格
使用 ROUND 函数将平均成绩保留2位小数

参考答案：

sql

-- 练习1
SELECT CONCAT(name, ' - 班级', class_id) AS 学生信息 FROM students;

-- 练习2
SELECT email, SUBSTR(email, INSTR(email, '@') + 1) AS 域名 FROM users;

-- 练习3
SELECT DATE(order_date) AS 日期, COUNT(*) AS 订单数 FROM orders GROUP BY DATE(order_date);

-- 练习4
SELECT name, score, IF(score >= 60, '及格', '不及格') AS 状态 FROM students;

-- 练习5
SELECT ROUND(AVG(score), 2) AS 平均成绩 FROM students;

第12章：MySQL 常用函数（提升效率） ​

12.1 字符串函数（常用） ​

12.1.1 CONCAT() - 拼接字符串 ​

12.1.2 LENGTH() - 获取字符串长度 ​

12.1.3 SUBSTR() - 截取字符串 ​

12.1.4 UPPER()/LOWER() - 转换大小写 ​

12.1.5 其他常用字符串函数 ​

12.2 日期时间函数（常用） ​

12.2.1 NOW() - 获取当前日期时间 ​

12.2.2 DATE() - 获取日期部分 ​

12.2.3 YEAR()/MONTH()/DAY() - 获取年、月、日 ​

12.2.4 DATEDIFF() - 计算两个日期差值 ​

12.2.5 其他常用日期时间函数 ​

12.3 其他常用函数 ​

12.3.1 IF() - 条件判断 ​

12.3.2 ROUND() - 四舍五入 ​

12.3.3 数学函数 ​

12.3.4 聚合函数 ​

12.4 实操案例 ​

案例1：使用字符串函数处理数据 ​

案例2：使用日期时间函数处理订单 ​

案例3：使用条件函数处理状态 ​

12.5 新手易错点 ​

❌ 错误1：字符串函数参数错误 ​

❌ 错误2：日期函数使用不当 ​

❌ 错误3：函数嵌套错误 ​

❌ 错误4：数据类型不匹配 ​

12.6 本章小结 ​

12.7 课后练习 ​

第12章：MySQL 常用函数（提升效率）

12.1 字符串函数（常用）

12.1.1 CONCAT() - 拼接字符串

12.1.2 LENGTH() - 获取字符串长度

12.1.3 SUBSTR() - 截取字符串

12.1.4 UPPER()/LOWER() - 转换大小写

12.1.5 其他常用字符串函数

12.2 日期时间函数（常用）

12.2.1 NOW() - 获取当前日期时间

12.2.2 DATE() - 获取日期部分

12.2.3 YEAR()/MONTH()/DAY() - 获取年、月、日

12.2.4 DATEDIFF() - 计算两个日期差值

12.2.5 其他常用日期时间函数

12.3 其他常用函数

12.3.1 IF() - 条件判断

12.3.2 ROUND() - 四舍五入

12.3.3 数学函数

12.3.4 聚合函数

12.4 实操案例

案例1：使用字符串函数处理数据

案例2：使用日期时间函数处理订单

案例3：使用条件函数处理状态

12.5 新手易错点

❌ 错误1：字符串函数参数错误

❌ 错误2：日期函数使用不当

❌ 错误3：函数嵌套错误

❌ 错误4：数据类型不匹配

12.6 本章小结

12.7 课后练习