一、查看视图

最直接的查法是 sqlite_master，sql 字段就是定义原文。 PRAGMA table_info 对视图也管用，列出的就是它暴露的列。

沙箱这边把视图和表存在同一张 sqlite_master 里，靠 type 区分。 MySQL 的视图是独立对象，查法完全不同。

二、修改定义：先删再建

沙箱没有 OR REPLACE，改定义只能两步走。 中间那一下空档是真的存在：DROP 之后、CREATE 之前，查它就会报错。

行数没变、列数多了一列，这就是「改定义」的全部内容。 它动的是那句 SELECT，一行数据都没碰。

三、改数据：沙箱一律不行

拿最简单的单表视图试三种改法，全都不行。

注意它连「简单视图」都不放过：只要对象是个视图，就不许直接改。 这一点和 MySQL 完全相反，也是照教材敲最容易出错的地方。

四、MySQL 的规矩：什么样的视图能改

MySQL 的判据只有一条：改完之后，「视图里那一行」还能不能唯一对应到「基表里那一行」。 聚合之后一行对应好几行，就不知道该改谁了，所以一律只读。

教材那张表跟这一张基本一致，只有第一行要补一句： 沙箱连这一行也不许，所以「简单视图可更新」这条在沙箱里用不上。 反过来说，读这篇的时候不要拿「沙箱不能改」去推「MySQL 也不能改」。 两边的默认值正好相反。

五、INSTEAD OF 触发器：让视图可写

既然视图不能改，SQLite 就给了一个补丁：让你自己写「改了之后干什么」。

CREATE TRIGGER t_ins INSTEAD OF INSERT ON v_price
BEGIN
  INSERT INTO tb_goods (id, name, price, stock)
  VALUES (NEW.id, NEW.name, NEW.price, NEW.stock);
END;

INSTEAD OF 三个字就是「别执行原来那句，执行我这段」。 插进视图的那条记录，被原样转发到了基表里。 这是 SQLite 特有的能力，MySQL 没有这个触发器。 因为它的简单视图本来就能改，不需要补这一层。 代价是这段逻辑要自己维护：视图一加列，触发器就得跟着改，改漏了写入就少字段。

六、删除视图，以及失效视图

DROP VIEW 之后查它，报的是 no such table 而不是 no such view。 因为在引擎眼里，视图本来就是一张表。

DROP VIEW v_price;
DROP VIEW IF EXISTS v_price;

IF EXISTS 让「删一个不存在的视图」不报错，脚本里几乎都会加。 还有一个更隐蔽的状态：删掉基表，视图不会跟着消失。 它还在 sqlite_master 里，但一查就报「没有这张表」，这叫失效视图。 所以删表之前要检查有没有视图指着它。 MySQL 那边也一样：删表不会顺手清理视图，它同样会变成失效状态。 所以这条检查两边都要做，不能指望引擎替你兜。 第 12 章到这里结束。这一章真正要带走的是两句：视图不存数据、加速不了查询； 以及「能不能改视图」这件事，两个引擎的默认值正好相反。 12.5 的练习可以挑这三道：建一个只暴露三列的视图、把 SELECT * 的视图改成写全列名、 给视图写一个 INSTEAD OF UPDATE 触发器。 三道都动手做过，这一章就算真读完了。

七、动手 Demo：查定义、改定义、改数据、删视图

这一段把教材 12.3 的四件事各跑一遍。 中间那件事（改数据）在沙箱里必然失败，所以把三条报错原样打出来， 再用 INSTEAD OF 触发器补上这条路。

import sqlite3
c = sqlite3.connect(u":memory:")
c.execute(u"CREATE VIEW v_price AS SELECT id, name, price "
          u"FROM tb_goods WHERE price >= 100")

# ① 查看：定义原文 + 列信息
print(c.execute(u"SELECT sql FROM sqlite_master "
                u"WHERE type = 'view'").fetchall())
print(c.execute(u"PRAGMA table_info(v_price)").fetchall())

# ② 改定义：先删再建
c.execute(u"DROP VIEW v_price")
c.execute(u"CREATE VIEW v_price AS SELECT id, name, price, stock "
          u"FROM tb_goods WHERE price >= 100")

