数据库系统概念 - 03

发表于 更新于 分类于 阅读次数： Waline：

《数据库系统概念（原书第 6 版）》读书笔记。

第一部分：2~6 章，详细讲解了关系型数据库。

第三章 SQL

3、4、5 章将会讲解使用最为广泛的查询语言 SQL，讲解的目的并不是提供一个完整的 SQL 用户手册，而是介绍 SQL 的基本结构和概念。

  • 第 3 章：对 SQL 的基本 DML 和 DDL 特征的概述；
  • 第 4 章：对 SQL 更详细的介绍（各种链接的表达、视图、事务、完整性约束等等）；
  • 第 5 章：SQL 高级特征：
    • 允许从编程语言中访问 SQL 的机制；
    • SQL 函数和过程
    • 触发器
    • 递归查询
    • 高级聚集特征

3.1 SQL 查询语言概览

SQL 最早叫 Squeal，由 IBM 开发，后来名称更改为 SQL(Structured Query Language)。

1986 年美国国家标准化组织（ANSI）和国际标准化组织（ISO）发布了 SQL 标准：SQL—86。现在 SQL 最新的版本是 SQL：2008。

截止到 2022 年 9 月 29 日，SQL 最新的版本为 SQL: 2016.

SQL 语言标准由以下几个部分组成：

  • 数据定义语言（Data-Definition Language，DDL）：DDL 提供定义关系模式、删除关系以及修改关系模式的命令。
  • 数据操纵语言（Data-Manipulation Language，DML）：DML 提供增删改查数据库的能力。
  • 完整性（integrity）：保存在数据库中的数据必须满足所定义的完整性约束。破坏完整性约束的更新是不允许的。
  • 视图定义（view definition）：定义视图的命令。
  • 事务控制（transaction control）：定义事务的开始和结束的命令。
  • 嵌入式 SQL 和动态 SQL（embedded SQL and dynamic SQL）：定义 SQL 语句如何嵌入到通用编程语言，如 C、C++和 Java 中。
  • 授权（authorization）：SQL DDL 包括定义对关系和视图的访问权限的命令。

SQL 是一种标准，MySQL、ORACLE、PostgreSQL、SQLServer 等是对标准的实现，且在标准之上添加了一些自有的特征。SQL 和他们的关系类似于 ES 规范和各家浏览器对 ES 规范的实现。

3.2 SQL 数据定义

3.2.1 基本类型

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

  • char(n)/character(n)：固定长度的字符串，用户指定长度 n。
  • varchar(n)/character varying(n)：可变长度的字符串，用户指定最大长度 n。
  • int/integer：整数类型（和机器相关的整数的有限子集）。
  • smallint：小整数类型（和机器相关的整数类型的子集）。
  • numeric(p, d)：定点数，精度由用户指定。
    • 这个数有 p 位数字（加上一个符号位），其中 d 位数字在小数点右边。
    • 所以在一个这种类型的字段上，numeric(3，1) 可以精确储存 44.5，但不能精确存储 444.5 或 0.32 这样的数。
  • real, double precision：浮点数与双精度浮点数，精度与机器相关。
  • float(n)：精度至少为 n 位的浮点数。

注意：

  • 每种类型都可能包含一个被称作空值的特殊值。空值表示一个缺失的值，该值可能存在但并不为人所知，或者可能根本不存在。在可能的情况下，我们希望禁止加入空值。
  • char 数据类型存放固定长度。例如：属性 A 的类型是 char(10)，当我们传入Avi时，该字符串会追加 7 个空格来使其达到 10 个字符的长度。varchar 类型则不会这样做。
  • 当 char 和 varchar 存入相同的字符Avi时，两者是否相等取决于数据库管理系统的实现，因此始终建议使用 varchar 类型。

3.2.2 基本模式定义

数据库模式：数据库的逻辑设计；
这里讲的是设计关系（表）时一些常用的命令。

使用 create table 定义 SQL 关系（表）。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 通用形式
create table r (
  A1 D1,
  A2 D2,
  ...,
  An Dn,
  <完整性约束>,
  ...,
  <完整性约束>,
);

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

常见的完整性约束命令：

  • primary key(Aj1, Aj2, …, Ajm)：声明主键，主键属性必须非空且唯一。主键约束是可选的，但强烈建议声明主键。
  • foreign key(Ak1, Ak2, …, Akm) references s：外键声明，表示关系中任意数组在属性 (Ak1, Ak2, …, Akm) 上的取值必须对应于关系 s 中某元组在主键属性上的取值。
  • not null：该属性不允许空值。

insert 命令将数据加载到关系中：

1
2
3
-- instructor 关系中插入一条数据
insert into instructor
  values (10211, 'Smith', 'Biology', 66000);

delete 命令用于从关系中删除元组：

1
2
-- 从 student 关系中删除所有元组
delete from student;

