数据库原理与应用期末复习

目录

第 1 章 概述

第 2 章 关系模型的基本概念

第 3 章 SQL 语言

第 4 章 中级 SQL

第 5 章 高级 SQL

第 6 章 关系代数语言

第 7 章 数据库设计和 ER 模型

第 8 章 关系数据库设计

第 13 章 事务

第 14 章 并发控制与恢复

---

第 1 章 概述

Database-management system (DBMS)

1. 功能概述
   • 是用于管理数据库的软件系统，在数据库系统中起着核心作用，负责数据的存储、检索、更新和管理等操作，为用户和应用程序提供了对数据库进行操作的接口。
2. 与数据库系统应用的关系
   • 在线事务处理（Online transaction processing）：DBMS 支持大量并发用户执行实时事务，如银行转账、电子商务交易等，确保事务的原子性、一致性、隔离性和持久性（ACID 特性）。例如，在电商平台的购物流程中，用户下单、支付、库存更新等操作都需要 DBMS 保证事务处理的正确性和高效性。
   • 数据分析（Data analytics）：DBMS 可以存储和管理大量数据，为数据分析提供基础。通过查询和分析工具，用户可以从数据库中提取有价值的信息，帮助企业做出决策。例如，企业可以分析销售数据来了解市场趋势、客户需求等。
3. 与文件处理系统的对比
   • 数据不一致性（Data inconsistency）：文件处理系统中，数据可能分散在多个文件中，由不同的应用程序管理，容易导致数据不一致。例如，一个员工的信息在人事文件和财务文件中可能存在不同的记录。而 DBMS 通过集中管理数据，采用一致性约束（Consistency constraints）等机制来确保数据的一致性。
   • 一致性约束（Consistency constraints）：DBMS 可以定义各种约束，如主键约束、外键约束、检查约束等，保证数据满足特定的规则。例如，在学生成绩管理系统中，成绩字段可以定义取值范围的检查约束，防止录入不合理的成绩。
4. 数据抽象（Data abstraction）
   • 物理层（Physical level）：描述数据在存储设备上的实际存储方式，包括文件结构、存储格式、索引等细节，用户通常不需要关心这一层的具体实现。
   • 逻辑层（Logical level）：定义数据的逻辑结构，如关系模式、表结构、视图等，用户可以通过逻辑层来操作数据，而不必了解物理层的存储细节。
   • 视图层（View level）：为用户提供了个性化的数据视图，是从一个或多个基本表（或其他视图）导出的虚拟表，用户可以通过视图进行数据查询和操作，同时可以对用户隐藏部分敏感数据或复杂的数据结构。
5. 实例（Instance）与模式（Schema）
   • 实例（Instance）：是数据库在某一时刻的具体数据内容，即数据库模式在特定时间点的取值，会随着数据的插入、删除和更新而变化。例如，在某一时刻学生数据库中实际存储的学生记录就是数据库实例。
   • 模式（Schema）
     • 物理模式（Physical schema）：描述数据库的物理存储结构，如数据文件的组织方式、索引的创建等，与硬件和操作系统密切相关，决定了数据在物理存储上的布局。
     • 逻辑模式（Logical schema）：定义数据库中数据的逻辑结构，包括关系模式、属性、约束等，是数据库设计的核心部分，独立于物理存储细节，应用程序主要通过逻辑模式来操作数据。
     • 子模式（Subschema）：是逻辑模式的子集，为特定用户或应用程序提供了个性化的数据视图，隐藏了不必要的信息，增强了数据的安全性和易用性。
6. 物理数据独立性（Physical data independence）：指当数据库的物理存储结构发生改变时（如更换存储设备、调整存储格式等），应用程序不需要修改，因为 DBMS 可以通过映射机制将逻辑模式与物理模式分离，使得逻辑模式不受物理存储变化的影响。
7. 数据模型（Data models）
   • 实体 - 关系模型（Entity - relationship model）：用于描述现实世界中的实体及其之间的关系，通过 E - R 图来直观表示，是数据库概念设计的重要工具，帮助设计人员理解和分析数据需求，为数据库逻辑设计提供基础。
   • 关系数据模型（Relational data model）：用二维表来表示数据和数据之间的关系，是目前最常用的数据模型之一，具有严格的数学理论基础，关系代数和关系演算等理论为关系数据库的操作和优化提供了支持。
   • 半结构化数据模型（Semi - structured data model）：适用于处理具有一定结构但不完全符合传统关系模型的数据，如 XML、JSON 格式的数据，能够灵活地表示复杂的数据结构，常用于 Web 数据和文档数据的管理。
   • 对象 - 基于数据模型（Object - based data model）：将数据和操作封装在一起，以对象的形式进行处理，更符合面向对象编程的思想，适合处理复杂对象和对象之间的关系，常用于工程设计、多媒体等领域。
8. 数据库语言（Database languages）
   • 数据定义语言（Data - definition language）：用于定义数据库的结构，包括创建数据库、表、视图、索引等，以及定义数据类型、约束、权限等。例如，CREATE TABLE 语句用于创建表，ALTER TABLE 语句用于修改表结构，GRANT 语句用于授权等。
   • 数据操纵语言（Data - manipulation language）
     • 过程性 DML（Procedural DML）：用户需要指定数据操作的详细步骤和过程，如使用游标来逐行处理数据，在一些早期的数据库系统或特定应用场景中使用。
     • 声明性 DML（Declarative DML）/ 非过程性 DML（nonprocedural DML）：用户只需声明要做什么，而不需要指定如何做，DBMS 会自动确定操作的执行方式，如 SQL 中的 SELECT、INSERT、UPDATE、DELETE 语句，更方便用户使用，提高了开发效率。
   • 查询语言（Query language）：是数据操纵语言的一部分，主要用于从数据库中检索数据，如 SQL 中的 SELECT 语句，用户可以通过指定查询条件、连接操作、聚合函数等来获取所需的数据。
