关系数据库标准语言SQL

  结构化查询语言（Structured Query Language，SQL）‌是关系数据库的标准语言，也是一个通用的、功能极强的关系数据库语言。其功能不仅仅是查询，而是包括数据库模式创建、数据库数据的插入与修改、数据库安全性完整性定义与控制等一系列功能。本章详细介绍SQL的基本功能，并进一步讲述关系数据库的基本概念。

SQL概述

  自SQL成为国际标准语言以后，各个数据库厂家纷纷推出各自的SQL软件或与SQL的接口软件。这就使大多数数据库均用SQL作为共同的数据存取语言和标准接口，使不同数据库系统之间的互操作有了共同的基础。SQL已成为数据库领域中的主流语言，其意义十分重大。有人把确立SQL为关系数据库语言标准及其后的发展称为是一场革命。

SQL的产生与发展

  SQL是在1974年由Boyce和Chamberlin提出的，最初叫Sequel，并在IBM公司研制的关系数据库管理系统原型System R上实现。由于SQL简单易学，功能丰富，深受用户及计算机工业界欢迎，因此被数据库厂商所采用。经各公司的不断修改、扩充和完善，SQL得到业界的认可。1986年10月，美国国家标准（American National Standard Institute，ANSI）的数据库委员会X3H2批准了SQL作为关系数据库语言的美国标准，同年公布了SQL标准文本（简称SQL-86）。1987年，国际标准化组织(International Organizationfor Standardization，ISO)也通过了这一标准。
  SQL标准从公布以来随数据库技术的发展而不断发展、不断丰富。.1是SQL标准的进展过程。

表1 SQL标准的进展过程

  2008年、2011年又对SQL2003做了一些修改和补充。可以发现，SQL标准的内容越来越丰富，也越来越复杂。SQL99合计超过1700页。SQL/86和SQL/89都是单个文档。SQL/92和SQL99已经扩展为一系列开放的部分。例如，SQL/92除了SQL基本部分外还增加了SQL调用接口、SQL永久存储模块；而SQL99则进一步扩展为框架、SQL基础部分、SQL调用接口、SQL永久存储模块、SQL宿主语言绑定、SQL外部数据的管理和SQL 对象语言绑定等多个部分。
  目前，没有一个数据库系统能够支持SQL标准的所有概念和特性。大部分数据库系统能支持SQL/92标准中的大部分功能以及SQL99、SQL2003中的部分新概念。同时，许多软件厂商对SQL基本命令集还进行了不同程度的扩充和修改，又可以支持标准以外的一些功能特性。本文不是介绍完整的SQL，而是介绍SQL的基本概念和基本功能。因此，在使用具体系统时要查阅各产品的用户手册。

SQL的特点

  SQL之所以能够为用户和业界所接受并成为国际标准，是因为它是一个综合的、功能极强同时又简洁易学的语言。SQL集数据查询(data query)、数据操纵(data manipulation)、数据定义(data definition)和数据控制(data control)功能于一体，其主要特点包括以下几部分。

综合统一

  数据库系统的主要功能是通过数据库支持的数据语言来实现的。
  非关系模型（层次模型、网状模型）的数据语言一般都分为：
  (1)模式数据定义语言（Schema Data Definition Language，模式 DDL）。
  (2)外模式数据定义语言（Subschema Data Definition Language，外模式DDL或子模式DDL）。
  (3)数据存储有关的描述语言(Data Storage Description Language, DSDL)。
  (4)数据操纵语言(Data Manipulation Language, DML)。
  它们分别用于定义模式、外模式、内模式和进行数据的存取与处置。当用户数据库投入运行后，如果需要修改模式，必须停止现有数据库的运行，转储数据，修改模式并编译后再重装数据库，十分麻烦。

  SQL集数据定义语言、数据操纵语言、数据控制语言的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动，包括以下一系列操作要求：
  ◦定义和修改、删除关系模式，定义和删除视图，插入数据，建立数据库。
  ◦对数据库中的数据进行查询和更新。
  ◦数据库重构和维护。
  ◦数据库安全性、完整性控制，以及事务控制。
  ◦嵌入式SQL和动态SQL定义。
  这就为数据库应用系统的开发提供了良好的环境。特别是用户在数据库系统投入运行后还可根据需要随时地、逐步地修改模式，并不影响数据库的运行，从而使系统具有良好的可扩展性。
  另外，在关系模型中实体和实体间的联系均用关系表示，这种数据结构的单一性带来了数据操作符的统一性，查找、插入、删除、更新等每一种操作都只需一种操作符， 从而克服了非关系系统由于信息表示方式的多样性带来的操作复杂性。例如，在DBTG网状数据库系统中，需要两种插入操作符：STORE用来把记录存入数据库，CONNECT用来把记录插入系值（系值是网状数据库中记录之间的一种联系方式）以建立数据之间的联系。

高度非过程化

  非关系数据模型的数据操纵语言是“面向过程”的语言，用“过程化”语言完成某项请求必须指定存取路径。而用SQL进行数据操作时，只要提出“做什么”，而无须指明“怎么做”，因此无须了解存取路径。存取路径的选择以及SQL的操作过程由系统自动完成。 这不但大大减轻了用户负担，而且有利于提高数据独立性。

面向集合的操作方式

  非关系数据模型采用的是面向记录的操作方式，操作对象是一条记录。例如查询所有平均成绩在80分以上的学生姓名，用户必须一条一条地把满足条件的学生记录找出来（通常要说明具体处理过程，即按照哪条路径，如何循环等）。而SQL采用集合操作方式，不仅操作对象、查找结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。

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

  SQL既是独立的语言，又是嵌入式语言。作为独立的语言，它能够独立地用于联机交互的使用方式，用户可以在终端键盘上直接键入SQL命令对数据库进行操作；作为嵌入式语言，SQL语句能够嵌入到高级语言（例如C、C++、Java）程序中，供程序员设计程序时使用。而在两种不同的使用方式下，SQL的语法结构基本上是一致的。这种以统一的语法结构提供多种不同使用方式的做法，提供了极大的灵活性与方便性。

语言简洁、易学易用

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

