第三章 关系数据库标准查询语言SQL 3.1 SQL概述 3.2数据定义语言（DDL） 3.3 SQL的数据查询（DML） 3.5视图 3.6数据控制语言（DCL） 3.7嵌入式SQL语言 3.8 存储过程*（T-SQL）

3.1 SQL概述 SQL(Standard/Structured Query Language)是关系数据库标准 1986年10月，美国国家标准局（American National Standard Institute ANSI）公布第一个标准ANSI X3.135-1986，国际标准化组织（International Organization for Standardization ISO）也通过这一标准称SQL－86 1989年ANSI再次公布标准ANSI X3.135-1989 ，ISO 相应SQL－89 1999年，ISO公布 SQL-1999（SQL99，SQL3） 2003年，ISO公布 SQL-2003

数据定义（DDL Data Definition/Description Language ） 3.1.1 SQL语言的组成 数据定义（DDL Data Definition/Description Language ） 定义数据库的逻辑结构，包括基本表、视图、索引等 数据操纵（DML Data Manipulation Language） 包括查询和更新，更新又包含插入、删除和修改 数据控制（DCL Data Control Language ） 授权、完整性规则描述、事务控制等 嵌入式SQL（ESQL） 在宿主语言中使用SQL的规则

综合统一：集DDL、DML、DCL于一体，语言风格统一 面向集合的操作方式：操作对象、查询结果都可以是元组的集合 3.1.2 SQL语言的特点 综合统一：集DDL、DML、DCL于一体，语言风格统一 面向集合的操作方式：操作对象、查询结果都可以是元组的集合 高度非过程化：存取路进透明。 以统一的语法结构提供两种使用方式：自含式、嵌入式 语言简洁，易学易用，主要共使用11个关键词 DDL：create drop alter DML：select insert delete update DCL：grant revoke commit rollback

3.2数据定义语言（DDL） 3.2.1定义、删除与修改基本表 定义基本表语法 CREATE TABLE <表名> (<列名><数据类型>[列级约束条件][,<列名><数据类型>[列级约束条件]... ...][,<表级完整性约束条件>]) 例：CREATE TABLE S（ S＃ CHAR（5） NOT NULL UNIQUE， SN CHAR（20）， SA INT， SD CHAR（3）, PRIMARY KEY (S#) ）；

修改表语法 ALTER TABLE <表名> [ADD <新列名><数据类型>[列级约束条件]] [DROP <完整性约束条件>] [MODIFY <列名><数据类型>]; 例： ALTER TABLE S ADD SCome DATE； ALTER TABLE S MODIFY SA SMALLINT； ALTER TABLE S DROP UNIQUE(S#)； 删除表语法 DROP TABLE <表名> DROP TABLE S

3.2.2建立和删除索引 索引的建立语法 例： 索引的删除语法 CREATE [UNIQUE][CLUSTER] INDEX <索引名> ON <表名>（<列名1>[<次序>][，<列名2><次序>... ...]） <次序>可以是ACS和DESC 例： CREATE UNIQUE INDEX S_S# ON S(S#) CREATE UNIQUE INDEX C_C# ON C(C#) CREATE UNIQUE INDEX SC_S#_C# ON SC(S# ASC,C# DESC) 索引的删除语法 DROP INDEX [<表名>.]<索引名> DROP INDEX [S.]S_S#

3.3 SQL的数据查询（DML） 关系代数表达式 A1,A2,.....An(F (R1×R2×... ...×Rn)) SQL 语句 SELECT A1，A2，......An FROM R1，R2，... ...Rm WHERE F

SELECT [ALL|DISTINCT] {*|<目标表达式1> [,<目标表达式2> ... ...]} 详细语法 SELECT [ALL|DISTINCT] {*|<目标表达式1> [,<目标表达式2> ... ...]} FROM <表名或视图名1> [，<表名或视图名2>]... ... [WHERE <条件表达式>] [GROUP BY <列名表达式1>[，<列名表达式2>]] [HAVING <条件表达式> ] [ORDER BY <列名表达式1> [ASC|DESC]], <列名表达式2> [ASC|DESC]]

1)先按WHERE子句条件从FROM子句指定的表/视图中 找出满足条件的元组（选择）; 执行过程 1)先按WHERE子句条件从FROM子句指定的表/视图中 找出满足条件的元组（选择）; 2)再按SELECT子句中的目标表达式选择出元组中的 属性，形成结果表（投影）； 3)如有GROUP子句，则将结果按<列名表达式>的值分 组，该<列名表达式>值相等的元组为一个组，通 常会在每组中使用聚合函数。 4)如果GROUP子句带HAVING子句，则对组过虑，将 满足条件的组输出 5)如果ORDER子句，则将结果按<列名表达式1>的值 升序或降序排列

