第三章 关系数据库语言SQL 3.1 SQL的特征 3.2 SQL的数据定义 3.3 SQL的数据查询 3.4 SQL的数据更新

Presentation on theme: "第三章 关系数据库语言SQL 3.1 SQL的特征 3.2 SQL的数据定义 3.3 SQL的数据查询 3.4 SQL的数据更新"— Presentation transcript:

1 第三章 关系数据库语言SQL 3.1 SQL的特征 3.2 SQL的数据定义 3.3 SQL的数据查询 3.4 SQL的数据更新
第二章主要内容： 基本概念：模式、关系等 关系模型的结构、操作、约束 关系代数：集合运算、关系运算等 3.7 复杂数据类型

2 3.1 SQL的特征 3.1.1 SQL的标准 SQL-86：SQL的第一个标准是1986年10月由美国国家标准化组织(ANSI)公布的。
SQL-89：ANSI以后通过对SQL-86的不断修改和完善，于1989年第二次公布了SQL标准，即SQL-89，该标准增强了完整性的语言特征。 SQL-92(SQL2)：1992年又公布了SQL-92标准，该标准增加了支持对远程数据库的访问，扩充了数据类型、操作类型、动态SQL等许多新的特征。 SQL-99(SQL3)：完成于1999年的SQL-99修订本具有更高级的特征。引入了支持对象-关系DBMS模型的SQL，扩展了对象、递归、触发等许多新的特征，支持用户自定义函数、自定义数据类型。

3 3.1 SQL的特征 SQL-2003：增加了对互联网上数据表示和交换的事实标准XML、OLAP、数据挖掘采样等的支持。
SQL-2006：定义了SQL与XML（包含Xquery）的关联应用，增强XML对数据处理的能力。 作为一个标准，SQL的目标是屏蔽掉不同DBMS产品之间的差异性，用户只要掌握SQL就能操作所有的产品 但现实与理想总是有差距的！各公司的DBMS在支持SQL时都有一些差异，但大体上对于SQL的语法形式都是能支持的，用户在使用DBMS时还是需要按照其要求使用SQL。

4 3.1 SQL的特征 2. SQL的功能特点 功能： 数据定义 数据查询 数据操纵 数据控制

5 SQL的功能特点 特点： 综合统一 非关系模型的数据语言不同模式有不同的数据定义语言及数据操纵语言。
包括定义关系模式、录入数据以建立数据库、查询、更新、维护、数据库重构、数据库安全性控制等一系列操作要求，这就为数据库应用系统开发提供了良好的环境。 例如用户在数据库投入运行后，还可根据需要随时地逐步地修改模式，并不影响数据库的运行，从而使系统具有良好的可扩充性。

6 SQL的功能特点 高度非过程化 非关系数据模型的数据操纵语言是面向过程的语言，用其完成某项请求，必须指定存取路径。
而用SQL语言进行数据操作，用户只需提出“做什么”，而不必指明“怎么做”，因此用户无需了解存取路径，存取路径的选择以及SQL语句的操作过程由系统自动完成。 这不但大大减轻了用户负担，而且有利于提高数据独立性。

7 SQL的功能特点 灵活的使用方式 SQL语言既是自含式语言，又是嵌入式语言。
作为嵌入式语言，SQL语句能够嵌入到高级语言（例如C、COBOL、FORTRAN、PL/1）程序中，供程序员设计程序时使用。而在两种不同的使用方式下，SQL语言的语法结构基本上是一致的。这种以统一的语法结构提供两种不同的使用方式的作法，为用户提供了极大的灵活性与方便性。

8 SQL的功能特点 简洁、通用、功能强 SQL语言功能极强，设计巧妙，语言简洁，完成数据定义、数据操纵、数据控制的核心功能只用了9个动词，如表所示。而且SQL语言语法简单，接近英语口语，因此容易学习，容易使用。 SQL功能 核心动词 数据查询 SELECT 数据定义 CREATE, DROP, ALTER 数据控制 INSERT, UPDATE, DELETE 数据操纵 GRANT, REVOKE

9 SQL的基本组成 术语对照： 一般关系模型 SQL 内模式-----存储文件（索引） 模式-----基本表(TABLE)
外模式-----视图（VIEW) 元组 行(ROW) 属性 列(COLUMN) SQL 视图1 视图2 基本表1 基本表2 基本表3 存储文件1 存储文件2

10 SQL的基本组成 基本表是本身独立存在的表，在SQL中一个关系就对应一个表。一些基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。 存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理文件结构是任意的。 视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。 用户可以用SQL语言对视图和基本表进行查询。在用户眼中，视图和基本表都是关系，而存储文件对用户是透明的。

11 SQL语句的分类 按照功能可分为4类： SQL模式定义语句（DDL，Data Definition Lang）：创建、更新、撤销各种数据库对象（表、视图、索引等） 模式定义语句是对数据库对象本身进行操作，而不是对其中包含的数据进行操作，比如创建一个空表、为表增加一个属性、修改某个属性的数据类型

12 SQL语句的分类 SQL数据操纵语句（DML，Data Manipulation Lang）：真正对数据库对象中的数据进行操作的语句
包括查询和更新操作 查询是从已存在的数据中根据要求检索出结果 更新操作是向数据库中放入新的数据，或者修改已存在的数据，或者删除已存在的数据

13 SQL语句的分类 SQL事务与控制语句：完成数据库授权、事务管理以及控制SQL语句集的运行。用来授予或回收访问数据库的某种特权，控制数据操纵事务的发生时间及效果、对数据库进行监视等。 SQL会话及诊断语句：SQL通过嵌入式和动态SQL规则规定了SQL语句在高级语言程序语言中的使用规范。会话规则可使应用程序连接到一个SQL服务器，并使之交互。这类语句主要功能是建立数据库连接，为SQL会话设置参数、获取诊断等。

14 SQL的数据类型 4. SQL的数据类型 预定义数据类型 构造数据类型 用户定义数据类型（UDT，User Defined Type）
以预定义数据类型为基础，组合形成新的数据类型，如数组、结构等 用户定义数据类型（UDT，User Defined Type） 用户按照一定规则组合预定义数据类型 形成的数据类型 DBMS还会扩充自己的数据类型，如IP、TEXT等

15 SQL环境 （1）SQL模式与目录 SQL模式：基本表、视图等对象的集合。 好处：允许在不同的SQL模式中出现同名的基表名或视图名。
定位基表的方式： <目录名>．<模式名>．<表名> （2）SQL环境 设置默认的目录和模式 设置用户身份

16 3.2 SQL的数据定义 1. SQL模式的定义与撤销 （1）SQL模式的定义
CREATE SCHEMA ＜模式名＞ AUTHRIZATION ＜用户名＞ [＜CREATE DOMAIN子句＞|＜CREATE TABLE子句＞|＜CREATE VIEW ＞|…… ] 例：CREATE SCHEMA Teaching_db AUTHRIZATION Hang； （2）数据库模式的删除 DROP SCHEMA ＜模式名＞ {CASCADE | RESTRICT} CASCADE(级联式) RESTRICT（约束式)

