1.1 数据库三级模式#

1. 数据库三级模式结构：外模式（子模式/用户模式）、模式（逻辑模式）、内模式（存储模式/物理模式），以及两层映像。

   • 外模式/模式映像：解决外模式和模式之间的对应关系，保证应用程序不受模式改变影响，提供 逻辑独立性。
   • 模式/内模式映像：解决模式和内模式之间的对应关系，保证应用程序不受存储结构改变影响，提供 物理独立性。

   数据库的三级模式由外模式、模式、内模式构成。外模式是用户可见的部分数据的存在形式；模式可以等价为全体数据的逻辑结构且用户不可见，是三级模式的中间部分；内模式对应数据库的物理结构和存储方式。当模式改变时，由数据库管理员对各个外模式/模式的映像作相应的改变，但是外模式可以不变。

2. 逻辑独立性 (Logical Data Independence)

   • 定义：当数据库的 概念模式（模式） 发生变化时（比如增加字段、改变字段名、拆表合并表），应用程序和外模式不需要修改。
   • 本质：外模式与模式之间通过 外模式/模式映像 隔离了变化。
   • 举例：
     • 原来学生表有字段：学号, 姓名
     • 后来数据库管理员在模式里加了 出生日期
     • 应用里只用 学号, 姓名 的地方照常运行，不受影响。
   • 比喻：就像你在手机上看菜单，餐厅后台换了厨师或者食材名，你点餐界面依旧一样。

3. 物理独立性 (Physical Data Independence)

   • 定义：当数据库的 存储结构（内模式） 发生变化时（比如文件结构、索引方式、存储路径），应用程序和模式不需要修改。
   • 本质：模式与内模式之间通过 模式/内模式映像 隔离了变化。
   • 举例：
     • 数据库存储学生表时，原来是 顺序存储，后来改为 B+树索引存储。
     • 对应用层的 SQL 查询 SELECT 姓名 FROM 学生 没有任何影响。
   • 比喻：就像你在外卖App上下单，不管餐厅是用煤气炒还是电磁炉炒，你看到的菜单和吃到的菜不变。

1.2 数据库应用系统逻辑设计#

数据库应用系统逻辑设计阶段工作内容分为三部分：

1. 数据库逻辑结构设计: 从数据库的概念模型出发，设计表示为逻辑模式的数据库逻辑结构，主要内容是在 ER 图的基础上设计数据库的关系模式。包括：
   1. 概念模型 → 关系模式 （以 ER 图为基础）
   2. 模式优化（如范式、反范式）
   3. 外模式设计（为用户设计视图）
2. 数据库事务概要设计：事务名称、事务所访问的关系表及关系属性、事务处理逻辑、事务用户(指使用、启动、调用该事务的软件模块或系统)。使用 read, write 表达存储操作
3. 应用程序概要设计

1.3 数据库应用系统物理设计#

物理设计阶段的主要活动有：

1. 确定存储结构：如存储方式、索引、分区等
2. 数据库事务详细设计：事务的实现细节，如并发控制、恢复策略
3. 应用程序详细设计：逻辑转成可实现的物理方案（模块、接口、数据访问方式）
4. 存取路径的选择和调整
5. 确定数据存放位置和确定存储分配

1.4 数据库应用系统事务规范#

事务规范包括了事务名称、事务描述、事务所访问的数据项、事务用户。

1.5 数据库应用系统的可行性分析#

1. 操作可行性分析: 论证是否具备DBAS开发所需的各类人员资源（项目管理人员、数据库系统分析员、应用编程人员等）、软件资源、硬件资源和工作环境等，以及为支持 DBAS 开发如何去改进加强这几方面资源。故答案为A选项。
2. 经济可行性分析
3. 技术可行性分析
4. 开发方案可行性分析

1.6 数据库应用系统生命周期#

1. 项目规划：
   1. 确定项目的目标和范围，根据系统规划与定义的工作内容，具体说明项目的最终产品以及期望的时间、成本和质量目标
   2. 根据DBAS软件开发模型，分解和定义整个项目包括的工作活动和任务
   3. 估算完成该项目的规模及所需各种资源
   4. 制定合理的DBAS项目计划，包括进度、成本、质量等方面的预测和控制方案
2. 需求分析
3. 系统设计
4. 实现与部署：（1）建立数据库结构 （2）数据加载 （3）事务和应用程序的编码及测试 （4）系统集成、测试与试运行 （5）系统部署
5. 运行与维护

实施活动: 将系统从设计阶段过渡到实际运行阶段的所有工作。

1.7 螺旋模型#

1. 项目规划

• 目标设定：明确当前周期要达成的目标和任务。例如：在这个阶段，项目团队需要确定需要完成的功能、设计、开发任务等。

2. 风险评估与规划（Risk Analysis & Planning）

• 风险识别：评估当前项目面临的潜在风险，包括技术风险、管理风险、人员风险等。团队会识别出可能导致项目失败的问题，比如技术实现困难、资源紧张、预算超支等。
• 风险评估：对于识别出的风险进行量化和优先级排序，评估它们对项目的潜在影响。
• 制定应对措施：针对每个风险，制定具体的应对策略，可能包括技术研究、原型开发、调整时间表等。

3. 工程开发与测试（Engineering & Development）

• 设计与开发：根据上一阶段的目标和需求，进行系统的设计、编码和开发工作。开发阶段可能包括数据库设计、界面设计、算法实现等。
• 系统实现：包括开发的具体实施，如编写代码、实现功能模块等。
• 测试：在开发的过程中，进行不断的测试，包括单元测试、集成测试、系统测试等，确保系统的稳定性、功能性和性能符合预期。

4. 客户评审与反馈（Customer Evaluation & Feedback）

• 客户评审：每个周期的末尾，开发团队会向客户展示当前阶段的成果。这可能是一个 原型，或者一个 部分功能实现，目的是获取客户的反馈，确保项目开发方向正确。
• 反馈与调整：客户对结果的反馈可能导致需求的调整或设计的修改。开发团队根据客户反馈对开发计划进行调整，为下一个周期的开发做准备。

1.8 数据库视图#

