第3章 数据库语言SQL 3.1 SQL语言概况 3.2 SQL数据定义语言 3.3 SQL数据查询语言 3.4 SQL数据操纵语言

Presentation on theme: "第3章 数据库语言SQL 3.1 SQL语言概况 3.2 SQL数据定义语言 3.3 SQL数据查询语言 3.4 SQL数据操纵语言"— Presentation transcript:

1 第3章 数据库语言SQL 3.1 SQL语言概况 3.2 SQL数据定义语言 3.3 SQL数据查询语言 3.4 SQL数据操纵语言

2 3.1 SQL语言概况 SQL简介 结构化查询语言SQL（Structured Query Language）是一种介于关系代数与关系演算之间的语言，其功能包括查询、操纵、定义和控制四个方面，是一个通用的、功能极强的关系数据库语言。目前已成为关系数据库的标准语言。 SQL语言的版本包括：SQL-89，SQL-92，SQL3。 SQL特点 SQL语言之所以能够为用户和业界所接受，成为国际标准，是因为它是一个综合的、通用的、功能极强同时又简洁易学的语言。 SQL语言集数据查询（data query）、数据操纵（data manipulation）、数据定义（data definition）和数据控制（data control）功能于一体，充分体现了关系数据语言的特点和优点。

3 3.1 SQL语言概况 3. SQL特点(续) 综合统一 参见 高度非过程化 参见 面向集合的操作方式 参见
以同一种语法结构提供两种使用方式 参见 语言简洁，易学易用 参见

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

5 3.2 SQL数据定义语言 定义基本表 一般格式如下： CREATE TABLE <表名>
(<列名><数据类型> [列级完整性约束条件] [， <列名> <数据类型> [列级完整性约束条件]...) [, <表级完整性约束条件>]; 语法图参见 其中<表名>是所要定义的基本表的名字，它可以由一个或多个属性（列）组成。建表的同时通常还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中，当用户操作表中数据时由DBMS自动检查该操作是否违背这些完整性约束条件。如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。

6 3.2 SQL数据定义语言 例1：创建STUDENT,COURSE,SC三个基表 CREATE TABLE STUDENT
(SNO CHAR(7) NOT NULL, SNAME VARCHAR(10) NOT NULL, SEX CHAR(1) NOT NULL, BDATE DATE NOT NULL, HEIGHT DEC(5,2) DEFAULT 00.0, PRIMARY KEY(SNO)); //定义主键 CREATE TABLE COURSE (CNO CHAR(6) NOT NULL, CNAME VARCHAR(30) NOT NULL, LHOUR SMALLINT NOT NULL, CREDIT DEC(1,0) NOT NULL, SEMESTER CHAR(2) NOT NULL, PRIMARY KEY(CNO)); //定义主键

7 3.2 SQL数据定义语言 例1：创建STUDENT,COURSE,SC三个基表 CREATE TABLE SC
(SNO CHAR(7) NOT NULL, CNO CHAR(6) NOT NULL, GRADE DEC(4,1) DEFAULT NULL, PRIMARY KEY(SNO,CNO), //定义主键 FOREIGN KEY(SNO) //定义外键 REFERENCES STUDENT ON DELETE CASCADE, FOREIGN KEY(CNO) //定义外键 REFERENCES COURSE ON DELETE RESTRICT)

8 3.2 SQL数据定义语言 定义表的各个属性时需要指明其数据类型及长度。不同的数据库系统支持的数据类型不完全相同，例如IBM DB2 SQL主要支持以下数据类型： SMALLINT 半字长二进制整数。 INTEGER或INT 全字长二进制整数。 DECIMAL(p[,q])或DEC(p[,q]) 压缩十进制数，共p位，其中小 数点后有q位。0≤q≤p≤15，q=0时可 以省略。 FLOAT 双字长浮点数。 CHARTER(n)或CHAR(n) 长度为n的定长字符串。 VARCHAR(n) 最大长度为n的变长字符串。 GRAPHIC(n) 长度为n的定长图形字符串。 VARGRAPHIC(n) 最大长度为n的变长图形字符串。 DATE 日期型，格式为YYYY-MM-DD。 TIME时间型， 格式为HH.MM.SS。 TIMESTAMP 日期加时间。

9 3.2 SQL数据定义语言 修改基本表 一般格式为： ALTER TABLE <表名>
[ADD <新列名> <数据类型> [完整性约束]] [DROP <完整性约束名>] [MODIFY<列名> <数据类型>]; 　 其中<表名>指定需要修改的基本表，ADD子句用于增加新列和新的完整性约束条件，DROP子句用于删除指定的完整性约束条件，MODIFY子句用于修改原有的列定义。

10 3.2 SQL数据定义语言 ALTER TABLE STUDENT ADD SCOME DATE;
例2:向STUDENT表增加“入学时间” (SCOME)列，其数据类型为日期型 ALTER TABLE STUDENT ADD SCOME DATE; 不论基本表中原来是否已有数据，新增加的列一律为空值。 例3:将学生姓名SNAME的长度增加到30 ALTER TABLE STUDENT MODIFY SNAME VARCHAR(30); 修改原有的列定义有可能会破坏已有数据。 例4： 删除关于学号为主键的约束 ALTER TABLE STUDENT DROP PRIMARY KEY;

11 3.2 SQL数据定义语言 删除基本表 一般格式为： DROP TABLE <表名> 例5： 删除STUDENT表
基本表定义一旦删除，表中的数据、在此表上建立的索引都将自动被删除掉，而建立在此表上的视图虽仍然保留，但已无法引用。因此执行删除操作一定要格外小心。

12 3.2 SQL数据定义语言 建立索引 一般格式为： CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
ON <表名> ( <列名>[<次序>][,<列名>[<次序>]]...); 其中，<表名>指定要建索引的基本表的名字。索引可以建在该表的一列或多列上，各列名之间用逗号分隔。每个<列名>后面还可以用<次序>指定索引值的排列次序，包括ASC（升序）和DESC（降序）两种，缺省值为ASC。

13 3.2 SQL数据定义语言 例6： 为学生-课程数据库中的Student、Couse、SC三个表建立索引。其中Student表按学号升序建唯一索引，Couse表按课程号升序建唯一索引，Sno、Cno表按学号升序和课程号降序建唯一索引。 CREATE UNIQUE INDEX Stusno ON Student(Sno); CREATE UNIQUE INDEX Coucno ON Couse(Cno); CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC); 删除索引 一般格式 DROP INDEX <索引名>

14 3.3 SQL数据查询语言 基本查询语句 一般格式： SELECT [ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]... FROM <表名或视图名>[,<表名或视图名>] ... [WHERE <条件表达式>] [GROUP BY <列名1>[HAVING <条件表达式>]] [ORDER BY <列名2> [ASC|DESC]]; 语法图 整个SELECT语句的含义是，根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。如果有GROUP子句，则将结果按<列名1>的值进行分组，该属性列值相等的元组为一个组，每个组产生结果表中的一条记录。通常会在每组中作用集函数。如果GROUP子句带HAVING短语，则只有满足指定条件的组才予输出。如果有ORDER子句，则结果表还要按<列名2>的值的升序或降序排序。

15 3.3 SQL数据查询语言 单表查询 选择表中的若干列 1) 查询指定列 例1: 查询全体学生的学号与姓名 SELECT Sno,Sname
FROM Student; 说明：<目标列表达式> 中各个列的先后顺序可以与表中的顺序不一致。也就是说，用户在查询时可以根据应用的需要改变列的显示顺序。 例2: 查询全体学生的姓名、学号、所在系 SELECT Sname, Sno, Sdept 说明：这时结果表中的列的顺序与基表中不同，是按查询要求，先列出姓名属性，然后再列学号属性和所在系属性。