17 3.2 SQL的数据定义 2.表的建立和删除 （1）表的建立 命令格式： 例：CREATE TABLE Student
( sno CHAR(5) NOT NULL UNIQUE， sname CHAR(20) NOT NULL ， sex CHAR(1)， age INT ， dept CHAR(15) )； 主码

18 3.2 SQL的数据定义 完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可定义在列级，也可以定义在表级。
注意： 完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可定义在列级，也可以定义在表级。 例：定义P.46的三个表结构，并指定相应的数据完整性约束条件。 分析 Student表： 主码：sno 姓名：非空 性别：男、女两值 Course表： 主码：cno 课程名：非空 外码：pcno SC表： 主码：(sno,cno) 成绩：0—100 外码：sno,cno

19 3.2 SQL的数据定义 CREATE TABLE Student ( sno CHAR (5),
sname CHAR (8) NOT NULL, sex CHAR (2), age SMALLINT, dept CHAR (20), PRIMARY KEY(sno), CHECK sex IN (‘男’,‘女’) ); 列级完整性约束条件 实体完整性约束条件 用户自定义完整性约束条件

20 3.2 SQL的数据定义 CREATE TABLE Course ( cno CHAR (4),
cname CHAR (10) NOT NULL, pcno CHAR (4), credit SMALLINT, PRIMARY KEY (cno), FOREIGN KEY (pcno) REFERENCES Course(cno) ); 参照完整性约束条件

21 3.2 SQL的数据定义 CREATE TABLE SC ( sno CHAR (5), cno CHAR (4) ,
grade SMALLINT, PRIMARY KEY (sno,cno), FOREIGN KEY (sno)REFERENCES Student(sno), FOREIGN KEY (cno)REFERENCES Course(cno), CHECK ((grade IS NULL) OR (grade BETWEEN 0 AND 100)) );

22 3.2 SQL的数据定义 （2）表的删除 格式： DROP TABLE <表名> [CASCADE | RESTRICT]
例：DROP TABLE Student CASCADE；

23 3.2 SQL的数据定义 3.基本表的扩充和修改 一般格式为： ALTER TABLE <表名>
[ADD <新列名><数据类型>[完整性约束]] [DROP<完整性约束名><完整性约束名>] [MODIFY<列名> <数据类型><数据类型>]; 　 其中<表名>指定需要修改的基本表，ADD子句用于增加新列和新的完整性约束条件，DROP子句用于删除指定的完整性约束条件，MODIFY子句用于修改原有的列定义。 （1）在现存表中增加新列 格式：ALTER TABLE <表名> ADD (<列名> <数据类型>，… )

24 思考：如果place原来是char(16)？
3.2 SQL的数据定义 （2）删除已存在的某个列 格式：ALTER TABLE <表名> DROP <列名> [CASCADE | RESTRICT] 例：ALTER TABLE Student DROP addr； （3）修改原有列的类型 MODIFY <列名> <类型>； MODIFY place CHAR(8)； 思考：如果place原来是char(16)？

25 3.2 SQL的数据定义 （4）补充定义主码 格式：ALTER TABLE <表名>
ADD PRIMARY KEY (<列名表> ) （5）删除主码 格式：ALTER TABLE <表名> DROP PRIMARY KEY 新增列一律为空值 修改列定义可能会破坏已有数据

26 3.2 SQL的数据定义 修改基本表实例 例：向Student表增加“入学时间”列，其数据类型为日期型
ALTER TABLE Student ADD Scome DATE; 例：将年龄的数据类型改为半字长整数 ALTER TABLE Student MODIFY Sage SMALLINT; 例：删除关于学号必须取唯一值的约束 ALTER TABLE Student DROP UNIQUE(Sno);

27 自定义的域类型 域类型：用于建立用户自定义属性的一个特定数据类型 格式：
带有约束的数据类型 缺省值 格式： CREATE DOMAIN <域名> [AS] <数据类型> [DEFAULT <缺省值>] [<域约束>]

28 自定义的域类型 建立一个建筑公司的数据库模式，名为Company，属主是Yin_min。它由三个表组成：Worker（职工）、Assignment（任务分配）和Building（建筑物）。 CREATE SCHEMA Company AUTHORIZATION Yin_min CREATE DOMAIN ITEM_ID NUMERIC(4) DEFAULT 0 CHECK (VALUE IS NOT NULL)

29 自定义的域类型 ITEM_ID可以作为和NUMERIC等一样的数据类型用来定义列 CREATE TABLE Worker(
worker_id ITEM_ID, worker_name CHAR(12), skill_type CHAR(8), supv_id NUMERIC(4) PRIMARY KEY(worker_id)) 以后对于ITEM_ID的改变会自动施加到使用该域类型的所有表中。

30 自定义的域类型 CREATE TABLE Building( bldg_id ITEM_ID, bldg_address CHAR(20), type CHAR(9) DEFAULT ‘office’ CHECK (type IN (‘office’,’warehouse’,’residence’)), qlty_level NUMERIC(1), status NUMERIC(1) DEFAULT 1 CHECK (status>0 and status<4), PRIMARY KEY(bldg_id))

31 自定义的域类型 CREATE TABLE Assignment( worker_id ITEM_ID, bldg_id ITEM_ID, start_date DATE, num_days INT, PRIMARY KEY (worker_id,bldg_id), FOREIGN KEY (worker_id) REFERENCES Worker ON DELETE CASCADE, FOREIGN KEY (bldg_id) REFERENCES Worker ON DELETE CASCADE);

32 索引 表A 目的： 提供多种存取路径，加快查找速度。 例子：从表中找出名称为“元组3”的元组
没有索引：需要遍历表中的元组来查找所有满足条件的元组 建有索引：可以直接定位到满足条件的元组 顺序扫描 索引扫描 表A 元组1 元组2 元组3 ...... 元组n 索引

33 3.2 SQL的数据定义 5.索引的建立与删除 建立索引的目的：基本表上建立一个或多个索引，以提供多种存取路径，加快查找速度。 命令格式：
CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名> (<列名><次序>[ [，<列名><次序>]]…)； 次序： 升序（ASC，缺省） 降序 （DESC） UNIQUE: 每一个索引值只对应惟一的数据记录。 CLUSTER: 建立聚簇索引，即索引项的顺序与表中记录的物理顺序一致。

34 聚簇索引 出于性能上的考虑，数据库与磁盘之间的交换单位以磁盘块为单位，一个磁盘块通常保存有多个元组
如果能在每次取出的磁盘块中能够包含尽可能多的“有用元组”，可以大幅减少无效I/O，提高性能 聚簇索引把元组在索引中的顺序和在物理存储上的顺序保持一致： 即在索引键值上相邻的两个元组在磁盘上也是相邻存放 在进行范围查找时，聚簇就特别有效

35 建立索引的原则 (1) 在表中插入数据后创建索引 (2) 索引正确的表和列 (3) 合理安排索引列 (4) 限制表中索引的数量
(5) 指定索引数据块空间的使用 根据索引大小设置存储参数 一个基本表上最多只能建立一个聚簇索引 经常更新的列上不适宜建立聚簇索引

