[大数据]《数据库系统概念》学习笔记——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包括定义对关系和视图的访问权限的命令。

SQL数据定义

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

每个关系的模式。
每个属性的取值类型。
完整性约束。
每个关系维护的索引集合。
每个关系的安全性和权限信息。
每个关系在磁盘上的物理存储结构。

基本类型

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

• char(n) 固定长度字符串，用户指定长度n。也可以使用全称 character。
• varchar(n) 变化长度字符串，用户指定最大成对n，等价于全称 character varying。
• int 整数类型（和机器相关的整数的有限子集），等价于全称 integer。
• smallint 小整数类型（和机器相关的整数类型的子集）。
• numeric(p,d) 定点数，精度由用户指定。p位数字，其中d位在小数点右边。
• real, double precision 浮点数与双精度浮点数，精度与机器相关。
• float(n) 精度至少为n位的浮点数。

每种类型可包含一个被称作空值的特殊值。

基本模式定义

用 create table 命令定义SQL关系。

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

create table 命令的通用形式是：

 
create table r
	(A1 D1,
	A2 D2,
	. . . ,
	An Dn,
	<完整性约束1>,
	. . . ,
	<完整性约束k>);

SQL支持许多不同的完整性约束：

• $primarykey(A_{j1},A_{j2},...,A_{jm})$
  表示属性$A_{j1},A_{j2},...,A_{jm}$构成关系的主码。
  主码属性必须非空且唯一。
  主码的声明是可选的。

• $foreignkey(A_{j1},A_{j2},...,A_{jm})references$
  foreign key声明表示关系中任意元组在属性 $A_{j1},A_{j2},...,A_{jm}$上的取值必须对应于关系s中某元组在主码属性上的取值。

• not null
  一个属性上的not null约束表明在该属性上不允许空值。

// 向关系插入数据
	insert into instructor
// 值的顺序应遵循对应属性在关系模式中列出的顺序
	values (10211, ’Smith’, ’Biology’, 66000);

// 从student关系中删除所有元组
	delete from student;

// 比delete from r;更强
// 不仅删除r的所有元组，还删除r的模式。
// 此后，除非用create table命令重建r，否则没有元组可以插入r。
	drop table r;

// r是现有关系的名字
// A是待添加属性的名字
// D是待添加属性的域
// 关系中所有元组在新属性上的取值被设为null
	alter table r add A D;

// 从关系r中去掉属性A
	alter table r drop A;

SQL查询的基本结构

SQL查询的基本结构由三个子句构成：select、 from 和 where。

查询的输入是在from子句中列出的关系。
在这些关系上进行where和select子句中指定的运算，然后产生一个关系作为结果。

单关系查询

简单查询的例子：“找出所有教师的名字”

select name
from instructor;

结果是属性名为name的单个属性构成的关系

找出所有教师所在的系名：

select dept_name
from instructor;

distinct 显式指明去除结果中的重复

select distinct dept_name
from instructor;

all 显式指明不去除结果中的重复

select all dept_name
from instructor;

select子句还可带含有 +、-、*、/ 运算符的算术表达式，运算对象可以是常数或元组的属性。

select ID, name, dept_name, salary * 1.1
from instructor;

每个instructor的一个元组对应返回关系中一个元祖，返回关系中元组包含4个属性，取值和instructor对应元组对应属性值的关系分别为1,1,1,1.1倍。

where子句允许我们只选出在from子句结果关系中满足特定谓词的元祖。

select name
from instructor
where dept name = ’Comp. Sci.’ and salary > 70000;

SQL允许在where子句中使用逻辑连词and，or或not。
逻辑连词的运算对象可以是包含比较运算符<, <=, >, >=, =, <>的表达式。
SQL允许我们用比较运算符来比较字符串，算术表达式，及特殊类型，如日期类型。

多关系查询

查询：“找出所有教师的姓名，以及他们所在系的名称和系所在建筑的名称。”
对出现在多个关系的属性，需要在关系限定来去除二义性。仅出现在多个关系中一个中的属性可以不必。

