函数是「一进一出」的加工机：喂一列，还一列，表里的数据不动。
这一章过三类最常用的——文本、日期、数值，再补一个能自己造函数的例子。
Demo 里 YEAR() 在 SQLite 上不存在，得用 strftime('%Y', 列)；
ROUND(2.5) 给 3，而本沙箱的 Python 给 2——同一句舍入，两个引擎差一分钱；
SOUNDEX 干脆没有，现场用 Python 补了一个，查拼错的 Y Lie 照样命中 Y Lee。
上一章的计算字段靠运算符，这一章靠函数——同样是造新列，但函数比运算符危险得多， 因为它没有统一标准。 函数是全书移植性最差的一块，第 2 章讲「同一件事不同数据库四种写法」时埋的伏笔， 到这一章正式兑现。
一、函数：好用，但不可移植
函数的输入输出都很干净：UPPER(vend_name) 进去小写、出来大写，
表里一个字节都没变。 它只影响这一次的查询结果。
问题在于没有统一标准：取年份，MySQL 写 YEAR()，SQLite 写 strftime()，Oracle 又不一样。
SQL 语句本身可移植，函数往往不可移植。
所以教材的态度是：能用标准写法就用标准写法，非用函数不可时，
把函数调用集中在一处（视图或应用层），换库时只改那一处。
二、文本函数
四个最常用的：UPPER／LOWER 改大小写、LENGTH 量长度、SUBSTR(列, 起点, 长度) 截取、
TRIM 去两头空格。
SUBSTR 从 1 开始数，不是 0——和大多数编程语言相反，这是最容易错的一位，
写 0 会得到空串或原串（看引擎）。
TRIM 只去两头，中间的空格不动；要连中间一起处理得配合 REPLACE(列, ' ', '')。
还有一个坑：SQL 里连续空格在有些客户端会显示成一个，看长度别看空格数。
三、日期函数
这是移植性最差的一块：MySQL 有 YEAR()、MONTH()、DAY()、DATE()、NOW()，
SQLite 只有一个 strftime(格式, 值) 加 date()、datetime()、julianday()。
对照着记：YEAR(列) 对应 strftime('%Y', 列)；MONTH 对应 %m；DAY 对应 %d；
%w 是星期几（0 是周日）。格式串里的字母是占位符，别当普通字符。
教材那个「九月订单」的例子给了两种写法：按年月筛（strftime('%Y-%m', ...) = '2005-09'），
或按区间筛（DATE(order_date) BETWEEN '2005-09-01' AND '2005-09-30'）。
两种写法行数必须一样，这才说明你写对了。
日期存成字符串时，能不能直接比大小全看格式：本课的 2005-09-01 是零填充的 ISO 格式，
字符串比较恰好等于时间比较；写成 2005-9-1 立刻就乱，因为 '1' > '0'。
四、数值函数
ROUND(值, 位数) 是第二参数保留几位小数，ABS 取绝对值，还有 SQRT、MOD、CEIL、
FLOOR 这一组。
ROUND 的坑在「.5 往哪边倒」：SQLite 一律往绝对值大的方向进（2.5 给 3、-2.5 给 -3），
Python 的 round 往偶数那边靠（2.5 给 2）。同一句舍入，两个引擎给出不同的数。
算钱的时候这条差异会真的差一分钱，所以金额要么全程用整数分，要么在应用层统一舍入规则。
别指望数据库的 ROUND 跨库一致。
五、SOUNDEX 与自建函数
SOUNDEX 把名字按读音编成四位码：首字母保留，后面的辅音按发音分组，元音和 h／w 丢掉，
不够四位补 0。所以 Lee 与 Lie 同码。
用途是「拼错了也能搜到」：WHERE SOUNDEX(cust_contact) = SOUNDEX('Y Lie') 能命中 Y Lee。
用户记不住拼写的时候，读音比字母靠谱。
SQLite 没有 SOUNDEX，Demo 现场注册了一个 Python 实现：
con.create_function('SOUNDEX', 1, soundex)。
这一节的真正含义不是背函数，而是「函数可以自己造」：引擎缺什么就补一个。
代价是可移植性更差——换库时这个函数得跟着搬。
六、小结
教材小结：函数分文本、日期、数值三类，好用但不可移植，日期最甚。
本课补三条：一是 SUBSTR 从 1 开始数；二是 ROUND 的 .5 各引擎方向不同，算钱别依赖它；
三是引擎缺函数可以自己注册，但要记账。
七、动手 Demo：三类函数各跑一遍，再补一个缺的
Demo 五段：先证明函数不改原表，再过文本函数、日期函数、数值函数， 最后现场补一个 SQLite 没有的 SOUNDEX。 日期那段有两种写法互相验算，行数一样才算写对； 数值那段把 SQLite 和 Python 的舍入放在一起对比。
import sqlite3
con = sqlite3.connect(":memory:")
# 文本函数：大写、截取、长度
con.execute("SELECT vend_name, UPPER(vend_name) AS up FROM vendors")
con.execute("SELECT prod_name, SUBSTR(prod_name, 1, 3) AS head3, "
"LENGTH(prod_name) AS len FROM products LIMIT 5")
# 日期函数：SQLite 用 strftime，MySQL 用 YEAR()、MONTH()
con.execute("SELECT order_num, strftime('%Y', order_date) AS y FROM orders")
con.execute("SELECT order_num FROM orders "
"WHERE strftime('%Y-%m', order_date) = '2005-09'")
con.execute("SELECT order_num FROM orders "
"WHERE date(order_date) BETWEEN '2005-09-01' AND '2005-09-30'")
# 数值函数：ROUND 的 .5 往绝对值大的方向进
con.execute("SELECT ROUND(2.5) AS a, ROUND(3.5) AS b, ROUND(-2.5) AS c")
# SOUNDEX：SQLite 没有，注册一个 Python 实现
con.create_function("SOUNDEX", 1, soundex)
con.execute("SELECT cust_name, cust_contact FROM customers "
"WHERE SOUNDEX(cust_contact) = SOUNDEX('Y Lie')")
实跑输出：
--------------------------------------------------------------------
第十一课：使用数据处理函数
--------------------------------------------------------------------
【一】函数：输入一列，输出一列，表里的数据不动
SQL> SELECT vend_name, UPPER(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name
vend_name vend_name_upcase
------------------------------------
ACME ACME
Anvils R Us ANVILS R US
Furball Inc. FURBALL INC.
Jet Set JET SET
Jouets Et Ours JOUETS ET OURS
LT Supplies LT SUPPLIES
（6 行）
**右手那列全大写了，但表里的 vend_name 一个字母都没变。**
**函数只影响这一次的查询结果。**
**教材反复提醒：函数不像 SQL 本身那么可移植。**同一个「取年份」，MySQL 写 `YEAR()`，
SQLite 写 `strftime()`，**换库就得改代码**——这是函数最大的代价。
【二】文本函数：大小写、长度、截取、去空格
SQL> SELECT prod_name, SUBSTR(prod_name, 1, 3) AS head3,
LENGTH(prod_name) AS len FROM products ORDER BY prod_name LIMIT 5
prod_name head3 len
-------------------------------------
.5 ton anvil .5 12
1 ton anvil 1 t 11
2 ton anvil 2 t 11
Bird seed Bir 9
Carrots Car 7
（5 行）
**SUBSTR 从 1 开始数，不是 0**——和大多数编程语言相反，这是最容易错的一位。
SQL> SELECT LOWER('ABC') AS low, TRIM(' x ') AS trimmed
low trimmed
----------------
abc x
（1 行）
**显示的时候连续空格被压成一个了**，但函数处理的是真的两空格——下面用 LENGTH 量给你看。
SQL> SELECT LENGTH(' x ') AS raw_len, LENGTH(TRIM(' x ')) AS trim_len
raw_len trim_len
---------------------
5 1
（1 行）
**5 对 1**：原始串两边各两个空格加中间一个 x，TRIM 之后只剩 x。
**TRIM 只去两头，中间的空格不动**；要连中间一起处理得配合 REPLACE。
【三】日期函数：这是移植性最差的一块
SQL> SELECT order_num, order_date, strftime('%Y',
order_date) AS y FROM orders ORDER BY order_num
order_num order_date y
----------------------------------------
20005 2005-09-01 2005
20006 2005-09-12 2005
20007 2005-09-30 2005
20008 2005-10-03 2005
20009 2005-10-08 2005
（5 行）
**教材原码是 `YEAR(order_date)`**，MySQL 的写法；
SQLite 没有 YEAR()，用 `strftime('%Y', 列)` 取年份。
**取月份、取日、取星期同理**：`%m` 月份、`%d` 日、`%w` 星期几（0 是周日）。
SQL> SELECT order_num, order_date FROM orders WHERE strftime('%Y-%m', order_date) = '2005-09'
order_num order_date
-------------------------
20005 2005-09-01
20006 2005-09-12
20007 2005-09-30
（3 行）
**2005 年 9 月有 3 张单**，这就是教材那个「九月订单」的例子。
教材还给了另一种写法：`WHERE DATE(order_date) BETWEEN '2005-09-01' AND '2005-09-30'`，
**MySQL 的 DATE() 是截掉时分秒，SQLite 的 date() 一样能截。**
SQL> SELECT order_num,
order_date FROM orders WHERE date(order_date) BETWEEN '2005-09-01' AND '2005-09-30'
order_num order_date
-------------------------
20005 2005-09-01
20006 2005-09-12
20007 2005-09-30
（3 行）
**两种写法行数必须一样，这才说明你写对了。**
**还有个坑：日期存成字符串时，能不能直接比大小全看格式。**
本课的 `2005-09-01` 是零填充的 ISO 格式，字符串比较恰好等于时间比较；
**写成 `2005-9-1` 立刻就乱。**
【四】数值函数：四舍五入的「半数往哪边倒」
SQL> SELECT prod_price, ROUND(prod_price * 0.9,
2) AS sale_price FROM products ORDER BY prod_price DESC LIMIT 4
prod_price sale_price
--------------------------
55.00 49.50
50.00 45.00
14.99 13.49
13.00 11.70
（4 行）
**ROUND(值, 位数)**，第二参数是保留几位小数。
SQL> SELECT ROUND(2.5) AS a, ROUND(3.5) AS b, ROUND(-2.5) AS c, ABS(-7) AS d
a b c d
------------------------------
3.00 4.00 -3.00 7
（1 行）
**SQLite 给 3、4、-3：遇到 .5 一律往绝对值大的方向进。**
**本沙箱的 Python 给的是 2 / 4 / -2**，遇到 .5 往偶数那边靠（银行家舍入）。
**同一句 ROUND，两个引擎给出不同的数。**
**算钱的时候这条差异会真的差一分钱**，所以金额要么全程用整数分，
要么在应用层统一舍入规则——**别指望数据库的 ROUND 跨库一致。**
【五】SOUNDEX：本沙箱没有，自己补一个
SQL> SELECT cust_contact, SOUNDEX(cust_contact) AS sx FROM customers ORDER BY cust_id
cust_contact sx
------------------------------
Y Lee Y400
Jerry Mouse J652
Jim Jones J525
Y Sam Y250
E Fudd E130
（5 行）
**SOUNDEX 把名字按读音编成四位码**，读音相近的码相同。
**SQLite 没有 SOUNDEX**，这一列的码是 Demo 现场用 Python 实现的：
首字母保留，后面的辅音按发音分组（b/f/p/v 一组、c/g/j/k/q/s/x/z 一组……），元音和 h/w 丢掉，
不够四位补 0。
SQL> SELECT cust_name,
cust_contact FROM customers WHERE SOUNDEX(cust_contact) = SOUNDEX('Y Lie')
cust_name cust_contact
-----------------------------
Coyote Inc. Y Lee
（1 行）
**查 'Y Lie' 命中 'Y Lee'**——这就是教材那个例子：**用户把姓拼错了，读音搜索照样找得到。**
**这节的真正含义不是「背函数」，而是「函数可以自己造」**：引擎缺什么，注册一个就行，
代价是可移植性更差。
第一段 UPPER。 右手那列全大写，左边的 vend_name 一个字母没变。
第二段文本三件套。 SUBSTR(prod_name, 1, 3) 取头三个字符，
.5 ton anvil 取出来是 .5——从 1 开始数才可能取到点号。
第三段日期。 strftime('%Y', order_date) 给 2005，换成 %Y-%m 就只剩九月那 3 张单。
第四段数值。 ROUND(2.5) 给 3、ROUND(-2.5) 给 -3，Python 对应给 2 和 -2，
同一句两个答案。
第五段 SOUNDEX。 五个联系人各有一个四位码，
查 Y Lie 命中 Y Lee——读音相同，拼写不同，照样找得到。
30 秒实验
- 把
UPPER换成LOWER，看六家供应商的名字变成什么样。 - 用
strftime('%m', order_date)筛出十月的订单，应该有几张？ - 把
SOUNDEX('Y Lie')换成SOUNDEX('Jery Mouse')，还能命中 Jerry Mouse 吗？
常见误区
- 以为
SUBSTR从 0 开始。SQL 里是从 1 开始，取第一个字符要写 1。 - 拿字符串日期直接比大小。只有零填充的 ISO 格式能这么干，
2005-9-1会排错。 - 以为 ROUND 全宇宙一致。SQLite 的 .5 往大进，Python 的 .5 往偶数靠，算钱别依赖它。
自测三问（点开对答案）
1. 为什么教材说函数「不像 SQL 本身那么可移植」？ —— SQL 语法有标准，函数各库自己定：取年份 MySQL 用 YEAR()、SQLite 用 strftime()， 换库时这部分代码必须改。 2. `SUBSTR('abcdef', 2, 3)` 返回什么？ —— 从第 2 个字符开始取 3 个，返回 bcd；SQL 的下标从 1 开始，不是 0。 3. 日期存成字符串，什么情况下能直接比大小？ —— 只有零填充的定宽格式（如 2005-09-01）才行，因为字典序恰好等于时间序； 2005-9-1 这种不定宽格式会排错。函数能把一列加工成另一列， 但它们都是「一行进、一行出」。 下一章换个方向：多行进、一行出——那叫聚集。
- ☐ （本章完，下一章 第12章 汇总数据）