【2020-2021春学期】数据库作业8：集合查询和视图例题练习

集合查询

集合操作的种类

• 并-union
• 交-intersect
• 差-except

【例3.64】查询计算机科学系的学生 及 年龄不大于19岁的学生。

select *
from Student
where Sdept='CS'
union  --及（并）
select *
from student
where Sage<=19;

可见union会自动distinct。那么可以让它不去重吗？当然可以。union的特性，去重与不去重
了解到用union all可以不去重，但感觉这个知识好像并没有什么卵用…

select *
from Student
where Sdept='CS'
union all
select *
from student
where Sage<=19;

【例3.65】查询选修了课程1或者选修了课程2的学生。

select Sno
from SC
where Cno='1'
union
select Sno
from SC
where Cno='2';

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

select *
from Student
where Sdept='CS'
intersect   --与（交）
select *
from student
where Sage<=19;

当然了，用连接查询解决更简单。

【例3.67】查询既选修了课程1又选修了课程2的学生

select Sno
from SC
where Cno='1'
intersect
select Sno
from SC
where Cno='2';

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

select *
from Student
where Sdept='CS'
except --差（用前面减去后面）
select *
from Student
where Sage<=19;

基于派生表的查询

子查询不止可以出现在where语句中，还可以出现在from子句中，这时子查询形成的临时派生表成为了主查询查询的对象。

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

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

【例】查询所有选修了1号课程的学生姓名，可以用如下查询完成：

select Sname
from student,(select Sno
			  from SC
			  where Cno='1')as SC1
where Student.Sno=SC1.Sno;

数据更新

插入子查询结果

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

第一步，建表

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在执行插入语句会自动检查完整性规则

【例3.73】将学生201911020的年龄改为22岁。

update Student
set Sage=22
where Sno='201911020';

【例3.74】将所有学生的年龄增加1岁。

update Student 
set Sage = Sage + 1

【例3.75】将计算机科学系全体学生的成绩置零。

update SC
set Grade=0
where Sno in(select Sno
			 from Student
			 where Sdept='CS');

因为全是CS系的，所以全变0。

【例3.76】删除学号为201215128的学生记录。

delete 
from student
where Sno='201215128';

【例3.77】删除所有学生的选课记录

delete 
from SC;

【例3.78】删除计算机科学系所有学生的选课记录。

delete
from SC
where Sno in(
	select Sno
	from Student
	where Sdept='CS');

空值的处理

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

insert into SC(Sno,Cno,Grade)
values('201215126','1',NULL);

【例3.80】将Student表中学生号为”201215200”的学生所属的系改为空值。

update Student
set Sdept=NULL
where Sno='201215200';

【例3.81】从Student表中找出漏填了数据的学生信息。

select *
from Student
where Sname IS NULL OR Ssex IS NULL OR Sage IS NULL OR Sdept IS NULL;
-- 判断一个属性是否为空值，用is null或者is not null来表示。

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

select Sno
from SC
where Grade<60 and Cno='1';
-- 结果中不会显示缺考的学生，因为他们的Grade为NULL

【例3.83】选出选修1号课程的不及格的学生以及缺考的学生。

select Sno
from SC
where Cno='1' AND (Grade<60 OR Grade IS NULL);

视图

视图的特点：

• 虚表。是从一个或几个基本表中导出的图。
• 只存放视图的定义。不负责存放数据。
• 基表中的数据发生变化，视图中查询出的数据也会发生改变。

1.定义视图

建立视图的一般形式是

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

其中WITH CHECK OPTION的作用是在更新操作时自动检查子查询中的条件。

【例3.84】建立信息系学生的视图。

create view IS_Student AS
select Sno,Sname,Sage
from Student
where Sdept='IS';

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

create view IS_Student AS
select Sno,Sname,Sage
from Student
where Sdept='IS'
WITH CHECK OPTION;

行列子集视图：只从一个基本表中导出的视图，且只保留了主码和某些行和列。

【例3.86】建立CS系选修了1号课程的学生的视图（包括学号、姓名、成绩）。

create view IS_S1(Sno,Sname,Grade) AS
select student.Sno,Sname,Grade
from Student,SC
where Student.Sno=SC.Sno
and Student.Sdept='CS'
and SC.Cno='1';

【例3.87】建立CS系选修了1号课程且成绩在90分以上的学生的视图。

视图不仅可以建立在基本表上，也可以建立在其他视图上。

create view IS_S2 as
select Sno,Sname,Grade 
from IS_S1
where Grade>=90;

【例3.88】定义一个反映学生出生年份的视图。

create view BT_S(Sno,Sname,Sbirth) as
select Sno,Sname,2021-Sage
from Student;

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

create view S_G(Sno,Gavg) as
select Sno,AVG(Grade)
from SC
group by Sno;

平均成绩都为0是因为前面把SC.Grade都变为0了

【例3.90】将Student表中所有女生记录定义为一个视图。

create view F_student(F_Sno,name,sex,age,dept) as
select Sno,Sname,Ssex,Sage,Sdept
from Student
where Ssex='女'

2.删除视图

语句格式为：

DROP VIEW<视图名>[CASCADE];

需要注意如果在一个视图上还导出了其他视图，则需要使用CASCADE 级联删除语句把该视图和由它导出的所有视图一起删除。

【例3.91】删除视图BT_S和视图IS_S1

drop view BT_S;
drop view IS_S1 cascade; --关键字 'cascade' 附近有语法错误。

这里又遇到了之前的问题，T-SQL里不支持cascade级联删除。但是之前的办法不能用了，百度也没找到好的解决办法，倒是参考同学的博客，发现原来在SQLserver中不加cascade直接drop view IS_S1;就可以删除了。

3.查询视图

对用户来说，查询表和查询视图没什么区别

RDBMS是通过视图消解法来进行转化查询的。

1. 进行有效性检查
2. 转换成等价的对基本表的查询
3. 执行修正后的查询

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

SELECT Sno,Sage FROM IS_Student WHERE Sage<20;

视图消解法后等价于：

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

【例3.93】查询选修了1号课程的信息系学生

select IS_Student.Sno,Sname
from IS_Student,SC
where IS_Student.Sno=SC.Sno AND SC.Cno='1';

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

SELECT * FROM S_G
WHERE Gavg>=90;

4.更新视图

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

【例3.95】将信息系学生视图IS_Student 中学号为“20191102062”的学生姓名改为“刘辰”。

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

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

INSERT
INTO IS_Student
VALUES('20191102074','赵新',25);

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

DELETE
FROM IS_Student
WHERE Sno='201215129';