青波的博客
数据库系统概念 - 03
编辑
2022-10-09
后端开发
00

目录

《数据库系统概念（原书第 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 关系（表）。

SQL
-- 通用形式
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 命令将数据加载到关系中：

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

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

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

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

SQL
-- 删除表 r
drop table r;

3.3 SQL 查询的基本结构

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

3.3.1 单关系查询

SQL
-- 可以包括重复数据 
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 自然连接

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

例如：

SQL
-- 笛卡尔积
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 子句中均可出现；
SQL
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 子句中的属性说明

*表示所有属性：

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

3.4.4 排列元组的显示次序

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

3.4.5 where 子句谓词

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

sql
-- 使用前
select name
from instructor
where salary <=100000 and salary >=90000;

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

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

sql
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 并运算

sql
(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 基本聚集

sql
-- 找出 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 子句可以将元祖进行分组。

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

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

sql
-- 错误示例！！！
select dept_name, ID, avg(salary)
from instructor
group by dept_name;

结果（错误的）：

dept_nameIDavg(salary)
Biology7676672000.000000
Comp. Sci.1010177333.333333
Elec. Eng.9834580000.000000
Finance1212185000.000000
History3234361000.000000
Music1515140000.000000
Physics2222291000.000000

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

sql
-- 正确示例
select dept_name, ID, avg(salary)
from instructor
group by dept_name, ID;

结果（正确的）：

dept_nameIDavg(salary)
Biology7676672000.000000
Comp. Sci.1010165000.000000
Comp. Sci.4556575000.000000
Comp. Sci.8382192000.000000
Elec. Eng.9834580000.000000
Finance1212190000.000000
Finance7654380000.000000
History3234360000.000000
History5858362000.000000
Music1515140000.000000
Physics2222295000.000000
Physics3345687000.000000

3.7.3 having 子句

  • where 用来过滤 select 的筛选结果，having 子句用来过滤分组；
  • having 支持所有 where 操作符；
  • 与 select 类似，任何出现在 having 子句中且没有被聚集的属性，必须出现在 group by 子句中！
sql
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 删除

sql
delete from r where P;

3.9.2 插入

sql
insert into r values (v1, v2, ..., vn); 
-- 先执行完 select 子句，然后执行 insert 子句
INSERT INTO
  r (
    SELECT
      (A1, A2,..., An)
    FROM
      r
    WHERE
      P
  );

3.9.3 更新

sql
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 总结

略

本文作者:青波

本文链接:

版权声明:本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！