一、四则运算：列能算，常量和列也能算

语法就是在 select 后面写表达式：qty * price、price * 0.8、stock - 40。 + - * / 四个都能用，还能加括号。 算出来的东西没有名字，所以照例起别名。 不起别名的话，表头就是一长串表达式，导出去没法看。 用在 where 里也合法：WHERE price * 0.8 > 100 是「打完八折还过 100 的」。 但这种写法用不上索引，数据量大时要改成「把条件反过来算」。

二、优先级：先乘除后加减，括号最优先

3 + 4 * 2 给 11，(3 + 4) * 2 给 14。 跟小学数学一模一样，不用记特例。 但有个反直觉的地方：and 的优先级高于 or，而比较运算又高于 and。 混在一起时括号不是可选项，是必需品。

三、除法：这一处两个引擎给的数不一样

本沙箱（SQLite）7 / 2 给 3，因为两边都是整数，它做整数除法。 MySQL 给 3.5000，它的 / 永远给小数。 MySQL 要整除得写 7 DIV 2，取余写 7 % 2 或 MOD(7, 2)。 本沙箱没有 DIV，这条是教材原码，不在这里执行。 想在这边拿到 3.5，把任一边写成小数（7 / 2.0），或者 CAST(7 AS REAL) / 2。 写 SQL 时凡是除法，都要先想清楚要整数还是要小数。

四、三个「不报错但结果不对」

一、除零：10 / 0 给 NULL，不报错。 MySQL 默认也不报错，只记一条警告； 开了严格模式才报错。 所以「除以零」会悄悄变成一个空值流下去。 二、字符串参与算术：'abc' + 1 给 1，'3abc' + 1 给 4。 规则是「能读多少数字算多少，读不到就算 0」。 MySQL 结果一样，但会记警告—— 这就是隐式转换最坑的地方。 三、null 参与算术：NULL + 1 还是 NULL。 任何一边是 null，整个表达式就是 null，凑不出别的结果。

五、浮点数：为什么钱不能用 float 存

0.1 + 0.2 给 0.30000000000000004。 这不是数据库的错，是二进制浮点的固有性质——0.1 在二进制里是无限循环小数。 更值得看的是这一组：明细逐行算 qty * price 再求和，给 1969.1000000000001； 而订单表里存的 amount 是 1969.1。 同一笔钱，两条路算出两个数。 所以钱一律用定点小数：MySQL 的 DECIMAL(10,2) 存的是十进制， 不会长尾巴。 本沙箱没有 DECIMAL（只能退化成浮点），上面那点误差就是这么露出来的。

六、动手 Demo：从明细算总价，一路撞到浮点误差

Demo 七段：算总价、打折、比优先级、试除法、撞除零、喂字符串、看浮点。 每一段都只有一句 SQL，重点是盯着输出里的数字对不对。

import _q46 as Q
c = Q.con()

# 明细算总价：qty * price
c.execute("SELECT id, qty, price, qty * price AS total "
          "FROM order_detail LIMIT 4")
# 打折、扣库存
c.execute("SELECT name, price, price * 0.8 AS sale FROM goods "
          "ORDER BY price DESC LIMIT 3")
c.execute("SELECT name, stock, stock - 40 AS after_sale FROM goods "
          "WHERE stock > 0 LIMIT 3")
# 优先级：先乘除后加减
c.execute("SELECT 3 + 4 * 2 AS no_paren, (3 + 4) * 2 AS with_paren")
# 除法：这里给 3，MySQL 给 3.5000
c.execute("SELECT 7 / 2 AS a, 7 / 2.0 AS b, 7 % 2 AS c")
# 除零与 null：都不报错
c.execute("SELECT 10 / 0 AS div_zero, 10 % 0 AS mod_zero, "
          "NULL + 1 AS null_add")
# 字符串参与算术：自动转数字
c.execute("SELECT 'abc' + 1 AS s1, '3abc' + 1 AS s2")
# 浮点误差
c.execute("SELECT CAST(0.1 + 0.2 AS TEXT) AS float_err")
c.execute("SELECT CAST(SUM(qty * price) AS TEXT) AS sum_detail, "
          "CAST((SELECT SUM(amount) FROM orders) AS TEXT) AS sum_order "
          "FROM order_detail")

实跑输出：

