《数据库系统教程（第3版）》第3章 关系数据库语言SQL

第3章关系数据库语言SQL

本章重要概念

（1）SQL数据库的体系结构，SQL的组成。（2）SQL的数据定义：SQL模式、基本表和索引的创建和撤销。（3）SQL的数据查询；SELECT语句的句法，SELECT语句的三种形式及各种限定，基本表的联接操作，SQL3中的递归查询。（4）SQL的数据更新：插入、删除和修改语句。（5）视图的创建和撤销，对视图更新操作的限制。（6）嵌入式SQL：预处理方式，使用规定，使用技术，卷游标，动态SQL语句。

本章概述SQL是关系数据库的标准语言，对关系模型的发展和商用DBMS的研制起着重要的作用。SQL语言是介乎于关系代数和元组演算之间的一种语言。本章详细介绍SQL的核心部分内容：数据定义、数据查询、数据更新和嵌入式SQL。

关系数据库语言SQL3.1SQL的体系结构

3.2SQL的数据定义

3.3SQL的数据查询3.4数据更新3.5视图的定义和对视图的操作3.6嵌入式SQL

3.1SQL的体系结构

3.1.1SQL的产生与发展

3.1.2SQL数据库的体系结构

3.1.3

SQL的组成

返回SQL的产生与发展

1970年，美国IBM研究中心的E.F.Codd连续发表多篇论文，提出关系模型。1972年，IBM公司开始研制实验型关系数据库管理系统SYSTEMR，配制的查询语言称为SQUARE(SpecifyingQueriesAsRelationalExpression)语言，在语言中使用了较多的数学符号。1974年，Boyce和Chamberlin把SQUARE修改为SEQUEL(StructuredEnglishQUEryLanguage)语言。后来SEQUEL简称为SQL(StructuredQueryLanguage)，即“结构式查询语言”，SQL的发音仍为“sequel”。现在SQL已经成为一个标准。SQL数据库的体系结构

用户1用户2用户3用户4视图1视图1基本表1基本表2基本表3基本表4存储文件1存储文件2存储文件3存储文件4图3.1SQL数据库的体系结构SQL用户ViewBasetableStoredfileSQL的组成

核心SQL主要有四个部分: (1)数据定义语言，即SQLDDL，用于定义SQL模式、基本表、视图、索引等结构。 (2)数据操纵语言，即SQLDML。数据操纵分成数据查询和数据更新两类。其中数据更新又分成插入、删除和修改三种操作。 (3)嵌入式SQL语言的使用规定。这一部分内容涉及到SQL语句嵌入在宿主语言程序中的规则。 (4)数据控制语言，即SQLDCL，这一部分包括对基本表和视图的授权、完整性规则的描述、事务控制等内容。返回3.2SQL的数据定义

3.2.1SQL模式的创建和撤销

3.2.2基本数据类型

3.2.3基本表的创建、修改和撤销

3.2.4索引的创建和撤销

返回SQL模式的创建和撤销

SQL模式的创建可用CREATESCHEMA语句定义，其基本句法如下：

CREATESCHEMA〈模式名〉AUTHORIZATION〈用户名〉DROP语句的句法如下：

DROPSCHEMA〈模式名〉［CASCADE│RESTRICT］其方式有两种：

CASCADE(级联式)方式。

RESTRICT(约束式)方式。

基本数据类型

SQL提供的主要数据类型(也称为“域类型”)有：（1）数值型（2）字符串型（3）位串型（4）时间型返回基本表的创建、修改和撤销(1)基本表的创建

例3.1

基本表SS（S#，SNAME，AGE，SEX）可用下列语句创建：

