03 数据库基础知识

Posted on Wed, 25 Dec 2024 16:40:17 +0800 by LiangMingJian

1.数据库基本原理 #

1.1 数据库的基础概念 #

• 数据（Data）数据库储存的基本对象，描述现实世界中各种事物的符号记录，这个记录可以是文字的、图形的、图像的、语言的、声音的等等。
• 数据库（DB）是按照一定结构组织、存储和管理数据的仓库。数据库本质是一个文件系统，用来保存一系列有组织的数据。特点是：
  • 具有较小的冗余度
  • 较高的数据独立性
  • 易扩展
  • 可共享
• 数据库管理系统（DBMS）是数据库系统的核心，是位于用户与操作系统之间的一层数据库管理软件。主要功能包括：
  • 数据定义语言（DDL）
  • 数据操纵功能（DML）【增，删，改，查】
  • 数据控制功能（DCL）

1.2 数据库的体系结构 #

• 数据库领域公认的标准结构是三级模式结构，将数据库结构分成如下 3 级：
  • 外模式，也称用户模式或子模式，是面向用户或应用程序员的用户级，用户可以使用外模式描述语言来描述定义用户的数据记录，也可以用数据操纵语言 DML 来处理数据记录，是用户与数据库的接口。对应数据库中的视图。外模式反应了数据库的用户观。
  • 概念模式，也称模式或逻辑模式，是面向建立和维护数据库人员的概念级，它使用数据模式描述语言 DDL 来描述并定义物理数据库。对应数据库中的基本表。体现和反应数据库系统的整体观，
  • 内模式，也称物理模式或存储模式，是面向系统程序员的物理级，它是进行数据库操作的基础。对应数据库中的存储文件。体现数据库的存储观。
• 映像是在三级模式之间的一个映像关系，体现的是数据的独立性，分为以下 2 级：
  • 外模式/模式映像：保证了数据与应用程序之间的逻辑独立性。逻辑独立性是指应用程序发生更改时，不会影响逻辑结构和物理结构，只需要将数据库中的视图用外模式和模式之间的关联和映像关系进行重新定义即可。
  • 模式/内模式映像：保证了数据与应用程序之间的物理独立性。物理独立性是指物理结构发生变化的时候，不会影响应用程序和逻辑模式，只需要调整模式和内模式之间的映像关系即可。

1.3 数据模型 #

1.3.1 数据模型的定义 #

• 数据模型（Data Model）是数据特征的抽象，它从抽象层次上描述了系统的静态特征、动态行为和约束条件，为数据库系统的信息表示与操作提供一个抽象的框架。
• 现实的数据通过认识抽象形成信息（概念模型），再进行转换存储形成机器数据（数据模型）。

1.3.2 数据模型的三要素 #

• 数据结构：指的是一组数据的存储结构，它是带结构的数据元素集合，结构是指数据元素之间存在的关系。
  • 层次模型：根以外的其他结点有且仅有一个双亲结点。
  • 网状模型：有一个以上的结点没有双亲结点，允许结点有多个双亲结点，允许两个结点之间有多种联系。
  • 关系模型：一个由二维表框架组成的集合，每个二维表又可称为关系，一个关系往往具有元组（行），属性（列），主码，属性名，关系名等内容。关系模型是最常用的数据结构。
• 数据操作：增，删，改，查
• 数据约束：又称数据的完整性约束，指的是为了防止不符合规范的数据进入数据库。在用户对数据进行插入、修改、删除等操作时，数据库自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。
  • 实体完整性：若属性 A 是基本关系的主属性，则属性 A 不能取空值。规定表的每一行在表中是唯一的实体。
  • 参照完整性：两个表的主关键字和外关键字的数据应一致，保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。
  • 用户定义的完整性：用户设置对某些具体关系数据库的约束条件。
  • 其中，实体完整性与参照完整性是关系模型中必须满足的完整性约束条件。

2.数据库的设计 #

2.1 数据库设计阶段 #

• 需求分析：分析用户的需求，包括数据、功能和性能需求。
• 概念结构设计：将需求分析得到的用户需求抽象为概念模型，并通过 E-R 图来对设计进行描述。
• 逻辑结构设计：把概念结构设计阶段设计好的 E-R 图转换为与选用数据库管理系统产品所支持的数据模型相符合的逻辑结构，即实现从 E-R 模型到关系模型的转换，进行关系的规范化。
• 物理结构设计：主要是为所设计的数据库选择合适的存储结构和存储路径。
• 数据库实施：包括编程、测试和试运行。
• 数据库运行和维护：系统的运行和数据库的日常维护。