表2 SQL的动词

SQL的基本概念

  支持SQL的关系数据库管理系统同样支持关系数据库三级模式结构。如图1所示，其中外模式包括若干视图(view)‌和部分基本表(base table)，数据库模式包括若干基本表，内模式包括若干存储文件(stored file)。

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

  用户可以用SQL对基本表和视图进行查询或其他操作，基本表和视图一样，都是关系。
  基本表是本身独立存在的表，在关系数据库管理系统中一个关系就对应一个基本表。一个或多个基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理结构对最终用户是隐蔽的。
  视图是从一个或几个基本表导出的表。它本身不独立存储在数据库中，即数据库中只存放视图的定义而不存放视图对应的数据。这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。视图在概念上与基本表等同，用户可以在视图上再定义视图。
  下面将逐一介绍各SQL语句的功能和格式。为了突出基本概念和基本功能，略去了许多语法细节。各个关系数据库管理系统产品在实现标准SQL时各有差别，与SQL标准的符合程度也不相同，一般在85%以上。因此，具体使用某个关系数据库管理系统产品时，还应参阅系统提供的有关手册。

学生—课程数据库

  本章以学生—课程数据库为例来讲解SQL的数据定义、数据操纵、数据查询和数据控制语句。
  为此，首先要定义一个学生—课程模式S-T（见例1）。学生-课程数据库中包括以下三个表，它们的定义见例5、例6和例7。
  ◦学生表：Student(Sno,Sname,Ssex,Sage,Sdept)
  ◦课程表：Course(Cno,Cnme,Cpno,Ccredit)
  ◦学生选课表：SC(Sno,Cno,Grade)
  关系的主码加下划线表示。各个表中的数据示例如表3、表4、表5所示。

表3 Student表格中的数据示例

表4 Course表格中的数据示例

表5 SC表格中的数据示例

数据定义

  关系数据库系统支持三级模式结构，其模式、外模式和内模式中的基本对象有模式、表、视图和索引等。因此SQL的数据定义功能包括模式定义、表定义、视图和索引的定义, 如表6所示。

表6 SQL的数据定义语句

  SQL标准不提供修改模式定义和修改视图定义的操作。用户如果想修改这些对象，只能先将它们删除然后再重建。SQL标准也没有提供索引相关的语句，但为了提高查询效率，商用关系数据库管理系统通常都提供了索引机制和相关的语句，如表6中创建、删除和修改索引等。
  在早期的数据库系统中，所有数据库对象都属于一个数据库，也就是说只有一个命名空间。现代的关系数据库管理系统提供了一个层次化的数据库对象命名机制，如图2所示。一个关系数据库管理系统的实例(instance)‌中可以建立多个数据库,一个数据库中可以建立多个模式，一个模式下通常包括多个表、视图和索引等数据库对象。

图2 数据库对象命名机制的层次结构

模式的定义与删除

定义模式

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

1

CREATE SCHEMA <模式名> AUTHORIZATION <用户名>;

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

  例1：为用户WANG定义一个学生—课程模式S-T。

1

CREATE SCHEMA "S-T" AUTHORIZATION WANG;

  例2：

1

CREATE SCHEMA AUTHORIZATION WANG;

  该语句没有指定<模式名>，所以<模式名>隐含为用户名WANG。

  定义模式实际上定义了一个命名空间，在这个空间中可以进一步定义该模式包含的数据库对象，例如基本表、视图、索引等。
  这些数据库对象可以用表6中相应的CREATE语句来定义。
  目前，在CREATE SCHEMA中可以接受CREATE TABLE、CREATE VIEW和GRANT子句。也就是说用户可以在创建模式的同时在这个模式定义中进一步创建基本表、视图，定义授权。即

1

CREATE SCHEMA <模式名> AUTHORIZATION <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>];

  例3：为用户ZHANG创建一个模式TEST，并且在其中定义一个表TAB1。

1
2
3
4
5
6
7

CREATE SCHEMA TEST AUTHORIZATION ZHANG
CREATE TABLE TAB 1 (COL 1 SMALLINT,
                    COL2 INT,
                    COL3 CHAR(20),
                    COL4 NUMERIC(10,3),
                    COL5 DECIMAL(5,2) 
                    );

删除模式

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

1

DROP SCHEMA <模式名><CASCADE|RESTRICT>;

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

  例4：

1

DROP SCHEMA ZHANG CASCADE;

  该语句删除了模式ZHANG，同时，该模式中已经定义的表TAB1也被删除了。

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

定义基本表

  创建了一个模式就建立了一个数据库的命名空间，一个框架。在这个空间中首先要定义的是该模式包含的数据库基本表。
  SQL语言使用CREATE TABLE语句定义基本表，其基本格式如下：

1
2
3
4

CREATE TABLE <表名>(<列名><数据类型> [列级完整性约束条件]
                   [<列名><数据类型> [列级完整性约束条件]]
                   ...
                   [,<表级完整性约束条件>]);

  建表的同时通常还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中，当用户操作表中数据时由关系数据库管理系统自动检查该操作是否违背这些完整性约束条件。如果完整性约束条件涉及该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。

  例5：建立一个“学生”表Student。

1
2
3
4
5
6
7

CREATE TABLE Student
    (Sno CHAR(9) PRIMARY KEY, /*列级完整性约束条件，Sno是主码*/
    Sname CHAR(20) UNIQUE,    /* Sname 取唯一值*/
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20)
    );

  系统执行该CREATE TABLE语句后，就在数据库中建立一个新的空“学生”表Student，并将有关“学生”表的定义及有关约束条件存放在数据字典中。

  例6：建立一个课程表Course。

1
2
3
4
5
6
7
8

