CREATE VIEW 把常用查询存成虚拟表,CREATE INDEX 给查询提速。

视图(VIEW):把常用查询存起来

有些查询要反复使用,每次都重写很麻烦。视图是一条被起了名字、存进数据库的查询,用起来像一张表:

sql
CREATE VIEW 视图名 AS SELECT ...;

把「科幻书」存成视图,然后立刻把它当表查询(建视图没有输出,所以把两条语句放在一起运行):

sql
CREATE VIEW v_scifi AS SELECT book_id, title, price FROM books WHERE category = '科幻'; SELECT title, price FROM v_scifi;

要点:

  • 视图是虚拟表,本身不存数据;每次查询视图,都是实时执行它背后的 SELECT
  • 基表数据变了,视图查询结果自动跟着变
  • 删除视图用 DROP VIEW 视图名;(删除不影响基表)

视图还能做轻量的「权限隔离」和「逻辑分层」——把复杂连接包成一张简洁的表,例如按订单汇总销售额:

sql
CREATE VIEW v_order_summary AS SELECT o.order_id, c.name AS customer_name, SUM(oi.quantity * oi.unit_price) AS total_amount FROM orders o JOIN customers c ON o.customer_id = c.customer_id JOIN order_items oi ON o.order_id = oi.order_id GROUP BY o.order_id, c.name; SELECT * FROM v_order_summary;

索引(INDEX):查询加速器

书的目录页让你不用翻全书就能找到某一章。索引就是表的目录:对某列建索引后,按该列检索的速度大幅提升。

sql
CREATE INDEX 索引名 ON 表名(列名);

给 books.category 建一个索引:

sql
CREATE INDEX idx_books_category ON books(category);

索引名建议用「idx_表_列」这样的约定。它建好后自动生效,查询语句不用做任何修改;删除用 DROP INDEX 索引名;

代价与使用场景:索引本身占存储空间,且每次 INSERT/UPDATE/DELETE 都要同步维护它。所以不要盲目给所有列建索引——经常出现在 WHERE、JOIN、ORDER BY 里的列才值得建;小表、写入频繁的表则要克制。

用 sqlite_master 可以看到库里的索引(我们建的这个):

sql
SELECT name, tbl_name FROM sqlite_master WHERE type = 'index';

小结

  • CREATE VIEW ... AS SELECT:保存查询为虚拟表,DROP VIEW 删除
  • 视图实时反映基表数据,可简化复杂查询
  • CREATE INDEX ON 表(列) 加速按列检索,代价是写入变慢
  • 给高频查询列建索引,而不是给所有列

练习题

建议先在本地 SQL 环境或浏览器 SQLite 练习本中写一遍,再展开参考答案对照。

1. 建科幻书视图

创建名为 v_scifi 的视图,包含科幻类图书的 book_id、title、price(建视图的 SELECT 里三列都要有)。创建后顺便查询它验证。

起始语句:

sql
-- CREATE VIEW 后,再 SELECT 验证一下

💡 提示:CREATE VIEW v_scifi AS SELECT book_id, title, price FROM books WHERE category = '科幻';

查看参考答案
sql
CREATE VIEW v_scifi AS SELECT book_id, title, price FROM books WHERE category = '科幻';

2. 订单汇总视图

创建名为 v_order_summary 的视图:每张订单的订单号(列名 order_id)、顾客姓名(列名 customer_name)、订单总金额(列名 total_amount,等于该订单所有明细 quantity × unit_price 之和)。判题会直接查询这个视图。

起始语句:

sql
-- 提示:三表连接 + GROUP BY,列别名必须严格用题目的三个名字

💡 提示:JOIN orders、customers、order_items,GROUP BY o.order_id, c.name,SUM(oi.quantity * oi.unit_price) AS total_amount

查看参考答案
sql
CREATE VIEW v_order_summary AS SELECT o.order_id, c.name AS customer_name, SUM(oi.quantity * oi.unit_price) AS total_amount FROM orders o JOIN customers c ON o.customer_id = c.customer_id JOIN order_items oi ON o.order_id = oi.order_id GROUP BY o.order_id, c.name;

3. 删除临时视图

数据库里有一个过时的视图 v_tmp_view(判题时会自动创建)。请用 DROP VIEW 删除它。

起始语句:

sql
-- 在下面写出 DROP 语句

💡 提示:DROP VIEW v_tmp_view;

查看参考答案
sql
DROP VIEW v_tmp_view;

4. 给分类列建索引

在 books 表的 category 列上创建名为 idx_books_category 的索引(索引名必须一致,判题用 sqlite_master 检查)。

起始语句:

sql
-- 在下面写出 CREATE INDEX 语句

💡 提示:CREATE INDEX idx_books_category ON books(category);

查看参考答案
sql
CREATE INDEX idx_books_category ON books(category);

涉及的数据表

  • books
  • orders
  • customers
  • order_items

由在线工具箱(www.vba.net)整理制作