数据库原理 第三章：关系数据库标准语言 SQL

授课教师：王哲

复习上节课内容连接查询

语法结构（多表查询） 使用表的别名 表自身连接 外连接

嵌套查询集合查询

连接查询 一般格式：

select [all|distinct] < 目标列表达式 >[,< 目标列表达式 >]…

from < 表名 1>[,< 表名 2>]…

[where < 条件表达式 >]

< 条件表达式 > 一般表示为：[< 表名 1>.]< 列名 1> < 比较运算符 > [< 表名 2>.]< 列名 2>

连接查询 等值和非等值连接 使用表的别名 自身连接 以 JOIN 关键字指定的外连接

嵌套查询

T-SQL 允许 SELECT 多层嵌套使用，即一个子查询中还可以嵌套子子查询，用来表示复杂的查询，从而增强 SQL 的查询能力。

子查询通常与 IN 、比较运算符及 EXISTS 谓词结合使用。 带有 In 谓词的子查询 带有比较运算符的子查询 带有 EXISTS 谓词的子查询

集合查询 集合操作的种类

并操作 UNION

交操作 INTERSECT

差操作 EXCEPT

参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同。

问题：

SELECT student.sno,sname

FROM Student,sc

WHERE cno='2‘ and Student.Sno = SC.Sno

1 、查询选修课程号为 2 的学号、学生姓名； SELECT sno, sname

FROM Student,sc

WHERE cno='2'

注意：•加上表名前缀是为了避免重复字段混淆；•应将两个表中同一学生的元组连起来；

本节课内容认识视图视图的相关操作

创建视图 查询 更新 修改 删除

一、视图创建视图（使用界面及 T-SQL 语言）查询视图更新视图修改视图删除视图

1 、认识视图 视图是一种数据库对象，是从一个或者多个数据表或视图中导

出的虚表，视图的结构和数据是对数据表进行查询的结果； 只存放视图的定义，不存放视图对应的数据； 基表中的数据发生变化，从视图中查询出的数据也随之改变。

使用视图的注意事项

只能在当前数据库中创建视图； 视图的命名必须遵循标识符命名规则，不可与表同名； 如果视图中某一列是函数、数学表达式、常量或者来自

多个表的列名相同，则必须为列定义名称。 不能在临时表上创建视图也不能创建临时视图。 定义视图时的查询语句通常不允许含有 order by 子句

或是 into 等关健字。

2 、使用 T-SQL 语句创建视图

用 CREATE VIEW 语句创建视图，其表示形式为：

CREATE VIEW 视图名 [( 列名 1, 列名 2[,…n])]

AS < 查询语句 >

[WITH CHECK OPTION]

查询语句：用来创建视图的 SELECT 语句。但对 SELECT 语句有以下的限制：① 定义视图的用户必须对所参照的表或视图有查询权限，即可执行 SELECT 语句。② 不能使用 COMPUTE或 COMPUTE BY 子句。③ 不能使用 ORDER BY 子句。④ 不能使用 INTO 子句。⑤ 不能在临时表或表变量上创建视图。

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

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

例 1 ：创建所有学生学号、姓名及年龄的信息视图 stu_info；

CREATE VIEW stu_cs

AS

SELECT sno,sname,sage,ssex from student

Where sdept=‘CS’

CREATE VIEW stu_infoAS SELECT sno,sname,sage From student

例 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’

2 ）创建基于多个基表的视图

例 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

CREATE VIEW stu_is_c1( 学号 , 姓名 , 成绩 )

AS

SELECT Student. Sno,Sname,Grade

FROM Student,SC

WHERE Sdept= 'IS' AND

Student.Sno=SC.Sno AND

SC.Cno= '1'

例 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

3 ）创建基于视图的视图

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

CREATE VIEW stu_is_grade

AS

SELECT Sno， Sname， Grade

FROM stu_is

WHERE Grade>=90 ；

4 ）创建带表达式的视图

例 1: 定义一个反映学生出生年份的视图 ;

CREATE VIEW stu_year(Sno ， Sname ， Sbirth)

AS

SELECT Sno ， Sname ，出生年份 =2007-Sage

FROM Student

5 ）创建分组视图

例 1:将学生的学号及其平均成绩定义为一个视图 ;

CREATE VIEW S_G(Sno， Gavg)

AS

SELECT Sno， AVG(Grade)

FROM SC

GROUP BY Sno

3 、查询视图

视图定义后，就可以像查询基本表那样对视图进行查询。 如果与视图相关联的表或视图被删除，则该视图将不能再使用。 使用视图查询时，若其关联的基本表中添加了新字段，则必须重新创

