第 3 章 结构化语言 SQL 基础
本章学习目标
本章主要讲解结构化语言 SQL 产生与发展以及利用 SQL 语言进行数据库定义、 数据检索、数据插入、数据修改、数据删除等。 通过本章的学习，读者应掌握以下内容： 了解 SQL 语言的产生与发展。 认识 SQL 语言的特点。 深入理解 SQL 语言所涉及的基本概念。 熟练使用 SQL 语言进行数据库定义、数据检索、数据插入、数据修改、数 据删除等操作。3.1 SQL 概述
SQL（Structured Query Language，结构化查询语言）在 1974 年由 Boyce 和 Chamberlin 提 出，首先在 IBM 公司的关系数据库系统 System R 上实现。由于 SQL 功能丰富、使用方便、 灵活、语言简洁易学，所以它的应用系统范围广，最终发展成为关系数据库标准语言。 现在，数据库厂商推出的大部分 DBMS 产品都支持 SQL，使其成为操作数据库的标准语 言，从而使得不同的数据库系统之间有了共同的操作基础。 SQL 介于关系代数与关系演算之间，集数据查询、数据操纵、数据定义和数据控制功能 于一体，其综合、强大、简洁的特点使其成为国际标准，主要特点如下： （1）综合统一。SQL 语言综合统一的特征体现在两个方面。首先，SQL 语言集数据定义 语言 DDL、数据操纵语言 DML、数据控制语言 DCL 功能于一体，充分体现了关系数据语言 的特点和优点。例如，用户在数据库投入运行以后，还可以根据要求随时地修改模式，并不需 要停止数据库的运行转入数据操纵语言 DML 才可以修改，这使得系统具有良好的可扩充性。 其次，关系模型的实体及实体间的联系均用关系表示，数据结构的单一性使得 SQL 数据操作 符也是统一的，其查询、插入、删除、修改都只有一种操作符。 （2）高度非过程化的语言。非关系数据模型的数据操纵语言是面向过程的语言，用其完 成操作必须指定存取路径。而用 SQL 语言进行数据操作，用户只需提出“干什么”，至于“怎 么干”由 DBMS 解决。这样大大减轻用户负担，而且有利于提高数据独立性。 （3）面向集合的语言。每一个 SQL 的操作对象是一个或多个关系，操作的结果也是一个 关系。 （4）以一种语法结构提供两种操作方式。既可独立使用，又可嵌入到宿主语言中使用， 具有自含型和宿主型两种特点。作为自含式语言，它能够独立地用于联机交互的使用方式，用
户可以在终端键盘上直接键入 SQL 命令对数据库进行操作。作为嵌入式语言，它又能够很方 便地嵌入到高级语言中使用。两者的语法结构基本一致。 （5）语言简捷，易学易用。实现 SQL 核心功能只需 9 个动词，如表 3-1 所示。在语言上 接近英语，因此易于掌握。 表 3-1 SQL 语言的动词 SQL 功能 操作符 数据查询 SELECT 数据定义 CREATE，DROP，ALTER 数据操纵 INSERT，UPDATE，DELETE 数据控制 GRANT，REVOKE 其简单解释如下： SELECT：从一个表或多个表中检索列和行。 CREATE：按特定的表模式创建一个新表。 DROP：删除一张表。 ALTER：在一个表被建立之后，修改表的字段设计。 INSERT：向一个表中增加行。 UPDATE：更新表中已存在的行的某几列的值。 DELETE：从一个表中删除行。 GRANT：向数据库中的用户授以操作权限（如修改某个表的权限、删除某个表的权限）。 REVOKE：收回以前授予给当前数据库中用户的权限。
3.2 数据定义
3.2.1 创建数据库 数据库是一个存放数据的表和支持这些数据的存储、检索、安全性和完整性的逻辑成分 所组成的集合。数据库对象包括组成数据库的逻辑成分，如表、视图、索引等。 ANSI 标准 SQL 建立数据库结构（模式）的命令是：CREATE WORKAMOUNTHEMA AUTHORIZATION <创建者>;
例如，创建者是李平的命令如下：
CREATE WORKAMOUNTHEMA AUTHORIZATION <李平>;
但是大部分的 DBMS，如 DB2、XDB、DBASE 创建数据库的命令语法格式如下：
CREATE DATABASE <DATABASE_NAME>;
例如，创建一个名为 TEST1 的数据库：
CREATE DATABASE TEST1;
3.2.2 表及其创建
段的数目是固定的，每个字段都有一个名字。行也叫元组或者记录，行的数目是变化的，它反 映在任意时刻表里存储的数据量，也就是表中记录的多少。每个字段都有一个数据类型。数据 类型是约束可以赋予一个字段可取数值的集合，并且约束为存储在字段里的数据赋以语义，这 样它就可以用于计算。比如，一个声明为一个数值类型的字段将不会接受任意文本字串，而存 储在这样的字段里的数据可以用于数学计算。相比之下，一个声明为字符串类型的字段将接受 几乎任意类型的数据，但是它们自身是不能进行数学计算的，不过可以进行其他像字符串连接 这样的操作。 Oracle 数据类型与 SQL Server 支持的数据类型就比较丰富，如表 3-2 所示。数据类型具有 精度、小数位数、长度等属性。精度是指数值中所存储的十进制数据的总位数。小数位数是指 数值数据中小数点右边可以有的数字位数的最大值。如 3563.863 的精度为 7，小数位数为 3。 长度是指存储数据所使用的字节数。 表 3-2 Oracle 数据类型与 SQL Server 数据类型比较（部分数据类型） Microsoft SQL Server Oracle
数据类型名称 说明 数据类型名称 说明 Bit 1 位，值为 0 或 1 INTEGER 范围较小的整数类型 Integer/Smallint 4 字节整数/2 字节整数 NUMBER(P,S)/ DECIMAL(P,S) 数字类型，P 为整数位， S 为小数位 Tinyint 1 字节，值为 0～255 的整数 FLOAT 浮点数类型，双精度 Decimal(p,s) 数字数据，固定精度为 p， 宽度为 s REAL 实数类型，精度更高 Char(n)/ Varchar(n) 非 Unicode 字符串的固定长 度/可变长度，n=1～8000 CHAR/VARCHAR 固定/可变长度字符串最 大长度 2000/4000 字节 Money 8 字节，存放货币类型 LONG 超长字符串，最大长度 2G（231-1） Datetime/Smalldatetime 8 字节/4 字节，描述某天的 日期和时刻，值的精确度为 1/300 秒 DATE 日期（日－月－年） Uniqueidentifier 16 字节，存放全局唯一标识 （GUID） ROWID 数据表中记录的唯一行 号 Binary(n)/ Varbinary(n) 固定长度/可变长度二进制 数据 BLOB 二进制数据，最大长度 4G Image 可变长度二进制数据 BFILE 存放在数据库外的二进 制数据 （1）基本表的定义。 语句格式： CREATE TABLE <表名> (<列名><数据类型>[<列级完整性约束条件>]
[,<列名><数据类型>[<列级完整性约束条件>]]… [,<表级完整性约束条件>]); <表名>：要定义的基本表的名字。 <列名>：组成该表的各个属性（列）。 <列级完整性约束条件>：涉及相应属性列的完整性约束条件。 <表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件。
例 3.1 建立一个“雇员”表 Employee，它由职工号 Number、姓名 Name、性别 Sex、年 龄 Age、所在部门 Department 五个属性组成。其中职工号不能为空，值是唯一的，并且姓名 取值也唯一。
CREATE TABLE Employee
(Number CHAR(5) NOT NULL UNIQUE, Name CHAR(20) UNIQUE,
Sex CHAR(1), Age Integer, Department CHAR(15)); 建立表时，常用完整性约束主要有以下几种： 主键约束：PRIMARY KEY。 唯一性约束：UNIQUE。 非空值约束：NOT NULL。 默认约束：DEFAULT(**)，将该列常用的值定义为默认值，减少数据输入。 检查约束：CHECK(**)，通过约束条件表达式设置列值应满足的条件。
其中，PRIMARY KEY 定义某属性为表主键，它与 UNIQUE 的区别是：定义为主键的属 性一定是互不相同的，即 PRIMARY KEY 约束包含 UNIQUE 约束；UNIQUE 约束只是表示该 属性的值互不相同，它不是主键。 （2）基本表的删除。 语句格式： DROP TABLE <表名>; 基本表被删除以后，基本表中的数据、表上建立的索引都被删除了，表上的视图往往仍 然保留，却无法引用。删除基本表时，系统会从数据字典中删去有关该基本表及其索引的描述。 例 3.2 删除 Employee 表。
DROP TABLE Employee;
（3）基本表的修改。 语句格式： ALTER TABLE <表名> [ADD <新列名> <数据类型>[ 完整性约束 ]] [DROP <完整性约束名>] [MODIFY <列名> <数据类型>]; <表名>：要修改的基本表。 ADD 子句：增加新列和新的完整性约束条件。 DROP 子句：删除指定的完整性约束条件。 MODIFY 子句：用于修改列名和数据类型。
例 3.3 向 Employee 表增加“进入公司时间”列，其数据类型为日期型。
ALTER TABLE Employee ADD Cometime DATE;
不论基本表中原来是否已有数据，新增加的列一律为空值。
删除属性列可以分为直接删除或间接删除，间接删除就是把表中要保留的列及其内容复 制到一个新表中，然后删除原表，再将新表重命名为原表名。直接删除属性列比较简单。
该语句还可以利用 DROP 子句删除关于职工号必须取唯一值的约束。语句如下：
ALTER TABLE Employee DROP UNIQUE(Number)
利用 MODIFY 子句将年龄列的数据类型改为半字节整数。
ALTER TABLE Employee MODIFY Age SMALLINT
修改原有的列定义有可能会破坏已有数据，所以有些 DBMS 不允许直接删除属性列，这 时就必须采用间接删除。 3.2.3 约束 数据类型是约束可以在表里存储什么类型数据的一种方法。不过，对于许多应用，它们 提供的约束太粗糙。比如，一个包含产品价格的字段可能应该只接受正数，但是没有哪种数据 类型只接受正数。另外一个问题是可能需要根据其他字段或者行的数据来约束字段数据。如在 一个包含产品信息的表中，每个产品编号都应该只有一行。 对于这些问题，SQL 允许在字段和表上定义约束。约束给予所需要对数据施加的一切控 制。如果一个用户企图在一个字段里存储违反约束的数据，就会给出一个错误信息。这种情况 同时也适用于数值为默认值的情况。 （1）检查约束。检查约束是最常见的约束类型。它允许声明在某个字段里的数值必须满 足一个任意的表达式。要声明一个检查约束，使用关键字 CONSTRAINT，它后面跟着一个标 识符，表示约束一个独立的名字，然后再跟着约束定义。 例 3.4 要强制一个产品价格是正数，在 Oracle 中可以用：
CREATE TABLE products( product_no CHAR(8), name CHAR(8),
price NUMBER(7)CONSTRAINT c1 CHECK (price>0) ); 可见，约束定义在数据类型后面。一个检查约束由一个关键字 CHECK 后面跟着一个放 在圆括弧里的表达式组成。检查约束表达式应该包含受约束的字段，否则这个约束就没什么意 义了。 （2）非空约束。非空约束只是简单地声明一个字段必须不能是空值。前面讲建表的时 候已经提过，它也是约束的一种类型。一个非空约束总能写成一个字段约束。非空约束在功 能上等效于创建一个检查约束 CHECK(column_name is NOT NULL)，非空约束可以不用明确 的名字。
当然，一个字段可以有多个约束。只要在一个约束后面继续写另外一个就可以了。 （3）唯一约束。唯一约束 UNIQUE 保证在一个字段或者一组字段里的数据与表中其他行 的数据相比是唯一的。
例 3.5 下面两个表定义接受同样的数据。
CREATE TABLE products(
product_no INT UNIQUE NOT NULL, name CHAR(8),
price NUMBER(7) );
CREATE TABLE products( product_no PRIMARY KEY, name CHAR(8),
price NUMBER(7) );
主键也可以约束多于一个字段；其语法类似唯一约束。 例 3.6 主键用于约束两个字段。
CREATE TABLE example( a INT,
b INT, c INT,
PRIMARY KEY (a,c) ); 主键表示一个字段或若干个字段的组合，可以用于表中数据行的唯一标识。一个表最多 可以有一个主键，但是它可以有多个唯一和非空约束。每个表都必须有一个主键。 （5）外键约束。外键约束声明一个字段（或者一组字段）的数值必须匹配另一个表中某 些行出现的数值。这个行为称做两个相关表之间的参照完整性。 假设我们已建起一个产品表：
CREATE TABLE products( product_no PRIMARY KEY, name CHAR(8),
price NUMBER(7) );
例 3.7 假设有一个存储这些产品订单的表，要保证订单表只包含实际存在的产品。因此 在订单表中定义一个外键约束引用产品表。
CREATE TABLE orders(
order_id INT PRIMARY KEY,
product_no INT REFERENCES products(product_no), quantity INT ); 现在，我们不可能创建任何其 product_no 没有在产品表中出现的订单。在这种情况下我 们把订单表叫做参照表，而产品表是被参照表。 一个表可以包含多于一个外键约束。这个特性用于实现表之间多对多的关系。 我们知道外键不允许创建和任何产品都无关的订单。但是如果一个订单创建之后，而其 引用的产品被删除了会怎么样呢？SQL 允许指明这个问题的解法。简单地说，我们有以下几 种选择：
1）级联删除（cascades）。将参照关系中的所有外键值与被参照关系中要删除元组值相对 应的元组一起删除。 2）受限删除（restricted）。当参照关系中没有任何元组的外键值与要删除的被参照关系的 元组的主键值相同时，系统才执行删除操作，否则拒绝此删除操作。 3）置空值删除。删除被参照关系的元组，并将参照关系的元组中所有与被参照关系中被 删除元组主键值相同的外键值置为空值。 受限删除和级联删除是两种最常见的选项。 3.2.4 索引及其创建 假设你想找到本书中的某一个句子，可以一页一页地逐页搜索，但这会花很多时间。通 过使用本书的索引（目录），可以很快地找到要搜索的主题。我们可以认为表的索引就是表中 数据的目录。 在进行数据查询时，如果不使用索引，就需要将数据文件分块，逐个读到内存中进行查 找比较操作。如果使用索引，可以先将索引文件读入内存，根据索引项找到元组的地址，然后 再根据地址将元组数据读入内存，并且由于索引文件中只含有索引项和元组地址，文件很小， 而且索引项经过排序，索引可以很快地读入内存并找到相应的元组地址，极大地提高了查询的 速度。对一个较大的表来说，通过加索引，一个通常要花费几个小时来完成的查询只要几分钟 就可以完成。 使用索引可以加快连接速度。在两个关系进行连接操作时，系统需要在连接关系中对每 一个被连接字段做查询操作。如果每个连接文件的连接字段上建有索引，就可以大大加快连接 速度。如要实现雇员和选课的连接操作，在选课表的职工号字段（外键）上建立索引，数据连 接速度就会非常快。 索引分为两种：聚簇索引和非聚簇索引。 在聚簇索引中，索引树的叶级页包含实际的数据；记录的索引顺序与物理顺序相同。非 常类似于目录表，目录表的顺序与实际页码顺序。 在非聚簇索引中，叶级页指向表中的记录，记录的物理顺序与逻辑顺序没有必然关系。 非聚簇索引更像书的标准索引表。索引表的顺序与实际的页码顺序是不同的。一本书也许有多 个索引，例如也许同时有主题索引和作者索引。同样，一个表也可以同时有多个非聚簇索引。 索引的建立原则如下：
（1）索引的建立与维护由 DBA 和 DBMS 完成。索引由 DBA 和 DBO（表的属主）负责 建立与删除，其他用户不得随意建立与删除。维护工作由 DBMS 自动完成。 （2）大表应当建索引，小表不必建索引，一个基本表不宜建较多索引。索引要占用文件 目录和存储空间，对于记录较多的表，有必要建立索引，但也不宜过多。索引自身需要维护， 当基本表数据增加、删除或修改时，索引文件都要随之变化，以与基本表保持一致。 （3）根据查询要求建立索引。对于一些查询频度高、实时性要求高的数据一定要建立 索引。 有些 DBMS 自动建立以下列上的索引： PRIMARY KEY UNIQUE
（1）建立索引。 语句格式： CREATE[UNIQUE][CLUSTER]INDEX<索引名 >ON< 表名>(<列名>[< 次序>][,< 列名>[< 次序 >]]…); 用<表名>指定要建索引的基本表名字，索引可以建立在该表的一列或多列上，各列名之 间用逗号分隔；<次序>指定索引值的排列次序，升序用 ASC 表示，降序用 DESC 表示。默认 值是 ASC。 UNIQUE 表明此索引的每一个索引值只对应唯一的数据记录，CLUSTER 表示要建立的索 引是聚簇索引。 例 3.8 为雇员 Employee 表按职工号升序建立唯一索引。
CREATE UNIQUE INDEX EmpNumber ON Employee(Number);
对于已含重复值的属性列不能建立 UNIQUE 索引，对某个列建立 UNIQUE 索引后，插 入新记录时 DBMS 会自动检查新记录在该列上是否取了重复值。这相当于增加了一个 UNIQUE 约束。
建立聚簇索引后，基表中的数据也需要按指定的聚簇属性值的升序或降序存放。也就是 聚簇索引的索引项顺序与表中记录的物理顺序一致。
例 3.9 在 Employee 表的 Name（姓名）列上建立一个聚簇索引，而且 Employee 表中的 记录将按照 Name 值的升序存放。
CREATE CLUSTER INDEX EmpName ON Employee(Name);
在一个基本表上最多只能建立一个聚簇索引，聚簇索引的用途是对于某些类型的查询可 以提高查询效率。例如，很少对基表进行增删操作，很少对其中的变长列进行修改操作，可以 建立聚簇索引。 （2）删除索引。 语句格式： DROP INDEX <索引名>; 删除索引时，系统会从数据字典中删去有关该索引的描述。 例 3.10 删除 Employee 表的 EmpName 索引。
DROP INDEX EmpName;
3.2.5 存储过程与触发器 为保证数据库中数据的完整性、一致性、提高应用的性能，常常采用存储过程和触发器 技术。 1．存储过程 存储过程（Stored Procedure）是一组为了完成特定功能的 SQL 语句集，经编译后存储在 数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。 存储过程是实现特定功能的程序体，不同的应用程序都可以通过名称和参数调用存储过 程，对存储过程的修改完善不会影响应用程序，所以存储过程可以提高系统的可移植性。存储 过程是经过预编译和优化的程序代码，它能够实现较快的执行速度。 下面是 T-SQL 命令创建存储过程语法格式：
[{@parameter data_type} [VARYING][=default][OUTPUT]][,...n] [WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}] |[FOR REPLICATION] AS sql_statement[...n] 参数说明如下： procedure_name：要创建的存储过程的名字。 number：用一个整数来区别一组同名的存储过程。 @parameter：存储过程的参数。 data_type：参数的数据类型。 VARYING：用于指定作为输出 OUTPUT 参数支持的结果集，仅应用于游标型参数。 default：用于指定参数的默认值。 OUTPUT：表明该参数是一个返回参数。 RECOMPILE：存储过程每执行一次都又要重新编译。
ENCRYPTION：表示 SQL Server 加密了 syWorkAmountomments 表，该表的 text 字段是 包含 CREATE PROCEDURE 语句的存储过程文本。 FOR REPLICATION：用于指定不能在订阅服务器上执行为复制创建的存储过程。 AS：用于指定该存储过程要执行的操作。 sql_statement：存储过程中要包含的任意数目和类型的 Transact-SQL 语句。 例 3.11 创建一个存储过程，实现数据库中人员代码的自动生成，每增加一个新人员， 就从自动生成库 t_table.number 中得到新人员的人员代码，将这个值存入局部变量 str 中，作 为新记录的 personnel_id；然后执行存储过程，使 t_table.number 的值加 1，以备下一个调用。 创建存储过程：
CREATE PROCEDURE get_number AS Update t_table Set number=number+1 COMMIT 调用存储过程： select t_table.number into:str from t_table;
DECLARE sp_getnumber PROCEDURE FOR get_number; Execute sp_getnumber; Close sp_getnumber; 2．触发器 触发器是一种特殊类型的存储过程。它与存储过程的区别是：触发器主要是通过事件进 行触发而被执行的，而存储过程可以通过存储过程名字被直接调用执行。 当对某一表进行诸如 UPDATE、INSERT、DELETE 等操作时，SQL 就会自动执行触发器 所定义的 SQL 语句。触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参 照完整性和数据的一致性。触发器能够实现比 CHECK 语句更为复杂的约束。
规则，在三个方面不同于前面讨论的约束类型。 （1）当数据库编程人员所指定的某些事件发生时才对触发程序进行测试。允许的事件种 类通常为对特定关系的插入、删除或修改。 （2）不是直接阻止事件的发生，而是由触发程序对条件进行测试。如果条件不满足，则 什么也不做；否则，为响应该事件就会进行与该触发相关的处理。 （3）如果触发条件得到满足，就由 DBMS 执行与该触发相关的动作。于是该动作可能阻 止事件的发生或撤消事件（如删除插入的元组）。实际上，动作可能是数据库操作的任何序列。 在给出触发程序的语法细节之前，先研究一个能阐明最重要的语法以及语义特点的例子。 在本例中，对于每个修改的元组都执行一次触发程序。 例 3.12 写出应用于表 Farmer(name，address，cert，netget)的 SQL3 触发程序。触发程 序是由对 netget 属性的修改而启动的。该规则的作用是阻止降低农民纯收入的任何尝试。
CREATE TRIGGER NetgetgetTrigger ...(1)
AFTER UPDATE OF netget ON Farmer ...(2)
REFERENCING ...(3) OLD AS OldTuple, ...(4) NEW AS NewTuple ...(5) WHEN(OldTuple.netget>NewTuple.netget) ...(6) UPDATE Farmer ...(7) SET netget=OldTuple．Netget ...(8) WHERE cert=NewTuple.cert...(9)
FOR EACH ROW ...(10)
（1）行给出具有关键字 CREATE TRIGGER 和触发程序名的说明。然后，（2）行给出了 触发事件，即修改关系 Farmer 中的属性 netget。（3）到（5）行是为该触发程序的条件和动作 部分如何引用旧元组（修改前的元组）和新元组（修改后的元组）提供一种方法。根据（4） 行和（5）行中的说明，将用 OldTuple 和 NewTuple 分别引用这两个元组。在条件和动作部分， 这些名称可以像在普通 SQL 查询的 FROM 子句中说明的元组变量一样使用。（6）行是触发程 序的条件部分，它表明只有在新的农民纯收入低于旧的农民净纯收入时才执行动作。 （7）行到（9）行构成动作部分，它们是普通的 SQL 修改语句，功能是将该农民的纯收 入恢复到修改以前的值。注意，原则上，会考虑每个 Farmer 元组，但是（9）行的 WHERE 子句保证只影响到修改的元组（具有特定 cert 的元组）。 最后，（10）行表明了要求，即每当修改元组时，该触发程序都会启动一次。如果没有这 一行，每个 SQL 语句都使触发程序执行一次而无论发生多少改变元组的触发事件。 当然，例 3.12 仅说明了 SQL3 触发的某些特点。在下面的要点中，我们将概述触发程序 提供的选项以及如何描述这些选项。 （2）行如关键字 AFTER 所指，其规则的动作将在触发事件之后执行。可供选择代替 AFTER 的还有 BEFORE，此时 WHEN 中的条件在触发事件之前检验。如果条件为真，则执行 触发程序的动作。此外，无论条件是否为真，都将执行触发程序修改的事件。
除 UPDATE 以外，其他可能的触发事件有 INSERT 和 DELETE。（2）行中的 OF netget 子句是 UPDATE 事件的选项，并且现在假定把事件仅定义为修改关键字 OF 后面列出的属性。 OF 子句不支持 INSERT 或 DELETE 事件，这些事件只对整个元组有意义。
当触发事件为修改时，有旧元组和新元组，分别为修改前后的元组。我们用在（4）行和 （5）行看到的 OLD AS 和 NEW AS 子句为这些元组命名。如果触发事件是插入，那么可以用 NEW AS 子句为插入的元组命名，而 OLD AS 则不予接受。相反，对于删除操作，将用 OLD AS 为删除的元组命名，而 NEW AS 则不予接受。
倘若我们略掉（10）行中的 FOR EACH ROW，那么例 3.12 这样的行级触发程序（row-level trigger）就变成了语句级的触发程序。对于生成一个或多个触发事件的一个语句，语句级的触 发程序只执行一次。例如，我们用 SQL 的修改语句修改整个表，那么，语句级的修改触发程 序只执行一次，而元组级的触发程序对每个元组都执行一次。在语句级的触发中，我们不能像 在（4）和（5）行所做的那样直接引用旧的或新的元组。相反，可以将旧元组的集合（删除的 元组或修改的元组的旧版本）和新元组的集合（插入的元组或修改的元组的新版本）作为两个 关系来引用。我们用诸如 OLD-TABLE AS Oldstuff 或 NEW-TABLE AS Newstuff 这样的说明来 代替（4）行和（5）行的说明。像上面定义的那样，Oldstuff 命名了包含所有旧元组的关系， 而 Newstuff 则指向包含所有新元组的关系。
3.3 数据操纵
数据操纵包括数据插入和数据更新。 3.3.1 数据插入 语句格式： INSERT INTO<表名>[(<属性列 1>[,<属性列 2>…)] VALUES(<常量 1>[,<常量 2>]…) 功能：将新元组插入指定表中。 例 3.13 将一个新雇员记录（职工号：2008020；姓名：陈冬；性别：男；所在部门：一 车间；年龄：28 岁）插入到 Employee 表中。INSERT INTO Employee VALUES ('2008020','陈冬','男','一车间',28);
没有指定属性列，表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一 致，如果只指定部分属性列，则插入的元组在其余属性列上取空值。 VALUES 子句提供的值必须与 INTO 子句匹配，包括值的个数和值的类型。 DBMS 在执行插入语句时会检查所插入的元组是否破坏表上已定义的完整性规则，即实 体完整性和参照完整性。破坏完整性规则的内容则拒绝插入。 3.3.2 数据更新 数据更新包括数据修改和数据删除。 （1）数据修改。 语句格式： UPDATE <表名> SET <列名>=<表达式>[,<列名>=<表达式>]… [WHERE <条件>];
功能：修改指定表中满足 WHERE 子句条件的元组。SET 子句指定修改方式、要修改的 列、修改后取值。WHERE 子句指定要修改的元组，缺省表示要修改表中的所有元组。
例 3.14 将雇员 2008001 的年龄改为 22 岁。
UPDATE Employee SET Age=22 WHERE Number='2008001';
DBMS 在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则，包括实体 完整性、主键不允许修改、用户定义的完整性、NOT NULL 约束、UNIQUE 约束、值域约束 等。若破坏这些规则，则拒绝修改。 （2）数据删除。 语句格式： DELETE FROM<表名> [WHERE<条件>]; 功能：删除指定表中满足 WHERE 子句条件的元组。WHERE 子句指定要删除的元组，缺 省表示要修改表中的所有元组。 DBMS 在执行删除语句时会检查所删除的元组是否破坏表上已定义的完整性规则，主要 是参照完整性。一般有三种处理方式：级联删除、受限删除、置空值删除，前面已经讲过。
3.4 数据检索
语句格式： SELECT[ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]… FROM<表名或视图名>[,<表名或视图名>]… [WHERE<条件表达式>][GROUP BY<列名 1>[HAVING<条件表达式>]] [ORDER BY<列名 2>[ASC|DESC]];
（1）SELECT 子句：指明要检索的结果集的目标列。目标列可以是直接从数据源中数据 投影得到的字段，也可以是与字段相关的表达式或数据统计的函数表达式、常量。如果使用了 两个基本表（或视图）中相同的列名，要在列名前面加表名限定，即使用“<表名>.<列名>”。 （2）FROM 子句：指明从哪（几）个表（视图）中进行数据检索。表（视图）间用“,” 进行分隔，如果查询使用的基本表或视图不在当前数据库中，还需要在表或视图前加上数据库 名加以说明，即使用“数据库名.表名”的形式表示。 （3）WHERE 子句（行条件子句）：通过条件表达式指明返回 FROM 子句中给出的列必 须满足的标准。DBMS 在处理语句时以元组为单位，逐个考察每个元组是否满足条件，将不 满足条件的元组过滤掉。
（4）GROUP BY 子句（分组子句）：对满足 WHERE 子句的行指明按照 SELECT 子句中 所选择的某个（几个）列的值对整个结果集进行分组。GROUP BY 子句使得同组的元组集中 在一起，也使数据能够分组进行统计。
（5）HAVING 子句（分组条件子句）：GROUP BY 子句后可以带上 HAVING 条件子句组 表达组选择条件，组选择条件为带有函数的条件表达式，它决定着整个组记录的取舍条件。
个排序列进行排序。每个排序列后面可以跟一个排序请求。ORDER BY 子句仅对检索的数据 的显示有影响，并不改变表中行的内部顺序。 3.4.1 单表查询 单表查询的查询仅涉及一个表，是一种最简单的查询操作。又可以分成五种情况：选 择表中的若干列、选择表中的若干元组、对查询结果排序、使用集函数查询、对查询结果 分组。 （1）选择表中的若干列。首先，可以查询表中的指定列。 例 3.15 查询全体雇员的职工号与姓名。
SELECT Number,Name FROM Employee;
当然也可以查询表中的全部列，查询表中的全部列时可以将表中的全部列名列出，也可 以简单地用*代替。
例 3.16 查询全体雇员的详细记录。
SELECT * FROM Employee;
还可以查询经过计算的值，此时 SELECT 子句的<目标列表达式>为表达式、算术表达式、 字符串常量、函数、列别名等。
例 3.17 查询全体雇员的姓名及其出生年份。
SELECT Name,2008-Age FROM Employee;
输出结果： Name 2008-Age 张鹏 1976 李敏 1977 王名 1978 周青 1978 再看一个例子，该例将一个常量串表达式放入查询结果。 例 3.18 查询全体雇员的姓名、出生年份和所在部门。
SELECT Name,'Year of Birth: ',2008-Age, Department FROM Employee;
输出结果：
Name 'Year of Birth:' 2008-Age Department 张鹏 Year of Birth: 1976 二车间 李敏 Year of Birth: 1977 一车间 王名 Year of Birth: 1978 三车间 周青 Year of Birth: 1977 一车间
查询时也可以使用列别名改变查询结果的列标题。请看下面的例子：
SELECT Name NAME,'Year of Birth: 'BIRTH,2008-Age BIRTHDAY, DEPARTMENT FROM Employee;
输出结果：
NAME BIRTH BIRTHDAY DEPARTMENT 张鹏 Year of Birth: 1976 二车间 李敏 Year of Birth: 1977 一车间 王名 Year of Birth: 1978 三车间 周青 Year of Birth: 1977 一车间
（2）选择表中的若干元组。选择表中的若干元组可以消除取值重复的行，此时可以在 SELECT 子句中使用 DISTINCT 短语。
假设雇员工作量 WorkAmount 表中有下列数据： Number JobNumber Amount
2008001 1 92 2008001 2 85 2008001 3 88 2008002 2 90 2008002 3 80 例 3.19 查询已经分配了工作的雇员职工号。
SELECT DISTINCT Number FROM WorkAmount;
输出结果： Number 2008001 2008002 选择元组最常见的就是查询满足条件的元组，条件可以是比较大小的条件，此时在 WHERE 子句的<比较条件>中使用比较运算符=、>、<、>=、<=、!=、<>、!>、!<或逻辑运算 符 NOT + 比较运算符。 例 3.20 查询所有年龄在 20 岁以下的雇员姓名及其年龄。
SELECT Name,Age FROM Employee WHERE Age<20;
选择元组还可以使用谓词 BETWEEN AND 或 NOT BETWEEN AND 确定元组范围。 例 3.21 查询年龄在 20~23 岁（包括 20 岁和 23 岁）之间的雇员姓名、部门和年龄。
SELECT Name,Department,Age FROM Employee WHERE Age BETWEEN 20 AND 23;
要想查询年龄不在 20~23 岁之间的雇员姓名、部门和年龄，只需在例 3.20 的 BETWEEN 前 加上 NOT 即可。 选择元组还可以使用谓词 IN<值表>\NOT IN<值表>来确定一个集合。<值表>是用逗号分 隔的一组取值。 例 3.22 查询一车间、三车间和二车间的雇员姓名和性别。 SELECT Name,Sex FROM Employee WHERE Department IN ('一车间','三车间','二车间');
选择元组时还可以用[NOT] LIKE '<匹配串>'[ESCAPE'<换码字符>']进行字符串匹配。 <匹配串>指定匹配模板，所谓匹配模板就是固定字符串或含通配符的字符串，当匹配模板为 固定字符串时，可以用=运算符取代 LIKE 谓词，用!=或<>运算符取代 NOT LIKE 谓词。
通配符有如下两种： _：也就是下划线，代表任意单个字符。例如，a_b 表示以 a 开头，以 b 结尾的长度 为 3 的任意字符串，如 aab、abb、avb 等都满足该匹配串。 %：也就是百分号，代表任意长度的字符串，也可以是长度为 0 的空串。例如，a%b 表示以 a 开头，以 b 结尾的任意长度的字符串，如 ab、afb、agghhb 等都满足该匹 配串。
ESCAPE 短语：当用户要查询的字符串本身就含有%或_时，要使用 ESCAPE'<换码字符 >'短语对通配符进行转义。
匹配模板为含通配符的字符串的例子如下。
例 3.23 查询所有姓刘的雇员的姓名、职工号和性别。
SELECT Name,Number,Sex FROM Employee WHERE Name LIKE '刘%';
使用换码字符将通配符转义为普通字符的例子如下。
例 3.24 Job 表如表 3-3 所示，查询工作名称以“_1”结尾的工作的详细情况。
SELECT * FROM Job
WHERE Jname LIKE '%\_1' ESCAPE'\'; 表 3-3 Job 表
JobNumber Jname Cpno JPrice 1 铣工_1 5 4 2 车工_2 2 3 铣工_2 1 4 4 磨工_1 6 3 5 刨工_1 7 4 6 车工_2 2 7 铣工_3 6 4
选择元组有时会涉及空值的查询，使用谓词 IS NULL 或 IS NOT NULL 可以进行涉及空值 的查询，“IS NULL”不能用“=NULL”代替。
例 3.25 某些雇员刚刚进入公司，还没有开始工作，所以工作数量是空的。查询那些刚 刚进入公司，还没有开始工作的雇员的职工号。
SELECT Number FROM WorkAmount WHERE Amount IS NULL;
选择元组时可以用逻辑运算符 AND 和 OR 来连接多个查询条件形成多重条件查询，AND 的优先级高于 OR，也可以用括号改变优先级，可用来实现多种其他谓词。
例 3.26 查询二车间且年龄在 20 岁以下的雇员姓名。
SELECT Name FROM Employee
WHERE Department='二车间' AND Age<20;
（3）对查询结果排序。使用 ORDER BY 子句可以按一个或多个属性列排序。升序：ASC； 降序：DESC。默认值为升序。当排序列含空值时，ASC 排序列为空值的元组最后显示；DESC 排序列为空值的元组最先显示。
例 3.27 查询完成了 3 号工作的雇员的职工号及其工作量，查询结果按工作量降序排列。
SELECT Number,Amount FROM WORKAMOUNT WHERE JobNumber='3'
ORDER BY Amount DESC;
例 3.28 查询全体雇员情况，查询结果按所在部门的部门号升序排列，同一部门中的雇 员按年龄降序排列。
SELECT * FROM Employee
ORDER BY Department,Age DESC;
（4）使用集函数。集函数主要有如下 5 类：
COUNT([DISTINCT | ALL]*)或 COUNT([DISTINCT | ALL]<列名>)：用于计数。
SUM([DISTINCT | ALL]<列名>)：用于计算总和。 AVG([DISTINCT | ALL]<列名>)：用于计算平均值。 MAX([DISTINCT | ALL]<列名>)：用于求最大值。 MIN([DISTINCT | ALL]<列名>)：用于求最小值。 DISTINCT 短语：在计算时要取消指定列中的重复值。 ALL 短语：不取消重复值。ALL 为默认值。 例 3.29 查询雇员总人数。 SELECT COUNT(*) FROM Employee; 例 3.30 查询完成了一定的工作量的雇员人数。
SELECT COUNT(DISTINCT Number) FROM WORKAMOUNT
WHERR AMOUNT IS NOT NULL;
注意：用 DISTINCT 以避免重复计算雇员人数。 例 3.31 计算做 1 号工作的雇员平均工作量。
SELECT AVG(Amount) FROM WORKAMOUNT WHERE JobNumber='1'; （5）对查询结果分组。使用 GROUP BY 子句对元组进行分组，细化集函数的作用对象， 如果未对查询结果分组，集函数将作用于整个查询结果，对查询结果分组后，集函数将分别作 用于每个组。 例 3.32 查询各工作号及相应的做该项工作的人数。 SELECT JobNumber,COUNT(Number) FROM WORKAMOUNT GROUP BY JobNumber; 输出结果： JobNumber COUNT(Number) 1 22 2 34 3 44 4 33 5 48 GROUP BY 子句的作用对象是查询的中间结果表，分组方法：按指定的一列或多列值分 组，值相等的为一组。使用 GROUP BY 子句后，SELECT 子句的列名列表中只能出现分组属 性和集函数。使用 HAVING 短语筛选最终输出结果。 例 3.33 查询做了 3 种以上工作并且工作量是 100 以上的雇员的职工号及工作种类数。 SELECT Number,COUNT(*) FROM WORKAMOUNT WHERE Amount>=100
GROUP BY Number HAVING COUNT(*)>=3;
使用 HAVING 短语筛选最终输出结果时，只有满足 HAVING 短语指定条件的组才输出。 注意 HAVING 短语与 WHERE 子句的作用对象不同，WHERE 子句作用于基表或视图，从中 选择满足条件的元组；HAVING 短语只作用于组，从中选择满足条件的组。 3.4.2 复杂查询 （1）连接查询。同时涉及多个表的查询称为连接查询，用来连接两个表的条件称为连接 条件或连接谓词。一般格式如下： [<表名 1>.]<列名 1><比较运算符>[<表名 2>.]<列名 2> 比较运算符有=、>、<、>=、<=、!=等几种。 [<表名 1>.]<列名 1>BETWEEN[<表名 2>.]<列名 2>AND[<表名 2>.]<列名 3> 连接谓词中的列名称为连接字段，连接条件中的各连接字段类型必须是可比的，但不必 是相同的。连接操作的执行过程有以下三种： 1）嵌套循环法（NESTED-LOOP）。首先在表 1 中找到第一个元组，然后从头开始扫描表 2，逐一查找满足连接条件的元组，找到后就将表 1 中的第一个元组与该元组拼接起来，形成 结果表中的一个元组。表 2 全部查找完后，再找表 1 中的第二个元组，然后再从头开始扫描表 2，逐一查找满足连接条件的元组，找到后就将表 1 中的第二个元组与该元组拼接起来，形成 结果表中的一个元组。重复上述操作，直到表 1 中的全部元组都处理完毕 。 2）排序合并法（SORT-MERGE）。首先按连接属性对表 1 和表 2 排序，对表 1 的第一个 元组，从头开始扫描表 2，顺序查找满足连接条件的元组，找到后就将表 1 中的第一个元组与 该元组拼接起来，形成结果表中的一个元组。当遇到表 2 中第一条大于表 1 连接字段值的元组 时，对表 2 的查询不再继续找到表 1 的第二条元组，然后从刚才的中断点处继续顺序扫描表 2， 查找满足连接条件的元组，找到后就将表 1 中的第一个元组与该元组拼接起来，形成结果表中 的一个元组。直到遇到表 2 中大于表 1 连接字段值的元组时，对表 2 的查询不再继续。重复上 述操作，直到表 1 或表 2 中的全部元组都处理完毕为止。 3）索引连接（INDEX-JOIN）：对表 2 按连接字段建立索引，对表 1 中的每个元组依次根 据其连接字段值查询表 2 的索引，从中找到满足条件的元组，找到后就将表 1 中的第一个元组 与该元组拼接起来，形成结果表中的一个元组。 SQL 中连接查询还包括广义笛卡尔积连接查询，也就是不带连接谓词的连接，往往没有 实际意义，所以很少使用。连接查询时任何子句中引用不同表的同名属性时，都必须加表名前 缀。引用唯一属性名时可以加也可以省略表名前缀。 例 3.34 查询每个雇员及其完成工作的情况。 SELECT Employee.*,WORKAMOUNT.* FROM Employee,WORKAMOUNT WHERE Employee.Number=WORKAMOUNT.Number; 假设 Employee 表（如表 3-4 所示）、WORKAMOUNT 表（如表 3-5 所示）分别在表中的 数据。
表 3-4 Employee 表
Number Name Sex Age Department 2008001 张鹏 男 28 二车间 2008002 李敏 女 39 一车间 2008003 王敏 女 38 三车间 2008004 周青 男 39 一车间
表 3-5 WORKAMOUNT 表 Number JobNumber Amount 2008001 1 92 2008001 2 85 2008001 3 88 2008002 2 90 2008002 3 80 等值连接结果表：
Employee.Number Name Sex Age Department WORKAMOUNT.Number JobNumber Amount 2008001 张鹏 男 28 二车间 2008001 1 92 2008001 张鹏 男 28 二车间 2008001 2 85 2008001 张鹏 男 28 二车间 2008001 3 88 2008002 李敏 女 39 一车间 2008002 2 90 2008002 李敏 女 39 一车间 2008002 3 80 3）自身连接。一个表与自己进行连接，称为表的自身连接，这时需要给表起别名以示区 别，由于所有属性名都是同名属性，因此必须使用别名前缀，用以区别该属性来自于哪个表中， 让系统能够区别。 例 3.35 查询每一项工作的间接先修工作（即先行工作的先行工作）。Job 表的内容如表 3-3 所示，其中 Cpno 列就是该工作的先行工作。 SELECT FIRST.JobNumber,SECOND.Cpno FROM Job FIRST,Job SECOND
WHERE FIRST.Cpno=SECOND.JobNumber; FIRST、SECOND 都是 Job 表的别名。 查询结果： 1 7 3 5 5 6 4）外连接（Outer Join）。普通连接操作只输出满足连接条件的元组，外连接操作以指定 表为连接主体，将主体表中不满足连接条件的元组一并输出。 例 3.36 查询每个雇员及其完成一定的工作量的情况，包括没有完成一定的工作量的雇 员，用外连接操作实现。
SELECT Employee.Number,Name,Sex,Age,Department,JobNumber,Amount FROM Employee,WORKAMOUNT
WHERE Employee.Number=WORKAMOUNT.Number(*);
结果如下：
Employee.Number Name Sex Age Department JobNumber Amount 2008001 张鹏 男 20 二车间 1 92 2008001 张鹏 男 20 二车间 2 85 2008001 张鹏 男 20 二车间 3 88 2008002 李敏 女 19 一车间 2 90 2008002 李敏 女 19 一车间 3 80 2008003 王敏 女 18 三车间 2008004 周青 男 19 一车间 在表名后面加外连接操作符（*）或（+）指定非主体表，非主体表有一“万能”的虚行， 该行全部由空值组成，虚行可以和主体表中所有不满足连接条件的元组进行连接。由于虚行各 列全部是空值，因此与虚行连接的结果中，来自非主体表的属性值全部是空值。 外连接分为左外连接和右外连接，左外连接是指外连接符出现在连接条件的左边；右外 连接是指外连接符出现在连接条件的右边。 （2）嵌套查询。一个 SELECT-FROM-WHERE 语句称为一个查询块，将一个查询块嵌套 在另一个查询块的 WHERE 子句或 HAVING 短语的条件中的查询称为嵌套查询。 SELECT Name 外层查询/父查询 FROM Employee WHERE Number IN (SELECT Number 内层查询/子查询 FROM WORKAMOUNT WHERE JobNumber='2'); 嵌套查询的子查询是有限制的，即子查询不能使用 ORDER BY 子句。层层嵌套方式反映 了 SQL 语言的结构化，有些嵌套查询可以用连接运算替代。 嵌套查询分为不相关子查询和相关子查询。不相关子查询即子查询的查询条件不依赖于 父查询；相关子查询的子查询即查询条件依赖于父查询。不相关子查询的求解方法是由里向外 逐层处理，即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找 条件。相关子查询的求解方法是首先取外层查询中表的第一个元组，根据它与内层查询相关的 属性值处理内层查询，若 WHERE 子句返回值为真，则取此元组放入结果表，然后再取外层 表的下一个元组。重复这一过程，直至外层表全部检查完为止。 嵌套查询子查询的谓词可以是带有 IN 谓词的子查询、带有比较运算符的子查询、带有 ANY 或 ALL 谓词的子查询、带有 EXISTS 谓词的子查询。
1）带有 IN 谓词的子查询。 例 3.37 查询与“李敏”在同一个部门工作的雇员。 构造嵌套查询：将第一步查询嵌入到第二步查询的条件中。 SELECT Number,Name,Department FROM Employee WHERE Department IN (SELECT Department
FROM Employee WHERE Name='李敏');
输出结果为：
Number Name Department 2008001 李敏 一车间 2008004 周青 一车间
此查询为不相关子查询。DBMS 求解该查询时也是分步去做的。这个查询可以用自身连 接完成。
SELECT S1.Number,S1.Name,S1.Department FROM Employee S1,Employee S2
WHERE S1.Department=S2.Department AND S2.Name='李敏';
当然，父查询和子查询中的表均可以定义别名进行区分。 SELECT Number,Name,Department FROM Employee S1 WHERE S1.Department IN (SELECT Department FROM Employee S2 WHERE S2.Name='李敏'); 例 3.38 查询完成了工作“铣工_2”的职工号和姓名。
SELECT Number,Name ③在 Employee 关系中取出 Number 和 Name FROM Employee
WHERE Number IN
(SELECT Number ②在 WORKAMOUNT 关系中找出完成了 3 号工作 FROM WORKAMOUNT 的雇员职工号
WHERE JobNumber IN
(SELECT JobNumber ①在 Job 关系中找出“铣工_2”的工作量号， FROM Job 结果为 3 号 WHERE Jname='铣工_2')); 输出结果为： Number Name 2008001 张鹏 2008002 李敏 当然，也可以用连接查询实现上述查询。 2）带有 EXISTS 谓词的子查询。带有 EXISTS 谓词的子查询可以分为以下 4 种情况。 ①EXISTS 谓词。即存在量词，带有 EXISTS 谓词的子查询不返回任何数据，只产生逻 辑真值 true 或逻辑假值 false。若内层查询结果非空，则返回真值；若内层查询结果为空，则 返回假值。由 EXISTS 引出的子查询，其目标列表达式通常都用*，因为带 EXISTS 的子查询 只返回真值或假值，给出列名无实际意义。 例 3.39 用嵌套查询所有完成了 1 号工作的雇员姓名。 SELECT Name FROM Employee WHERE EXISTS (SELECT * FROM WORKAMOUNT /*相关子查询*/
WHERE Number=Employee.Number AND JobNumber='1');
思路分析：本查询涉及 Employee 和 WORKAMOUNT 关系。在 Employee 中依次取每个 元组的 Number 值，用此值去检查 WORKAMOUNT 关系。若 WORKAMOUNT 中存在这样的 元组，其 Number 值等于此 Employee.Number 值，并且其 JobNumber= '1'，则取此 Employee.Name 送入结果关系。
②NOT EXISTS 谓词。
例 3.40 查询没有完成 1 号工作量的雇员姓名。
SELECT Name FROM Employee WHERE NOT EXISTS (SELECT *
FROM WORKAMOUNT
WHERE Number=Employee.Number AND JobNumber='1');
③不同形式查询的替换。.一些带 EXISTS 或 NOT EXISTS 谓词的子查询不能被其他形式 的子查询等价替换。但是所有带 IN 谓词、比较运算符、ANY 和 ALL 谓词的子查询都能用带 EXISTS 谓词的子查询等价替换。 例 3.41 查询与“李敏”在同一个部门工作的雇员。可以用带 EXISTS 谓词的子查询替换。 SELECT Number,Name,Department FROM Employee S1 WHERE EXISTS SELECT * FROM Employee S2
WHERE S2.Department=S1.Department AND S2.Name='李敏';
④用 EXISTS/NOT EXISTS 实现全称量词。 （3）集合查询。标准 SQL 直接支持的集合操作只有并操作（UNION）。而一般商用数据 库支持的集合操作种类有并操作（UNION）、交操作（INTERSECT）、差操作（MINUS）。 1）并操作：将两个以上的查询结果合并起来。 语法形式： <查询块> UNION <查询块> 参加 UNION 操作的各结果表的列数必须相同；对应项的数据类型也必须相同。 例 3.42 查询二车间的雇员及年龄不大于 29 岁的雇员。 SELECT * FROM Employee WHERE Department='二车间' UNION SELECT * FROM Employee WHERE Age<=29; 2）交操作：标准 SQL 中没有提供集合交操作，但可以用其他方法间接实现。 例 3.43 查询完成工作 1 的雇员集合与完成工作 2 的雇员集合的交集。本例实际上是查 询既完成了一定的工作 1 又完成了一定的工作 2 的雇员。
SELECT Number FROM WORKAMOUNT
WHERE JobNumber='1' AND Number IN (SELECT Number FROM WORKAMOUNT WHERE JobNumber='2'); 3）差操作：标准 SQL 中也没有提供集合差操作，同样可以用其他方法间接实现。差操作 允许我们确定存在于一个表中但不存在于另一表中的行。不像 UNION 运算符，MINUS 运算 符没有交换性。也就是说，表 A MINUS 表 B 的结果通常与表 B MINUS 表 A 的结果不同。 例 3.44 查询 Employee 表中职工号与 WORKAMOUNT 表中职工号的差集。本例实际上 是查询未分配工作的职工号。
SELECT DISTINCT Number FROM Employee
WHERE Number NOT IN (SELECT Number FROM WORKAMOUNT); ORDER BY 子句也可以用于对集合查询的最终查询结果排序，但是不能对中间结果排序。 任何情况下，ORDER BY 子句只能出现在最后。 学会了查询语句，我们也可以将查询语句嵌入其他的语句中，从而使其他操作更简洁。 可以嵌入子查询的语句有如下几种： （1）插入子查询结果。 语句格式： INSERT INTO<表名>[(<属性列 1>[,<属性列 2>…)] 子查询; 功能是将子查询结果插入指定表中，见例 3.45。 例 3.45 对每一个部门，求雇员的平均年龄，并把结果存入数据库。 第一步：建表。
CREATE TABLE Deptage
(Department CHAR(15) /* 部门名*/ Avgage SmaLLINT); /*雇员平均年龄*/ 第二步：插入数据。 INSERT INTO Deptage(Department,Avgage) SELECT Department,AVG(Age) FROM Employee GROUP BY Department; INTO 子句（与插入单条元组类似）是指定要插入数据的表名及属性列，属性列的顺序可 与表定义中的顺序不一致；没有指定属性列表示要插入的是一条完整的元组；指定部分属性列 表示插入的元组在其余属性列上取空值。子查询中的 SELECT 子句目标列必须与 INTO 子句 匹配，包括值的个数和值的类型。 （2）带子查询的修改语句。格式与修改语句基本相同，只是在 WHERE 子句中可以嵌入
SELECT 子句。 例 3.46 将二车间全体雇员的工作量置零。 UPDATE WORKAMOUNT SET Amount=0 WHERE '二车间'= (SELECT Department FROM Employee WHERE Employee.Number=WORKAMOUNT.Number); （3）带子查询的删除语句。格式与删除语句基本相同，只是在 WHERE 子句中可以嵌入 SELECT 子句。 例 3.47 删除二车间所有雇员的工作量记录。 DELETE FROM WORKAMOUNT WHERE '二车间'= (SELETE Department FROM Employee WHERE Employee.Number=WORKAMOUNT.Number); 3.4.3 视图 视图是从一个或几个基本表（或视图）中导出的表，它与基本表不同，是一个虚表。数 据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。基 本表中的数据发生变化，从视图中查询出的数据也就随之改变了。从这个意义上讲，视图就像 一个窗口，透过它可以看到数据库中自己感兴趣的数据及其变化。 视图一经定义，就可以和基本表一样被查询、被删除，也可以在一个视图之上再定义新的 视图，但对视图的更新（增、删、改）操作则有一定的限制。视图的维护由 DBMS 自动完成。 合理使用视图能够带来许多好处，主要有以下几方面： （1）视图能够简化用户的操作。可以通过定义视图使用户眼中的数据库结构简单、清晰， 并且可以简化用户的数据查询操作。例如，那些定义了由若干张表连接生成的视图，就将表与 表之间的连接操作对用户隐蔽起来了。用户所做的只是对一个虚表的简单查询，无须处理复杂 的连接操作。 （2）视图使用户能以多种角度看待同一数据。视图机制能使不同的用户以不同的方式看 待同一数据，当许多不同种类的用户使用同一个数据库时，这种灵活性是非常重要的，可以根 据用户的不同在相同的基本表上制作出不同的视图。 （3）视图对重构数据库提供了一定程度的逻辑独立性。关系数据库中，数据库的重构往 往是不可避免的。重构数据库最常见的是将一个基本表“垂直”或“水平”地分成多个表。例 如，将雇员关系 Employee(Number,Name,Sex,Age,Department)分为 SX(Number,Name,Age)和 SY(Number,Sex,Department)两个关系，这时原表 Employee 为 SX 表和 SY 表自然连接的结果。 如建立一个视图 Employee：
CREATE VIEW Employee(Number,Name,Sex,Age,Department) AS
FROM SX,SY WHERE SX.Number=SY.Number; 上例中，尽管数据库的逻辑结构改变了，但应用程序并不需要修改，因为新建立的视图定 义了用户原来的关系，使用户的外模式保持不变，用户的应用程序通过视图仍然能够查找数据。 视图只能在一定程度上提供数据的逻辑独立性，由于对视图的更新是有条件的，因此应 用程序中修改数据的语句可能会因基本表结构的改变而改变。 （4）通过视图隐藏数据。有了视图机制，就可以在设计数据库应用系统时，对不同的 用户定义不同的视图，使数据不出现在不应看到这些数据的用户视图上，视图显示剪裁过的 数据，使数据隐藏在查询的背后。视图还可以建立在其他的视图之上，因而提供了一种额外 的保护机制。 1．建立视图 语句格式： CREATE VIEW<视图名>[(<列名>[,<列名>]…)] AS<子查询>
[WITH CHECK OPTION];
DBMS 执行 CREATE VIEW 语句时只是把视图的定义存入数据字典，并不执行其中的 SELECT 语句。在对视图查询时，按视图的定义从基本表中将数据查出。 组成视图的属性列名全部省略或全部指定；若全部省略，则由子查询中 SELECT 目标列 中的诸字段组成；若明确指定视图的所有列名，则可以是如下几种情况： 某个目标列是集函数或列表达式。 目标列为*。 多表连接时选出了几个同名列作为视图的字段。 需要在视图中为某个列启用新的更合适的名字。 最常见的视图是行列子集视图。行列子集视图就是从单个基本表导出只是去掉了基本表 的某些行和某些列、保留了关键字的视图。 例 3.48 建立一车间雇员的视图。
CREATE VIEW 一车间_Employee AS
SELECT Number,Name,Age FROM Employee
WHERE Department='一车间';
如有 WITH CHECK OPTION 选项，则透过视图进行增删改操作时，不得破坏视图定义中 的谓词条件（即子查询中的条件表达式）。
例 3.49 建立一车间雇员视图，要求透过该视图进行的更新操作只涉及部门雇员。
CREATE VIEW 一车间_Employee AS
SELECT Number,Name,Age FROM Employee
WHERE Department='一车间' WITH CHECK OPTION;
删除操作 DBMS 自动加上 Department='一车间'的条件；插入操作 DBMS 自动检查 Department 属性值是否为'一车间'，如果不是，则拒绝该插入操作；如果没有提供 Department 属性值，则 自动定义 Department 为'一车间'。
视图也可以基于多个基表，看下面的例 3.50。 例 3.50 建立一车间完成了 1 号工作的雇员视图。
CREATE VIEW 一车间_S1(Number,Name,Amount)
AS SELECT Employee.Number, Employee.Name, WORKAMOUNT.Amount FROM Employee,WORKAMOUNT WHERE Department='一车间'AND Employee.Number=WORKAMOUNT.Number AND WORKAMOUNT.JobNumber='1'; 视图还可以基于视图，如例 3.51 所示。 例 3.51 建立一车间完成了 1 号工作且工作量在 90 分以上的雇员的视图。 CREATE VIEW 一车间_S2 AS SELECT Number,Name,Amount FROM 一车间_S1 WHERE Amount>=90; 视图中也可以带表达式，如例 3.52 所示。 例 3.52 定义一个反映雇员出生年份的视图。
CREATE VIEW BT_S(Number,Name,Sbirth) AS SELECT Number,Name,2008-Age FROM Employee 在上述视图中设置了一个派生属性列，也称为虚拟列 Sbirth，该属性列是表达式 2008-Age 的视图属性名。这种带表达式的视图必须明确定义组成视图的各个属性列名。 也可以建立分组视图，如例 3.53 所示。 例 3.53 将雇员的职工号及他的平均工作量定义为一个视图。假设 WORKAMOUNT 表 中“工作量”列 Amount 为数字型。
CREATE VIEW E_A(Number,Gavg) AS SELECT Number,AVG(Amount) FROM WORKAMOUNT
GROUP BY Number;
以 SELECT *方式创建的视图可扩充性差，应尽可能避免。请看下面的例 3.54。 例 3.54 将 Employee 表中所有女雇员记录定义为一个视图。
CREATE VIEW F_Employee1(stdnum,name,sex,age,dept) AS SELECT *
FROM Employee WHERE Sex='女';
修改基表 Employee 的结构后，Employee 表与 F_Employee1 视图的映像关系可能被破坏， 导致该视图不能正确工作，可以用属性列表代替*，上述的例子改写如下：
CREATE VIEW F_Employee2(stdnum,name,sex,age,dept) AS SELECT Number,Name,Sex,Age,Department
FROM Employee WHERE Sex='女';
这样一来基表 Employee 增加属性列不会破坏 Employee 表与 F_Employee2 视图的映像 关系。 2．删除视图 语句格式： DROP VIEW <视图名>; 该语句从数据字典中删除指定的视图定义，由该视图导出的其他视图定义仍在数据字典中， 但已不能使用，必须显式删除。删除基表时，由该基表导出的所有视图定义都必须显式删除。 例 3.55 删除视图一车间_S1。 DROP VIEW 一车间_S1; 3．查询视图 从用户角度看，查询视图与查询基本表是相同的，一般 DBMS 实现视图查询的方法有两 种，第一种是实体化视图（View Materialization），该方法先做有效性检查，即检查所查询的视 图是否存在。若存在，则执行视图定义，将视图临时实体化，生成临时表，查询完毕删除被实 体化的视图（临时表）。第二种方法是视图消解法（View Resolution），该方法也是先进行有效 性检查，检查查询的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义，把视 图定义中的子查询与用户的查询结合起来，转换成等价的对基本表的查询，执行修正后的查询。 例 3.56 在一车间雇员的视图中找出年龄小于 40 岁的雇员。 SELECT Number,Age FROM 一车间_Employee WHERE Age<40; 利用视图消解法，转换后的查询语句为： SELECT Number,Age FROM Employee
WHERE Department='一车间' AND Age<40;
有些情况下，视图消解法不能生成正确查询。采用视图消解法的 DBMS 会限制这类查询。 例 3.57 在 E_A 视图中查询平均工作量在 90 以上的雇员职工号和平均工作量。 SELECT * FROM E_A WHERE Gavg>=90; E_A 视图定义如下：
CREATE VIEW E_A(Number,Gavg) AS SELECT Number,AVG(Amount) FROM WORKAMOUNT GROUP BY Number; 利用视图消解法查询转换形成的查询语句如下： SELECT Number,AVG(Amount) FROM WORKAMOUNT WHERE AVG(Amount)>=90 GROUP BY Number; 显然，该语句是错误的，因为 WHERE 子句中是不能用集函数作为条件表达式的。正确 的查询语句应该是： SELECT Number,AVG(Amount)
FROM WORKAMOUNT GROUP BY Number HAVING AVG(Amount)>=90; 在这种情况下，系统可以采用视图实体化的方法来解决。 4．更新视图 从用户的角度看，更新视图与更新基本表相同，DBMS 实现视图更新的方法有视图实体 化法（View Materialization）和视图消解法（View Resolution）。指定 WITH CHECK OPTION 子句后，DBMS 在更新视图时会进行检查，防止用户通过视图对不属于视图范围内的基本表 数据进行更新。 例 3.58 将一车间雇员视图一车间_Employee 中职工号 2008002 的雇员姓名改为“张振”。 UPDATE 一车间_Employee SET Name= '张振' WHERE Number='2008002'; 转换后的语句如下： UPDATE Employee SET Name='张振'
WHERE Number='2008002' AND Department='一车间';
一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本 表的更新。
例如，视图 E_A 为不可更新视图。
CREATE VIEW E_A(Number,Gavg) AS SELECT Number,AVG(Amount) FROM WORKAMOUNT GROUP BY Number; 对于如下更新语句： UPDATE E_A SET Gavg=90 WHERE Number='2008001'; 无论实体化法还是消解法都无法将其转换成对基本表 WORKAMOUNT 的更新，视图的可更新 性分为理论上可更新和理论上不可更新。实际系统对视图更新的限制是大部分的 DBMS 允许 对行列子集视图进行更新，对其他类型视图的更新不同系统有不同的限制。 DB2 对视图更新的限制如下： 若视图是由两个以上基本表导出的，则此视图不允许更新。 若视图的字段来自字段表达式或常数，则不允许对此视图执行 INSERT 和 UPDATE 操作，但允许执行 DELETE 操作。 若视图的字段来自集函数，则此视图不允许更新。 若视图定义中含有 GROUP BY 子句，则此视图不允许更新。 若视图定义中含有 DISTINCT 短语，则此视图不允许更新。 若视图定义中有嵌套查询，并且内层查询的 FROM 子句中涉及的表也是导出该视图 的基本表，则此视图不允许更新。 一个不允许更新的视图上定义的视图也不允许更新。
5．视图的作用 （1）视图能够简化用户的操作。当视图中数据不是直接来自基本表时，定义视图能够简 化用户操作。 （2）视图使用户能以多种角度看待同一数据。视图机制能使不同用户以不同方式看待同 一数据，适应数据库共享的需要。 （3）视图对重构数据库提供了一定程度的逻辑独立性。 例如：数据库逻辑结构发生改变，雇员关系 Employee(Number,Name,Sex,Age,Department) “垂直”地分成两个基本表： SX(Number,Name,Age) SY(Number,Sex,Department) 这时原表 Employee 为 SX 和 SY 表自然连接的结果。若建立一个视图 Employee：
CREATE VIEW Employee(Number,Name,Sex,Age,Department) AS SELECT SX.Number,SX.Name,SY.Sex,SX.Age,SY.Department FROM SX,SY WHERE SX.Number=SY.Number; 使用户的外模式保持不变，从而对原 Employee 表的查询程序不必修改。 视图只能在一定程度上提供数据的逻辑独立性，由于对视图的更新是有条件的，因此应 用程序中修改数据的语句可能仍会因基本表结构的改变而改变。 （4）视图能够对机密数据提供安全保护。对不同用户定义不同视图，使每个用户只能看 到权限范围内的数据，通过 WITH CHECK OPTION 对关键数据定义操作时间限制。
例 3.59 建立 1 号工作的完成情况视图，并要求透过该视图进行的更新操作只涉及 1 号 工作，同时对该视图的任何操作只能在工作时间进行。
CREATE VIEW 一车间_WORKAMOUNT AS
SELECT Number,JobNumber,Amount FROM WORKAMOUNT
WHERE JobNumber='1'
AND TO_CHAR(SYSDATE,'HH24') BETWEEN 9 AND 17 AND TO_CHAR(SYSDATE,'D') BETWEEN 2 AND 6 WITH CHECK OPTION;
3.5 事务处理
事务（Transaction）是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做， 是一个不可分割的工作单位，反映的是现实世界中需要以完整单位提交的一项工作。事务和程 序是两个概念，在关系数据库中，一个事务可以是一条 SQL 语句、一组 SQL 语句或整个程序， 一个应用程序通常包含多个事务。事务是恢复和并发控制的基本单位。 SQL 中显式定义方式如下： BEGIN TRANSACTION SQL 语句 1 SQL 语句 2…
COMMIT (ROLLBACK)
BEGIN TRANSACTION 是指事务的开始、事务的结束有两种方式。其中 COMMIT 表示 事务正常结束，即提交事务的所有操作（读+更新），事务中所有对数据库的更新永久生效。 ROLLBACK 表示事务异常终止，事务运行的过程中发生了故障，不能继续执行，回滚事务的 所有更新操作，事务滚回到开始时的状态。当用户没有显式地定义事务时，DBMS 按默认规 定自动划分事务。 事务具有 4 个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和 持续性（Durability）。这 4 个特性简称 ACID 特性。 （1）原子性（Atomicity）。事务是数据库的逻辑工作单位，事务中包括的诸操作要么都 做，要么都不做。也就是说，事物是作为一个整体的单位被处理，不可以被分割。 （2）一致性（Consistency）。事务执行的结果必须是使数据库从一个一致性状态变到另一 个一致性状态。数据库中只包含成功事务提交的结果的状态就称数据库处于一致性状态。数据 库中包含失败事务的结果时，就称数据库处于不一致状态。例如银行转账，从账号 A 中取出一 万元，存入账号 B。定义一个事务，该事务包括两个操作，这两个操作要么全做，要么全不做， 全做或者全不做，数据库都处于一致性状态。如果只做一个操作，数据库就处于非一致性状态。 （3）隔离性（Isolation）。对并发执行而言，一个事务的执行不能被其他事务干扰，一 个事务内部的操作及使用的数据对其他并发事务是隔离的，并发执行的各个事务之间不能互 相干扰。 （4）持续性（Durability）。持续性也称为永久性（Permanence），一个事务一旦提交，数据 库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其执行结果有任何影响。
本章小结
SQL 功能丰富、使用方便、灵活、语言简洁易学，所以它的应用系统范围广，最终发展 成为关系数据库标准语言。各个数据库厂商支持的 SQL 语言在遵循标准的基础上常常作不同 的扩充或修改。在介绍标准 SQL 的基础上，为了让读者易于理解一些基本概念，本章用一些 实例作了部分讲解分析。 本章从 SQL 语言的数据定义、数据查询、数据更新、数据控制四大部分出发。用比较通 俗易懂的实例介绍了这几部分的内容，望读者通过具体上机实践很好地理解这些内容。同时深 入理解数据库系统的基本概念，使这些概念更加具体、更加丰富。 视图具有很多优点，合理地使用视图可以提高数据库系统的性能，读者应掌握它。习 题
1．简述 SQL 语言的特点。 2．什么是表？表的作用是什么？ 3．什么是约束？约束的种类和作用是什么？ 4．什么是存储过程？什么是触发器？简述它们的区别与联系。5．设有下列关系模式：
S(S#,SNAME,CTYE)。S 表示 SUPPLIER（供应商），S#为供应商代号，SNAME 为供应商 名字，CITY 为供应商所在城市，主键为 S#。
P(P#,PNAME,COLOR,WEIGHT)。P 表示 PART（零件），P#为零件代号，PNAME 为零件 名字，COLOR 为零件颜色，WEIGHT 为零件重量，主键为 P#。
J(J#,JNAME,CITY)。J 表示 JOB（工程），J#为工程编号，JNAME 为工程名字，CITY 为 工程所在城市，主键为 J#。 SPJ(S#,P#,J#,QTY)。SPJ 表示供应关系，S#是为指定工程提供零件的供应者代号，P#为所 提供的零件代号，J#为工程编号，QTY 表示提供的零件数量，主键为 S#、P#、J#、外键分别 为 S#、P#、J#。 下表表示供应商（S）、零件（P）、工程（J）数据库，试做以下各题。 S S# SNAME CITY S1 N1 上海 S2 N2 北京 S3 N3 北京 S4 N4 上海 S5 N5 南京 P
P# PNAME COLOR WEIGHT P1 PN1 红 12 P2 PN2 绿 18 P3 PN3 蓝 20 P4 PN4 红 13 P5 PN5 蓝 11 P6 PN6 红 15 J J# JNAME CITY J1 JN1 上海 J2 JN2 广州 J3 JN3 南京 J4 JN4 南京 J5 JN5 上海 J6 JN6 武汉 J7 JN7 上海
SPJ S# P# J# QTY S1 P1 J1 200 S1 P1 J4 700 S2 P3 J1 400 S2 P3 J2 200 S2 P3 J3 200 S2 P3 J4 500 S2 P3 J5 600 S2 P3 J6 400 S2 P3 J7 800 S2 P5 J2 100 S3 P3 J1 200 S3 P4 J2 500 S4 P6 J3 300 S5 P6 J7 300 S5 P2 J2 200 S5 P2 J4 100 S5 P5 J5 500 S5 P5 J7 100 S5 P6 J2 200 S5 P1 J4 1000 S5 P3 J4 1200 S5 P4 J4 800 S5 P5 J4 400 S5 P6 J4 500 （1）用 SQL 的 DDL 语言创建 S、P、J、SPJ 共 4 个基本表。 （2）写出完成下列各题的语句序列，并上机验证、打印出操作结果。 1）取出所有工程的全部细节。 2）取出所在城市为上海的所有工程的全部细节。 3）取出重量最轻的那些零件的号码。 4）取出为工程 J1 提供零件的供应商的代号。 5）取出为工程 J1 提供零件 P1 的供应商的代号。 6）取出由供应商 S1 提供零件的工程名称。 7）取出供应商 S1 提供的零件的颜色。 8）取出为工程 J2 提供零件的供应商的代号。
9）取出为工程 J1 提供红色零件的供应商的代号。 10）取出为所在城市为上海的工程提供零件的供应商的代号。 11）取出为所在城市为上海或北京的工程提供红色零件的供应商的代号。 12）取出供应商与工程所在城市相同的供应商提供的零件的代号。 13）取出上海的供应商提供给上海的任一工程的零件的代号。 14）取出至少一个和工程不在同一城市的供应商提供零件的工程的代号。 15）取出上海供应商不提供任何零件的工程的代号。 6．什么是基本表？什么是视图？两者的区别和联系是什么？ 7．简述视图的优点。 8．所有的视图是否都可以更新？为什么？ 9．哪类视图是可以更新的？哪类视图是不可更新的？各举一例说明。 10．请为三建工程项目建立一个供应情况的视图，包括供应商代码（SNUM）、零件代码 （PNUM）、供应数量（QTY），针对该视图完成下列查询： （1）找出三建工程项目使用的各种零件代码及其数量。 （2）找出供应商的供应情况。 11．什么是事务？事务的特性是什么？