drop table 用于从数据库中删除关系。该命令也会将关系中的所有元组删除。

1
2
-- 删除表 r
drop table r;

3.3 SQL 查询的基本结构

  • SQL 查询的基本结构由三个子句 (Clause) 组成：select、from、where；
  • from 后放置关系的名称，select、where 后面放置进行运算的子句；
  • 返回一个关系作为结果；

3.3.1 单关系查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 可以包括重复数据 
select A1, A2,.., An from R; 

-- 去除重复 
select distinct A1, A2,.., An from R; 

-- 显示的不去除重复 
select all A1, A2, ..., An from R; 

-- select 子句可以含有 + - * / 运算符的算术表达式；运算对象可以是常数或元组的属性。 
select A1, A2, ..., A3 * 1.1 from R; 

-- where 子句可以包括逻辑连词 and, or 和 not 
-- 逻辑连词的运算对象可以使包含比较运算符的 <, <=, >, >=, = 和 <> 的表达式 
select A1, A2, ... An from R where A1=V1 and A2=V2;

3.3.2 多关系查询

涉及多个关系的 SQL 查询中，各子句作用：

  • select 子句：列出查询结果中所需要的属性
    • 若有多个属性名相同，需使用关系名作为前缀加以区分。
    • 星号*可表示“所有的属性”。
  • from 子句：列出查询求值中需要访问的关系
  • where 子句：列出作用在 from 子句中关系的属性上的谓词

理解查询所代表的的运算：

  • 为 from 子句中列出的关系产生笛卡尔积
  • 在步骤 1 的结果上应用 where 子句中指定的谓词
  • 对步骤 2 结果中的每个元组，输出 select 子句中指定的属性（或表达式的结果）

上述步骤的顺序有助于理解 SQL 查询的结果应该是怎样的，而不是此结果怎样被执行的。

SQL 的实际实现中不会执行这种形式的查询，它尽可能只产生满足 where 子句谓词的笛卡尔积元素，以此优化执行。

笛卡尔积：
cartesuan-product.png

3.3.3 自然连接

  • 笛卡尔积：将第一个关系的每个元组与第二个关系的所有元组都进行连接。
  • 自然连接：只考虑连接在两个关系模式中都出现的属性上取值相同的元组对。

例如：

1
2
3
4
5
6
7
8
-- 笛卡尔积
select name, course_id
from instructor,teaches
where instructor. ID = teaches.ID;

-- 自然连接
select name, course_id
from instructor natural join teaches;

3.4 附加的基本运算

3.4.1 更名运算

  • old-name as new-name
  • select、from 子句中均可出现；
1
2
3
select T.name, S.course_id
from instructor as T, teaches as S
where T.ID = S.ID;

3.4.2 字符串运算

  • 使用单引号标识字符串
  • 字符串中的单引号使用双引号代替
  • 有多种常用函数
    • upper(s)：将字符串 s 转为大写
    • lower(s)：将字符串 s 转为小写
    • trim(s): 去掉字符串后面的空格
  • 可以使用 like 操作符实现模式匹配：
    • 百分号 (%) 匹配任意子串；
    • 下划线 (_) 匹配任意的单个子串；
    • 大小写敏感；

3.4.3 select 子句中的属性说明

*表示所有属性：

1
2
3
select instructor.*
from instructor,teaches
where instructor.ID = teaches.ID;

3.4.4 排列元组的显示次序

  • 使用 order by 让查询结果中的元组按排列顺序显示
  • 支持多列排序
  • 默认升序，可以使用 asc/desc 表示升序/降序
1
2
3
select
from instructor
order by salary desc, name asc;

3.4.5 where 子句谓词

使用比较运算符 between 可以简化 where 子句。

1
2
3
4
5
6
7
8
9
-- 使用前
select name
from instructor
where salary <=100000 and salary >=90000;

-- 使用后
select name
from instructor
where salary between 90000 and 100000;

在元组上使用比较运算符，那么会按照字典顺序进行比较。

1
2
3
4
5
6
7
8
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');

3.5 集合运算

SQL 作用在关系上的 union、intersect 和 except 运算对应于数学集合论中的并集 (∪)、交集 (∩) 和差集 (−) 运算。

3.5.1 并运算

