2021-4-12课程——SQL Server查询【5】+数据更新+空值处理+视图

本篇涉及内容较多，有集合查询，基于派生表的查询，数据更新操作（增删改），空值的处理和视图。总结顺序也是按照这个顺序。

一、集合查询

集合操作主要包括并操作UNION、交操作INTERSECT和差操作EXCEPT
PS：参加集合操作的各查询结果的列数必须相同，对应项的数据类型也必须相同

例1：查询计算机科学系的学生及年龄不大于19岁的学生。
即查询计算机科学系的学生和年龄不大于19岁的学生，很明显是一个并操作。使用union时，系统会自动去掉重复元组，如果要保留重复元组则需要使用union all操作符

使用union

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

使用union all并对比

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

例2：查询选修了课程1或者选修了课程2的学生

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

也可使用or

select distinct Sno -- 使用distinct去重
from SC
where Cno='1' or Cno='2';

例3：查询计算机科学系的学生与年龄不大于19岁的学生的交集

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

例4：查询既选修了课程1又选修了课程2的学生

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

上述语句也可更改为

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

例4：查询计算机科学系的学生与年龄不大于19岁的学生的差集
含义即为在计算机科学系的学生中去掉年龄不大于19岁的

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

二、基于派生表的查询

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

例1：查询每个学生超过自己选修课程平均成绩的课程号

该句中的子查询生成了一个派生表Avg_sc，记录了每个学生的学号和平均成绩。

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），否则不是必须。

例2：查询所有选修了1号课程的学生姓名

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

三、数据更新

1.插入数据

直接插入数据在之前的博客中已经总结过，不再赘述，放上链接

SQL Server——索引+基于单表的数据插入与简单查询【1】

下面总结插入子查询结果的插入

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

①首先新建一个表，其中一个存放系名，另一列存放相应的学生的平均年龄

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

2.修改数据

2.1修改某一个元组的值

例：将学生201215121的年龄改为22岁

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

2.2修改多个元组的值

例：将所有学生的年龄增加1岁

update Student
set Sage=Sage+1;

2.3带子查询的修改语句

例：将计算机科学系全体学生的成绩置0

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

3.删除数据

使用delete关键字，使用where限制条件，如果没有则表示删除表中所有元组，但表的定义仍在字典中。即delete语句删除的是表中的数据，而不是关于表的定义。

3.1删除某一个元组

例：删除学号为201215128的学生纪录

delete
from Student
where Sno='201215128';

3.2删除多个元组的值

例：删除所有的学生选课记录

delete
from SC;

3.3带子查询的删除语句

例：删除计算机科学系所有学生的选课记录

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

四、空值的处理

1.数据库中的空值即NULL，它有三种含义：不知道、不存在、无意义。SQL语言中允许某些元组的某些属性在一定情况下取空值，一般有以下三种情况。

①该属性应该有一个值，但目前不知道它的具体值。如，某学生的年龄属性，因为学生登记表漏填了，不知道该学生年龄，因此取空值。
②该属性不应该存值。例如，缺考学生的成绩为空，因为他没有参加考试。
③由于某种原因不便于填写。如一个人的电话号码等涉及个人隐私的内容。

2.空值的判断

使用is null或is not null

例：从Student表中找出漏填了数据的学生信息

select *
from Student
where Sname is null or Ssex is null or Sage is null or Sdept is null;

3.空值的约束条件

属性定义（或者域定义）中有not null约束条件的不能取空值，加了unique限制的属性不能取空值，码属性不能取空值。
空值与另一个值（包括另一个空值）的算术运算结果为空值，空值与另一个值（包括另一个空值）的比较运算的结果为unknown。在查询语句中，只有使where和having子句中的选择条件为true的元组才被选出作为输出结果

例1：找出选修1号课程的不及格的学生

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

选出的学生是那些参加了考试（Grade属性为非空值）而不及格的学生，不包括缺考的学生。因为前者使条件Grade<60的值为true，后者使条件的值为unknown。

例2：选出选修1号课程的不及格的学生以及缺考的学生

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

上述语句等价于

select Sno
from SC
where Cno='1' and (Grade<60 or Grade is NULL);

五、视图

视图是从一个或及格基本表（或视图）导出的表。它与基本表不同，是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍然存放在基本表中。所以一旦基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。视图一经定义，就可以和基本表一样被查询、被删除。也可以在一个视图之上再定义新的视图。

1.定义视图

1.1建立视图