视图是从一个或几个基本表(或视图)中导出的虚表，数据库中只存放了视图的定义，不存放视图对应的数据。它是原始数据库数据的一种变换，是查看表中数据的另外一种方式。可以将视图看成是一个移动的窗口，通过它可以看到感兴趣的数据。

视图是从一个或多个实际表中获得的，这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。

视图的特点体现在三个方面：

1. 简单性，看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化对它们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件
2. 安全性，通过视图用户只能查询和修改他们所能见到的数据。数据库中的其它数据则既看不见也取不到
3. 逻辑数据独立性，视图可帮助用户屏蔽真实表结构变化带来的影响

根据视图的特点可以达到以下的几点作用：

1. 简化数据查询语句
2. 使用户从多角度看待同一数据
3. 提高数据安全性
4. 提供了一定程度的逻辑独立性。

1.9 DBAS的性能指标#

1. 数据操作响应时间(或数据访问响应时间)：从提交请求到返回结果的时间
2. 系统吞吐量：指系统在单位时间内所完成的事务或查询的数量，单位为TPS
3. 允许并发访问的最大用户数：在保证响应时间的前提下，系统最多允许多少用户同时访问数据库;
4. 每TPS代价值，用于衡量系统性价(性能价格)比的指标。

1.10 从功能角度DBAS的四个层次实现#

1. 表示层：负责所有与用户交互的功能，用户对数据库应用系统的最直观感受均在这层实现
2. 业务逻辑层：负责根据业务逻辑需要将表示层获取的数据进行组织后，传递给数据访问层，或将数据访问层获取的数据进行相应的加工处理后，传送给表示层用于展示。
3. 数据访问层：负责与DBMS系统进行交互，提取或存入应用系统所需的数据。
4. **数据持久层：**负责保存和管理应用系统数据。 故答案是表示。

2.1 数据库应用系统需求建模工具#

数据库应用系统需求建模工具主要有DFD、IDEFO和UML。

1. DFD (数据流图)

数据流图（Data Flow Diagram, DFD） 是用于表示信息系统中数据流动、处理过程、存储和外部实体之间关系的图形工具。DFD 是一种非常直观、简洁的表示方式，通常用于分析和设计阶段，帮助开发者理解系统如何处理数据。DFD 图采用自顶向下逐步细化的结构化分析方法。

DFD的主要元素：

• 外部项（External Entities）：系统外部的参与者或其他系统（如用户、外部设备、其他应用系统等）。通常表示为圆角矩形或平行四边形框。
• 处理（Processes）：对数据进行处理的操作，通常表示为矩形框。
• 数据流（Data Flows）：数据在系统内部和外部之间流动的路径，通常用箭头表示。
• 数据存储（Data Stores）：系统中存储的数据，如数据库、文件等，通常表示为两个平行的线条或圆角矩形

DFD的层次：

• 层级化：DFD 通常是逐层细化的，从 上下文图（最顶层）开始，逐渐展开成 分解图。上下文图只包含外部实体和系统的边界，而分解图会详细描述系统内部的每个过程。

DFD中的箭头只表示数据流，而不是控制流或约束。

2. IDEFO (功能模型)

IDEFO (Integration Definition for Function Modeling) 是一种用于表示系统功能、过程和功能之间相互关系的建模方法。IDEFO 强调系统的 功能分解 和 过程建模，常用于描述业务流程和功能需求。

IDEF0需求建模方法由箭头和矩形框两种元素构成。矩形框代表功能活动，写在矩形框内的动词短语描述功能活动的名称。

IDEFO的主要元素：

• 功能块（Function Blocks）：表示功能或过程，是模型中的核心。通常用于表示系统中的一个操作或活动。
• 输入（Inputs）：数据流向功能的输入。
• 输出（Outputs）：功能处理后产生的结果或输出。
• 控制（Controls）：约束功能或过程执行的条件或规则。
• 机制（Mechanisms）：支持功能执行的资源、工具或人员。

IDEFO 图通常是层次化的，能够清晰地显示功能的层次结构。顶层表示系统的主要功能，而下层则会逐步展开，详细描述每个子功能。

3. UML (统一建模语言)

UML (Unified Modeling Language) 是一种标准的图形化建模语言，用于软件系统的 需求建模、设计 和 文档化。它为开发人员、设计师和分析师提供了一套统一的工具，广泛应用于面向对象系统的建模。

UML 包括多种图示，常见的有以下几类：

1. 结构图：
   • 类图（Class Diagram）：表示系统的类及其关系。
   • 对象图（Object Diagram）：表示系统在某一时刻的对象状态。
   • 组件图（Component Diagram）：展示系统中各个组件的结构和依赖关系。
   • 部署图（Deployment Diagram）：展示系统如何在硬件设备上部署。
2. 行为图：
   • 用例图（Use Case Diagram）：描述系统的功能需求和用户交互。
   • 顺序图（Sequence Diagram）：展示对象之间如何通过消息进行交互。
   • 活动图（Activity Diagram）：描述系统的工作流或业务流程。
   • 状态图（State Diagram）：表示对象在生命周期中的状态变化。
3. 交互图：
   • 时序图（Timing Diagram）：描述系统各组件在特定时间内的行为。
   • 通信图（Communication Diagram）：展示系统中的对象和它们之间的交互。

2.2 数据库系统信息建模方法#

IDEF1X（Integration Definition for Information Modeling）是一种用于 信息建模 的标准方法，它专门用于设计 实体-关系（ER）模型，以支持数据库系统的结构化设计。IDEF1X 是 IDEF 系列方法中的一部分，主要用于 数据建模 和 数据存储设计。

IDEF1X 的核心目标：

IDEF1X 的主要目的是通过模型化系统中的 数据实体 和它们之间的 关系，帮助设计数据库系统的结构，特别是在 关系型数据库 的上下文中。

IDEF1X 的特点：

1. 强制性规则：IDEF1X 对实体和关系建模有严格的规范，保证了模型的标准化，便于不同团队或系统之间的交流。
2. 面向对象：它采用 面向实体 的建模方式，每个实体代表数据库中的一类对象或数据记录。
3. 支持多层次建模：IDEF1X 支持层次化的建模，能够表示不同粒度的设计内容，从总体设计到细节设计。