CREATE TABLE Course
    (Cno CHAR(4) PRIMARY KEY, /*列级完整性约束条件，Cno是主码*/
     Cname CHAR(40) NOT NULL, /*列级完整性约束条件，Cname不能取空值*/
     Cpno CHAR(4),            /* Cpno的含义是先修课*/
     Ccredit SMALLINT,
     FOREIGN KEY (Cpno) REFERENCES Course(Cno)
        /*表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno。*/
    );

  本例说明参照表和被参照表可以是同一个表。

  例7：建立学生选课表SC。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE SC 
    (Sno CHAR(9),
    Cno CHAR(4),
    Grade SMALLINT,
    PRIMARY KEY (Sno,Cno), /*主码由两个属性构成，必须作为表级完整性进行定义*/
    FOREIGN KEY (Sno) REFERENCES Student(Sno), 
        /*表级完整性约束条件，Sno是外码，被参照表是Student */
    FOREIGN KEY (Cno) REFERENCES Course(Cno)
        /*表级完整性约束条件，Cno是外码，被参照表是Course*/
    );

数据类型

  关系模型中一个很重要的概念是域。每一个属性来自一个域，它的取值必须是域中的值。在SQL中域的概念用数据类型来实现。定义表的各个属性时需要指明其数据类型及长度。SQL标准支持多种数据类型，表7列出了几种常用数据类型。要注意，不同的关系数据库管理系统中支持的数据类型不完全相同。

表7 数据类型

  一个属性选用哪种数据类型要根据实际情况来决定，一般要从两个方面来考虑，一是取值范围，二是要做哪些运算。例如，对于年龄(Sage)属性，可以采用CHAR(3)作为数据类型，但考虑到要在年龄上做算术运算（如求平均年龄），所以要采用整数作为数据类型，因为在CHAR(n)数据类型上不能进行算术运算。整数又有长整数和短整数两种，因为一个人的年龄在百岁左右，所以选用短整数作为年龄的数据类型。

模式与表

  每一个基本表都属于某一个模式，一个模式包含多个基本表。当定义基本表时一般可以有三种方法定义它所属的模式。例如在例3.1中定义了一个学生—课程模式S-T。现在要在S-T中定义Student、Cource、SC等基本表。

  方法一，在表名中明显地给出模式名。

1
2
3

CREATE TABLE "S-T".Student(···); /* Student所属的模式是S-T */
CREATE TABLE "S-T".Course(···);  /* Course所属的模式是S-T */
CREATE TABLE "S-T".SC(···);      /* SC所属的模式是S-T */

  方法二，在创建模式语句中同时创建表，如例3所示。
  方法三，设置所属的模式，设置所属的模式，这样在创建表时表名中不必给出模式名。

  当用户创建基本表（其他数据库对象也一样）时若没有指定模式，系统根据搜索路径(search path)来确定该对象所属的模式。
  搜索路径包含一组模式列表，关系数据库管理系统会使用模式列表中第一个存在的模式作为数据库对象的模式名。若搜索路径中的模式名都不存在，系统将给出错误。使用下面的语句可以显示当前的搜索路径：

1

SHOW search_path;

  搜索路径的当前默认值是$user,PUBLIC。其含义是首先搜索与用户名相同的模式名，如果该模式名不存在，则使用PUBLIC模式。
  数据库管理员也可以设置搜索路径，例如：

1

SET search_path TO "S-T",PUBLIC;

  然后，定义基本表：

1

CREATE TABLE Student (···);

  实际结果是建立了S-T.Student基本表。因为关系数据库管理系统发现搜索路径中第一 个模式名S-T存在，就把该模式作为基本表Student所属的模式。

修改基本表

  随着应用环境和应用需求的变化，有时需要修改已建立好的基本表。SQL语言用ALTER TABLE语句修改基本表，其一般格式为

1
2
3
4
5
6

ALTER TABLE <表名>
    [ADD [COLUMN] <新列名> <数据类型> [完整性约束]]
    [ADD <表级完整性约束>]
    [DROP [COLUMN] <列名> [CASCADE|RESTRICT]]
    [DROP CONSTRAINT <完整性约束名> [CASCADE|RESTRICT]]
    [ALTER COLUMN <列名> <数据类型>];

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

  例8：向Student表增加“入学时间”列，其数据类型为日期型。

1

ALTER TABLE Student ADD Sentrance DATE;

  不论基本表中原来是否已有数据，新增加的列一律为空值。

  例9：]将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。

1

ALTER TABLE Student ALTER COLUMN Sage INT;

  例10：增加课程名称必须取唯一值的约束条件。

1

ALTER TABLE Course ADD UNIQUE(Cname);

删除基本表

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

1

DROP TABLE <表名> [RESTRICT|CASCADE];

  若选择RESTRICT，则该表的删除是有限制条件的。欲删除的基本表不能被其他表的约束所引用（如CHECK，FOREIGN KEY等约束），不能有视图，不能有触发器(trigger)，不能有存储过程或函数等。如果存在这些依赖该表的对象，则此表不能被删除。
  若选择CASCADE，则该表的删除没有限制条件。在删除基本表的同时，相关的依赖对象，例如视图，都将被一起删除。
  默认情况是RESTRICT。

  例11：删除Student表。

1

DROP TABLE Student CASCADE;

  基本表定义一旦被删除，不仅表中的数据和此表的定义将被删除，而且此表上建立的索引、触发器等对象一般也都将被删除。有的关系数据库管理系统还会同时删除在此表上建立的视图。如果欲删除的基本表被其他基本表所引用，则这些表也可能被删除。例如SC表通过外码Sno引用Student，则执行例例11后Student表被删除，SC也被级联删除。因此执行删除基本表的操作一定要格外小心。

  例12：若表上建有视图，选择RESTRICT时表不能删除；选择CASCADE时可以删除表，视图也自动被删除。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

CREATE VIEW IS_Student /* Student表上建立视图 */
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS';

DROP TABLE Student RESTRICT; /* 删除Student表 */
--ERROR: cannot drop table Student because other objects depend on it 
/* 系统返回错误信息，存在依赖该表的对象，此表不能被删除 */
                             
DROP TABLE Student CASCADE; /* 删除 Student 表 */
--NOTICE: drop cascades to view IS Student
/* 系统返回提示，此表上的视图也被删除 */

