2、掌握SQL中各种查询方法和数据更新方法 3、掌握SQL中视图的定义方法和用法 4、掌握SQL的授权机制 本章要求： 1、掌握SQL定义基本表和建立索引的方法 2、掌握SQL中各种查询方法和数据更新方法 3、掌握SQL中视图的定义方法和用法 4、掌握SQL的授权机制 5、了解嵌入式SQL的基本使用方法 本章内容： 请选择内容 §1 SQL概述 §2 SQL数据定义功能 §3 SQL数据操纵功能 §4 视图 返回 §5 SQL数据控制功能 §6 嵌入式SQL 2018/11/19 数据库系统

§1 SQL概述 一、SQL 的发展 SQL是 Structured Query Language的缩写 （ANSI解释为Standard Query Language） 74年 Boyce &Chambarlin提出，在IBM的System R上 首先实现 79年 Oracle 82年 IBM的DB2 84年 Sybase 采用SQL作为数据库语言 2018/11/19 数据库系统

87年 国际标准化组织（ISO）采纳为国际标准 89年 ISO推出SQL89 92年 ISO推出SQL2 目前正制定SQL3标准 86年10月 成为美国国家标准 87年 国际标准化组织（ISO）采纳为国际标准 89年 ISO推出SQL89 92年 ISO推出SQL2 目前正制定SQL3标准 二、SQL的主要特点 1、一体化：两方面 集DDL、DML、DCL为一体 实体和联系都是关系，因此每种操作只需一种操作符 2018/11/19 数据库系统

2、高度非过程化语言 （WHAT  HOW  ） 3、面向集合的操作方式（一次一集合） 4、交互式和嵌入式两种使用方式，统一的语法结构 5、语言简洁，易学易用 完成核心功能只有9个动词： 数据查询： SELECT 数据定义： CREATE，DROP，ALTER 数据操纵： INSERT，DELETE，UPDATE 数据控制： GRANT，REVOKE 6、支持三级模式结构 视图  外模式 基本表（的集合）  模式 存储文件和索引  内模式 2018/11/19 数据库系统

SQL支持的三级模式结构 用户 SQL View V1 View V2 Base table B1 B2 B3 B4 Stored file 外模式 模式 内模式 2018/11/19 数据库系统

 基本表是独立存在的表。一个关系对应一个表。一个（或多个）表对应一个存储文件，每个表可有若干索引，这些索引也可放在存储文件中。 说明：  基本表是独立存在的表。一个关系对应一个表。一个（或多个）表对应一个存储文件，每个表可有若干索引，这些索引也可放在存储文件中。  视图是从一个或几个基本表中导出的表，概念上同基本表。但它并不真正存储数据，也不独立存在，它依赖于导出它的基本表，数据也存放在原来的基本表中。  对内模式，只需定义索引，其余的一切均有DBMS自动完成 本节开头 本章开头 下一节 2018/11/19 数据库系统

§2 SQL数据定义功能 三部分：  定义和修改基本表（定义模式）：CREATE TABLE DROP TABLE ALTER TABLE  定义视图（定义外模式）：CREATE VIEW DROP VIEW  定义索引（定义内模式）： CREATE INDEX DROP INDEX 说明：视图是从基本表导出的虚表，索引依赖于基本表，SQL没有修改视图和索引的操作，可通过先删除，再创建达此目的。 2018/11/19 数据库系统

