第3章 关系数据库标准语言SQL——复习笔记

第3章 关系数据库标准语言SQL

复习笔记

一、SQL概述

01SQL的产生与发展

SQL在1974年由Boyce和Chamberlin提出的，最初叫Sequel，并在IBM公司研制的关系数据库管理系统原型SystemR上实现。SQL简单易学，功能丰富。1986年10月，美国国家标准局（American National standard Institute AMSI）的数据库委员会 X3H2批准了SQL作为关系数据库语言的美国标准，同年公布了 SQL标准文本（简称 SQL-86）。1987年，国际标准化组织（IntemationaI Organization for Standardization，ISO）也通过了这一标准。

02SQL的特点

（1）综合统一

数据库系统的主要功能是通过数据库支持的数据语言来实现的。非关系模型（层次模型、网状模型）的数据语言一般都分为：

①模式数据定义语言（Schema Data Definition Language，模式DDL）。

②外模式数据定义语言（Subschema Data Definition Language，外模式 DDDL或子模式DDL）。

③数据存储有关的描述语言（Data Storage Description Language，DSDL）。

④数据操纵语言（Data Manipulation Language，DML）。

（2）高度非过程化

SQL进行数据操作时，只要提出"做什么"，面无须指明"怎么做"，因此无须了解存取路径。存取路径的选择以及SQL的操作过程由系统自动完成。

（3）面向集合的操作方式

SQL采用集合操作方式，不仅操作对象、查找结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。

（4）以同一种语法结构提供多种使用方式

SQL 既是独立的语言，又是嵌入式语言。在两种不同的使用方式下，SQL的语法结构基本上是一致的。这种以统一的语法结构提供多种不同使用方式的做法，提供了极大的灵活性与方便性。

（5）语言简洁，易学易用

SQL功能极强，但由于设计巧妙，语言十分简洁，完成核心功能只用了9个动词，如表3-1所示。SQL接近英语口语，因此易于学习和使用。

表3-1 SQL的动词

03SQL的基本概念

支持SQL的关系数据库管理系统同样支持关系数据库三级模式结构。如图3-1所示。

图3-1 SQL对关系数据库模式的支持

外模式包括若干视图（view）和部分基本表（base table），数据库模式包括若干基本表，内模式包括若干存储文件（stored file）。

（1）基本表

基本表是本身独立存在的表，在关系数据库管理系统中一个关系就对应一个基本表。一个或多个基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。

（2）视图

视图是从一个或几个基本表导出的表。它本身不独立存储在数据库中，即数据库中只存放视图的定义而不存放视图对应的数据。视图是一个虚表。视图在概念上与基本表等同，用户可以在视图上再定义视图。

二、数据定义

SQL的数据定义功能包括模式定义、表定义、视图和索引的定义，如表3-2所示。

表3-2 SQL的数据定义语句

01模式的定义与删除

（1）定义模式

在SQL中，模式定义语句如下：

create schema<模式名>authorization<用户名>

如果没有指定<模式名>，那么<模式名>隐含为<用户名>。要创建模式，调用该命令的用户必须拥有数据库管理员权限，或者获得了数据库管理员授予的create schema的权限。

定义模式实际上定义了一个命名空间，在这个空间可以进一步定义该模式包含的数据库对象，例如基本表、视图、索引等。

在create schema中可以接受create table，create view和grant子句。也就是说用户可以在创建模式的同时在这个模式定义中进一步创建基本表、视图，定义授权。

模式嵌套定义的语句是：

create schema<模式名>authorization<用户名>[<表定义子句>|<视图定义子句>|<授权定义子句>]

（2）删除模式

在SQL中，删除模式语句如下：

drop schema <模式名><cascade|restrict>

其中cascade和restrict两者必选其一。选择了cascade（级联），表示在删除模式的同时把该模式中所有的数据库对象全部删除；选择了restrict（限制），表示如果该模式中已经定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。只有当该模式中没有任何下属的对象时才能执行drop schema语句。

02基本表的定义、删除与修改

（1）定义基本表

创建了一个模式就建立了一个数据库的命名空间，一个框架。在这个空间中首先要定义的是该模式包含的数据库基本表。

SQL语言使用create table语句定义基本表，其基本格式如下：

create table<表名>（<列名><数据类型>[列级完整性约束条件]

[，<列名><数据类型>[列级完整性约束条件]]

…

[，<表级完整性约束条件>]）；

