查询收尾、带子查询的数据更新、视图 4.12数据库课程实验

学习完了SQL语言对数据库的增删改查，包括集合查询、基于派生表的查询、数据更新和视图

1、集合查询

select 的查询结果是元组的集合，所以可进行多个查询结果间交集、并集和差集的计算，直接将两个查询语句连接即可

1.1 并集

• union：并集

--并集，cs系的学生及(或)年龄不大于20岁的学生
select Sname,Sage,Sdept
from Student
where Sdept='cs'
union
select Sname,Sage,Sdept
from Student
where Sage<=20; --自动去除重复元组

select Sname,Sage,Sdept
from Student
where Sdept='cs'
union all
select Sname,Sage,Sdept
from Student
where Sage<=20; --保留所有元组使用union all

1.2 交集

• intersect：交集

select Sname,Sage,Sdept
from Student
where Sdept='cs'
intersect
select Sname,Sage,Sdept
from Student
where Sage<=20; --cs系且年龄不大于20的学生

1.3 差集

• except：差集

select Sname,Sage,Sdept
from Student
where Sdept='cs'
except
select Sname,Sage,Sdept
from Student
where Sage>20; --查询cs系学生与年龄大于20岁的学生的差集
--先查询出cs系学生，再将其中年龄大于20的剔除

2、基于派生表的查询

派生表使用在 from 语句后，临时使用，查询结束后删除，不会像基本表那样占用内存

select Student.Sname,Course.Cname,SC.Grade,Avg_sc.avg_grade
from Student,SC,Course,
	(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
	and Student.Sno=SC.Sno and SC.Cno=Course.Cno; --查询学生分数大于其平均分的学科

派生表必须指定别名，没有使用聚集函数时，列名可省略

3、带子查询的数据更新

简单的增删改操作以前总结过了，今天学习了使用子查询的增删改

3.1 插入数据

子查询可嵌套在 insert 中，一次插入一个查询结果

create table Dept_age
(Sdept varchar(20),Avg_age smallint); --建个新表

insert into Dept_age(Sdept,Avg_age)
select Sdept,avg(Sage)
from Student
group by Sdept; --插入查询结果

select * from Dept_age;

3.2 修改数据

子查询也可嵌套在 update 语句中

update SC
set Grade=null
where Sno in
	(select Sno
	from Student
	where Sdept='cs'); --cs系成绩置空

select Student.Sname,Student.Sdept,SC.Cno,SC.Grade
from Student,SC
where Student.Sno=SC.Sno and Student.Sdept='cs';

3.3 删除数据

子查询也可嵌套在 delete 语句中

delete
from SC
where Sno in
	(select Sno
	from Student
	where Sdept is null); --删除无专业学生的选课记录

4、空值

空值是无意义或不存在的属性值，不是nulll字符串，所以在判断空值时需要使用 is (not) null 而不是 =null

5、视图

视图是从一个或几个基本表导出的表，是一个虚表。它只存放视图的定义，而不存放具体对应的数据

5.1 创建视图

若一个视图是从一个基本表中导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，则为行列子集视图

/*create view <视图名> [<列名>,<列名>...]
as <子查询>
[with check option]*/

create view cs_student
as
select Sno,Sname,Sage
from Student
where Sdept='cs'; --建立行列子集视图，保存cs系的学生信息

如果在结尾添加了with check option，则在修改该视图时数据库管理系统会自动加上建立视图时的where条件，即保证该视图中仅有cs系学生

create view cs_student
as
select Sno,Sname,Sage,Sdept
from Student
where Sdept='cs'
with check option; --建立视图

建立视图的语句必须单一执行

insert into cs_student
values('1998','王大狗',21,'cs'); --可成功执行

insert into cs_student
values('199889','王小狗',21,'is'); --无法执行，不是cs系

视图还可建立在多个基本表之上，建立视图时可指定视图的列名

create view cs_sc(学号,姓名,课程号)
as
select Student.Sno,Sname,Cno
from Student,SC
where Sdept='cs' and Student.Sno=SC.Sno;
--建立cs系选修课学生的视图

视图也可建立在一个或多个定义好的视图之上

create view cs_wbc(Sname,Cname,Grade)
as
select Sname,Cname,Grade
from Student,SC,Course
where Student.Sname='王伯成' and 
	Student.Sno=SC.Sno and SC.Cno=Course.Cno; --建立王伯成的选修课成绩视图

create view cs_wbc_m90(Sname,Cname,Grade)
as
select Sname,Cname,Grade
from cs_wbc
where Grade>=90; --依托cs_wbc视图再建立视图

还可以用带有聚集函数和group by子句的查询来定义视图，称为分组视图

create view avg_sdept(Sdept,avg_age)
as
select Sdept,avg(Sage)
from Student
group by Sdept; --建立反应专业平均年龄的视图

5.2 删除视图

和删除模式一样，分为cascade级联和restrict删除，T-SQL都不支持

drop view avg_sdept;
drop view cs_wbc; --删除后，其导出的视图仍存在且仍可使用
--都可成功执行

5.3 查询视图

查询视图和查询基本表相同，因为视图存储的是视图的定义语句，在查询视图时会执行视图消解：把视图定义语句和视图查询语句融合为对表的查询语句

select Sname,Sage
from cs_student
where Sage<=20;

select Sname,Sage
from Student
where Sdept='cs' and Sage<=20; --与上述等价

当建立视图使用聚集函数时，查询视图在视图消解转换时会发生错误

/*select Sno,avg(Grade)
from SC
group by Sno; --定义S_G视图时的子查询 */

select *
from S_G
where Gavg>=90; --查询选修课平均分大于90的学生

--试图消解后:
select Sno,avg(Grade)
from SC
where avg(Grade)>=90
gropu by Sno;

很明显是错误的，聚集函数用作筛选条件时需要放在 having 语句后，可用临时派生表完成上述查询

select *
from
	(select Sno,avg(Grade)
	from SC
	group by Sno)as S_G(Sno,Gavg)
where Gavg>=90;

5.4 更新视图

对视图的更新最终将转换为对基本表的更新，对视图更新后，其依赖的基本表也将做出变化

update cs_student
set Sname='王大宝'
where Sno='20191102041';

update Student
set Sname='王大宝'
where Sno='20191102041'; --与上述等价

--该视图使用了with check option
insert into cs_student
values('1998','王大狗',21,'cs'); --可成功执行

insert into Student
values('1998','王大狗','男',21,'cs'); --与上述等价

insert into cs_student
values('199889','王小狗',21,'is'); --无法执行，不是cs系

delete from cs_student
where Sno='1998';

delete from Student
where Sno='1998'; --与上述等价

对视图进行更新时，更新的列需要唯一的对应其基本表的某一列，即若视图的某列是聚集函数或运算表达式得出的，则无法更新

update avg_sdept
set avg_age=21
where Sdept='cs'; --无法修改，基本表没有avg_age这一列，这是聚集函数分组得到的列

SQL语言似乎学完了，但是感觉好多需要重新看一遍。夜深了，睡觉吧