04735 - 数据库系统原理知识点

最编程 2024-07-09 11:10:29

...

04735-数据库系统原理

人生最大的遗憾，往往并不是没有去做，而是没有坚持；明明只要再坚持努力一丢丢，却选择放弃，与成功失之交臂。

一、数据库系统概述

1. 数据库基本概念

1.1 数据

是描述事务的符号记录，是指用物理符号记录下来的、可以鉴别的信息。

多种表现形式包括数字、字母、文字、特殊字符组成的文本数据，也可以是图形、图像、动画、影像、声音、语言等多媒体数据。

数据的解释是对数据含义的说明，也称为数据的语义，即数据所蕴含的信息。数据与其语义密不可分，没有语义的数据是没有意义和不完整的。因此，数据是信息存在的一种形式，只有通过解释或处理的数据才能成为有用的信息。

1.2 数据库

是长期存储在计算机中的有组织的、可共享的大量数据集合，且数据库中的数据按一定的数据模型组织、描述和存储，具有较小的冗余度、较高的数据独立性，系统易于扩展，并可以被多个用户共享。

数据库中存储的数据具有永久存储、有组织和可共享三个基本特点。

1.3 数据库管理系统

是专门用来建立和管理数据库的一套软件，介于应用程序和操作系统之间。它负责科学有效地组织和存储数据，并帮助数据库的使用者能够从大量的数据中快速地获取所需数据，以及提供必要的安全性和完整性等统一控制机制，实现对数据有效的管理和维护。

数据库管理系统的主要功能包括：

数据定义功能（DDL）
数据操纵功能（DML）
数据库的运行管理功能
数据库的建立维护功能
数据组织、存储和管理功能
其他功能，主要包括与其他软件的网络通信功能、不同数据库管理系统之间的数据传输以及相互访问的功能等。

1.4 数据库系统

是指在计算机中引入数据库技术之后的系统。通常，一个完整的数据库系统包括数据库、数据库管理系统以及相关实用工具、应用程序、数据库管理员和用户：

数据库管理员（DBA）

不同于普通数据库用户，他们是专门负责对数据库进行维护，并保证数据库正常、高效运行的人员。
用户

数据库系统的服务对象，其通常包括程序员和数据库终端用户两类用户，程序员通过高级程序设计语言和数据库语言编写数据库应用程序，应用程序会根据需要向数据库管理系统发出适当的请求，再由数据库管理系统对数据库执行相应操作。而终端用户则是从客户机或联机终端上以交互方式向数据库系统提出各种操作请求，并由数据库管理系统操作执行，而后访问数据库中的数据。

2. 数据管理技术的发展

数据管理可以从两个方面来理解：一是针对组织业务的管理，负责制定并执行整个组织中关于数据的定义、组织、保护与有效使用的策略、过程和计划。二是依靠技术，负责实现数据作为一种资源的集中控制管理。

将收集的数据进行适当的构造，这称为数据组织。数据的组织分为逻辑组织和物理组织两种：逻辑组织是用户或应用程序所使用的数据结构形式；而物理组织则是数据在物理存储设备上的结构形式。这两者之间可以相互独立。

为了备用，需要将数据归类进行存储。为了向用户提供信息，存储的数据要能够方便的被选择提取，这称为检索。为了保护数据的正确性和安全性等，必须建立一些相应的规则和执行这些规则的过程来控制数据的存与取，以实现数据库管理的目标。

保管的数据是那些需要长期、多次使用的数据，但随着时间的推移，数据组织单位的内外环境会发生变化，因而存储的数据在规模和结构方面都可能变化，所以数据管理还必须能够对其进行维护。

数据管理的任务：进行数据收集、组织、控制、存储、选取、维护，实现在适当的时刻、以适当的形式、给适当的人、提供适当的数据，它是数据处理的中心问题，而数据处理则是指对各种数据进行收集、存储、加工和传播的一系列活动的总和。

数据管理技术共经历了人工管理、文件系统和数据库系统三个阶段：

2.1 人工管理阶段

数据不保存
应用程序管理数据
数据面向应用

2.2 文件系统阶段

文件系统是将数据的逻辑结构和物理结构分离，由 “存放方法” 实现逻辑结构和物理结构之间的映射。
应用程序只涉及数据的逻辑结构，系统决定数据的物理结构，两者之间可以有差别。
当物理结构发生改变时，不会导致应用程序的修改，这称为数据的物理独立性。
数据的物理独立性使应用程序脱离数据的物理结构，使其适用性得以提高。同时，应用程序的编程人员不必关心数据的物理存储细节，因而其生产效率相对也得到提高。
相对于人工管理数据的方法，文件系统管理数据有了很大的改进，具有数据可以长期保存和专门管理的特点，它提供了物理数据独立性，使应用程序与数据的具体物理结构分离并通过数据的抽取、排序、合并等可以为应用程序提供新的文件，从而使得数据共享成为可能；但是在文件系统中，不能实现数据的普通共享，只能实现文件级的共享，而不能在记录或数据项级实现数据的共享。文件的逻辑结构使根据它的应用而设计的，数据的逻辑结构与应用程序之间相互依赖。

2.3 数据库系统阶段

与人工管理、文件系统两种数据库管理方法相比较，数据库系统具有以下特点：

数据集成

是数据库管理系统的主要目的
数据共享性高

不只是同一数据可以被多个不同用户存取，还包含了并发共享
数据冗余小

并非所有的冗余都可以或者应该被消除
数据一致性

通过消除或者控制数据冗余，可以在一定的范围内避免数据的不一致性，引起不一致的根源是数据冗余
数据独立性高

数据定义与使用数据的应用程序分离称为数据独立。数据定义的修改，在一定范围内不会引起应用程序的修改，这种独立称为数据的逻辑独立。可改变数据的存储结构或存取方法以响应变化的需求而无需修改现有程序，这种称为数据的物理独立。
实施统一管理与控制

主要包括数据的安全性、完整性、并发控制与故障恢复等，即数据库保护
减少应用程序的开发与维护的工作量

数据库方法表现在应用方面的一个优点是在数据库上开发与维护新的应用所花费的代价和时间大大减少

