SQLServer中的视图介绍



视图概述

在数据查询中,可以看到数据表设计过程中,考虑到数据的冗余度低、数据一致性等问题,通常对数据表的设计要满足范式的要求,因此也会造成一个实体的所有信息保存在多个表中。当检索数据时,往往在一个表中不能够得到想要的所有信息。 为了解决这种矛盾,在SQL Server中提供了视图。


视图的概念

视图是一种数据库对象,是从一个或者多个数据表或视图中导出的虚表,视图的结构和数据是对数据表进行查询的结果;
只存放视图的定义,不存放视图对应的数据;
基表中的数据发生变化,从视图中查询出的数据也随之改变。


视图的特点

1. 视图能够简化用户的操作,从而简化查询语句
2. 视图使用户能以多种角度看待同一数据,增加可读性
3. 视图对重构数据库提供了一定程度的逻辑独立性;
4. 视图能够对机密数据提供安全保护;
5. 适当的利用视图可以更清晰的表达查询。


使用视图的注意事项

只能在当前数据库中创建视图;
视图的命名必须遵循标识符命名规则,不可与表同名;
如果视图中某一列是函数、数学表达式、常量或者来自多个表的列名相同,则必须为列定义名称。
当视图引用基表或视图被删除,该视图也不能再被使用。
不能在视图上创建全文索引,不能在规则、默认的定义中引用视图。
一个视图最多可以引用1024个列。
视图最多可以嵌套32层。


创建视图

用户可以根据自己的需要创建视图。
创建视图与创建数据表一样,在SQL Server 2008中可以使用SQL Server Management Studio的对象资源管理器和T-SQL语句两种方法。


利用资源管理器创建视图

在SQL Server Management Studio中创建视图的方法主要在视图设计器中完成。



添加好各表后,单击关闭按钮关闭【添加表】对话框。以后需要用可以在关系图窗口的空白处右击。




在关系图窗口中,可以建立表与表之间的联系,只需要将相关联的字段拖动到要连接的字段上即可

在每个表列名前的复选框选择,可以设置视图需要输出的字段,在条件窗格里还可以设置要过滤的查询条件。



单击【执行SQL】按钮     ,运行select语句,查看运行结果。

测试正常后,单击【保存】,在弹出的对话框中输入视图名称,完成视图的创建



用命令创建视图

利用CREATE VIEW语句可以创建视图,该命令的基本语法如下:

CREATE VIEW [ schema_name . ] view_name    

 [ (column [ ,...n ] ) ]

[ WITH ENCRYPTION ]

AS SELECT_statement 

[ WITH CHECK OPTION ]


参数说明如下:

schema_name:视图所属架构名

view_name:视图名

column:视图中所使用的列名。

WITH ENCRYPTION:加密视图。

WITH CHECK OPTION:指出在视图上所进行的修改都要符合查询语句所指定的限制条件,这样可以确保数据修改后仍可通过视图看到修改的数据。


查询语句:

用来创建视图的SELECT语句。但对SELECT语句有以下的限制:

① 定义视图的用户必须对所参照的表或视图有查询权限,即可执行SELECT语句。

② 不能使用COMPUTE或COMPUTE BY子句。

③ 不能使用ORDER BY子句。

④ 不能使用INTO子句。

⑤ 不能在临时表或表变量上创建视图。


例1:创建所有学生学号、姓名及年龄的信息视图stu_info
CREATE  VIEW  stu_info
AS
SELECT sno,sname,sage  From  student


例2: 创建计算机系学生基本信息视图stu_cs;

CREATE  VIEW  stu_cs
AS
SELECT sno,sname,sage,ssex  from  student
Where sdept=‘CS’

例3:创建信息系男学生基本信息视图stu_is,包括学生的学号、姓名及年龄,并要求进行修改和插入操作时仍需保证该视图只有信息系的学生;

