西华师范大学计算机学院
第三章 关系数据库标准语言 SQL
(续 2）
第三章 关系数据库标准语言 SQL
3.1 SQL概述
3.2 数据定义
3.3 查询
3.4 数据更新
3.5 视图
3.6 数据控制
3.7 嵌入式 SQL
3.8 小结
3.4 数 据 更 新
3.4.1 插入数据
3.4.2 修改数据
3.4.3 删除数据
3.4.1 插入数据
? 两种插入数据方式
– 插入单个元组
– 插入子查询结果
1,插入单个元组
? 语句格式
INSERT
INTO <表名 > [(<属性列 1>[,<属性列 2 >…)]
VALUES (<常量 1> [,<常量 2>] … )
? 功能
将新元组插入指定表中。
插入单个元组（续）
[例 1] 将一个新学生记录
（学号,95020；姓名：陈冬；性别：男；所在系,IS；
年龄,18岁）插入到 Student表中。
INSERT
INTO Student
VALUES ('95020','陈冬 ','男 ','IS',18)；
插入单个元组（续）
[例 2] 插入一条选课记录 ( '95020','1 ')。
INSERT
INTO SC(Sno,Cno)
VALUES (' 95020 ',' 1 ')；
新插入的记录在 Grade列上取空值
插入单个元组（续）
? INTO子句
– 指定要插入数据的表名及属性列
– 属性列的顺序可与表定义中的顺序不一致
– 没有指定属性列：表示要插入的是一条完整的元组，
且属性列属性与表定义中的顺序一致
– 指定部分属性列：插入的元组在其余属性列上取空
值
? VALUES子句
– 提供的值必须与 INTO子句匹配
> 值的个数
> 值的类型
2,插入子查询结果
? 语句格式
INSERT
INTO <表名 > [(<属性列 1> [,<属性列 2>… )]
子查询 ；
? 功能
将子查询结果一次插入基本表中, 子查询
结果通常为多个元组 。
? 注意子查询结果与列名序列的一一对应关系 。
插入子查询结果（续）
[例 3] 对每一个系，求学生的平均年龄，并
把结果存入数据库。
第一步：建表
CREATE TABLE Deptage
(Sdept CHAR(15) /* 系名 */
Avgage SMALLINT)； /*学生平均年龄 */
插入子查询结果（续）
第二步：插入数据
INSERT
INTO Deptage(Sdept,Avgage)
SELECT Sdept,AVG(Sage)
FROM Student
GROUP BY Sdept；
插入子查询结果（续）
– INTO子句 (与插入单条元组类似 )
? 指定要插入数据的表名及属性列
? 属性列的顺序可与表定义中的顺序不一致
? 没有指定属性列：表示要插入的是一条完整的元组
? 指定部分属性列：插入的元组在其余属性列上取空值
– 子查询
? SELECT子句目标列必须与 INTO子句匹配
– 值的个数
– 值的类型
插入子查询结果（续）
DBMS在执行插入语句时会检查所插元组是
否破坏表上已定义的完整性规则
– 实体完整性
– 参照完整性
– 用户定义的完整性
? 对于有 NOT NULL约束的属性列是否提供了非空值
? 对于有 UNIQUE约束的属性列是否提供了非重复值
? 对于有值域约束的属性列所提供的属性值是否在值域范围内
3.4 数 据 更 新
3.4.1 插入数据
3.4.2 修改数据
3.4.3 删除数据
3.4.2 修改数据
? 语句格式
UPDATE <表名 >
SET <列名 >=<表达式 >[,<列名 >=<表达式 >]…
[WHERE <条件 >]；
? 功能
修改指定表中满足 WHERE子句条件的元组
修改数据（续）
? 三种修改方式
–修改某一个元组的值
–修改多个元组的值
–带子查询的修改语句
1,修改某一个元组的值
[例 4] 将学生 95001的年龄改为 22岁 。
UPDATE Student
SET Sage=22
WHERE Sno=' 95001 '；
2,修改多个元组的值
[例 5] 将所有学生的年龄增加 1岁 。
UPDATE Student
SET Sage= Sage+1；
修改多个元组的值 (续 )
[例 6] 将信息系所有学生的年龄增加 1岁 。
UPDATE Student
SET Sage= Sage+1
WHERE Sdept=' IS '；
【 例 】 将少数民族考生的分数提高 3％ 。
UPDATE Examinee
SET Exgrade=Exgrade * (1+0.03)
WHERE Nation ＜＞ ′汉 ′
3,带子查询的修改语句
[例 7] 将计算机科学系全体学生的成绩置零 。
UPDATE SC
SET Grade=0
WHERE 'CS'=
(SELECT Sdept
FROM Student
WHERE Student.Sno = SC.Sno)；
【 例 】 将填报一本志愿学校代码为 301的
少数民族考生考分提高 4％ 。
UPDATE Examinee
SET Exgrade= Exgrade * (1+0.04)
WHERE Nation＜＞ ′汉 ′AND Exno IN
（ SELECT Exno
FROM Ewill
WHERE Scode1=301）
修改数据（续）
– SET子句
指定修改方式
要修改的列
修改后取值
– WHERE子句
指定要修改的元组
缺省表示要修改表中的所有元组
修改数据（续）
DBMS在执行修改语句时会检查修改操作
是否破坏表上已定义的完整性规则
– 实体完整性
– 主码不允许修改
– 用户定义的完整性
? NOT NULL约束
? UNIQUE约束
? 值域约束
3.4 数 据 更 新
3.4.1 插入数据
3.4.2 修改数据
3.4.3 删除数据
3.4.3 删除数据
DELETE
FROM <表名 >
[WHERE <条件 >]；
– 功能
?删除指定表中满足 WHERE子句条件的 元组
– WHERE子句
?指定要删除的元组
?缺省表示要修改表中的所有元组
删除数据（续）
? 三种删除方式
–删除某一个元组的值
–删除多个元组的值
–带子查询的删除语句
1,删除某一个元组的值
[例 8] 删除学号为 95019的学生记录。
DELETE
FROM Student
WHERE Sno='95019'；
2,删除多个元组的值
[例 9] 删除 2号课程的所有选课记录。
DELETE
FROM SC
WHERE Cno='2'；
[例 10] 删除所有的学生选课记录。
DELETE
FROM SC；
3,带子查询的删除语句
[例 11] 删除计算机科学系所有学生的选课记录。
DELETE
FROM SC
WHERE 'CS'=
(SELETE Sdept
FROM Student
WHERE Student.Sno=SC.Sno)；
【 例 】 在考生志愿 Ewill表中删除考分少于 200分
的考生志愿信息 。
DELETE
FROM Ewill
WHERE Exno IN
（ SELECT Exno
FROM Examinee
WHERE Exgrade〈 200）
注意, DELETE语句一次只能从一个表中
删除记录, 而不能从多个表中删除记录 。 要
删除多个表中的记录, 就要写多个 DELETE语
句 。
更新数据与数据一致性
DBMS在执行插入, 删除, 更新语句时必
须保证数据库一致性
?必须有事务的概念和原子性
?完整性检查和保证
第三章 关系数据库标准语言 SQL
3.1 SQL概述
3.2 数据定义
3.3 查询
3.4 数据更新
3.5 视图
3.6 数据控制
3.7 嵌入式 SQL
3.8 小结
3.5 视 图
视图的特点
? 虚表，是从一个或几个基本表（或视图）
导出的表
? 只存放视图的定义，不会出现数据冗余
? 基表中的数据发生变化，从视图中查询
出的数据也随之改变
3.5 视 图
基于视图的操作
? 查询
? 删除
? 受限更新
? 定义基于该视图的新视图
3.5 视 图
3.5.1 定义视图
3.5.2 查询视图
3.5.3 更新视图
3.5.4 视图的作用
1,建立视图
? 语句格式
CREATE VIEW
<视图名 > [(<列名 > [,<列名 >]…)]
AS <子查询 >
[WITH CHECK OPTION]；
【 例 】 把计算机系学生学号和
姓名作为视图：
CREATE VIEW CSStudent AS
SELECT StudentNo,StudentName
FROM Student
WHERE Dept=‘计算机系 ’ ；
? 可把 CSStudent看作是一个虚拟的表 。
? 为与这种虚拟表相区别, 又把实际存在的表称
为基本表 。
【 例 】
学生关系 Student的如下所示：
StudentNo StudentName Age Dept
2002101 王云 19 计算机
2002102 张小华 20 化学
2002103 孟建 20 机械
那么“关系” CSStudent应该是：
StudentNo StudentName
2002101 王云
建立视图（续）
DBMS执行 CREATE VIEW语句时只是把
视图的定义存入数据字典，并不执行其中
的 SELECT语句。
在对视图查询时，按视图的定义从基本表
中将数据查出。
组成视图的属性列名
全部省略或全部指定
– 省略,
由子查询中 SELECT目标列中的诸字段组成
– 明确指定视图的所有列名,
(1) 某个目标列是集函数或列表达式
(2) 多表连接时选出了几个同名列作为视图的字段
(3) 需要在视图中为某个列启用新的更合适的名字
行列子集视图
[例 1] 建立信息系学生的视图。
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept= 'IS'；
【 例 】 建立关于陕西西安考生信息的视图 。
CREATE VIEW SXExamer
AS
SELECT Exno,Exgrade,Exname,Sex,Nation
FROM Examinee
WHERE City=′陕西西安 ′
这里, 视图中列名及顺序与 SELECT子句中一
样, 所以视图名 SXExamer后列名序列被省略 。
此例中视图是从单个基本表 （ Examinee） 导出
的, 且只是去掉了基本表的某些行和某些列, 但
保留了码 （ Exno）, 这类视图被称为行列子集视
图 。
建立视图（续）
? WITH CHECK OPTION
透过视图进行增删改操作时，不得破坏视
图定义中的谓词条件
（即子查询中的条件表达式）
WITH CHECK OPTION的视图
[例 2] 建立信息系学生的视图，并要求透过该视
图进行的更新操作只涉及信息系学生。
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept= 'IS'
WITH CHECK OPTION；
对 IS_Student视图的更新操作
? 修改操作,DBMS自动加上 Sdept= 'IS'的条件
? 删除操作,DBMS自动加上 Sdept= 'IS'的条件
? 插入操作,DBMS自动检查 Sdept属性值是否为 'IS'
– 如果不是，则拒绝该插入操作
– 如果没有提供 Sdept属性值，则自动定义 Sdept
为 'IS'
基于多个基表的视图
[例 4] 建立信息系选修了 1号课程的学生视图。
CREATE VIEW IS_S1(Sno,Sname,Grade)
AS
SELECT Student.Sno,Sname,Grade
FROM Student,SC
WHERE Sdept= 'IS' AND Student.Sno=SC.Sno AND
SC.Cno= '1'；
[例 ]定义一个选修, 程序设计, 课的计算机系学生该课成绩单视图
CREATE VIEW
S1(SNo,SName,Score) AS
SELECT S.SNo,S.SName,SC.Score
FROM S,SC,C
WHERE S.SNo=SC.Sno AND SC.CNo=C.Cno AND Dept=‘ 计算机系 ’ AND
CName=‘程序设计 ’ ；
基于视图的视图
[例 5] 建立信息系选修了 1号课程且成绩在 90分
以上的学生的视图。
CREATE VIEW IS_S2
AS
SELECT Sno,Sname,Grade
FROM IS_S1
WHERE Grade>=90；
带表达式的视图
[例 6] 定义一个反映学生出生年份的视图。
CREATE VIEW BT_S(Sno,Sname,Sbirth)
AS
SELECT Sno,Sname,2000-Sage
FROM Student
设置一些派生属性列,也 称为虚拟列 --Sbirth
带表达式的视图必须明确定义组成视图的各个属
性列名
建立分组视图
[例 7] 将学生的学号及他的平均成绩定义为一个视图
假设 SC表中“成绩”列 Grade为数字型
CREAT VIEW S_G(Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno；
建立视图（续）
? 一类不易扩充的视图
– 以 SELECT * 方式创建的视图可扩充性差，
应尽可能避免
建立视图（续）
[例 8]将 Student表中所有女生记录定义为一个视图
CREATE VIEW
F_Student1(stdnum,name,sex,age,dept)
AS SELECT *
FROM Student
WHERE Ssex='女 '；
缺点：修改基表 Student的结构后,Student表
与 F_Student1视图的映象关系被破坏，
导致该视图不能正确工作。
建立视图（续）
CREATE VIEW
F_Student2 (stdnum,name,sex,age,dept)
AS SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student
WHERE Ssex='女 '；
为基表 Student增加属性列不会破坏 Student表
与 F_Student2视图的映象关系。
常见的视图形式
– 行列子集视图
– WITH CHECK OPTION的视图
– 基于多个基表的视图
– 基于视图的视图
– 带表达式的视图
– 分组视图
2,删除视图
? DROP VIEW <视图名 >；
– 该语句从数据字典中删除指定的视图定义
– 由该视图导出的其他视图定义仍在数据字典
中，但已不能使用，必须显式删除
– 删除基表时，由该基表导出的所有视图定义
都必须显式删除
删除视图 (续）
[例 9] 删除视图 IS_S1
DROP VIEW IS_S1；
3.5 视 图
3.5.1 定义视图
3.5.2 查询视图
3.5.3 更新视图
3.5.4 视图的作用
3.5.2 查询视图
? 从用户角度：查询视图与查询基本表相同
? DBMS实现 视图查询的方法
– 实体化视图（ View Materialization）
? 有效性检查：检查所查询的视图是否存在
? 执行视图定义，将视图临时实体化，生成临时表
? 查询视图转换为查询临时表
? 查询完毕删除被实体化的视图 (临时表 )
new
查询视图（续）
– 视图消解法（ View Resolution）
? 进行有效性检查，检查查询的表、视图等是否存在。如果
存在，则从数据字典中取出视图的定义
? 把视图定义中的子查询与用户的查询结合起来，转换成等
价的对基本表的查询
? 执行 修正 后的查询
查询视图（续）
[例 1] 在信息系学生的视图中找出年龄小于 20岁的学生。
SELECT Sno,Sage
FROM IS_Student
WHERE Sage<20；
IS_Student视图的定义 (视图定义例 1)：
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept= 'IS‘；
查询视图（续）
– 视图实体化法
– 视图消解法
转换后的查询语句为：
SELECT Sno,Sage
FROM Student
WHERE Sdept= 'IS' AND Sage<20；
查询视图（续）
[例 2] 查询信息系选修了 1号课程的学生
SELECT Sno,Sname
FROM IS_Student,SC
WHERE IS_Student.Sno =SC.Sno AND
SC.Cno= '1'；
查询视图（续）
? 视图消解法的局限
– 有些情况下，视图消解法不能生成正确查询。
采用视图消解法的 DBMS会限制这类查询。
查询视图（续）
[例 3]在 S_G视图中查询平均成绩在 90分以上的学生学号
和平均成绩
SELECT *
FROM S_G
WHERE Gavg>=90；
S_G视图定义：
CREATE VIEW S_G (Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno；
查询转换
错误：
SELECT Sno,AVG(Grade)
FROM SC
WHERE AVG(Grade)>=90 /*where子句中不能用集函
数作为条件表达式 */
GROUP BY Sno；
正确：
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90；
3.5 视 图
3.5.1 定义视图
3.5.2 查询视图
3.5.3 更新视图
3.5.4 视图的作用
3.5.3 更新视图
? 用户角度：更新视图与更新基本表相同
? DBMS实现视图更新的方法
– 视图实体化法（ View Materialization）
– 视图消解法（ View Resolution）
? 指定 WITH CHECK OPTION子句后
DBMS在更新视图时会进行检查，防止用户通过视图
对 不属于视图范围内 的基本表数据进行更新
更新视图（续）
[例 1] 将信息系学生视图 IS_Student中学号 95002
的学生姓名改为“刘辰”。
UPDATE IS_Student
SET Sname= '刘辰 '
WHERE Sno= '95002'；
转换后的语句：
UPDATE Student
SET Sname= '刘辰 '
WHERE Sno= '95002' AND Sdept= 'IS'；
更新视图（续）
[例 2] 向信息系学生视图 IS_S中插入一个新的学
生记录,95029，赵新,20岁
INSERT
INTO IS_Student
VALUES(‘95029’，‘赵新’,20)；
转换为对基本表的更新：
INSERT
INTO Student(Sno,Sname,Sage,Sdept)
VALUES('95029','赵新 ',20,'IS' )；
更新视图（续）
[例 3] 删除视图 CS_S中学号为 95029的记录
DELETE
FROM IS_Student
WHERE Sno= '95029'；
转换为对基本表的更新：
DELETE
FROM Student
WHERE Sno= '95029' AND Sdept= 'IS'；
更新视图的限制
? 一些视图是不可更新的，因为对这些视图的更
新不能唯一地有意义地转换成对相应基本表的
更新 (对两类方法均如此 )
例：视图 S_G为不可更新视图。
CREATE VIEW S_G (Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno；
更新视图（续）
对于如下更新语句：
UPDATE S_G
SET Gavg=90
WHERE Sno= '95001'；
无论实体化法还是消解法都无法将其转换成对
基本表 SC的更新
SQL Server 2000规定：
① 如果一个视图是从多个基本表使用连接操作导
出的, 则不允许对这个视图执行更新操作 。
② 如 果 在 视 图 定 义 中 使 用 了 聚 集 函 数 或
DISTINCT短语或 GROUP BY子句, 则不允许
对该视图执行更新操作 。
③ 如果视图的列的值为表达式或常数, 则不允
许该这视图执行更新操作 。
④ 如果视图为行列子集视图, 则可以对该视图
执行更新操作 。
视图的可更新性
【 例 】 设一个包含民族名及各民族考生平均成
绩的视图定义如下：
CREAT VIEW N-ag（ Nname,Agrade）
AS
SELECT Nation,AVG(Exgrade)
FROM Examinee
GROUP BY Nation
在此视图的定义中, 包含了 AVG聚合函
数和 GROUP BY子句, 根据规定 ②, N-ag视
图不能更新 。 例如, 要在 N-ag视图中更新回
族考生的平均成绩为 90分, 显然这是无法转换
成对基本表 Examinee的更新的 。
综合示例,use northwind
create view customerview as
select customerid,companyname
from customers
--1/插入数据
insert customerview
values('test1','test company')
select * from customerview //查询视图的数据
--2,通过视图修改数据
update customerview
set customerid='test2'
where customerid='test1'
--3,通过视图删除数据
delete customerview
where customerid='test2'
3.5 视 图
3.5.1 定义视图
3.5.2 查询视图
3.5.3 更新视图
3.5.4 视图的作用
1,视图能够 简化 用户的操作
? 数据库的完整结构往往是复杂的,但用户在
使用数据库时,经常只使用其中的部分数据
。通过定义视图,就可满足用户的需要,使
用户眼中的数据库结构简单,清晰,简化了
用户对数据库的操作。
2,视图使用户能以 多种角度 看待同一数据
? 视图机制能使不同用户以不同方式看待
同一数据，适应数据库共享的需要
3.视图对重构数据库提供了一定程度的逻辑独立性
例：数据库逻辑结构发生改变
学生关系 Student(Sno,Sname,Ssex,Sage,Sdept)
“垂直”地分成两个基本表：
SX(Sno,Sname,Sage)
SY(Sno,Ssex,Sdept)
3.视图对重构数据库提供了一定程度的逻辑独立性
通过建立一个视图 Student：
CREATE VIEW Student(Sno,Sname,Ssex,Sage,Sdept)
AS
SELECT SX.Sno,SX.Sname,SY.Ssex,SX.Sage,SY.Sdept
FROM SX,SY
WHERE SX.Sno=SY.Sno；
使用户的外模式保持不变，从而对原 Student表的
查询程序不必修改
3,视图对重构数据库提供了一定程度的 逻辑独立
性
? 物理独立性与逻辑独立性的概念
? 视图在一定程度上保证了数据的逻辑独立性
? 视图只能在一定程度上提供数据的逻辑独立性
– 由于对视图的更新是有条件的，因此应用程序中修
改数据的语句可能仍会因基本表结构的改变而改变。
4,视图能够对机密数据提供安全保护
? 对不同用户定义不同视图，使每个用户
只能看到他有权看到的数据
? 通过 WITH CHECK OPTION对关键数据
定义操作时间限制
建立视图（续）
[例 3 ] 建立 1号课程的选课视图，并要求透过该视图进
行的更新操作只涉及 1号课程，同时对该视图的任何操
作只能在工作时间进行。
CREATE VIEW IS_SC
AS
SELECT Sno,Cno,Grade
FROM SC
WHERE Cno= '1'
AND TO_CHAR(SYSDATE,'HH24') BETWEEN 9 AND 17
AND TO_CHAR(SYSDATE,'D') BETWEEN 2 AND 6
WITH CHECK OPTION; new
第三章 关系数据库标准语言 SQL
(续 2）
第三章 关系数据库标准语言 SQL
3.1 SQL概述
3.2 数据定义
3.3 查询
3.4 数据更新
3.5 视图
3.6 数据控制
3.7 嵌入式 SQL
3.8 小结
3.4 数 据 更 新
3.4.1 插入数据
3.4.2 修改数据
3.4.3 删除数据
3.4.1 插入数据
? 两种插入数据方式
– 插入单个元组
– 插入子查询结果
1,插入单个元组
? 语句格式
INSERT
INTO <表名 > [(<属性列 1>[,<属性列 2 >…)]
VALUES (<常量 1> [,<常量 2>] … )
? 功能
将新元组插入指定表中。
插入单个元组（续）
[例 1] 将一个新学生记录
（学号,95020；姓名：陈冬；性别：男；所在系,IS；
年龄,18岁）插入到 Student表中。
INSERT
INTO Student
VALUES ('95020','陈冬 ','男 ','IS',18)；
插入单个元组（续）
[例 2] 插入一条选课记录 ( '95020','1 ')。
INSERT
INTO SC(Sno,Cno)
VALUES (' 95020 ',' 1 ')；
新插入的记录在 Grade列上取空值
插入单个元组（续）
? INTO子句
– 指定要插入数据的表名及属性列
– 属性列的顺序可与表定义中的顺序不一致
– 没有指定属性列：表示要插入的是一条完整的元组，
且属性列属性与表定义中的顺序一致
– 指定部分属性列：插入的元组在其余属性列上取空
值
? VALUES子句
– 提供的值必须与 INTO子句匹配
> 值的个数
> 值的类型
2,插入子查询结果
? 语句格式
INSERT
INTO <表名 > [(<属性列 1> [,<属性列 2>… )]
子查询 ；
? 功能
将子查询结果一次插入基本表中, 子查询
结果通常为多个元组 。
? 注意子查询结果与列名序列的一一对应关系 。
插入子查询结果（续）
[例 3] 对每一个系，求学生的平均年龄，并
把结果存入数据库。
第一步：建表
CREATE TABLE Deptage
(Sdept CHAR(15) /* 系名 */
Avgage SMALLINT)； /*学生平均年龄 */
插入子查询结果（续）
第二步：插入数据
INSERT
INTO Deptage(Sdept,Avgage)
SELECT Sdept,AVG(Sage)
FROM Student
GROUP BY Sdept；
插入子查询结果（续）
– INTO子句 (与插入单条元组类似 )
? 指定要插入数据的表名及属性列
? 属性列的顺序可与表定义中的顺序不一致
? 没有指定属性列：表示要插入的是一条完整的元组
? 指定部分属性列：插入的元组在其余属性列上取空值
– 子查询
? SELECT子句目标列必须与 INTO子句匹配
– 值的个数
– 值的类型
插入子查询结果（续）
DBMS在执行插入语句时会检查所插元组是
否破坏表上已定义的完整性规则
– 实体完整性
– 参照完整性
– 用户定义的完整性
? 对于有 NOT NULL约束的属性列是否提供了非空值
? 对于有 UNIQUE约束的属性列是否提供了非重复值
? 对于有值域约束的属性列所提供的属性值是否在值域范围内
3.4 数 据 更 新
3.4.1 插入数据
3.4.2 修改数据
3.4.3 删除数据
3.4.2 修改数据
? 语句格式
UPDATE <表名 >
SET <列名 >=<表达式 >[,<列名 >=<表达式 >]…
[WHERE <条件 >]；
? 功能
修改指定表中满足 WHERE子句条件的元组
修改数据（续）
? 三种修改方式
–修改某一个元组的值
–修改多个元组的值
–带子查询的修改语句
1,修改某一个元组的值
[例 4] 将学生 95001的年龄改为 22岁 。
UPDATE Student
SET Sage=22
WHERE Sno=' 95001 '；
2,修改多个元组的值
[例 5] 将所有学生的年龄增加 1岁 。
UPDATE Student
SET Sage= Sage+1；
修改多个元组的值 (续 )
[例 6] 将信息系所有学生的年龄增加 1岁 。
UPDATE Student
SET Sage= Sage+1
WHERE Sdept=' IS '；
【 例 】 将少数民族考生的分数提高 3％ 。
UPDATE Examinee
SET Exgrade=Exgrade * (1+0.03)
WHERE Nation ＜＞ ′汉 ′
3,带子查询的修改语句
[例 7] 将计算机科学系全体学生的成绩置零 。
UPDATE SC
SET Grade=0
WHERE 'CS'=
(SELECT Sdept
FROM Student
WHERE Student.Sno = SC.Sno)；
【 例 】 将填报一本志愿学校代码为 301的
少数民族考生考分提高 4％ 。
UPDATE Examinee
SET Exgrade= Exgrade * (1+0.04)
WHERE Nation＜＞ ′汉 ′AND Exno IN
（ SELECT Exno
FROM Ewill
WHERE Scode1=301）
修改数据（续）
– SET子句
指定修改方式
要修改的列
修改后取值
– WHERE子句
指定要修改的元组
缺省表示要修改表中的所有元组
修改数据（续）
DBMS在执行修改语句时会检查修改操作
是否破坏表上已定义的完整性规则
– 实体完整性
– 主码不允许修改
– 用户定义的完整性
? NOT NULL约束
? UNIQUE约束
? 值域约束
3.4 数 据 更 新
3.4.1 插入数据
3.4.2 修改数据
3.4.3 删除数据
3.4.3 删除数据
DELETE
FROM <表名 >
[WHERE <条件 >]；
– 功能
?删除指定表中满足 WHERE子句条件的 元组
– WHERE子句
?指定要删除的元组
?缺省表示要修改表中的所有元组
删除数据（续）
? 三种删除方式
–删除某一个元组的值
–删除多个元组的值
–带子查询的删除语句
1,删除某一个元组的值
[例 8] 删除学号为 95019的学生记录。
DELETE
FROM Student
WHERE Sno='95019'；
2,删除多个元组的值
[例 9] 删除 2号课程的所有选课记录。
DELETE
FROM SC
WHERE Cno='2'；
[例 10] 删除所有的学生选课记录。
DELETE
FROM SC；
3,带子查询的删除语句
[例 11] 删除计算机科学系所有学生的选课记录。
DELETE
FROM SC
WHERE 'CS'=
(SELETE Sdept
FROM Student
WHERE Student.Sno=SC.Sno)；
【 例 】 在考生志愿 Ewill表中删除考分少于 200分
的考生志愿信息 。
DELETE
FROM Ewill
WHERE Exno IN
（ SELECT Exno
FROM Examinee
WHERE Exgrade〈 200）
注意, DELETE语句一次只能从一个表中
删除记录, 而不能从多个表中删除记录 。 要
删除多个表中的记录, 就要写多个 DELETE语
句 。
更新数据与数据一致性
DBMS在执行插入, 删除, 更新语句时必
须保证数据库一致性
?必须有事务的概念和原子性
?完整性检查和保证
第三章 关系数据库标准语言 SQL
3.1 SQL概述
3.2 数据定义
3.3 查询
3.4 数据更新
3.5 视图
3.6 数据控制
3.7 嵌入式 SQL
3.8 小结
3.5 视 图
视图的特点
? 虚表，是从一个或几个基本表（或视图）
导出的表
? 只存放视图的定义，不会出现数据冗余
? 基表中的数据发生变化，从视图中查询
出的数据也随之改变
3.5 视 图
基于视图的操作
? 查询
? 删除
? 受限更新
? 定义基于该视图的新视图
3.5 视 图
3.5.1 定义视图
3.5.2 查询视图
3.5.3 更新视图
3.5.4 视图的作用
1,建立视图
? 语句格式
CREATE VIEW
<视图名 > [(<列名 > [,<列名 >]…)]
AS <子查询 >
[WITH CHECK OPTION]；
【 例 】 把计算机系学生学号和
姓名作为视图：
CREATE VIEW CSStudent AS
SELECT StudentNo,StudentName
FROM Student
WHERE Dept=‘计算机系 ’ ；
? 可把 CSStudent看作是一个虚拟的表 。
? 为与这种虚拟表相区别, 又把实际存在的表称
为基本表 。
【 例 】
学生关系 Student的如下所示：
StudentNo StudentName Age Dept
2002101 王云 19 计算机
2002102 张小华 20 化学
2002103 孟建 20 机械
那么“关系” CSStudent应该是：
StudentNo StudentName
2002101 王云
建立视图（续）
DBMS执行 CREATE VIEW语句时只是把
视图的定义存入数据字典，并不执行其中
的 SELECT语句。
在对视图查询时，按视图的定义从基本表
中将数据查出。
组成视图的属性列名
全部省略或全部指定
– 省略,
由子查询中 SELECT目标列中的诸字段组成
– 明确指定视图的所有列名,
(1) 某个目标列是集函数或列表达式
(2) 多表连接时选出了几个同名列作为视图的字段
(3) 需要在视图中为某个列启用新的更合适的名字
行列子集视图
[例 1] 建立信息系学生的视图。
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept= 'IS'；
【 例 】 建立关于陕西西安考生信息的视图 。
CREATE VIEW SXExamer
AS
SELECT Exno,Exgrade,Exname,Sex,Nation
FROM Examinee
WHERE City=′陕西西安 ′
这里, 视图中列名及顺序与 SELECT子句中一
样, 所以视图名 SXExamer后列名序列被省略 。
此例中视图是从单个基本表 （ Examinee） 导出
的, 且只是去掉了基本表的某些行和某些列, 但
保留了码 （ Exno）, 这类视图被称为行列子集视
图 。
建立视图（续）
? WITH CHECK OPTION
透过视图进行增删改操作时，不得破坏视
图定义中的谓词条件
（即子查询中的条件表达式）
WITH CHECK OPTION的视图
[例 2] 建立信息系学生的视图，并要求透过该视
图进行的更新操作只涉及信息系学生。
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept= 'IS'
WITH CHECK OPTION；
对 IS_Student视图的更新操作
? 修改操作,DBMS自动加上 Sdept= 'IS'的条件
? 删除操作,DBMS自动加上 Sdept= 'IS'的条件
? 插入操作,DBMS自动检查 Sdept属性值是否为 'IS'
– 如果不是，则拒绝该插入操作
– 如果没有提供 Sdept属性值，则自动定义 Sdept
为 'IS'
基于多个基表的视图
[例 4] 建立信息系选修了 1号课程的学生视图。
CREATE VIEW IS_S1(Sno,Sname,Grade)
AS
SELECT Student.Sno,Sname,Grade
FROM Student,SC
WHERE Sdept= 'IS' AND Student.Sno=SC.Sno AND
SC.Cno= '1'；
[例 ]定义一个选修, 程序设计, 课的计算机系学生该课成绩单视图
CREATE VIEW
S1(SNo,SName,Score) AS
SELECT S.SNo,S.SName,SC.Score
FROM S,SC,C
WHERE S.SNo=SC.Sno AND SC.CNo=C.Cno AND Dept=‘ 计算机系 ’ AND
CName=‘程序设计 ’ ；
基于视图的视图
[例 5] 建立信息系选修了 1号课程且成绩在 90分
以上的学生的视图。
CREATE VIEW IS_S2
AS
SELECT Sno,Sname,Grade
FROM IS_S1
WHERE Grade>=90；
带表达式的视图
[例 6] 定义一个反映学生出生年份的视图。
CREATE VIEW BT_S(Sno,Sname,Sbirth)
AS
SELECT Sno,Sname,2000-Sage
FROM Student
设置一些派生属性列,也 称为虚拟列 --Sbirth
带表达式的视图必须明确定义组成视图的各个属
性列名
建立分组视图
[例 7] 将学生的学号及他的平均成绩定义为一个视图
假设 SC表中“成绩”列 Grade为数字型
CREAT VIEW S_G(Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno；
建立视图（续）
? 一类不易扩充的视图
– 以 SELECT * 方式创建的视图可扩充性差，
应尽可能避免
建立视图（续）
[例 8]将 Student表中所有女生记录定义为一个视图
CREATE VIEW
F_Student1(stdnum,name,sex,age,dept)
AS SELECT *
FROM Student
WHERE Ssex='女 '；
缺点：修改基表 Student的结构后,Student表
与 F_Student1视图的映象关系被破坏，
导致该视图不能正确工作。
建立视图（续）
CREATE VIEW
F_Student2 (stdnum,name,sex,age,dept)
AS SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student
WHERE Ssex='女 '；
为基表 Student增加属性列不会破坏 Student表
与 F_Student2视图的映象关系。
常见的视图形式
– 行列子集视图
– WITH CHECK OPTION的视图
– 基于多个基表的视图
– 基于视图的视图
– 带表达式的视图
– 分组视图
2,删除视图
? DROP VIEW <视图名 >；
– 该语句从数据字典中删除指定的视图定义
– 由该视图导出的其他视图定义仍在数据字典
中，但已不能使用，必须显式删除
– 删除基表时，由该基表导出的所有视图定义
都必须显式删除
删除视图 (续）
[例 9] 删除视图 IS_S1
DROP VIEW IS_S1；
3.5 视 图
3.5.1 定义视图
3.5.2 查询视图
3.5.3 更新视图
3.5.4 视图的作用
3.5.2 查询视图
? 从用户角度：查询视图与查询基本表相同
? DBMS实现 视图查询的方法
– 实体化视图（ View Materialization）
? 有效性检查：检查所查询的视图是否存在
? 执行视图定义，将视图临时实体化，生成临时表
? 查询视图转换为查询临时表
? 查询完毕删除被实体化的视图 (临时表 )
new
查询视图（续）
– 视图消解法（ View Resolution）
? 进行有效性检查，检查查询的表、视图等是否存在。如果
存在，则从数据字典中取出视图的定义
? 把视图定义中的子查询与用户的查询结合起来，转换成等
价的对基本表的查询
? 执行 修正 后的查询
查询视图（续）
[例 1] 在信息系学生的视图中找出年龄小于 20岁的学生。
SELECT Sno,Sage
FROM IS_Student
WHERE Sage<20；
IS_Student视图的定义 (视图定义例 1)：
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept= 'IS‘；
查询视图（续）
– 视图实体化法
– 视图消解法
转换后的查询语句为：
SELECT Sno,Sage
FROM Student
WHERE Sdept= 'IS' AND Sage<20；
查询视图（续）
[例 2] 查询信息系选修了 1号课程的学生
SELECT Sno,Sname
FROM IS_Student,SC
WHERE IS_Student.Sno =SC.Sno AND
SC.Cno= '1'；
查询视图（续）
? 视图消解法的局限
– 有些情况下，视图消解法不能生成正确查询。
采用视图消解法的 DBMS会限制这类查询。
查询视图（续）
[例 3]在 S_G视图中查询平均成绩在 90分以上的学生学号
和平均成绩
SELECT *
FROM S_G
WHERE Gavg>=90；
S_G视图定义：
CREATE VIEW S_G (Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno；
查询转换
错误：
SELECT Sno,AVG(Grade)
FROM SC
WHERE AVG(Grade)>=90 /*where子句中不能用集函
数作为条件表达式 */
GROUP BY Sno；
正确：
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90；
3.5 视 图
3.5.1 定义视图
3.5.2 查询视图
3.5.3 更新视图
3.5.4 视图的作用
3.5.3 更新视图
? 用户角度：更新视图与更新基本表相同
? DBMS实现视图更新的方法
– 视图实体化法（ View Materialization）
– 视图消解法（ View Resolution）
? 指定 WITH CHECK OPTION子句后
DBMS在更新视图时会进行检查，防止用户通过视图
对 不属于视图范围内 的基本表数据进行更新
更新视图（续）
[例 1] 将信息系学生视图 IS_Student中学号 95002
的学生姓名改为“刘辰”。
UPDATE IS_Student
SET Sname= '刘辰 '
WHERE Sno= '95002'；
转换后的语句：
UPDATE Student
SET Sname= '刘辰 '
WHERE Sno= '95002' AND Sdept= 'IS'；
更新视图（续）
[例 2] 向信息系学生视图 IS_S中插入一个新的学
生记录,95029，赵新,20岁
INSERT
INTO IS_Student
VALUES(‘95029’，‘赵新’,20)；
转换为对基本表的更新：
INSERT
INTO Student(Sno,Sname,Sage,Sdept)
VALUES('95029','赵新 ',20,'IS' )；
更新视图（续）
[例 3] 删除视图 CS_S中学号为 95029的记录
DELETE
FROM IS_Student
WHERE Sno= '95029'；
转换为对基本表的更新：
DELETE
FROM Student
WHERE Sno= '95029' AND Sdept= 'IS'；
更新视图的限制
? 一些视图是不可更新的，因为对这些视图的更
新不能唯一地有意义地转换成对相应基本表的
更新 (对两类方法均如此 )
例：视图 S_G为不可更新视图。
CREATE VIEW S_G (Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno；
更新视图（续）
对于如下更新语句：
UPDATE S_G
SET Gavg=90
WHERE Sno= '95001'；
无论实体化法还是消解法都无法将其转换成对
基本表 SC的更新
SQL Server 2000规定：
① 如果一个视图是从多个基本表使用连接操作导
出的, 则不允许对这个视图执行更新操作 。
② 如 果 在 视 图 定 义 中 使 用 了 聚 集 函 数 或
DISTINCT短语或 GROUP BY子句, 则不允许
对该视图执行更新操作 。
③ 如果视图的列的值为表达式或常数, 则不允
许该这视图执行更新操作 。
④ 如果视图为行列子集视图, 则可以对该视图
执行更新操作 。
视图的可更新性
【 例 】 设一个包含民族名及各民族考生平均成
绩的视图定义如下：
CREAT VIEW N-ag（ Nname,Agrade）
AS
SELECT Nation,AVG(Exgrade)
FROM Examinee
GROUP BY Nation
在此视图的定义中, 包含了 AVG聚合函
数和 GROUP BY子句, 根据规定 ②, N-ag视
图不能更新 。 例如, 要在 N-ag视图中更新回
族考生的平均成绩为 90分, 显然这是无法转换
成对基本表 Examinee的更新的 。
综合示例,use northwind
create view customerview as
select customerid,companyname
from customers
--1/插入数据
insert customerview
values('test1','test company')
select * from customerview //查询视图的数据
--2,通过视图修改数据
update customerview
set customerid='test2'
where customerid='test1'
--3,通过视图删除数据
delete customerview
where customerid='test2'
3.5 视 图
3.5.1 定义视图
3.5.2 查询视图
3.5.3 更新视图
3.5.4 视图的作用
1,视图能够 简化 用户的操作
? 数据库的完整结构往往是复杂的,但用户在
使用数据库时,经常只使用其中的部分数据
。通过定义视图,就可满足用户的需要,使
用户眼中的数据库结构简单,清晰,简化了
用户对数据库的操作。
2,视图使用户能以 多种角度 看待同一数据
? 视图机制能使不同用户以不同方式看待
同一数据，适应数据库共享的需要
3.视图对重构数据库提供了一定程度的逻辑独立性
例：数据库逻辑结构发生改变
学生关系 Student(Sno,Sname,Ssex,Sage,Sdept)
“垂直”地分成两个基本表：
SX(Sno,Sname,Sage)
SY(Sno,Ssex,Sdept)
3.视图对重构数据库提供了一定程度的逻辑独立性
通过建立一个视图 Student：
CREATE VIEW Student(Sno,Sname,Ssex,Sage,Sdept)
AS
SELECT SX.Sno,SX.Sname,SY.Ssex,SX.Sage,SY.Sdept
FROM SX,SY
WHERE SX.Sno=SY.Sno；
使用户的外模式保持不变，从而对原 Student表的
查询程序不必修改
3,视图对重构数据库提供了一定程度的 逻辑独立
性
? 物理独立性与逻辑独立性的概念
? 视图在一定程度上保证了数据的逻辑独立性
? 视图只能在一定程度上提供数据的逻辑独立性
– 由于对视图的更新是有条件的，因此应用程序中修
改数据的语句可能仍会因基本表结构的改变而改变。
4,视图能够对机密数据提供安全保护
? 对不同用户定义不同视图，使每个用户
只能看到他有权看到的数据
? 通过 WITH CHECK OPTION对关键数据
定义操作时间限制
建立视图（续）
[例 3 ] 建立 1号课程的选课视图，并要求透过该视图进
行的更新操作只涉及 1号课程，同时对该视图的任何操
作只能在工作时间进行。
CREATE VIEW IS_SC
AS
SELECT Sno,Cno,Grade
FROM SC
WHERE Cno= '1'
AND TO_CHAR(SYSDATE,'HH24') BETWEEN 9 AND 17
AND TO_CHAR(SYSDATE,'D') BETWEEN 2 AND 6
WITH CHECK OPTION; new