create view<视图名> [(<列名> [,<列名>]…)]
as <子查询>
[vith check option];

with check option表示对视图进行update，insert和delete操作时要保证更新、插件或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）。组成视图的属性列名或者全部省略或者全部指定，没有第三种选择。如果省略了，则隐含该视图由子查询中的select子句目标列中的诸字段组成，但在下列三种情况必须明确指定组成视图的所有列名。
（1）某个目标列不是单纯的属性名，而是聚集函数或列表达式
（2）多表连接时选出了几个同名列做出视图的字段
（3）需要在视图中为某个列启用新的更合适的名字

例1：建立信息系学生的视图

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

关系数据库管理系统执行create view语句的结果只是把视图的定义存入数据字典，并不执行其中的select语句。只是在堆视图进行查询时，才按视图的定义从基本表中将数据查出。

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

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

加上了with check option，这样以后在对该视图进行插入、修改和删除操作时，关系数据库管理系统会自动加上Sdept='IS’的条件。

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

例3：建立信息系选修了1号课程的学生的视图（包括学号、姓名、成绩）

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

视图不仅可以建立在一个或多个基本表上，也可以建立在一个或多个已定义好的视图上，或建立在基本表与视图上。

例4：建立信息系选修了1号课程且成绩在90分以上的学生的视图

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

例5：定义一个反映学生出生年份的视图

派生属性列，实际并不存在，也称虚拟列，带虚拟列的视图也称为带表达式的视图。

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

例6：将学生的学号及平均成绩定义为一个视图

分组视图：带有聚集函数和group by子句的视图。通过聚集函数得到的目标列必须明确定义各个属性列名。

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

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

create view F_Student(F_sno,F_name,F_sex,F_age,F_dept)
as
select *
from Student
where Ssex='女';

1.2删除视图

drop view <视图名> [cascade];

例：删除视图BT_S和视图IS_S1

drop view BT_S;
drop view IS_S1;

2.查询视图

例1：在信息系学生的视图中找出年龄小于20岁的学生

select Sno,Sname
from IS_Student
where Sage<20;

视图消解：（关系数据库）在对视图进行查询时，首先进行有效性检查，检查涉及的表和视图等是否存在。若存在，则从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了查询。

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

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

例3：在S_G视图中查询平均成绩在80分以上的学生的学号和平均成绩（并未出问题。。就很迷）

select *
from S_G
where Gavg>=80

等价于

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

3.更新视图

概念：是指通过视图来插入、删除和修改数据，由于视图是不是存储数据的虚表，所以对视图的更新最终要转换为对基本表的更新。为了提高安全性，可以在定义视图时加上with check option子句，这样只有满足条件时才会执行。

例1：将信息系学生视图IS_Student中学号为“201215125”的学生姓名改为“刘辰”

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

例2：向信息系学生视图IS_Student中插入一个新的学生记录，其中学号为“201215129”，姓名为“赵新”，年龄为20岁

insert into
IS_Student
values('201215129','赵新',20);

在该视图中会发现并没有这条数据，而在基本表中是有的，下面这张图片就是。百度了一下，我都醉了，这T-SQL和标准SQL的差别也真是。。书上说的和实际的操作很多东西差别都很大。。

在sql2008中向视图插入数据发生错误

解决办法：
①重新建立视图

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

②插入数据

insert into
IS_Student
values('201215129','赵新',20,'IS');

插入成功！

例3：删除信息系学生视图IS_Student中学号为“201215129”的记录

delete
from IS_Student
where Sno='201215129';

没有赵新了！

4.视图的作用

①视图能够简化用户的操作
视图机制使用用户将注意力集中在所关心的数据上，可以简化用户的数据查询操作。

②视图使用户能以多种角度看待同一数据
视图机制能使不同的用户以不同的方式看待同一数据，有很大的灵活性。

③视图对重构数据库提供了一定程度的逻辑独立性
关系数据库中，新建立的视图定义为用户原来的关系，使使用的外模式保持不变，用户的应用程序通过视图仍然能够查找数据。

④视图能够对机密数据提供安全保护
对不同的用户定义不同的视图，使机密数据不出现在不应看到这些数据的用户视图上。

⑤适当利用视图可以更清晰地表达查询
因为视图是简化了之后的查询，所以查询起来更加容易。

六、课程总结

感觉T-SQL和标准SQL的差别还挺大的，许多问题需要自己实践发现并想办法解决。问题和要记忆的很多，许多东西要经常回顾，联想到一起效果会更好。