3. 数据库系统结构

若从数据库管理员的角度来看，数据库系统可分为内部系统结构和外部体系结构，其中内部系统结构通常采用三级模式结构。

3.1 数据库系统的三级模式结构

模式

在三级模式中，模式也称为概念模式或逻辑模式，它是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图。

DBMS 提供模式描述语言来严格的定义模式，不仅要定义数据的逻辑结构，还要定义数据之间的联系，定义数据相关的安全性、完整性等要求。
外模式

外模式也称为子模式或用户模式，它是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述，是与某一应用有关的数据的逻辑表示。

外模式实际上是用于满足不同数据库用户需求的数据视图，即用户视图。相应地，DBMS 提供子模式描述语言来严格的定义子模式。
内模式

内模式也称为存储模式，它是对数据库中物理结构和存储方式的描述，是数据在数据库内部的表现形式。

内模式实际上是整个数据库的最底层表示，它不同于物理层，是数据库管理员所见到的，特定的 DBMS 所处理的数据库的内部结构，即内部视图或存储视图。相应地，DBMS 提供内模式描述语言来严格的定义内模式，即定义所有的内部记录类型、索引和文件的组织方式，以及所有数据控制方面的细节。
三级模式的两层映像与数据独立性

构成数据库系统三级模式结构的三个模式分别是对数据的三级抽象，它们彼此之间具有以下特点：
1. 一个数据库的整体逻辑结构和特征的描述（概念模式）是独立于数据库其他层次结构（内/外模式）的描述，其是数据库的核心，也是数据库设计的关键。
2. 一个数据库的内部存储模式依赖于概念模式，但存储模式独立于外部模式，也独立于具体的存储设备。
3. 用户逻辑结构（外模式）是在全局逻辑结构描述的基础上定义的，它面向具体的应用程序，独立于内部模式和存储设备。
4. 特定的应用程序是在外模式的逻辑结构上编写的，它依赖于特定的外模式，与数据库的模式和存储结构独立。
外模式/模式映像（逻辑独立性），所谓映像，就是一种对应规则，它指出映像双方是如何进行转换的。

模式/内模式映像（物理独立性），定义了数据库全局逻辑结构与物理存储之间的对应关系，这种映像的定义通常是在模式中加以描述的，模式/内模式映像是唯一的。

3.2 数据库系统的运行与应用结构

客户端/服务器结构（C/S）

在客户端/服务器结构中，命令行客户端、图形化界面管理工具、应用程序等称为“客户端”、“前台”或“表示层”，主要完成数据库使用者的交互任务。而数据库管理系统则称为“服务器”、“后台”或“数据层”，主要负责数据管理。这种操作数据库的模式称为客户端/服务器（C/S）模式。在这个模式中，客户端与服务器可以同时工作在同一台计算机上，这种工作方式称为“单机方式”，也可以“网络方式”进行运行。
浏览器/服务器结构（B/S）

是一种基于 Web 应用的客户端/服务器结构，也成为三层客户端/服务器结构。在数据库系统中，它将与数据库管理系统交互的客户端进一步细分为“表示层”和“处理层”，“表示层”是数据使用者的操作和展示界面，“处理层”也称为中间层。

4. 数据模型

数据模型是对现实世界数据特征的抽象，描述的是数据的共性内容。

4.1 数据特征与数据模型组成要素

数据具有静态和动态两种特征。其中数据的静态特征包括数据的基本结构、数据间的联系以及对数据取值范围的约束；数据的动态特征是指对数据可以进行符合一定规则的操作。

数据模型通常由数据结构、数据操作和数据约束三个要素组成。

数据结构描述的是系统的静态特性，即对象的数据类型、内容、属性以及数据对象之间的联系。
数据操作描述的是系统的动态特性，是对各种对象的实例允许执行的操作的集合，包括操作以及有关的操作规则。
数据约束描述数据结构中数据间的语法和语义关联，包括相互制约与依存关系以及数据动态变化规则，以保证数据的正确性、有效性和相容性。

4.2 数据模型的分类

概念层数据模型

也称为数据的概念模型或信息模型，它用来描述现实世界的事物，与具体的计算机系统无关，且独立于任何 DBMS，但容易向 DBMS 所支持的逻辑数据模型转换。

基本概念：
1. 实体（Entity）
  
  客观存在并可相互区别的事物称为实体
2. 属性（Attribute）
  
  实体所具有的某种特性称为实体的属性
3. 码/键（Key）
  
  可唯一标识实体的属性集称为码或键
4. 域（Domain）
  
  属性的取值范围称为该属性的域。
5. 实体型（Entity Type）
  
  具有相同属性的实体必然具有共同的特征和性质
6. 实体集（Entity Set）
  
  同类型实体的集合称为实体集
7. 联系（Relationship）
  
  实体内部的联系通常是指实体个属性之间的联系，实体之间的联系是指不同实体之间的联系
8. 概念模型的表示方法
  
  用 E-R 图来描述现实世界的概念模型，实体用矩形表示，属性用椭圆表示，联系用菱形表示。
逻辑层数据模型

逻辑层是数据抽象的中间层，描述数据整体的逻辑结构。

主要的逻辑数据模型有：
1. 层次模型
  
  数据库最早使用的数据模型。
  
  特点：有且仅有一个节点没有父节点，它称作根节点，其他节点有且仅有一个父节点。
2. 网状模型
  
  以网状结构表示实体与实体之间的联系。
3. 关系模型
  
  用二维表结构来表示实体与实体之间的联系的模型，并以二维表格的形式组织数据库中的数据。
  
  优点：关系模型是建立在严格的数学概念基础之上的，关系模型的概念单一，统一用关系来表示实体与实体之间的联系，对数据的检索和更新结果同样也是用关系来表示，关系模型的存取路径对用户透明，从而具有更高的数据独立性、更好的安全保密性，也简化了程序员的工作和数据库开发建立的工作。
4. 面向对象模型
  
  与数据库相结合所构成的数据模型。
物理层数据模型

也称为数据的物理模型，其描述数据在存储介质上的组织结构，是逻辑模型的物理实现，即每一种逻辑模型在实现时都有与之对应的物理模型。

