数据库系统概论笔记（第三章 SQL）——持续更新，争取每周一章

第三章 SQL

3.1 SQL语言概述

尽管我们说SQL语言是一种查询语言。但是，除了数据库查询。它还有具有很多别的功能，它可以定义数据结构修改数据库中的数据以及说明安全性约束条件的。

SQL语言具有以下几个方面：

• 数据定义语言(Data-Definition Language,DDL):SQL DDL提供定义关系模式、删除关系以及修改关系模式的命令。

• 数据操纵语言(Data-Manipulation Language,DML):SQL DML提供从数据库中查询信息，以及在数据库中插入元组、删除元组、修改元组的能力。

• 完整性(integrity)：SQL DDL包括定义完整性约束的命令，保存在数据库中的数据必须满足所定义的完整性约束。破坏完整性约束的更新是不允许的。

• 视图定义(view definition):SQL DDL包括定义视图的命令。

• 事务控制(transaction control)：SQL包括定义事务的开始和结束的命令。嵌入式SQL和动态SQL(embedded SQL and dynamic SQL):嵌入式和动态SQL定义SQL语句如何嵌入到通用编程语言，如C、C++和Java中。

• 授权(authorization):SQL DDL包括定义对关系和视图的访问权限的命令。

3.2 SQL数据定义

数据库中的关系集合必须由数据定义语言(DDL)指定给系统。SQL的DDL不仅能够定义一组关系，还能够定义每个关系的信息，包括：

• 每个关系的模式。

• 每个属性的取值类型。

• 完整性约束。

• 每个关系维护的索引集合。

• 每个关系的安全性和权限信息。

• 每个关系在磁盘上的物理存储结构。

我们在此只讨论基本模式定义和基本类型，对SQL DLL其他特征的讨论将放到第4章和第5章

3.2.1 基本类型

SQL标准支持多种固有类型，包括：

• char(n):固定长度的字符串，用户指定长度n。也可以使用全称character

• varchar(n):可变长度的字符串，用户指定最大长度n,等价于全称character varying

• int:整数类型（和机器相关的整数的有限子集），等价于全称integer。

• smallint:小整数类型（和机器相关的整数类型的子集）。

• numeric(p, d):定点数，精度由用户指定。这个数有p位数字（加上一个符号位），其中d位数字在小数点右边。所以在一个这种类型的字段上，numeric(3,1)可以精确储存44.5，但不能精确存储444.5或0.32这样的数。

• real,double precision:浮点数与双精度浮点数，精度与机器相关。

• float(n):精度至少为n位的浮点数。

更多类型将在4.5节介绍。

每种类型都可能包含一个被称作空值的特殊值。空值表示一个缺失的值，该值可能存在但并不为人所知，或者可能根本不存在。在可能的情况下，我们希望禁止加入空值，正如我们马上将看到的那样。

char数据类型存放固定长度的字符串。

例如，属性A的类型是char(10),如果我们为此属性存人字符串“Avi”,那么该字符串后会追加7个空格来使其达到10个字符的串长度。反之，如果属性B的类型是varchar(10),我们在属性B中存人字符串“Avi”,则不会增加空格。

当比较两个char类型的值时，如果它们的长度不同，在比较之前会自动在短值后面加上额外的空格以使它们的长度一致。当比较一个char类型和一个varchar类型的时候，也许读者会期望在比较之前会自动在varchar类型后面加上额外的空格以使长度一致：然而、这种情况可能发生也可能不发生，这取决于数据库系统。其结果是，即便上述属性A和B中存放的是相同的值“A”,A=B的此较也可能返回假。

我们建议始终使用varchar类型而不是char类型来避免这样的问题。SOL也提供nvarchar类型来存放使用Unieode表示的多语言数据。然而，很多数据库甚至允许在varchar类型中存放Unicode(采用UTF-8表示)。

3.2.2 基本模式定义

我们用create table命令定义SQL关系。下面的命令在数据库中创建了一个department关系：

create table department
	   (dept_name varchar (20),
        building varchar (15),
        budget numeric (12,2),
        primary key (dept name));

其基本格式如下：

create table r# r :关系名
	   (A1 D1,# Ai：属性名， Di指定了属性Ai的类型以及可选的约束
        A2 D2,
        ...,
        An, Dn,
        <完整性约束1>，
         ...,
         <完整性约束k>);

其中，r是关系名，每个Ai是关系r模式中的一个属性名，Di是属性Ai的域，也就是说Di指定了属性Ai的类型以及可选的约束，用于限制所允许的Ai取值的集合。

create table命令后面用分号结束，本章后面的其他SQL语句也是如此，在很多SQL实现中，分号是可选的。SQL支持许多不同的完整性约束。在本节我们只讨论其中少数几个：

