【Hbu数据库 】第六周 集合与基于派生表的查询、数据操作、空值处理、视图

经历了期末考试，新的一周周一感到异常的疲惫。

集合查询

集合操作主要包括并操作UNION 交操作INTERSECT和差操作EXCEPT

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

Select * from student
where sdept='cs' union 
select * from student
where sage<=19;

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

Select sno 
FROM sc
where cno='1'
UNION select sno
from sc
where cno='2';

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

select * from student
where sdept='cs'
intersect 
select *
from student
where sage<=19;

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

SELECT sno from sc
where CNO='1'
intersect
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(ave_sno,avg_grade)
where sc.sno=avg_sc.ave_sno and sc.grade>=avg_sc.avg_grade;

from子句的子查询将生成一个派生表AVG_sc,该表由avg_sno和avg_grade两个属性组成，记录每个学生的学号和平均成绩，主查询将sc表和avg_sc按照学号相同进行连接，并找出选修课成绩大于平时成绩的课程号。

如果子查询中没有聚集函数，派生表可以不指定属性列，子查询select子句后面的列明为其默认属性。

查询选修了一号课程的学生姓名

Select Sname
from student,(select sno from sc where cno='1') as sc1
where student.sno=sc1.sno;

这里只是举个例子 实际上要实现这个目的有许多简单的办法。

数据更新

数据更新分为三种：
向表中添加若干行数据
修改表中的数据
删除表中的若干行

插入数据

插入元组

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

Insert
into student(sno,sname,ssex,sdept,sage)
values('201911248','蔡徐坤','男','CS','18');

插入一个学生的信息到STUDNET表

INSERT
INTO student
values('201911824','布莱恩特','男','42','ba');

这里只说了表名没有说列名 插入的数据就要按照表的属性列一一对应

插入一条选课记录:

INSERT
INTO SC(SNO,CNO)
values('201215128','1');

注意这里是存在约束的 插入的学号必须是已经存在的。
等价于

INSERT
INTO SC
values('201215128','1',NULL);

插入子查询

子查询不仅可以嵌套在SELECT语句中用以构造父查询的条件 也可以嵌套在INSERT语句中用以生成要插入的批量数据

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

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

create table dept_age
(sdept CHAR(15),
	age smallint);

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

INSERT
INTO dept_age(sdept,age)
select sdept,avg(sage)
from student 
group by sdept;

修改数据

update 其功能是修改表中满足WHERE子句条件的元祖，其中SET语句给出《表达式》的值用于取代相应的属性列值。如果省略where子句，则表示要修改表中的所有元组。
1.修改一个元组的值

UPDATE student
SET sage=22
where sno='201215121';

2.修改多个元祖的值

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

UPDATE student
SET sage=sage+1;

3.带子查询的修改语句

将计算机科学系全体学生成绩归零

UPDATE sc
SET grade=0
where sno in
(select sno 
from student
where sdept='cs');

删除数据

delete语句删除的表的数据而非顶级

删除学号为201215128的学生记录

delete from Student
where sno='201215128';

删除多个元组的值：

	删除所有学生的选课记录

Delete from sc;

带子查询的删除语句

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

delete
from sc
where sno IN
	(
	select sno 
 from student
 where sdept='cs'
 );

空值的处理

空值是一种比较特殊的存在，在现实中有很多情况，所以要针对此种情况进行特殊的处理。

空值的产生：

	向sc表插入一个元组，学生号是'201215128',课程号是1，成绩为空

 insert into sc(sNO,CNO,GRADE)//不带括号里的也可以
values('201215128','1',NULL);
或者
 insert into sc(sNO,CNO)
values('201215128','1');

将Student表中学生号为’201215200’的学生所属的系改为空值

update student
set sdept =NULL
where sno='201215200';

空值的判断

判断一个属性是否为空值，用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;

显然 都填了

空值的算术运算

找出1号课程不满分的学生

select sno from sc 
where grade<100 and cno='1';

在sql里 这里选出的是不满分的学生 不包括空值（缺考）的学生，但是T-SQL里 都会筛选出来。

选出选修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.建立视图

CREATE  VIEW 
             <视图名>  [(<列名>  [,<列名>]…)]
       AS  <子查询>
       [WITH  CHECK  OPTION];

WITH CHECK OPTION
对视图进行更新操作时自动添加子查询中的条件
建立信息系学生的视图。

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

全部省略: 
由子查询中SELECT目标列中的诸字段组成
全部指定:（明确指定所有列名）
某个目标列是聚集函数或列表达式
多表连接时选出了几个同名列作为视图的字段
需要在视图中为某个列启用新的更合适的名字

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

这里省略了视图的列名，隐含了子查询中select子句中的三个列名组成

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

  CREATE VIEW Kun2_Student
        AS 
        SELECT Sno,Sname,Sage
        FROM  Student
        WHERE  Sdept= 'IS'
        WITH CHECK OPTION;

因为有with check option 在之后操作视图的时候 会自动带上sdept='is’的条件。

视图不仅可以建立在一个基本表上，还可以建立在多个基本表上。

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

也可以用聚集函数和Group by子句

用学生的学号及平均成绩定义一个视图

CREATE  VIEW S_G(Sno,Gavg)
             AS  
             SELECT Sno,AVG(Grade)
             FROM  SC
             GROUP BY Sno;

用Studnet表中所有女生定义为一个视图

    CREATE VIEW F_Student(F_Sno,name,sex,age,dept)
      AS
      SELECT  *                        /*没有指定属性列*/
      FROM  Student
      WHERE Ssex=‘女’;

需要注意的是！ 这里是用* 在后期如果改动了Student的表结构 那么视图也会被破坏 需要在修改基本表后 也同时删除视图 重建视图

删除视图

 删除视图BT_S和IS_S1

DROP VIEW BT_S;
DROP VIEW IS_S1;	

第二句会执行失败 因为在它之上还有IS_s2视图，需要使用CASCADE级联删除。

查询视图

视图消解法

1. 进行有效性检查

2. 转换成等价的对基本表的查询

3. 执行修正后的查询

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

      SELECT   Sno,Sage
       FROM      IS_Student
       WHERE   Sage<20;

其消解后为:

SELECT  Sno,Sage       
 FROM  Student
 WHERE  Sdept= 'IS'  AND  Sage<20;

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

SELECT *
FROM   S_G
WHERE  Gavg>=90;

其转换后为:

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

这里 where子句不能和聚合函数相搭配
应该为：

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

更新视图

将信息系学生视图IS_Student中学号”201215122”的学生姓名改为”wzk”。

UPDATE  IS_Student
SET  Sname= 'wzk'
WHERE  Sno= '201215121';

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

INSERT
INTO IS_Student
VALUES('201215129','赵新',20);

删除信息系学生视图IS_Student中学号为'201215121'学生的记录

DELETE
FROM IS_Student
WHERE Sno= '201215121';

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

UPDATE  S_G
SET          Gavg=90
WHERE  Sno= '201215121';

Gavg并不存在对应的基本表中的实际数据。是聚合出来的数据