select name, instructor.dept name, building
from instructor, department
where instructor.dept name= department.dept name;

查询的结构关系如下图所示：

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

一个典型的SQL查询具有如下形式：

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

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

通过from子句定义了一个在该子句中所列出关系上的笛卡尔积。
可用下面迭代过程来理解，此过程可为from子句的结果关系产生元组

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

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

关系instructor 和 teaches 的笛卡尔积的关系模式为：

(instructor.ID, instructor.name, instructor.dept name, instructor.salary
teaches.ID, teaches.course id, teaches.sec id, teaches.semester, teaches.year)

对只出现在单个模式中的属性，可以去掉关系名前缀来简化。进而写成：

(instructor.ID, name, dept name, salary
teaches.ID, course id, sec id, semester, year)

其他多关系查询举例：

select name, course id
from instructor, teaches
where instructor.ID= teaches.ID and instructor.dept name = ’Comp. Sci.’;

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

1. 为from子句中列出的关系产生笛卡尔积。
2. 在步骤1的结果上应用where子句中指定的谓词。
3. 对于步骤2结果中的每个元组，输出select子句中指定的属性。

自然连接

自然连接（natural join）运算作用于两个关系，产生一个关系作为结果。。
自然连接只考虑哪些在两个关系模式中都出现的属性上取值相同的元组对。

查询“对于大学中所有讲授课程的教师，找出他们的姓名以及所讲述的所有课程标识”：

select name, course id
from instructor, teaches
where instructor.ID= teaches.ID;

等价的自然连接

select name, course id
from instructor natural join teaches;

自然连接的更一般形式
自然连接运算的结果是一个关系。

用自然连接将多个关系结合在一起：

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

更一般形式的from

from E1, E2, . . . , En

每个E_{i}可以是单个关系或一个包含自然连接的表达式

查询：“列出教师的名字以及他们所讲授课程的名称”

select name, title
// 自然连接关系和course关系的笛卡尔积作为大集合
from instructor natural join teaches, course
// 大集合中过滤条件
where teaches.course id= course.course id;

SQL提供了一种自然连接的构造形式，允许用户来指定需要哪些列相等。

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

附加的基本运算

更名运算

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

old-name as new-name

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

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;

重命名关系的另一个原因是为了适用于需要比较同一个关系中的元组的情况。

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）。

字符串运算

SQL使用一对单引号来标示字符串，例如 ‘Computer’。
如果单引号是字符串的组成部分，那就用两个单引号字符来表示，如字符串 “it’s right” 表示为 “it’'s right”。

在SQL标准中，字符串的相等运算是大小写敏感的，所以表达式 " ‘comp.sci’ = ‘Comp.Sci’ "的结果是假。

SQL还允许在字符串上有多种函数，例如串联（使用“ || ”）、提取子串、计算字符串长度、大小写转换（用 upper(s) 将字符串s转换为大写或用 lower(s) 将字符串 s 转换为小写）、去掉字符串后面的空格（使用 trim(s) ），等等。

在字符串上可以使用 like 操作符来实现模式匹配。
使用两个特殊的字符来描述模式：

• 百分号（%）：匹配任意子串。
• 下划线（_）：匹配任意一个字符。

模式是大小写敏感的，也就是说，大写字符与小写字符不匹配，反之亦然。
例：

• ‘Intro%’ 匹配任何以 “Intro” 打头的字符串。
• ‘% Comp%’ 匹配任何包含 “Comp” 子串的字符串。
• ‘_ _ _’ 匹配只含三个字符的字符串。
• ‘_ _ _%’ 匹配至少含三个字符的字符串。

在SQL中用比较运算符 like 来表达模式。

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 比较运算符搜寻不匹配项。

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

select 子句中的属性说明

星号 “ * ”可以用在 select 子句中表示“所有的属性”。

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

表示 instructor 中的所有属性都被选中。