1
2
3
4
5
6
7
(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 保留所有重复；

3.5.2 交运算

  • 与 union 使用方式一致，会自动去除重复；
  • 可以使用 intersect all 保留所有重复；

3.5.3 差运算

  • 与 union 使用方式一致，会自动去除重复；
  • 可以使用 except all 保留所有重复；

3.6 空值

空值运算规则：

如果算数表达式（±*/）的任意输入为空，则该算数表达式结果为空
涉及空值的任何比较运算的结果视为 unknown（既不是谓词 is null, 也不是 is not null），是 true 和 false 之外的第三个逻辑值
扩展到 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 ===> unknown
  • not
    • not unknown ==> unknown

注意：

select distinct 会去除重复元组。在该过程中，需要比较两个元组的对应的属性值。如果两个值都是非空并且值相等，或者都为空，那么它们是相同的。

但是这与谓词对待空值的方式不同，谓词中 null = null 会返回 unknown，而不是 true。

3.7 聚集函数

以值的一个集合（集或多重集）为输入，返回单个值。

  • 平均值：avg（必须数字集）
  • 求和：sum（必须数字集）
  • 计数：count
  • 最大值：max
  • 最小值：min

3.7.1 基本聚集

1
2
3
4
5
6
7
8
9
10
11
-- 找出 Comp. Sci. 系教师的平均工资
-- 计算平均值需要保留重复元祖
select avg(salary)
from instructor
where department = 'Comp. Sci.';

-- 找出在 2010 年春季学期讲授一门课程的教师总数
-- distinct 表示去掉重复元祖
select count(distinct ID)
from teaches
where semester = 'Spring' and year = 2010

3.7.2 分组聚集

目前为止的所有计算都是在表的所有数据或匹配特定的 WHERE 子句的数据上进行的。group by 子句可以将元祖进行分组。

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

需要注意的是：select 子句中没有使用聚集函数的属性，必须出现在 group by 子句中！

1
2
3
4
-- 错误示例！！！
select dept_name, ID, avg(salary)
from instructor
group by dept_name;

结果（错误的）：

dept_name ID avg(salary)
Biology 76766 72000.000000
Comp. Sci. 10101 77333.333333
Elec. Eng. 98345 80000.000000
Finance 12121 85000.000000
History 32343 61000.000000
Music 15151 40000.000000
Physics 22222 91000.000000

在一个特定分组中（dept_name）的每位老师都有一个 ID，因为每个分组只能输出一个元祖，所以无法确定选哪个 ID 作为输出结果。

1
2
3
4
-- 正确示例
select dept_name, ID, avg(salary)
from instructor
group by dept_name, ID;

结果（正确的）：

dept_name ID avg(salary)
Biology 76766 72000.000000
Comp. Sci. 10101 65000.000000
Comp. Sci. 45565 75000.000000
Comp. Sci. 83821 92000.000000
Elec. Eng. 98345 80000.000000
Finance 12121 90000.000000
Finance 76543 80000.000000
History 32343 60000.000000
History 58583 62000.000000
Music 15151 40000.000000
Physics 22222 95000.000000
Physics 33456 87000.000000

3.7.3 having 子句

  • where 用来过滤 select 的筛选结果，having 子句用来过滤分组；
  • having 支持所有 where 操作符；
  • 与 select 类似，任何出现在 having 子句中且没有被聚集的属性，必须出现在 group by 子句中！
1
2
3
4
select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary) > 42000;

3.7.4 对空值和布尔值的聚集

聚集函数根据以下原则处理空值：除了 count(*) 外所有的聚集函数都忽略输入集合中的空值。
由于空值被忽略，有可能造成参加函数运算的输入值集合为空集。

3.8 嵌套子查询

3.8.1 集合成员资格
3.8.2 集合的比较
3.8.3 空关系测试
3.8.4 重复元组存在性测试
3.8.5 from 子句中的子查询
3.8.6 with 子句
3.8.7 标量子查询

3.9 数据库的修改

3.9.1 删除

1
delete from r where P;

3.9.2 插入

1
2
3
4
5
6
7
8
9
10
11
insert into r values (v1, v2, ..., vn); 
-- 先执行完 select 子句，然后执行 insert 子句
INSERT INTO
  r (
    SELECT
      (A1, A2,..., An)
    FROM
      r
    WHERE
      P
  );

3.9.3 更新

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
UPDATE
  r
SET
  A1 = v1
WHERE
  P;

-- case 结构 
CASE
  WHEN pred1 THEN res1
  WHEN pred2 THEN res2...
  WHEN predn THEN resn
  ELSE res0
END

-- 更新每个 student 的 tot_cred 属性值设为该生成功 学完的课程的总学分。
-- grade 既不是 F 也不是 null，则表明成功学完了该门课程
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.grode IS NOT NULL
  );

-- 如果学生没有成功完成任何一门课程，则 tot_cred 被设置为 null。 
-- 如果希望将这样的属性值设为 0，那么需要使用另一条 update

-- 上述 sql 可改写为 
UPDATE
  student S
SET
  tot_cred = (
    SELECT
      CASE
        WHEN sum(credits) IS NOT NULL THEN sum(credits)
        ELSE 0
      END
    FROM
      takes NATURAL
      JOIN course
    WHERE
      S.ID = takes.ID
      AND takes.grade <> 'F'
      AND takes.grode IS NOT NULL
  );

3.10 总结

略