从现实世界到概念模型的转换是由数据库设计人员完成的；从概念模型到逻辑模型的转换可以由数据库设计人员完成，也可以用数据库设计工具协助设计人员完成；从逻辑模型到物理模型的转换是由数据库管理系统完成。

二、关系数据库

1. 关系数据库概述

关系数据库的基本特征是使用关系数据模型组织数据，这种思想源于数学。

关系数据库的优点：包括高级的非过程语言接口、较好的数据独立性等，为商品化的关系数据库管理系统的研制做好了技术上的准备。

2. 关系数据模型

关系数据库系统是支持关系模型的数据库系统。作为一种数据模型，关系模型同样包含三个组成要素，分别是关系数据结构、关系操作结合和关系完整性约束。

2.1 关系数据结构

关系模型只包含单一的数据结构，即关系。在关系模型中，现实世界的实体以及实体间的各种联系，均是使用关系来表示。在用户看来，关系模型是把数据库表示为关系的集合，且关系数据库是以二维表格的形式组织数据。

表

也称关系，是一个二维的数据结构，它由表名、构成表的各个列及若干行数据组成。

每个表有一个唯一的表名，表中每一行数据描述一条具体的记录值。
关系

一个关系逻辑上对应一张二维表，可以为每个关系取一个名称进行标识。

关系可以有三种类型，即基本关系、查询表和视图表。基本关系通常又称为基本表或基表，是实际存在的表，它是实际存储数据的逻辑表示；查询表是查询结果；视图表是由基本表或其他视图表导出的表，是虚表，不对应实际存储的数据。
列

表中的列，也称作字段或属性。表中每一列都有一个名称，称为列名、字段名或属性名。每一列表示实体的一个属性，具有相同的数据类型。

在一个数据库中，表名必须唯一；在表中，字段名必须唯一，不同表中可以出现相同字段名；表和字段的命名应尽量有意义，并尽量简单。
属性

表中的一个列即为一个属性，给每一个属性起一个名称即为属性名。与之同义的术语是“列”。表中属性的个数称为关系的元或度。列的值称为属性值；属性的取值范围称为值域。
行

表中的行，也称作元组或记录。表中的数据是按行存储的，表中的一行数据即为一个元组或一条记录，其每行由若干字段值组成，每个字段描述该对象的一个属性或特征。
元组

表中的一行即为一个元组
分量

元组中的一个属性值称为分量
码或键

如果在一个关系中，存在这样的属性，使得在该关系的任何一个关系状态中的两个元组，在该属性上值的组合都不相同，即这些属性的值都能用来唯一标识该关系的元组，则称这些属性为该关系的码或键。
超码或超键

如果在关系的一个码中移去某个属性，它仍然是这个关系的码，则称这样的码或键为该关系的超码或超键。一般每个关系至少有一个默认的超码或超键，即该关系的所有属性的集合，也是这个关系的最大超码或超键。
候选码或候选键

如果在关系的一个码或键中，不能从中移去任何一个属性，否则它就不是这个关系的码或键，则称这样的码或键为该关系的候选码或候选键，一个关系的候选码或候选键是这个关系的最小超码或超键。

有些情况下，需要几个属性才能唯一确定一条记录。
主码或主键

在一个关系的若干个候选码或候选键中指定一个用来唯一标识关系的元组，则称这个被指定的候选码或候选键位该关系的主码或主键。
全码或全键

一个关系模式的所有属性集合是这个关系的主码或主键，则称这样的主码或主键为全码或全键。
主属性和非主属性

关系中包含任何一个候选码中的属性称为主属性或码属性，不包含在任何一个候选码中的属性称为非主属性或非码属性。
外码或外键

当关系中的某个属性不是这个关系的主码或候选码，而是另一个关系的主码时，称该属性为这个关系的外码或外键。
参照关系和被参照关系

参照关系也称为从关系，被参照关系也称为主关系，它们是指以外码或外键相关联的两个关系，以外码作为主码的关系称为被参照关系，外码所在的关系称为参照关系。被参照关系域参照关系是通过外码相联系的，这种联系通常是一对多的联系。
域

表示属性的取值范围
数据类型

表中每个列都有相应的数据类型，它用于限制该列中存储的数据。每个字段表示同一类信息，具有相同的数据类型。
关系模式

同数据模型一样，数据库也有型和值之分，在关系数据库中，关系模式是型，关系是值，即关系模式是对关系的描述。

关系模式是静态的、稳定的，而关系是动态的、随时间不断变化的。这是因为关系操作在不断地更新着数据库中的数据。人们常常会把关系模式和关系都笼统地称为关系。
关系数据库

关系数据库是以关系模式作为数据库的逻辑模型，并采用关系作为数据组织方式的一类数据，其数据库操作建立在关系代数的基础上。在一个给定的应用领域中，所有关系的集合构成一个关系数据库。

关系数据库对关系是有限定的，具体要求如下：
1. 每一个属性都是不可分解的
2. 每一个关系仅仅有一种关系模式
3. 每一个关系模式中的属性必须命名，在同一个关系模式中，属性名必须是不同的
4. 同一关系中不允许出现候选码或候选键完全相同的元组
5. 在关系中元组和属性的顺序是无关紧要的，可以任意交换

2.2 关系操作集合

基本的关系操作

关系模型中常用的关系操作包括查询（Query）操作和插入（Insert）、删除（Delete）、修改（Update）操作两大部分。

查询操作分为：选择、投影、连接、除、并、差、交、笛卡尔积等。

五种基本操作：选择、投影、并、差、笛卡尔积。

关系操作的特点是集合操作方式，即操作的对象和结果都是集合，这种操作方式也称为一次一集合的方式。
关系数据语言的分类

关系操作是通过关系语言来实现的，关系语言的特点是高度非过程化，即用户不必请求数据库管理员为其建立特殊的存取路径，存取路径的选择由 DBMS 的优化机制来完成；用户也不必求助于循环和递归来完成数据的重复操作。

关系操作的能力可以用代数方式和逻辑方式来表示。

