3.5 《数据库系统概论》之基本表更新（INSERT、UPDATE、ALTER、DELETE）与视图VIEW（定义、查询、更新）
两种插入数据方式
插入元组 插入子查询结果 可以一次插入多个元组 ① 插入元组语句格式
INSERT
INTO [([，…)]
VALUES ( [，] … )
功能:将新元组插入指定表中
INTO子句
属性列的顺序可与表定义中的顺序不一致 没有指定属性列 指定部分属性列VALUES子句
提供的值必须与INTO子句匹配 值的个数 值的类型［例1］ 将一个新学生元组（学号：200215128；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。
INSERT
INTO Student (Sno，Sname，Ssex，Sdept，Sage)
VALUES ('200215128'，'陈冬'，'男'，'IS'，18)；
［例2］ 将学生张成民的信息插入到Student表中。
INSERT
INTO Student
VALUES ('200215126', '张成民', '男',18,'CS');
［例3］ 插入一条选课记录( ‘200215128’，'1 ')。
INSERT
INTO SC(Sno,Cno)//RDBMS将在新插入记录的Grade列上自动地赋空值
VALUES ('200215128','1');
//等价
INSERT
INTO SC
VALUES ('200215128','1',NULL);
语句格式
INSERT
INTO [( [，… )]
子查询；
功能
将子查询结果插入指定表中
INTO子句(与插入元组类似)
子查询
SELECT子句目标列必须与INTO子句匹配值的个数
值的类型
[例4] 对每一个系，求学生的平均年龄，并把结果存入数据库。
第一步：建表
CREATE TABLE Dept_age(
Sdept CHAR(15), /* 系名*/
Avg_age SMALLINT /*学生平均年龄*/
);
第二步：插入数据
INSERT INTO Dept_age(Sdept,Avg_age)
SELECT Sdept,AVG(Sage)
FROM Student
GROUP BY Sdept;
RDBMS在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则
实体完整性 参照完整性 用户定义的完整性 NOT NULL约束 UNIQUE约束 值域约束 （2）修改数据—UPDATE SET语句格式
UPDATE
SET =[，=]…
[WHERE ];
功能
修改指定表中满足WHERE子句条件的元组
SET子句
指定修改方式 要修改的列 修改后取值WHERE子句
指定要修改的元组缺省表示要修改表中的所有元组
三种修改方式
修改某一个元组的值 修改多个元组的值 带子查询的修改语句 ① 修改某一个元组的值[例5] 将学生201215121的年龄改为22岁
UPDATE Student
SET Sage=22
WHERE Sno='201215121';
[例6] 将所有学生的年龄增加1岁
UPDATE Student
SET Sage= Sage+1;
[例7] 将计算机科学系全体学生的成绩置零。
UPDATE SC
SET Grade=0
WHERE 'CS'=(
SELECT Sdept
FROM Student
WHERE Student.Sno = SC.Sno
);
RDBMS在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则
实体完整性 主码不允许修改 用户定义的完整性 NOT NULL约束 UNIQUE约束 值域约束 （3）删除数据 —DELETE三种删除方式
删除某一个元组的值 删除多个元组的值 带子查询的删除语句 ① 删除某一个元组的值DELETE
FROM Student
WHERE Sno= '200215128';
[例9] 删除所有的学生选课记录。
DELETE
FROM SC；//这个我就不截图了
③ 带子查询的删除语句
[例10] 删除计算机科学系所有学生的选课记录。
DELETE
FROM SC
WHERE 'CS'=(
SELECT Sdept
FROM Student
WHERE Student.Sno=SC.Sno
);
视图的作用：
视图能够简化用户的操作 视图使用户能以多种角度看待同一数据 视图对重构数据库提供了一定程度的逻辑独立性 视图能够对机密数据提供安全保护 适当的利用视图可以更清晰的表达查询视图的特点
虚表，是从一个或几个基本表（或视图）导出的表 只存放视图的定义，不存放视图对应的数据 基表中的数据发生变化，从视图中查询出的数据也随之改变基于视图的操作
查询 删除 受限更新 定义基于该视图的新视图视图的UPDATE、DELETE、INSERT INTO(有受限)，与基本表同步。
（1）定义视图 ① 建立视图 语句格式CREATE VIEW [( [，]…)]
AS
[WITH CHECK OPTION]；
组成视图的属性列名：全部省略或全部指定
子查询不允许含有ORDER BY子句和DISTINCT短语
RDBMS执行CREATE VIEW语句时只是把视图定义存入数据字典，并不执行其中的SELECT语句。
在对视图查询时，按视图的定义从基本表中将数据查出。
[例1] 建立信息系IS学生的视图。
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept= 'IS';
[例2]建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系IS的学生 。
对IS_Student视图的更新操作：
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept= 'IS'
WITH CHECK OPTION;
基于多个基表的视图
[例3] 建立信息系选修了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';
基于视图的视图
[例4] 建立信息系选修了1号课程且成绩在90分以上的学生的视图。
CREATE VIEW IS_S2
AS
SELECT Sno,Sname,Grade
FROM IS_S1
WHERE Grade>=90;
带表达式的视图
[例5] 定义一个反映学生出生年份的视图。
CREATE VIEW BT_S(Sno,Sname,Sbirth)
AS
SELECT Sno,Sname,2000-Sage
FROM Student;
分组视图
[例6] 将学生的学号及他的平均成绩定义为一个视图
CREATE VIEW S_G(Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;
不指定属性列
[例7]将Student表中所有女生记录定义为一个视图
CREATE VIEW F_Student(F_Sno,name,sex,age,dept)
AS
SELECT *
FROM Student
WHERE Ssex='女';
缺点：
语句的格式:
DROP VIEW ;
数据字典中删除指定的视图定义
如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除
删除基表时，由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除
［例8］ 删除视图IS_S1
DROP VIEW IS_S1;
用户角度：查询视图与查询基本表相同
RDBMS实现视图查询的方法
视图消解法（View Resolution） 进行有效性检查 转换成等价的对基本表的查询 执行修正后的查询
关系数据库管理系统执行对视图的查询时，首先进行有效性检查，检查查询中涉及的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询。这一转换过程称为视图消解(view resolution)。
[例9] 在信息系学生的视图中找出年龄小于等于20岁的学生。
SELECT Sno,Sage
FROM IS_Student
WHERE Sage<=20;
IS_Student视图的定义 (参见视图定义例1)
视图消解转换后的查询语句为：
SELECT Sno，Sage
FROM Student
WHERE Sdept= 'IS' AND Sage<=20；
[例10] 查询选修了1号课程的信息系学生
SELECT IS_Student.Sno,Sname
FROM IS_Student,SC
WHERE IS_Student.Sno =SC.Sno AND SC.Cno= '1';
视图消解法的局限
有些情况下，视图消解法不能生成正确查询。[例11]在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
GROUP BY Sno;
正确：
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90;
[例12] 将信息系学生视图IS_Student中学号201215125的学生姓名改为“刘辰”。
UPDATE IS_Student
SET Sname= '刘辰'
WHERE Sno= '201215125';
转换后的语句： //基本表和视图表同步更新
UPDATE Student
SET Sname= '刘辰'
WHERE Sno= '201215125'
AND Sdept= 'IS';
[例13] 向信息系学生视图IS_Student中插入一个新的学生记录：200215111，赵新，20岁
视图IS_Student的定义
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept= 'IS';
//如果加了WITH CHECK OPTION，则视图不能进行插入数据操作
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept= 'IS'
WITH CHECK OPTION;
//插入数据
INSERT
INTO IS_Student
VALUES('200215111','赵新',20);
//插入后基本表Student字段Sdept为空，视图表IS_Student无数据
转换为对基本表的更新：
INSERT
INTO Student(Sno,Sname,Sage,Sdept)
VALUES('200215129','赵新2',20,'IS');
[例14]删除信息系学生视图IS_Student中学号为200215129的记录
DELETE
FROM IS_Student
WHERE Sno= '200215129';
转换为对基本表的更新：
DELETE
FROM Student
WHERE Sno= '200215129' AND Sdept= 'IS';
更新视图的限制：一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新
例：视图S_G为不可更新视图。
UPDATE S_G
SET Gavg=90
WHERE Sno= '200215121';
这个对视图的更新无法转换成对基本表SC的更新
允许对行列子集视图进行更新
对其他类型视图的更新不同系统有不同限制
作者：BitHachi