16 3.3 SQL数据查询语言 2) 查询全部列 3) 查询经过计算的值 例3：查询全体学生的详细记录
SELECT * FROM Student; 说明：该SELECT语句实际上是无条件地把Student表的全部信息都查询出来，所以也称为全表查询，这是最简单的一种查询。 3) 查询经过计算的值 SELECT子句的<目标列表达式>不仅可以是表中的属性列，也可以是有关表达式，即可以将查询出来的属性列经过一定的计算后列出结果。

17 3.3 SQL数据查询语言 例4:查全体学生的姓名及其出生年份
SELECT Sname, 1996-Sage FROM Student; <目标列表达式>不仅可以是算术表达式，还可以是字符串常量、函数等 例5：查全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名 SELECT Sname, 'Year of Birth:', 1996-Sage, ISLOWER(Sdept) FROM Student； 用户可以通过指定别名来改变查询结果的列标题，这对于含算术表达式、常量、函数名的目标列表达式尤为有用。例如对于上例，可以如下定义列别名： 例6： SELECT Sname NAME, 'Year of Birth:‘ BIRTH, 1996-Sage BIRTHDAY, ISLOWER(Sdept) DEPARTMENT FROM Student; (软件学院 3月6日)

18 3.3 SQL数据查询语言 (2) 选择表中的若干元组 1). 消除取值重复的行 例7: 查所有选修过课的学生的学号 SELECT Sno
 1). 消除取值重复的行 例7: 查所有选修过课的学生的学号 SELECT Sno FROM SC; 假设SC表中有下列数据  Sno      Cno    Grade       1             2             3             2             3       80 　　

19 3.3 SQL数据查询语言  例7 (续)：执行上面的SELECT语句后，结果为：   Sno    95001       该查询结果里包含了许多重复的行。如果想去掉结果表中的重复行，必须指定DISTINCT短语： SELECT DISTINCT Sno FROM SC; 　 执行结果为：  Sno    

20 3.3 SQL数据查询语言 (2) 选择表中的若干元组 1) 查询满足条件的元组
 1) 查询满足条件的元组 查询满足指定条件的元组可以通过WHERE子句实现。WHERE子句常用的查询条件如表3-3所示。 表3-3 常用的查询条件 查询条件 谓 词 比较 上述比较运算符 确定范围 BETWEEN AND, NOT BETWEEN AND 确定集合 IN, NOT IN 字符匹配 LIKE, NOT LIKE 空值 IS NULL, IS NOT NULL 多重条件 AND, OR

21 3.3 SQL数据查询语言 2).查询满足条件的元组 例8: 查计算机系全体学生的名单 SELECT Sname FROM Student
 2).查询满足条件的元组 例8: 查计算机系全体学生的名单 SELECT Sname FROM Student WHERE Sdept = 'CS'; 例9: 查所有年龄在20岁以下的学生姓名及其年龄 SELECT Sname, Sage FROM Student WHERE Sage <20; 或 SELECT Sname, Sage FROM Student WHERE NOT Sage>= 20;

22 3.3 SQL数据查询语言 例10:查考试成绩有不及格的学生的学号 例11: 查询年龄在20至23岁之间的学生的姓名、系别、和年龄
SELECT DISTINCT Sno FROM Course WHERE Grade <60; 这里使用了DISTINCT短语，当一个学生有多门课程不及格，他的学号也只列一次。 例11: 查询年龄在20至23岁之间的学生的姓名、系别、和年龄 SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23; 与BETWEEN...AND...相对的谓词是NOT BETWEEN...AND...。

23 3.3 SQL数据查询语言 例12:查询年龄不在20至23岁之间的学生姓名、系别和年龄。 SELECT Sname, Sdept, Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23; 例13: 查信息系（IS）、数学系（MA）和计算机科学系（CS）的学生的姓名和性别 SELECT Sname, Ssex 　 FROM Student 　 WHERE Sdept IN ('IS', 'MA', 'CS') 与IN相对的谓词是NOT IN，用于查找属性值不属于指定集合的元组。

24 3.3 SQL数据查询语言 例13:查既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别
SELECT Sname, Ssex 　 FROM Student 　 WHERE Sdept NOT IN ('IS', 'MA', 'CS') ; 谓词LIKE可以用来进行字符串的匹配。其一般语法格式如下： [NOT] LIKE ‘<匹配串>’ [ESCAPE ‘<换码字符>’]  其含义是查找指定的属性列值与<匹配串>相匹配的元组。<匹配串>可以是一个完整的字符串，也可以含有通配符%和_。%(百分号) 代表任意长度（长度可以为0）的字符串。_(下横线) 代表任意单个字符。

25 3.3 SQL数据查询语言 例14: 查所有姓刘的学生的姓名、学号和性别 SELECT Sname, Sno, Ssex FROM Student WHERE Sname LIKE ‘刘%’; 例15: 查姓“欧阳”且全名为三个汉字的学生的姓名 SELECT Sname FROM Student WHERE Sname LIKE ‘欧阳__’; 注意，由于一个汉字占两个字符的位置，所以匹配串欧阳后面需要跟２个_。

