数据库系统概论（超详解！！！）第三节关系数据库标准语言SQL（Ⅵ）

1.空值的处理

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

一般有以下几种情况：该属性应该有一个值，但目前不知道它的具体值；该属性不应该有值；由于某种原因不便于填写。

1.空值的产生

空值是一个很特殊的值，含有不确定性。对关系运算带来特殊的问题，需要做特殊的处理

向SC表中插入一个元组，学生号是”201215126”，课程号是”1”，成绩为空。
 INSERT INTO SC(Sno,Cno,Grade)
 VALUES('201215126 ','1',NULL);   /*该学生还没有考试成绩，取空值*/
或
 INSERT INTO SC(Sno,Cno)
 VALUES(' 201215126 ','1');             /*没有赋值的属性，其值为空值*/

将Student表中学生号为”201215200”的学生所属的系改为空值。
	UPDATE Student
	SET Sdept = NULL
	WHERE Sno='201215200';

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限制的属性不能取空值；码属性不能取空值

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

空值与另一个值（包括另一个空值）的算术运算的结果为空值

空值与另一个值（包括另一个空值）的比较运算的结果为UNKNOWN。

有UNKNOWN后，传统二值（TRUE，FALSE）逻辑就扩展成了三值逻辑。

找出选修1号课程的不及格的学生。
   SELECT Sno
   FROM SC
   WHERE Grade < 60 AND Cno='1';

  查询结果不包括缺考的学生，因为他们的Grade值为
  null。

选出选修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);

2.视图

视图的特点：

虚表，是从一个或几个基本表（或视图）导出的表

只存放视图的定义，不存放视图对应的数据

基表中的数据发生变化，从视图中查询出的数据也随之改变

1.定义视图

1.建立视图

语句格式：

CREATE VIEW <视图名> [(<列名> [,<列名>]…)]

AS <子查询>

[WITH CHECK OPTION];

WITH CHECK OPTION ：对视图进行UPDATE，INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）

子查询可以是任意的SELECT语句，是否可以含有ORDER BY子句和DISTINCT短语，则决定具体系统的实现。

组成视图的属性列名：全部省略或全部指定

全部省略: 由子查询中SELECT目标列中的诸字段组成

明确指定视图的所有列名: 某个目标列是聚集函数或列表达式；多表连接时选出了几个同名列作为视图的字段；需要在视图中为某个列启用新的更合适的名字

关系数据库管理系统执行CREATE VIEW语句时只是把视图定义存入数据字典，并不执行其中的SELECT语句。

在对视图查询时，按视图的定义从基本表中将数据查出。

建立信息系学生的视图。

        CREATE VIEW IS_Student
        AS 
            SELECT Sno,Sname,Sage
            FROM     Student
            WHERE  Sdept= 'IS';

建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生 。
         CREATE VIEW IS_Student
        AS 
             SELECT Sno,Sname,Sage
             FROM  Student
             WHERE  Sdept= 'IS'
             WITH CHECK OPTION;

定义IS_Student视图时加上了WITH CHECK OPTION子句，对该视图进行插入、修改和删除操作时，RDBMS会自动加上Sdept='IS'的条件。

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

基于多个基表的视图：

建立信息系选修了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';

基于视图的视图：
建立信息系选修了1号课程且成绩在90分以上的学生的视图。
        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;

分组视图：
将学生的学号及平均成绩定义为一个视图
	       CREAT  VIEW S_G(Sno,Gavg)
             AS  
             SELECT Sno,AVG(Grade)
             FROM  SC
             GROUP BY Sno;

将Student表中所有女生记录定义为一个视图
      CREATE VIEW F_Student(F_Sno,name,sex,age,dept)
      AS
      SELECT  *                        /*没有指定属性列*/
      FROM  Student
      WHERE Ssex=‘女’;
     缺点：修改基表Student的结构后，Student表与F_Student视图的映象关系被破坏，导致该视图不能正确工作。