排列元组的显示次序

order by 子句可以让查询结果中元组按排列顺序显示。

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

order by 子句默认使用升序。

说明顺序排序，可以用 desc 表示降序， 用 asc 表示升序。
排序可在多个属性上进行：

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

where 子句谓词

between 比较运算符来说明一个值是小于或等于某个值，同时大于或等于另一个值的。

select name
from instructor
where salary between 90000 and 100000;

它可以取代

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

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

SQL允许用记号(v1, v2, …, vn)来表示一个分量值分别为 v1, v2, … , vn 的 n 维元组。
在元组上可以运用比较运算符，按字典顺序进行比较运算。
例如，(a1, a2) <= (b1, b2) 在 a1<=b1 且 a2<=b2时为真。类似地，当两个元组在所有属性上相等时，它们是相等的。

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

等于

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

集合运算

SQL作用在关系上的 union、intersect 和 except 运算对应于数学集合论中的?、? 和 - 运算。

例，构造集合

• 在2009年秋季学期开设的所有课程的集合：

select course_id
from section
where semester = 'Fall' and year = 2009;

• 在2010年春季学期开设的所有课程的集合：

select course_id
from section
where semester = 'Spring' and year = 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);

交运算

(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);

差运算

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

except 运算从其第一个输入中输出所有不出现在第二个输入中的元组，也即它执行集差操作。
此运算在执行集差操作之前自动去除输入中的重复。

保留所有重复，必须使用 except 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);

空值

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

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

SQL将涉及空值的任何比较运算的结果视为 unknow 。

布尔运算的定义可以处理unknow值：

• and： true and unknow 的结果是 unknow，false and unknow 结果是 false，unknow and unknow 的结果是 unknow。
• or： true or unknow 的结果是true，false or unknow 的结果是unknow，unknow or unknow 的结果是 unknow。
• not：not unknow 的结果是unknow。

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

SQL在谓词中使用特殊的关键词 null 测试空值。

select name
from instructor
where salary is null;

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

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

当一个查询使用 select distinct 子句时，重复元组将被去除。

聚集函数

聚集函数是以值的一个集合（集或多重集）为输入、返回单个值的函数。
SQL提供了五个固有聚集函数：

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

sum 和 avg 的输入必须是数字集，但其他运算符还可以作用在非数字数据类型的集合上，如字符串。

基本聚集

查询平均工资：

select avg(salary)
from instructor
where dept_name = 'Comp. Sci.';

数据库系统可以给结果关系的属性一个任意的名字，该属性是由聚集产生的。
可以使用 as 子句给属性赋个有意义的名称：

select avg(salary) as avg_salary
from instructor
where dept_name = 'Como. Sci.';

在计算平均值时保留重复元组是很重要的。

有些情况下载计算聚集函数前需先删掉重复元组。
可在聚集表达式中使用关键词 distinct ，删除重复元组。

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

使用聚集函数 count 计算一个关系中元组的个数。
SQL中该函数的写法是 count(*)。

select count(*)
from course;

SQL不允许在用 count(*) 时使用 distinct 。
在使用 max 和 min 时使用 distinct 是合法的。

分组聚集

SQL中可用 group by 子句将聚集函数作用到一组元组集上。
group by 子句中给出的一个或多个属性是用来构造分组的。
在group by 子句中的所有属性上取值相同的元组将被分在一个组中。

示例：“找出每个系的平均工资”

select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name;

查询“找出所有教师的平均工资”

select avg(salary)
from instructor;

在这里省略了group by子句，因此整个关系被当作是一个分组。

作为在元组分组上进行聚集操作的另一个例子，查询“找出每个系在2010年春季学期讲授一门课程的教师人数”：

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

任何没有出现在 group by 子句中的属性如果出现在 select 子句中的话，它只能出现在聚集函数内部，否则这样的查询就是错误的。

/*错误查询*/
select dept_name, ID, avg(salary)
from instructor
group by dept_name;

having 子句