9. 数据定义语言（Data - definition language）中的具体内容
   • 域约束（Domain Constraints）：定义属性的取值范围，如定义年龄属性为整数且在 15 到 45 之间，确保数据的有效性和合法性。
   • 参照完整性（Referential Integrity）：通过外键约束来保证相关表之间数据的一致性，例如在学生选课表中，学生学号必须是学生表中存在的学号，课程号必须是课程表中存在的课程号。
   • 授权（Authorization）
     • 读授权（Read authorization）：决定用户是否有权读取数据库中的数据，例如授予普通用户读取学生成绩表中成绩的权限，但可能限制其查看其他敏感信息。
     • 插入授权（Insert authorization）：控制用户是否可以向表中插入新数据，例如只允许管理员向用户表中插入新用户记录，防止非法用户随意添加数据。
     • 更新授权（Update authorization）：指定用户对数据进行更新的权限范围，如允许教师更新其所教授课程的成绩，但不允许学生自行修改成绩。
     • 删除授权（Delete authorization）：确定用户是否能够删除数据库中的数据，例如只有管理员有权删除过期的订单记录，以保证数据的安全性和完整性。
10. 元数据（Metadata）：关于数据的数据，包括数据库模式、表结构、索引、用户权限等信息，由 DBMS 自动维护和管理，用于帮助 DBMS 管理和操作数据，同时也为开发人员和管理员提供了关于数据库结构和配置的信息。
11. 应用程序（Application program）与数据库设计（Database design）

• 应用程序（Application program）：通过 DBMS 提供的接口与数据库交互，实现各种业务功能，如企业资源规划（ERP）系统、客户关系管理（CRM）系统等，应用程序需要正确地调用数据库语言来操作数据库中的数据。
• 数据库设计（Database design）
  • 概念设计（Conceptual design）：使用 E - R 模型等工具，从用户需求出发，识别和定义实体、属性和关系，建立数据库的概念模型，是数据库设计的第一步，为后续设计提供了总体框架。
  • 规范化（Normalization）：将关系模式分解为更合适的范式，以减少数据冗余、避免数据更新异常等问题，如将一个包含学生信息、课程信息和成绩的大表分解为学生表、课程表和选课表，提高数据库的性能和可维护性。
  • 功能需求规范（Specification of functional requirements）：明确数据库应支持的功能，如数据存储、查询、更新、报表生成等，为数据库设计和应用程序开发提供依据。
  • 物理设计阶段（Physical - design phase）：根据逻辑设计和应用需求，确定数据库的物理存储结构，包括选择存储设备、确定文件组织方式、创建索引等，以优化数据库的性能。

1. 数据库引擎（Database Engine）

• 存储管理器（Storage manager）
  • 授权和完整性管理器（Authorization and integrity manager）：负责管理用户权限，确保用户只能进行其授权范围内的操作，同时维护数据的完整性，检查数据是否满足各种约束条件。
  • 事务管理器（Transaction manager）：协调和管理事务的执行，确保事务的 ACID 特性，处理事务的开始、提交、回滚等操作，保证数据库在并发事务和系统故障情况下的正确性。
  • 文件管理器（File manager）：负责管理数据库文件的存储、读取和写入操作，包括数据文件和日志文件等，确保数据在物理存储上的正确组织和访问。
  • 缓冲管理器（Buffer manager）：管理内存中的数据缓冲区，提高数据的读写效率，减少磁盘 I/O 操作，将经常访问的数据块缓存在内存中，以加速数据的访问速度。
  • 数据文件（Data files）：存储数据库中的实际数据，是数据库的核心存储部分，数据以特定的格式和结构存储在数据文件中。
  • 数据字典（Data dictionary）：存储数据库的元数据，包括数据库结构、用户信息、权限等，是 DBMS 管理数据库的重要依据，通过查询数据字典可以获取数据库的各种信息。
  • 索引（Indices）：用于提高数据查询的效率，通过创建索引可以快速定位和访问数据，如在学生表的学号字段上创建索引，可以加快根据学号查询学生信息的速度。
• 查询处理器（Query processor）
  • DDL 解释器（DDL interpreter）：解析和执行数据定义语言（DDL）语句，如创建表、修改表结构等操作，将 DDL 语句转换为数据库内部的操作指令，更新数据字典等相关信息。
  • DML 编译器（DML compiler）：对数据操纵语言（DML）语句进行编译和优化，将 DML 语句转换为可执行的操作序列，选择合适的执行计划，提高数据操作的效率。
  • 查询优化（Query optimization）：分析查询语句，选择最优的查询执行计划，考虑数据的分布、索引的使用、连接算法等因素，以最小化查询执行的成本（如时间和资源消耗）。
  • 查询评估引擎（Query evaluation engine）：根据优化后的执行计划执行查询操作，从数据库中检索数据，对数据进行处理和计算，最终返回查询结果给用户或应用程序。

1. 数据库架构（Database Architecture）与数据库应用架构（Database Application Architecture）

• 数据库架构（Database Architecture）：包括集中式、并行、分布式等架构类型，不同架构有不同的特点和适用场景。集中式架构将数据库系统集中在一台服务器上，管理简单但扩展性有限；并行架构利用多个处理器或节点并行处理数据，提高处理能力；分布式架构将数据分布在多个节点上，实现数据的分布存储和处理，提高可靠性和扩展性。
• 数据库应用架构（Database Application Architecture）
  • 两层架构（Two - tier）：由客户端应用程序和数据库服务器组成，客户端直接与数据库服务器通信，适用于简单的应用场景，但随着应用复杂度增加，可能存在性能和维护问题。
  • 三层架构（Three - tier）：包括客户端、应用服务器和数据库服务器，客户端通过应用服务器间接访问数据库，应用服务器可以处理业务逻辑、提供安全控制和负载均衡等功能，提高了系统的灵活性、可维护性和安全性，适用于复杂的企业级应用。
  • 应用服务器（Application server）：在多层架构中起着关键作用，负责处理业务逻辑、管理事务、提供数据缓存和安全控制等功能，将客户端与数据库服务器分离，提高了系统的性能和可扩展性，同时便于系统的维护和升级。

