《空间数据库原理与方法》笔记/期末复习资料（4. 增、删、改、定义、查询、更新）

数据更新操作有三种：向表中添加若干行数据、修改表中的数据、删除表中的若干行数据。

插入数据：

SQL 的数据插入语句 INSERT 通常有两种形式，一种是插入一个元组，另一种是插入查询结果。后者可以一次插入多个元组。

1.插入元组：

插入元组的 INSERT 语句格式为：

INSERT
INTO <表名> [(<属性列1> [,<属性列2>]...)]
VALUES (<常量1> [,<常量2>]...);

其功能是将新元组插入指定的表中。其中新元组的属性列 1 的值为常量 1，属性列 2 的值为常量 2，…。INTO 子句中没有出现的属性列，新元组在这些列上将取空值。但必须注意的是，在表定义时说明了 NOT NULL 的属性列不能取空值，否则会出错。

如果 INTO 子句中没有指定任何属性列名，则新插入的元组必须在每个属性列上都有值。

例题：将一个新学生元组（学号：201215128，姓名：陈冬，性别：男，所在系：IS，年龄：18 岁 ）插入到 Student 表中。

INSERT
INTO Student(Sno,Sname,Ssex,Sdept,Sage)
VALUES('201215128','陈冬','男','IS',18);

在 INTO 子句中指出了表名 Student，并指出了新增加的元组在哪些属性列上要赋值，属性的顺序可以与 CREATE TABLE 中的顺序不一样。VALUES 子句对新元组的各属性赋值，字符串常数要用英文单引号括起来。

例题：将学生“张成民”的信息插入到 Student 表中。

INSERT
INTO Student
VALUES('201215126','张成民','男',18,'CS');

本例题中没有指出属性名，这表示新元组要在表的所有属性列上都指定值，且属性的顺序与 CREATE TABLE 中的顺序相同。VALUES 子句对新元组的各属性列赋值，一定要注意值与属性列要一一对应，否则会由于数据类型不匹配而出错。

例题：插入一条选课记录（'201215128','1'）。

INSERT
INTO SC(Sno,Cno)
VALUES('201215128','1');

关系数据库的管理系统将在新插入记录的 Grade 列上自动地赋值为空。

或者：

INSERT
INTO SC
VALUES('201215128','1',NULL);

因为没有指出 SC 的属性名，所以在 Grade 列上要明确给出空值。

2.插入子查询结果：

子查询不仅仅可以嵌套在 SELECT 子句中用以构造父查询的条件，也可以嵌套在 INSERT 子句中用以生成要插入的批量数据。

插入子查询结果的 INSERT 语句格式为：

INSERT
INTO <表名> [<属性列1>[,<属性列2>...])
子查询;

例题：对每一个系，求学生的平均年龄，并把结果存入数据库。

首先在数据库中建立一个新表，其中一列存放系名，另一列存放相应的学生平均年龄。

CREAT TABLE Dept_age
      (Sdept CHAR(15)
       Avg_age SMALLINT);

然后对 Student 表按系分组求平均年龄，再把系名和平均年龄存入新表中。

INSERT
INTO Dept_age(Sdept,Avg_age)
SELECT Sdept,AVG(Sage)
FROM Student
GROUP BY Sdept;

修改数据：

修改数据又称为更新操作，其语句一般格式为：

UPDATE<表名>
SET<列名>=<表达式> [,<列名>=<表达式>]...
[WHERE<条件>];

功能是修改指定表中满足 WHERE 子句条件的元组。其中 SET 子句给出 <表达式> 的值用于取代相应的属性列值。如果省略 WHERE 子句，则表示要修改表中所有的元组。

1.修改某一个元组的值：

例题：将学生 201215121 的年龄改为 22 岁。

UPDATE Student
SET Sage=22
WHERE Sno='201215121';

2.修改多个元组的值：

例题：将所有学生的年龄增加 1 岁。

UPDATE Student
SET Sage=Sage+1;

3.带子查询的修改语句：

子查询也可以嵌套在 UPDATE 语句中，用以构造修改的条件。

例题：将计算机科学系全体学生的成绩置零。