2.2 E-R 实体-联系模型 #

2.2.1 概念 #

• E-R 模型，实体 - 联系模型，是概念数据模型的高层描述所使用的数据模型或模式图。
• E-R 模型分为实体、属性、关系三个核心部分。

2.2.2 实体 #

• 在 E-R 模型中以矩形表示，是数据模型中的数据对象，例如门店、员工、物品都可以作为一个数据对象。
• 每个实体都有自己的实体成员（entity member）或者说实体对象（entity instance），例如员工实体里包括张三、李四等，实体成员（entity member）/ 实体实例（entity instance） 不需要出现在 ER 图中。
• 实体的类型可以分为：
  • 弱实体：一个实体必须依赖于另一个实体存在，那么前者是弱实体，后者是强实体，弱实体必须依赖强实体存在，表现 1:N 或 1:1 的关系。例如下图 A 的门店实体和员工实体，员工依赖于门店实体而存在，因此门店是强实体，而员工是弱实体。弱实体与强实体之间的联系使用双线菱形。
  • 复合实体：复合实体也称联合实体或桥接实体，常常用于实现两个或多个实体间的 M:N 联系，它由每个关联实体的主码组成，用长方体内加一个菱形来表示。以下图 B 的汽车美容门店为例，车主和施工项目两个实体是 M:N 的关系，中间由施工订单这个实体联系，因此施工订单这个实体是一个复合实体，同时如果车主实体不存在，就没有施工订单实体的存在，因此对于车主实体来讲施工订单是弱实体，同理施工项目实体如果不存在，同样不存在施工订单实体，因此对施工项目实体而言施工订单是弱实体。

2.2.3 属性 #

• 在 E-R 模型中以椭圆形表示，是数据对象所具有的属性，例如员工具有姓名、工号、职位等属性。
• 每一个实体至少有一个唯一属性，用来唯一标识该实体实例或者成员的属性，用下划线表示。
• 属性的类型可以分为：
  • 复合属性：复合属性是指具有多个属性的组合，例如「姓名」属性，它可以包含「姓氏」属性和「名字」属性。
  • 多值属性：一个实体的某个属性可以有多个不同的取值，例如一辆车的「车型」属性，这辆车有多个「车型」，例如SUV、MPV等，这个「车型」就是多值属性， 用双线椭圆表示。
  • 派生属性：是非永久性存于数据库的属性。派生属性的值可以从别的属性值或其他数据（如当前日期）派生出来，用虚线椭圆表示。
  • 可选属性：并不是所有的属性都必须有值，有些属性的可以没有值，这就是可选属性，在椭圆的文字后用（O）来表示。
  • 联系属性：联系属性用于表示多个实体之间联系所具有的属性，一般来讲M:N的两个实体的联系具有联系属性，在1:1和1：M的实体联系中联系属性并不必要。

2.2.4 联系 #

• 实体间的联系用菱形框表示，菱形内写明联系名，并使用无向边与相关的实体链接起来。无向边旁标注联系的类型。
• 联系的类型可以分为
  • 1对1（1:1）：1对1关系是指对于实体集A与实体集B，A中的每一个实体至多与B中一个实体有关系，反之，在实体集B中的每个实体至多与实体集A中一个实体有关系。
  • 1对多（1:N）：1对多关系是指实体集A与实体集B，A中一个实体至少与B中N(N>0)个实体有关系，并且实体集B中每一个实体至多与实体集A中一个实体有关系。在这种联系中，B应当存在外键与A相连。
  • 多对多（M:N）：多对多关系是指实体集A中的每一个实体与实体集B中至少有M(M>0)个实体有关系，并且实体集B中的每一个实体与实体集A中的至少N（N>0）个实体有关系。多对多联系转换为关系时，必须转为一个独立的关系，其属性由双方实体主码和联系的属性组成。

2.3 函数依赖 #

2.3.1 种类 #