36 3.2 SQL的数据定义 所建索引放何处？ 例: ① 为Student表按学号升序建惟一聚簇索引。
② 为SC表按学号升序和课程号降序建惟一索引。 ① CREATE UNIQUE CLUSTER INDEX Stno ON Student(Sno)； ② CREATE UNIQUE INDEX Scno ON SC(Sno ，Cno DESC)； 删除索引一般格式为： DROP INDEX<索引名>； 例： DROP INDEX Stno ； 删除索引时，系统会同时从数据字典中 删去有关该索引的描述。

37 3.3 SQL的数据查询 一般形式： SELECT <A> FROM <R> WHERE <F>
GROUP BY <列名表1> HAVING <条件表达式> ORDER BY <列名表2>; 查询目标 数据来源 选择元组的条件 将结果按<列名表1>的值进行分组 满足条件的组才予输出 按<列名表2>排序查询结果 基本语句含义: 根据WHERE中的F，从基表或视图R中找出满足条件的元组，再从中选出目标属性值形成结果表。

38 3.3 SQL的数据查询 如果有GROUP子句，则将结果按<列名表1>的值进行分组，该列值相等的元组为一个组，每个组产生结果表中的一条记录。通常在成组后再使用集函数。 如果GROUP子句带HAVING短语，则只有满足指定条件的组才予输出。 如果有ORDER子句，则结果表还要按<列名表2>的值的排序。

39 3.3 SQL的数据查询－单表查询 3.3.1 单表查询 例 ：查询所有学生的姓名、学号、所在系。
单表查询 一、选择表中的列 例 ：查询所有学生的姓名、学号、所在系。 SELECT sname，sno，dept FROM Student （次序不同 ）; 如果要查询所有列，可以使用*表示所有列 SELECT * FROM Student; sname sno dept ——————————— 王萧虎 信息院 ……

40 3.3 SQL的数据查询－单表查询 SELECT sno SELECT DISTINCT sno 二、选择表中的行 1 消除取值重复的行
例：查考试成绩不及格的学号。 Sno Cno Grade 001 55 002 50 003 66 SELECT sno FROM SC WHERE grade<60 ； ALL ALL Sno 001 SELECT DISTINCT sno FROM SC WHERE grade<60 ； DISTINCT Sno 001

41 3.3 SQL的数据查询－单表查询 2. 查询满足条件的元组
查询满足指定条件的元组可以通过WHERE子句实现。WHERE子句常用的查询条件如下所示。 常用的查询条件： 查询条件 谓 词 比较 比较运算符 确定范围 BETWEEN AND, NOT BETWEEN AND 确定集合 IN, NOT IN 字符匹配 LIKE, NOT LIKE 空值 IS NULL, IS NOT NULL 多重条件 AND, OR

42 3.3 SQL的数据查询－单表查询 (1) 比较（=, <, >… ） 例 查计算机系全体学生的姓名
(1) 比较（=, <, >… ） 例 查计算机系全体学生的姓名 SELECT Sname FROM Student WHERE Sdept = 'CS'; 例 查所有年龄在20岁以下的学生姓名及其年龄 SELECT Sname, Sage FROM Student WHERE Sage <20; 或 WHERE NOT Sage>= 20;

43 3.3 SQL的数据查询－单表查询 (2)确定范围 (BETWEEN … AND … )
例: 查询选002号课程且成绩在80--90的学生号。 SELECT sno FROM SC WHERE sno=‘002’ AND grade BETWEEN 80 AND 90； (3)确定集合(IN) 例：查询信息院、数学系和计算机学院学生的姓名和性别。 SELECT sname，sex FROM Student WHERE dept IN ('信息院'，'数学系'，'计算机学院') 其他表示？ WHERE sno=‘002’ AND grade>=80 AND grade <=90; 其他表示？ WHERE dept='信息院' OR dept='数学系‘ OR dept='计算机学院' NOT 查询不是这三个系的学生的姓名和性别？

44 3.3 SQL的数据查询－单表查询 NOT NOT (4)字符匹配---近似查询，模糊查询
格式：[NOT] LIKE ’<匹配串>’ 含义：是查找指定的属性列值与<匹配串>相匹配的元组。 其中匹配串可含： ％：代表任意长度(可为0)的字符串。 _：代表任意单个字符。 例： 查所有姓刘或姓王的学生姓名、学号和性别。 SELECT sname，sno，sex FROM Student WHERE sname LIKE ‘刘％’ OR sname LIKE ‘王％’ ； ? 查询所有非姓刘或非姓王的学生姓名、学号和性别。 NOT NOT

45 3.3 SQL的数据查询－单表查询 这里是两个下划线！ 例 查姓“欧阳”且全名为三个汉字的学生的姓名
例 查姓“欧阳”且全名为三个汉字的学生的姓名 SELECT Sname FROM Student WHERE Sname LIKE ‘欧阳__'; 注意，由于一个汉字占两个字符的位置，所以匹配串欧阳后面需要跟２个_。 例 查名字中第二字为“阳”字的学生的姓名和学号 SELECT Sname, Sno FROM Student WHERE Sname LIKE '__阳%'; 这里是两个下划线！

46 3.3 SQL的数据查询－单表查询 格式2： LIKE ’<匹配串>’ ESCAPE ’<换码字符>’
若要查的串本身就含有％或_ ，则用ESCAPE ‘<换码字符>’对通配符进行转义。ESCAPE ’\’短语表示\为换码字符，这样匹配串中紧跟在\后面的字符”_”不再具有通配符的含义，而是取其本身含义，被转义为普通的”_”字符。 例：查“DB_”开头且倒数第3个字符为 i 的课程情况。 SELECT * FROM Course WHERE cname LIKE ‘DB\ _ ％i__’ ESCAPE ’ \’；

47 3.3 SQL的数据查询－单表查询 注意这里的匹配字符串’DB\_%i__’。第一个_前面有换码字符\，所以它被转义为普通的_字符。而%、第二个_和第三个_前面均没有换码字符\，所以它们仍作为通配符。 执行结果为： Cno Cname Ccredit DB_Design DB_Programming 2 13  DB_DBMS Design 4

48 3.3 SQL的数据查询－单表查询 (5) 涉及空值的查询
(5) 涉及空值的查询 例 某些学生选修某门课程后没有参加考试，所以有选课记录，但没有考试成绩，下面我们来查一下缺少成绩的学生的学号和相应的课程号 SELECT Sno, Cno 　 FROM SC WHERE Grade IS NULL; 注意这里的‘IS’不能用等号（‘=’） 代替。 例 查所有有成绩的记录的学生学号和课程号 SELECT Sno, Cno 　 FROM SC WHERE Grade IS NOT NULL;

49 3.3 SQL的数据查询－单表查询 SELECT sno, dept, age FROM Student WHERE sex=‘男’
三、对查询结果排序 例: 查询全体男学生的学号、系、年龄 结果按所在的系升序排列， 同一系中的学生按年龄降序排列。 条件 F 来源 R 目标 A 排序一 排序二 SELECT sno, dept, age FROM Student WHERE sex=‘男’ ORDER BY dept，age DESC； Order by的排序键应该 是查询清单中的列名