IDEF1X 的基本构成元素：

1. 实体（Entity）：
   • 实体表示一个数据对象或概念，通常用矩形框表示。
   • 实体具有属性，每个属性可以是 标识符 或 普通属性。
   • 每个实体可以有一个 主键（Primary Key），用于唯一标识实体的实例。
2. 属性（Attributes）：
   • 属性是实体的特征，用于描述实体的状态或性质。属性通常由 名称 和 数据类型 组成。
   • 标识符属性（通常是主键）用于唯一标识实体中的每个实例。
   • 普通属性用于描述实体的非唯一特征。
   • 主属性是包含在任何一个候选码中的属性（并集）。
   • 不包含在任何候选码中的属性称为非主(非码)属性。
3. 关系（Relationships）：
   • 关系表示实体之间的关联，关系通常用线连接两个或多个实体。
   • 每个关系有 基数（Cardinality），表示一个实体与另一个实体之间的联系，如“一对多”或“多对多”。
   • 关系还可以通过 参与度（Participation）来表示，指实体是否必须参与关系。
4. 基数（Cardinality）：
   • 基数表示实体之间关系的数量限制。常见的基数类型有：
     • 一对一（1:1）
     • 一对多（1）→ 非标定型联系（不会产生从属实体集）
     • 多对多（M） → 非确定联系（多对多联系可以通过引入一个第3个实体集来转化为两个一对多联系，这样可以通过辅助实体来表示这两个实体集之间的多对多关系。对于这两个一对多联系，它们属于标定型联系）
   • 基数帮助确定实体之间的约束条件。
5. 参与度（Participation）：
   • 完全参与（Total Participation）：实体必须参与关系。
   • 部分参与（Partial Participation）：实体可选择是否参与关系。

IDEF1X 中的符号和表示方式：

• 矩形：表示 实体。
  • 实体用实体名/实体号标识
  • 独立实体用矩形框，从属实体用圆角矩形框表示
  • 独立实体的主关键字没有外键，从属实体的主关键字含有外键
  • 从属实体的实例依赖于独立实体实例存在而存在
• 椭圆：表示 属性。
• 菱形：表示 关系（用于表示实体之间的联系）。
• 线条：表示 实体、关系 和 属性 之间的连接。标定型联系用一条实线或者线段把他们连接起来
• 基数标注：通常位于连接关系和实体的线条两端，用于指示基数（如 “1”、“N” 或 “M”）。

IDEF1X 的建模规则：

1. 每个实体必须有一个唯一标识符（主键），用于唯一标识该实体的实例。
2. 每个实体之间的关系必须通过基数表示清楚，并确保所有关系的约束都符合业务需求。
3. 每个实体只能有一个主键，并且主键只能由属性来组成。
4. 关系必须有清晰的方向性和基数，这有助于理解实体之间的交互。

2.3 数据字典#

数据字典（Data Dictionary）是数据库管理系统中用于存储关于数据库结构、约束、关系、数据类型等元数据（metadata）的重要工具。它提供了关于数据定义和数据存取的详细描述，并确保数据的一致性、完整性和规范性。

数据字典的主要作用：

1. 元数据存储：
   • 数据字典存储了有关数据库中所有数据元素的 定义 和 描述，例如表结构、字段类型、字段约束、索引、视图等。
2. 提供数据访问信息：
   • 它为开发者、管理员以及数据库管理系统（DBMS）提供了如何访问和操作数据的信息，包括字段名称、数据类型、默认值、索引等。
3. 支持数据完整性和一致性：
   • 通过对数据约束（如主键、外键、唯一性约束等）的描述，数据字典帮助确保数据的一致性和完整性。
4. 增强数据库管理：
   • 数据字典为数据库管理员提供了一个集中的视图，帮助他们管理数据库结构、监控和优化数据库。

数据字典的组成内容：

数据字典包含了数据库的多种元数据，通常包括以下几种信息：

1. 表的结构：
   • 包括每个表的名称、字段（列）名称、数据类型、长度、允许的空值（null）等。
2. 字段属性：
   • 字段名称、数据类型（如整数、字符、日期等）、默认值、是否为空、主键、外键、唯一性等。
3. 关系和约束：
   • 定义表之间的 关系（如外键约束）、约束条件（如主键、唯一约束、检查约束等）。
4. 索引：
   • 包括索引的名称、字段、索引类型（如唯一索引、普通索引、复合索引等）。
5. 视图和存储过程：
   • 记录数据库中视图（视图名称、所依赖的表、字段等）和存储过程（名称、参数、返回类型等）的信息。
6. 触发器（Triggers）：
   • 记录触发器的定义，包括触发条件、执行操作等。
7. 数据访问权限：
   • 存储有关用户、角色及其在数据库中可以执行的操作（如SELECT、INSERT、UPDATE、DELETE等）的信息。

数据字典的类型：

1. 系统数据字典：
   • 由数据库管理系统自动生成和维护，用于记录数据库结构和对象（表、视图、存储过程等）的元数据。系统数据字典通常是隐藏的，普通用户无法直接访问。
2. 用户数据字典：
   • 由开发人员或数据库管理员为特定的应用程序或项目手动创建，描述项目相关的数据对象和结构。用户数据字典通常是公开的，可以为开发和维护提供参考。

数据字典的管理和使用：

1. 维护：
   • 数据字典通常由 数据库管理员（DBA）负责维护。在数据库结构变更时（如添加新表、修改字段、添加索引等），数据字典会进行更新。
2. 查询和生成：
   • 用户可以通过查询数据库的系统数据字典来获取元数据（例如在 SQL Server 中，可以查询 INFORMATION_SCHEMA 视图）。很多数据库管理系统（如 MySQL、PostgreSQL）提供了查看数据字典的内置工具。
3. 文档化：
   • 在大型项目中，用户数据字典经常作为项目文档的一部分，用于描述数据库的设计和使用方法。它帮助开发人员理解数据的结构、关系以及如何有效地使用数据库。

数据字典的优点：

1. 规范化数据结构：
   • 通过数据字典的定义，可以确保数据库结构的一致性和规范性，减少因数据误用或不一致带来的错误。