建表的同时通常还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中，当用户操作表中数据时由关系数据库管理系统自动检查该操作是否违背这些完整性约束条件。

（2）数据类型

SQL标准支持多种数据类型，常用数据类型如表3-3所示。

表3.4  数据类型

（3）模式与表

每一个基本表都属于某一个模式，一个模式包含多个基本表。定义基本表所属模式的三种方法如下所示：

①在表名中明显地给出模式名。

②创建模式语句中同时创建表。

③设置所属的模式，这样在创建表时表名中不必给出模式名。

（4）修改基本表

SQL语言用alter table语句修改基本表，其一般格式为

alter table <表名>

[add [column] <新列名><数据类型>[完整性约束]]

[add <表级完整性约束>]

[drop [column] <列名> [cascade|restrict]]

[drop constraint <完整性约束名>[restrict|cascade]]

[alter column <列名><数据类型>]；

其中<表名>是要修改的基本表，add子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件。drop column子句用于删除表中的列，如果指定了cascade短语，则自动删除引用了该列的其他对象，比如视图；如果指定了restrict短语，则如果该列被其他对象引用，RDBMS将拒绝删除该列。drop constraint子句用于删除指定的完整性约束条件。alter column子句用于修改原有的列定义，包括修改列名和数据类型。

（5）删除基本表

当某个基本表不再需要时，可以使用drop table语句删除它。其一般格式为：

drop table<表名>[ restrict|cascade]；

若选择restrict，则该表的删除是有限制条件的；若选择cascade，则该表的删除没有限制条件。默认情况是restrict。基本表定义一旦被删除，不仅表中的数据和此表的定义将被删除，而且此表上建立的索引、触发器等对象一般也都将被删除。

03索引的建立与删除

建立索引是加快查询速度的有效手段。数据库索引有多种类型，常见索引包括顺序文件上的索引、B+树索引、散列（hash）索引、位图索引等。索引虽然能够加速数据库查询，但需要占用一定的存储空间，当基本表更新时，索引要进行相应的维护，这些都会增加数据库的负担，因此要根据实际应用的需要有选择地创建索引。

（1）建立索引

在SQL语言中，建立索引使用create index语句，其一般格式为

create [unique][cluster] index <索引名>

on <表名>(<列名>[<次序>][,<列名>[<次序>]]···)；

其中，<表名>是要建索引的基本表的名字。索引可以建立在该表的一列或多列上，各列名之间用逗号分隔。每个<列名>后面还可以用<次序>指定索引值的排列次序，可选asc（升序）或desc（降序），默认值为asc。

unique表明此索引的每一个索引值只对应唯一的数据记录。

cluster表示要建立的索引是聚簇索引。

（2）删除索引

索引一经建立，就由系统使用和维护它，不需用户干预。

在SQL中，删除索引使用drop index语句，其一般格式为"drop index<索引名>"。删除索引时，系统会同时从数据字典中删去有关该索引的描述。

04数据字典

数据字典是关系数据库管理系统内部的一组系统表，它记录了数据库中所有的定义信息，包括关系模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等。

三、数据查询

数据查询是数据库的核心操作。SQL提供了 SELECT 语句进行数据查询，该语句具有灵活的使用方式和丰富的功能。其一般格式为：

select [all|disinct] <目标列表达式> [别名] [,<目标列表达式> [别名]]···

from <表名或视图名> [别名] [,<表名或视图名>[别名]]···|(<select语句>) [as] <别名>

[where <条件表达式>]

[group by <列名1> [having <条件表达式>]]

[order by <列名2> [asc|desc]];

整个 select语句的含义是：根据where子句的条件表达式，从 from子句指定的基本表或视图中找出满足条件的元组，再按 select子句中的目标列表达式，选出元组中的属性值形成结果表。

如果有group by子句，则将结果按<列名1>的值进行分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。如果group by子句带 having短语，则只有满足指定条件的组才予以输出。如果有order by子句，则结果表还要按<列名2>的值的升序或降序排序。

select语句既可以完成简单的单表查询，也可以完成复杂的连接查询和嵌套查询。

01单表查询

单表查询是指仅涉及一个表的查询。

（1）选择表中的若干列

选择表中的全部或部分列即关系代数的投影运算。

①查询指定列

在很多情况下，用户只对表中的一部分属性列感兴趣，这时可以通过在 select子句的<目标列表达式>中指定要查询的属性列。

②查询全部列

将表中的所有属性列都选出来有两种方法：