50 3.3 SQL的数据查询－单表查询 别名 sname 2012-age ——————————— …… Birthday 四、查询经过计算的值
SELECT子句的<目标列表达式>不仅可以是表中的属性列，也可以是有关表达式，即可以将查询出来的属性列经过一定的计算后列出结果。 例：查全体学生的姓名及其出生年份 SELECT Sname, 2012-Sage AS Birthday FROM Student; 例：查全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名 SELECT Sname, 'Year of Birth:', 1996-Sage, ISLOWER(Sdept) FROM Student; 别名 sname age ——————————— 王萧虎 …… Birthday

51 3.3 SQL的数据查询－单表查询 五、使用集函数 COUNT([DISTINCT | ALL] * ) 统计元组个数
SUM([DISTINCT | ALL]<列名> ) 计算一数值型列值的总和 AVG([DISTINCT | ALL]<列名> ) 计算一数值型列值的平均值 MAX([DISTINCT | ALL]<列名> ) 求一列值的最大值 MIN([DISTINCT | ALL]<列名> ) 求一列值的最小值 无重复值 缺省值

52 3.3 SQL的数据查询－单表查询 例: 查询女学生的总人数和平均年龄。 SELECT COUNT(sno),AVG(age)
例: 查询女学生的总人数和平均年龄。 SELECT COUNT(sno),AVG(age) FROM Student WHERE sex=‘女’ 例: 查询选修001号课程并及格的学生的最高分数、最低分及总分。 SELECT MAX(grade),MIN(grade),SUM(grade) FROM SC WHERE cno=‘001’ and grade>=60

53 3.3 SQL的数据查询－单表查询 六、对查询结果分组： GROUP BY 子句 将查询结果表按某一(多)列值分组，值相等的为一组。
目的：细化集函数的作用对象。如果未对查询结果分组，集函数将作用于整个查询结果，即整个查询结果只有一个函数值。否则，集函数将作用于每一个组，即每一组都有一个函数值。 例 查询各个课程号与相应的选课人数 SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno;

54 3.3 SQL的数据查询－单表查询 该SELECT语句对SC表按Cno的取值进行分组，所有具有相同Cno值的元组为一组，然后对每一组作用集函数COUNT以求得该组的学生人数。 查询结果为： Cno Sno … 1 9901 2 9902 9903 3 4 9904 Cno COUNT(Sno) 1 3 2 4

55 3.3 SQL的数据查询－单表查询 HAVING短语：在各组中选择满足条件的分组。 WHERE子句 ：在表中选择满足条件的元组。
例：求每个学生（号）的平均成绩，并将其超过88分的输出。 SELECT sno，AVG( grade) FROM SC GROUP BY sno HAVING AVG( grade) >88； WHERE ? HAVING短语：在各组中选择满足条件的分组。 WHERE子句 ：在表中选择满足条件的元组。

56 3.3 SQL的数据查询－单表查询 若HAVING子句无前导GROUPBY， 选择清单中不能含有非集函数列。
是一个集函数。 集函数在成组之前不计算，因此 不能用于WHERE子句, 一般将简单条件写入WHERE。

57 3.3 SQL的数据查询－单表查询 例：找出选课学生超过30人的课程的平均成绩及选课人数。
SELECT cno,AVG(grade),COUNT(*) AS st_number FROM SC GROUP BY sno HAVING st_number>=30

58 3.3 SQL的数据查询－单表查询 例：求学生关系中女生的每一年龄组（超过20人）有多少，要求查询结果按人数升序排列，人数相同时按年龄降序排列。 SELECT age,COUNT(sno) AS number FROM Student WHERE sex=‘女’ GROUP BY age HAVING number>20 ORDER BY number,age DESC

59 3.3 SQL的数据查询 嵌套查询 1. 子查询（嵌套查询） 查询块： 子查询（嵌套查询）： 一个查询块嵌套在另一查询块的条件之中。
SELECT <A> FROM <R> WHERE <F> 子查询（嵌套查询）： 一个查询块嵌套在另一查询块的条件之中。 上层的查询块又称为外层查询或父查询或主查询，下层查询块又称为内层查询或子查询。

60 3.3 SQL的数据查询 子查询一般跟在IN、SOME（ANY）、ALL和EXIST等谓词后面。 SQL语言允许多层嵌套查询。
嵌套查询的求解方法是由里向外处理。即每个子查询在其上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。 嵌套查询使得可以用一系列简单查询构成复杂的查询，从而明显地增强了SQL的查询能力。以层层嵌套的方式来构造程序正是 SQL(Structurred Query Language)中“结构化”的含义所在。

61 3.3 SQL的数据查询 (1) 带有IN谓词的子查询
带有IN谓词的子查询是指父查询与子查询之间用IN进行连接，判断某个属性列值是否在子查询的结果中。 例：查询与“刘晨”在同一个系学习的学生的学号、姓名、系 ①确定“刘晨”所在系名 SELECT Sdept FROM Student WHERE Sname=‘刘晨’; ②查找所有在IS系学习的学生。 SELECT Sno, Sname, Sdept FROM Student WHERE Sdept='IS'; 子查询实现：将第一步查询嵌入到第二步查询中，用以构造第二步查询的条件。 WHERE Sdept IN (SELECT…)

62 3.3 SQL的数据查询 例:查询选修了2学分课程的学生号、成绩。 SELECT sno， grade FROM SC
WHERE cno IN ( SELECT cno FROM Course WHERE credit=2)；

63 3.3 SQL的数据查询 （2）带有比较运算符的子查询 例:找出年龄超过平均年龄的学生姓名。 SELECT sname
FROM Student WHERE age ＞ (SELECT AVG(age) FROM Student)；

64 3.3 SQL的数据查询 （3）带SOME ( ANY)、ALL谓词的子查询 >SOME 大于子查询结果中的某个值 <ALL
小于子查询结果中的所有值 注意：SOME、ALL必须与关系比较符同时使用。 例：查询有一门课程成绩在90分以上的学生的姓名。 SELECT sname FROM Student WHERE sno = SOME ( SELECT DISTINCT sno FROM SC WHERE grade > 90);

65 3.3 SQL的数据查询 例：找出(平均)成绩最高的学生号。 SELECT sno FROM SC GROUP BY sno
HAVING AVG(grade) >= ALL (SELECT AVG(grade) FROM SC GROUP BY sno)； SELECT sno FROM SC WHERE AVG(grade) >=ALL ( SELECT AVG(grade) FROM SC GROUP BY sno) 正确吗？

66 3.3 SQL的数据查询 例：查询其他系中比IS系任一学生年龄大的学生名单
SELECT Sname, Sage FROM Student WHERE Sage >       (SELECT MAX(Sage)         FROM Student         WHERE Sdept='IS') AND Sdept <> 'IS'

