SQL Server 数据库实验课第七周——数据查询、数据更新、空值处理、视图

本节课内容数据查询、数据更新、空值处理、视图

目录

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

SQL Server 数据库实验课第七周——数据查询、数据更新、空值处理、视图
使用UNION将多个查询结果合并起来时,系统自动去掉重复元组。若想保留重复元组则用UNION ALL操作符
例 3.65询选修了课程1或者选修了课程2的学生。

SELECT Sno
FROM SC
WHERE Cno='1'
UNION
SELECT Sno
FROM SC
WHERE Cno='2';

SQL Server 数据库实验课第七周——数据查询、数据更新、空值处理、视图

2.INTERSECT 交

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

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

SQL Server 数据库实验课第七周——数据查询、数据更新、空值处理、视图

实际上就是查询计算机科学系中年龄不大于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';

SQL Server 数据库实验课第七周——数据查询、数据更新、空值处理、视图
或嵌套查询

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

SQL Server 数据库实验课第七周——数据查询、数据更新、空值处理、视图
如果子查询中没有聚集函数,派生表可以不指定属性列
子查询SELECT子句后面的列名为其缺省属性
查询所有选修了1号课程的学生姓名,可以用如下查询完成:

SELECT Sname
FROM Student,(SELECT Sno FROM SC GROUP BY Sno)
	AS SC1
WHERE Student.Sno=SC1.Sno

SQL Server 数据库实验课第七周——数据查询、数据更新、空值处理、视图

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;

SQL Server 数据库实验课第七周——数据查询、数据更新、空值处理、视图
RDBMS在执行插入语句会自动检查完整性规则(实体完整性、参照完整性
、用户定义的完整性)。

3.5.2修改数据

语句格式

UPDATE  <表名>
SET  <列名>=<表达式>[,<列名>=<表达式>]…
[WHERE <条件>];

修改指定表中满足WHERE子句条件的元组。SET子句给出<表达式>的值用于取代相应的属性列值。如果省略WHERE子句,表示要修改表中的所有元组。
三种修改方式
1.修改某一个元组的值
例3.73将学生201215121的年龄改为22岁

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

SQL Server 数据库实验课第七周——数据查询、数据更新、空值处理、视图
2.修改多个元组的值
例3.74将所有学生的年龄增加1岁。

UPDATE Student
SET Sage=Sage+1;

SQL Server 数据库实验课第七周——数据查询、数据更新、空值处理、视图
3.带子查询的修改语句
例3.75将计算机科学系全体学生的成绩置零

UPDATE SC
SET Grade=0
WHERE Sno IN(SELECT Sno
			FROM Student
			WHERE Sdept='CS');

SQL Server 数据库实验课第七周——数据查询、数据更新、空值处理、视图

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

SQL Server 数据库实验课第七周——数据查询、数据更新、空值处理、视图

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

UPDATE Student
SET Sdept=NULL
WHERE Sno='201215128';

SQL Server 数据库实验课第七周——数据查询、数据更新、空值处理、视图

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'

SQL Server 数据库实验课第七周——数据查询、数据更新、空值处理、视图
例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';

SQL Server 数据库实验课第七周——数据查询、数据更新、空值处理、视图
视图不仅可以建在一个或多个基本表上,也可以建立在一个或多个已定义的视图上。
例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

SQL Server 数据库实验课第七周——数据查询、数据更新、空值处理、视图
例3.89将学生的学号及平均成绩定义为一个视图

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

SQL Server 数据库实验课第七周——数据查询、数据更新、空值处理、视图
例3.90将Student表中所有女生记录定义为一个视图

CREATE VIEW F_Student(F_sno,name,sex,age,dept)
AS
SELECT *
FROM Student
WHERE Ssex='女';
--SELECT * FROM F_Student

SQL Server 数据库实验课第七周——数据查询、数据更新、空值处理、视图
该视图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的
SQL Server 数据库实验课第七周——数据查询、数据更新、空值处理、视图

3.7.2 查询视图

用户角度:查询视图与查询基本表相同
RDBMS实现视图查询的方法:视图消解法(View Resolution)
①进行有效性检查
②转换成等价的对基本表的查询
③执行修正后的查询
例3.92在信息系学生的视图中找出年龄小于20岁的学生。

SELECT Sno,Sage
FROM IS_Student
WHERE Sage<=20

SQL Server 数据库实验课第七周——数据查询、数据更新、空值处理、视图

视图消解转换后的查询语句为:

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 数据库实验课第七周——数据查询、数据更新、空值处理、视图
报错原因
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 视图的作用

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

上一篇:视图、存储过程、触发器、函数、索引、游标


下一篇:SQL 数据查询(5)—— 集合查询、基于派生表的查询、数据更新、视图