2. 提高数据库管理效率：
   • 数据字典提供了一个中心化的信息存储，使得开发者和管理员能够快速访问和理解数据库的结构。
3. 促进协作和沟通：
   • 数据字典是团队成员之间沟通的桥梁，有助于开发者、DBA 和业务分析师之间的协作。
4. 自动化和文档化支持：
   • 数据字典可以自动生成文档，减少手动文档编写的工作量，提高工作效率。

2.4 实体之间关系#

1. 标定型联系（Identifying Relationship）

定义：

标定型联系是指子实体（弱实体）的主键部分或全部依赖于父实体的主键。也就是说，子实体无法独立存在，必须依赖于父实体才能被唯一标识。

特点：

• 子实体是弱实体（Weak Entity）。
• 子实体的主键由父实体的主键 + 自身的部分属性组成。
• 在ER图中通常用双线矩形表示弱实体，用双线菱形或带实心菱形的连线表示标定型联系。

举例：

• 实体：学生（父实体），学生证（子实体）
  • 一个学生可以有一张学生证，学生证不能脱离学生存在。
  • 学生证的主键可能包含学生的学号（来自“学生”）+ 发证年份。
  • 因此，“学生”与“学生证”之间的联系是标定型联系。

✅ 关键点：子实体的主键包含父实体的主键 → 标定型联系。

2. 非标定型联系（Non-Identifying Relationship）

定义：

非标定型联系是指子实体的主键不依赖于父实体的主键。子实体是强实体，可以独立存在，只是逻辑上与另一个实体有关联。

特点：

• 子实体是强实体（Strong Entity）。
• 联系通过外键实现，但外键不构成主键的一部分。
• 在ER图中用单线连接表示。
• 不会产生从属实体集。
• ER图中的一对多关系用 1 表示，UML 类图中用 1:* 表示

举例：

• 实体：客户 和 订单
  • 每个订单属于一个客户，但订单有自己独立的主键（如订单号）。
  • 订单表中包含客户ID作为外键，但订单号本身就能唯一标识订单。
  • 因此，“客户”与“订单”之间是非标定型联系。

✅ 关键点：外键存在，但不参与主键 → 非标定型联系。

子类型：可选 vs 必选

• 可选非标定型联系：子实体可以没有父实体（外键可为空）。
  • 例如：员工可以暂时不分配部门（部门ID可为空）。
• 必选非标定型联系：子实体必须关联一个父实体（外键不可为空）。

3. 非确定联系（Non-Deterministic Relationship） & 多对多关系（Many-to-Many）

• 两个实体之间没有明确的一对一或一对多映射。
• 例如：学生 ↔ 课程，一个学生可选多门课，一门课可被多个学生选。
• 这种关系在数据库中需要通过中间关联表来实现。
• 因为无法通过一方确定另一方，所以有人称其为“非确定”。

总结对比表：

3.1 无损连接&保持函数依赖#

对模式进行分解时，既要保证分解具有”无损连接性”，又要保证分解“保持函数依赖”。所谓”无损连接”指分解的若干连接重组时可以精确恢复到原来的数据表，数据记录既没有增加也没有减少。“保持函数依赖”是指原关系模式含有的属性之间的隐含关系在分解后不能丢失。

3.2 数据模型三要素#

1. 数据结构：数据结构是所研究的对象类型的集合。它从语法角度表述了客观世界中数据对象本身的结构和数据对象之间的关联关系,是对系统静态特征的描述。
2. 数据操作：数据操作是对数据库中对象的实例允许执行的操作的集合，主要指检索和更新(插入、删除、修改)两类操作。数据模型必须定义这些操作的确切含义、操作符号、操作规则(如优先级)以及实现操作的语言。数据操作是对系统动态特性的描述。
3. 完整性约束：数据完整性约束是一组完整性规则的集合，规定数据库状态及状态变化所应满足的条件，以保证数据的正确性、有效性和相容性。

3.3 ER 图转换为实体关系#

在ER图中，将实体和联系转换成关系模式的规则是：

1. 每一个实体集转换为一个关系模式
2. 一对一、一对多联系中一端并入多端的实体关系模式中
3. 每一个多对多联系转换成一个关系模式。

3.4 数据范式#

1. 第一范式（1NF）：

• 1NF 要求所有属性的值都是 原子值，即每个字段只包含一个值，不能有重复的记录或集合值。

2. 第二范式（2NF）：

• 2NF 要求 R 必须先满足 1NF，且 R 中的每一个非主属性必须完全依赖于 主键（即不存在部分依赖）。

3. 第三范式（3NF）：

• 3NF 要求 R 必须先满足 2NF，且每个非主属性必须直接依赖于 主键（即不存在传递依赖）。

4. 博茨-科得范式（BCNF）：

• BCNF 要求关系模式中的每一个非平凡的函数依赖 X → Y，其中 X 必须是 候选键。

3.5 完整性约束#

在数据库中，完整性约束可以根据其作用范围分为三个层次：

⚠️ 特别注意：关系级约束 是指跨越多行或多表的约束，比如：

• “每个部门最多有10名员工” → 涉及多行聚合，不能用简单的列或元组约束表达
• 外键约束虽然通常写在建表时，但它本质上是表与表之间的引用完整性约束，属于关系级

3.6 数据分布设计#

数据分布设计任务主要体现在以下几个方面：

1. 不同类型数据的物理分布：当系统采用 RAID 等多磁盘存储系统时，可以将基本表和建立在表上的索引分别放在不同的磁盘上。
2. 应用数据的划分与分布：DDBS 采用水平划分或垂直划分两种方法。
3. 派生属性数据分布
4. 关系模式的去规范化：
5. 根据实际需要对数据库中某些 3NF、BCNF 模式考虑是否可以降低其规范化程度，以提高查询效率，这种为关系模式的去规范化处理。

3.7 死锁#

在进行DBAS系统设计时，通常为了避免死锁，可以考虑以下原则：

1. 提高事务隔离性级别
2. 按同一顺序访问资源
3. 采用小事务模式，尽量缩短事务的长度，减少占有锁的时间
4. 尽量使用记录级别的锁（行锁），少用表级别的锁