SELECT * FROM IS_Student;
--ERROR: relation "IS_Student" does not exist

  注意：不同的数据库产品在遵循SQL标准的基础上具体实现细节和处理策略会与标准有差别。

  下面就SQL 2011标准对DROP TABLE的规定，对比分析Kingbase ES、Oracle 12c Release 1(12.1)、MS SQL Server 2012这三种数据库产品对DROP TABLE的不同处理策略。
  表8中的R表示RESTRICT，即DROP TABLE <基本表名> RESTRICT；C表示CASCADE，即DROP TABLE <基本表名> CASCADE；其中Oracle 12c没有RESTRICT选项；SQL Server没有RESTRICT和CASCADE选项。

表8 DROP TABLE时，SQL2011与3个关系数据库管理系统的处理策略比较

  “×”表示不能删除基本表，“√”表示能删除基本表，“保留”表示删除基本表后，还保留依赖对象。从比较表中可以知道：
  (1)对于索引，删除基本表后，这三个关系数据库管理系统都自动删除该基本表上己经建立的所有索引。
  (2)对于视图，Oracle 12c与SQL Server 2012是删除基本表后，还保留此基本表上的视图定义，但是己经失效。Kingbase ES分两种情况，若删除基本表时带RESTRICT选项， 则不可以删除基本表；若删除基本表时带CASCADE选项，则可以删除基本表，同时也删 除视图。Kingbase ES的这种策略符合SQL 2011标准。
  (3)对于存储过程和函数，删除基本表后，这三个数据库产品都不自动删除建立在此基本表上的存储过程和函数，但是已经失效。
  (4)如果欲删除的基本表上有触发器，或者被其他基本表的约束所引用（CHECK、FOREIGN KEY等），读者可以从比较表中得到这三个系统的处理策略，这里就不一一说明了。同样，对于其他的SQL语句，不同的数据库产品在处理策略上会与标准有所差别。因此，如果发现本文中个别例子在某个数据库产品上不能通过时，请读者参见有关产品的用户手册，适当修改即可。

索引的建立与删除

  当表的数据量比较大时，查询操作会比较耗时。建立索引是加快查询速度的有效手段。数据库索引类似于图书后面的索引，能快速定位到需要查询的内容。用户可以根据应用环境的需要在基本表上建立一个或多个索引，以提供多种存取路径，加快查找速度。
  数据库索引有多种类型，常见索引包括顺序文件上的索引、B+树索引、散列(hash)索引、位图索引等。顺序文件上的索引是针对按指定属性值升序或降序存储的关系，在该属性上建立一个顺序索引文件，索引文件由属性值和相应的元组指针组成。B+树索引是将索引属性组织成B+树形式，B+树的叶结点为属性值和相应的元组指针。B+树索引具有动态平衡的优点。散列索引是建立若干个桶，将索引属性按照其散列函数值映射到相应桶中，桶中存放索引属性值和相应的元组指针。散列索引具有查找速度快的特点。位图索引是用位向量记录索引属性中可能出现的值，每个位向量对应一个可能值。
  索引虽然能够加速数据库查询，但需要占用一定的存储空间，当基本表更新时，索引要进行相应的维护，这些都会增加数据库的负担，因此要根据实际应用的需要有选择地创建索引。目前SQL标准中没有涉及索引，但商用关系数据库管理系统一般都支持索引机制，只是不同的关系数据库管理系统支持的索引类型不尽相同。
  一般说来，建立与删除索引由数据库管理员或表的属主(owner)，即建立表的人负责完成。关系数据库管理系统在执行查询时会自动选择合适的索引作为存取路径，用户不必也不能显式地选择索引。索引是关系数据库管理系统的内部实现技术，属于内模式的范畴。

建立索引

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

1
2

CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
ON <表名> (<列名>[<次序>][,<列名>[<次序>]]···);

  其中，<表名>是要建索引的基本表的名字。索引可以建立在该表的一列或多列上，各列名之间用逗号分隔。每个<列名>后面还可以用<次序>指定索引值的排列次序，可选ASC（升序）或DESC（降序），默认值为ASC。
  UNIQUE表明此索引的每一个索引值只对应唯一的数据记录。
  CLUSTER表示要建立的索引是聚簇索引。有关聚簇索引的概念在数据库系统（五）：数据库设计中介绍。

  例13：为学生—课程数据库中的Student、Course和SC三个表建立索引。其中Student表按学号升序唯一索引，Course表按课程号号升序建唯-索引，SC表按学号升序和课程号降序建唯一索引。

1
2
3

CREATE UNIQUE INDEX Stusno ON Student(Sno);
CREATE UNIQUE INDEX Coucno ON Course(Cno);
CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);

修改索引

  对于已经建立的索引，如果需要对其重新命名，可以使用ALTER INDEX语句。其一般格式为

1

ALTER INDEX <旧索引名> RENAME TO <新索引名>;

  例14：将SC表的SCno索引名改为SCSno。

1

ALTER INDEX SCno RENAME TO SCSno;

删除索引

  索引一经建立就由系统使用和维护，不需用户干预。建立索引是为了减少查询操作的时间，但如果数据增、删、改频繁，系统会花费许多时间来维护索引，从而降低了查询效率。这时可以删除一些不必要的索引。

  在SQL中，删除索引使用DROP INDEX语句，其一般格式为

1

DROP INDEX <索引名>;

  例15：删除Student表的Stusname索引。

1

DROP INDEX Stusname;

  删除索引时，系统会同时从数据字典中删去有关该索引的描述。

数据字典

  数据字典是关系数据库管理系统内部的一组系统表，它记录了数据库中所有的定义信息，包括关系模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等。关系数据库管理系统在执行SQL的数据定义语句时，实际上就是在更新数据字典表中的相应信息。在进行查询优化和查询处理时，数据字典中的信息是其重要依据。

数据查询

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

1
2
3
4
5

SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>] ···
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语句既可以完成简单的单表查询，也可以完成复杂的连接查询和嵌套查询。 下面以学生—课程数据库为例说明SELECT语句的各种用法。

单表查询

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

选择表中的若干列

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

1. 查询指定列

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

  例16：查询全体学生的学号与姓名。

1
2

