初级 SQL

2.1 SQL 数据定义

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

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

在此只包含基本模式定义和基本类型。

2.1.1 基本类型

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

每种类型都可能包含一个被称作空值的特殊值。在可能的情况下，我们希望禁止加入空值。

2.1.2 基本模式定义

create table 命令定义 SQL 关系。通用形式为：

/*r为关系名，A为属性名，D为属性A的域*/
create table r(
	A1	D1,
	A2	D2,
	...
	An	Dn,
	<完整性约束1>,
	...
	<完整性约束n>,
);

• primary key(A₁, A₂, …, A_n) : primary key 声明表示属性A1, A2, …, An 构成关系的主码。
• foreign key(A₁, A₂, …, A_n) references : foreign key 声明表示关系中任意元组在属性A₁, A₂, …, A_n上的取值必须对应于关系 s 中某元组在主码属性上的取值。
• not null : 一个属性上的 nut null 约束表明在该属性上不允许空值。

SQL 禁止破坏完整性约束的任何数据库更新。(主码属性上有空、元组在主码属性上的取值与另一个元组相同)

insert 命令加载数据。形式如下：

insert into r values(d1, d2, ..., dn);

delete 命令删除元组。形式如下：

/*删除所有元组*/
delete from r;

drop table 命令去掉一个关系。形式如下：

drop table r;

alter table 命令为已有关系修改属性。关系中所有元组在新属性上的取值将被设为 null。形式如下：

/*增加属性，r为现有关系名，A是待添加属性名，D是待添加属性域*/
alter table r add A D;
/*去掉属性，很多数据库并不支持*/
alter table r drop A;

2.2 SQL 查询的基本结构

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

2.2.1 单关系查询

查询形式如下：

select A1, A2, ..., An
from r
where P;

• 在关系模型的形式化数学定义中，关系是一个集合。因此，重复的元组不会出现在关系中。在实践中，去除重复是相当费时的，所以 SQL 允许在关系以及 SQL 表达式结果中出现重复。有时想强行去除重复，可在 select 后加入 distinct 。
• SQL 也允许使用关键词 all 来显示指明不去除重复。
• select 子句还可含有 +、-、*、/ 运算符的算术表达式，运算对象可以是常数或元组的属性。
• where 子句允许只选出那些在 from 的结果关系中满足特定谓词的元组。
• SQL 允许 where 子句中使用逻辑连词 and、or 和 not 。逻辑连词的运算对象可以是包含比较运算符<、<=、>、>=、= 和 <>的表达式。SQL 允许使用比较运算符来比较字符串、算术表达式以及特殊类型，如日期类型。

2.2.2 多关系查询

查询形式如下;

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

• select 子句用于列出查询结果中所需要的属性。
• from 子句是一个查询求职中需要访问的关系列表。
• where 字句是一个作用在 from 子句中关系的属性上的谓词。如果省略 where 子句。则谓词 P 为 true 。

2.2.3 自然连接

自然连接运算作用于两个关系，并产生一个关系作为结果。不同于笛卡尔积 ，自然连接只考虑来自两个关系的元组在共同属性上的取值相同的元组对。
在一个SQL 查询的 from 子句中，可以用自然连接将多个管子结合在一起，形式如下：

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

更为一般的，from 子句可以为如下形式：

/*其中每个 Ei可以是单个关系或包含一个自然连接的表达式。*/
from E1, E2, ..., En

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

from r1 jion r2 using (A1, A2, ..., Ai)

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

2.3 附加的基本运算

2.3.1 更名运算

SQL 提供了一个重命名结果关系中属性的方法。适用于不同关系中含有同名属性或者比较同一个关系中的元组的情况。形式如下：

/*as子句既可出现在 select子句中，也可出现在 from子句中*/
old-name as new-name

2.3.2 字符串运算

SQL 用一对单引号来标示字符串，如果单引号是字符串的组成部分，那就用两个单引号字符来表示。在 SQL 标准中，字符串上的相等运算是大小写敏感的，然而一些数据库系统，如 MySQL 和 SQL Server，在匹配字符串时并不区分大小写，这种默认方式是可以修改的。

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

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

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

SQL 允许定义转义字符。转义字符直接放在特殊字符的前面，表示该特殊字符被当成普通字符。在 like 比较运算中使用 escape 关键词来定义转义字符，形式如下：

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

2.3.3 select 子句中的属性说明

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

2.3.4 排列元素的显示次序

SQL 提供了一些对关系中元组显示次序的控制。order by 子句就可以让查询结果中元组按排列次序显示，默认使用升序，可以用 desc 表示降序，或者用 asc 表示升序。

2.3.5 where 子句谓词

SQL 提供 between 运算符来说明一个值是大于等于某个值同时小于等于某个值。

SQL 允许我们使用记号（v1, v2, …, vn）来表示一个 n 维元组。在元组上可以使用比较运行算符，按字典顺序进行比较运算。

2.4 集合运算

SQL 作用在关系上的 union、intersect 和 except 运算对应于数学中的 ⋃、⋂ 和 - 运算。

• union 运算自动去除重复。如果想保留重复，就必须使用 union all 代替 union 。
• intersect 运算自动去除重复。如果想保留重复，就必须使用 intersect all 代替 intersect 。
• except 运算自动去除重复。如果想保留重复，就必须使用 except all 代替 except 。