3.8 数据库文件组织结构#

3.9 数据库索引类型#

4.1 三层 B/S 结构#

三层 B/S（Browser/Server）结构是典型的 Web 应用架构，分为：

1. 表现层（客户端）：浏览器（如 Chrome、Firefox）
2. 业务逻辑层（Web 应用服务器）：如 Tomcat、Nginx、Node.js 等，处理业务逻辑
3. 数据层（数据库服务器）：如 MySQL、Oracle，负责数据存储与操作

4.2 封锁粒度#

封锁粒度是指事务加锁的数据单位大小，常见的有：

📌 什么是“提高封锁粒度”？

• “提高” = 变粗
• 例如：从行锁 → 页锁 → 表锁
• 即：使用更大范围的锁

封锁开销包括：

• 锁管理器的内存消耗
• 加锁/解锁的操作次数
• 锁表的维护成本

粒度越粗封锁开销越小

4.3 原型迭代法#

1. 快速构建低保真原型（草图、线框图）
2. 与用户讨论、获取反馈
3. 修改原型
4. 逐步迭代到高保真设计
5. 最后再进行详细设计和开发

4.4 业务逻辑层概要设计#

业务逻辑层概要设计的原则主要体现在以下几个方面：

1. 构件本身应由相关性很强的代码组成，一个构件或一个模块只负责完成一项任务，也就是常说的单一责任原则
2. 组成系统业务逻辑层的各个构件应具备独立的功能，并且最大限度地减少与其他构件功能重叠
3. 构件之间的接口应尽量简单明确
4. 如果某两个构件间的关系比较复杂,应考虑进一步进行模块划分
5. 如果构件过于复杂,可以将其细分。

4.5 数据库应用系统总体设计#

在数据库应用系统开发过程中，总体设计（也称概要设计）阶段的主要任务是：

在需求分析和概念设计的基础上，进行系统的宏观架构设计，确定系统的技术路线、结构、资源配置等。

系统总体设计的主要内容包括确定 DBAS 体系结构、软硬件选型和配置设计、应用软件总体设计和业务规则初步设计。

4.6 事务的 ACID 四大性质#

4.7 两阶段加锁协议#

两阶段加锁协议是数据库中用于控制并发事务的一种封锁协议，它规定事务的加锁和解锁行为分为两个阶段：

1. 增长阶段（Growing Phase）
   • 事务可以不断申请锁（读锁、写锁）
   • 但不能释放任何锁
2. 收缩阶段（Shrinking Phase）
   • 事务可以释放锁
   • 但不能再申请新的锁

🔁 一旦开始释放锁，就不能再加锁

两阶段加锁协议的作用

• 能够保证事务调度的 可串行化（Serializable）
• 即：并发执行的结果，等价于某个串行执行顺序的结果
• 这是并发控制的最高一致性目标

4.8 类的 UML 表示#

1. 类的 UML 表示（空心三角实线连接），指的是子类对父类关系的继承
2. 接口的 UML 表示（空心三角虚线连接），与子类继承比较相似，区别主要在于多继承上
3. 聚合关系的 UML 表示（空心菱形实线连接），主要表示一种弱的拥有关系，如A对象可以包含B对象，但B对象不是A对象的一部分
4. 以及合成关系的 UML 表示（实心菱形实线连接），是一种强拥有，体现了严格的部分和整体关系。

5.1 UML 的 14 种图#

🌟 UML 的 14 种图（按 UML 2.x 标准）

UML 图分为两大类：

5.2 UML 用例模型#

1
        +-----------------------+
2
        |      网上购物系统      |
3
        |                       |
4
        |   +-------------+     |
5
        |   |   登录       |<----|---- (用户)
6
        |   +-------------+     |
7
        |         ▲             |
8
        |         | <<include>> |
9
        |   +-------------+     |
10
        |   | 验证用户身份 |     |
11
        |   +-------------+     |
12
        |         ▲             |
13
        |         | <<extend>>  |
14
        |   +-------------+     |
15
        |   | 发送验证码   |     |
16
        |   +-------------+     |
17
        +-----------------------+

1
用例名称：用户登录
2
参与者：注册用户
3
前置条件：用户已注册，系统运行正常
4
后置条件：用户登录成功，进入个人主页
5

6
主事件流：
7
1. 用户打开登录页面
8
2. 输入用户名和密码
9
3. 点击“登录”按钮
10
4. 系统验证身份
11
5. 跳转到个人主页
12

13
备选事件流：
14
2a. 用户名不存在 → 提示“用户不存在”
15
3a. 密码错误 → 提示“密码错误，请重试”
16
4a. 系统异常 → 显示“系统繁忙，请稍后重试”

5.3 UML 建模概念架构#

UML 建模概念架构中包括元元模型层、元模型层、模型层和用户模型层四个层次。

1. 元元模型层。组成了 UML 的最基本的元素“事务”，代表要定义的所有事物。它是UML体系中最高的抽象层级
2. 元模型层。组成了 UML 的基本元素，包括面向对象和面向组件的概念。这一层的每个概念都是元元模型中“事物”概念的实例
3. 模型层。组成了 UML 的模型，这一层中的每个概念都是元模型层中概念的一个实例，这一层的模型通常叫作类模型或类型模型
4. 用户模型层。层中的所有元素都是UML模型的实例。这层中的每个概念都是模型层的一个实例（通过分类）也是元模型层的一个实例。这一层的模型通常叫作对象模型或实例模型。

5.4 UML 中的四种关系#

6.1 WITH TIES#

用于在排序分页或取前 N 行时，包含并列（相同排序值）的记录。

1
SELECT TOP (n) [WITH TIES] 列名
2
FROM 表名
3
ORDER BY 排序列 [ASC|DESC]

1
CREATE TABLE Scores (
2
    StudentID INT,
3
    Name VARCHAR(50),
4
    Score INT
5
);
6

7
INSERT INTO Scores VALUES
8
(1, 'Alice', 95),
9
(2, 'Bob', 90),
10
(3, 'Carol', 90),
11
(4, 'David', 85),
12
(5, 'Eve', 90),
13
(6, 'Frank', 80);