假设： S（S#，SN，SS，SA，SD） C（C#，CN，CP，CR） SC（S#，C#，GR） 选取表中的某些列，即投影运算 3.3.1单表查询 假设： S（S#，SN，SS，SA，SD） C（C#，CN，CP，CR） SC（S#，C#，GR） 选取表中的某些列，即投影运算 查指定列 SELECT S#,SN FROM S 查全部列 SELECT * FROM STUDENT 查经过计算的列 SELECT SN，2005-SA FROM S

选择表中的若干元组，即选择运算 表 消除取值重复行 查询满足条件的元组 SELECT DISTINCT SD FROM S 比较大小：<、<= 、>、>=、=、<> SELECT SN,SA FROM S WHERE SD=’CS’ SELECT * FROM S WHERE SA<20 确定范围：BETWEEN... AND SELECT * FROM S WHERE SA BETWEEN 20 AND 21 确定集合：IN SELECT * FROM S WHERE SD IN (‘CS’,’IS’,’MA’) 字符匹配：LIKE，转义字符’\’ SELECT * FROM S WHERE S# LIKE ‘TB%’ SELECT * FROM S WHERE SN LIKE ‘刘_’ 涉及空值的查询：IS NULL SELECT * FROM SC WHERE GR IS NULL 多重条件查询： SELECT * FROM S WHERE SD=’CS’ AND SA<20 表

ORDER BY <字段表达式> ASC|DESC 使用集(聚合)函数 查询结果排序 ORDER BY <字段表达式> ASC|DESC SELECT * FROM SC WHERE C#=’3’ ORDER BY GR DESC 使用集(聚合)函数 COUNT 、SUM、AVG、MAX、MIN SELECT COUNT(*) FROM S SELECT COUNT(DISTINCT S#) FROM SC SELECT AVG(GR) FROM SC WHERE S#=’95001’ SELECT MAX(GR) FROM SC WHERE C#=’1’ 查询分组：GROUP BY SELECT C#,COUNT(C#) FROM SC GROUP BY C# SELECT S# FROM SC GROUP BY S# HAVING COUNT(*) >3 检索选修>3门的课学生学号

3.3.2连接查询 等值与非等值连接查询 自然连接 自身连接 表 等值与非等值连接查询 自然连接 SELECT S.*,SC.* FROM S,SC WHERE S.S# = SC.S# 自身连接 检索每门课的间接预修课 SELECT f.C#, s.CP FROM C f,C s WHERE f.CP=s.C# C＃ CN CP CR 1 DB 5 4 2 MA 3 IS OS 6 DataStruct 7 DataProcess PASCAL C＃ CN P CR 1 DB 5 4 2 MA 3 IS OS 6 DataStruct 7 DataProcess PASCAL

外连接 表 列出所有学生的修课情况，如果没有选课也列出其基本信息(左外连接) SELECT S#,SN,SS,SA,SD,C#,GR FROM S, SC WHERE S.S# *=SC.S# (T-SQL语法 SYBASE) WHERE S.S# =SC.S#(+) (PL/SQL语法ORACLE) SELECT S#,SN,SS,SA,SD,C#,GR FROM S LEFT OUTER JION SC ON S.S#=SC.S# (MYSQL MSSQL)

复合条件连接 表 检索选修课程号‘2’且成绩在90分以上的所有学生 SELECT S.S# ,SN FROM S,SC WHERE S.S# = SC.S# AND SC.C#=’2’ AND SC.GR>=90 检索每个学生选修的课程名及其成绩 SELECT S.S#,SN,C.CN,SC.GR from S,SC,C WHERE S.S# = SC.S# AND SC.C# = C.C#

3.3.3嵌套查询 带IN谓词的子查询 表 检索与“刘晨”同在一系的学生信息 SELECT S#,SN,SD FROM S WHERE SD IN (SELECT SD FROM S WHERE SN＝‘刘晨’) 本例可以通过自连接来实现 SELECT s1.S#, s1.SN, s1.SD FROM S s1, S s2 WHERE s1.SD = s2.SD AND s2.SN=’刘晨’