1. 完全函数依赖：在关系中，如果 X -> Y，对与 X 的任意一个真子集 X’，都不存在 X’ -> Y，则称 Y 对 X 完全依赖。例如，在成绩表（学号，课程，成绩）关系中，有函数依赖：（学号，课程）-> 成绩，但学号不决定成绩，课程不决定成绩，所以（学号，课程）->成绩 是完全函数依赖
2. 平凡函数依赖：在关系中，如果 X ->Y，但 X 包含 Y，则称 X→Y 是平凡的函数依赖。如：关系R(Sno, Cno)，依赖关系(Sno, Cno)→Sno，(Sno, Cno)→Cno都是平凡函数依赖。
3. 非平凡函数依赖：在关系中，如果 X→Y，但 X 不包含 Y，则称 X→Y 是非平凡的函数依赖。如：关系R(Sno, Cno, Grade)，依赖关系(Sno, Cno)→Grade是非平凡函数依赖。
4. 部分函数依赖：在关系中，如果 X -> Y，但 Y 不完全依赖于 X，存在 X 的子集使得 X1 -> Y，则称 Y 对 X 部分函数依赖。
5. 传递函数依赖：在关系中，当且仅当 X->Y，Y->Z 时，称 Z 对 X 传递函数依赖。例如，S# -> SB，SB -> TN，即 TN 传递函数依赖于 S#

2.3.2 公理系统 #

2.3.4 数据库范式 1~4NF #

• 1NF：每个分量是不可再分割的数据项，也就是元组的属性是不能再分割，就相当于元组不能出现多值属性和复合属性，必须是单值的简单属性。
• 2NF：在 1NF 的基础上，非主属性对码不存在部分依赖，也就是说非主属性函数一定是由码来决定的。
• 3NF：在 2NF 的基础上，非主属性对码不存在传递依赖。
• BCNF：在 1NF 的基础上，主属性对码不存在部分和传递依赖。
• 4NF：在全链表中，键的一部分可以确定至多一个其他字段的多个值，也就是说在全码表中就只允许一个多值函数依赖。

3.数据库管理系统 #

3.1 管理系统的功能 #

• 数据定义
• 数据操作
• 数据库运行管理
• 数据组织，存储和管理
• 数据的建立和维护

3.2 管理系统的特征 #

• 数据结构化且统一管理。
• 具有较高的数据独立性。
• 具备数据控制功能，控制内容如下：
  • 数据库的安全性：防止不合法使用数据库造成数据的泄露和破坏，杜绝所有可能的数据库非法访问，保护数据库以防止不合法使用造成的数据泄露、更改或破坏。
  • 数据库的完整性：通过设置一些完整性规则等约束条件，确保数据的正确性（数据是否合法）、有效性（数据是否在其定义的有效范围）和相容性（同一事实的两个数据是否相同），防止错误的数据进入数据库造成无效操作。
  • 并发控制：多名用户同时存取或修改数据库时，系统可防止由于相互干扰而提供给用户不正确的数据，并防止数据库受到破坏。实现并发控制的方法：封锁技术和时标记数。
  • 故障恢复：在数据库中的数据不正确或数据丢失时，系统有能力将数据库从错误状态恢复到最近某一时刻的正确状态。

3.3 管理系统的安全机制 #

1. 用户标识和鉴别
2. 授权和回收
3. 数据库角色
4. 视图机制：将要保密的数据对无权的用户进行隐藏
5. 审计：记录用户对数据库的所有操作
6. 数据加密：防止数据库中数据在存储和传输中的失密

3.4 管理员 DBA 的职责 #

• 决定数据库中信息内容和结构
• 决定数据库的存储结构和存储策略
• 定义数据库的安全性要求和完整性约束条件
• 监控数据库运行
• 数据库性能改进
• 数据库重组和重构
• 提高系统性能

4.数据库语言 #

4.1 SQL 语句 #

• SQL 语句对大小写不敏感。
• 某些数据库系统要求在每条 SQL 命令的末端使用分号，分号是在数据库系统中分隔每条 SQL 语句的标准方法，这样就可以在对服务器的相同请求中执行一条以上的语句。
• SQL 语句中，字符类型字段需要使用单引号。

4.1.1 创建视图 #

CREATE VIEW 视图名 
    AS SELECT 表(查询字句) 
    WITH CHECK OPTION(限制保护)

4.1.2 查询语句 #

SELECT <列名>[,...n](需要的属性名，多个属性以 , 分隔)
     FROM <表名|视图名>[,...n](需要的表名，多个表以 , 分隔)
     [WHERE <条件表达式>]

4.1.3 聚集函数 #

AVG()
MIN()
MAX()
SUM()
COUNT()

4.1.4 授权语句 #

GRANT <权限>[,...n](需要的权限名，多个属性以 , 分隔)
    ON <对象类型><对象名>
    TO <用户>[,...n](需要的用户名，多个属性以 , 分隔)
    WITH GRANT OPTION

