数据查询—集合、派生表的查询以及数据更新和视图
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适当利用视图能够更清晰的表达查询。
————————————————————
完成。