1. 数据库管理员（DBA）：负责数据库的规划、设计、安装、配置、维护、监控、优化和安全管理等工作，确保数据库系统的正常运行和高效性能，需要具备数据库技术、操作系统、网络等多方面的知识，是数据库系统中不可或缺的角色。例如，DBA 需要定期备份数据库、优化查询性能、管理用户权限、处理系统故障等

第 2 章 关系模型的基本概念

1. 关系（Relation）
   • 定义：一个关系就是一个二维表，表中的行对应元组，列对应属性。例如，学生表（student）就是一个关系，其中每行代表一个学生的信息（一个元组），每列代表学生的一个属性（如学号、姓名等）。
   • 特点：关系中的任意两个元组不能完全相同，但属性名允许重复。例如，在学生表中不能有两个完全相同的学生记录（所有属性值都相同），但可以有多个列名为 “年龄”（属性名重复）的情况（实际应用中一般不这样设计，但从概念上是允许的）。
2. 关系模式（Relation schema）
   • 定义：对关系的描述，包括关系名、属性名、属性的数据类型等。例如，学生关系模式可以表示为 student (sno CHAR (5), sname VARCHAR (20), ssex CHAR (2), sage INT, sdept VARCHAR (20))，其中 “student” 是关系名，“sno”“sname” 等是属性名，“CHAR (5)”“VARCHAR (20)” 等是属性的数据类型。
   • 作用：用于定义关系的结构，是数据库设计的重要部分，它决定了关系中可以存储哪些数据以及数据的类型和格式。
3. 关系实例（Relation instance）
   • 定义：关系模式在某一时刻的取值，即二维表中的数据。例如，在某一时刻，学生表中实际存储的学生信息（如学号为 “95001” 的学生姓名为 “张三” 等具体数据）就是学生关系模式的一个关系实例。
   • 与关系模式的关系：关系模式是相对稳定的结构定义，而关系实例会随着数据的插入、删除和更新而变化。
4. 属性（Attribute）
   • 定义：二维表中的一列，也称为字段。例如，学生表中的 “学号”“姓名”“性别” 等都是属性，每个属性都有其特定的数据类型和取值范围。
   • 属性的域：属性的取值范围称为域，例如 “性别” 属性的域可能是 {男，女}，“年龄” 属性的域可能是某个整数范围（如 15 到 45 岁之间）。
5. 元组（Tuple）
   • 定义：二维表中的一行，也称为记录。例如，学生表中一个学生的完整信息（如学号 “95001”、姓名 “张三”、性别 “男”、年龄 “20”、所在系 “CS”）就是一个元组。
   • 元组的唯一性：在一个关系中，不允许有两个完全相同的元组，这是由关系的定义和实体完整性约束保证的。
6. 数据库模式（Database schema）
   • 定义：数据库中所有关系模式的集合。例如，一个学校数据库可能包含学生关系模式、课程关系模式、选课关系模式等，这些关系模式共同构成了该数据库的数据库模式。
   • 重要性：数据库模式定义了数据库的整体结构和组织方式，是数据库设计的核心，它决定了数据库中数据的存储和管理方式，以及数据之间的关系。
7. 数据库实例（Database instance）
   • 定义：数据库模式在某一时刻的取值，即数据库中所有关系实例的集合。例如，在某一时刻，学校数据库中所有关系（学生表、课程表、选课表等）中的实际数据就是该数据库模式的一个数据库实例。
   • 动态性：数据库实例会随着时间的推移，由于数据的操作（插入、删除、更新）而发生变化，而数据库模式相对稳定，除非进行数据库结构的修改（如添加或删除关系模式、修改关系模式的结构等）。
8. 超键（Superkey）
   • 定义：能唯一标识关系中元组的属性或属性组。例如，在学生表中，{学号，姓名} 可以作为一个超键，因为通过学号和姓名的组合可以唯一确定一个学生元组（假设姓名不重复）；{学号，年龄，性别} 也可以是超键，只要它能确保唯一性。
   • 特点：超键可能包含多余的属性，只要能唯一标识元组即可。例如，{学号，姓名，系别，年龄} 是超键，但如果学号本身就能唯一标识，那么姓名、系别和年龄在这里就是多余的属性。
9. 候选键（Candidate Key）
   • 定义：不含多余属性的超键。例如，在学生表中，如果学号是唯一的，那么学号就是一个候选键；如果姓名和性别组合起来能唯一确定一个学生（假设不允许同名同性别的学生存在），那么 {姓名，性别} 也是候选键。
   • 作用：候选键用于确定关系中的主键，在关系数据库设计中具有重要意义，它可以保证数据的唯一性和完整性，并且可以作为其他关系建立关联的依据（如外键引用）。
10. 主键（Primary Key）

• 定义：从候选键中任选一个作为主键。例如，在学生表中，如果选择学号作为候选键，那么学号就可以被指定为主键。
• 特性：主键具有唯一性（不允许有重复值）和非空性（不能取空值），它是关系中最重要的约束之一，用于唯一标识关系中的每个元组，确保数据的完整性和一致性，并且在建立表之间的关联（通过外键）时起到关键作用。

第 3 章 SQL 语言

1. SELECT 语句
   • 用于从表中查询数据，基本语法为：
     • SELECT [ALL|DISTINCT] {*|column1 [AS alias1], column2 [AS alias2],...}
     • FROM table_name1 [AS alias1], table_name2 [AS alias2],...
     • [WHERE condition]
     • [GROUP BY column1, column2,...]
     • [HAVING condition]
     • [ORDER BY column1 [ASC|DESC], column2 [ASC|DESC],...]
   • 例如，查询学生表（student）中所有学生的信息：SELECT * FROM student;
   • 查询学生表中年龄大于 18 岁的学生姓名和年龄：SELECT sname, sage FROM student WHERE sage > 18;