SELECT Sno,Sname
FROM Student;

  该语句的执行过程可以是这样的：从Student表中取出一个元组，取出该元组在属性Sno和Sname上的值，形成一个新的元组作为输出。对Student表中的所有元组做相同的处理，最后形成一个结果关系作为输出。

  例17：查询全体学生的姓名、学号、所在系。

1
2

SELECT Sname,Sno,Sdept
FROM Student;

  <目标列表达式>中各个列的先后顺序可以与表中的顺序不一致。用户可以根据应用的需要改变列的显示顺序。本例中先列出姓名，再列出学号和所在系。

2. 查询全部列

  将表中的所有属性列都选出来有两种方法，一种方法就是在SELECT关键字后列出所有列名；如果列的显示顺序与其在基表中的顺序相同，也可以简单地将<目标列表达式>指定为*。

  例18：查询全体学生的详细记录。

1
2

SELECT *
FROM Student;

  等价于

1
2

SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student;

3. 查询经过计算的值

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

  例19：查询全体学生的姓名及其出生年份。

1
2

SELECT Sname,2014-Sage /*查询结果的第2列是一个算术表达式*/
FROM Student;

  查询结果中第2列不是列名而是一个计算表达式，是用当时的年份（假设为2014年）减去学生的年龄。这样所得的即是学生的出生年份。输出的结果为

表9

  <目标列表达式>不仅可以是算术表达式，还可以是字符串常量、函数等。

  例20：查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示系名。

1
2

SELECT Sname,'Year of Birth: ',2014-Sage,LOWER(Sdept)
FROM Student;

  结果为

表10

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

1
2
3

SELECT Sname NAME,'Year of Birth:' BIRTH,2014-Sage BIRTHDAY,
       LOWER(Sdept) DEPARTMENT
FROM Student;

  结果为

表11

选择表中的若干元组

1. 消除取值重复的行

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

  例21：查询选修了课程的学生学号。

1
2

SELECT Sno
FROM SC;

  执行上面的SELECT语句后，结果为

表12

  该查询结果里包含了许多重复的行。如想去掉结果表中的重复行，必须指定 DISTINCT：

1
2

SELECT DISTINCT Sno
FROM SC;

  则执行结果为

表13

  如果没有指定DISTINCT关键词，则默认为ALL,即保留结果表中取值重复的行。

1
2

SELECT Sno
FROM SC;

  等价于

1
2

SELECT ALL Sno
FROM SC;

2. 查询满足条件的元组

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

表14

  (1)比较大小
  用于进行比较的运算符一般包括=（等于），>（大于），<（小于），>=（大于等于），<=（小于等于），!=或<>（不等于），!>（不大于），!<（不小于）。

  例22：查询计算机科学系全体学生的名单。

1
2
3

SELECT Sname
FROM Student
WHERE Sdept='CS';

  关系数据库管理系统执行该查询的一种可能过程是：对Student表进行全表扫描，取 出一个元组，检查该元组在Sdept列的值是否等于CS，如果相等，则取出Sname列的值形成一个新的元组输出；否则跳过该元组，取下一个元组。重复该过程，直到处理完Student表的所有元组。
  如果全校有数万个学生，计算机系的学生人数是全校学生的5%左右，可以在Student表的Sdept列上建立索引，系统会利用该索引找出Sdept='CS'的元组，从中取出Sname列值形成结果关系。这就避免了对Student表的全表扫描，加快了查询速度。注意如果学生较少，索引查找不一定能提高查询效率，系统仍会使用全表扫描。这由查询优化器按照某些规则或估计执行代价来作出选择。

  例23：查询所有年龄在20岁以下的学生姓名及其年龄。

1
2
3

SELECT Sname, Sage
FROM Student
WHERE Sage<20;

  例24：查询考试成绩不及格的学生的学号。

1
2
3

SELECT DISTINCT Sno
FROM SC
WHERE Grade<60;

  这里使用了DISTINCT短语，当一个学生有多门课程不及格，他的学号也只列一次。

  (2)确定范围
  谓词BETWEEN···AND···和NOT BETWEEN···AND··可以用来查找找属性值在（或不在）指定范围内的元组，其中BETWEEN后是范围的下限（即低值），AND后是范围的上限（即高值）。

  例25：查询年龄在20〜23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。

1
2
3

SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23;

  例26：查询年龄不在20〜23岁之间的学生姓名、系别和年龄。

1
2
3

SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage NOT BETWEEN 20 AND 23;

  (3)确定集合
  谓词IN可以用来查找属性值属于指定集合的元组。

  例27：查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别。

1
2
3

SELECT Sname,Ssex
FROM Student
WHERE Sdept IN ('CS','MA','IS');

  与IN相对的谓词是NOT IN，用于查找属性值不属于指定集合的元组。

  例28：查询既不是计算机科学系、数学系，也不是信息系的学生的姓名和性别。

1
2
3

SELECT Sname,Ssex
FROM Student
WHERE Sdept NOT IN ('CS','MA','IS');

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

1

[NOT] LIKE '<匹配串>' [ESCAPE '换码字符' ]

  其含义是查找指定的属性列值与<匹配串> 相匹配的元组。<匹配串>可以是一个完整的字符串，也可以含有通配符%和_。其中：
  ◦%（百分号）代表任意长度（长度可以为0）的字符串。例如a%b表示以a开头，以b结尾的任意长度的字符串。如acb、addgb、ab等都满足该匹配串。
  ◦_（下横线）代表任意单个字符。例如a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb、afb等都满足该匹配串。

  例29：查询学号为201215121的学生的详细情况。

1
2
3

SELECT *
FROM Student
WHERE Sno LIKE '201215121';

  等价于

1
2
3

SELECT *
FROM Student
WHERE Sno='201215121';

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

  例30：查询所有姓刘的学生的姓名、学号和性别。

1
2
3

SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname LIKE '刘%'；

  例31：查询姓“欧阳”且全名为三个汉字的学生的姓名。

1
2
3

SELECT Sname
FROM Student
WHERE Sname LIKE '欧阳_'；

  注意：数据库字符集为ASCII时一个汉字需要两个_；当字符集为GBK时只需要一个_。

  例32：查询名字中第二个字为“阳"的学生的姓名和学号。

