2021-04-14

数据查询—集合、派生表的查询以及数据更新和视图

3.3.4–集合查询
select语句的查询结果也可以是元组的集合，所以多个select语句的结果可进行集合操作。集合操作主要包括并操作UNION、叫操作INTERSECT和差操作EXCEPT。
注意，参加集合操作的列数必须相同；对应项的数据类型也必须相同。
例3.64 查询计算机科学系的学生及年龄不大于19岁的学生。

select *
from Student
where Sdept='CS'
UNION select *
from Student 
where Sage<=19;

本查询是求计算机科学系的所有学生与年龄不大于19岁的学生的并集。

例3.65
查询选修了课程1或者选修了课程2的学生。
（本例即查询选修课程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';

这实际上就是查询计算机科学系中年龄不大于19岁的学生。
例3.67
查询既选修了课程1又选修了课程2的学生。就是查询选修课程1的学生集合与选修课程2的学生集合的交集。

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

本例也可以表示为

select Sno
from SC
where Cno='1' and Sno in
		(select Sno
		 from SC
		 where Cn0='2');

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

select *
from Student
where Sdept='CS'
except
select *
from Student
where Sage<=19;

也就是查询计算机科学系中年龄大于19岁的学生。

select *
from Student
where Sdept='CS' and Sage>19;

3.4.5 基于派生表的查询
子查询不仅可以出现在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;

3.5 数据更新
数据更新操作有三种：向表中添加若干行数据、修改表中的数据和删除表中的若干行数据。在SQL中有相应的三类语句。
3.5.1插入数据
SQL的插入语句insert通常有两种形式，一种是插入一个元组，另一种是插入子查询结果。后者可以一次插入多个元组。
1.插入元组
插入元组的insert语句的格式为
insert
into<表名>[(<属性名1>[,<属性名2>]…)]
values(<常量1>[,<常量2>]…);
其功能是将新元组插入指定表中。其中新元组的属性列1 的值为常量1，属性列2的值为常量2。into子句中没有出现的属性列，新元组在这些列上将取空值。但必须注意的是，在表定义时说明了not null的属性列不能取空表，否则会出错。如果into子句中没有指明任何属性列名，则新插入的元组必须在每个属性列上均有值。
例3.69
将一个新学生元组（学号：201215140，姓名：丞东，性别：男，所在系：IS，年龄：18）插入到student表中。

insert
into Student (Sno,Sname,Ssex,Sdept,Sage)
values ('201215140','丞东','男','IS',18);

values子句对新元组的个属性赋值，字符串常数要用单引号括起来。
例3.70
将学生章程的信息输入到Student表中。

insert
into Student
values('201215643','章程一','男',18,'CS');

例3.71
插入一条学科记录（’2012151128‘，’1‘）

insert
into SC(Sno,Cno)
values('201215128','1');

如果没有给出指定的属性名，那么每个属性名都要依次给出值。（这里Grade系统自动给NULL）.
2.插入子查询结果
子查询不仅可以嵌套在select语句中用以构造父查询的条件，也可以嵌套在insert语句中用以生成要插入的批量数据。
例3.72
对每一个系，求学生的平均年龄，并把结果存入数据库。
首先在数据库中建立一个新表，其中一列存放系名，另一列存放相应的学生平均年龄。

create 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;

3.5.2 修改数据

1.修改某一个元组的值。
例3.73
将学生201215121的年龄改为22岁。

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

2.修改多个元组的值
例3.74
将所有学生的年龄增加一岁。

update Student
set Sage=Sage+1;

3.53 删除数据
1. 删除某一个元组的值
例3.76 删除学号为201215140的学生记录。

delete 
from Student 
where Sno='201215140';

2.删除多个元组的值
delete from SC
这条语句使SC成为空表，他删除了SC表的所有元组。
3.带子查询的删除语句
例3.78
删除计算机科学系所有学生的选课记录。

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

3.6 空值的处理

1.空值的产生
例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‘；
2.空值的判断
判断一个属性的值是否为空值，用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.82
找出选修1号课程的不及格的学生。

select Sno
from SC
where Grade<60 and Cno='1';

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

select Sno
from SC
where Grade<60 and Cno='1'
union
select Sno
from SC
where Grade is null and Cno='1';