2. INSERT 语句
   • 用于向表中插入数据，有两种常见形式：
     • 每次插入一条记录：INSERT INTO table_name [(column1, column2,...)] VALUES (value1, value2,...);
     • 插入子查询的结果：INSERT INTO table_name [(column1, column2,...)] subquery;
   • 例如，向学生表中插入一条学生记录：INSERT INTO student (sno, sname, ssex, sage, sdept) VALUES ('95021', '李四', '男', 20, 'CS');
3. DELETE 语句
   • 用于从表中删除数据，语法为：DELETE FROM table_name [WHERE condition];
   • 例如，删除学生表中学号为 '95020' 的学生记录：DELETE FROM student WHERE sno = '95020';
4. UPDATE 语句
   • 用于更新表中的数据，语法为：UPDATE table_name SET column1 = value1, column2 = value2,... [WHERE condition];
   • 例如，将学生表中所有学生的年龄增加 1 岁：UPDATE student SET sage = sage + 1;
5. CREATE TABLE 语句
   • 用于创建表，语法为：CREATE TABLE table_name (column1 data_type [constraint1], column2 data_type [constraint2],...);
   • 例如，创建一个课程表（course）：CREATE TABLE course (cno CHAR(5) PRIMARY KEY, cname VARCHAR(50), credit INT);
6. ALTER TABLE 语句
   • 用于修改表的结构，如添加列、修改列的数据类型、添加约束等，语法为：ALTER TABLE table_name [ADD|ALTER|DROP COLUMN|CONSTRAINT]...
   • 例如，向学生表中添加一个新列（email）：ALTER TABLE student ADD email VARCHAR(50);

第 4 章 中级 SQL

1. 连接类型（与第 3 章合并考察）
   • 内连接（INNER JOIN）：返回两个表中满足连接条件的行，语法为：SELECT * FROM table_name1 INNER JOIN table_name2 ON condition; 或 SELECT * FROM table_name1, table_name2 WHERE condition;（隐式内连接）。例如，查询学生和选课信息：SELECT student.sno, sname, cno, grade FROM student INNER JOIN sc ON student.sno = sc.sno;
   • 左外连接（LEFT OUTER JOIN）：返回左表中的所有行，以及右表中满足连接条件的行，语法为：SELECT * FROM table_name1 LEFT OUTER JOIN table_name2 ON condition;。例如，查询所有学生及其选课信息（包括未选课的学生）：SELECT student.sno, sname, cno, grade FROM student LEFT OUTER JOIN sc ON student.sno = sc.sno;
   • 右外连接（RIGHT OUTER JOIN）：返回右表中的所有行，以及左表中满足连接条件的行，语法为：SELECT * FROM table_name1 RIGHT OUTER JOIN table_name2 ON condition;。
   • 全外连接（FULL OUTER JOIN）：返回两个表中的所有行，当某行在另一个表中没有匹配行时，则另一个表的相应列为空值，语法为：SELECT * FROM table_name1 FULL OUTER JOIN table_name2 ON condition;。
2. 视图的定义与使用
   • 创建视图（CREATE VIEW）：语法为CREATE VIEW view_name [(column1, column2,...)] AS subquery [WITH CHECK OPTION];。例如，创建一个计算机科学系学生的视图：CREATE VIEW cs_student AS SELECT * FROM student WHERE sdept = 'CS';
   • 查询视图（SELECT FROM）：可以像查询基本表一样查询视图，例如：SELECT * FROM cs_student;
   • 更新视图（INSERT INTO、UPDATE、DELETE FROM）：在一定条件下，可以通过视图对基本表的数据进行更新，但要注意视图定义的限制和相关约束。例如，向视图中插入一条记录（如果视图可更新）：INSERT INTO cs_student VALUES ('95022', '王五', '男', 19, 'CS');
3. 事务（与 17 - 19 章合并考察）
   • 事务的概念：事务是用户定义的一组操作序列，这些操作要么都执行，要么都不执行，是并发控制的基本单位，具有原子性、一致性、隔离性和持久性（ACID 特性）。
   • 事务的操作：
     • 开始事务：BEGIN TRANSACTION或START TRANSACTION。
     • 提交事务：COMMIT。
     • 回滚事务：ROLLBACK。例如，在一个银行转账事务中，先从一个账户扣除金额，再向另一个账户增加金额，若过程中出现错误，则回滚整个事务，保证数据的一致性。
4. 完整性的概念
   • 数据库完整性是指数据的正确性、有效性和相容性，包括实体完整性、参照完整性和用户定义完整性。