1
2
3

SELECT Sname, Sno
FROM Student
WHERE Sname LIKE '_阳%';

  例33：查询所有不姓刘的学生的姓名、学号和性别。

1
2
3

SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname NOT LIKE '刘％';

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

  例34：查询DB_Design课程的课程号和学分。

1
2
3

SELECT Cno,Ccredit
FROM Course
WHERE Cname LIKE `DB\_Design` ESCAPE '\';

  ESCAPE '_'表示为“_”为换码字符。这样匹配串中紧跟在后面的字符“_”不再具有通配符的含义，转义为普通的字符。

  例35：查询以'DB_'开头，且倒数第三个字符为'i'的课程的详细情况。

1
2
3

SELECT *
FROM Course
WHERE Cname LIKE 'DB\_%i__' ESCAPE '\';

  这里的匹配串为'DB\_%i__'。第一个_前面有换码字符所以它被转义为普通的_字符。而i后面的两个_的前面均没有换码字符\，所以它们仍作为通配符。

  (5)涉及空值的查询
  例36：某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

1
2
3

SELECT Sno,Cno
FROM SC
WHERE Grade IS NULL; /*分数 Grade 是空值*/

  注意这里的IS不能用等号（=）代替。

  例37：查所有有成绩的学生学号和课程号。

1
2
3

SELECT Sno,Cno
FROM SC
WHERE Grade IS NOT NULL;

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

  例38：查询计算机科学系年龄在20岁以下的学生姓名。

1
2
3

SELECT Sname
FROM Student
WHERE Sdept='CS' AND Sage<20;

  在例27中的IN谓词实际上是多个OR运算符的缩写，因此该例中的查询也可以用OR运算符写成如下等价形式：

1
2
3

SELECT Sname,Ssex
FROM Student
WHERE Sdept='CS ' OR Sdept='MA' OR Sdept='IS';

ORDER BY子句

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

  例39：查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。

1
2
3
4

SELECT Sno,Grade
FROM SC
WHERE Cno='3'
ORDER BY Grade DESC;

  对于空值，排序时显示的次序由具体系统实现来决定。例如按升序排，含空值的元组最后显示；按降序排，空值的元组则最先显示。各个系统的实现可以不同，只要保持一致就行。

  例40：查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

1
2
3

SELECT *
FROM Student
ORDER BY Sdept,Sage DESC;

聚集函数

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

表15 SQL提供的聚集函数

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

  例41：查询学生总人数。

1
2

SELECT COUNT(*)
FROM Student;

  例42：查询选修了课程的学生人数。

1
2

SELECT COUNT(DISTINCT Sno)
FROM SC;

  学生每选修一门课，在SC中都有一条相应的记录。一个学生要选修多门课程，为避免重复计算学生人数，必须在COUNT函数中用DISTINCT短语。

  例43：计算选修1号课程的学生平均成绩。

1
2
3

SELECT AVG(Grade)
FROM SC
WHERE Cno='1';

  例44：查询选修1号课程的学生最高分数。

1
2
3

SELECT MAX(Grade)
FROM SC
WHERE Cno='1';

  例45：查询学生201215012选修课程的总学分数。

1
2
3

SELECT SUM(Ccredit)
FROM SC,Course
WHERE Sno='201215012' AND SC.Cno=Course.Cno;

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

GROUP BY子句

  GROUP BY子句将查询结果按某一列或多列的值分组，值相等的为一组。
  对查询结果分组的目的是为了细化聚集函数的作用对象。如果未对查询结果分组，聚集函数将作用于整个查询结果，如前面的例41〜例45。分组后聚集函数将作用于每一个组，即每一组都有一个函数值。

  例46：求各个课程号及相应的选课人数。

1
2
3

SELECT Cno,COUNT(Sno)
FROM SC
GROUP BY Cno;

  该语句对查询结果按Cno的值分组，所有具有相同Cno值的元组为一组，然后对每一组作用聚集函数COUNT进行计算，以求得该组的学生人数。

  查询结果可能为

表16

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

  例47：查询选修了三门以上课程的学生学号。

1
2
3
4

SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*)>3;

  这里先用GROUP BY子句按Sno进行分组，再用聚集函数COUNT对每一组计数；HAVING短语给出了选择组的条件，只有满足条件（即元组个数>3，表示此学生选修的课超过3门）的组才会被选出来。
  WHERE子句与HAVING短语的区别在于作用对象不同。WHERE子句作用于基本表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。

  例48：查询平均成绩大于等于90分的学生学号和平均成绩。

  下面的语句是不对的：

1
2
3
4

SELECT Sno,AVG(Grade)
FROM SC
WHERE AVG(Grade)>=90
GROUP BY Sno;

  因为WHERE子句中是不能用聚集函数作为条件表达式的，正确的查询语句应该是：

1
2
3
4

SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90;

连接查询

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

等值与非等值连接查询

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

1

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

  其中比较运算符主要有=、>、<、>=、<=、!=（或＜＞）等。

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

1

[<表名 1>.] <列名 1> BETWEEN [<表名 2>.] <列名 2> AND [<表名 2>.] <列名 3>

  当连接运算符为=时，称为等值连接。使用其他运算符称为非等值连接。
  连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须是可比的，但名字不必相同。

  例49：查询每个学生及其选修课程的情况。

  学生情况存放在Student表中，学生选课情况存放在SC表中，所以本查询实际上涉及Student与SC两个表。这两个表之间的联系是通过公共属性Sno实现的。

1
2
3

SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno=SC.Sno; /* 将Student与SC中同一学生的元组连接起来 */

  假设Student表、SC表的数据如表3和表5所示，该查询的执行结果如下表所示。

表17

  本例中，SELECT子句与WHERE子句中的属性名前都加上了表名前缀，这是为了避免混淆。如果属性名在参加连接的各表中是唯一的，则可以省略表名前缀。
  关系数据库管理系统执行该连接操作的一种可能过程是：首先在表Student中找到第一个元组，然后从头开始扫描SC表，逐一查找与Student第一个元组的Sno相等的SC元组，找到后就将Student中的第一个元组与该元组拼接起来，形成结果表中一个元组。SC全部查找完后，再找Student中第二个元组，然后再从头开始扫描SC，逐一查找满足连接条件的元组，找到后就将Student中的第二个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到Student中的全部元组都处理完毕为止。这就是嵌套循环连接算法的基本思想。如图3所示。