关系代数、元组关系演算和域关系演算均是抽象的查询语言。

结构化查询语言 SQL 充分体现了关系数据语言的特点和优点，是关于数据库的标准语言。

关系数据语言可以分为三类：关系代数语言、关系演算语言及兼具两种特点的语言。共同特点是：语言具有完备的表达能力，是非过程化的集合操作语言，功能强，能够独立使用也可以嵌入高级语言中使用。

关系代数

操作包含三大要素：操作对象、操作符、操作结果。在关系代数操作中，操作对象和操作结果均为关系。

关系代数直接应用关系的运算来表达操作目的，而代数用到的运算符包括集合运算符和专门的关系运算符两类：

运算符		含义
集合运算符	∪	并
	∩	交
	-	差
	⨉	笛卡尔积
专门的关系运算符	∂	选择
	π	投影
	⨝	连接
	÷	除
比较操作符	>	大于
	⩾	大于等于
	<	小于
	⩽	小于等于
	=	等于
	≠	不等于
逻辑操作符	┐	非
	∧	与
	∨	或

关系代数经过有限次复合的式子称为关系代数操作表达式，简称关系代数表达式。

传统的集合运算

是二目运算，关系看成元组的集合，其运算关系是从关系的水平方向，即行的角度来进行，具体有并、差、交、笛卡尔积 4 种运算。

并

R3 = R1 ∪ R2，R3 是由属于关系 R1 或 R2 的所有不同元组组成
差

R3 = R1 - R2，R3 是由属于关系 R1，但不属于 R2 的元组组成
交

R3 = R1 ∩ R2，R3 是由既属于关系 R1，同时又属于 R2 的元组组成
笛卡尔积

R3 = R1 X R2，R1 为 M 元关系，R2 为 N 元关系，R3 是由 R1 和 R2 的所有元组连接而成的具有（M + N）个分量的元组组成

专门的关系运算

不仅涉及行，而且涉及列，它可分为一元专门关系操作和二元专门关系操作。一元专门关系操作包括对单个关系进行垂直分解的投影运算和进行水平分解的选择运算。二元专门关系操作则是对两个关系进行操作，包括连接运算和除运算。

选择（SELECT）

SELECT 关系名 WHERE 条件，其中条件是由常数、属性名或列名、比较操作符及逻辑操作符组成的条件表达式。
投影（PROJECTION）

PROJECTION 关系名（属性名1，属性名2，...... 属性名N）
连接（JOIN）

JOIN 关系名1 AND 关系名2 WHERE 条件，条件是由比较操作符和属性名或列名组成的条件表达式，连接操作中有两种最为常用的连接：等值连接和自然连接。
除（DIVISION）

R ÷ S，R 和 S 代表两个不同的关系，在除运算中，若被除关系为 M 元关系，除关系为 N 元关系，则运算结果为一个 M- N 元关系。

2.3 关系的完整性约束

数据库的数据完整性是指数据库中数据的正确性、相容性和一致性。

实体完整性约束

是指关系的主属性，即主码的组成不能为空，也就是关系的主属性不能是空值 NULL。
参照完整性约束

就是定义外码和主码之间的引用规则，它是对关系间引用数据的一种限制。

描述定义：若属性 F 是基本关系 R 的外码，它与基本关系 S 的主码 K 相对应，则对 R 中每个元组在 F 上的值只允许两种可能，即要么取空值，要么等于 S 中某个元组的主码值。
用户定义完整性约束

是针对某一应用环境的完整性约束条件，它反映了某一具体应用所涉及的数据应满足的要求。
关系模型完整性约束的校验

为了维护数据库中数据的完整性，在对关系数据库执行插入、删除和更新操作时，需要检验是否满足上述三类完整性约束。

3. 关系数据库的规范化理论

3.1 关系模式下可能存在的冗余和异常问题

数据冗余
更新异常
插入异常
删除异常

3.2 函数依赖与关键字

函数依赖是指关系中属性间的对应关系。设 R 为任一给定关系，如果对于 R 中属性 X 的每一个值，R 中属性 Y 只有唯一值与之对应，则称 X 函数决定 Y 或称 Y 函数依赖于 X，记作 X -> Y，其中 X 称为决定因素。

完全函数依赖

设 R 为任一给定关系，X、Y 为其属性集，若 X -> Y，且对 X 中的任何真子集 X 都有 X -> Y，则称 Y 完全函数依赖于 X。
部分函数依赖

设 R 为任一给定关系，X、Y 为其属性集，若 X -> Y，且 X 中存在一个真子集 X，满足 X -> Y，则称 Y 部分函数依赖于 X。
传递函数依赖

设 R 为任一给定关系，X、Y、Z 为其不同属性子集，若 X -> Y，Y -> X，Y -> Z，则有 X -> Z，称为 Z 传递函数依赖于 X。

关键字的定义：设 R 为任一给定关系，U 为其所含的全部属性集合，X 为 U 的子集，若有完全函数依赖 X -> U，则 X 为 R 的一个候选关键字。

3.3 范式与关系规范化过程

一个低一级范式的关系模式通过模式分解可以转换为若干个高一级范式的关系模式的集合，这种过程化就叫规范化。

第一范式

设 R 为任一给定关系，如果 R 中每一列与行的交点处的取值都是不可再分的基本元素，则称 R 为第一范式。第一范式是一个不含重复组的关系，其中不存在嵌套结构，不满足第一范式的关系为非规范关系。
第二范式

设 R 为任一给定关系，若 R 为 1NF，且其所有非主属性都完全函数依赖与候选关键字，则 R 为第二范式。
第三范式

设 R 为任一给定关系，若 R 为 2NF，且其每一个非主属性都不传递函数依赖于候选关键字，则 R 为第三范式。
BCNF

设 R 为任一给定关系，X、Y 为其属性集，F 为其函数依赖集，若 R 为 3NF，且其 F 中所有函数依赖 X -> Y（Y 不属于 X）中的 X 必须包含候选关键字，则 R 为 BCNF。

3.4 关系规范化理论的应用