having 子句中的谓词在形成分组后才起作用，因此可以使用聚集函数。
查询如下：

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

任何出现在 having 子句中，但没有被聚集的属性必须出现在 group by 子句中，否则查询就被当成是错误的。

包含聚集、group by 或 having 自己的查询的含义可通过下述操作序列来定义：

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

在同一个查询中同时使用 having子句和 where子句的情况：

select course_id, semester, year, sec_id, avg(tot_cred)
from takes natural join student
where year = 2009
group by course_id, semester, year, sec_id
having count(ID) >= 2;

对空值和布尔值的聚集

聚集函数根据以下原则处理空值：除了 count(*) 外所有的聚集函数都忽略输入集合中的空值。
由于空值被忽略，有可能造成参加函数运算的输入值集合为空集。
规定空集的 count 运算值为0，其他所有聚集运算在输入为空集的情况下返回一个空值。

有两个聚集函数： some 和 every，可用来处理布尔（boolean）值的集合。

嵌套子查询

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

集合成员资格

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

查询“在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);

in 和 not in 操作符也用于枚举集合。

select distinct name
from instructor
where name not in ('Mozart','Einstein')

在SQL中可测试任意关系的成员资格。

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);

集合的比较

嵌套子查询能够对集合进行比较的例子：

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

SQL 提供另外一种方式书写上面的查询。

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

SQL也允许 < some， <= some，>= some， = some 和 <> some 的比较。

结构 > all 对应于词组“比所有的都大”。查询如下：

select name
from instructor
where salary > all(select salary
					from instructor
					where dept_name = 'Biology');

SQL也允许 < all， <= all，>= all， = all 和 <> all 的比较。

作为集合比较的另一个例子：

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

空关系测试

SQL还有一个特性可测试一个子查询的结果中是否存在元组。
ezists 结构在作为参数的子查询非空时返回 true 值。

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);

使用来自外层查询相关名称的子查询被称为相关子查询（correlated subquery）。

not exists 结构测试子查询结果集中是否不存在元组。

查询“找出选修了Biology系开设的所有课程的学生”

select distinct 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));

重复元组存在性测试

SQL提供了一个布尔函数，用于测试在一个子查询的结果中是否存在重复元组。
如果作为参数的子查询结果中没有重复的元组，unique 结构将返回 true 值。

// 找出所有在2009年最多开设一次的课程
select T.course_id
// 所有课程集合
from course as T
// 在排的2009的T.course_id唯一的被选出
where unique 
(select R.course_id
// 所有已排课程集合
from section as R
// 选出T.course_id在2009已经在排课程集合。选出course_id属性。
where T.course_id= R.course_id and
	R.year = 2009);

等价于

select T.course_id
// 所有课程
from course as T
// 如果T.course_id在2009开设课程数小于等于1，则被选出
where 1 <= 
(
// 在唯一组使用聚集函数，得到只含一个元组的结果，这里是个数
select count(R.course id)
// 开设课程集合
from section as R
// 课程T.course_id在2009在开设课程被选出
where T.course id= R.course id and
	R.year = 2009);

unique谓词在空集上计算出真值。
not unique测试在一个子查询结果中是否存在重复元组。

形式化地，对一个关系的unique测试结果为假的定义是，当且仅当在关系中存在着两个元组t1和t2，且t1=t2。
有时一个元组有多个副本，但某个属性为空时，由于t1=t2此时为假，故unique测试可能为真。

from 子句中的子查询

任何 select-from-where 表达式返回的结果都是关系。
因而可以被插入到另一个 select-from-where 中任何关系可以出现的位置。

// 找出系平均工资超过42000美元的那些系中教师的平均工资

// 选出属性 系名，平均工资
select dept_name, avg_salary
// 所有系的系名，平均工资构成的集合
from (
// 使用聚集函数，每个分组产生一个结果元组。选出属性 系名，聚集结果：平均工资
select dept_name, avg (salary) as avg_salary
// 教师集合
from instructor
// 按系分组
group by dept_name)
// 平均工资大于42000，则选出
where avg_salary > 42000;

