SQL Server中的集合查询、数据更新、空值处理与视图

集合查询

SELECT语句的查询结果是元组的集合，所以多个SELECT语句的结果可进行集合操作。集合操作主要包括并操作UNION、交操作INTERSECT和差操作EXCEPT。（注意参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同。）

查询计算机系的学生及年龄不大于19岁的学生：

select*
from Student
WHERE Sdept='CS'
UNION
SELECT*
FROM Student
WHERE Sage<=19;

使用UNION将多个查询结果合并起来时，系统会自动去掉重复元组。如果要保留重复元组则用UNION ALL操作符。

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

select*
from Student
WHERE Sdept='CS'
INTERSECT
SELECT*
FROM Student
WHERE Sage<=19; 

插入的数据中无满足条件的信息。

该查询也可以用连接查询来完成：

select*
from Student
WHERE Sdept='CS'AND Sage<=19;

查询既选修了课程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 Cno='2');

查询计算机系的学生与年龄不大于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

如果子查询没有聚焦函数，派生表可以不指定属性列，子查询SELECT子句后面的列名为其默认属性。
而且通过FROM子句生成派生表时，AS关键字可以省略，但必须为派生关系指定一个别名。而对于基本表，别名是可选择项。

数据更新

数据更新操作有三种：向表中添加若干行数据、修改表中的数据和删除表中的若干行数据。在SQL中有对应的三类语句。

插入数据
SQL的数据插入语句INSERT通常有两种形式，一种是插入一个元组，另一种是插入子查询结果。后者可以一次插入多个元组。

将一个新学生元组插入Student表中：

INSERT
INTO Student (Sno,Sname,Ssex,Sage,Sdept)
VALUES('201215128','陈冬','男',18,'CS');

插入元组的功能是将新元组插入指定表中,INTO子句中没有出现的属性列，新元组在这些列上将取空值。但必须注意在表定义是说明了NOT NULL的属性名不能取空值，否则会出错。
如果INTO子句中没有指明任何属性列名，则新插入的元组必须在每个属性列上均有值

插入一条选课记录;

INSERT
INTO SC(Sno,Cno)
VALUES('201215128','1');

插入子查询结果

对每个系，求学生的平均年龄，并把结果存入数据库:
首先先建立一个新表，然后对Student表按系分组求平均年龄，再将系名和平均年龄存入新表。

修改数据
修改操作又称为更新操作，其功能是修改指定表中满足WHERE子句条件的元组，其中SET子句给出的值用于取代相应的属性列值。如果省略WHERE子句，则表示要修改表中的所有元组。

修改某一个元组的值（修改一个学生的年龄）：

UPDATE Student
SET Sage=22
WHERE Sno='201215121';

修改多个元组的值（所有学生的年龄加一）：

UPDATE Student
SET Sage=Sage+1;

带子查询的修改语句（将成绩全部置零）：

UPDATE SC
set Grade=0
where sno in
	(select sno
	 from student
	 where sdept='CS');

删除数据

DELETE 语句的功能是指从指定表中删除满足WHERE子句条件的所有元组。如果省略WHERE子句则表示删除表中全部元组，但表中的定义仍在字典中。也就是说，DELETE语句删除的是表中的数据，而不是关于表的定义。

删除某一个元组的值（删除一个学生记录）：

DELETE
from Student
WHERE Sno='201215128';

删除多个元组的值（删除所有学生选课记录）：

DELETE
from sc;

该DELETE语句使SC成为空表，它删除了SC的所有元组。

带子查询的删除语句（删除所有学生选课记录）：
子查询同样也可以嵌套在DELETE语句，用以构造执行删除操作的条件

DELETE
from SC
WHERE Sno in
		(select Sno
		 from Student
		 where sdept='cs');

应注意对某个基本表中数据的增、删、改操作有可能会破坏参照完整性。

空值的处理

所谓空值就是“不知道”或“不存在”或“无意义”的值。SQL语言中允许某些元组的某些属性在一定情况下取空值。一般有以下几种情况：
1.该属性应该有一个值，但目前不知道它的具体值。
2.该属性不应该有值
3.由于某种原因不便于填写。
因此，空值是一个很特殊的值，含不确定性，对关系运算带来特殊的问题，需要做特殊的处理。

空值的产生（插入为空的数据或者将某一数据改为空）：

insert into sc(sno,cno,grade)
		values('201215126','1',null);

update student
set sdept=null
where sno='201215121';

注意，外连接也会产生空值，空值的关系运算也会产生空值。

空值的判断
判断一个属性的值是否为空值，用IS NULL或IS NOT NULL来表示。
搜索漏填的数据：

select*
from Student
where sname is null or ssex is null or sage is null or sdept is null;

空值的约束条件：
属性定义（或者域定义）中有NOT NULL约束条件的不能取空值，加了UNIQUE限制的属性不能取空值，码属性不能取空值。

空值的算术运算、比较运算和逻辑运算

找出选修某课程不及格的学生：

select sno
from sc
where grade<60 and cno='2';

查询选修了某课程的不及格学生以及缺考的学生：

select sno
from sc
where grade<60 and cno='2'
union
select sno
from sc
where grade is null and cno='2';

视图

视图是从一个或几个基本表（或视图）导出的表。与基本表不同，是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。视图一经定义，就可以和基本表一样被查询、被删除。也可以在一个视图之上再定义新的视图，但对视图的更新（增、删、改）操作则有一定的限制。

定义视图
建立视图
建立学生的视图：

create view is_student
as
select sno,sname,sage
from student
where sdept='is';

建立选修了课程的学生信息的视图：

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

在另一个视图上建立视图：

create view is_s2
as
select Sno,sname,grade
from is_s1
where grade>=90;

建立带表达式的视图：

create view bt_s(sno,sname,sbirth)
as
select sno,sname,2014-sage
from student;

删除视图

drop view bt_s;
drop view is_s1;

查询视图

select sno
from is_student;

视图的查询与表的查询差别不大，查询语句基本可以转换过来，建立的视图中数据不足，其余查询省略。

更新视图

更新视图是指通过视图插入、删除和修改数据。因为视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新。像查询视图那样，对视图的更新操作也是通过视图消解，转换为对基本表的更新操作。

修改信息（修改某学生名字）：

update is_student
set sname='刘辰'
where sno='201215122';

插入一个学生记录：

insert
into is_student
values('201215129','赵新');

删除一个学生信息：

delete
from is_student
where sno='201215129';

视图的作用

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

SQL可以分为数据定义、数据查询、数据更新、数据控制四大部分，有时把数据更新为数据操纵，或把数据查询与数据更新合称为数据操纵。

本次实验内容较多，其中对视图的操作与对表的操纵基本具有相似性，但视图的操作涉及的限制更多，此处就不详说。