67 3.3 SQL的数据查询 2. 条件连接查询 连接条件的一般格式为：
[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2> 当连接运算符为=时，称为等值连接。 例: 查询每个学生的情况及其选课成绩。 SELECT Student. sno, sname, sex, age, dept, cno,grade FROM Student, SC WHERE Student.sno=SC.sno;

68 3.3 SQL的数据查询 例：找出系为信息系，选课成绩为90分以上的学生的姓名、课号和成绩。 SELECT sname，cno，grade
FROM Student，SC WHERE Student.sno = SC.sno AND dept =‘信息系’ AND grade＞＝90；

69 3.3 SQL的数据查询 = SELECT sno， grade FROM SC，Course
例:查询选修了数据库的学生号、成绩 SELECT sno， grade FROM SC WHERE cno IN ( SELECT cno FROM Course WHERE cname=‘数据库’)； 学分 ？ = 子查询 SELECT sno， grade FROM SC，Course WHERE SC. cno = Course . cno AND cname=‘数据库’ ； ， Ccredit 连接 查询

70 3.3 SQL的数据查询 例：按平均成绩的降序给出所有课程都及格的学生（号、名）及其平均成绩，其中成绩统计时不包括008号考查课。
SELECT Student.sno，sname，AVG(grade) AS avg_g FROM Student，SC WHERE Student.sno=SC.sno AND cno<>’008’ GROUP BY sno HAVING MIN(grade)>=60 ORDER BY avg_g DESC ；

71 3.3 SQL的数据查询 3.自身连接查询 例：找出年龄比“王迎”同学大的同学的姓名及年龄。 SELECT s1.sname，s1.age
其他方法？ 3.自身连接查询 例：找出年龄比“王迎”同学大的同学的姓名及年龄。 SELECT s1.sname，s1.age FROM S AS s1，S AS s2 WHERE s1.age > s2.age AND s2.sname =‘王迎’； 练习：查询每一门课的间接先修课（即先修课的先修课）。 SELECT FIRST.cno,SECOND.pcno FROM Course FIRST,Course SECOND WHERE FIRST.pcno=SECOND.cno;

72 3.3 SQL的数据查询 4. FROM子句中的子查询 在FROM子句中可以使用子查询，其查询的结果表称为导出关系（Derived Relation）。 例：查出选课成绩在80分以上的女学生的姓名、课程名和成绩。 SELECT sname，cname， grade FROM (SELECT sname， cname， grade FROM Student，SC，Course WHERE Student.sno=SC.sno AND SC.cno=Course.cno AND sex＝'女') AS Temp (sname， cname，grade) WHERE grade >= 80 ； 导出关系

73 3.3 SQL的数据查询 5. 相关子查询 当一个子查询的判断条件涉及到一个来自外部查询的列时，称为相关子查询。
带存在谓词的子查询：只产生逻辑值 存在谓词EXISTS作用: 若内层查询结果非空，则外层的WHERE子句返回真值，否则返回假值。 求解相关子查询不能象求解不相关子查询那样，一次将子查询求解出来，然后求解父查询。相关子查询的内层查询由于与外层查询有关，因此必须反复求值。

74 3.3 SQL的数据查询 例：查询所有选修了1号课程的学生姓名。
SELECT sname FROM Student WHERE EXISTS ( SELECT * FROM SC WHERE Sno=Student.Sno AND cno=’1’)； 分析： 查询所有选修了1号课程的学生姓名涉及Student关系和SC关系，在Student关系中依次取每个元组的Sno值，用此Student.Sno值去检查SC关系，若SC中存在这样的元组：其SC.Sno值等于用来检查的Student.Sno值，并且其SC.Cno=‘1’，则取此Student.Sname送入结果关系。

75 3.3 SQL的数据查询 相关子查询的一般处理过程是： 首先取外层查询中Student表的第一个元组，根据它与内层查询相关的属性值（即Sno值）处理内层查询，若WHERE子句返回值为真（即内层查询结果非空），则取此元组放入结果表；然后再检查Student表的下一个元组；重复这一过程，直至Student表全部检查完毕为止。 其它方法？ SELECT sname FROM Student, SC WHERE Student.sno = SC.sno AND cno=‘1’;

76 3.3 SQL的数据查询 SQL语言中没有全称量词∨（For all）。因此对于求所有的操作，必须利用谓词演算将一个带有全称量词的谓词转换为等价的带有存在量词的谓词。 SQL语言中也没有蕴涵（Implication）逻辑运算。因此也必须利用谓词演算将一个逻辑蕴函的谓词转换为等价的带有存在量词的谓词。

77 3.3 SQL的数据查询 SELECT sname 例: 查询选修了全部课程的学生姓名。 转换成：查询这样的学生，没有一门课程是他不选修的。
例: 查询选修了全部课程的学生姓名。 转换成：查询这样的学生，没有一门课程是他不选修的。 (x)P ≡ (x(P)) SELECT sname FROM Student WHERE NOT EXISTS ( SELECT * FROM Course FROM SC WHERE sno=Student. sno AND cno=Course. cno))； 每门课都没选 某学生选某课

78 3.3 SQL的数据查询 练习：查询至少选修了学号为200101的学生选修的全部课程的学生学号。
分析：查询这样的学生，凡是200101选修的课，他都选修了。换句话说，若有一个学号为x的学生，对所有的课程y，只要学号为200101的学生选修了课程y，则x也选修了y；那么就将他的学号选出来。它所表达的语义为：不存在这样的课程y，学生200101选修了y，而学生x没有选。

79 3.3 SQL的数据查询 SELECT sno FROM Student X WHERE NOT EXISTS (SELECT *
FROM SC Y WHERE Y.sno=‘200101’ AND NOT EXISTS FROM SC Z WHERE X.sno=Z.sno AND Y.cno=Z.cno))

80 3.3 SQL的数据查询 6.集合运算 （1）UNION （并） （2）INTERSECT （交） （3）EXCEPT （差）
例：求选修了001或002号而没有选003号课程的学生号。 (SELECT sno FROM SC WHERE cno =’001’ OR cno =’002’) EXCEPT FROM SC WHERE cno =‘003’)；

81 3.3 SQL的数据查询 3.3.4 自然连接与外连接 1.自然连接(NATURAL JOIN)
自然连接的定义？ 3.3.4 自然连接与外连接 1.自然连接(NATURAL JOIN) 例：查系别为信息、课程成绩在90分以上的学生档案及其成绩情况。 SELECT * FROM Student NATURAL JOIN SC WHERE dept=‘信息’ AND grade >=90； 2. 外连接 左外连接。LEFT [OUTER] JOIN，保留左关系的所有元组。 右外连接。RIGHT [OUTER] JOIN，保留右关系的所有元组。 全外连接。FULL [OUTER] JOIN，保留左右两关系的所有元组。 FROM SC,Student?

82 3.3 SQL的数据查询 教师表：Teacher(教师号，姓名，所属大学，职称) 任职表：Post(编号，姓名，职务) 例：SELECT *
FROM Teacher FULL OUTER JOIN Post； FROM Teacher LEFT OUTER JOIN Post； FROM Teacher RIGHT OUTER JOIN Post；