主要应用于数据库设计中的概念设计阶段。在实现设计中当将 E-R 图向关系模型转换时，还可以用它来分析发现概念设计中可能存在的遗漏或不当之处，特别是联系实体是否不单独转换为一独立关系而集成到与之相连的基本实体中取处理时，规范化理论时最有效的评价准则。

三、数据库设计

1. 数据库设计概述

1.1 数据库的生命周期

数据库的生命周期可分为两个阶段：

数据库分析与设计阶段
- 需求分析
- 概念设计
- 逻辑设计
- 物理设计
数据库实现与操作阶段
- 数据库的实现
- 操作与监督
- 修改与调整

1.2 数据库设计的目标

设计的目标：

满足应用功能需求

指用户当前与可预知的将来应用所需求的数据及其联系，应全部准确地存储在数据库中，从而可满足用户应用中所需要的对数据进行存、取、删、改等操作。
良好的数据库性能

主要是指对数据库的高效率存取和空间的节省，并具有良好的数据共享性、完整性、一致性及安全保密性。

1.3 数据库设计的内容

数据库结构设计

是针对给定的应用环境进行数据库的模式或子模式的设计，包括数据库的概念结构设计、逻辑结构设计和物理结构设计。模式定义并给出各应用程序共享的结构，是静态的，一经形成通常不会改变。
数据库行为设计

是确定数据库用户的行为和动作，而用户的行为和动作是对数据库的操作，它们通常是通过应用程序来实现的，行为设计是动态的。

1.4 数据库设计的方法

直观设计法

最原始的数据库设计方法
规范设计法
1. 新奥尔良设计方法
2. 基于 E-R 模型的数据库设计方法
3. 基于第三范式的设计方法
计算机辅助设计法

通常通过人机交互的方式来完成（例如 CASE 工具）

1.5 数据库设计的过程

数据库设计过程实际上是一个反复修改、反复设计的迭代过程。

需求分析阶段
结构设计阶段

包括概念结构设计、逻辑结构设计和物理结构设计
行为设计阶段

包括功能设计、事务设计和程序设计
数据库实施阶段

包括加载数据库和调试运行应用程序
数据库运行和维护阶段

2. 数据库设计的基本步骤

数据库设计的特点是结构设计与行为设计分离。

2.1 需求分析

需求分析是数据库设计的起点，目标是了解与分析用户的信息及应用处理的要求，并将结果按一定的格式整理而形成需求分析报告，该分析报告是后续过程的依据。

数据库设计人员进行需求分析的基本方法是听取数据库应用部门工作人员的报告，并与之座谈。

需求分析分为四个步骤：

确定数据库范围

数据库设计的第一项工作就是要确定数据库范围，即确定数据库应支持哪些应用功能。
应用过程分析

是指了解并分析数据与数据处理间的关系，应用过程分析的结果是数据库结构设计的重要依据。数据库应用过程可以借助数据流图或其他信息及应用结构图形表示。
收集与分析数据

数据收集与分析的任务是了解并分析数据的组成格式及操作特征，每个数据元素的语义及关系等，并将它们收集起来整理归档。

数据流图中所涉及的数据都是收集与分析的对象，主要包括报表、文件、单据及各种原始资料。

数据的收集与分析工作：
- 静态结构
  
  是指不施加应用操作于其上时数据的原始状况，这可通过数据分类表和数据元素表进行说明：
  1. 数据分类表用于数据的总体描述，格式如下：
    
    数据 id 数据名用途主人用户来源去向存档时间数据量
  2. 数据元素表指通常意义下的数据项或属性，格式如下：
    
    数据 id 数据元素 id 元素名意义类型长度算法备注
- 动态结构
  
  是指将应用操作施加于数据之上后数据的状况，可通过任务分类表和数据表操作特征表进行说明。
  1. 任务分类表示根据对数据流图的分析，可将业务处理过程划分为不同任务，格式如下：
    
    任务 id 名称功能类型主人用户执行日期频率操作过程备注
  2. 数据操作特征表用以描述任务和数据之间的关系，包括不同任务对数据执行不同的操作频率，格式如下：
    
    任务 id 数据 id 建立查询插入修改删除数据量
- 数据约束
  
  是指使用数据时的特殊要求：
  1. 数据的安全保密性
  2. 数据的完整性
  3. 响应时间
  4. 数据恢复
编写需求分析报告

作为需求分析的一个总结，内容如下：
- 数据库的应用功能目标
- 标明不同用户视图范围
- 应用处理过程需求说明
  1. 数据流程图
  2. 任务分类表
  3. 数据操作特征表
  4. 操作过程说明书
- 数据字典
  
  是数据库系统中存储三级结构定义的数据库，通常指的是数据库系统中各类数据详细描述的集合。包括数据分类表、数据元素表和各类原始资料。
- 数据量
  
  根据数据分类表中的静态数据量和操作特征表中的动态数据量，进行统计计算，求出数据总量。
- 数据约束
  1. 数据的安全保密性
  2. 数据的完整性
  3. 响应时间
  4. 数据恢复

2.2 概念结构设计

概念结构设计的任务是需求分析中产生的需求分析报告的基础上，按照特定的方法设计满足应用需求的用户信息结构，该信息结构通常称为概念模型。E-R 图作为概念模型的描述工具。

概念结构设计的常用方法有实体分析法和属性综合法两种，它们也分别称为自顶向下和自底向上法。

2.3 逻辑结构设计

逻辑结构设计的目标是将概念模型转为等价的、并为特定 DBMS 所支持数据模型的结构。

数据库逻辑模型一般由层次、网状、关系数据模型表示。

逻辑结构设计的输入信息包括：

独立于特定 DBMS 的概念模型
有关响应时间、安全保密性、数据完整性及恢复方法的要求说明，包括保持数据一致性的规则说明。
数据量及使用频率
特定 DBMS 特性，包括 DBMS 支持的数据模型及数据定义语言的说明。

逻辑结构设计的输出信息包括：

一个特定的 DBMS 支持的概念模式，或称之为模式
一个或多个外部视图，或称之为子模式
物理设计说明，其主要包括存入数据库中的数据量、使用频率及响应时间要求
程序设计说明