CREATE TABLE 表名（列名1 类型 [列级完整性约束] [，列名2 类型 [列级完整性约束]…）； 一、基本表的定义和修改 1、定义：基本格式为 CREATE TABLE 表名（列名1 类型 [列级完整性约束] [，列名2 类型 [列级完整性约束]…）； 示例 CREATE TABLE S（ S# CHAR（3） NOT NULL UNIQUE， SN CHAR（15）， SD CHAR（15）， SA SMALLINT）； 不允许取空值 取值唯一 说明：  注意SQL语句的书写格式  SQL支持空值的概念。允许空值的列未输入数据时系统自动置为空值。  SQL支持的数据类型随系统不同而有所差异。 2018/11/19 数据库系统

ALTER TABLE 表名 ADD 列名 类型 [完整性约束]； （2）修改列 ALTER TABLE 表名 MODIFY 列名 类型； 2、修改基本表 （1）增加列： ALTER TABLE 表名 ADD 列名 类型 [完整性约束]； （2）修改列 ALTER TABLE 表名 MODIFY 列名 类型； （3）删除完整性约束 ALTER TABLE 表名 DROP 完整性约束名； 不支持NOT NULL选择 如 ALTER TABLE S ADD SD INT； 如 ALTER TABLE S MODIFY SD CHAR（20） 注意：不能删除列，新增列的值一律为空值， 可增加列宽，但一般不能减小列宽，修改可能会破坏已有数据。 在定义基本表时要考虑充分 2018/11/19 数据库系统

注意：删除基本表时，表中的数据、建立在表上的索引和视图将一并被删除，因此应格外小心。 3、删除： DROP TABLE 表名； 注意：删除基本表时，表中的数据、建立在表上的索引和视图将一并被删除，因此应格外小心。 二、索引的建立和删除 由DBA或表的属主进行，存取数据时由系统自动选取合适的索引作为存取路径，用户不必也不能选择索引。 2018/11/19 数据库系统

CREATE [ UNIQUE ] [CLUSTER] INDEX 索引名 ON 表名（列名 一个索引项值仅对应唯一的数据记录 1、建立 CREATE [ UNIQUE ] [CLUSTER] INDEX 索引名 ON 表名（列名 一个索引项值仅对应唯一的数据记录 改变记录的物理顺序使之与索引项值的排列顺序相同，称为聚簇索引。显然一个表只能建立一个聚簇索引。可通过在经常查询而改动小的表上建立这种索引来提高查询效率。 ASC DESC ASC DESC [，列名 ]…）； 升序或降序 缺省为升序 如 CREATE UNIQUE INDEX XSNO ON S（S#）； CREATE UNIQUE INDEX SCNO ON SC（SNO ASC，CNO DESC）； 2、删除 DROP INDEX 索引名； 2018/11/19 数据库系统

查询是数据库的核心操作。SQL仅提供了唯一的语句SELECT，其使用方式灵活，功能非常丰富。 1、一般格式 相当于投影 细节见P115 全部字段 SELECT [ALL | DISTINCT] * | 目标列 FROM 基本表（或视图） [ WHERE 条件表达式 ] [ GROUP BY 列名1 [ HAVING 内部函数表达式 ] ] [ ORDER BY 列名2 ]； 被查询的关系 相当于选择或连接 分组 统计 满足条件的组才输出 ASC DESC 对查询结果排序 2018/11/19 数据库系统

SELECT S#，‘Birthday：’，2000-SA FROM S WHERE SD=‘CS’； 2、简单查询 例1：求选修了课程的学生学号 从结果中去掉重复的元组 SELECT DISTINCT S# FROM SC； 例2：SELECT的后面可以是表达式。 如求计算机系学生的学号和出生年份： SELECT S#，‘Birthday：’，2000-SA FROM S WHERE SD=‘CS’； 例3：连续范围查询，使用BETWEEN （NOT BETWEEN） SELECT S#，SA FROM S WHERE SA BETWEEN 20 AND 22； 即求20到22岁之间 的学生学号和年龄 2018/11/19 数据库系统

例4：离散范围查询，使用 IN （NOT IN） SELECT * FROM S WHERE SD IN （‘MA’，‘CS’）； 星号表示无投影 相当于若干 ‘OR’的缩写 SD=‘MA’ OR SD=‘CS’ 2018/11/19 数据库系统

SELECT S#，‘Birthday：’，1998-SA FROM S WHERE SD=‘CS’； 2、简单查询 例1：求选修了课程的学生学号 从结果中去掉重复的元组 SELECT DISTINCT S# FROM SC； 例2：SELECT的后面可以是表达式。 如求计算机系学生的学号和出生年份： SELECT S#，‘Birthday：’，1998-SA FROM S WHERE SD=‘CS’； 例3：连续范围查询，使用BETWEEN （NOT BETWEEN） SELECT S#，SA FROM S WHERE SA BETWEEN 20 AND 22； SA>=20 AND SA<=22 相当于若干 ‘AND’ 的缩写 2018/11/19 数据库系统

例4：离散范围查询，使用 IN （NOT IN） SELECT * FROM S WHERE SD IN （‘MA’，‘CS’）； 相当与若干 ‘OR’的缩写 例5：模糊查询，使用 LIKE （NOT LIKE） SELECT * FROM S WHERE SN LIKE ‘%清%’； 查姓名中有‘清’字的学生 DB2中，下划线 ‘_’表示匹配任何单个字符 百分号‘%’表示匹配任何字符串 其它语言中, 常用 ‘?’ 常用 ‘*’ 例6: 涉及空值的查询 , IS NULL （IS NOT NULL） SELECT S#, C# FROM SC WHERE G IS NULL ; 2018/11/19 数据库系统

SQL中没有专门的JOIN命令，而是靠SELECT语句中的 WHERE子句来达到连接运算的目的，因此更加灵活、简便。 3、连接查询：涉及至少两个表的查询 SQL中没有专门的JOIN命令，而是靠SELECT语句中的 WHERE子句来达到连接运算的目的，因此更加灵活、简便。 用来连接两个表的条件称为连接条件或连接谓词。 连接条件的一般格式为： [表名1.]列名 1 比较运算符 [表名2.]列名2 [表名1.]列名1 BETWEEN [表名2.]列名2 AND [表名2.]列名3 比较运算符主要有：=、> 、< 、>=、<=、!=。 等值连接：运算符为“=”时。 非等值连接：运算符不是“=”时。 自然连接：等值连接且目标列不含重复属性。 连接操作的实现过程：见教材P102中部。 2018/11/19 数据库系统

例1：简单的连接查询 S : S# SN SD SA SC:S# C# G 求选修C1课程的学生学号、 姓名和成绩 SELECT S.S#, SN, G FROM S, SC WHERE S.S#=SC.S# AND SC.C#=‘C1’; 连接字段 01 A MA 20 02 B CS 19 03 C IS 21 04 D MA 19 05 E MA 20 01 C1 A 01 C2 A 02 C2 B 02 C3 C 03 C3 B 04 C1 B 04 C4 A 连接条件 或称连接谓词 表名前缀 (字段名 唯一时可省略) 2018/11/19 数据库系统

在多个表中出现的列名，必须用表名限定，仅在一个表中出现的属性，可省略表名。 查询结果：S# SN G 01 A A 例1：简单的连接查询 S : S# SN SD SA SC:S# C# G 求选修C1课程的学生学号、 姓名和成绩 SELECT S.S#, SN, G FROM S, SC WHERE S.S#=SC.S# AND SC.C#=‘C1’; 01 A MA 20 02 B CS 19 03 C IS 21 04 D MA 19 05 E MA 20 01 C1 A 01 C2 A 02 C2 B 02 C3 C 03 C3 B 04 C1 B 04 C4 A 条件满足 条件不满足 条件不满足 在多个表中出现的列名，必须用表名限定，仅在一个表中出现的属性，可省略表名。 查询结果：S# SN G 01 A A 2018/11/19 数据库系统

S： S# SN SD SA C： C# CN PC# SC： 例2：多表连接 求学生学号、姓名、 选修课程名、成绩 直观的查找过程 S# C# G 例2：多表连接 求学生学号、姓名、 选修课程名、成绩 S： S# SN SD SA S1 C1 A S1 C2 A S1 C3 A S1 C5 B S2 C1 B S2 C2 C S2 C4 C S3 C2 B S3 C3 C S3 C4 B S4 C3 B S4 C5 D S5 C2 C S5 C3 B S5 C5 B S6 C4 A S6 C5 A S1 A CS 20 S2 B CS 21 S3 C MA 19 S4 D CI 19 S5 E MA 20 S6 F CS 22 直观的查找过程 C： C# CN PC# C1 G C2 H C1 C3 I C1 C4 J C2 C5 K C4 2018/11/19 数据库系统

S： S# SN SD SA C： C# CN PC# SC： 例2：多表连接 求学生学号、姓名、 选修课程名、成绩 S# C# G 例2：多表连接 求学生学号、姓名、 选修课程名、成绩 S： S# SN SD SA S1 C1 A S1 C2 A S1 C3 A S1 C5 B S2 C1 B S2 C2 C S2 C4 C S3 C2 B S3 C3 C S3 C4 B S4 C3 B S4 C5 D S5 C2 C S5 C3 B S5 C5 B S6 C4 A S6 C5 A S1 A CS 20 S2 B CS 21 S3 C MA 19 S4 D CI 19 S5 E MA 20 S6 F CS 22 SELECT S.S#，SN，CN，G FROM S，C，SC WHERE S.S#=SC.S# AND SC.S#=C.C#; C： C# CN PC# C1 G C2 H C1 C3 I C1 C4 J C2 C5 K C4 2018/11/19 数据库系统

SELECT FIRST.C#, SECOND.PC# FROM C FIRST, C SECOND 例3: 单表连接 求每一门课程的 间接先行课 C： C# CN PC# C1 G C2 H C1 C3 I C1 C4 J C2 C5 K C4 C: C# CN PC# C1 G C2 H C1 C3 I C1 C4 J C2 C5 K C4 直观的查找过程 FIRST SECOND SQL语句为: 查询结果为: C# PC# SELECT FIRST.C#, SECOND.PC# FROM C FIRST, C SECOND WHERE FIRST.PC# = SECOND.C# ; C2 C3 别名引入 C4 C1 C5 C2 2018/11/19 数据库系统

S:S# SN SD SA S1 A CS 20 S2 B CS 21 S3 C MA 19 S4 D CI 19 SC:S# C# G S1 C1 A S1 C2 A S2 C1 B S2 C2 C S2 C4 C 例4：外连接查询 求每个学生选修的课程及成绩。 不使用外连接时 使用外连接时 SQL语句为: 查询结果为: S# SN C# G SELECT S.S#, SN, C#, G FROM S, SC WHERE S.S# = SC.S# ; S1 A C1 A S1 A C2 A S2 B C1 B S2 B C2 C S2 B C4 C (*); 有些数据库系统用+ 有些将*放在=前后，*=、=* S3 C S4 D 2018/11/19 数据库系统

分析：可用连接来实现，但最后结果只包含S中的字段，应该考虑更为有效、 直观的方法： 分析：可用连接来实现， SC： S# C# G 4、嵌套查询（子查询） SELECT-FROM-WHERE 查询块嵌入另一个查询块中 S1 C1 A S1 C2 A S1 C3 A S1 C5 B S2 C1 B S2 C2 C S2 C4 C S3 C2 B S3 C3 C S3 C4 B S4 C3 B S4 C5 D S5 C2 C S5 C3 B S5 C5 B S6 C4 A S6 C5 A S：S# SN SD SA S1 A CS 20 S2 B CS 21 S3 C MA 19 S4 D CI 19 S5 E MA 20 S6 F CS 22 例1：求选修了课程名为‘J’ 的学生学号和姓名  分析：可用连接来实现，但最后结果只包含S中的字段，应该考虑更为有效、 直观的方法： 分析：可用连接来实现， SELECT S.S#, S.SN FROM S, C, SC WHERE S.S#=SC.S# AND SC.C#=C.C# AND C.CN=“J” ; C： C# CN PC# C1 G C2 H C1 C3 I C1 C4 J C2 C5 K C4   在C中找课程‘J’的编号   在SC中找选修该课的学号  在S中找选修该课的学生姓名 2018/11/19 数据库系统

WHERE S# IN (‘S2’,‘S3’,‘S6’)；  SC： S# C# G  在C中找课程‘J’的编号 S1 C1 A S1 C2 A S1 C3 A S1 C5 B S2 C1 B S2 C2 C S2 C4 C S3 C2 B S3 C3 C S3 C4 B S4 C3 B S4 C5 D S5 C2 C S5 C3 B S5 C5 B S6 C4 A S6 C5 A S：S# SN SD SA SELECT C# FROM C WHERE CN=‘J’； S1 A CS 20 S2 B CS 21 S3 C MA 19 S4 D CI 19 S5 E MA 20 S6 F CS 22   在SC中找选修该课的学号 SELECT S# FROM SC WHERE C# IN （‘C4’）； C： C# CN PC# C1 G C2 H C1 C3 I C1 C4 J C2 C5 K C4  在S中找选修该课的学生姓名  SELECT S#，SN FROM S WHERE S# IN (‘S2’,‘S3’,‘S6’)；  2018/11/19 数据库系统

WHERE S# IN (‘S2’,‘S3’,‘S6’)； ) ); 最后的查询语句:  在C中找课程‘J’的编号 SELECT C# FROM C WHERE CN=‘J’； SELECT S#，SN FROM S WHERE S# IN （‘S2’,‘S3’,‘S6’）；  在SC中找选修该课的学号 ( SELECT S# FROM SC WHERE C# IN (‘C4’)； SELECT S# FROM SC WHERE C# IN （‘C4’）； (  在S中找选修该课的学生姓名 SELECT C# FROM C WHERE CN=‘J’ SELECT S#，SN FROM S WHERE S# IN (‘S2’,‘S3’,‘S6’)； ) ); 2018/11/19 数据库系统

最后的查询语句: 说明： （1）嵌套查询由内向外处理 SELECT S#，SN （2）SQL允许多层嵌套 FROM S （3）嵌套查询中最常用的 谓词是IN （4）嵌套查询层次分明、 容易理解 SELECT S#，SN FROM S WHERE S# IN SELECT S# FROM SC WHERE C# IN SELECT C# FROM C WHERE CN=‘J’ ( ) ); 2018/11/19 数据库系统

例2：求“张三”选修的课程名称及成绩（设无同名学生） SELECT C.CN, SC.G FROM C, SC 带有比较运算符的子查询 当用户确切知道内层查询的结果是单值（只有一个元组，且该元组只有一个字段）时，可将外层查询的某字段与内层查询的结果用>、<、=、>=、<=、!=等比较运算符进行比较。 例2：求“张三”选修的课程名称及成绩（设无同名学生） SELECT C.CN, SC.G FROM C, SC WHERE C.C# = SC.C# AND SC.S# = （SELECT S# FROM S WHERE SN=“张三”）; S(S#, SN, SD,SA) C(C#, CN, PC#) SC(S#, C#, G) 子查询必须出现在比较符之后。 2018/11/19 数据库系统

使用存在量词 EXISTS 和 NOT EXISTS 的查询 例3：求至少一门不及格的学生姓名 SELECT SN FROM S WHERE EXISTS （SELECT * FROM SC WHERE S# = S.S# AND G=‘D’）； 若内层查询结果非空，则为真 否则为假 等价于： SELECT S.SN FROM S, SC WHERE S.S#=SC.S# AND SC.G=‘D’ ; 称为相关子查询： 查询条件依赖于外层 查询中某个值 相当于一个变量，根据它的值处理内层查询, S中有多少个学号，内层查询就进行多少次 2018/11/19 数据库系统

例4：查询没有选修“C1”课程的学生姓名。 SELECT SN SELECT SN FROM S FROM S WHERE NOT EXISTS （SELECT * FROM SC WHERE S# = S.S# AND C#=“C1”); SELECT SN FROM S WHERE S# NOT IN （SELECT S# FROM SC WHERE C#=“C1”); 哪种形式效率高？ 2018/11/19 数据库系统

用除法！ 例5：对全称量词的处理 求选修了全部课程的学生姓名 想一想：用关系代数如何实现？ 带全称量词的谓词 带存在量词的谓词 （x）P（x） （ x（ P（x）） 本例变为：选这样的学生姓名，没有一门课程是他不选修的 分析: 任给一学号S#, 若不存在他不选修的课, 则显示他的姓名  (  C# (学号为S#的学生没修课程C#) )  (  C# (在SC中不存在选课单 (S#, C#,… ) ) ) 2018/11/19 数据库系统

SELECT SN FROM S WHERE NOT EXISTS 为真 ( SELECT * 查询结果为空 FROM C 都为假 ( SELECT * FROM SC WHERE S#=S.S# AND C#=C.C# 即所有的课程 都选修 查询结果都非空 ) ); 2018/11/19 数据库系统

 (  C# (学号为S#的学生没修课程C#) ) ( SELECT * FROM C WHERE NOT EXISTS SELECT SN FROM S WHERE NOT EXISTS 选修全部课课  (  C# (学号为S#的学生没修课程C#) ) ( SELECT * FROM C WHERE NOT EXISTS 在SC中不存在选课单 (S#, C#,… )？ ( SELECT * FROM SC WHERE S#=S.S# AND C#=C.C# 在SC中查选课单 (S#, C#,… ) ) ); 2018/11/19 数据库系统

如: 求这样的学生学号, 该生至少选修了学生S2所修的全部课程 例6 对“蕴涵”的处理 P  Q  P  Q 如: 求这样的学生学号, 该生至少选修了学生S2所修的全部课程 即找这样的学号Sx, 对所有的课程Cy, 若S2选修了Cy, 则Sx也选修了Cy P表示谓词“学生S2选修课程Cy” Q表示谓词“学生Sx选修课程Cy” 查询条件为: (Cy) (PQ)  Cy ( (PQ))  Cy ( (PQ))  Cy (PQ) 2018/11/19 数据库系统

SELECT DISTINCT S# FROM SC SCX WHERE NOT EXISTS ( SELECT * FROM SC SCY WHERE SCY.S# = ‘S2’ AND NOT EXISTS FROM SC SCZ WHERE SCZ.S# = SCX.S# AND SCZ.C# =SCY.C# ) ); 条件  Cy ( P  Q ) 请思考: 直观的查找过程? 2018/11/19 数据库系统

选这样的学号Sx, 不存在S2选修的一门课程Sx未选修 问题: 上例可否改为下面的SQL语句? SELECT S# FROM S WHERE NOT EXISTS ( SELECT * FROM SC SCY WHERE SCY.S# = ‘S2’ AND NOT EXISTS FROM SC SCZ WHERE SCZ.S# = S.S# AND SCZ.C# =SCY.C# ) ); 选这样的学号Sx, 不存在S2选修的一门课程Sx未选修 选这样的学号Sx,S2选修的课程Sx都选修了 2018/11/19 数据库系统

参加UNION操作的各结果表的列数、对应项的数据类型必须相同 查询结果 S# SN S1 A S2 B S6 F S# SN SD SA S1 A CS 17 S2 B CS 21 S3 C MA 19 S4 D CI 17 S5 E MA 20 S6 F CS 20 S9 Z IS 18 S# C# G S1 C1 A S1 C3 A S1 C5 B S2 C1 A S2 C2 C S2 C4 A S3 C3 S3 C4 S4 C3 B S4 C5 D S5 C2 S5 C3 S5 C5 S6 C4 A S： SC： 例7：使用UNION的查询 求C1和C4课程成绩为A的学生的学号及姓名 SELECT S.S#, S.SN FROM S, SC WHERE S.S#=SC.S# AND SC.C#=‘C1’ AND SC.G=‘A’ UNION FROM S，SC SC.C#=‘C4’ AND SC.G=‘A’; 先选择C1课程成绩为A的学号姓名 参加UNION操作的各结果表的列数、对应项的数据类型必须相同 查询结果 S# SN S1 A S2 B S6 F 再选择C4课程成绩为A的学号姓名 2018/11/19 数据库系统

S# SN SD SA S1 A CS 17 S2 B CS 21 S3 C MA 19 S4 D CI 17 S5 E MA 20 S6 F CS 20 S9 Z IS 18 S# C# G S1 C1 A S1 C3 A S1 C5 B S2 C1 B S2 C2 C S2 C4 C S3 C3 0 S3 C4 0 S4 C3 B S4 C5 D S5 C2 0 S5 C3 0 S5 C5 0 S6 C4 A S： SC： 例7：使用UNION的查询 求C1和C4课程成绩为A的学生的学号及姓名 等价于： SELECT S.S#,S.SN FROM S, SC WHERE (S.S#=SC.S# AND SC.C#=‘C1’ AND SC.G=‘A’) OR (S.S#=SC.S# AND SC.C#=‘C4’ AND SC.G=‘A’); DISTINCT S.S#,S.SN 查询结果 S# SN S1 A S2 B S6 F 有重复的行 2018/11/19 数据库系统

SC.G='A' AND (SC.C#='C1'OR SC.C#='C4'); 查询结果 S# SN S1 A S2 B S6 F S# SN SD SA S1 A CS 17 S2 B CS 21 S3 C MA 19 S4 D CI 17 S5 E MA 20 S6 F CS 20 S9 Z IS 18 S# C# G S1 C1 A S1 C3 A S1 C5 B S2 C1 B S2 C2 C S2 C4 C S3 C3 0 S3 C4 0 S4 C3 B S4 C5 D S5 C2 0 S5 C3 0 S5 C5 0 S6 C4 A S： SC： 例7：使用UNION的查询 求C1和C4课程成绩为A的学生的学号及姓名 等价于： SELECT S.S#,S.SN FROM S, SC WHERE (S.S#=SC.S# AND SC.C#=‘C1’ AND SC.G=‘A’) OR (S.S#=SC.S# AND SC.C#=‘C4’ AND SC.G=‘A’); DISTINCT S.S#,S.SN WHERE S.S#=SC.S# AND SC.G='A' AND SC.C# IN ('C1’,'C4'); WHERE S.S#=SC.S# AND SC.G='A' AND (SC.C#='C1'OR SC.C#='C4'); 查询结果 S# SN S1 A S2 B S6 F 2018/11/19 数据库系统

（1）用UNION查询时，每个子查询选择的目标列必须相同， 但所基于的表可以不同 说明： （1）用UNION查询时，每个子查询选择的目标列必须相同， 但所基于的表可以不同 （2）UNION查询实际上是将几个子查询的结果合并在一起 （3）UNION查询时自动去掉重复的行 （4）可以通过将各子查询的限制条件合并而将UNION查询 变成一个单一查询 例：求计算机系及选修C5课程的学生学号和姓名 SELECT S.S#, S.SN FROM S WHERE SD='CS' UNION SELECT S.S#, S.SN FROM S, SC WHERE S.S#=SC.S# AND SC.C#='C5'; 2018/11/19 数据库系统

例9：求计算机系学生与年龄小于20岁的学生的差集。 即求计算机系年龄不小于20岁的学生。 例8：求选修课程C1的学生与选修C2的学生的交集。 即求同时选修了课程C1和C2的学生学号。 SELECT S# FROM SC WHERE C# =‘C1’ AND S# IN （SELECT S# WHERE C# =‘C2’）； SELECT S#，SN FROM S WHERE SD=‘CS’AND SA>=20； 2018/11/19 数据库系统

COUNT 统计一列中的（NOT NULL）值的个数 COUNT（*） 计算记录个数 SUM 对一列求和 AVG 对一列求平均值 5、库函数（聚集函数） COUNT 统计一列中的（NOT NULL）值的个数 COUNT（*） 计算记录个数 SUM 对一列求和 AVG 对一列求平均值 MAX 对一列求最大值 MIN 对一列求最小值 例1：求选修了课程的学生人数 SELECT COUNT（DISTINCT S#） FROM SC； 重复的只记一个 2018/11/19 数据库系统

SELECT SC.C#，C.CN, COUNT（S#） FROM SC, C WHERE SC.C#=C.C# 例2 分组统计：使用GROUP BY 求选修各门课程的学生人数 SELECT C#，COUNT（S#） FROM SC GROUP BY C#； 将表按列的值分组，列 的值相同的分在一组，产生 一个结果行。GROUP BY常 和库函数一起使用，用于分 组统计。 SELECT SC.C#，C.CN, COUNT（S#） FROM SC, C WHERE SC.C#=C.C# GROUP BY SC.C#, CN； 上例中，如果要求显示课程名称，则可以 目标列中没有作用库函数的列必须出现在GROUP BY中，并且先按第一列的值分组，第一列值相同的再按第二列分组，依次类推。 这里，实际上仅需按C#分组 即可，不必再按CN分组，为此可用MIN函数作用之。因为同一分组中C#相同，CN也相同。 SELECT SC.C#,MIN(C.CN), COUNT(S#) FROM SC, C WHERE SC.C#=C.C# GROUP BY SC.C#； 2018/11/19 数据库系统

例3 带条件的分组查询、统计：使用HAVING 求选修课程超过3门的学生学号 SELECT S# FROM SC GROUP BY S# HAVING COUNT（*）>3； WHERE是选择记录的条件； HAVING是选择分组的条件且 必须和GROUP BY一起使用 库函数只能作用于HAVING和目标列，而不能用于WHERE。 假定SC中的G用百分制表示，求最低成绩不低于85分，平均成绩不低于90分的学生学号和姓名，并按学号降序排序。 SELECT SC.S#,MIN(S.SN) FROM S,SC WHERE S.S#=SC.S# GROUP BY SC.S# HAVING MIN(SC.G)>=85 AND AVG(SC.G)>=90 ORDER BY SC.S# DESC; 过滤分组，可用库函数多次作用同一字段 通过一个库函数，使之可不必出现在GROUP BY中 建立两个表的连接 按学号分组 按学号降序排序 2018/11/19 数据库系统

§4 SQL数据操纵----数据更新 一、插入数据 1、插入单个元组 INSERT VALUES （常量 [，常量] … ] ； INTO 表名 [（字段名 [，字段名 ] … ] VALUES （常量 [，常量] … ] ； 例1：插入一条选课记录（S1，C5）。 INSERT INTO SC（S#，C#） VALUES（‘S1’，‘C1’）； 2018/11/19 数据库系统

★ 当在INTO后面仅指定部分属性列时，插入记录后其它列的值为空值； 说明： ★ 当在INTO后面仅指定部分属性列时，插入记录后其它列的值为空值； ★ 如果INTO后面没有指定属性列，则必须按表列的定义次序为每个列指定一个值； ★ 具有NOT NULL属性的列，必须指定值。 2、插入子查询结果 INSERT INTO 表名 [（字段名 [，字段名 ] … ] 子查询； 2018/11/19 数据库系统

例2：求每个学生的平均成绩，并按学号、姓名、平均成 绩存入数据库。（设成绩G是数值型） 先创建一个表 CREATE TABLE AG（S# CHAR（8）， SN CHAR（8）， AG SMALLINT）； 将计算结果插入上表中 INSERT INTO AG（S#，SN，AG） SELECT S.S#，MIN(SN)，AVG（G） FROM S，SC WHERE S.S# = SC.S# GROUP BY S.S# ; 为什么用MIN（）函数？ 2018/11/19 数据库系统

★ 当省略WHERE子句时，修改表中所有记录，否则仅修改满足条件的记录； 二、修改数据 UPDATE 表名 SET 列名 = 表达式[，列名 = 表达式]… [WHERE 条件]； 说明： ★ 当省略WHERE子句时，修改表中所有记录，否则仅修改满足条件的记录； ★ 条件也可以使用子查询。 例1：将所有学生的年龄增加1岁。 UPDATE S SET SA = SA +1 ； 2018/11/19 数据库系统

对SC中的每个选课单，检查其对应学生所在的系是否‘MA’。 找出‘MA’系的所有学生，检查SC选课单所对应的学生是否是这些学生中的一员。 例2：把数学系全体学生的成绩置零 UPDATE SC SET G=0 WHERE ‘MA’= （SELECT SD FROM S WHERE S.S# = SC.S# )； UPDATE SC SET G=0 WHERE S# IN ( SELECT S# FROM S WHERE SD=‘MA’ ); 对SC中的每个选课单，检查其对应学生所在的系是否‘MA’。 找出‘MA’系的所有学生，检查SC选课单所对应的学生是否是这些学生中的一员。 2018/11/19 数据库系统

只能删除表记录，不删除表结构。无条件时，删除全部记录，仅剩一个空表；有条件时删除满足条件的记录。 三、删除数据： 例1：删除不及格的学生记录。 DELETE FROM SC WHERE G < 60； DELETE FROM 表名 [ WHERE 条件 ]； 例2：删除物理课的全部选课单 DELETE FROM SC WHERE C# = （SELECT C# FROM C WHERE CN = ‘物理’）； 只能删除表记录，不删除表结构。无条件时，删除全部记录，仅剩一个空表；有条件时删除满足条件的记录。 WHERE ‘物理’ = （SELECT CN FROM C WHERE C.C# = SC.C#）； 为物理删除命令 删除表结构用DROP TABLE 2018/11/19 数据库系统

增、删、改操作只能对一个表操作，可能会造成数据的不一致性。例如： 四、更新操作与数据库的一致性 增、删、改操作只能对一个表操作，可能会造成数据的不一致性。例如： 删除“物理”课程的全部信息，需使用两个操作实现，删除C表的课程记录和删除SC表的选课记录。若在完成第一个操作后发生意外，致使第二个操作未能实现，则造成数据库的不一致。因为SC中的物理课程号还存在，而被参照的C表中已没有该课程号的课程了。所以应保证这两个操作要么全做，要么全不做，为此，在数据库系统中引入了事物的概念。 为保证数据的一致性，大型数据库系统一般都提供若干策略： 2018/11/19 数据库系统

（2）检查参照表中是否有数据参照，若有则拒绝删除。 向参照表中插入数据时 检查所有被参照表中是否有被参照的信息，若没有则拒绝插入。 删除主表（被参照表）中的数据时 （1）自动删除参照表中的相应数据； （2）检查参照表中是否有数据参照，若有则拒绝删除。 向参照表中插入数据时 检查所有被参照表中是否有被参照的信息，若没有则拒绝插入。 修改主表中的被参照字段 检查参照表中是否有数据参照，若有则拒绝修改。 虽说SQL的DML语句只有4个，但语句中成分多样，因此简单易学、功能丰富、灵活多样。 2018/11/19 数据库系统

 视图是从一个或几个基本表（或视图）导出的表。 （用户外模式是由若干基本表和/或若干视图构成的） §5 视 图  视图是从一个或几个基本表（或视图）导出的表。 （用户外模式是由若干基本表和/或若干视图构成的）  视图是一个虚表，只存储视图的定义，数据存在所基于 的基本表中。  视图定义后就可象基本表一样来使用。 可创建、删除视图 可用来定义新的视图 可在视图上查询（SELECT） 可更新（INSERT，DELETE，UPDATE）视图，但受 限制 2018/11/19 数据库系统

格式：CREATE VIEW 视图名 [（字段名 [，字段名]…）] AS 子查询 [WITH CHECK OPTION ]； 一、视图的定义 1、建立视图 格式：CREATE VIEW 视图名 [（字段名 [，字段名]…）] AS 子查询 [WITH CHECK OPTION ]； 功能：在数据字典中存储视图的定义 （但并不执行子查询）， 此后视图名就可作为一个表来使用。 视图中包含的字段 对视图UPDATE或 INSERT时,记录要 满足子查询中的条件 例1：建立计算机系的学生视图 CREATE VIEW CS_S AS SELECT S#, SN, SA FROM S WHERE SD=‘CS’； 属性列省略，隐含同 2018/11/19 数据库系统

例2：把学生的学号和平均成绩定义为一个视图 CREATE VIEW S_G(S#, GAVG) ★ 组成视图的属性列名，要么全部写出，要么全部省略，省略时，隐含视图的属性列同子查询的目标列。当SELECT语句中有库函数、或字段表达式、或多表连接有同名字段时，则视图中必须指定字段名。 例2：把学生的学号和平均成绩定义为一个视图 CREATE VIEW S_G(S#, GAVG) AS SELECT S#, AVG(G) FROM SC GROUP BY S#; ★ 视图中字段名可以和基本表中的字段名不同 例3：建一个课程号、课程名及选课人数(不少于10)的视图 CREATE VIEW C_N(KCH, KCM, XXRS) AS SELECT SC.C#, MAX( CN), COUNT(S#) FROM C, SC WHERE C.C#=SC.C# GROUP BY SC.C# HAVING COUNT(S#)>=10 ; 2018/11/19 数据库系统

★ 没有修改视图的方法，要实现此功能，唯一的途径是先删除，再重建。 ★ 视图的子查询可以基于一个或多个基本表或/和视图上。 ★ 没有修改视图的方法，要实现此功能，唯一的途径是先删除，再重建。 ★ 视图的子查询可以基于一个或多个基本表或/和视图上。 2、删除视图 DROP VIEW 视图名； ★ 删除基本表或视图后，由被删除的基本表或视图导出的视图仍然存在，但已无法使用，需另行删除。 二、视图上的查询 1、执行过程 从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，最后在基本表上执行修改后的查询，这一转换称为视图消解。 2018/11/19 数据库系统

请看下例 例1：SELECT S#，SA SELECT S#，SA FROM CS_S FROM 修改为 WHERE SA < 20; SD=‘CS’ CS_S 视图中的子查询 SELECT S#, SN, SA FROM S WHERE SD=‘CS’ AND 2、注意事项 当视图中的字段对应的是一个库函数或字段表达式时， 有些系统 转换后的查询可能会不正确 请看下例 2018/11/19 数据库系统

例2：求平均成绩90分以上的学生 SELECT S#, AVG(G) FROM SC WHERE AVG(G) >=90 GROUP BY S# ; SELECT * FROM S_G WHERE GAVG >=90; 修改为 正 确 吗 ? S_G视图中的定义是 CREATE VIEW S_G(S#, GAVG) AS SELECT S#, AVG(G) FROM SC GROUP BY S# 正确的应为: SELECT S#, AVG(G) FROM SC GROUP BY S# HAVING AVG(G) >=90; 2018/11/19 数据库系统

三、视图上的更新（ISNERT，DELETE，UPDATE） 1、执行方式 将对视图的更新语句转化为对相应的基本表的更新语句，然后执行。为防止更新基本表中不属于本视图的数据，可在视图定义时加上WITH CHECK OPTION子句。 例1: INSERT INTO CS_S VALUES (‘S12’, ‘YanXi’, 19) 视图中的子查询 SELECT S#, SN, SA FROM S WHERE SD=‘CS’ INSERT INTO S(S#, SN, SA, SD) VALUES (‘S12’, ‘YanXi’, 19,‘CS’) 2018/11/19 数据库系统

例2：将计算机系学号为“S2”的学生姓名改为“刘辰”。 UPDATE CS_S SET SN = “刘辰” WHERE S#=“S2”; UPDATE S SET SN = “刘辰” WHERE S#=“S2” AND SD=“CS”; 例3：删除计算机系学号为“S2”的学生 . DELETE FROM CS_S WHERE S#=“S2”; DELETE FROM S WHERE S#=“S2” AND SD=“CS”; 2、注意事项 不是所有的视图更新都可正确转化为对基本表的更新语句 2018/11/19 数据库系统

 有些视图是可更新的, 有些视图是不可更新的。但现在还无判别方法。 例如：UPDATE S_G SET GAVG=90 WHERE S#=‘S1’; 不能有意义地转化  有些视图是可更新的, 有些视图是不可更新的。但现在还无判别方法。  肯定可以更新的视图是 行列子集视图 从单个表导出，且只是去掉了基本表的某些行和某些列并保留了码  处理方式：只有从单个表导出的视图才允许更新操作， 且作一系列的限制。（限制见P127）  从概念上分清不可更新视图和不允许更新视图。 2018/11/19 数据库系统

2、用户能以不同的方式对待同一数据，方便灵活 3、提供一定程度的逻辑独立性 4、有利于安全保密 四、视图的优点（P128） 1、能够简化用户的操作 2、用户能以不同的方式对待同一数据，方便灵活 3、提供一定程度的逻辑独立性 4、有利于安全保密 本节开头 本章开头 下一节 2018/11/19 数据库系统

数据控制功能包括事物管理功能和数据保护功能。即 数据的安全性、完整性、事务控制、并发控制和恢复功能 §6 SQL数据控制功能 数据控制功能包括事物管理功能和数据保护功能。即 数据的安全性、完整性、事务控制、并发控制和恢复功能 本节只讨论安全性机制，即用户对数据的存取权力。 可通过在CREATE TABLE、ALTER TABLE语句中定义码、取值唯一的列、不允许空值的列、外码（参照完整性）及其它一些约束条件来体现。 在后面章节介绍。 一、授权 1、机制：大的DBMS中有一个超级用户DBA，其他用户能否 存在、对某类数据具有何种操作权力是由DBA决定 的，系统提供授权机制。执行过程为： 2018/11/19 数据库系统

（3）当用户提出 操作请求时，系统根据授权情况进行检查， 以决定是否执行。 （1）用数据控制语言把授权决定告知系统； （2）系统把授权的结果存入数据字典； （3）当用户提出 操作请求时，系统根据授权情况进行检查， 以决定是否执行。 2018/11/19 数据库系统

GRANT 权力 [，权力 ] … [ ON 对象类型 对象名 ] TO 用户名 [，用户名] … 2、权力的授予与收回 若干权力 操作对象 授予： GRANT 权力 [，权力 ] … [ ON 对象类型 对象名 ] TO 用户名 [，用户名] … [ WITH GRANT OPTION ]； 获得权力的用户 有此项，被授权用户可再授权给其他用户 收回： REVOKE 权力 [，权力 ] … [ ON 对象类型 对象名 ] FROM 用户名 [，用户名] …； 3、操作权力分类： 见P130表3.4 2018/11/19 数据库系统

 数据库的属主、表的属主、数据库对象的属主在他创建的对象上具有一切可能的权力，其他用户得不到主人的授权就不能在该对象上操作； 4、示例： 见P131 5、说明  数据库的属主、表的属主、数据库对象的属主在他创建的对象上具有一切可能的权力，其他用户得不到主人的授权就不能在该对象上操作；  SQL的授权机制十分灵活，各种系统又作了适当的补充， 使用十分方便；  SQL的安全性控制除了上述授权机制外，还可设置口令进一步保密。 本节开头 本章开头 下一节 2018/11/19 数据库系统

1、交互式：在终端上每输入一条SQL语句，系统立即执行，然后等待用户输入下一条语句。 2、自编程式：在实际的DMBS中，都对SQL进行了扩充，增加了条件、循环等控制语句，并提供编程机制。如SYBASE中，用户可以编写存储过程并调用它。 3、嵌入式（嵌入到某种主语言中使用）： 宿主语言负责：运算、处理、流程控制等 SQL负责：数据库操作 2018/11/19 数据库系统

用前缀，如EXEC SQL或$等，标记SQL语句的开始； 用END-EXEC或分号‘；’等标记SQL语句的结束。 必须解决和主语言相互配合、连接等问题 1、标识SQL语句 用前缀，如EXEC SQL或$等，标记SQL语句的开始； 用END-EXEC或分号‘；’等标记SQL语句的结束。 SQL语句标识是通知主语言的预编译程序将SQL语句转化为等价的主语言语句，然后再由编译程序形成目标代码 2、SQL语句与主语言之间的通信 （1）通过SQL通信区（SQLCA）将SQL语句的执行状态传递给主语言。 SQLCA是一个数据结构，其中有一个重要变量SQLCODE，存放SQL语句是否执行成功的信息，每执行一个SQL语句，主语言都应测试该变量。 2018/11/19 数据库系统

（3）SQL语句中可使用主语言的程序变量（叫主变量），但要加前缀标志，一般用冒号‘：’。 办法：用游标（Cursor），有的叫位置指针 （6）通常用CONNECT语句来连接（申请使用）数据库 例子见P136 2018/11/19 数据库系统

最简单的一类语句，不需返回结果，不使用主变量，在主语言中只需加前缀EXEC SQL和语句结束符即可。 说明性语句 数据定义语句 数据控制语句 查询结果为单记录的SELECT语句 非CURRENT形式的UPDATE语句 非CURRENT形式的DELETE语句 INSERT语句 一般均使用主变量 1、说明、数据定义、数据控制语句 见教材P137-138 2018/11/19 数据库系统

EXEC SQL SELECT S#，C#，G INTO :SNO，:CNO，:G :GID FROM SC 查询结果存入这三个主变量中 指示变量，<0说明取得的G为空值 例 根据主变量的值查找学生的信息 EXEC SQL SELECT S#，C#，G INTO :SNO，:CNO，:G :GID FROM SC WHERE S#= :GS AND C#=:GC； 待查的学号存在GIVENS#中  SELECT语句的INTO、WHERE、HAVING子句中可使用主变量  可在INTO子句中使用指示变量，以指明某字段是否空值  若SQLCODE=100，说明没有满足条件的记录 当查询到的记录多于1条时，在SQLCODE中返回错误信息 2018/11/19 数据库系统

3、非CURRENT形式的UPDATE语句 例 将计算机系全体学生的成绩置为空值 GID = -1； EXEC SQL UPDATE SC 例 将计算机系全体学生的成绩置为空值 GID = -1； EXEC SQL UPDATE SC SET G = :GG :GID WHERE “CS”= (SELECT SD FROM S WHERE S.S#=SC.S#); 这里使用了值为负的指示变量GID, 主变量GG可为任意值  WHERE、SET子句中可以使用主变量，同时SET子句中还可以使用指示变量 通过检查SQLCA的值，判别更新是否成功 2018/11/19 数据库系统

4、非CURRENT形式的DELETE语句 例 学号在主变量X1和X2之间的学生已毕业，删除他们的信息。 先删除他们的选课信息 EXEC SQL DELETE FROM SC WHERE S# BETWEEN :X1 AND :X2; 再删除他们的基本情况信息 FROM S  WHERE子句中可使用主变量 2018/11/19 数据库系统

VALUES（:SNO, :CNO, :GG :GID）； 5、INSERT语句 例 插入一条学生选课记录 GID = -1； EXEC SQL INSERT INTO SC（S#，C#，G） VALUES（:SNO, :CNO, :GG :GID）； 欲插入记录的值由主语言存放在三个主变量中，GID指示成绩字段值为空值，GG可为任意值 四、使用游标的SQL语句 下列情况必须使用游标 查询结果为多条记录的SELECT 语句 CURRENT形式的UPDATE语句 CURRENT形式的DELETE语句 2018/11/19 数据库系统

如：查找由主变量DEPT中给出的某个系的全体学生信息 1、查询结果为多条记录的SELECT语句 如：查找由主变量DEPT中给出的某个系的全体学生信息 EXEC SQL DECLARE SX CURSOR FOR SELECT S#，SN，SA FROM S WHERE SD=:DEPT; EXEC SQL OPEN SX; DO WHILE EXEC SQL FETCH SX INTO :S#, :SNAME, :AGE; …… END; EXEC SQL CLOSE SX; 定义游标 打开游标 推进游标 关闭游标 2018/11/19 数据库系统

EXEC SQL DECLARE SX CURSOR FOR SELECT S#，SN，SA FROM S WHERE SD=:DEPT; EXEC SQL OPEN SX; DO WHILE EXEC SQL FETCH SX INTO :S#, :SNAME, :AGE; …… END; EXEC SQL CLOSE SX; S：S# SN SD SA S0 X MA 18 S1 A CS 20 S2 B CS 21 S3 C MA 19 S4 D CI 19 S5 E MA 20 S6 F CS 22 S7 G CI 21 游标SX 游标SX  假设DEPT中为‘CS’ 主变量 S# SNAME AGE S1 A 20 2018/11/19 数据库系统

EXEC SQL DECLARE SX CURSOR FOR SELECT S#，SN，SA FROM S WHERE SD=:DEPT; EXEC SQL OPEN SX; DO WHILE EXEC SQL FETCH SX INTO :S#, :SNAME, :AGE; …… END; EXEC SQL CLOSE SX; S：S# SN SD SA S0 X MA 18 S1 A CS 20 S2 B CS 21 S3 C MA 19 S4 D CI 19 S5 E MA 20 S6 F CS 22 S7 G CI 21 游标SX  假设DEPT中为‘CS’ 主变量 S# SNAME AGE S1 A S2 B 21 20 2018/11/19 数据库系统

EXEC SQL DECLARE SX CURSOR FOR SELECT S#，SN，SA FROM S WHERE SD=:DEPT; EXEC SQL OPEN SX; DO WHILE EXEC SQL FETCH SX INTO :S#, :SNAME, :AGE; …… END; EXEC SQL CLOSE SX; S：S# SN SD SA S0 X MA 18 S1 A CS 20 S2 B CS 21 S3 C MA 19 S4 D CI 19 S5 E MA 20 S6 F CS 22 S7 G CI 21  游标SX 假设DEPT中为‘CS’ 主变量 S# SNAME AGE S2 S6 B F 21 22 2018/11/19 数据库系统

EXEC SQL DECLARE SX CURSOR FOR SELECT S#，SN，SA FROM S WHERE SD=:DEPT; EXEC SQL OPEN SX; DO WHILE EXEC SQL FETCH SX INTO :S#, :SNAME, :AGE; …… END; EXEC SQL CLOSE SX; S：S# SN SD SA S0 X MA 18 S1 A CS 20 S2 B CS 21 S3 C MA 19 S4 D CI 19 S5 E MA 20 S6 F CS 22 S7 G CI 21 游标SX 主变量 S# SNAME AGE S6 F 22 2018/11/19 数据库系统

2、CURRENT形式的UPDATE和DELETE语句 可修改或删除当前活动游标所指向的记录. 例子见P144-146 本节开头 本章开头 2018/11/19 数据库系统