# ③ 改数据：三种改法全失败
for s in [u"INSERT INTO v_price VALUES (11, 'x', 300, 5)",
          u"UPDATE v_price SET price = 1 WHERE id = 2",
          u"DELETE FROM v_price WHERE id = 2"]:
    try:
        c.execute(s)
    except sqlite3.OperationalError as e:
        print(e)

# ④ 补一条路：INSTEAD OF 触发器
c.execute(u"CREATE TRIGGER t_ins INSTEAD OF INSERT ON v_price "
          u"BEGIN INSERT INTO tb_goods (id, name, price, stock) "
          u"VALUES (NEW.id, NEW.name, NEW.price, NEW.stock); END")
c.execute(u"INSERT INTO v_price VALUES (11, '试插', 300, 5)")
print(c.execute(u"SELECT * FROM tb_goods WHERE id = 11").fetchall())

# ⑤ 删视图，以及删基表之后的失效视图
c.execute(u"DROP VIEW v_price")
c.execute(u"CREATE VIEW v_lonely AS SELECT id, name FROM tb_goods")
c.execute(u"DROP TABLE tb_goods")
try:
    c.execute(u"SELECT * FROM v_lonely")
except sqlite3.OperationalError as e:
    print(e)

实跑输出：

--------------------------------------------------------------------
第五十六课：视图能查、能改定义，就是不能改数据
--------------------------------------------------------------------

  **教材 12.3 讲四件事：查看、修改、更新、删除。**
  **前三件里有一件在沙箱里是「一律失败」，这一课的重点就在那件上。**
  **SQLite 的视图全是只读的，连单表视图也不许改；MySQL 相反，简单视图默认可改。**
  **这个分叉必须讲清楚，不然照着教材敲会一头雾水。**

  mysql> CREATE VIEW v_price AS SELECT id, name, price FROM tb_goods WHERE price >= 100

  一、查看视图
  --------------------------------------------------------------------

  视图     SELECT 里的列数
  ------------------------
  v_price  3
  **`sqlite_master` 的 `sql` 字段就是定义原文，这是最直接的查法。**

  mysql> PRAGMA table_info(v_price)
  cid name  type    notnull dflt_value pk
  ---------------------------------------
  0   id    INTEGER 0       NULL       0
  1   name  TEXT    0       NULL       0
  2   price REAL    0       NULL       0
  3 rows in set
  **`PRAGMA table_info` 对视图也管用，列出的就是视图暴露的那几列。**
  **MySQL 那边对应的是 `SHOW CREATE VIEW` 和 `DESCRIBE`，思路一样。**

  二、修改定义：先删再建
  --------------------------------------------------------------------

  mysql> DROP VIEW v_price

  mysql> CREATE VIEW v_price AS SELECT id, name, price, stock FROM tb_goods WHERE price >= 100

  时机    视图的行数  视图的列数
  ------------------------------
  改之前  5           3
  改之后  5           4
  **行数没变、列数多了一列，这就是「改定义」的全部内容。**
  **它动的是那句 SELECT，一行数据都没碰。**

  三、改数据：沙箱一律不行
  --------------------------------------------------------------------
  **先拿最简单的单表视图试三种改法，全都不行：**

  动作  结果
  ------------------------------------------------
  插入  cannot modify v_price because it is a view
  更新  cannot modify v_price because it is a view
  删除  cannot modify v_price because it is a view
  **三条报的都是同一句：`cannot modify ... because it is a view`。**
  **注意它连「简单视图」都不放过——只要是个视图，就不许直接改。**
  **这一点和 MySQL 完全不同，也是这一章最容易按教材敲出错的地方。**

  四、MySQL 的规矩：什么样的视图能改
  --------------------------------------------------------------------

  视图里有什么              能不能改
  ----------------------------------------------------
  单表 + 不含聚合函数       能
  含 `SUM`／`COUNT`／`AVG`  不能
  含 `GROUP BY`／`HAVING`   不能
  含 `DISTINCT`             不能
  含 `UNION`                不能
  多表连接                  通常不能，除非只改其中一张
  **判断依据只有一条：改完之后，「视图里那一行」还能不能唯一对应到「基表里那一行」。**
  **聚合之后一行对应好几行，就不知道该改谁了。**
  **教材里那张表跟这一张基本一致，只有第一行要补一句：**
  **沙箱连这一行也不许，所以「简单视图可更新」这条在沙箱里用不上。**

  五、INSTEAD OF 触发器：让视图可写
  --------------------------------------------------------------------
  **触发器要写清「往视图里插」怎么翻译成「往基表里插」：**
  mysql> CREATE TRIGGER t_ins INSTEAD OF INSERT ON v_price
      -> BEGIN
      ->   INSERT INTO tb_goods (id, name, price, stock)
      ->   VALUES (NEW.id, NEW.name, NEW.price, NEW.stock);
      -> END;

  mysql> INSERT INTO v_price VALUES (11, '试插', 300, 5)

  mysql> SELECT id, name, price, stock FROM tb_goods WHERE id = 11
  id name price stock
  -------------------
  11 试插 300   5
  1 row in set
  **插进去了。触发器把「往视图里插」翻译成「往基表里插」，所以能成。**
  **`INSTEAD OF` 三个字就是「别执行原来那句话，执行我这段」。**
  **这是 SQLite 的补丁：既然视图不能改，就让你自己写「改了之后干什么」。**
  **MySQL 没有这个触发器——它的简单视图本来就能改，不需要补。**
  **代价是每次写都要自己维护这段触发器的逻辑，视图一改列，触发器就得跟着改。**

  六、删除视图，以及删基表的后果
  --------------------------------------------------------------------

  mysql> DROP VIEW v_price
  **删掉之后查它，报的是 `no such table: v_price`。**
  **注意报的是 `no such table` 不是 `no such view`——在引擎眼里它本来就是一张表。**

  mysql> DROP VIEW IF EXISTS v_price
  **`IF EXISTS` 让「删一个不存在的视图」不报错，脚本里很常用。**

  mysql> CREATE VIEW v_lonely AS SELECT id, name FROM tb_goods
  **删掉基表之后，视图还在 `sqlite_master` 里，但一查就报 `no such table: main.tb_goods`。**
  **这个状态叫「失效视图」：它没被自动删掉，只是不能再用了。**
  **所以删表之前要检查有没有视图指着它，这一步很容易漏。**
  **第 12 章到这里结束。这一章真正要带走的是两句：**
  **视图不存数据、视图加速不了查询；以及「能不能改视图」这件事两边完全相反。**