逻辑结构设计的步骤：

模型转换是指将概念模型等价地转换为特定 DBMS 支持的关系模型、网状模型或层次模型表示
子模式设计的目标是抽取或导出模式的子集，以构造不同用户使用的局部数据逻辑结构
编制应用程序设计说明的目的是为可实际运行的应用程序设计提供依据与指导，并作为设计评价的基础
设计评价的任务是分析并检验模式及子模式的正确性与合理性

2.4 物理设计

是指对于一个给定的数据库逻辑结构，研究并构造物理结构的过程，其具体任务主要是确定数据库在存储设备上的存储结构及存取方法，因 DBMS 的不同还可能包括建立索引和聚集，以及物理块大小、缓冲区个数和大小、数据压缩的选择等。

2.5 数据库实施

工作包括：加载数据、应用程序设计和数据库试运行。

2.6 数据库运行和维护

系统维护中最困难的工作是数据库重组与重构。

3. 关系数据库设计方法

关系数据库是一类采用关系模型作为逻辑数据模型的数据库系统，遵从数据库设计的基本步骤，包括：需求分析、逻辑结构设计、物理结构设计、数据库实施、数据库的运行和维护等阶段。

关系数据库的概念结构设计与逻辑结构设计是关系数据库整个设计过程的关键。

3.1 关系数据库设计过程与各级模式

在关系数据库设计的不同阶段，会形成数据库的各级模式：

在需求分析阶段，综合各个用户的应用需求
在概念结构设计阶段，形成独立于机器特点、独立于各个关系数据库管理系统产品的概念模式
在逻辑结构设计阶段，将 E-R 图转换成具体的数据库产品支持的关系数据模型，形成数据库逻辑模式，然后根据用户处理要求、安全性的考虑，在基本表的基础上再建立必要的视图，形成数据的外模式
在物理结构设计阶段，根据关系数据库管理系统的特点和处理的需要，进行物理存储安排，建立索引，形成数据库内模式

3.2 概念结构设计方法

关系数据库的概念结构设计通常采用自顶向下法，它通过两个步骤来完成概念设计，首先建立局部信息结构，然后将局部信息结构合成全局信息结构并优化，使用 E-R 图作为概念模式的描述工具。

E-R 图的表示方法：

概念结构设计就是将需求分析得到的用户需求抽象为信息结构的过程，通常使用 E-R 图来作为描述现实世界的建模工具。E-R 图提供了表示现实世界中实体、属性和联系的方法。

实体型，用矩形表示，写明实体的名称
属性，用椭圆形表示，并用无向边将其与相应的实体连接起来
联系，用菱形表示，写明联系的名称，用无向边分别与有关实体连接起来，同时在无向边旁标注联系的类型（1:1、1:N 或 M:N），如果一个联系具有属性，则这些属性也要用无向边与该联系连接起来

两个及两个以上的实体型之间和单个实体型内的联系，都会存在如下关系：

一对一联系（1:1）
一对多联系（1:N ）
多对多联系（M:N）

局部信息结构设计：

根据需求分析报告中标明的不同用户视图范围所建立的满足该范围内用户需求的信息结构，称为局部信息结构。

局部信息结构的设计步骤包括确定局部范围、选择实体、选择实体关键字、确定实体间联系、确定实体的属性。

全局信息结构设计：

全局信息结构设计是将上述步骤中产生的所有局部信息结构合并成一个全局信息结构。

各局部 E-R 图之间的冲突主要表现在三个方面：

属性冲突

属性域冲突和属性取值单位冲突
命名冲突

同名异义或异名同义
结构冲突
1. 同一对象在一个局部 E-R 图中作为实体，而在另一个局部 E-R 图中作为属性
2. 同一实体在不同的 E-R 图中属性个数和类型不同
3. 实体之间的联系在不同的 E-R 图中是不同的类型

3.3 逻辑结构设计方法

逻辑结构设计的任务是把概念结构设计产生的概念模型转换为具体的 DBMS 所支持的逻辑数据模型，也就是导出特定的 DBMS 可以处理的数据库逻辑结构。

通常包括三项工作：

E-R 图向关系模型转换

遵循原则如下：
- 一个实体型转换为一个关系模式
- 一个一对一联系可以转换为一个独立的关系模式
- 一个一对多联系可以转换为一个关系模式
- 一个多对多联系转换为一个关系模式
- 三个多对多联系转换为一个关系模式
- 具有相同码的关系模式可以合并
数据模型的优化

方法如下：
- 确定各属性之间的函数依赖关系
- 对于各个关系模式的范式，根据实际需要确定最合适的范式
- 判断每个关系模式的范式，根据实际需要确定最合适的范式
- 按照需求分析阶段得到的处理要求，分析这些模式对于这样的应用环境是否合适，确定是否要对某些模式进行合并或分解
- 对关系模式进行必要的分解，提高数据操作的效率和存储空间的利用率
设计用户子模式

定义用户模式具体包括以下几个方面：
- 可以通过视图机制在设计用户视图时，重新定义某些属性的别名，使其更符合用户的习惯，以方便使用
- 可以对不同级别的用户定义不同的视图，以保证系统的安全性
- 简化用户对系统的使用

3.4 物理设计方法

关系数据库系统的优点之一是用户通常不用进行数据的存储结构和存取方法的设计。

物理设计的任务主要是通过对关系建立索引和聚集来实现与应用相关数据的逻辑连接和物理聚集，以改善对数据库的存取效率。

建立索引：

索引的建立是通过 DBMS 提供的有关命令来实现的，用以建立索引的哪些属性也应是其所在关系中使用频率较高的属性，建立索引通常有静态和动态两种：

静态建立索引是指应用人员预先建立索引，一旦建立，后续的应用程序均可通过该索引存取数据，它多适合于用户较多且使用周期相对较长的数据。

动态建立索引是指应用人员在程序内外临时建立索引，它适合于单独用户或临时性使用要求情况。

建立聚集：

聚集是将相关数据集中存放的物理存储技术，借以提高 IO 的数据命中率而改善存取速度，其功能由具体的 DBMS 所提供，如 MySQL。