--------------------------------------------------------------------
第十五课：查的时候顺手算——算术运算
--------------------------------------------------------------------

  四则运算：列和列能算，列和数字也能算
  SQL> SELECT id, qty, price, qty * price AS total FROM order_detail LIMIT 4
  id     qty     price      total
  ---------------------------------
  1      1       128.00     128.00
  2      2       15.80      31.60
  3      1       89.90      89.90
  4      1       299.00     299.00
  （4 行）

  **`qty * price` 是查的时候现算的，表里没有 `total` 这一列。**
  **四个运算符 `+ - * /` 都能用，还能加括号。**

  打折、扣库存：乘一个数、减一个数
  SQL> SELECT name, price, price * 0.8 AS sale FROM goods ORDER BY price DESC LIMIT 3
  name       price      sale
  -----------------------------
  运动鞋     299.00     239.20
  足球       156.00     124.80
  篮球       128.00     102.40
  （3 行）

  SQL> SELECT name, stock, stock - 40 AS after_sale FROM goods WHERE stock > 0 LIMIT 3
  name         stock     after_sale
  ----------------------------------
  篮球         50        10
  足球         30        -10
  羽毛球拍     80        40
  （3 行）

  **第二段第二个数出现负数（足球 30 减 40 得 -10）**——
  **算术运算不会替你检查业务合理性，该拦的地方要用 `where` 或程序判。**

  优先级：先乘除后加减，括号最优先
  SQL> SELECT 3 + 4 * 2 AS no_paren, (3 + 4) * 2 AS with_paren
  no_paren     with_paren
  ------------------------
  11           14
  （1 行）

  **11 和 14，差在括号上。** **这跟小学数学一模一样，不用记特例。**

  除法：这里给整数，MySQL 给小数
  SQL> SELECT 7 / 2 AS a, 7 / 2.0 AS b, 7 % 2 AS c
  a     b        c
  -----------------
  3     3.50     1
  （1 行）

  **`7 / 2` 这里给 3，MySQL 给 3.5000。**
  **两个引擎对整数除法的默认行为不同，这一条最容易在迁移时炸。**
  **MySQL 要整除得写 `7 DIV 2`，取余写 `7 % 2` 或 `MOD(7, 2)`。**
  **本沙箱没有 `DIV`，上面那句 `7 DIV 2` 是教材原码，不在这里执行。**
  **想在这边拿到 3.5，把任一边写成小数**（`7 / 2.0`），**或者 `CAST(7 AS REAL) / 2`。**

  除零和 null：都不报错，都给 null
  SQL> SELECT 10 / 0 AS div_zero, 10 % 0 AS mod_zero, NULL + 1 AS null_add
  div_zero     mod_zero     null_add
  -----------------------------------
  NULL         NULL         NULL
  （1 行）

  **MySQL 默认也不报错，给 NULL 加一条警告**（`sql_mode` 开着严格模式才报错）。
  **所以「除以零」在 SQL 里不会中断流程，只会悄悄变成空值。**

  字符串参与算术：自动转成数字
  SQL> SELECT 'abc' + 1 AS s1, '3abc' + 1 AS s2
  s1     s2
  ----------
  1      4
  （1 行）

  **`'abc'` 转成 0，`'3abc'` 转成 3（读到第一个非数字就停），所以给 1 和 4。**
  **MySQL 结果一样，但会记一条警告。**
  **这是隐式转换最坑人的地方**：不报错、结果还挺「合理」，**线上就变成一个查不出来的错数。**

  浮点数：算出来的钱不是整数分
  SQL> SELECT CAST(0.1 + 0.2 AS TEXT) AS float_err
  float_err
  --------------------
  0.30000000000000004
  （1 行）

  SQL> SELECT CAST(SUM(qty * price) AS TEXT) AS sum_detail, CAST((SELECT SUM(amount)
       FROM orders) AS TEXT) AS sum_order FROM order_detail
  sum_detail             sum_order
  ----------------------------------
  1969.1000000000001     1969.1
  （1 行）

  **`0.1 + 0.2` 给 `0.30000000000000004`，不是 0.3。**
  **明细合计给 `1969.1000000000001`，订单表里存的却是 `1969.1`。**
  **两个数差在小数点后第 13 位，但用 `=` 比就永远不相等。**
  **所以钱别用浮点存——MySQL 用 `DECIMAL(10,2)`。**
  **本沙箱没有 DECIMAL，只能用 REAL 代替，上面那点误差就是这么露出来的。**

第一段算总价。 1 × 128.00、2 × 15.80，total 这一列是现算的，表里没有。 第二段打折与扣库存。 299.00 打八折给 239.20； 足球库存 30 减 40 给 -10，负数照样算出来，没人拦你。 第三段优先级。 11 和 14，差的就是括号。 第四段除法。 7 / 2 给 3、7 / 2.0 给 3.50、7 % 2 给 1。 同一句搬到 MySQL，第一个数会变成 3.5000。 第五段除零。 三列全是 NULL，一句报错都没有。 第六段字符串。 'abc' + 1 给 1，'3abc' + 1 给 4。 第七段浮点。 0.30000000000000004 和 1969.1000000000001 两个尾巴，就是「钱别用浮点存」最直白的证据。

30 秒实验

1. 把 qty * price 改成 price * qty，结果会变吗？为什么？
2. SELECT 10 / 4 与 SELECT 10 / 4.0 分别给什么？跟你想的一样吗？
3. SELECT CAST(SUM(qty * price) AS TEXT) 那串尾巴，换成先算整数分再除 100 还在吗？

常见误区

1. 以为 / 一定给小数。 整数除整数在不同引擎给不同结果，迁移时这一处最容易被漏掉。
2. 用 float 存金额。 单行看不出问题，一旦求和或比较相等就露馅，而且这种错不会报错， 只会算错钱。
3. 以为除零会中断。 它给 NULL 继续往下跑，于是「除以零」变成一个藏在结果里的空值。

一行之内能算了。 下一篇把这些运算套到「一堆行」上——sum、avg、max、min、count 五个聚合函数。

• ☐ （本篇完，下一篇 4.2 聚合函数）

相关笔记

• select 语句简介
• 数据类型
• 🦏 MySQL即学即用 · 索引