5,2 结构化查询语言 SQL
5,2,1 SQL概念
结构化查询语言 SQL(Structured Query Language)
是一种介于关系代数与关系演算之间的语言,其功能包括 查询,操纵,定义和控制 四个方面,是一个通用的功能极强的关系数据库标准语言 。
SQL语言被确定为 关系数据库系统的国际标准,被绝大多数商品化关系数据库系统采用 。
使用 SQL语言,可以指定数据库要做什么,而不需要告诉 SQL如何访问数据库 。
5,2,1 SQL概念
1,SQL数据库的体系结构
SQL数据库的体系结构基本上是三级模式结构
SQL用户 用户 1 用户 2 用户 3 用户 4
视图 1 视图 2
基本表 2
存储文件 1
基本表 3基本表 1 基本表 4
存储文件 2 存储文件 3 存储文件 4
BaseTable
Stored File
View外模式对应于模式对应于内模式对应于
SQL数据库的体系结构具有如下特征
（ 1） 一个 SQL模式 (Schema)是表和约束的集合 。
（ 2） 一个表 (Table)是行 (Row)的集合,每行是列
(Column)的序列,每列对应一个数据项 。
（ 3）表可以是一个基本表，也可以是一个视图。基本表是实际存储在数据库中的表。
视图 是从基本表中导出的表,它本身不独立存储在数据库中,数据库中只存放视图的定义而不存放视图的数据,这些数据仍存放在导出视图的基本表中,因此 视图是一个虚表 。
结构化查询语言
5,2,1 SQL概念
一个 基本表 可以跨一个或多个 存储文件,
一个 存储文件 也可存放一个或多个 基本表,
一个 表 可以带若干 索引,
索引 也存放在 存储文件 中 。
每个 存储文件 对应外部存储器上一个 物理文件 。
在用户看来,视图和基本表是一样的,都是关系
(即表格 )。
结构化查询语言
5,2,1 SQL概念
3,SQL的组成,分成四个部分：
数据定义 SQL DDL：
定义 SQL模式,基本表,视图和索引 。
数据操纵 SQL DML：
数据操纵分成数据查询和数据更新两类 。 其中数据更新又分成插入,删除和修改三种操作 。
数据控制 SQL DCL：
数据控制包括对基本表和视图的授权,完整性规则的描述,事务控制语句等 。
嵌入式 SQL：
SQL语句在嵌入到宿主语言程序中时的使用规则。
结构化查询语言
5,2,1 SQL概念
SQL的数据定义部分包括对 SQL模式 (Schema),基本表 (关系,Table),视图 (View),索引 （ Index）
的创建和撤消操作 。
1,SQL模式的创建和撤消
（ 1） SQL模式的创建
SQL模式 (即数据库模式 )被定义为基本表的集合 。
SQL模式由模式名和模式拥有者的用户名或帐号来确定,并包含模式中每一个元素 (基本表,视图,索引等 )的定义 。
创建一个 SQL模式,就是定义了一个存储空间 。
结构化查询语言
5,2,2 SQL数据定义 DLL
创建 SQL模式的语法如下：
CREATE SCHEMA <模式名 > AUTHORIZATION <用户名 >
例如,定义教学数据库的 SQL模式：
CREATE SCHEMA ST_COURSE AUTHORIZATION 李斌
该模式名为 ST_COURSE,拥有者为李斌 。
创建 SQL模式一般不用? CREATE SCHEMA…”,而用
CREATE DATABASE…”。
结构化查询语言
5,2,2 SQL数据定义 DLL
（ 2） SQL模式的撤消
使用 DROP语句撤消 SQL模式 。 DROP语句的语法如下：
DROP SCHEMA <模式名 > [CASCADE|RESTRICT]
撤消方式有两种：
① CASCADE(连锁式 )方式,执行 DROP语句时,把 SQL模式及其下属的基本表,视图,索引等所有元素全部撤消；
② RESTRICT(约束式 )方式,执行 DROP语句时,只有当
SQL模式中没有任何下属元素时,才能撤消 SQL模式,
否则拒绝执行 DROP语句 。
结构化查询语言
5,2,2 SQL数据定义 DLL
5,2,2 SQL数据定义 DLL
DROP语句的语法：
DROP SCHEMA <模式名 > [CASCADE|RESTRICT]
例如,要撤消 SQL模式 ST_COURSE及其下属所有的元素,可用下列语句实现：
DROP SCHEMA ST_COURSE CASCADE
2,SQL提供的基本数据类型
（ 1） 数值型
INTEGER 长整数 (也可写成 INT)
SMALLINT 短整数
REAL 取决于机器精度的浮点数
DOUBLE PRECISION 取决于机器精度的双精度浮点数
FLOAT（ n） 浮点数,精度至少为 n位数字
NUMERIC（ p,d） 定点数,由 p位数字 （ 不包括符号,小数点 ） 组成,小数点后面有 d位数字 (也可写成
DECIMAL(P,d)或 DEC(P,d))
结构化查询语言
5,2,2 SQL数据定义 DLL
（ 2） 字符串型
CHAR（ n） 长度为 n的定长字符串
VARCHAR(n) 具有最大长度为 n的变长字符串
（ 3） 位串型
BIT(n) 长度为 n的二进制位串
BIT VARYING(n) 最大长度为 n的变长二进制位串
（ 4） 时间型
DATE 日期,包含年,月,日,形式为 YYYY-MM-DD
TIME 时间,包含一日的时,分,秒,
形式为 HH:MM:SS
结构化查询语言
5,2,2 SQL数据定义 DLL
3,基本表的创建,修改和撤消
对基本表结构的操作有三种：创建,修改和撤消 。
（ 1） 基本表的创建
句法,CREATE TABLE SQL模式名,基本表名
（ 列名 类型,
… …
完整性约束,
…… ）
结构化查询语言
5,2,2 SQL数据定义 DLL
创建基本表,需要定义基本表的结构包括属性和完整性规则,并指出它放在哪个模式中,为简单起见,模式名可省略不写 。
① 属性：每个属性的类型可以是基本类型,也可以是用户事先定义的类型 。
② 完整性规则：完整性规则主要有三种子句：
主键子句 (PRIMARY KEY)、
检查子句 (CHECK)
外键子句 (FOREIGN KEY)。
结构化查询语言
5,2,2 SQL数据定义 DLL
例 5.l 在有关零件、供应商、工程项目的数据库中，有四个关系，其结构如图 5.8所示：
图 5.8 零件、供应商、工程项目四个关系结构化查询语言
5,2,2 SQL数据定义 DLL
供应商关系,S(SNO,SNAME,STATUS,ADDR)
零件关系,P(PNO,PNAME,COLOR,WEIGHT)
工程项目关系,J(JNO,JNAME,CITY,BALANCE)
供应情况关系,SPJ(SNO,PNO,JNO,PRICE,QTY)
① 创建供应商关系 S：
结构化查询语言
5,2,2 SQL数据定义 DLL
CREATE TABLE S (SNO CHAR(4) NOT NULL,
SNAME CHAR(20) NOT NULL,
STATUS CHAR(10),
ADDR CHAR(20),
PRIMARY KEY(SNO));
供应商关系,S(SNO,SNAME,STATUS,ADDR)
关系 S有四个属性供应商号
(SNO)
字符型长度为 4
供应商名
(SNAME)
字符型长度为 20
状态 (STATUS)
字符型长度为 10地址 ( ADDR)字符型长度为 20
① 创建供应商关系 S：
结构化查询语言
5,2,2 SQL数据定义 DLL
CREATE TABLE S (SNO CHAR(4) NOT NULL,
SNAME CHAR(20) NOT NULL,
STATUS CHAR(10),
ADDR CHAR(20),
PRIMARY KEY(SNO));
供应商关系,S(SNO,SNAME,STATUS,ADDR)
主键是供应商号
SNO
本例中规定供应商号和供应商名不能取空值当一个属性定义为主键，其属性不能为空值，所以? NOT NULL”可以省略不写。
在 SQL中允许属性值为空值，当规定某一属性值不能为空值时，在定义该属性时写上保留字? NOT NULL”。
各属性的含义：
零件号 (PNO),零件名 （ PNAME）,颜色 （ COLOR）,
重量 （ WEIGHT）,单价 (PRICE),工程项目号 (JNO)、
工程项目名称 (JNAME),城市 (CITY),余额 （ BALANCE)、
供应数量 (QTY)。
供应商关系,S(SNO,SNAME,STATUS,ADDR)
零件关系,P(PNO,PNAME,COLOR,WEIGHT)
工程项目关系,J(JNO,JNAME,CITY,BALANCE)
供应情况关系,SPJ(SNO,PNO,JNO,PRICE,QTY)
结构化查询语言
5,2,2 SQL数据定义 DLL
② 创建基本表 P,J,SPJ：
结构化查询语言
5,2,2 SQL数据定义 DLL
CREATE TABLE J (JNO CHAR(4) NOT NULL,
JNAME CHAR(20),
CITY CHAR(20),
BALANCE NUMERIC(7,2),
PRIMARY KEY (JNO));
CREATE TABLE P (PNO CHAR(4) NOT NULL,
PNAME CHAR(20) NOT NULL,
COLOR CHAR(8),
WEIGHT SMALLINT,
PRIMARY KEY (PNO));
结构化查询语言
5,2,2 SQL数据定义 DLL
CREATE TABLE
SPJ (SNO CHAR(4) NOT NULL,（ 定长字符串 ）
PNO CHAR(4) NOT NULL,
JNO CHAR(4) NOT NULL,
PRICE NUMERIC(7,2),（ 定点数 ）
QTY SMALLINT,（ 短整数 ）
PRIMARY KEY (SNO,PNO,JNO),（ 主键 ）
FOREIGN KEY (SNO) REFERENCES S(SNO),（ 外键 ）
FOREIGN KEY (PNO) REFERENCES P(PNO),
FOREIGN KEY (JNO) REFERENCES J(JNO),
CHECK(QTY BETWEEN 0 AND 10000)); （ 检查子句 ）
定义中说明了五个属性定义了三个外键外键 SNO和基本表 S
中 SNO属性相对应外键 PNO和基本表 P
中 PNO属性相对应外键 JNO和基本表 J
中 JNO属性相对应定义中还使用了一个检查子句 CHECK
指出供应数量 QTY在 0-10000之间
（ 2） 基本表结构的修改
基本表建立后,可根据需要增加或删除属性 。
① 增加新的属性句法：
ALTER TABLE 基本表名 ADD 新属性名 新属性类型
例 5.2 在基本表 S中增加一个电话号码 (TELE)属性,
可用下列语句：
ALTER TABLE S ADD TELE CHAR(12);
注意,新增加的属性不能定义为? NOT NULL”。
基本表在增加一个属性后,原有元组在新增加的属性列上的值都被定义为空值 (NULL)。
结构化查询语言
5,2,2 SQL数据定义 DLL
② 删除原有的属性句法,ALTER TABLE 基本表名 DROP 属性名
[CASCADE|RESTRICT]
CASCADE方式表示：在基本表中删除某属性时,所有引用到该属性的视图和约束也要一起自动地被删除 。
RESTRICT方式表示,在没有视图或约束引用该属性时,
才能在基本表中删除该属性,否则拒绝删除 。
例 5.3 在基本表 S中删除状态 (STATUS)属性,并且将引用该属性的所有视图和约束也一起删除,可用下列语句：
ALTER TABLE S DROP STATUS CASCADE;
结构化查询语言
5,2,2 SQL数据定义 DLL
（ 3） 基本表的撤消
DROP语句的句法如下：
DROP TABLE 基本表名 （ CASCADE|RESTRICT）
撤消基本表,其所有数据均不存在 。
CASCADE和 RESTRICT的语义同前面句法中的语义一样 。
例 5.4 需要撤消基本表 S,但只有在没有视图或约束引用基本表 S中的列时才能撤消,否则拒绝撤消 。
可用下列语句实现：
DROP TABLE S RESTRICT;
结构化查询语言
5,2,2 SQL数据定义 DLL
4,视图的创建和撤消
在 SQL中,外模式级数据结构的基本单位是视图
(VIEW).
视图是从若干基本表和 (或 )其他视图构造出来的表 。 这种构造方式采用 SELECT语句实现 。
在创建一个视图时,系统把视图的定义存放在数据字典中,而并不存储视图对应的数据,在用户使用视图时才去找对应的数据 。 因此,视图被称为? 虚表? 。
结构化查询语言
5,2,2 SQL数据定义 DLL
5,2,2 SQL数据定义 DLL
（ 1） 视图的创建
句法：
CREATE VIEW 视图名 (列名表 ) AS SELECT 查询语句
例 5.5 对工程项目零件供应数据库中基本表 S,P、
J,SPJ,用户经常要用到有关项目使用零件情况的信息为：工程号 (JNO),工程项目名称 (JNAME),供应商号 (SNO),供应商名 (SNAME),零件号 (PNO)、
零件名 (PNAME),供应数量 (QTY)等列的数据，那么可用下列语句建立视图：
CREATE VIEW JSP_NAME (JNO,JNAME,SNO,SNAME,
PNO,PNAME,QTY)
AS SELECT (J.JNO,JNAME,S.SNO,SNAME,
P.PNO,PNAME,SPJ.QTY)
FROM S,P,J,SPJ
WHERE S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO AND
J.JNO=SPJ.JNO;
当视图中列名顺序与 SELECT子句中的列名顺序一致,
视图名 JSP_NAME后的列名可省 。
结构化查询语言
5,2,2 SQL数据定义 DLL
（ 2） 视图的撤消
句法,DROP VIEW 视图名
例 5.6 撤消 JSP_NAME视图,可用下列语句实现：
DROP VIEW JSP_NAME;
结构化查询语言
5,2,2 SQL数据定义 DLL
5,索引的创建和撤消
（ 1） 索引的创建
句法,CREATE [UNIQUE] INDEX 索引名 ON 基本表名 (<列名 > [<次序 >] [,<列名 > [<次序 >]]…)
基本表名指定要建索引的基本表的名字。
索引可以建在该表的一列或多列上，各列名之间用逗号分隔。
每个 <列名 >后面还可以用 <次序 >指定索引值的排列次序，包括 ASC(升序 )和 DESC(降序 )两种，默认值为
ASC。
UNIQUE表示此索引的每一个索引值只对应惟一的数据记录。
结构化查询语言
5,2,2 SQL数据定义 DLL
例 5.7 如果工程项目表已经创建,
CREATE TABLE J (JNO CHAR(4) NOT NULL,
JNAME CHAR(20),
CITY CHAR(20),
BALANCE NUMERIC(7,2),
PRIMARY KEY(JNO));
对基本表 J建立一个索引：
CREATE INDEX JNO_INDEX ON J(JNO);
此语句表示对基本表 J的列 JNO建立索引,索引键的名为 JNO_INDEX。
结构化查询语言
5,2,2 SQL数据定义 DLL
如果要求列 JNO的值在索引表中不重复,那么在
INDEX前加上保留字 UNIQUE：
CREATE UNIQUE INDEX JNO_INDEX ON J(JNO)；
SQL中的索引是非显式索引,在索引创建以后,用户在索引撤消前不会再用到该索引键的名,但是索引在用户查询时会自动起作用 。
当一个主键由几个属性构成时,一个索引键需要有多个列 。
当一个索引键有多个列时,列排序时可以升序,
也可以降序,升序排列用 ASC表示,降序排列用
DESC表示,默认时表示升序排列 。
结构化查询语言
5,2,2 SQL数据定义 DLL
例如,对基本表 SPJ中的 (SNO,PNO,JNO)建立索引 。
CREATE TABLE SPJ (SNO CHAR(4) NOT NULL,
PNO CHAR(4) NOT NULL,
JNO CHAR(4) NOT NULL,
PRICE NUMERIC(7,2),
QTY SMALLINT。
在表 SPJ中主键为 SNO,PNO,JNO。 建立索引如下：
CREATE UNIQUE INDEX SPJ_INDEX ON SPJ(SNO ASC,PNO
ASC,JNO DESC);
结构化查询语言
5,2,2 SQL数据定义 DLL
（ 2） 索引的撤消句法,DROP INDEX <索引名 >
例 5.8 撤消索引 JNO_INDEX和 SPJ_INDEX,用如下语句：
DROP INDEX JNO_INDEX,SPJ_INDEX;
结构化查询语言
5,2,2 SQL数据定义 DLL
数据操纵分成数据查询和数据更新两类。
1,SQL的数据查询
SQL中最经常使用的是从数据库中获取数据 。 从数据库中获取数据称为查询数据库,查询数据库通过使用 SELECT语句 完成 。
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
5,2,3 SQL的数据操纵（ DML）
（ 1） SELECT语句格式
语句包含 6部分,其语法形式为：
SELECT 字段表 FROM 表名 WHERE 查询条件
GROUP BY 分组字段 HAVING分组条件 ORDER BY
字段 [ASC|DESC]
其中：
字段表部分包含了查询结果要显示的 字段清单,字段之间用逗号分开 。
要选择表中所有字段,可用星号 代替 。
如果所选定的字段要更名,可在该字段后用 AS[新名 ]实现 。
FROM 表名,用于指定一个或多个表 。 如果所选的字段来自不同的表,则字段名前应加表名前缀 。
WHERE 查询条件,用于限制记录的选择。构造查询条件可使用大多数的 Visual Basic内部函数和运算符，以及 SQL特有的运算符构成表达式。
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
5,2,3 SQL的数据操纵（ DML）
GROUP BY分组字段和 HAVING分组条件子句用于 分组和分组条件设定 。 能把指定字段列表中有相同值的记录合并成一条记录 。
ORDER BY字段,决定了查找出来的记录的排列顺序 。
在 ORDER BY字段子句中,可以指定一个或多个字段作为排序键,ASC选项代表升序,DESC代表降序 。
可用 WHERE查询条件子句来排除不想分组的行,将记录分组后,也可用 HAVING分组条件子句来筛选它们 。
HAVING子句与 WHERE子句类似 。
当 GROUP BY完成记录分组后,HAVING就筛选出由
GROUP BY子句分组的,且满足 HAVING子句条件的所有记录 。
5,2,3 SQL的数据操纵（ DML）
在 SELECT语句中,SELECT和 FROM子句是必须的 。
在 SELECT子句内可使用合计函数对记录进行操作,
它返回一组记录的单 —值 。
例如,COUNT（ *） 用于计算元组的个数
COUNT（ 列名 ） 用于对一列中的值计算个数
SUM（ 列名 ） 用于求某一列值的总和 （ 此列必须是数值型 ）
AVG函数用于返回记录集的特定字段中所有值的平均数 。
（ 2） 单表查询
SQL语句的所有查询都是利用 SELECT语句完成的 。
下面通过例子说明它的使用方法 。
例 5.9 假设项目零件供应数据库中有四个基本表
(关系 )参见下图：
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
供应商关系,S(SNO,SNAME,STATUS,ADDR)
零件关系,P(PNO,PNAME,COLOR,WEIGHT)
工程项目关系,J(JNO,JNAME,CITY,BALANCE)
供应情况关系,SPJ(SNO,PNO,JNO,PRICE,QTY)
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
表 5.10 供应商关系,S
SNO SNAME STATUS ADDR
S1 原料公司 10 西安友谊路 23号
S2 红星钢管厂 30 上海浦东 100号
S3 零件制造公司 20 西安东郊 55号
S4 配件公司 50 江西胜利路 58号
S5 原料厂 40 北京三环路 89号
S6 东方配件厂 60 天津叶西路 100号结构化查询语言
5,2,3 SQL的数据操纵（ DML）
表 5.11 零件关系 P：
PNO PNAME COLOR WEIGHT
P1 钢筋 黑 25
P2 钢管 白 26
P3 螺母 红 11
P4 螺丝 黄 12
P5 齿轮 红 18
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
表 5.12 工程项目关系,J
JNO JNAME CITY BALANCE
J1 东方明珠 上海 0.00
J2 炼油厂 长春 -11.20
J3 地铁三号 北京 678.00
J4 明珠线 上海 345.00
J5 炼钢工地 天津 123.00
J6 南浦大桥 上海 234.50
J7 红星水泥厂 江西 345.60
表 5.13 供应情况关系,SPJ
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P1 J4 22.60 60
S1 P3 J1 22.80 100
S1 P3 J4 22.80 60
S3 P3 J5 22.80 100
S3 P4 J1 11.90 30
S3 P4 J4 11.90 60
S4 P2 J4 33.80 60
S5 P5 J1 15.60 20
S5 P5 J4 15.60 60
S6 P3 J1 22.80 20
S1 P3 J6 22.80 6
S3 P4 J6 11.90 6
S4 P2 J6 33.80 8
S5 P5 J6 15.60 8
试用 SQL语句表达下列查询语句 。
① 检索供应工程 J1零件的供应商编号 SNO。
SELECT DISTINCT SNO FROM SPJ WHERE JNO='J1';
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
DISTINCT表示在结果中去掉重复的供应商编号 SNO。供应情况关系 SPJ JNO='J1'
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P3 J1 22.80 100
S3 P4 J1 11.90 30
S5 P5 J1 15.60 20
S6 P3 J1 22.80 20
JNO='J1'
SNO
S1
S1
S3
S5
S6
JNO='J1'
SNO
S1
S3
S5
S6
DISTINCT SNO
表 5.13 供应情况关系,SPJ
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P1 J4 22.60 60
S1 P3 J1 22.80 100
S1 P3 J4 22.80 60
S3 P3 J5 22.80 100
S3 P4 J1 11.90 30
S3 P4 J4 11.90 60
S4 P2 J4 33.80 60
S5 P5 J1 15.60 20
S5 P5 J4 15.60 60
S6 P3 J1 22.80 20
S1 P3 J6 22.80 6
S3 P4 J6 11.90 6
S4 P2 J6 33.80 8
S5 P5 J6 15.60 8
② 检索供应工程 J1零件 P1的供应商编号 SNO。
SELECT SNO FROM SPJ WHERE JNO='J1' AND PNO='P1'
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
表 5.13 供应情况关系,SPJ
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P1 J4 22.60 60
S1 P3 J1 22.80 100
S1 P3 J4 22.80 60
S3 P3 J5 22.80 100
S3 P4 J1 11.90 30
S3 P4 J4 11.90 60
S4 P2 J4 33.80 60
S5 P5 J1 15.60 20
S5 P5 J4 15.60 60
S6 P3 J1 22.80 20
S1 P3 J6 22.80 6
S3 P4 J6 11.90 6
S4 P2 J6 33.80 8
S5 P5 J6 15.60 8
关系 SPJ JNO='J1'
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P3 J1 22.80 100
S3 P4 J1 11.90 30
S5 P5 J1 15.60 20
S6 P3 J1 22.80 20
供应情况关系 SPJ
JNO='J1'AND PNO='P1';
SNO
S1
供应情况关系 SPJ
JNO='J1'AND PNO='P1';
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
③ 查询全体工程项目的详细信息
SELECT * FROM J
如果要查询 FROM子句后面指定的基本表的全体属性时，可以用? *? 表示。所以上面的语句等价于：
SELECT JNO,JNAME,CITY,BALANCE FROM J;
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
表 5.12 工程项目关系,J
JNO JNAME CITY BALANCE
J1 东方明珠 上海 0.00
J2 炼油厂 长春 -11.20
J3 地铁三号 北京 678.00
J4 明珠线 上海 345.00
J5 炼钢工地 天津 123.00
J6 南浦大桥 上海 234.50
J7 红星水泥厂 江西 345.60
④ 查询没有正余额的工程编号,名称及城市,结果按工程编号升序排列 。
SELECT JNO,JNAME,CITY FROM J WHERE BALANCE IS
NULL OR BALANCE<=0 ORDER BY JNO；
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
表 5.12 工程项目关系,J
JNO JNAME CITY BALANCE
J1 东方明珠 上海 0.00
J2 炼油厂 长春 -11.20
J3 地铁三号 北京 678.00
J4 明珠线 上海 345.00
J5 炼钢工地 天津 123.00
J6 南浦大桥 上海 234.50
J7 红星水泥厂 江西 345.60
J1 东方明珠 上海 0.00
J2 炼油厂 长春 -11.20
谓词? IS NULL”,当 BALANCE值为 空时,
BALANCE IS NULL的值为真 (TRUE),否则为假
(FALSE)。
与? IS NULL”相对的谓词是? IS NOT NULL”，
当 BALANCE值为非空值时,BALANCE IS NOT
NULL的值为真 (TRUE),否则为假 (FALSE)。
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
⑤ 求使用零件数量在 100与 1000之间的工程项目编号,零件号和数量 。
SELECT JNO,PNO,QTY FROM SPJ WHERE QTY
BETWEEN 100 AND 1000；
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
表 5.13 供应情况关系,SPJ
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P1 J4 22.60 60
S1 P3 J1 22.80 100
S1 P3 J4 22.80 60
S3 P3 J5 22.80 100
S3 P4 J1 11.90 30
S3 P4 J4 11.90 60
S4 P2 J4 33.80 60
S5 P5 J1 15.60 20
S5 P5 J4 15.60 60
S6 P3 J1 22.80 20
S1 P3 J6 22.80 6
S3 P4 J6 11.90 6
S4 P2 J6 33.80 8
S5 P5 J6 15.60 8
WHERE QTY BETWEEN 100 AND 1000
⑥ 查询上海的供应商名称,假设供应商关系的 ADDR列的值都以城市名开头 。
SELECT SNAME FROM S WHERE ADDR LIKE '上海 %'
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
表 5.10 供应商关系,S
SNO SNAME STATUS ADDR
S1 原料公司 10 西安友谊路 23号
S2 红星钢管厂 30 上海 浦东 100号
S3 零件制造公司 20 西安东郊 55号
S4 配件公司 50 江西胜利路 58号
S5 原料厂 40 北京三环路 89号
S6 东方配件厂 60 天津叶西路 100号
字符串匹配操作符 LIKE的一般形式是：
列名 LIKE 字符串常数
列名的类型必须是字符串或可变字符串 。 在字符串常数中字符的含义如下：
％ (百分号 )：表示可以与在这个位臵的任意长度
(可以为零 )的字符串匹配 。 例如,上海明珠,上海滩 。
SELECT SNAME FROM S WHERE ADDR LIKE '上海 %'
_(下划线 )：表示可以与这个位臵的任意单个字符匹配 。 例如,北京市,北海市
SELECT SNAME FROM S WHERE ADDR LIKE ‘北 _市结构化查询语言
5,2,3 SQL的数据操纵（ DML）
新加举例：
学生表 （ student）
Id_card sname sage ssex School_number
11010519740506001 刘志刚 28 男 A_15
11010719770304002 蒋辉 25 女 A_01
11013019781008004 许静 24 女 B_19
12109619810706001 王军 21 男 C_82
13070519750215002 程红 27 女 B-57
32605619800318004 王言 22 女 A_01
40507819801124003 李执 22 男 B_19
贷款单表 (LOAN)
Loan_number amount
L_04 15000
L_11 20000
L_16 35000
L_25 10000
L_28 15000
L_30 10000
L_33 15000
学生贷款表 （ borrower）
Id_card Loan_number
11010519740506001 L_33
11010719770304002 L_16
11013019781008004 L-28
13070519750215002 L_25
13070519750215002 L_30
40507819801124003 L_11
例 1,例 1.1查询全体学生的详细信息 。
Select * from student;
例 1 例 1.2查询所属学校代号是 B_19的学生的姓名和年龄。
Select sname,sage from student where sschool_number=’
B_19’ ;
例 1,例 1.3查询所有贷款的学生的身份证号。
Select distinct id_card from borrower;
例 1,4查询所属学校代号是 B_57的学生中年龄大于 24的学生的姓名、年龄和性别。
Select sname,sage,ssex from student where sschool_number=’
B_57’ and sage>24;
例 1,5查询所属学校代号是 B_19的学生的姓名、年龄和性别，
并按年龄降序排序。
Select sname,sage,ssex from student where sschool_number=’
B_19’ order by sage desc;
例 1,6查询贷款金额为 15000的学生的贷款单号。
Select loan_number from loan where amount=15000;
例 1,7查询贷款金额在 15000至 20000之间的贷款单号，
并按贷款金额升序排序。
Select loan_number,amount from loan where amount
between 15000 and 20000 order by amount;
例 1,8查询所属学校代号是 B_57,A_01,C_82的学生的身份证号、姓名和所属学校代号，并按学校代号升序排序。
Select id_card,sname,sschool_number
from student where sschool_number in
(‘ B_57’,’ A_01’,’ C_82’ ) order by sschool_number;
in 等价于多个 or 。 如果查询所属学校代号 不是 B_57,A_01、
C_82的学生的身份证号、姓名和所属学校代号，并按学校代号升序排序。
Select id_card,sname,sschool_number
from student where sschool_number not in
(‘ B_57’,’ A_01’,’ C_82’ ) order by sschool_number;
例 1,例 1.9 查询身份证号以,110” 开始的学生的所有信息。
Select * from student where id_card like ‘ 110*’ ;
例 1,例 1.10 查询最高和最低的贷款金额。
Select min(amount) as amountofmin,max(amount) as
amountofmax from loan;
（ 3） 多表查询
实现来自多个关系的查询时,如果要引用不同关系中的同名属性,则在属性名前加关系名,即用? 关系名,属性名? 的形式表示,以便区分 。
在多个关系上的查询可以用联接查询表示也可以用嵌套查询来表示 。
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
合计函数
SQL 提供了下列合计函数（表 5.14）
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
表 5.14 合计函数合计函数 描 述
COUNT（ *） 计算元组的个数
COUNT（ 列名 ） 对一列中的值计算个数
SUM（ 列名 ） 求某一列值的总和 （ 此列必须是数值型 ）
AVG（ 列名 ） 求某一列值的平均值 （ 此列必须是数值型 ）
MAX（ 列名 ） 求某一列值的最大值
MIN（ 列名 ） 求某一列值的最小值例 5.10 试用 SQL语句表达下列每个查询语句
① 求使用了 P3零件的工程项目名称
SELECT DISTINCT JNAME
FROM J,SPJ
WHERE SPJ.JNO=J.JNO AND PNO='P3';
这个 SELECT语句执行时,要对关系 SPJ和 J做连接操作 。 执行连接操作的表示方法是 FROM子句后面写上执行 连接操作的表名 SPJ和 J,再在 WHERE子句中写上 连接的条件 SPJ,JNO=J.JNO。
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
表 5.13 供应情况关系,SPJ
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P1 J4 22.60 60
S1 P3 J1 22.80 100
S1 P3 J4 22.80 60
S3 P3 J5 22.80 100
S3 P4 J1 11.90 30
S3 P4 J4 11.90 60
S4 P2 J4 33.80 60
S5 P5 J1 15.60 20
S5 P5 J4 15.60 60
S6 P3 J1 22.80 20
S1 P3 J6 22.80 6
S3 P4 J6 11.90 6
S4 P2 J6 33.80 8
S5 P5 J6 15.60 8
PNO=‘P3’;
J1
J4
J5
J6
当 PNO=‘P3’时,找到 J1,J4,J5和 J6,根据关系 J,可得
DISTINCT JNAME,
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
表 5.12 工程项目关系,J
JNO JNAME CITY BALANCE
J1 东方明珠 上海 0.00
J2 炼油厂 长春 -11.20
J3 地铁三号 北京 678.00
J4 明珠线 上海 345.00
J5 炼钢工地 天津 123.00
J6 南浦大桥 上海 234.50
J7 红星水泥厂 江西 345.60
东方明珠明珠线炼钢工地南浦大桥
② 求供应工程 J1零件为红色的供应商号 SNO
SELECT DISTINCT SNO FROM SPJ,P WHERE
SPJ.PNO=P.PNO AND JNO='J1' AND COLOR='红 ';
由 COLOR=‘红 ’ 可找到 PNO为 P3,P5
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
表 5.11 零件关系 P：
PNO PNAME COLOR WEIGHT
P1 钢筋 黑 25
P2 钢管 白 26
P3 螺母 红 11
P4 螺丝 黄 12
P5 齿轮 红 18
根据 COLOR=‘红 ’ ;可找到 P3,P5。 根据 JNO=‘J1’
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P1 J4 22.60 60
S1 P3 J1 22.80 100
S1 P3 J4 22.80 60
S3 P3 J5 22.80 100
S3 P4 J1 11.90 30
S3 P4 J4 11.90 60
S4 P2 J4 33.80 60
S5 P5 J1 15.60 20
S5 P5 J4 15.60 60
S6 P3 J1 22.80 20
S1 P3 J6 22.80 6
S3 P4 J6 11.90 6
S4 P2 J6 33.80 8
S5 P5 J6 15.60 8
SNO PNO JNO
S1 P3 J1
S5 P5 J1
S6 P3 J1
SNO
S1
S5
S6
5,2,3 SQL的数据操纵（ DML）
③ 求至少使用了零件编号为 P3和 P5的工程编号 JNO。
SELECT DISTINCT X.JNO
FROM SPJ AS X,SPJ AS Y
WHERE X.JNO=Y.JNO AND X.PNO='P3' AND
Y.PNO='P5'
同一个关系 SPJ在一层中出现两次,为区别,引入两个元组变量 (即别名 )X和 Y。 在语句中应用元组变量对列名进行限定 。 保留字 AS 在语句中可省略 。
表 5.13 供应情况关系,SPJ
SNO PNO JNO PRICE QTY
S1 P1 1 22.60 80
S1 P1 J4 22.60 60
S1 P3 J1 22.80 100
S1 P3 J4 22.80 60
S3 P3 J5 22.80 100
S3 P4 J1 11.90 30
S3 P4 J4 11.90 60
S4 P2 J4 33.80 60
S5 P5 J1 15.60 20
S5 P5 J4 15.60 60
S6 P3 J1 22.80 20
S1 P3 J6 22.80 6
S3 P4 J6 11.90 6
S4 P2 J6 33.80 8
S5 P5 J6 15.60 8
5,2,3 SQL的数据操纵（ DML）
例 5.11对关系 J,P,SPJ进行查询 。
① 求供应 P3零件的供应商个数 。
分析,这个查询结果只有一行和一列,就是供应? P3”
零件的供应商个数 。
SELECT COUNT(DISTINCT SNO) AS COUNT_P3
FROM SPJ
WHERE PNO='P3';
或者
SELECT COUNT(SNO) AS COUNT_P3
FROM (SELECT DISTINCT SNO FROM SPJ
WHERE PNO='P3')
谓词 DISTINCT用在列名前表示消除该列中重复的值
COUNT_P3为输出的列名
5,2,3 SQL的数据操纵（ DML）
SELECT COUNT(DISTINCT SNO) AS COUNT_P3 FROM
SPJ WHERE PNO='P3';
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P1 J4 22.60 60
S1 P3 J1 22.80 100
S1 P3 J4 22.80 60
S3 P3 J5 22.80 100
S3 P4 J1 11.90 30
S3 P4 J4 11.90 60
S4 P2 J4 33.80 60
S5 P5 J1 15.60 20
S5 P5 J4 15.60 60
S6 P3 J1 22.80 20
S1 P3 J6 22.80 6
S3 P4 J6 11.90 6
S4 P2 J6 33.80 8
S5 P5 J6 15.60 8
SNO PNO
S1 P3
S1 P3
S3 P3
S6 P3
S1 P3
SNO PNO
S1 P3
S3 P3
S6 P3
COUNT_P3
3
5,2,3 SQL的数据操纵（ DML）
② 求项目余额的最大值,最小值,总值和平均值,输出的列名 分别为,MAX_NUMBER,MIN_NUMBER,SUM_NUMBER,AVG_NUMBER。
SELECT MAX(BALANCE) AS MAX_NUMBER,
MIN(BLANCE) AS MIN_NUMBER，
SUM(BALANCE) AS SUM_NUMBER,
AVG(BALANCE) AS AVG_NUMBER FROM J;
工程项目关系,J
JNO JNAME CITY BALANCE
J1 东方明珠 上海 0.00
J2 炼油厂 长春 -11.20
J3 地铁三号 北京 678.00
J4 明珠线 上海 345.00
J5 炼钢工地 天津 123.00
J6 南浦大桥 上海 234.50
J7 红星水泥厂 江西 345.60
MAX_NUMBER MIN_NUMBER SUM_NUMBER AVG_NUMBER
678.00 -11.20 1317.8 244.8
5,2,3 SQL的数据操纵（ DML）
（ 5） 数据分组
SQL语言提供了 GROUP BY 子句和 HAVING子句,将查询结果进行分组,然后再对每个分组进行统计,实现分组统计 。
例 5.12对关系 J,P,SPJ进行查询 。
① 统计每个供应商供应不同零件的种数和供应总数量 。
SELECT SNO,
COUNT(DISTINCT PNO) AS COUNT_QTY,
SUM(QTY) AS SUM_ QTY
FROM SPJ GROUP BY SNO
SELECT SNO,COUNT(DISTINCT PNO) AS COUNT_QTY,
SUM(QTY) AS SUM_ QTY FROM SPJ GROUP BY SNO
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P1 J4 22.60 60
S1 P3 J1 22.80 100
S1 P3 J4 22.80 60
S1 P3 J6 22.80 6
SNO PNO JNO PRICE QTY
S3 P3 J5 22.80 100
S3 P4 J1 11.90 30
S3 P4 J4 11.90 60
S3 P4 J6 11.90 6
SNO PNO JNO PRICE QTY
S4 P2 J4 33.80 60
S4 P2 J6 33.80 8
SNO PNO JNO PRICE QTY
S5 P5 J1 15.60 20
S5 P5 J4 15.60 60
S5 P5 J6 15.60 8
SNO PNO JNO PRICE QTY
S6 P3 J1 22.80 20
SNO COUNT_QTY SUM_ QTY
S1 2 306
S3 2 196
S4 1 68
S5 1 88
S6 1 20
5,2,3 SQL的数据操纵（ DML）
② 统计上海地区的每个项目使用零件的种数 (超过 3
种 )和零件总数量 。 要求查询结果按零件的种数升序排列,种数相同时按总数量降序排列 。
SELECT SPJ.JNO,
COUNT(DISTINCT SPJ.PNO) AS COUNT_PNO,
SUM(QTY) AS SUM_QTY
FROM J,SPJ
WHERE J.CITY=’上海 ’ AND J.JNO=SPJ.JNO
GROUP BY SPJ.JNO
HAVING COUNT(DISTINCT PNO)>3
ORDER BY 2,3 DESC;
SELECT SPJ.JNO,COUNT(DISTINCT SPJ.PNO) AS COUNT_PNO,
SUM(QTY) AS SUM_QTY FROM J,SPJ
WHERE J.CITY=’上海 ’ AND J.JNO=SPJ.JNO
GROUP BY SPJ.JNO
HAVING COUNT(DISTINCT PNO)>3
ORDER BY 2,3 DESC;
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P1 J4 22.60 60
S1 P3 J1 22.80 100
S1 P3 J4 22.80 60
S1 P3 J6 22.80 6
S3 P3 J5 22.80 100
S3 P4 J1 11.90 30
S3 P4 J4 11.90 60
S3 P4 J6 11.90 6
S4 P2 J4 33.80 60
S4 P2 J6 33.80 8
S5 P5 J1 15.60 20
S5 P5 J4 15.60 60
S5 P5 J6 15.60 8
S6 P3 J1 22.80 20
工程项目关系,J
JNO JNAME CITY BALANCE
J1 东方明珠 上海 0.00
J2 炼油厂 长春 -11.20
J3 地铁三号 北京 678.00
J4 明珠线 上海 345.00
J5 炼钢工地 天津 123.00
J6 南浦大桥 上海 234.50
J7 红星水泥厂 江西 345.60
根据 WHERE子句的条件,对关系 J和 SPJ执行连接操作找出满足条件为? 上海? 的工程的元组
GROUP BY SPJ.JNO
HAVING COUNT(DISTINCT PNO)>3
ORDER BY 2,3 DESC;
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P1 J4 22.60 60
S1 P3 J1 22.80 100
S1 P3 J4 22.80 60
S1 P3 J6 22.80 6
S3 P4 J1 11.90 30
S3 P4 J4 11.90 60
S3 P4 J6 11.90 6
S4 P2 J4 33.80 60
S4 P2 J6 33.80 8
S5 P5 J1 15.60 20
S5 P5 J4 15.60 60
S5 P5 J6 15.60 8
S6 P3 J1 22.80 20
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P3 J1 22.80 100
S3 P4 J1 11.90 30
S5 P5 J1 15.60 20
S6 P3 J1 22.80 20
SNO PNO JNO PRICE QTY
S1 P1 J4 22.60 60
S1 P3 J4 22.80 60
S3 P4 J4 11.90 60
S4 P2 J4 33.80 60
S5 P5 J4 15.60 60
SNO PNO JNO PRICE QTY
S1 P3 J6 22.80 6
S3 P4 J6 11.90 6
S4 P2 J6 33.80 8
S5 P5 J6 15.60 8
按工程号 JNO的值对上海的工程进行分组,将 JNO列的值相同的元组分为一组
SNO PNO JNO PRICE QTY
S1 P1 J4 22.60 60
S1 P3 J4 22.80 60
S3 P4 J4 11.90 60
S4 P2 J4 33.80 60
S5 P5 J4 15.60 60
GROUP BY SPJ.JNO HAVING COUNT(DISTINCT PNO)>3 ORDER
BY 2,3 DESC;
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
JNO COUNT_PNO SUM_ QTY
J1 4 306
J4 5 68
J6 4 20
对每一个分组进行合计操作；并按 HAVING子句的条件对产生的元组进行选择,消除只使用三种以下零件的元组
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P3 J1 22.80 100
S3 P4 J1 11.90 30
S5 P5 J1 15.60 20
S6 P3 J1 22.80 20
SNO PNO JNO PRICE QTY
S1 P3 J6 22.80 6
S3 P4 J6 11.90 6
S4 P2 J6 33.80 8
S5 P5 J6 15.60 8
对结果进行排序
5,2,3 SQL的数据操纵（ DML）
SELECT 字段表 FROM 表名 WHERE 查询条件 GROUP
BY 分组字段 HAVING分组条件 ORDER BY字段
[ASC|DESC]
学号 =SN； 学生姓名 =SNAME； 课程号 =CN;课程名
=CNAME; 成绩 =SG; 系 =SD； 年龄 =SA
学生选课 =CS(CN,SN);
学生课程成绩 =SC(SN,CN,SG)
学生情况表 =S(SN,SNAME,SD,SA)
课程表 =C(CN,CNAME)
5,2,3 SQL的数据操纵（ DML）
SELECT SN
FROM S
WHERE SD='D2'
学生情况表 S
SN SNAME SD SA
S1 王洪 D1 20
S2 李景 D1 19
S3 章林 D2 19
S4 武斌 D2 20
S5 程信 D3 20
例 1:求 2系学生的学号。
学生情况表 =S(SN,SNAME,SD,SA)
S3
S4
5,2,3 SQL的数据操纵（ DML）
例 2:求选修了课程的学生学号 。
学生课程成绩 =SC(SN,CN,SG)
SELECT DISTINCT SN
FROM SC
这里 WHERE不写,表示条件为空 。 学生课程成绩 SC
SN CN SG
S1 C1 98
S2 C2 85
S3 C5 76
S1 C3 78
S2 C1 89
S4 C5 95
SELECT后面的
DISTINCT
表示要去掉重复的 SN
S4
S3
S2
S1
例 3:求选修 C1的学生学号和得分,
并按分数降序排列,
学生课程成绩 =SC(SN,CN,SG)
SELECT SN,SG
FROM SC
WHERE CN='C1'
ORDER BY SG DESC
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
这里 ORDER BY表示结果要排序,BY后面指出排序字段,DESC表示降序。 89S2
98S1
SGSN
学生课程成绩 SC
SN CN SG
S1 C1 98
S2 C2 85
S3 C5 76
S1 C3 78
S2 C1 89
S4 C5 95
例 4:找学号为 S1,S3,S4的学生姓名,所属系名学生情况表 =S(SN,SNAME,SD,SA)
SELECT SNAME,SD
FROM S
WHERE SN = 'S1' or
SN = 'S3' or
SN ='S4'
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
学生情况表 S
SN SNAME SD SA
S1 王洪 D1 20
S2 李景 D1 19
S3 章林 D2 19
S4 武斌 D2 20
S5 程信 D3 20
例 5:求有人选修的课程号,课程名字 。
课程表 =C(CN,CNAME)
学生课程成绩 =SC(SN,CN,SG)
SELECT DISTINCT C.CN,CNAME
FROM C,SC
WHERE SC.CN = C.CN
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
学生成绩 SC
SN CN SG
S1 C1 98
S2 C2 85
S3 C5 76
S1 C1 78
S2 C1 89
S4 C5 95
课程表 C
CN CNAME
C1 高数
C2 物理
C3 英语
C4 电工
C5 网络C2C1
CN
有人选修
C5
高数网络物理
CNAME
C1
C2
C5
C1
C1
C5
5,2,3 SQL的数据操纵（ DML）
例 6,求进修了课程 C1的学生人数 。
学生课程成绩 =SC(SN,CN,SG)
SELECT COUNT(SN)
FROM SC
WHERE CN='C1'
学生课程成绩 SC
SN CN SG
S1 C1 98
S2 C2 85
S3 C5 76
S1 C3 78
S2 C1 89
S4 C5 95COUNT（ 列名 ）对一列中的值计算个数结构化查询语言
5,2,3 SQL的数据操纵（ DML）
例 7:求 5系学生的平均年龄 。
学生情况表 =S(SN,SNAME,SD,SA)
SELECT AVG(SA)
FROM S
WHERE SD='D5' 学生情况表 SSN SNAME SD SA
S1 王洪 D1 20
S2 李景 D1 19
S3 章林 D2 19
S4 武斌 D2 20
S5 程信 D3 20
AVG（ 列名） 求某一列值的平均值（此列必须是数值型）
结构化查询语言例 8:找出选修课程超过 2门的学生学号学生课程成绩 =SC(SN,CN,SG)
SELECT SN
FROM SC
GROUP BY SN
HAVING COUNT(*) >2
5,2,3 SQL的数据操纵（ DML）
学生课程成绩 SC
SN CN SG
S1 C1 98
S2 C2 85
S3 C5 76
S1 C3 78
S2 C1 89
S4 C5 95
S1 C2 88
S2 C4 78
COUNT（ *） 计算元组的个数结构化查询语言学生课程成绩 SC1
SN CN SG
S1 C1 98
S1 C3 78
S1 C2 88
5,2,3 SQL的数据操纵（ DML）
S1
S2
COUNT(*) >2
GROUP BY SN
结构化查询语言学生课程成绩 SC2
SN CN SG
S3 C5 76
学生课程成绩 SC2
SN CN SG
S2 C2 85
S2 C1 89
S2 C4 78
COUNT(*) >2
例 9:找出选修课程名字等于
高数,的学号和学生名字学生情况表
=S(SN,SNAME,SD,SA)
课程表 =C(CN,CNAME)
学 生 课 程 成 绩 =SC(SN,CN,
SG)
SELECT S.SN,SNAME
FROM S,C,SC
WHERE SC.SN=S.SN
AND SC.CN=C.CN
AND CNAME='高数 '
学生情况表 S
SN SNAME SD SA
S1 王洪 D1 20
S2 李景 D1 19
S3 章林 D2 19
S4 武斌 D2 20
S5 程信 D3 20
学生成绩 SC
SN CN SG
S1 C1 98
S2 C3 85
S3 C5 76
S1 C3 78
S2 C1 89
S4 C5 95
S
N
SNAME
S
1
王洪
S
2
李景课程表 C
CN CNAME
C1 高数
C2 物理
C3 英语
C4 电工
C5 网络结构化查询语言
5,2,3 SQL的数据操纵（ DML）
新加举例：
学生表（ student）
Id_card sname sage ssex School_number
11010519740506001 刘志刚 28 男 A_15
11010719770304002 蒋辉 25 女 A_01
11013019781008004 许静 24 女 B_19
12109619810706001 王军 21 男 C_82
13070519750215002 程红 27 女 B-57
32605619800318004 王言 22 女 A_01
40507819801124003 李执 22 男 B_19
贷款单表 (LOAN)
Loan_number amount
L_04 15000
L_11 20000
L_16 35000
L_25 10000
L_28 15000
L_30 10000
L_33 15000
学生贷款表 （ borrower）
Id_card Loan_number
11010519740506001 L_33
11010719770304002 L_16
11013019781008004 L-28
13070519750215002 L_25
13070519750215002 L_30
40507819801124003 L_11
例 2.1 查询已参加贷款的学生的全部信息和其贷款单号 。
Select student.*,borrower.loan_number
from student,borrower
where student.id_card,borrower.id_card;
可写为：
Select s.*,b.loan_number
from student as s,borrower as b
where s.id_card,b.id_card;
例 1,例 2.2 查询贷款号为 L_33的学生信息 。
Select s.* from student as s,borrower as b
where s.id_card,b.id_card and b.loan_number=’ L_33’ ;
例 2,3查询贷款金额为 15000元的学生信息。
Select s.*,l.loan_number
from student as s,borrower as b,loan as l
where s.id_card,b.id_card and b.loan_number=l.loan_number
and l.amount=15000;
2,SQL的数据更新
SQL的数据更新包括数据插入,数据修改和数据删除等操作 。
（ 1） 数据插入
SQL的数据插入语句 INSERT有两种形式 。
输入单个元组和输入多个元组
① 输入单个元组
句法,INSERT INTO 基本表名 ( 列名表 )
VALUES (元组值 )
VALUES后的元组值中列的顺序必须同基本表的列名表一一对应 。
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
如基本表后不跟列名表,表示在 VALUES后的元组值中提供插入元组的每个分量的值,分量的顺序和关系模式中列名的顺序一致 。
如基本表后有列名表,则表示在 VALUES后的元组值中只提供插入元组对应于列名中的分量的值,元组的输入顺序和列名表的顺序一致 。
基本表后如有列名表,必须包括关系的所有非空的属性,也自然应包括关键码属性 。
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
5,2,3 SQL的数据操纵（ DML）
例 5.13 往基本表 J中插入一个元组 ('J8','地铁二号线 ','上海 ')
INSERT INTO J(JNO,JNAME,CITY) VALUES(‘J8’,
‘地铁二号线 ’,‘ 上海 ’ );
例 5.14 假设供应商关系 S的 STATUS,ADDR属性允许空,
插入一个新的供应商编号 'S10',供应商名 '光明零件厂 '。
INSERT INTO S（ SNO,SNAME） VALUES ('S10','光明零件厂 ');
② 输入多个元组
句法,INSERT INTO 基本表名 （ 列名表 ）
VALUES(元组值 ),(元组值 ),…
例 5.15 往 SPJ中连续插入三个元组
INSERT INTO SPJ VALUES
(( 'S3','P2','J8',23.3,1500)，
( 'S2','Pl','J8',33.4,50)，
('S3','P5','J8',34.5,80));
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
5,2,3 SQL的数据操纵（ DML）
（ 2） 数据删除
句法,DELETE FROM <表名 > WHERE <条件表达式 >
例 5.16 删除工程号为 J4的所有零件供应记录 。
DELETE FROM SPJ WHERE JNO='J4';
注意,DELETE语句只能从一个关系中删除元组,
而不能一次从多个关系中删除元组 。 要删除多个元组,就要写多个 DELETE语句 。
（ 3） 数据修改
当需要修改指定关系中元组的某些值时,用如下语句：
UPDATE 基本表名 SET 列名 =值表达式 [,列名 =值表达式 …] [WHERE 条件表达式 ]
该语句的意义是：修改指定表中满足条件表达式的元组中的指定属性值,其中 SET子句用于指定修改方法,即用 （ 表达式 ） 的值取代相应的属性列值 。 如果省略 WHERE子句,表示要修改表中的所有元组 。
例 5.17 将供应商 S4提供的零件 P2的价格提高 6％
UPDATE SPJ SET PRICE=PRICE*1.06 WHERE
SNO='S4' AND PNO='P2';
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
数据库系统提供安全机制,一般采用基于角色的多级授权安全机制
根据用户的特性,把用户分为不同的类别 。 如管理员,数据库备份管理员,数据库用户管理员,
普通用户等 。 不同数据库系统的用户角色不完全相同 ),
所有对数据库的操作都需要更高一级的授权,任何级别的用户在使用数据库系统时,除了必须拥有的授权外,还必须提供正确的用户名和用户口令 。
结构化查询语言
5,2,4 SQL的数据控制（ DCL）
SQL的数据控制功能是指控制用户对数据的存取权力,语句有两条：
授权语句 (GRANT) 是使某个用户具有某些权限,
收权语句 (REVOKE) 是收回已授给用户的权限 。
用户对数据的存取操作包括：
增 (INSERT),删 (DELETE)、
改 (UPDATE),查 (SELECT)
只有被授以某项操作的权限的用户才能进行某项操作 。
结构化查询语言
5,2,4 SQL的数据控制（ DCL）
5,2,4 SQL的数据控制（ DCL）
例 5.18 假设把对表 LTemp的所有操作权限授权给用户 LUSER。
GRANT ALL ON LTemp TO LUSER;
例 5.19 假设只把查看 (即 SELECT)的权限授权给用户 LUSER
GRANT SELECT ON Ltemp TO LUSER;
例 5.20 赋予用户 LUSER的对表 LTemp的更新权 (包括
INSERT,UPDATE,DELETE)收回
REVOKE INSERT,UPDATE,DELETE ON Ltemp FROM
LUSER;
5,2,1 SQL概念
结构化查询语言 SQL(Structured Query Language)
是一种介于关系代数与关系演算之间的语言,其功能包括 查询,操纵,定义和控制 四个方面,是一个通用的功能极强的关系数据库标准语言 。
SQL语言被确定为 关系数据库系统的国际标准,被绝大多数商品化关系数据库系统采用 。
使用 SQL语言,可以指定数据库要做什么,而不需要告诉 SQL如何访问数据库 。
5,2,1 SQL概念
1,SQL数据库的体系结构
SQL数据库的体系结构基本上是三级模式结构
SQL用户 用户 1 用户 2 用户 3 用户 4
视图 1 视图 2
基本表 2
存储文件 1
基本表 3基本表 1 基本表 4
存储文件 2 存储文件 3 存储文件 4
BaseTable
Stored File
View外模式对应于模式对应于内模式对应于
SQL数据库的体系结构具有如下特征
（ 1） 一个 SQL模式 (Schema)是表和约束的集合 。
（ 2） 一个表 (Table)是行 (Row)的集合,每行是列
(Column)的序列,每列对应一个数据项 。
（ 3）表可以是一个基本表，也可以是一个视图。基本表是实际存储在数据库中的表。
视图 是从基本表中导出的表,它本身不独立存储在数据库中,数据库中只存放视图的定义而不存放视图的数据,这些数据仍存放在导出视图的基本表中,因此 视图是一个虚表 。
结构化查询语言
5,2,1 SQL概念
一个 基本表 可以跨一个或多个 存储文件,
一个 存储文件 也可存放一个或多个 基本表,
一个 表 可以带若干 索引,
索引 也存放在 存储文件 中 。
每个 存储文件 对应外部存储器上一个 物理文件 。
在用户看来,视图和基本表是一样的,都是关系
(即表格 )。
结构化查询语言
5,2,1 SQL概念
3,SQL的组成,分成四个部分：
数据定义 SQL DDL：
定义 SQL模式,基本表,视图和索引 。
数据操纵 SQL DML：
数据操纵分成数据查询和数据更新两类 。 其中数据更新又分成插入,删除和修改三种操作 。
数据控制 SQL DCL：
数据控制包括对基本表和视图的授权,完整性规则的描述,事务控制语句等 。
嵌入式 SQL：
SQL语句在嵌入到宿主语言程序中时的使用规则。
结构化查询语言
5,2,1 SQL概念
SQL的数据定义部分包括对 SQL模式 (Schema),基本表 (关系,Table),视图 (View),索引 （ Index）
的创建和撤消操作 。
1,SQL模式的创建和撤消
（ 1） SQL模式的创建
SQL模式 (即数据库模式 )被定义为基本表的集合 。
SQL模式由模式名和模式拥有者的用户名或帐号来确定,并包含模式中每一个元素 (基本表,视图,索引等 )的定义 。
创建一个 SQL模式,就是定义了一个存储空间 。
结构化查询语言
5,2,2 SQL数据定义 DLL
创建 SQL模式的语法如下：
CREATE SCHEMA <模式名 > AUTHORIZATION <用户名 >
例如,定义教学数据库的 SQL模式：
CREATE SCHEMA ST_COURSE AUTHORIZATION 李斌
该模式名为 ST_COURSE,拥有者为李斌 。
创建 SQL模式一般不用? CREATE SCHEMA…”,而用
CREATE DATABASE…”。
结构化查询语言
5,2,2 SQL数据定义 DLL
（ 2） SQL模式的撤消
使用 DROP语句撤消 SQL模式 。 DROP语句的语法如下：
DROP SCHEMA <模式名 > [CASCADE|RESTRICT]
撤消方式有两种：
① CASCADE(连锁式 )方式,执行 DROP语句时,把 SQL模式及其下属的基本表,视图,索引等所有元素全部撤消；
② RESTRICT(约束式 )方式,执行 DROP语句时,只有当
SQL模式中没有任何下属元素时,才能撤消 SQL模式,
否则拒绝执行 DROP语句 。
结构化查询语言
5,2,2 SQL数据定义 DLL
5,2,2 SQL数据定义 DLL
DROP语句的语法：
DROP SCHEMA <模式名 > [CASCADE|RESTRICT]
例如,要撤消 SQL模式 ST_COURSE及其下属所有的元素,可用下列语句实现：
DROP SCHEMA ST_COURSE CASCADE
2,SQL提供的基本数据类型
（ 1） 数值型
INTEGER 长整数 (也可写成 INT)
SMALLINT 短整数
REAL 取决于机器精度的浮点数
DOUBLE PRECISION 取决于机器精度的双精度浮点数
FLOAT（ n） 浮点数,精度至少为 n位数字
NUMERIC（ p,d） 定点数,由 p位数字 （ 不包括符号,小数点 ） 组成,小数点后面有 d位数字 (也可写成
DECIMAL(P,d)或 DEC(P,d))
结构化查询语言
5,2,2 SQL数据定义 DLL
（ 2） 字符串型
CHAR（ n） 长度为 n的定长字符串
VARCHAR(n) 具有最大长度为 n的变长字符串
（ 3） 位串型
BIT(n) 长度为 n的二进制位串
BIT VARYING(n) 最大长度为 n的变长二进制位串
（ 4） 时间型
DATE 日期,包含年,月,日,形式为 YYYY-MM-DD
TIME 时间,包含一日的时,分,秒,
形式为 HH:MM:SS
结构化查询语言
5,2,2 SQL数据定义 DLL
3,基本表的创建,修改和撤消
对基本表结构的操作有三种：创建,修改和撤消 。
（ 1） 基本表的创建
句法,CREATE TABLE SQL模式名,基本表名
（ 列名 类型,
… …
完整性约束,
…… ）
结构化查询语言
5,2,2 SQL数据定义 DLL
创建基本表,需要定义基本表的结构包括属性和完整性规则,并指出它放在哪个模式中,为简单起见,模式名可省略不写 。
① 属性：每个属性的类型可以是基本类型,也可以是用户事先定义的类型 。
② 完整性规则：完整性规则主要有三种子句：
主键子句 (PRIMARY KEY)、
检查子句 (CHECK)
外键子句 (FOREIGN KEY)。
结构化查询语言
5,2,2 SQL数据定义 DLL
例 5.l 在有关零件、供应商、工程项目的数据库中，有四个关系，其结构如图 5.8所示：
图 5.8 零件、供应商、工程项目四个关系结构化查询语言
5,2,2 SQL数据定义 DLL
供应商关系,S(SNO,SNAME,STATUS,ADDR)
零件关系,P(PNO,PNAME,COLOR,WEIGHT)
工程项目关系,J(JNO,JNAME,CITY,BALANCE)
供应情况关系,SPJ(SNO,PNO,JNO,PRICE,QTY)
① 创建供应商关系 S：
结构化查询语言
5,2,2 SQL数据定义 DLL
CREATE TABLE S (SNO CHAR(4) NOT NULL,
SNAME CHAR(20) NOT NULL,
STATUS CHAR(10),
ADDR CHAR(20),
PRIMARY KEY(SNO));
供应商关系,S(SNO,SNAME,STATUS,ADDR)
关系 S有四个属性供应商号
(SNO)
字符型长度为 4
供应商名
(SNAME)
字符型长度为 20
状态 (STATUS)
字符型长度为 10地址 ( ADDR)字符型长度为 20
① 创建供应商关系 S：
结构化查询语言
5,2,2 SQL数据定义 DLL
CREATE TABLE S (SNO CHAR(4) NOT NULL,
SNAME CHAR(20) NOT NULL,
STATUS CHAR(10),
ADDR CHAR(20),
PRIMARY KEY(SNO));
供应商关系,S(SNO,SNAME,STATUS,ADDR)
主键是供应商号
SNO
本例中规定供应商号和供应商名不能取空值当一个属性定义为主键，其属性不能为空值，所以? NOT NULL”可以省略不写。
在 SQL中允许属性值为空值，当规定某一属性值不能为空值时，在定义该属性时写上保留字? NOT NULL”。
各属性的含义：
零件号 (PNO),零件名 （ PNAME）,颜色 （ COLOR）,
重量 （ WEIGHT）,单价 (PRICE),工程项目号 (JNO)、
工程项目名称 (JNAME),城市 (CITY),余额 （ BALANCE)、
供应数量 (QTY)。
供应商关系,S(SNO,SNAME,STATUS,ADDR)
零件关系,P(PNO,PNAME,COLOR,WEIGHT)
工程项目关系,J(JNO,JNAME,CITY,BALANCE)
供应情况关系,SPJ(SNO,PNO,JNO,PRICE,QTY)
结构化查询语言
5,2,2 SQL数据定义 DLL
② 创建基本表 P,J,SPJ：
结构化查询语言
5,2,2 SQL数据定义 DLL
CREATE TABLE J (JNO CHAR(4) NOT NULL,
JNAME CHAR(20),
CITY CHAR(20),
BALANCE NUMERIC(7,2),
PRIMARY KEY (JNO));
CREATE TABLE P (PNO CHAR(4) NOT NULL,
PNAME CHAR(20) NOT NULL,
COLOR CHAR(8),
WEIGHT SMALLINT,
PRIMARY KEY (PNO));
结构化查询语言
5,2,2 SQL数据定义 DLL
CREATE TABLE
SPJ (SNO CHAR(4) NOT NULL,（ 定长字符串 ）
PNO CHAR(4) NOT NULL,
JNO CHAR(4) NOT NULL,
PRICE NUMERIC(7,2),（ 定点数 ）
QTY SMALLINT,（ 短整数 ）
PRIMARY KEY (SNO,PNO,JNO),（ 主键 ）
FOREIGN KEY (SNO) REFERENCES S(SNO),（ 外键 ）
FOREIGN KEY (PNO) REFERENCES P(PNO),
FOREIGN KEY (JNO) REFERENCES J(JNO),
CHECK(QTY BETWEEN 0 AND 10000)); （ 检查子句 ）
定义中说明了五个属性定义了三个外键外键 SNO和基本表 S
中 SNO属性相对应外键 PNO和基本表 P
中 PNO属性相对应外键 JNO和基本表 J
中 JNO属性相对应定义中还使用了一个检查子句 CHECK
指出供应数量 QTY在 0-10000之间
（ 2） 基本表结构的修改
基本表建立后,可根据需要增加或删除属性 。
① 增加新的属性句法：
ALTER TABLE 基本表名 ADD 新属性名 新属性类型
例 5.2 在基本表 S中增加一个电话号码 (TELE)属性,
可用下列语句：
ALTER TABLE S ADD TELE CHAR(12);
注意,新增加的属性不能定义为? NOT NULL”。
基本表在增加一个属性后,原有元组在新增加的属性列上的值都被定义为空值 (NULL)。
结构化查询语言
5,2,2 SQL数据定义 DLL
② 删除原有的属性句法,ALTER TABLE 基本表名 DROP 属性名
[CASCADE|RESTRICT]
CASCADE方式表示：在基本表中删除某属性时,所有引用到该属性的视图和约束也要一起自动地被删除 。
RESTRICT方式表示,在没有视图或约束引用该属性时,
才能在基本表中删除该属性,否则拒绝删除 。
例 5.3 在基本表 S中删除状态 (STATUS)属性,并且将引用该属性的所有视图和约束也一起删除,可用下列语句：
ALTER TABLE S DROP STATUS CASCADE;
结构化查询语言
5,2,2 SQL数据定义 DLL
（ 3） 基本表的撤消
DROP语句的句法如下：
DROP TABLE 基本表名 （ CASCADE|RESTRICT）
撤消基本表,其所有数据均不存在 。
CASCADE和 RESTRICT的语义同前面句法中的语义一样 。
例 5.4 需要撤消基本表 S,但只有在没有视图或约束引用基本表 S中的列时才能撤消,否则拒绝撤消 。
可用下列语句实现：
DROP TABLE S RESTRICT;
结构化查询语言
5,2,2 SQL数据定义 DLL
4,视图的创建和撤消
在 SQL中,外模式级数据结构的基本单位是视图
(VIEW).
视图是从若干基本表和 (或 )其他视图构造出来的表 。 这种构造方式采用 SELECT语句实现 。
在创建一个视图时,系统把视图的定义存放在数据字典中,而并不存储视图对应的数据,在用户使用视图时才去找对应的数据 。 因此,视图被称为? 虚表? 。
结构化查询语言
5,2,2 SQL数据定义 DLL
5,2,2 SQL数据定义 DLL
（ 1） 视图的创建
句法：
CREATE VIEW 视图名 (列名表 ) AS SELECT 查询语句
例 5.5 对工程项目零件供应数据库中基本表 S,P、
J,SPJ,用户经常要用到有关项目使用零件情况的信息为：工程号 (JNO),工程项目名称 (JNAME),供应商号 (SNO),供应商名 (SNAME),零件号 (PNO)、
零件名 (PNAME),供应数量 (QTY)等列的数据，那么可用下列语句建立视图：
CREATE VIEW JSP_NAME (JNO,JNAME,SNO,SNAME,
PNO,PNAME,QTY)
AS SELECT (J.JNO,JNAME,S.SNO,SNAME,
P.PNO,PNAME,SPJ.QTY)
FROM S,P,J,SPJ
WHERE S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO AND
J.JNO=SPJ.JNO;
当视图中列名顺序与 SELECT子句中的列名顺序一致,
视图名 JSP_NAME后的列名可省 。
结构化查询语言
5,2,2 SQL数据定义 DLL
（ 2） 视图的撤消
句法,DROP VIEW 视图名
例 5.6 撤消 JSP_NAME视图,可用下列语句实现：
DROP VIEW JSP_NAME;
结构化查询语言
5,2,2 SQL数据定义 DLL
5,索引的创建和撤消
（ 1） 索引的创建
句法,CREATE [UNIQUE] INDEX 索引名 ON 基本表名 (<列名 > [<次序 >] [,<列名 > [<次序 >]]…)
基本表名指定要建索引的基本表的名字。
索引可以建在该表的一列或多列上，各列名之间用逗号分隔。
每个 <列名 >后面还可以用 <次序 >指定索引值的排列次序，包括 ASC(升序 )和 DESC(降序 )两种，默认值为
ASC。
UNIQUE表示此索引的每一个索引值只对应惟一的数据记录。
结构化查询语言
5,2,2 SQL数据定义 DLL
例 5.7 如果工程项目表已经创建,
CREATE TABLE J (JNO CHAR(4) NOT NULL,
JNAME CHAR(20),
CITY CHAR(20),
BALANCE NUMERIC(7,2),
PRIMARY KEY(JNO));
对基本表 J建立一个索引：
CREATE INDEX JNO_INDEX ON J(JNO);
此语句表示对基本表 J的列 JNO建立索引,索引键的名为 JNO_INDEX。
结构化查询语言
5,2,2 SQL数据定义 DLL
如果要求列 JNO的值在索引表中不重复,那么在
INDEX前加上保留字 UNIQUE：
CREATE UNIQUE INDEX JNO_INDEX ON J(JNO)；
SQL中的索引是非显式索引,在索引创建以后,用户在索引撤消前不会再用到该索引键的名,但是索引在用户查询时会自动起作用 。
当一个主键由几个属性构成时,一个索引键需要有多个列 。
当一个索引键有多个列时,列排序时可以升序,
也可以降序,升序排列用 ASC表示,降序排列用
DESC表示,默认时表示升序排列 。
结构化查询语言
5,2,2 SQL数据定义 DLL
例如,对基本表 SPJ中的 (SNO,PNO,JNO)建立索引 。
CREATE TABLE SPJ (SNO CHAR(4) NOT NULL,
PNO CHAR(4) NOT NULL,
JNO CHAR(4) NOT NULL,
PRICE NUMERIC(7,2),
QTY SMALLINT。
在表 SPJ中主键为 SNO,PNO,JNO。 建立索引如下：
CREATE UNIQUE INDEX SPJ_INDEX ON SPJ(SNO ASC,PNO
ASC,JNO DESC);
结构化查询语言
5,2,2 SQL数据定义 DLL
（ 2） 索引的撤消句法,DROP INDEX <索引名 >
例 5.8 撤消索引 JNO_INDEX和 SPJ_INDEX,用如下语句：
DROP INDEX JNO_INDEX,SPJ_INDEX;
结构化查询语言
5,2,2 SQL数据定义 DLL
数据操纵分成数据查询和数据更新两类。
1,SQL的数据查询
SQL中最经常使用的是从数据库中获取数据 。 从数据库中获取数据称为查询数据库,查询数据库通过使用 SELECT语句 完成 。
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
5,2,3 SQL的数据操纵（ DML）
（ 1） SELECT语句格式
语句包含 6部分,其语法形式为：
SELECT 字段表 FROM 表名 WHERE 查询条件
GROUP BY 分组字段 HAVING分组条件 ORDER BY
字段 [ASC|DESC]
其中：
字段表部分包含了查询结果要显示的 字段清单,字段之间用逗号分开 。
要选择表中所有字段,可用星号 代替 。
如果所选定的字段要更名,可在该字段后用 AS[新名 ]实现 。
FROM 表名,用于指定一个或多个表 。 如果所选的字段来自不同的表,则字段名前应加表名前缀 。
WHERE 查询条件,用于限制记录的选择。构造查询条件可使用大多数的 Visual Basic内部函数和运算符，以及 SQL特有的运算符构成表达式。
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
5,2,3 SQL的数据操纵（ DML）
GROUP BY分组字段和 HAVING分组条件子句用于 分组和分组条件设定 。 能把指定字段列表中有相同值的记录合并成一条记录 。
ORDER BY字段,决定了查找出来的记录的排列顺序 。
在 ORDER BY字段子句中,可以指定一个或多个字段作为排序键,ASC选项代表升序,DESC代表降序 。
可用 WHERE查询条件子句来排除不想分组的行,将记录分组后,也可用 HAVING分组条件子句来筛选它们 。
HAVING子句与 WHERE子句类似 。
当 GROUP BY完成记录分组后,HAVING就筛选出由
GROUP BY子句分组的,且满足 HAVING子句条件的所有记录 。
5,2,3 SQL的数据操纵（ DML）
在 SELECT语句中,SELECT和 FROM子句是必须的 。
在 SELECT子句内可使用合计函数对记录进行操作,
它返回一组记录的单 —值 。
例如,COUNT（ *） 用于计算元组的个数
COUNT（ 列名 ） 用于对一列中的值计算个数
SUM（ 列名 ） 用于求某一列值的总和 （ 此列必须是数值型 ）
AVG函数用于返回记录集的特定字段中所有值的平均数 。
（ 2） 单表查询
SQL语句的所有查询都是利用 SELECT语句完成的 。
下面通过例子说明它的使用方法 。
例 5.9 假设项目零件供应数据库中有四个基本表
(关系 )参见下图：
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
供应商关系,S(SNO,SNAME,STATUS,ADDR)
零件关系,P(PNO,PNAME,COLOR,WEIGHT)
工程项目关系,J(JNO,JNAME,CITY,BALANCE)
供应情况关系,SPJ(SNO,PNO,JNO,PRICE,QTY)
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
表 5.10 供应商关系,S
SNO SNAME STATUS ADDR
S1 原料公司 10 西安友谊路 23号
S2 红星钢管厂 30 上海浦东 100号
S3 零件制造公司 20 西安东郊 55号
S4 配件公司 50 江西胜利路 58号
S5 原料厂 40 北京三环路 89号
S6 东方配件厂 60 天津叶西路 100号结构化查询语言
5,2,3 SQL的数据操纵（ DML）
表 5.11 零件关系 P：
PNO PNAME COLOR WEIGHT
P1 钢筋 黑 25
P2 钢管 白 26
P3 螺母 红 11
P4 螺丝 黄 12
P5 齿轮 红 18
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
表 5.12 工程项目关系,J
JNO JNAME CITY BALANCE
J1 东方明珠 上海 0.00
J2 炼油厂 长春 -11.20
J3 地铁三号 北京 678.00
J4 明珠线 上海 345.00
J5 炼钢工地 天津 123.00
J6 南浦大桥 上海 234.50
J7 红星水泥厂 江西 345.60
表 5.13 供应情况关系,SPJ
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P1 J4 22.60 60
S1 P3 J1 22.80 100
S1 P3 J4 22.80 60
S3 P3 J5 22.80 100
S3 P4 J1 11.90 30
S3 P4 J4 11.90 60
S4 P2 J4 33.80 60
S5 P5 J1 15.60 20
S5 P5 J4 15.60 60
S6 P3 J1 22.80 20
S1 P3 J6 22.80 6
S3 P4 J6 11.90 6
S4 P2 J6 33.80 8
S5 P5 J6 15.60 8
试用 SQL语句表达下列查询语句 。
① 检索供应工程 J1零件的供应商编号 SNO。
SELECT DISTINCT SNO FROM SPJ WHERE JNO='J1';
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
DISTINCT表示在结果中去掉重复的供应商编号 SNO。供应情况关系 SPJ JNO='J1'
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P3 J1 22.80 100
S3 P4 J1 11.90 30
S5 P5 J1 15.60 20
S6 P3 J1 22.80 20
JNO='J1'
SNO
S1
S1
S3
S5
S6
JNO='J1'
SNO
S1
S3
S5
S6
DISTINCT SNO
表 5.13 供应情况关系,SPJ
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P1 J4 22.60 60
S1 P3 J1 22.80 100
S1 P3 J4 22.80 60
S3 P3 J5 22.80 100
S3 P4 J1 11.90 30
S3 P4 J4 11.90 60
S4 P2 J4 33.80 60
S5 P5 J1 15.60 20
S5 P5 J4 15.60 60
S6 P3 J1 22.80 20
S1 P3 J6 22.80 6
S3 P4 J6 11.90 6
S4 P2 J6 33.80 8
S5 P5 J6 15.60 8
② 检索供应工程 J1零件 P1的供应商编号 SNO。
SELECT SNO FROM SPJ WHERE JNO='J1' AND PNO='P1'
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
表 5.13 供应情况关系,SPJ
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P1 J4 22.60 60
S1 P3 J1 22.80 100
S1 P3 J4 22.80 60
S3 P3 J5 22.80 100
S3 P4 J1 11.90 30
S3 P4 J4 11.90 60
S4 P2 J4 33.80 60
S5 P5 J1 15.60 20
S5 P5 J4 15.60 60
S6 P3 J1 22.80 20
S1 P3 J6 22.80 6
S3 P4 J6 11.90 6
S4 P2 J6 33.80 8
S5 P5 J6 15.60 8
关系 SPJ JNO='J1'
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P3 J1 22.80 100
S3 P4 J1 11.90 30
S5 P5 J1 15.60 20
S6 P3 J1 22.80 20
供应情况关系 SPJ
JNO='J1'AND PNO='P1';
SNO
S1
供应情况关系 SPJ
JNO='J1'AND PNO='P1';
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
③ 查询全体工程项目的详细信息
SELECT * FROM J
如果要查询 FROM子句后面指定的基本表的全体属性时，可以用? *? 表示。所以上面的语句等价于：
SELECT JNO,JNAME,CITY,BALANCE FROM J;
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
表 5.12 工程项目关系,J
JNO JNAME CITY BALANCE
J1 东方明珠 上海 0.00
J2 炼油厂 长春 -11.20
J3 地铁三号 北京 678.00
J4 明珠线 上海 345.00
J5 炼钢工地 天津 123.00
J6 南浦大桥 上海 234.50
J7 红星水泥厂 江西 345.60
④ 查询没有正余额的工程编号,名称及城市,结果按工程编号升序排列 。
SELECT JNO,JNAME,CITY FROM J WHERE BALANCE IS
NULL OR BALANCE<=0 ORDER BY JNO；
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
表 5.12 工程项目关系,J
JNO JNAME CITY BALANCE
J1 东方明珠 上海 0.00
J2 炼油厂 长春 -11.20
J3 地铁三号 北京 678.00
J4 明珠线 上海 345.00
J5 炼钢工地 天津 123.00
J6 南浦大桥 上海 234.50
J7 红星水泥厂 江西 345.60
J1 东方明珠 上海 0.00
J2 炼油厂 长春 -11.20
谓词? IS NULL”,当 BALANCE值为 空时,
BALANCE IS NULL的值为真 (TRUE),否则为假
(FALSE)。
与? IS NULL”相对的谓词是? IS NOT NULL”，
当 BALANCE值为非空值时,BALANCE IS NOT
NULL的值为真 (TRUE),否则为假 (FALSE)。
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
⑤ 求使用零件数量在 100与 1000之间的工程项目编号,零件号和数量 。
SELECT JNO,PNO,QTY FROM SPJ WHERE QTY
BETWEEN 100 AND 1000；
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
表 5.13 供应情况关系,SPJ
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P1 J4 22.60 60
S1 P3 J1 22.80 100
S1 P3 J4 22.80 60
S3 P3 J5 22.80 100
S3 P4 J1 11.90 30
S3 P4 J4 11.90 60
S4 P2 J4 33.80 60
S5 P5 J1 15.60 20
S5 P5 J4 15.60 60
S6 P3 J1 22.80 20
S1 P3 J6 22.80 6
S3 P4 J6 11.90 6
S4 P2 J6 33.80 8
S5 P5 J6 15.60 8
WHERE QTY BETWEEN 100 AND 1000
⑥ 查询上海的供应商名称,假设供应商关系的 ADDR列的值都以城市名开头 。
SELECT SNAME FROM S WHERE ADDR LIKE '上海 %'
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
表 5.10 供应商关系,S
SNO SNAME STATUS ADDR
S1 原料公司 10 西安友谊路 23号
S2 红星钢管厂 30 上海 浦东 100号
S3 零件制造公司 20 西安东郊 55号
S4 配件公司 50 江西胜利路 58号
S5 原料厂 40 北京三环路 89号
S6 东方配件厂 60 天津叶西路 100号
字符串匹配操作符 LIKE的一般形式是：
列名 LIKE 字符串常数
列名的类型必须是字符串或可变字符串 。 在字符串常数中字符的含义如下：
％ (百分号 )：表示可以与在这个位臵的任意长度
(可以为零 )的字符串匹配 。 例如,上海明珠,上海滩 。
SELECT SNAME FROM S WHERE ADDR LIKE '上海 %'
_(下划线 )：表示可以与这个位臵的任意单个字符匹配 。 例如,北京市,北海市
SELECT SNAME FROM S WHERE ADDR LIKE ‘北 _市结构化查询语言
5,2,3 SQL的数据操纵（ DML）
新加举例：
学生表 （ student）
Id_card sname sage ssex School_number
11010519740506001 刘志刚 28 男 A_15
11010719770304002 蒋辉 25 女 A_01
11013019781008004 许静 24 女 B_19
12109619810706001 王军 21 男 C_82
13070519750215002 程红 27 女 B-57
32605619800318004 王言 22 女 A_01
40507819801124003 李执 22 男 B_19
贷款单表 (LOAN)
Loan_number amount
L_04 15000
L_11 20000
L_16 35000
L_25 10000
L_28 15000
L_30 10000
L_33 15000
学生贷款表 （ borrower）
Id_card Loan_number
11010519740506001 L_33
11010719770304002 L_16
11013019781008004 L-28
13070519750215002 L_25
13070519750215002 L_30
40507819801124003 L_11
例 1,例 1.1查询全体学生的详细信息 。
Select * from student;
例 1 例 1.2查询所属学校代号是 B_19的学生的姓名和年龄。
Select sname,sage from student where sschool_number=’
B_19’ ;
例 1,例 1.3查询所有贷款的学生的身份证号。
Select distinct id_card from borrower;
例 1,4查询所属学校代号是 B_57的学生中年龄大于 24的学生的姓名、年龄和性别。
Select sname,sage,ssex from student where sschool_number=’
B_57’ and sage>24;
例 1,5查询所属学校代号是 B_19的学生的姓名、年龄和性别，
并按年龄降序排序。
Select sname,sage,ssex from student where sschool_number=’
B_19’ order by sage desc;
例 1,6查询贷款金额为 15000的学生的贷款单号。
Select loan_number from loan where amount=15000;
例 1,7查询贷款金额在 15000至 20000之间的贷款单号，
并按贷款金额升序排序。
Select loan_number,amount from loan where amount
between 15000 and 20000 order by amount;
例 1,8查询所属学校代号是 B_57,A_01,C_82的学生的身份证号、姓名和所属学校代号，并按学校代号升序排序。
Select id_card,sname,sschool_number
from student where sschool_number in
(‘ B_57’,’ A_01’,’ C_82’ ) order by sschool_number;
in 等价于多个 or 。 如果查询所属学校代号 不是 B_57,A_01、
C_82的学生的身份证号、姓名和所属学校代号，并按学校代号升序排序。
Select id_card,sname,sschool_number
from student where sschool_number not in
(‘ B_57’,’ A_01’,’ C_82’ ) order by sschool_number;
例 1,例 1.9 查询身份证号以,110” 开始的学生的所有信息。
Select * from student where id_card like ‘ 110*’ ;
例 1,例 1.10 查询最高和最低的贷款金额。
Select min(amount) as amountofmin,max(amount) as
amountofmax from loan;
（ 3） 多表查询
实现来自多个关系的查询时,如果要引用不同关系中的同名属性,则在属性名前加关系名,即用? 关系名,属性名? 的形式表示,以便区分 。
在多个关系上的查询可以用联接查询表示也可以用嵌套查询来表示 。
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
合计函数
SQL 提供了下列合计函数（表 5.14）
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
表 5.14 合计函数合计函数 描 述
COUNT（ *） 计算元组的个数
COUNT（ 列名 ） 对一列中的值计算个数
SUM（ 列名 ） 求某一列值的总和 （ 此列必须是数值型 ）
AVG（ 列名 ） 求某一列值的平均值 （ 此列必须是数值型 ）
MAX（ 列名 ） 求某一列值的最大值
MIN（ 列名 ） 求某一列值的最小值例 5.10 试用 SQL语句表达下列每个查询语句
① 求使用了 P3零件的工程项目名称
SELECT DISTINCT JNAME
FROM J,SPJ
WHERE SPJ.JNO=J.JNO AND PNO='P3';
这个 SELECT语句执行时,要对关系 SPJ和 J做连接操作 。 执行连接操作的表示方法是 FROM子句后面写上执行 连接操作的表名 SPJ和 J,再在 WHERE子句中写上 连接的条件 SPJ,JNO=J.JNO。
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
表 5.13 供应情况关系,SPJ
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P1 J4 22.60 60
S1 P3 J1 22.80 100
S1 P3 J4 22.80 60
S3 P3 J5 22.80 100
S3 P4 J1 11.90 30
S3 P4 J4 11.90 60
S4 P2 J4 33.80 60
S5 P5 J1 15.60 20
S5 P5 J4 15.60 60
S6 P3 J1 22.80 20
S1 P3 J6 22.80 6
S3 P4 J6 11.90 6
S4 P2 J6 33.80 8
S5 P5 J6 15.60 8
PNO=‘P3’;
J1
J4
J5
J6
当 PNO=‘P3’时,找到 J1,J4,J5和 J6,根据关系 J,可得
DISTINCT JNAME,
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
表 5.12 工程项目关系,J
JNO JNAME CITY BALANCE
J1 东方明珠 上海 0.00
J2 炼油厂 长春 -11.20
J3 地铁三号 北京 678.00
J4 明珠线 上海 345.00
J5 炼钢工地 天津 123.00
J6 南浦大桥 上海 234.50
J7 红星水泥厂 江西 345.60
东方明珠明珠线炼钢工地南浦大桥
② 求供应工程 J1零件为红色的供应商号 SNO
SELECT DISTINCT SNO FROM SPJ,P WHERE
SPJ.PNO=P.PNO AND JNO='J1' AND COLOR='红 ';
由 COLOR=‘红 ’ 可找到 PNO为 P3,P5
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
表 5.11 零件关系 P：
PNO PNAME COLOR WEIGHT
P1 钢筋 黑 25
P2 钢管 白 26
P3 螺母 红 11
P4 螺丝 黄 12
P5 齿轮 红 18
根据 COLOR=‘红 ’ ;可找到 P3,P5。 根据 JNO=‘J1’
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P1 J4 22.60 60
S1 P3 J1 22.80 100
S1 P3 J4 22.80 60
S3 P3 J5 22.80 100
S3 P4 J1 11.90 30
S3 P4 J4 11.90 60
S4 P2 J4 33.80 60
S5 P5 J1 15.60 20
S5 P5 J4 15.60 60
S6 P3 J1 22.80 20
S1 P3 J6 22.80 6
S3 P4 J6 11.90 6
S4 P2 J6 33.80 8
S5 P5 J6 15.60 8
SNO PNO JNO
S1 P3 J1
S5 P5 J1
S6 P3 J1
SNO
S1
S5
S6
5,2,3 SQL的数据操纵（ DML）
③ 求至少使用了零件编号为 P3和 P5的工程编号 JNO。
SELECT DISTINCT X.JNO
FROM SPJ AS X,SPJ AS Y
WHERE X.JNO=Y.JNO AND X.PNO='P3' AND
Y.PNO='P5'
同一个关系 SPJ在一层中出现两次,为区别,引入两个元组变量 (即别名 )X和 Y。 在语句中应用元组变量对列名进行限定 。 保留字 AS 在语句中可省略 。
表 5.13 供应情况关系,SPJ
SNO PNO JNO PRICE QTY
S1 P1 1 22.60 80
S1 P1 J4 22.60 60
S1 P3 J1 22.80 100
S1 P3 J4 22.80 60
S3 P3 J5 22.80 100
S3 P4 J1 11.90 30
S3 P4 J4 11.90 60
S4 P2 J4 33.80 60
S5 P5 J1 15.60 20
S5 P5 J4 15.60 60
S6 P3 J1 22.80 20
S1 P3 J6 22.80 6
S3 P4 J6 11.90 6
S4 P2 J6 33.80 8
S5 P5 J6 15.60 8
5,2,3 SQL的数据操纵（ DML）
例 5.11对关系 J,P,SPJ进行查询 。
① 求供应 P3零件的供应商个数 。
分析,这个查询结果只有一行和一列,就是供应? P3”
零件的供应商个数 。
SELECT COUNT(DISTINCT SNO) AS COUNT_P3
FROM SPJ
WHERE PNO='P3';
或者
SELECT COUNT(SNO) AS COUNT_P3
FROM (SELECT DISTINCT SNO FROM SPJ
WHERE PNO='P3')
谓词 DISTINCT用在列名前表示消除该列中重复的值
COUNT_P3为输出的列名
5,2,3 SQL的数据操纵（ DML）
SELECT COUNT(DISTINCT SNO) AS COUNT_P3 FROM
SPJ WHERE PNO='P3';
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P1 J4 22.60 60
S1 P3 J1 22.80 100
S1 P3 J4 22.80 60
S3 P3 J5 22.80 100
S3 P4 J1 11.90 30
S3 P4 J4 11.90 60
S4 P2 J4 33.80 60
S5 P5 J1 15.60 20
S5 P5 J4 15.60 60
S6 P3 J1 22.80 20
S1 P3 J6 22.80 6
S3 P4 J6 11.90 6
S4 P2 J6 33.80 8
S5 P5 J6 15.60 8
SNO PNO
S1 P3
S1 P3
S3 P3
S6 P3
S1 P3
SNO PNO
S1 P3
S3 P3
S6 P3
COUNT_P3
3
5,2,3 SQL的数据操纵（ DML）
② 求项目余额的最大值,最小值,总值和平均值,输出的列名 分别为,MAX_NUMBER,MIN_NUMBER,SUM_NUMBER,AVG_NUMBER。
SELECT MAX(BALANCE) AS MAX_NUMBER,
MIN(BLANCE) AS MIN_NUMBER，
SUM(BALANCE) AS SUM_NUMBER,
AVG(BALANCE) AS AVG_NUMBER FROM J;
工程项目关系,J
JNO JNAME CITY BALANCE
J1 东方明珠 上海 0.00
J2 炼油厂 长春 -11.20
J3 地铁三号 北京 678.00
J4 明珠线 上海 345.00
J5 炼钢工地 天津 123.00
J6 南浦大桥 上海 234.50
J7 红星水泥厂 江西 345.60
MAX_NUMBER MIN_NUMBER SUM_NUMBER AVG_NUMBER
678.00 -11.20 1317.8 244.8
5,2,3 SQL的数据操纵（ DML）
（ 5） 数据分组
SQL语言提供了 GROUP BY 子句和 HAVING子句,将查询结果进行分组,然后再对每个分组进行统计,实现分组统计 。
例 5.12对关系 J,P,SPJ进行查询 。
① 统计每个供应商供应不同零件的种数和供应总数量 。
SELECT SNO,
COUNT(DISTINCT PNO) AS COUNT_QTY,
SUM(QTY) AS SUM_ QTY
FROM SPJ GROUP BY SNO
SELECT SNO,COUNT(DISTINCT PNO) AS COUNT_QTY,
SUM(QTY) AS SUM_ QTY FROM SPJ GROUP BY SNO
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P1 J4 22.60 60
S1 P3 J1 22.80 100
S1 P3 J4 22.80 60
S1 P3 J6 22.80 6
SNO PNO JNO PRICE QTY
S3 P3 J5 22.80 100
S3 P4 J1 11.90 30
S3 P4 J4 11.90 60
S3 P4 J6 11.90 6
SNO PNO JNO PRICE QTY
S4 P2 J4 33.80 60
S4 P2 J6 33.80 8
SNO PNO JNO PRICE QTY
S5 P5 J1 15.60 20
S5 P5 J4 15.60 60
S5 P5 J6 15.60 8
SNO PNO JNO PRICE QTY
S6 P3 J1 22.80 20
SNO COUNT_QTY SUM_ QTY
S1 2 306
S3 2 196
S4 1 68
S5 1 88
S6 1 20
5,2,3 SQL的数据操纵（ DML）
② 统计上海地区的每个项目使用零件的种数 (超过 3
种 )和零件总数量 。 要求查询结果按零件的种数升序排列,种数相同时按总数量降序排列 。
SELECT SPJ.JNO,
COUNT(DISTINCT SPJ.PNO) AS COUNT_PNO,
SUM(QTY) AS SUM_QTY
FROM J,SPJ
WHERE J.CITY=’上海 ’ AND J.JNO=SPJ.JNO
GROUP BY SPJ.JNO
HAVING COUNT(DISTINCT PNO)>3
ORDER BY 2,3 DESC;
SELECT SPJ.JNO,COUNT(DISTINCT SPJ.PNO) AS COUNT_PNO,
SUM(QTY) AS SUM_QTY FROM J,SPJ
WHERE J.CITY=’上海 ’ AND J.JNO=SPJ.JNO
GROUP BY SPJ.JNO
HAVING COUNT(DISTINCT PNO)>3
ORDER BY 2,3 DESC;
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P1 J4 22.60 60
S1 P3 J1 22.80 100
S1 P3 J4 22.80 60
S1 P3 J6 22.80 6
S3 P3 J5 22.80 100
S3 P4 J1 11.90 30
S3 P4 J4 11.90 60
S3 P4 J6 11.90 6
S4 P2 J4 33.80 60
S4 P2 J6 33.80 8
S5 P5 J1 15.60 20
S5 P5 J4 15.60 60
S5 P5 J6 15.60 8
S6 P3 J1 22.80 20
工程项目关系,J
JNO JNAME CITY BALANCE
J1 东方明珠 上海 0.00
J2 炼油厂 长春 -11.20
J3 地铁三号 北京 678.00
J4 明珠线 上海 345.00
J5 炼钢工地 天津 123.00
J6 南浦大桥 上海 234.50
J7 红星水泥厂 江西 345.60
根据 WHERE子句的条件,对关系 J和 SPJ执行连接操作找出满足条件为? 上海? 的工程的元组
GROUP BY SPJ.JNO
HAVING COUNT(DISTINCT PNO)>3
ORDER BY 2,3 DESC;
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P1 J4 22.60 60
S1 P3 J1 22.80 100
S1 P3 J4 22.80 60
S1 P3 J6 22.80 6
S3 P4 J1 11.90 30
S3 P4 J4 11.90 60
S3 P4 J6 11.90 6
S4 P2 J4 33.80 60
S4 P2 J6 33.80 8
S5 P5 J1 15.60 20
S5 P5 J4 15.60 60
S5 P5 J6 15.60 8
S6 P3 J1 22.80 20
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P3 J1 22.80 100
S3 P4 J1 11.90 30
S5 P5 J1 15.60 20
S6 P3 J1 22.80 20
SNO PNO JNO PRICE QTY
S1 P1 J4 22.60 60
S1 P3 J4 22.80 60
S3 P4 J4 11.90 60
S4 P2 J4 33.80 60
S5 P5 J4 15.60 60
SNO PNO JNO PRICE QTY
S1 P3 J6 22.80 6
S3 P4 J6 11.90 6
S4 P2 J6 33.80 8
S5 P5 J6 15.60 8
按工程号 JNO的值对上海的工程进行分组,将 JNO列的值相同的元组分为一组
SNO PNO JNO PRICE QTY
S1 P1 J4 22.60 60
S1 P3 J4 22.80 60
S3 P4 J4 11.90 60
S4 P2 J4 33.80 60
S5 P5 J4 15.60 60
GROUP BY SPJ.JNO HAVING COUNT(DISTINCT PNO)>3 ORDER
BY 2,3 DESC;
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
JNO COUNT_PNO SUM_ QTY
J1 4 306
J4 5 68
J6 4 20
对每一个分组进行合计操作；并按 HAVING子句的条件对产生的元组进行选择,消除只使用三种以下零件的元组
SNO PNO JNO PRICE QTY
S1 P1 J1 22.60 80
S1 P3 J1 22.80 100
S3 P4 J1 11.90 30
S5 P5 J1 15.60 20
S6 P3 J1 22.80 20
SNO PNO JNO PRICE QTY
S1 P3 J6 22.80 6
S3 P4 J6 11.90 6
S4 P2 J6 33.80 8
S5 P5 J6 15.60 8
对结果进行排序
5,2,3 SQL的数据操纵（ DML）
SELECT 字段表 FROM 表名 WHERE 查询条件 GROUP
BY 分组字段 HAVING分组条件 ORDER BY字段
[ASC|DESC]
学号 =SN； 学生姓名 =SNAME； 课程号 =CN;课程名
=CNAME; 成绩 =SG; 系 =SD； 年龄 =SA
学生选课 =CS(CN,SN);
学生课程成绩 =SC(SN,CN,SG)
学生情况表 =S(SN,SNAME,SD,SA)
课程表 =C(CN,CNAME)
5,2,3 SQL的数据操纵（ DML）
SELECT SN
FROM S
WHERE SD='D2'
学生情况表 S
SN SNAME SD SA
S1 王洪 D1 20
S2 李景 D1 19
S3 章林 D2 19
S4 武斌 D2 20
S5 程信 D3 20
例 1:求 2系学生的学号。
学生情况表 =S(SN,SNAME,SD,SA)
S3
S4
5,2,3 SQL的数据操纵（ DML）
例 2:求选修了课程的学生学号 。
学生课程成绩 =SC(SN,CN,SG)
SELECT DISTINCT SN
FROM SC
这里 WHERE不写,表示条件为空 。 学生课程成绩 SC
SN CN SG
S1 C1 98
S2 C2 85
S3 C5 76
S1 C3 78
S2 C1 89
S4 C5 95
SELECT后面的
DISTINCT
表示要去掉重复的 SN
S4
S3
S2
S1
例 3:求选修 C1的学生学号和得分,
并按分数降序排列,
学生课程成绩 =SC(SN,CN,SG)
SELECT SN,SG
FROM SC
WHERE CN='C1'
ORDER BY SG DESC
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
这里 ORDER BY表示结果要排序,BY后面指出排序字段,DESC表示降序。 89S2
98S1
SGSN
学生课程成绩 SC
SN CN SG
S1 C1 98
S2 C2 85
S3 C5 76
S1 C3 78
S2 C1 89
S4 C5 95
例 4:找学号为 S1,S3,S4的学生姓名,所属系名学生情况表 =S(SN,SNAME,SD,SA)
SELECT SNAME,SD
FROM S
WHERE SN = 'S1' or
SN = 'S3' or
SN ='S4'
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
学生情况表 S
SN SNAME SD SA
S1 王洪 D1 20
S2 李景 D1 19
S3 章林 D2 19
S4 武斌 D2 20
S5 程信 D3 20
例 5:求有人选修的课程号,课程名字 。
课程表 =C(CN,CNAME)
学生课程成绩 =SC(SN,CN,SG)
SELECT DISTINCT C.CN,CNAME
FROM C,SC
WHERE SC.CN = C.CN
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
学生成绩 SC
SN CN SG
S1 C1 98
S2 C2 85
S3 C5 76
S1 C1 78
S2 C1 89
S4 C5 95
课程表 C
CN CNAME
C1 高数
C2 物理
C3 英语
C4 电工
C5 网络C2C1
CN
有人选修
C5
高数网络物理
CNAME
C1
C2
C5
C1
C1
C5
5,2,3 SQL的数据操纵（ DML）
例 6,求进修了课程 C1的学生人数 。
学生课程成绩 =SC(SN,CN,SG)
SELECT COUNT(SN)
FROM SC
WHERE CN='C1'
学生课程成绩 SC
SN CN SG
S1 C1 98
S2 C2 85
S3 C5 76
S1 C3 78
S2 C1 89
S4 C5 95COUNT（ 列名 ）对一列中的值计算个数结构化查询语言
5,2,3 SQL的数据操纵（ DML）
例 7:求 5系学生的平均年龄 。
学生情况表 =S(SN,SNAME,SD,SA)
SELECT AVG(SA)
FROM S
WHERE SD='D5' 学生情况表 SSN SNAME SD SA
S1 王洪 D1 20
S2 李景 D1 19
S3 章林 D2 19
S4 武斌 D2 20
S5 程信 D3 20
AVG（ 列名） 求某一列值的平均值（此列必须是数值型）
结构化查询语言例 8:找出选修课程超过 2门的学生学号学生课程成绩 =SC(SN,CN,SG)
SELECT SN
FROM SC
GROUP BY SN
HAVING COUNT(*) >2
5,2,3 SQL的数据操纵（ DML）
学生课程成绩 SC
SN CN SG
S1 C1 98
S2 C2 85
S3 C5 76
S1 C3 78
S2 C1 89
S4 C5 95
S1 C2 88
S2 C4 78
COUNT（ *） 计算元组的个数结构化查询语言学生课程成绩 SC1
SN CN SG
S1 C1 98
S1 C3 78
S1 C2 88
5,2,3 SQL的数据操纵（ DML）
S1
S2
COUNT(*) >2
GROUP BY SN
结构化查询语言学生课程成绩 SC2
SN CN SG
S3 C5 76
学生课程成绩 SC2
SN CN SG
S2 C2 85
S2 C1 89
S2 C4 78
COUNT(*) >2
例 9:找出选修课程名字等于
高数,的学号和学生名字学生情况表
=S(SN,SNAME,SD,SA)
课程表 =C(CN,CNAME)
学 生 课 程 成 绩 =SC(SN,CN,
SG)
SELECT S.SN,SNAME
FROM S,C,SC
WHERE SC.SN=S.SN
AND SC.CN=C.CN
AND CNAME='高数 '
学生情况表 S
SN SNAME SD SA
S1 王洪 D1 20
S2 李景 D1 19
S3 章林 D2 19
S4 武斌 D2 20
S5 程信 D3 20
学生成绩 SC
SN CN SG
S1 C1 98
S2 C3 85
S3 C5 76
S1 C3 78
S2 C1 89
S4 C5 95
S
N
SNAME
S
1
王洪
S
2
李景课程表 C
CN CNAME
C1 高数
C2 物理
C3 英语
C4 电工
C5 网络结构化查询语言
5,2,3 SQL的数据操纵（ DML）
新加举例：
学生表（ student）
Id_card sname sage ssex School_number
11010519740506001 刘志刚 28 男 A_15
11010719770304002 蒋辉 25 女 A_01
11013019781008004 许静 24 女 B_19
12109619810706001 王军 21 男 C_82
13070519750215002 程红 27 女 B-57
32605619800318004 王言 22 女 A_01
40507819801124003 李执 22 男 B_19
贷款单表 (LOAN)
Loan_number amount
L_04 15000
L_11 20000
L_16 35000
L_25 10000
L_28 15000
L_30 10000
L_33 15000
学生贷款表 （ borrower）
Id_card Loan_number
11010519740506001 L_33
11010719770304002 L_16
11013019781008004 L-28
13070519750215002 L_25
13070519750215002 L_30
40507819801124003 L_11
例 2.1 查询已参加贷款的学生的全部信息和其贷款单号 。
Select student.*,borrower.loan_number
from student,borrower
where student.id_card,borrower.id_card;
可写为：
Select s.*,b.loan_number
from student as s,borrower as b
where s.id_card,b.id_card;
例 1,例 2.2 查询贷款号为 L_33的学生信息 。
Select s.* from student as s,borrower as b
where s.id_card,b.id_card and b.loan_number=’ L_33’ ;
例 2,3查询贷款金额为 15000元的学生信息。
Select s.*,l.loan_number
from student as s,borrower as b,loan as l
where s.id_card,b.id_card and b.loan_number=l.loan_number
and l.amount=15000;
2,SQL的数据更新
SQL的数据更新包括数据插入,数据修改和数据删除等操作 。
（ 1） 数据插入
SQL的数据插入语句 INSERT有两种形式 。
输入单个元组和输入多个元组
① 输入单个元组
句法,INSERT INTO 基本表名 ( 列名表 )
VALUES (元组值 )
VALUES后的元组值中列的顺序必须同基本表的列名表一一对应 。
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
如基本表后不跟列名表,表示在 VALUES后的元组值中提供插入元组的每个分量的值,分量的顺序和关系模式中列名的顺序一致 。
如基本表后有列名表,则表示在 VALUES后的元组值中只提供插入元组对应于列名中的分量的值,元组的输入顺序和列名表的顺序一致 。
基本表后如有列名表,必须包括关系的所有非空的属性,也自然应包括关键码属性 。
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
5,2,3 SQL的数据操纵（ DML）
例 5.13 往基本表 J中插入一个元组 ('J8','地铁二号线 ','上海 ')
INSERT INTO J(JNO,JNAME,CITY) VALUES(‘J8’,
‘地铁二号线 ’,‘ 上海 ’ );
例 5.14 假设供应商关系 S的 STATUS,ADDR属性允许空,
插入一个新的供应商编号 'S10',供应商名 '光明零件厂 '。
INSERT INTO S（ SNO,SNAME） VALUES ('S10','光明零件厂 ');
② 输入多个元组
句法,INSERT INTO 基本表名 （ 列名表 ）
VALUES(元组值 ),(元组值 ),…
例 5.15 往 SPJ中连续插入三个元组
INSERT INTO SPJ VALUES
(( 'S3','P2','J8',23.3,1500)，
( 'S2','Pl','J8',33.4,50)，
('S3','P5','J8',34.5,80));
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
5,2,3 SQL的数据操纵（ DML）
（ 2） 数据删除
句法,DELETE FROM <表名 > WHERE <条件表达式 >
例 5.16 删除工程号为 J4的所有零件供应记录 。
DELETE FROM SPJ WHERE JNO='J4';
注意,DELETE语句只能从一个关系中删除元组,
而不能一次从多个关系中删除元组 。 要删除多个元组,就要写多个 DELETE语句 。
（ 3） 数据修改
当需要修改指定关系中元组的某些值时,用如下语句：
UPDATE 基本表名 SET 列名 =值表达式 [,列名 =值表达式 …] [WHERE 条件表达式 ]
该语句的意义是：修改指定表中满足条件表达式的元组中的指定属性值,其中 SET子句用于指定修改方法,即用 （ 表达式 ） 的值取代相应的属性列值 。 如果省略 WHERE子句,表示要修改表中的所有元组 。
例 5.17 将供应商 S4提供的零件 P2的价格提高 6％
UPDATE SPJ SET PRICE=PRICE*1.06 WHERE
SNO='S4' AND PNO='P2';
结构化查询语言
5,2,3 SQL的数据操纵（ DML）
数据库系统提供安全机制,一般采用基于角色的多级授权安全机制
根据用户的特性,把用户分为不同的类别 。 如管理员,数据库备份管理员,数据库用户管理员,
普通用户等 。 不同数据库系统的用户角色不完全相同 ),
所有对数据库的操作都需要更高一级的授权,任何级别的用户在使用数据库系统时,除了必须拥有的授权外,还必须提供正确的用户名和用户口令 。
结构化查询语言
5,2,4 SQL的数据控制（ DCL）
SQL的数据控制功能是指控制用户对数据的存取权力,语句有两条：
授权语句 (GRANT) 是使某个用户具有某些权限,
收权语句 (REVOKE) 是收回已授给用户的权限 。
用户对数据的存取操作包括：
增 (INSERT),删 (DELETE)、
改 (UPDATE),查 (SELECT)
只有被授以某项操作的权限的用户才能进行某项操作 。
结构化查询语言
5,2,4 SQL的数据控制（ DCL）
5,2,4 SQL的数据控制（ DCL）
例 5.18 假设把对表 LTemp的所有操作权限授权给用户 LUSER。
GRANT ALL ON LTemp TO LUSER;
例 5.19 假设只把查看 (即 SELECT)的权限授权给用户 LUSER
GRANT SELECT ON Ltemp TO LUSER;
例 5.20 赋予用户 LUSER的对表 LTemp的更新权 (包括
INSERT,UPDATE,DELETE)收回
REVOKE INSERT,UPDATE,DELETE ON Ltemp FROM
LUSER;