a.在select关键字后列出所有列名；

b.如果列的显示顺序与其在基表中的顺序相同，也可以简单地将<目标列表达式>指定为"*"。

③查询经过计算的值

select子句的<目标列表达式>不仅可以是表中的属性列，也可以是表达式。

用户可以通过指定别名来改变查询结果的列标题，这对于含算术表达式、常量、函数名的目标列表达式尤为有用。

（2）选择表中的若干元组

①消除取值重复的行

两个本来并不完全相同的元组，投影到指定的某些列上后，可能变成相同的行了，可以用distinct取消它们。

如果没有指定distinct关键词，则缺省为all，即保留结果表中取值重复的行。

②查询满足条件的元组

查询满足指定条件的元组可以通过where子句实现。where子句常用的查询条件如表3-4所示。

表3-4 常用的查询条件

a. 比较大小

用于进行比较的运算符一般包括：=（等于），>（大于），<（小于），>=（大于等于），<=（小于等于），=或◇（不等于），！>（不大于），！<（不小于）。

b.确定谓词范围

谓词between···and···和not between···and···可以用来查找属性值在（或不在）指定范围内的元组，其中between后是范围的下限（即低值），and后是范围的上限（即高值）。

c.确定集合

谓词in可以用来查找属性值属于指定集合的元组；与in相对的谓词是not in，用于查找属性值不属于指定集合的元组。

d.字符匹配

谓词like可以用来进行字符串的匹配。其一般语法格式如下：

[not]like<匹配串>[escape'<换码字符>']

其含义是查找指定的属性列值与<匹配串>相匹配的元组。<匹配串>可以是一个完整的字符串，也可以含有通配符%和_。其中：%（百分号）代表任意长度（长度可以为0）的字符串。（下横线）代表任意单个字符。

如果like后面的匹配串中不含通配符，则可以用=（等于）运算符取代like谓词，用！=或<<（不等于）运算符取代not like谓词。

如果用户要查询的字符串本身就含有通配符%或_，这时就要使用escape'<换码字符>'短语，对通配符进行转义了。

e.涉及多值的查询

f.多重条件查询

逻辑运算符and和or可用来连接多个查询条件。and的优先级高于or，但用户可以用括号改变优先级。

（3）order by子句

用户可以用order by子句对查询结果按照一个或多个属性列的升序（asc）或降序（desc）排列，默认值为升序。

对于空值，排序时显示的次序由具体系统实现来决定。

（4）聚集函数

为了进一步方便用户，增强检索功能，SQL提供了许多聚集函数，主要有：

count(*)                                                   统计元组个数

count([distinct|all]<列名>)                         统计一列中值的个数

sum([distinct|all]<列名>)                           计算一列值的总和（此列必须是数值型）

avg([distinct|all]<列名>)                            计算一列值的平均值（此列必须是数值型）

max([distinct|all]<列名>)                           求一列值中的最大值

min([distinct|all]<列名>)                           求一列值中的最小值

如果指定distinct短语，则表示在计算时要取消指定列中的重复值。如果不指定distinct短语或指定all短语（all为默认值），则表示不取消重复值。

当聚集函数遇到空值时，除count(*)外，都跳过空值而只处理非空值。count(*)是对元组进行计数，某个元组的一个或部分列取空值不影响count的统计结果。where子句中是不能用聚集函数作为条件表达式的。聚集函数只能用于select子句和group by中的having子句。

（5）group by子句

group by子句将查询结果按某一列或多列的值分组，值相等的为一组。

对查询结果分组的目的是为了细化聚集函数的作用对象。如果未对查询结果分组，聚集函数将作用于整个查询结果。分组后聚集函数将作用于每一个组，即每一组都有一个函数值。

如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用having短语指定筛选条件。

02连接查询

若一个查询同时涉及两个以上的表，则称之为连接查询。连接查询是关系数据库中最主要的查询，包括等值连接查询、自然连接查询、非等值连接查询、自身连接查询、外连接查询和复合条件连接查询等。

（1）等值与非等值连接查询

连接查询的where子句中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为：

[<表名1>]<列名1><比较运算符>[<表名2>]<列名2>

其中比较运算符主要有：=、>、<、>=、<=、！=（或<>）等。此

外连接谓词还可以使用下面形式：

[<表名1>.]<列名1>between[<表名2>.]<列名2>and[<表名2>.]<列名3>