3.7 视图
视图是从一个或者几个基本表到处的表。他与基本表不同，是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。所以一旦基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。从这个意义上讲，视图就像是一个窗户，透过它可以看到数据库中自己感兴趣的东西及其变化。
3.7.1 定义视图
1.建立视图
例 3.84 建立信息系学生的视图。

create view IS_Student
as
select Sno,Sname,Sage
from Student where Sdept='CS';

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

create view IS_Student
as
select Sno,Sname,Sage
from Student
where Sdept='IS'
with check option;

由于在定义IS_Student 视图时加上了with check option子句，以后对该视图进行行插入、修改和删除操作时，关系数据库系统会自动加数Sdept=’IS’的条件。
若一个是从单个基本表导出的，并且知识去掉了基本表到某些行和某些列，但保留了主码，则称这类视图为行列子集视图。
例3.86
建立信息系选修了1号课程的学生的视图。（包括学号，姓名和成绩）

create view IS_S2(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.87
建立信息系选修了1号课程并且成绩在80分以上的学生的视图。

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

这里的视图IS_S1就是建立在IS_S2之上的。
例 3.88
定义一个反映学生出生年份的视图

create view S_G(Sno,Sname,Sbirth)
as
select Sno,Sname,2014-Sage
from Student

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

create view S_GG(Sno,Gavg)
as 
select Sno,avg(Grade)
from SC
group by Sno;

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

create view F_Student(F_Sno,name,sex,age,dept)
as 
select *
from Student
where Ssex='女';

2. 删除视图
视图定义后，用户就可以像对基本表一样对视图进行查询了。
*视图删除后视图的定义就从数字字典上删除。如果该视图上还导出了其它视图，则使用CASCADE级联删除语句把该视图和由他导出的所有试图一起删除。
基本表删除后，又该基本表导出的所有视图都无法使用了，但是视图的定义没有从字典中清除。删除这些视图定义需要显式的地使用DROP BY语句。
3.查询视图
例3.92
在信息系学生的视图中找出年龄小于20岁的学生。

select Sno,Sage
from IS_Student
where  Sage<20;

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

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

本查询设计视图IS_Student(虚表)和基本表SC，通过这两个表的连接来完成用户请求。在一般情况下，视图查询的转换是直截了当的。但是在某些去情况下，这种转换不能直接进行，查询时就会出现错误，如例3.94
例 3.94
在S—G视图中查询平均成绩在80分以上的学生的学号和平均成绩，语句为
select *
from S_G
where Gavh>80;
例3.89中定义S_G视图的子查询为
select Sno,avg(grade)
from SC
group by Sno;
将本例中的查询语句与定义S_G视图的子查询结合，形成一下查询语句。
select Sno,avg(Grade)
from SC
where avg(Grade)>80
group by Sno;
因为where子句中不能使用聚集函数作为条件表达式，因此执行修正后的查询将会出现语法错误，正确的查询语句因该为;

select Sno,avg(Grade)
from SC
group by Sno
having avg(Grade)>80;

例 3.73更新视图
更新视图是指通过视图来插入、删除和修改数据。
例3.95
将信息系学生视图IS_Student中学号为“201215125”的学生姓名改为‘刘臣’。

update IS_Student
set Sname='刘臣'
where Sno='201215125';

例3.96
向信息系学生视图IS_Student 中插入一个新的学生记录，其中学号为‘201215129’，姓名为‘赵信’，年龄为20岁。
insert
into IS_Student
values(‘201215129’,‘赵信’,20);
转换为对基本表的更新;
insert
into Student（Sno,Sname,Sage,Sdept)
values（‘201215129’，‘赵信’,20,'IS’）；
这里系统自动将系名’IS‘放入VALUES子句中。
例3.97
删除信息系学生视图IS_Student中学号为’201215129‘的记录。

delete
from IS_Student
where Sno='201215129';

转换为对基本表的更新：

delete 
from Student
where Sno='201215129' and Sdept='IS';

在关系数据库中，并不是所有的视图都是可以更新的，因为i有些视图的更新不能唯一有意义的转换对应基本表的更新。
目前，各个关系数据库管理系统一般都只允许对行列子集视图进行更新，而且各个系统对视图的更新还有更进一步的规定。

3.7.4 视图的作用
1.视图能够简化用户的操作
2.视图能使用户以多种角度看待同一数据。
3.视图对重构数据库提供了一定程度的逻辑独立性。
4.视图能够对机密数据提供安全保护。
5适当利用视图能够更清晰的表达查询。

————————————————————
完成。