5. SQL 中定义、修改各类完整性
   • 主键（PRIMARY KEY）：用于保证关系中主键的唯一性和非空性，定义方式有列级约束和表级约束。例如，在创建学生表时定义主键：CREATE TABLE student (sno CHAR(5) PRIMARY KEY, sname VARCHAR(6), ssex CHAR(2), sage INT, sdept CHAR(10));（列级约束），或CREATE TABLE student (sno CHAR(5), sname VARCHAR(6), ssex CHAR(2), sage INT, sdept CHAR(10), PRIMARY KEY(sno));（表级约束）。
   • 外键（FOREIGN KEY）：用于保证参照完整性，定义方式为：FOREIGN KEY (column_name) REFERENCES parent_table (parent_column_name)。例如，在创建选课表（sc）时定义外键关联学生表：CREATE TABLE sc (sno CHAR(5) FOREIGN KEY REFERENCES student(sno), cno CHAR(2), grade INT, PRIMARY KEY(sno, cno));
   • 检查约束（CHECK）：用于定义用户自定义的完整性约束，限制列的取值范围，例如：CREATE TABLE student (sno CHAR(5), sname VARCHAR(6), ssex CHAR(2) CHECK(ssex IN ('男', '女')), sage INT CHECK(sage >= 15 AND sage <= 20), sdept CHAR(10));
   • 唯一约束（UNIQUE）：用于保证列或列组合的唯一性，例如：CREATE TABLE student (sno CHAR(5) UNIQUE, sname VARCHAR(6), ssex CHAR(2), sage INT, sdept CHAR(10));
   • 非空约束（NOT NULL）：用于保证列的值不能为空，例如：CREATE TABLE student (sno CHAR(5) NOT NULL, sname VARCHAR(6), ssex CHAR(2), sage INT, sdept CHAR(10));
   • 默认约束（DEFAULT）：用于为列指定默认值，例如：CREATE TABLE student (sno CHAR(5), sname VARCHAR(6), ssex CHAR(2), sage INT DEFAULT 18, sdept CHAR(10));
   • 添加字段（ALTER TABLE ADD COLUMN）：例如，向学生表中添加一个新字段（phone）：ALTER TABLE student ADD phone VARCHAR(20);
   • 删除字段（ALTER TABLE DROP COLUMN）：例如，从学生表中删除字段（phone）：ALTER TABLE student DROP COLUMN phone;
   • 添加约束（ALTER TABLE ADD CONSTRAINT）：例如，为学生表的姓名字段添加唯一约束：ALTER TABLE student ADD CONSTRAINT UK_student_sname UNIQUE(sname);
   • 删除约束（ALTER TABLE DROP CONSTRAINT）：例如，删除学生表中姓名字段的唯一约束：ALTER TABLE student DROP CONSTRAINT UK_student_sname;
   • 添加索引（CREATE INDEX）：例如，为学生表的学号字段创建索引：CREATE INDEX IX_student_sno ON student(sno);
   • 删除索引（DROP INDEX）：例如，删除学生表的学号索引：DROP INDEX IX_student_sno ON student;
6. SQL 数据类型和模式（会用）
   • 数据类型：
     • 字符型：如CHAR(n)（定长字符型）、VARCHAR(n)（变长字符型）、TEXT（可存储任意长字符串）等。
     • 统一码字符型：如NCHAR(n)、NVARCHAR(n)、NTEXT等。
     • 整型：如BIGINT、INT、SMALLINT、TINYINT等。
     • 实型：如DECIMAL(p, s)（精确数值型）、NUMERIC(p, s)、REAL（精确到 7 位有效数字）、FLOAT（精确到 15 位有效数字）等。
     • 货币型：如MONEY（占 8 个字节，保留 4 位小数）、SMALLMONEY（占 4 个字节，保留 4 位小数）等。
     • 日期时间型：如DATETIME、SMALLDATETIME等。
   • 模式：可以理解为数据库对象的命名空间，用于组织和管理数据库对象，但文档中未详细提及具体使用方式，一般在数据库管理系统中有相关的操作和设置。
7. 授权（GRANT、REVOKE、查看授权等）
   • 授权（GRANT）：用于向用户或角色授予权限，例如，授予用户user1对学生表的查询权限：GRANT SELECT ON student TO user1;，也可以授予多个权限，如GRANT SELECT, INSERT, UPDATE ON student TO user1;。
   • 收回权限（REVOKE）：用于收回用户或角色已有的权限，例如，收回用户user1对学生表的插入权限：REVOKE INSERT ON student FROM user1;。
   • 查看授权：不同数据库系统查看授权的方式可能不同，一般可以通过系统表或系统视图来查看，例如在某些数据库中可以查询系统表来获取用户的权限信息，但文档中未详细提及具体查询方式。

第 5 章 高级 SQL

函数

1. 概念
   • 函数是一段可重复使用的 SQL 代码，用于执行特定的计算或操作，并返回一个值。它可以接收参数，在函数体中进行处理，最后返回结果。函数在数据库中主要用于封装常用的计算逻辑，提高代码的复用性和可维护性。
   • 例如，在一个学生成绩管理系统中，可能需要计算学生的平均成绩、总成绩等，这些计算逻辑可以封装成函数，方便在不同的查询或应用程序中调用。
2. 定义函数
   • 语法：

收起

sql

CREATE FUNCTION function_name([parameter1 data_type1 [= default_value1], parameter2 data_type2 [= default_value2],...])

RETURNS return_data_type

AS

BEGIN

    -- 函数体，包含变量声明、逻辑处理等

    DECLARE variable1 data_type1;

    DECLARE variable2 data_type2;

    -- 执行计算或操作

    SET variable1 =...;

    SET variable2 =...;

    -- 返回结果

    RETURN result_expression;

END;

• 例如，创建一个计算两个整数之和的函数：

收起

sql

CREATE FUNCTION add_numbers(@num1 INT, @num2 INT)

RETURNS INT

AS

BEGIN

    DECLARE @result INT;

    SET @result = @num1 + @num2;

    RETURN @result;

END;

1. 调用函数
   • 可以在查询中直接调用函数，将函数的返回值作为查询结果的一部分。例如，调用上述add_numbers函数计算 5 和 3 的和：SELECT add_numbers(5, 3);
   • 也可以在其他 SQL 语句中使用函数，如在SELECT语句的列表达式中使用函数。例如，查询学生表中每个学生的年龄加上 5 后的新年龄：SELECT sname, add_numbers(sage, 5) AS new_age FROM student;

二、过程

1. 概念
   • 过程也是一段可重复使用的 SQL 代码，但它与函数不同，过程主要用于执行一系列操作，不返回值或可以通过输出参数返回多个值。过程在数据库中常用于处理复杂的业务逻辑，如数据的批量处理、业务规则的实现等。
   • 例如，在一个电商系统中，处理订单的流程可能包括检查库存、更新订单状态、计算总价等一系列操作，这些操作可以封装在一个存储过程中，方便统一管理和调用。
2. 定义过程
   • 语法：

收起

sql

CREATE PROCEDURE procedure_name

    [@parameter1 data_type1 [= default_value1] OUTPUT, @parameter2 data_type2 [= default_value2] OUTPUT,...]

AS

BEGIN

    -- 过程体，包含变量声明、逻辑处理、事务控制等

    DECLARE variable1 data_type1;

    DECLARE variable2 data_type2;

    -- 执行一系列操作

    SET variable1 =...;

    SET variable2 =...;

    -- 如果有输出参数，设置输出参数的值

    SET @parameter1 = value1;

    SET @parameter2 = value2;