当连接运算符为"="时，称为等值连接，若在等值连接中把目标列中重复的属性列去掉则为自然连接。使用其他运算符称为非等值连接。

（2）自身连接

连接操作是一个表与其自己进行连接，称为表的自身连接。

（3）外连接

左外连接列出左边关系中所有的元组，右外连接列出右边关系中所有的元组。

（4）多表连接

连接操作除了可以是两表连接、一个表与其自身连接外，还可以是两个以上的表进行连接，后者通常称为多表连接。

关系数据库管理系统在执行多表连接时，通常是先进行两个表的连接操作，再将其连接结果与第三个表进行连接。

03嵌套查询

在SQL语言中，一个select form where语句称为一个查询块。将一个查询块嵌套在另一个查询块的where子句或having短语的条件中的查询称为嵌套查询（nested query）。上层的查询块称为外层查询或父查询，下层查询块称为内层查询或子查询。

SQL语言允许多层嵌套查询。子查询的select语句中不能使用order by子句，order by子句只能对最终查询结果排序。

（1）带有in谓词的子查询

在嵌套查询中，子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最经常使用的谓词。查询涉及多个关系时，用嵌套查询逐步求解，层次清楚，易于构造，具有结构化程序设计的优点。如果子查询的查询条件依赖于父查询，这类子查询称为相关子查询（Correlated Subquery），整个查询语句称为相关嵌套查询（Correlated nested query）语句。求解相关子查询不能像求解不相关子查询那样，一次将子查询求解出来，然后求解父查询。内层查询由于与外层查询有关，因此必须反复求值。

（2）带有比较运算符的子查询

带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单值时，可以用>、<、=、>=、<=、！=和<>等比较运算符。

（3）带有any（some）或all谓词的子查询

子查询返回单值时可以用比较运算符，但返回多值时要用any（有的系统用some）或 all谓词修饰符。而使用any或all谓词时则必须同时使用比较运算符。其语义为：

>any                                   大于子查询结果中的某个值

>all                              大于子查询结果中的所有值

<any                                   小于子查询结果中的某个值

<all                              小于子查询结果中的所有值

>=any                                 大于等于子查询结果中的某个值

>=all                                   大于等于子查询结果中的所有值

<=any                                 小于等于子查询结果中的某个值

<=all                                   小于等于子查询结果中的所有值

=any                                   等于子查询结果中的某个值

=all                              等于子查询结果中的所有值（通常没有实际意义）

!=(或<>)any                        不等于子查询结果中的某个值

!=(或<>)all                         不等于子查询结果中的任何一个值

any、all与聚集函数的对应关系如表3-5所示。

表3-5  any（或some）、all谓词与聚集函数、in谓词的等价转换关系

表3-5中，=any等价于in谓词，<any等价于<max，<>all等价于not in谓词，<all等价于<min，等等。

（4）带有EXISTS谓词的子查询

exists代表存在量词$。带有exists谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。

可以利用exists来判断X∈S、S∈R、S=R、S∩R非空等是否成立。

04集合查询

集合操作主要包括并操作union、交操作intersect和差操作except。

参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同。

05基于派生表的查询

子查询不仅可以出现在where子句中，还可以出现在form子句中，这时子查询生成的临时派生表（derived table）成为主查询的查询对象。

如果子查询中没有聚集函数，派生表可以不指定属性列，子查询select子句后面的列名为其默认属性。通过from子句生成派生表时，as关键字可以省略，但必须为派生关系指定一个别名。而对于基本表，别名是可选择项。

06select语句的一般格式

select语句的一般格式：

select [all|disinct] <目标列表达式> [别名] [,<目标列表达式> [别名]]···

from <表名或视图名> [别名] [,<表名或视图名>[别名]]···|(<select语句>) [as] <别名>

[where <条件表达式>]

[group by <列名1> [having <条件表达式>]]

[order by <列名2> [asc|desc]];

（1）目标列表达式的可选格式：

①*。

②<表名>.*。

③count（[distinct|all] *）。

④[<表名>.]<属性列名表达式> [,[<表名>.] <属性列名表达式>]···。

（2）聚集函数的一般格式为：

 （3）where子句的条件表达式的可选格式：

①

②

③

④

<属性列名> [not] like <匹配串>

⑤

<属性列名> is [not] null

⑥

[not] exists (select语句)

⑦

四、数据更新

数据更新操作有三种：向表中添加若干行数据、修改表中的数据和删除表中的若干行数据。

01插入数据

