11.5 多表视图 · 波仔的 AI 编程实战课

📄 一句话概括

视图里可以连表、可以聚合，「三张表的连接 + 分组 + 聚合」全收进一个名字里，以后一句 select * from v_cls 就够。 真正的好处是「口径统一」：「平均身高怎么算」被固定在视图里，十个人写十份报表也不会各算各的。 多表视图一定不可写——带聚合、distinct、union、group by 的视图， 在任何数据库上都只读，因为「改视图的一行」没法翻译成「改基表的哪几行」。

📝 本章和前面几章的关系

前四篇的视图都建在一张表上，这一篇把它用在真正的场景里：多表、聚合。 这才是视图最常被用的地方——把「一段没人看得懂的连接」收成一个名字， 让后面所有人都用同一个口径。

一、视图里可以连表、可以聚合

classes 连接 students、按班级分组、算人数和平均身高，全收进 v_cls 这一个名字里。 以后要「各班人数和平均身高」，一句 select * from v_cls 就够。 而且别人不用再去想「平均身高要不要排除空值」——这个决定已经写死在视图里了。 真正的好处是「口径统一」： 「平均身高怎么算」这件事被固定在视图里，十个人写十份报表，口径也不会各算各的。 这是视图在数据团队里最大的价值，比省打字重要得多。

二、视图可以再被聚合

视图当表用，随便再聚合。 Demo 里对 v_cls 再算一次：三个班、平均每班 4 人。 注意 AVG(人数) 算的是「各班人数的平均值」，不是「全校平均每班多少人」的另一种算法。 这两个数在这里碰巧一样（每班都 4 人），但一般情况下不一样。 这就是「口径」问题的具体样子： 同一个中文问题，两次聚合的顺序不同，结果就不同。 写成视图能把这个顺序固定下来，避免每次重新想。

三、视图套视图：两层很常见，三层就该警惕

v_cls_big 建在 v_cls 上，两层。 好处是「一层管明细、一层管筛选」，各司其职。 但层数一多就有两个问题：一是排查困难（一个数字不对，得顺着三层定义往上翻）； 二是性能可能变差（每层都可能是一次完整查询）。 经验上的界线是「两层可以，三层要写清注释，四层就该重构」。 层数多了之后，一个视图被改，影响面会沿着依赖链扩散—— 这也是为什么「视图套视图」在有的团队里被明令禁止。

四、多表视图一定不可写

UPDATE v_cls SET 人数 = 10 报 cannot modify v_cls because it is a view。 这里两层原因叠在一起：本沙箱的视图本来就只读， 而且带 group by 的视图在任何数据库上都不可写。 MySQL 那边也一样：带聚合、distinct、union、group by 的视图一律只读， 因为「改视图里的一行」根本没法翻译成「改基表的哪几行」。 一个「人数 4」是四行学生算出来的，你把它改成 10，该改哪一行？ 这个问题没有答案，所以数据库直接禁止。 这条逻辑值得想清楚：不可写不是限制，是「语义上做不到」。

五、多表视图该注意的三件事

一是列名必须起全。 多表连接时 id、name 很容易撞名， 不起别名就会变成两个 id，引用的时候没法区分。 建视图时最好把所有列都显式起名，别指望 select *。 二是别在视图里写 order by。 排序是「最终输出」的事，不是「中间结果」的事—— 外层查询自己排一次就行。 很多数据库会直接忽略视图里的 order by（只有带 limit 时才不忽略）， 所以「在视图里排好序」这件事靠不住。 三是 select * 有版本风险。 视图定义里的 * 在建视图那一刻就展开成具体列名了， 所以基表后来加列，视图不会自动多出来。 这既是好事（行为稳定），也是坑（你以为会跟着变）。

六、动手 Demo：把三张表的连接收成一个视图，再套一层

Demo 五段：建一个带连接、分组、聚合的视图；对它再聚合一次；在它上面再套一层；最后撞一次「多表视图不能写」。 第二段那个「再聚合一次」值得细看——它能让你看清「平均的平均」和「总的平均」是两件事。

import _q46 as Q
c = Q.con()

# 三张表的连接 + 分组 + 聚合，收成一个名字
Q.run(c, "CREATE VIEW v_cls AS "
          "SELECT c.name AS 班级, COUNT(s.id) AS 人数, "
          "ROUND(AVG(s.height), 2) AS 平均身高 "
          "FROM classes c JOIN students s ON s.class_id = c.id "
          "GROUP BY c.id")
Q.show(c, "SELECT * FROM v_cls")

# 视图当表用，再聚合一次
Q.show(c, "SELECT COUNT(*) AS 班级数, ROUND(AVG(人数), 2) AS 平均班级人数 "
          "FROM v_cls")

# 视图套视图：两层
Q.run(c, "CREATE VIEW v_cls_big AS "
          "SELECT 班级, 人数 FROM v_cls WHERE 人数 >= 4")
Q.show(c, "SELECT * FROM v_cls_big")

# 多表视图一定不可写
c.execute("UPDATE v_cls SET 人数 = 10 WHERE 班级 = '一班'")

实跑输出：