END;

• 例如，创建一个存储过程来更新学生的成绩：

收起

sql

CREATE PROCEDURE update_grade

    @sno CHAR(5),

    @cno CHAR(2),

    @new_grade INT

AS

BEGIN

    UPDATE sc SET grade = @new_grade WHERE sno = @sno AND cno = @cno;

END;

1. 调用过程
   • 使用EXEC或EXECUTE关键字来调用过程。例如，调用上述update_grade存储过程来更新学号为 '95001'、课程号为 '01' 的学生成绩为 90：EXEC update_grade '95001', '01', 90;
   • 如果过程有输出参数，需要在调用时声明变量来接收输出值。例如，创建一个存储过程来获取学生的平均成绩并通过输出参数返回：

收起

sql

CREATE PROCEDURE get_average_grade

    @sno CHAR(5),

    @average_grade DECIMAL(5, 2) OUTPUT

AS

BEGIN

    SELECT @average_grade = AVG(grade) FROM sc WHERE sno = @sno;

END;

调用时：

收起

sql

DECLARE @avg_grade DECIMAL(5, 2);

EXEC get_average_grade '95001', @avg_grade OUTPUT;

SELECT @avg_grade;

三、触发器

1. 概念
   • 触发器是一种特殊的存储过程，它与表相关联，当对表执行 INSERT、UPDATE 或 DELETE 操作时自动触发执行。触发器主要用于实现数据的完整性约束、审计等功能，确保数据在插入、更新或删除时满足特定的业务规则。
   • 例如，在一个员工工资管理系统中，当更新员工工资时，可以使用触发器来检查新工资是否在合理范围内，或者记录工资更新的历史信息。
2. 定义触发器
   • 语法：

收起

sql

CREATE TRIGGER trigger_name

ON table_name

[FOR|AFTER|INSTEAD OF] [INSERT, UPDATE, DELETE]

AS

BEGIN

    -- 触发器执行的SQL语句序列

    -- 可以使用两个临时表：inserted（插入或更新后的数据）和deleted（删除前的数据）

    IF EXISTS (SELECT * FROM inserted WHERE... )

    BEGIN

        -- 执行相关操作，如检查数据、更新其他表等

        PRINT '满足某种条件，执行操作...';

        ROLLBACK TRANSACTION; -- 如果不满足条件可以回滚事务

    END

END;

• 例如，创建一个在学生表插入数据后更新相关统计信息的触发器（假设存在一个统计学生总数的表student_count）：

收起

sql

CREATE TRIGGER update_student_count

ON student

AFTER INSERT

AS

BEGIN

    UPDATE student_count SET total_students = total_students + 1;

END;

• 再例如，创建一个防止学生表中性别输入错误的触发器：

收起

sql

CREATE TRIGGER check_gender

ON student

AFTER INSERT, UPDATE

AS

IF EXISTS (SELECT * FROM inserted WHERE ssex NOT IN ('男', '女'))

BEGIN

    PRINT '性别只能输入男或女';

    ROLLBACK TRANSACTION;

END;

1. 注意事项
   • 一个表可以建立多个AFTER INSERT、AFTER DELETE、AFTER UPDATE触发器，它们会按照创建的顺序依次执行。
   • INSTEAD OF触发器比较特殊，它会替代原有的操作（INSERT、UPDATE、DELETE）执行，并且一个表或视图只能建立一个INSTEAD OF INSERT、INSTEAD OF DELETE、INSTEAD OF UPDATE触发器。
   • 在触发器中可以使用inserted和deleted临时表来获取操作前后的数据，以便进行数据检查和处理。例如，在UPDATE触发器中，deleted表中保存更新前的数据，inserted表中保存更新后的新数据。

综合题示例

1. 定义函数计算学生的平均成绩

收起

sql

CREATE FUNCTION calculate_average_grade(@sno CHAR(5))

RETURNS DECIMAL(5, 2)

AS

BEGIN

    DECLARE @avg_grade DECIMAL(5, 2);

    SELECT @avg_grade = AVG(grade) FROM sc WHERE sno = @sno;

    RETURN @avg_grade;

END;

1. 定义过程根据学生学号更新其某门课程的成绩，并返回更新后的平均成绩

收起

sql

CREATE PROCEDURE update_grade_and_get_average

    @sno CHAR(5),

    @cno CHAR(2),

    @new_grade INT,

    @updated_average DECIMAL(5, 2) OUTPUT

AS

BEGIN

    UPDATE sc SET grade = @new_grade WHERE sno = @sno AND cno = @cno;

    SET @updated_average = dbo.calculate_average_grade(@sno);

END;

1. 定义触发器在更新学生成绩时检查成绩是否在合理范围内（0 - 100），如果不在则回滚更新操作

收起

sql

CREATE TRIGGER check_grade_range

ON sc

AFTER UPDATE

AS

IF EXISTS (SELECT * FROM inserted WHERE grade < 0 OR grade > 100)

BEGIN

    PRINT '成绩必须在0到100之间，请重新输入！';

    ROLLBACK TRANSACTION;

END;

第 6 章 关系代数语言

1. Cartesian product（笛卡尔积）
   • 设关系有个属性，个元组；关系有个属性，个元组，则含有个属性，个元组。例如，关系和，有元组，；有元组，，则的结果为，，，。
2. Selection（选择）
   • 从关系中选择满足条件的元组，记为，其中为选择条件。例如，从学生表中选择年龄大于 18 岁的学生：。
3. Projection（投影）
   • 从关系中选择若干属性组成新的关系，并去掉重复元组，记为，其中为要选择的属性集。例如，从学生表中选择姓名和年龄两列：。
4. Join（连接）
   • 条件连接（）：将两关系按一定条件连接成一个新关系，两关系可以没有公共属性，若有公共属性，则新关系含有重复属性。例如，关系和，连接条件为，则连接结果为满足该条件的元组组合。
   • 自然连接（）：将两关系按公共属性连接成一个新关系，并去掉重复属性，两关系至少有一个公共属性。例如，关系和，自然连接后会根据公共属性和进行连接，并去除重复的和列。