26 3.3 SQL数据查询语言 例16: 查名字中第二字为“阳”字的学生的姓名和学号
SELECT Sname, Sno FROM Student WHERE Sname LIKE '__阳%'; 例17: 查所以不姓刘的学生姓名 SELECT Sname, Sno, Ssex FROM Student WHERE Sname NOT LIKE ‘刘%’; 如果用户要查询的匹配字符串本身就含有%或_，比如要查名字为DB_Design的课程的学分，应如何实现呢？这时就要使用ESCAPE ‘ ’短语对通配符进行转义了。 (计算机 月7日）

27 3.3 SQL数据查询语言 SELECT * WHERE Cname LIKE ’DB\_%i__’ ESCAPE ’\’;
例18: 查DB_Design课程的课程号和学分 SELECT Cno, Ccredit FROM Course WHERE Cname LIKE ’DB\_Design’ ESCAPE ’\’ 说明：ESCAPE ’\’短语表示\为换码字符，这样匹配串中紧跟在\后面的字符”_”不再具有通配符的含义，而是取其本身含义，被转义为普通的”_”字符。 例19：查以”DB_”开头，且倒数第三个字符为i的课程的详细情况 SELECT * WHERE Cname LIKE ’DB\_%i__’ ESCAPE ’\’;

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

29 3.3 SQL数据查询语言 例22:查CS系年龄在20岁以下的学生姓名
SELECT Sname FROM Student WHERE Sdept='CS' AND Sage<20; 例12 中的IN谓词实际上是多个OR运算符的缩写，因此例12中的查询也可以用OR运算符写成如下等价形式： SELECT Sname, Ssex 　 FROM Student 　 WHERE Sdept='IS' OR Sdept='MA' OR Sdept='CS';

30 3.3 SQL数据查询语言 (3) 对查询结果排序 如果没有指定查询结果的显示顺序，DBMS将按其最方便的顺序（通常是元组在表中的先后顺序）输出查询结果。用户也可以用ORDER BY子句指定按照一个或多个属性列的升序（ASC）或降序（DESC）重新排列查询结果，其中升序ASC为缺省值。 例23:查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列 SELECT Sno, Grade FROM SC WHERE Cno='1' ORDER BY Grade DESC; 前面已经提到，可能有些学生选修了3号课程后没有参加考试，即成绩列为空值。用ORDER BY子句对查询结果按成绩排序时，若按升序排，成绩为空值的元组将最后显示，若按降序排，成绩为空值的元组将最先显示。

31 3.3 SQL数据查询语言 (4) 使用集函数 例24: 查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列
SELECT * FROM Student ORDER BY Sdept, Sage DESC; (4) 使用集函数 为了进一步方便用户，增强检索功能，SQL提供了许多集函数，主要包括： COUNT([DISTINCT|ALL] *) 统计元组个数 COUNT([DISTINCT|ALL] <列名>) 统计一列中值的个数 SUM([DISTINCT|ALL] <列名>) 计算一列值的总和（此列必须是数值型） AVG([DISTINCT|ALL] <列名>) 计算一列值的平均值（此列必须是数值型） MAX([DISTINCT|ALL] <列名>) 求一列值中的最大值 MIN([DISTINCT|ALL] <列名>) 求一列值中的最小值

32 3.3 SQL数据查询语言 例25:查询学生总人数 SELECT COUNT(*) FROM Student;
例26: 查询选修了课程的学生人数 SELECT COUNT(DISTINCT Sno) FROM SC; 说明：学生每选修一门课，在SC中都有一条相应的记录，而一个学生一般都要选修多门课程，为避免重复计算学生人数，必须在COUNT函数中用DISTINCT短语。 例27：计算1号课程的学生平均成绩 SELECT AVG(Grade) FROM SC WHERE Cno='1';

33 3.3 SQL数据查询语言 例28：查询学习1号课程的学生最高分数 SELECT MAX(Grade) FROM SC
WHERE Cno='1'; (5) 对查询结果分组 GROUP BY子句可以将查询结果表的各行按一列或多列取值相等的原则进行分组。对查询结果分组的目的是为了细化集函数的作用对象。如果未对查询结果分组，集函数将作用于整个查询结果，即整个查询结果只有一个函数值。否则，集函数将作用于每一个组，即每一组都有一个函数值。 例29：查询各个课程号与相应的选课人数 SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno; 如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING短语指定筛选条件。

34 3.3 SQL数据查询语言 例30：查询信息系选修了3门以上课程的学生的学号 SELECT Sno FROM SC
WHERE SNO IN (SELECT SNO FROM STUDENTS WHERE Sdept='IS) GROUP BY Sno HAVING COUNT(*)>3;

35 3.3 SQL数据查询语言 连接查询 一个数据库中的多个表之间一般都存在某种内在联系，它们共同提供有用的信息。前面的查询都是针对一个表进行的。若一个查询同时涉及两个以上的表，则称之为连接查询。连接查询主要包括等值连接、非等值连接查询、自身连接查询（连接 二、自身连接）、外连接查询（连接 三、外连接）和复合条件连接查询（连接 四、复合条件连接）。 （1） 等值连接与非等值连接查询 用来连接两个表的条件称为连接条件或连接谓词，其一般格式为： [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2> 　　 其中比较运算符主要有：=、>、<、>=、<=、!=<=、!= 此外连接谓词词还可以使用下面形式： [<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>   当连接运算符为=时，称为等值连接。使用其它运算符称为非等值连接。 详细说明

36 3.3 SQL数据查询语言 例32：查询每个学生及其选修课程的情况
SELECT Student.*, SC.* FROM Student, SC WHERE Student.Sno=SC.Sno 连接运算中有两种特殊情况，一种称为卡氏积连接，另一种称为自然连接。卡氏积是不带连接谓词的连接。两个表的卡氏积即是两表中元组的交叉乘积，也即其中一表中的每一元组都要与另一表中的每一元组作拼接，因此结果表往往很大。 如果是按照两个表中的相同属性进行等值连接，且目标列中去掉了重复的属性列，但保留了所有不重复的属性列，则称之为自然连接。 例33：自然连接Student和SC表 SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM Student, SC WHERE Student.Sno=SC.Sno;

37 3.3 SQL数据查询语言 (2) 自连接 连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，这种连接称为表的自连接。 例34:查询每一门课的间接先修课（即先修课的先修课） 完成该查询的SQL语句为： SELECT FIRST.Cno, SECOND. Pcno FROM Course FIRST, Course SECOND WHERE FIRST.Pcno=SECOND.Cno; (3) 外连接 在通常的连接操作中，只有满足连接条件的元组才能作为结果输出，如在例32和例33的结果表中没有关于95003和95004两个学生的信息，原因在于他们没有选课，在SC表中没有相应的元组。但是有时我们想以Student表为主体列出每个学生的基本情况及其选课情况，若某个学生没有选课，则只输出其基本情况信息，其选课信息为空值即可，这时就需要使用外连接（Outer Join）。外连接的运算符通常为*。有的关系数据库中也用+。这样，我们就可以如下改写例33：

38 3.3 SQL数据查询语言 SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM Student, SC WHERE Student.Sno=SC.Sno(*); 上例中外连接符*出现在连接运算符的右边，所以也称其为右外连接。相应地，如果外连接符出现在连接运算符的左边，则称为左外连接。 (4) 复合条件连接 上面各个连接查询中，WHERE子句中只有一个条件，即用于连接两个表的谓词。WHERE子句中有多个条件的连接操作，称为复合条件连接。 例35:查询选修2号课程且成绩在90分以上的所有学生 SELECT Student.Sno, Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno='2' AND SC.Grade>90;

39 3.3 SQL数据查询语言 连接操作除了可以是两表连接，一个表与其自身连接外，还可以是两个以上的表进行连接，后者通常称为多表连接。
例36:查询每个学生及其选修的课程名其及成绩 SELECT Student.Sno, Sname, Course.Cname, SC.Grade FROM Student, SC, Course WHERE Student.Sno=SC.Sno and SC.Cno=Course.Cno;

40 3.3 SQL数据查询语言 3. 嵌套查询 在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询或子查询。例如： SELECT Sname FROM Student WHERE Sno IN       SELECT Sno       FROM SC       WHERE Cno='2';

41 3.3 SQL数据查询语言 (1). 带有IN谓词的子查询
带有IN谓词的子查询是指父查询与子查询之间用IN进行连接，判断某个属性列值是否在子查询的结果中。由于在嵌套查询中，子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最经常使用的谓词。 例37 :查询与“刘晨”在同一个系学习的学生 SELECT Sno, Sname, Sdept FROM Student WHERE Sdept IN 　       SELECT Sdept       FROM Student       WHERE Sname=‘刘晨’; 　 本例中的查询也可以用我们前面学过的表的自身连接查询来完成： SELECT Sno, Sname, Sdept FROM Student S1, Student S2 WHERE S1.Sdept = S2.Sdept AND S2.Sname='刘晨';

42 3.3 SQL数据查询语言 例38:查询选修了课程名为‘信息系统’的学生学号和姓名
SELECT Sno, Sname FROM Student WHERE Sno IN     ( SELECT Sno       FROM SC       WHERE Cno IN           (SELECT Cno             FROM Course             WHERE Cname=‘信息系统’)); 本查询同样可以用连接查询实现： SELECT Sno, Sname FROM Student, SC, Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Course.Cname='信息系统';

43 3.3 SQL数据查询语言 (2) 带有比较运算符的子查询
带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单值时，可以用>、 <、 =、 >=、<=、!=或<>等比较运算符。 (3) 带有ANY或ALL谓词的子查询 子查询返回单值时可以用比较运算符。而使用ANY或ALL谓词时则必须同时使用比较运算符。 例39 :查询其他系中比IS系任一学生年龄小的学生名单 SELECT Sname, Sage FROM Student WHERE Sage < ANY (SELECT Sage        FROM Student       WHERE Sdept="IS" )  AND Sdept <> 'IS'  ORDER BY Sage DESC;

44 3.3 SQL数据查询语言 本查询实际上也可以用集函数实现。 SELECT Sname, Sage FROM Student WHERE Sage <  (SELECT MAX(Sage)         FROM Student          WHERE Sdept='IS') AND Sdept <> 'IS' ORDER BY Sage DESC; (4) 带有EXISTS谓词的子查询 EXISTS代表存在量词彐。带有EXISTS谓词的子查询不返回任何实际数据，它只产生逻辑真值“true"或逻辑假值“false"。 例40: 查询所有选修了1号课程的学生姓名 SELECT Sname FROM Student S WHERE EXISTS  (SELECT *         FROM SC          WHERE Sno=S.Sno AND Cno='1'); 使用存在量词EXISTS后，若内层查询结果非空，则外层的WHERE子句返回真值，否则返回假值 (软件学院 3月18日）

45 3.3 SQL数据查询语言 例41:查询所有未修1号课程的学生姓名
SELECT Sname FROM Student WHERE NOT EXISTS       (SELECT *         FROM SC         WHERE Sno=Student.Sno AND Cno='1'); 例42: 查询选修了全部课程的学生姓名 SELECT Sname FROM Student S WHERE NOT EXISTS       (SELECT *         FROM Course C         WHERE NOT EXISTS               (SELECT *                 FROM SC                 WHERE S.Sno=SC.Sno AND C.Cno=C.Cno));

46 3.3 SQL数据查询语言 例43:查询至少选修了学生95002选修的全部课程的学生号码。 SELECT DISTINCT Sno
FROM SC SCX WHERE NOT EXISTS ( SELECT *         FROM SC SCY         WHERE SCY.Sno='95002' AND             NOT EXISTS                  (SELECT *                    FROM SC SCZ                    WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno) );

47 3.3 SQL数据查询语言 4. 集合查询 每一个SELECT语句都能获得一个或一组元组。若要把多个SELECT语句的结果合并为一个结果,可用集合操作来完成。集合操作主要包括并操作UNION、交操作INTERSECT和差操作MINUS。 例45：查询计算机科学系的学生及年龄不大于19岁的学生 SELECT * FROM Student WHERE Sdept='CS' UNION SELECT * FROM Student WHERE Sage<=19；

48 3.4 SQL数据操纵语言 Insert语句 (1) 插入单个元组
插入单个元组的INSERT语句的格式为： INSERT INTO <表名> [(<属性列1>[,<属性列2>...)] VALUES (<常量1> [,<常量2>]...) 例1:将一个新学生记录（学号：95020；姓名：陈冬；性别：男；所在系： IS；年龄：18岁）插入Student表中 INSERT INTO Student VALUES ('95020', '陈冬', '男', 'IS', 18); 例2: 插入一条选课记录('95020'，'1') INSERT INTO SC(Sno, Cno) VALUES ('95020', '1'); 新插入的记录在Grade列上取空值。

49 3.4 SQL数据操纵语言 (2) 插入子查询结果 插入子查询结果的INSERT语句的格式为： INSERT INTO <表名> [(<属性列1> [,<属性列2>...)] 子查询； 其功能是以批量插入，一次将子查询的结果全部插入指定表中。 例3 :对每一个系，求学生的平均年龄，并把结果存入数据库 CREATE TABLE Deptage       (Sdept CHAR(15) ,        Avgage SMALLINT); INSERT INTO Deptage(Sdept, Avgage)       SELECT Sdept, AVG(Sage)       FROM Student GROUP BY Sdept;

50 3.4 SQL数据操纵语言 2. Update语句 一般格式为： UPDATE <表名>
SET <列名>=<表达式>[,<列名>=<表达式>]... [WHERE <条件>]; 其功能是修改指定表中满足WHERE子句条件的元组。其中SET子句用于指定修改方法，即用<表达式>的值取代相应的属性列值。如果省略WHERE子句，则表示要修改表中的所有元组。 (1) 修改某一个元组的值 例4: 将学生95001的年龄改为22岁 UPDATE Student SET Sage=22 WHERE Sno='95001';

51 3.4 SQL数据操纵语言 (2) 修改多个元组的值 例5: 将所有学生的年龄增加1岁
(2) 修改多个元组的值 例5: 将所有学生的年龄增加1岁 UPDATE Student SET Sage=Sage+1; (3) 带子查询的修改语句 子查询也可以嵌套在UPDATE语句中，用以构造执行修改操作的条件。 例6:将计算机科学系全体学生的成绩置零 UPDATE SC SET GRADE=0 WHERE SNO IN (SELECT SNO FROM STUDENT WHERE SDEPT=‘SC’)

52 3.4 SQL数据操纵语言 (4) 修改操作与数据库的一致性 UPDATE语句一次只能操作一个表。这会带来一些问题。
例如，学号为95007的学生因病休学一年，复学后需要将其学号改为96089，由于Student表和SC表都有关于95007的信息，因此两个表都需要修改，这种修改只能通过两条UPDATE语句进行。 第一条UPDATE语句修改Student表： UPDATE Student SET Sno='96089' WHERE Sno='95007'; 第二条UPDATE语句修改SC表： UPDATE SC SET Sno='96089' WHERE Sno='95007';

53 3.4 SQL数据操纵语言 在执行了第一条UPDATE语句之后，数据库中的数据已处于不一致状态，因为这时实际上已没有学号为95007的学生了，但SC表中仍然记录着关于95007学生的选课信息，即数据的参照完整性受到破坏。只有执行了第二条UPDATE语句之后，数据才重新处于一致状态。但如果执行完一条语句之后，机器突然出现故障，无法再继续执行第二条UPDATE语句，则数据库中的数据将永远处于不一致状态。 因此必须保证这两条UPDATE语句要么都做，要么都不做。为解决这一问题，数据库系统通常都引入了事务（Transaction）的概念。 (计算机3月19日）

54 3.4 SQL数据操纵语言 3. Delete语句 一般格式为： DELETE FROM <表名> [WHERE <条件>]; DELETE语句的功能是从指定表中删除满足WHERE子句条件的所有元组。如果省略WHERE子句，表示删除表中全部元组，但表的定义仍在字典中。也就是说，DELETE语句删除的是表中的数据，而不是关于表的定义。 例7: 删除学号为95019的学生记录 DELETE FROM Student WHERE Sno='95019'; 例8: 删除所有的学生选课记录 DELETE FROM SC;

55 3.5 SQL视图 定义视图 (1) 创建视图 SQL语言用CREATE VIEW命令建立视图，其一般格式为：
CREATE VIEW <视图名>[(<列名>[,<列名>]...)] AS <子查询> [WITH CHECK OPTION]; 其中子查询可以是任意复杂的SELECT语句，但通常不允许含有ORDER BY子句和DISTINCT短语。 WITH CHECK OPTION表示对视图进行UPDATE、 INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）。 例1: 建立信息系学生的视图。 CREATE VIEW IS_Student    AS        SELECT Sno, Sname, Sage        FROM Student        WHERE Sdept='IS';

56 3.5 SQL视图 例2: 建立信息系学生的视图，并要求进行修改和插入操作时仍须保 证该视图只有信息系的学生
例2: 建立信息系学生的视图，并要求进行修改和插入操作时仍须保 证该视图只有信息系的学生 CREATE VIEW IS_Student   AS        SELECT Sno, Sname, Sage        FROM Student        WHERE Sdept='IS'        WITH CHECK OPTION; 由于在定义IS_Student视图时加上了WITH CHECK OPTION子句，以后对该视图进行插入、修改和删除操作时，DBMS会自动加上Sdept='IS'的条件。 例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';

57 3.5 SQL视图 2. 查询视图 视图定义后，用户就可以象对基本表进行查询一样对视图进行查询了。
DBMS执行对视图的查询时，首先进行有效性检查，检查查询涉及的表、视图等是否在数据库中存在，如果存在，则从数据字典中取出查询涉及的视图的定义，把定义中的子查询和用户对视图的查询结合起来，转换成对基本表的查询，然后再执行这个经过修正的查询。将对视图的查询转换为对基本表的查询的过程称为视图的消解（View Resolution）。 例1：在信息系学生的视图中找出年龄小于20岁的学生 SELECT Sno, Sage FROM IS_Student WHERE Sage<20;

58 3.5 SQL视图 DBMS执行此查询时，将其与IS_Student视图定义中的子查询 SELECT Sno, Sname, Sage 　　　 FROM Student WHERE Sdept="IS" ; 结合起来，转换成对基本表Student的查询，修正后的查询语句为： SELECT Sno, Sage FROM Student WHERE Sdept="IS" AND Sage<20; 视图是定义在基本上的虚表，它可以和其他基本表一起使用，实现连接查询或嵌套查询。这也就是说，在关系数据库的三级模式结构中，外模式不仅包括视图，而且还可以包括一些基本表。

59 3.5 SQL视图 例2：查询信息系选修了1号课程的学生
SELECT Sno, Sname FROM IS_Student, SC WHERE IS_Student.Sno=SC.Sno AND      SC.Cno='1'; 本查询涉及虚表IS_Student和基本表SC，通过这两个表的连接来完成用户请求： SELECT SNO, SNAME FROM STUDENT S,SC WHERE S.SNO=SC.SNO AND SC.CNO=‘1’ AND SDEPT=‘IS’;

60 3.5 SQL视图 3. 更新视图 更新视图包括插入（INSERT）、删除（DELETE）和修改（UPDATE）三类操作。
由于视图是不实际存储数据的虚表，因此对视图的更新，最终要转换为对基本表的更新。 为防止用户通过视图对数据进行增删改时，无意或故意操作不属于视图范围内的基本表数据，可在定义视图时加上WITH CHECK OPTION子句，这样在视图上增删改数据时，DBMS会进一步检查视图定义中的条件，若不满足条件，则拒绝执行该操作。 例1：将信息系学生视图IS_Student中学号为95002的学生姓名改为“刘辰” UPDATE IS_Student SET Sname='刘辰' WHERE Sno='95002';

61 3.5 SQL视图 与查询视图类似，DBMS执行此语句时，首先进行有效性检查，检查所涉及的表、视图等是否在数据库中存在，如果存在，则从数据字典中取出该语句涉及的视图的定义，把定义中的子查询和用户对视图的更新操作结合起来，转换成对基本表的更新，然后再执行这个经过修正的更新操作。转换后的更新语句为： UPDATE Student SET Sname=‘刘辰’ WHERE Sno='95002' AND Sdept='IS'; 例2：向信息系学生视图IS_S中插入一个新的学生记录，其中学号为95029，姓名为赵新，年龄为20岁 INSERT INTO IS_Student VALUES(‘95029’, ‘赵新’, 20); DBMS将其转换为对基本表的更新： INSERT INTO Student(Sno,Sname,Sage,Sdept) VALUES('95029', '赵新', 20, 'IS'); 这里系统自动将系名'IS'放入VALUES子句中。

62 3.5 SQL视图 关于更新的限制：在关系数据库中，并不是所有的视图都是可更新的，因为有些视图的更新不能唯一地有意义地转换成对相应基本表的更
关于更新的限制：在关系数据库中，并不是所有的视图都是可更新的，因为有些视图的更新不能唯一地有意义地转换成对相应基本表的更　 例如DB2规定： 若视图是由两个以上基本表导出的，则此视图不允许更新。 若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作。 若视图的字段来自集函数，则此视图不允许更新。 若视图定义中含有GROUP BY子句，则此视图不允许更新。 若视图定义中含有DISTINCT短语，则此视图不允许更新。 若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。 一个不允许更新的视图上定义的视图也不允许更新。 应该指出的是，不可更新的视图与不允许更新的视图是两个不同的概念。前者指理论上已证明其是不可更新的视图。后者指实际系统中不支持其更新，但它本身有可能是可更新的视图。

63 3.5 SQL视图 4. 视图的特点 视图最终是定义在基本表之上的，对视图的一切操作最终也要转换为对基本表的操作。而且对于非行列子集视图进行查询或更新时还有可能出现问题。既然如此，为什么还要定义视图呢？这是因为合理使用视图能够带来许多好处: 视图能够简化用户的操作; 视图使用户能以多种角度看待同一数据; 视图对重构数据库提供了一定程度的逻辑独立性; 视图能够对机密数据提供安全保护;

64 3.6 SQL数据控制功能 数据控制功能简介 由DBMS提供统一的数据控制功能是数据库系统的特点之一。数据控制亦称为数据保护，包括数据的安全性控制、完整性控制、并发控制和恢复。这里主要介绍SQL的数据控制功能。 2. GRANT语句 SQL语言用GRANT语句向用户授予操作权限，GRANT语句的一般格式为： GRANT <权限>[,<权限>]...         [ON <对象类型> <对象名>]         TO <用户>[,<用户>]...         [WITH GRANT OPTION]; 其语义为：将对指定操作对象的指定操作权限授予指定的用户。 接受权限的用户可以是一个或多个具体用户，也可以是PUBLIC即全体用户。 如果指定了WITH GRANT OPTION子句，则获得某种权限的用户还可以把这种权限再授予别的用户。如果没有指定WITH GRANT OPTION子句，则获得某种权限的用户只能使用该权限，但不能传播该权限。

65 3.6 SQL数据控制功能 例1：把查询Student表权限授给用户U1
GRANT SELECT ON TABLE Student TO U1; 例2：把对Student表和Course表的全部权限授予用户U2和U3 GRANT ALL PRIVILIGES ON TABLE Student, Course TO U2, U3; 例3：把对表SC的查询权限授予所有用户 GRANT SELECT ON TABLE SC TO PUBLIC; 例4：把查询Student表和修改学生学号的权限授给用户U4 GRANT UPDATE(Sno), SELECT ON TABLE Student TO U4; 例5：把对表SC的INSERT权限授予U5用户，并允许他再将此权限授予其他用户 GRANT INSERT ON TABLE SC TO U5 WITH GRANT OPTION; 例6：DBA把在数据库S_C中建立表的权限授予用户U8 GRANT CREATETAB ON DATABASE S_C TO U8;

66 3.6 SQL数据控制功能 3. REVOKE语句 授予的权限可以由DBA或其他授权者用REVOKE语句收回，REVOKE语句的一般格式为：
REVOKE <权限>[,<权限>]... [ON <对象类型> <对象名>] FROM <用户>[,<用户>]...; 例6：把用户U4修改学生学号的权限收回 REVOKE UPDATE(Sno) ON TABLE Student FROM U4; 例7：收回所有用户对表SC的查询权限 REVOKE SELECT ON TABLE SC FROM PUBLIC; 例8：把用户U5对SC表的INSERT权限收回 REVOKE INSERT ON TABLE SC FROM U5;

67 3.7 嵌入式SQL 1. 嵌入式SQL简介 SQL语言提供了两种不同的使用方式：
另一种是将SQL语言嵌入到某种高级语言如PL/1、COBOL、FORTRAN、C中使用，利用高级语言的过程性结构来弥补SQL语言在实现复杂应用方面的不足，这种方式下使用的SQL语言称为嵌入式SQL（Embedded SQL），而嵌入SQL的高级语言称为主语言或宿主语言。 对宿主型数据库语言SQL，DBMS可采用两种方法处理：一种是预编译，另一种是修改和扩充主语言使之能处理SQL语句。目前采用较多的是预编译的方法。即由DBMS的预处理程序对源程序进行扫描，识别出SQL语句，把它们转换成主语言调用语句，以使主语言编译程序能识别它，最后由主语言的编译程序将整个源程序编译成目标码。

68 3.7 嵌入式SQL 1. 嵌入式SQL简介（续） 在嵌入式SQL中，为了能够区分SQL语句与主语言语句，所有SQL语句都必须加前缀EXEC SQL。 SQL语句的结束标志则随主语言的不同而不同，例如在PL/1和C中以分号（;）结束，在COBOL中以END-EXEC结束。这样，以C或PL/1作为主语言的嵌入式SQL语句的一般形式为： EXEC SQL<SQL语句>; 嵌入SQL语句根据其作用的不同，可分为可执行语句和说明性语句两类。可执行语句又分为数据定义、数据控制、数据操纵三种。 　　 在宿主程序中，任何允许出现可执行的高级语言语句的地方，都可以写可执行SQL语句； 任何允许出现说明性高级语言语句的地方，都可以写说明性SQL语句。

69 3.7 嵌入式SQL 2. 嵌入式SQL与主语言之间的通信 数据库工作单元与源程序工作单元之间通信主要包括：
在嵌入式SQL中，向主语言程序传递SQL执行状态信息主要用SQL通信区（SQL Communication Area，简称SQLCA）实现； 主语言程序向SQL语句输入数据主要用主变量（host variable）实现； SQL语句向主语言程序输出数据主要用主变量和游标（cursor）实现。

70 3.7 嵌入式SQL (1) SQL通信区 SQL语句执行后，系统要反馈给应用程序若干信息，主要包括描述系统当前工作状态和运行环境的各种数据，这些信息将送到SQL通信区SQLCA中。应用程序从SQLCA中取出这些状态信息，据此决定接下来执行的语句。 SQLCA是一个数据结构，在应用程序中用EXEC SQL INCLUDE SQLCA加以定义。 SQLCA中有一个存放每次执行SQL语句后返回代码的变量SQLCODE。 应用程序每执行完一条SQL 语句之后都应该测试一下SQLCODE的值，以了解该SQL语句执行情况并做相应处理。 如果SQLCODE等于预定义的常量SUCCESS，则表示SQL语句成功，否则表示错误代码。 例如在执行删除语句DELETE后，根据不同的执行情况，SQLCA中有下列不同的信息： ·违反数据保护规则，操作拒绝 ·没有满足条件的行，一行也没有删除· 成功删除，并有删除的行数（SQLCODE=SUCCESS）· 无条件删除警告信息· 由于各种原因，执行出错

71 3.7 嵌入式SQL (2)主变量 嵌入式SQL语句中可以使用主语言的程序变量来输入或输出数据。我们把在SQL语句中使用的主语言程序变量简称为主变量。 主变量根据其作用的不同，分为输入主变量和输出主变量。 输入主变量由应用程序对其赋值，SQL语句引用； 输出主变量由SQL语句对其赋值或设置状态信息，返回给应用程序。 一个主变量有可能既是输入主变量又是输出主变量。 利用输入主变量，我们可以指定向数据库中插入的数据，可以将数据库中的数据修改为指定值，可以指定执行的操作，可以指定WHERE子句或HAVING子句中的条件。 利用输出主变量，我们可以得到SQL语句的结果数据和状态。 (软件学院3月20日)

72 3.7 嵌入式SQL (2)主变量（续） 一个主变量可以附带一个任选的指示变量（Indicator Variable）。所谓指示变量是一个整型变量，用来“指示”所指主变量的值或条件。 输入主变量可以利用指示变量赋空值，输出主变量可以利用指示变量检测出是否空值，值是否被截断。 使用主变量及指示变量的方法是，所有主变量和指示变量必须在SQL语句BEGIN DECLARE SECTION与END DECLARE SECTION之间进行说明。 说明之后，主变量可以在SQL语句中任何一个能够使用表达式的地方出现，为了与数据库对象名（表名、视图名、列名等）区别， SQL语句中的主变量名前要加冒号（:）作为标志。同样，SQL语句中的指示变量前也必须加冒号标志，并且要紧跟在所指主变量之后。 而在SQL语句之外，主变量和指示变量均可以直接引用，不必加冒号。

73 3.7 嵌入式SQL (3) 游标 SQL语言与主语言具有不同数据处理方式。

74 3.7 嵌入式SQL (3)游标 举例：为了能够更好地理解上面的概念，下面给出带有嵌入式SQL的一小段C程序
EXEC SQL INCLUDE SQLCA; (1) 定义SQL通信区 EXEC SQL BEGIN DECLARE SECTION; (2) 说明主变量 CHAR title_id(7); CHAR title(81); INT royalty; EXEC SQL END DECLARE SECTION; main(){     EXEC SQL DECLARE C1 CURSOR FOR (3) 游标操作（定义游标）     SELECT tit_id, tit, roy FROM titles;                        /* 从titles表中查询 tit_id, tit, roy */     EXEC SQL OPEN C1; (4) 游标操作（打开游标）     for(;;)    {…        

75 3.7 嵌入式SQL     for(;;)  {      EXEC SQL FETCH C1 INTO :title_id, :title, :royalty;             (5) 游标操作（推进游标指针）                                                                 （将当前数据放入主变量）          if (sqlca.sqlcode <> SUCCESS) (6) 利用SQLCA中的状态信息                                                                 决定何时退出循环              break;          printf("Title ID: %s, Royalty: %d", :title_id, :royalty);          printf("Title: %s", :title);                       /* 打印查询结果 */ } EXEC SQL CLOSE C1; (7) 游标操作（关闭游标）

76 3.7 嵌入式SQL 3. 不用游标的SQL语句 说明性语句 数据定义语句
交互式SQL中没有说明性语句，说明性语句是专为在嵌入式SQL中说明主变量等而设置的，主要有两条语句： EXEC SQL BEGIN DECLARE SECTION; 和 EXEC SQL END DECLARE SECTION; 两条语句必须配对出现，相当于一个括号，两条语句中间是主变量的说明。 数据定义语句 例1: 建立一个“学生”表Student EXEC SQL CREATE TABLE Student (Sno CHAR(5) NOT NULL UNIQUE, Sname CHAR(20), Ssex CHAR(1), Sage INT, Sdept CHAR(15)); 数据定义语句中不允许使用主变量。例如下列语句是错误的： EXEC SQL DROP TABLE :table_name;

77 3.7 嵌入式SQL 数据控制语句 例2: 把查询Student表权限授给用户U1 EXEC SQL GRANT SELECT ON TABLE Student TO U1; 查询结果为单记录的SELECT语句 在嵌入式SQL中，查询结果为单记录的SELECT语句需要用INTO子句指定查询结果的存放地点。该语句的一般格式为： EXEC SQL SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>]...     INTO <主变量>[<指示变量>][,<主变量>[<指示变量>]]...     FROM <表名或视图名>[,<表名或视图名>] ...     [WHERE <条件表达式>]     [GROUP BY <列名1> [HAVING <条件表达式>]]     [ORDER BY <列名2> [ASC|DESC]]; 该语句对交互式SELECT语句的扩充就是多了一个INTO子句，把从数据库中找到的符合条件的记录，放到INTO子句指出的主变量中去。其他子句的含义不变。

78 3.7 嵌入式SQL 例3:根据学生号码查询学生信息。假设已将要查询的学生的学号赋给了主变量givensno
EXEC SQL SELECT Sno, Sname, Ssex, Sage, Sdept     INTO :Hsno, :Hname, :Hsex, :Hage, :Hdept     FROM Student     WHERE Sno=:givensno; 上面的SELECT语句中Hsno, Hname, Hsex, Hage, Hdept和givensno均是主变量，并均已在前面的程序中说明过了。 例4: 查询某个学生选修某门课程的成绩。假设已将要查询的学生的学号赋给了主变量givensno，将课程号赋给了主变量givencno EXEC SQL SELECT Sno, Cno, Grade     INTO :Hsno, :Hcno, :Hgrade:Gradeid     FROM SC     WHERE Sno=:givensno AND Cno=:givencno;

79 3.7 嵌入式SQL 非CURRENT形式的UPDATE语句
在UPDATE语句中，SET子句和WHERE子句中均可以使用主变量，其中SET子句中还可以使用指示变量。 例5： 将全体学生1号课程的考试成绩增加若干分。假设增加的分数已赋给主变量Raise EXEC SQL UPDATE SC     SET Grade=Grade+:Raise     WHERE Cno=‘1’; 该操作实际上是一个集合操作，DBMS会修改所有学生的1号课程的Grade属性列。 例6： 修改某个学生1号课程的成绩。假设该学生的学号已赋给主变量givensno，修改后的成绩已赋给主变量newgrade EXEC SQL UPDATE SC     SET Grade=:newgrade     WHERE Sno=:givensno;

80 3.7 嵌入式SQL 非CURRENT形式的DELETE语句 DELETE语句的WHERE子句中可以使用主变量指定删除条件。
例8 ：某个学生退学了，现要将有关他的所有选课记录删除掉。假设该学生的姓名已赋给主变量stdname EXEC SQL DELETE     FROM SC     WHERE Sno= (SELECT Sno          FROM Student WHERE Sname=:stdname); 另一种等价实现方法为： EXEC SQL DELETE     FROM SC     WHERE :stdname= (SELECT Sname           FROM Student           WHERE Studnet.Sno=SC.sno); 显然第一种方法更直接，从而也更高效些。 如果该学生选修了多门课程，执行上面的语句时，DBMS会自动执行集合操作，即把他选修的所有课程都删除掉。

81 3.7 嵌入式SQL INSERT语句 INSERT语句的VALUES子句中可以使用主变量和指示变量。
例9： 某个学生新选修了某门课程，将有关记录插入SC表中。假设学生的学号已赋给主变量stdno，课程号已赋给主变量couno。 gradeid=-1; EXEC SQL INSERT     INTO SC(Sno, Cno, Grade)     VALUES(:stdno, :couno, :gr:gradeid); 由于该学生刚选修课程，尚未考试，因此成绩列为空。所以本例中用指示变量指示相应的主变量为空值。

82 3.7 嵌入式SQL 4. 使用游标的SQL语句 1) 查询结果为多条记录的SELECT语句
使用游标的步骤为： 1. 说明游标: 用DECLARE语句为一条SELECT语句定义游标。 DECLARE语句的一般形式为： EXEC SQL DECLARE <游标名> CURSOR FOR <SELECT语句>; 其中SELECT语句可以是简单查询，也可以是复杂的连接查询和嵌套查询。 定义游标仅仅是一条说明性语句，这时DBMS并不执行SELECT指定的查询操作。

83 3.7 嵌入式SQL 4. 使用游标的SQL语句 1) 查询结果为多条记录的SELECT语句
使用游标的步骤为： 说明游标: 用DECLARE语句为一条SELECT语句定义游标。 DECLARE语句的一般形式为： EXEC SQL DECLARE <游标名> CURSOR FOR <SELECT语句>; 其中SELECT语句可以是简单查询，也可以是复杂的连接查询和嵌套查询。 定义游标仅仅是一条说明性语句，这时DBMS并不执行SELECT指定的查询操作。

84 3.7 嵌入式SQL 打开游标:用OPEN语句将上面定义的游标打开。OPEN语句的一般形式为： EXEC SQL OPEN <游标名>; 打开游标实际上是执行相应的SELECT语句，把所有满足查询条件的记录从指定表取到缓冲区中。这时游标处于活动状态，指针指向查询结果集中第一条记录。 推进游标指针并取当前记录。用FETCH语句把游标指针向前推进一条记录，同时将缓冲区中的当前记录取出来出来送至主变量供主语言进一步处理。FETCH语句的一般形式为： EXEC SQL FETCH <游标名> INTO <主变量>[<指示变量>][,<主变量>[<指示变量>]]...; 其中主变量必须与SELECT语句中的目标列表达式具有一一对应关系。

85 3.7 嵌入式SQL 为进一步方便用户处理数据，现在许多关系数据库管理系统对FETCH语句做了扩充，允许用户向任意方向以任意步长移动游标指针，而不仅仅是把游标指针向前推进一行了。 关闭游标。用CLOSE语句关闭游标，释放结果集占用的缓冲区及其他资源。CLOSE语句的一般形式为： EXEC SQL CLOSE <游标名>; 游标被关闭后，就不再和原来的查询结果集相联系。但被关闭的游标可以再次被打开，与新的查询结果相联系。

86 3.7 嵌入式SQL 例1: 查询某个系全体学生的信息。要查询的系名由用户在程序运行过程中指定，放在主变量deptname中 ......
EXEC SQL BEGIN DECLARE SECTION;               /* 说明主变量deptname,HSno,HSname,HSsex,HSage等*/ EXEC SQL END DECLARE SECTION; gets(deptname);   /* 为主变量deptname赋值 */ EXEC SQL DECLARE SX CURSOR FOR       SELECT Sno, Sname, Ssex, Sage       FROM Student       WHERE SDept=:deptname;  /* 说明游标 */ EXEC SQL OPEN SX  /* 打开游标 */

87 3.7 嵌入式SQL WHILE(1)        /* 用循环结构逐条处理结果集中的记录 */ { EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex, :HSage;        /* 游标指针向前推进一行，然后从结果集中取当前行，送相应主变量 */ if (sqlca.sqlcode <> SUCCESS)       break; 　 /* 若所有查询结果均已处理完或出现SQL语句错误，则退出循环 */     /* 由主语言语句进行进一步处理 */ };   EXEC SQL CLOSE SX; /* 关闭游标 */ 　

88 3.7 嵌入式SQL 例2: 查询某些系全体学生的选课信息。 EXEC SQL BEGIN DECLARE SECTION; /* 说明主变量 deptname,HSno,HSname,HSsex,HSage等*/ EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE SX CURSOR FOR       SELECT Sno, Sname, Ssex, Sage       FROM Student       WHERE SDept=:deptname;    /* 说明游标 */ WHILE (gets(deptname)!=NULL)   /* 接收主变量deptname的值 */ {    /* 下面开始处理deptname指定系的学生信息,每次循环中 deptname可具有不同的值 */   EXEC SQL OPEN SX     /* 打开游标 */  

89 3.7 嵌入式SQL WHILE (1) { /* 用循环结构逐条处理结果集中的记录 */ EXEC SQL FETCH SX
INTO :HSno, :HSname, :HSsex, :HSage; /* 游标指针向前推进一行，然后从结果集中取当前行，送相应主变量 */  if (sqlca.sqlcode <> SUCCESS)        break; /* 若所有查询结果均已处理完或出现SQL语句错误，则退出循环 */   /* 由主语言语句进行进一步处理 */                       };             /* 内循环结束 */   EXEC SQL CLOSE SX;       /* 关闭游标 */ };        /* 外循环结束 */

90 3.7 嵌入式SQL 非CURRENT形式的UPDATE语句和DELETE语句都是集合操作，一次修改或删除所有满足条件的记录。
如果只想修改或删除其中某个记录，则需要用带游标的SELECT语句查出所有满足条件的记录，从中进一步找出要修改或删除的记录，然后修改或删除之。 具体地说就是： 用DECLARE语句说明游标。如果是为CURRENT形式的UPDATE语句作准备，则SELECT语句中要用 FOR UPDATE OF <列名> 子句指明将来检索出的数据在指定列是可修改的。如果是为CURRENT形式的DELETE语句作准备，则不必使用上述子句。 用OPEN语句打开游标，把所有满足查询条件的记录从指定表取到缓冲区中。 用FETCH语句推进游标指针，并把当前记录从缓冲区中取出来送至主变量。

91 3.7 嵌入式SQL 检查该记录是否是要修改或删除的记录。如果是，则用UPDATE语句或DELETE语句修改或删除该记录。
这时UPDATE语句和DELETE语句中要用 WHERE CURRENT OF <游标名> 子句，表示修改或删除的是该游标中最近一次取出的记录，即游标指针指向的记录。 第3和4步通常用在一个循环结构中，通过循环执行FETCH语句，逐条取出结果集中的行进行判断和处理。 处理完毕用CLOSE语句关闭游标，释放结果集占用的缓冲区和其他资源。 例3: 查询某个系全体学生的信息（要查询的系名由主变量deptname指定），然后根据用户的要求修改其中某些记录的年龄字段。

92 3.7 嵌入式SQL EXEC SQL BEGIN DECLARE SECTION;     /* 说明主变量 deptname,HSno,HSname,HSsex,HSage,NEWAge等*/ EXEC SQL END DECLARE SECTION; gets(deptname);     /* 为主变量deptname赋值 */ EXEC SQL DECLARE SX CURSOR FOR       SELECT Sno, Sname, Ssex, Sage       FROM Student       WHERE SDept=:deptname       FOR UPDATE OF Sage;     /* 说明游标 */ EXEC SQL OPEN SX     /* 打开游标 */ WHILE(1) {      /* 用循环结构逐条处理结果集中的记录 */      EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex, :HSage;          /* 游标指针向前推进一行，然后从结果集中取当前行，送相应主变量 */          

93 3.7 嵌入式SQL printf("%s, %s, %s, %d", Sno, Sname, Ssex, Sage);
/* 显示该记录 */ printf("UPDATE AGE ? "); /* 问用户是否要修改 */ scanf("%c",&yn); if (yn='y' or yn='Y') /* 需要修改 */ {            printf("INPUT NEW AGE: ");            scanf("%d",&NEWAge); /* 输入新的年龄值 */            EXEC SQL UPDATE Student            SET Sage=:NEWAge            WHERE CURRENT OF SX; /* 修改当前记录的年龄字段 */         }; };

94 3.7 嵌入式SQL printf("%s, %s, %s, %d", Sno, Sname, Ssex, Sage);
/* 显示该记录 */ printf("UPDATE AGE ? "); /* 问用户是否要修改 */ scanf("%c",&yn); if (yn='y' or yn='Y') /* 需要修改 */ {            printf("INPUT NEW AGE: ");            scanf("%d",&NEWAge); /* 输入新的年龄值 */            EXEC SQL UPDATE Student            SET Sage=:NEWAge            WHERE CURRENT OF SX; /* 修改当前记录的年龄字段 */         }; };

95 3.7 嵌入式SQL 例4 :查询某个系全体学生的信息（要查询的系名由主变量deptname指定），然后根据用户的要求修改删除其中某些记录。 EXEC SQL BEGIN DECLARE SECTION; /* 说明主变量 deptname,HSno,HSname,HSsex,HSage等*/ EXEC SQL END DECLARE SECTION; gets(deptname); /* 为主变量deptname赋值 */ EXEC SQL DECLARE SX CURSOR FOR       SELECT Sno, Sname, Ssex, Sage       FROM Student       WHERE SDept=:deptname; /* 说明游标 */ EXEC SQL OPEN SX        /* 打开游标 */ WHILE(1){      /* 用循环结构逐条处理结果集中的记录 */         EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex, :HSage;       /* 游标指针向前推进一行，然后从结果集中取当前行，送相应主变量 */        ….

96 3.7 嵌入式SQL WHILE(1){      /* 用循环结构逐条处理结果集中的记录 */         EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex, :HSage;       /* 游标指针向前推进一行，然后从结果集中取当前行，送相应主变量 */         if (sqlca.sqlcode <> SUCCESS)              break; 　   /* 若所有查询结果均已处理完或出现SQL语句错误，则退出循环 */         printf("%s, %s, %s, %d", Sno, Sname, Ssex, Sage); /* 显示该记录 */         printf("DELETE ? "); /* 问用户是否要删除 */         scanf("%c",&yn);         if (yn='y' or yn='Y') /* 需要删除 */         EXEC SQL DELETE         FROM Student         WHERE CURRENT OF SX; /* 删除当前记录 */                 }; EXEC SQL CLOSE SX; /* 关闭游标 */ 注意，当游标定义中的SELECT语句带有UNION或ORDER BY子句时，或者该SELECT语句相当于定义了一个不可更新的视图时，不能使用CURRENT形式的UPDATE语句和DELETE语句。

97 3.7 嵌入式SQL 5. 动态SQL 前面节中介绍的嵌入式SQL语句为编程提供了一定的灵活性，使用户可以在程序运行过程中根据实际需要输入WHERE子句或HAVING子句中某些变量的值。 这些SQL语句的共同特点是，语句中主变量的个数与数据类型在预编译时都是确定的，只有主变量的值是程序运行过程中动态输入的，我们称这类嵌入式SQL语句为静态SQL语句。 静态SQL语句提供的编程灵活性在许多情况下仍显得不足，有时候我们需要编写更为通用的程序。例如，查询学生选课关系SC，任课教师想查选修某门课程的所有学生的学号及其成绩，班主任想查某个学生选修的所有课程的课程号及相应成绩，学生想查某个学生选修某门课程的成绩，也就是说查询条件是不确定的，要查询的属性列也是不确定的，这时就无法用一条静态SQL语句实现了。 实际上，如果在预编译时下列信息不能确定，我们就必须使用动态SQL技术：     ·SQL语句正文     ·主变量个数     ·主变量的数据类型     ·SQL语句中引用的数据库对象（例如列、索引、基本表、视图等）

98 3.7 嵌入式SQL 5. 动态SQL(续) 动态SQL方法允许在程序运行过程中临时“组装”SQL语句，主要有三种形式：
条件可变： 对于非查询语句，条件子句有一定的可变性。例如删除学生选课记录，既可以是因某门课临时取消，需要删除有关该课程的所有选课记录，也可以是因为某个学生退学，需要删除该学生的所有选课记录。 对于查询语句，SELECT子句是确定的，即语句的输出是确定的，其他子句（如WHERE子句、HAVING短语）有一定的可变性。例如查询学生人数，可以是查某个系的学生总数，查某个性别的学生人数，查某个年龄段的学生人数，查某个系某个年龄段的学生人数等等，这时SELECT子句的目标列表达式是确定的（COUNT(*)），但WHERE子句的条件是不确定的. 数据库对象、查询条件均可变：对于查询语句，SELECT子句中的列名、FROM子句中的表名或视图名、WHERE子句和HAVING短语中的条件等等均可由用户临时构造，即语句的输入和输出可能都是不确定的。例如我们前面查询学生选课关系SC的例子。对于非查询语句，涉及的数据库对象及条件也是可变的。 这几种动态形式几乎可覆盖所有的可变要求。 为了实现上述三种可变形式，SQL提供了相应的语句，例如EXECUTE IMMEDIATE、PREPARE、EXECUTE、DESCRIBE等。使用动态SQL技术更多的是涉及程序设计方面的知识，而不是SQL语言本身，所以这里就不详细介绍了，有兴趣的读者可以参阅有关书籍。

99 即SQL集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动，包括定义关系模式、录入数据以建立数据库、查询、更新、维护、数据库重构、数据库安全性控制等一系列操作要求，这就为数据库应用系统开发提供了良好的环境。 例如用户在数据库投入运行后，还可根据需要随时地逐步地修改模式，并不影响数据库的运行，从而使系统具有良好的可扩充性。 返回

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

101 SQL语言采用集合操作方式，不仅查找结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。
非关系数据模型采用的是面向记录的操作方式，任何一个操作其对象都是一条记录。例如查询所有平均成绩在80分以上的学生姓名，用户必须说明完成该请求的具体处理过程，即如何用循环结构按照某条路径一条一条地把满足条件的学生记录读出来。 返回

102 SQL语言既是自含式语言，又是嵌入式语言。
作为自含式语言，它能够独立地用于联机交互的使用方式，用户可以在终端键盘上直接键入SQL命令对数据库进行操作。作为嵌入式语言，SQL语句能够嵌入到高级语言（例如C、COBOL、FORTRAN、PL/1）程序中，供程序员设计程序时使用。而在两种不同的使用方式下，SQL语言的语法结构基本上是一致的。这种以统一的语法结构提供两种不同的使用方式的作法，为用户提供了极大的灵活性与方便性。 返回

103 SQL语言功能极强，但由于设计巧妙，语言十分简洁，完成数据定义、数据操纵、数据控制的核心功能只用了9个动词：CREATE、DROP、SELECT、INSERT、UPDATE、DELETE、GRANT、REVOKE。而且SQL语言语法简单，接近英语口语，因此容易学习，容易使用。 返回

104 连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须是可比的，但不必是相同的。例如，可以都是字符型，或都是日期型；也可以一个是整型，另一个是实型，整型和实型都是数值型，因此是可比的。但若一个是字符型，另一个是整数型就不允许了，因为它们是不可比的类型。 从概念上讲DBMS执行连接操作的过程是，首先在表1中找到第一个元组，然后从头开始顺序扫描或按索引扫描表2，查找满足连接条件的元组，每找到一个元组, 就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。表2全部扫描完毕后，再到表1中找第二个元组，然后再从头开始顺序扫描或按索引扫描表2，查找满足连接条件的元组，每找到一个元组, 就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到表1全部元组都处理完毕为止。