第三章 关系数据库标准语言 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更好,
3.1 SQL 概 述
SQL(Structured Query Language)的英文名称是结构查询语
言。实际上它的功能包括查询 (Query),操纵 (Manipulation),定义
(Definition)和控制 (Control)四个方面,是一个综合的、通用的、功能
极强的关系数据库语言。
3.1.1 SQL的主要特点
1,综合统一
2,高度非过程化
3,面向集合的操作方式
4,两种使用方式,统一的语法结构
5,语言简洁,易 学 易用
(1).数据库查询 SELECT
(2),数据定义 CREATE,DROP
(3),数据操纵 INSERT,UPDATE,DELETE
(5),数据控制 GRANT,REVOKE
3.1.2 SQL语言的基本概念
图 3.1 SQL对关系数据库模式的支持
SQL
View V1 View V2
Base table
B1
Base table
B2
Base table
B3
Base table
B4
Stored file
S1
Stored file
S2
外模式
模式
内模式
3.2 SQL 数据定义
SQL的数据定义功能包括三部分,定义基本表,定义视图和定义
索引,他们是,
CREATE TABLE CREATE VIEW CREATE INDEX
DROP TABLE DROP VIEW DROP INDEX
ALTER TABLE
3.2.1 定义、删除和修改基本表
一, 定义基本表的语句
1,格式,
CREATE TABLE <表名 >(<列名 1> <类型 >[列级完整性约束条件 ]
[,<列名 2><类型 >[列级完整性约束条件 ]]…
[<表级完整性约束条件 >]; )
2,举 例,
例 1,P88
CREATE TABLE Student
(Sno CHAR(5) NOT NULL UNIQUE,
Sname CHAR(20) UNIQUE,
Ssex CHAR(1),
Sage INT
Sdept CHAR(15));
二, 修改基本表
1,格式,
ALTER TABLE <表名 >
[ADD <新列名 ><数据类型 >[完整性约束 ]];
[DROP<完整性约束名 >]
[MODIFY<列名 ><数据类型 >];
2.举例
例 2:
ALTER TABLE Student ADD Scome date;
三, 删除基本表
1,格式,
DROP TABLE <表名 >;
2.举例
例 5,删除 Student表
ALTER TABLE Student ;
例 3:
ALTER TABLE Student MODIFY Sage SMALLINT;
例 4:
ALTER TABLE Student DROP UNIQUE(Sname);
5.2.2 建立与删除索引
通常索引的建立和删除由 DBA(Dada Base Administrator)或表
的主人负责,
一,建立索引
1.格式
CREATE [UNIQUE] [CLUSTER] INDEX<索引名 >
ON <表名 >(<列名 >[<次序 >][,<列名 >[<次序,]]…) ;
2.举例,
例 6:
CREATE UNIQUE INDEX Stusno ON Student(Sno);
CREATE UNIQUE INDEX Coucno ON Couse(Cno);
CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);
二,删除索引
1.格式
DROP INDEX <索引名 >;
2.举例
例 7:删除 Student表的 Stusname索引,
DROP INDEX Stusname
小 结
在关系数据库中允许 DBA随时使用 SQL数据定义语句来定义基
本表、索引和视图，系统十分方便灵活。
在非关系系统中，数据库的模式一经建立再要加入新的记录型
或修改老的记录型是十分费事的工作。
而象 DB2这样的 SQL关系数据库系统，增删改基本表、索引和
视图都不必停止系统的其它处理，不会影响其它用户对数据库的正
常使用。
3.3 查 询
数据库查询是数据库的核心操作。 SQL语句提供了
SELECT语句进行数据库的查询。
查询语句的一般格式
SELECT [ALL| DISTINCT] <目标列表达式 >[,<目标列表达式
>]…
FROM <表名或视图名 >[,<表名或视图名 >]…
[WHERE <条件表达式 >]
[GROUP BY <列名 1>[HAVING <条件表达式 >]]
[ORDER BY <列名 2>[ASC|DESC] ];
3.3.1 单表查询
一、选择表中的若干列
1.查询指定列
例 1,查询全体学生的学号与姓名。
SELECT Sno,Sname
FROM Student;
例 2,查询全体学生的姓名、学号、所在系。
SELECT Sname,Sno,Sdept
FROM Student；
2.查询全部列
例 3,查询全体学生的详细信息。
SELECT *
FROM Student;
等价于,
SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student;
3,查询经过计算的值。
例 4.查询全体学生学号和出生年份,
SELECT Sno,2003-Sage
FROM Student;
二、选择表中的若干元组
1.消除取值重复的行
例 6,查询选修了课程的学生的学号,
SELECT Sno
FROM SC;
指定 DISTINCT短语,可去掉结果表中的重复行,
SELECT DISTINCT Sno
FROM SC;
2,查询满足条件的元组
查询满足指定条件的元组可以通过 WHERE子句实现,
(1) 比较大小
例 7,查询计算机系全体学生的名单,
SELECT Sname
FROM Student
WHERE Sdept=?CS?;
例 8,查询所有年龄在 20岁以下的学生的姓名及年龄,
SELECT Sname,Sage
FROM Student
WHERE Sage<20;
SELECT Sname,Sage
或 FROM Student
WHERE NOT Sage>=20;
(2) 确定范围
例 10,查询年龄在 20~23岁之间 (包括 20岁和 23岁 )的学生的姓名、
系别和年龄。
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23;
例 11,查询年龄不在 20~23岁之间的学生的姓名、系别和年龄。
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage NOT BETWEEN 20 AND 23;
(3) 确定集合
谓词 IN可以用来查找属性值属于指定集合的元组,
例 12,查询信息系 (IS)、数学系 (MA)、计算机科学系 (CS)学生
的姓名和性别,
SELECT Sname,Ssex
FROM Student
WHERE Sdept IN(?IS?,?MA?,?CS?);
谓词 IN实际上是一系列谓词‘ OR?的缩写,等价的 WHERE子句为,
WHERE Sdept=?IS? OR Sdept=?MA? OR Sdept=?CS?;
例 13,查询既不是信息系 (IS)、数学系 (MA),也不是计算机科
学系 (CS)的学生的姓名和性别,
SELECT Sname,Ssex
FROM Student
WHERE Sdept NOT IN(?IS?,?MA?,?CS?);
(4) 字符匹配
LIKE 谓词的一般形式为,
<列名 >[NOT] LIKE ?<匹配串 >?[ESCAPE?<换码字符 >?]
字符串中通配符的含义,
⑴, 字符? _”,表示任意的单个字符 ;
⑵, 字符? %”,表示任意字符串 (长度可以为零 );
例 14,查询学号为 95001的学生的详细情况 。
SELECT *
FROM Student
WHERE Sno LIKE ?95001?;
等价的 WHERE子句为,
WHERE Sno = ?95001?;
例 15,查询所有姓刘的学生的姓名、学号和性别。
SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname LIKE ?刘 %?;
例 16,查询姓, 欧阳, 且全名为三个汉字的学生的姓名。
SELECT Sname
FROM Student
WHERE Sname LIKE ?欧阳 _ _ ?;
例 19,查询 DB_Design课程的课程号和学分。
SELECT Cno,Ccredit
FROM Course
WHERE Cname LIKE ?DB\_Design ?ESCAPE?\?;
(5),涉及空值的查询,
例 21 查询缺少学习成绩的学生的学号和课程号。
SELECT Sno,Cno
FROM SC
WHERE Grade IS NULL;
例 22 查询所有有成绩的学生的学号和课程号。
SELECT Sno,Cno
FROM SC
WHERE Grade IS NOT NULL;
(6),多重条件查询
用逻辑运算符 AND和 OR来联结多个查询条件,
例 23 查询计算机科学系年龄在 20岁以下的学生的姓名及年龄,
SELECT Sname,Sage
FROM Student
WHERE Sdept=?CS? AND Sage<20;
三,对查询结果排序
可以用 ORDER BY 字句对查询结果按照一个或多个属性列的升
序 (ASC)或降序 (DESC)排列,缺省值为升序,
例 24 查询选修了 3号课程的学生的学号和成绩,查询结果按分数
的降序排序。
SELECT Sno,Grade
FROM SC
WHERE Cno=?3?
ORDER BY Sdept,Sage DESC;
例 25 查询全体学生的详细信息,查询结果按所在系的系号升序
排列,同一系中的学生按年龄降序排序。
SELECT *
FROM Student;
ORDER BY Sdept,Sage DESC;
四,使用集函数 (库函数 )
SQL提供了 6种库函数,其功能如下,
COUNT ([DISTINCT | ALL]*) 统计元组个数
COUNT ([DISTINCT | ALL]<列名 >) 统计一列中值的个数
SUM ([DISTINCT | ALL]<列名 >) 计算某一列值的总和
AVG ([DISTINCT | ALL]<列名 >) 计算某一列值的平均值
MAX ([DISTINCT | ALL]<列名 >) 求某一列值中的最大值
M I N ([DISTINCT | ALL]<列名 >) 求某一列值中的最小值
例 26,查询学生总人数,
SELECT COUNT(*)
FROM Student;
例 27 查询选修了课程的学生人数,
SELECT COUNT(DISTINCT Sno)
FROM SC;
例 28 计算 1号课程的学生的平均成绩,
SELECT AVG(Grade)
FROM SC
WHERE Cno=?1?;
五、对查询结果分组
GROUP BY子句将查询结果表按某列或多列值分组，值相
等的为一组。
例 30 求各课程号及选修该课程的学生人数,
SELECT Cno,COUNT(Sno)
FROM SC
GROUP BY Cno;
可以使用 HAVING 短语指定筛选条件 。
例 31 查询选修了 3门以上课程的学生学号,
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*)>3 ;
3.3.2 连接查询
定义, 若查询涉及两个以上的表,则称之为连接查询,
一、等值与非等值连接查询
例 32,查询每个 学生以及其选修课程的情况。
SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno =SC.Sno;例 33,用自然连接完成例 32的查询,
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno =SC.Sno;
二、自身连接
例 34 查询每一门课程的间接先行课 (即先行课的先行课 ).
此例中的 FIRST和 SECOND为引入的别名 ;
SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST,Course SECOND
WHERE FIRST.Cpno =SECOND.Cno ;
三、外连接
如果我们想以 Student表为主体列出每个学生的基本情况及
其选课情况，若某个学生没有选课，只输出其基本情况信息，
其选课信息为空值即可，这时就需要外连接。
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno =SC.Sno(*);
四、复合条件连接
例 35 查询选修 2号课程且成绩在 90分以上的学生,
SELECT Student.Sno,Sname
FROM Student,SC
WHERE Student.Sno =SC.Sno
AND SC.Cn=?2? AND SC.Grade>90;
例如：
SELECT Sname
FROM Student
WHERE Sno IN
SELECT Sno
FROM SC
WHERE Cno=?2?；
3.3.3 嵌 套 查 询
嵌套查询亦称为子查询,嵌套查询是指一个 SELECT-FROM-WHERE
查询块嵌入另一个查询块之中,SQL中允许多层嵌套,
一、带有 IN谓词的查询
例 37 查询与?刘晨?在同
一个系学习的学生。
① 确定?刘晨?所在的系名。
SELECT Sdept
FROM Student
WHERE Sname=?刘晨’；
② 查询所有在 IS系学习的学生,
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept=?IS?；
将第 1步查询嵌入到第 2步查询
的条件中,构造嵌套查询,SQL语
句如下,
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
( SELECT Sdept
FROM Student
WHERE Sname=?刘
晨’ )；
SELECT Sno,Sname
FROM Student
WHERE Sno IN
(SELECT Sno
FROM SC
WHERE Cno IN
(SELECT Cno
FROM Course
WHERE Cname=?信息系统’ ));
例 38,查询选修了课程名为‘信息系统’的学生的学号和姓名,
上述查询可用连接查询实现,
SELECT Sno,Sname
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno
AND SC.Cno=Course.Cno
AND Course.Cname=?信息系统’
二, 带有比较运算符的子查询
当确切知道内层查询返回的是单值时,可以用比较运算符,
用比较运算符‘ =?代替 IN改写例 37
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept =
( SELECT Sdept
FROM Student
WHERE Sname=?刘晨’ )；
三, 带有 ANY或 ALL谓词的子查询
子查询返回的是单值时,可以用比较运算符,而使用 ANY或 ALL
谓词时则必须同时使用比较运算符。
例 39 查询其他系中比信息系某一学生年龄小的学生的姓名和年龄。
SELECT Sname,Sage
FROM Student
WHERE Sage<ANY ( SELECT Sage
FROM Student
WHERE Sdept=?IS?)
AND Sdept<>?IS?；
四, 代有 EXISTS的嵌套查询,
例 41 查询所有选修了 1号课程的学生姓名,
SELECT Sname
FROM Student
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno =Student.Sno AND Cno =?1?);
例 42 查询没有选修 1号课程的学生姓名,
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno =Student.Sno AND Cno =?1?);
例 43,查询选修了全部课程的学生姓名,
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM Course
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno =Student.Sno AND Cno =Course.Cno));
例 45 查询计算机科学系的学生以及年不大于 19岁的学生,
SELECT *
FROM Student
WHERE Sdept=?CS?
UNION
SELECT *
FROM Student
WHERE Sage<=19;
3.3.4 集合查询
例 46 查询选修了 1号课程或选修了 2号课程的学生,
SELECT Sno
FROM SC
WHERE Cno=?1?
UNION
SELECT Sno
FROM SC
WHERE Cno=?2?;
例 48 查询选修了 1号课程的学生集合与选修了 2号课程的学生集合的交集,.
SELECT *
FROM Student
WHERE Cno=?1? AND Sno IN
(SELECT Sno
FROM SC
WHERE Cno=?2?);
3.4.1 插入数据
一、插入单个元组
语句的一般格式,
INSERT
INTO 表名 [(<属性列 1>[,<属性列 2>]…)]
VALUES (<常量 1>[,<常量 2>]…);
例 1 将一个新学生,95020(学号 ),陈东 (姓名 ),男 (性别 ),IS(信息
系 ),18(年龄 ),插入到 Student表中,
INSERT
INTO Student
VALUES (?95020?,?陈东’,?男’,?IS?,18);
3.4 数 据 更 新
SQL更新语句包括修改、删除和插入三类语句。
例 2,插入一个选课记录 (‘95020’,’1’)
INSERT
INTO SC(Sno,Cno)
VALUES (‘95020’,’1’);
二, 插入子查询结果
语句的一般格式,
INSERT
INTO 表名 [(<属性列 1>[,<属性列 2>]…)]
子查询 ;
CREATE TABEL Deptage
(Sdept CHAR(15),Avgage SMALLINT);
INSERT
INTO Deptage (Sdept,Avgage)
SELECT Sdept,AVG(SA)
FROM Student
GROUP BY Sdept;
例 3, 对每个系,求学生的平均年龄,并把结果插入数据库中,
一、修改某一个元组的值
例 4,把学生 95001的姓名改为‘王平’,
UPDATE Student
SET Sname=?王平’
WHERE Sno=?95001?;
3.4.2 修改数据
语句的一般格式,
UPDATE <表名 >
SET <列名 >=<表达式 >[,<列名 >=<表达式>]…
[WHERE <条件 >];
二、修改多个元组的值
例 5,将所有学生的年龄增加 1岁,
UPDATE Student
SET Sage=Sage+1;
三、带子查询的修改语句
例 6,将计算机科学系全体学生的
成绩臵零,
UPDATE SC
SET Grade=0
WHERE ?CS?=
(SELECT Sdept
FROM Student
WHERE Student.Sno=SC.Sno);
3.4.3 删除数据
删除语句的一般格式,
DELETE
FROM <表名 >
[WHERE <条件 >];
一, 删除某一个元组的值
例 7,删除学号为 95019的学生记录,
DELETE
FROM Student
WHERE Sno =?95019?;
执行删除操作也可能产生破坏完整性的情况,
二、删除多个元组的值
例 8 删除所有的学生选课记录,
DELETE
FROM SC;
三、带有子查询的删除语句
例 9 删除计算机科学系全体学生的选课记录,
DELETE
FROM SC
WHERE ?CS? =
(SELECT Sdept
FROM Student
WHERE Student.Sno =SC.Sno);
四、更新操作与数据库的一致性
增删改操作一次只能对一个表操作，例如，删除学号为 95019
的学生记录后，相应该生的选课记录也应同时删除，而这只能通过
两条删除语句进行。
DELETE
FROM Student
WHERE Sno =?95019?;
DELETE
FROM SC
WHERE Sno =?95019?；
3.5 视 图
视图 (View)是从一个或几个基本表 (或视图 )导出的表,
3.5.1 视图的定义
一、建立视图
SQL建立视图的语句格式,
CREATE VIEW <视图名 >[(<列名 >[,<列名 >]…)]
AS 子查询
[WITH CHECK OPTION];
例 1,建立信息系的学生视图,
CREATE VIEW IS_Student
AS SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept=?IS?;
例 2,建立信息系的学生视图,并要求进行修改和插入
操作时仍需保证该视图只有信息系的学生,
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept=?IS?
WITH CHECK OPTION;
例 3,建立信息系选修了 1号课程的学生视图,
CREATE VIEW IS_S1(Sno,Sname,Grade)
AS
SELECT Student.Sno,Sname,Grade
FROM Student,SC
WHERE Sdept=?IS? AND
Student.Sno=SC.Sno AND SC.Cno=?1?;
例 4 建立信息系选修了 1号课程且成绩在 90分及以上的
学生视图,
CREATE VIEW IS_S2
AS
SELECT Sno,Sname,Grade
FROM IS_S1
WHERE Grade>=90;
例 5,定义一个反映学生出生年份的视图,
CREATE VIEW BT_S(Sno,Sname,Sbirth)
AS
SELECT Sno,Sname,2003-Sage
FROM Student;
例 6,把学生的学号及它的平均成绩定义为一个视图,
CREATE VIEW S_G(Sno,Gavg)
AS SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno ;
二,.删除视图
语句格式为：
DROP VIEW <视图名 >;
例 8 删除视图 IS_S1
DROP VIEW IS_S1;
3.5.2 查询视图
视图定义后,用户可以如同基本表那样对视图查询,
例 1 在信息系学生的视图中找出年龄小于 20岁的学生,
SELECT Sno,Sage
FROM IS_Student
WHERE Sage<20;
SELECT Sno,Sage
FROM Student
WHERE Sdept=?IS? AND Sage<20;
系统执行此查询时首先把它转换成等价的对基本表的查询,然后
执行修改了的查询,本例的修正后的查询语句为,
例 2 查询信息系选修了 1号课程的学生,
SELECT Sno,Sname
FROM IS_Student,SC
WHERE IS_Student.Sno=SC.Sno AND SC.Cno=?1?;
例 3 在 S_G视图中查询平均成绩在 90分以上的学生的学号和
平均成绩,
相应的语句为,
SELECT *
FROM S_G
WHERE Gave>=90;
S_G视图定义为,
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno ;
将上面查询语句与子查询
结合后,形成下列查询语句,
SELECT Sno,AVG(Grade)
FROM SC
WHERE AVG(Grade)>=90
GROUP BY Sno ;
正确的查询语句为,
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90;
3.5.3 视图的更新
对视图的更新最终要转换成对基本表的更新,
例 1 将信息系学生视图 IS_Student中学号为 95002的学生姓名
改为 ? 刘辰 ? 。
UPDATE IS_Student
SET Sname=?刘辰’
WHERE Sno =?95002?;
将转换成对基本表 Student的更新,
UPDATE Student
SET Sname=?刘辰’
WHERE Sno =?95002? AND Sdept=?IS?;
例 2 向信息系学生视图 IS_Student中插入一个新的学生记录，
其中学号为 95029，姓名为赵新，年龄为 20岁。
INSERT
INTO IS_Student
VALUES (?95029?,?赵新 ’,20);
转换成对基本表的插入
INSERT
INTO Student(Sno,Sname,Sage,Sdept)
VALUES (?95029?,?赵新 ’,20,?IS?);
例 3 删除信息系学生视图 IS_Student中学号为 95029的记录。
DELETE
FROM IS_Student
WHERE Sno=?95029?;
视图分类,
(1),行列子集视图 (可更新的视图 ).
(2),理论上可更新的视图,
(3),不可更新的视图
在 BD2中,只有从单个基本表导出的视图才允许对它
进行更新操作,并且具有以下限制,
P136-137 共 7条,
不可更新的视图 与 不允许更新的视图 的概念
在关系数据库中,并非所有的视图都是可更新的,如,
UPDATE S_G
SET Gavge=90
WHERE Sno =?95001?;
3.5.4 视图的作用
1,视图能够简化用户的操作,
关系数据库中，数据库的重构造是不可避免的。最常见的情况
是把一个表‘垂直’地分为两个以上的表。例如把学生关系
Student(Sno,Sname,Ssex,Sage,Sdept)
分为：
SX(Sno,Sname,Sage)
SY(Sno,Ssex,Sdept)
这时原表 Student是 SX,SY的自然连接的结果,因此可以建立一个
视图 Student:
CREATE VIEW Student(Sno,Sname,Ssex,Sage,Sdept)
AS SELECT SX.Sno,SX.Sname,SY.Ssex,SX.Sage,SY.Sdept
FROM SX,SY
WHERE SX.Sno =SY.Sno;
4.视图能对机密数据提供 自动的安全保护
2,视图使用户能以多种角度看待同一数据,
3.视图对重构数据库提供了一定程度的逻辑独立性,
3.6 数 据 控 制
授权语句的一般格式为,
GRANT <权限 >[,<权限 >]…
[ON <对象类型 ><对象名 >]
TO <用户 >[,<用户 >]…
[WITH GRANT OPTION];
对不同类型的操作对象可有不同的操作权力,见表 3.4
任选项 WITH GRANT OPTION的作用是使获得某种权力的
用户可以把权力再授予别的用户,
3.6.1 授权
例 1,把查询 Student表的权 XJ6授予用户 U1.
GRANT SELECT
ON TABLE Student
TO U1;
例 4,把修改学生学号和查询 Student表的权限授给用户 U4.
GRANT UPDATE(Sno),SELECT
ON TABLE Student TO U4;
例 2,把对表 Student和 Course的全部操作权限授予 U2和 U3。
GRANT ALL PRIVILIGES
ON TABLE Student,Course TO U2,U3;
例 3,把对表 SC的查询权限授予所有用户,
GRANT SELECT
ON TABLE SC TO PUBLIC;
例 6,DBA把在数据库 S_C中建立表的权限授予用户 U8.
GRANT CREATETAB ON DATABASE S_C TO U8;
例 5 把对表 SC的 INSERT权限授予 U5用户,并允许将此权限再授予
其他用户,
GRANT INSERT
ON TABLE SC TO U5 WITH GRANT OPTION;
3.6.2 回收权限
授予的权力可以用 REVOKE语句收回，格式为,
REVOKE <权限 >[,<权限 >]…
[ON <对象类型 > <对象名 >]
FROM <用户 >[,<用户 >]… ;
例 7,把用户 U4修改学生学号的权力收回,
REVOKE UPDATE(Sno) ON TABLE Studeny FROM U4;
例 8,收回所有用户对表 SC的查询权限,
REVOKE SELECT ON TABLE Student FROM PUBLIC;
例 9,把用户 U5对表 SC的 INSERT权限收回,
REVOKE INSERT ON TABLE SC FROM U5;
3.7 嵌入式 SQL
3.7.1 嵌入式 SQL的一般形式
在嵌入式 SQL中,为了能够区分 SQL语句与主语言语句,所有
SQL语句都必须加前缀 EXEC SQL。 SQL语句的结束标志则随主语
言的不同而不同,
例如,在 PL/1和 C中以分号? ;”结束,
EXEC SQL <SQL语句 >;
在 COBOL中以 END-EXEC结束,
EXEC SQL <SQL语句 > END-EXEC
例如一条交互式的 SQL语句,
DROP TABLE Student;
嵌入到 C程序中,应写作,
EXEC SQL DROP TABLE Student;
注意时间
3.7.2 嵌入式 SQL语句与主语言之间的通信
一,SQL通信区
SQL通信区 SQLCA
二、主变量
1,输入主变量
2,输出主变量
3,指示变量
所有主变量和指示变量必须在 SQL语句
BEGIN DECLARE SECYION 与 END DECLARE SECYION之
间进行说明,
三、游标
四、程序实例
……
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
CHAR Sno(5);
CHAR Cno(3);
INT Grade;
EXEC SQL END DECLARE SECTION;
main()
{ EXEC SQL DECLARE C1 CURSOR FOR
SELECT Sno,Cno,Grade
FROM SC;
EXEC SQL OPEN C1;
FOR( ; ; )
{ EXEC SQL FETCH C1 INTO:Sno,:Cno,:Grade;
if (sqlca.sqlcode<>SUCCESS)
break;
printf(“Sno:%s,Cno:%s,Grade:%d”,:Sno,:Cno,:Grade);
}
EXEC AQL CLOSE C1;
}
3.7.3 不用游标的 SQL语句
一, 说明性语句
EXEC SQL BEGIN DECLARE SECYION
EXEC SQL END DECLARE SECYION
二, 数据定义语句
例 1 建立一个?学生?表 Student.
EXEX SQL CREATE TABLE Student
(Sno CHAR(5) NOT NULL UNIQUE,
Sname CHAR(20),
Ssex CHAR(1),
Sage INT,
Sdept CHAR(15));
三, 数据控制语句
例 2 把查询 Student表的权限授予用户 U1.
EXEX SQL GRANT SELECT ON TABLE Student TO U1;
四, 查询结果为单记录的 SELECT语句
一般格式为,
EXEC SQL SELECT [ALL| DISTINCT] <目标列表达式 >[,<目标列表达式>]…
INTO<主变量 >[<指示变量 >][,<主变量 >[<指示变量 >]]…
FROM <表名或视图名 >[,<表名或视图名 >]…
[WHERE <条件表达式 >]
[GROUP BY <列名 1>[HAVING <条件表达式 >]]
[ORDER BY <列名 2>[ASC|DESC] ];使用该语句需要注意以下四点,
例 3,根据学生号码查询学生信息,假设已将要查询的学生的学
号赋给了主变量 Givensno.
EXEC SQL SELECT Sno,Sname,Ssex,Sage,Sdept
INTO,Hno,:Hname,:Hsex,:Hage,:Hdept
FROM Student
WHERE Sno =,Givensno;
五, 非 CURRENT 形式的 UPDATE 语句
例 5 将全体学生 1号课程的考试成绩增加若干分,假设增加的分
数已赋给了主变量 Raise.
EXEC SQL UPDATE SC
SET Grade=Grade+:Raise
WHERE Cno=?1?;
例 7,把计算机科学系全体学生的年龄臵 NULL值,
SAID=-1;
EXEC SQL UPDATE Student
SET Sage=:Raise:Sageid
WHERE Sdpt=?CS?;
它等价于,
EXEC SQL UPDATE Student
SET Sage=NULL
WHERE Sdpt=?CS?;
六,非 CURRENT形式的 DELETE语句
例 8,某个学生退学了,现要将有关他的所有选课记录删除掉,假
设该学生的姓名已赋给主变量 Stdname.
EXEC SQL DELETE
FROM SC
WHERE Sno=
(SELECT Sno
FROM Student
WHERE Sname=:Stdname );
另一种等价实现方法为,
EXEC SQL DELETE
FROM SC
WHERE, Stdname =
(SELECT Sname
FROM Student
WHERE Student.Sno=SC.Sno );
七, INSERT 语句
例 9,某个学生选修了某门课程,将有关记录插入 SC表中,假设学
生学号已赋给主变量 stdno,课程号已赋主变量 couno.
gradeid=-1;
EXEC SQL INSERT
INTO SC(Sno,Cno,Grade)
VALUES(:stdno,:couno,:gr:gradeid);
3.7.4 使用游标的 SQL语句
必须使用游标 SQL语句有,
● 查询结果为多条记录的 SELECT 语句
● CURRENT 形式的 UPDATE语句
● CURRENT 形式的 DELETE语句
一, 查询结果为多条记录的 SELECT 语句
使用游标的步骤,
(1),说明游标
EXEC SQL DECLARE<游标名 >CURSOR FOR <SELECT语句 >;
(2),打开游标
EXEC SQL OPEN<游标名 >;
(3),推进游标指针并取当前记录
EXEC SQL FETCH<游标名 >
INTO<主变量 >[<指示变量 >[; <主变量 >[<指示变量 >]]… ;
(4),关闭游标
EXEC SQL CLOSE<游标名 >;
例 1,查询某个系全体学生的信息,要查的系名由用户在程序运行
过程中指定,存放在主变量 deptname中,
……
EXEC SQL BEGIN DECLARE SECTION;
….,/*说明主变量 deptname,Hsno,Hsname,Shsex,Hsage等 */
EXEC SQL END DECLARE SECTION
……
gets(deptname);
……
EXCE SQL DECLARE SX CURSOR FOR
SELECT Sno,Sname,Ssex,Sage
FROM Student
WHERE SDept=,deptname;
EXEC SQL OPEN SX;
WHILE(1)
{ EXEC SQL FETCH SX INTO:HSno,:HSname,:Hssex,:HSage;
if(sqlca.sqlcode<>SUCCES)
break;
……
};
EXEC SQL CLOSE SX;
……,
例 2,查询某些系全体学生的选课信息,
……
EXEC SQL BEGIN DECLARE SECTION;
….,/*说明主变量 deptname,Hsno,Hsname,Shsex,Hsage等 */
EXEC SQL END DECLARE SECTION
……
EXCE SQL DECLARE SX CURSOR FOR
SELECT Sno,Sname,Ssex,Sage
FROM Student
WHERE SDept=,deptname;
WHILE(gets(deptname)!=NULL)
{EXEC SQL OPEN SX;
WHILE(1)
{ EXEC SQL FETCH SX INTO:HSno,:HSname,:Hssex,:HSage;
if(sqlca.sqlcode<>SUCCES)
break;
……
};
EXEC SQL CLOSE SX;
};
……,
二, CURRENT 形式的 UPDATE 语句和 DELETE语句
具体步骤,
(1),用 DECLARE语句说明游标。
(2),用 OPEN 语句打开游标。
(3),用 FETCH 语句推进游标指针,并将当前记录从缓冲去中取出
来送至主变量。
(4),检查该记录是否是要修改或删除的记录,
(5),处理完毕用 CLOSE语句关闭游标,
例 3,查询某个系全体学生的信息,(要查的系名由主变量 deptname指定 ),然后根据用户的要求修改其中某些记录的年龄字段,
……
EXEC SQL BEGIN DECLARE SECTION;
….,/*说明主变量 deptname,Hsno,Hsname,Shsex,Hsage等 */
EXEC SQL END DECLARE SECTION……
gets(deptname);
……
EXCE SQL DECLARE SX CURSOR FOR
SELECT Sno,Sname,Ssex,SageFROM Student
WHERE SDept=,deptname
FOR UPDATE OF Sage;
EXEC SQL OPEN SX;
WHILE(1)
{ EXEC SQL FETCH SX INTO:HSno,:HSname,:Hssex,:HSage;
if(sqlca.sqlcode<>SUCCES)
break;
printf(“%s,%s,%s,%d”,Sno,Sname,Ssex,Sage);
printf(“UPDATE AGE?,);
scanf(“%c”,&yn);
if(yn=?y? or yn=?Y?)
{printf(“INPUT NEW AGE:”);
scanf(“%d”,&NEWAge);
EXEC SQL UPDATE Student
SET Sage=:NEWAge
WHERE CURRENT OF SX;
};
……
};
EXEC SQL CLOSE SX;
……,
3.7.5 动态 SQL简介
如果在预编译时下列信息不能确定，就必须使用动态 SQL技术，
如,
(1) SQL语句正文
(2) 主变量个数
(3) 主变量的数据类型
(4) SQL语句中引用的数据库对象
动态 SQL方法允许在程序运行过程中临时?组装? SQL语句,主
要有三种形式,
1,语句可变
2,条件可变
3,数据库对象、查询条件均可变
第三章结束
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更好,
3.1 SQL 概 述
SQL(Structured Query Language)的英文名称是结构查询语
言。实际上它的功能包括查询 (Query),操纵 (Manipulation),定义
(Definition)和控制 (Control)四个方面,是一个综合的、通用的、功能
极强的关系数据库语言。
3.1.1 SQL的主要特点
1,综合统一
2,高度非过程化
3,面向集合的操作方式
4,两种使用方式,统一的语法结构
5,语言简洁,易 学 易用
(1).数据库查询 SELECT
(2),数据定义 CREATE,DROP
(3),数据操纵 INSERT,UPDATE,DELETE
(5),数据控制 GRANT,REVOKE
3.1.2 SQL语言的基本概念
图 3.1 SQL对关系数据库模式的支持
SQL
View V1 View V2
Base table
B1
Base table
B2
Base table
B3
Base table
B4
Stored file
S1
Stored file
S2
外模式
模式
内模式
3.2 SQL 数据定义
SQL的数据定义功能包括三部分,定义基本表,定义视图和定义
索引,他们是,
CREATE TABLE CREATE VIEW CREATE INDEX
DROP TABLE DROP VIEW DROP INDEX
ALTER TABLE
3.2.1 定义、删除和修改基本表
一, 定义基本表的语句
1,格式,
CREATE TABLE <表名 >(<列名 1> <类型 >[列级完整性约束条件 ]
[,<列名 2><类型 >[列级完整性约束条件 ]]…
[<表级完整性约束条件 >]; )
2,举 例,
例 1,P88
CREATE TABLE Student
(Sno CHAR(5) NOT NULL UNIQUE,
Sname CHAR(20) UNIQUE,
Ssex CHAR(1),
Sage INT
Sdept CHAR(15));
二, 修改基本表
1,格式,
ALTER TABLE <表名 >
[ADD <新列名 ><数据类型 >[完整性约束 ]];
[DROP<完整性约束名 >]
[MODIFY<列名 ><数据类型 >];
2.举例
例 2:
ALTER TABLE Student ADD Scome date;
三, 删除基本表
1,格式,
DROP TABLE <表名 >;
2.举例
例 5,删除 Student表
ALTER TABLE Student ;
例 3:
ALTER TABLE Student MODIFY Sage SMALLINT;
例 4:
ALTER TABLE Student DROP UNIQUE(Sname);
5.2.2 建立与删除索引
通常索引的建立和删除由 DBA(Dada Base Administrator)或表
的主人负责,
一,建立索引
1.格式
CREATE [UNIQUE] [CLUSTER] INDEX<索引名 >
ON <表名 >(<列名 >[<次序 >][,<列名 >[<次序,]]…) ;
2.举例,
例 6:
CREATE UNIQUE INDEX Stusno ON Student(Sno);
CREATE UNIQUE INDEX Coucno ON Couse(Cno);
CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);
二,删除索引
1.格式
DROP INDEX <索引名 >;
2.举例
例 7:删除 Student表的 Stusname索引,
DROP INDEX Stusname
小 结
在关系数据库中允许 DBA随时使用 SQL数据定义语句来定义基
本表、索引和视图，系统十分方便灵活。
在非关系系统中，数据库的模式一经建立再要加入新的记录型
或修改老的记录型是十分费事的工作。
而象 DB2这样的 SQL关系数据库系统，增删改基本表、索引和
视图都不必停止系统的其它处理，不会影响其它用户对数据库的正
常使用。
3.3 查 询
数据库查询是数据库的核心操作。 SQL语句提供了
SELECT语句进行数据库的查询。
查询语句的一般格式
SELECT [ALL| DISTINCT] <目标列表达式 >[,<目标列表达式
>]…
FROM <表名或视图名 >[,<表名或视图名 >]…
[WHERE <条件表达式 >]
[GROUP BY <列名 1>[HAVING <条件表达式 >]]
[ORDER BY <列名 2>[ASC|DESC] ];
3.3.1 单表查询
一、选择表中的若干列
1.查询指定列
例 1,查询全体学生的学号与姓名。
SELECT Sno,Sname
FROM Student;
例 2,查询全体学生的姓名、学号、所在系。
SELECT Sname,Sno,Sdept
FROM Student；
2.查询全部列
例 3,查询全体学生的详细信息。
SELECT *
FROM Student;
等价于,
SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student;
3,查询经过计算的值。
例 4.查询全体学生学号和出生年份,
SELECT Sno,2003-Sage
FROM Student;
二、选择表中的若干元组
1.消除取值重复的行
例 6,查询选修了课程的学生的学号,
SELECT Sno
FROM SC;
指定 DISTINCT短语,可去掉结果表中的重复行,
SELECT DISTINCT Sno
FROM SC;
2,查询满足条件的元组
查询满足指定条件的元组可以通过 WHERE子句实现,
(1) 比较大小
例 7,查询计算机系全体学生的名单,
SELECT Sname
FROM Student
WHERE Sdept=?CS?;
例 8,查询所有年龄在 20岁以下的学生的姓名及年龄,
SELECT Sname,Sage
FROM Student
WHERE Sage<20;
SELECT Sname,Sage
或 FROM Student
WHERE NOT Sage>=20;
(2) 确定范围
例 10,查询年龄在 20~23岁之间 (包括 20岁和 23岁 )的学生的姓名、
系别和年龄。
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23;
例 11,查询年龄不在 20~23岁之间的学生的姓名、系别和年龄。
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage NOT BETWEEN 20 AND 23;
(3) 确定集合
谓词 IN可以用来查找属性值属于指定集合的元组,
例 12,查询信息系 (IS)、数学系 (MA)、计算机科学系 (CS)学生
的姓名和性别,
SELECT Sname,Ssex
FROM Student
WHERE Sdept IN(?IS?,?MA?,?CS?);
谓词 IN实际上是一系列谓词‘ OR?的缩写,等价的 WHERE子句为,
WHERE Sdept=?IS? OR Sdept=?MA? OR Sdept=?CS?;
例 13,查询既不是信息系 (IS)、数学系 (MA),也不是计算机科
学系 (CS)的学生的姓名和性别,
SELECT Sname,Ssex
FROM Student
WHERE Sdept NOT IN(?IS?,?MA?,?CS?);
(4) 字符匹配
LIKE 谓词的一般形式为,
<列名 >[NOT] LIKE ?<匹配串 >?[ESCAPE?<换码字符 >?]
字符串中通配符的含义,
⑴, 字符? _”,表示任意的单个字符 ;
⑵, 字符? %”,表示任意字符串 (长度可以为零 );
例 14,查询学号为 95001的学生的详细情况 。
SELECT *
FROM Student
WHERE Sno LIKE ?95001?;
等价的 WHERE子句为,
WHERE Sno = ?95001?;
例 15,查询所有姓刘的学生的姓名、学号和性别。
SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname LIKE ?刘 %?;
例 16,查询姓, 欧阳, 且全名为三个汉字的学生的姓名。
SELECT Sname
FROM Student
WHERE Sname LIKE ?欧阳 _ _ ?;
例 19,查询 DB_Design课程的课程号和学分。
SELECT Cno,Ccredit
FROM Course
WHERE Cname LIKE ?DB\_Design ?ESCAPE?\?;
(5),涉及空值的查询,
例 21 查询缺少学习成绩的学生的学号和课程号。
SELECT Sno,Cno
FROM SC
WHERE Grade IS NULL;
例 22 查询所有有成绩的学生的学号和课程号。
SELECT Sno,Cno
FROM SC
WHERE Grade IS NOT NULL;
(6),多重条件查询
用逻辑运算符 AND和 OR来联结多个查询条件,
例 23 查询计算机科学系年龄在 20岁以下的学生的姓名及年龄,
SELECT Sname,Sage
FROM Student
WHERE Sdept=?CS? AND Sage<20;
三,对查询结果排序
可以用 ORDER BY 字句对查询结果按照一个或多个属性列的升
序 (ASC)或降序 (DESC)排列,缺省值为升序,
例 24 查询选修了 3号课程的学生的学号和成绩,查询结果按分数
的降序排序。
SELECT Sno,Grade
FROM SC
WHERE Cno=?3?
ORDER BY Sdept,Sage DESC;
例 25 查询全体学生的详细信息,查询结果按所在系的系号升序
排列,同一系中的学生按年龄降序排序。
SELECT *
FROM Student;
ORDER BY Sdept,Sage DESC;
四,使用集函数 (库函数 )
SQL提供了 6种库函数,其功能如下,
COUNT ([DISTINCT | ALL]*) 统计元组个数
COUNT ([DISTINCT | ALL]<列名 >) 统计一列中值的个数
SUM ([DISTINCT | ALL]<列名 >) 计算某一列值的总和
AVG ([DISTINCT | ALL]<列名 >) 计算某一列值的平均值
MAX ([DISTINCT | ALL]<列名 >) 求某一列值中的最大值
M I N ([DISTINCT | ALL]<列名 >) 求某一列值中的最小值
例 26,查询学生总人数,
SELECT COUNT(*)
FROM Student;
例 27 查询选修了课程的学生人数,
SELECT COUNT(DISTINCT Sno)
FROM SC;
例 28 计算 1号课程的学生的平均成绩,
SELECT AVG(Grade)
FROM SC
WHERE Cno=?1?;
五、对查询结果分组
GROUP BY子句将查询结果表按某列或多列值分组，值相
等的为一组。
例 30 求各课程号及选修该课程的学生人数,
SELECT Cno,COUNT(Sno)
FROM SC
GROUP BY Cno;
可以使用 HAVING 短语指定筛选条件 。
例 31 查询选修了 3门以上课程的学生学号,
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*)>3 ;
3.3.2 连接查询
定义, 若查询涉及两个以上的表,则称之为连接查询,
一、等值与非等值连接查询
例 32,查询每个 学生以及其选修课程的情况。
SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno =SC.Sno;例 33,用自然连接完成例 32的查询,
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno =SC.Sno;
二、自身连接
例 34 查询每一门课程的间接先行课 (即先行课的先行课 ).
此例中的 FIRST和 SECOND为引入的别名 ;
SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST,Course SECOND
WHERE FIRST.Cpno =SECOND.Cno ;
三、外连接
如果我们想以 Student表为主体列出每个学生的基本情况及
其选课情况，若某个学生没有选课，只输出其基本情况信息，
其选课信息为空值即可，这时就需要外连接。
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno =SC.Sno(*);
四、复合条件连接
例 35 查询选修 2号课程且成绩在 90分以上的学生,
SELECT Student.Sno,Sname
FROM Student,SC
WHERE Student.Sno =SC.Sno
AND SC.Cn=?2? AND SC.Grade>90;
例如：
SELECT Sname
FROM Student
WHERE Sno IN
SELECT Sno
FROM SC
WHERE Cno=?2?；
3.3.3 嵌 套 查 询
嵌套查询亦称为子查询,嵌套查询是指一个 SELECT-FROM-WHERE
查询块嵌入另一个查询块之中,SQL中允许多层嵌套,
一、带有 IN谓词的查询
例 37 查询与?刘晨?在同
一个系学习的学生。
① 确定?刘晨?所在的系名。
SELECT Sdept
FROM Student
WHERE Sname=?刘晨’；
② 查询所有在 IS系学习的学生,
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept=?IS?；
将第 1步查询嵌入到第 2步查询
的条件中,构造嵌套查询,SQL语
句如下,
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
( SELECT Sdept
FROM Student
WHERE Sname=?刘
晨’ )；
SELECT Sno,Sname
FROM Student
WHERE Sno IN
(SELECT Sno
FROM SC
WHERE Cno IN
(SELECT Cno
FROM Course
WHERE Cname=?信息系统’ ));
例 38,查询选修了课程名为‘信息系统’的学生的学号和姓名,
上述查询可用连接查询实现,
SELECT Sno,Sname
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno
AND SC.Cno=Course.Cno
AND Course.Cname=?信息系统’
二, 带有比较运算符的子查询
当确切知道内层查询返回的是单值时,可以用比较运算符,
用比较运算符‘ =?代替 IN改写例 37
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept =
( SELECT Sdept
FROM Student
WHERE Sname=?刘晨’ )；
三, 带有 ANY或 ALL谓词的子查询
子查询返回的是单值时,可以用比较运算符,而使用 ANY或 ALL
谓词时则必须同时使用比较运算符。
例 39 查询其他系中比信息系某一学生年龄小的学生的姓名和年龄。
SELECT Sname,Sage
FROM Student
WHERE Sage<ANY ( SELECT Sage
FROM Student
WHERE Sdept=?IS?)
AND Sdept<>?IS?；
四, 代有 EXISTS的嵌套查询,
例 41 查询所有选修了 1号课程的学生姓名,
SELECT Sname
FROM Student
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno =Student.Sno AND Cno =?1?);
例 42 查询没有选修 1号课程的学生姓名,
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno =Student.Sno AND Cno =?1?);
例 43,查询选修了全部课程的学生姓名,
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM Course
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno =Student.Sno AND Cno =Course.Cno));
例 45 查询计算机科学系的学生以及年不大于 19岁的学生,
SELECT *
FROM Student
WHERE Sdept=?CS?
UNION
SELECT *
FROM Student
WHERE Sage<=19;
3.3.4 集合查询
例 46 查询选修了 1号课程或选修了 2号课程的学生,
SELECT Sno
FROM SC
WHERE Cno=?1?
UNION
SELECT Sno
FROM SC
WHERE Cno=?2?;
例 48 查询选修了 1号课程的学生集合与选修了 2号课程的学生集合的交集,.
SELECT *
FROM Student
WHERE Cno=?1? AND Sno IN
(SELECT Sno
FROM SC
WHERE Cno=?2?);
3.4.1 插入数据
一、插入单个元组
语句的一般格式,
INSERT
INTO 表名 [(<属性列 1>[,<属性列 2>]…)]
VALUES (<常量 1>[,<常量 2>]…);
例 1 将一个新学生,95020(学号 ),陈东 (姓名 ),男 (性别 ),IS(信息
系 ),18(年龄 ),插入到 Student表中,
INSERT
INTO Student
VALUES (?95020?,?陈东’,?男’,?IS?,18);
3.4 数 据 更 新
SQL更新语句包括修改、删除和插入三类语句。
例 2,插入一个选课记录 (‘95020’,’1’)
INSERT
INTO SC(Sno,Cno)
VALUES (‘95020’,’1’);
二, 插入子查询结果
语句的一般格式,
INSERT
INTO 表名 [(<属性列 1>[,<属性列 2>]…)]
子查询 ;
CREATE TABEL Deptage
(Sdept CHAR(15),Avgage SMALLINT);
INSERT
INTO Deptage (Sdept,Avgage)
SELECT Sdept,AVG(SA)
FROM Student
GROUP BY Sdept;
例 3, 对每个系,求学生的平均年龄,并把结果插入数据库中,
一、修改某一个元组的值
例 4,把学生 95001的姓名改为‘王平’,
UPDATE Student
SET Sname=?王平’
WHERE Sno=?95001?;
3.4.2 修改数据
语句的一般格式,
UPDATE <表名 >
SET <列名 >=<表达式 >[,<列名 >=<表达式>]…
[WHERE <条件 >];
二、修改多个元组的值
例 5,将所有学生的年龄增加 1岁,
UPDATE Student
SET Sage=Sage+1;
三、带子查询的修改语句
例 6,将计算机科学系全体学生的
成绩臵零,
UPDATE SC
SET Grade=0
WHERE ?CS?=
(SELECT Sdept
FROM Student
WHERE Student.Sno=SC.Sno);
3.4.3 删除数据
删除语句的一般格式,
DELETE
FROM <表名 >
[WHERE <条件 >];
一, 删除某一个元组的值
例 7,删除学号为 95019的学生记录,
DELETE
FROM Student
WHERE Sno =?95019?;
执行删除操作也可能产生破坏完整性的情况,
二、删除多个元组的值
例 8 删除所有的学生选课记录,
DELETE
FROM SC;
三、带有子查询的删除语句
例 9 删除计算机科学系全体学生的选课记录,
DELETE
FROM SC
WHERE ?CS? =
(SELECT Sdept
FROM Student
WHERE Student.Sno =SC.Sno);
四、更新操作与数据库的一致性
增删改操作一次只能对一个表操作，例如，删除学号为 95019
的学生记录后，相应该生的选课记录也应同时删除，而这只能通过
两条删除语句进行。
DELETE
FROM Student
WHERE Sno =?95019?;
DELETE
FROM SC
WHERE Sno =?95019?；
3.5 视 图
视图 (View)是从一个或几个基本表 (或视图 )导出的表,
3.5.1 视图的定义
一、建立视图
SQL建立视图的语句格式,
CREATE VIEW <视图名 >[(<列名 >[,<列名 >]…)]
AS 子查询
[WITH CHECK OPTION];
例 1,建立信息系的学生视图,
CREATE VIEW IS_Student
AS SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept=?IS?;
例 2,建立信息系的学生视图,并要求进行修改和插入
操作时仍需保证该视图只有信息系的学生,
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept=?IS?
WITH CHECK OPTION;
例 3,建立信息系选修了 1号课程的学生视图,
CREATE VIEW IS_S1(Sno,Sname,Grade)
AS
SELECT Student.Sno,Sname,Grade
FROM Student,SC
WHERE Sdept=?IS? AND
Student.Sno=SC.Sno AND SC.Cno=?1?;
例 4 建立信息系选修了 1号课程且成绩在 90分及以上的
学生视图,
CREATE VIEW IS_S2
AS
SELECT Sno,Sname,Grade
FROM IS_S1
WHERE Grade>=90;
例 5,定义一个反映学生出生年份的视图,
CREATE VIEW BT_S(Sno,Sname,Sbirth)
AS
SELECT Sno,Sname,2003-Sage
FROM Student;
例 6,把学生的学号及它的平均成绩定义为一个视图,
CREATE VIEW S_G(Sno,Gavg)
AS SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno ;
二,.删除视图
语句格式为：
DROP VIEW <视图名 >;
例 8 删除视图 IS_S1
DROP VIEW IS_S1;
3.5.2 查询视图
视图定义后,用户可以如同基本表那样对视图查询,
例 1 在信息系学生的视图中找出年龄小于 20岁的学生,
SELECT Sno,Sage
FROM IS_Student
WHERE Sage<20;
SELECT Sno,Sage
FROM Student
WHERE Sdept=?IS? AND Sage<20;
系统执行此查询时首先把它转换成等价的对基本表的查询,然后
执行修改了的查询,本例的修正后的查询语句为,
例 2 查询信息系选修了 1号课程的学生,
SELECT Sno,Sname
FROM IS_Student,SC
WHERE IS_Student.Sno=SC.Sno AND SC.Cno=?1?;
例 3 在 S_G视图中查询平均成绩在 90分以上的学生的学号和
平均成绩,
相应的语句为,
SELECT *
FROM S_G
WHERE Gave>=90;
S_G视图定义为,
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno ;
将上面查询语句与子查询
结合后,形成下列查询语句,
SELECT Sno,AVG(Grade)
FROM SC
WHERE AVG(Grade)>=90
GROUP BY Sno ;
正确的查询语句为,
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90;
3.5.3 视图的更新
对视图的更新最终要转换成对基本表的更新,
例 1 将信息系学生视图 IS_Student中学号为 95002的学生姓名
改为 ? 刘辰 ? 。
UPDATE IS_Student
SET Sname=?刘辰’
WHERE Sno =?95002?;
将转换成对基本表 Student的更新,
UPDATE Student
SET Sname=?刘辰’
WHERE Sno =?95002? AND Sdept=?IS?;
例 2 向信息系学生视图 IS_Student中插入一个新的学生记录，
其中学号为 95029，姓名为赵新，年龄为 20岁。
INSERT
INTO IS_Student
VALUES (?95029?,?赵新 ’,20);
转换成对基本表的插入
INSERT
INTO Student(Sno,Sname,Sage,Sdept)
VALUES (?95029?,?赵新 ’,20,?IS?);
例 3 删除信息系学生视图 IS_Student中学号为 95029的记录。
DELETE
FROM IS_Student
WHERE Sno=?95029?;
视图分类,
(1),行列子集视图 (可更新的视图 ).
(2),理论上可更新的视图,
(3),不可更新的视图
在 BD2中,只有从单个基本表导出的视图才允许对它
进行更新操作,并且具有以下限制,
P136-137 共 7条,
不可更新的视图 与 不允许更新的视图 的概念
在关系数据库中,并非所有的视图都是可更新的,如,
UPDATE S_G
SET Gavge=90
WHERE Sno =?95001?;
3.5.4 视图的作用
1,视图能够简化用户的操作,
关系数据库中，数据库的重构造是不可避免的。最常见的情况
是把一个表‘垂直’地分为两个以上的表。例如把学生关系
Student(Sno,Sname,Ssex,Sage,Sdept)
分为：
SX(Sno,Sname,Sage)
SY(Sno,Ssex,Sdept)
这时原表 Student是 SX,SY的自然连接的结果,因此可以建立一个
视图 Student:
CREATE VIEW Student(Sno,Sname,Ssex,Sage,Sdept)
AS SELECT SX.Sno,SX.Sname,SY.Ssex,SX.Sage,SY.Sdept
FROM SX,SY
WHERE SX.Sno =SY.Sno;
4.视图能对机密数据提供 自动的安全保护
2,视图使用户能以多种角度看待同一数据,
3.视图对重构数据库提供了一定程度的逻辑独立性,
3.6 数 据 控 制
授权语句的一般格式为,
GRANT <权限 >[,<权限 >]…
[ON <对象类型 ><对象名 >]
TO <用户 >[,<用户 >]…
[WITH GRANT OPTION];
对不同类型的操作对象可有不同的操作权力,见表 3.4
任选项 WITH GRANT OPTION的作用是使获得某种权力的
用户可以把权力再授予别的用户,
3.6.1 授权
例 1,把查询 Student表的权 XJ6授予用户 U1.
GRANT SELECT
ON TABLE Student
TO U1;
例 4,把修改学生学号和查询 Student表的权限授给用户 U4.
GRANT UPDATE(Sno),SELECT
ON TABLE Student TO U4;
例 2,把对表 Student和 Course的全部操作权限授予 U2和 U3。
GRANT ALL PRIVILIGES
ON TABLE Student,Course TO U2,U3;
例 3,把对表 SC的查询权限授予所有用户,
GRANT SELECT
ON TABLE SC TO PUBLIC;
例 6,DBA把在数据库 S_C中建立表的权限授予用户 U8.
GRANT CREATETAB ON DATABASE S_C TO U8;
例 5 把对表 SC的 INSERT权限授予 U5用户,并允许将此权限再授予
其他用户,
GRANT INSERT
ON TABLE SC TO U5 WITH GRANT OPTION;
3.6.2 回收权限
授予的权力可以用 REVOKE语句收回，格式为,
REVOKE <权限 >[,<权限 >]…
[ON <对象类型 > <对象名 >]
FROM <用户 >[,<用户 >]… ;
例 7,把用户 U4修改学生学号的权力收回,
REVOKE UPDATE(Sno) ON TABLE Studeny FROM U4;
例 8,收回所有用户对表 SC的查询权限,
REVOKE SELECT ON TABLE Student FROM PUBLIC;
例 9,把用户 U5对表 SC的 INSERT权限收回,
REVOKE INSERT ON TABLE SC FROM U5;
3.7 嵌入式 SQL
3.7.1 嵌入式 SQL的一般形式
在嵌入式 SQL中,为了能够区分 SQL语句与主语言语句,所有
SQL语句都必须加前缀 EXEC SQL。 SQL语句的结束标志则随主语
言的不同而不同,
例如,在 PL/1和 C中以分号? ;”结束,
EXEC SQL <SQL语句 >;
在 COBOL中以 END-EXEC结束,
EXEC SQL <SQL语句 > END-EXEC
例如一条交互式的 SQL语句,
DROP TABLE Student;
嵌入到 C程序中,应写作,
EXEC SQL DROP TABLE Student;
注意时间
3.7.2 嵌入式 SQL语句与主语言之间的通信
一,SQL通信区
SQL通信区 SQLCA
二、主变量
1,输入主变量
2,输出主变量
3,指示变量
所有主变量和指示变量必须在 SQL语句
BEGIN DECLARE SECYION 与 END DECLARE SECYION之
间进行说明,
三、游标
四、程序实例
……
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
CHAR Sno(5);
CHAR Cno(3);
INT Grade;
EXEC SQL END DECLARE SECTION;
main()
{ EXEC SQL DECLARE C1 CURSOR FOR
SELECT Sno,Cno,Grade
FROM SC;
EXEC SQL OPEN C1;
FOR( ; ; )
{ EXEC SQL FETCH C1 INTO:Sno,:Cno,:Grade;
if (sqlca.sqlcode<>SUCCESS)
break;
printf(“Sno:%s,Cno:%s,Grade:%d”,:Sno,:Cno,:Grade);
}
EXEC AQL CLOSE C1;
}
3.7.3 不用游标的 SQL语句
一, 说明性语句
EXEC SQL BEGIN DECLARE SECYION
EXEC SQL END DECLARE SECYION
二, 数据定义语句
例 1 建立一个?学生?表 Student.
EXEX SQL CREATE TABLE Student
(Sno CHAR(5) NOT NULL UNIQUE,
Sname CHAR(20),
Ssex CHAR(1),
Sage INT,
Sdept CHAR(15));
三, 数据控制语句
例 2 把查询 Student表的权限授予用户 U1.
EXEX SQL GRANT SELECT ON TABLE Student TO U1;
四, 查询结果为单记录的 SELECT语句
一般格式为,
EXEC SQL SELECT [ALL| DISTINCT] <目标列表达式 >[,<目标列表达式>]…
INTO<主变量 >[<指示变量 >][,<主变量 >[<指示变量 >]]…
FROM <表名或视图名 >[,<表名或视图名 >]…
[WHERE <条件表达式 >]
[GROUP BY <列名 1>[HAVING <条件表达式 >]]
[ORDER BY <列名 2>[ASC|DESC] ];使用该语句需要注意以下四点,
例 3,根据学生号码查询学生信息,假设已将要查询的学生的学
号赋给了主变量 Givensno.
EXEC SQL SELECT Sno,Sname,Ssex,Sage,Sdept
INTO,Hno,:Hname,:Hsex,:Hage,:Hdept
FROM Student
WHERE Sno =,Givensno;
五, 非 CURRENT 形式的 UPDATE 语句
例 5 将全体学生 1号课程的考试成绩增加若干分,假设增加的分
数已赋给了主变量 Raise.
EXEC SQL UPDATE SC
SET Grade=Grade+:Raise
WHERE Cno=?1?;
例 7,把计算机科学系全体学生的年龄臵 NULL值,
SAID=-1;
EXEC SQL UPDATE Student
SET Sage=:Raise:Sageid
WHERE Sdpt=?CS?;
它等价于,
EXEC SQL UPDATE Student
SET Sage=NULL
WHERE Sdpt=?CS?;
六,非 CURRENT形式的 DELETE语句
例 8,某个学生退学了,现要将有关他的所有选课记录删除掉,假
设该学生的姓名已赋给主变量 Stdname.
EXEC SQL DELETE
FROM SC
WHERE Sno=
(SELECT Sno
FROM Student
WHERE Sname=:Stdname );
另一种等价实现方法为,
EXEC SQL DELETE
FROM SC
WHERE, Stdname =
(SELECT Sname
FROM Student
WHERE Student.Sno=SC.Sno );
七, INSERT 语句
例 9,某个学生选修了某门课程,将有关记录插入 SC表中,假设学
生学号已赋给主变量 stdno,课程号已赋主变量 couno.
gradeid=-1;
EXEC SQL INSERT
INTO SC(Sno,Cno,Grade)
VALUES(:stdno,:couno,:gr:gradeid);
3.7.4 使用游标的 SQL语句
必须使用游标 SQL语句有,
● 查询结果为多条记录的 SELECT 语句
● CURRENT 形式的 UPDATE语句
● CURRENT 形式的 DELETE语句
一, 查询结果为多条记录的 SELECT 语句
使用游标的步骤,
(1),说明游标
EXEC SQL DECLARE<游标名 >CURSOR FOR <SELECT语句 >;
(2),打开游标
EXEC SQL OPEN<游标名 >;
(3),推进游标指针并取当前记录
EXEC SQL FETCH<游标名 >
INTO<主变量 >[<指示变量 >[; <主变量 >[<指示变量 >]]… ;
(4),关闭游标
EXEC SQL CLOSE<游标名 >;
例 1,查询某个系全体学生的信息,要查的系名由用户在程序运行
过程中指定,存放在主变量 deptname中,
……
EXEC SQL BEGIN DECLARE SECTION;
….,/*说明主变量 deptname,Hsno,Hsname,Shsex,Hsage等 */
EXEC SQL END DECLARE SECTION
……
gets(deptname);
……
EXCE SQL DECLARE SX CURSOR FOR
SELECT Sno,Sname,Ssex,Sage
FROM Student
WHERE SDept=,deptname;
EXEC SQL OPEN SX;
WHILE(1)
{ EXEC SQL FETCH SX INTO:HSno,:HSname,:Hssex,:HSage;
if(sqlca.sqlcode<>SUCCES)
break;
……
};
EXEC SQL CLOSE SX;
……,
例 2,查询某些系全体学生的选课信息,
……
EXEC SQL BEGIN DECLARE SECTION;
….,/*说明主变量 deptname,Hsno,Hsname,Shsex,Hsage等 */
EXEC SQL END DECLARE SECTION
……
EXCE SQL DECLARE SX CURSOR FOR
SELECT Sno,Sname,Ssex,Sage
FROM Student
WHERE SDept=,deptname;
WHILE(gets(deptname)!=NULL)
{EXEC SQL OPEN SX;
WHILE(1)
{ EXEC SQL FETCH SX INTO:HSno,:HSname,:Hssex,:HSage;
if(sqlca.sqlcode<>SUCCES)
break;
……
};
EXEC SQL CLOSE SX;
};
……,
二, CURRENT 形式的 UPDATE 语句和 DELETE语句
具体步骤,
(1),用 DECLARE语句说明游标。
(2),用 OPEN 语句打开游标。
(3),用 FETCH 语句推进游标指针,并将当前记录从缓冲去中取出
来送至主变量。
(4),检查该记录是否是要修改或删除的记录,
(5),处理完毕用 CLOSE语句关闭游标,
例 3,查询某个系全体学生的信息,(要查的系名由主变量 deptname指定 ),然后根据用户的要求修改其中某些记录的年龄字段,
……
EXEC SQL BEGIN DECLARE SECTION;
….,/*说明主变量 deptname,Hsno,Hsname,Shsex,Hsage等 */
EXEC SQL END DECLARE SECTION……
gets(deptname);
……
EXCE SQL DECLARE SX CURSOR FOR
SELECT Sno,Sname,Ssex,SageFROM Student
WHERE SDept=,deptname
FOR UPDATE OF Sage;
EXEC SQL OPEN SX;
WHILE(1)
{ EXEC SQL FETCH SX INTO:HSno,:HSname,:Hssex,:HSage;
if(sqlca.sqlcode<>SUCCES)
break;
printf(“%s,%s,%s,%d”,Sno,Sname,Ssex,Sage);
printf(“UPDATE AGE?,);
scanf(“%c”,&yn);
if(yn=?y? or yn=?Y?)
{printf(“INPUT NEW AGE:”);
scanf(“%d”,&NEWAge);
EXEC SQL UPDATE Student
SET Sage=:NEWAge
WHERE CURRENT OF SX;
};
……
};
EXEC SQL CLOSE SX;
……,
3.7.5 动态 SQL简介
如果在预编译时下列信息不能确定，就必须使用动态 SQL技术，
如,
(1) SQL语句正文
(2) 主变量个数
(3) 主变量的数据类型
(4) SQL语句中引用的数据库对象
动态 SQL方法允许在程序运行过程中临时?组装? SQL语句,主
要有三种形式,
1,语句可变
2,条件可变
3,数据库对象、查询条件均可变
第三章结束