5. Rename（重命名）：用于给关系或属性重新命名，以便在复杂的关系代数表达式中更好地表示和理解。例如，将关系重命名为：。
6. Union（并）
   • 设关系和的结构完全相同，则由属于或属于的元组组成。例如，关系有元组，；关系有元组，，则的结果为，，。
7. Set - difference（差）
   • 设关系和的结构完全相同，则由属于而不属于的元组组成。例如，对于上述和，的结果为。
8. Intersection（交）
   • 设关系和的结构完全相同，则由既属于又属于的元组组成。例如，对于上述和，的结果为。

第 7 章 数据库设计和 ER 模型

• 基本 ER 模型的概念与 ER 图的设计
  • 实体（Entity）：客观存在并可相互区别的事物，用矩形表示。
  • 属性（Attribute）：实体所具有的某一特性，用椭圆形表示，与实体相连。
  • 联系（Relationship）：实体之间的相互关系，用菱形表示，与相关实体相连，菱形内注明联系的名称，联系的类型可以是一对一（1:1）、一对多（1:n）或多对多（m:n）。
• 将 ER 模型转换成关系模式的方法
  • 实体型转换为关系模式：将每个实体型转换为一个关系模式，实体的属性就是关系模式的属性，实体的码就是关系模式的主键。
  • 联系的转换
    • 1:1 联系：可以将联系合并到任意一个相关联的实体型对应的关系模式中，也可以单独创建一个关系模式，关系模式的属性包括两个实体型的主键和联系本身的属性，主键可以任选一个实体型的主键。
    • 1:n 联系：将联系合并到 n 方实体型对应的关系模式中，在该关系模式中添加 1 方实体型的主键作为外键。
    • m:n 联系：必须转换为一个独立的关系模式，关系模式的属性包括相关联的各实体型的主键和联系本身的属性，主键为各实体型主键的组合。

第 8 章 关系数据库设计

• 1NF 概念：若关系 R 的所有属性不能再分，则。
• 非规范化设计的问题
  • 数据冗余：数据在多个地方重复存储，浪费存储空间，可能导致数据不一致。
  • 插入异常：例如在插入数据时，由于某些属性不能为空，而相关数据尚未存在，导致无法插入。
  • 删除异常：删除某条记录时，可能会意外删除其他相关信息。
  • 更新异常：更新数据时，可能需要在多个地方同时更新，否则会导致数据不一致。
• 函数依赖的概念：任给，U 为属性集，x、y 为 U 的子集，如果对于 x 的每个值，y 有唯一确定的值与之对应，则称 x 决定 y，或 y 函数依赖于 x，记为。
• 平凡函数依赖与非平凡函数依赖
  • 平凡函数依赖：如果是的子集，则该依赖是平凡的，如。
  • 非平凡函数依赖：如果中至少有一个属性不在中，则该依赖是非平凡的，如。
• 函数依赖集：关系 R 中所有函数依赖的集合。
• 最小（正则）覆盖：一个函数依赖集的最小覆盖是指该函数依赖集的一个等价子集，满足每个函数依赖的右边都是单个属性，且没有多余的函数依赖。
• 函数依赖集的闭包：由给定函数依赖集 F 所能推出的所有函数依赖的集合，记为。
• 属性集的闭包：设为属性集上的一组函数依赖，，则关于的闭包是指由所能推出的所有属性的集合。
• 无损连接分解（或称无损分解，lossless decomposition）：将关系模式分解为多个关系模式，如果对于的任何一个可能的关系实例，都有成立，则称这种分解是无损连接分解。
• 依赖保持分解（function dependency preserving）：设是关系模式上的函数依赖集，是的一个分解，如果，则称分解是保持函数依赖的分解。
• BCNF 的定义、判断和分解
  • 定义：关系模式，若对于中的每一个函数依赖（），都包含了的一个候选键，则称属于 BCNF。
  • 判断方法：逐一检查关系模式中的函数依赖，看其是否满足 BCNF 的定义。
  • 分解方法：如果关系模式不满足 BCNF，可以将其分解为多个满足 BCNF 的子关系模式，分解过程要确保是无损连接分解且保持函数依赖（如果可能的话）。
• 3NF 的定义、判断和分解
  • 定义：若关系，且它的每个非主属性都不传递依赖于主码，则称。
  • 判断方法：先判断关系是否属于 2NF，然后检查非主属性是否存在传递依赖于主码的情况。
  • 分解方法：如果关系模式不满足 3NF，可以将其分解为多个满足 3NF 的子关系模式，分解过程要确保是无损连接分解且保持函数依赖（如果可能的话）

第 13 章 事务

1. 事务的概念与特性（ACID）
   • 事务是用户定义的一组操作序列，这些操作要么都执行，要么都不执行，是并发控制的基本单位。
   • 原子性（Atomicity）：事务中的操作要么全部执行，要么全部不执行。
   • 一致性（Consistency）：事务必须使数据库从一个一致性状态变到另一个一致性状态。
   • 隔离性（Isolation）：一个事务的执行不能被其他事务干扰。
   • 持久性（Durability）：一旦事务提交，其对数据库的修改将永久保存。
2. 事务的状态图
   • 事务在执行过程中可能处于活动（active）、部分提交（partially committed）、失败（failed）、中止（aborted）、提交（committed）等状态。事务从活动状态开始，正常执行完所有操作后进入部分提交状态，若此时系统正常则进入提交状态，若系统故障则进入失败状态并回滚到中止状态；在部分提交状态若发生故障也会进入失败状态并回滚。
3. 事务调度（schedule）的概念
   • 事务调度是指多个事务在并发执行时的执行顺序安排。例如，假设有事务 T1 和 T2，它们包含一系列操作，事务调度确定了这些操作在时间上的执行先后顺序，比如先执行 T1 的某些操作，再执行 T2 的某些操作，然后又执行 T1 的其他操作等。