UPDATE SC
SET Grade=0
WHERE Sno IN
      (SELECT Sno
       FROM Student
       WHERE Sdept='CS');

删除数据：

删除数据的一般格式为：

DELETE
FROM<表名>
[WHERE<条件>];

DELETE 语句的功能是从指定表中删除满足 WHERE 子句条件的所有元组。如果省略 WHERE 子句则表示删除表中所有的元组，但表的定义仍在数据字典中。也就是说, DELETE 语句删除的是表中的数据，而不是关于表的定义。

1.删除某一个元组的值：

例题：删除学号为 201215128 的学生记录。

DELETE
FROM Student
WHERE Sno='201215128';

2.删除多个元组的值：

例题：删除所有的学生选课记录。

DELETE
FROM SC;

这条 DELETE 语句将使 SC 表成为空表，它删除了 SC 表的所有元组，但是并没有删除 SC 表的定义，即 SC 表仍然存在，只是为空而已。

3.带子查询的删除语句：

子查询同样也可以嵌套在 DELETE 语句中，用以构造执行删除语句的条件。

例题：删除计算机科学系所有学生的选课记录。

DELETE
FROM SC
WHERE Sno IN
      (SELECT Sno
       FROM Student
       WHERE Sdept='CS');

对某个基本表中数据的增、删、改操作有可能会破坏参照完整性。

空值的处理：

所谓空值就是“不知道”或“不存在”或“无意义”的值，SQL 语言中允许某些元组的某些属性在一定情况下取空值。一般有以下几种情况：

①该属性应该有一个值，但是目前不知道它的具体指。例如：某学生的年龄属性，因为学生登记表漏填了，不知道该学生年龄，因此取空值。

②该属性不应该有值。例如，缺考学生的成绩为空，因为他没有参加考试。

③由于某种原因不便于填写。例如，一个人的电话号码不方便透露，则取空值。

因此空值是一个很特殊的值，含有不确定性。对关系运算带来特殊的问题，需要做特殊的处理。

1.空值的产生：

例题：向 SC 表中插入一个元组，学生号是“201215126”，课程号是“1”，成绩为空。

INSERT
INTO SC(Sno,Cno,Grade)
VALUES('201215126','1',NULL);  /*在插入时该学生还没有考试成绩，成绩取空值*/

或者：

INSERT
INTO SC(Sno,Cno)
VALUES('201215126','1');  /*在插入语句中没有赋值的属性列，其值为空值*/

例题：将 Student 表中学生号为“201215200”的学生所属的系改为空值。

UPDATE Student
SET Sdept=NULL
WHERE Sno='201215200';

2.空值的判断：

判断一个属性值是否为空用 IS NULL 或 IS NOT NULL 来表示。

例题：从 Student 表中找出漏填了数据的学生信息。

SELECT *
FROM Student
WHERE Sname IS NULL OR Ssex IS NULL OR Sage IS NULL OR Sdept IS NULL;

3.空值的约束条件：

属性定义（或者域定义）中有 NOT NULL 约束条件的不能取空值，加了 UNIQUE 限制的属性不能取空值，码属性不能取空值。

4.空值的算术运算、比较运算和逻辑运算：

在查询语言中，只有 WHERE 和 HAVING 子句中的选择条件为真（TRUE）的元组才可以被选出作为输出结果。

例题：找出选修 1 号课程的不及格的学生。

SELECT Sno
FROM SC
WHERE Grade<60 AND Cno='1';

例题：选出选修 1 号课程的不及格的学生以及缺考的学生。

SELECT Sno
FROM SC
WHERE Grade<60 AND Cno='1'
UNION
SELECT Sno
FROM SC
WHERE Grade IS NULL AND Cno='1';

或者：

SELECT Sno
FROM SC
WHERE Cno='1' AND (Grade<60 OR Grade IS NULL);

视图：

视图是从一个（或几个）基本表（或视图）导出的表。它与基本表不同，视图是一个“虚表”。数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍然存放在原来的基本表中。所以一旦基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。从这个意义上讲，视图就像一个窗口，透过它可以看到数据库中自己感兴趣的数据及其变化。

视图一经定义，就可以和基本表一样被查询、被删除，也可以在一个视图之上再定义新的视图，但是对视图的更新（增、删、改）操作则有一定的限制。