图3 关系数据库管理系统执行连接操作的示意图

  如果在SC表Sno上建立了索引的话，就不用每次全表扫描SC表了，而是根据Sno值通过索引找到相应的SC元组。用索引查询SC中满足条件的元组一般会比全表扫描快。

  若在等值连接中把目标列中重复的属性列去掉则为自然连接。

  例50：对例49用自然连接完成。

1
2
3

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno=SC.Sno;

  本例中，由于Sname、Ssex、Sage、Sdept、Cno和Grade属性列在Student表与SC表中是唯一的，因此引用时可以去掉表名前缀；而Sno在两个表都出现了，因此引用时必须加上表名前缀。
  一条SQL语句可以同时完成选择和连接查询，这时WHERE子句是由连接谓词和选择谓词组成的复合条件。

  例51：查询选修2号课程且成绩在90分以上的所有学生的学号和姓名。

1
2
3
4

SELECT Student.Sno,Sname
FROM Student,SC
WHERE Student.Sno=SC.Sno AND /* 连接谓词 */
SC.Cno='2' AND SC.Grade>90;  /* 其他限定条件 */

  该查询的一种优化（高效）的执行过程是，先从SC中挑选出Cno='2'并且Grade>90的元组形成一个中间关系，再和Student中满足连接条件的元组进行连接得到最终的结果关系。

自身连接

  连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，称为表的自身连接。

  例52：查询每一门课的间接先修课（即先修课的先修课）。

  在Course表中只有每门课的直接先修课信息，而没有先修课的先修课。要得到这个信息，必须先对一门课找到其先修课，再按此先修课的课程号查找它的先修课程。这就要将Course表与其自身连接。
  为此，要为Course表取两个别名，一个是FIRST，另一个是SECOND。

图4 Course表的两个别名FIRST和SECOND

  完成该查询的SQL语句为

1
2
3

SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST,Course SECOND
WHERE FIRST.Cpno=SECOND.Cno;

  结果为

表18

外连接

  在通常的连接操作中，只有满足连接条件的元组才能作为结果输出。如例例49的结果表中没有201215123和201215125两个学生的信息，原因在于他们没有选课，在SC表中没有相应的元组，导致Student中这些元组在连接时被舍弃了。
  有时想以Student表为主体列出每个学生的基本情况及其选课情况。若某个学生没有选课，仍把Student的悬浮元组保存在结果关系中，而在SC表的属性上填空值NULL，这时就需要使用外连接。外连接的概念见关系数据库-外连接。可以参照例53改写例49。

  例53：

1
2

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno);

  也可以使用USING来去掉重复值。

1
2

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC USING (Sno);

  执行结果如下。

表19

  左外连接列出左边关系（如本例Student）中所有的元组，右外连接列出右边关系中所有的元组。

多表连接

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

  例54：查询每个学生的学号、姓名、选修的课程名及成绩。

  本查询涉及三个表，完成该查询的SQL语句如下：

1
2
3

SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;

  关系数据库管理系统在执行多表连接时，通常是先进行两个表的连接操作，再将其连接结果与第三个表进行连接。本例的一种可能的执行方式是，先将Student表与SC表进行连接，得到每个学生的学号、姓名、所选课程号和相应的成绩，然后再将其与Course表进行连接，得到最终结果。

嵌套查询

  在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询(nested query)。例如：

1
2
3
4
5
6
7

SELECT Sname
FROM Student       /*外层查询或父查询*/
WHERE Sno IN
    (SELECT Sno    /*内层查询或子查询*/
    FROM SC
    WHERE Cno='2');

  本例中，下层查询块SELECT Sno FROM SC WHERE Cno='2'是嵌套在上层查询块SELECT Sname FROM Student WHERE Sno IN的WHERE条件中的。上层的查询块称为外层查询或父查询，下层查询块称为内层查询或子查询。
  SQL语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。需要特别指出 的是，子查询的SELECT语句中不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果排序。
  嵌套查询使用户可以用多个简单查询构成复杂的查询，从而增强SQL的查询能力。以层层嵌套的方式来构造程序正是SQL中“结构化”的含义所在。

带有IN谓词的子查询

  在嵌套查询中，子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最经常使用的谓词。

  例55：查询与"刘晨”在同一个系学习的学生。

  先分步来完成此查询，然后再构造嵌套查询。

  ①确定"刘晨”所在系名

1
2
3

SELECT Sdept
FROM Student
WHERE Sname='刘晨';

  结果为CS。

  ②查找所有在CS系学习的学生。

1
2
3

SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept='CS';

  结果为

表20

  将第一步查询嵌入到第二步查询的条件中，构造嵌套查询如下：

1
2
3
4
5
6

SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
      (SELECT Sdept
      FROM Student
      WHERE Sname='刘晨');

  本例中，子查询的查询条件不依赖于父查询，称为不相关子查询。一种求解方法是由里向外处理，即先执行子查询，子查询的结果用于建立其父查询的查找条件。得到如下的语句：

1
2
3

SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN ('CS');

  然后执行该语句。
  本例中的查询也可以用自身连接来完成：

1
2
3

SELECT S1.Sno,S1.Sname,S1.Sdept
FROM Student S1,Student S2
WHERE S1.Sdept=S2.Sdept AND S2.Sname='刘晨';

  可见，实现同一个查询请求可以有多种方法，当然不同的方法其执行效率可能会有差别，甚至会差别很大。这就是数据库编程人员应该掌握的数据库性能调优技术。

  例56：查询选修了课程名为“信息系统”的学生学号和姓名。

  本查询涉及学号、姓名和课程名三个属性。学号和姓名存放在Student表中，课程名存放在Course表中，但Student与Course两个表之间没有直接联系，必须通过SC表建立它们二者之间的联系。所以本查询实际上涉及三个关系。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT Sno,Sname                      /* ③最后在Student关系中 */
