波仔
🦏 第6章 内置函数
📄 一句话概括

函数就是「输入进、输出出」的黑盒：函数名(参数, …)，返回值当一列用。 参数个数看函数，pi() 一个都不要，replace() 要三个。 三个位置都能放：select 里造列、where 里当条件、order by 里排序。 函数还能套函数：round(avg(height), 2)。 但有个代价：WHERE YEAR(birthday) = 2006 把每一行都改了， 索引按原始值排的，算完再去比就用不上。 换成范围比较，结果一样、索引能用。

📝 本章和前面几章的关系

前五章学的都是「子句」——怎么写条件、怎么分组、怎么排序。 从这一篇起换一套工具：函数。 函数是 SQL 里最像「编程语言」的部分，也是最容易踩「两个引擎行为不同」的地方。 这一章每一篇都会标出「这里和 MySQL 差在哪」。

一、函数的三个要素

函数名、参数、返回值。 写法统一是 函数名(参数, 参数, …)，abs(-3.5) 给 3.50、 round(3.14159, 2) 给 3.14、upper('abc') 给 ABC。 返回值能当一列用，所以最常见的位置就是 select 后面。 也能放进 where 和 order by，那里它就当一个值参与比较。 参数个数不是固定的：pi() 零个、abs() 一个、round() 一到两个、replace() 三个。 记不住就去查手册，这不是该硬背的东西。

二、函数能出现在哪三个位置

select 后面：造一列。SELECT ROUND(height / 100, 2) AS m 给「身高换算成米」。 where 里面：当条件。WHERE YEAR(birthday) = 2006 给两个 2006 年出生的。 order by 后面：按算出来的值排。ORDER BY m DESC 就是按换算后的米数排。 这三个位置覆盖了绝大多数用法。 剩下还有 group by 和 having， 原理一样：函数先算出一个值，再拿这个值去干那件事。

三、函数能套函数

SELECT ROUND(AVG(height), 2) ——avg 先算出 168.4416…，round 再截到两位，给 168.44。 这就是「函数套函数」。 套的顺序是从里往外算，跟数学里的括号一样。 套太多层会难读，超过三层就该考虑拆成子查询。 唯一的禁区：聚合函数不能套聚合函数（max(count(*)) 报错）。 但聚合套普通函数、普通函数套聚合都可以。

四、代价：where 里给列包了函数，索引就废了

WHERE YEAR(birthday) = 2006 看着自然，但它把每一行都改了再比。 索引是按 birthday 的原始值排好的，你算完年份再去比，索引不知道自己该查哪儿。 换成范围比较：WHERE birthday >= '2006-01-01' AND birthday < '2007-01-01'， 结果一模一样，但 birthday 上真有索引就能用。 这是 SQL 优化的第一条常识： 函数别包在「要查索引的那一列」外面。 左边随便包（YEAR(now())），右边别包。 这一条在第 4 章讲 where 时没提，是因为那时还没函数。

五、怎么查函数：手册比记忆可靠

MySQL 有 HELP 'ROUND'，命令行里直接给语法和例子；图形工具里也能右键看文档。 本沙箱没有这套帮助系统。 所以这一章的每个函数都在 Demo 里跑给你看。 看得见的输出，比背下来的语法靠谱。 真正该记住的是「有没有这个函数」，不是它的第三个参数能不能省。 查手册三十秒，硬背记错三天。

六、动手 Demo：从三类各一个，到撞上索引那条红线

Demo 五段：先三类各来一个，再把函数放到 where、select、order by 三个位置，然后套两层。 最后一段是重点：同一个需求两种写法，结果一样，但一种能用索引、一种不能。

import _q46 as Q
c = Q.con()

# 三类各来一个
c.execute("SELECT ABS(-3.5) AS a, ROUND(3.14159, 2) AS b, "
          "UPPER('abc') AS c")
# 函数出现在 where 里
c.execute("SELECT name FROM students WHERE YEAR(birthday) = 2006")
# 函数出现在 select 与 order by 里
c.execute("SELECT name, ROUND(height / 100, 2) AS m FROM students "
          "ORDER BY m DESC LIMIT 3")
# 函数套函数
c.execute("SELECT ROUND(AVG(height), 2) AS avg_h FROM students")
# 换成范围比较：结果一样，但索引能用
c.execute("SELECT name FROM students "
          "WHERE birthday >= '2006-01-01' "
          "AND birthday < '2007-01-01'")

实跑输出：

