本节课内容数据查询、数据更新、空值处理、视图
目录
3.4 数据查询
3.4.4 集合查询
集合操作的种类
并-UNION
交-INTERSECT
差-EXCEPT
注意，参加集合操作各查询结果的列数必须相同，对应项的数据类型也必须相同
1.UNION并
例 3.64查询计算机科学系的学生及年龄不大于19岁的学生。
SELECT *
FROM Student
WHERE Sdept='CS'
UNION
SELECT *
FROM Student
WHERE Sage<=19
使用UNION将多个查询结果合并起来时，系统自动去掉重复元组。若想保留重复元组则用UNION ALL操作符
例 3.65询选修了课程1或者选修了课程2的学生。
SELECT Sno
FROM SC
WHERE Cno='1'
UNION
SELECT Sno
FROM SC
WHERE Cno='2';
2.INTERSECT 交
例3.66查询计算机科学系的学生与年龄不大于19岁的学生交集。
SELECT *
FROM Student
WHERE Sdept='CS'
INTERSECT
SELECT *
FROM Student
WHERE Sage<=19;
实际上就是查询计算机科学系中年龄不大于19岁的学生。
用连接查询解决该问题：
SELECT *
FROM Student
WHERE Sdept='CS'AND Sage<=19;
例 3.67查询既选修了课程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.EXCEPT差
例 3.68 查询计算机科学系的学生与年龄不大于19岁的学生的差集。
SELECT *
FROM Student
WHERE Sdept='CS'
EXCEPT
SELECT *
FROM Student
WHERE Sage<=19;
实际上是查询计算机科学系中年龄大于19岁的学生
SELECT *
FROM Student
WHERE Sdept='CS'AND Sage>19
3.4.5 基于派生表的查询
子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，
这时子查询生成的临时派生表成为主查询的查询对象。
如例3.57找出每个学生超过他自己选修课程平均成绩的课程号，也可用如下的查询完成
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子句后面的列名为其缺省属性。
查询所有选修了1号课程的学生姓名，可以用如下查询完成：
SELECT Sname
FROM Student,(SELECT Sno FROM SC GROUP BY Sno)
AS SC1
WHERE Student.Sno=SC1.Sno
3.4.6 SELECT语句的一般形式
SELECT [ALL|DISTINCT]
<目标列表达式> [别名] [ ,<目标列表达式> [别名]] …
FROM <表名或视图名> [别名]
[ ,<表名或视图名> [别名]] …
|(<SELECT语句>)[AS]<别名>
[WHERE <条件表达式>]
[GROUP BY <列名1>[HAVING<条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]];
3.5 数据更新
3.5.1 插入数据
两种插入数据方式
①插入元组
之前的博客已经写过例3.69，例3.70，例3.71SQL Server 数据库实验课第三周——INDEX、INSERT、SELECT语句
②插入子查询结果（可以一次插入多个元组）
语句格式
INSERT
INTO <表名> [(<属性列1> [,<属性列2>… )]
子查询;
子查询SELECT子句目标列必须与INTO子句匹配
例3.72对每一个系，求学生的平均年龄，并把结果存入数据库
①首先在数据库中建立一个新表，其中一列存放系名，另一列存放相应的学生平均年龄。
CREATE 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;
RDBMS在执行插入语句会自动检查完整性规则（实体完整性、参照完整性
、用户定义的完整性）。
3.5.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;
3.带子查询的修改语句
例3.75将计算机科学系全体学生的成绩置零
UPDATE SC
SET Grade=0
WHERE Sno IN(SELECT Sno
FROM Student
WHERE Sdept='CS');
3.5.3删除数据
格式
DELETE
FROM <表名>
[WHERE <条件>];
省略WHERE子句表示要删除表中的全部元组，表的定义仍在字典中。
1.删除某一个元组的值
例3.76删除学号为201215128的学生记录。
DELETE
FROM Student
WHERE Sno='201215128';
2.删除多个元组的值
例3.77删除所有的学生选课记录。
DELETE
FROM SC
3.带子查询的删除语句
子查询同样也可以嵌套在DELECT语句中，用以构造执行操作
例3.76删除学号为201215128的学生记录。
DELETE
FROM SC
WHERE Sno IN(SELECT Sno
FROM Student
WHERE Sdept='CS');
3.6空值的处理
空值就是**“不知道”或“不存在”或“无意义”**的值。
SQL语言中允许某些元组的某些属性在一定情况下取空值，一般有以下几种情况：
①该属性应该有一个值，但目前不知道它的具体值。
②该属性不应该有值。如缺考学生成绩为空。
③由于某种原因不便于填写。如一个人电话号码不想让大家知道。
1.空值的产生
例 3.79向SC表中插入一个元组，学生号是”201215128”，课程号是”1”，成绩为空。
INSERT
INTO SC
VALUES ('201215128',1,NULL);
例3.80将Student表中学生号为”201215128”的学生所属的系改为空值。
UPDATE Student
SET Sdept=NULL
WHERE Sno='201215128';
2.空值的判断
判断一个属性的值是否为空值，用IS NULL或IS NOT NULL来表示。
例 3.81从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.空值的算术运算
例3.82找出选修1号课程的不及格的学生。
SELECT Sno
FROM SC
WHERE Grade<60 AND Cno='1';
查询结果不包括缺考的学生，因为他们的Grade值为null。
例 3.83选出选修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)
3.7 视图
视图的特点
①虚表，是从一个或几个基本表（或视图）导出的表
②只存放视图的定义，不存放视图对应的数据
③基表中的数据发生变化，从视图中查询出的数据也随之改变
3.7.1 定义视图
1.建立视图
格式
CREATE VIEW
<视图名> [(<列名> [,<列名>]…)]
AS <子查询>
[WITH CHECK OPTION];
WITH CHECK OPTION
对视图进行更新、插入或删除操作时自动添加子查询中的条件。
组成视图的属性列名：全部省略或全部指定
全部省略: 由子查询中SELECT目标列中的诸字段组成
全部指定:（明确指定所有列名）
①某个目标列是聚集函数或列表达式
②多表连接时选出了几个同名列作为视图的字段
③需要在视图中为某个列启用新的更合适的名字
关系数据库管理系统执行CREATE VIEW语句时只是把视图定义存入数据字典，并不执行其中的SELECT语句。
在对视图查询时，按视图的定义从基本表中将数据查出。
例3.84 建立信息系学生的视图。
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS'
例3.85建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生 。
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS'
WITH CHECK OPTION;
因为带有WITH CHECK OPTION子句，对该视图进行插入、修改和删除操作时，RDBMS会自动加上Sdept='IS’的条件
若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，我们称这类视图为行列子集视图。
IS_Student视图就是一个行列子集视图。
视图不仅可以建立在单个基本表，也可以建立在多个基本表上。
例3.86建立信息系选修了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';
视图不仅可以建在一个或多个基本表上，也可以建立在一个或多个已定义的视图上。
例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,2014-Sage
FROM Student;
--SELECT * FROM BT_S
例3.89将学生的学号及平均成绩定义为一个视图
CREATE VIEW S_G(Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;
--SELECT * FROM S_G
例3.90将Student表中所有女生记录定义为一个视图
CREATE VIEW F_Student(F_sno,name,sex,age,dept)
AS
SELECT *
FROM Student
WHERE Ssex='女';
--SELECT * FROM F_Student
该视图F_Student是由SELECT 建立的，F_Student视图的属性列与Student表的属性列一一对应。若以后修改了基本表Student的结构，则Student表与F_Student视图映像关系就会破坏*。故工作中若修改基本表之后会删除由其导出的视图。
2.删除视图
格式：
DROP VIEW <视图名>[CASCADE];
该语句从数据字典中删除指定的视图定义
如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除
删除基表时，由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除
例3.91删除视图BT_S和IS_S1
DROP VIEW BT_S; --成功执行
DROP VIEW IS_S1; --拒绝执行
要删除CS_S1，需使用级联删除CASCADE，同时删除CS_S2
DROP VIEW CS_S1 CASCADE;
不过在SQL Server中是可以先单独删除CS_S1的
3.7.2 查询视图
用户角度：查询视图与查询基本表相同
RDBMS实现视图查询的方法：视图消解法（View Resolution）
①进行有效性检查
②转换成等价的对基本表的查询
③执行修正后的查询
例3.92在信息系学生的视图中找出年龄小于20岁的学生。
SELECT Sno,Sage
FROM IS_Student
WHERE Sage<=20
视图消解转换后的查询语句为：
SELECT Sno,Sage
FROM Student
WHERE Sdept='IS'AND Sage<=20
例3.93查询选修了1号课程的学生
SELECT IS_Student.Sno,Sname
FROM IS_Student,SC
WHERE IS_Student.Sno=SC.Sno AND SC.Cno='1';
视图消解法的局限
有些情况下，视图消解法不能生成正确的查询。
例3.94在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 --WHERE子句是不能用聚集函数作表达式的
GROUP BY Sno;
正确：
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90;
目前多数关系数据库系统对行列子图均能进行正确转换，但对非行列子集视图的查询就不一定做转换了。
例3.94可以用如下SQL语句完成
SELECT *
FROM (SELECT Sno,AVG(Grade) --子查询生成一个派生表
FROM SC
GROUP BY Sno) AS S_G(Sno,Gavg)
WHERE Gavg>=90;
3.7.3 更新视图
更新视图是指插入、删除、修改数据。
对视图的更新最重要转换为对基本表的更新。为防止用户对不属于视图范围内的基本表数据进行操作，可在定义视图时加上WITH CHENCK OPYION子句。
例3.95将信息系学生视图IS_Student中学号”201215122”的学生姓名改为”刘辰”。
UPDATE IS_Student
SET Sname='刘辰'
WHERE Sno='201215122'
转换后语句
UPDATE Student
SET Sname='刘辰'
WHERE Sno='201215122'AND Sdept='IS'
例3.96向信息系学生视图IS_Student中插入一个新的学生记录，其中学号为”201215129”，姓名为”赵新”，年龄为20岁
IS_Student不能
INSERT
INTO IS_Student
VALUES('201215129','赵新',20);
转换为对基本表的更新：
INSERT
INTO Student(Sno,Sname,Sage,Sdept)
VALUES('201215129','赵新',20,'IS');
报错原因
SQL SERVER 之 with check option 功能
例3.97删除信息系学生视图IS_Student中学号为”201215129”的记录
DELETE
FROM IS_Student
WHERE Sno= ' 201215129 ';
转换为对基本表的更新：
DELETE
FROM Student
WHERE Sno= ' 201215129 ' AND Sdept= 'IS';
3.7.4 视图的作用
①简化用户的操作
②使用户能以多种角度看待同一数据
③对重构数据库提供了一定程度的逻辑独立性
④对机密数据提供安全保护
⑤适当的利用视图可以更清晰的表达查询