4.2 视图 VIEW #

4.2.1 概念 #

• 视图是基于 SQL 语句结果集的一个可视化表。在一个查询需要频繁作为子查询使用时，视图可以简化代码，可以直接调用。
• 视图是一张虚拟的表，包含行和列，是从一个或几个基本表（或视图）中导出的一个查询结果，在系统的数据字典中仅存放了视图的定义，不存放视图对应的数据。
• 视图在数据库管理系统中也是一个对象，也是以文件形式存在的。

4.2.2 作用 #

• 通过视图，可以对机密数据提供安全保护，屏蔽后台数据的复杂性。
• 通过视图，可以根据用户的不同需求，为其定制专属的呈现出来的数据。
• 通过视图，使用户能以多种角度看待同一数据。
• 视图对重构数据库提供了一定程度的逻辑独立性。

4.2.1 视图与表的区别 #

• 视图是已经编译好的 SQL 语句，而表不是；
• 视图没有实际的物理记录，而表有；
• 表是内容，视图是窗口；
• 表占用物理空间而视图不占用物理空间，视图只是逻辑概念的存在。
• 表是内模式，视图是外模式；
• 表属于全局模式中的表，是实表，视图属于局部模式的表，是虚表；
• 视图的建立和删除只影响视图本身，不影响对应的基本表；
• 不能对视图进行 update 或者 insert into 等修改操作，只能通过创建来修改视图。

4.3 索引 INDEX #

4.3.1 索引的作用 #

1. 保证了数据的唯一性
2. 加快数据检索的速度
3. 加速表与表之间的连接，实现数据的参照完整性
4. 减少了在分组、排序的检索时间
5. 可以在查询过程中使用优化隐藏器来提高系统性能

4.3.2 索引的缺点 #

1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2. 除了数据表占数据空间之外，每一个索引还要占一定的物理空间。
3. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

4.3.3 创建索引的原则 #

1. 在经常需要搜索的列上，可以加快搜索的速度。
2. 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构。
3. 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度。
4. 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。
5. 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
6. 在经常使用在where子句中的列上面创建索引，加快条件的判断速度。

4.3.4 不应该创建索引的原则 #

1. 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
2. 对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
3. 对于那些定义为 text，image 和 bit 数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少，此时全表扫描会比索引更快。
4. 当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

4.4 主键 #

4.4.1 主键的定义 #

• 主键是数据表中唯一标识记录的属性，不能重复，不能为空，一个表只有一个主键。
• 主键可以由一个字段，也可以由多个字段组成，分别称为单字段主键或多字段主键，它可以唯一确定表中的一行数据，或者可以唯一确定一个实体。
• 多对多关系的主键大部分情况下由双方实体主码组合而成，并根据实际添加其他条件进行限制。

4.4.2 主键的候选码 #

• 关系模式 R<U，F>，关系名 R，一组属性 U，属性组 U 上的一组数据依赖 F。设 K 为 R 中的属性或属性组合。若 U 完全函数依赖于 K，则称 K 为 R 的候选码。候选码可能多于一个，则选定其中一个为主码（主键）。
• 例如：一个学生，身份证可以唯一表示一个学生，学号也可以唯一标识一个学生，（身份证，学号）这个集合可以唯一标识一个学生。我们可以选学号或身份证来作为这个学生的主键，那么（身份证、学号）就是候选码。
• 候选码的选择应当依循以下规则：
  • 先找只出现箭头右边的属性，它一定不是候选码。
  • 再找在函数依赖集 F 中没有出现过的属性，那么它一定是候选码。
  • 只出现在左边的一定是候选码的成员。

4.5 关系代数运算 #

4.5.1 概念 #

4.5.2 笛卡尔积（$x$） #

• 从每个集合中挑选出一个分量组成一个元组，从集合中挑选不同的分量就会组成不同的元组，所有可能的元组组成的新集合就是笛卡尔积。
• 注意：这里挑选的分量只有一个属性，如 A={ a, b, c }。

4.5.3 广义笛卡尔积（X） #

• 如果从每个元组中挑选出来的一个分量是由多个属性构成的，那么此时的笛卡尔积运算称为广义笛卡尔积运算，如 A={ (a, b, c), (b, c, d) }。
• 如果一个集合有 n 列，另一个集合有 m 列，那么笛卡尔积就一定有 n+m 列。
• 如果存在相同的列名的列，那么一定要用对象引用的方式，标明每一列属于哪个集合（关系）。比如：关系 R 和关系 S 中的三列都是 A、B、C，那么笛卡尔积的列就需要用 R.A，S.A 的方式标明。