定义视图：

1.建立视图：

SQL 语言用 CREAT VIEW 命令建立视图，其一般格式为：

CREAT VIEW <视图名> [(<列名> [,<列名>]...)]
AS 子查询
[WITH CHECK OPTION];

WITH CHECK OPTION 表示对视图进行 UPDATE、INSERT 和 DELETE 操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）。

组成视图的属性列名要么全部省略要么全部指定，没有第三种选择。如果省略了视图的各个属性列名，则隐含该视图由子查询中 SELECT 子句目标列中的诸字段组成。但是在下列三种情况下必须明确指定组成视图的所有列名：

①某个目标列不是单纯的属性名，而是聚集函数或列表达式。

②多表连接时选出了几个同名列作为视图的字段。

③需要在视图中为某个列启用新的更合适的名字。

例题：建立信息系学生的视图。

CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS';

本例中省略了视图 IS_Student 的列名，隐含了由子查询中 SELECT 子句中的三个列名组成。

例题：建立信息系学生的视图，并要求进行修改和插入操作时仍然保证该视图只有信息系的学生。

CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS'
WITH CHECK OPTION;

由于在定义 IS_Student 视图时加上了 WITH CHECK OPTION 子句，以后对该视图进行插入、修改和删除操作时，关系数据库管理系统会自动加上 Sdept='IS' 的条件。

若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，则这类视图称为行列子集视图。IS_Student 视图就是一个行列子集视图。

视图不仅可以建立在单个基本表上，也可以建立在多个基本表上。

例题：建立信息系统选修了 1 号课程的学生的视图（包括学号、姓名、成绩）。

CREATE VIEW IS_S1(Sno,Sname,Grade)
AS
SELECT Student.Sno,Sname,Grade
FROM Student,SC
WHERE Sdept='IS' AND
      Student.Sno=CS.Sno AND
      SC.Cno='1';

由于视图 IS_S1 的属性列中包含了 Student 表和 SC 表的同名列 Sno，所以必须在视图名后面明确说明视图的各个属性列名。

视图不仅可以建立在一个或多个基本表上，也可以建立在一个或多个已经定义好的视图上，或建立在基本表与视图上。

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

CREATE VIEW IS_S2
AS
SELECT Sno,Sname,Grade
FROM IS_S1  /*视图IS_S1在前面一个例题中已经定义好了，所以这里可以直接调用*/
WHERE Grade>=90;

例题：定义一个反映学生出生年份的视图。

CREATE VIEW BT_S(Sno,Sname,Sbirth)
AS
SELECT Sno,Sname,2014-Sage  /*假设当前年份是2014年，用当前年份减去年龄即为出生年份*/
FROM Student;

这里的 BT_S 视图是一个带表达式的视图，视图中的出生年份值是通过计算得到的。

还可以用带有聚集函数和 GROUP BY 子句的查询来定义视图，这种视图称为“分组视图”。

例题：将学生的学号和平均成绩定义为一视图。

CREATE VIEW S_G(Sno,Gavg)  /*S_G是一个分组视图*/
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;

例题：将 Student 表中所有女生记录定义为一个视图。

CREATE VIEW F_Student(F_sno,name,sex,age,dept)
AS
SELECT*
FROM Student
WHERE Ssex='女';

2.删除视图：

删除视图语句的格式为：

DROP VIEW <视图名> [CASCADE];

视图删除后视图的定义将从数据字典中删除，如果该视图上还导出了其他视图，则使用 CASCADE 级联删除语句把该视图和由它导出的所有视图一起删除。

基本表删除后，由它导出的所有视图均无法使用了，但是视图的定义并没有从数据字典中清除，删除这些视图定义需要使用 DROP VIEW 语句。

例题：删除视图 BT_S 和视图 IS_S1。

DROP VIEW BT_S;  /*成功执行*/
DROP VIEW IS_S1;  /*拒绝执行*/

执行此语句时由于 IS_S1 视图上还导出了 IS_S2 视图，所以该语句被拒绝执行，如果确定要删除，则使用级联删除语句：

DROP VIEW IS_S1 CASCADE;  /*删除了视图IS_S1和由它导出的所有视图*/

