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

三个最常用的杂项：cast() 转类型、if() 三目、ifnull() 兜空。 cast 有一处必须记住：CAST(3.7 AS INTEGER) 这里给 3（砍掉小数）， MySQL 的 CAST(3.7 AS SIGNED) 给 4（四舍五入）——同一个意思不同结果。 Demo 里 if(height >= 170, '高个', '普通') 按身高分类； ifnull(phone, '未填') 把 null 换成文字。

📝 本章和前面几章的关系

上一篇查环境，这一篇是「格式转换与取值选择」——报表里天天用的三个。 它们的共同点是「只影响这次查询的输出」，都不改表里的数据。 要真把 null 改掉，得 update（第 7 章讲）。

一、cast：强制转换类型

写法：cast(值 as 类型)。 常用类型这里写 INTEGER／REAL／TEXT，MySQL 写 SIGNED／DECIMAL／CHAR／DATE。 教材里的 CAST(x AS CHAR) 在本沙箱要写成 CAST(x AS TEXT)。 字符串转数字是「能读多少读多少」：'12abc' 给 12，读不动就停。 小数转整数有一处两边不一样：这里（SQLite）直接砍， 3.7 给 3；MySQL 的 SIGNED 是四舍五入，给 4。 最常用的场景其实是排序：字符串 '10' 会排在 '9' 前面， 因为按字面比 1 小于 9。 转成数字才不会。 这一招在清洗「本该是数字却存成文本」的列时天天用。

二、if：三目运算

if(条件, 真值, 假值)，跟编程语言里的三目运算一模一样。 条件是 0 或 null 都走假分支。 它比 case when 短，但只能处理两种情况。 三种以上就得用 case when——那个是标准 SQL， MySQL 和这里都支持。 注意 if 是 MySQL 的方言，标准 SQL 里没有（标准写法是 case when）。 跨库的代码优先用 case when，兼容性更好。

三、ifnull 与 coalesce：null 兜底

ifnull(值, 兜底)：值为 null 就给兜底，否则原样返回。 它只接两个参数。 coalesce 能接一串：coalesce(a, b, c) 给「第一个不是 null 的」。 两个以上备选就用 coalesce，只有一个备用时 ifnull 更直观。 它俩都不改表里的数据，只影响这次查询的输出。 要真把 null 改掉，得 update。 这一点很多人会搞混：查询里兜了底，就以为表里已经补上了。

四、三个合起来：一条报表语句

ifnull 补空、if 分类、cast 转显示格式——这三个经常一起出现在同一条 select 里。 但要记住第 23 课那条红线： 这些函数一旦包在 where 的列上，索引就废了。 放在 select 里造列没问题，放在 where 里要当心。 一个折中办法：如果确实要按「分类结果」筛， 先把筛选条件写成能用到索引的形式，再在 select 里做展示。 筛选用原值，展示用函数—— 这是最稳的分工。

五、动手 Demo：转类型、分两类、兜空值

Demo 五段：cast 的两种方向、if 分类、ifnull 兜空，最后三个合起来用。 第一段的 3.7 → 3 是这一篇最该带走的一格，因为 MySQL 上同一句给 4。

import _q46 as Q
c = Q.con()

# cast：小数转整数，这里砍、MySQL 四舍五入
c.execute("SELECT CAST('12abc' AS INTEGER) AS a, "
          "CAST(3.7 AS INTEGER) AS b, CAST(3.2 AS INTEGER) AS c")
c.execute("SELECT CAST(12 AS TEXT) AS a, CAST('3.5' AS REAL) AS b")
# if：三目运算
c.execute("SELECT name, height, "
          "IF(height >= 170, '高个', '普通') AS tag "
          "FROM students ORDER BY height DESC LIMIT 6")
# ifnull 与 coalesce
c.execute("SELECT IFNULL(NULL, '默认') AS a, "
          "IFNULL('有值', '默认') AS b")
c.execute("SELECT name, IFNULL(phone, '未填') AS phone "
          "FROM students WHERE phone IS NULL")
# 三个一起用
c.execute("SELECT name, IFNULL(phone, '未填') AS phone, "
          "IF(height >= 170, '高个', '普通') AS tag, "
          "CAST(height AS TEXT) AS h FROM students LIMIT 3")

实跑输出：

