数据库第三章：集合查询、空值的处理和视图

集合查询

SELECT语句的查询结果是元组集合，所以多个SELECT语句的结果可进行集合操作。集合操作主要包括并操作UNION，交操作INTERSECT和差操作EXCEPT
 

例 3.66 查询计算机科学系的学生年龄不大于19岁的学生的交集

SELECT *
FROM Student
WHERE Sdept='CS'
INTERSECT
SELECT *
FROM Student
WHERE Sage<=19;

等价于

SELECT*
FROM Student
WHERE Sdept='CS' AND Sage<=19;

 

例 3.68 查询计算机科学系的学生年龄不大于19岁的学生的差集

SELECT*
FROM Student
WHERE Sdept='CS'
EXCEPT
SELECT*
FROM Student
WHERE Sage<=19;

等价于

SELECT *
FROM Student
WHERE Sdept='CS' AND Sage>19;

 

基于派生表的查询

子查询不仅可以出现在WHERE子句中，还可以出现在FROM中，这时子查询生成的临时派生表成为中查询查询对象

例 3.57 找出每个学生超过他自己选修课程平均成绩的课程号。

SELECT Sno,Cno
FROM SC,(SELECT Sno,Avg(Grade) FROM SC GROUP BY Sno)
		AS Avg_sc(avg_sno,avg_grade)
WHERE SC.Sno=Avg_sc.avg_sno and SC.Grade>=Avg_sc.avg_grade

这里FROM子句中的子查询将生成一个派生表Avg_sc该表由 avg_sno和 avg_grade两个属性组成，记录了每个学生的学号及平均成绩。主查询将SC表 Avg_sc按学号相等进行连接，选出修课成绩大于其平均成绩的课程号。

如果子查询中没有聚集函数，派生表可以不指定属性列，子查询 SELECT子句后面的列名为其默认属性。例如例3.60查询所有选修了号课程的学生姓名，可以用如下查询完成：

SELECT Snmae
FROM Student,(SELECT Sno FROM SC WHERE Cno='1') AS SC1
WHERE Student.Sno=SC1.Sno;

 

空值的处理

所谓空值就是“不知道”或“不存在”或“无意义”的值。

SQL语言中允许某些元组的某些属性在一定情况下取空值。一般有以下几种情况：

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

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

由于某种原因不便于填写。例如，一个人的电话号码不想让大家知道，则取空值。

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

1.空值的产生

INSERT INTO SC(Sno,Cno,Grade)
	VALUES('201215126','1',NULL);

或

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

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

另外，外连接也会产生空值。空值的关系运算也会产生空值。
 

空值的判断

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

例 3.81 从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.空值的算术运算、比较运算和逻辑运算

空值与另一个值(包括另一个空值)的算术运算的结果为空值，空值与另一个值(包括另一个空值)的比较运算的结果为 UNKNOWN。有了 UNKNOWN后，传统的逻辑运算中二值(TRUE， FALSE)逻辑就扩展成了三值逻辑。在查询语句中，只有使 WHERE和 HAVING子句中的选择条件为TRUE的元组才被选出作为输出结果。

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

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

选出的学生是那些参加的考试(Grade属性为非空值)而不及格的学生,不包括缺考的学生。因为前者使条件Grade<60的值为TRUE，后者使条件的值为UNKNOWN。
 

视图

3.7.1 定义视图

1.建立视图

例 3.85 建立信息系学生的师徒并要求进行修改和插入操作时，仍需保证该视图只有信息系的学生

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

关系数据库管理系统执行 CREATE VIEW语句的结果只是把视图的定义存入数据字典，并不执行其中的 SELECT语句。只是在对视图查询时，才按视图的定义从基本表中将数据査出。

由于在定义 IS_Student视图时加上了 WITH CHECK OPTION子句，以后对该视图进行插入、修改和删除操作时，关系数据库管理系统会自动加上 Sdept='IS’的条件。
*************************************************************************
若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，则称这类视图为行列子集视图。IS_Student视图就是一个行列子集视图。

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

例 3.86 建立信息系选修了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';

 

例 3.89 将学生的学号及平均成绩定义为一个视图。

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

由于AS子句中 SELECT语句的目标列平均成绩是通过作用聚集函数得到的，所以 CREATE VIEW中必须明确定义组成SG视图的各个属性列名。S_G是一个分组视图
 

2.删除视图

例 3.91 删除视图BT_S和视图IS_S1

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

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

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

 

3.7.1 查询视图

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

SELECT Sno,Sage
FROM IS_Student
WHERE Sage<20;

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

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

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

 

例 3.94 在S_G视图中查询平均成绩在90分以上的学生和平均成绩

SELECT*
FROM S_G
WHERE Gavg>=90;

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;

SELECT*
FROM(SELECT Sno,AVG(Grade)
		FROM SC
		GROUP BY Sno) AS S_G(Sno,Gavg)
WHERE Gavg>=80;

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

3.7.1 更新视图

例 3.97 删除信息系学生视图IS_Student中学号为“201215129”的记录。

DELETE
FROM Student
WHERE Sno='201215129';

转换为对基本表的更新：

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

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

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

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

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

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

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