• primary key(Aj1, Aj2, …, Ajm）:primary-key声明表示属性Aj1, Aj2, …, Ajm构成关系的主码。主码属性必须非空且唯一,也就是说没有一个元组在主码属性上取空值，关系中也没有两个元组在所有主码属性上取值相同。虽然主码的声明是可选的，但为每个关系指定一个主码通常会更好。

• foreign key(Ak1, Ak2, … ,Akn) references: foreign key声明表示关系中任意元组在属性(Ak1, Ak2, … ,Akn)上的取值必须对应于关系s中某元组在主码属性上的取值。

• not null：一个属性上的not null约束表明在该属性上不允许空值。换句话说，此约束把空值排除在该属性域之外。例如在图3-1中，instructor关系的name属性上的not null约束保证了教师的姓名不会为空。
  

图3-1给出了我们在书中使用的大学数据库的部分SQL DDL定义。course表的定义中声明了“foreign key(dept_name) references department”。此外码声明表明对于每个课程元组来说，该元组所表示的系名必然存在于department关系的主码属性(dept_name)中。没有这个约束的话，就可能有某门课程指定了一个不存在的系名。图3-1还给出了表section、instructor和teaches上的外码约束。

SQL禁止破坏完整性约束的任何数据库的更新。

相关操作：

## 一个新创建关系最初是空的，我们可以使用insert命令将数据加载到关系中。例如，如果我们将插入一下事实：在Biology系有个叫Smith的教师，其instructer_id为10211，工资为66000美元，我们可以这样写：
	insert into instructer
		values (10211, 'Smith', 'Biology', 66000)

## 我们也可以使用delete命令来删除元组
## 此命令将从student关系中删除所有元组。其他格式的删除指令允许指定待删除的元组。
delete from Student;

## 要从SQL数据库中去掉一个关系，我们使用drop table命令。drop table命令从数据库中删除关于被去掉关系的所有信息，包括关系本身
## 该命令比 delete from r;语气更强
drop table r;

## 我们使用alter table命令为已有关系增加属性。关系中所有元组在新属性上取值将被设为null。
alter table r add A D;
## 其中r是现有关系，A是待添加的属性的名字，D是属性待添加属性的域
## 我们还可以通过命令,从关系中去掉属性。
alter table r drop A;

3.3 SQL查询的基本结构

SQL查询由基本三个部分构成：select， from和where。

3.3.1 单关系查询

## 找出所有教师的名字
select name
from instructer;

## 找到所有教师所在的系别
select dept_name
from instruter;
## 如果要去除重复项，使用distinct
select distinct dept_name
from instrutor;
## 当然，SQL也支持我们关键词all来显示指明不去除重复(既然保留重复项是默认的，我们可以省略all)：
select all dept_name
from instrutor;

## select 子句还可以含有+、 -、 *、 /运算符的算数表达式，运算对象可以是常数或元组的属性。例如查询每位员工工资为1.1倍的结果：
select ID, name, dept_name, salary*1.1
from instrutor;

## where 允许我们只选出那些在from子句的结果关系中满足特定谓词的元组。考虑找出所有在Computer Science系并且工资超过70000美元的教师的姓名，该查询用SQL可以写为：
select name
from instructor
where dept_name = 'Comp. sci' and salary > 70000
## 除此之外，SQL允许我们使用逻辑连词（and、 or和not）、比较运算符，算数表达式以及特殊表达式，如日期类型。

3.3.2 多关系查询

到目前为止，我们所查询的都是基于单个关系的。通常查询需要从多个关系中获取信息。我们现在来学习如何书写这样的查询。

## 目标：找到所有教师的姓名，以及他们所在系的名称和系所在建筑的名称。
## 考虑instructor关系模式，我们可以发现从dept_name属性得到系名，但是系所在建筑的名称是在department关系的building属性中给出的。
select name, instructor.dept_name, building
from instrutor, department
where instructor.dept_name = department.dept_name;
## 为了在SQL中回答上述查询，我们把需要访问的关系都列在from字句中，并且where子语句中指定了匹配条件。

现在，我们考虑一下涉及多个关系的SQL查询的通用形式。正如我们前面所看到的那样，一个SQL查询可以包括三种类型的子句：select子句、 from子句和where语句。每个语句作用如下：

• select语句：用于列出查询结果中所需的属性；
• from语句：是一个查询求值中需要访问的关系列表；
• where语句：是一个作用在from子句中关系的属性上的谓词。

每个Ai代表一个属性，每个ri代表一个关系。P是一个谓词。如果省略where子句，则谓词P为true。

尽管各子句必须以select、from、where的次序写出，但理解查询所代表运算的最容易的方式是以运算的顺序来考察各子句：

**首先是from,然后是where,最后是select。**通过from子句定义了一个在该子句中所列出关系上的笛卡儿积。它可以用集合理论来形式化地定义，但最好通过下面的迭代过程来理解，此过程可为from子句的结果关系产生元组。

for each元组t1 in 关系r1
 for each元组t2 in 关系r2
 	...
 	for each元组tm in关系 rm
		把t1,t2,…,tm连接成单个元组t
		把t加入结果关系中

此结果关系具有来自from子句中所有关系的所有属性。由于在关系r和，中可能出现相同的属性名，正如我们此前所看到的，我们在属性名前加上关系名作为前缀，表示该属性来自于哪个关系。

通常说来，一个SQL查询的含义可以理解如下：

1. 为from子句中列出的关系产生笛卡儿积。

2. 在步骤1的结果上应用where子句中指定的谓词。

3. 对于步骤2结果中的每个元组，输出select子句中指定属性（或表达式的结果）。

**注意：**我们需要小心设置where子句的条件。如果我们忽略where语句，我们将输出笛卡尔积（如果有where子句，那就输出符合where语句的笛卡尔积）。如果数据过大，那运算量会非常大。

3.3.3 自然连接

**自然连接(natural join)**运算作用于两个关系，并产生一个关系作为结果。不同于两个关系上的笛卡儿积，它将第一个关系的每个元组与第二个关系的所有元组都进行连接；自然连接只考虑那些在两个关系模式中都出现的属性上取值相同的元组对。

因此，回到instructor和teaches关系的例子上，instructor和teaches的自然连接计算中只考虑这样的元组对。来自instructor的元组和来自teaches的元组在共同属性ID上的取值相同。

结果关系如图3-8所示，只有13个元组，它们给出了关于每个教师以及该教师实际讲授的课程的信息。注意我们并没有重复列出那些在两个关系模式中都出现的属性，这样的属性只出现一次。还要注意列出属性的顺序：先是两个关系模式中的共同属性，然后是那些只出现在第一个关系模式中的属性，最后是那些只出现在第二个关系模式中的属性。

select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID

可以写为更简洁的形式：

# 使用自然连接来改写
select name, course_id
from instructor natrual join teaches;

我们还可以将多个关系自然连接起来：

select A1, A2, A3, ..., An
from r1 natural join r2 natural join ... natural join rm
where P;

为了发扬自然连接的优点，同时避免不必要的相等属性带来的危险，SQL提供了一种自然连接的构造形式， 允许用户来指定需要哪一列相等。下面的查询说明了这个特征：

select name,title
from (instructor natural join teaches)join course using (course_id);

join…using运算中需要给定一个属性名列表，其两个输入中都必须具有指定名称的属性。考虑运算:

join r2  using(A1,A2)

它与r1和r2的自然连接类似，只不过在t1.A1=t2.A1, 并且t1.A2=t2.A2成立的前提下，来自r1的元组t1和来自r2的元组t2就能匹配，即使r1和r2都具有名为A3的属性，也不需要t1.A3=t2.A3成立。

这样，在前述SQL查询中，连接构造允许teaches.dept_name和course.dept_name是不同的，该SQL查询给出了正确的答案。

3.4 附加的基本运算

SQL中还支持几种附加的基本运算。

3.4.1 更名运算

重新考察我们之前使用过的查询：

select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID

此查询的结果是一个具有下列属性的关系：

name, course id
## 结果中的属性名来自from子句中关系的属性名。

但我们不能总是用这个方法派生名字，其原因有几点：

• 首先，from子句的两个关系中可能存在同名属性，在这种情况下，结果中就会出现重复的属性名；

• 其次，如果我们在select子句中使用算术表达式，那么结果属性就没有名字；

• 再次，尽管如上例所示，属性名可以从基关系导出，但我们也许想要改变结果中的属性名字。

因此，SQL提供了一个重命名结果关系中属性的方法。即使用如下形式的as子句：

old-name as new-name

as子句既可出现在select子句中，也可出现在from子句中。

例如，如果我们想用名字instructor_name来代替属性名name,我们可以重写上述查询如下：

select name as instructor name, course_id
from instructor,teaches
where instructor.ID=teaches.ID;

as子句在重命名关系时特别有用。重命名关系的一个原因是把一个长的关系名替换成短的，这样在查询的其他地方使用起来就更为方便。为了说明这一点，我们重写查询“对于大学中所有讲授课程献教师，找出他们的姓名以及所讲述的所有课程标识”：

select T.name, S.course_id
from instructor as T,teaches as S
where T.ID = S.ID;

重命名关系的另一个原因是为了适用于需要比较同一个关系中的元组的情况。为此我们需要把一个关系跟它自身进行笛卡儿积运算，如果不重命名的话，就不可能把一个元组与其他元组区分开来。假设我们希望写出查询：“找出满足下面条件的所有教师的姓名，他们的工资至少比Biology系某一个教师的工资要高”，我们可以写出这样的SQL表达式：

select distinct T.name
from instructor as T,instructor as S
where T.salary>S.salary and S.dept_name ='Biology';

注意：我们不能使用instructor.salary这样的写法，因为这样并不清楚到底是希望引用哪一个instructor。

在上述查询中，T和S可以被认为是instructor关系的两个拷贝，但更准确地说是被声明为instructor关系的别名，也就是另外的名字。像T和S那样被用来重命名关系的标识符在SQL标准中被称作相关名称(correlation name),但通常也被称作表别名(table alias),或者相关变量(correlation variable),或者元组变量(tuple variable)。

3.4.2 字符串运算

SQL使用一对单引号来标示字符串，例如‘Computer’。如果单引号是字符串的组成部分，那就用两个单引号字符来表示，如字符串“it’s right”可表示为“it’s right”。在SQL标准中，字符串上的相等运算是大小写敏感的，所以表达式’comp.sci.’=‘Comp.Sci."的结果是假。然而一些数据库系统，如MySQL和SQL Server,在匹配字符串时并不区分大小写，所以在这些数据库中“comp.sci.’='Comp.Sci.”的结果可能是真。然而这种默认方式是可以在数据库级或特定属性级被修改的。

SQL在字符串上的多种函数：

• 串联（||）、提取子串、计算字符串长度、大小写转换（upper(s)、 lower(s)）、去掉字符串后面的空格（trim（s））
• 模式匹配等。

下面我们主要介绍下like实现模式匹配（区分大小写）：

• %：匹配任意字符；
• 下划线（_）:匹配任意一个字符。

相关例子：

• ‘intro%’：匹配任何以”intro“开头的字符串；
• ’%Comp%‘匹配任何包含“Comp”子串的字符串，例如’Intro. to Computer Science‘ 和’Computational Biology‘；
• ’___‘（这里是三个下划线）匹配只含三个字符的字符串；
• ’___%‘匹配至少含三个字符的字符串。

语言操作实例：

在SQL语句中用比较运算符like来表示模式。考虑查询“找到所在建筑名称中包含子串‘Watson’的所有系名”。该查询写法如下：

select dept_name
from department
where building like '%Watson%';

为了使模式中能够包含特殊模式的字符（即%和_）,SQL允许定义转义字符。转义字符直接放在特殊字符的前面，表示该特殊字符被当成普通字符。我们在like比较运算符中使用escape关键字来定义转义字符。为了说明这一用法，考虑一下模式，他们使用反斜杠（\）作为转义字符：

• like ‘ab \ % cd %’ escape’ \ ’ 匹配所有以”ab%cd“开头的字符串

• like 'ab \ \ cd% ’ escape ’ \ '匹配所有以“ab \ cd”开头的字符串

在SQL允许使用not like比较运算符搜寻不匹配项。一些数据库还提供like运算的变体，不区分大小。

在SQL:1999中还提供similar to操作，它具备比ike运算更强大的模式匹配能力。它的模式定义语法类似于UNIX中的正则表达式。

3.4.3 select子句中的属性说明

星号 “*” 可以用在select子句中表示“所有的属性”，因而，如下查询的select子句中使用instructor.*:

select instructor.*
from instructor,teaches
where instructor.ID =teaches.ID;

表示instructor中的所有属性都被选中。 形如select * 的select子句表示from子句结果关系的所有属性都被选中。

3.4.4 排列元组的显示次序

SQL为用户提供了一些对关系中元组显示次序的控制。order by子句就可以让查询结果中元组按排列顺序显示。

例子：

为了按字母顺序列出在Physics系的所有教师，我们可以这样写：

select name
from instructor
where dept_name ='Physics'order by name;

order by子句默认使用升序。要说明排序顺序，我们可以用desc表示降序，或者用asc表示升序。

此外，排序可在多个属性上进行。

例子：

假设我们希望按salary的降序列出整个instructor关系。如果有几位教师的工资相同，就将它们按姓名升序排列。我们用SQL将该查询表示如下：

select *
from instructor
order by salary desc,name asc;

3.4.5 where子句谓词

为了简化where子句，SQL提供between比较运算符来说明一个值是小于或等于某个值，同时大于或等于另一个值的。如果我们想找出工资在90000美元和100000美元之间的教师的姓名，我们可以使用between比较运算符。如下所示：

select name
from instructor
where salary between 90000 and 100000;

它可以取代：

select name
from instructor
where salary <=100000 and salary >=90000;

类似地，我们还可以使用not between比较运算符。

我们可以扩展前面看到过的查找教师名以及课程标识的查询，但考虑更复杂的情况，要求教师生物系的：“查找Biology系讲授了课程的所有教师的姓名和他们所讲授的课程”。为了写出这样的查询，我们可以在前面看到过的两个SQL查询的任意一个的基础上进行修改，在where子句中增加额外的条件。我们下面给出修改后的不使用自然连接的SQL查询形式：

select name,course_id
from instructor,teaches
where instructor.ID = teaches.ID and dept name ='Biology';

SQL允许我们用记号(V1，V2，V3，…, Vn)来表示一个分量值分别为V1，V2，…，Vn的n维元组。在元组上可以运用比较运算符，按照字典的顺序进行比较。例如，（a1, a2） <= (b1, b2)在a1 <= b1,且a2 <= b2时为真。类似的，当两个元组在所有属性上相等时，他们是相等的。这样，前述查询可被重写为如下形式：

select name, course_id
from instructor, teaches
where (instructor.ID, dept_name) = (teaches.ID, 'Biology');

3.5 集合运算

SQL作用在关系上的union、intersect和except运算对应于数学集合论中的U、∩和 - 运算。我们现在来构造包含在两个集合上使用union、intersect和except运算的查询。

源关系：

3.5.1 并运算

为了找出在2009年秋季开课，或者在2010年春季开课或两个学期都开课的所有课程，我们可写查询语句：

(select course_id
from section
where semester = 'Fall'and year =2009)
union
(select course_id
from section
where semester = 'Spring'and year=2010);

与select子句不同，union运算自动去除重复。

如果想要保留所有重复，我们需要使用union all代替union：

(select course_id
from section
where semester = 'Fall'and year =2009)
union all
(select course_id
from section
where semester = 'Spring'and year=2010);

3.5.2 交运算

为了找出在2009年秋季开课同时在2010年春季开课的所有课程，我们可写查询语句：

(select course_id
from section
where semester = 'Fall'and year =2009)
intersect
(select course_id
from section
where semester = 'Spring'and year=2010);

intersect运算自动去除重复。

如果想要保留所有重复，我们需要使用intersect all代替intersect：

(select course_id
from section
where semester = 'Fall'and year =2009)
intersect all
(select course_id
from section
where semester = 'Spring'and year=2010);

3.5.3 差运算

为了找出在2009年秋季开课但是不在2010年春季开课的所有课程，我们可写查询语句：

(select course_id
from section
where semester = 'Fall'and year =2009)
except
(select course_id
from section
where semester = 'Spring'and year=2010);

except运算自动去除重复。

如果想要保留所有重复，我们需要使用iexcept all代替except：

(select course_id
from section
where semester = 'Fall'and year =2009)
except all
(select course_id
from section
where semester = 'Spring'and year=2010);

3.6 空值

空值给关系运算带来了特殊的问题，包括算术运算、比较运算和集合运算。

对于算数运算：

如果算术表达式的任一输入为空，则该算术表达式（涉及诸如+、-、*或/）结果为空。

例如，如果查询中有一个表达式是r.A+5,并且对于某个特定的元组，r.A为空，那么对此元组来说，该表达式的结果也为空。

对于比较运算：

涉及空值的比较问题更多。例如，考虑比较运算“1<null”。因为我们不知道空值代表的是什么，所以说上述比较为真可能是错误的。但是说上述比较为假也可能是错误的，如果我们认为比较为假，那么“not(1<null)”就应该为真，但这是没有意义的。因而SQL将涉及空值的任何比较运算的结果视为unknown(既不是谓词is null,也不是is not null,我们在本节的后面介绍这两个谓词)。这创建了除true和false之外的第三个逻辑值。

由于在where子句的谓词中可以对比较结果使用诸如and、or和not的布尔运算，所以这些布尔运算的定义也被扩展到可以处理unknown值。

• and: true and unknown的结果是unknown,false and unknown结果是false,unknown and unknown的结果是unknown。
• or: true or unknown的结果是true,false or unknown结果是unknown,unknown or unknown结果是unk.own。
• not: not unknown的结果是unknown。

可以验证，如果r.A为空，那么“1<r.A”和“not(1<r.A)”结果都是unknown。

如果where子句谓词对一个元组计算出false或unknown,那么该元组不能被加入到结果集中。

SQL在谓词中使用特殊的关键词null测试空值。因而为找出instructor关系中salary为空值的所有教师，我们可以写成：

select name
from instructor
where salary is null;

如果谓词is not null所作用的值非空，那么它为真。

某些SQL实现还允许我们使用子句is unknown和is not unknown来测试一个表达式的结果是否unknown,而不是true或false。

当一个查询使用select distinct子句时，重复元组将被去除。为了达到这个目的，当比较两个元对应的属性值时，如果这两个值都是非空并且值相等，或者都是空，那么它们是相同的。所以诸**{(‘A’,null),('A’,null)}**这样的两个元组拷贝被认为是相同的，即使在某些属性上存在空值。用distinct子句会保留这样的相同元组的一份拷贝。注意上述对待空值的方式与谓词中对待空值的方是不同的，在谓词中“null=null”会返回unknown,而不是true。(这地地方就是拷贝和谓词中对null的态度不同)

如果元组在所有属性上的取值相等，那么它们就被当作相同元组，即使某些值为空。上述方式还应用于集合的并、交和差运算。

3.7 聚集函数

聚集函数是以值的一个集合(集或多重集)为输入、返回单个值的函数。

SQL提供了五个固有聚集函数：

• 平均值：avg
• 最小值：min
• 最大值：max
• 总和：sum
• 计数：count

3.7.1 基本聚集

示例1：找出2010年春季学期讲授一门课程的教师数

select count (distinct ID)
from teaches
where semester = 'Spring' and year = 2010;   

示例2：计算元组个数

select count(*)
from course;

SQL不允许在用count(*)时使用distinct。在用max和min时使用distinct是合法的，尽管结果并无差别。我们可以使用关键词all代替distinct来说明保留重复元组。但是，既然all是默认，就没必要做了。

3.7.2 分组聚集

有时候我们不仅希望将聚集函数作用在单个元组集上，而且也希望将其作用到一组元组集上；在SQL中可用group by子句实现这个愿望。group by子句中给出的一个或多个属性是用来构造分组的。在group by子句的所有属性上取值相同的元组将被分在一个组中。

示例1：

select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name;
//如果我们把group by 省略，那么我们就把整个关系作为一个分组

示例2：查询每个系在2010年春季学期讲授一门课程的教师人数

select dept_name, count(distinct ID) as instr_count
from instructor natrual join teaches
where semester = 'Spring' and year = 2010
group by dept_name;

注意：

当SQL查询使用分组时，一个很重要的事情是需要保证出现在select语句中但没有被聚集的属性只能是出现在group by子句中的那些属性。换句话说，任何没有出现在group by子句中的属性如果出现在select子句中的话，它只能出现在聚集函数内部，否则这样的查询就是错误的。

错误示例：

# 例如，下述查询是错误的，因为ID没有出现在group by子句中，但它出现在了select子句中，而且没有被聚集：
select dept_name, ID， avg(salary)
from instructor
group by dept_name;

在一个特定的分组（通过dept_name）中有多个不同的ID，既然每个分组就只输出一个元组，那就无法确定选哪一个ID值作为输出。SQL不允许这样的情况出现。

3.7.3 having语句

有时候，对分组限定条件比对元组限定条件更有用。例如，我们也许只对教师平均工资超过2000美元的系感兴趣。该条件并不针对单个元组，而是针对group by子句构成的分组。为表达这样的查询，我们使用SQL的having子句。having子句中的谓词在形成分组后才起作用，因此可以使用聚集函数。我们用SQL表达该查询如下：

示例：查询“找出系平均工资超过42000美元的那些系中教师的平均工资”的结果关系

select dept name,avg (salary)as avg_salary
from instructor
group by dept_name
having avg (salary)>42000;

结果：

下述操作序列来说明：

1. 与不带聚集的查询情况类似，最先根据from子句来计算出一个关系。
2. 如果出现了where子句，where子句中的谓词将应用到from子句的结果关系上。
3. 如果出现了group by子句，满足where谓词的元组通过group by子句形成分组。如果没有group by子句，满足where谓词的整个元组集被当作一个分组。
4. 如果出现了having子句，它将应用到每个分组上；不满足having子句谓词的分组将被抛弃。
5. select子句利用剩下的分组产生出查询结果中的元组，即在每个分组上应用聚集函数来得到单个结果元组。

3.7.4 对空值和布尔值的聚集

空值的存在给聚集运算的处理带来了麻烦。例如，假设instructor关系中有些元组在salar)y上取会值。考虑以下计算所有工资总额的查询：

select sum(salary）
from instructor;

由于一些元组在salary上取空值，上述查询待求和的值中就包含了空值。SQL标准并不认为总和本身为null,而是认为sum运算符应忽略输人中的null值。

总而言之，聚集函数根据以下原则处理空值：除了cout()外所有的聚集函数都忽略输人集合中的空值。由于空值被忽略，有可能造成参加函数运算的输入值集合为空集。规定空集的cout运算值为0，其他所有聚集运算在输人为空集的情况下返回一个空值。在一些更复杂的SQL结构中空值的影响会更难以琢磨。

在SQL:l999中引人了布尔(boolean)数据类型，它可以取true、false、unknown三个值。有两个聚集函数：some和every,其含义正如直观意义一样，可用来处理布尔(boolean)值的集合。

3.8 嵌套子查询

SQL提供子查询机制。子查询是嵌套在另一个查询的select-from-where表达式。子查询嵌套在where子句中，通常用于对集合的成员资格、集合的比较以及集合的基数进行检查。

3.8.1 集合成员资格

SQL允许测试元组在关系中的成员资格。连接词in测试元组中是否为集合中的成员，集合是由select子句产生的一组值所构成的。连接词 not in来检测元组是不是不是集合的成员。

示例1: 查询2009年秋季和2010年春季学期同时开设的课程

select distinct course_id
from section 
where semester = 'Fall' and year = 2009 and 
	course_id in (select course_id
                 from section
                 where semester = 'Spring' and year = 2010);

示例2：查询2009年秋季开课，但不在2010年春季学期开课的课程

select distinct course_id
from section 
where semester = 'Fall' and year = 2009 and 
	course_id not in (select course_id
                 from section
                 where semester = 'Spring' and year = 2010);

示例3：in和not in也可以用于枚举操作

select distinct name
from instructer
where not in("张三", "李四");

示例4：不仅可以在单关系中使用，也可以在多关系中使用

select count(distinct ID)
from takes
where (course_id, sec_id, semester, year) in (select course_id, sec_id, semester, year
                                             from teaches
                                             where teaches.ID = 10101);

3.8.2 集合的比较

示例1： 找出满足下面条件的所有教师的姓名，他们的工资至少比Biology系某一个教师的工资要高

# 我们先考虑之前的写法
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Biology';

SQL允许我们使用另一种方式来书写上面的查询。我们可以使用 > some表示。于是，我们得到如下代码：

# > some 改写
select name
from instructor 
where salary > some (select salary
					 from instructor
                     where dept_name = 'Biology');

SQL也允许<some, <= some, >= some, = some和<> some的比较。其中， = some等价于 in；然而，<>some 并不等价于not in。

注意：

SQL中关键字any同义于some。早期的SQL版本中仅允许使用any，后来的版本为了避免与英语上的混淆，又添加了另一个可选的关键词：some。

现在我们修改下我们的查询。

示例2：找出满足下面条件的所有教师的姓名，他们的工资比所有Biology系某一个教师的工资要高

select dept_name
from instructor
group by dept_name
having avg(salary) >= all (select salary
                           from instructor
                           where dept_name = 'Biology'
                          );

类似于some, SQL也允许< all, <= all, >= all, = all和<>all 的比较。其中，<>all 等价于not in；然而，=all并不等等价于in。

示例3：找出平均工资最高的系

select dept_name
from instructor
group by dept_name
having avg(salary) >= all(select avg(salary)
                          from instructor
                          group by dept_name
                         );

3.8.3 空关系测试

SQL还有一个特性可测试一个子查询结果是否存在元组。

exists结构在作为参数的子查询非空时返回true值。

示例1：找出在2009年秋季学期和2010年春季学期同时开设的课程

select course_id
from section as S
where semester = 'Fall' and year = 2009 and exists (select *
                                                   	from section as T
                                                   	where semester = 'Spring' and year = 2010 and 
                                                   	S.course_id = T.course_id);

上述查询还说明了SQL的一个特性，来自外层查询的一个相关名称（上述查询中的S)可以用在where子句的子查询中。使用了来自外层查询相关名称的子查询被称作相关子查询(correlatedsubquery)。

在包含了子查询的查询中，在相关名称上可以应用作用域规则。根据此规则，在一个子查询中只能使用此子查询本身定义的，或者在包含此子查询的任何查询中定义的相关名称。如果一个相关名称既在子查询中定义，又在包含该子查询的查询中定义，则子查询中的定义有效。这条规则类似于编程语言中通用的变量作用域规则。

我们可以用not exists结构测试子查询结果集中是否不存在元组。我们可以使用not exists结构模拟集合包含(即超集)操作：我们可将“***关系A包含关系B”写成“not exists(B except A)***”。(尽管contains运算符并不是当前SQL标准的一部分，但这一运算符曾出现在某些早期的关系系统中。)

示例2：找出选修了Biology系开设的所有课程的学生

select S.ID, S.name
from student as S
where not exists ((select course_id
                   from course
                   where dept_name = 'Biology')
                   except
                  (select T.course_id
                   from takes as T
                   where S.ID = T.ID));

3.8.4 重复元组存在测试

SQL提供一个布尔函数，用于测试一个子查询中是否存在重复元素。如果没有，返回true；有，返回false。

示例1：找出所有在2009年最多开设一次的课程

select T.course_id
from course as T
where unique (select R.course_id
              from section as R
              where T.course_id = R.course_id and R.year = 2009);

注意如果某门课程不在2009年开设，那么子查询会返回一个空的结果，unique谓词在空集上计算真值。

在不使用unique结构的情况下，上述查询的一种等价表达方式是：

select T.course_id
from course as T
where 1 >= (select count(R.course_id)
			from section as R
			where T.course_id = R.course_id and R.year=2009);

我们可以用not unique结构测试在一个子查询结果中是否存在重复元组。

示例2：找出所有在2009年最少开设两次的课程

select T.course_id
from course as T
where not unique (select R.course_id
				  from section as R
				  where T.course_id = R.course_id and R.year = 2009);

注意：

形式化地，对一个关系的unique测试结果为假的定义是，当且仅当在关系中存在着两个元组，t1和t2,且t1=t2。由于在t1或t2的某个域为空时，判断t1=t2为假，所以尽管一个元组有多个副本，只要该元组有一个属性为空，unique测试就有可能为真。

3.8.5 from子句中的子查询

SQL允许在from子句中使用子查询表达式。在此采用的主要观点是：任何select-from-where式返回的结果都是关系，因而可以被插入到另一个select-from-where中任何关系可以出现的位置。

示例1：考虑查询找出系平均工资超过4200美元的那些系中教师的平均工资

# 原来我们曾使用过having子句来重写的这个查询
select dept name,avg salary
from (select dept name,avg (salary)as augsalary
      from instructor
	  group by deptname)
where avg_salary > 42000;
# 该子查询产生的关系包含所有系的名字和相应的教师平均工资。子查询的结果属性可以在外层查使用。

注意我们不需要使用having子句，因为from子句中的子查询计算出了每个系的平均工资，在having子句中使用的谓词现在出现在外层查询的where子句中。

我们可以用as子句给此子查询的结果关系起个名字，并对属性进行重命名。如下所示：

select dept_name,avg_salary
from (select dept_name, avg(salary)
	  from instructor
      group by dept_name)
      as dept_avg (dept name,avg_salary)
      where avg_salary >42000;

子查询的结果关系被命名为dept _avg,其属性名是dept_name和awg_salary。

很多（但并非全部）SQL实现都支持在rom子句中嵌套子查询。

请注意，某些SQL实现要求每一个子查询结果关系都给一个名字，即使该名字从不被引用；Oracle允许对子查询结果关系命名（省掉关键字 as),但是不允许对关系中的属性重命名。

作为另一个例子，假设我们想要找出在所有系中工资总额最大的系。在此having子句是无能的，但我们可以用from子句中的子查询轻易地写出如下查询：

select max (tot_salary)
from (select dept_name, sum(salary)
      from instructor
	  group by dept_name)
	  as dept_total (dept_name,tot_salary);

我们注意到在from子句嵌套的子查询中不能使用来自from子句其他关系的相关变量。

然而SQL:2003允许from子句中的子查询用关键词 lateral作为前缀，以便访问from子句中在它前面的表或子查询中的属性。

例如，如果我们想打印每位教师的姓名，以及他们的工资和所在系的平均工资，可书写查询如下：

select name, salary, avg_salary
from instructor I1,lateral (select avg(salary) as avg_salary
                            from instructor 12
							where I1.dept_name = I1.dept_name);

没有lateral子句的话，子查询就不能访问来自外层查询的相关变量I1。目前只有少数SQL实现支持lateral子句，比如IBM DB2。

3.8.6 with语句

wth子句提供定义临时关系的方法，这个定义只对包含wh子句的查询有效。考虑下面的查询，它找出具有最大预算值的系。

with max budget (value) as
	(select max(budget)
	 from department)
select budget
from department,max_budget
where department.budget = maxbudget.value;

with子句定义了临时关系max_budget,此关系在随后的查询中马上被使用了。

with子句是在SQL:1999中引入的，目前有许多（但并非所有）数据库系统都提供了支持。

我们也能用from子句或where子句中的嵌套子查询书写上述查询。但是，用嵌套子查询会使得查询语句晦涩难懂。with子句使查询在逻辑上更加清晰，它还允许在一个查询内的多个地方使用视图定义。

例如，假设我们要查出所有工资总额大于所有系平均工资总额的系，我们可以利用如下with子句写出查询：

with dept_total (dept_name,value)as 
	(select dept_name,sum(salary)
     from instructor
	 group by dept_name),
	dep_total_avg(value) as
	(select avg(value)
	 from dept_total)
select dept_name
from dept_total,dept_total_avg 
where dept_total.value >= dept_total_avg.value;

我们当然也可以不用with子句来建立等价的查询，但是那样会复杂很多，而且也不易看懂。

3.8.7 标量子查询

SQL允许子查询出现在返回单个值的和表达式能够出现的任何地方。只要该子查询只返回包含单个属性的单个元组。这样的子查询称为标量子查询（scalar subquery）。

示例：一个子查询可以使用下面的例子select子句中，这个例子列出所有系以及它们拥有的教师数。

selert dept_name, 
			(select count(*)
             from instructor
             where depariment.dept_name = instructor.dept_name)
             as num instructors
from department;

上述例子中的子查询保证只返回单个值，因为它使用了不带group by的count(*)聚集函数。此例也说明了对相关变量的使用，即使用在外层查询的from子句中关系的属性，例如上例中department, dept_name。

标量子查询可以出现在select、where和having子句中。也可以不使用聚集函数来定义标量子查询。

在编译时并非总能判断一个子查询返回的结果中是否有多个元组，如果在子查询被执行后其结果中有不止一个元组，则产生一个运行时错误。

注意从技术上讲标量子查询的结果类型仍然是关系，尽管其中只包含单个元组。然而，当在表达式中使用标量子查询时，它出现的位置是单个值出现的地方，SQL就从该关系中包含单属性的单元组中取出相应的值，并返回该值。

3.9 数据库的修改

目前为止，我们的注意力一直位于数据库信息抽取上。现在，我们将展示如何使用SQL来增加、删除和修改数据。

3.9.1 删除

删除请求的表达与查询非常类似。我们只能删除整个元组，而不能只删除某些属性上的值。SQL使用如下语句表示删除：

delete from r
where P;

其中P代表一个谓词，r代表一个关系。delete语句首先从r中找出所有使P(t)为真的元组L,然后把它们从r中删除。

如果省略where子句，则r中所有元组将被删除。

注意delete命令只能作用于一个关系。如果我们想从多个关系中删除元组，必须在每个关系上使用一条delete命令。where子句中的谓词可以和select命令的where子句中的谓词一样复杂。在另一种极端情况下，where子句可以为空，请求

delete from instructor;

将删除instructor关系中的所有元组。instructor关系本身仍然存在，但它变成空的了。下面是SQL删除请求的一些例子：

• 从instructor关系中删除与Finance系教师相关的所有元组。

delete from instructor
where dept_name = 'Finance';

• 删除所有工资在13000美元到15000美元之间的教师。

delete from instructor
where salary between 13000 and 15000;

• 从instructor关系中删除所有这样的教师元组，他们在位于Watson大楼的系工作。

delete from instructor
where dept name in (select dept_name
                    from department
					where building ='Watson');

此delete请求首先找出所有位于Watson大楼的系，然后将属于这些系的instructor元组全部删除。

注意，虽然我们一次只能从一个关系中删除元组，但是通过在delete的where子句中嵌套select-from-where,我们可以引用任何数目的关系。

delete请求可以包含嵌套的select,该select引用待删除元组的关系。

例如，假设我们想删除工资低于大学平均工资的教师记录，可以写出如下语句：

delete from instructor
where salary < (select avg (salary)
			    from instructor）:

该delete语句首先测试instructor关系中的每一个元组，检查其工资是否小于大学教师的平均工资。然后删除所有符合条件的元组，即所有低于平均工资的教师。在执行任何删除之前先进行所有元组的测试是至关重要的，因为若有些元组在其余元组未被测试前先被删除，则平均工资将会改变，这样delete的最后结果将依赖于元组被处理的顺序！

3.9.2 插入

要往关系中插入数据，我们可以指定待插入的元组，或者写一条查询语句来生成待插入的元组集合。

显然，待插入元组的属性值必须在相应属性的域中。同样，待插入元组的分量数也必须是正确的。最简单的insert语句是单个元组的插入请求。假设我们想要插入的信息是Computer Science系开设的名为“Database Systems’”的课程CS437,它有4个学分。我们可写成：

insert into course
		values ('CS-437','Database Systems','Comp.Sci.', 4);

在此例中，元组属性值的排列顺序和关系模式中属性排列的顺序一致。

考虑到用户可能不记得关系属性的排列顺序，SQL允许在insert语句中指定属性。

例如，以下SQL insert语句与前述语句的功能相同。

insert into course (course_id,title,dept_name,credits)
		values ('CS-437','Database Systems','Comp.Sci.',4);
insert into course (title,course_id,credits,dept_name)
		values ('Database Systems','CS-437',4,'Comp.Sci.');

更通常的情况是，我们可能想在查询结果的基础上插入元组。假设我们想让Musc系每个修满144学分的学生成为Music系的教师，其工资为18000美元。我们可写作：

insert into instructor
	select ID, name, dept_name,18000
	from student
	where dept_name ='Music' and tot_cred > 144;

和本节前面的例子不同的是，我们没有指定一个元组，而是用select选出一个元组集合。

SQL先执行这条select语句，求出将要插人到instructor关系中的元组集合。每个元组都有ID、name、dept_name(Music)和工资(18000美元)。在执行插入之前先执行完select语句是非常重要的。如果在执行select语句的同时执行插入动作，如果在student上没有主码约束的话，像

insert into student
    select *
    from student;

这样的请求就可能会插入无数元组。如果没有主码约束，上述请求会重新插人student中的第一个元组，产生该元组的第二份拷贝。由于这个副本现在是student中的一部分，select语句可能找到它，于是第三份拷贝被插入到student中。第三份拷贝又可能被select语句发现，于是又插入第四份拷贝，如此等等，无限循环。在执行插人之前先完成select语句的执行可以避免这样的问题。这样，如果在student关系上没有主码约束，那么上述insert语句就只是把student关系中的每个元组都复制一遍。

在讨论insert语句时我们只考虑了这样的例子：待插入元组的每个属性都被赋了值。但是有可能待插人元组中只给出了模式中部分属性的值，那么其余属性将被赋空值，用null表示。考虑请求：

insert into student
	values('300',Green','Finance’, null);

此请求所插人的元组代表了一个在Finance系、ID为3003”的学生，但其tot_cred值是未知的。

考虑查询：

select ID
from student
where tot cred>45:

既然“3003”号学生的以tot_cred值未知。我们不能确定它是否大于45。

大部分关系数能库产品有特殊的“bulk loader”工具，它可以向关系中插入一个非常大的元组集台。

这些工具允许从格式化文本文件中读出数据，且执行速度比同等目的的插人语句序列要快得多。

3.9.3更新

有些情况下，我们可能希望在不改变整个元组的情况下改变其部分属性的值.为达到这一目的，我们可以使用update语句。

与使用insert、delete类似，待更新的元组可以用查询语句找到。

示例1：假设要进行年度工资增长，所有教师的工资将增长5%。

update instructor
set salary salary*1.05;

上面的更新语句将在instructor关系的每个元组上执行一次。

示例2：如果只给那些工资低于70000美元的教师涨工资

update instructor
set salary salary*1.05
where salary <70000;

总之，updatei语句的where子句可以包含selecti语句的where子句中的任何合法结构（包括嵌套select)。和insert、delete类似，update语句中嵌套的select可以引用待更新的关系。同样，SQL首先检查关系中的所有元组，看它们是否应该被更新，然后才执行更新。例如，请求“对工资低于平均数的教师涨5%的工资”可以写为如下形式：

update instructor
set salary = salary*1.05
where salary <(select avg (salary)
               from instructor);

我们现在假设给工资超过100000美元的教师涨3%的工资，其余教师涨5%。我们可以写两条update语句：

update instructor
set salary =salary*1.03
where salary > 100000;

update instructor
set salary salary'1.05
where salary <= 100000;

注意这两条update语句的顺序十分重要。假如我们改变这两条语句的顺序，工资略少于1000美无的教师将增长8%的工资。

SQL提供case结构，我们可以利用它在一条update语句中执行前面的两种更新，避免更新次序发的问题：

update instructor
set salary = case
	when salary = 100000 then salary*1.05
	else salary*1.03
end

case语句的一般格式如下：

case
    when pred1,then result1,
    when pred2,then result2,
    …
    when predn,then resultn
    else result0
end

当i是第一个满足的pred1,pred2,…predn,时，此操作就会返回result;如果没有一个谓词可以满足，则返回resulto。

case语句可以用在任何应该出现值的地方。

标量子查询在SQL更新语句中也非常有用，它们可以用在set子句中。考虑这样一种更新：我们把每个student元组的tot_cred属性值设为该生成功学完的课程学分的总和。我们假设如果一个学生在某门课程上的成绩既不是’F’,也不是空，那么他成功学完了这门课程。我们需要使用set子句中的子查询来写出这种更新，如下所示：

update student S
set tot_cred = (
	select sum(credits)
	from takes natural join course
    where S.ID = takes.ID and
        takes.grade <>'F'and
        takes.grade is not null);

注意子查询使用了来自update语句中的相关变量S。如果一个学生没有成功学完任何课程，上述更新语句将把其tot_cred属性值设为空。如果想把这样的属性值设为0的话，我们可以使用另一条pdate语句来把空值替换为0。更好的方案是把上述子查询中的“select sum(credits)”子句替换为如下使用case表达式的select子句：

select case
when sum(credits)is not null then sum(credits)
else 0
end