CREATE VIEW 把常用查询存成虚拟表,CREATE INDEX 给查询提速。
视图(VIEW):把常用查询存起来
有些查询要反复使用,每次都重写很麻烦。视图是一条被起了名字、存进数据库的查询,用起来像一张表:
sqlCREATE VIEW 视图名 AS SELECT ...;把「科幻书」存成视图,然后立刻把它当表查询(建视图没有输出,所以把两条语句放在一起运行):
sqlCREATE VIEW v_scifi AS
SELECT book_id, title, price FROM books WHERE category = '科幻';
SELECT title, price FROM v_scifi;要点:
- 视图是虚拟表,本身不存数据;每次查询视图,都是实时执行它背后的 SELECT
- 基表数据变了,视图查询结果自动跟着变
- 删除视图用 DROP VIEW 视图名;(删除不影响基表)
视图还能做轻量的「权限隔离」和「逻辑分层」——把复杂连接包成一张简洁的表,例如按订单汇总销售额:
sqlCREATE 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):查询加速器
书的目录页让你不用翻全书就能找到某一章。索引就是表的目录:对某列建索引后,按该列检索的速度大幅提升。
sqlCREATE INDEX 索引名 ON 表名(列名);给 books.category 建一个索引:
sqlCREATE INDEX idx_books_category ON books(category);索引名建议用「idx_表_列」这样的约定。它建好后自动生效,查询语句不用做任何修改;删除用 DROP INDEX 索引名;
代价与使用场景:索引本身占存储空间,且每次 INSERT/UPDATE/DELETE 都要同步维护它。所以不要盲目给所有列建索引——经常出现在 WHERE、JOIN、ORDER BY 里的列才值得建;小表、写入频繁的表则要克制。
用 sqlite_master 可以看到库里的索引(我们建的这个):
sqlSELECT 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 = '科幻';
查看参考答案
sqlCREATE 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
查看参考答案
sqlCREATE 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;
查看参考答案
sqlDROP VIEW v_tmp_view;4. 给分类列建索引
在 books 表的 category 列上创建名为 idx_books_category 的索引(索引名必须一致,判题用 sqlite_master 检查)。
起始语句:
sql-- 在下面写出 CREATE INDEX 语句
💡 提示:CREATE INDEX idx_books_category ON books(category);
查看参考答案
sqlCREATE INDEX idx_books_category ON books(category);涉及的数据表
booksorderscustomersorder_items
由在线工具箱(www.vba.net)整理制作