CREATETABLES (S# CHAR(4)NOTNULL， SNAMECHAR(8)NOTNULL， AGE CHAR(1)， SEX CHAR(1), PRIMARYKEY(S#))；

基本表的创建、修改和撤销(2)基本表结构的修改增加新的列用“ALTER…ADD…”语句，其句法如下：

ALTERTABLE<基本表名>

ADD<列名><类型>删除原有的列用“ALTER…DROP…”语句，句法如下：

ALTERTABLE<基本表名>DROP<列名>［CASCADE│RESTRICT］

此处CASCADE方式表示：在基本表中删除某列时，所有引用到该列的视图和约束也要一起自动地被删除。而RESTRICT方式表示在没有视图或约束引用该属性时，才能在基本表中删除该列，否则拒绝删除操作。

基本表的创建、修改和撤销(3)例3.2在基本表S中增加一个地址（ADDRESS）列，可用下列语句：

ALTERTABLESADDADDRESSVARCHAR（30）；

应注意，新增加的列不能定义为“NOTNULL”。基本表在增加一列后，原有元组在新增加的列上的值都被定义为空值（NULL）。例3.3在基本表S中删除年龄（AGE）列，并且把引用该列的所有视图和约束也一起删除，可用下列语句：

ALTERTABLESDROPAGECASCADE；例3.4在基本表S中S#的长度修改为6，可用下列语句：

ALTERTABLESMODIFYS#CHAR（6）；

基本表的创建、修改和撤销(4)基本表的撤销撤销语句的句法如下：

DROPTABLE<基本表名>［CASCADE│RESTRICT］

此处的CASCADE、RESTRICT的语义同前面句法中的语义一样。

返回索引的创建和撤销

索引的创建 创建索引可用“CREATEINDEX”语句实现。句法如下：

CREATE［UNIQUE］INDEX<索引名>ON<基本表名>（列名表）索引的撤销 当索引不需要时，可以用“DROPINDEX”语句撤销，其句法如下：

DROPINDEX<索引名>返回3.3SQL的数据查询

3.3.1

SELECT查询语句

3.3.2

SELECT语句完整的句法

3.3.3

SELECT语句中的限定

3.3.4

基本表的联接操作

3.3.5

SQL3中的递归查询

返回

SELECT查询语句(1)SELECT—FROM—WHERE句型 在关系代数中最常用的式子是下列表达式：

πA1,…,An(σF(R1×…×Rm))

这里R1、…、Rm为关系，F是公式，A1、…、An为属性。 针对上述表达式，SQL为此设计了SELECT—FROM—WHERE句型：

SELECTA1，…，An FROMR1，…，Rm

WHEREF

这个句型是从关系代数表达式演变来的，但WHERE子句中的条件表达式F要比关系代数中公式更灵活。SELECT查询语句(2)SELECT句型使用实例 例3.8教学数据中有三个基本表（关系）：

S（S#，SNAME，AGE，SEX） SC（S#，C#，GRADE） C（C#，CNAME，TEACHER）

下面用SELECT查询语句表达每个查询。 ①检索学习课程号为C2的学生学号与成绩。

②检索学习课程号为C2的学生学号与姓名。 ③检索选修课程名为MATHS的学生学号与姓名。④检索选修课程号为C2或C4的学生学号。⑤检索至少选修课程号为C2和C4的学生学号。SELECT查询语句(3)SELECT语句的图示形式

例3.9对于例3.8中的第二个查询语句的图示形式如下所述。SELECT查询语句(4)

聚合函数

COUNT（*）计算元组的个数COUNT（列名）对一列中的值计算个数SUM（列名）求某一列值的总和（此列的值必须是数值型）AVG（列名）求某一列值的平均值（此列的值必须是数值型）MAX（列名）求某一列值的最大值MIN（列名）求某一列值的最小值返回SELECT语句完整的句法

(1)SELECT语句完整的句法如下：

SELECT目标表的列名或列表达式序列

FROM基本表名和（或）视图序列[WHERE行条件表达式][GROUPBY列名序列[HAVING组条件表达式]][ORDERBY列名[ASC|DESC]，…]SELECT语句完整的句法(2)整个语句的执行过程如下：（1）读取FROM子句中基本表、视图的数据，执行笛卡尔积操作。（2）选取满足WHERE子句中给出的条件表达式的元组。（3）按GROUP子句中指定列的值分组，同时提取满足HAVING子句中组条件表达式的那些组。（4）按SELECT子句中给出的列名或列表达式求值输出。（5）ORDER子句对输出的目标表进行排序，按附加说明ASC升序排列，或按DESC降序排列。SELECT语句完整的句法(3)例3.11对教学数据库的基本表S、SC、C中数据进行查询和计算。①统计每一年龄选修课程的学生人数

SELECTAGE，COUNT（DISTINCTS.S#）FROMS，SCWHERES.S#=SC.S#GROUPBYAGE；

由于要统计每一个年龄的学生人数，因此要把满足WHERE子句中条件的查询结果按年龄分组，在每一组中的学生年龄相同。此时的SELECT子句应对每一组分开进行操作，在每一组中，年龄只有一个值，统计的人数是这一组中的学生人数。SELECT语句完整的句法(4)②求基本表S中男同学的每一年龄组（超过50人）有多少人？要求查询结果按人数升序排列，人数相同按年龄降序排列。

SELECTAGE，COUNT（S#） FROMS WHERESEX='M' GROUPBYAGE HAVINGCOUNT（*）>50 ORDERBY2，AGEDESC；返回SELECT语句中的限定(1)

SELECT子句中的规定

SELECT子句描述查询输出的表格结构，即输出值的列名或表达式。其形式如下：

SELECT[ALL|DISTINCT]<列名或列表达式序列>|*条件表达式中的算术比较操作条件表达式中可出现算术比较运算符（＜，＜＝，＞，＞＝，＝，!＝），也可以用“BETWEEN…AND…”比较运算符限定一个值的范围。列和基本表的改名操作SELECT语句中的限定(2)字符串的匹配操作 条件表达式中字符串匹配操作符是“LIKE”。在表达式中可使用两个通配符： 百分号（%）：与零个或多个字符组成的字符串匹配。 下划线（_）：与单个字符匹配。集合的并、交、差操作当两个子查询结果的结构完全一致时，可以让这两个子查询执行并、交、差操作。并、交、差的运算符为UNION、INTERSECT和EXCEPT。空值的比较操作

SQL中允许列值为空，空值用保留字NULL表示。

SELECT语句中的限定(3)集合的比较操作

SQL提供SELECT语句的嵌套子查询机制。子查询是嵌套在另一个查询中的SELECT语句。（1）集合成员资格的比较（2）集合成员的算术比较（3）空关系的测试（4）重复元组的测试导出表的使用

SQL2允许在FROM子句中使用子查询。如果在FROM子句中使用了子查询，那么要给子查询的结果起个表名和相应的列名。

返回基本表的联接操作

基本表的联接操作(例)SQL3中的递归查询

例3.23

C1（BASIC）C3（COBOL）C2（FORTRAN）C5（C）C4（PASCAL）C9（PL/I）C8（VC++）C6（Ada）C7（C++）图3.13课程之间的先修与后继的联系（箭尾指向的课程是箭头指向课程的直接先修课）返回3.4数据更新

3.4.1数据插入

3.4.2

数据删除

3.4.3数据修改

返回数据插入

（1）单元组的插入

INSERTINTO

基本表名[（列名表）]VALUES（元组值）（2）多元组的插入

INSERTINTO

基本表名[（列名表）]

VALUES（元组值），（元组值），……，（元组值）（3）查询结果的插入

INSERTINTO

基本表名[(列名表)]＜SELECT查询语句＞这个语句可把一个SELECT语句的查询结果插到某个基本表中。（4）表的插入

INSERTINTO

基本表名1[（列名表）]TABLE基本表名2这个语句可把基本表2的值插入到基本表1中。数据删除

SQL的删除操作是指从基本表中删除元组，其句法如下：

DELETEFROM基本表名

[WHERE条件表达式] 其语义是从基本表中删除满足条件表达式的元组。删除语句实际上是“SELECT*FROM基本表名[WHERE条件表达式]”和DELETE操作的结合，每找到一个元组，就把它删去。应该注意，DELETE语句只能从一个基本表中删除元组。WHERE子句中条件可以嵌套，也可以是来自几个基本表的复合条件。数据修改

当需要修改基本表中元组的某些列值时，可以用UPDATE语句实现，其句法如下：

UPDATE基本表名

SET列名=值表达式[，列名=值表达式…]│ROW=（元组）

[WHERE条件表达式]其语义是：修改基本表中满足条件表达式的那些元组中的列值，需修改的列值在SET子句中指出。返回3.5视图的定义和对视图的操作

3.5.1视图的创建和撤销

3.5.2

对视图的更新操作

3.5.3视图的优点

返回视图的创建和撤销(1)视图的创建创建视图可用“CREATEVIEW”语句实现。其句法如下：

CREATEVIEW<视图名>（列表名）

AS<SELECT查询语句>例3.27对于教学数据库中基本表S、SC、C，用户经常要用到S#、SNAME、CNAME和GRADE等列的数据，那么可用下列语句建立视图：

CREATEVIEWSTUDENT_GRADE(S#，SNAME，CNAME，GRADE)

ASSELECTS.S#，SNAME，CNAME，GRADE

FROMS，SC，C

WHERES.S#=SC.S#ANDSC.C#=C.C#；视图的创建和撤销(2)视图的撤销在视图不需要时，可以用“DROPVIEW”语句把其从系统中撤销。其句法如下：

DROPVIEW视图名例3.28撤销STUDENT_GRADE视图，可用下列语句实现：

DROPVIEWSTUDENT_GRADE；

对视图的更新操作(1)定义3.1如果视图是从单个基本表只使用选择、投影操作导出的，并且包含了基本表的主键，那么这样的视图称为“行列子集视图”，并且可以被执行更新操作。对视图的更新操作(2)例3.29下面讨论对视图更新的几个例子。①设有一个视图定义

CREATEVIEWSUDENT_GRADE ASSELECTS.S#,SNAME，CNAME，GRADE FROMS，SC，C WHERES.S#=SC.S#ANDSC.C#=C.C#；

这个视图定义了学生选修的课程名和成绩，是由三个基本表联接而成的。 如果用户要在视图中执行插入操作:

INSERTINTOSTUDENT_GRADE VALUES('S24','WANG','MATHS',80)；

若在基本表C中，课程名为MATHS的课程号有多个，则往基本表SC插入元组时，课程号C#就不定了。因此，对这个视图的更新是不允许的。

对视图的更新操作(3)②设有一个视图定义，包含每个学生选修课程（成绩为非空）的门数和平均成绩：

CREATEVIEWS_GRADE（S#，C_NUM，AVG_GRADE） ASSELECTS#，COUNT（C#），AVG（GRADE） FROMSC WHEREGRADEISNOTNULL

视图S_GRADE虽然是从单个基本表导出，但导出时使用了分组和聚集操作，因此也是不能更新的。譬如，在未更改基本表SC中值时，要在视图S_GRADE中更改学生的平均成绩，显然是不切实际的。对视图的更新操作(4) ③如果定义了一个有关男学生的视图：

CREATEVIEWS_MALE ASSELECTS#,SNAME,AGE FROMS WHERESEX='M'；

这个视图是从单个关系只使用选择和投影导出的，并且包含键S#，因此是可更新的。例如，执行插入操作：

INSERTINTOS_MALE

VALUES（'S28','WU',18）；

系统自动会把它转变成下列语句：

INSERTINTOS VALUES('S28','WU',18,'M')；

返回视图的优点

视图是用户一级的数据观点，由于有了视图，使数据库系统具有下列优点：（1）视图提供了逻辑数据独立性。

（2）简化了用户观点。数据库的全部结构是复杂的,并有多种联系。

（3）数据的安全保护功能。

返回3.6嵌入式SQL

SQL语言有两种使用方式：一种是在终端交互方式下使用，称为交互式SQL；另一种是嵌入在高级语言的程序中使用，称为嵌入式SQL，而这些高级语言可以是C、ADA、PASCAL、COBOL或PL/I等，称为宿主语言。

3.6.1SQL的运行环境

3.6.2嵌入式SQL的使用规定

3.6.3

嵌入式SQL的使用技术

3.6.4

动态SQL语句

SQL的运行环境

宿主语言十嵌入式SQL

预处理程序

宿主语言十函数调用

SQL函数定义库宿主语言编译程序

目标程序

图3.14源程序处理过程嵌入式SQL的使用规定

在程序中要区分SQL语句与宿主语言语句允许嵌入的SQL语句引用宿主语言的程序变量（称为共享变量），但有两条规定： (1)引用时，这些变量前必须加冒号“：”作为前缀标识，以示与数据库中变量有区别。(2)这些变量由宿主语言的程序定义，并用SQL的DECLARE语句说明。SQL的集合处理方式与宿主语言单记录处理方式之间的协调。

(1)游标定义语句（DECLARE）。

(2)游标打开语句（OPEN）。

(3)游标推进语句(FETCH)。

(4)游标关闭语句（CLOSE）。

嵌入式SQL的使用技术不涉及游标的SQLDML语句涉及游标的SQLDML语句卷游标的定义和推进涉及游标的SQLDML语句

（1）SELECT语句的使用方式

当SELECT语句查询结果是多个元组时，此时宿主语言程序无法使用，一定要用游标机制把多个元组一次一个地传送给宿主语言程序处理。（2）对游标指向元组的修改或删除操作 在游标处于活动状况时，可以修改或删除游标指向的元组。

动态SQL语句(1)动态SQL预备语句

EXECSQLPREPARE〈动态SQL语句名〉FROM〈共享变量或字符串〉这里共享变量或字符串的值应是一个完整的SQL语句。这个语句可以在程序运行时由用户输入才组合起来。此时，这个语句并不执行。动态SQL语句(2)动态SQL执行语句

EXECSQLEXECUTE〈动态SQL语句名〉 动态SQL语句使用时，还可以有两点改进：（1）当预备语句中组合而成的SQL语句只需执行一次时，那么预备语句和执行语句可合并成一个语句：

EXECSQLEXECUTEIMMEDIATE〈共享变量或字符串〉（2）当预备语句中组合而成的SQL语句的条件值尚缺时，可以在执行语句中用USING短语补上：

EXECSQLEXECUTE<动态SQL语句名>USING<共享变量>动态SQL语句(3)例3.33下面两个C语言的程序段说明了动态SQL语句的使用技术。①EXECSQLBEGINDECLARESECTION；char*query； EXECSQLENDDECLARESECTION； scanf（〞%s〞，query）； /*从键盘输入一个SQL语句*/

EXECSQLPREPAREqueFROM：query； EXECSQLEXECUTEque；

这个程序段表示从键盘输入一个SQL语句到字符数组中；字符指针query指向字符串的第1个字符。如果执行语句只做一次，那么程序段最后两个语句可合并成一个语句：

EXECSQLEXECUTEIMMEDIATE：query；动态SQL语句(4)②char*query=〞UPDATEsc SETgrade=grade*1.1 WHEREc#=？〞；

EXECSQLPREPAREdynprogFROM:query；

charcno[5]=〞C4〞；

EXECSQLEXECUTEdynprogUSING:cno；

这里第一个char语句表示用户组合成一个SQL语句，但有一个值（课程号）还不能确定，因此用“？”表示。第二个语句是动态SQL预备语句。第三个语句（char语句）表示取到了课程号值。第四个语句是动态SQL执行语句，“？”值到共享变量cno中取。

小结

SQL是关系数据库的标准语言，已广泛应用在商用系统中。SQL的数据定义部分包括对SQL模式、基本表、视图、索引的创建和撤销。SQL的数据操纵分成数据查询和数据更新两部分。SQL的数据查询是用SELECT语句实现，兼有关系代数和元组演算的特点。SQL的数据更新包括插入、删除和修改等三种操作，在视图中只有行列子集视图是可以更新的。嵌入式SQL涉及到SQL语句的宿主语言程序中的使用规定，以解决两种语言的不一致和相互联系的问题。同时还介绍了动态SQL语句。

本章的重点篇幅

（1）教材中P97的例3.8（SELECT语句）。（2）教材