FROM Student                          /*  取出Sno和Sname     */
WHERE Sno IN
      (SELECT Sno                     /* ②然后在SC关系中找出  */
      FROM SC                         /*  选修了3号课程的学生  */
      WHERE Cno IN                    /*  学号               */
            (SELECT Cno               /* ①首先在Course关系中  */
             FROM Course              /*  找出"信息系统"的课程 */
             WHERE Cname='信息系统'    /*  号，结果为3号       */
            )
      );

  本查询同样可以用连接查询实现：

1
2
3
4
5

SELECT Student.Sno,Sname
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND
      SC.Cno=Course.Cno AND
      Course.Cname='信息系统';

  有些嵌套查询可以用连接运算替代，有些是不能替代的。从例55和例56可以看到，查询涉及多个关系时，用嵌套查询逐步求解层次清楚，易于构造，具有结构化程序设计的优点。但是相比于连接运算，目前商用关系数据库管理系统对嵌套查询的优化做得还不够完善，所以在实际应用中，能够用连接运算表达的查询尽可能采用连接运算。
  例55和例56中子查询的查询条件不依赖于父查询，这类子查询称为不相关子查询。不相关子查询是较简单的一类子查询。如果子查询的查询条件依赖于父查询，这类子查询称为相关子查询(correlated subquery)‌整个查询语句称为相关嵌套查询(correlated nested query)‌语句。例57就是一个相关子查询的例子。

带有比较运算符的子查询

  带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单个值时，可以用>、<、=、>=、<=、!=或<>等比较运算符。
  例如在例55中，由于一个学生只可能在一个系学习，也就是说内查询的结果是一个值，因此可以用=代替IN：

1
2
3
4
5
6

SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept =
      (SELECT Sdept
       FROM Student
       WHERE Sname='刘晨');

  例57：找出每个学生超过他自己选修课程平均成绩的课程号。

1
2
3
4
5

SELECT Sno,Cno
FROM SC x
WHERE Grade>=(SELECT AVG(Grade)
              FROM SC y
              WHERE y.Sno=x.Sno);

  x是表SC的别名，又称为元组变量，可以用来表示SC的一个元组。内层查询是求一个学生所有选修课程平均成绩的，至于是哪个学生的平均成绩要看参数x.Sno的值，而该值是与父查询相关的，因此这类查询称为相关子查询。这个语句的一种可能的执行过程采用以下三个步骤。
  ①从外层查询中取出SC的一个元组x，将元组x的Sno值(201215121)传送给内层查询。

1
2
3

SELECT AVG(Grade)
FROM SC y
WHERE y.Sno='201215121‘;

  ②执行内层查询，得到AVG值为88（近似值），用该值代替内层查询，得到外层查询：

1
2
3

SELECT Sno, Cno
FROM SC x
WHERE Grade>=88;

  ③执行这个查询，得到

表21

  然后外层查询取出下一个元组重复做上述①至③步骤的处理，直到外层的SC元组全部处理完毕。结果为

表22

  求解相关子查询不能像求解不相关子查询那样一次将子查询求解出来，然后求解父查询。内层查询由于与外层查询有关，因此必须反复求值。

带有ANY(SOME)或(ALL)谓词的子查询

子查询返回单值时可以用比较运算符，但返回多值时要用ANY（有的系统用SOME）或ALL谓词修饰符。而使用ANY或ALL谓词时则必须同时使用比较运算符。其语义如下表所示：

表23 ANY或ALL运算符与比较运算符组合后的语义

  例58：查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄。

1
2
3
4
5
6

SELECT Sname,Sage
FROM Student
WHERE Sage<ANY (SELECT Sage
                FROM Student
                WHERE Sdept='CS')
AND Sdept<>'CS';

  结果如下：

表24

  关系数据库管理系统执行此查询时，首先处理子查询，找出CS系中所有学生的年龄，构成一个集合(20,19)；然后处理父查询，找所有不是CS系且年龄小于20或19的学生。
  本查询也可以用聚集函数来实现，首先用子查询找出CS系中最大年龄(20)，然后在父查询中查所有非CS系且年龄小于20岁的学生。SQL语句如下：

1
2
3
4
5
6
7

SELECT Sname,Sage
FROM Student
WHERE Sage <
    (SELECT MAX(Sage)
    FROM Student
    WHERE SdepU 'CS')
AND Sdept <> 'CS';

  例59：查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。

1
2
3
4
5
6
7

SELECT Sname,Sage
FROM Student
WHERE Sage < ALL
            (SELECT Sage
            FROM Student
            WHERE Sdept='CS')
AND Sdept<>'CS';'

  关系数据库管理系统执行此查询时，首先处理子查询，找出CS系中所有学生的年龄，构成一个集合(20,19)。然后处理父查询，找所有不是CS系且年龄既小于20，也小于19的学生。查询结果为

表25

  本查询同样也可以用聚集函数实现。SQL语句如下：

1
2
3
4
5
6
7

SELECT Sname,Sage
FROM Student
WHERE Sage <
            (SELECT MIN(Sage)
            FROM Student
            WHERE Sdept='CS')
AND Sdept<>'CS';

  事实上，用聚集函数实现子查询通常比直接用ANY或ALL查询效率要高。ANY、ALL与聚集函数的对应关系如表26所示。

表26 ANY（或SOME）、ALL谓词与聚集函数，IN谓词的等价转换关系

  表26中，=ANY等价于IN谓词，<ANY等价于<MAX，<>ALL等价于NOT IN谓词，<ALL等价于<MIN，等等。

带有EXISTS谓词的子查询

  EXISTS代表存在量词 \(\exists\) 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
  可以利用EXISTS来判断 \(x \in S\) 、 \(S \sube R\) 、 \(S = R\) 、 \(S \cap R 非空\) 等是否成立。

  例60：查询所有选修了1号课程的学生姓名。

  本查询涉及Student和SC表。可以在Student中依次取每个元组的Sno值，用此值去检查SC表。若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno='1'，则取此Student.Sname送入结果表。将此想法写成SQL语句是