换一个数据吧，之前的不要用了。还是根据教程《数据库系统概论》（第5版）中的例子比较好。

数据表准备

Student表

image-20220816102319093

数据

image-20220816102354172

SC表

image-20220816102429827

数据

image-20220816102455810

Course表

image-20220816102523358

数据

image-20220816102539880

EXIST

重新举一些例子

查询选修了1号课程的学生姓名

1
2
SELECT sname From Student
WHERE EXISTS (SELECT * FROM SC WHERE SC.sno=Student.sno AND cno='1');

image-20220816104320917

查询没有选修1号课程的学生姓名

1
2
SELECT sname From Student
WHERE NOT EXISTS (SELECT * FROM SC WHERE SC.sno=Student.sno AND cno='1');

image-20220816104435466

查询选修了所有课程的学生姓名

使用NOT EXISITS实现全称量词查询

题目等价变化为：查询没有一个科目没有选的学生姓名

1
2
SELECT sname FROM Student
WHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE sno=Student.sno AND Cno=Course.Cno))

补充

一些EXISTS或NOT EXISTS谓词的子查询不能被其它形式的子查询等价替换，但所有的IN、比较运算符、ANY、ALL谓词的子查询都能用EXISTS谓词的子查询等价替换。

集合查询

UNION并、INTERSECT交、EXCEPT叉

注意

参加集合操作的各查询结果的列数必须相同，对应项的数据类型也必须相同。

UNION

UNION ALL可以将重复值也列出来

img

img

INTERSECT

EXCEPT

派生表

子查询不仅可以放在WHERE 子句后，还可以放在From 子句后，这时子查询产生临时的派生表

示例

查询选修课程分数超过选修课程平均分的学生学号和课程

1
2
3
SELECT Sno,Cno 
FROM SC,(SELECT Cno,AVG(Grade) FROM SC GROUP BY Cno) AS Avg(avg_Cno,avg_Grade) 
WHERE SC.Grade>Avg.avg_Grade
1
2
3
SELECT Sno,SC.Cno 
FROM SC,(SELECT Cno,AVG(Grade) AS avg_Grade FROM SC GROUP BY Cno) Avg 
WHERE SC.Grade>Avg.avg_Grade
1
2
3
SELECT Sno,Cno
FROM SC,(SELECT Cno AS avg_Cno,AVG(Grade) AS avg_Grade FROM SC GROUP BY Cno) Avg 
WHERE SC.Grade>Avg.avg_Grade
1
2
3
SELECT Sno,Cno
FROM SC,(SELECT Cno avg_Cno,AVG(Grade) avg_Grade FROM SC GROUP BY Cno) Avg 
WHERE SC.Grade>Avg.avg_Grade

在使用派生表时有多种写法，但必须给临时派生表设置AS别名，AS可以省略。

视图

视图的使用和普通表一样，可以查询，增删改查。

定义视图

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

说明

子查询可以是任意的SELECT语句，是否可以含有ORDER BY子句和DISTINCT短语，取决于具体系统的实现。

启用WITH CHECK OPTION表示在对视图进行增删改操作时，需要满足视图定义中子查询中的条件表达式（WHERE条件）。

视图和派生表的区别

视图被创建后，保存在数据字典中，以后可以直接用视图名引用；

派生表只是临时表。

示例

创建一个学生表的视图，只需要Sno、Sdept

1
2
3
CREATE VIEW vstudent 
AS
SELECT Sno,Sdept FROM Student

image-20220816185942448

创建一个计算机科学专业的学生表，并启用WITH CHECK OPTION

1
2
3
4
5
CREATE VIEW vcsstudent
AS
SELECT * FROM Student
WHERE Sdept='CS'
WITH CHECK OPTION;

image-20220816190231462

更新视图

使用vcsstudent视图

为vcsstudent添加数据

1
INSERT INTO vcsstudent VALUES(201215129,'李明','女',22,'MA')

image-20220816190605700

我们再尝试更新数据

1
UPDATE vcsstudent SET Sdept='IS' WHERE Sno=201215121

image-20220816191011430

同上

由于插入、更新的数据未符合WHERE条件，故失败。

正确的插入

1
INSERT INTO vcsstudent VALUES(201215129,'李明','女',22,'CS')

image-20220816191700062

通过观察上述插入的结果，我们发现，视图其实是不存储数据的虚表，因此对视图的更新操作都最终转换为了对基本表的更新。

但是，并不是所有的视图都是可以更新的！

DB2规定的不允许更新的情况：

  • 由多个基本表导出的
  • 字段来源为表达式或常数，只能执行DELETE操作
  • 字段来源为聚集函数
  • 定义中含有GROUP BY子句
  • 定义中含有DISTINCT短语
  • 定义中含有嵌套查询，且内层嵌套的FROM子句中设计的表也是导出该视图的基本表。
  • 在不允许更新的视图上定义的视图

另外还有一种情况，就是定义的视图为基础表中的部分列，如果我们进行插入操作，若未指定的列在基础表中不允许为空，则会报错：

image-20220816193056866

image-20220816193039336