--------------------------------------------------------------------
第二十三课：函数是「输入进、输出出」的黑盒
--------------------------------------------------------------------

  三类各来一个：一个输入一个输出
  SQL> SELECT ABS(-3.5) AS a, ROUND(3.14159, 2) AS b, UPPER('abc') AS c
  a        b        c
  ----------------------
  3.50     3.14     ABC
  （1 行）

  **`abs` 是数学、`round` 是数学、`upper` 是字符串**——**参数放括号里，返回值当一列用。**
  **函数的写法都一样**：`函数名(参数, 参数, …)`。
  **参数个数看函数**，`pi()` 一个都不要，`replace()` 要三个。

  三个位置都能放：select、where、order by
  SQL> SELECT name FROM students WHERE YEAR(birthday) = 2006
  name
  -----
  陈静
  黄磊
  （2 行）

  **函数出现在 `where` 里，就是「先算再比」**——把生日算出年份，再跟 2006 比。
  两个 2006 年出生的都出来了。

  SQL> SELECT name, ROUND(height / 100, 2) AS m FROM students ORDER BY m DESC LIMIT 3
  name     m
  --------------
  黄磊     1.80
  刘洋     1.78
  杨帆     1.75
  （3 行）

  **函数出现在 `select` 里就是造一列，出现在 `order by` 里就是按算出来的值排。**

  函数能套函数：一层套一层
  SQL> SELECT ROUND(AVG(height), 2) AS avg_h FROM students
  avg_h
  -------
  168.44
  （1 行）

  **`avg` 先算出 168.4416…，`round` 再把它截到两位。**
  **聚合函数和普通函数能套着用，注意别把聚合套进聚合就行。**

  坏消息：where 里给列包了函数，索引就废了
  **`WHERE YEAR(birthday) = 2006` 看着自然，但它把每一行都改了。**
  **索引是按 `birthday` 的原始值排的，你算完年份再去比，索引不知道自己该查哪儿。**

  SQL> SELECT name FROM students WHERE birthday >= '2006-01-01' AND birthday < '2007-01-01'
  name
  -----
  陈静
  黄磊
  （2 行）

  **换成范围比较，结果一模一样，但 `birthday` 上真有索引就能用上。**
  **这是 SQL 优化的第一条常识：函数别包在「要查索引的那一列」外面。**

  函数怎么查：手册比记忆可靠
  **MySQL 有 `HELP 'ROUND'`，图形工具里也能右键看文档。** **本沙箱没有这套帮助系统**，
  所以这一篇的每个函数都在 Demo 里跑给你看。
  **记不住参数个数很正常。** **要紧的是记住「有没有这个函数」，细节查手册**——
  而不是硬背 `substring` 的第三个参数省不省。

第一段三个函数。 abs 给 3.50、round 给 3.14、 upper 给 ABC——数学、数学、字符串，写法完全一样。 第二段 where 里。 2006 年出生的两个：陈静、黄磊。 第三段 select 与 order by。 身高换算成米，按米数排，前三名 1.80／1.78／1.75。 第四段套两层。 avg 算出 168.4416…，round 截成 168.44。 第五段换范围。 同样是陈静、黄磊，但这次 birthday 没被函数包住，索引能干活。

30 秒实验

  1. UPPER('abc') 与 UPPER(name)（name 是列）用法有什么不同？
  2. 把 WHERE YEAR(birthday) = 2006 改成 LIKE '2006%'，能查出同样的两个人吗？
  3. ROUND(AVG(height), 2) 里如果把 round 去掉，输出会是什么样？

常见误区

  1. 在 where 里给要查索引的列包函数。 YEAR(birthday) = 2006 逻辑没错，但索引完全用不上， 几百万行就是全表扫。
  2. 硬背参数个数。 函数太多，记混了写出来就是错，查手册比试错快。
  3. 函数套太多层。 三层以上基本没人读得懂，该拆成子查询。
自测三问（点开对答案） 1. 函数的返回值能放在哪几个位置？ —— `select`（造列）、`where`（当条件）、`order by`（按值排），还有 `group by` 和 `having`。 2. 为什么 `WHERE YEAR(birthday) = 2006` 用不上索引？ —— 索引按 `birthday` 原始值排序，函数把列改了之后索引无法定位。改成范围比较就能用。 3. 聚合函数能套普通函数吗？反过来呢？ —— 都能。`round(avg(x), 2)` 合法；`max(count(*))` 这种「聚合套聚合」报错。

总览看完，下一篇开始逐个过——先是数学函数，重点在 round 和 truncate 的差别。

相关笔记