第20节 用视图保存常用查询

现在你能写连接查询,但不想每次显示图书和分类都重写JOIN。可以把这条查询定义保存成一个对象,叫视图。

1 先有普通查询,再包成视图

-- 前面的操作先结束成一个批,确保CREATE VIEW位于下一批开头
GO
CREATE VIEW dbo.V_BookCategory
AS
SELECT b.BookId,b.Title,b.Price,b.Stock,c.CategoryName
FROM dbo.Book b
LEFT JOIN dbo.Category c ON b.CategoryId=c.CategoryId;
GO
 
-- 以后像查表一样查视图;这里看60元以下的书及分类
SELECT Title,Price,CategoryName
FROM dbo.V_BookCategory
WHERE Price<60
ORDER BY Price;

CREATE VIEW 名称 AS 查询是基本结构。普通视图保存的是定义,查询时读取基础表,不是固定保存了创建那一刻的6行快照。基础表更新后,再查视图会得到当前数据。

2 排序放在查询视图时

不要为了“保存一个降序视图”,直接给普通视图定义末尾加ORDER BY。即使TOP/OFFSET等情况下允许某些内部排序写法,也不保证以后查询视图的最终顺序;外层要什么顺序,外层就ORDER BY。

3 ALTER修改定义,DROP删除定义

GO
-- ALTER VIEW写出完整新定义,这次在视图里额外提供作者
ALTER VIEW dbo.V_BookCategory
AS
SELECT b.BookId,b.Title,b.Author,b.Price,b.Stock,c.CategoryName
FROM dbo.Book b
LEFT JOIN dbo.Category c ON b.CategoryId=c.CategoryId;
GO
 
-- 只查看定义文本,不会修改任何数据
EXEC sp_helptext N'dbo.V_BookCategory';

SQL Server 2012使用CREATE与ALTER分开写,不能用新版CREATE OR ALTER。删除语法是 DROP VIEW dbo.V_BookCategory;;只删视图,不删Book。后续还要练习,先保留这个视图。

4 通过简单视图修改基础表

接下来用独立练习表演示,避免调动Book的价格。一个只查询单表、没有聚合等限制的简单视图,通常可以映射回基础表进行修改。

CREATE TABLE dbo.ViewPractice(Id int PRIMARY KEY,Price decimal(8,2) NOT NULL);
INSERT dbo.ViewPractice(Id,Price) VALUES(1,20);
GO
CREATE VIEW dbo.V_PricePractice
AS
SELECT Id,Price FROM dbo.ViewPractice WHERE Price>=10
WITH CHECK OPTION; -- 经此视图改数据后,记录仍须符合视图的筛选条件
GO
 
-- 把视图中1号价格改到25,实际上修改的是ViewPractice
UPDATE dbo.V_PricePractice SET Price=25 WHERE Id=1;
SELECT * FROM dbo.ViewPractice; -- 25
 
-- 简单视图也可插入和删除:操作仍落到同一张基础表
INSERT dbo.V_PricePractice(Id,Price) VALUES(2,15);
SELECT * FROM dbo.ViewPractice; -- 基础表现在有编号1和2
DELETE FROM dbo.V_PricePractice WHERE Id=2;
SELECT * FROM dbo.ViewPractice; -- 删除后只剩编号1
 
-- 下面是假设操作,不执行;改成5会违反WITH CHECK OPTION
-- UPDATE dbo.V_PricePractice SET Price=5 WHERE Id=1;
 
DROP VIEW dbo.V_PricePractice;
DROP TABLE dbo.ViewPractice; -- 只清理刚建立的演示对象

视图包含GROUP BY、聚合、DISTINCT、集合运算等,或修改无法清晰映射到基础表时,通常不能直接更新。连接视图也有较多限制,不要把它当成可以随意写的普通表;本课程四处组合的信息视图用于读取。

本节练习

查V_BookCategory里已知库存至少10的图书。回答:删视图会删图书吗?修改视图定义和通过视图修改价格,分别用什么命令?


上一节:19 从业务需求设计数据库 | 下一节:21 变量判断与TSQL代码块 | 课程目录