1
SELECT TOP 2 Name, Score
2
FROM Scores
3
ORDER BY Score DESC;

1
Name   | Score
2
-------|------
3
Alice  | 95
4
Bob    | 90

1
SELECT TOP 2 WITH TIES Name, Score
2
FROM Scores
3
ORDER BY Score DESC;

1
Name   | Score
2
-------|------
3
Alice  | 95
4
Bob    | 90
5
Carol  | 90
6
Eve    | 90

1
SELECT Name, Score
2
FROM Scores
3
ORDER BY Score DESC
4
FETCH FIRST 2 ROWS WITH TIES;

6.2 标量函数#

在 SQL Server 中，标量函数（Scalar Function） 是：

• 接收 0 个或多个参数
• 返回单个值
• 可用于 SELECT、WHERE、ORDER BY 等子句中

标量函数可以返回几乎所有基础数据类型，如 int、varchar、datetime、decimal 等，不能返回 timestamp ，因为 timestamp 数据类型不是真正的“时间戳”，而是一个自动生成的二进制行版本号

6.3 INTO 关键字#

在 SQL 中，INTO 是一个非常实用的关键字，主要用于 将查询结果保存到新表中。它常用于数据备份、临时表创建、数据归档、ETL 过程等场景。

1
SELECT 列1, 列2, ...
2
INTO 新表名
3
FROM 原表
4
[WHERE 条件]
5
[ORDER BY ...]
6
[GROUP BY ...]

1
SELECT *
2
INTO Employees_Backup
3
FROM Employees
4
WHERE Department = 'Sales';

1
SELECT *
2
INTO NewTable
3
FROM OldTable
4
WHERE 1 = 0;  -- 条件永假，不复制数据

1
SELECT *
2
INTO #TempEmployees
3
FROM Employees
4
WHERE Salary > 5000;

6.4 UNION 操作符#

UNION 是 SQL 中用于合并多个 SELECT 查询结果集的重要操作符。它允许你将两个或多个查询的结果纵向“堆叠”在一起，形成一个统一的结果集。

1
SELECT column1, column2, ...
2
FROM table1
3
[WHERE condition]
4
UNION [ALL]
5
SELECT column1, column2, ...
6
FROM table2
7
[WHERE condition]
8
[ORDER BY ...];

1
SELECT ID, Name, Dept FROM Employees_China
2
UNION
3
SELECT ID, Name, Dept FROM Employees_USA;

1
ID | Name   | Dept
2
---|--------|-------
3
1  | Alice  | Sales
4
2  | Bob    | HR
5
3  | Carol  | Sales

1
SELECT ID, Name, Dept FROM Employees_China
2
UNION ALL
3
SELECT ID, Name, Dept FROM Employees_USA;

1
ID | Name   | Dept
2
---|--------|-------
3
1  | Alice  | Sales
4
2  | Bob    | HR
5
3  | Carol  | Sales
6
2  | Bob    | HR   ← 重复保留

1
SELECT Name FROM Table1
2
UNION
3
SELECT Age FROM Table2; -- ❌ 类型不兼容（字符串 vs 数字）

1
SELECT Name, CAST(Age AS VARCHAR) FROM Table1
2
UNION
3
SELECT Name, CAST(Salary AS VARCHAR) FROM Table2;

1
SELECT Name FROM Table1 ORDER BY Name -- ❌ 错误！
2
UNION
3
SELECT Name FROM Table2;

1
SELECT Name FROM Table1
2
UNION
3
SELECT Name FROM Table2
4
ORDER BY Name; -- ✅ 只在最后写一次

1
SELECT * FROM (
2
    SELECT Name FROM Table1 WHERE Dept = 'Sales' ORDER BY Name -- ❌ 子查询中 ORDER BY 无意义
3
) t1
4
UNION
5
SELECT * FROM (
6
    SELECT Name FROM Table2 WHERE Dept = 'HR' ORDER BY Name
7
) t2;

6.5 SQL 约束#

在 SQL Server 中，约束（Constraints） 是用于强制数据完整性的重要机制。它们定义在表的列或表级别上，确保插入、更新或删除数据时满足特定规则，从而保证数据库中数据的准确性、一致性和可靠性。

SQL Server 支持以下 5 种主要约束类型：

1
CREATE TABLE Employees (
2
    EmployeeID INT PRIMARY KEY,
3
    Name VARCHAR(50)
4
);

1
ALTER TABLE Employees
2
ADD CONSTRAINT PK_EmployeeID PRIMARY KEY (EmployeeID);

1
CREATE TABLE Orders (
2
    OrderID INT PRIMARY KEY,
3
    EmployeeID INT,
4
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
5
);

1
ALTER TABLE Orders
2
ADD CONSTRAINT FK_Orders_Employees
3
FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID);

1
CREATE TABLE Users (
2
    UserID INT PRIMARY KEY,
3
    Email VARCHAR(100) UNIQUE
4
);

1
ALTER TABLE Users
2
ADD CONSTRAINT UQ_Email UNIQUE (Email);

1
CREATE TABLE Products (
2
    ProductID INT PRIMARY KEY,
3
    Price MONEY CHECK (Price > 0),
4
    Category VARCHAR(20) CHECK (Category IN ('Electronics', 'Books', 'Clothing'))
5
);

1
ALTER TABLE Products
2
ADD CONSTRAINT CK_Price CHECK (Price > 0);

1
CREATE TABLE Orders (
2
    OrderID INT PRIMARY KEY,
3
    OrderDate DATETIME DEFAULT GETDATE(),
4
    Status VARCHAR(20) DEFAULT 'Pending'
5
);

1
ALTER TABLE Orders
2
ADD CONSTRAINT DF_Status DEFAULT 'Pending' FOR Status;

6.6 连接#

在 SQL 中，连接（JOIN） 是用于组合多个表中的数据的核心操作。不同类型的 JOIN 适用于不同场景，理解它们的区别对编写高效、正确的查询至关重要。

1
SELECT *
2
FROM Departments D
3
INNER JOIN Employees E ON D.DeptID = E.DeptID;

1
SELECT *
2
FROM Departments D
3
LEFT JOIN Employees E ON D.DeptID = E.DeptID;