83 3.3 SQL的数据查询 3.3.5 递归合并查询 递归合并语句格式： WITH RECURSIVE <临时表R> AS
UNION <涉及到R的查询> <递归结果查询> 例：对表3.5所示的航班表Flight(airline，from，to，depart，arrive) ，求出：能从一个城市飞到另一个城市的城市对集合(含直接到达和间接中转到达)。

84 3.3 SQL的数据查询 直接到达： Reach(f，t)=Flight(a，f，t，d，r) f，t在表的同一个元组中。
间接到达：Reach(f，t)=Flight(a，f，t1，d，r) AND Reach(t1，t) SQL语句： WITH RECURSIVE Reach(from，to) AS (SELECT from，to //选出直接到达城市对 FROM Flight) UNION (SELECT Flight.from，Reach.to //选出间接到达城市对 FROM Flight，Reach //递归 WHERE Flight.to=Reach.from) SELECT * FROM Reach；

85 3.3 SQL的数据查询 例：有下列所示的一棵二叉树： 求出:该子树中除子树根结点k以外的所有子孙结点的集合。 id pid name 1
b e c f d 1 6 2 4 5 3 id pid name 1 NULL a 2 b 3 c 4 d 5 e 6 f 求出:该子树中除子树根结点k以外的所有子孙结点的集合。

86 3.3 SQL的数据查询 分析：定义结果集为Tsub(id，name)：
结点k的所有子女：Tsub(id，name)=Tree(id，pid，name) AND pid=k 结点k的所有子女的孩子（k的子孙）： Tsub(id，name)=Tsub(id1，name1) AND Tree(id，id1，name) SQL: WITH RECURSIVE Tsub(id，name) AS (SELECT id，name FROM Tree //结点k的所有子女 WHERE pid=k ) UNION (SELECT Tree.id，Tree.name FROM Tsub，Tree //结点k的所有子孙 WHERE Tsub.id=Tree.pid) SELECT * FROM Tsub；

87 3.3 SQL的数据查询 3.3.6 SQL中的空值处理 1.空值的含义 值暂未知。 值不适用。 值需隐瞒。 2.空值的若干规则
（1）空值与数值型数据进行算术运算，结果为空值。 （2）空值和任何值(包括空值)进行比较运算，结果为UNKNOWN。

88 3.3 SQL的数据查询 （3）空值的布尔运算 U AND T = U ； U AND F =F ； U AND U = U ；
U OR T = T ； U OR F =U ； U OR U = U ； NOT U = U 。 空值的测试：IS NULL和IS NOT NULL。 例：查询缺少选课成绩的学生号和相应的课程号。 SELECT Sno，Cno FROM SC WHERE grade IS NULL；

89 3.3 SQL的数据查询 使用空值应注意： 空值不是常量，不能拿空值作为操作数 空值不能用来表示缺省值 大部分聚集函数忽略空值 尽量少用空值

90 3.4 SQL的数据更新 一. 插入数据 1. 插入单个元组 格式：INSERT
INTO <表名> [（<属性列1> [，<属性列2>…)] VALUES (<常量1>[,<常量2>]…); 说明：（1）若插入全部属性，则属性列可省略。 （2）表定义中说明为NOT NULL的列不能取空值。 （3）属性值与相对应的属性列的数据类型要匹配。 （4）向参照表中插入元组，关系系统自动支持： · 实体完整性 · 参照完整性 修改至此，此页未改

91 3.4 SQL的数据更新 2. 插入子查询结果 格式：INSERT
INTO <表名> [(<属性列1>[,<属性列2>…)] 子查询； 二. 修改数据 格式：UPDATE <表名> SET <列名>=<表达式>[,<列名>=<表达式>]… [WHERE <条件>]; 三. 删除数据 格式：DELETE FROM <表名>

92 3.5 SQL的视图 视图的概念及作用 1.视图与表的关系 视图（View）是从一个或多个表（视图）导出的虚表 视图是查看数据的窗口
对视图的操作最终转变为对表的操作 视图更新受到限制

93 3.5 SQL的视图 2.视图的作用 视图可以简化结构及操作 多角度、更灵活地共享数据 提高逻辑独立性 提供安全保护

94 3.5 SQL的视图 创建与使用视图 例如： CREATE VIEW myview
1. 创建视图 一般格式： CREATE VIEW <视图名> [(<视图列名>[,<视图列名>]…)] AS <子查询> [WITH CHECK OPTION]； 作用： 命名一个视图，AS子句定义每次查看视图时将看到的数据，在任何时刻，视图的数据由对其查询定义求值的结果行构成。 其中：子查询可以是任意复杂的SELECT语句，但通常不允许含有ORDER BY子句和DISTINCT短语。 WITH CHECK OPTION表示对视图进行UPDATE、 INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）。 例如： CREATE VIEW myview AS (SELECT * FROM mytable WHERE size>10) WITH CHECK OPTION; 如果要INSERT一个size小于10的元组到myview中，会被拒绝。

95 3.5 SQL的视图 WITH CHECK OPTION; 例1：建立信息院学生的视图 CREATE VIEW IS_ Student AS
SELECT sno，sname，age FROM Student WHERE dept=‘信息院’; 例2：建立信息院学生的视图，要求进行更新时仍保证该视图只有信息院的学生。 WHERE dept=‘信息院’ WITH CHECK OPTION;

96 3.5 SQL的视图 视图不仅可以建立在一个或多个基本表上，也可以建立在一个或多个已定义好的视图上，或同时建立在基本表与视图上。
例3 建立信息系选修了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';

97 3.5 SQL的视图 例4 建立信息系选修了1号课程且成绩在90分以上的学生的视图
例4 建立信息系选修了1号课程且成绩在90分以上的学生的视图 CREATE VIEW IS_S2        AS        SELECT Sno, Sname, Grade        FROM IS_S1        WHERE Grade>=90; 这里的视图IS_S2就是建立在视图IS_S1之上的。

98 3.5 SQL的视图 行列子集视图：从单个基表导出，保留了主码的视图。 例5 将Student表中所有女生记录定义为一个视图
CREATE VIEW F_Student(stdnum,name,sex,age,dept)     AS SELECT *     FROM Student     WHERE Ssex='女'; 分组视图：带有集函数和GROUP BY子句查询所定义的视图。 例6 将学生的学号及他的平均成绩定义为一个视图 假设SC表中“成绩”列Grade为数字型，否则无法求平均值。 CREAT VIEW S_G(Sno, Gavg)       AS SELECT Sno, AVG(Grade)       FROM SC       GROUP BY Sno;

99 3.5 SQL的视图 带虚拟列的视图：是指设置了一些基本表中并不存在的派生列(虚拟列)的视图。
定义基本表时，为了减少数据库中的冗余数据，表中只存放基本数据，由基本数据经过各种计算派生出的数据一般是不存储的。但由于视图中的数据并不实际存储，所以定义视图时可以根据应用的需要，设置一些派生属性列。这些派生属性由于在基本表中并不实际存在，所以有时也称他们为虚拟列，带虚拟列的视图也称为带表达式的视图。 例7 定义一个反映学生出生年份的视图 CREATE VIEW BT_S(Sno, Sname, Sbirth)        AS SELECT Sno, Sname, 1996-Sage        FROM Student;

100 3.5 SQL的视图 作用： 删除视图 撤销一个视图，但不消除数据，在基础表中的数据仍然保留。 DROP VIEW <视图名>
RESTRICT 确保只有不具有相关对象的视图（不涉及任何的约束、断言、触发器）才能被撤消。 删除视图 DROP VIEW <视图名> [CASCADE | RESTRICT] 注意： 视图删除后，视图的定义将从数据字典中自动删除。但要删除该视图导出的其他视图应用CASCADE 。

101 3.5 SQL的视图 查询视图 视图定义后，用户就可以象对基本表进行查询一样对视图进行查询了。 DBMS执行对视图的查询时，首先进行有效性检查，检查查询涉及的表、视图等是否在数据库中存在，如果存在，则从数据字典中取出查询涉及的视图的定义，把定义中的子查询和用户对视图的查询结合起来，转换成对基本表的查询，然后再执行这个经过修正的查询。将对视图的查询转换为对基本表的查询的过程称为视图的消解（View Resolution）。

102 3.5 SQL的视图 CREATE VIEW IS_ Student AS SELECT sno，sname，age
FROM Student WHERE dept=‘信息院’; 3.5 SQL的视图 例1 在信息系学生的视图中找出年龄小于20岁的学生 SELECT Sno, Sage FROM IS_Student WHERE Sage<20; 视图是定义在基本上的虚表，它可以和其他基本表一起使用，实现连接查询或嵌套查询。 例2 查询信息系选修了1号课程的学生 SELECT Sno, Sname FROM IS_Student, SC WHERE IS_Student.Sno=SC.Sno AND       SC.Cno='1'; 本查询涉及虚表IS_Student和基本表SC，通过这两个表的连接来完成用户请求。 SELECT sno, sage FROM Student WHERE dept=‘信息院’ AND sage<20; SELECT sno, sname FROM Student, SC WHERE dept=‘信息院’ AND Student.sno=SC.sno AND SC.cno=‘1’;

103 3.5 SQL的视图 更新视图 对视图进行的增、删、改都是变换到基本表上进行。 例：删除IS_Student视图中的学生200029
DELETE FROM IS_Student WHERE sno=‘200029’; 变为 DELETE FROM Student WHERE sno=‘200029’ AND dept=‘信息院’; 并不是所有的视图都可以更新的。

104 3.5 SQL的视图 (??+??+??)/3=92 不能更新的视图，例如分组视图
CREAT VIEW S_G(Sno, Gavg)       AS SELECT Sno, AVG(Grade)       FROM SC       GROUP BY Sno; 3.5 SQL的视图 不能更新的视图，例如分组视图 学生1 课程1 85 平均成绩 学生1 课程2 95 学生1 90 ( )/3=90 学生1 课程3 90 92 (??+??+??)/3=92

105 3.5 SQL的视图 目前DBMS中一般允许对行列子集视图进行更新，各系统对视图的更新有不同的规定。 DB2 连接产生的视图不能更新
表达式列或常数列不能更新，视图不能插入 包含聚集函数、分组子句、去重关键词的不能更新 ......

106 3.6 过程化SQL 过程化SQL是为了扩展SQL作为一种程序语言的能力而出现的，增加了基本SQL处理业务流程的功能

107 3.6 过程化SQL 过程化SQL的概念 1.永久存储模块 为使SQL适合编程，扩充了流程控制、函数等概念
这些扩充被放在永久存储模块扩展包中 程序块长期被保存，用户需要时即可执行

108 3.6 过程化SQL 关于程序块 SQL/PSM的好处： 每个模块可包含一个或多个SQL语句 每个模块作为一个模式对象存在于一个SQL模式中
每个模块有名字、授权号、字符集、临时表等声明 SQL/PSM的好处： 在SQL中引入过程 可接受I/O及直接返回值 用SQL语句组，提高性能

109 3.6 过程化SQL 2.过程化SQL 简称PL/SQL（Procedural Language/SQL）
很多DBMS都支持PL/SQL，Oracle的过程化语言就叫PL/SQL

110 3.6 过程化SQL 3.存储过程 SQL3标准中允许自定义存储模块，分为存储过程和函数 自定义的方法 用外部程序设计语言
用SQL/PSM有关的过程扩展语句：即存储过程，安全、高效

111 3.6 过程化SQL 过程化SQL编程（Oracle） 1.PL/SQL块结构
DECLARE <说明的变量、常量、游标等> //声明部分 BEGIN <SQL语句、PL／SQL的流程控制语句> //执行部分 [EXCEPTION <异常处理部分> ] //异常处理部分 END； 声明：在块中使用的所有变量、常量、游标都必须在此定义 执行：可以用NULL语句，也可以嵌套其他块 异常：对执行过程中可能产生的异常进行处理

112 <变量名>[CONSTANT]<数据类型>[NOT NULL][:=|DEFAULT<表达式>]
3.6 过程化SQL 2.变量类型与赋值 PL/SQL中可用的数据类型和Oracle中的类型一致 (1)变量 <变量名>[CONSTANT]<数据类型>[NOT NULL][:=|DEFAULT<表达式>] 赋值： 直接赋值：<变量名>:=<表达式> x:=200; 通过SELECT INTO(FETCH INTO)赋值 例子：st_name和st_age是变量 SELECT sname,sage INTO st_name,st_age FROM Student WHERE sno=200128;

113 3.6 过程化SQL (2)常量 和变量的定义类似，必须用CONSTANT，且定义时赋值，之后不能改变值
例如：定义一个名叫ZERO_V、数据类型为NUMBER、值为0的常量 ZERO_V CONSTANT NUMBER:=0; PL/SQL中有一系列操作符，分类为： 算术操作符：+、-、*、/ 关系操作符：>、<、= 比较操作符：IS NULL、LIKE、BETWEEN、IN 逻辑操作符：AND、NOT、OR

114 3. 控制结构 主要有条件控制和循环控制语句： 1) 条件控制语句
条件结构有三种形式：IF—THEN，IF—THEN—ELSE和嵌套的IF语句。 常用: IF <条件> THEN <语句1> ；ELSE <语句2> END IF ； 2) 循环控制语句 有三种循环结构，提供EXIT、BREAK等循环结束语句。形式： ① LOOP <循环体> END LOOP； ② WHILE <条件> LOOP <循环体> END LOOP； ③ FOR <循环变量> IN <下界> .. <上界> LOOP <循环体> END LOOP

115 3. 控制结构 例：通过循环变量i来控制n增加的次数并输出结果。 SET SERVEROUTPUT ON DECLARE
n INTEGER :=80；i INTEGER :=0； BEGIN FOR i IN 1..10 LOOP n:=n+1； END LOOP； DBMS_OUTPUT.PUT_LINE('n的值：'||TO_CHAR(n)) END；

116 PL/SQL程序块分类 分为匿名块和命名块 命名块可分为： 匿名块每次执行都要进行编译，不存储 命名块保存在DB中，反复调用，只编译一次
存储过程 函数 包 触发器

117 3.6 过程化SQL 存储过程 1.概念及特点 存储过程是一个PL/SQL程序块，可有多个参数作为输入或输出源。它不能由SQL语句直接使用，只能通过EXECUT命令或PL/SQL程序块内部调用。 优点： 运行效率高：不需实时编译 降低网络通信量：一个名字代替多个语句 方便实施用户规则：用户规则独立表示

118 3.6 过程化SQL 2.建立和执行 可通过SQL语句创建、重命名、执行和删除存储过程。 1)创建存储过程
//过程首部 CREATEP OR REPLACE PROCEDURE <过程名> [(<参数1>[,<参数2>,...])] AS <PL/SQL块>； //存储过程体 参数分为输入参数、输出参数、输入输出参数三种类型 2)调用存储过程 CALL PROCEDURE<过程名>([实参1，实参2…])

