视图

视图是一种虚拟表（虚表）。它基于一张表或多张表（原表）的查询结果。

创建一个视图语句如：

SELECT * FROM `typecho_contents` WHERE cid>120 ORDER BY typecho_contents.`created` DESC

视图就是基于一条固定的sql查询语句，

注意以下几点内容：

超键、候选键、主键、外键

  • 主键：一个数据表中对数据对象能够唯一和完整标识的数据列或数据列的组合。一个表只能有一个主键，而且主键取值不能为空值（null）
  • 外键：一个表中存在另一个的主键，成为此表的外键
  • 候选键：主键只有一个，但是候选键可以有多个，共性都是能唯一完整标识表的数据对象。且不能含有多余的属性列。
  • 超键：候选键和一些无关/多余数据列组合，就可以形成超键。

菜鸟学数据库——超键、候选键、主键、外键

关系模式及其分解

我们所研究的都是关系型数据库。那么什么是关系？什么是关系模式呢？

关系

我们常说，我们两个人关系不错，这里关系是两个人的关系，从这句话，我们可以看出，关系由哪些元素组成呢？

人A和人B，对就是两个人而已。

我们也可以把人A和人B换成域A和域B，这不过这两个域只有一个元素。

这里介绍下域的概念，大家都听说过定义域，域就是元素集合，不过是换个名字。

我们再举个例子，1班和2班同学的关系大部分都不错。这里的关系的域A的元素就包括了1班全体同学，域B包括了2班全体同学。

再来思考一个问题，我们举得第二个例子，这个关系只有固定的一个吗？

一班假设只有3个人ABC，二班也只有2个人EF，

如果1班与2班所有人关系都很好，我们可以把关系表示为（1班每个人和2班都有关系）：

A E

A F

B E

B F

C E 

C F

但也可以可能1班的B和2班的每个人关系都不好，那么关系可以表示为

A E
A F
C E
C F

可以看到，关系并不是只有一个固定，但都是我们第一个关系的子集。第一个关系我们也称为域A和域B的笛卡尔积，笛卡尔积很简单，就是两个集合的运算。我们也可以看出计算方法也很简单，由以前的一维集合，变成二维矩阵，方法就是二个集合元素两两组合。

说到这里，关系到底是什么？

关系就是几个域的笛卡尔乘积得到的矩阵的子集。

矩阵的每一行我们称为元组。

我们对应到我们熟悉的数据库的表中，关系不就是一个表的的所有数据吗(表本身就是一个矩阵)。域就是每个字段的集合呀。每个字段的名称我们叫做属性。

很容易看到属性和域的区别，属性是字段的名称，域是字段的可以取值的集合。

关系模式

我们前面说了，关系就是矩阵（域的笛卡尔积计算出来的）的一些元组的集合。关系是实实在在的数据。

但是要用文字具体描述这个关系，这个文字就是关系模式。

关系模式的定义为：

R(U，D，DOM，F)

R就是这个关系的名称，你可以理解为表的名称

U就是这个关系的属性名集合，你可以理解为表的字段名集合

D就是U中每个属性的域的集合

DOM就是U与D两个集合的映射，就是给你说U中的这个字段是取值范围是D中的哪个元素

F就是属性之间的依赖关系。

依赖关系

这个更简单了，就是一些课本不说人话，你根本不知道他在说什么东西？？

A依赖于B，反过来的意思就是B能唯一决定A的存在。

举个例子。学生的学号和姓名，那就是姓名依赖于学号，因为我们通过学号就可以查到姓名了。

什么叫做完全依赖与部分依赖呢？

前面说的，姓名依赖于学号就是完全依赖。姓名也依赖于(学号,性别)(两个属性的组合)，但是就是部分依赖，因为

去掉性别，学号仍然可以唯一确认一个人的姓名。

所以完全依赖就是某个属性依赖于某个属性的集合，但这个属性的集合元素必须是最少的，不能是无关属性都加进来，否则就是部分依赖。

Y部分依赖X就是Y完全依赖X的一部分。

主属性和非主属性

候选码（而不是主键）的属性集合中的每个属性都叫主属性（不一定是主键的属性，别的候选码都行）。

非主属性，除了主属性都是非主属性

模式分解

关系模式也可以简写成R<U，F>，因为我们不考虑属性取值范围，所以D去掉，D都没有了，DOM留着过年吗？