1
SELECT *
2
FROM Departments D
3
RIGHT JOIN Employees E ON D.DeptID = E.DeptID;

1
SELECT *
2
FROM Departments D
3
FULL JOIN Employees E ON D.DeptID = E.DeptID;

1
SELECT *
2
FROM Departments D
3
CROSS JOIN Employees E;

1
SELECT e1.EmpName AS Employee, e2.EmpName AS Manager
2
FROM Employees2 e1
3
LEFT JOIN Employees2 e2 ON e1.ManagerID = e2.EmpID;

1
SELECT * FROM Departments NATURAL JOIN Employees;

6.10 DISTINCT#

当然可以！DISTINCT 是 SQL 中一个非常常用的关键字，用于去除查询结果中的重复行，返回唯一值。

1
SELECT DISTINCT 列1, 列2, ...
2
FROM 表名
3
[WHERE ...]
4
[ORDER BY ...];

1
SELECT DISTINCT Student, Course
2
FROM Enrollments;

1
SELECT COUNT(DISTINCT Student) AS StudentCount
2
FROM Enrollments;

1
SELECT DISTINCT ON (Student) Student, Course
2
FROM Enrollments
3
ORDER BY Student, Course;

6.11 GROUP BY 子句#

GROUP BY 是 SQL 中最核心、最强大的子句之一，用于对数据进行分组聚合，常与聚合函数（如 COUNT, SUM, AVG, MAX, MIN）配合使用，实现数据的分类统计。

1
SELECT 列1, 聚合函数(列2)
2
FROM 表名
3
[WHERE 条件]
4
GROUP BY 列1
5
[HAVING 分组后过滤条件]
6
[ORDER BY ...];

1
SELECT Product, SUM(Amount) AS TotalSales
2
FROM Sales
3
GROUP BY Product;

1
SELECT Region, COUNT(*) AS OrderCount
2
FROM Sales
3
GROUP BY Region;

1
SELECT Product, Region, SUM(Amount) AS TotalSales
2
FROM Sales
3
GROUP BY Product, Region;

6.12 OVER 窗口函数#

OVER 子句是 SQL 中用于定义窗口函数（Window Function） 的核心语法。它允许你在不改变行数的前提下，对一组相关的行（即“窗口”）进行计算，常用于排名、累计、移动平均等分析场景。

1
函数名() OVER (
2
    [PARTITION BY 列名]     -- 分区（可选）
3
    [ORDER BY 列名 [ASC|DESC]] -- 排序（可选）
4
    [ROWS/RANGE 子句]      -- 窗口范围（可选）
5
)

1
SELECT *,
2
       ROW_NUMBER() OVER (ORDER BY Amount DESC) AS RowNum
3
FROM Sales;

1
SELECT *,
2
       RANK() OVER (ORDER BY Amount DESC) AS Rank,
3
       DENSE_RANK() OVER (ORDER BY Amount DESC) AS DenseRank
4
FROM Sales;

1
SELECT *,
2
       SUM(Amount) OVER (ORDER BY OrderDate) AS CumulativeSum
3
FROM Sales;

1
SELECT *,
2
       SUM(Amount) OVER (PARTITION BY Product ORDER BY OrderDate) AS ProductCumulative
3
FROM Sales;

1
SELECT *,
2
       LAG(Amount, 1) OVER (ORDER BY OrderDate) AS PrevAmount,
3
       LEAD(Amount, 1) OVER (ORDER BY OrderDate) AS NextAmount
4
FROM Sales;

1
SELECT *,
2
       AVG(Amount) OVER (
3
           ORDER BY OrderDate
4
           ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
5
       ) AS MovingAvg
6
FROM Sales;

6.13 TOP 关键字#

TOP 是 SQL Server 和 MS Access 中特有的关键字，用于限制查询返回的行数。它常用于分页、取前 N 名、抽样等场景。

⚠️ 注意：

• TOP 不是 ANSI SQL 标准，在 MySQL、PostgreSQL、Oracle 中不支持
• 其他数据库有类似功能，如：
  • MySQL / PostgreSQL：LIMIT
  • Oracle：ROWNUM 或 FETCH FIRST

1
SELECT [TOP (n) | TOP n [PERCENT]] [WITH TIES] 列名
2
FROM 表名
3
[ORDER BY ...]

1
SELECT TOP 5 EmployeeID, Name, Salary
2
FROM Employees
3
ORDER BY Salary DESC;

1
SELECT TOP 10 PERCENT EmployeeID, Name, Salary
2
FROM Employees
3
ORDER BY Salary DESC;

1
SELECT TOP 3 WITH TIES EmployeeID, Name, Salary
2
FROM Employees
3
ORDER BY Salary DESC;

6.14 SELECT 逻辑执行顺序#

1
SELECT 查询内容
2
FROM 表名
3
WHERE 条件表达式
4
GROUP BY 待分组的列名
5
HAVING 列名
6
ORDER BY 待排序的列名

6.15 LIKE 操作符#

LIKE 是 SQL 中用于模糊匹配字符串的重要操作符，常用于 WHERE 子句中，配合通配符实现灵活的文本搜索。

1
SELECT 列名
2
FROM 表名
3
WHERE 列名 LIKE '模式';

1
-- 以 'A' 开头的名字
2
SELECT * FROM Employees WHERE Name LIKE 'A%';
3
→ Alice
4

5
-- 包含 'a' 的邮箱
6
SELECT * FROM Employees WHERE Email LIKE '%a%';
7
→ alice@company.com, carol@company.com, david@other.com
8

9
-- 以 '.com' 结尾的邮箱
10
SELECT * FROM Employees WHERE Email LIKE '%.com';
11
→ 所有公司邮箱

1
-- 名字是 3 个字符
2
SELECT * FROM Employees WHERE Name LIKE '___';
3
→ Bob, Eve
4

5
-- 名字以 'A' 开头，后跟两个字符
6
SELECT * FROM Employees WHERE Name LIKE 'A__';
7
→ Alice（A-l-i）

1
-- 名字以 A、B 或 C 开头
2
SELECT * FROM Employees WHERE Name LIKE '[ABC]%';
3
→ Alice, Bob, Carol
4

