字符串/数值/日期函数处理单个值,CASE WHEN 写行内逻辑,COALESCE 优雅兜底 NULL。

函数:对每一行的值做加工

聚合函数把许多行算成一个数,而普通函数作用在单行的单个值上,SELECT 里出现多少行就算多少次。

字符串函数

函数 作用 示例结果
LENGTH(s) 字符个数 LENGTH('SQL教程') → 5
UPPER(s) / LOWER(s) 大写 / 小写(英文) UPPER('sql') → SQL
SUBSTR(s, 起, 长) 截取子串,位置从 1 开始 SUBSTR('2025-01-08', 1, 4) → 2025
REPLACE(s, 旧, 新) 替换 REPLACE('a-b', '-', '/') → a/b
TRIM(s) 去两端空白

文本拼接不用函数,用两个竖线 ||:

sql
SELECT name || '(' || country || ')' AS 简介 FROM authors LIMIT 4;

数值函数

sql
SELECT title, price, ROUND(price * 0.85, 1) AS 促销价 FROM books LIMIT 5;

ROUND(x, 位数) 四舍五入,ABS(x) 取绝对值,CAST(值 AS 类型) 强制转换类型。

日期处理

示例库的日期是 TEXT 型(YYYY-MM-DD),这在 SQLite 里是常见做法,按字典序比较就是时间先后。取年份月份可以用 STRFTIME 或 SUBSTR,两者等价:

sql
SELECT order_date, STRFTIME('%Y', order_date) AS 年, SUBSTR(order_date, 1, 7) AS 年月 FROM orders LIMIT 5;

CASE WHEN:行内的 if/else

按条件给每行打标签,CASE 的两种写法里最常用的是「搜索 CASE」:

sql
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ELSE 其他结果 END

给图书按价格分档:

sql
SELECT title, price, CASE WHEN price < 40 THEN '平价' WHEN price <= 60 THEN '中等' ELSE '高价' END AS 价位 FROM books LIMIT 8;

条件从上到下依次判断,命中一个就结束;没有 ELSE 且都不满足时结果是 NULL。CASE 可以出现在 SELECT、ORDER BY、聚合函数内部等任何表达式可用的地方。

COALESCE / IFNULL:给 NULL 兜底

COALESCE(a, b, c...) 返回第一个不为 NULL 的值。phone 有空的顾客,展示时兜底成一句话:

sql
SELECT name, COALESCE(phone, '未提供') AS 联系方式 FROM customers;

IFNULL(a, b) 是两参数的简写,效果相同。

与第 3 章呼应:IS NULL 判断,COALESCE 替换——这是与 NULL 打交道的两大法宝。

小结

  • LENGTH/SUBSTR/REPLACE/UPPER/LOWER 处理文本;|| 拼接
  • ROUND/ABS/CAST 处理数值
  • CASE WHEN ... THEN ... ELSE ... END 写行内逻辑
  • COALESCE / IFNULL 把 NULL 替换成默认值

练习题

建议先在本地 SQL 环境或浏览器 SQLite 练习本中写一遍,再展开参考答案对照。

1. 没留电话显示未提供

查询顾客的 name 和联系方式:phone 不为空就显示 phone,为空显示「未提供」(列名随意)。

💡 提示:COALESCE(phone, '未提供')

查看参考答案
sql
SELECT name, COALESCE(phone, '未提供') AS 联系方式 FROM customers;

2. 图书价位分档

查询图书的 title、price 和价位:价格低于 40 显示 平价;40 到 60(含)显示 中等;60 以上显示 高价。用 CASE WHEN 实现,输出全部 16 本图书。

💡 提示:CASE WHEN price < 40 THEN '平价' WHEN price <= 60 THEN '中等' ELSE '高价' END

查看参考答案
sql
SELECT title, price, CASE WHEN price < 40 THEN '平价' WHEN price <= 60 THEN '中等' ELSE '高价' END AS 价位 FROM books;

3. 订单的年月统计

查询每张订单的 order_id 和下单年月(格式形如 2025-01 的 7 位文本,如 2025-01)。STRFTIME 或 SUBSTR 都可以。

💡 提示:SUBSTR(order_date, 1, 7) 或 STRFTIME('%Y-%m', order_date)

查看参考答案
sql
SELECT order_id, STRFTIME('%Y-%m', order_date) AS 年月 FROM orders;

4. 书名字数

查询每本书的 title 和书名字数(LENGTH 按字符计数,中文也算 1 个字符)。

💡 提示:LENGTH(title)

查看参考答案
sql
SELECT title, LENGTH(title) AS 字数 FROM books;

5. 各分类平均评分

统计每个分类的平均评分,保留 1 位小数(ROUND);没有评分书的分类(AVG 结果为 NULL 的极端情况)在本题中直接输出 NULL 即可,输出 category 和平均评分。

💡 提示:SELECT category, ROUND(AVG(rating), 1) FROM books GROUP BY category;

查看参考答案
sql
SELECT category, ROUND(AVG(rating), 1) FROM books GROUP BY category;

6. 作者档案

查询每位作者的 name、国籍、出生年:国籍为 NULL 显示 未知,出生年为 NULL 显示 不详。

💡 提示:COALESCE(country, '未知') 和 COALESCE(birth_year, '不详')

查看参考答案
sql
SELECT name, COALESCE(country, '未知') AS 国籍, COALESCE(birth_year, '不详') AS 出生年 FROM authors;

涉及的数据表

  • books
  • authors
  • customers
  • orders

由在线工具箱(www.vba.net)整理制作