可用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;

// 找出在所有系中工资总额最大的系
select max (tot_salary)
from (select dept_name, sum(salary)
	  from instructor
	  group by dept_name) 
	  as dept_total (dept_name, tot_salary);

with 子句

with子句提供定义临时关系的方法。定义只对包含with子句的查询有效。

// 找出具有最大预算值的系
with max_budget (value) as
	(select max(budget)
	 from department)
// 选出budget属性
select budget
// 部分与只含一个元组关系笛卡尔积
from department, max_budget
// 选出部分值为max_budget.value的
where department.budget = max_budget.value;

// 查出所有工资总额大于所有系平均工资总额的系
with dept_total (dept_name, value) as
	(select dept_name, sum(salary)
	from instructor
	group by dept_name),
	dept_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;

标量子查询

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

// 列出所有的系及它们拥有的教师数
select dept_name,
	(select count(*)
	from instructor
	where department.dept_name = instructor.dept name)
	as num_instructors
from department;

数据库的修改

删除

只能删除整个元组。

delete from r
where P;

删除满足where的所有元组。where为空时，删除所有元组。

// 删除Finance系教师
delete from instructor
where dept_name= ’Finance’;
	
// 删除工资在13000美元到15000美元之间的教师
delete from instructor
where salary between 13000 and 15000;

// 删除在Watson大楼的系工作的教师元组
delete from instructor
where dept_name in (select dept_name
					from department
					where building = ’Watson’);

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

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

// 删除工资低于大学平均工资的教师
delete from instructor
where salary< (select avg (salary)
			   from instructor);

插入

可以指定待插入的元组，或者写一条查询语句来生成待插入元组集合。

// 插入Computer Science系开设的名为“Database Systems”的课程CS-437
insert into course
// 元组属性值的排列顺序和关系模式中属性排练了的顺序一致
	values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4);

等价

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.’);

在查询结果的基础上插入元组

// 让Muisc系每个修满144学分的学生称为Music系的教师，工资为18000$
insert into instructor
	select ID, name, dept_name, 18000
	from student
	where dept_name = ’Music’ and tot_cred > 144;

待插入元组中只给出了模式中部分属性的值，其余属性将被赋空值，用null表示。

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

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

select student
from student
where tot_cred > 45;

更新

希望在不改变整个元组的情况下改变其部分属性的值。
为达到这一目的，可以使用 update 语句。
与使用insert、delete类似，待更新的元组可以用查询语句找到。

// 所有教师工资增长5%
update instructor
set salary= salary * 1.05;

// 所有工资低于70000美元的教师，工资增加5%
update instructor
set salary = salary * 1.05
where salary < 70000;

// 对工资低于平均数的教师涨5%的工资，可写为
update instructor
set salary = salary * 1.05
// 条件
where salary < (select avg (salary)
				from instructor)

// 给工资超过100000$的教师涨3%的工资，其余教师涨5%
update instructor
set salary = salary * 1.03
where salary > 100000;

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

SQL首先检查关系中的所有元组，看它们是否应该更新，再更新。

SQL提供case，可在一条update语句执行前面的两种更新，避免更新次序引发的问题。

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

case语句一般格式：

// 当i是第一个满足的pred时，此操作返回result
// 如果没有一个谓词可以满足，则返回result0
// case语句可用在任何该出现值的地方
case
	when pred1 then result1
	when pred2 then result2
	. . .
	when predn then resultn
	else result0
end

// 我们把每个student元组的tot_cred属性值设为该生成功学完的课程学分的总和。
// 设一个学生在某门课程上的成绩既不是'F'，也不是空，
// 则他成功学完了这门课程
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);

更好的做法

// 把select sum(credits)替换为
select case 
	when sum(credits) is not null then sum(credits)
	else 0
	end

学习参考资料：

《数据库系统概念》第6版