4.5.4 选择（$ \sigma $） #

• 选择是在水平方向做的筛选，也就是在行方向做的筛选。
• 选择运算，就是在做元组筛选。
• 例如：$ {\sigma_{A>B}}(R)=\{t|t\in R,t[A]>t[B]\} $，选取关系 R 中 A > B 的内容。

4.5.5 投影（$ \pi $） #

• 投影是在垂直方向做的筛选，也就是在列方向做的筛选。
• 投影运算，就是在做列的筛选。
• 例如：${\pi_{X}}(R)=\{t|t\in R,t[ X ]\}$，选取关系 R 中 X 列的内容。

4.5.6 连接（$ >< $） #

• 对两个或者两个以上的关系进行先做笛卡尔积再做水平筛选的运算称为连接。
• 连接分为以下 3 种：
  • θ 连接：对关系做笛卡尔积合并运算后，筛选出符合指定连接条件的内容 ，例如： $R{><\atop{i <= j}}S={\sigma_{i <= j}}(R×S)$
  • 等值连接：如果 θ 运算的连接条件是 = ，那么这个连接运算称为等值连接，例如：$R{><\atop{i = j}}S={\sigma_{i = j}}(R×S)$
  • 自然连接： 如果连接符下方没有连接的条件，那么这个连接运算称为自然连接。自然连接从关系中选取具有相同元素的组进行合并，并移除合并后的重复元素。例如：$R{><}S={\sigma}(R×S)$

4.5.7 除（$ \div $） #

• 设关系 R 除以关系 S 的结果为关系 T。
• 关系 R 是 T 和 S 的笛卡尔积， T 的元组与 S 的元组的所有组合都在 R 中。
• 关系 T 包含所有在 R 但不在 S 中的属性及其值。

4.5.8 关系计算的示例 #

• 首先关系 R 与关系 S 进行自然连接运算，形成新的结果集，然后在新的结果集中进行选择运算，条件是第 2 列为软件工程的值，接下来再进行投影运算，选择 1 3 5 列。
• 由于 R 与 S 关系中，存在重复的列 C、D，因此需要在计算时加上关系的名称，表示该列来自哪个关系，如： R.C。
• 在自然连接运算时，要求在结果集中消除重复的列，即 R 与 S 关系中 C、D 列，只能保留一个关系中的 C、D 列，所以结果中只能取 R.C（或者S.C）、R.D（或者S.D）。
• 连接运算为 R.C == S.C AND R.D == S.D。

4.5.9 关系计算的优化 #

• 提早执行选取运算。对于有选择运算的表达式，优化的原则可能先执行选择运算的等价表达式，以得到较小的中间结果，减少运算量和从外存读块的次数。
• 合并乘积与其后的选择运算为连接运算。在表达式中，当乘积运算后面是选择运算时，应该合并为连接运算，使选择与乘积一道完成，以避免完成乘积后，再对一个大的乘积关系进行选择运算。
• 将投影运算与其后的其他运算同时进行，以避免重复扫描关系。
• 将投影运算和其后的二目运算结合起来，使得没有必要为去掉某些字段再扫描一遍关系。
• 在执行连接前对关系做适当的预处理，就能快速地找到要连接的元组。方法有两种：即索引连接法和排序合并连接法。
• 存储公共子表达式。公共子表达式的结果应存于外存（中间结果），这样，当从外存读出它的时间比计算时间少时，就可节约操作时间。

5.分布式数据库的透明特性 #

• 分片透明：指用户不必了解数据是如何分片的，用户或应用程序不需要知道逻辑上访问的表是怎么分块存储的。分片透明性是最高层次的透明性，如果用户能在全局关系一级操作，则数据如何分布，如何存储等细节自不必关系，其应用程序的编写与集中式数据库相同。
• 复制透明：采用复制技术的分布方法，用户不知道数据是复制到哪些节点，如何复制的。用户不用关心数据库在网络中各个节点的复制情况。
• 位置透明：用户不必知道所操作的数据放在何处，即数据分配到哪个或哪些站点存储对用户是透明的。
• 逻辑透明：局部映像透明性（逻辑透明）是最低层次的透明性，该透明性提供数据到局部数据库的映像，即用户或应用程序无需知道局部使用是哪种数据模型、使用哪种数据操纵语言。局部映像透明性对异构型和同构异质的分布式数据库系统非常重要。