数据库原理及应用课件：第9章 视图

1、数据库基础与实践技术（SQL Server 2008）第九章第9章 视图9.1 标准视图9.2 分区视图9.3 索引视图2/429.1 标准视图基本概念视图是由从数据库的基本表中选取出来的数据组成的逻辑窗口。是基本表的部分行和列数据的组合。视图是一个虚表。数据库中只存储视图的定义，而不存储视图所包含的数据。3/42视图与基本表关系4/429.1.1 定义视图CREATE VIEW schema_name. view_name (column ,.n ) WITH ENCRYPTION | SCHEMABINDING AS select_statement WITH CHECK OPTION ;

2、 5/429.1.1 定义视图如果省略了视图的“列名表”部分，则视图的列名与查询语句结果显示的列名相同。下列情况必须明确指定视图列名：1）某个目标列是函数或表达式，并且没有为这样的列起别名。2）多表连接时选出了几个同名列作为视图的字段。3）需要在视图中为列选用新的更合适的列名。6/421. 定义单源表视图 视图取自一个基本表的部分行、列，视图行列与基本表行列对应，一般可查询可更新。7/42示例例1建立查询信息管理系学生的学号、姓名、性别和出生日期的视图。 CREATE VIEW IS_StudentAS SELECT Sno, Sname, Sex, Birthdate FROM Studen

3、t WHERE Dept = 信息管理系8/422.定义多源表视图 子查询源表多于一个，一般可查询不可更新。9/42示例例2建立查询信息管理系选了C001课程的学生学号、姓名和成绩的视图。 CREATE VIEW V_IS_S1（Sno, Sname, Grade）AS SELECT Student.Sno, Sname, Grade FROM Student join SC ON Student.Sno = SC.Sno WHERE Dept = 信息管理系 AND SC.Cno = C001 10/423.在已有视图上定义新视图 视图的数据源可以来自其它的视图,一般可查询不可更新。11/4

4、2示例例3利用例1建立的视图，建立查询信息管理系1991年4月1日之后出生的学生学号、姓名和出生日期的视图。 CREATE VIEW IS_Student_BirthAS SELECT Sno, Sname, Birthdate FROM IS_Student WHERE Birthdate = 1991/4/112/424.定义带表达式的视图 包含表达式生成的虚拟列的视图为带表达式的视图，一般可以查询不可以更新。13/42示例例4.定义一个查询学生出生年份的视图，内容包括学号，姓名和出生年份。CREATE VIEW BT_S(Sno, Sname, BirthYear)AS SELECT S

5、no, Sname, year(Birthdate) FROM Student14/425. 含分组统计信息的视图 子查询中含GROUP BY子句，视图行列由基本表行列得到，数据只可查询不可更新。15/42示例例5定义一个查询每个学生的学号及平均成绩的视图。 CREATE VIEW S_GAS SELECT Sno,AVG(Grade) AverageGrade FROM SC GROUP BY Sno 16/429.1.3 通过视图查询数据 视图定义好后，可以对其进行查询，通过视图查询数据同基本表一样。17例6. 利用例1建立的视图，查询信息管理系男生的信息。 SELECT * FROM I

6、S_Student WHERE Sex = 男转换成相关基本表的等价查询 SELECT Sno, Sname, Sex, Sage FROM Student WHERE Dept = 信息管理系 AND Sex = 男189.1.2 通过视图查询数据 例7. 查询信息管理系选修了“C001”号课程且成绩大于等于60的学生的学号、姓名和成绩。这个查询可以利用例2的视图实现。 SELECT * FROM V_IS_S1 WHERE Grade = 60199.1.2 通过视图查询数据 例8.查询信息管理系学生的学号、姓名、所选课程名SELECT v.Sno, Sname, Cname FROM I

7、S_Student v JOIN SC ON v.Sno = SC.Sno JOIN Course C ON C.Cno = SC.Cno209.1.2 通过视图查询数据 例9.利用例6建立的视图，查询平均成绩大于等于80的学生的学号和平均成绩。SELECT * FROM S_G WHERE AverageGrade = 80219.1.2 通过视图查询数据 9.1.3 修改和删除视图1.修改视图ALTER VIEW 视图名( 列名 ,.n ) AS 查询语句22示例例10.修改S_G视图，使其统计每个学生的考试平均成绩和修课总门数。ALTER VIEW S_G(Sno, AverageGra

8、de,Count_Cno)AS SELECT Sno, AVG(Grade), Count(*) FROM SC GROUP BY Sno232. 删除视图格式：DROP VIEW 例11删除IS_Student视图。 DROP VIEW IS_Student【注意】删除视图时，如果被删除的视图是其他视图的数据源，那么删除该视图，其导出视图将无法再使用。249.1.4 视图的作用 简化数据查询语句 使用户能从多角度看待同一数据 提高了数据的安全性 提供了一定程度的逻辑独立性 25【练习】1.利用学生_课程数据库建立满足下述要求的视图：统计每个学生的修课总学分，要求列出学生学号和总学分（考试成绩