5
-- 名字第二个字符是 a、e 或 i
6
SELECT * FROM Employees WHERE Name LIKE '_[aei]%';
7
→ Alice (l-i-c-e), Carol (a-r-o-l)

1
-- 名字不以 A 开头
2
SELECT * FROM Employees WHERE Name LIKE '[^A]%';
3
→ Bob, Carol, David, Eve
4

5
-- 邮箱域名不是 company.com
6
SELECT * FROM Employees WHERE Email LIKE '%@[^c]%.%';
7
→ david@other.com
8
``

6.16 EXCEPT 关键字#

EXCEPT 是 SQL 中用于集合操作的关键字，用于返回第一个查询结果中存在、但第二个查询结果中不存在的行。它类似于数学中的“差集”（A - B）。

1
SELECT 列1, 列2, ...
2
FROM 表1
3
[WHERE 条件]
4
EXCEPT
5
SELECT 列1, 列2, ...
6
FROM 表2
7
[WHERE 条件];

1
SELECT EmployeeID, Name FROM Employees_2023
2
EXCEPT
3
SELECT EmployeeID, Name FROM Employees_2024;

6.17 CASE 条件表达式#

CASE 是 SQL 中最强大的条件表达式，用于实现“如果…那么…否则…”的逻辑，类似于编程语言中的 if-else 或 switch-case。它可以在 SELECT、WHERE、ORDER BY、GROUP BY 等子句中使用，实现动态计算、分类、排序等高级功能。

1
SELECT Name, Department,
2
       CASE Department
3
           WHEN 'Sales' THEN '销售部'
4
           WHEN 'HR'    THEN '人力资源部'
5
           WHEN 'IT'    THEN '技术部'
6
           ELSE '其他部门'
7
       END AS DeptChinese
8
FROM Employees;

1
SELECT Name, Salary,
2
       CASE
3
           WHEN Salary >= 10000 THEN '高薪'
4
           WHEN Salary >= 5000  THEN '中薪'
5
           ELSE '低薪'
6
       END AS SalaryLevel
7
FROM Employees;

1
-- 查询：如果部门是 Sales，则查工资 > 5000；否则查工资 > 3000
2
SELECT *
3
FROM Employees
4
WHERE Salary > CASE
5
                   WHEN Department = 'Sales' THEN 5000
6
                   ELSE 3000
7
               END;

1
-- 按部门排序：Sales 在前，然后是 IT，最后是 HR
2
SELECT *
3
FROM Employees
4
ORDER BY CASE Department
5
             WHEN 'Sales' THEN 1
6
             WHEN 'IT'    THEN 2
7
             WHEN 'HR'    THEN 3
8
             ELSE 4
9
         END;

1
-- 按工资级别分组统计人数
2
SELECT
3
       CASE
4
           WHEN Salary >= 10000 THEN '高薪'
5
           WHEN Salary >= 5000  THEN '中薪'
6
           ELSE '低薪'
7
       END AS SalaryLevel,
8
       COUNT(*) AS EmployeeCount
9
FROM Employees
10
GROUP BY
11
       CASE
12
           WHEN Salary >= 10000 THEN '高薪'
13
           WHEN Salary >= 5000  THEN '中薪'
14
           ELSE '低薪'
15
       END;

6.18 WITH AS#

WITH AS 是 SQL 中用于定义公用表表达式（Common Table Expression, CTE） 的语法结构。它允许你临时定义一个结果集，并在后续查询中像表一样引用它。CTE 使复杂查询更清晰、更易维护，支持递归查询，是现代 SQL 开发的重要工具。

1
WITH 别名 AS (
2
    SELECT 语句
3
)
4
SELECT * FROM 别名;

1
WITH HighSalary AS (
2
    SELECT EmployeeID, Name, Salary
3
    FROM Employees
4
    WHERE Salary > 5000
5
)
6
SELECT Name, Salary
7
FROM HighSalary
8
ORDER BY Salary DESC;

1
WITH
2
HighSalary AS (
3
    SELECT EmployeeID, Name, Salary
4
    FROM Employees
5
    WHERE Salary > 5000
6
),
7
DeptAvg AS (
8
    SELECT Department, AVG(Salary) AS AvgSalary
9
    FROM Employees
10
    GROUP BY Department
11
)
12
SELECT h.Name, h.Salary, d.AvgSalary
13
FROM HighSalary h
14
JOIN DeptAvg d ON h.Department = d.Department;

1
WITH EmployeeHierarchy AS (
2
    -- 锚点成员：顶级经理
3
    SELECT EmployeeID, Name, ManagerID, 0 AS Level
4
    FROM Employees
5
    WHERE ManagerID IS NULL
6

7
    UNION ALL
8

9
    -- 递归成员：下属员工
10
    SELECT e.EmployeeID, e.Name, e.ManagerID, eh.Level + 1
11
    FROM Employees e
12
    JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
13
)
14
SELECT Name, Level
15
FROM EmployeeHierarchy
16
ORDER BY Level, Name;

6.19 排名函数#

在 SQL 中，排名函数（Ranking Functions） 是窗口函数的一种，用于为查询结果集中的每一行分配一个“排名”值。它们常用于实现排行榜、分页、分组排名等场景。

SQL 标准中主要的排名函数有四个：

✅ ROW_NUMBER()
✅ RANK()
✅ DENSE_RANK()
✅ NTILE(n)

1
函数名() OVER (
2
    [PARTITION BY 列名]     -- 可选：分组
3
    ORDER BY 列名 [ASC|DESC] -- 必须：排序
4
)

1
-- ❌ 错误
2
ROW_NUMBER() OVER ()
3

4
-- ✅ 正确
5
ROW_NUMBER() OVER (ORDER BY Amount DESC)

6.20 EXISTS#

当然可以！EXISTS 是 SQL 中一个非常重要的谓词（Predicate），用于检测子查询是否返回至少一行数据。它常用于“存在性检查”，是实现“查找存在/不存在匹配项”的高效方式。

1
SELECT 列名
2
FROM 表1
3
WHERE [NOT] EXISTS (子查询);