2021-04-12--数据查询（空值，视图）

1.集合查询

  select语句的査询结果是元组的集合，所以多个select语句的结果可进行集合操作。集合操作主要包括并操作union、交操intersect和差操作except.

【例 3.64】 査询计算机科学系的学生及年龄不大于19岁的学生。

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

【例 3.65】査询选修了课程1或者选修了课程2的学生。

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

【例 3.66】査询计算机科学系的学生与年龄不大于19岁的学生的交集。

select *
from Student
where Sdep='CS'
intersect
select *
from Student
where Sage<=19;
--等价于
select *
from Student
where Sdep='CS' and
	  Sage<=19;

【例 3.67】査询既选修了课程1又选修了课程2的学生。就是査询选修课程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');

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

select *
from Student
where Sdep='CS'
except
select *
from Student
where Sage<=19;
--等价于
select *
from Student
where Sdep='CS' and Sage>19;

3.5数据更新

1.插入数据

（1）插入元组的语句格式如下：

insert 
into <表名>[(<属性列1> [,<属性列2>] ... )]
values(<常量1> [ , <常量2>] ...);

enmmm，那三个例子前面已经写过，不再叙述。

（2）插入子查询结果的语句格式如下：

insert
into <表名> [(<属性列1>  [ ,<属性列2>] ...)]
子查询;

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

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

create table Dept_age
		(Sdep char(15),
		 Avg_age smallint);

然后将Student表中按系别分组求平均年龄，再将系名和平均年龄存入新表。

insert 
into Dept_age(Sdep,Avg_age)
select Sdep,avg(Sage)
from Student
group by Sdep;

2.修改数据

修改数据，格式如下：

update  <表名>
set  <列名> = <表达式>[,<列名> = <表达式>]...
[where <条件>];

  其功能是修改指定表中满足WHERE 子句条件的元祖。其中SET 子句给出<表达式>的值用于取代相应的属性列值。如果省略WHERE 子句，则表示要修改表中的所有元祖。

（1）修改某一个元祖的值

[例3.73] 将学生201215121的年龄改为22岁

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

（2）修改多个元祖的值。

[ 例3.74] 将所有学生的年龄增加1岁。

 update Student 
 set Sage=Sage+1;

对比上个表，可以发现，所有人的年龄均增加1。

（3）带子查询的修改语句

[例3.75] 将计算机科学系全体学生的成绩置为1。

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

3.删除数据

删除语句的一般格式为：

delete
from <表名>
[where <条件> ];

1、删除某一个元组的值

[例3.76] 删除学号为201215128的学生记录。

delete
from Student
where Sno='201215128';

2、删除多个元组的值

[例3.77] 删除所有的学生选课记录。

delete 
from SC;

[例3.78] 删除计算机科学系所有学生的选课记录。

DELETE
FROM SC
WHERE Sno IN
	(SELECT Sno
     FROM Student
     WHERE Sdept='CS');

对某个基本表中数据的增、删、改操作有可能破坏参照完整性。而且删除前，记得保存之前存入的数据，否则再输入时会很累人的！！！
 
3.6 空值的处理

1.空值就是“不知道”或“不存在”或“无意义”的值。

2.该属性应该有一个值，但目前不知道它的具体值

3.该属性不应该有值

4.由于某种原因不便于填写。空值是一个很特殊的值，含有不确定性。

1、空值的产生：

[例 3.79]向SC表中插入一个元组，学生号是”201215125”，课程号是”1”，成绩为空。

insert into SC(Sno,Cno,Grade)
		values('201215125','1',NULL);
--或者
insert into SC(Sno,Cno)
        values('201215125','1');

[例3.80] 将Student表中学生号为”201215125”的学生所属的系改为空值。

update Student
set Sdep = NULL
where Sno='201215125';

2、 空值的判断
  判断一个属性的值是否为空值，用is null或is not null来表示。

[例 3.81] 从Student表中找出漏填了数据的学生信息。

3、空值的约束条件

（1）属性定义中，如果有not null约束条件的不能取空值
（2）加了unique限制的属性不能取空值
（3）码属性不能取空值

[例3.82] 找出选修1号课程的不及格的学生。

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

[例 3.83] 选出选修1号课程的不及格的学生以及缺考的学生。查询结果要包括grade值为null的学生。

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

 
3.7视图

  视图是从一个或几个基本表（或视图）导出的表。它与基本表不同，是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。所以一旦基本表中的数据发生变化，从试图查询出来的数据也随之改变了。视图就像一个窗口。

  视图一经定义，就可以和基本表一样被查询、被删除。也可以在一个视图上再定义新的视图，但对视图的更新操作则有一定的限制。

1.定义视图

格式如下：

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

[例3.84] 建立信息系学生的视图。

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

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

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

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

  视图不仅可以建立在单个基本表中，也可以建立在多个基本表上。

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

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

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

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

[例3.88] 定义一个反映学生出生年份的视图。

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

[例3.89] 将学生的学号及平均成绩定义为一个视图。

create view S_G(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='女';

删除视图,语句格式如下：

drop view  <视图名>[cascade];

1.如果该视图上还导出了其他视图，使用cascade级联删除语句，把该视图和由它导出的所有视图一起删除;
2.删除基表时，由该基表导出的所有视图定义都必须显式地使用drop view语句删除

[例3.91 ] 删除视图BT_S和IS_S1。

drop view IS_S1 cascade;

其实下面语句就可以删除（这是在SQL Server中）

drop view BT_S;
drop view IS_S1;

2、查询视图

[例3.92] 在信息系学生的视图中找出年龄小于20岁的学生。

select   Sno,Sage
from      IS_Student
where   Sage<25;
--等价于
select  Sno,Sage       
from  Student
where  Sdep= 'IS'  AND  Sage<25;

enmmm，年龄改的大一点，有数据。

[例3.94]在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩。

select *
from S_G
where Gavg>=90;

  但是聚合不应出现在 WHERE 子句中，除非该聚合位于 HAVING 子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。

SELECT Sno,AVG(Grade)
FROM     SC
WHERE  AVG(Grade)>=90
GROUP BY Sno;

应该改为：

SELECT  Sno,AVG(Grade)
FROM  SC
GROUP BY Sno
HAVING AVG(Grade)>=90;

3.更新视图

[例3.95] 将信息系学生视图IS_Student中学号”201215125”的学生姓名改为”刘辰”。

update  IS_Student
set  Sname= '刘辰'
where  Sno= ' 201215125 ';
--等价于
update  Student
set Sname = '刘辰'
where Sno = ' 201215125 ' and Sdept= 'IS';

[例3.96] 向信息系学生视图IS_S中插入一个新的学生记录，其中学号为”201215130”，姓名为”赵新”，年龄为20岁。

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

带有with check option子句，对该视图进行插入、修改和删除操作时，会自动加上Sdept='IS’的条件。

with check option子句会在更新数据的时候检查新数据是否符合视图定义中where子句的条件。

[例3.97]删除信息系学生视图IS_Student中学号为”201215129”的记录。

delete
from IS_Student
where Sno= ' 201215129 ';
--等价于
delete
from Student
where Sno= ' 201215129 ' AND Sdept= 'IS';

总结

  例题有点小多， SQL Server和标准的有些地方还有一些不一样的地方，还得抽时间理解和记忆一些知识点。加油吧。。