数据聚集结构的一种有效结构方式是块结构方式，块与块之间由指针连接，一个块对应于一个物理分区。

四、SQL 与关系数据库基本操作

1. SQL 概述

SQL 已经成为关系数据库的标准语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理数据库系统。功能不仅仅是查询，还包括数据定义、数据操纵和数据控制等用于数据库有关的一系列功能。

1.1 SQL 的发展

各个关系数据库管理系统产品在实现标准 SQL 时各有差别，与 SQL 标准的符合程度也不相同，但它们仍然遵循 SQL 标准。

1.2 SQL 的特点

SQL 不是某个特定数据库供应商专有的语言
简单易学
尽管看上去简单，但它实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作

1.3 SQL 的组成

四大功能：数据查询、数据定义、数据操纵和数据控制。

数据定义语言：

主要用于对数据库中的各种对象进行创建、删除、修改等操作。

数据库对象主要有表、默认约束、规则、视图、触发器、存储过程等。

包括 SQL 语句有：

CREATE

用于创建数据库或数据库对象
ALTER

用于对数据库或数据库对象进行更改
DROP

用于删除数据库或数据库对象

数据操纵语言：

主要用于操纵数据库中各种对象，检索和修改数据。

包括 SQL 语句主要有：

SELECT

用于从表或视图中检索数据，使用最为频繁的语句之一
INSERT

用于将数据插入到列表或视图中
UPDATE

用于修改表或视图中的数据，其既可修改表或视图中一行数据，也可同时修改多行或全部数据
DELETE

用于删除表或视图中的数据

数据控制语言：

主要用于安全管理。

主要 SQL 语句如下：

GRANT

用于授权，把语句许可或对象许可的权限授予其他用户和角色
REVOKE

用于收回权限

嵌入式和动态 SQL 规则：

规定了 SQL 语句在高级程序设计语言中使用的规范方法，以便适应较为复杂的应用。

SQL 调用和会话规则:

调用包括 SQL 例程和调用规则，以便提高 SQL 的灵活性、有效性、共享性以及使 SQL 具有更多的高级语言的特征。

2. MySQL 预备知识

是一个关系数据库管理系统（RDBMS），它具有客户/服务器体系结构。

2.1 MySQL 使用基础

两种架构方式：

LAMP

即使用 Linux 作为操作系统，Apache 作为 Web 服务器，MySQL 作为数据库管理系统，PHP、Perl 或 Python 语言作为服务器端脚本解释器。
WAMP

即使用 Windows 作为操作系统，Apache 作为 Web 服务器，MySQL 作为数据库管理系统，PHP、Perl 或 Python 语言作为服务器端脚本解释器。

2.2 MySQL 中的 SQL

作为一种关系数据库管理系统，遵循 SQL 标准，提供了对数据库定义语言 DDL、数据操纵语言 DML、数据控制语言 DCL 的支持，同样支持关系数据库的三级模式结构。

MySQL 中一个关系对应一个基本表，一个或多个基本表对应一个存储文件，一个表可以有若干索引，索引也存放在存储文件中。

MySQL 在 SQL 标准的基础上增加了部分扩展语言要素：包括常量、变量、运算符、表达式、函数、流程控制语句和注解等：

常量

是指在程序运行过程中值不变的量，也称为字面值或标量值。常量的使用格式取决于值的数据类型，可分为字符串常量、数值常量、十六进制常量、时间日期常量、位字段值、布尔值和 NULL 值：
- 字符串常量是指用单引号括起来的字符序列，分为 ASCII 字符串常量和 Unicode 字符串常量。
- 数据值常量可以分为整数常量和浮点数常量。整数常量是不带小数点的十进制数。浮点数常量则是使用小数点的数值常量。
- 一个十六进制值通常指定为一个字符串常量，每对十六进制数字被转换为一个字符，其最前面有一个大写字母 X 或小写字母 x。
- 日期和时间常量是用单引号将表示日期时间的字符串括起来构成的。
- 使用 b'value' 格式书写位字段值，Value 是一个用 0 和 1 书写的二进制值。
- 布尔值的两个值：TRUE（数字1）和 FALSE （数值0）。
- NULL 值通常用于表示 “没有值”、“无数据” 等意义，与数字类型的 “0” 或字符串类型的空字符串是完全不同的。
变量

在 MySQL 中，变量分为用户变量和系统变量。用户变量前常添加一个符号 “@”，大多数系统变量前添加两个 “@” 符号。
运算符

MySQL 几类编程语言中常用的运算符：
- 算数运算符
  
  +（加）、-（减）、*（乘）、/（除）、%（取模）
- 位运算符
  
  &（位与）、|（位或）、^（异位或）、~（位取反）、>>（位右移）、<<（位左移）
- 比较运算符
  
  =（等于）、>（大于）、<（小于）、>=（大于等于）、<=（小于等于）、<>（不等于）、!=（不等于）、<=>（相等或都等于空）
- 逻辑运算符
  
  NOT 或 !（逻辑非）、AND 或 &&（逻辑与）、OR 或 ||（逻辑或）、XOR（逻辑异或）
表达式

是常量、变量、列名、复杂计算、运算符和函数的组合，表达式可分为字符表达式、数值型表达式和日期表达式。
内置函数

MySQL 包含了 100 多个函数，基本分类如下：
- 数学函数，如 ABS()、SORT()
- 聚合函数，如 COUNT()
- 字符串函数，如 ASCII()、CHAR()
- 日期和时间函数，如 NOW()、YEAR()
- 加密函数，如 ENCODE()、ENCRYPT()
- 控制流程函数，如 IF()、IFNULL()
- 格式化函数，如 FORMAT()
- 类型转换函数，如 CAST()
- 系统信息函数，如 USER()、VERSION()

3. 数据定义

关系数据库系统支持三级模式结构，其模式、外模式和内模式中的基本对象有数据库模式、表、索引、视图等。

SQL 标准提供的数据定义语句如下表：

操作对象	操作方式
操作对象	创建	删除	修改
模式	CREATE SCHEMA 语句	DROP SCHEMA 语句
表	CREATE TABLE 语句	DROP TABLE 语句	ALTER TABLE 语句
视图	CREATE VIEW 语句	DROP VIEW 语句