119 3.6 过程化SQL 例：设表:product(pno,…standard_price,sale_price)
建立一设置出售价格的过程, 标准价为400美元或更高的产品降价10％，低于400美元的产品降价15％。 CREATE OR REPLACE PROCDEDURE p-sale AS BEGIN UPDATE PRODUCT SET sale_price=0.90*standard_price WHERE standard_price>=400； SET sale_price=0.85*standard_price WHERE standard_price<400； END；

120 3.6 过程化SQL 存储过程 游标可以看成是分配给用户的一块数据缓冲区，将SQL（SELECT）的结果放在其中，便于程序从中逐一取出结果进行处理。 1.游标的定义 游标分为两种：显式游标和隐式游标 显式游标：需要由程序员定义、命名 隐式游标：系统自动为DML语句定义

121 3.6 过程化SQL 显式游标可以认为是一系列同类型行的数组，需要在DECLARE段定义。 语法： CURSOR <游标名> IS <SELECT语句>; 游标的SELECT语句中可以使用WHERE、ORDER BY、GROUP BY等。

122 3.6 过程化SQL 2.游标的使用 使用游标时，要先打开游标： OPEN <游标名>;
然后用FETCH语句从游标中取记录到变量中： FETCH <游标名> INTO 变量1,变量2,......; FETCH <游标名> INTO 记录变量; 最后关闭游标： CLOSE <游标名>;