2.删除视图

语句的格式：

DROP VIEW <视图名>[CASCADE];

该语句从数据字典中删除指定的视图定义

如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除

删除基表时，由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除

删除视图BT_S和IS_S1
		DROP VIEW BT_S;	/*成功执行*/
		DROP VIEW IS_S1;	/*拒绝执行*/

2.查询视图

用户角度：查询视图与查询基本表相同

关系数据库管理系统实现视图查询的方法：

视图消解法（View Resolution）：进行有效性检查；转换成等价的对基本表的查询；执行修正后的查询。

在信息系学生的视图中找出年龄小于20岁的学生。
       SELECT   Sno,Sage
       FROM      IS_Student
       WHERE   Sage<20;

视图消解转换后的查询语句为：
 SELECT  Sno,Sage       
 FROM  Student
 WHERE  Sdept= 'IS'  AND  Sage<20;

查询选修了1号课程的信息系学生
SELECT  IS_Student.Sno,Sname
FROM     IS_Student,SC
WHERE  IS_Student.Sno =SC.Sno AND SC.Cno= '1';

视图消解法的局限：

有些情况下，视图消解法不能生成正确的查询。

在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩
SELECT *
FROM   S_G
WHERE  Gavg>=90;

S_G视图的子查询定义： 
CREATE VIEW S_G (Sno,Gavg)
AS 
SELECT  Sno,AVG(Grade)
FROM  SC
GROUP BY Sno;

错误：
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;

也可以用如下SQL语句完成
	SELECT *
    FROM  (SELECT Sno,AVG(Grade)
		       FROM  SC 
 		       GROUP BY Sno) AS S_G(Sno,Gavg)
    WHERE Gavg>=90;

3.更新视图

将信息系学生视图IS_Student中学号”201215122”的学生姓名改为”刘辰”。
UPDATE  IS_Student
SET  Sname= '刘辰'
WHERE  Sno= ' 201215122 ';
转换后的语句：
UPDATE  Student
SET Sname= '刘辰'
WHERE Sno= ' 201215122 ' AND Sdept= 'IS';