CREATE  VIEW  stu_is
AS
SELECT sno,sname, sage from student
Where sdept = ‘IS’ and ssex=‘男’
WITH CHECK OPTION


例4:创建年龄大于20的各学生的学号、姓名及年龄的视图stu_age,并保证对视图文本的修改都要符合年龄大于20这个条件。

CREATE  VIEW  stu_age
AS
SELECT sno,sname,sage from student 
where sage>20
WITH CHECK OPTION

WITH CHECK OPTION:

对Stu_IS及stu_age视图的更新操作:
修改操作:自动加上Sdept= 'IS'的条件
删除操作:自动加上Sdept= 'IS'的条件
插入操作:自动检查Sdept属性值是否为'IS'
如果不是,则拒绝该插入操作
如果没有提供Sdept属性值,则自动定义Sdept为'IS’


创建基于多个基表的视图

例1:建立信息系选修了1号课程的学生视图stu_is_c1;
CREATE VIEW  stu_is_c1 (学号,姓名,成绩)
AS
 SELECT Student. Sno,Sname,Grade
 FROM  Student,SC
 WHERE  Sdept= ‘IS’ AND SC.Cno= '1'
 AND Student.Sno=SC.Sno


例2:创建学生选修课程详细情况视图stu_sc;

CREATE  VIEW  stu_sc
AS
SELECT s.sno,sname,ssex, sage,sdept,c.cno,cname,grade
From  student s,sc,course c
Where s.sno=sc.sno and c.cno=sc.cno

创建基于视图的视图
例1
:建立信息系选修了1号课程且成绩在90分以上的学生的视图;

CREATE VIEW stu_sc_grade
AS
SELECT Sno,Sname,Grade
FROM  stu_sc
WHERE  Grade>=90;


创建代表达式的视图

例1: 定义一个反映学生出生年份的视图;
   CREATE  VIEW   stu_year(Sno,Sname,Sbirth)
   AS
   SELECT Sno,Sname,出生年份=2007-Sage
   FROM  Student

创建分组视图
例1
:将学生的学号及其平均成绩定义为一个视图;
 CREATE  VIEW  S_G(Sno,Gavg)
     AS 
     SELECT Sno,AVG(Grade)
     FROM  SC
     GROUP BY Sno

管理视图

视图创建之后,可以利用SQL Server Management Studio或者T-SQL语句对视图进行管理。例如,在视图使用过程中,可能经常会发生基表改变,而使视图无法正常工作的情况,那么就需要重新修改视图的定义。另外,一个视图如果不再具有使用价值,则可以将其删除。


查看视图定义

1.利用对象管理器查看视图定义


2.利用系统存储过程查看视图定义

sp_help 用于返回视图的特征信息。

sp_helptext 查看视图的定义文本。

sp_depends 查看视图对表的依赖关系和引用的字段。


Exec sp_help 视图名


Exec sp_helptext 视图名


Exec sp_depends 视图名



修改视图定义

1.在“视图设计器”中直接修改。
2. 利用T-SQL语句修改视图定义 利用ALTER VIEW语句可以修改视图定义,该命令的基本语法如下:

ALTER VIEW [ schema_name . ] view_name    

 [ (column [ ,...n ] ) ] [ WITH ENCRYPTION ]

AS SELECT_statement 

[ WITH CHECK OPTION ]

其中,参数的含义与创建视图CREATE VIEW命令中的参数含义相同。


例1:将stu_info视图修改为只包含学生学号、姓名。
Alter  view  stu_info
AS
Select sno,sname  from student

例2:将stu_is视图修改为只包含信息系学生学号、姓名及年龄。
Alter  view  stu_is
AS
Select sno,sname,sage  from student
Where sdept =‘IS’


更名视图
1.利用对象管理器更名视图 选择要更名视图,单击鼠标右键,然后从弹出的快捷菜单中选择“重命名”命令。
2.利用系统存储过程更名视图 利用系统提供的存储过程sp_rename可以对视图进行重命名,其语法格式如下:

