关系模型
数据模型是用于描述数据,数据间联系,数据语义以一致性约束的概念工具的集合。当今,商用数据处理用的主要数据模型是关系模型 ( 即使用 SQL 语句进行查询的数据库 )。
关系数据库由 表 ( table ) 来组成。比如说一个名为 instructor 的教职工表,它有 id
,name
,dept_name
,salary
等信息。表里的每一行记录的这 4 个值共同描述了一位老师的详细信息,如:{101,Tang,CS,20000}
表示了一个 "编号为 101,计算机系的唐老师的薪资是 20k"。我们可以说,这一行数据的一组值存在着联系。而表正是一组组联系的集合。
"数学" 一点的角度来说,表又可以称之为 关系 ( Relation ),而一行记录可以被称之为 元组 ( Tuple )。关系是元组的 无序集合 ( Set )。关系中的每一列又称之为一个属性 ( Attribute ),每一个属性都存在一个包含所有可能取值的集合,如性别为 {'male','female'}
,普通成年人身高 (cm) 的为 {x | 140.0 ≤ x ≤ 200.0}
。这个集合称为这个属性的 域 ( Domain )。如果域内的每一个元组都是不可再分的,则称域是 原子的 ( atomic )。
如何界定 "不可再分" 是一个很主观的概念。比如一个存放手机号的域 {0451-44448888,010-55556666}
,我们如果将区号和本地号视作是一个整体,那么这个域就是原子的。如果选择将这两段号码区分开来,即 {{0451,44448888},{010,55556666}}
,那么此时这个域又不是原子的了。
模式
我们讨论数据库时,也经常提及一个关键词 —— 那就是模式 ( Schema )。数据库的模式分为三级:
逻辑模式,通常简称为模式。指代数据库中全体数据的逻辑结构,和特征的描述。有以下两个重要的特征:
- 一个数据库只有一个模式,可以理解成:一个数据库中所有表构成的复杂的逻辑关系就是模式。
- 定义模式时不仅要定义数据的逻辑结构:( 比如一条记录由哪些数据项构成,数据项的名字,类型,取值范围等 ),还要定义与数据有关的安全性,完整性需求。
外模式 ( External Schema ),也称用户模式,子模式。是数据库用户根据应用需求而能够见到并使用的部分数据的逻辑结构和特征的描述。说地再通俗一些,它相当于用户视图。
内模式 ( Internal Schema ),也称存储模式 ( Storage Schema ),是数据物理结构和存储方式的描述,是数据在数据库内部的表示方式(例如,记录的存储方式是顺序存储、按照B树结构存储还是按hash方法存储;索引按照什么方式组织;数据是否压缩存储,是否加密;数据的存储记录结构有何规定)。
码
每一个元组必须保证是唯一的,因此我们必须从元组内选出可以唯一标示元组的一个或多个属性组合成 超码 ( Superkey )。超码的任意超集都是超码,但是我们一般只选取最小 ( 指包含最少的属性数量 ) 超码作为候选码 ( candidate key )。在一些情况下,一个表可以选出多个候选码,可以从中挑选出任意一个作为主键,即主码 ( primary key )。
在一个表内,所有元组的主码不可以重复,这个规则称之为主码约束,比如 instructor 表中的 id
,或者 department 表中的 dept_name
。相对的,存在一种外码约束:比如教职工 instructor 表中的 dept_name
属性必须取自于另一个专业系表 department 的 dept_name
域,而 dept_name
正好作为 department 的主码。因此称 dept_name
是从 instructor 引用 department 的外码。instructor 和这个码 dept_name
是引用关系,而 department 和 dept_name
则是 被引用关系。
SQL
SQL ( Structured Query Language,结构化查询语言 ),最早期被称之为 Sequel。现如今,SQL 已经明显确立了自己作为标准的数据库语言的地位。各个产品都支持 SQL 语言 ( 但是可能会存在着略小的差异 )。
从大类来看,SQL 可以大致分为两种:
DDL ( Data-Definition Language ) 数据定义语言:用于组织,定义数据库模式。比如说,创建了一个表,并且在必要的情况下,可以为其设置各种约束。
create table department(
dept_name varchar(20),building char(15),budget numeric(12,2),
primary key (dept_name)
);
在定义一个表的属性和数据类型之后,我们还要在后面添加额外的约束:比如 primary key
主码约束或者是 foreign key
外码约束。在章节 "码" 中提到的引用关系可以用 foreign key(dept_name) references department
。
声明属性的过程中也可以添加一个约束:即非空约束。比如声明 building char(15) not null
则表示 building
属性不允许为空值。
我们使用 DDL 或是创建,或是删除了一个数据库内的表。DDL 会创建一些额外的输出,它们存在 数据字典 ( data dictionary ) 当中。数据字典描述数据库自身的信息,这些信息又称作是元信息。
DML ( Data-Manipulation Language ) 数据操作语言:用于访问或者是操纵表内数据的语言。CRUD 操作都在这个范围。
SELECT * FROM `instructor`
数据库引擎
从一个数据 “应如何被保存,又应被如何操作” 的角度出发,数据库的模块可大致分为两层:存储管理器,查询处理器。
存储管理器又分为:
- 权限及完整性管理器,用于检测完整性约束,用户权限。
- 事务管理器,保证系统在发生故障的时候,数据库也能保持一致的状态;且负责保证并发的事务之间互不冲突。
- 文件管理器:管理磁盘空间的分配。
- 缓冲区管理器:负责将数据从磁盘调度到内存当中,并决定对那些数据这样做。
存储管理器还保存了以下数据结构:
- 数据文件,存储数据库自身的数据。
- 数据字典,存储描述数据库自身的信息。
- 索引,提供对数据的快速访问。
查询处理器根据 DDL 和 DML 可分为:
- DDL 解释器:解释 DDL 语句,并将相应的元信息输出到数据字典当中。
- DML 编译器:将高级的 DML 翻译成低级指令供查询执行引擎执行。
- 查询执行引擎,真正负责执行由 DML 转换得到的低级查询指令。
重要的关系代数与 SQL DML
对数据库的各种查询都可以抽象为代数计算,笔者列举的 SQL 语句在 MySQL 数据库中测试。注意,SQL 标准和 MySQL 的实现并不总是相同的。
投影 ( Project )
一元运算,用于过滤掉特定的属性。在教职工表 instructor 中,我们仅希望筛查其中的 id
,name
和 salary
,则可以记作:ΠID,name,salary ( instructor )。投影运算对应 SQL 语句中的 Select
。
-- 在 MySQL 中,有些字段作为数据库的关键字。为了避嫌,我们会在这些字段加上反引号。
-- MySQL 不限定关键字的大小写,一般情况下会保持全部大写。
SELECT ID,`name`,salary FROM `instructor`
额外地,如果要过滤掉重复结果,可以在 select
子句中添加一个 distinct
关键字。
选择 ( Select )
一元运算,用于筛选出满足条件的元组。比如我们希望在教职工表 instructor 中筛选出薪资大于 50k 的人的名字,符号记为:
Πname((σsalary > 50000 (instructor))
然而,关系代数中的选择对应的是 SQL 语句中的 where
:
SELECT `name` FROM `instructor`
WHERE salary > 50000
连接 ( Join )
连接操作有相当多的子分类:交叉连接 ( 笛卡尔积 ),内连接,外连接。
笛卡尔积 ( Cartesian-product ):即交叉连接。将 r1 × r2 称作是一个笛卡尔积计算。假设 r1 是一个具有 3 个属性,2 个元组的关系,r2 是一个具有 4 个属性,5 个元组的关系,笛卡尔积的结果将是一个具有 7 个属性,10 个元组的关系。
笛卡尔积对应 SQL 语句中的 cross Join
。但在 MySQL 实现中,笛卡尔积还可表示为 join
,inner join
,以及纯逗号 ,
。
-- 将 `instrctor` 和 department 做笛卡尔积。
SELECT * FROM `instructor` CROSS JOIN department
-- 这是更简略,且更被大众熟知的写法.
SELECT * FROM `instructor`,department
-- 若是没有任何约束的笛卡尔积,这些写法也等价.
-- 原因是 MySQL 的实现中,如果没有 on 条件,则 Inner join, cross join, join 三者语义都是相同的。
SELECT * FROM `instructor` JOIN department
-- 而从纯 SQL 的定义来看,INNER JOIN 应该是介于 cross join 和 natural join 的一种连接。
SELECT * FROM `instructor` INNER JOIN department
交叉连接对两个表没有任何约束,任意两个表都可以进行交叉连接。但一般情况下,随意的交叉连接都是没有意义的 —— 大部分情况下连接的两个表都应当具有公共的属性,并且将公共属性值都相同的两个元组串联起来。如:
SELECT * FROM `instructor` CROSS JOIN department
WHERE `instructor`.dept_name = department.dept_name
这个语义更倾向于是自然连接,它是等值连接的一部分 ( 反过来,等值连接不一定都是自然连接 )。尤其要注意,在 SQL 标准中,inner join
和 cross join
是不同的语义,inner join
需要配合 on
一同使用,可以实现更一般的等值连接 ( 即连接的属性不一定是两个表的公共属性,由用户指定 )。而在 MySQL 标准中,cross join
和 inner join
被 "模糊对待" 了。详细的文章可以参考:MySQL中inner join 和 cross join 的区别? - 知乎 (zhihu.com)
下面这条 SQL 和上面带条件的交叉连接等价:
-- 两个表的公共属性是 dept_name,自然连接自动按这个属性进行连接,并去掉重复列。
SELECT * FROM `instructor` NATURAL JOIN department
此外,交叉连接会保留两个表所有的属性,因此连接后的关系存在重复的公共属性,而自然连接会自动删掉那些多余的属性。偶尔,如果要指定根据哪个属性进行自然连接,可以使用关键字 join ... using
。比如:``
-- Using 后面不允许空格,用括号紧接着指定连接的一个或者是多个属性。
SELECT * FROM `instructor` JOIN department USING(dept_name)
连接的条件关键字是 on
而非 where
,尽管我们大部分都选择后者进行等价实现。只从结果上来看,哪种方式都没有问题:
SELECT * FROM `instructor` CROSS JOIN department on `instructor`.dept_name = department.dept_name
但从查询的过程来看,两个 SQL 语句的执行顺序不完全相同 。on 条件先于 where 条件的执行。on
条件是在生成临时表时候执行,where
是在临时表生成后再对数据进行筛选的。因此,在稍后提及的外连接中,两者的表现会体现出微妙的差异。
在等值连接 r1 join r2 on(...)
中,那些在无法被正确连接的元组会被抛弃掉。但在外连接中,我们可以选择性保留那些原本在 r1
或 r2
中应当被抛弃的元组 ( 当然,也可以两者全部保留 ),因无法连接而空缺出的右半 ( 左半 ) 部分会以 null
值填充。
- 如果选择保留
r1
中丢失的元组,那么称为左连接,关键字left join
。 - 如果选择保留
r2
中丢失的元组,那么称为右连接,关键字right join
。 - 左连接和右连接的并集,称全连接,关键字
full join
。
相对的,之前那些会丢失元组的连接称之为内连接。概念上,内连接包含了等值连接,和特殊的自然连接。但在 MySQL 实现中,不包含 on
条件的内连接和交叉连接等价。左连接和右连接保持对称性,如 r1 left join r2
等价于 r2 right join r1
。
另外有两个比较特殊的连接:半连接和反连接。以 r1 和 r2 做半连接为例,设两表用于半 (反) 连接的属性为 k1,k2 ( k1 和 k2 可以是相同的属性,这里考虑的是一般情况 ),而 r2 表对应 k2 属性的域为 D。设 t 是 r1 的一条元组,只要 t.k1 能落入到域 D 中 ( 换一种说法是,只要 t.k1 至少能和 D 中的一个值做连接 ),那么该 t 就可以被选中。最终计算得到的 r3 是 r1 的一个子集。如果说 t.k1 不落入域 D 才可以被选中,那么这是一个反连接操作。这两个连接对应 SQL 语句中的 exists
和 not exists
关键字。
-- r1 半连接 r2
SELECT r1.k1 FROM r1
WHERE EXISTS {
SELECT k2 FROM r2
WHERE r1.k1 = r2.k2
}
-- r1 反连接 r2
SELECT r1.k1 FROM r1
WHERE NOT EXISTS {
SELECT k2 FROM r2
WHERE r1.k1 = r2.k2
}
-- 反连接的另一种形式
SELECT r1.k1 FROM r1
WHERE EXISTS {
SELECT k2 FROM r2
WHERE r1.k1 <> r2.k2
}
因此,从语义上来看,exists
仍然属于连接的范畴,而非简单的条件判断。它和 between
,any
,all
这类关键字有本质的不同。
集合运算
并集 ( Union ) :查询 2017 年春季学期开设的所有课程的集合,我们记作是:
Πcourse_id(σsemester= "fall" ∧ year = 2017 (section)),后面简称此为条件 1。
查询 2018 年春季学期开始的所有课程的集合,我们记作是:
Πcourse_id(σsemester= "spring" ∧ year = 2018 (section)),后面简称此为条件 2。
想要一起查询这两个集合,我们记作:
Πcourse_id(σsemester= "fall" ∧ year = 2017 (section)) ∪ Πcourse_id(σsemester= "spring" ∧ year = 2018 (section))
这对应 SQL 语句中的:
-- r1
SELECT course_id FROM section
WHERE semester = "fall" and `year` = 2017
union -- r1 ∪ r2
-- r2
SELECT course_id FROM section
WHERE semester = "spring" and `year` = 2018
不过,这段 SQL 有更简洁的写法:
SELECT course_id FROM section
WHERE (semester = "fall" and `year`) or ( semester = "spring" and `year` = 2018)
也就是说下面的关系代数和上面等价:
Πcourse_id(σ(semester = "fall" ∧ year = 2017) ∨ (semester = "spring" ∧ year = 2018)(section))
交集 ( intersect ):若要查询同时满足条件 1 和条件 2 的课程,关系代数记作:
Πcourse_id(σsemester= "fall" ∧ year = 2017 (section)) ∩ Πcourse_id(σsemester= "spring" ∧ year = 2018 (section))
显然,根据前面的代码块,我们很容易就能推测出对应的 SQL 语句:
-- intersect 不是 Mysql 的关键字。
-- r1
SELECT course_id FROM section
WHERE semester = "fall" and `year` = 2017
intersect -- r1 ∩ r2
-- r2
SELECT course_id FROM section
WHERE semester = "spring" and `year` = 2018
不过,MySQL 数据库不支持 intersect
这样的关键字。我们通过上述并集运算的 SQL,也许会 "推理" 出这样的 SQL 语句 ......
SELECT course_id FROM section
WHERE (semester = "spring" and `year` = 2018 ) and (semester = "fall" and `year` = 2017)
这有点奇怪。为了更清楚地发现问题,这里根据逻辑运算的结合律,对 where
条件做一点等价代换:
SELECT course_id FROM section
WHERE (semester = "spring" and semester = "fall") and (`year` = 2017 and `year` = 2018 )
也就是说:这段 SQL 语句表达 "开课学年又是 2017 年又是 2018 年" 且 "开课学期又是春季学期又是秋季学期" 的课程。对于每一个属性,它都应该是唯一一个确切的值,就像笔者的名字要么叫 "张三" 要么叫 "李四",开课学期同理。显然这里语义出现了问题,因此这条 SQL 什么都查询不出来。那么这样的语句呢?
SELECT course_id FROM section
WHERE (semester = "spring" or semester = "fall") and (`year` = 2017 or `year` = 2018 )
同样不对。根据逻辑运算的分配律,它等价于:
SELECT course_id FROM section
WHERE (semester = "fall" and `year` = 2017) or (semester = "spring" and `year` = 2018)
and (semester = "spring" and `year` = 2017) or (semester = "fall" and `year` = 2017)
现在需要转换一下思路:不妨先查找满足其中一个条件的临时关系 table1,再从这个临时关系 table1 中查找满足另一个条件的元组。实现方式有两种:exists
或者是 in
关键字,两者均涉及到嵌套子查询。
-- in version,比较好理解
SELECT course_id FROM section
WHERE semester = "fall" and `year` = 2017 and course_id in (
SELECT course_id FROM section
WHERE semester = "spring" and `year` = 2018
)
-- exists version
SELECT course_id FROM section as t1
WHERE (semester = "fall" and `year` = 2017) and EXISTS (
SELECT course_id FROM section as t2
WHERE semester = "spring" and `year` = 2018
and t1.course_id = t2.course_id
)
两个方法都可以得到期望的结果,但是 in
子句更适合用于遍历小表。反之,则应当使用 exists
子句的写法。可参考:MYSQL中IN与EXISTS的区别_魏梦筱_xiao的博客-CSDN博客
差集 ( except ):它的代数运算。
Πcourse_id(σsemester= "fall" ∧ year = 2017 (section)) - Πcourse_id(σsemester= "spring" ∧ year = 2018 (section))。
它的 SQL 语句应该是这样:
-- intersect 不是 Mysql 的关键字。
-- r1
SELECT course_id FROM section
WHERE semester = "fall" and `year` = 2017
except -- r1 - r2
-- r2
SELECT course_id FROM section
WHERE semester = "spring" and `year` = 2018
MySQL 也没有 except
关键字。但有了之前的例子,实现它并不困难,甚至说只需一点小改动即可 —— 在之前代码块的 exists
和 in
前面加一个 not
就好。不过,真正值得注意的地方在于:差集运算不满足交换律,r1 - r2 不等价于 r2 - r1。因此下面两段查询的结果是不同的 ( exists
方式的查询同理 ):
-- (r1 - r2): 在 2017 年开课但不在 2018 年开课。
SELECT course_id FROM section
WHERE semester = "fall" and `year` = 2017 and course_id not in (
SELECT course_id FROM section
WHERE semester = "spring" and `year` = 2018
)
-- (r2 - r1): 在 2018 年开课但是不在 2017 年开课。
SELECT course_id FROM section
WHERE semester = "spring" and `year` = 2018 and course_id not in (
SELECT course_id FROM section
WHERE semester = "fall" and `year` = 2017
)