查询视图：

定义视图后用户就可以像基本表一样对视图进行查询了。

例题：在信息系学生视图中找出年龄小于 20 岁的学生。

SELECT Sno,Sage
FROM IS_Student
WHERE Sage<20;

关系数据库管理系统执行对视图的查询时，首先进行有效性检查，检查查询中涉及的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询。这一转换过程称为视图消解（view resolution）。

本例题转换后的查询语句为：

SELECT Sno,Sage
FROM Student
WHERE Sdept='IS' AND Sage<20;

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

SELECT IS_Studen.Sno,Sname
FROM IS_Student,SC
WHERE IS_Student.Sno=SC.Sno AND SC.Cno='1';

本查询涉及视图 IS＿Student（虚表）和基本表 SC，通过这两个表的连接来完成用户请求。在一般情况下，视图查询的转换是直截了当的。但有些情况下，这种转换不能直接进行，查询时就会出现问题。

例题：在 S_G 视图（某个前面的例题定义的视图）中查询平均成绩在 90 分以上的学生学号和平均成绩。

SELECT*
FROM S_G
WHERE Gavg>=90;

前面的某个例题定义 S＿G 视图的子查询为：

SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;

将本例中的查询语句与定义 S＿G 视图的子查询结合，形成下列查询语句：

SELECT Sno,AVG(Grade)
FROM SC
WHERE AVG(Grade)>=90
GROUP BY Sno;

因为 WHERE 子句中是不能用聚集函数作为条件表达式的,因此执行此修正后的查询将会出现语法错误，正确转换的查询语句应该是：

SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90;

目前多数关系数据库系统对行列子集视图的查询均能进行正确转换。但对非行列子集视图的查询就不一定能做转换了,因此这类查询应该直接对基本表进行。

本例也可以用如下 SQL 语句完成:

SELECT*
FROM (SELECT Sno,AVG(Grade)  /*子查询生成一个派生表S_G*/
      FROM SC
      GROUP BY Sno) AS S_G(Sno,Gavg)
WHERE Gavg>=90;

但定义视图并查询视图与基于派生表的查询是有区别的。视图一旦定义,其定义将永久保存在数据字典中,之后的所有查询都可以直接引用该视图。而派生表只是在语句执行时临时定义,语句执行后该定义即被删除。

更新视图：

更新视图是指通过视图来插入（INSERT）、删除（DELETE）和修改（UPDATE）数据。

由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新。像查询视图那样，对视图的更新操作也是通过视图消解，转换为对基本表的更新操作。

为防止用户通过视图对数据进行增加、删除、修改时，有意无意地对不属于视图范围内的基本表数据进行操作，可在定义视图时加上 WITH CHECK OPTION 子句。这样在视图上增、删、改数据时，关系数据库管理系统会检查视图定义中的条件，若不满足条件则拒绝执行该操作。

例题：将信息系学生视图 IS＿Student 中学号为“201215122”的学生姓名改为“刘辰”。

UPDATE IS_Student
SET Sname='刘辰'
WHERE Sno='201215122';

转换后的更新语句为:

UPDATE Student
SET Sname='刘辰'
WHERE Sno='201215122' AND Sdept='IS';

例题：向信息系学生视图 IS_Student 中插入一个新的学生记录，其中学号为“201215129”，姓名为“赵新”，年龄为20岁。

INSERT
INTO IS_Student
VALUES('201215129','赵新',20);

转换为对基本表的更新：

INSERT
INTO Student(Sno,Sname,Sage,Sdept)
VALUES('201215129','赵新',20,'IS');

这里系统自动将系名 “IS” 放入 VALUES 子句中。

例题：删除信息系学生视图 IS＿Student 中学号为“201215129”的记录。

DELETE
FROM IS_Student
WHERE Sno='201215129';

转换为对基本表的更新：

DELETE
FROM Student
WHERE Sno='201215129' AND Sdept='IS';

在关系数据库中，并不是所有的视图都是可更新的，因为有些视图的更新不能唯一地有意义地转换成对相应基本表的更新。

例如前面某个例题定义的视图 S_G 是由学号和平均成绩两个属性列组成的，其中平均成绩一项是由 Student 表中对元组分组后计算平均值得来的：