表 检索选修了课程名的为‘MA’的学生学号和姓名 SELECT S#, SN FROM S WHERE S# IN (SELECT S# FROM SC WHERE C# IN (SELECT C# FROM C WHERE CN=’MA’) ) 本例同样可以用连接来实现 SELECT S#,SN FROM S ,SC,C WHERE S.S# = SC.S# AND SC.C# = C.C# AND C.CN=’MA’

带比较运算的子查询 当确定子查询的返回值是唯一时，可以使用比较运算 符(注意子查询在比较符后) SELECT S#,SN FROM S WHERE SD= (SELECT SD FROM S WHERE CN=’刘晨’)

带ANY和ALL的子查询（子查询返回多值时用） 检索其他系中比IS系任一学生年龄小的学生名单 SELECT S＃,SN FROM S WHERE SA < ANY (SELECT SA FROM S WHERE SD＝‘IS’) AND SD<>‘IS’ ORDER BY SA DESC 等价于 SELECT S＃，SN FROM S WHERE SA < (SELECT MAX（SA） FROM S WHERE SD＝‘IS’) AND SD <> ‘IS’

检索其他系中比IS系所有学生年龄都小的学生名单 SELECT S＃，SN FROM S WHERE SA < ALL (SELECT SA FROM S WHERE SD＝‘IS’) AND SD<>‘IS’ 等价于 SELECT S＃，SN FROM S WHERE SA < (SELECT MIN（SA） FROM S WHERE SD＝‘IS’) AND SD <> ‘IS’ ORDER BY SA DESC

