北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3 SQL
? 自从 20世纪 80年代以来,SQL就是一个通用的、功能
极强的关系数据库语言。现在,SQL语言正从关系形
式（ ANSI SQL-92标准）转向对象 -关系形式（ ANSI
SQL-99标准,1999年颁布）。
? SQL语言是 1974年由 Boyce和 Chamberlin提出。 1986年
10月美国国家标准局 (ANSl)批准了 SQL作为关系数据
库语言的美国标准。同年公布了 SQL标准文本 (简称
SQL-86)。 1987年 6月国际标准化组织 (ISO)也采纳了此
标准。 1989年，美国国家标准局（ ANSI）采纳了新的
规范 SQL-89标准，取代 SQL-86，同时 SQL-89标准也被
国际标准化组织 (ISO)采纳。 1992年,ANSI/ISO颁布了
SQL2版本，标准的名称为 SQL-92。 SQL-92分称几个
顺序级别：从代表 SQL-89最小扩展集的,Entry”到
,Intermediate”和,Full”。完成于己于 1999年的 SQL-99
具有更加高级的特征（包括对象 -关系特性），亦称
SQL3。
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3 SQL
? 主要的几个数据库生产厂商并不可能完全遵守
SQL-99（以及更老的 SQL-92）。我们一般更
加关注在产品中已经实现的 SQL-99特征。
1.3.1 SQL特点
1.3.2数据定义
1.3.2.1 SQL模式的创建和删除
1.3.2.2 SQL提供的基本数据类型
1.3.2.3 定义、删除与修改基本表
1.3.2.4 建立与删除索引
1.3.2.5 视图的创建和删除
1.3.3 数据查询
1.3.3.1 简单查询
1.3.3.2 子查询
1.3.3.3 UNION运算和 FOR ALL条件
1.3.3.4 高级 SQL语法
1.3.3.5 集合函数
1.3.3.6 行分组
1.3.4 数据更新
1.3.4.1 插入数据
1.3.4.2 修改数据
1.3.4.3 删除数据
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.1 SQL特点
? 1)综合统一
? 2)高度非过程化
? 3)面向集合的操作方式
? 4)以同一种语法结构提供两种使用方式
? 5)语言简捷, 易学易用
和关系代数相比, 就查询能力而言, SQL并
没有根本的改进, 在关系代数查询方面的经验
可以成为用 SQL来实现查询的良好借鉴 。 在构
造查询时 SQL的 select语句比关系代数要灵活,
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.2数据定义
? SQL的数据定义功能包括对模式 (Schema),表
(关系, Table),视图 (View)和索引 (Index)
的创建, 删除和修改操作 。 如下表所示 。
操作对象
操 作 方 式
创 建 删 除 修 改
模式 CREATE SCHEMA DROP SCHEMA
表 CREATE TABLE DROP TABLE ALTER TABLE
视图 CREATE VIEW DROP VIEW
索引 CREATE INDEX DROP INDEX
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.2.1 SQL模式的创建和删除
? 在 SQL-99中, 模式 是表, 索引及其他数据库对
象 的集合 。 模式名通常是一个用户名 。 在 Core
SQL-99和当前大多数产品中, 当用户的数据库
帐户建立时, 其模式在用户名之后给出, 他们
不能再建立其他模式 。 SQL-99的扩展特性允许
用户建立附加模式, 一个 SQL模式由模式名和
模式拥有者的用户名或账号来确定 。
? SQL模式的创建可用 CREATE语句实现, 其句法
如下：
CREATE SCHEMA <模式名 > AUTHORIZATION <用户名 >
目前只有 DB2 UDB允许用户建立附加模式 。
其它产品中模式用用户名替代 。
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.2.1 SQL模式的创建和删除
? 在 SQL中还有一个, 目录, 概念 。 目录是 SQL环
境中所有模式的集合 。 包含数据库中定义的对
象的信息的表, 由系统维护 。 ORACLE叫数据字
典, DB2 UDB叫目录表, INFORMIX叫系统目录 。
目录表在建立数据库时建立, 用户不能更新,
但 DBA可以用 select获取这些信息 。
? 当一个 SQL模式及其所属的基本表, 视图等元
素都不需要时, 可以用 DROP语句撤消这个 SQL
模式 。 DROP语句的句法如下：
DROP SCHEMA <模式名 > [CASCADE|RESTRICT]
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.2.2 SQL提供的基本数据类型
SQL-99 ORACLE INFORMIX DB2 UDB 说明 C
char(n) char(n)
n<=4000
char(n)
n<=32767
char(n)
n<=254
定长字符型 char
array[n+1]
varchar(n) varchar(n)
varchar2(n)
varchar(n) varchar(n) 变长字符型 char
array[n+1]
numeric(p,d)
decimal(p,d)
numeric(p,d)
decimal(p,d)
number(p,d)
numeric(p,d)
decimal(p,d)
numeric(p,d)
decimal(p,d)
定点数，由 p位数
字 (不包括符号、
小数点 ) 小数点后
面有 d位数字
无
smallint smallint smallint smallint 短整数 short int
integer integer integer integer 长整数 int,long int
real real real real 浮点数 float
double
precision,
float,
float(n)
double
precision,
number,float
float(n)
double
precision,
float
double
precision,
double,float,
float(n)
取决于机器精
度的双精度浮
点数
至少为 n位精度
double
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.2.3 定义、删除与修改基本表
? 建立数据库最重要的一步就是定义一些基本表 。 SQL语
言使用 CREATE TABLE语句定义基本表, 一般格式如下：
CREATE TABLE <表名 >
(<列名 ><数据类型 >[列级完整性约束条件 ]
[,<列名 ><数据类型 >[列级完整性约束条件 ]]…
[,<表级完整性约束条件 >])；
? 例,CREATE TABLE Student
(Sno CHAR(5) PRIMARY KEY,
Sname CHAR(8) NOT NULL,
Sage SMALLINT CHECK(Sage BETWEEN 17 AND 22),
Ssex CHAR(2) CHECK(Ssex IN(’男 ’,’女 ’ )),
Sdept CHAR(20) DEFAULT(‘软件学院 ’ ));
创建学生表,Sno为主键 (非空唯一 ),Sname非空,Sage在 17到 20之
间取值,Ssex只能取 ‘ 男 ’ 或 ‘ 女 ’,Sdept默认值为 ‘ 软件学院 ’,
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.2.3 定义、删除与修改基本表
? 实际使用时要有用户 ID和密码,进入交互式环
境, 才能完成数据库操作 。
? 修改基本表
ALTER TABLE<表名 >
[ADD<新列名 ><数据类型 >[完整性约束 ]]
[DROP<完整性约束名 >]
[MODIFY<列名 ><数据类型 >];
其中 <表名 >是要修改的基本表, ADD子句用于
增加新列和新的完整性约束条件, DROP子句用
于删除指定的完整性约束条件, MODIPY子句用
于修改原有的列定义, 包括修改列名和数据类
型 。
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.2.3 定义、删除与修改基本表
? 删除基本表
DROP TABLE <表名 >
基本表一旦删除, 表中的数据, 此表上
建立的索引和视图都将自动被删除掉 。
因此执行删除基本表的操作一定要格外
小心 。
? 注意：有的系统, 如 Oracle,删除基本
表后建立在此表上的视图定义仍然保留
在数据字典中 。 但是, 当用户引用时就
报错 。
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.2.4建立与删除索引
? 索引的功能表现在以下 3方面 。
(1)使用索引可以明显地加快数据查询的速度
(2)使用索引可保证数据的唯一性
(3)使用索引可以加快连接速度
? 建立索引的原则
(1)索引的建立和维护由 DBA和 DBMS完成
(2)大表应当建索引, 小表则不必建索引
(3)对于一个基本表, 不要建立过多的索引
(4)根据查询要求建索引
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.2.4建立与删除索引
? CREATE [UNIQUE] [CLUSTER] INDEX <索
引名 > ON <表名 >
(< 列名 >[<asc|desc>[, < 列名 >[<
asc|desc >]])；
? 如果数据增加删改频繁, 系统会花费许
多时间来维护索引 。 这时, 可以删除一
些不必要的索引 。
DROP INDEX <索引名 >；
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.2.5视图的创建和删除
? 视图是关系数据库系统提供给用户以多种角度观
察数据库中数据的重要机制 。 视图一经定义, 就
可以和基本表一样被查询, 被删除, 我们也可以
在一个视图之上再定义新的视图, 但对视图的更
新 (增加, 删除, 修改 )操作则有一定的限制 。
? CREAT VIEW <视图名 >[(列名 >[,<列名 >]...)]
AS <子查询 > [WITH CHECK OPTION]；
其中子查询可以是任意复杂的 SELECT语句, 但通
常不允许含有 ORDER BY子句和 DISTINCT短语 。
WITH CHECK OPTION表示对 视 图进 行 UPDATE，
INSERT和 DELETE操作时要保证更新, 插入或删除
的行满足视图定义中的谓词条件 (即子查询中的条
件表达式 ) 。
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.2.5视图的创建和删除
? DROP VIEW <视图名 >；
视图删除后视图的定义将从数据字典中删除 。
但是由该视图导出的其他视图定义仍在数据字
典中, 不过该视图已失效 。 用户使用时会出错,
要用 DROP VIEW语句将它们一一删除 。
? DBMS执行 CREATE VIEW语句的结果只把视图的
定义存入数据字典, 并不执行其中的 SELECT。
在关系数据库中, 并不是所有的视图都是可更
新的,因为有些视图的更新不能的有意义的转
换成对基本表的更新 。 行列子集视图是可更新
的 。 各个 DBMS对视图的更新有自己的规定 。
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3 数据查询
? 数据查询是数据库的核心操作 。 SQL语言的数
据查询只有一条 SELECT语句,
SELECT[ALL|DISTINCT]{*|<目标列表达式 [[as]
别名 ]>[,<目标列表达式 [[as] 别名 ]>]...}
FROM<表名或视图名 >[,<表名或视图名 >]…
[WHERE<条件表达式 >]
[GROUP BY<列名 >[,<列名 >][HAVING<条件表达
式 >]]]
[ORDER BY< 列名 >[ASC|DESC][, < 列名
>[ASC|DESC]] …];
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3 数据查询
? Select语句的执行过程 可以理解为,
首先, 对 FROM子句中的所有表做关系乘积
接着, 删除不满足 WHERE子句的行
根据 GROUP BY子句对剩余的行进行分组
然后删除不满足 HAVING子句的组
求出 SELECT子句选择列表的表达式的值
若有关键词 DISTINCT存在, 则删除重复的行
? Select中的 标识符：一般的 SQL标识符是大小
写无关的 。 实际上 SQL在解释以前会把它们转
化为大写形式 。 一个标识符必须以一个字母打
头, Entry SQL-92和 Core SQL-99将一个标识
符字节数限制在 18个以内 。
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3 数据查询
? 表达式可以是数值表达式, 字符串表达式和日
期表达式等 。
数值表达式由常数, 表属性, 算术运算符, 算术函数
所组成 。
字符串表达式由常数, 表属性, 字符串运算符, 字符
串函数所组成 。
日期表达式由常数, 表属性, 日期运算符, 日期函数
所组成
? WHERE中使用谓词来表示条件 。 一般情况谓词
运算结果为 TRUE或 FALSE,但如果遇到空值时,
可能为 UNKNOWN。
? SQL查询的一个争议点即：对于同一个查询会
存在众多不同的构造方法 。
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3 ORACLE,INFORMIX,DB2 UDB
中的 一些 数学函数
名称 描述 结果
abs(n)
mod(n,b)
sqrt(n)
n的绝对值, n为数值型
n被 b除后得到的余数 。 n,b为
整数
n的平方根, n为整数或浮点数
另外, 三角函数, 指数函数,
对数函数, 幂函数和 round(n)
数值类型
整数
浮点
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3一些标准的和特定产品的串处理函数
SQL-99中的描述 ORACLE DB2 UDB INFORMIX
返回串长度 (整数个字符 )
CHAR_LENGTH(str)
返回子串,从 m个开始取 n个
SUBSTRING（ str）
FORM m FOR(n)
返回去掉左或右空格后得到的包
含空格的串
TRIM（ [[LEADING|TRAILING
|BOTH] [SET]FROM]str）
返回子串 str2在 str1中位置, 如
果指定 n,则从 n开始
POSITION（ str1 IN str2）
字母小写
LOWER（ str）
字母大写
UPPER（ str）
length(str)
substr(str,m[,n])
trim([[leading|
trailing|both]
[set] from] str),
ltrim(str[,set]),
rtrim(str[,set])
instr(str1,str2
[,n])
lower(str)
upper(str)
length(str)
substr(str,m[
,n])
ltrim(str);
rtrim(str)
posstr(str1,
str2[,n])
lcase(str)
ucase(str)
length(str),
char_length(str)
substr(str,m[,n]),
substring(str
from m for n )
trim([[leading|
trailing|both]
[set] from]str);
lower(str)
upper(str)
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3 SQL中的标准谓词
谓词 形式 例子
比较谓词
BETWEEN谓词
量化谓词
IN谓词
EXISTS谓词
IS NULL谓词
LIKE谓词
expr1θ {expr2|(subquery)}
expr1[NOT] BETWEEN expr2 and
expr3
exprθ [SOME|ANY|ALL]
(subquery)
expr [NOT] IN (subquery)
[NOT]EXISTS(subquery)
colname IS [NOT]NULL
cloname [NOT] LIKE val
[ESCAPE val]
p.price>(subquery)
c.discnt between 10.0
and 12.0
c.discnt>=all
(subquery)
pid in (select pid
from orders)
exist(select * …)
c.discnt is null
cname like ‘A%’
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.1 简单查询
? 例：检索定货记录中所有 pid值：
select pid from orders --结果中有重复的 pid
select distinct pid from orders --结果中 pid唯一
select缺省为 all,distinct没有出现时允许重复行, 缺
省情况不遵守行唯一性规则 。
? 例：在 orders表上生成每笔业务的利润 profit(收入减去
60%的成本, 顾客的折扣以及代理商的酬金 ):
select ordno,x.cid,x.aid,x.pid,.40*(x.qty*p.price)-
.01*(c.discnt+a.percent)*(x.qty*p.price) as profit
from orders as x,customer as c,agents as a,products as p
where c.cid=x.cid and a.aid=x.aid and p.pid=x.pid;
乘积 ×投影 π
选择 σ
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.1 简单查询
注,1） SQL-99规定 FROM子句中执行连接运算, 但
大多数产品通过笛卡尔积运算并且在 WHERE子句
中包含表示参与连接的列值相等的条件来模拟连
接运算, 具体实现方法 (执行计划, 查询优化 )各
不相同 。
2） FROM中 AS被省略, SQL仍能识别表别名 。
ORACLE和 INFORMIX使用 别名 或 表别名, DB2 UDB
使用 相关名 。
3） 列表达式的列名可以通过 AS指明, ORACLE
中称 列别名, INFORMIX中称 显示标签, DB2 UDB
中简单称为 列名 。 如果没有 AS,ORACLE中将完整
表达式文本作为列名 。
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.2 子查询
? 每个 Select查询都会生成一张表, 但我们不能像
关系代数表达式那样任意将一个 Select语句嵌入
另一个 Select语句 。 这是 SQL与关系代数的一个
很重要的不同点 。 例如,from子句中不能出现
select(SQL-99标准已去掉该限制, 但数据库产
品中并未完全实现 ),where子句中显然可以出现
select。
? 出现在另一个 select语句之内的 select语句形式
称为子查询 。 一个子查询能以许多种方式出现在
另一个 select语句的 WHERE子句条件中 。
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.2 子查询
? 1） IN谓词 (NOT IN)
例,求通过住在北京或上海的代理商订货的顾客的姓名和折扣,
select cname,discnt from customers where cid in
(select cid from orders where aid in
(select aid from agents where city in (‘北京 ’,’上海 ’ )))；
例,求由住在北京的顾客和住在北京的代理商组成的所有订货 ordno.
select ordno from orders where (cid,aid) in
(select cid,aid from customers c,agents a
where c.city=‘北京 ’ and a.city=‘北京 ’ );
以上两例为 不相关查询,内层子查询独立于外层的 select.
例,找出订购了产品 p05的顾客的名字,
select distinct cname from customers where ‘p05’ in
(select pid from orders where cid=customers.cid)
本例为 相关查询,子查询使用外层 select语句提供的数据,
SQL-99允许,有
些系统不允许,
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.2 子查询
? 2)量化比较谓词, exprθ [SOME|ANY|ALL] (subquery)
θ 为比较运算符 <,<=,=,<>,>,>=。 SOME和 ANY含义相
同, SOME是最新版本的推崇形式 。
例：找出佣金百分率最小的代理商 aid。
select aid from agents where percent <=all
(select percent from agents);
例：求出满足以下条件的顾客 cid:该顾客的 discnt小于任一住在北
京的顾客的 discnt.
错误,select cid from customer where discnt <any
(select discnt from customers where city=‘北京 ’ );
正确, select cid from customer where discnt <all
(select discnt from customers where city=‘北京 ’ );
注意 any不是任意
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.2 子查询
? 3)EXISTS谓词,EXISTS (Subquery)为真当且仅
当 子 查 询 返 回 一 个 非 空 集 合 ;NOT
EXISTS(Subquery)为真当且仅当返回集合为空,
例,求出既订购了产品 p01有订购了产品 p07的顾客 cid.
关系代数,πcid(σpid=’p01’(O))∩ πcid(σpid=’p07’(O))
select distinct cid from orders x where pid=‘p01’ and exists
(select * from orders where cid=x.cid and pid=‘p07’);
或 select distinct x.cid from orders x,orders y
where x.pid=‘p01’ and x.cid=y.cid and y.cid=‘p07’ ;
EXISTS的查询一般能找到等价的其他查询形式 。
使用和不
使用 exists
交
运
算
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.2 子查询
? NOT EXISTS确实为我们带来了一些新的功能 。
not exists能被用来实现关系代数的 MINUS运算 。
如果 R和 S是两个兼容表 (属性相同 A1… An),R-S
用 SQL计算,
select A1… An from R
where not exists (select * from S
where S.A1=R.A1 and ……… and S.An=R.An);
例,找出没有通过代理商 a03订货的顾客 cid.
关系代数,π cid(O)— π cid(σ aid=’a03’(O))
select distinct cid from orders x where not exists
(select * from orders where cid=x.cid and aid=‘a03’);
差
运
算
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.3 UNION运算和 FOR ALL条件
? 为了提供关系代数的 ∪ 运算, SQL使用
UNION：
subquery UNION [ALL] subquery
例：包含了顾客所在的或代理商所在或
两者皆在的城市名单 。
select city from customers union
select city from agents;
或 select city from customers union all
select city from agents;
行不重复
行重复
∪
运
算
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.3 UNION运算和 FOR ALL条件
? SQL中没有等价的 ÷ 运算 。 如果面临的查询, 要求被检
索的对象集合必须符合 ‘ 所有 ’ 这类关键词的条
件, (FOR ALL)时, 关系代数要用到除运算 。 SQL中可以：
1.表述要检索的候选对象的一个反例 (至少一个对象不
符合条件 ),.并建立 select语句 (选出所有反例 )； 2.建
立表示这类反例不存在的条件 (not exists)； 3.建立最
终 select。
例：求通过住在北京的所有代理商订了货的顾客 cid.
1.反例：住在北京但没有为所求顾客 c.cid订货的代理商：
select * from agents where a.city=‘北京 ’ and not exists
(select * from orders x where x.cid=c.cid and x.aid=a.aid)
2.反例不存在,not exists (反例 )
3.最终,select c.cid from customers where not exists
(select * from agents where a.city=‘北京 ’ and not exists
(select * from orders x where x.cid=c.cid and x.aid=a.aid));
÷
运
算
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.3 UNION运算和 FOR ALL条件
例：找出订购了所有被顾客 c006订购的商品的顾客的 cid.
反例：被 c006订购但没有被 c.cid订购的商品：
select p.pid from products p
where p.pid in (select pid from orders x where x.cid=‘c006’) and
not exists (select * from orders y where y.pid=p.pid and y.cid=c.cid)
反例不存在,not exists
(select p.pid from products p
where p.pid in (select pid from orders x where x.cid=‘c006’) and
not exists (select * from orders y where y.pid=p.pid and y.cid=c.cid))
最终,select cid from customers where
not exists (select p.pid from products p
where p.pid in (select pid from orders x where x.cid=‘c006’) and
not exists (select * from orders y where y.pid=p.pid and y.cid=c.cid)) ;
÷
运
算 被 c006订购
没有被 c.cid订购
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.4 高级 SQL语法
? 以下介绍的高级 SQL运算符不是 Entry SQL-92的
部分,但几乎都属于 SQL-99,目前产品中不一定支
持, 但可能出现在未来的数据库产品中 。
? 1)INTERSECT(∩) 和 EXCEPT(—)运算符
Subquery[UNION [ALL]|INTERSECT [ALL]|EXCEPT [ALL] Subquery]
注,Core SQL-99只有 EXCEPT而没有 EXCEPT ALL。
两个子查询从左到右的列类型是兼容的,即可
以并, 交, 差 。 如类型 char(5)和类型 char(10)
的列运算,结果为 char(10).
ORACLE提供 UNION,UNION ALL,INTERSECT、
MINUS(EXCEPT) 。 但 不 支 持 INTERSECT ALL 或
MINUS ALL。 DB2 UDB都支持 。
ALL考虑重
复行及数目
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.4 高级 SQL语法
例, (Q UNION ALL Q UNION ALL Q)
INTERSECT ALL (Q UNION ALL Q)
结果是 (Q UNION ALL Q),包含两个重复行 。
(Q UNION ALL Q UNION ALL Q)
INTERSECT (Q UNION ALL Q)
结果是 (Q),没有包含重复行 。
(Q UNION ALL Q UNION ALL Q)
EXCEPT ALL (Q UNION ALL Q)
结果是 (Q),没有包含重复行 。
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.4 高级 SQL语法
? 2)连接形式
通用形式,FROM tablename [[AS] corr_name [,…… ]…… ]
SQL-99,FROM后面还可以是：
一般形式：
tablename[[AS] corr_name[(colname[,colname…… ])]]
子查询：
(subquery) [AS] corr_name[(colname[,colname…… ])]
显式连接：
table1[INNER|{LEFT|RIGHT|FULL}[OUTER]]JOINtable2
ONcondition
显然 SQL-99中允许子查询出现在 FROM子句中 。 以上三
种形式在产品中并没有完全实现 。 使用时可以试用或帮
助 。
可以是视图名 ORACLE不能有 AS
允许为表的列重新命名
允许是子查询,此时别名必须
内连,左连,右连,外连
连接条件
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.4 高级 SQL语法
例：检索至少订购了一件价格低于 0.5的商品的顾客姓名 。
select distinct cname from
(orders o join products p on o.pid=p.pid)
join customers c on o.cid=c.cid
where p.price<0.5;
注, ORACLE仅提供左连和右连,而且语法与标准 SQL也不同,
SELECT … FROM T1,T2
WHERE [T1.c1[(+)]=T2.c2|T1.c1=T2.c2[(+)]] AND
condition
T2保留所有行 与 T1连不上的用 NULL
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.5 SQL中的集合函数
? SQL中称集合函数 (set function),ORACLE称组函数 (group
function),DB2 UDB称列函数 (column function),INFORMIX
称聚集函数 (aggregate function).
? 集合函数语法,SET_FUNCTION ([ALL|DISTINCT] col)|COUNT(*)
注,ALL时包括重复行, DISTINCT不包括重复行 。
WHERE子句比较操作中不能使用集合函数,如 discnt<max(…)
集合函数忽略了所有空值,
集合函数不允许嵌套使用,如 AVG(select MAX(dollars)……)
名称 参数类型 结果类型 描述
COUNT 任意 (*) 数值 出现次数
SUM 数值 数值 参数和
AVG 数值 数值 参数均值
MAX 字符,数值 字符,数值 最大值
MIN 字符,数值 字符,数值 最小值
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.6 SQL中行的分组
? SQL报表功能,根据某些列值的共性把一个表所包
含的全部行分成若干个子集,然后对每个子集执
行集合函数,
例,打印每个代理商为顾客 c002和 c003订购产品及产品
总数量,
select a.aid,aname,p.pid,pname,sum(qty)
from orders x,products p,agents a
where x.pid=p.pid and x.aid=a.aid
and x.cid in (‘c002’,’c003’)
group by a.aid,a.aname,p.pid,p.pname;
? GROUP BY对象的列上的空值会被分在同一组里,
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.6 SQL中行的分组
? 如果要去掉分组后的某些行,不能用 where,
只能用 HAVING子句,
例,求被至少两个顾客订购的产品 pid.
select pid from orders
group by pid having count(distinct cid) >=2;
? 如果没有 GROUP BY只有 HAVING,则整个结果
为一组,
? 基本 SQL select语句的通用形式不允许集合
函数的嵌套,但可以有变通的 GROUP BY形式,
例,求所有代理商的最大销售额的平均值,
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.6 SQL中行的分组
? 例,求所有代理商的最大销售额的平均值,
错, select avg(select max(dollars) from orders
group by aid);
对, SQL高级形式,ORACLE中可行,
select avg(t.x) from (select aid,max(dollars) as x
from orders group by aid) t;
常用作法,
creat view t as (select aid,max(dollars) as x
from orders group by aid);
select avg(t.x) from t;
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.4 SQL数据更新
? SQL中数据更新包括插入数据 (Insert),修改数据
(Update)和删除数据 (Delete)三条语句,
? 1.3.4.1 插入数据
INSERT INTO <表名 >[(<属性列 1>[,<属性列 2>…)]
[VALUES(expr1|NULL[,expr2|NULL]...)|Subquery]
将新元组插入指定表中,其中新记录属性列 1的值
为 expr1,属性列 2的值为 expr2...。 INTO子句中没
有出现的属性列, 新记录在这些列上将取空值 。 还
可通过子查询批量插入数据 。
? 在表定义时说明了 NOT NULL的属性列不能取空值,
否则会出错 。
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.4 SQL数据更新
? 如果 INTO子句中没有指明任何列名, 则新插入的
记录必须在每个属性列上均有值, 给定值对应于表
定义的字段顺序 。
? 子查询不需要用括号括起来 。
insert into my_c select * from customers where
city=‘北京 ’ ;
insert into my_c (select * from customers where
city=‘北京 ’ );
? 1.3.4.1 修改 数据
UPDATE <表名 >
SET <列名 1>=<expr1|NULL|(Subquery)>
[,<列名 2>=<expr2|NULL|(Subquery)>...]
[WHERE <条件 >]；
与 customers表结构一样
错误
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.4 SQL数据更新
? 其功能是修改指定表中满足 WHERE子句条件的元组 。
其中 SET子句给出 expr的值用于取代相应的属性列
值 。 如果省略 WHERE子句, 则表示要修改表中的所
有元组 。
? 只有一个表可以作为 UPDATE的对象 。 有些系统中不
允许在 SET子句中使用限定属性名 。
update agents set agents.percent=1.1*agents.percent
? Entry SQL-92中 SET子句中不能用子查询, 但 SQL-
99及 ORACLE,DB2 UDB等都支持 。
用 customers表中最新 discnt更新 my_c中 discnt
update my_c set discnt=(select discnt from
customers where cid=my_c.cid)
错误
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.4 SQL数据更新
? 删除语句的一般格式为：
DELETE FROM <表名 > [WHERE <条件 >]
从指定表中删除满足 WHERE子句条件的所有元组 。 如果
省略 WHERE子句,表示删除表中全部元组,但表的定义仍
在字典中 。 DELETE语句删除的是表中的数据, 而不是关
于表的定义 。
? 删除条件中可以有子查询
删除总订货金额小于 600的代理商,
delete from agents where aid in (select aid from
orders group by aid having sum(dollars)<600);
? 增删改操作只能对一个表操作 。 因此在执行增删改操作
时,要注意数据库中数据的一致性 。
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
作业：
1.检索佣金百分率大于最小百分率的代理商 aid.
2.求通过住在北京或上海的代理商订货的顾客 cid.(使用子
查询和不使用子查询两种 )
3.R,S和 T具有相同的属性列 A1……An,不用高级 SQL来实现关
系表达式 (R UNION S) MINUS T.
4.求出没有为任何住在北京的顾客订购任何在天津生产的
产品的代理商的 aid.
5.没有一个包含 GROUP BY子句的查询会返回重复行,这一命
题为真吗?如果为真,解释原因 ;否则,给出反例,
6.说出你所用到 DBMS和其中的 SQL语句,他们与讲课时的不
一样或者不能用,
,数据库设计与开发, 讲义
1.3 SQL
? 自从 20世纪 80年代以来,SQL就是一个通用的、功能
极强的关系数据库语言。现在,SQL语言正从关系形
式（ ANSI SQL-92标准）转向对象 -关系形式（ ANSI
SQL-99标准,1999年颁布）。
? SQL语言是 1974年由 Boyce和 Chamberlin提出。 1986年
10月美国国家标准局 (ANSl)批准了 SQL作为关系数据
库语言的美国标准。同年公布了 SQL标准文本 (简称
SQL-86)。 1987年 6月国际标准化组织 (ISO)也采纳了此
标准。 1989年，美国国家标准局（ ANSI）采纳了新的
规范 SQL-89标准，取代 SQL-86，同时 SQL-89标准也被
国际标准化组织 (ISO)采纳。 1992年,ANSI/ISO颁布了
SQL2版本，标准的名称为 SQL-92。 SQL-92分称几个
顺序级别：从代表 SQL-89最小扩展集的,Entry”到
,Intermediate”和,Full”。完成于己于 1999年的 SQL-99
具有更加高级的特征（包括对象 -关系特性），亦称
SQL3。
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3 SQL
? 主要的几个数据库生产厂商并不可能完全遵守
SQL-99（以及更老的 SQL-92）。我们一般更
加关注在产品中已经实现的 SQL-99特征。
1.3.1 SQL特点
1.3.2数据定义
1.3.2.1 SQL模式的创建和删除
1.3.2.2 SQL提供的基本数据类型
1.3.2.3 定义、删除与修改基本表
1.3.2.4 建立与删除索引
1.3.2.5 视图的创建和删除
1.3.3 数据查询
1.3.3.1 简单查询
1.3.3.2 子查询
1.3.3.3 UNION运算和 FOR ALL条件
1.3.3.4 高级 SQL语法
1.3.3.5 集合函数
1.3.3.6 行分组
1.3.4 数据更新
1.3.4.1 插入数据
1.3.4.2 修改数据
1.3.4.3 删除数据
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.1 SQL特点
? 1)综合统一
? 2)高度非过程化
? 3)面向集合的操作方式
? 4)以同一种语法结构提供两种使用方式
? 5)语言简捷, 易学易用
和关系代数相比, 就查询能力而言, SQL并
没有根本的改进, 在关系代数查询方面的经验
可以成为用 SQL来实现查询的良好借鉴 。 在构
造查询时 SQL的 select语句比关系代数要灵活,
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.2数据定义
? SQL的数据定义功能包括对模式 (Schema),表
(关系, Table),视图 (View)和索引 (Index)
的创建, 删除和修改操作 。 如下表所示 。
操作对象
操 作 方 式
创 建 删 除 修 改
模式 CREATE SCHEMA DROP SCHEMA
表 CREATE TABLE DROP TABLE ALTER TABLE
视图 CREATE VIEW DROP VIEW
索引 CREATE INDEX DROP INDEX
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.2.1 SQL模式的创建和删除
? 在 SQL-99中, 模式 是表, 索引及其他数据库对
象 的集合 。 模式名通常是一个用户名 。 在 Core
SQL-99和当前大多数产品中, 当用户的数据库
帐户建立时, 其模式在用户名之后给出, 他们
不能再建立其他模式 。 SQL-99的扩展特性允许
用户建立附加模式, 一个 SQL模式由模式名和
模式拥有者的用户名或账号来确定 。
? SQL模式的创建可用 CREATE语句实现, 其句法
如下：
CREATE SCHEMA <模式名 > AUTHORIZATION <用户名 >
目前只有 DB2 UDB允许用户建立附加模式 。
其它产品中模式用用户名替代 。
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.2.1 SQL模式的创建和删除
? 在 SQL中还有一个, 目录, 概念 。 目录是 SQL环
境中所有模式的集合 。 包含数据库中定义的对
象的信息的表, 由系统维护 。 ORACLE叫数据字
典, DB2 UDB叫目录表, INFORMIX叫系统目录 。
目录表在建立数据库时建立, 用户不能更新,
但 DBA可以用 select获取这些信息 。
? 当一个 SQL模式及其所属的基本表, 视图等元
素都不需要时, 可以用 DROP语句撤消这个 SQL
模式 。 DROP语句的句法如下：
DROP SCHEMA <模式名 > [CASCADE|RESTRICT]
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.2.2 SQL提供的基本数据类型
SQL-99 ORACLE INFORMIX DB2 UDB 说明 C
char(n) char(n)
n<=4000
char(n)
n<=32767
char(n)
n<=254
定长字符型 char
array[n+1]
varchar(n) varchar(n)
varchar2(n)
varchar(n) varchar(n) 变长字符型 char
array[n+1]
numeric(p,d)
decimal(p,d)
numeric(p,d)
decimal(p,d)
number(p,d)
numeric(p,d)
decimal(p,d)
numeric(p,d)
decimal(p,d)
定点数，由 p位数
字 (不包括符号、
小数点 ) 小数点后
面有 d位数字
无
smallint smallint smallint smallint 短整数 short int
integer integer integer integer 长整数 int,long int
real real real real 浮点数 float
double
precision,
float,
float(n)
double
precision,
number,float
float(n)
double
precision,
float
double
precision,
double,float,
float(n)
取决于机器精
度的双精度浮
点数
至少为 n位精度
double
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.2.3 定义、删除与修改基本表
? 建立数据库最重要的一步就是定义一些基本表 。 SQL语
言使用 CREATE TABLE语句定义基本表, 一般格式如下：
CREATE TABLE <表名 >
(<列名 ><数据类型 >[列级完整性约束条件 ]
[,<列名 ><数据类型 >[列级完整性约束条件 ]]…
[,<表级完整性约束条件 >])；
? 例,CREATE TABLE Student
(Sno CHAR(5) PRIMARY KEY,
Sname CHAR(8) NOT NULL,
Sage SMALLINT CHECK(Sage BETWEEN 17 AND 22),
Ssex CHAR(2) CHECK(Ssex IN(’男 ’,’女 ’ )),
Sdept CHAR(20) DEFAULT(‘软件学院 ’ ));
创建学生表,Sno为主键 (非空唯一 ),Sname非空,Sage在 17到 20之
间取值,Ssex只能取 ‘ 男 ’ 或 ‘ 女 ’,Sdept默认值为 ‘ 软件学院 ’,
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.2.3 定义、删除与修改基本表
? 实际使用时要有用户 ID和密码,进入交互式环
境, 才能完成数据库操作 。
? 修改基本表
ALTER TABLE<表名 >
[ADD<新列名 ><数据类型 >[完整性约束 ]]
[DROP<完整性约束名 >]
[MODIFY<列名 ><数据类型 >];
其中 <表名 >是要修改的基本表, ADD子句用于
增加新列和新的完整性约束条件, DROP子句用
于删除指定的完整性约束条件, MODIPY子句用
于修改原有的列定义, 包括修改列名和数据类
型 。
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.2.3 定义、删除与修改基本表
? 删除基本表
DROP TABLE <表名 >
基本表一旦删除, 表中的数据, 此表上
建立的索引和视图都将自动被删除掉 。
因此执行删除基本表的操作一定要格外
小心 。
? 注意：有的系统, 如 Oracle,删除基本
表后建立在此表上的视图定义仍然保留
在数据字典中 。 但是, 当用户引用时就
报错 。
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.2.4建立与删除索引
? 索引的功能表现在以下 3方面 。
(1)使用索引可以明显地加快数据查询的速度
(2)使用索引可保证数据的唯一性
(3)使用索引可以加快连接速度
? 建立索引的原则
(1)索引的建立和维护由 DBA和 DBMS完成
(2)大表应当建索引, 小表则不必建索引
(3)对于一个基本表, 不要建立过多的索引
(4)根据查询要求建索引
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.2.4建立与删除索引
? CREATE [UNIQUE] [CLUSTER] INDEX <索
引名 > ON <表名 >
(< 列名 >[<asc|desc>[, < 列名 >[<
asc|desc >]])；
? 如果数据增加删改频繁, 系统会花费许
多时间来维护索引 。 这时, 可以删除一
些不必要的索引 。
DROP INDEX <索引名 >；
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.2.5视图的创建和删除
? 视图是关系数据库系统提供给用户以多种角度观
察数据库中数据的重要机制 。 视图一经定义, 就
可以和基本表一样被查询, 被删除, 我们也可以
在一个视图之上再定义新的视图, 但对视图的更
新 (增加, 删除, 修改 )操作则有一定的限制 。
? CREAT VIEW <视图名 >[(列名 >[,<列名 >]...)]
AS <子查询 > [WITH CHECK OPTION]；
其中子查询可以是任意复杂的 SELECT语句, 但通
常不允许含有 ORDER BY子句和 DISTINCT短语 。
WITH CHECK OPTION表示对 视 图进 行 UPDATE，
INSERT和 DELETE操作时要保证更新, 插入或删除
的行满足视图定义中的谓词条件 (即子查询中的条
件表达式 ) 。
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.2.5视图的创建和删除
? DROP VIEW <视图名 >；
视图删除后视图的定义将从数据字典中删除 。
但是由该视图导出的其他视图定义仍在数据字
典中, 不过该视图已失效 。 用户使用时会出错,
要用 DROP VIEW语句将它们一一删除 。
? DBMS执行 CREATE VIEW语句的结果只把视图的
定义存入数据字典, 并不执行其中的 SELECT。
在关系数据库中, 并不是所有的视图都是可更
新的,因为有些视图的更新不能的有意义的转
换成对基本表的更新 。 行列子集视图是可更新
的 。 各个 DBMS对视图的更新有自己的规定 。
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3 数据查询
? 数据查询是数据库的核心操作 。 SQL语言的数
据查询只有一条 SELECT语句,
SELECT[ALL|DISTINCT]{*|<目标列表达式 [[as]
别名 ]>[,<目标列表达式 [[as] 别名 ]>]...}
FROM<表名或视图名 >[,<表名或视图名 >]…
[WHERE<条件表达式 >]
[GROUP BY<列名 >[,<列名 >][HAVING<条件表达
式 >]]]
[ORDER BY< 列名 >[ASC|DESC][, < 列名
>[ASC|DESC]] …];
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3 数据查询
? Select语句的执行过程 可以理解为,
首先, 对 FROM子句中的所有表做关系乘积
接着, 删除不满足 WHERE子句的行
根据 GROUP BY子句对剩余的行进行分组
然后删除不满足 HAVING子句的组
求出 SELECT子句选择列表的表达式的值
若有关键词 DISTINCT存在, 则删除重复的行
? Select中的 标识符：一般的 SQL标识符是大小
写无关的 。 实际上 SQL在解释以前会把它们转
化为大写形式 。 一个标识符必须以一个字母打
头, Entry SQL-92和 Core SQL-99将一个标识
符字节数限制在 18个以内 。
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3 数据查询
? 表达式可以是数值表达式, 字符串表达式和日
期表达式等 。
数值表达式由常数, 表属性, 算术运算符, 算术函数
所组成 。
字符串表达式由常数, 表属性, 字符串运算符, 字符
串函数所组成 。
日期表达式由常数, 表属性, 日期运算符, 日期函数
所组成
? WHERE中使用谓词来表示条件 。 一般情况谓词
运算结果为 TRUE或 FALSE,但如果遇到空值时,
可能为 UNKNOWN。
? SQL查询的一个争议点即：对于同一个查询会
存在众多不同的构造方法 。
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3 ORACLE,INFORMIX,DB2 UDB
中的 一些 数学函数
名称 描述 结果
abs(n)
mod(n,b)
sqrt(n)
n的绝对值, n为数值型
n被 b除后得到的余数 。 n,b为
整数
n的平方根, n为整数或浮点数
另外, 三角函数, 指数函数,
对数函数, 幂函数和 round(n)
数值类型
整数
浮点
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3一些标准的和特定产品的串处理函数
SQL-99中的描述 ORACLE DB2 UDB INFORMIX
返回串长度 (整数个字符 )
CHAR_LENGTH(str)
返回子串,从 m个开始取 n个
SUBSTRING（ str）
FORM m FOR(n)
返回去掉左或右空格后得到的包
含空格的串
TRIM（ [[LEADING|TRAILING
|BOTH] [SET]FROM]str）
返回子串 str2在 str1中位置, 如
果指定 n,则从 n开始
POSITION（ str1 IN str2）
字母小写
LOWER（ str）
字母大写
UPPER（ str）
length(str)
substr(str,m[,n])
trim([[leading|
trailing|both]
[set] from] str),
ltrim(str[,set]),
rtrim(str[,set])
instr(str1,str2
[,n])
lower(str)
upper(str)
length(str)
substr(str,m[
,n])
ltrim(str);
rtrim(str)
posstr(str1,
str2[,n])
lcase(str)
ucase(str)
length(str),
char_length(str)
substr(str,m[,n]),
substring(str
from m for n )
trim([[leading|
trailing|both]
[set] from]str);
lower(str)
upper(str)
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3 SQL中的标准谓词
谓词 形式 例子
比较谓词
BETWEEN谓词
量化谓词
IN谓词
EXISTS谓词
IS NULL谓词
LIKE谓词
expr1θ {expr2|(subquery)}
expr1[NOT] BETWEEN expr2 and
expr3
exprθ [SOME|ANY|ALL]
(subquery)
expr [NOT] IN (subquery)
[NOT]EXISTS(subquery)
colname IS [NOT]NULL
cloname [NOT] LIKE val
[ESCAPE val]
p.price>(subquery)
c.discnt between 10.0
and 12.0
c.discnt>=all
(subquery)
pid in (select pid
from orders)
exist(select * …)
c.discnt is null
cname like ‘A%’
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.1 简单查询
? 例：检索定货记录中所有 pid值：
select pid from orders --结果中有重复的 pid
select distinct pid from orders --结果中 pid唯一
select缺省为 all,distinct没有出现时允许重复行, 缺
省情况不遵守行唯一性规则 。
? 例：在 orders表上生成每笔业务的利润 profit(收入减去
60%的成本, 顾客的折扣以及代理商的酬金 ):
select ordno,x.cid,x.aid,x.pid,.40*(x.qty*p.price)-
.01*(c.discnt+a.percent)*(x.qty*p.price) as profit
from orders as x,customer as c,agents as a,products as p
where c.cid=x.cid and a.aid=x.aid and p.pid=x.pid;
乘积 ×投影 π
选择 σ
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.1 简单查询
注,1） SQL-99规定 FROM子句中执行连接运算, 但
大多数产品通过笛卡尔积运算并且在 WHERE子句
中包含表示参与连接的列值相等的条件来模拟连
接运算, 具体实现方法 (执行计划, 查询优化 )各
不相同 。
2） FROM中 AS被省略, SQL仍能识别表别名 。
ORACLE和 INFORMIX使用 别名 或 表别名, DB2 UDB
使用 相关名 。
3） 列表达式的列名可以通过 AS指明, ORACLE
中称 列别名, INFORMIX中称 显示标签, DB2 UDB
中简单称为 列名 。 如果没有 AS,ORACLE中将完整
表达式文本作为列名 。
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.2 子查询
? 每个 Select查询都会生成一张表, 但我们不能像
关系代数表达式那样任意将一个 Select语句嵌入
另一个 Select语句 。 这是 SQL与关系代数的一个
很重要的不同点 。 例如,from子句中不能出现
select(SQL-99标准已去掉该限制, 但数据库产
品中并未完全实现 ),where子句中显然可以出现
select。
? 出现在另一个 select语句之内的 select语句形式
称为子查询 。 一个子查询能以许多种方式出现在
另一个 select语句的 WHERE子句条件中 。
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.2 子查询
? 1） IN谓词 (NOT IN)
例,求通过住在北京或上海的代理商订货的顾客的姓名和折扣,
select cname,discnt from customers where cid in
(select cid from orders where aid in
(select aid from agents where city in (‘北京 ’,’上海 ’ )))；
例,求由住在北京的顾客和住在北京的代理商组成的所有订货 ordno.
select ordno from orders where (cid,aid) in
(select cid,aid from customers c,agents a
where c.city=‘北京 ’ and a.city=‘北京 ’ );
以上两例为 不相关查询,内层子查询独立于外层的 select.
例,找出订购了产品 p05的顾客的名字,
select distinct cname from customers where ‘p05’ in
(select pid from orders where cid=customers.cid)
本例为 相关查询,子查询使用外层 select语句提供的数据,
SQL-99允许,有
些系统不允许,
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.2 子查询
? 2)量化比较谓词, exprθ [SOME|ANY|ALL] (subquery)
θ 为比较运算符 <,<=,=,<>,>,>=。 SOME和 ANY含义相
同, SOME是最新版本的推崇形式 。
例：找出佣金百分率最小的代理商 aid。
select aid from agents where percent <=all
(select percent from agents);
例：求出满足以下条件的顾客 cid:该顾客的 discnt小于任一住在北
京的顾客的 discnt.
错误,select cid from customer where discnt <any
(select discnt from customers where city=‘北京 ’ );
正确, select cid from customer where discnt <all
(select discnt from customers where city=‘北京 ’ );
注意 any不是任意
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.2 子查询
? 3)EXISTS谓词,EXISTS (Subquery)为真当且仅
当 子 查 询 返 回 一 个 非 空 集 合 ;NOT
EXISTS(Subquery)为真当且仅当返回集合为空,
例,求出既订购了产品 p01有订购了产品 p07的顾客 cid.
关系代数,πcid(σpid=’p01’(O))∩ πcid(σpid=’p07’(O))
select distinct cid from orders x where pid=‘p01’ and exists
(select * from orders where cid=x.cid and pid=‘p07’);
或 select distinct x.cid from orders x,orders y
where x.pid=‘p01’ and x.cid=y.cid and y.cid=‘p07’ ;
EXISTS的查询一般能找到等价的其他查询形式 。
使用和不
使用 exists
交
运
算
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.2 子查询
? NOT EXISTS确实为我们带来了一些新的功能 。
not exists能被用来实现关系代数的 MINUS运算 。
如果 R和 S是两个兼容表 (属性相同 A1… An),R-S
用 SQL计算,
select A1… An from R
where not exists (select * from S
where S.A1=R.A1 and ……… and S.An=R.An);
例,找出没有通过代理商 a03订货的顾客 cid.
关系代数,π cid(O)— π cid(σ aid=’a03’(O))
select distinct cid from orders x where not exists
(select * from orders where cid=x.cid and aid=‘a03’);
差
运
算
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.3 UNION运算和 FOR ALL条件
? 为了提供关系代数的 ∪ 运算, SQL使用
UNION：
subquery UNION [ALL] subquery
例：包含了顾客所在的或代理商所在或
两者皆在的城市名单 。
select city from customers union
select city from agents;
或 select city from customers union all
select city from agents;
行不重复
行重复
∪
运
算
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.3 UNION运算和 FOR ALL条件
? SQL中没有等价的 ÷ 运算 。 如果面临的查询, 要求被检
索的对象集合必须符合 ‘ 所有 ’ 这类关键词的条
件, (FOR ALL)时, 关系代数要用到除运算 。 SQL中可以：
1.表述要检索的候选对象的一个反例 (至少一个对象不
符合条件 ),.并建立 select语句 (选出所有反例 )； 2.建
立表示这类反例不存在的条件 (not exists)； 3.建立最
终 select。
例：求通过住在北京的所有代理商订了货的顾客 cid.
1.反例：住在北京但没有为所求顾客 c.cid订货的代理商：
select * from agents where a.city=‘北京 ’ and not exists
(select * from orders x where x.cid=c.cid and x.aid=a.aid)
2.反例不存在,not exists (反例 )
3.最终,select c.cid from customers where not exists
(select * from agents where a.city=‘北京 ’ and not exists
(select * from orders x where x.cid=c.cid and x.aid=a.aid));
÷
运
算
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.3 UNION运算和 FOR ALL条件
例：找出订购了所有被顾客 c006订购的商品的顾客的 cid.
反例：被 c006订购但没有被 c.cid订购的商品：
select p.pid from products p
where p.pid in (select pid from orders x where x.cid=‘c006’) and
not exists (select * from orders y where y.pid=p.pid and y.cid=c.cid)
反例不存在,not exists
(select p.pid from products p
where p.pid in (select pid from orders x where x.cid=‘c006’) and
not exists (select * from orders y where y.pid=p.pid and y.cid=c.cid))
最终,select cid from customers where
not exists (select p.pid from products p
where p.pid in (select pid from orders x where x.cid=‘c006’) and
not exists (select * from orders y where y.pid=p.pid and y.cid=c.cid)) ;
÷
运
算 被 c006订购
没有被 c.cid订购
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.4 高级 SQL语法
? 以下介绍的高级 SQL运算符不是 Entry SQL-92的
部分,但几乎都属于 SQL-99,目前产品中不一定支
持, 但可能出现在未来的数据库产品中 。
? 1)INTERSECT(∩) 和 EXCEPT(—)运算符
Subquery[UNION [ALL]|INTERSECT [ALL]|EXCEPT [ALL] Subquery]
注,Core SQL-99只有 EXCEPT而没有 EXCEPT ALL。
两个子查询从左到右的列类型是兼容的,即可
以并, 交, 差 。 如类型 char(5)和类型 char(10)
的列运算,结果为 char(10).
ORACLE提供 UNION,UNION ALL,INTERSECT、
MINUS(EXCEPT) 。 但 不 支 持 INTERSECT ALL 或
MINUS ALL。 DB2 UDB都支持 。
ALL考虑重
复行及数目
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.4 高级 SQL语法
例, (Q UNION ALL Q UNION ALL Q)
INTERSECT ALL (Q UNION ALL Q)
结果是 (Q UNION ALL Q),包含两个重复行 。
(Q UNION ALL Q UNION ALL Q)
INTERSECT (Q UNION ALL Q)
结果是 (Q),没有包含重复行 。
(Q UNION ALL Q UNION ALL Q)
EXCEPT ALL (Q UNION ALL Q)
结果是 (Q),没有包含重复行 。
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.4 高级 SQL语法
? 2)连接形式
通用形式,FROM tablename [[AS] corr_name [,…… ]…… ]
SQL-99,FROM后面还可以是：
一般形式：
tablename[[AS] corr_name[(colname[,colname…… ])]]
子查询：
(subquery) [AS] corr_name[(colname[,colname…… ])]
显式连接：
table1[INNER|{LEFT|RIGHT|FULL}[OUTER]]JOINtable2
ONcondition
显然 SQL-99中允许子查询出现在 FROM子句中 。 以上三
种形式在产品中并没有完全实现 。 使用时可以试用或帮
助 。
可以是视图名 ORACLE不能有 AS
允许为表的列重新命名
允许是子查询,此时别名必须
内连,左连,右连,外连
连接条件
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.4 高级 SQL语法
例：检索至少订购了一件价格低于 0.5的商品的顾客姓名 。
select distinct cname from
(orders o join products p on o.pid=p.pid)
join customers c on o.cid=c.cid
where p.price<0.5;
注, ORACLE仅提供左连和右连,而且语法与标准 SQL也不同,
SELECT … FROM T1,T2
WHERE [T1.c1[(+)]=T2.c2|T1.c1=T2.c2[(+)]] AND
condition
T2保留所有行 与 T1连不上的用 NULL
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.5 SQL中的集合函数
? SQL中称集合函数 (set function),ORACLE称组函数 (group
function),DB2 UDB称列函数 (column function),INFORMIX
称聚集函数 (aggregate function).
? 集合函数语法,SET_FUNCTION ([ALL|DISTINCT] col)|COUNT(*)
注,ALL时包括重复行, DISTINCT不包括重复行 。
WHERE子句比较操作中不能使用集合函数,如 discnt<max(…)
集合函数忽略了所有空值,
集合函数不允许嵌套使用,如 AVG(select MAX(dollars)……)
名称 参数类型 结果类型 描述
COUNT 任意 (*) 数值 出现次数
SUM 数值 数值 参数和
AVG 数值 数值 参数均值
MAX 字符,数值 字符,数值 最大值
MIN 字符,数值 字符,数值 最小值
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.6 SQL中行的分组
? SQL报表功能,根据某些列值的共性把一个表所包
含的全部行分成若干个子集,然后对每个子集执
行集合函数,
例,打印每个代理商为顾客 c002和 c003订购产品及产品
总数量,
select a.aid,aname,p.pid,pname,sum(qty)
from orders x,products p,agents a
where x.pid=p.pid and x.aid=a.aid
and x.cid in (‘c002’,’c003’)
group by a.aid,a.aname,p.pid,p.pname;
? GROUP BY对象的列上的空值会被分在同一组里,
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.6 SQL中行的分组
? 如果要去掉分组后的某些行,不能用 where,
只能用 HAVING子句,
例,求被至少两个顾客订购的产品 pid.
select pid from orders
group by pid having count(distinct cid) >=2;
? 如果没有 GROUP BY只有 HAVING,则整个结果
为一组,
? 基本 SQL select语句的通用形式不允许集合
函数的嵌套,但可以有变通的 GROUP BY形式,
例,求所有代理商的最大销售额的平均值,
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.3.6 SQL中行的分组
? 例,求所有代理商的最大销售额的平均值,
错, select avg(select max(dollars) from orders
group by aid);
对, SQL高级形式,ORACLE中可行,
select avg(t.x) from (select aid,max(dollars) as x
from orders group by aid) t;
常用作法,
creat view t as (select aid,max(dollars) as x
from orders group by aid);
select avg(t.x) from t;
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.4 SQL数据更新
? SQL中数据更新包括插入数据 (Insert),修改数据
(Update)和删除数据 (Delete)三条语句,
? 1.3.4.1 插入数据
INSERT INTO <表名 >[(<属性列 1>[,<属性列 2>…)]
[VALUES(expr1|NULL[,expr2|NULL]...)|Subquery]
将新元组插入指定表中,其中新记录属性列 1的值
为 expr1,属性列 2的值为 expr2...。 INTO子句中没
有出现的属性列, 新记录在这些列上将取空值 。 还
可通过子查询批量插入数据 。
? 在表定义时说明了 NOT NULL的属性列不能取空值,
否则会出错 。
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.4 SQL数据更新
? 如果 INTO子句中没有指明任何列名, 则新插入的
记录必须在每个属性列上均有值, 给定值对应于表
定义的字段顺序 。
? 子查询不需要用括号括起来 。
insert into my_c select * from customers where
city=‘北京 ’ ;
insert into my_c (select * from customers where
city=‘北京 ’ );
? 1.3.4.1 修改 数据
UPDATE <表名 >
SET <列名 1>=<expr1|NULL|(Subquery)>
[,<列名 2>=<expr2|NULL|(Subquery)>...]
[WHERE <条件 >]；
与 customers表结构一样
错误
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.4 SQL数据更新
? 其功能是修改指定表中满足 WHERE子句条件的元组 。
其中 SET子句给出 expr的值用于取代相应的属性列
值 。 如果省略 WHERE子句, 则表示要修改表中的所
有元组 。
? 只有一个表可以作为 UPDATE的对象 。 有些系统中不
允许在 SET子句中使用限定属性名 。
update agents set agents.percent=1.1*agents.percent
? Entry SQL-92中 SET子句中不能用子查询, 但 SQL-
99及 ORACLE,DB2 UDB等都支持 。
用 customers表中最新 discnt更新 my_c中 discnt
update my_c set discnt=(select discnt from
customers where cid=my_c.cid)
错误
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
1.3.4 SQL数据更新
? 删除语句的一般格式为：
DELETE FROM <表名 > [WHERE <条件 >]
从指定表中删除满足 WHERE子句条件的所有元组 。 如果
省略 WHERE子句,表示删除表中全部元组,但表的定义仍
在字典中 。 DELETE语句删除的是表中的数据, 而不是关
于表的定义 。
? 删除条件中可以有子查询
删除总订货金额小于 600的代理商,
delete from agents where aid in (select aid from
orders group by aid having sum(dollars)<600);
? 增删改操作只能对一个表操作 。 因此在执行增删改操作
时,要注意数据库中数据的一致性 。
北京邮电大学软件学院 郭文明 2003.06
,数据库设计与开发, 讲义
作业：
1.检索佣金百分率大于最小百分率的代理商 aid.
2.求通过住在北京或上海的代理商订货的顾客 cid.(使用子
查询和不使用子查询两种 )
3.R,S和 T具有相同的属性列 A1……An,不用高级 SQL来实现关
系表达式 (R UNION S) MINUS T.
4.求出没有为任何住在北京的顾客订购任何在天津生产的
产品的代理商的 aid.
5.没有一个包含 GROUP BY子句的查询会返回重复行,这一命
题为真吗?如果为真,解释原因 ;否则,给出反例,
6.说出你所用到 DBMS和其中的 SQL语句,他们与讲课时的不
一样或者不能用,