向信息系学生视图IS_S中插入一个新的学生记录，其中学号为”201215129”，姓名为”赵新”，年龄为20岁
INSERT
INTO IS_Student
VALUES(‘201215129’,’赵新’,20);
转换为对基本表的更新：
INSERT
INTO   Student(Sno,Sname,Sage,Sdept)
VALUES(‘200215129 ','赵新',20,'IS' );

删除信息系学生视图IS_Student中学号为”201215129”的记录 
DELETE
FROM IS_Student
WHERE Sno= ' 201215129 ';
转换为对基本表的更新：
DELETE
FROM Student
WHERE Sno= ' 201215129 ' AND Sdept= 'IS';

更新视图的限制：一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新

视图S_G为不可更新视图。
UPDATE  S_G
SET          Gavg=90
WHERE  Sno= '201215121';

这个对视图的更新无法转换成对基本表SC的更新

允许对行列子集视图进行更新对其他类型视图的更新不同系统有不同限制

将SC中成绩在平均成绩之上的元组定义成一个视图
 
      CREATE VIEW GOOD_SC
      AS 
      SELECT  Sno,Cno,Grade
      FROM     SC
      WHERE Grade >(SELECT AVG(Grade)
                    FROM     SC);
 一个不允许更新的视图上定义的视图也不允许更新

4.视图的作用

视图能够简化用户的操作：当视图中数据不是直接来自基本表时，定义视图能够简化用户的操作基于多张表连接形成的视图；基于复杂嵌套查询的视图；含导出属性的视图

视图使用户能以多种角度看待同一数据：视图机制能使不同用户以不同方式看待同一数据，适应数据库共享的需要

视图对重构数据库提供了一定程度的逻辑独立性
学生关系Student(Sno,Sname,Ssex,Sage,Sdept) 
	“垂直”地分成两个基本表：
        SX(Sno,Sname,Sage)        
	    SY(Sno,Ssex,Sdept)

通过建立一个视图Student：
CREATE VIEW  Student(Sno,Sname,Ssex,Sage,Sdept)
AS  
     SELECT  SX.Sno,SX.Sname,SY.Ssex,SX.Sage,SY.Sdept
     FROM  SX,SY
     WHERE  SX.Sno=SY.Sno;
   使用户的外模式保持不变，用户的应用程序通过视图仍然能够
   查找数据

视图对重构数据库提供了一定程度的逻辑独立性：由于对视图的更新是有条件的，因此应用程序中修改数据的语句可能仍会因基本表结构的改变而改变。

视图能够对机密数据提供安全保护：对不同用户定义不同视图，使每个用户只能看到他有权看到的数据

适当的利用视图可以更清晰的表达查询：经常需要执行这样的查询“对每个同学找出他获得最高成绩的课程号”。可以先定义一个视图，求出每个同学获得的最高成绩

CREATE VIEW VMGRADE
     AS
          SELECT Sno, MAX(Grade)  Mgrade
          FROM  SC
          GROUP BY Sno;
然后用如下的查询语句完成查询：
     SELECT SC.Sno,Cno
     FROM SC,VMGRADE 
     WHERE SC.Sno=VMGRADE.Sno AND       
     SC.Grade=VMGRADE .Mgrade;

3.练习

/*1.建立一个计算机科学与技术专业的学生视图V1*/
create view v1
as
select *
from Student
where Smajor='计算机科学与技术';

/*2.建立一个'数据库系统概论'课程的选修情况视图（包括学号，姓名，成绩和所在专业）V2*/
create view v2
as
select student.Sno,Sname,Grade,Smajor,Cname
from Student,SC,Course
where Student.Sno=SC.Sno and SC.Cno=Course.Cno and Cname='数据库系统概论';

/*3.建立计算机科学与技术专业选修了’数据库系统概论'的学生的视图（包括学号、姓名、成绩）V3*/
create view v3
as
select student.Sno,Sname,Grade
from Student,SC,Course
where Student.Sno=SC.Sno and SC.Cno=Course.Cno and Cname='数据库系统概论' and Smajor='计算机科学与技术';

/*4.建立信息安全专业选修了’数据结构'且成绩不及格的学生视图（包括学号、姓名、成绩）V4*/
create view v4
as
select student.Sno,Sname,Grade
from Student,SC,Course
where Student.Sno=SC.Sno and SC.Cno=Course.Cno and Cname='数据结构'and Smajor='信息安全' and Grade<60;

/*5.将每门课程的课程号，课程名、最高分、最低分和平均分定义为一个视图V5*/
create view v5
as
select Course.Cno,Cname,MAX(Grade)as max,MIN(Grade)as min,AVG(Grade)as avg
from Student,SC,Course
where Student.Sno=SC.Sno and SC.Cno=Course.Cno
group by Course.Cno,Cname;

/*6.查询计算机科学与技术专业小于21岁的女生学号和姓名（基于以上视图实现）*/
select sno,sname
from v1
where YEAR(GETDATE())-YEAR(Sbirthdate)<21 and Ssex='女';

/*7.查询既选了'数据库系统概论'又选了'数据结构'的学生学号和姓名（基于以上视图实现）*/
select v2.sno,sname
from v2,sc
where v2.Sno=SC.Sno and Cno in (select Cno
                                from Course
                                where Cname='数据结构');

/*8.查询平均分最高的课程号、课程名和平均分（基于以上视图实现）*/
select top (1) Cno,Cname, avg
from v5
order by avg desc;

/*9.将计算机科学与技术专业的所有学生选修的81001号课程成绩加2分基于以上视图实现）*/
update  SC
set Grade
where Cno='81001' and Sno in (select sno
                              from v1);

/*10.删除计算机科学与技术专业的学生视图V1*/
drop view v1;