123 3.6 过程化SQL 3.游标的属性 游标有四个属性用来表示其状态，均为只读属性： <游标名>%<属性名> 游标属性
用途 <游标名>%ISOPEN TRUE表示游标已打开 <游标名>%NOTFOUND TRUE表示最近一次从游标中没有取到结果 <游标名>%FOUND TRUE表示最近一次从游标中取到了结果 <游标名>%ROWCOUNT 表示当前为止返回的记录数，初值为0

124 3.6 过程化SQL 例：设emp(empno,ename,sal,address,deptno)用游标从中取出某一个部门的职工姓名及工资
DECLARE v_deptno emp.deptno%type:=&p_deptno; CURSOR emp_cursor IS SELECT ename,sal FROM emp WHERE deptno=v_deptno; BEGIN FOR emp_record IN emp_cursor LOOP INSERT INTO temp(ename,sal) VALUES(emp_record.ename, emp_record.sal); END LOOP; COMMIT; END; 记录变量，隐式

125 3.6 过程化SQL 4.通过游标修改数据 PL/SQL可通过游标对刚刚取出的那条记录进行删除或更新：
定义游标时，加上FOR UPDATE子句，表示要对表加锁； 在UPDATE或DELETE语句中加上WHERE CURRENT OF子句，指定将游标中取出的当前行进行更新或删除

126 3.6 过程化SQL 1)定义对表加锁的游标 CURSOR <游标名> IS SELECT <目标列表>
FROM <表> WHERE <条件> FOR UPDATE [OF <列>][NOWAIT] FOR UPDATE表示对表加锁 NOWAIT表示如果表已经被别的会话锁住，就不等待，直接返回错误

127 3.6 过程化SQL 2)更新或修改 DELETE FROM <表> WHERE CURRENT OF <游标名>;
UPDATE <表> SET <列1>=<值1>[,<列2>=<值2>…] WHERE CURRENT OF表示更新或修改是在指定游标当前行上进行。

128 3.7 复杂数据类型 SQL3中扩展了一些新的数据类型： LOB：大对象 BOOLEAN：布尔型 ARRAY：聚合（数组）
ROW：行类型（结构） 自定义域类型 REF类型（引用/参照） 结构类型：复杂的结构 区分类型：区分两个相同数据类型属性的类别 ADT：抽象数据类型

129 <数据类型> ARRAY [<无符号整数>]
3.7 复杂数据类型 构造数据类型 1.聚合类型 SQL中的聚合类型是数组，是同类元素的集合，且只能是一维数组。定义方式： <数据类型> ARRAY [<无符号整数>] ARRAY后的方括号不是表示“可选”，而是表示数组长度定义 “无符号整数”指定了数组元素的最大数目

130 <数组变量名>[<下标>]
3.7 复杂数据类型 引用数组元素的方法： <数组变量名>[<下标>] 例： CREATE TABLE mailout (name CHAR(8), address CHAR(20) ARRAY[3]); INSERT INTO mailout(name,address) VALUES (‘Wang’, ARRAY[‘20 Hongsang Road’,’Wuhan’,’430079’]); SELECT address[3] FROM mailout;

131 ROW (<列名> <数据类型>[{, <列名> <数据类型>}…]
3.7 复杂数据类型 2.行类型 SQL中的行类型用于将一个完整的行存储在变量中，同一个表中的所有行的行类型相同。定义方式： ROW (<列名> <数据类型>[{, <列名> <数据类型>}…] 行类型变量中列名的引用： <行变量>.<列名>

132 3.7 复杂数据类型 例： CREATE TABLE lineage (name CHAR(8), status CHAR(10),
lastone ROW(baby CHAR(8), birth DATE)); ….//插入数据 SELECT name,status FROM lineage WHERE lastone.baby=‘Liming’;

133 3.7 复杂数据类型 大对象数据类型 LOB（Large Object）主要用于存储多媒体数据，如大型文档、视频、音频等。其长度可达到2G字节以上。 SQL3中提供了： CLOB：字符型大对象 BLOB：二进制大对象 LOB可用于定义表中的列，可进行大对象的插入、删除、修改、查询，以及赋值、比较、连接、取子串等运算。

134 3.7 复杂数据类型 例： CREATE TABLE mail (origin VARCHAR(20),
address VARCHAR(20), arrival DATE, message BLOB(10M));

135 3.7 复杂数据类型 大对象一般被用于外部应用，通过SQL对它们进行全体检索是没有任何意义的。
通常是根据非LOB属性找到行，然后对行中的LOB数据进行处理，这种处理通过“定位器”（Locator）来完成。 主语言通过定位器，将大对象分成小片来进行操作。

136 第三章 关系数据库语言SQL 要点: 1. SQL的特点、功能 2. SQL查询等操作 3. 视图的概念、定义及作用
本章练习：