模式分解可以分解为（这里分成2个）：R<U1,F1>、R<U2,F2>
U = U1 ∪ U2，且属性列表之间不能是包含关系。

模式分解确保属性集取并可以还原就可以了吗？显然不可以。需要满足下面两个条件：

  • 无损连接性
  • 保持函数依赖

这两个词又看起来很头大，其实解释起来很简单。

我们举个例子：

有一个学生信息表，字段有学号、所在的系名、系主任的名字

很显然，这里学号是主键

但是系名也可以决定系主任名称，即系主任依赖于系名，记为系名 —> 系主任(这个箭头顺序让人误解，但是就是这样……)

所以这个表是二范式。

我们可以模式分解为三个表，其中一个表，里面是学生学号，另一个表里面是系名称，还有一个表示系主任名称。

但这样，字段的映射关系全部丢失了，换句话说这三个表没办法还原成以前那个表了。即不符合无损连接性。

上面不行，我这么分解成两个表，一个表示学号和系的映射，另一个表示学号和系主任的映射。

看起来符合无损连接性条件，但是有什么新的问题呢？

我们先回过头看下以前那一个学生表中，非主属性系名 —> 系主任，而这两个表中都丢失了这个依赖关系了。所以不符合保持函数依赖条件。

那怎么搞，很简单啊，二范式转三范式就是一个模式分解的例子呀，就是把非主属性中依赖拿出来存到一个新的表中。

即一个表，存储学生，系名称的映射，另一个表存储系名称和系主任的映射。完美。

范式

我发现很多人（包括我一开始）对范式理解都是错误的。

比如二范式，章口就来，有主键，而且其他属性完全依赖于主键。这样差不多，但不严谨。应该说是候选码而不不是主键，主键只是候选码的一个自己。其他属性应该是非主属性（如果有多个候选码，主属性对非主键的候选码不完全依赖，也有可能是二范式）（见BCNF范式的例子）。

范式的定义都是基于候选码的。

一范式

是属性（字段）不可分割。这个字段只能是一个值，不能被拆分成多个字段

二范式

有候选键，且其他非主属性完全函数依赖候选键。

举个例子：

一个学生成绩表字段有：学号, 学生名字, 学科编号, 学科名称, 成绩

候选码有(学号、学科编号)。

其中，学科姓名完全依赖学科编号，部分依赖(学号，学科编号)

解决方法是：把学科编号和学科名称都拿出去做一个新的表，然后这个表里面删掉学科名称字段就可以了。

即把那个 非主属性 以及 非主属性依赖的那个部分主键**都移出去新建一个表，并且删掉那个非主属性即可。

上面的例子中，学生名字和学号也是同理，移出去新建一个表，删掉学生名字。

三范式

消除传递函数依赖。这个也很容易判断。一个表中，如果在非主键的属性集合中，出现一个非主属性C完全依赖另一个一个非主属性B（候选键是A），那么一定有传递依赖。因为属性B依赖于候选键A，A与C的依赖关系就是传递依赖。即三范式中，不能存在非主属性完全函数依赖于另一个非主属性。

解决方法是非主属性中相互依赖的属性都拿出去新建新的表，然后把新的表的主键作为原来表的一个字段就可以了。(部分函数依赖的一般解决方法)

BCNF范式：

官方定义是：不能存在 主属性 对候选码的部分依赖或者是传递依赖。(听听这是人话吗？？太绕了！！)

也就是有多个候选码的时候，不能存在其中一个候选码中 部分属性 决定另一个候选码。

举个例子：

有一个学生上课表：字段有学号、老师编号、课程编号

这个表满足三范式。候选码是(学号，老师编号) -> 课程编号、 (学号，课程编号)->老师编号。所以这三个字段都是主属性。没有非主属性，肯定满足3NF。

还有一个依赖关系是 课程编号->老师编号。

主属性老师编号完全依赖于课程编号，部分依赖候选码(学号，课程编号) 。

这就是定义所说的，所以不是BCNF范式。

解决方法：就是把 那个主属性 和 它依赖的那个属性(这里是老师编号 和 课程编号)拿出去新建一个新表，然后删掉它依赖的那个属性（删掉课程编号）。

即：(学号，老师编号)

(课程编号，老师编号)