SQL的数据插入语句insert通常有两种形式，一种是插入一个元组，另一种是插入子查询结果。后者可以一次插入多个元组。

（1）插入元组

插入元组的insert语句的格式为

insert into <表名> [(<属性列1> [,<属性列2>]···)]

values (<常量1> [,<常量2>]···);

其功能是将新元组插入指定表中。其中新元组的属性列1的值为常量1，属性列2的值为常量2，···。into子句中没有出现的属性列，新元组在这些列上将取空值。但必须注意的是，在表定义时说明了not null的属性列不能取空值，否则会出错。

如果into子句中没有指明任何属性列名，则新插入的元组必须在每个属性列上均有值。

（2）插入子查询结果

插入子查询结果的insert语句格式为

insert

into <表名> [(<属性列1>  [,<属性列2>···])]

子查询；

02修改数据

修改操作又称为更新操作，其语句的一般格式为

update <表名>

set <列名> = <表达式> [,<列名> = <表达式>]···

[where <条件>];

其功能是修改指定表中满足where子句条件的元组。其中set子句给出<表达式>的值用于取代相应的属性列值。如果省略where子句，则表示要修改表中的所有元组。

03删除数据

删除语句的一般格式为

delete

from <表名>

[where <条件>];

delete语句的功能是从指定表中删除满足where子句条件的所有元组。如果省略where子句则表示删除表中全部元组，但表的定义仍在字典中。也就是说，delete语句删除的是表中的数据，而不是关于表的定义。

五、空值的处理

空值就是“不知道”或“不存在”或“无意义”的值。SQL 语言中允许某些元组的某些属性在一定情况下取空值。

01空值的可能性

（1）该属性应该有一个值，但目前不知道它的具体值。

（2）该属性不应该有值。

（3）由于某种原因不便于填写。

02空值的处理

（1）空值的产生。

（2）空值的判断。

（3）空值的约束条件。

（4）空值的算术运算、比较运算和逻辑运算。

六、视图

视图是从一个或几个基本表（或视图）导出的表。它与基本表不同。视图就像一个窗口，透过它可以看到数据库中自己感兴趣的数据及其变化，它可以和基本表一样被查询、被删除。也可以在一个视图之上再定义新的初图，但对视图的更新（增、删、改）操作则有一定的限制。

01定义视图

（1）建立视图

SQL语言用create view命令建立视图，其一般格式为

create view <视图名> [(<列名> [,<列名>]···)]

as <子查询>

[with check option];

其中，子查询可以是任意的select语句，是否可以含有order by子句和distinct短语，则取决于具体系统的实现。

with check option表示对视图进行update、insert和delete操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）。

在下列三种情况下必须明确指定组成视图的所有列名：

（1）某个目标列不是单纯的属性名，而是聚集函数或列表达式；

（2）多表连接时选出了几个同名列作为视图的字段；

（3）需要在视图中为某个列启用新的更合适的名字。

RDBMS执行 create view语句的结果只是把视图的定义存入数据字典，并不执行其中的 select语句。只是在对视图查询时，才按视图的定义从基本表中将数据查出。

视图不仅可以建立在一个或多个基本表上，也可以建立在一个或多个已定义好的视图上，或建立在基本表与视图上。

（2）删除视图

删除语句的格式为

drop view <视图名> [cascade];

视图删除后视图的定义将从数据字典中删除。如果该视图上还导出了其他视图，则使用cascade级联删除语句把该视图和由它导出的所有视图一起删除。

基本表删除后，由该基本表导出的所有视图均无法使用了，但是视图的定义没有从字典中清除。删除这些视图定义需要显式地使用drop view语句。

02查询视图

RDBMS执行对视图的查询时，首先进行有效性检查。检查查询中涉及的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询。

03更新视图

更新视图是指通过视图来插入(insert)、删除(delete)和修改(update)数据。

为防止用户通过视图对数据进行增加、删除、修改时，有意无意地对不属于视图范围内的基本表数据进行操作，可在定义视图时加上with check option子句。这样在视图上增、删、改数据时，关系数据库管理系统会检查视图定义中的条件，若不满足条件则拒绝执行该操作。

04视图的作用

合理使用视图能够带来许多好处：

（1）视图能够简化用户的操作。

（2）视图使用户能以多种角度看待同一数据。

（3）视图对重构数据库提供了一定程度的逻辑独立性。

（4）视图能够对机密数据提供安全保护。

（5）适当的利用视图可以更清晰的表达查询。