2.5 空值

SQL 将涉及空值的任何比较运算的结果视为 unknown（这创建了除 true 和 false 之外的第三个逻辑值。）

2.6 聚集函数

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

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

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

2.6.1 基本聚集

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

select count(distinct A)

使用聚集函数计算一个关系中元组的个数：

/*SQL不允许在用 count(*)时使用 distinct*/
select count(*)
from r;

2.6.2 分组聚集

group by 子句中给出一个或多个属性用来构造分组，在 group by 子句中的所有属性上取值相同的元组将被分在一个元组中。

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

2.6.3 having 子句

having 子句中的谓词在形成分组后才起作用。即只针对 group by 形成的分组，而不针对单个分组。

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

操作序列：

1. 根据 from 子句来计算出一个关系。
2. where 子句中的谓词将应用到 from 子句的结果关系上。
3. 满足 where 谓词的元组通过 group by 子句形成分组。
4. having 子句将应用到每个分组上，不满足 having 子句谓词的分组将被抛弃。
5. select 子句利用剩下的分组产生出查询结果中的元组，在每个分组上应用聚集函数来得到单个结果元组。

2.6.4 对空值和布尔值的聚集

• 空值的 count 运算值为0
• 除了 count(*) 外所有的聚集函数都忽略输入集合中的空值。

2.7 嵌套子查询

2.7.1 集合成员资格

• SQL 允许测试元组在关系中的成员资格。连接词 in 测试元组是否是集合中的成员，集合是由 select 子句产生的一组值构成的。连接词 not in 则测试元组是否不是集合中的成员。
• in 和 not in 操作符也能用于枚举集合。

2.7.2 集合的比较

• 在 SQL 中，短语 “ 至少比某一个要大 ” 用 > some (子查询) 表示。
• SQL 也允许 < some ，>= some ，<= some ，= some ，<> some
  > all ，< all ，>= all ，<= all ，= all ，<> all 。

2.7.3 空关系测试

• SQL 有一个特性可测试一个子查询的结果中是否存在元组。exists 结构在作为参数的子查询非空时返回 true 值。
• not exists 结构测试子查询结果集中是否不存在元组。可以用 not exists 结构模拟集合包含操作：可将 “ 关系 A 包含关系 B ” 写成 “ not exists ( B except A ) ” 。

2.7.4 重复元组存在性测试

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

2.7.5 from 子句中的子查询

SQL 允许在 from 子句中使用子查询表达式，可以使用 as 子句给此子查询的结果关系起个名字，并对属性进行重命名。

2.7.6 with 子句

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

2.7.7 标量子查询

SQL 允许子查询出现在返回单个值的表达式能够出现的任何地方，只要该子查询只返回单个属性的单个元组，这样的子查询称为标量子查询。标量子查询可以出现在 select、where 和 having 子句中。

2.8 数据库的修改

2.8.1 删除

删除请求的表达与查询较为相似。只能删除整个元组，而不能只删除某些属性上的值。形式如下：

delete from r
where P;

• delete 命令只能作用于一个关系，若想从多个关系中删除元组，必须在每个关系上使用一条 delete 命令。当 where 子句为空时，将删除整个关系中的所有元组。但关系本身仍然存在，它只是变成空的了。
• 在执行任何删除之前先进行所有元组的测试是至关重要的，因为若有些元组在其余元组未被测试前先被删除，则测试信息将会发生改变，这样 delete 的最后结果将依赖于元组被处理的顺序。

2.8.2 插入

最简单的 insert 语句是单个元组的插入请求。更通常的情况是，可能想在查询结果的基础上插入元组。形式如下：

/*在插入之前会先执行完 select语句*/
insert into r
	select A1, A2, ..., An
	from s
	where P;

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

2.8.3 更新

update 语句在不改变整个元组的情况下改变其部分属性的值，待更新的元组可以用查询语句找到。形式如下：

update r
set old-d = new-d
where P

SQL 提供 case 结构进行不同条件时对应的不同更新。形式如下：

update r
set d =
	case
		when pred1 then result1
		when pred2 then result2
		...
		when predn then resultn
		else result0
	end

2.9 总结

• SQL 语言包括几个部分：
  • 数据定义语言(DDL)，它提供了定义关系模式、删除关系以及修改关系模式的命令。
  • 数据操纵定义(DML)，它包括查询语言，以及往数据库中插入元组、从数据库中删除元组和修改数据库中元组的命令。
• SQL 的数据定义语言用于创建具有特定模式的关系。除了声明关系属性的名称和类型之外，SQL 还允许声明完整性约束，例如主码约束和外码约束。
• SQL 提供多种用于查询数据库的语言结构，其中包括 select、from 和 where 子句。SQL 支持自然连接操作。
• SQL 还提供了对属性和关系重命名，以及对查询结果按特定属性进行排序的机制。
• SQL 支持关系上的基本集合运算，包括并、交和差运算。
• SQL 支持聚集，可以把关系进行分组，在每个分组上单独的运用聚集。SQL 还支持在分组上的集合运算。
• SQL 支持在外层查询的 where 和 from 子句中嵌套子查询，它还在一个表达式返回的单个值所允许出现的任何地方支持标量子查询。
• SQL 提供了用于更新、插入、删除信息的结构。