--------------------------------------------------------------------
第六十二课：多表视图——把三张表的连接收成一张表
--------------------------------------------------------------------

  视图里可以连表、可以聚合
  SQL> CREATE VIEW v_cls AS SELECT c.name AS 班级, COUNT(s.id) AS 人数, ROUND(AVG(s.height),
       2) AS 平均身高 FROM classes c JOIN students s ON s.class_id = c.id GROUP BY c.id
  完成
  SQL> SELECT * FROM v_cls
  班级     人数     平均身高
  ---------------------------
  一班     4        170.45
  二班     4        167.40
  三班     4        167.47
  （3 行）
  **三张样例表的连接、分组、聚合，全收进这一个名字里。**
  **以后要「各班人数和平均身高」，一句 `select * from v_cls` 就够。**
  **真正的好处是「口径统一」**：
  **「平均身高怎么算」这件事被固定在视图里，十个人写十份报表，口径也不会各算各的。**
  **这是视图在数据团队里最大的价值，比省打字重要得多。**

  视图可以再被聚合
  SQL> SELECT COUNT(*) AS 班级数, ROUND(AVG(人数), 2) AS 平均班级人数 FROM v_cls
  班级数     平均班级人数
  ------------------------
  3          4.00
  （1 行）
  **视图当表用，随便再聚合。**
  **注意 `AVG(人数)` 算的是「各班人数的平均值」，不是「全校平均每班多少人」的另一种算法**——
  **这两个数在这里碰巧一样（每班都 4 人），但一般情况下不一样。**
  **这就是「口径」问题的具体样子**：
  **同一个中文问题，两次聚合的顺序不同，结果就不同。**
  **写成视图能把这个顺序固定下来，避免每次重新想。**

  视图套视图：两层很常见，三层就该警惕
  SQL> CREATE VIEW v_cls_big AS SELECT 班级, 人数 FROM v_cls WHERE 人数 >= 4
  完成
  SQL> SELECT * FROM v_cls_big
  班级     人数
  --------------
  一班     4
  二班     4
  三班     4
  （3 行）
  **`v_cls_big` 建在 `v_cls` 上，两层。** **好处是「一层管明细、一层管筛选」，各司其职。**
  **但层数一多就有两个问题**：**一是排查困难**（一个数字不对，得顺着三层定义往上翻）；
  **二是性能可能变差**（每层都可能是一次完整查询）。
  **经验上的界线是「两层可以，三层要写清注释，四层就该重构」。**

  多表视图一定不可写
  SQL> UPDATE v_cls SET 人数 = 10 WHERE 班级 = '一班'
  报错：cannot modify v_cls because it is a view
  **报 `cannot modify v_cls because it is a view`。** **这里两层原因叠在一起**：
  **本沙箱的视图本来就只读；而且带 `group by` 的视图在任何数据库上都不可写。**
  **MySQL 那边也一样**：**带聚合、`distinct`、`union`、`group by` 的视图一律只读**，
  **因为「改视图里的一行」根本没法翻译成「改基表的哪几行」。**
  **一个「人数 4」是四行学生算出来的，你把它改成 10，该改哪一行？**

  多表视图该注意的三件事
  **一是列名必须起全。**
  **多表连接时 `id`、`name` 很容易撞名**，
  **不起别名就会变成两个 `id`，引用的时候没法区分。** **建视图时最好把所有列都显式起名**，
  别指望 `select *`。
  **二是别在视图里写 `order by`。**
  **排序是「最终输出」的事，不是「中间结果」的事**——**外层查询自己排一次就行。**
  **很多数据库会直接忽略视图里的 `order by`**（**只有带 `limit` 时才不忽略**），
  **所以「在视图里排好序」这件事靠不住，换个数据库或者换个版本就可能变。**
  **三是`select *` 有版本风险。**
  **视图定义里的 `*` 在建视图那一刻就展开成具体列名了**，
  **所以基表后来加列，视图不会自动多出来。**
  **这既是好事（行为稳定），也是坑（你以为会跟着变）。**

第一段三行：三个班各 4 人，平均身高 170.45、167.40、167.47。 第二段再聚合：3 个班、平均每班 4 人。 第三段视图套视图，v_cls_big 筛出「人数 ≥ 4」的三个班。 第四段 UPDATE 报错——多表聚合视图在任何数据库上都不可写。

30 秒实验

为什么带 group by 的视图不可写？
AVG(人数) 和「全校平均每班多少人」是一回事吗？
视图套了两层之后，改最底层那张表，两个视图会不会都受影响？

常见误区

拿视图当「性能优化」。 它不存数据，复杂视图只会更慢。
在视图里写 order by 指望外层有序。 多数数据库会忽略它，要排序就在外层写。
视图套四五层。 排查困难、性能变差，改一处影响一片。两层是常规，三层要克制。

自测三问（点开对答案）

1. 多表视图最大的价值是什么？ —— 口径统一：把复杂的连接和算法固定在视图里，所有人查同一个视图就得到同一个口径。 2. 为什么带 `group by` 的视图不可写？ —— 「视图里的一行」是基表好几行算出来的，改它没法翻译成改基表的哪几行，语义上做不到。 3. 视图里的 `select *` 会跟着基表加列吗？ —— 不会。`*` 在建视图那一刻就展开成具体列名，基表加列视图不会变。

第 11 章完了： 建、看、改、删、多表，视图的五件事都会了。 下一篇进第 12 章——索引，把「查得快」这件事讲清。

☐ （本篇完，下一篇 12.1 索引简介）