1
Pno(零件名 ) Pname（零件名称） Quty（ Quty）
101 Cam 150
102 Bolt 300
105 Cear 50
203 Belt 30
207 Wheel 120
215 Washer 1300
P（零件表）
SNO Sname Addr
51 Liming Beijing
52 Xinghua Tianjin
58 Kehai Beijing
67 Vesam Shanghai
69 Smith Shanghai
75 Huahe Beijing
S（ 供应商表 ）
2
Q （报价表）
SNO PNO Price D_time（供货时间） Deliquty（供货量）
51 101 25 10 50
51 105 42 15 100
52 101 20 15 75
52 203 13 7 50
58 102 9 5 200
67 207 34 12 0
67 215 4 3 500
69 105 36 20 40
69 203 15 10 30
表 4.1 零件供应数据库
3
第 4章 使用高级查询 ---SQL语言
本章要求
?了解 SQL语言的特点。
?掌握 SQL语言的四大功能及使用方法。
?重点掌握其数据查询功能及其使用。
4
4.1 背景
4.1.1 SQL语言的起源
1.1975年由 CHAMBERLIN和 BOYEE提出, 当时称为
SEQUEL(STUCTURED ENGLISH QUERY LANGUAGE)；
2.1981年 IBM推出其商用关系关系数据库 SQL/DS；
3.今天广泛应用于各种大型数据库, 如 SYBASE、
INFORMIX,ORACLE,DB2,INGRES等, 也用于各
种小型数据库, 如 FOXPRO,ACCESS。
5
4.1.2 SQL语言标准化
1,1986年, 美国国家标准化协会公布了 SQL语言的第
一个标准 SQL86；
2,1987年, ISO通过了 SQL86标准；
3,1989年, ISO推出了 SQL89标准；
4,1992年, ISO推出了 SQL92标准；
5,目前 SQL99（ 也称为 SQL3） 在起草中, 增加了面向
对象的功能 。
6
4.1.3 SQL语言的主要特点
1,SQL语言类似于自然语言, 简洁易用；
2,SQL语言是一种非过程语言；
3,SQL语言是一种面向集合的语言；
4,SQL语言既是自含式语言, 又是嵌入式语言 ；
5,SQL语言具有,
数据查询
数据定义
数据操纵
数据控制 四种语言一体化的功能。
7
4.1.4 SQL语言的基本概念
? 基本表 （ BASE TABLE）,是独立存在的表, 不是由其
它的表导出的表 。 一个关系对应一个基本表, 一个或
多个基本表对应一个存储文件 。
? 视图 （ VIEW）,是一个虚拟的表, 是从一个或几个基
本表导出的表 。 它本身不独立存在于数据库中, 数据
库中只存放视图的定义而不存放视图对应的数据, 这
些数据仍存放在导出视图的基本表中 。 当基本表中的
数据发生变化时, 从视图中查询出来的数据也随之改
变 。
8
4.2 SQL数据定义
4.2.1 字段数据类型
? SQL语言使用数据定义语言（简称 DDL）实现其数据定
功能，可对数据库用户、基本表、视图、索引进行定
义和撤消。
? 字段类型的定义和具体的 DBMS有关, 因此具体定义只
能从有关的 DBMS手册中得到 。 教材 P92列出了 SQL-92
支持的数据类型 。
9
4.2.2 创建, 修改和删除数据表
4.2.2.1 创建数据表
1,创建一个数据表时主要包括以下几个组成部分：
（ 1） 字段名
（ 2） 字段数据类型
（ 3） 字段的长度, 精度和小数位数；
2,创建数据表的 SQL语法格式为：
CREATE TABLE 表名 (列名 1 数据类型 1 [NOT NULL]
[,列名 2 数据类型 2 [NOT NULL] ]? )
10
例 4.1 建立一学生表 S
CREATE TABLE S(SNO CHAR(8),SN VARCHAR(20),
AGE INT,SEX CHAR(2) DEFAULT '男 ',DEPT
VARCHAR(20))
? 执行该语句后, 便产生了学生基本表的表框架, 此表
为一个空表 。
? 其中, SEX列的缺省值为, 男, 。
11
3,定义完整性约束
? 还可以对表进一步定义, 如主键, 空值的设定 。 在
SQL SERVER中可以定义五种类型的完整性约束, 下面
分别加以介绍：
① NULL/NOT NULL
② UNIQUE约束
UNIQUE约束用于指明基本表在某一列或多个列的组合
上的取值必须唯一。
③ PRIMARY KEY约束
PRIMARY KEY约束用于定义基本表的主键，起唯一标
识作用，其值不能为 NULL，也不能重复，以此来保证
实体的完整性 。
12
④ FOREIGN KEY约束
FOREIGN KEY约束指定某一个列或一组列作为外部
键，其中，包含外部键的表称为从表，包含外部键所引
用的主键或唯一键的表称主表。
⑤ CHECK约束
CHECK约束用来检查字段值所允许的范围, 以此来保
证域的完整性 。
13
例 4.2 建立包含完整性定义的学生表
CREATE TABLE S
(SNO CHAR(6) CONSTRAINT S_PRIM PRIMARY KEY,
SN CHAR(8) CONSTRAINT SN_CONS NOT NULL,
AGE NUMERIC(2) CONSTRAINT AGE_CONS NOT NULL
CONSTRAINT AGE_CHK CHECK (AGE BETWEEN 15 AND
50),SEX CHAR(2) DEFAULT '男 ',
DEPT CHAR(10) CONSTRAINT DEPT_CONS NOT NULL);
14
4.2.2.2 修改基本表
1.为已有的基本增加新列
用于增加新列和完整性约束，定义方式同 CREATE
TABLE语句中的定义方式相同，其语法格式为：
ALTER TABLE 表名 ADD <列定义 > |<完整性约束定义 >
? 例 4.3 在 S表中增加一个班号列和住址列。
ALTER TABLE S
ADD
CLASS_NO CHAR(6),
ADDRESS CHAR(40)
? 注意：使用此方式增加的新列自动填充 NULL值，所以
不能为增加的新列指定 NOT NULL约束 。
15
2,从基本表中删除某些属性（列）
其语法格式为：
ALTER TABLE 表名 DROP 列名
例 4.4 删除 S表中的 AGE列
ALTER TABLE S DROP AGE
16
3 删除基本表
? 删除后，该表中的数据和在此表上所建的索引都被删
除，而建立在该表上的视图不会随之删除，系统将继
续保留其定义，但已无法使用。
? 删除表的语法格式：
DROP TABLE <表名 >
? 例 4.5 删除表 S
DROP TABLE S
17
4.2.3 索引
4.2.3.1 索引的作用
? 索引是数据库随机检索的常用手段，它实际上就是记
录的关键字与其相应地址的对应表。此外在 SQL中，行
的唯一性也是通过建立唯一索引来维护的。
? 索引的作用可归纳为：
1,加快查询速度；
2,保证行的唯一性。
18
4.2.3.2 建立索引
建立索引的语句是 CREATE INDEX，其语法格式为：
CREATE [UNIQUE] INDEX <索引名 > ON <表名 > (<列名 1>
[ASC/DESC] [{,<列名 2>}] [ASC/DESC] ?)
? UNIQUE表明建立唯一索引。
? 次序用来指定索引值的排列顺序，可为 ASC（升序）或
DESC（降序），缺省值为 ASC。
19
例 4.6 为 S表在 SNO上建立唯一索引 IP。
CREATE UNIQUE INDEX ISNO ON S(SNO)
? 执行此命令后，为 S表建立一个索引名为 IP的唯一索引，
此索引为 SNO的 唯一索引,即对 S表中的行先按 SNO的递
增顺序索引。
? 由于有 UNIQUE的限制，所以该索引在 SNO列的排序上具
有唯一性，不存在重复值。
20
注意：
? 改变表中的数据（如增加或删除记录）时，索引将自
动更新。索引建立后，在查询使用该列时，系统将自
动使用索引进行查询。
? 索引数目无限制，但索引越多，更新数据的速度越慢。
对于仅用于查询的表可多建索引，对于数据更新频繁
的表则应少建索引。
21
4.2.3.3 删除索引
删除索引的语句是 DROP INDEX，其语法格式为：
DROP INDEX 索引名 ON 数据表名
例 4.7删除表 S的索引 ISNO。
DROP INDEX ISNO ON S
22
4.3 SQL数据查询
4.3.1 SELECT命令的格式与基本使用
? SQL语言提供 SELECT语句, 通过查询操作可得到所需的
信息 。
? SELECT语句的常用格式：
SELECT [ALL/DISTINCT] ＊ /选择列表
FROM 基表名
[WHERE 条件表达式 ]
[GROUP BY 列名 1[HAVING <条件表达式 >]]
[ORDER BY 列名 2[ASC|DESC]];
23
? SELECT语句的执行过程是,
?根据 WHERE子句的检索条件, 从 FROM子句指定的基
本表或视图中选取满足条件的元组, 再按照 SELECT
子句中指定的列, 投影得到结果表 。
?如果有 GROUP子句, 则将查询结果按照 <列名 1>相同
的值进行分组 。
?如果 GROUP子句后有 HAVING短语, 则只输出满
足 HAVING条件的元组 。
?如果有 ORDER子句, 查询结果还要按照 <列名 2>的
值进行排序 。
24
例 4.7 查询 Q表中的所有供应者号 。
SELECT DISTINCT SNO FROM Q
查询结果中的重复行被去掉
例 4.8 查询供应商的全部信息 。
SELECT * FROM S
用’ *’ 表示 S表的全部列名，而不必逐一列出。
上述查询均为不使用 WHERE子句的无条件查询，也称作
投影查询 。另外，利用投影查询可控制列名的顺序。
见下例 4.9：
例 4.9 查询全体供应商的姓名, 编号和地址 。
SELECT SNAME,SNO,ADDR FROM S
25
4.3.2 条件查询
? 当要在表中找出满足某些条件的行时, 则需使用
WHERE子句指定查询条件 。
? WHERE子句中, 条件通常通过三部分来描述：
① 列名；
② 比较运算符；
③ 列名, 常数 。
? 常用的比较运算符 见下表 4.2所示
26
表 4.2 常用的比较运算符
查询条件 谓词
比较 =,>,<,>=,<=,<>,
确定范围 BETWEEN AND,
NOT BETWEEN AND
确定集合 IN,NOT IN
字符匹配 LIKE,NOT LIKE
空值 IS NULL,IS NOT NULL
多重条件 AND,OR
27
4.3.2.1 比较大小
例 4.11 查询出 SNO为 52的供应者信息 。
SELECT * FROM S WHERE SNO=52
例 4.12 查询库存量小于 100的零件 。
SELECT * FROM P WHERE QUTY<100
28
4.3.2.2 多重条件查询
? 当 WHERE子句需要指定一个以上的查询条件时, 则需要
使用逻辑运算符 AND,OR或 NOT将其连结成复合的逻辑
表达式 。
? 其 优先级 由高到低为,NOT,AND,OR,用户可以使用
括号改变优先级 。
例 4.13 查询供应 101号或 102号零件且供货量大于 50的供
应商的编号 。
SELECT SNO
FROM Q
WHERE((PNO=101 OR PNO=102) AND (DELIQUTY>50))
29
4.3.2.3 确定范围
例 4.14 查询供应时间在 5至 10天之间的零件号, 供应者
号及供应时间, 并按时间排序 。
SELECT PNO,SNO,D_TIME
FROM Q
WHERE (D_TIME BETWEEN 5 AND 10)
ORDER BY D_TIME
? 等价于
SELECT PNO,SNO,D_TIME
FROM Q
WHERE D_TIME>=5 AND D_TIME<=10
ORDER BY D_TIME
30
4.3.2.4 确定集合
利用, IN” 操作可以查询属性值属于指定集合的元组 。
例 4.15 查询 ‘ BOLT’, ‘ CAM’, ‘ BELT’ 的库存量 。
SELECT PNO,PNAME,QUTY
FROM P
WHERE PNAME IN('BOLT','CAM','BELT ')
? 此语句也可以使用逻辑运算符, OR” 实现 。
SELECT PNO,PNAME,QUTY
FROM P
WHERE(PNAME='BOLT'OR PNAME='CAM'OR PNAME='BELT ')
31
4.3.2.5 部分匹配查询
? 上例均属于完全匹配查询, 当不知道完全精确的値时,
用户还可以使用 LIKE或 NOT LIKE进行部分匹配查询
（ 也称模糊查询 ） 。
? LIKE定义的一般格式为：
<属性名 > LIKE <字符串常量 >
? 属性名必须为字符型, 字符串常量的字符可以包含如下两个
特殊符号：
? *：表示任意长度的字符串；
??：表示任意单个字符 。
例 4.16 查询所有姓名第一个字母为, X” 的供应商的编号
和姓名 。
SELECT SNO,SNAME
FROM S
WHERE SNAME LIKE 'X*'
32
4.3.2.6 空值查询
? 某个字段没有值称之为具有空值（ NULL）。
例 4.17 查询没有考试成绩的学生的学号和相应的课程号 。
SELECT SNO,CNO
FROM SC
WHERE SCORE IS NULL
? 注意：这里的空值条件为 IS NULL, 不能写成
SCORE=NULL。
33
4.3.3 常用库函数及统计汇总查询
? SQL提供了许多 库函数, 增强了基本检索能力 。 常用的
库函数, 如表 4.3所示,
函数名称 功能
AVG 按列计算平均值
SUM 按列计算值的总和
MAX 求一列中的最大值
MIN 求一列中的最小值
COUNT 按列值计个数
表 4.3 常用库函数
34
例 4.18 检索 PNO为 101的零件的平均供货时间 。
SELECT ‘PNO 101’,AVG(D_TIME)
FROM Q WHERE PNO=101
? 注意：函数 SUM和 AVG只能对 数值型 字段进行计算。
例 4.19 求计地址为,beijing” 的供应商的总数。
SELECT COUNT(SNO) FROM S
WHERE ADDR='beijing‘
例 4.20 利用特殊函数 COUNT(*)求计地址为, beijing”
的供应商的总数 。
SELECT COUNT(*) FROM S
WHERE ADDR=‘beijing’
? COUNT（ *） 用来统计元组的个数
? 要消除重复行时可使用 DISTINCT关键字
35
4.3.4 分组查询
? GROUP BY子句可以将查询结果按属性列或属性列组合
在 行 的方向上进行分组, 每组在 属性列 或 属性列组合 上具
有相同的值 。
例 4.21 检索所有零件的最高最低价格, 并按零件号排序 。
SELECT PNO,MIN(PRICE) AS PRICEOFMINS,
MAX(PRICE) AS PRICEOFMAX
FROM Q
GROUP BY PNO
ORDER BY PNO
? GROUP BY子句按 PNO的值分组, 所有具有相同 PNO的
元组为一组, 对每一组使用函数 MAX和 MIN分别进行计
算出各种零件的最低及最高价格 。
36
? 若在分组后还要按照一定的条件进行筛选，则需使用
HAVING子句。
例 4.22 查询有多于一种价格的零件的最, 最高价 。
SELECT PNO,MIN(PRICE) AS PRICEOFMINS,
MAX(PRICE) AS PRICEOFMAX
FROM Q
GROUP BY PNO HAVING COUNT(*)>1
ORDER BY PNO
? GROUP BY子句按 PNO的值分组, 所有具有相同 PNO的元
组为一组, 对每一组使用函数 COUNT进行计算, 统计出
一组中的商品条数 。
? HAVING子句去掉不满足 COUNT（ *） >1的组 。
37
4.3.5 数据表连接及连接查询
? 数据表之间 的联系是通过表的 字段值 来体现的, 这种
字段称为 连接字段 。
? 连接操作的 目的 就是通过加在连接字段的条件将多个
表连接起来, 以便从多个表中查询数据 。
? 前面的查询都是针对一个表进行的, 当查询同时涉及
两个以上的表时, 称为 连接查询 。
? 表的连接方法有两种：
?方法 1,表之间满足一定的条件的行进行连接, 此
时 FROM子句中指明进行连接的表名, WHERE子句指
明连接的列名及其连接条件 。
?方法 2,利用关键字 JOIN进行连接 。
38
具体分为以下几种：
? INNER JOIN,显示符合条件的记录, 此为 默认值 ；
? LEFT （ OUTER） JOIN,显示符合条件的数据行以及左
边表中不符合条件的数据行, 此时右边数据行会以
NULL来显示, 此称为 左连接 ；
? RIGHT （ OUTER） JOIN,显示符合条件的数据行以及
右边表中不符合条件的数据行, 此时左边数据行会以
NULL来显示, 此称为 右连接；
? CROSS JOIN,会将一个表的每一笔数据和另一表的每
笔数据匹配成新的数据行 。 此称为 广义笛卡尔积 ；
? 当把 JOIN 关键词放于 FROM子句中时, 应有关键词 ON与
之相对应, 以表明连接的条件 。
39
4.3.5.1 等值连接与非等值连接
例 4.23 检索出供应者号为 51的供应者所供应的所有零件
的零件号, 零件名, 零件价格 。
方法 1：
SELECT P.PNO,P.PNAME,Q.PRICE
FROM P,Q
WHERE(P.PNO=Q.PNO
AND Q.SNO=51)
? 这里 SNO=51为 查询条件,
而 P.PNO = Q.PNO 为 连接条件,
PNO为 连接字段 。
40
? 连接条件的一般格式为：
[<表名 1>.] <列名 1> <比较运算符 > [<表名 2>.] <
列名 2>
方法 2：
SELECT P.PNO,P.PNAME,Q.PRICE
FROM(P INNER JOIN Q
ON P.PNO=Q.PNO)
WHERE Q.SNO=51
41
例 4.24 检索供应者 ‘ kehai’ 供应的零件细目 。
SELECT S.SNAME,Q.PNO,P.PNAME,Q.PRICE,Q.D_TIME
FROM ((P INNER JOIN Q ON P.PNO=Q.PNO) INNER
JOIN
S ON Q.SNO=S.SNO)
WHERE (S.SNAME='kehai')
? 本例涉及三个表, 称为 多表连接 。
42
4.3.5.2 自身连接
当一个表与其自已进行连接操作时, 称为表的 自身连接 。
例 4.25 查询能同时供应 207或 215零件的供应商号 。
? 要查询的内容均在同一表 Q中, 可以将表 Q分别取两个 别
名, 一个是 QA, 一个是 QB 。 将 QA,QB 中满足
QA.PNO=207且 QB.PNO=215的行连接起来 。 然后从中选择
出 QA.SNO=QB.SNPO的元组 。 这实际上是同一表 Q的自身
连接 。
SELECT
QA.SNO FROM Q AS QA,
Q AS QB
WHERE ((QA.PNO=207 AND QB.PNO=215) AND
QA.SNO=QB.SNO )
43
4.3.6 子查询
? 在 WHERE子句中包含一个形如 SELECT-FROM-WHERE的查询
块, 此查询块称为 子查询 或 嵌套查询, 包含子查询的语
句称为 父查询 或 外部查询 。
? 嵌套查询 可以将一系列简单查询构成复杂查询, 增强查
询能力 。
? 子查询的嵌套层次最多可达到 255层, 以层层嵌套的方
式构造查询充分体现了 SQL,结构化, 的特点 。
? 嵌套查询在执行时 由里向外 处理, 每个子查询是在上一
级外部查询处理之前完成, 父查询要用到子查询的结果 。
44
4.3.6.1 返回一个值的子查询
当子查询的返回值只有一个时, 可以使用比较运算符
=,>,<,>=,<=,<>将父查询和子查询连接起来 。
例 4.26 查询与 51号供应者地址相同的供应商号, 供应商
姓名 。
SELECT SNO,SNAME
FROM S
WHERE ADDR=(SELECT ADDR
FROM S
WHERE SNO=51)
45
? 此查询相当于分成两个查询块来执行 。 先执行子查询：
SELECT ADDR
FROM S
WHERE SNO=51
? 子查询向主查询只返回一个值, 即 51号供应商的地址
‘ beijing’, 然后以此作为父查询的条件, 相当于再
执行父查询,查询所有地址为 ‘ beijing’ 的供应商号,
姓名 。
SELECT SNO,SNAME
FROM S
WHERE ADDR=’beijing’
46
4.3.6.2 返回一组值的子查询
? 如果子查询的返回值不止一个, 而是一个集合时, 则
不能直接使用比较运算符, 可以在比较运算符和子查
询之间插入 IN或 [NOT]EXISTS。
1,使用 IN
? 例 4.27 检索库存量小于 200的零件的供应号 。
SELECT DISTINCT SNO FROM Q
WHERE PNO IN
(SELECT PNO FROM P WHERE QUTY<200)
47
2,使用 EXISTS
? EXISTS表示存在量词, 带有 EXISTS的子查询不返回任
何实际数据, 它只得到逻辑值, 真, 或, 假, 。
? 当子查询的的查询结果集合为非空时, 外层的 WHERE子
句返回真值, 否则返回假值 。 NOT EXISTS与此相反 。
? 含有 IN的查询通常可用 EXISTS表示, 但反过来不一定 。
48
例 4.28 检索可供应 207号零件的供应商名字 。
SELECT SNAME FROM S
WHERE EXISTS(SELECT * FROM Q
WHERE PNO=207 AND SNO=S.SNO)
? 当子查询 Q表存在一行记录满足其 WHERE子句中的条件
时, 则父查询便得到一个值, 重复执行以上过程, 直
到得出最后结果 。
49
例 4.29 查询没有供应零件的供应商姓名 。
SELECT SNAME FROM S
WHERE NOT EXISTS
(SELECT * FROM Q
WHERE SNO=S.SNO)
? 选出这样一些供应商名单，在 Q表中不存在他们供应商
品的记录。
50
4.3.7 使用了 UNION的查询
? SQL中提供了并 (UNION)运算 。 如果二个查询结果是并
相容的, 则可以并为一个查询结果, UNION运算自动消
去重复元组 。
? 例 4.30 检索库存量大于约 1000或由 67号供应者供应的
零件号 。
SELECT PNO FROM P
WHERE QUTY>1000 UNION
(SELECT PNO FROM Q
WHERE SNO=67)
51
课堂练习
设有如下一个数据库,
课题
课题编名 课题名称 课题经费 (万元 )
101 CAD 150
102 CAM 30
105 CAPP 50
203 CIMS 300
207 GT 12
215 ERP 130
52
人员编号 课题编号 设备费 (万元 ) 工作量 使用经费 (万元 )
51 101 15 400 80
51 105 5 180 25
52 101 10 400 70
52 203 40 600 200
58 102 30 5 200
67 207 1 90 12
67 215 25 700 30
69 105 4 200 25
69 203 15 600 100
情况
53
人员编号 人员名称 职称 性别 地址
51 张三 工程师 女 北京
52 李四 教授 男 天津
58 王五 博士 男 北京
67 赵露 硕士 男 上海
69 刘红 工程师 男 上海
75 杨达 教授 女 北京
人员
54
1,按要求写出下列 SQL查询语句：
① 查询课题经费小于 100万元的课题编号及其名称；
② 查询同时参加 207和 215课题的科研人员编号；
③ 查询情况表中所有人员的编号；
④ 检索 52号科研人员的信息；
⑤ 检索 101号课题的平均工作量；
⑥ 检索工作量在 200-400天的课题编号, 人员编号及工
作量, 并按工作量排序；
55
2,写出下列 SQL查询语句的含义：
① SELECT 人员编号 FROM 情况 WHERE 课题编号 IN
（ SELECT 课题编号 FROM 课题 WHERE 课题经费
<200）
② SELECT * FROM 人员 WHERE 人 员 编 号 IN
（ SELECT 人员编号 FROM 情况 WHERE 课题编号
IN （ SELECT 课题编号 FROM 课题 WHERE 课题经
费 <200 ））
③ SELECT 课题编号 FROM 情况 GROUP BY 课题编号
HAVING COUNT(*)>1
④ SELECT 人员姓名 FROM 人员 WHERE EXISTS
（ SELECT * FROM 情况 WHERE 情况,课题编号 =207
AND 情况,人员编号 =人员,人员编号 ）
⑤ SELECT 课题编号 FROM 课题 WHERE 课题经费 >100
UNION SELECT 课题编号 FROM 情况 WHERE 人员编
号 =67
56
课堂练习答案：
1、
① SELECT 课题编号, 课题名称 FROM 课题 WHERE 课
题经费 <100
② SELECT QK1.人员编号 FROM 情况 AS QK1,情况 AS
QK2 WHERE QK1.课题编号 =207 AND QK2.课题编号
=215 AND QK1.人员编号 = QK2.人员编号
③ SELECT DISTINCT 人员编号 FROM 情况
④ SELECT * FROM 人员 WHERE 人员编号 <‘S 2’
⑤ SELECT ‘ 101号人员 ’, AVG(工作量 ) FROM 情况
WHERE 课题编号 =101
⑥ SELECT 课题编号, 人员编号, 工作量 FROM 情况
57
课堂练习答案：
2、
① 检索参加了课题经费 <200万元的课题的科研人员编号；
② 检索参加了课题经费 <200万元的课题的科研人员基本
信息；
③ 检索至少有两人以上参与的课题编号；
④ 检索检索参与课题编号为 207的人员姓名；
⑤ 检索课题经费 >100或者 67号科研人员参与的课题编号 。
58
4.4 SQL数据更新
? SQL语言的数据更新语句 DML主要包括插入数据, 修改
数据和删除数据三种语句 。
4.4.1 插入数据记录
? 插入数据是把新的记录插入到一个已存在的表中 。 插
入数据使用语句 INSERT INTO,可分为以下几种情况 。
4.4.1.1 插入一行新记录
? 语法格式为：
INSERT INTO < 表名 >[(< 列名 1>[,< 列名 2>? ])]
VALUES(<值 >)
? 其中, <表名 >是指要插入新记录的表
<列名 >是可选项, 指定待添加数据的列
VALUES子句指定待添加数据的具体值 。
59
例 4.31 在 S表中插入一条供应者记录
（ SNO:53； SNAME:'daihong' ； ADDR:'tianjin' ）。
INSERT INTO S
VALUES (53,'daihong','tianjin')
60
4.4.1.2 插入多行记录
? 用于表间的拷贝, 将一个表中的数据抽取几行插入另
一表中, 可以通过子查询来实现 。
? 插入数据的命令语法格式为：
INSERT INTO <表名 > [(<列名 1>[,<列名 2>? ])]
子查询
例 4.32 现 在 已 建 立 了 一 新 表 QUO52
（ PNO,PNAME,PRICE,D_TIME),要求加入 52号供应者供
应零件的情况 。
?首先建立新表 QUO52：
CREATE TABLE QUO52
(pno SMALLINT,pname CHAR(20),
price FLOAT,D_time SMALLINT)
61
? 然后利用子查询求出 Q表中 52号供应者提供的零件号,
然后在 P表中查询到这几种零件的情况, 把结果存放在
新表 QUO52中 。
INSERT INTO QUO52
SELECT Q.PNO,PNAME,PRICE,D_TIME
FROM P,Q
WHERE Q.SNO=52
AND P.PNO=Q.PNO
62
4.4.2 修改数据记录
? SQL语言可以使用 UPDATE语句对表中的一行或多行记录
的某些列值进行修改, 其语法格式为：
UPDATE <表名 >
SET <列名 >=<表达式 > [,<列名 >=<表达式 >]?
[WHERE <条件 >]
其中：
? <表名 >是指要修改的表
? SET子句给出要修改的列及其修改后的值
? WHERE子句指定待修改的记录应当满足的条件, WHERE
子句省略时, 则修改表中的所有记录 。
63
4.4.2.1 修改一行
例 4.33 修改 203号零件的库存量为 30。
UPDATE P SET QUTY=30 WHERE PNO=203
4.4.2.2 修改多行
例 4.34 将所有 52号供应者供应的零件供应价格上调 10％ 。
UPDATE Q SET PRICE=PRICE+0.1*PRICE
WHERE SNO=52
64
4.4.3 删除数据记录
? 使用 DELETE语句可以删除表中的一行或多行记录, 其
语法格式为：
DELETE FROM <表名 > [WHERE <条件 >]
其中,
?<表名 >是指要删除数据的表 。
?WHERE子句指定待删除的记录应当满足的条件,
WHERE子句省略时, 则删除表中的所有记录 。
65
4.4.3.1 删除一行记录
例 4.35 删除 S表中 51号供应商的记录 。
DELETE
FROM S
WHERE SNO=51
执行此语句后, 从 Q表中删除一行 。
4.4.3.2 删除多行记录
例 4.36 删除供应者名为 ‘ vesam’ 供应的所有零件 。
DELETE FROM Q
WHERE SNO=(SELECT SNO FROM S
WHERE SNAME='vesam')
执行此语句后, 从 Q表中删除两行 。
66
4.5 视图
? 视图是 虚表, 其数据不存储, 其记录来自基本表, 只
在数据库中 存储其定义 。
4.5.1 定义和删除视图
4.5.1.1 定义视图
定义视图使用语句 CREATE VIEW，其语法格式为：
CREATE VIEW <视图名 >[(<视图列表 >)] AS <子查询 >
? 其中,<视图列表 >为可选项，省略时，视图的列名由
子查询的结果决定。
67
例 4.37 创建一个北京地区供应商供应的零件价目表视图
PBJ。
CREATE VIEW PBJ(S#,PNO,PRICE)
AS SELECT Q.SNO,PNO,PRICE
FROM Q,S
WHERE ADDR =‘beijing’ AND Q.SNO=S.SNO
? 视图创建后，只在数据字典中存放 视图的定义,而其
中的子查询 SELECT语句并不执行。只有当用户对视图
进行操作时，才按照视图的定义将数据从基本表中取
出。
68
4.5.1.2 删除视图
视图定义后可随时删除，删除视图的语法格式为：
DROP VIEW <视图名 >
例 4.38 删除由北京地区供应商供应的零件价目表视图
PBJ。
DROP VIEW PBJ
69
4.5.2 查询视图
? 视图定义后，对视图的查询操作如同对基本表的查询
操作一样。
例 4.36 通过视图 PBJ检索单价超过 20元的零件。
SELECT * FROM PBJ WHERE PRICE>20
? 由上例可以看出，当对一个基本表进行复杂的查询时，
可以先对基本表建立一个视图，然后只需对此视图进
行查询，这样就不必再键入复杂的查询语句，而将一
个复杂的查询转换成一个简单的查询，从而简化了查
询操作。
70
4.5.3 更新视图
? 由于视图是一张虚表，所以对视图的更新，最终实际
上是转换成对基本表的更新。
? 其更新操作包括 插入, 修改 和 删除 数据，
? 其语法格式如同对基本表的更新操作一样。
? 有些更新在理论上是不可能的，有些实现起来比较困
难，以下仅考虑可以更新的视图。
? 使用视图有如下几个优点：
1,利于数据保密
2,简化查询操作
3,保证数据的逻辑独立性 。
71
小 结
?本章以 ACCESS中的 SQL查询为例, 介绍了 SQL语言的
使用方法 。
?在讲解 SQL语言的同时, 进一步介绍了关系数据库
的有关概念, 如索引和视图的概念及其作用 。
?SQL语言具有数据定义, 数据查询, 数据更新, 数
据控制四大功能 。 其全部功能可以用表 4.4的 9个动
词概括出来 。
72
表 4.4 SQL语言的动词
?其中：其数据查询功能最为丰富和复杂，也非常重要，
初学者掌握起来有一定的困难，应反复上机加强练习。
SQL功能 动词
数据定义 CREATE,DROP,ALTER
数据查询 SELECT
数据操纵 INSERT,UPDATE,DELETE
数据控制 GRANT,REVOKE
Pno(零件名 ) Pname（零件名称） Quty（ Quty）
101 Cam 150
102 Bolt 300
105 Cear 50
203 Belt 30
207 Wheel 120
215 Washer 1300
P（零件表）
SNO Sname Addr
51 Liming Beijing
52 Xinghua Tianjin
58 Kehai Beijing
67 Vesam Shanghai
69 Smith Shanghai
75 Huahe Beijing
S（ 供应商表 ）
2
Q （报价表）
SNO PNO Price D_time（供货时间） Deliquty（供货量）
51 101 25 10 50
51 105 42 15 100
52 101 20 15 75
52 203 13 7 50
58 102 9 5 200
67 207 34 12 0
67 215 4 3 500
69 105 36 20 40
69 203 15 10 30
表 4.1 零件供应数据库
3
第 4章 使用高级查询 ---SQL语言
本章要求
?了解 SQL语言的特点。
?掌握 SQL语言的四大功能及使用方法。
?重点掌握其数据查询功能及其使用。
4
4.1 背景
4.1.1 SQL语言的起源
1.1975年由 CHAMBERLIN和 BOYEE提出, 当时称为
SEQUEL(STUCTURED ENGLISH QUERY LANGUAGE)；
2.1981年 IBM推出其商用关系关系数据库 SQL/DS；
3.今天广泛应用于各种大型数据库, 如 SYBASE、
INFORMIX,ORACLE,DB2,INGRES等, 也用于各
种小型数据库, 如 FOXPRO,ACCESS。
5
4.1.2 SQL语言标准化
1,1986年, 美国国家标准化协会公布了 SQL语言的第
一个标准 SQL86；
2,1987年, ISO通过了 SQL86标准；
3,1989年, ISO推出了 SQL89标准；
4,1992年, ISO推出了 SQL92标准；
5,目前 SQL99（ 也称为 SQL3） 在起草中, 增加了面向
对象的功能 。
6
4.1.3 SQL语言的主要特点
1,SQL语言类似于自然语言, 简洁易用；
2,SQL语言是一种非过程语言；
3,SQL语言是一种面向集合的语言；
4,SQL语言既是自含式语言, 又是嵌入式语言 ；
5,SQL语言具有,
数据查询
数据定义
数据操纵
数据控制 四种语言一体化的功能。
7
4.1.4 SQL语言的基本概念
? 基本表 （ BASE TABLE）,是独立存在的表, 不是由其
它的表导出的表 。 一个关系对应一个基本表, 一个或
多个基本表对应一个存储文件 。
? 视图 （ VIEW）,是一个虚拟的表, 是从一个或几个基
本表导出的表 。 它本身不独立存在于数据库中, 数据
库中只存放视图的定义而不存放视图对应的数据, 这
些数据仍存放在导出视图的基本表中 。 当基本表中的
数据发生变化时, 从视图中查询出来的数据也随之改
变 。
8
4.2 SQL数据定义
4.2.1 字段数据类型
? SQL语言使用数据定义语言（简称 DDL）实现其数据定
功能，可对数据库用户、基本表、视图、索引进行定
义和撤消。
? 字段类型的定义和具体的 DBMS有关, 因此具体定义只
能从有关的 DBMS手册中得到 。 教材 P92列出了 SQL-92
支持的数据类型 。
9
4.2.2 创建, 修改和删除数据表
4.2.2.1 创建数据表
1,创建一个数据表时主要包括以下几个组成部分：
（ 1） 字段名
（ 2） 字段数据类型
（ 3） 字段的长度, 精度和小数位数；
2,创建数据表的 SQL语法格式为：
CREATE TABLE 表名 (列名 1 数据类型 1 [NOT NULL]
[,列名 2 数据类型 2 [NOT NULL] ]? )
10
例 4.1 建立一学生表 S
CREATE TABLE S(SNO CHAR(8),SN VARCHAR(20),
AGE INT,SEX CHAR(2) DEFAULT '男 ',DEPT
VARCHAR(20))
? 执行该语句后, 便产生了学生基本表的表框架, 此表
为一个空表 。
? 其中, SEX列的缺省值为, 男, 。
11
3,定义完整性约束
? 还可以对表进一步定义, 如主键, 空值的设定 。 在
SQL SERVER中可以定义五种类型的完整性约束, 下面
分别加以介绍：
① NULL/NOT NULL
② UNIQUE约束
UNIQUE约束用于指明基本表在某一列或多个列的组合
上的取值必须唯一。
③ PRIMARY KEY约束
PRIMARY KEY约束用于定义基本表的主键，起唯一标
识作用，其值不能为 NULL，也不能重复，以此来保证
实体的完整性 。
12
④ FOREIGN KEY约束
FOREIGN KEY约束指定某一个列或一组列作为外部
键，其中，包含外部键的表称为从表，包含外部键所引
用的主键或唯一键的表称主表。
⑤ CHECK约束
CHECK约束用来检查字段值所允许的范围, 以此来保
证域的完整性 。
13
例 4.2 建立包含完整性定义的学生表
CREATE TABLE S
(SNO CHAR(6) CONSTRAINT S_PRIM PRIMARY KEY,
SN CHAR(8) CONSTRAINT SN_CONS NOT NULL,
AGE NUMERIC(2) CONSTRAINT AGE_CONS NOT NULL
CONSTRAINT AGE_CHK CHECK (AGE BETWEEN 15 AND
50),SEX CHAR(2) DEFAULT '男 ',
DEPT CHAR(10) CONSTRAINT DEPT_CONS NOT NULL);
14
4.2.2.2 修改基本表
1.为已有的基本增加新列
用于增加新列和完整性约束，定义方式同 CREATE
TABLE语句中的定义方式相同，其语法格式为：
ALTER TABLE 表名 ADD <列定义 > |<完整性约束定义 >
? 例 4.3 在 S表中增加一个班号列和住址列。
ALTER TABLE S
ADD
CLASS_NO CHAR(6),
ADDRESS CHAR(40)
? 注意：使用此方式增加的新列自动填充 NULL值，所以
不能为增加的新列指定 NOT NULL约束 。
15
2,从基本表中删除某些属性（列）
其语法格式为：
ALTER TABLE 表名 DROP 列名
例 4.4 删除 S表中的 AGE列
ALTER TABLE S DROP AGE
16
3 删除基本表
? 删除后，该表中的数据和在此表上所建的索引都被删
除，而建立在该表上的视图不会随之删除，系统将继
续保留其定义，但已无法使用。
? 删除表的语法格式：
DROP TABLE <表名 >
? 例 4.5 删除表 S
DROP TABLE S
17
4.2.3 索引
4.2.3.1 索引的作用
? 索引是数据库随机检索的常用手段，它实际上就是记
录的关键字与其相应地址的对应表。此外在 SQL中，行
的唯一性也是通过建立唯一索引来维护的。
? 索引的作用可归纳为：
1,加快查询速度；
2,保证行的唯一性。
18
4.2.3.2 建立索引
建立索引的语句是 CREATE INDEX，其语法格式为：
CREATE [UNIQUE] INDEX <索引名 > ON <表名 > (<列名 1>
[ASC/DESC] [{,<列名 2>}] [ASC/DESC] ?)
? UNIQUE表明建立唯一索引。
? 次序用来指定索引值的排列顺序，可为 ASC（升序）或
DESC（降序），缺省值为 ASC。
19
例 4.6 为 S表在 SNO上建立唯一索引 IP。
CREATE UNIQUE INDEX ISNO ON S(SNO)
? 执行此命令后，为 S表建立一个索引名为 IP的唯一索引，
此索引为 SNO的 唯一索引,即对 S表中的行先按 SNO的递
增顺序索引。
? 由于有 UNIQUE的限制，所以该索引在 SNO列的排序上具
有唯一性，不存在重复值。
20
注意：
? 改变表中的数据（如增加或删除记录）时，索引将自
动更新。索引建立后，在查询使用该列时，系统将自
动使用索引进行查询。
? 索引数目无限制，但索引越多，更新数据的速度越慢。
对于仅用于查询的表可多建索引，对于数据更新频繁
的表则应少建索引。
21
4.2.3.3 删除索引
删除索引的语句是 DROP INDEX，其语法格式为：
DROP INDEX 索引名 ON 数据表名
例 4.7删除表 S的索引 ISNO。
DROP INDEX ISNO ON S
22
4.3 SQL数据查询
4.3.1 SELECT命令的格式与基本使用
? SQL语言提供 SELECT语句, 通过查询操作可得到所需的
信息 。
? SELECT语句的常用格式：
SELECT [ALL/DISTINCT] ＊ /选择列表
FROM 基表名
[WHERE 条件表达式 ]
[GROUP BY 列名 1[HAVING <条件表达式 >]]
[ORDER BY 列名 2[ASC|DESC]];
23
? SELECT语句的执行过程是,
?根据 WHERE子句的检索条件, 从 FROM子句指定的基
本表或视图中选取满足条件的元组, 再按照 SELECT
子句中指定的列, 投影得到结果表 。
?如果有 GROUP子句, 则将查询结果按照 <列名 1>相同
的值进行分组 。
?如果 GROUP子句后有 HAVING短语, 则只输出满
足 HAVING条件的元组 。
?如果有 ORDER子句, 查询结果还要按照 <列名 2>的
值进行排序 。
24
例 4.7 查询 Q表中的所有供应者号 。
SELECT DISTINCT SNO FROM Q
查询结果中的重复行被去掉
例 4.8 查询供应商的全部信息 。
SELECT * FROM S
用’ *’ 表示 S表的全部列名，而不必逐一列出。
上述查询均为不使用 WHERE子句的无条件查询，也称作
投影查询 。另外，利用投影查询可控制列名的顺序。
见下例 4.9：
例 4.9 查询全体供应商的姓名, 编号和地址 。
SELECT SNAME,SNO,ADDR FROM S
25
4.3.2 条件查询
? 当要在表中找出满足某些条件的行时, 则需使用
WHERE子句指定查询条件 。
? WHERE子句中, 条件通常通过三部分来描述：
① 列名；
② 比较运算符；
③ 列名, 常数 。
? 常用的比较运算符 见下表 4.2所示
26
表 4.2 常用的比较运算符
查询条件 谓词
比较 =,>,<,>=,<=,<>,
确定范围 BETWEEN AND,
NOT BETWEEN AND
确定集合 IN,NOT IN
字符匹配 LIKE,NOT LIKE
空值 IS NULL,IS NOT NULL
多重条件 AND,OR
27
4.3.2.1 比较大小
例 4.11 查询出 SNO为 52的供应者信息 。
SELECT * FROM S WHERE SNO=52
例 4.12 查询库存量小于 100的零件 。
SELECT * FROM P WHERE QUTY<100
28
4.3.2.2 多重条件查询
? 当 WHERE子句需要指定一个以上的查询条件时, 则需要
使用逻辑运算符 AND,OR或 NOT将其连结成复合的逻辑
表达式 。
? 其 优先级 由高到低为,NOT,AND,OR,用户可以使用
括号改变优先级 。
例 4.13 查询供应 101号或 102号零件且供货量大于 50的供
应商的编号 。
SELECT SNO
FROM Q
WHERE((PNO=101 OR PNO=102) AND (DELIQUTY>50))
29
4.3.2.3 确定范围
例 4.14 查询供应时间在 5至 10天之间的零件号, 供应者
号及供应时间, 并按时间排序 。
SELECT PNO,SNO,D_TIME
FROM Q
WHERE (D_TIME BETWEEN 5 AND 10)
ORDER BY D_TIME
? 等价于
SELECT PNO,SNO,D_TIME
FROM Q
WHERE D_TIME>=5 AND D_TIME<=10
ORDER BY D_TIME
30
4.3.2.4 确定集合
利用, IN” 操作可以查询属性值属于指定集合的元组 。
例 4.15 查询 ‘ BOLT’, ‘ CAM’, ‘ BELT’ 的库存量 。
SELECT PNO,PNAME,QUTY
FROM P
WHERE PNAME IN('BOLT','CAM','BELT ')
? 此语句也可以使用逻辑运算符, OR” 实现 。
SELECT PNO,PNAME,QUTY
FROM P
WHERE(PNAME='BOLT'OR PNAME='CAM'OR PNAME='BELT ')
31
4.3.2.5 部分匹配查询
? 上例均属于完全匹配查询, 当不知道完全精确的値时,
用户还可以使用 LIKE或 NOT LIKE进行部分匹配查询
（ 也称模糊查询 ） 。
? LIKE定义的一般格式为：
<属性名 > LIKE <字符串常量 >
? 属性名必须为字符型, 字符串常量的字符可以包含如下两个
特殊符号：
? *：表示任意长度的字符串；
??：表示任意单个字符 。
例 4.16 查询所有姓名第一个字母为, X” 的供应商的编号
和姓名 。
SELECT SNO,SNAME
FROM S
WHERE SNAME LIKE 'X*'
32
4.3.2.6 空值查询
? 某个字段没有值称之为具有空值（ NULL）。
例 4.17 查询没有考试成绩的学生的学号和相应的课程号 。
SELECT SNO,CNO
FROM SC
WHERE SCORE IS NULL
? 注意：这里的空值条件为 IS NULL, 不能写成
SCORE=NULL。
33
4.3.3 常用库函数及统计汇总查询
? SQL提供了许多 库函数, 增强了基本检索能力 。 常用的
库函数, 如表 4.3所示,
函数名称 功能
AVG 按列计算平均值
SUM 按列计算值的总和
MAX 求一列中的最大值
MIN 求一列中的最小值
COUNT 按列值计个数
表 4.3 常用库函数
34
例 4.18 检索 PNO为 101的零件的平均供货时间 。
SELECT ‘PNO 101’,AVG(D_TIME)
FROM Q WHERE PNO=101
? 注意：函数 SUM和 AVG只能对 数值型 字段进行计算。
例 4.19 求计地址为,beijing” 的供应商的总数。
SELECT COUNT(SNO) FROM S
WHERE ADDR='beijing‘
例 4.20 利用特殊函数 COUNT(*)求计地址为, beijing”
的供应商的总数 。
SELECT COUNT(*) FROM S
WHERE ADDR=‘beijing’
? COUNT（ *） 用来统计元组的个数
? 要消除重复行时可使用 DISTINCT关键字
35
4.3.4 分组查询
? GROUP BY子句可以将查询结果按属性列或属性列组合
在 行 的方向上进行分组, 每组在 属性列 或 属性列组合 上具
有相同的值 。
例 4.21 检索所有零件的最高最低价格, 并按零件号排序 。
SELECT PNO,MIN(PRICE) AS PRICEOFMINS,
MAX(PRICE) AS PRICEOFMAX
FROM Q
GROUP BY PNO
ORDER BY PNO
? GROUP BY子句按 PNO的值分组, 所有具有相同 PNO的
元组为一组, 对每一组使用函数 MAX和 MIN分别进行计
算出各种零件的最低及最高价格 。
36
? 若在分组后还要按照一定的条件进行筛选，则需使用
HAVING子句。
例 4.22 查询有多于一种价格的零件的最, 最高价 。
SELECT PNO,MIN(PRICE) AS PRICEOFMINS,
MAX(PRICE) AS PRICEOFMAX
FROM Q
GROUP BY PNO HAVING COUNT(*)>1
ORDER BY PNO
? GROUP BY子句按 PNO的值分组, 所有具有相同 PNO的元
组为一组, 对每一组使用函数 COUNT进行计算, 统计出
一组中的商品条数 。
? HAVING子句去掉不满足 COUNT（ *） >1的组 。
37
4.3.5 数据表连接及连接查询
? 数据表之间 的联系是通过表的 字段值 来体现的, 这种
字段称为 连接字段 。
? 连接操作的 目的 就是通过加在连接字段的条件将多个
表连接起来, 以便从多个表中查询数据 。
? 前面的查询都是针对一个表进行的, 当查询同时涉及
两个以上的表时, 称为 连接查询 。
? 表的连接方法有两种：
?方法 1,表之间满足一定的条件的行进行连接, 此
时 FROM子句中指明进行连接的表名, WHERE子句指
明连接的列名及其连接条件 。
?方法 2,利用关键字 JOIN进行连接 。
38
具体分为以下几种：
? INNER JOIN,显示符合条件的记录, 此为 默认值 ；
? LEFT （ OUTER） JOIN,显示符合条件的数据行以及左
边表中不符合条件的数据行, 此时右边数据行会以
NULL来显示, 此称为 左连接 ；
? RIGHT （ OUTER） JOIN,显示符合条件的数据行以及
右边表中不符合条件的数据行, 此时左边数据行会以
NULL来显示, 此称为 右连接；
? CROSS JOIN,会将一个表的每一笔数据和另一表的每
笔数据匹配成新的数据行 。 此称为 广义笛卡尔积 ；
? 当把 JOIN 关键词放于 FROM子句中时, 应有关键词 ON与
之相对应, 以表明连接的条件 。
39
4.3.5.1 等值连接与非等值连接
例 4.23 检索出供应者号为 51的供应者所供应的所有零件
的零件号, 零件名, 零件价格 。
方法 1：
SELECT P.PNO,P.PNAME,Q.PRICE
FROM P,Q
WHERE(P.PNO=Q.PNO
AND Q.SNO=51)
? 这里 SNO=51为 查询条件,
而 P.PNO = Q.PNO 为 连接条件,
PNO为 连接字段 。
40
? 连接条件的一般格式为：
[<表名 1>.] <列名 1> <比较运算符 > [<表名 2>.] <
列名 2>
方法 2：
SELECT P.PNO,P.PNAME,Q.PRICE
FROM(P INNER JOIN Q
ON P.PNO=Q.PNO)
WHERE Q.SNO=51
41
例 4.24 检索供应者 ‘ kehai’ 供应的零件细目 。
SELECT S.SNAME,Q.PNO,P.PNAME,Q.PRICE,Q.D_TIME
FROM ((P INNER JOIN Q ON P.PNO=Q.PNO) INNER
JOIN
S ON Q.SNO=S.SNO)
WHERE (S.SNAME='kehai')
? 本例涉及三个表, 称为 多表连接 。
42
4.3.5.2 自身连接
当一个表与其自已进行连接操作时, 称为表的 自身连接 。
例 4.25 查询能同时供应 207或 215零件的供应商号 。
? 要查询的内容均在同一表 Q中, 可以将表 Q分别取两个 别
名, 一个是 QA, 一个是 QB 。 将 QA,QB 中满足
QA.PNO=207且 QB.PNO=215的行连接起来 。 然后从中选择
出 QA.SNO=QB.SNPO的元组 。 这实际上是同一表 Q的自身
连接 。
SELECT
QA.SNO FROM Q AS QA,
Q AS QB
WHERE ((QA.PNO=207 AND QB.PNO=215) AND
QA.SNO=QB.SNO )
43
4.3.6 子查询
? 在 WHERE子句中包含一个形如 SELECT-FROM-WHERE的查询
块, 此查询块称为 子查询 或 嵌套查询, 包含子查询的语
句称为 父查询 或 外部查询 。
? 嵌套查询 可以将一系列简单查询构成复杂查询, 增强查
询能力 。
? 子查询的嵌套层次最多可达到 255层, 以层层嵌套的方
式构造查询充分体现了 SQL,结构化, 的特点 。
? 嵌套查询在执行时 由里向外 处理, 每个子查询是在上一
级外部查询处理之前完成, 父查询要用到子查询的结果 。
44
4.3.6.1 返回一个值的子查询
当子查询的返回值只有一个时, 可以使用比较运算符
=,>,<,>=,<=,<>将父查询和子查询连接起来 。
例 4.26 查询与 51号供应者地址相同的供应商号, 供应商
姓名 。
SELECT SNO,SNAME
FROM S
WHERE ADDR=(SELECT ADDR
FROM S
WHERE SNO=51)
45
? 此查询相当于分成两个查询块来执行 。 先执行子查询：
SELECT ADDR
FROM S
WHERE SNO=51
? 子查询向主查询只返回一个值, 即 51号供应商的地址
‘ beijing’, 然后以此作为父查询的条件, 相当于再
执行父查询,查询所有地址为 ‘ beijing’ 的供应商号,
姓名 。
SELECT SNO,SNAME
FROM S
WHERE ADDR=’beijing’
46
4.3.6.2 返回一组值的子查询
? 如果子查询的返回值不止一个, 而是一个集合时, 则
不能直接使用比较运算符, 可以在比较运算符和子查
询之间插入 IN或 [NOT]EXISTS。
1,使用 IN
? 例 4.27 检索库存量小于 200的零件的供应号 。
SELECT DISTINCT SNO FROM Q
WHERE PNO IN
(SELECT PNO FROM P WHERE QUTY<200)
47
2,使用 EXISTS
? EXISTS表示存在量词, 带有 EXISTS的子查询不返回任
何实际数据, 它只得到逻辑值, 真, 或, 假, 。
? 当子查询的的查询结果集合为非空时, 外层的 WHERE子
句返回真值, 否则返回假值 。 NOT EXISTS与此相反 。
? 含有 IN的查询通常可用 EXISTS表示, 但反过来不一定 。
48
例 4.28 检索可供应 207号零件的供应商名字 。
SELECT SNAME FROM S
WHERE EXISTS(SELECT * FROM Q
WHERE PNO=207 AND SNO=S.SNO)
? 当子查询 Q表存在一行记录满足其 WHERE子句中的条件
时, 则父查询便得到一个值, 重复执行以上过程, 直
到得出最后结果 。
49
例 4.29 查询没有供应零件的供应商姓名 。
SELECT SNAME FROM S
WHERE NOT EXISTS
(SELECT * FROM Q
WHERE SNO=S.SNO)
? 选出这样一些供应商名单，在 Q表中不存在他们供应商
品的记录。
50
4.3.7 使用了 UNION的查询
? SQL中提供了并 (UNION)运算 。 如果二个查询结果是并
相容的, 则可以并为一个查询结果, UNION运算自动消
去重复元组 。
? 例 4.30 检索库存量大于约 1000或由 67号供应者供应的
零件号 。
SELECT PNO FROM P
WHERE QUTY>1000 UNION
(SELECT PNO FROM Q
WHERE SNO=67)
51
课堂练习
设有如下一个数据库,
课题
课题编名 课题名称 课题经费 (万元 )
101 CAD 150
102 CAM 30
105 CAPP 50
203 CIMS 300
207 GT 12
215 ERP 130
52
人员编号 课题编号 设备费 (万元 ) 工作量 使用经费 (万元 )
51 101 15 400 80
51 105 5 180 25
52 101 10 400 70
52 203 40 600 200
58 102 30 5 200
67 207 1 90 12
67 215 25 700 30
69 105 4 200 25
69 203 15 600 100
情况
53
人员编号 人员名称 职称 性别 地址
51 张三 工程师 女 北京
52 李四 教授 男 天津
58 王五 博士 男 北京
67 赵露 硕士 男 上海
69 刘红 工程师 男 上海
75 杨达 教授 女 北京
人员
54
1,按要求写出下列 SQL查询语句：
① 查询课题经费小于 100万元的课题编号及其名称；
② 查询同时参加 207和 215课题的科研人员编号；
③ 查询情况表中所有人员的编号；
④ 检索 52号科研人员的信息；
⑤ 检索 101号课题的平均工作量；
⑥ 检索工作量在 200-400天的课题编号, 人员编号及工
作量, 并按工作量排序；
55
2,写出下列 SQL查询语句的含义：
① SELECT 人员编号 FROM 情况 WHERE 课题编号 IN
（ SELECT 课题编号 FROM 课题 WHERE 课题经费
<200）
② SELECT * FROM 人员 WHERE 人 员 编 号 IN
（ SELECT 人员编号 FROM 情况 WHERE 课题编号
IN （ SELECT 课题编号 FROM 课题 WHERE 课题经
费 <200 ））
③ SELECT 课题编号 FROM 情况 GROUP BY 课题编号
HAVING COUNT(*)>1
④ SELECT 人员姓名 FROM 人员 WHERE EXISTS
（ SELECT * FROM 情况 WHERE 情况,课题编号 =207
AND 情况,人员编号 =人员,人员编号 ）
⑤ SELECT 课题编号 FROM 课题 WHERE 课题经费 >100
UNION SELECT 课题编号 FROM 情况 WHERE 人员编
号 =67
56
课堂练习答案：
1、
① SELECT 课题编号, 课题名称 FROM 课题 WHERE 课
题经费 <100
② SELECT QK1.人员编号 FROM 情况 AS QK1,情况 AS
QK2 WHERE QK1.课题编号 =207 AND QK2.课题编号
=215 AND QK1.人员编号 = QK2.人员编号
③ SELECT DISTINCT 人员编号 FROM 情况
④ SELECT * FROM 人员 WHERE 人员编号 <‘S 2’
⑤ SELECT ‘ 101号人员 ’, AVG(工作量 ) FROM 情况
WHERE 课题编号 =101
⑥ SELECT 课题编号, 人员编号, 工作量 FROM 情况
57
课堂练习答案：
2、
① 检索参加了课题经费 <200万元的课题的科研人员编号；
② 检索参加了课题经费 <200万元的课题的科研人员基本
信息；
③ 检索至少有两人以上参与的课题编号；
④ 检索检索参与课题编号为 207的人员姓名；
⑤ 检索课题经费 >100或者 67号科研人员参与的课题编号 。
58
4.4 SQL数据更新
? SQL语言的数据更新语句 DML主要包括插入数据, 修改
数据和删除数据三种语句 。
4.4.1 插入数据记录
? 插入数据是把新的记录插入到一个已存在的表中 。 插
入数据使用语句 INSERT INTO,可分为以下几种情况 。
4.4.1.1 插入一行新记录
? 语法格式为：
INSERT INTO < 表名 >[(< 列名 1>[,< 列名 2>? ])]
VALUES(<值 >)
? 其中, <表名 >是指要插入新记录的表
<列名 >是可选项, 指定待添加数据的列
VALUES子句指定待添加数据的具体值 。
59
例 4.31 在 S表中插入一条供应者记录
（ SNO:53； SNAME:'daihong' ； ADDR:'tianjin' ）。
INSERT INTO S
VALUES (53,'daihong','tianjin')
60
4.4.1.2 插入多行记录
? 用于表间的拷贝, 将一个表中的数据抽取几行插入另
一表中, 可以通过子查询来实现 。
? 插入数据的命令语法格式为：
INSERT INTO <表名 > [(<列名 1>[,<列名 2>? ])]
子查询
例 4.32 现 在 已 建 立 了 一 新 表 QUO52
（ PNO,PNAME,PRICE,D_TIME),要求加入 52号供应者供
应零件的情况 。
?首先建立新表 QUO52：
CREATE TABLE QUO52
(pno SMALLINT,pname CHAR(20),
price FLOAT,D_time SMALLINT)
61
? 然后利用子查询求出 Q表中 52号供应者提供的零件号,
然后在 P表中查询到这几种零件的情况, 把结果存放在
新表 QUO52中 。
INSERT INTO QUO52
SELECT Q.PNO,PNAME,PRICE,D_TIME
FROM P,Q
WHERE Q.SNO=52
AND P.PNO=Q.PNO
62
4.4.2 修改数据记录
? SQL语言可以使用 UPDATE语句对表中的一行或多行记录
的某些列值进行修改, 其语法格式为：
UPDATE <表名 >
SET <列名 >=<表达式 > [,<列名 >=<表达式 >]?
[WHERE <条件 >]
其中：
? <表名 >是指要修改的表
? SET子句给出要修改的列及其修改后的值
? WHERE子句指定待修改的记录应当满足的条件, WHERE
子句省略时, 则修改表中的所有记录 。
63
4.4.2.1 修改一行
例 4.33 修改 203号零件的库存量为 30。
UPDATE P SET QUTY=30 WHERE PNO=203
4.4.2.2 修改多行
例 4.34 将所有 52号供应者供应的零件供应价格上调 10％ 。
UPDATE Q SET PRICE=PRICE+0.1*PRICE
WHERE SNO=52
64
4.4.3 删除数据记录
? 使用 DELETE语句可以删除表中的一行或多行记录, 其
语法格式为：
DELETE FROM <表名 > [WHERE <条件 >]
其中,
?<表名 >是指要删除数据的表 。
?WHERE子句指定待删除的记录应当满足的条件,
WHERE子句省略时, 则删除表中的所有记录 。
65
4.4.3.1 删除一行记录
例 4.35 删除 S表中 51号供应商的记录 。
DELETE
FROM S
WHERE SNO=51
执行此语句后, 从 Q表中删除一行 。
4.4.3.2 删除多行记录
例 4.36 删除供应者名为 ‘ vesam’ 供应的所有零件 。
DELETE FROM Q
WHERE SNO=(SELECT SNO FROM S
WHERE SNAME='vesam')
执行此语句后, 从 Q表中删除两行 。
66
4.5 视图
? 视图是 虚表, 其数据不存储, 其记录来自基本表, 只
在数据库中 存储其定义 。
4.5.1 定义和删除视图
4.5.1.1 定义视图
定义视图使用语句 CREATE VIEW，其语法格式为：
CREATE VIEW <视图名 >[(<视图列表 >)] AS <子查询 >
? 其中,<视图列表 >为可选项，省略时，视图的列名由
子查询的结果决定。
67
例 4.37 创建一个北京地区供应商供应的零件价目表视图
PBJ。
CREATE VIEW PBJ(S#,PNO,PRICE)
AS SELECT Q.SNO,PNO,PRICE
FROM Q,S
WHERE ADDR =‘beijing’ AND Q.SNO=S.SNO
? 视图创建后，只在数据字典中存放 视图的定义,而其
中的子查询 SELECT语句并不执行。只有当用户对视图
进行操作时，才按照视图的定义将数据从基本表中取
出。
68
4.5.1.2 删除视图
视图定义后可随时删除，删除视图的语法格式为：
DROP VIEW <视图名 >
例 4.38 删除由北京地区供应商供应的零件价目表视图
PBJ。
DROP VIEW PBJ
69
4.5.2 查询视图
? 视图定义后，对视图的查询操作如同对基本表的查询
操作一样。
例 4.36 通过视图 PBJ检索单价超过 20元的零件。
SELECT * FROM PBJ WHERE PRICE>20
? 由上例可以看出，当对一个基本表进行复杂的查询时，
可以先对基本表建立一个视图，然后只需对此视图进
行查询，这样就不必再键入复杂的查询语句，而将一
个复杂的查询转换成一个简单的查询，从而简化了查
询操作。
70
4.5.3 更新视图
? 由于视图是一张虚表，所以对视图的更新，最终实际
上是转换成对基本表的更新。
? 其更新操作包括 插入, 修改 和 删除 数据，
? 其语法格式如同对基本表的更新操作一样。
? 有些更新在理论上是不可能的，有些实现起来比较困
难，以下仅考虑可以更新的视图。
? 使用视图有如下几个优点：
1,利于数据保密
2,简化查询操作
3,保证数据的逻辑独立性 。
71
小 结
?本章以 ACCESS中的 SQL查询为例, 介绍了 SQL语言的
使用方法 。
?在讲解 SQL语言的同时, 进一步介绍了关系数据库
的有关概念, 如索引和视图的概念及其作用 。
?SQL语言具有数据定义, 数据查询, 数据更新, 数
据控制四大功能 。 其全部功能可以用表 4.4的 9个动
词概括出来 。
72
表 4.4 SQL语言的动词
?其中：其数据查询功能最为丰富和复杂，也非常重要，
初学者掌握起来有一定的困难，应反复上机加强练习。
SQL功能 动词
数据定义 CREATE,DROP,ALTER
数据查询 SELECT
数据操纵 INSERT,UPDATE,DELETE
数据控制 GRANT,REVOKE