建或者修改视图才能查询到新字段。 查看视图的 T-SQL 定义： sp_helptext

实现视图查询的方法——视图消解法（ View Resolution ） 进行有效性检查，转换成等价的对基本表的查询 执行修正后的查询

例 1 ：查询计算机系年龄大于 20的学生；

SELECT * From stu_cs

WHERE sage>=20

视图消解转换后的查询语句为： SELECT Sno ， Sname ， Sage

FROM Student

WHERE Sdept= ‘CS' AND Sage>=20

例 2 ：查询选修了 1 号课程的信息系学生；

SELECT * FROM stu_is_c1

视图消解转换后的查询语句为： SELECT Student.Sno ， Sname

FROM Student ， SC

WHERE Student.Sno =SC.Sno AND SC.Cno= '1‘ AND

sdept=‘CS’

4 、更新视图

更新视图是指通过视图来插入（ 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_is_grade、 stu_year

、 S_G、 stu_nv 中完全不可更新视图有： stu_sc 、 stu_year、 S_G

1 ）在视图中插入数据使用 insert 语句通过视图向基本表插入数据。由于视图不一定包括表中的所有字段，所以在插入记录时

可能会遇到问题。视图中那些没有出现的字段无法显式插入数据，假如这些

字段不接受系统指派的 null 值，那么插入操作将失败。

例 1: 向视图 stu_info 中插入一个新的学生记录，学号为200515006 ，姓名为王无，年龄为 20 ；Insert into stu_info

Values('200515006','王无 ',20)

等价于：Insert into student（ sno， sname， sage ）Values('200515026','王无 ',20)

例 2: 向视图 stu_is 中插入一个新的学生记录，学号为200515027 ，姓名为王唔，年龄为 20 ；Insert into stu_is

Values(‘200515027’,‘王唔 ',20)

等价于：

Insert into student（ sno， sname， sage， ssex,sdep

t ）Values(‘200515026’,‘王无’ ,20 ,‘男’ ,‘IS’)

例 3: 向视图 stu_is_c1 中插入一个新的学生记录，学号为200515027 ，姓名为王唔，成绩为 60 ；Insert into stu_is_c1

Values(‘200515027’,‘王唔 ',60)

“系统将发出错误信息： 视图或函数 stu_is_c1不可更新，因为修改会影响多个基表”。在表 sc中，只有成绩而主键课程号 cno不确定，显然不能把数据插入 sc表中。

例 4: 向视图 S_G 中插入一个新的学生记录，学号为200515027 ，平均成绩为 60 ；Insert into S_G Values(‘200515027’ , 60)

“系统将发出错误信息： ‘视图或函数 e_view’ 不可更新，因为它包含聚合函数。

2 ）通过视图更新数据

使用 UPDATE语句可以通过视图修改基本表的数据。例 1 ：将视图 stu_info中学号为“ 200515001”的学生姓名改为“张

山”update stu_info

set sname='张山 '

where sno='200515001‘

等价于：update student

set sname='张山 '

where sno='200515001‘

例 2 ：将视图 stu_is中学号为“ 200515004”的学生姓名改为“张珊”update stu_is

set sname=‘张珊 '

where sno='200515004‘

等价于：update student

set sname=‘张珊 '

where sno=‘200515004‘ and sdept=‘IS’ and ssex=‘男’

若更新视图时只影响其中一个表，同时新数据值中含有主键字，系统将接受这个修改操作。

例 3 ：将视图 stu_is_c1 中学号为“ 200515006”的学生成绩改为 75；Update stu_is_c1

Set grade=75

Where sno='200515006‘

等价于：Update sc

Set grade=75

Where sno='200515006‘ and cno=‘1’

3 ）通过视图删除数据使用 DELETE 语句可以通过视图删除基本表的数据。但对于依赖于多个基本表的视图，不能使用 DELETE 语句。例 1 ：删除视图 stu_is 中学号为“ 200515020” 的学生记录

DELETE

FROM stu_is

WHERE Sno= ' 200215020 ‘

等价于：DELETE

FROM Student

WHERE Sno= ‘ 200215020 ’ AND Sdept= ‘IS‘ AND ssex=‘ 男’

5 、修改视图

格式：ALTER VIEW view_name [(column[,...n])]

AS

select_statement

[ WITH CHECK OPTION ]

例 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’

6 、删除视图删除视图的 T-SQL 语句是 DROP VIEW ，格式为：

DROP VIEW { view } [ , …n ]

例 1 ：同时删除视图 stu_is和 s_g 。drop view stu_is , s_g

视图的优点（ P125 ）：

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

总结

本次课学习内容管理视图

创建、查询、更新、编辑及删除