3.1 数据库模式定义

创建数据库

CREATE {DATABASE|SCHEMA}[IF NOT EXISTS] db_name
[DEFAULT]CHARACTER SET[=]charset_name | [DEFAULT]COLLATE[=]collation_name

选择数据库
```
USE db_name;
```

修改数据库

ALTER {DATABASE|SCHEMA}[db_name]
	alter_specification ...

删除数据库

DROP {DATABASE|SCHEMA}[IF EXISTS]db_name

查看数据库

SHOW {DATABASES|SCHEMAS}[LIKE 'pattern'|WHERE expr]

3.2 表定义

成功创建数据库之后，才能在数据库中创建数据表。

数据表被定义为字段的集合，数据在表中是按照行和列的格式来存储的，每一行代表一条记录，每一列代表记录中一个字段的取值。

创建表
```
CREATE [TEMPORARY] TABLE tbl_name
(
字段名1 数据类型 [列级完整性约束条件][默认值]
[,字段名2 数据类型 [列级完整性约束条件][默认值]
......]
    [,表级完整性约束条件]
)[ENGINE=引擎类型]；
```
- 临时表与持久表
  
  在创建表的 CREATE TABLE 语句中，若添加可选项 “TEMPORARY” 关键字，则表示使用该语句创建的表为临时表，而若不选用该关键字创建表则为持久表。
- 数据类型
  
  指系统中所允许的数据的类型。主要的数据类型包括数值类型、日期和时间类型、字符串类型、空间数据类型等。
- 关键字（AUTO_INCREMENT）
  
  可以为表中数据类型为整型的列设置为自增属性。
- 指定默认值
  
  是指在向表插入数据时，如果没有明确给出某个表所对应的值，则 DBMS 此时允许为此表列指定一个值。
- NULL 值
  
  NULL 值是指没有值或缺值。不能将 NULL 值与空串相混淆，NULL 是没有值的，它不是空串。
- 主键
  
  是通过 PRIMARY KEY 关键字来指定的。主键值必须唯一，而且主键一定要为 NOT NULL。
更新表

在 MySQL 中，可以使用 ALTER TABLE 语句来更改原有表的结构。例如可以增加或删减列、创建或取消索引、更改原有列的数据类型、重新命名列或表，还可以更改表的评注和引擎类型等，甚至还可以使用该语句为表重新创建触发器、存储过程、索引和外键等。
- ADD[COLUMN] 子句
  
  增加新列，可同时增加多个列。
- CHANGE[COLUMN] 子句
  
  修改表中列的名称或或数据类型。
- ALTER[COLUMN] 子句
  
  需要修改或删除表中指定列的默认值。
- MODIFY[COLUMN] 子句
  
  只会修改指定列的数据类型，而不会干涉它的列名。
- DROP[COLUMN] 子句
  
  卸除表中多余的列。
- RENAME[TO] 子句
  
  为表重新赋予一个表名。

重命名表

RENAME TABLE tb1_name TO new_tb1_name[,tb1_name2 TO new_tb1_name2] ...

删除表

DROP [TEMPORARY] TABLE [IF EXISTS] 
tb1_name[,tb1_name] ...
[RESTRICT | CASCADE]

查看表

显示表的名称

SHOW [FULL] TABLES [{FROM | IN} db_name]

显示表的结构

SHOW [FULL] COLUMNS {FROM | IN} tb_name [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]
{DESCRIBE | DESC} tb1_name [co1_name | wild]

3.3 索引定义

索引是 DBMS 根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系，因而索引实质上是一张描述索引的列值与原表记录中记录行之间一一对应关系的有序表。

弊端：

索引是以文件形式存储的，DBMS 会将一个表的所有索引保存在同一个索引文件中，索引文件需要占用磁盘空间。
索引在提高查询速度的同时，会降低更新表的速度。

分类：

普通索引

最基本的索引类型，没有任何限制。
唯一性索引

索引列中的所有值都只能出现一次，必须是唯一的。
主键

一种特殊的唯一性索引。

索引的创建：

使用 CREATE INDEX 语句创建

CREATE [UNIQUE] INDEX index_name ON tb1_name(index_col_name,...)

使用 CREATE TABLE 语句创建
使用 ALTER TABLE 语句创建

索引的查看：

SHOW {INDEX|INDEXES|KEYS} {FROM|IN} tb1_name [{FROM|IN} db_name][WHERE expr]

索引的删除：

使用 DROP INDEX 语句
```
DROP INDEX index_name ON tb1_name
```
使用 ALTER TABLE 语句

4. 数据更新

添加若干行数据、修改表中的数据和删除表中若干行数据。

4.1 插入数据

使用 INSERT …… VALUES 语句插入单行或多行元组数据

INSERT [INTO] tb1_name [(col_name,……)] {VALUES|VALUE}({expr|DEFAULT},……),(……),……

使用 INSERT …… SET 语句插入部分列值数据

INSERT [INTO] tb1_name SET col_name = {expr|DEFAULT},……

使用 INSERT …… SELECT 语句插入子查询数据
```
INSERT 					
```


																				
						
							上一篇：							
								数据库原理与应用							
						
						
							下一篇：							
								数据库系统原理（第 1 章概述）							
						
					
															
						
							推荐阅读						
						
														
								
									
										电脑组成基础知识点整理：了解计算机系统的概览——408计算机组成原理学习笔记									
								
							
														
								
									
										商业运作的核心原理，新手必备知识点讲解									
								
							
														
								
									
										无线传感器网络技术原理与应用》知识点									
								
							
														
								
									
										数据库系统原理（第 3 章 数据库设计）									
								
							
														
								
									
										数据库系统 数据库设计原理									
								
							
														
								
									
										数据库系统原理 数据库系统概述									
								
							
														
								
									
										数据库系统原理									
								
							
														
								
									
										数据库原理：数据库系统概述									
								
							
														
								
									
										数据库系统原理 "通过指南									
								
							
														
								
									
										数据库系统原理（第 8 章：数据库管理技术的发展）