集合查询
- SELECT语句的查找结果是元组的集合，所以多个SELECT语句的结果可进行集合操作。集合操作主要包括并操作UNION、交操作INTERSECT和查操作EXCEPT。
- 注意：参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同。
例1：查询计算机科学系的学生及年龄不大于19岁的学生
SELECT *
FROM Student
WHERE Sdept=‘CS’
UNION
SELECT *
FROM Student
WHERE Sage<=19;
基于派生表的查询
- 子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成的临时派生表(derived table)成为主查询的查询对象
- 需要说明的是，通过FROM子句生成派生表时，AS关键字可以省略，但必须为派生关系指定一个别名。而对于基本表，别名是可选择项。
SELECT语句的一般格式
- SELECT语句的一般格式为：
目标列表达式的可选格式
1.*
2.<表名>.*
3.COUNT([DISTINCT|ALL]*)
4.[<表名>.]<属性列名表达式>[,[<表名>.]<属性列名表达式>]…
- 其中，[<表名>]<属性列名表达式>可以是由属性列、作用于属性列的聚集函数和常量的任意算述运算（，-，*，/)组成的运算公式。
聚集函数的一般格式
WHERE子句的条件表达式的可选格式
3.
4. <属性列名> [NOT] LIKE<匹配串>
5. <属性列名> IS [NOT] NULL
6. [NOT] EXISTS(SELECT语句)
7.
数据更新
- 数据更新操作有三种：向表中添加若干行数据、修改表中的数据和删除表中的若干行数据
插入数据
- SQL的数据插入语句INSERT通常有两种形式，一种是插入一个元组，另一种是插入子查询结果。后者可以一次插入多个元组。
插入元组
插入元组的INSERT语句的格式为：
INSERT
INTO<表名> [(<属性列1>[,<属性列2>]…)]
VALUES(<常量1>[,<常量2>]…);
- 其功能是将元组插入指定表中。其中新元组的属性列1的值为常量1，属性列2的值为常量2，…。INTO子句中没有出现的属性列，新元组在这些列上将取空值。但必须注意的是，在表定义时说明了NOT NULL的属性列不能取空值，否则会出错
- 如果INTO子句中没有指明任何属性列名，则新插入的元组必须在每个属性列上均有值
例2：将一个新学生元组插入到Student表中
INSERT
INTO Student(Sno,Sname,Ssex,Sdept,Sage)
VALUES(‘201215128’,‘陈冬’,‘男’,‘IS’,18);
- 在INTO子句中指出了表名Student，并指出了新增的元组在哪些属性上要赋值，属性的顺序可以与CREATE TABLE中的顺序不一样。VALUES子句对新元组的各属性赋值，字符串常数要用单引号(英文字符)括起来
- 如果在INTO子句中指出了表名，没有指出属性名。这表示新元组要在表的所有属性列上都指定值，属性列的次序与CREATE TABLE中的次序相同。
例3：插入一条选课记录
INSERT
INTO SC(Sno,Cno)
VALUES(‘201215126’,‘1’);
- 关系数据库管理系统将在新插入记录的Grade列上自动地赋空值
或者：
INSERT
INTO SC
VALUES(‘201215128’,‘1’,NULL);
- 因为没有指出SC的属性名，在Grade列上要明确给出空值
插入子查询结果
- 子查询不仅可以嵌套在SELECT语句中用以构造父查询的条件，也可以嵌套在INSET语句中以生成要插入的批量数据
插入子查询结果的INSERT语句格式为：
SELECT
INTO<表名> (<属性列1>[,<属性列2>])]
子查询;
例4：对每一个系，求学生的平均年龄，并把结果存入数据库
首先在数据库中建立一个新表，其中一列存放系名，另一列存放相应的学生平均年龄
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;
修改数据
修改操作又称为更新操作，其语句的一般格式为：
UPDATE<表名>
SET<列名>=<表达式> [,<列名>=<表达式>]…
[WHERE<条件>];
- 其功能是修改指定表中满足WHERE子句条件的元组。其中SET子句给出<表达式>的值用于取代相应的属性列值。如果省略WHERE子句，则表示要修改表中的所有元组。
- 修改某一个元组的值
例5：将学生201215121的年龄改为22岁
UPDATE Student
SET Sage=22
WHERE Sno=‘201215121’;
- 修改多个元组的值
例6：将所有学生的年龄增加1岁
UPDATE Student
SET Sage=Sage+1;
- 带子查询的修改语句
- 子查询也可以嵌套在UPDATE语句中，用以构造修改的条件
删除数据
删除语句的一般格式为：
DELETE
FROM<表名>
[WHERE<条件>];
- DELETE语句的功能是从指定表中删除满足WHERE子句条件的所有元组。如果省略WHERE子句则表示删除表中全部元组，但表的定义仍在字典中。也就是说，DELETE语句删除的是表中的数据，而不是关于表的定义
删除某一个元组的值
例8：删除学号为201215128的学生记录
DELETE
FROM Student
WHERE Sno=‘201215128’;
删除多个元组的值
例9：删除所有的学生选课记录
DELETE
FROM SC;
- 这条DELETE语句将使SC成为空表，它删除了SC的所有元组
带子查询的删除语句
- 子查询同样也可以嵌套在DELETE语句中，用以构造执行删除操作的条件
空值的处理
- 所谓空值就是"不知道"或"不存在"或"无意义"的值
- SQL语言中允许某些元组的某些属性在一定情况下取空值。一般有以下几种情况：
- 该属性应该有一个值，但目前不知道它的具体值
- 该属性不应该有值
- 由于某种原因不便于填写
空值的产生
例11：向SC表中插入一个元组，学生号是"201215126"，课程号是"1"，成绩为空
INSERT INTO SC(Sno,Cno,Grade)
\;\;\;\;\;\;\;\;\;\;\;\; VALUE(‘201215126’,‘1’,NULL);
- 在插入时该学生还没有考试成绩，取空值
或
INSERT INTO SC(Sno,Cno)
\;\;\;\;\;\;\;\;\;\;\;\; VALUES(‘201215126’,‘1’);- 在插入语句中没有赋值的属性，其值为空值
空值的判断
- 判断一个属性的值是否为空值，用IS NULL或IS NOT NULL来表示
例12：从Student表中找出遗漏了数据的学生信息
SELECT *
FROM Student
WHERE Sname IS NULL OR Ssex IS NULL OR Sage IS NULL OR Sdept IS NULL;
空值的约束条件
- 属性定义(或者域定义)中有NOT NULL约束条件的不能取空值，码属性不能取空值
空值的算术运算、比较运算和逻辑运算
- 空值与另一个值(包括另一个空值)的算数运算的结果为空值，空值与另一个值(包括另一个空值)的比较运算符的结果为UNKNOWN。有了UNKNOWN后，传统的逻辑运算中二值(TRUE、FALSE)逻辑就扩展成了三值逻辑。AND、OR、NOT的真值表如下：
| x y | x AND y | x OR y | NOT x |
|---|---|---|---|
| T T | T | T | F |
| T U | U | T | F |
| T F | F | T | F |
| U T | U | T | U |
| U U | U | U | U |
| U F | F | U | U |
| F T | F | T | T |
| F U | F | U | T |
| F F | F | F | T |
- T表示TRUE，F表示FALSE，U表示UNKNOWN
- 在查询语句中，只有使WHERE和HAVING子句中的选择条件为TRUE的元组才被选出作为输出结果
例13：选出选修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);
视图
- 视图是从一个或几个基本表(或视图)导出的表。它与基本表不同，是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。所以一旦基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。从这个意义上讲，视图就像一个窗口，透过它可以看到数据库中自己感兴趣的数据及其变化。
- 视图一经定义，就可以和基本表一样被查询、被删除。也可以在一个视图之上再定义新的视图，但对视图的更新(增、删、改)操作则有一定的限制
定义视图
建立视图
SQL语言用CREATE VIEW命令建立视图，其一般格式为：
CREATE VIEW <视图名> [(<列名>[,<列名>]…)]
AS <子查询>
[WITH CHECK OPTION];
- 其中，子查询可以是任意的SELECT语句，是否可以含有ORDER BY子句和DISTINCT短语，则取决于具体系统的实现。
- WITH CHECK OPTION表示对视图进行UPDATE、INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件(即子查询的条件表达式)
- 组成视图的属性列名或者全部省略或者全部指定，没有第三种选择。如果省略了视图的各个属性列名，则隐含该视图由子查询中SELECT子句目标列中的诸字段组成。但在下列三种情况下必须明确指定组成视图的所有列名：
- 某个目标列不是单纯的属性名，而是聚集函数或列表达式
- 多表连接时选出来几个同名列作为视图的字段
- 需要在视图中为某个列启用新的更合适的名字
例14：建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept=‘IS’
WITH CHECK OPTION；
- 由于在定义IS_Student视图时加上了WITH CHECK OPTION子句，以后对该视图进行插入、修改和删除操作时，关系数据库管理系统会自动加上Sdept='IS’的条件
- 若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，则称这类视图为行列子集视图
例15：将学生的学号及平均成绩定义为一个视图
CREATE VIEW S_G(Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;
- 由于AS子句中SELECT语句的目标列平均成绩是通过作用聚集函数得到的，所以CREATE VIEW中必须明确定义组成S_G视图的各个属性列名。
- 用带有聚集函数和GROUP BY子句的查询来定义视图，这种视图称为分组视图
删除视图
该语句的格式为：DROP VIEW <视图名> [CASCADE];
- 视图删除后视图的定义将从数据字典中删除。如果该视图上还导出了其他视图，则使用CASCADE级联删除语句把该视图和由它导出的所有试图一起删除
- 基本表删除后，由该基本表导出的所有视图均无法使用了，但是视图的定义没有从字典中清除。删除这些视图定义需要显示地使用DROP VIEW语句。
查询视图
- 视图定义后，用户就可以像对基本表一样对视图进行查询了
例16：在信息系学生的视图中找出年龄小于20岁的学生
SELECT Sno,Sage
FROM IS_Student
WHERE Sage<20;
- 关系数据库管理系统执行对视图的查询时，首先进行有效性检查，检查查询中涉及的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询。这一转换过程称为视图消解(view resolution)。
更新视图
- 更新视图是指通过视图来插入(INSERT)、删除(DELETE)和修改(UPDATE)数据
- 由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新。像查询视图那样，对视图的更新操作也是通过视图消解，转换为对基本表的更新操作。
- 为防止用户通过视图对数据进行增加、删除、修改时，有意无意地对不属于视图范围内的基本表数据进行操作，可在定义视图时加上WITH CHECK OPTION子句。这样在视图上增、删、改数据时，关系数据库管理系统会检查视图定义中的条件，若不满足条件则拒绝执行该操作。
例17：将信息系学生视图IS_Student中学号为’201215122’的学生姓名改为"刘辰"
UPDATE IS_Student
SET Sname=‘刘辰’
WHERE Sno=‘201215122’;
视图的作用
- 视图能够简化用户的操作
- 视图使用户能以多种角度看待同一数据
- 视图对重构数据库提供了一定程度的逻辑独立性
- 视图能够对机密数据提供安全保护
- 适当利用视图可以更清晰地表达查询
3886
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