sp_rename [ @objname = ] ‘object_name’,

[ @newname = ] ‘new_name’ [, [@objtype = ] ‘object_type’ ]


例:Exec sp_rename ‘v_example1’ ,’v_stu’


删除视图

当一个视图不再需要时,可对其进行删除 操作,以释放存储空间。
删除视图的语法格式如下:    

DROP VIEW view_name [,...n ]

其中,view_name为所要删除的视图的名称。
例:DROP VIEW v_example1

利用视图管理数据
在创建视图之后,可以通过视图来对基表的数据进行管理。但是无论在什么时候对视图的数据进行管理,实际上都是在对视图对应的数据表中的数据进行管理。


利用视图查询数据

利用对象管理器查询视图数据


利用T-SQL语言查询视图数据
例1:查询2007070101同学的“计算机基础”课程成绩。

SELECT * FROM v_example3 WHERE stu_id = ’2007070101’ AND course_name = ‘计算机基础’


利用视图更新数据
更新视图是指通过视图来插入(Insert)、修改(update)和删除(delete)数据;
由于视图是虚表,因此对视图的更新最终要转换为对基本表的更新;
为了防止用户对不属于视图范围内的基本表数据进行操作,可在定义视图时加上 with check option子句。

在关系数据库中,并不是所有的视图都是可更新的,因为有些视图的更新并不能有意义地转换成相应表的查询。
所以要通过视图更新表数据,必须保证视图是可更新视图。
对视图进行更新操作时,还要注意基本表对数据的各种约束和规则要求。

可更新视图的条件
创建视图的select 语句中没有聚合函数,且没有top、group by、having及distinct 关键字;
创建视图的select 语句的各列必须来自于基表(视图)的列,不能是表达式;
视图定义必须是一个简单的SELECT语句,不能带连接、集合操作。即SELECT语句的FROM子句中不能出现多个表,也不能有 JOIN、EXCEPT、UNION、INTERSECT ;


对于视图:stu_info、 stu_cs、 stu_is、 stu_age、 stu_is_c1、 stu_sc、 stu_sc_grade、stu_year、S_G中
完全不可更新视图有:
stu_sc 、stu_year、S_G

在视图中插入数据
使用insert 语句通过视图向基本表插入数据。
由于视图不一定包括表中的所有字段,所以在插入记录时可能会遇到问题。
视图中那些没有出现的字段无法显式插入数据,假如这些字段不接受系统指派的null值,那么插入操作将失败。

例1:创建stu_info视图

CREATE  VIEW  stu_info
AS
SELECT sno,sname,sage  From  student

向视图stu_info中插入一个新的学生记录,学号为200515006,姓名为王无,年龄为20;
Insert into stu_info Values('200515006','王无',20)

等价于:
Insert into student(sno,sname,sage)Values('200515026','王无',20)

通过视图更新数据

CREATE  VIEW  stu_info
AS
SELECT sno,sname,sage  From  student

使用UPDATE语句可以通过视图修改基本表的数据。
例1:将视图stu_info中学号为“200515001”的学生姓名改为“张山”
 update stu_info set sname='张山' where sno='200515001‘
等价于:
 update student set sname='张山' where sno='200515001‘


通过视图删除数据

CREATE  VIEW  stu_is
AS
SELECT sno,sname, sage from student
Where sdept = ‘IS’ and ssex=‘男’
WITH CHECK OPTION

使用DELETE语句可以通过视图删除基本表的数据。但对于依赖于多个基本表的视图,不能使用DELETE语句。
:删除视图stu_is中学号为“200515020”的学生记录
DELETE
FROM  stu_is
WHERE Sno= ' 200215020 ‘
等价于:
DELETE
FROM Student
WHERE Sno= ‘ 200215020 ’ AND Sdept= ‘IS‘ AND ssex=‘男’


相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页