Transcript 3-6下载

数据库原理
第三章：关系数据库标准语言SQL
授课教师：王哲
复习上节课内容
 连接查询
 语法结构（多表查询）
 使用表的别名
 表自身连接
 外连接
 嵌套查询
 集合查询
连接查询
 一般格式：
select [all|distinct] <目标列表达式>[,<目标列表达式>]…
from <表名1>[,<表名2>]…
[where <条件表达式>]
 <条件表达式>一般表示为：
[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>
 连接查询
 等值和非等值连接
 使用表的别名
 自身连接
 以JOIN关键字指定的外连接
嵌套查询
 T-SQL允许SELECT多层嵌套使用，即一个子查询中还可以嵌
套子子查询，用来表示复杂的查询，从而增强SQL的查询能力
。
 子查询通常与IN 、比较运算符及EXISTS谓词结合使用。
 带有In谓词的子查询
 带有比较运算符的子查询
 带有EXISTS谓词的子查询
集合查询
 集合操作的种类
 并操作 UNION
 交操作 INTERSECT
 差操作 EXCEPT
 参加集合操作的各查询结果的列数必须相同；对应项的数据类
型也必须相同。
问题：
1、查询选修课程号为2的学号、学生姓名；
SELECT sno, sname
FROM
Student,sc
WHERE cno='2'
SELECT student.sno,sname
FROM
Student,sc
WHERE cno='2‘ and Student.Sno = SC.Sno
注意：
•加上表名前缀是为了避免重复字段混淆；
•应将两个表中同一学生的元组连起来；
本节课内容
 认识视图
 视图的相关操作
 创建视图
 查询
 更新
 修改
 删除
一、视图
 创建视图（使用界面及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：指出在视图上所进行的修改都要符合查
询语句所指定的限制条件，这样可以确保数据修改后仍可通过视
图看到修改的数据。
例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’
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,sdept）
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. 适当的利用视图可以更清晰的表达查询。
总结
本次课学习内容
 管理视图
 创建、查询、更新、编辑及删除