第一组两行：sqlite_master 里那一条 CREATE VIEW ... 原文，加上三行列信息。 视图和表存在同一张系统表里，靠 type 区分，这是 SQLite 的做法。 第二组是「改定义」：删了再建，行数没变、列数从 3 变 4。 它动的是那句 SELECT，一行数据都没碰。 第三组三条报错完全一样：cannot modify v_price because it is a view。 这是沙箱和 MySQL 最大的一处分叉，也是最容易照书敲错的地方。 第四组插成功了一行，说明触发器把「往视图插」翻译成了「往基表插」。 这是 SQLite 给的补丁，MySQL 不需要。 第五组最后一行报 no such table: main.tb_goods：视图还在，基表没了。 这种失效视图不会自动清理，删表之前得自己查一遍。

30 秒实验

1. 给 v_price 再写一个 INSTEAD OF UPDATE 触发器，看更新能不能转发到基表。
2. 把 DROP VIEW 换成 DROP TABLE v_price，看会报什么错。
3. 删掉 tb_goods 之后查 sqlite_master，确认 v_lonely 那一条还在不在。

常见误区

1. 拿教材的 UPDATE 视图 直接在沙箱里敲，报错之后以为是自己写错了。
2. 以为沙箱不能改就等于 MySQL 不能改，把「视图只读」当成普适结论。
3. 删了基表之后没管视图，等到某天查询报「没有这张表」才回头找原因。

第 12 章到这里结束。下一篇进第 13 章：存储过程和函数，第一次把逻辑写进数据库。

• ☐ （本篇完，下一篇 13.1 定义完整性约束）

相关笔记

• 视图概述
• 创建视图
• 子查询
• 🛢️ MySQL从入门到精通 · 索引