9、大于等于60才可获得此课程的学分）。3.修改视图，使其查询每个学生的学号、总学分和总的选课门数。2.利用上述视图，查询年龄大于等于20岁的学生中，修课总学分超过10分的学生的姓名、年龄、所在系和修课总学分。9.2 分区视图分区视图可以在一台或多台服务器间水平连接一组成员表中的分区数据，使数据看起来就像来自于一张表。SQL Server分为本地分区视图和分布式分区视图在本地分区视图中，所有参与的表和视图都位于同一个SQL Server实例上。在分布式分区视图中，至少有一个参与表位于不同的（远程）服务器上。27/42定义分区视图通过对成员表使用UNION ALL定义，而且这些成员表具有相同的结构。

10、如果定义视图的语句为下列格式，则将所定义的视图就称为分区视图：SELECT FROM T1UNION ALLSELECT FROM T2UNION ALL.SELECT FROM Tn28/42定义分区视图步骤在定义分区视图前，首先将大型表中的数据拆分成较小的成员表。在每个成员表中通过为分区依据列定义CHECK约束来划分数据范围。然后再定义一个视图，该视图使用UNION ALL将所有成员表中的数据组合成单个结果。29/42创建分区视图1）创建较小成员表CREATE TABLE dbo.DemoTab01( id int NOT NULL， insdate datetime NULL )ON P

11、RIMARY CREATE TABLE dbo.DemoTab02( id int NOT NULL， insdate datetime NULL )ON PRIMARYCREATE TABLE dbo.DemoTab03( id int NOT NULL， insdate datetime NULL)ON PRIMARY 创建分区视图2）为分区依据设定范围ALTER TABLE dbo.DemoTab01 WITH CHECK ADD CONSTRAINT CK_DemoTab01_id CHECK (id BETWEEN 0 AND 99999 ) ALTER TABLE dbo.Demo

12、Tab02 WITH CHECK ADD CONSTRAINT CK_DemoTab02_id CHECK (id BETWEEN 100000 AND 199999 ) ALTER TABLE dbo.DemoTab03 WITH CHECK ADD CONSTRAINT CK_DemoTab03_id CHECK (id BETWEEN 200000 AND 299999 ) 创建分区视图3）定义分区视图CREATE VIEW dbo.V_DemoTab AS SELECT id,insdate FROM dbo.DemoTab01UNION ALL SELECT id,insdate F

13、ROM dbo.DemoTab02 UNION ALL SELECT id,insdate FROM dbo.DemoTab03 创建分区视图4）通过视图进行数据操作INSERT INTO V_DemoTab(id,insdate) SELECT SalesOrderDetailID,ModifiedDate FROM AdventureWorks.Sales.SalesOrderDetail SELECT * FROM dbo.V_DemoTab WHERE id between 3000 and 15000 9.3 索引视图9.3.1 索引视图基本概念9.3.2 定义索引视图34/429.

14、3.1 索引视图基本概念如果在查询中频繁地引用视图，可通过对视图创建唯一聚集索引来提高性能。对视图创建唯一聚集索引后，视图返回的结果集将被存储在数据库中。建有唯一聚集索引的视图就称为索引视图，也称为物化视图。35/429.3.2 定义索引视图定义索引视图时，视图不能引用任何其他视图，只能引用基表。视图引用的所有基表必须与视图位于同一个数据库中，并且所有者也与视图相同。必须使用SCHEMABINDING选项创建视图。视图中的表达式引用的所有函数必须是确定的。表和用户定义函数在定义视图语句中必须由两部分名称组成（架构名.表名）。对视图创建的第一个索引必须是唯一聚集索引。36/42SQL Serve

15、r定义索引视图的额外要求当执行 CREATE VIEW 语句时，ANSI_NULLS 和 QUOTED_IDENTIFIER 选项必须设置为ON。在执行CREATE TABLE 语句创建视图引用的表时，ANSI_NULLS 选项必须设置为 ON。37/42示例（1）设置选项USE MySimpleDB;GOSET NUMERIC_ROUNDABORT OFF;SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON;GO38/42示例（2）定义视图CREATE VIEW Sales.vOrdersWITH SCHEMABINDINGASSELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount) AS Revenue, OrderDate, ProductID, COUNT_BIG(*) AS C