CREATE VIEW S_G(Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;

如果想把视图 S_G 中学号为“201215121”的学生的平均成绩改成 90 分，SQL 语句如下：

UPDATE S_G
SET Gavg=90
WHERE Sno='201215121';

但这个对视图的更新是无法转换成对基本表 SC 的更新的，因为系统无法修改各科成绩，以使平均成绩成为 90。所以 S_G 视图是不可更新的。

一般地，行列子集视图是可更新的。除行列子集视图外，有些视图理论上是可更新的，但它们的确切特征还是尚待研究的课题。还有些视图从理论上就是不可更新的。

目前，各个关系数据库管理系统一般都只允许对行列子集视图进行更新，而且各个系统对视图的更新还有更进一步的规定。由于各系统实现方法上的差异，这些规定也不尽相同。

例如，DB2 规定：
（1）若视图是由两个以上基本表导出的，则此视图不允许更新。

（2）若视图的字段来自字段表达式或常数，则不允许对此视图执行 INSERT 和 UPDATE操作，但允许执行 DELETE 操作。

（3）若视图的字段来自聚集函数，则此视图不允许更新。

（4）若视图定义中含有 GROUP BY 子句，则此视图不允许更新。

（5）若视图定义中含有 DISTINCT 短语，则此视图不允许更新。

（6）若视图定义中有嵌套查询,并且内层查询的 FROM 子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。

（7）一个不允许更新的视图上定义的视图也不允许更新。

应该指出的是，不可更新的视图与不允许更新的视图是两个不同的概念。前者指理论上已证明其是不可更新的视图。后者指实际系统中不支持其更新，但它本身有可能是可更新的视图。

视图的作用：

视图最终是定义在基本表之上的,对视图的一切操作最终也要转换为对基本表的操作。而且对于非行列子集视图进行查询或更新时还有可能出现问题。既然如此,为什么还要定义视图呢?这是因为合理使用视图能够带来许多好处。

1. 视图能够简化用户的操作
视图机制使用户可以将注意力集中在所关心的数据上。如果这些数据不是直接来自基本表,则可以通过定义视图使数据库看起来结构简单、清晰,并且可以简化用户的数据查询操作。例如,那些定义了若干张表连接的视图就将表与表之间的连接操作对用户隐蔽起来了。换句话说,用户所做的只是对一个虚表的简单查询,而这个虚表是怎样得来的,用户无须了解。

2. 视图使用户能以多种角度看待同一数据
视图机制能使不同的用户以不同的方式看待同一数据,当许多不同种类的用户共享同一个数据库时,这种灵活性是非常重要的。

3. 视图对重构数据库提供了一定程度的逻辑独立性
前面已经介绍过数据的物理独立性与逻辑独立性的概念。数据的物理独立性是指用户的应用程序不依赖于数据库的物理结构。数据的逻辑独立性是指当数据库重构造时,如增加新的关系或对原有关系增加新的字段等,用户的应用程序不会受影响。层次数据库和网状数据库一般能较好地支持数据的物理独立性,而对于逻辑独立性则不能完全地支持。

4. 视图能够对机密数据提供安全保护
有了视图机制，就可以在设计数据库应用系统时对不同的用户定义不同的视图，使机密数据不出现在不应看到这些数据的用户视图上。这样视图机制就自动提供了对机密数据的安全保护功能。例如，Student 表涉及全校 15 个院系的学生数据，可以在其上定义 15 个视图，每个视图只包含一个院系的学生数据，并只允许每个院系的主任查询和修改本院系的学生视图。

5. 适当利用视图可以更清晰地表达查询
例如，经常需要执行这样的查询“对每个同学找出他获得最高成绩的课程号”。可以先定义一个视图，求出每个同学获得的最高成绩：

CREATE VIEW VMGRADE
AS
SELECT Sno,MAX(Grade) Mgrade
FROM SC
GROUP BY Sno;

然后用如下的查询语句完成查询：

SELECT SC.Sno,Cno
FROM SC,VMGRADE
WHERE SC.Sno=VMGRADE.Sno AND SC.Grade=VMGRADE.Mgrade;