知乎上有一个回答是这样定义的：只能有一个候选键，就是主键。我认为是错误的。

举个例子：

学号，学科学科编号，成绩，

比如一个学生信息表，字段有学号、身份证号、姓名、性别。

这里候选键有两个，学号和身份证号。

其中，学号是完全依赖身份证号的，所以满足BCNF范式的条件。

如何解释关系数据库的第一第二第三范式？

索引

索引可以换个词叫“表的目录”，就和新华字典一样。

建立索引后的表，分为两部分，“索引页”（相当于目录）和“数据页”（字典后面真正的部分）。

  • 用拼音首字母的目录表，就叫“聚集索引”，因为目录中字的顺序和真实的字典中字的顺序是一致的。

    • 所以，聚集索引只有一个，因为物理的顺序也只有一种。
    • 但是有所区别的是，表的聚集索引的叶子节点直接存储了真正数据行，而不是存储数据行的指针。（就好比目录中查到某个字，这一行就包含了这个字详细解释，没有字典后面的部分了）
  • 用偏旁部首的目录表，就叫“非聚集索引”，原因和上面相反。目录中字顺序和字典中字的顺序不一致。

漫谈数据库索引

T_SQL语句

全称是Transact-SQL。除此sql还有PL/SQL

  • PL/SQL和T-SQL都是标准SQL语言的一个版本。
  • PL/SQL的支持者有oracle、MySql等。
  • T-SQL的支持者有Sql Server、Sybase等。

两种版本也有具体一些区别：PL/SQL和T-SQL的简单区别

SQL、T-SQL与PL-SQL的区别

存储过程

也可以给他换个名字，叫“sql语句函数”。就是一个sql语句组成的代码块，创建的时候取个名字，使用的时候调用这个名字即可。

好处有下面三点：

  • 存储过程是一个预编译的代码块，执行效率比较高
  • 一个存储过程替代大量T_SQL语句 ，可以降低网络通信量，提高通信速率
  • 可以一定程度上确保数据安全

举个例子：

# 创建名为 GetUserAccount 存储过程
create Procedure GetUserAccount
as
select * from UserAccount
go

# 执行上面的存储过程
exec GetUserAccount

事务

  • 事务（Transaction）是并发控制的基本单位。
  • 所谓的事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。
  • 事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。
  • 事务有四个性质（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

我们普通输入sql语句，是默认直接就commit了。

下面举个例子说明事务的原子性的好处。

比如A给B转账500，需要两条sql语句，A的账户减少500，B的账户增加500。
如果不开启事务，A的账户先少了500，然后发生一个错误，这样B的账户无法收到A丢失的500元，造成错误。
开启事务后，如果中间发生错误，会自动回滚，这样AB都没有损失。

一个正确的事务执行过程的语句：

mysql> begin;  # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)
 
mysql> insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)
 
mysql> commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)

MySQL 事务

可串行化调度

全称是事务的可串行化调度，也有的试卷上叫视图的可串行化调度(我一直疑惑，这个事务和视图到底什么关系，为什么叫这个名字？？？)

事务，前面说了就是一个不可分割的操作序列。

都说了不可分割，那岂不是一定是串行调度了，关并行调度什么事？

其实不是这样的，只要满足事务的ACID四个性质，我们可以对多个事务的操作序列进行并发调度。

那并发操作的最小单位应该是sql语句呀，为什么说是事务？(等待一个大佬来解释一下)

我们先解释什么叫做事务的可串行话调度，很好理解，就是它是并行调度的，但是结果和串行调度的结果是一致的。

串行调度也会有不同结果，但没关系，只要并行调度结果匹配上串行调度任意一个结果，都可以算可串行化调度。

那怎么判断多个事务的并发调度序列是否满足事务的可串行化调度呢？

我们把两个不同事务对同一变量的读写操作，或者对同一变量的写写操作定义为冲突操作。

在并发调度序列中，我们对任何满足该条件的操作(不是冲突操作并且不是同一事务的两个操作)调换次序，最终可以转化成串行事务调度序列，我们就说该并发调度是冲突可串行化调度。

同时有以下结论：

一个并发调度是冲突可串行化调度，一定是可串行话调度，但反过来不成立

举个例子：

调度序列为R1(A)W1(A)R2(A)W2(A)R1(B)W1(B)R2(B)W2(B)