带EXISTS的子查询（不返回任何数据，只返回Ture和False） 检索所有选修了课程号为‘1’的学生姓名 SELECT SN FROM S WHERE EXISTS (SELECT * FROM SC WHERE S# = S.S# AND C# = ‘1’) 注意：此例中子查询的查询条件依赖于外层父查询，称此类查询为相关子查询(corelated subquery)。 等价连接实现： SELECT SN FROM S,SC WHERE S.S# = SC.S# AND C# = ‘1’

SQL中没有(x)p，故须转换为¬(x(¬p)) 如检索选修了全部课程的学生,即没有一门课没有选的学生 SELECT SN FROM S WHERE NOT EXISTS (SELECT * FROM C WHERE NOT EXISTS (SELECT * FROM SC WHERE C#＝ C.C# AND S# = S.S#)) p->q应被等价为¬p∨q 如检索至少选修了学生S001选修的全部课程的学生 令p=‘学生S001选修了y’ q＝‘学生x选修了y’ (y)（p->q）=¬y(¬(p->q))= ¬y(¬(¬p∨q))= ¬y(p∧¬q)) 即不存在这么一门课程，学生S001选修了而x没有选修 SELECT SN FROM S WHERE NOT EXISTS( SELECT * FROM SC SC2 WHERE S# = ‘S001’ AND NOT EXISTS (SELECT * FROM SC WHERE C# = SC2.C# AND S# = S.S#))

使用交、并、差的集合运算概念，INTERSECT ，UNION，MINUS 3.3.4集合查询 使用交、并、差的集合运算概念，INTERSECT ，UNION，MINUS 检索计算机科学系及年龄不大于19岁的学生 SELECT * FROM S WHER SD=’CS’ UNION SELECT * FROM S WHERE SA<＝19 等价于： SELECT * FROM S WHERE SD＝‘CS’ OR SA <=19 检索选修了课程号为C01或C02的学生学号 SELECT S# FROM SC WHERE C＃＝‘C01’ UNION SELECT S# FROM SC WHERE C＃＝‘C02’ SELECT S# FROM SC WHERE C# IN (‘C01’，‘C02’)

检索同时选修了课程号为C01和C02的学生学号 SELECT S# FROM SC WHERE C＃＝‘C01’ INTERSECT SELECT S# FROM SC WHERE C＃＝‘C02’(仅ORACLE) 等价于： SELECT S# FROM SC WHERE C# = ‘C01’ AND S# IN (SELECT S# FROM SC WHERE C# = ‘C02’) 检索选修了课程号为C01而未选修C02的学生学号。 SELECT S# FROM SC WHERE C#＝‘C01’ MINUS SELECT S# FROM SC WHERE C#＝‘C02’(仅ORACLE) SELECT S# FROM SC WHERE C#＝‘C01’AND S# NOT IN (SELECT S# FROM SC WHERE C#＝‘C02’)

3.4 SQL的数据更新（DML） 3.4.1数据插入 插入单个元组 插入子查询结果 语法： INSERT INTO <表名>[ (<列名1> [, <列名2>]......)] VALUES(<常量1>[,<常量2>]......) INSERT INTO S VALUES(’S001’,‘张三’,‘男’,18,‘IS’) 插入子查询结果 语法: INSERT INTO <表名> [ (<列名1> [, <列名2>]......)]<子查询> 例：为所有学生插入一条选修C01课程的记录 INSERT INTO SC SELECT S#,‘C01’,null FROM S

3.4.2数据修改 语法: 修改某一个元组的值 修改多个元组的值 UPDATE <表名> SET <列名>＝<表达式>[, <列名>＝ <表达式>]...... [WHERE <条件>]; 修改某一个元组的值 将学生S001的年龄该为22岁 UPDATE S SET SA=22 WHERE S# =’S001’ 修改多个元组的值 将所有的学生年龄增加1岁 UPDATE S SET SA＝SA＋1

带子查询的修改语句 修改操作与数据库的一致性 将计算机科学系所有的学生成绩置零 UPDATE SC SET GR=0 WHERE ‘CS’ = (SELECT SD FROM S WHERE S# = SC.S#) (相关子查询) 不同的DBMS可以使用join实现同样功能，如SYBASE UPDATE SC set GR=0 from S where S.S#=SC.S# and SD=’CS’ 或 UPDATE SC set GR=0 where S# in (SELECT S# from S where SD=’CS’) 修改操作与数据库的一致性 如同时修改S和SC两表中的S#的值 为了保证数据库的一致性，引入事务概念

3.4.3数据删除 语法： 删除某一个元组的值 删除多个元组的值 带子查询的删除语句 DELETE FROM <表名> [WHERE <条件>]; 删除某一个元组的值 删除学号为S001的学生 DELETE FROM S WHERE S#=‘S001’ 删除多个元组的值 删除所有学生的选课记录 DELETE FROM SC 带子查询的删除语句 删除计算机科学系所有学生的选课记录 DELETE FROM SC WHERE ‘CS’=( SELECT SD FROM S WHERE S#=SC.S#) (相关子查询) DELETE from SC where S# in (SELECT S# from S where SD=’CS’) (非相关子查询)

3.5视图 视图只是一个窗口，其数据依赖于基本表 3.5.1定义视图 建立视图 语法 ： CREATE VIEW <视图名> [(<列名1>[，<列名2>......])] AS <子查询> [WITH CHECK OPTION] 列名在以下情况必须列出 子查询的目标列是集函数等，不是单纯的列 多表连接时出现同名的列作为视图字段 需要在视图中启用新的名字

WITH CHECK OPTION 表示对视图更新时自动验证子查询条件 行列子集视图：若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了码，称行列子集视图 例：建立学生视图 CREATE VIEW IS_S AS SELECT S#, SN , SA FROM S WHERE SD=’IS’ 建立信息系选修了C1号课程的学生视图 CREATE VIEW IS_S1 (S#, SN ,GR) AS SELECT S.S# ,SN ,GR FROM S, SC WHERE S.S# = SC.S# AND S.SD=’IS’ AND SC.C# = ‘C1’

视图之上可以建立视图 其它视图建立例子 建立选修了C1课程且成绩在90以上的学生视图 CREATE VIEW IS_S2 AS SELECT S#, SN, GR FROM IS_S1 WHERE GR>=90 其它视图建立例子 建立一个反映学生出生年月的视图 CREATE VIEW BT_S（S＃，SN，SB）AS SELECT S#, SN,2003－SA FROM S 建立一个学生学号和平均成绩的视图 CREATE VIEW S_G（S＃，AVG_GR）AS SELECT S#, AVG(GR) FROM SC GROUP BY S#

删除视图 语法 建立一个女学生的视图 CREATE VIEW S_F（S＃，SN，SS，SA，SD）AS SELECT * FROM S WHERE SS=’女’ 本视图在S表结构改变时会出错，解决办法是去掉列说明或改*为列表 删除视图 语法 DROP VIEW <视图名> 例子：删除视图IS_S DROP VIEW IS_S

把对视图的查询转化为对基本表的查询称为视图的消解(View Resolution) 3.5.2查询视图 把对视图的查询转化为对基本表的查询称为视图的消解(View Resolution) SELECT S＃，SA FROM IS_S WHERE SA <20 消解为: SELECT S# ,SA FROM S WHERE SD=’IS’ AND SA <20 SELECT * FROM S_G WHERE AVG_GR>90 SELECT S#, AVG(GR) FROM SC WHERE AVG(GR)>90 GROUP BY S# (错误) SELECT S#, AVG(GR) FROM SC GROUP BY S# HAVING AVG(GR)>90 （正确）

3.5.3更新视图 视图的修改 视图的插入 将信息系学生视图中学号为S001的学生姓名改为‘刘辰’ UPDATE IS_S SET SN=‘刘辰’ WHERE S#=‘S001’ 视图消解为： UPDATE S SET SN=‘刘辰’ WHERE S#=‘S001’AND SD=‘IS’ 视图的插入 在信息系学生视图中插入记录 INSERT INTO IS_S VALUES (‘S001’,‘刘辰’,20) 视图消解： INSERT INTO S VALUES (‘S001’,‘刘辰’,NULL,20,‘IS’)

视图的删除： 某些带聚合/集函数的视图是不可修改的 在信息系学生视图中插入记录 DELETE FROM IS_S WHERE S#=‘S001’ 视图消解 : DELETE FROM S WHERE S#=‘S001’AND SD=‘IS’ 某些带聚合/集函数的视图是不可修改的 例如: UPDATE S_G SET AVG_GR=80 WHERE S#=‘S001’ (错误)

不运行更新的视图规则 由两个以上基本表导出的视图 视图的字段来自常数或表达式，只运行DELETE 视图的字段来自集函数 视图中含有GROUP BY子句 视图中含有DISTINCT语句 视图定义有嵌套查询，且内层查询涉及到导出本视图的基本表 不允许更新的视图上定义的视图

3.5.4视图的用途 视图能简化用户的操作 视图可以使用户多角度看待同一数据 视图对重构数据库提供了一定的逻辑独立性 例如：S(S#, SN, SS, SA, SD)需要拆分为 SX(S#，SN, SS, SA), SY(S#, SD) 则可以通过视图来保证应用不需改变 CREATE VIEW S AS SELECT SX.S#, SN, SS, SA, SD FROM SX, SY WHERE SX.S# = SY.S# 视图能对数据提供安全保护

3.6数据控制语言（DCL） 3.6.1授权 语法 GRANT {ALL PRIVILEGES|<权限>[,<权限>... ...]} [ON <对象类型> <对象名>] TO {PUBLIC|<用户>[,<用户>]... ...} [WITH GRANT OPTION]; 示例： GRANT SELECT ON TABLE S TO USER1; GRANT ALL Privileges ON TABLE S, C TO U2,U3; GRANT SELECT ON TABLE SC TO PUBLIC; GRANT UPDATE(SD),SELECT ON TABLE S TO U4; GRANT INSERT ON TABLE SC TO U5 WITH GRANT OPTION; GRANT CREATETAB ON DATABASE S_C TO U8;

3.6.2收回权限 语法 示例 REVOKE {ALL PRIVILEGES|<权限>[,<权限>... ...]} [ON <对象类型> <对象名>] FROM {PUBLIC|<用户>[,<用户>]... ...}; 示例 REVOKE SELECT ON TABLE SC FROM PUBLIC; REVOKE UPDATE(SD),SELECT ON TABLE S FROM U4; REVOKE INSERT ON TABLE SC FROM U5;

3.7 嵌入式SQL语言 SQL语言是非过程的，而应用大多是过程化的，故通过高级语言来弥补SQL过程控制的不足. 将SQL嵌入(Embedded SQL )高级语言（宿主语言）来执行，称嵌入式SQL语言(简称ESQL)。

对于ESQL的处理，DBMS一般有两种处理方式： 预编译 修改和扩充宿主语言以处理SQL ESQL一般形式：EXEC SQL <SQL 语句> ESQL根据其作用不同分为两类 : 可执行语句 说明性语句 可执行语句包括DDL、DML、DCL。两类SQL语句 应和宿主语言两类语句出现在同一地方

数据库工作单元和主语言工作单元之间的通信有 3.7.2嵌入式SQL语句与主语言之间的通信 数据库工作单元和主语言工作单元之间的通信有 向主语言传递SQL语句的执行状态 主语言向SQL语句提供参数 将SQL语句查询数据库结果交主语言进一步处理 相应地通过SQLCA、主变量、游标来实现  SQL通信区 SQLCA（SQL Communication Area）是一个数据结构，定义语句 EXEC SQL INCLUDE SQLCA ; SQLCODE反映每次执行SQL语句的结果

主要功能：ESQL可以使用主语言的变量来输入和输出数据 分类：输入、输出主变量、指示变量 使用方法  主变量 主要功能：ESQL可以使用主语言的变量来输入和输出数据 分类：输入、输出主变量、指示变量 使用方法 所有主变量必须在定义区定义（BEGIN DECLARE SECTION、END DECLARE SECTION） 可以在SQL中任意表达式的对方出现 在SQL语句中，主变量前要加‘：’,而在主语言中不必加。 指示变量用于为输入变量赋空值或指示输出变量是否空值

使用原因：SQL语句是面向集合的，而主语言是面向记录的 主语言和SQL语言的分工  游标 使用原因：SQL语句是面向集合的，而主语言是面向记录的 主语言和SQL语言的分工 SQL语言负责直接与数据库打交道 主语言用来控制程序流程以及对SQL的执行结构进一步处理 SQL语言用主变量从主语言接受执行参数操作数据库－>SQL语言的执行状态由DBMS送至SQLCA->主语言从SQLCA取出状态信息，据此决定下一步操作。 SQL的执行结果通过主变量或游标传给主语言处理

3.7.3不使用游标的SQL语句 说明性语句 数据定义语句 数据控制语句 查询结果为单记录的SELECT语句 非CURRENT形式的UPDATE语句 非CURRENT形式的DELETE语句 INSERT语句

1 ）说明性语句 2 ）数据定义语句 EXEC SQL INCLUDE SQLCA； EXEC SQL BEGIN DECALRE SECTION； EXEC SQL END DECALRE SECTION 2 ）数据定义语句 EXEC SQL CREATE TABLE S( S# char(10), SN char(10), SS char(2), SA int, SD char(5)); EXEC SQL DROP TABLE； 数据定义语句中不允许使用主变量 EXEC SQL DROP TABLE :tablename; （错误）

3） 数据控制语句 4） 查询结果为单条记录SELECT语句 EXEC SQL GRANT SELECT ON TABLE S TO U1 语法 EXEC SQL SELECT [ALL|DISTINCT] {*|<目标表达式1> [,<目标表达式2> ... ...]} INTO <主变量1> [<指示变量1>] [,<主变量1> [<指示变量1>]......] FROM <表名或视图名1> [，<表名或视图名2>]... ... [WHERE <条件表达式>] [GROUP BY <列名表达式1>[，<列名表达式2>]] [HAVING <条件表达式> ] [ORDER BY <列名表达式1> [ASC|DESC][, <列名表达式2> [ASC|DESC]]

例： EXEC SQL SELECT S#, SN INTO :sno, :sn FROM S WHERE S# =:GivenSno 注意： into、where和having子句中均可以使用主变量，需要事先申明。 返回值某列为NULL时，系统会将指示变量赋值为-1，主变量不变。 如查询结果没有满足条件的记录，则DBMS置sqlcode值为100，正常有结果为0。 如结果不止单条，程序出错，SQLCA中包含返回信息

5 ）非CURRENT形式的UPDATE语句 EXEC SQL UPDATE SC SET GR=GR+:Raise WHERE C# =’C01’ 重新设置某个学生成绩 EXEC SQL UPDATE SC SET GR=:newgr WHERE S# =’S001’ 将计算机系所有同学成绩置空 Grid=-1 EXEC SQL UPDATE SC SET GR=:newgr :grid WHERE S# IN （SELECT S# FROM S WHERE SD=‘CS’） 等价： EXEC SQL UPDATE SC SET GR=NULL WHERE

6 ）非CURRENT形式的DELETE语句 例 删除某学生的选课情况 EXEC SQL DELETE FROM SC WHERE S# IN (SELECT S# FROM S WHERE SN=:sname) 或者: EXEC SQL DELETE FROM SC WHERE :sname= (SELECT SN FROM S WHERE S.S# = SC.S#)

7 ）INSERT语句 例： 某个学生选修了一门课程 grid=-1; EXEC SQL INSERT INTO SC VALUES (:sno, :cno, :gr :grid); 或者： EXEC SQL INSERT INTO SC (S#, C#) VALUES (:sno, :cno);

3.7.4使用游标的SQL语句 使用游标的语句有 查询结果为多条记录的SELECT语句 CURRENT形式的UPDATE语句 CURRENT形式的DELETE语句

1 ）查询结果为多条记录的SELECT语句 游标步骤 游标在SELECT语句的集合和主语言的一次只能处理一条记录之间架起桥梁 说明游标：仅仅是定义，并不执行查询 EXEC SQL DECLARE <游标名> CURSOR FOR <SELECT 语句> 打开游标：执行相应的查询，把结果放进缓冲区，并把指针指向第一条记录 EXEC SQL OPEN <游标名> 读取当前记录并推进游标指针 EXEC SQL FETCH <游标名> INTO <主变量>[<指示变量>][,<主变量>[<指示变量>]......] 关闭游标：释放缓冲区等资源 EXEC SQL CLOSE <游标名>；

例：查询某个指定系的所有学生情况 EXEC SQL INCLEDE SQLCA； //说明性语句 EXEC SQL BEGIN DECLARE SECTION； VARCHAR depname[5]； VARCHAR HSno[10]； VARCHAR HSname[10]； VARCHAR HSex[2]； int HSage； EXEC SQL END DECLARE SECTION； …

... ... gets(depname)； EXEC SQL DECLARE SX CURSOR FOR //说明游标 SELECT S# , SN ,SS , SA FROM S WHERE SD = :depname； EXEC SQL OPEN SX； //打开游标 WHILE (1){ EXEC SQL FETCH SX INTO :Hsno,:Hsname,:Hsex,:HSage； //读取当前记录并推进游标指针 if（sqlca.sqlcode!=SUCCESS）break； printf(“%s,%s,%s,%d\n”, Hsno, Hsname, Hsex, HSage); } EXEC SQL CLOSE SX； //关闭游标 :depname值改变后可以重新打开游标，获得不同的集合。

2） CURRENT形式的UPDATE和DELETE语句 操作步骤 说明游标 EXEC SQL DECLARE <游标名> CURSOR FOR <SELECT查询> FOR UPDATE [OF <列名>]； OPEN游标 FETCH游标 检查是否要修改或删除，若是执行DELETE或UPDATE，并且使用WHERE CURRENT OF <游标名> 处理完毕CLOSE游标

例：检索某系的学生，根据要求处理数据 ... ... EXEC SQL INCLEDE SQLCA； EXEC SQL BEGIN DECLARE SECTION； VARCHAR depname[5]； VARCHAR HSno[10]； VARCHAR HSname[10]； VARCHAR HSex[2]； int HSage； EXEC SQL END DECLARE SECTION； .... ...

gets(depname)； ... ... EXEC SQL DECLARE SX CURSOR FOR SELECT S# , SN ,SS , SA FROM S WHERE SD =:depname [FOR UPDATE OF SA]； EXEC SQL OPEN SX； WHILE (1){ EXEC SQL FETCH SX INTO :Hsno, :Hsname, :Hsex, :Hsage; if（sqlca.sqlcode!=SUCCESS）break；

printf(“UPDATE(U) or DELETE(D) or NO(N)?\n”); scanf(“%c”,&op); printf(“%s,%s,%s,%d\n”, Hsno, Hsname, Hsex, HSage); printf(“UPDATE(U) or DELETE(D) or NO(N)?\n”); scanf(“%c”,&op); if(op==’U’){ printf(“Input new age:”); scanf(“%d”,&newage); EXEC SQL UPDATE S SET SA=:newage WHERE CURRENT OF SX; } else if(OP==’D’) EXEC SQL DELETE FROM S WHERE CURRENT OF SX; else continue; ... ... EXEC SQL CLOSE SX；

3.7.5动态SQL语句（以SYBASE的ESQL为例） 在预编译时无法获得如下信息的必须使用动态SQL技术 ，未知信息可能包括： 主变量个数 主变量数据类型 SQL语句引用的数据对象

动态语句的四种实现方式 （仅介绍方法一） 特点：执行语句不能返回任何结果 语法 EXEC SQL EXECUTE IMMEDIATE {: host_variable | string} 例： EXEC SQL BEGIN DECLARE SECTION; CS_CHAR sqlstring[200]; EXEC SQL END DECLARE SECTION; char cond[150]; strcpy(sqlstring,"update titles set price=price*1.10 where "); printf("Enter search condition:"); scanf("%s", cond); strcat(sqlstring, cond); EXEC SQL EXECUTE IMMEDIATE :sqlstring;

3.8 存储过程*（T-SQL） 语法 语言要素： create procedure [owner.]procedure_name [(@parameter_name datatype [= default][output] [, @parameter_name datatype [= default][output]]...)] [with recompile] as <SQL_statements> 语言要素： 语句块 begin <statement block> end

变量 以@开始的为用户变量，以@@开始的为全局变量 定义变量：DECLARE @@rowcount 操作影响的行数 @@sqlstatus游标Fetch的状态 条件控制 if logical_expression statements [else [if logical_expression] statements]

循环控制 while boolean_expression statement break continue 顺序控制 label: goto label 返回值 return [integer_expression]

打印信息 print {format_string | @local_variable | @@global_variable} [, arg_list] select @local_variable | @@global_variable 执行 [execute] [@return_status =] [[[server.]database.]owner.]procedure_name [[@parameter_name =] value | [@parameter_name =] @variable [output] [,[@parameter_name =] value|[@parameter_name = ] @variable [output]...]] [with recompile]

例：给定学号，获得该学生成绩，若是C01课程，成绩加1，否则加2 CREATE PROCEDURE get_gr @sno varchar(10), @GR OUTPUT int AS DECLARE @cno varchar(5) BEGIN SELECT @cno=C#,@GR=GR FROM S WHERE S# = @sno IF (@cno =’C01’)THEN select @GR=@GR+1 ELSE select @GR=@GR+2 END 执行： declare @gr int execute get_gr ‘s001’,@gr output select @gr 或print @gr

S C SC S# SN SS SA SD 95001 李勇 男 20 CS 95002 刘晨 女 19 IS 95003 王名 18 MA 95004 张立 C SC C# CN CP CR 1 DB 5 4 2 MA 3 IS OS 6 DataStruct 7 DataProcess PASCAL S# C# GR 95001 1 92 2 85 3 88 95002 90 80

SELECT DISTINCT SD FROM S SELECT S#,SN FROM S S# SN 95001 李勇 95002 刘晨 95003 王名 95004 张立 SELECT SN，2005-SA FROM S SELECT DISTINCT SD FROM S SN 2005-SA 李勇 1985 刘晨 1986 王名 1987 张立 SD CS IS MA

SELECT * FROM SC WHERE C#=’3’ ORDER BY GR DESC 95001 3 88 95002 80 SELECT C#,COUNT(C#) FROM SC GROUP BY C# C# COUNT(C#) 1 2 3 SELECT S# FROM SC GROUP BY S# HAVING COUNT(*) >3 S# 95001

SELECT S.*,SC.* FROM S,SC WHERE S.S# = SC.S# SN SS SA SD SC.S# C# GR 95001 李勇 男 20 CS 2 85 1 92 3 88 95002 刘晨 女 19 IS 90 80 SELECT f.C#, s.CP FROM C f,C s WHERE f.CP=s.C# f.C＃ s.CP 1 7 3 5 6

SELECT S#,SN,SS,SA,SD,C#,GR FROM S LEFT OUTER JION SC ON S.S#=SC.S# 95001 李勇 男 20 CS 2 85 1 92 3 88 95002 刘晨 女 19 IS 90 80 95003 王名 18 MA null 95004 张立

WHERE S.S# = SC.S# AND SC.C#=’2’ AND SC.GR>=90 SELECT S.S# ,SN FROM S,SC WHERE S.S# = SC.S# AND SC.C#=’2’ AND SC.GR>=90 S# SN SC.C# SC.GR 95002 刘晨 2 90 SELECT S.S#,SN,C.CN,SC.GR from S,SC,C WHERE S.S# = SC.S# AND SC.C# = C.C# S.S# SN C.C# C.CN SC.GR 95001 李勇 1 DB 92 2 MA 85 3 IS 88 95002 刘晨 90 80