4. 可恢复调度 / 级连回滚的概念
   • 可恢复调度：如果一个调度中，当事务 Tj 读取了事务 Ti 修改过的数据后，Ti 必须在 Tj 提交之前提交，那么这个调度就是可恢复的。例如，事务 T1 修改了数据 X 并写入数据库，事务 T2 读取了 X，那么只有在 T1 提交之后 T2 提交，该调度才是可恢复的，否则若 T1 回滚而 T2 提交，就会出现数据不一致。
   • 级连回滚：如果在一个调度中，由于一个事务的失败导致一系列事务都需要回滚，这种现象称为级连回滚。比如事务 T1 失败，而事务 T2 依赖于 T1 的结果，T3 又依赖于 T2 的结果，那么 T1 的失败可能导致 T2、T3 都需要回滚。
5. 串行调度 / 并发调度
   • 串行调度：多个事务依次执行，一个事务执行完后再执行下一个事务。例如，有事务 T1、T2、T3，串行调度可能是 T1 执行完所有操作后，再执行 T2 的所有操作，最后执行 T3 的所有操作。这种调度方式可以保证事务的隔离性，但效率较低。
   • 并发调度：多个事务在时间上重叠执行，以提高系统的效率。比如 T1 在执行某些操作的同时，T2 也在执行其部分操作，然后 T1 和 T2 交替执行各自剩余的操作。但并发调度可能会出现数据不一致等问题，需要通过并发控制机制来解决。
6. 冲突等价 / 冲突可串行化 / 冲突可串行化的判断
   • 冲突等价：如果两个调度在不考虑事务执行顺序的情况下，对数据库中数据项的读写操作顺序相同，那么这两个调度是冲突等价的。例如，调度 S1 和 S2 中，对于相同的数据项，读写操作的先后顺序一致（不考虑事务内部操作顺序），则 S1 和 S2 冲突等价。
   • 冲突可串行化：如果一个调度与某个串行调度冲突等价，那么这个调度是冲突可串行化的。判断一个调度是否冲突可串行化可以通过构造优先图来分析，如果优先图中不存在环，则该调度是冲突可串行化的，反之则不是。例如，对于一个包含事务 T1、T2、T3 的调度，通过分析它们对数据项的读写操作构建优先图，若图中无环，则该调度冲突可串行化。

第 14 章 并发控制与恢复

1. 锁 / 共享锁 / 排它锁的概念
   • 锁是一种并发控制机制，用于控制多个事务对数据对象的并发访问。
   • 共享锁（S 锁）：事务 T 对数据 A 加 S 锁，其他事务只能再对 A 加 S 锁，即其他事务只能读 A，不能修改 A。例如，多个事务可以同时对同一数据对象加共享锁以进行并发读操作。
   • 排它锁（X 锁）：事务 T 对数据 A 加 X 锁，其他事务不能再对 A 加锁，即其他事务不能读取和修改 A。比如，当一个事务对数据进行写操作时，会加排它锁，防止其他事务同时读写该数据。
2. 多粒度锁
   • 封锁对象可以是属性列、元组、关系、整个数据库等，封锁对象的大小称为封锁粒度。封锁粒度越小，并发度越高，但并发控制的开销越大。例如，在一个数据库应用中，如果对单个数据项加锁（小粒度），可以提高并发度，但需要管理更多的锁，开销较大；而如果对整个表加锁（大粒度），并发度可能降低，但锁管理开销较小。
3. 两阶段封锁协议
   • 事务在修改数据 A 之前，必须对其加 X 锁，直到事务结束才释放；事务在读取数据 A 之前，必须对其加 S 锁，直到事务结束才释放。遵循该协议可以解决丢失修改、不可重复读、读 “脏” 数据等数据不一致性问题。例如，一个事务在更新数据时，先获取排它锁，在整个更新过程中一直持有该锁，直到事务提交或回滚才释放，这样可以保证数据的一致性和完整性。
4. 日志的概念
   • 日志用来记录对数据库对象的所有更新操作，包括事务开始、事务提交、数据修改等信息。日志在数据库恢复中起着关键作用，通过日志可以重现事务的执行过程，以便在系统故障后恢复数据库到一致性状态。例如，当数据库系统崩溃后，可以根据日志中的记录，将数据库恢复到崩溃前的某个一致状态。
5. Checkpoint 概念
   • Checkpoint 是数据库系统中的一个操作点，在该点之前的所有更新操作都已经持久化到磁盘上。在恢复过程中，可以从最近的 Checkpoint 开始，减少恢复的工作量。例如，系统定期设置 Checkpoint，将内存中的数据页和日志记录写入磁盘，之后发生故障时，从最近的 Checkpoint 开始恢复，而不需要从最早的事务开始。
6. 立即的数据库更新
   • 立即的数据库更新是指事务对数据库的修改操作在事务执行过程中立即写入磁盘，而不是等到事务提交时才写入。这种方式可以提高数据的实时性和持久性，但可能会增加系统的 I/O 开销。例如，在某些对数据实时性要求较高的应用中，如银行交易系统，可能会采用立即的数据库更新方式，确保数据的及时更新和持久保存。
7. 恢复策略（redo/undo 哪些事务）
   • Redo 操作：用于重新执行已经提交但在故障发生时尚未写入磁盘的事务操作，以确保这些事务的更新能够持久化到数据库中。例如，在系统故障恢复时，如果发现某些事务已经提交但数据修改还未完全写入磁盘，就需要根据日志进行 Redo 操作。
   • Undo 操作：用于撤销未完成（未提交）事务对数据库的修改，使数据库恢复到事务开始前的状态。比如，在事务执行过程中发生故障，未提交的事务需要通过 Undo 操作回滚，以保证数据的一致性。恢复策略会根据日志记录和事务的状态（已提交、未提交等）来确定哪些事务需要进行 Redo 或 Undo 操作，从而将数据库恢复到一致性状态。