其中R1(A)表示事务1对A读操作，W1(A)表示事务1对A写操作s

这样题目，我们重点找不同事务对不同变量的操作(读还是写不重要)和不同事务对相同变量连续的读操作

这里可以找到W2(A)R1(B),以此为分界点，把W2(A)R1(B)W1(B)以刚才的分界点，调换，即为

R1(A)W1(A)R2(A)R1(B)W1(B)W2(A)R2(B)W2(B)

又找到R2(A)R1(B)，再次把R2(A)R1(B)W1(B)以此分界点调换，即

R1(A)W1(A)R1(B)W1(B) R2(A)W2(A)R2(B)W2(B)

这是事务1、事务2调度的串行调度，所以上面的调度是冲突可串行化调度。

数据库的结构

模式和实例

模式其实在关系模式中说过了，模式就是对关系的符号化描述。

课本上定义是模式是全体数据的逻辑结构和特征的描述，换句人话就是模式就是所有属性（字段）的描述（包括名称和取值范围）

实例，学过java都知道，实例就是类的实例化。同样，这里的实例就是对模式的实例化。同一个模式肯定有不同的实例呀。他们虽然字段相同，但是数据不同嘛。

模式、外模式、内模式

模式又称逻辑模式。一个表只有一个，它是表的所有数据的逻辑结构，但不涉及数据的物理结构。

外模式又称子模式或用户模式，一个模式可以对应好多个外模式。一个应用对应一个外模式，它是数据库用户直接看到的。外模式是模式的子集。

内模式又称存储模式，一个表只有一个，是所有数据的物理结构。

其实这个很像操作系统的文件（记录）的存储。记录也有逻辑结构和物理结构。逻辑结构就是模式或者外模式，物理结构就对应了内模式。

2个映射关系和2个独立性

外模式和模式的映射。

映射是什么意思？

就是当模式的结构变化(删除属性或者改变属性类型)，我们不需要更改外模式，我们只需要修改这个映射就可以了。这就是逻辑独立性，也就是用户那边的逻辑结构是独立的。

模式和内模式的映射。

当内模式结构变化(数据结构变化了)，模式不需要变化，通过修改这个映射就可以了。这就是物理独立性。

数据库的恢复技术

了解两种故障：

  • 事务故障：事务内部语句出现的逻辑问题，比如除0之类的，这个时候就需要撤销事务(UNDO)。

  • 系统故障：比如断电了，系统坏了，导致内存数据全部丢失。

    • 未完成事务，但是已经事务在系统坏之前已经有结果写到数据库了。这个时候需要撤销事务(UNDO)。
    • 已完成事务，结果还在缓存区中，没有写到数据库中，系统就坏了，这个时候需要重做事务（REDO）。

(记住已完成的需要重做，可以理解着记忆，因为系统坏之前，人家已经做完了，你总不能撤销，这不是破坏别人劳动成果嘛)

具有检查点的数据库恢复技术

检查点可以理解为检查事务的提交结果是否已经写到数据库里面了。

也就是说，检查点就是判断在故障点已完成事务的提交是否已经写到数据库里面了。

如果是，就不需要重做，否则还是需要重做。

关系代数

数据库专门的关系运算有这些：

  • 选择：选择符合条件的行
  • 投影：选择符合条件的列
  • 连接：效果就是两个关系的笛卡尔乘积 + 选择符号条件的行
  • 除：R ➗ S ，从R中去除 R和S的重复的列，并且选择R和S重复列中行相同的行。

传统的集合运算：

  • 交：可以由差来表示，R∩S = R -(R-S)
  • 并
  • 差
  • 笛卡尔积

SQL基本语法

  • 删除数据Delete from <表名> [where条件]
  • 修改数据 Update <表名> set <列名> = <值/表达式> [where 条件]
  • 查询语句 Select <列名> from <表名> [where条件]
  • 增加数据 Insert <表名> [<列名1>,<列名2>] values ([<常量1>,<常量2>])
  • 删除表 Droop table <表名> [restrict|cascade]

参考文章

数据库常见面试题(开发者篇)

常见面试题整理--数据库篇（每位开发者必备）

最后修改：2019 年 03 月 26 日
© 允许规范转载
喜欢我的文章吗？
别忘了点赞或赞赏，让我知道创作的路上有你陪伴。