第五章 关系数据库标准语言 —— SQL
SQL（ Structured Query Language）是 1974年由 Boyce和
Chamberlin提出的。在 IBM公司 San Jose Research Laboratory 研制
的 System R上实现了这种语言。由于它功能丰富、使用方式灵活、
语言简洁易学等突出优点，在计算机工业界和计算机用户中倍受欢
迎并深深扎根。 1986年 10月，美国国家标准局（ ANSI）的数据库委
员会 X3H2批准了 SQL作为关系数据库语言的美国标准。同年公布了
标准 SQL文本。此后不久，国际标准化组织 (ISO)也作出了同样的决
定 。
本章介绍 SQL语言,并进一步讨论关系数据库的基本概念, SQL
标准文本和实施的 SQL语言相比,后者常常作了许多必要的扩充,因
此我们选用 IBM公司在 DB2上的 SQL语言,它基本上和标准 SQL一致,
而在对关系模型的符合程度上比标准 SQL更好,
5.1 SQL 概貌及其特点
SQL(Structured Query Language)的英文名称是结构查询语
言。实际上它的功能包括查询 (Query),操纵 (Manipulation),定义
(Definition)和控制 (Control)四个方面,是一个综合的、通用的、功能
极强的关系数据库语言。
一,SQL的主要特点
1,一体化的特点
2,两种使用方式,统一的语法结构
3,高度非过程化
4,语言简洁,易学易用
(1).数据库查询 SELECT
(2),数据定义 CREATE,DROP
(3),数据操纵 INSERT,UPDATE,DELETE
(5),数据控制 GRANT,REVOKE
二, SQL语言支持关系数据库三级模式结构 (图 5-1)
SQL
View V1 View V2
Base table
B1
Base table
B2
Base table
B3
Base table
B4
Stored file
S1
Stored file
S2
Stored file
S3
Stored file
S4
外模式
模式
内模式
用户
图 5-1
5.2 SQL 数据定义功能
SQL的数据定义功能包括三部分,定义基本表,定义视图和定义
索引,他们是,
CREATE TABLE CREATE VIEW CREATE INDEX
DROP TABLE DROP VIEW DROP INDEX
ALTER TABLE
5.2.1 基本表定义和修改
一, 定义基本表的语句
1,格式,
CREATE TABLE 表名 (列名 1 类型 [NOT NULL]
[,列名 1 类型 [NOT NULL]]…)
[其它参数 ];
2.几点说明
(1).任选项?其它参数?是与物理存储有关的参数,随具体系统而
异,
(2),DB2支持的数据类型
①, INTEGER 全字长 (31 bits)的二进制整数,
②, SMALLINT 半字长 (15 bits)的二进制整数
③, DECIMAL(p[,q]) 压缩十进制数,共 p位,小数点后有 q位,
④, FLOAT 双字长的浮点数,
⑤, CHAR(n) 长度为 n的定长字符串,
⑥, VARCHAR(n) 变长字符串,最大长度为 n.
(3),SQL 支持空值的概念3.举例,
CHEATE TABLE S(S# CHAR(3) NOT NULL,
SN CHAR(15),
SD CHAR(15),
SA SMALLINT);
二, 修改基本表定义的语句
1,格式,
ALTER TABLE 表名 ADD 列名 类型 ;
2.举例
ALTER TABLE S ADD SEX SMALLINT;
三, 删除基本表的语句
1,格式,
DROP TABLE 表名 ;
2.举例
ALTER TABLE S ;
5.2.2 索引的建立和删除
通常索引的建立和删除由 DBA(Dada Base Administrator)或表
的主人负责,
一,建立索引的语句
1.格式
CREATE [UNIQUE] INDEX 索引名
ON 基本表名 (列名 [次序 ][,列名 [次序 ]]…)
[其它参数 ];
2.举例,
CREATE UNIQUE INDEX XSNO ON S(S#)
CREATE UNIQUE INDEX XCNO ON C(C#)
CREATE UNIQUE INDEX XSC ON SC(S# ASC,C# DESC)
二,删除索引的语句
1.格式
DROP INDEX 索引名 ;
2.举例
DROP INDEX XSNO;
5.2.3 小 结
在关系数据库中允许 DBA随时使用 SQL数据定义语句来定义基
本表、索引和视图，系统十分方便灵活。
在非关系系统中，数据库的模式一经建立再要加入新的记录型
或修改老的记录型是十分费事的工作。
而象 DB2这样的 SQL关系数据库系统，增删改基本表、索引和
视图都不必停止系统的其它处理，不会影响其它用户对数据库的正
常使用。
5.3 SQL 数据操纵功能
SQL的数据操纵功能包括 SELECT,INSERT,DELETE,和
UPDATE 四个语句,即检索和更新 (包括增、删、改 )两部分功能,
5.3.1 SQL 查询语句
1,查询语句的格式
SELECT 目表列
FROM 基本表 (或视图 )
[WHERE 条件表达式 ]
[GROUP BY 列名 1[HAVING 内部函数表达式 ]]
[ORDER BY 列名 2 ];ASCDESC
2,功能
5.3.1.1 简单查询
例 1,求数学系学生的学号、姓名。
SELECT S#,SN
FROM S
WHERE SD=‘MA’;
例 2,求选修了课程的学生的学号。
SELECT DISTINCT S#
FROM SC;
例 3,求全体学生的详细信息。
SELECT *
FROM S;
例 4,查询经过计算的值。求学生学号和出生年份,
SELECT S#,2000-SA
FROM S;
例 5,要求排序的查询,
求选修 C1课程的学生的学号和得分,结果按分数降序排列。
SELECT S#,G
FROM SC
WHERE C#=‘C1’
ORDER BY G DESC;
例 6,使用 BETWEEN的查询,
求年龄在 20岁与 22岁之间 (包括 20岁和 22岁 )的学生学号和年龄,
SELECT S#,SA
FROM S
WHERE SA BETWEEN 20AND 22;
例 7,使用 IN的查询,
求在下列各系的学生,MA(数学系 ),CS(计算机科学系 ).
SELECT *
FROM S
WHERE SD IN(‘MA’,’CS’);
谓词 IN实际上是一系列谓词‘ OR’的缩写,等价的查询为,
SELECT *
FROM S
WHERE SD=‘MA’ OR SD=‘CS’;
求不是数学系、计算机科学系的学生。
SELECT *
FROM S
WHERE SD NOT IN(‘MA’,’CS’);
例 8,使用 LIKE的查询,
求姓名是以字母 D打头的学生。
SELECT *
FROM S
WHERE SN LIKE ‘D%’;
LIKE 谓词的一般形式为,
列名 LIKE 字符串常数
*说明,
1.列名的类型必须是字符串或变长字符串,
2.字符串常数中字符定含义,
⑴, 字符? _”,表示任意的单个字符 ;
⑵, 字符? %”,表示任意字符串 (长度可以为零 );
⑶, 所有其它字符只代表自己,
例 8,涉及空值 NULL的查询,
求缺少学习成绩的学生学号合课程号。
SELECT S#,C#
FROM SC
WHERE G IS NULL;
涉及空值的谓词的一般形式是,
列名 IS [NOT] NULL.
注意, 不能写成, 列名 =NULL
或 列名 ┐=NULL
5.3.1.2 连接查询
定义, 若查询涉及两个以上的表,则称之为连接查询,
例 10,求学生以及他选修课程的课程号码和成绩。
SELECT S.*,SC.*
FROM S,SC
WHERE S.S# =SC.S#;
几点说明,
(1),连接字段的类型必须是可比的,但不必相同,多数是相同的,
(2),连接谓词中的比较符可以是,
=,>,<,>=,<=,┐=六种,
(3),等值连接与自然连接的概念,
例 11,自然连接查询,
SELECT S.S#,S.SN,S.SD,S.SA,SC.C#,SC.G
FROM S,SC
WHERE S.S# =SC.S#;
若字段名在各表中是唯一的,可以把字段名前的表名去掉,否则必
须加上表名作为前缀,以免引起混淆,例 11可写成,
SELECT S.S#,SN,SD,SA,C#,G
FROM S,SC
WHERE S.S# =SC.S#;
例 12,带有其他条件的连接查询,
求选修 C1课程且成绩为 B以上的学生及成绩,
SELECT S.S#,SN,SD,SA,G
FROM S,SC
WHERE S.S# =SC.S# AND SC.C# =‘C1’ AND
(SC.G=‘A’ OR SC.G=‘B’);
例 13,求每一门课程的间接先行课 (即先行课的先行课 ).
SELECT FIRST.C# SECOND.PC#
FROM C FIRST C SECOND
WHERE FIRST.PC# =SECOND.C# ;
此例中的 FIRST和 SECOND为引入的别名 ;
例 14,求选修了课程名为‘ J’的学生的学号和姓名,
SELECT S#,SN
FROM S,SC
WHERE S# IN
SELECT S#
FROM SC
WHERE C# IN
SELECT C#
FROM C
WHERE CN=‘J’;
5.3.1.3 嵌 套 查 询
嵌套查询亦称为子查询,嵌套查询是指一个 SELECT-FROM-WHERE
查询块嵌入另一个查询块之中,SQL中允许多层嵌套,
例 15,使用存在量词 EXISTS 和 NOT EXISTS的嵌套查询,
求选学了 C2课程的学生姓名,
SELECT SN
FROM S
WHERE EXISTS
(SELECT *
FROM SC
WHERE S# =SC.S# AND C# =‘C2’);
例 16.使用 NOT EXISTS的嵌套查询,求不选修 C3课程的学生姓名,
SELECT SN
FROM S
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE S# =SC.S# AND C# =‘C3’);
例 17,求选修了全部课程的学生姓名,
SELECT SN
FROM S
WHERE NOT EXISTS
(SELECT *
FROM C
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE S# =S.S# AND C# =C.C#));
例 19,使用 UNION 的查询,
求计算机科学系的学生以及年龄小于 18岁的学生,
SELECT *
FROM S
WHERE SD=‘CS’
SELECT *
FROM S
WHERE SA<18;
5.3.1.4 库 函 数
SQL提供了 6种库函数,其功能如下,
COUNT 对一列中的值计算个数
COUNT(*) 计算记录个数
SUM 求某一列值的总和
AVG 求某一列值的平均值
MAX 求某一列值中的最大值
MIN 求某一列值中的最小值
例 20,在 SELECT子句中使用库函数,
(1),求学生总人数,
SELECT COUNT(*)
FROM S;
(2),求选修了课程的学生人数,
SELECT COUNT(DISTINCT S#)
FROM SC;
(3),求计算机科学系学生的平均年龄,
SELECT AVG(SA)
FROM S
WHERE SD=‘CS’;
例 21,GROUP BY 的使用
求课程号及选修该课程的学生人数,
SELECT C#,COUNT(S#)
FROM SC
GROUP BY C# ;
例 22,HAVING 的使用
求选修课程超过 3门的学生学号,
SELECT S#
FROM SC
GROUP BY S#
HAVING COUNT(*)>3 ;
5.3.2 SQL 更新语句
SQL更新语句包括修改、删除和插入三类语句。
一、修改 (UPDATE)(也称为更新 )
1,语句的一般格式,
UPDATE 表名
SET 字段 =表达式 [,字段 =表达式 ]…
[WHERE 谓词 ];
2,功能
3,举例,
例 23,单记录修改,把学生 S1的姓名改为 WANG PING.
UPDATE S
SET SN=‘WANG PING’
WHERE S#=‘S1’;
例 24 多记录修改, 把所有学生的年龄加 1.
UPDATE S
SET SA=SA+1;
例 25 具有子查询的更新,把数学系全体学生的成绩置零,
UPDATE S
SET G=0
WHERE ‘MA’=
(SELECT SD
FROM S
WHERE S.S# =SC.S#);
例 26,多个表的更新,
UPDATE S
SET S# =‘S9’
WHERE S# = ‘S2’;
UPDATE SC
SET S# =‘S9’
WHERE S# = ‘S2’;
二、删除语句 (DELETE)
1,语句的一般格式,
DELETE
FROM 表名
[WHERE 谓词 ];
2,功能
3,举例,
例 27,单记录删除,把学生 S9删除,
DELETE
FROM S
WHERE S# =‘S9’;
执行删除操作也可能产生破坏完整性的情况,
例 28 多记录删除,删除所有的学生选课记录,
DELETE
FROM SC;
例 29,带有子查询的删除,删除计算机科学系全体学生的选课记录,
DELETE
FROM SC
WHERE ‘CS’ =
(SELECT SD
FROM S
WHERE S.S# =SC.S#);
三、插入 语句 (INSERT)
1,语句的一般格式,
INSERT
INTO 表名 [(字段名 [,字段名 ]…)]
VALUES (常量 [,常量 ]…);
或
INSERT
INTO 表名 [(字段名 [,字段名 ]…)]
子查询 ;
2,功能
3,举例,
例 30 单记录插入,把一个新学生,S10(学号 ),’Zhang Ming’(姓名 ),
IS(信息系 ),18(年龄 ),插入表中,
INSERT
INTO S
VALUES (‘S10’,’Zhang Ming’,’IS’,18);
例 31,插入一个选课记录 (‘S15’,’C10’,’A’)
INSERT
INTO SC(S#,C#,G)
VALUES (‘S15’,’C10’,’A’);
例 32,多记录插入,
对每个系,求学生的平均年龄,并把结果插入数据库中,
CREATE TABEL DEPTAGE
(SDEPT CHAR(5),AVGAGE SMALLINT);
INSERT
INTO DEPTAGE (SDEPT,AVGAGE)
SELECT SD,AVG(SA)
FROM S
GROUP BY SD;
5.4 视 图
视图 (View)是从一个或几个基本表 (或视图 )导出的表,
5.4.1 视图的定义
1,SQL建立视图的语句格式,
CREATE VIEW 视图名 [(字段名 [,字段名 ]…)]
AS 子查询
[WITH CHECK OPTION];
2.举例,
例 1,建立计算机科学系的学生视图,
CREATE VIEW CS_S
AS SELECT S#,SN,SA
FROM S
WHERE SD=‘CS’;
例 2,把学生的学号及它的平均成绩定义为一个视图,
CREATE VIEW S_G(S#,GAVG)
AS SELECT S#,AVG(G)
FROM SC
GROUP BY S# ;
3.删除视图的语句格式
DROP VIEW 视图名 ;
例如, DROP VIEW CS_S;
注意,视图的删除就是从数据字典中删除,由此视图导出的其它
视图也将自动删除,
若导出此视图的基本表删除了,则此视图也将自动删除,
5.4.2 视图的查询
视图定义后,用户可以如同基本表那样对视图查询,
举例,
SELECT S#,SA
FROM CS_S
WHERE SA<20;
SELECT S#,SA
FROM S
WHERE SD=‘CS’ AND SA<20;
系统执行此查询时首先把它转换成等价的对基本表的查询,然后
执行修改了的查询,本例的修正后的查询语句为,
5.4.3 视图的更新
对视图的更新最终要转换成对基本表的更新,
1,修 改
UPDATE CS_S
SET SN=‘WANG PING’
WHERE S# =‘S1’;
将转换成对基本表 S的更新,
UPDATE S
SET SN=‘WANG PING’
WHERE SD=‘CS’ AND S# =‘S1’;
2,插入
INSERT
INTO CS_S
VALUES (‘S12’,‘Yan Xi’,19);
转换成对基本表的插入
INSERT
INTO S
VALUES (‘S12’,‘Yan Xi’,’CS’,19);
在关系数据库中,并非所有的视图都是可更新的,如,
UPDATE S_G
SET GAVG=90
WHERE S# =‘S1’;
注意时间
3,视图分类,
(1),行列子集视图 (可更新的视图 ).
(2),理论上可更新的视图,
(3),不可更新的视图
4,在 BD2中,只有从单个基本表导出的视图才允许对它进行更新
操作,并且具有以下限制,
P136-137 共 7条,
不可更新的视图 与 不允许更新的视图 的概念
5.4.4 视图的优点
1,视图对于数据库的重构造提供了一定程度的逻辑独立性,
关系数据库中，数据库的重构造是不可避免的。最常见的情况是把一个表‘垂直’地分为两个以上的表。例如把学生关系
S(S#,SN,SD,SA)
分为：
SX(S#,SN,SA)
SY(S#,SD)
这时原表 S是 SX,SY的自然连接的结果,因此可以建立一个视图 S:
CREATE VIEW S(S#,SN,SD,,SA)
AS SELECT SX.S#,SX.SN,SY.SD,SX.SA
FROM SX,SY
WHERE SX.S# =SY.S#;
2.简化了用户观点
3,视图机制使不同的用户能以不同的方式看待同一数据库,
4.视图机制对机密数据提供了自动的安全保护功能
5.5 SQL 数据控制功能
SQL 数据控制功能是指控制用户对数据的存取权力,
一,DB2中授权语句的一般格式为,
GRANT 权力 [,权力 ]…[ON 对象类型 对象名 ] TO
用户 [,用户 ]…
[WITH GRANT OPTION];
对不同类型的操作对象可有不同的操作权力,见表 5-2
任选项 WITH GRANT OPTION的作用是使获得某种权力的
用户可以把权力再授予别的用户,
二,举例
例 1,把修改学生学号和查询 S表的权力授给王平,
GRANT UPDATE(S#),SELECT ON TABLE S TO WANGPING;
例 2,把对表 S,C,SC的查询、修改插入和删除等全部权力授予张
勇、杨斌。
GRANT ALL PRIVILIGES ON TABLE S,C,SC TO
ZHANG YON,YANBIN;
例 3,把对表 C的查询权力授予所有用户,
GRANT SELECT ON TABLE C TO PUBLIC;
例 4,把在数据库 S-C中建立表的权力授予用户杜明,
GRANT CREATETAB ON DATABASE S-C TO DUMING;
例 5,把对表 S的查询权力授予用户 U2，并给 U2有再授权的权力。
GRANT SELECT ON TABLE S TO U2
WITH GRANT OPTION；
例 6,U2把查询表 S的权力授给用户 U3.
GRANT SELECT ON TABLE S TO U3;
三,授予的权力可以用 REVOKE语句收回，格式为：
REVOKE 权力 [，权力 ]…[ON 对象类型 对象名 ]FROM 用户 [,用户 ]…
例 7,把用户王平修改学生学号的权力收回,
REVOKE UPDATE(S#) ON TABLE S FROM WANGPING;
例 8,把用户 U2查询 S表的权力收回,
REVOKE SELECT ON TABLE S FROM U2;
5.6 嵌入式 SQL
把 SQL 嵌入主语言使用时必须解决三个问题,
1.区分 SQL语句与主语言语句,
2.数据库工作单元和程序工作单元之间的通信,
3.一个 SQL语句原则上可产生或处理一组记录,而主语言一次只能处理一个记录,为此必须协调两种处理方式,
5.6.1 不用游标的 DML语句
一, 查询结果为单记录的 SELECT语句
1.格式,(主语言为 PL/1)
EXEC SQL SELECT 目标列
INTO 主变量 [空值标志 ]列
FROM 基本表 (或视图 )
[WHERE 条件表达式 ]
…;
2.举例
例 1,根据主变量 GIVENS#的值找到该学生的信息,
EXEC SQL SELECT SN,SD,SA
INTO,SNAME,:DEPT,:AGE
FROM S
WHERE S# =:GIVENS#;
二, UP DATE 语句
例 3,把计算机科学系全体学生年龄加上主变量 RAISE的值,
EXEC SQL UPDATE S
SET SA=SA+:RAISE
WHERE SD=‘CS’;
例 4,把计算机科学系全体学生的年龄置 NULL值,
SAID=-1;
EXEC SQL UPDATE S
SET SA=SA+:RAISE:SAID
WHERE SD=‘CS’;
它等价于,
例 5,
EXEC SQL UPDATE S
SET SA=NULL
WHERE SD=‘CS’;
三, DELETE 语句
例 6,删除这样的选课记录,这些学生的系名在主变量 DEPT中
给出,
EXEC SQL DELETE
FROM SC
WHERE,DEPT=
(SELECT SD
FROM S
WHERE S.S# =SC.S#);
四, INSERT 语句
例 7,把一个新生插入 S表中,诸字段值由相应的主变量给出,
EXEC SQL INSERT
INTO S(S#,SN,SD)
VALUES(:GIVENS#,:SNAME,:DEPT);
插入的新生 SA字段为 NULL值,也可以用空值标志变量来表示,
例 8.
AGEID=-1
EXEC SQL INSERT
INTO S(S#,SN,SD,SA)
VALUES(:GIVENS#,:SNAME,:DEPT,:AGE:AGEID);
5.6.2 使用游标的 DML语句
例 1,查找由 DEPT变量中给出的某个系的全体学生信息,
EXCE SQL DECLARE SX CURSOR FOR ………………,①
SELECT S#,SN,SA
FROM S
WHERE SD=:DEPT;
EXEC SQL OPEN SX; …………………………..… ②
DO WHILE
EXEC SQL FECH SX INTO:S#,:SNAME.:AGE;…,③
……..
END;
EXEC SQL CLOSE SX; …………………………..… ④
例 2 把当前游标所指向的学生年龄增加主变量 RAISE的值,
EXEC SQL UPDATE S
SET SA=AS+:RAISE
WHERE CURRENT OF SX;
例 3 删除当前游标指向的那个学生记录,
EXEC SQL DELETE
FROM S
WHERE CURRENT OF SX;
第五章结束
SQL（ Structured Query Language）是 1974年由 Boyce和
Chamberlin提出的。在 IBM公司 San Jose Research Laboratory 研制
的 System R上实现了这种语言。由于它功能丰富、使用方式灵活、
语言简洁易学等突出优点，在计算机工业界和计算机用户中倍受欢
迎并深深扎根。 1986年 10月，美国国家标准局（ ANSI）的数据库委
员会 X3H2批准了 SQL作为关系数据库语言的美国标准。同年公布了
标准 SQL文本。此后不久，国际标准化组织 (ISO)也作出了同样的决
定 。
本章介绍 SQL语言,并进一步讨论关系数据库的基本概念, SQL
标准文本和实施的 SQL语言相比,后者常常作了许多必要的扩充,因
此我们选用 IBM公司在 DB2上的 SQL语言,它基本上和标准 SQL一致,
而在对关系模型的符合程度上比标准 SQL更好,
5.1 SQL 概貌及其特点
SQL(Structured Query Language)的英文名称是结构查询语
言。实际上它的功能包括查询 (Query),操纵 (Manipulation),定义
(Definition)和控制 (Control)四个方面,是一个综合的、通用的、功能
极强的关系数据库语言。
一,SQL的主要特点
1,一体化的特点
2,两种使用方式,统一的语法结构
3,高度非过程化
4,语言简洁,易学易用
(1).数据库查询 SELECT
(2),数据定义 CREATE,DROP
(3),数据操纵 INSERT,UPDATE,DELETE
(5),数据控制 GRANT,REVOKE
二, SQL语言支持关系数据库三级模式结构 (图 5-1)
SQL
View V1 View V2
Base table
B1
Base table
B2
Base table
B3
Base table
B4
Stored file
S1
Stored file
S2
Stored file
S3
Stored file
S4
外模式
模式
内模式
用户
图 5-1
5.2 SQL 数据定义功能
SQL的数据定义功能包括三部分,定义基本表,定义视图和定义
索引,他们是,
CREATE TABLE CREATE VIEW CREATE INDEX
DROP TABLE DROP VIEW DROP INDEX
ALTER TABLE
5.2.1 基本表定义和修改
一, 定义基本表的语句
1,格式,
CREATE TABLE 表名 (列名 1 类型 [NOT NULL]
[,列名 1 类型 [NOT NULL]]…)
[其它参数 ];
2.几点说明
(1).任选项?其它参数?是与物理存储有关的参数,随具体系统而
异,
(2),DB2支持的数据类型
①, INTEGER 全字长 (31 bits)的二进制整数,
②, SMALLINT 半字长 (15 bits)的二进制整数
③, DECIMAL(p[,q]) 压缩十进制数,共 p位,小数点后有 q位,
④, FLOAT 双字长的浮点数,
⑤, CHAR(n) 长度为 n的定长字符串,
⑥, VARCHAR(n) 变长字符串,最大长度为 n.
(3),SQL 支持空值的概念3.举例,
CHEATE TABLE S(S# CHAR(3) NOT NULL,
SN CHAR(15),
SD CHAR(15),
SA SMALLINT);
二, 修改基本表定义的语句
1,格式,
ALTER TABLE 表名 ADD 列名 类型 ;
2.举例
ALTER TABLE S ADD SEX SMALLINT;
三, 删除基本表的语句
1,格式,
DROP TABLE 表名 ;
2.举例
ALTER TABLE S ;
5.2.2 索引的建立和删除
通常索引的建立和删除由 DBA(Dada Base Administrator)或表
的主人负责,
一,建立索引的语句
1.格式
CREATE [UNIQUE] INDEX 索引名
ON 基本表名 (列名 [次序 ][,列名 [次序 ]]…)
[其它参数 ];
2.举例,
CREATE UNIQUE INDEX XSNO ON S(S#)
CREATE UNIQUE INDEX XCNO ON C(C#)
CREATE UNIQUE INDEX XSC ON SC(S# ASC,C# DESC)
二,删除索引的语句
1.格式
DROP INDEX 索引名 ;
2.举例
DROP INDEX XSNO;
5.2.3 小 结
在关系数据库中允许 DBA随时使用 SQL数据定义语句来定义基
本表、索引和视图，系统十分方便灵活。
在非关系系统中，数据库的模式一经建立再要加入新的记录型
或修改老的记录型是十分费事的工作。
而象 DB2这样的 SQL关系数据库系统，增删改基本表、索引和
视图都不必停止系统的其它处理，不会影响其它用户对数据库的正
常使用。
5.3 SQL 数据操纵功能
SQL的数据操纵功能包括 SELECT,INSERT,DELETE,和
UPDATE 四个语句,即检索和更新 (包括增、删、改 )两部分功能,
5.3.1 SQL 查询语句
1,查询语句的格式
SELECT 目表列
FROM 基本表 (或视图 )
[WHERE 条件表达式 ]
[GROUP BY 列名 1[HAVING 内部函数表达式 ]]
[ORDER BY 列名 2 ];ASCDESC
2,功能
5.3.1.1 简单查询
例 1,求数学系学生的学号、姓名。
SELECT S#,SN
FROM S
WHERE SD=‘MA’;
例 2,求选修了课程的学生的学号。
SELECT DISTINCT S#
FROM SC;
例 3,求全体学生的详细信息。
SELECT *
FROM S;
例 4,查询经过计算的值。求学生学号和出生年份,
SELECT S#,2000-SA
FROM S;
例 5,要求排序的查询,
求选修 C1课程的学生的学号和得分,结果按分数降序排列。
SELECT S#,G
FROM SC
WHERE C#=‘C1’
ORDER BY G DESC;
例 6,使用 BETWEEN的查询,
求年龄在 20岁与 22岁之间 (包括 20岁和 22岁 )的学生学号和年龄,
SELECT S#,SA
FROM S
WHERE SA BETWEEN 20AND 22;
例 7,使用 IN的查询,
求在下列各系的学生,MA(数学系 ),CS(计算机科学系 ).
SELECT *
FROM S
WHERE SD IN(‘MA’,’CS’);
谓词 IN实际上是一系列谓词‘ OR’的缩写,等价的查询为,
SELECT *
FROM S
WHERE SD=‘MA’ OR SD=‘CS’;
求不是数学系、计算机科学系的学生。
SELECT *
FROM S
WHERE SD NOT IN(‘MA’,’CS’);
例 8,使用 LIKE的查询,
求姓名是以字母 D打头的学生。
SELECT *
FROM S
WHERE SN LIKE ‘D%’;
LIKE 谓词的一般形式为,
列名 LIKE 字符串常数
*说明,
1.列名的类型必须是字符串或变长字符串,
2.字符串常数中字符定含义,
⑴, 字符? _”,表示任意的单个字符 ;
⑵, 字符? %”,表示任意字符串 (长度可以为零 );
⑶, 所有其它字符只代表自己,
例 8,涉及空值 NULL的查询,
求缺少学习成绩的学生学号合课程号。
SELECT S#,C#
FROM SC
WHERE G IS NULL;
涉及空值的谓词的一般形式是,
列名 IS [NOT] NULL.
注意, 不能写成, 列名 =NULL
或 列名 ┐=NULL
5.3.1.2 连接查询
定义, 若查询涉及两个以上的表,则称之为连接查询,
例 10,求学生以及他选修课程的课程号码和成绩。
SELECT S.*,SC.*
FROM S,SC
WHERE S.S# =SC.S#;
几点说明,
(1),连接字段的类型必须是可比的,但不必相同,多数是相同的,
(2),连接谓词中的比较符可以是,
=,>,<,>=,<=,┐=六种,
(3),等值连接与自然连接的概念,
例 11,自然连接查询,
SELECT S.S#,S.SN,S.SD,S.SA,SC.C#,SC.G
FROM S,SC
WHERE S.S# =SC.S#;
若字段名在各表中是唯一的,可以把字段名前的表名去掉,否则必
须加上表名作为前缀,以免引起混淆,例 11可写成,
SELECT S.S#,SN,SD,SA,C#,G
FROM S,SC
WHERE S.S# =SC.S#;
例 12,带有其他条件的连接查询,
求选修 C1课程且成绩为 B以上的学生及成绩,
SELECT S.S#,SN,SD,SA,G
FROM S,SC
WHERE S.S# =SC.S# AND SC.C# =‘C1’ AND
(SC.G=‘A’ OR SC.G=‘B’);
例 13,求每一门课程的间接先行课 (即先行课的先行课 ).
SELECT FIRST.C# SECOND.PC#
FROM C FIRST C SECOND
WHERE FIRST.PC# =SECOND.C# ;
此例中的 FIRST和 SECOND为引入的别名 ;
例 14,求选修了课程名为‘ J’的学生的学号和姓名,
SELECT S#,SN
FROM S,SC
WHERE S# IN
SELECT S#
FROM SC
WHERE C# IN
SELECT C#
FROM C
WHERE CN=‘J’;
5.3.1.3 嵌 套 查 询
嵌套查询亦称为子查询,嵌套查询是指一个 SELECT-FROM-WHERE
查询块嵌入另一个查询块之中,SQL中允许多层嵌套,
例 15,使用存在量词 EXISTS 和 NOT EXISTS的嵌套查询,
求选学了 C2课程的学生姓名,
SELECT SN
FROM S
WHERE EXISTS
(SELECT *
FROM SC
WHERE S# =SC.S# AND C# =‘C2’);
例 16.使用 NOT EXISTS的嵌套查询,求不选修 C3课程的学生姓名,
SELECT SN
FROM S
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE S# =SC.S# AND C# =‘C3’);
例 17,求选修了全部课程的学生姓名,
SELECT SN
FROM S
WHERE NOT EXISTS
(SELECT *
FROM C
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE S# =S.S# AND C# =C.C#));
例 19,使用 UNION 的查询,
求计算机科学系的学生以及年龄小于 18岁的学生,
SELECT *
FROM S
WHERE SD=‘CS’
SELECT *
FROM S
WHERE SA<18;
5.3.1.4 库 函 数
SQL提供了 6种库函数,其功能如下,
COUNT 对一列中的值计算个数
COUNT(*) 计算记录个数
SUM 求某一列值的总和
AVG 求某一列值的平均值
MAX 求某一列值中的最大值
MIN 求某一列值中的最小值
例 20,在 SELECT子句中使用库函数,
(1),求学生总人数,
SELECT COUNT(*)
FROM S;
(2),求选修了课程的学生人数,
SELECT COUNT(DISTINCT S#)
FROM SC;
(3),求计算机科学系学生的平均年龄,
SELECT AVG(SA)
FROM S
WHERE SD=‘CS’;
例 21,GROUP BY 的使用
求课程号及选修该课程的学生人数,
SELECT C#,COUNT(S#)
FROM SC
GROUP BY C# ;
例 22,HAVING 的使用
求选修课程超过 3门的学生学号,
SELECT S#
FROM SC
GROUP BY S#
HAVING COUNT(*)>3 ;
5.3.2 SQL 更新语句
SQL更新语句包括修改、删除和插入三类语句。
一、修改 (UPDATE)(也称为更新 )
1,语句的一般格式,
UPDATE 表名
SET 字段 =表达式 [,字段 =表达式 ]…
[WHERE 谓词 ];
2,功能
3,举例,
例 23,单记录修改,把学生 S1的姓名改为 WANG PING.
UPDATE S
SET SN=‘WANG PING’
WHERE S#=‘S1’;
例 24 多记录修改, 把所有学生的年龄加 1.
UPDATE S
SET SA=SA+1;
例 25 具有子查询的更新,把数学系全体学生的成绩置零,
UPDATE S
SET G=0
WHERE ‘MA’=
(SELECT SD
FROM S
WHERE S.S# =SC.S#);
例 26,多个表的更新,
UPDATE S
SET S# =‘S9’
WHERE S# = ‘S2’;
UPDATE SC
SET S# =‘S9’
WHERE S# = ‘S2’;
二、删除语句 (DELETE)
1,语句的一般格式,
DELETE
FROM 表名
[WHERE 谓词 ];
2,功能
3,举例,
例 27,单记录删除,把学生 S9删除,
DELETE
FROM S
WHERE S# =‘S9’;
执行删除操作也可能产生破坏完整性的情况,
例 28 多记录删除,删除所有的学生选课记录,
DELETE
FROM SC;
例 29,带有子查询的删除,删除计算机科学系全体学生的选课记录,
DELETE
FROM SC
WHERE ‘CS’ =
(SELECT SD
FROM S
WHERE S.S# =SC.S#);
三、插入 语句 (INSERT)
1,语句的一般格式,
INSERT
INTO 表名 [(字段名 [,字段名 ]…)]
VALUES (常量 [,常量 ]…);
或
INSERT
INTO 表名 [(字段名 [,字段名 ]…)]
子查询 ;
2,功能
3,举例,
例 30 单记录插入,把一个新学生,S10(学号 ),’Zhang Ming’(姓名 ),
IS(信息系 ),18(年龄 ),插入表中,
INSERT
INTO S
VALUES (‘S10’,’Zhang Ming’,’IS’,18);
例 31,插入一个选课记录 (‘S15’,’C10’,’A’)
INSERT
INTO SC(S#,C#,G)
VALUES (‘S15’,’C10’,’A’);
例 32,多记录插入,
对每个系,求学生的平均年龄,并把结果插入数据库中,
CREATE TABEL DEPTAGE
(SDEPT CHAR(5),AVGAGE SMALLINT);
INSERT
INTO DEPTAGE (SDEPT,AVGAGE)
SELECT SD,AVG(SA)
FROM S
GROUP BY SD;
5.4 视 图
视图 (View)是从一个或几个基本表 (或视图 )导出的表,
5.4.1 视图的定义
1,SQL建立视图的语句格式,
CREATE VIEW 视图名 [(字段名 [,字段名 ]…)]
AS 子查询
[WITH CHECK OPTION];
2.举例,
例 1,建立计算机科学系的学生视图,
CREATE VIEW CS_S
AS SELECT S#,SN,SA
FROM S
WHERE SD=‘CS’;
例 2,把学生的学号及它的平均成绩定义为一个视图,
CREATE VIEW S_G(S#,GAVG)
AS SELECT S#,AVG(G)
FROM SC
GROUP BY S# ;
3.删除视图的语句格式
DROP VIEW 视图名 ;
例如, DROP VIEW CS_S;
注意,视图的删除就是从数据字典中删除,由此视图导出的其它
视图也将自动删除,
若导出此视图的基本表删除了,则此视图也将自动删除,
5.4.2 视图的查询
视图定义后,用户可以如同基本表那样对视图查询,
举例,
SELECT S#,SA
FROM CS_S
WHERE SA<20;
SELECT S#,SA
FROM S
WHERE SD=‘CS’ AND SA<20;
系统执行此查询时首先把它转换成等价的对基本表的查询,然后
执行修改了的查询,本例的修正后的查询语句为,
5.4.3 视图的更新
对视图的更新最终要转换成对基本表的更新,
1,修 改
UPDATE CS_S
SET SN=‘WANG PING’
WHERE S# =‘S1’;
将转换成对基本表 S的更新,
UPDATE S
SET SN=‘WANG PING’
WHERE SD=‘CS’ AND S# =‘S1’;
2,插入
INSERT
INTO CS_S
VALUES (‘S12’,‘Yan Xi’,19);
转换成对基本表的插入
INSERT
INTO S
VALUES (‘S12’,‘Yan Xi’,’CS’,19);
在关系数据库中,并非所有的视图都是可更新的,如,
UPDATE S_G
SET GAVG=90
WHERE S# =‘S1’;
注意时间
3,视图分类,
(1),行列子集视图 (可更新的视图 ).
(2),理论上可更新的视图,
(3),不可更新的视图
4,在 BD2中,只有从单个基本表导出的视图才允许对它进行更新
操作,并且具有以下限制,
P136-137 共 7条,
不可更新的视图 与 不允许更新的视图 的概念
5.4.4 视图的优点
1,视图对于数据库的重构造提供了一定程度的逻辑独立性,
关系数据库中，数据库的重构造是不可避免的。最常见的情况是把一个表‘垂直’地分为两个以上的表。例如把学生关系
S(S#,SN,SD,SA)
分为：
SX(S#,SN,SA)
SY(S#,SD)
这时原表 S是 SX,SY的自然连接的结果,因此可以建立一个视图 S:
CREATE VIEW S(S#,SN,SD,,SA)
AS SELECT SX.S#,SX.SN,SY.SD,SX.SA
FROM SX,SY
WHERE SX.S# =SY.S#;
2.简化了用户观点
3,视图机制使不同的用户能以不同的方式看待同一数据库,
4.视图机制对机密数据提供了自动的安全保护功能
5.5 SQL 数据控制功能
SQL 数据控制功能是指控制用户对数据的存取权力,
一,DB2中授权语句的一般格式为,
GRANT 权力 [,权力 ]…[ON 对象类型 对象名 ] TO
用户 [,用户 ]…
[WITH GRANT OPTION];
对不同类型的操作对象可有不同的操作权力,见表 5-2
任选项 WITH GRANT OPTION的作用是使获得某种权力的
用户可以把权力再授予别的用户,
二,举例
例 1,把修改学生学号和查询 S表的权力授给王平,
GRANT UPDATE(S#),SELECT ON TABLE S TO WANGPING;
例 2,把对表 S,C,SC的查询、修改插入和删除等全部权力授予张
勇、杨斌。
GRANT ALL PRIVILIGES ON TABLE S,C,SC TO
ZHANG YON,YANBIN;
例 3,把对表 C的查询权力授予所有用户,
GRANT SELECT ON TABLE C TO PUBLIC;
例 4,把在数据库 S-C中建立表的权力授予用户杜明,
GRANT CREATETAB ON DATABASE S-C TO DUMING;
例 5,把对表 S的查询权力授予用户 U2，并给 U2有再授权的权力。
GRANT SELECT ON TABLE S TO U2
WITH GRANT OPTION；
例 6,U2把查询表 S的权力授给用户 U3.
GRANT SELECT ON TABLE S TO U3;
三,授予的权力可以用 REVOKE语句收回，格式为：
REVOKE 权力 [，权力 ]…[ON 对象类型 对象名 ]FROM 用户 [,用户 ]…
例 7,把用户王平修改学生学号的权力收回,
REVOKE UPDATE(S#) ON TABLE S FROM WANGPING;
例 8,把用户 U2查询 S表的权力收回,
REVOKE SELECT ON TABLE S FROM U2;
5.6 嵌入式 SQL
把 SQL 嵌入主语言使用时必须解决三个问题,
1.区分 SQL语句与主语言语句,
2.数据库工作单元和程序工作单元之间的通信,
3.一个 SQL语句原则上可产生或处理一组记录,而主语言一次只能处理一个记录,为此必须协调两种处理方式,
5.6.1 不用游标的 DML语句
一, 查询结果为单记录的 SELECT语句
1.格式,(主语言为 PL/1)
EXEC SQL SELECT 目标列
INTO 主变量 [空值标志 ]列
FROM 基本表 (或视图 )
[WHERE 条件表达式 ]
…;
2.举例
例 1,根据主变量 GIVENS#的值找到该学生的信息,
EXEC SQL SELECT SN,SD,SA
INTO,SNAME,:DEPT,:AGE
FROM S
WHERE S# =:GIVENS#;
二, UP DATE 语句
例 3,把计算机科学系全体学生年龄加上主变量 RAISE的值,
EXEC SQL UPDATE S
SET SA=SA+:RAISE
WHERE SD=‘CS’;
例 4,把计算机科学系全体学生的年龄置 NULL值,
SAID=-1;
EXEC SQL UPDATE S
SET SA=SA+:RAISE:SAID
WHERE SD=‘CS’;
它等价于,
例 5,
EXEC SQL UPDATE S
SET SA=NULL
WHERE SD=‘CS’;
三, DELETE 语句
例 6,删除这样的选课记录,这些学生的系名在主变量 DEPT中
给出,
EXEC SQL DELETE
FROM SC
WHERE,DEPT=
(SELECT SD
FROM S
WHERE S.S# =SC.S#);
四, INSERT 语句
例 7,把一个新生插入 S表中,诸字段值由相应的主变量给出,
EXEC SQL INSERT
INTO S(S#,SN,SD)
VALUES(:GIVENS#,:SNAME,:DEPT);
插入的新生 SA字段为 NULL值,也可以用空值标志变量来表示,
例 8.
AGEID=-1
EXEC SQL INSERT
INTO S(S#,SN,SD,SA)
VALUES(:GIVENS#,:SNAME,:DEPT,:AGE:AGEID);
5.6.2 使用游标的 DML语句
例 1,查找由 DEPT变量中给出的某个系的全体学生信息,
EXCE SQL DECLARE SX CURSOR FOR ………………,①
SELECT S#,SN,SA
FROM S
WHERE SD=:DEPT;
EXEC SQL OPEN SX; …………………………..… ②
DO WHILE
EXEC SQL FECH SX INTO:S#,:SNAME.:AGE;…,③
……..
END;
EXEC SQL CLOSE SX; …………………………..… ④
例 2 把当前游标所指向的学生年龄增加主变量 RAISE的值,
EXEC SQL UPDATE S
SET SA=AS+:RAISE
WHERE CURRENT OF SX;
例 3 删除当前游标指向的那个学生记录,
EXEC SQL DELETE
FROM S
WHERE CURRENT OF SX;
第五章结束