--------------------------------------------------------------------
第三十课：cast、if、ifnull，三个最常用的杂项函数
--------------------------------------------------------------------

  cast：强制转换类型，小数这一处两边不一样
  SQL> SELECT CAST('12abc' AS INTEGER) AS a, CAST(3.7 AS INTEGER) AS b,
       CAST(3.2 AS INTEGER) AS c
  a      b     c
  ---------------
  12     3     3
  （1 行）

  **`'12abc'` 转成 12**——能读多少读多少，读不动就停。
  **`3.7` 转成 3、`3.2` 也转成 3**，因为这里（SQLite）转整数是「直接砍掉小数」。
  **MySQL 的 `CAST(3.7 AS SIGNED)` 给 4**——它是四舍五入，不是砍。
  **同一个意思、同一个写法，两个引擎给不同的数，这是很典型的一处。**
  **类型名也不一样**：这里写 `INTEGER`／`REAL`／`TEXT`，
  **MySQL 写 `SIGNED`／`DECIMAL`／`CHAR`／`DATE`。**
  **教材里的 `CAST(x AS CHAR)` 在本沙箱要写成 `CAST(x AS TEXT)`。**

  SQL> SELECT CAST(12 AS TEXT) AS a, CAST('3.5' AS REAL) AS b
  a      b
  ------------
  12     3.50
  （1 行）

  **数字转字符串、字符串转小数，都正常。** **`cast` 最常用的场景其实是「排序时把字符串当数字」**——
  **字符串 `'10'` 排在 `'9'` 前面，转成数字才不会。**

  if：三目运算，SQL 里的写法
  SQL> SELECT name, height, IF(height >= 170, '高个', '普通') AS tag FROM students
       ORDER BY height DESC LIMIT 6
  name     height     tag
  ---------------------------
  黄磊     180.20     高个
  刘洋     178.30     高个
  杨帆     175.10     高个
  周涛     174.00     高个
  张伟     172.50     高个
  徐明     169.70     普通
  （6 行）

  **`if(条件, 真值, 假值)`，跟编程语言里的三目运算一样。** **条件是 0 或 null 都走假分支。**
  **这比写 `case when` 短，但只能处理两种情况。** **三种以上就得用 `case when`**，那个是标准 SQL，
  MySQL 和这里都支持。

  ifnull 与 coalesce：null 兜底
  SQL> SELECT IFNULL(NULL, '默认') AS a, IFNULL('有值', '默认') AS b
  a        b
  --------------
  默认     有值
  （1 行）

  SQL> SELECT name, IFNULL(phone, '未填') AS phone FROM students WHERE phone IS NULL
  name     phone
  ---------------
  孙悦     未填
  （1 行）

  **`ifnull(值, 兜底)`：值为 null 就给兜底，否则原样返回。** **它只接两个参数。**
  **`coalesce` 能接一串**：`coalesce(a, b, c)` 给「第一个不是 null 的」。
  **两个以上备选就用 `coalesce`，只有一个备选用 `ifnull` 更直观。**
  **它俩都不改表里的数据，只影响这次查询的输出。** **要真把 null 改掉，得 `update`。**

  三个函数合起来用：一条报表语句
  SQL> SELECT name, IFNULL(phone, '未填') AS phone, IF(height >= 170, '高个', '普通') AS tag,
       CAST(height AS TEXT) AS h FROM students LIMIT 3
  name     phone           tag      h
  ----------------------------------------
  张伟     13800001111     高个     172.5
  王芳     13800002222     普通     160.2
  李娜     13800003333     普通     165.0
  （3 行）

  **这三个函数经常一起出现在报表里**：`ifnull` 补空、`if` 分类、`cast` 转显示格式。
  **但记住第 23 课那条**：
  **这些函数一旦包在 `where` 的列上，索引就废了。** 放在 `select` 里造列没问题，
  放在 `where` 里要当心。

第一段 cast。 '12abc' 给 12，3.7 和 3.2 都给 3——这里转整数是砍，不是四舍五入。 第二段反向转换。 12 转成 '12'，'3.5' 转成 3.50。 第三段 if 分类。 前五个「高个」， 第六个徐明 169.70 是「普通」——两类都出现了，一眼能看出分界在哪儿。 第四段 ifnull。 NULL 换成「默认」，有值原样返回。 第五段三个一起用。 前三个学生：都没有 null 需要兜底， 所以看的是 tag 和 h 这两列——h 是转成文本的身高。

30 秒实验

  1. CAST(3.7 AS INTEGER) 在本沙箱和 MySQL 分别给什么？
  2. IF(NULL, 'a', 'b') 给哪一个？为什么？
  3. IFNULL(NULL, NULL) 给什么？COALESCE(NULL, NULL) 呢？

常见误区

  1. 以为 cast 转整数是四舍五入。 这里砍、MySQL 进，同一句 SQL 两边差 1，而且不报错。
  2. 在 where 里用 ifnull(phone, '') = '' 找空值。 索引失效， 而且语义上该直接写 phone IS NULL。
  3. 在查询里兜了底，就以为表里补上了。 函数只影响输出，改数据得 update。
自测三问（点开对答案） 1. `CAST(3.7 AS INTEGER)` 在本沙箱和 MySQL 分别给什么？ —— 这里给 3（砍掉小数），MySQL 的 `CAST(3.7 AS SIGNED)` 给 4（四舍五入）。 2. `ifnull` 和 `coalesce` 有什么区别？ —— `ifnull` 只接两个参数；`coalesce` 可以接一串，返回第一个非 null 的。 3. 为什么字符串排序前要先 `cast` 成数字？ —— 字符串按字面比，`'10'` 排在 `'9'` 前面；转成数字才是数值序。

内置函数这一章到此结束。下一篇换方向——insert、update、delete，开始改数据。

相关笔记