第1章 绪论
1.1术语
1.数据（Data）
数据是数据库中存储的基本对象。界说为描述事务的符号记录。数据的寄义称为语义，数据与其语义是不可分的。
2.数据库（DataBase，DB）
恒久存储在盘算机内，有构造的，可共享的大量数据的聚集。
数据库基本特性：1.永世存储 2.有构造 3.可共享 4.冗余度小 5.易扩展
3.数据库管理体系（DataBase Management System,DBMS）（core）
位于用户与操作体系之间的一层数据管理软件
数据库管理体系和操作体系一样是盘算机的底子软件。
主要功能：提供数据界说语言（DDL）、数据利用语言（DML）等
4.数据库体系（DataBase System,DBS）
数据库体系由数据库（DB），数据库管理体系(DBMS)，应用程序和数据库管理员(DBA)组成的存储、管理、处置惩罚和维护数据的体系。
1.2重要概念
数据库管理技术的发展过程（三个阶段）
1.人工管理阶段：
在盘算机出现之前，人们运用通例的本领从事记录、存储和对数据加工，也就是利用纸张来记录和利用盘算工具（算盘、盘算尺）来进行盘算，并主要利用人的大脑来管理和利用这些数据。
特点:
(1)盘算机体系不提供对用户数据的管理功能；
(2)数据不能共享；
(3)不保存数据。
2.文件体系阶段：
在这一阶段（20世纪50年代后期至60年代中期）盘算机不光用于科学盘算，还利用在信息管理方面。随着数据量的增加，数据的存储、检索和维护标题成为紧迫的需要，数据结构和数据管理技术敏捷发展起来。此时，外部存储器已有磁盘、磁鼓等直接存取的存储设备。软件领域出现了操作体系和高级软件。操作体系中的文件体系是专门管理外存的数据管理软件，文件是操作体系管理的重要资源之一。
特点:
数据以“文件”形式可恒久保存在外部存储器的磁盘上。由于盘算机的应用转向信息管理，因此对文件要进行大量的查询、修改和插入等操作，这些操作由文件体系提供。
缺点:
数据冗余;不同等性;数据独立性差。
3.数据库体系阶段：
20世纪60年代后期以来 ，盘算机性能得到进一步提高，更重要的是出现了大容量磁盘，存储容量大大增加且代价降落。在此底子上，才有可能降服文件 体系管理数据时的不敷，而满足和办理现实应用中多个用户、多个 应用程序共享数据的要求，从而使数据能为尽可能多的 应用程序服务，这就出现了数据库这样的数据管理技术。
特点:
（1）数据结构化。
（2）数据共享性高、冗余少且易扩充。
（3）数据独立性高。
（4）数据由DBMS统一管理和控制。
1.3 数据库体系结构
1.3.1 外部架构（External Architecture）：
- 单用户结构、主从结构、分布式结构、客户/服务器结构。
- 优点：提高了体系性能和服从，减少了通过网络传输的数据量。
外部架构涉及了数据库体系与用户或应用程序之间的交互。以下是四种主要的外部架构范例：
1.3.1.1 单用户结构（Single-user Structure）
- 描述：这种结构指一个数据库体系只支持一个用户访问。通常用于简单的桌面数据库应用，如Microsoft Access
- 优点：实现简单，无需处置惩罚多用户并发标题
- 缺点：无法支持多用户同时访问，不适用于大型或企业级应用
1.3.1.2 主从结构（Master-slave Structure）
- 描述：主从结构包括一个主数据库和多个从数据库。主数据库接受所有的写操作，并将更新传播到从数据库。从数据库主要用于读操作
- 优点：通过分离读写操作，提高了体系的性能和可扩展性
- 缺点：需要额外的管理和配置以确保主从数据库之间的数据同等性
1.3.1.3 分布式结构（Distributed Structure）
- 描述：分布式结构将数据库分布在多个物理位置，这些位置通过网络连接。每个节点可以独立处置惩罚查询并到场全局事务
- 优点：提高了体系的可靠性和可用性，数据可以分布在不同地理位置以提高访问速率和容错能力
- 缺点：实现和管理复杂，需要处置惩罚数据同步、分布式事务和网络耽误等标题
1.3.1.4 客户/服务器结构（Client-server Structure）
- 描述：在客户/服务器结构中，客户端负责用户接口和应用逻辑，服务器负责数据存储和管理。客户端向服务器发送查询，服务器执行查询并返回效果
- 优点：分离了数据管理和应用逻辑，提升了体系的性能和扩展性。减少了网络数据传输量，因为只传输查询效果而不是全部数据
- 缺点：需要网络支持，可能存在网络耽误标题
1.3.2 内部架构（Internal Architecture）：
内部架构主要关注数据库的模式和实例两个焦点概念：
模式与实例（Schema and Instance）
1.模式（Schema）
- 描述数据结构的界说（Type），比方：学生的名字、性别、出生年份、籍贯、系别、入学时间。
- 模式是数据库筹划过程中的一部分，并且不经常变化。
- 模式是数据库的描述或结构的描述。
- 模式代表数据库的内涵（Intension）。
2.实例（Instance）
- 实例是现实存储在数据库中的数据（Value），比方：李明，男，2003年，浙江，盘算机科学，2023年入学。
- 实例是数据库在某一时刻的数据，可能经常变化。
- 实例是数据库的外延（Extension）。
- 同一个数据库模式可以对应多个不同的数据库实例。
总结
模式和实例分别代表了数据库体系中的两个条理：模式是对数据库结构的静态描述，是固定的筹划蓝图；而实例则是对数据库内容的动态描述，是详细的数据。模式界说了数据的范例和格式，而实例则是符合这些范例和格式的详细数据。理解这两个概念，有助于更好地筹划和管理数据库体系。
1.3.3 三层体系结构（Three-level Architecture）
- 外部层（External Level）：由多个用户视图组成，每个视图包罗用户相关的数据库部分，不同视图对同一数据可能有不同表示。
- 概念层（Conceptual Level）：包罗数据库的逻辑表示，支持各个用户视图，界说了所有实体、属性及其关系、数据约束、安全性和完备性信息。
- 内部层（Internal Level）：包罗数据库的物理表示，管理数据的存储方式和空间分配等。
详细表明:
1.外部层（External Level）
- 外部层由多个用户视图（external schema外模式）组成。
- 每个用户视图包罗与该用户相关的数据库部分。这意味着每个用户只能看到他们需要的数据，而看不到其他不相关的数据。
- 用户不知道他们视图之外的数据存在。这种隔离确保了数据的安全性和隐私性。
- 不同的视图可以对相同的数据有不同的表示方式。也就是说，不同用户可以根据本身的需求看到不同形式的同一数据。
2.概念层（Conceptual Level）
- 概念层是数据库的焦点和关键（Core and key）。
- 它界说了数据库的整体结构和约束条件，而不涉及详细的物理存储细节。
- 概念层模式（conceptual schema）统一了外部层的多个视图，并与内部层的存储细节相连接。
3.内部层（Internal Level）
- 内部层界说了数据在数据库内部的物理存储方式。
- 它包括内部模式（internal schema），描述了数据的存储路径和访问方法。
- 内部层是现实的数据存储层，负责详细的物理数据构造。
总结
数据库的三层体系结构通过将数据的外部视图、概念表示和内部存储分离，提供了高度的抽象和独立性。这种结构使得数据库体系能够更好地管理和控制数据，确保数据的同等性、安全性和高效的访问。外部层通过多个用户视图为不同的用户提供了个性化的数据访问方式，概念层作为中间层连接外部层和内部层，统一管理数据结构，而内部层则处置惩罚详细的数据存储和访问细节。理解这一结构有助于筹划更高效和安全的数据库体系。
1.3.4 映像（Mapping）
在数据库体系结构中，映像是指不同条理之间的对应关系：
1.外模式/概念模式映像（External/Conceptual Mapping）
- 这种映像界说了用户视图（外模式）与概念模式之间的对应关系，使得用户能够以本身的视角访问和操作数据 。
2.概念模式/内模式映像（Conceptual/Logical Mapping）
- 这种映像界说了概念模式与内部模式之间的对应关系，确保逻辑数据结构与物理存储结构之间的兼容性。
1.3.5 数据独立性（Data Independence）
数据独立性是指当数据库的某一条理发生变化时，不需要对其他条理进行相应的修改。数据独立性分为两种：
逻辑数据独立性（Logical Data Independence）
界说：外模式不受概念模式变化的影响。这意味着，当我们在概念模式中添加或删除某些属性时，不需要修改用户的视图或应用程序。
重要性：逻辑数据独立性使得数据库体系更具机动性和扩展性，因为我们可以在不影响用户的情况下修改数据库的逻辑结构。
实现：通过外部/概念映像来实现，即将用户视图映射到概念模式上。
物理数据独立性（Physical Data Independence）
界说：概念模式不受内部模式变化的影响。这意味着，当我们改变数据的物理存储方式时，不需要修改数据库的逻辑结构或用户视图。
重要性：物理数据独立性提高了体系的服从和性能，因为可以在不影响逻辑结构和用户视图的情况下优化数据存储和访问方法。
实现：通过概念/内部映像来实现，即将概念模式映射到内部模式上。
第二章 关系模型
2.1 数据模型概述
2.1.1 数据模型的界说
—— 数据模型是对现实天下数据的抽象。
- 用于描述数据、构造数据和操作数据
- 模拟现实天下
- 所有当前的数据库体系（DBS）都是基于某种范例的数据模型
- 数据模型是数据库体系的焦点和底子
2.1.2 数据模型的要求
- 能够模拟现实天下的现实性
- 易于理解
- 能够在盘算机上实现
2.1.3 数据模型的三要素
- 数据结构：描述数据库中数据对象的结构及其相互关系
- 数据操作：一组操作，用于处置惩罚数据库中的所有数据对象
- 完备性约束：一组规则，确保数据结构和数据操作的正确性，以保证数据的同等性和准确性
2.2 关系模型
2.2.1 关系模型的起源与功能
- 关系模型是从表格及表格处置惩罚方法中抽象出来的。
- 它是三种经典数据模型之一 （条理模型、网状模型和关系模型）。
- SQL 是基于关系模型提出和开发的。
- 它是许多数据库理论的底子。
2.2.2 关系模型的组成部分
关系就是一个表格。
关系模型用于处置惩罚表格，由三个部分组成：
- 数据结构：表（Table）。
- 数据操作：关系代数和关系演算。
- 数据约束：完备性约束。
2.2.3 关系模型的基本概念
- 关系（Relation）/ 表格（Table）：一个关系对应通常说的一张表。
- 属性（Attribute）/ 列（Column）/字段（Field）：表中的一列即为一个属性。列可以存储的数据范例。
- 模式（Schema）：模式是表格结构的界说，包括表格的名称、每列的名称和数据范例等。模式决定了表格的结构和约束条件。
- 元组（Tuple）/ 行（Row）：表中的一行即为一个元组。每行中的数据按照列的界说进行存储。
- 码（key）：也称码键。表中的某个属性组，它可以唯一确定一个元组。
- 域（domain）：列/属性值的范围。
- 域是一组值的聚集，所有值具有相同的数据范例。
- 比方：一组整数、一组字符字符串、一组所有学生。
- 聚集中元素的数量称为基数（Cardinality）。
比方，D1 中有两个元素，所以它的基数是2
- 域（Domain）界说了表中某一列可以接受的数据范例和范围。比方，学生年事的域可以界说为0到150之间的整数。域确保了数据的同等性和正确性，避免错误的数据范例输入。基数表示域中可能的值的数量，有助于理解数据的规模和范围。
2.2.4 笛卡尔积与关系
2.2.4.1 笛卡尔积
- 笛卡尔积（Cartesian Product）是域上面的一种聚集运算，是将多个域组合起来生成所有可能的值组合。
- 给定域聚集 D1, D2, ......, Dn，其笛卡尔积表示为：
- 笛卡尔积的元素
称为n-元组（n-tuple）。
- 元组
中的每个
称为一个组件（component）。
- 如果域
的基数是
，则笛卡尔积的基数，即元组的数量，是
2.2.4.2 关系的界说
- 正式界说：给定域的聚集D1, D2, ..., Dn，一个关系 r 是D1
D2
...
Dn的一个子集，因此一个关系是
-元组 (d1, d2, ..., dn) 的聚集，此中每个di
Dn (d1 = 1, 2, ..., n)。
- 并不是笛卡尔积中的所有元组都是有意义的，所以只有那些有意义的元组被用来形成关系。
- 关系的不同列可能来自相同的域，所以有必要给每列赋予不同的名称，我们称之为列名/属性名。
2.2.4.3 关系的表示方法
- 关系表示为R(A1
 1, A22, ..., Ann) ，或简要表示为R(A1, A2, ..., An) ，我们也称这种关系为关系模式（Relation Schema）。
- R是关系的名称，Ai是属性，Di是属性的域，n是关系的度（degree），关系中的元组数称为关系的基数（Cardinality of Relation）。
- 度是关系中属性的数量。比方，家庭关系有三个属性：丈夫、妻子和孩子，所以它的度是3。
- 基数是关系中的元组数量。比方，如果一个家庭关系有3个家庭记录，那么它的基数是3。
- 在关系模式中，每个属性 (Ai) 必须是独特的，但是不同的属性可以来自相同的域。比方，多个属性可以都是名字，但它们代表不同的角色，如丈夫和妻子。
2.2.4.4 关系的例子
在许多DBMS中，关系模式 R(A11, A22, ..., Ann) 中附属性到域的映射通常描述为属性的范例和长度。
属性范例和长度：
在现实数据库中，属性的界说不光包括名称，还包括数据范例和长度。比方，学生表的学号（Sno）可以界说为字符型（char）且长度为10。
例子：
- 学生表：Student(Sno char(10), Sname char(20), Gender char(2), Age integer, Dno char(2), Class char(8))
- 课程表：Course(Cno char(3), Cname char(30), Hours integer, Credit float(1), Tno char(3))
- 效果表：SC(Sno char(10), Cno char(3), Score float(1))
这些界说展示了怎样在关系模式中描述属性及其数据范例和长度。
2.2.4.5 关系（关系实例）和关系模式
- 一个关系模式可以有许多不同的关系（关系实例）。
- 关系模式是关系的结构，关系（关系实例）是在特定时间点的数据关系模式的快照。
- 关系模式通常是稳定的，但关系会随着时间的推移而变化。
例子：
- 学生表的关系模式：Student(Sno char(10), Sname char(20), Gender char(2), Age integer, Dno char(2), Class char(8))
- 在特定时间点，这张表格可能包罗不同的学生记录，如表中展示的详细数据。
2.3 关系的特性
1.同质性（Homogeneity）：
列/属性的同质性：每列的所有值来自同一域，具有相同的数据范例。
2.属性（列）的序次：
在关系中，列的序次可有可无。
3.元组（行）的序次：
在关系中，元组的序次可有可无。
4.元组的唯一性：
每个元组/行必须是唯一的，即关系中不允许有两个完全相同的元组。
5.属性的原子性：
属性值是不可再分的，这一特性也称为第一范式（1NF）。
2.4 键的范例
2.4.1 候选键（Candidate Key）
- 候选键的界说：候选键是关系中可以唯一标识一个元组的属性或属性组合。比方，学生表中的学号（Sno）可以唯一标识每个学生，因此是一个候选键。
- 多个候选键：一个关系可能有多个候选键。比方，学生表除了学号外，可能另有身份证号作为候选键，因为它们都能唯一标识学生。
2.4.2 主键与超键（Primary key & Super key）
- 主键的界说：主键是从候选键中选出的，用于唯一标识关系中的每个元组。比方，学生表中的 Sno 可以被选为主键。
- 超键的界说：超键是一个包罗候选键的属性聚集。比方，学生表中的 Sno 是候选键，那么 Sno + Sname 就是一个超键。
- 例子表明：
- 在学生表中，Sno 是主键，而 Sno + Sname 是超键。
- 在员工表中，EmpID 是主键，而 EmpID + Mobile 是超键。
2.4.3 单键/全键与主属性/非主属性（Single key / All key & Primary attribute / Non-primary attribute）
- 单键（Single Key）：只有一个属性的候选键。
- 全键（ALL Key）：关系中所有属性的聚集是候选键。
- 主属性（Primary attribute）：出现在任何候选键中的属性。
- 非主属性（Non-primary attribute）：除所有主属性外的其他属性。
1. 单键和全键：
- 单键（Single key）是只有一个属性的候选键，比方课程表中的Cno。
- 全键（All key）是指关系中所有属性的聚集，比方家庭表中的 Husband, Wife和 Children，所有这些属性一起构成一个候选键。
2. 主属性和非主属性：
- 主属性（Primary attribute）是指在任何候选键中出现的属性。比方，学生表中的 Sno 是一个主属性，因为它出现在候选键中。
- 非主属性（Non-primary attribute）是指除了主属性外的其他属性。比方，学生表中的 Sname, Gender, Age, Dno, Class, Address 都黑白主属性。
2.4.4 外键（Foreign Key）
- 外键（Foreign key）：一个关系R1中可能包罗一组属性，这些属性不是R1的候选键，而是另一个关系R2的候选键，这组属性称为R1的外键。外键显示了不同关系之间的关系。
- 引用关系（Referencing Relation）：R1
- 被引用关系（Referenced Relation）：R2
1. 外键的界说：
- 外键用于创建两个关系之间的连接。比方，学生表中的 Dno 不是学生表的候选键，但它是系表（Dept）的候选键。因此，Dno 是学生表的外键，表示学生属于某个系。
2. 引用关系和被引用关系：
- 引用关系是包罗外键的关系，比方学生表。
- 被引用关系是外键所引用的关系，比方系表。
2.4.5 键范例总结
- 候选键：可以唯一标识关系中元组的属性或属性组合。
- 主键：从候选键中选出的用于唯一标识关系中元组的主要键。
- 超键：包罗候选键的属性聚集。
- 单键：只有一个属性的候选键。
- 全键：关系中的所有属性聚集。
- 主属性：出现在任何候选键中的属性。
- 非主属性：除了主属性外的其他属性。
- 外键：用于创建不同关系之间连接的属性。
2.5 完备性约束
2.5.1 完备性约束的组成
关系模型用于处置惩罚表格，由三个部分组成：
1.数据结构（表格）
2.数据操作（关系代数和关系演算）
3.数据约束（完备性约束）
2.5.2 实体完备性
实体完备性：候选键的属性值不允许为空（NULL）。
实体完备性规则规定，每个表的候选键中的所有值都不能为空。这确保了每一行记录在候选键上的唯一性和可辨识性。
NULL值：表示未知值或不存在的值，通常用于表示数据缺失或不可用。
NULL的影响：聚合、算术表达式、比较等。
- 在盘算和比较中，NULL会影响效果。比方，任何数与NULL相加，效果仍然是NULL。
- 在逻辑操作中，NULL的处置惩罚需要特别注意，以避免错误。
2.5.3 参照完备性
参照完备性：如果Fk是关系R1的外键，Pk是关系R2的候选键，那么Fk的值必须即是Pk的值或为空（NULL）。
参照完备性确保一个表中的外键值必须在被引用表中存在，或为空。这维护了不同表之间的数据同等性。
- 学生表中的系编号（Dno）是外键，引用了系表中的系编号（Dno）。学生表中的每个系编号必须在系表中存在。
用户界说的完备性
2.5.4 用户界说的完备性
用户界说的完备性：根据不同的应用程序，用户可以界说的完备性约束。
用户界说的完备性是指根据特定应用需求，由用户界说的约束规则。这些规则可以保证数据符合特定业务逻辑。
- 学生表中的性别（Gender）必须是“M”或“F”。
- 年事（Age）必须在12到35岁之间。
2.6 本章术语回顾
- 域（Domain）：属性值的聚集。
- 笛卡尔积（Cartesian Product）：两个或多个聚集的组合。
- 元组（Tuple）：关系中的一行。
- 关系（Relation）：数据表。
- 关系实例（Relation Instance）：在特定时刻关系的详细数据。
- 关系模式（Relation Schema）：关系的结构界说。
- 属性（Attribute）：数据表中的列。
- 度（Degree）：关系中的属性数量。
- 基数（Cardinality）：关系中的元组数量。
- 候选键、主键、超键、外键：用于标识和引用关系中数据的键。
- 主属性/非主属性：候选键中的属性/其他属性。
- 实体完备性、参照完备性、用户界说的完备性约束：用于确保数据同等性和完备性的规则。
- NULL：表示未知或不存在的值。
- 模式图：展示数据库结构和表间关系的图。
第三章 形式化关系查询语言
3.1 查询语言 (Query Language)
查询语言是用户从数据库中请求信息的语言。
3.1.1 语言分类 (Categories of languages)
过程化语言 (procedural)：用户需要描述怎样获取数据。
非过程化语言 (non-procedural)：用户只需描述需要什么数据，而不需要描述怎样获取数据。
3.1.2 “纯”语言 (" ure" languages)
“纯”语言指只包罗数据操作元素的语言。
过程化语言 (Procedural)
- 关系代数 (Relational Algebra)
非过程化语言 (Non-procedural)
- 元组关系演算 (Tuple Relational Calculus)
- 域关系演算 (Domain Relational Calculus)
纯语言的作用
纯语言构建了查询语言的底子，查询语言是用户常用的语言。
3.2 关系代数 (Relational Algebra)
关系代数是一种过程化语言，提供了一系列的操作，包括：
- 并 (Union, ∪)：用于归并两个关系的所有元组。
- 交 (Intersection, ∩)：用于获取两个关系中共同的元组。
- 差 (Difference, -)：用于获取在第一个关系中但不在第二个关系中的元组。
- 笛卡尔积 (Cartesian Product, ×)：将两个关系的所有元组归并。
- 投影 (Project, π)：用于选择关系中的某些列。
- 选择 (Select, σ)：用于选择满足特定条件的元组。
- 连接 (Join, ⨝)：用于归并两个关系，根据某些条件匹配元组。
- 除 (Division, ÷)：用于查找在一个关系中但不在另一个关系中的元组。
关系代数的运算符操作基于聚集论，输入一个或多个关系，输出一个新的关系。这些操作是关系数据库管理体系（RDBMS）中SQL查询的底子。
3.2.1 关系代数概览
关系代数提供了一系列基于聚集的操作，包括并、交、差、笛卡尔积、投影、选择、连接和除。操作符接受一个或多个关系作为输入，并输出一个新的关系。
3.2.1.1 传统关系代数操作 (Traditional Relational Algebra Operations)
这些操作只涉及行：
暗号：R
S
寄义：返回两个关系中所有不重复的元组。
运算规则：所有在关系 R 或 S 中的元组。
暗号：R
S
寄义：返回两个关系中共有的元组。
运算规则：所有在关系 R 和 S 中的元组。
暗号：R - S
寄义：返回在关系 R 中但不在关系 S 中的元组。
运算规则：所有在关系 R 中但不在关系 S 中的元组。
- 笛卡尔积 (Cartesian Product, ×)：
暗号：R
S
寄义：返回关系 R 和 S 的所有可能组合。
运算规则：所有关系 R 的元组和关系 S 的元组的组合。
3.2.1.2 特别关系代数操作 (Special Relational Algebra Operations)
这些操作涉及行和列：
暗号：
寄义：从关系 R 中选择指定的列。
运算规则：返回关系 R 的某些列，并去重。
暗号：
寄义：关系 R 中选择满足条件 p 的元组。
运算规则：返回关系 R 中所有满足条件 p 的元组。
暗号：
寄义：将关系 R 和 S 中的元组基于某个条件连接。
运算规则：返回关系 R 和 S 的所有满足连接条件的组合。
暗号：R ÷ S
寄义：从关系 R 中找出在 S 中出现的所有元组。
运算规则：返回关系 R 中在 S 中出现的元组。
3.2.2 关系的相容性 (Compatibility for the Relational Algebra)
要保证并、交、差操作的有用性，到场操作的关系必须相容。
相容条件：
- 关系 ( R ) 和 ( S ) 必须有相同的元数（属性数量相同）
- 属性域必须兼容（比方，关系 ( R ) 的第二列与关系 ( S ) 的第二列处置惩罚相同范例的值 / 域）
3.3 关系代数（详细）：
3.3.1 并 (Relational Algebra (1): Union)
暗号：R
S
界说：( R
S =
)
要求：( R ) 和 ( S ) 必须相容。
性子：( R
S = S
R )
3.3.2 差、交同理
3.3.3 笛卡尔积 (Cartesian Product)
暗号：R
S
界说：( R
S = { t q | t
R
q
S } )
假设：假设 ( R ) 和 ( S ) 的属性不相交。
性子：( R
S = S
R )
- 若 ( R ) 的度为 ( n )，( S ) 的度为 ( m )，则 ( R
S ) 的度为 ( n + m )。
- 若 ( R ) 的基数为 ( n )，( S ) 的基数为 ( m )，则 ( R
S ) 的基数为 ( n
m )。
示例：
关系 ( R ) 和 ( S ) 的笛卡尔积效果展示了所有可能的组合。
3.3.4 选择 (Select)
暗号：
( p ) 被称为选择谓词。
界说：
=
此中 ( p ) 是命题演算中的公式，由 (
)（和）、(
)（或）、(
)（非）等连接词组成。
示例
关系 R：Student，选择年事小于20岁的学生：
3.3.4.1 操作符优先级
操作符的优先级序次如下：
- 括号：( )
- 比较运算符：=，≠，>，≥，<，≤
- 逻辑非：¬
- 逻辑与：∧
- 逻辑或：∨
3.3.5 投影 (Project)
暗号：
( A1, A2, ..., Ak ) 是属性名称，( R ) 是关系名称。
界说：
投影的效果是通过删除未列出的列而获得的具有 ( k ) 列的关系。
由于关系是聚集，因此效果中重复的行会被删除。
示例：
关系 R：Student
投影得到学生的名字和年事：
投影得到学生的名字和部门编号：
组合操作：先选择部门编号为“03”且年事大于19岁的学生，再投影得到学号和名字：
3.3.6 连接 (Join)
为什么利用连接 (Why use Join?)
- 连接操作用于归并两个关系，通过特定条件将相关联的元组归并。
- 示例查询：选择所有修读过“传感器网络”课程且效果超过90分的学生姓名。
暗号： R
S
界说： R
-
，此中
-
，此中
- t
R, s
S
- A 和 B 需要兼容
-
可以是比较运算符，如
θ-连接 通常与选择和投影一起利用。
连接操作示例：
示例 1
展示了关系R和S的笛卡尔积与连接的区别。
示例 2
选择学历不低于岗位要求的员工，找出所有员工的姓名及其可能竞聘职位的名称。
步骤 1：笛卡尔积
归并员工和职位两个关系。
步骤 2：选择
过滤出学历不低于岗位要求的员工。
步骤 3：投影
最终效果仅保留员工编号、姓名和职位名称。
3.3.7 更名 (Rename)
暗号：
界说：
将一个关系重命名为另一个不同的名称。
复制一个关系并赋予新名称。
，此中
和
关系名不同，但结构相同。
示例
选择既修读过“2015030101”又修读过“2015040101”的课程编号列表。
3.3.8 各类连接
3.3.8.1 等值连接（Equal-Join）
暗号：
界说：
等值连接是 θ-连接的一种特别情况，此中连接条件是相等比较（=）。
示例：
展示了关系 ( R ) 和 ( S ) 的等值连接：
3.3.8.2 自然连接（Natural-Join）
暗号：
界说：
自然连接要求 R 和 S 具有相同的属性，且在效果中删除重复的列。
自然连接是等值连接的一种特别情况，它不光检查相等条件，还自动去除重复的属性。
示例：
展示了关系 R 和 S 的自然连接：
3.3.8.3 外连接（Outer Join）
查询：列出所有西席的全部信息。
查询语句：
标题：只利用内连接时，编号为“003”的西席的信息丢失了。
为什么利用外连接
- 避免信息丢失：外连接是连接操作的一种扩展，避免信息丢失。
- 操作描述：盘算连接操作，并将另一关系中不匹配的元组添加到连接效果中。
- 利用空值 (null)：空值 (null) 表示值未知或不存在。
外连接范例
只返回两个关系中匹配的元组。
主要用于保留左表中的所有记录，纵然在右表中没有对应的匹配记录。
示例：列出所有西席的信息，纵然他们没有传授任何课程。
主要用于保留右表中的所有记录，纵然在左表中没有对应的匹配记录。
示例：列出所有课程的信息，纵然这些课程没有任何西席传授。
返回左右关系中的所有元组，岂论是否存在匹配。
示例：列出所有西席和所有课程的信息，岂论是否存在匹配。
3.3.9 除法（Division）
符号表示：
关系模式：
-
-
-
：模式 ( S ) 中的每一个属性也是模式 ( R ) 中的属性。
除法运算效果：
-
的效果是一个关系，其模式包罗所有在 R 中但不在 S 中的属性。
适用于包罗“对于所有...”的查询。
数学界说：
示例：通过除法找出选了全部课程的同砚的学号
3.4 元组关系演算 (2024不考)
3.4.1 界说与底子
元组关系演算公式：
关系：
元组关系演算基于谓词演算（Predicate Calculus），用于数理逻辑中的查询表达。
元组关系演算的界说
非过程化查询语言：
元组关系演算是一种非过程化查询语言，每个查询形式为
。
术语表明：
- t：元组变量。
- t[A]：元组 t 在属性 A 上的值。
- t ∈ R：元组 t 在关系 R 中。
- P：谓词演算中的公式。
3.4.2 谓词演算公式的构成
组成部分：
- 属性和常量的聚集。
- 比较操作符聚集（如 <, ≤, =, ≠, >, ≥）
- 连接词聚集，如 and (∧), or (∨), not (¬)
- 量词：
- 存在量词：( ∃ t ∈ R (P(t)) ) 表示存在一个元组 t 使得 P(t) 为真。
- 全称量词：( ∀ t ∈ R (P(t)) ) 表示对于所有元组 t，P(t) 都为真。
3.4.3 原子公式
元组在关系中：
属性值比较：
示例：( t[A] θ u[B] )
查询：找出所有不是最年轻的学生。
查询语句：
3.4.4 连接词
连接词的界说：
- 如果 F 是一个公式，则
也是一个公式。
- 如果
和
是公式，则
也是一个公式。
- 如果
和
是公式，则
也是一个公式。
连接词的利用：
查询示例：
查询1：找出所有年事小于20岁的男学生。
查询2：找出所有03号系年事小于20岁的男学生。
查询3：找出所有不在03号系的学生。
查询4：找出所有不属于年事小于20岁的男学生的学生。
3.4.5 量词
量词的界说：
如果 F 是一个公式，则 ( ∃ t ∈ R (F(t)) ) 也是一个公式。
如果 F 是一个公式，则 ( ∀ t ∈ R (F(t)) ) 也是一个公式。
量词的利用
查询示例：
查询1：找出所有不是最年轻的学生。
查询2：找出所有每门课效果都大于60分的学生。
查询3：找出所有就读于“盘算机科学”系的学生。
查询语句：
查询4：找到所有年事比“Sun Mei”小的学生。
查询5：找到选修了所有课程的学生。
查询6：找到每个学生的分数都大于60分的系信息。
3.4.6 运算符的优先级
优先级序次：
( ) 、 ¬ 、∧ 、∨ 、∃ 、∀
示例查询：
3.4.7 关系代数与元组关系演算的结合
查询 1：列出选修了由西席编号为“001”的西席授课的至少一门课程的所有学生的名字。
关系代数：
元组关系演算：
查询 2：列出没有选修由西席编号为“001”的西席授课的课程的所有学生的名字。
关系代数：
元组关系演算：
查询 3：列出选修了由西席编号为“001”的西席授课的所有课程的学生的名字。
关系代数：
元组关系演算：
查询 4：列出没有选修由西席编号为“001”的西席授课的至少一门课程的所有学生的名字。
关系代数：
元组关系演算：
焦点概念
存在量词 (∃)：表示存在至少一个元组满足条件。
全称量词 (∀)：表示所有元组都满足条件。
逻辑运算符：
与 (∧)：表示两个条件都为真。
或 (∨)：表示至少一个条件为真。
非 (¬)：表示条件为假。
应用场景
复杂查询：用于描述需要复杂逻辑判定的查询。
多表关联：能够处置惩罚多表之间的关联和过滤条件。
第四章 结构化查询语言
4.1 SQL 概述 (Overview of SQL)
4.1.1 SQL 操作
创建 (Create)
修改 (Alter)
删除 (Drop)
插入 (Insert)
更新 (Update)
删除 (Delete)
查询 (Select)
授权 (Grant)
收回 (Revoke)
4.1.2 数据界说语言 (DDL)
允许指定一组关系及每个关系的详细信息，包括：
- 创建数据库
- 创建关系模式（表）
- 界说每个属性的域值
- 创建完备性约束
- 创建视图、索引、表空间（关系在磁盘上的物理存储结构）
- 安全和授权信息
- 等等……
4.1.3 SQL 中的域范例
- char(n)：固定长度字符字符串，用户指定长度 n
- varchar(n)：可变长度字符字符串，用户指定最大长度 n
- int：整数（呆板相关的有限整数子集）
- float(n)：浮点数，用户指定精度至少为 n 位
- numeric(p, d)：固定点数，用户指定精度 p 位，此中 d 位为小数点右边的位数
4.1.4 SQL 中的日期和时间范例
- date：包罗年、月和日的日期（4 位数字年份）。
比方：date '2001-7-27'
比方：time '09:00:30' 或 time '09:00:30.75'
比方：datetime '2012-3-13 08:25:30.75'
*其他：所有域范例允许利用空值（null），声明属性为 not null 则克制该属性为空
4.2 SQL-DDL/DML：创建数据库 (Create Database)
4.2.1 SCT 数据库示例
Student 表：( Sno ) char(10), ( Sname ) char(20), ( Gender ) char(2), ( Age ) integer, ( Dno ) char(2), ( Class ) char(8)
Dept 表：( Dno ) char(2), ( Dname ) char(20), ( Dean ) char(20)
Course 表：( Cno ) char(3), ( Cname ) char(30), ( Hours ) integer, ( Credit ) float(1), ( Tno ) char(3)
Teacher 表：( Tno ) char(3), ( Tname ) char(30), ( Dno ) char(2), ( Salary ) float(2)
SC 表：( Sno ) char(10), ( Cno ) char(3), ( Score ) float(1)
4.2.2 创建数据库
下令：Create database db_name;
示例：Create database SCT;
创建表
下令：Create table R (A1 D1, A2 D2, ..., An Dn, (integrity-constraint1), ..., (integrity-constraintk));
表明：
- ( R ) 是关系或表的名称。
- 每个
是关系模式中属性的名称。
-
是属性
的数据范例。
示例：
Create Table Student (Sno char(10) Not Null, Sname char(20), Gender char(2), Age integer, Dno char(2), Class char(8));
4.2.3 创建表中的完备性约束
- Not NULL：非空约束，确保字段不能为NULL。
- Primary Key：主键约束，用于唯一标识表中的每一行。
- Check：检查约束，用于验证数据满足特定条件。
- 示例：
- Sno char(10) Not NULL,
- Sname char(20),
- Gender char(2),
- Age integer,
- Dno char(2),
- Class char(8),
- Primary Key (Sno),
- Check (Age > 12)
这里界说了Student表，此中Sno为主键且不能为空，Age字段必须大于12。
4.2.4 插入数据和删除数据
插入数据：
利用 INSERT INTO 下令：
Insert into R [(A1, A2, ..., An)]
values (value1, value2, ..., valuen);
示例：
Insert into Student values ('2015030101', 'Li Jian', 'M', 20, '03', '20150301');
注意：如果违背任何完备性约束，插入操作将失败。
删除数据：
利用 DELETE FROM 下令删除表中的所有元组：
示例：
注意：后续我们将学习怎样删除特定的元组。
4.3 SQL-DML：SELECT 语句
SQL 基于聚集和关系操作，经过一定的修改和加强。
典型的 SQL 查询形式：
这个查询等价于关系代数表达式：
SQL 查询的效果是一个关系。
4.3.1 SELECT 子句
SELECT 子句列出查询效果中所需的属性，对应于关系代数中的投影操作。
示例：
找出所有学生的名字：
Select Sname From Student;
在关系代数中，这个查询等价于：
注意：SQL 名称不区分大小写，可以利用大写或小写字母。
星号 (*) 表示选择所有属性：
Select * From Student;
SQL 允许关系中有重复值。
去除重复值，在 SELECT 后插入关键字 DISTINCT：
示例：找出效果大于95的所有学生学号，去除重复：
elect distinct Sno From SC Where score > 95;
保留重复值，关键字 ALL 表示不去除重复值：
示例：
Select all Sno From SC Where score > 95;
SELECT 子句中可以包罗算术表达式：
- Tno, Tname, Salary * 2 From Teacher;
4.3.2 WHERE 子句
WHERE 子句指定查询效果必须满足的条件，对应于关系代数中的选择谓词。
找出所有在 '03' 号系且年事小于20岁的学生姓名：
Select Sname
From Student
Where Dno = '03' and Age < 20;
比较效果可以利用逻辑连接词 and, or, not 组合。
SQL 包罗 BETWEEN 比较操作符：
示例：找出工资在3000到4000之间的西席姓名：
Select Tname
From Teacher
Where Salary between 3000 and 4000;
示例：找出年事在19到21岁的学生姓名：
Select Sname
From Student
Where Age between 19 and 21;
注意利用 and, or, not 时的优先序次：
Select Tname
From Teacher
Where Salary < 2000 or (Salary > 2500 and Dno = '03');
列出选修了课程 '001' 或 '002' 的所有学生的学号：
Select Sno
From SC
Where Cno = '001' or Cno = '002';
4.3.3 FROM 子句
FROM 子句列出查询中涉及的关系，对应于关系代数中的笛卡尔积操作。
示例：
找出 Student 和 SC 表的笛卡尔积：
Select *
From Student, SC;
找出选修了课程编号为 '002' 的所有学生的姓名：
Select Sname
From Student, SC
Where Student.Sno = SC.Sno and Cno = '002';
4.3.4 重命名操作
SQL 允许利用 AS 子句对关系和属性进行重命名：
示例：
old_name as new_name
找出所有学生的名字、学号和年事，并将列名 Sno 重命名为 Student_ID：
Select Sname, Sno as Student_ID, Age
From Student;
示例：找出选修了课程编号为 '003' 的所有学生的名字和效果：
Select T.Sname, S.score
From Student as T, SC as S
Where T.Sno = S.Sno and S.Cno = '003';
4.3.5字符串操作：LIKE 和 NOT LIKE
SQL 包罗用于字符串比较的字符串匹配操作符。
- 百分号 (%)：匹配零个或多个字符。
- 下划线 (_)：匹配单个字符。
- 反斜杠 ()：转义字符，表示反面的字符按字面意义表明。
- LIKE 子句：
示例：找出姓氏为 '刘' 的所有学生的名字和年事：
Select Sname, Age
From Student
Where Sname like '刘%';
查询名字是“刘某某”的学生的名字和年事：
Select Sname, Age
From Student
Where Sname like '刘_ _';
说明：利用LIKE操作符和下划线（_）进行字符串匹配，此中每个下划线代表一个字符。
查询姓氏不是“刘”的学生的名字和年事：
Select Sname, Age
From Student
Where Sname not like '刘%';
说明：利用NOT LIKE操作符和百分号（%）进行字符串匹配，百分号代表任意多个字符。
4.3.6 效果排序：ORDER BY
有时候需要对查询效果进行重新排序。
ORDER BY 子句：
- by column_name [asc | desc];
示例：按学号升序排列显示所有学生的学号和姓名：
Select Sno, Sname
From Student
Order by Sno asc;
示例：按效果降序排列显示效果大于90并选修了课程'002'的学生学号：
Select Sno
From SC
Where Cno = '002' and Score > 90
Order by Score desc;
4.3.7 多表连接操作
4.3.7.1 等值连接 (Equal-Join)
多表连接的基本结构：
Select A1, A2, ..., An
From R1, R2, ..., Rm
Where P;
关系代数表示为：
示例：找出选修了课程'001'的所有学生的名字，并按效果降序排列：
Select Sname
From Student, SC
Where Student.Sno = SC.Sno and SC.Cno = '001'
Order by Score DESC;
示例：三表连接，找出选修了数据库课程的所有学生的名字，并按效果降序排列：
Select Sname
From Student, SC, Course
Where Student.Sno = SC.Sno and SC.Cno = Course.Cno and Cname = 'Database'
Order by Score DESC;
4.3.7.2 不等值连接 (Not Equal-Join)
示例：找出薪水不同的两位西席的名字：
Select T1.Tname, T2.Tname
From Teacher T1, Teacher T2
Where T1.Salary > T2.Salary;
示例：找出年事不同的两位学生的名字：
Select S1.Sname as Stud1, S2.Sname as Stud2
From Student S1, Student S2
Where S1.Age < S2.Age;
示例：列出选修了课程'001'和'002'的所有学生的学号：
Select S1.Sno
From SC S1, SC S2
Where S1.Sno = S2.Sno and S1.Cno = '001' and S2.Cno = '002';
示例：列出同时选修了课程'001'和'002'，且'001'课程效果高于'002'课程效果的学生学号：
Select S1.Sno
From SC S1, SC S2
Where S1.Sno = S2.Sno and S1.Cno = '001' and S2.Cno = '002' and S1.Score > S2.Score;
示例：列出未选修任何由“李敏”传授课程的所有学生的名字：
Select Sname
From Student S, SC, Course C, Teacher T
Where S.Sno = SC.Sno and SC.Cno = C.Cno and C.Tno = T.Tno and T.Tname <> 'Li Min';
4.3.8 嵌套子查询
- SQL 提供了嵌套子查询的机制。
- 嵌套子查询是一个嵌套在另一个查询中的 SELECT-FROM-WHERE 表达式。
- 常见用途：
4.3.8.1 嵌套子查询：[not] in
基本语法：
Expression 通常是列名或常量。
示例：列出“刘刚”和“张强”的所有信息：
Select *
From Student
Where Sname in ('Liu Gang', 'Zhang Qiang');
示例：列出选修课程号为'001'的所有学生的学号和名字：
Select Sno, Sname
From Student
Where Sno in (Select Sno From SC Where Cno = '001');
示例：列出选修了课程'001'和'002'的所有学生的学号：
Select Sno
From SC
Where Cno = '001' and Sno in (Select Sno From SC Where Cno = '002');
示例：列出未选修任何由“李敏”传授的课程的所有学生的名字：
Select Sname
From Student
Where Sno not in (Select Sno
From SC, Course C, Teacher T
Where SC.Cno = C.Cno and C.Tno = T.Tno and T.Tname = 'Li Min');
4.3.8.2 相对子查询与非相对子查询
示例：包括外层查询和内层查询：
如果内层查询可以独立执行且不需要从外层查询中导入参数值，则称为非相对子查询。
有时内层查询需要从外层查询中导入参数值，称为相对子查询。
示例：列出未通过任何课程考试的所有学生的名字：
Select Sname
From Student S1
Where 60 > all
(Select Score
From SC
Where SC.Sno = S1.Sno);
4.3.8.3 嵌套子查询：some / all
基本语法：
Expression θ some (subquery);
Expression θ all (subquery);
此中 θ 为比较操作符（>, >=, <, <=, =, <>）。
表明：
Expression θ some (subquery) 等价于存在某个子查询效果使得表达式为真。
Expression θ all (subquery) 等价于所有子查询效果使得表达式为真。
示例：
(5 < some (5, 6)) 为真，因为 5 < 6。
(5 = some (5, 6)) 为真，因为 5 = 5。
示例：
(5 < all (5, 6)) 为假，因为 5 不小于 5。
(5 = all (5)) 为真，因为 5 即是 5。
找出工资最低的西席姓名：
Select Tname
From Teacher T1
Where T1.Salary <= all (Select Salary From Teacher);
找出选修了课程'001'但效果不是最高的所有学生的学号：
Select Sno
From SC
Where Cno = '001' and Score < some (Select Score From SC Where Cno = '001');
Select Sname
From Student S1
Where 60 > all (Select Score From SC Where SC.Sno = S1.Sno);
- 找到选修'001'课程并且得分最高的所有学生的姓名：
Select Sno
From SC
Where Cno = '001' and Score = (Select Max(Score) From SC Where Cno = '001');
- 找到'2015030101'学生选修的分数最低的课程数量：
Select Count(*)
From SC
Where Score = (Select Min(Score) From SC Where Sno = '2015030101');
Select Count(*)
From SC
Where Score = (Select Min(Score) From SC Where Sno = '2015030101' and Sname = '李建');
- 表达式 = some (子查询) 等价于 表达式 in (子查询)：
Select Sno, Sname
From Student
Where Sno in (Select Sno From SC Where Cno = '001');
Select Sno, Sname
From Student
Where Sno = some (Select Sno From SC Where Cno = '001');
- 表达式 not in (子查询) 等价于 表达式 <> some (子查询)：
Select Sno, Sname
From Student
Where Sno not in (Select Sno From SC Where Cno = '001');
Select Sno, Sname
From Student
Where Sno <> some (Select Sno From SC Where Cno = '001');
4.3.8.4 嵌套子查询：[not] Exists
基本语法：
- [not] exists (子查询)：exists 构造返回 true 如果子查询不为空，返回 false 如果子查询为空。
- exists (子查询) ≡ (子查询 ≠ Ø)
- not exists (子查询) ≡ (子查询 = Ø)
列出由'李明'西席传授的所有学生的姓名：
Select distinct Sname
From Student S, SC, Course C, Teacher T
Where exists (Select * From SC, Course, Teacher
Where SC.Sno = S.Sno and SC.Cno = C.Cno and C.Tno = T.Tno and T.Tname = '李明');
not exists 在某些查询中非常重要和必要：
列出所有选修了'2015030101'学生所选的所有课程的学生数量（至少，多于）：
Select DISTINCT Sno From SC SC1
Where not exists
( Select * From SC SC2
Where SC2.Sno = '2015030101' and
not exists
( Select * From SC SC3
Where SC3.Cno = SC2.Cno and SC3.Sno = SC1.Sno));
例子解析：
列出所有选修了'2015030101'学生所选课程的学生数量（至少，多于）：
- 找出所有课程Cy，如果'2015030101'学生选了它，那么学生Sx也选了它。
- 谓词P：'2015030101'学生选了课程Cy。
- 谓词Q：学生Sx选了课程Cy。
- 逻辑公式转换：
(∀Cy)(P → Q) ≡ ¬∃Cy(¬(P → Q))
≡ ¬∃Cy(¬(¬P ∨ Q)) ≡ ¬∃Cy(P ∧ ¬Q)
列出所有选修了'2015030101'学生所选的所有课程的学生数量（至少，多于）：
Select DISTINCT Sno From SC SC1
Where not exists
( Select * From SC SC2
Where SC2.Sno = '2015030101' and
not exists
( Select * From SC SC3
Where SC3.Cno = SC2.Cno and SC3.Sno = SC1.Sno));
列出由'001'西席传授的所有学生的姓名：
- ( Select * From Course C
- Where C.Tno = '001' and
- not exists
- ( Select * From SC
- Where SC.Sno = S.Sno and SC.Cno = C.Cno));
列出未选修由'李明'西席传授的任何课程的所有学生的姓名：
Select Sname From Student S
Where not exists
( Select * From Course, SC, Teacher
Where SC.Sno = S.Sno and SC.Cno = Course.Cno and Course.Tno = Teacher.Tno and Teacher.Tname = '李明');
列出所有利用'S1'供应的所有零件的项目编号：
Select DISTINCT Jno From SPJ SPJ1
Where not exists
( Select * From SPJ SPJ2
Where SPJ2.Sno = 'S1' and
not exists
( Select * From SPJ SPJ3
Where SPJ3.Pno = SPJ2.Pno and SPJ3.Jno = SPJ1.Jno));
4.3.9 效果盘算和聚合函数
SELECT 子句不光仅可以包罗列名，还可以包罗盘算表达式或聚合函数：
例子：如果两个西席的工资不同，请列出他们的姓名和工资差：
Select T1.Tname as TR1, T2.Tname as TR2, T1.Salary - T2.Salary
From Teacher T1, Teacher T2
Where T1.Salary > T2.Salary;
SQL 中的聚合函数：
- Count：盘算出现的次数。
- Sum：求和。
- Avg：求匀称值。
- Max：求最大值。
- Min：求最小值。
例子：
列出所有西席的工资总和：
Select SUM(Salary) From Teacher;
列出学生的数量：
Select Count(*) From Student;
列出'数据库'课程的匀称分数：
Select Avg(Score)
From Course C, SC
Where C.Cname = 'database' and C.Cno = SC.Cno;
Select 子句不光遵循列名，还包罗一些盘算表达式或聚集函数。
Select 列名 | 表达式 | 聚集函数(列名) [...]
From R1, R2, ..., Rm
Where P
比方，如果两个老师的工资不同，请列出他们的姓名和工资差额。
Select T1.Tname as TR1, T2.Tname as TR2, T1.Salary - T2.Salary
From Teacher T1, Teacher T2
Where T1.Salary > T2.Salary
4.3.9.1 聚集函数
聚集函数在SQL中
这些函数操作关系中某列的多值聚集，返回一个值。
列出所有老师工资的总和。
Select SUM(Salary) From Teacher
列出学生的数量。
Select Count(*) From Student
列出“数据库”课程的匀称分。
Select Avg(Score)
From Course C, SC
Where C.Cname='database' and C.Cno=SC.Cno
Select MAX(Score) Select MIN(Score)
4.3.10 group by分组与Having子句
分组与Having子句
Select Cno, Avg(Score) From SC Group by Cno
Select Sno, Avg(Score) From SC Group by Sno
注意：Select子句中除聚集函数外的属性必须出现在Group by列表中。
Select Sno
From SC
Where Score < 60
Group by Sno
having Count(*) > 2
Select Sno,Avg(Score)
From SC
Where Sno in (Select Sno
From SC
Where Score<60
Group by Sno
Having Count(*)>2
)
Group by Sno
4.3.11 聚集操作(union/intersect/except)
聚集操作
- 聚集操作（union、intersect和except）操作关系并对应于关系代数操作∪、∩、－。
- 上述每个操作自动消除重复项；要保留所有重复项，请利用相应的多集版本union all、intersect all和except all。
- 假设一个元组在关系R中出现m次，在关系S中出现n次，那么在不同聚集操作中的出现次数为：
- 在R和S的union all操作中出现m + n次。
- 在R和S的intersect all操作中出现min(m, n)次。
- 在R和S的except all操作中出现max(0, m - n)次。
示例：
列出修了‘002’或‘003’课程的学生人数。
Select Sno From SC Where Cno = '002'
Union [all]
Select Sno From SC Where Cno = '003'
表明：
在SQL中，聚集操作如UNION、INTERSECT和EXCEPT用于归并或比较两个或多个效果集。UNION操作符用于归并两个效果集，并自动消除重复项。INTERSECT操作符返回两个效果会合共有的行，而EXCEPT操作符返回在第一个效果会合但不在第二个效果会合的行。
4.3.12 空值
空值
- 元组可能有一个空值，用null表示，表示一个未知的值或不存在的值。
- 谓词is null（is not null）可用于检查空值。
示例：5 + null 效果为 null；100 * null 效果为 null；等等。
- 与空值的任何比较返回 false 或 unknown。
示例：5 < null 效果为 false；null = null 效果为 unknown；null < null 效果为 unknown；
- 所有聚合操作（除了 count(*) 以外）都会忽略包罗空值的元组。
示例：
Select Avg(Score) From SC;
Select Count(*) From SC;
4.3.13 关系连接操作
连接操作主要包罗三个要素：连接范例（Join type）、连接条件（Join condition）和最终效果。
4.3.13.1 连接范例（Join Type）
关系连接（Join operation）是关系数据库中最基本且最重要的操作之一。它用于将两个关系（表）结合起来，生成一个新的关系作为效果。其主要目的是从多个表中获取相关数据。
连接范例决定了在连接过程中如那边理在一个关系中但不在另一个关系中的元组。主要的连接范例包括：
- 内连接（Inner Join）：只返回两个表中满足连接条件的记录。
- 左外连接（Left Outer Join）：返回左表中的所有记录，以及右表中满足连接条件的记录。不满足条件的右表记录以NULL填充。
- 右外连接（Right Outer Join）：返回右表中的所有记录，以及左表中满足连接条件的记录。不满足条件的左表记录以NULL填充。
- 全外连接（Full Outer Join）：返回两个表中的所有记录，不满足连接条件的记录以NULL填充。
4.3.13.2 连接条件（Join Condition）
连接条件界说了两个表中哪些记录匹配，以及连接效果中包罗哪些属性。常见的连接条件有：
- 自然连接（Natural Join）：自动匹配两个表中同名的列，并返回这些列匹配的记录。
- ON ：利用明确的谓词界说连接条件。
- USING (A1, A2, ..., An)：利用指定的列进行连接。
实例讲解：
关系Loan
| Loan_number
| Branch_name
| Amount
| | L-170
| Downtown
| 3000
| | L-230
| Redwood
| 4000
| | L-260
| Perryridge
| 1700
|
关系Borrower
| Customer_name
| Loan_number
| | Jones
| L-170
| | Smith
| L-230
| | Hayes
| L-155
|
4.3.13.4 内连接（Inner Join）
SELECT *
FROM Loan
INNER JOIN Borrower ON Loan.Loan_number = Borrower.Loan_number;
效果：
| Loan_number
| Branch_name
| Amount
| Customer_name
| | L-170
| Downtown
| 3000
| Jones
| | L-230
| Redwood
| 4000
| Smith
|
4.3.13.5 自然内连接（Natural Inner Join）
SELECT *
FROM Loan
NATURAL JOIN Borrower;
效果：
| Loan_number
| Branch_name
| Amount
| Customer_name
| | L-170
| Downtown
| 3000
| Jones
| | L-230
| Redwood
| 4000
| Smith
|
4.3.13.6 左外连接（Left Outer Join）
SELECT *
FROM Loan
LEFT OUTER JOIN Borrower ON Loan.Loan_number = Borrower.Loan_number;
效果：
| Loan_number
| Branch_name
| Amount
| Customer_name
| | L-170
| Downtown
| 3000
| Jones
| | L-230
| Redwood
| 4000
| Smith
| | L-260
| Perryridge
| 1700
| NULL
|
4.3.13.7 右外连接（Right Outer Join）
SELECT *
FROM Loan
RIGHT OUTER JOIN Borrower ON Loan.Loan_number = Borrower.Loan_number;
效果：
| Loan_number
| Branch_name
| Amount
| Customer_name
| | L-170
| Downtown
| 3000
| Jones
| | L-230
| Redwood
| 4000
| Smith
| | NULL
| NULL
| NULL
| Hayes
|
4.3.13.8 全外连接（Full Outer Join）
SELECT *
FROM Loan
FULL OUTER JOIN Borrower ON Loan.Loan_number = Borrower.Loan_number;
效果：
| Loan_number
| Branch_name
| Amount
| Customer_name
| | L-170
| Downtown
| 3000
| Jones
| | L-230
| Redwood
| 4000
| Smith
| | L-260
| Perryridge
| 1700
| NULL
| | NULL
| NULL
| NULL
| Hayes
|
连接查询实例
假设我们需要列出所有借款人和贷款的详细信息，并按贷款金额排序：
SELECT Customer_name, Loan_number, Branch_name, Amount
FROM Loan
FULL OUTER JOIN Borrower USING (Loan_number)
ORDER BY Amount;
4.4 SQL-DML：INSERT/DELETE/UPDATE
概述:
数据操作语言 (DML) 是SQL语言的一部分，用于查询和修改数据库中的数据。主要包括四种操作：插入 (INSERT)、删除 (DELETE)、更新 (UPDATE) 和选择 (SELECT)。以下将重点先容插入、删除和更新操作。
4.4.1 插入操作 (INSERT)
插入操作用于向表中添加一条或多条记录。基本语法如下：
INSERT INTO 表名 (列1, 列2, ..., 列N) VALUES (值1, 值2, ..., 值N);
示例：
向学生表中插入一条新的学生记录：
INSERT INTO Student VALUES ('2015030101', 'Li Jian', 'M', 20, '03', '20150301');
高级插入：
将一个查询效果插入到新表中。比方，我们创建一个新的表 St，其结构为 St(Sno, Sname, avgScore)，并将 Student 和 SC 两个表的查询效果插入到 St 表中：
INSERT INTO St (Sno, Sname, avgScore)
SELECT Sno, Sname, AVG(Score)
FROM Student, SC
WHERE Student.Sno = SC.Sno
GROUP BY Student.Sno;
4.4.2 删除操作 (DELETE)
删除操作用于从表中删除一条或多条记录。基本语法如下：
DELETE FROM 表名 WHERE 条件;
示例：
从 SC 表中删除记录：
DELETE FROM SC;
根据特定条件删除记录：
DELETE FROM Student WHERE Sno = '2015030102';
高级删除：
删除所有至少有四门课程不及格的学生：
DELETE FROM Student
WHERE Sno IN (
SELECT Sno
FROM SC
WHERE Score < 60
GROUP BY Sno
HAVING COUNT(*) >= 4
);
4.4.3 更新操作 (UPDATE)
更新操作用于修改表中的记录。基本语法如下：
UPDATE 表名 SET 列1 = 值1, 列2 = 值2, ... WHERE 条件;
示例：
将西席表中所有西席的工资提高10%：
UPDATE Teacher
SET Salary = Salary * 1.1;
根据条件更新工资：
UPDATE Teacher
SET Salary = Salary * 1.2
WHERE Dno IN (
SELECT Dno
FROM Dept
WHERE Dname = 'Computer Science'
);
高级更新：
若学生效果低于匀称分，则将其效果提高5%：
UPDATE SC
SET Score = Score * 1.05
WHERE Cno = '001' AND Score < (
SELECT AVG(Score)
FROM SC
WHERE Cno = '001'
);
将 Li Ji 的效果更新为其班级的匀称分：
UPDATE SC
SET Score = (
SELECT AVG(SC2.Score)
FROM SC SC1, Student S1, SC SC2, Student S2
WHERE S1.Class = S2.Class
AND SC1.Sno = S1.Sno
AND SC2.Sno = S2.Sno
AND S1.Sname = 'Li Ji'
AND SC1.Cno = '001'
AND SC1.Cno = SC2.Cno
)
WHERE Cno = '001' AND Sno IN (
SELECT Sno
FROM Student
WHERE Sname = 'Li Ji'
);
4.5 数据库视图详解
视图 (VIEW) 概述
视图是数据库中的一种捏造表，它通过SQL查询从一个或多个表中提取数据并生成效果集。视图不存储现实数据，只存储查询效果，这使得视图非常机动且高效。
视图的优点
- 简化查询：视图可以简化复杂的查询语句，使得查询更加简洁易读。
- 数据安全：通过视图可以限定用户访问表中的某些列或某些行，加强数据的安全性。
- 数据独立性：视图可以隐藏底层表的复杂结构，提供一种更友好的数据表示方式。
视图的创建
创建视图的基本语法如下：
CREATE VIEW 视图名 [(列名1, 列名2, ...)] AS 子查询;
示例：
创建一个视图 CompStud，包罗所有盘算机科学系学生的信息：
CREATE VIEW CompStud AS
SELECT *
FROM Student
WHERE Dno IN (
SELECT Dno
FROM Dept
WHERE Dname = 'Computer Science'
);
创建一个视图 Teach，包罗西席传授课程的信息：
CREATE VIEW Teach AS
SELECT T.Tname, C.Cname, Credit
FROM Teacher T, Course C
WHERE T.Tno = C.Tno;
视图的查询
通过视图，我们可以执行与普通表相同的查询操作。比方：
- Teach T
- T.Cname = 'Network';
视图的更新
大多数SQL实现只允许在简单视图（不含聚合）的情况下更新。视图可以更新的条件包括：
- FROM 子句只能包罗一个表。
- SELECT 子句包罗表的主键。
- SELECT 子句只包罗表的属性名，不包罗表达式、聚合或 DISTINCT 规范。
示例：
创建一个视图 ClassStud，包罗学生的姓名和班级信息：
CREATE VIEW ClassStud (Sname, Sclass) AS
SELECT Sname, Class
FROM Student;
向 ClassStud 插入数据：
INSERT INTO ClassStud
VALUES ('Qian San', '20150402');
更新视图 S_G 中的匀称效果：
UPDATE S_G
SET AvgS = 85
WHERE Sno = '2015030102';
数据界说语言 (DDL) 操作详解
删除操作 (DROP)
删除操作用于移除数据库中的视图、表或数据库。
语法：
示例：
修改操作 (ALTER)
修改操作用于修改数据库表的结构，如添加、删除或修改列。
语法：
示例：
- Saddr CHAR(40), PID CHAR(18);
- TABLE Student
- Sname CHAR(30);
第五章 数据库筹划
5.1 数据库筹划概述
数据库筹划的界说：
数据库筹划是创建一个详细的数据模型的过程。
数据库筹划 — 模型化
什么是模型？
模型是对现实体系（如企业）的目标、结构和行为的抽象描述。现实天下的某个部分视观察者的观点和任务设置而定。
数据库筹划：
天下上每个对象都有许多信息。
应该涉及多少以及什么样的信息？
这取决于要办理的标题。
比方：
- 一头牛
- 一头玄色的牛
- 一头有四条腿的牛
- 公牛，不是奶牛
- 等等
筹划阶段：
1.描述数据需求
2.概念筹划
3.数据库实行
筹划替换方案：
必须避免两大陷阱：
- 冗余：不良筹划可能会重复信息。
- 不完备性：不良筹划可能会使企业的某些方面难以或不可能建模。
5.2 实体-关系模型
5.2.1 实体（聚集）
实体：现实天下中与其他对象区别开来的“事物”或“对象”。
实体集：同范例实体的聚集，它们具有相同的属性。
实体的第一步是找出相关应用领域中的所有实体。
比方，图书管理中的实体有：读者、册本、书架。
5.2.2 属性
属性：一个实体由一组属性表示，属性是每个实体成员拥有的描述性特性。
值：每个实体都有其属性的值。
示例
- 读者
- 实体集按范例表示：LB_ID、姓名、年事、性别、地址
- 实体按范例值表示：001，李华，25，男，北京
键：实体的键是一组属性，这些属性足以区分实体。
比方，读者的键是LB_ID，因为LB_ID的值是唯一的，其他属性的值可能会重复。
简单属性与复合属性(Simple and Composite attribute)
- 简单属性：未分为子部分。
- 复合属性：可以分为子部分。比方，地址可以分为街道、城市、州、邮政编码等。
单值属性与多值属性(Single-valued and Multi-valued)
- 单值属性：对于特定实体只有一个值。
- 多值属性：对于特定实体有一组值。
- 比方，电话号码：西席和学生可能有多个电话号码。
派生属性(Derived attribute)：可以从其他相关属性或实体的值中导出。
比方，生日可以导出年事。
空值属性与非空值属性：当实体没有属性值时，属性取空值。
弱实体与强实体(Weak Entity and Strong Entity)
弱实体:依赖于其他实体存在的实体。
强实体:不依赖于其他实体存在的实体。
例子
- 父母（强实体）/ 孩子（弱实体）
- 书（强实体）/ 版本（弱实体）
- 公司（强实体）/ 产品（弱实体）
合同（强实体）和合同条目（弱实体）的关系图。
实体分类
分类是辨认、区分和理解对象（实体）的过程。分类意味着将对象（实体）分为某些特定用途的类别。
学生分类：本科生、研究生、硕士、博士。
关系是多个实体之间的关联。
- 到场（Participation）：实体之间的关联称为到场。
- 度（Degree）：关系中实体的数量称为关系的度。
- 关系的度可以是1度、2度或多度。
- 大多数数据库体系中的关系是2度关系。
3度关系：供应商、零件和项目。
1度关系：零件与部门之间的关系。
角色（Role）
实体在关系中扮演的功能称为实体的角色。
描述性属性
关系也可以有称为描述性属性的属性。
比方：借书关系的描述性属性包括借书时间和还书时间。
描述性属性的例子包括供应数量和供应时间。
映射基数
- 映射基数表示通过关系可以与另一个实体关联的实体数量。
- 映射基数在描述2度关系时最有用。
- 对于2度关系，映射基数范例包括：
- 一对一（1:1）
- 一对多（1:m）
- 多对一（m:1）
- 多对多（m:n）
示例
- 例子：画家（A）与画作（B）的关系。
- 一对一：每个画家对应一个画作。
- 一对多：每个画家可以有多个画作。
- 例子：学生（A）与课程（B）的关系。
- 多对一：每个学生可以选修多门课程。
- 多对多：多个学生可以选修多个课程。
通常，到场关系的一个实体在关系中有一个最小基数和最大基数，表示为（MinCard..MaxCard）
完全到场（Full Participation）
至少有一个实体到场关系，最小基数为1。
部分到场（Partial Participation）
允许没有实体到场关系，最小基数为0。
5.3实体-关系图
E-R建模过程
1.辨认实体
2.确定每个实体的属性
3.为每个实体选择主键
4.创建实体之间的关系
5.绘制E-R模型
E-R图
常用的E-R图方法有：
- Chen方法
- Crow’s Foot方法
- IDEF1X方法
- UML
- ......
5.3.1 Chen方法
- 矩形：实体
- 菱形：关系
- 椭圆形：属性
- 双线椭圆：多值属性
- 虚线椭圆：派生属性
- 实线：连接带有属性的实体
- 虚线：连接带有属性的关系
- 下划线：主键
- 同数字下划线：多属性的主键
- 箭头线：从关系到一方
- 非箭头线：从关系到多方
- 单线：部分到场
- 双线：完全到场
- 带有1标志的线：从关系到一方
- 带有m或n标志的线：从关系到多方
- 带有1..1、0..1、0..m等标志的线：表示部分到场和完全到场
读者、册本、书架之间的借阅关系。
图书管理体系中的实体及其关系，包括借书和保管关系。
复合属性、多值属性和派生属性的示例。
带有笔墨标志的线：表示实体在关系中的角色。
现实案例分析：企业员工、部门、项目之间的关系。
Chen方法：弱实体
- 双矩形：弱实体
- 双菱形：弱实体与强实体之间的关系
- 虚线下划线：弱实体的键
三角形带有isa笔墨：表示分类。
5.3.2 Crow’s Foot方法
映射基数
- 1：一对一
- 1或多：一对多
- 0或1：零或一
- 多：多对多
示例
- 零件由多个部件组成的关系。
- 员工与其配偶的婚姻关系。
分类
示例
车辆分类为汽车和卡车的关系图。
IDEF1X 方法案例
展示了订单、产品、服务等的关系和属性。
概念筹划阶段
1.辨认并列出实体。
2.辨认业务规则，这些规则有助于辨认实体间的关系，并同时找到约束条件。
3.辨认实体的属性和主键。
4.检查实体间的关系。
5.绘制ER图。
5.3.3 业务规则
1.描述特定构造内的政策、程序或原则。
2.描述在构造情况内创建/执行操作的规则。
3.必须书面形式。
4.必须易于理解和广泛传播。
业务规则的来源
1.公司经理。
2.政策制定者。
3.部门经理。
4.书面文档（程序、标准、操作手册）。
5.与最终用户的直接访谈。
概念筹划案例研究
- 包括研究效果、观察分析效果、原体系输出（报表和屏幕）、原体系的输入（数据的形式和屏幕）、原体系数据库/文件的说明。
- 实体可能是由多少实体归并后形成的，也可能是从源中按实体规则提取出来的。
- 客户名称为公司名称，必填项，长度<=50个字符。
- 客户地址必须包罗省、市（县）、街道（镇/乡）及详细地址，必填项，长度<=100个字符。
- 法人，必填项，长度<=20个字符。
- 电话号码可以填写一个座机号码和/或一个手机号码，必填项，长度<=13个字符。
- 传真为座机号码，非必填项，长度<=13个字符。
- 等等
- 例子中展示了客户、产品、客户项目、客户跟踪等实体及其关系。
概念筹划案例研究
展示了员工登记表，包括员工编号、姓名、部门、职位、入职日期、学历、培训经历、工作经历等信息。
5.4 转换ER模型到关系模式的步骤
以下是从ER模型（实体-关系模型）转换到关系模式的详细步骤和表明：
目标:
1.从概念数据模型中派生关系聚集：
2.利用规范化技术验证这些关系：
- 学习怎样通过规范化来验证派生的关系，以确保其结构正确。
3.验证逻辑数据模型是否支持所需的事务：
4.将多个用户视图归并为全局逻辑数据模型：
- 将多个用户视图归并成一个代表所有用户视图的全局逻辑数据模型。
详细步骤:
5.4.1 从逻辑数据模型派生关系
- 实体到关系：
- 将每个实体转换为一个关系。
- 比方，实体“Book”转换为关系：Book(ID, Name, Pub_date, Publisher)。
- 实体的属性到关系的属性：
- 实体的键到关系的主键：
1.复合属性
- 复合属性的处置惩罚：
- 复合属性（如日期中的年、月、日）在关系中分解为简单属性。
- 比方，实体“Student”有一个复合属性“Birthday”，分解为：Student(ID, Name, Year, Month, Day)。
2.多值属性
- 多值属性的处置惩罚：
- 为多值属性创建一个新的关系，并将该属性及其对应实体的主键作为新的关系的外键。
- 比方，实体“Teacher”有一个多值属性“Phone”，转换为：Contact_Info(ID, Phone)。
3.一对一（1:1）关系
- 部分到场的关系：
- 如果两个实体之间的关系是部分到场（0..1），则创建一个新关系，并将两个实体的主键组合为新关系的主键。
- 比方：Employee(ID, Name)和Spouse(Husband_EMPID, Wife_EMPID)。
- 完全到场的关系：
- 如果一个实体完全到场关系，则将另一个实体的主键作为外键添加到这个实体中。
- 比方：Employee(Emp_ID, Name, Dept_ID)。
4.一对多（1:n）关系
- 一对多关系的处置惩罚：
- 将“多”方的实体的主键作为外键添加到“多”方的关系中。
- 比方：Parent(Parent_ID)和Child(Child_ID, Child_name, Parent_ID)。
5.多对多（m:n）关系
- 多对多关系的处置惩罚：
- 创建一个新的关系来表示多对多关系，并包括到场关系的实体的主键作为外键。
- 比方：Student(S_ID, ...)和Course(C_ID, ...)通过关系CourseTaken(S_ID, C_ID, Score)。
6.弱实体
- 弱实体的处置惩罚：
- 创建一个关系，并将弱实体的所有简单属性及其主实体的主键作为新的关系的复合主键。
- 比方：Company(CID, Cname, Address)和Product(CID, PID, Pname, Price)。
7.多度关系
- 多度关系的处置惩罚：
- 创建一个关系来表示多度关系，并包括所有到场实体的主键作为外键。
- 比方：Supplier(S_ID, S_Name)和Part(P_ID, P_Name)通过关系Supply(S_ID, P_ID, Sup_Time, Quantity)。
这些步骤确保了从ER模型到关系模式的转换过程清晰、准确，并且最终的关系模式能够有用支持数据库体系的所有事务和操作。
分类实体（Classified Entity）
在ER模型中，分类实体通常包罗一个高层实体和一个或多个低层实体。转换时需要注意以下几点：
- 高层和低层实体分别转换成不同的关系：
- 高层实体和低层实体都会转换成独立的关系。
- 低层实体继承高层实体的主键作为本身的主键和外键：
- 比方，实体“Student”有两个子类实体“Undergraduate”和“Graduate”。转换后，关系如下：
- ID, Name, Gender, Age)
- ID, Military_Train)
- ID, Dissertation)
详细实例：
图中展示了一个ER图的转换示例：
- Person 实体具有属性 name, street, city
- Person 实体被分为 Employee（员工）和 Customer（顾客），分别具有特有属性 salary 和 credit-rating
有两种转换方式：
1.方式1：
- city, street)
- credit-rating)
- employee或customer的详细信息时需要访问两个表。
2.方式2：
- city, street)
- street, city, credit-rating)
5.4.2 利用规范化验证关系
- 验证逻辑数据模型中的关系是否经过规范化：
- 规范化是一种确保关系模式结构正确且无冗余的方法.
5.4.3 验证关系是否支持用户事务
确保逻辑数据模型中的关系支持所需的用户事务：
5.4.4 检查完备性约束
主要包括：
- 必要数据（Required data）
- 属性域约束（Attribute domain constraints）
- 多重性（Multiplicity）
- 实体完备性（Entity integrity）
- 参照完备性（Referential integrity）
- 一样平常约束（General constraints）
5.4.5 与用户一起审查逻辑数据模型
- 与用户一起审查逻辑数据模型：
- 确保用户认为模型是企业或构造数据需求的真实表示。
5.4.6 归并逻辑数据模型到全局模型
- 这是一个可选步骤：
- 将多个逻辑数据模型归并为一个全局逻辑数据模型，代表数据库的所有用户视图。
- 归并逻辑数据模型中的冲突：
- 属性冲突（Attribute conflict）：比方，不同数据范例、不同值范围等。
- 命名冲突（Naming conflict）：比方，同名异物（Homonymy），同物异名（Synonymy）。
- 结构冲突（Structure conflict）：比方，同一对象在不同应用领域具有不同的抽象级别。
5.5 规范化（Normalization）
本部分的目标是学习以下内容：
1.规范化的目的：
- 理解为什么要进行数据库规范化，即规范化的意义和作用。
2.没有规范化的潜在标题：
3.函数依赖的概念：
4.怎样进行规范化过程：
5.怎样辨认第一范式（1NF），第二范式（2NF），第三范式（3NF）和BC范式（BCNF）：
6.违背1NF、2NF、3NF和BCNF规则的关系所存在的标题：
5.5.1 为什么规范化（Why normalize）
5.5.1.1 不良数据库筹划的例子
以下是一个公司的订单体系信息表的示例，展示了不良数据库筹划：
Cno Cname Caddr Ono Pno Pname Pprice Qty
C001 Kris New York O001 P001 iPhone 600 3
C001 Kris New York O001 P002 iTouch 350 2
C001 Kris New York O002 P003 iPad 900 5
C002 Jenny Washington O003 P001 iPhone 600 8
字段表明：
- Cno：顾客编号
- Cname：顾客姓名
- Caddr：顾客地址
- Ono：订单编号
- Pno：产品编号
- Pname：产品名称
- Pprice：产品代价
- Qty：产品数量
标题：
- 如果删除订单'0002'，则产品'P003'将不存在。
- 如果没有订单，产品将不会被插入。
- 顾客、产品和订单的信息冗余，可能导致数据不同等。
导致上述标题的原因：
- 存在一些数据依赖：
- 该关系的主键为（Cno, Pno, Ono）。
- 产品名称和代价可以由产品编号（Pno）确定，而不是由（Cno, Pno, Ono）确定。
- （Cname, Caddr）？
5.5.1.2 办理方案：规范化
通过规范化，将上述关系拆分为以下三个关系：
1.Customer(Cno, Cname, Caddr)
2.Product(Pno, Pname, Pprice)
3.Order(Ono, Cno, Pno, Qty)
5.5.1.3 规范化
界说：
- 规范化是一种用于生成适合企业/构造数据需求的关系集的技术。
适合的关系特性：
- 最少的属性数：支持企业/构造的数据需求的最少属性数。
- 紧密的逻辑关系：具有紧密逻辑关系的属性应在同一个关系中。
- 最小的冗余：每个属性只表示一次，重要例外是构成或部分构成外键的属性。
5.5.1.4 规范化的好处
- 更容易访问和维护数据：用户可以更方便地访问和维护数据。
- 最小的存储空间：占用最小的存储空间。
5.5.1.5 数据冗余和更新异常
具有冗余信息的关系可能会产生更新异常，主要有三种范例：
1.修改异常：因遗漏更新而导致的不同等性。
2.插入异常：由于某些信息无法插入而引发的标题。
3.删除异常：因删除导致的非预期数据丢失。
例子：
- 数据冗余：客户、产品和订单信息冗余，导致数据不同等。
- 删除异常：删除订单'0002'，则产品'P003'将不存在。
- 插入异常：如果没有订单，产品将不会被插入。
- 修改异常：若更新'C001'为'Chris'，只更新第一个记录，会导致数据不同等。
5.5.1.6更新异常的原因
- 依赖关系：单一关系模式中的某些属性之间的依赖关系。
- 函数依赖：
- 部分函数依赖（Partial functional dependency）
- 传递函数依赖（Transitive functional dependency）
5.5.1.7 各种范式
- 第一范式（1NF）：消除重复组，每个属性值都是原子的。
- 第二范式（2NF）：消除部分依赖，非主属性完全依赖主键。
- 第三范式（3NF）：消除传递依赖，非主属性不依赖于其他非主属性。
- BC范式（BCNF）：每个决定因素都是候选键。
通过这些规范化步骤，可以有用减少数据冗余，避免更新异常，从而提高数据库的完备性和同等性。
5.5.2 函数依赖（Functional Dependencies）
5.5.2.1 函数依赖的界说：
- 设
为一个关系模式，
且
。
- 当且仅当对
的所有合法关系
的任何两个元组
和
来说，如果
，则
。
- 用符号表示为：
，表示“
函数依赖于
”。
- 反之：如果
不函数依赖于
，则记作：
。
例子
学生关系：
- 学生信息：Student (Sno, Sname, Gender, Age, Address)。
- 函数依赖：
- → Sname
- → Age
- → Gender
- → Address
例子分析
考虑一个关系
的实例：
A B
5.5.2.2 常见的函数依赖范例
1.平常依赖与非平常依赖：
2.例子：
5.5.2.3 部分函数依赖与完全函数依赖
1.部分函数依赖：
- 如果
，但存在
的真子集
使得
，则称
部分函数依赖于
。
2.完全函数依赖：
- 如果
，且不存在
的真子集
使得
，则称
完全函数依赖于
。
3.例子：
- 关系 SC (Sno, Cno, Score)：
为完全函数依赖。
5.5.2.4 候选键与非主属性
1.候选键：
- 能唯一标识关系中的元组的属性或属性组。
- 比方：Teaching (Sno, Age, Address, Cno, Cname, Score, Tno, Tname)，候选键：
。
2.主属性：
3.非主属性：
5.5.2.5 传递依赖
1.传递函数依赖：
2.例子：
- 关系 Student (Sno, Dno, Dname)：
5.5.3 第一范式（1NF）
1.界说：
- 如果关系的每个属性值都是原子的（不可再分的），则该关系满足第一范式。
2.例子：
- | name | fname | class | telephone
- | ------- | ------- | ------- | ----------
- | Jones | Allan | 2 | 555-234
- | Smith | John | 3 | 555-431
- | Brown | Harry | 2 | 555-1122
- | White | Edward | 3 | 555-334
5.5.4 第二范式（2NF）
1.界说：
- 如果关系中的每个非主属性完全函数依赖于任意候选键，则该关系满足第二范式。
2.怎样满足 2NF：
- 分析关系中的所有函数依赖。
- 如果存在非主属性对候选键的部分函数依赖，则将关系分解为两个新关系。
3.例子：
- 关系 Std_Dept (Sno, Cno, Score, SDept, Dname)：
- (Sno, Cno) → Score
- Sno → SDept
- Sno → Dname
- (Sno, Cno) → SDept
- (Sno, Cno) → Dname
- Cno, Score)
- SDept, Dname)
这些步骤和概念是理解和应用数据库规范化的底子，帮助我们筹划更高效和可靠的数据库。
5.5.5 第三范式（3NF）
第三范式 (3NF)：对于关系
，如果每个非主属性都不传递依赖于候选键，那么我们称关系
满足 3NF。
怎样使关系满足 3NF
1.分析关系的所有函数依赖。
2.如果有些非主属性传递依赖于候选键，则将关系分解为两个新关系。
例子
- 关系 Std_Dept (Sno, Cno, Score, SDept, Dname) 已经满足 2NF，现在我们进一步分解：
[1] Sno → SDept
[2] SDept → Dname
[3] Sno → Dname (传递依赖)
分解后的关系：
SC(Sno, Cno, Score)
SD(Sno, SDept)
Dept(SDept, Dname)
5.5.6 Boyce-Codd 范式（BCNF）
Boyce-Codd 范式 (BCNF)：对于关系
，如果每个类似于
的函数依赖中，
必须是候选键，那么我们称关系
满足 BCNF。
怎样使关系满足 BCNF
1.分析关系的所有函数依赖。
2.如果有些函数依赖不满足 BCNF，则将关系分解为两个新关系。
例子
- 关系 STC (Sno, Cno, Tno)：
- 业务规则：每位老师只传授一门课程，每门课程只有一位老师传授。
- 候选键：
、
- 非主属性：无
- 函数依赖：
[1] (Sno, Cno) → Tno
[2] (Sno, Tno) → Cno
[3] (Cno) → Tno
[4] (Tno) → Cno
SC(Sno, Cno)
CT(Cno, Tno)
规范化与连接操作的衡量
- 规范化的必要性：
- 减少数据冗余
- 防止删除异常
- 防止插入异常
- 防止修改异常
- 规范化带来的标题：
SC(Sno, Cno, Score)
SD(Sno, SDept)
Dept(SDept, Name)
- 这些分解后的关系需要通过连接操作才能恢复到原始数据形式。
结论
数据库筹划的意义和阶段：
E-R 模型
- 实体（强实体、弱实体、分类实体）
- 关系（度、角色、映射基数）
- 属性（键、简单属性、复合属性、单值属性、多值属性、派生属性、描述属性）
- E-R 图（Chen、Crow's Foot、IDEF1X、UML 等）
从 E-R 模型到关系模式的转换
规范化
- 没有规范化的标题（数据冗余、三种异常）
- 函数依赖（非平常函数依赖、部分函数依赖、完全函数依赖、传递函数依赖）
- 1NF、2NF、3NF、BCNF
- 从1NF到2NF到3NF到BCNF的分解方法
通过上述过程和方法，能够有用地筹划和实现一个高效、无冗余且同等性强的数据库体系。
第六章：数据库的完备性
6.1 基本完备性概念
关系模型中的完备性
在特定应用的数据库筹划中有许多完备性约束
1.怎样在数据库中界说完备性约束
- 实体完备性约束：确保每个实体有一个唯一的标识符，通常是主键。
- 参照完备性约束：确保外键值在引用的表中存在，保持数据的同等性和完备性。
- 用户界说的完备性约束：根据特定应用需求界说的其他约束，如特定字段的值范围、格式等。
2.完备性约束怎样影响数据库
- 完备性约束是数据库管理体系 (DBMS) 自动维护数据正确性和同等性的重要机制。
- 界说的完备性约束规则可以自动检查和强制执行，如果有操作违背了这些约束，DBMS 会拒绝执行这些操作，从而保护数据库的完备性。
- 完备性：确保在任何时间和任何情况下数据的正确性和同等性。
- 引起完备性标题的原因：
- 怎样确保数据库的完备性：
- 界说一组完备性约束规则（通过 SQL-DDL）
- 数据库管理体系 (DBMS) 会自动检查完备性约束规则，如果违背规则则不执行更新操作。
数据库完备性的控制机制
- 完备性约束的界说：数据模型的一个组成部分；约束数据的语义；存储在数据库中，作为模式的一部分。
- 完备性检查：检查操作要求是否违背完备性约束。
- 违约反应：如果操作要求违背完备性约束，则 DBMS 应采取步伐保护数据的完备性。
完备性检查是基于完备性约束进行的，因此完备性约束的界说是数据库完备性控制机制的焦点。
6.2 完备性约束的分类
6.2.1 按约束对象的粒度分类
1.列级约束：
2.元组级约束：
3.关系级约束：
6.2.2 按约束状态分类
1.静态约束：
2.动态约束：
动态约束的例子
- 婚姻状态的动态约束：
- 只身 → 已婚
- 已婚 → 鳏寡
- 已婚 → 离婚
- 鳏寡 → 再婚
- 只身 → 离婚（不允许）
- 离婚 → 鳏寡（不允许）
6.2.3 静态列约束
- 数据范例约束：列的数据范例、长度、单元等。
- 数据格式约束：比方学生编号的格式、日期的格式等。
- 值范围约束：比方年事范围、性别范围、效果范围等。
- 空值约束：是否允许空值。
- 其他约束：比方列的排序描述等。
6.2.4 静态元组约束
- 元组级约束：单个元组内列之间的关系约束。
- 比方：交货数量 <= 订单数量，在订单关系中。
- 比方：传授的工资 >= 3600，在西席关系中。
6.2.5 静态关系约束
- 关系级约束：元组之间或关系之间的约束。
- 实体完备性约束
- 参照完备性约束
- 函数依赖
- 统计约束
- 比方：2*员工的匀称工资 <= 经理的工资 <= 5*员工的匀称工资
6.2.6 动态列约束
- 动态列约束：修改列界说或值时的约束。
- 修改列界说的约束：
- 比方：当将一列的空值标志从“允许”改为“不允许”时，如果该列具有空值，则拒绝修改。
- 修改值的约束：
6.2.7 动态元组约束
- 动态元组约束：修改元组值时，单个元组内不同属性应满足的约束。
- 比方：当调整工资时，新的工资 >= 旧工资 + 1.5 * 工作年限。
6.2.7 动态关系约束
- 动态关系约束：关系在从一个状态变为另一个状态时应满足的约束。
6.3 完备性约束的表示方法
6.3.1 完备性约束的五元组表示法
完备性约束可以表示为五元组：( {D, O, A, C, P} )
- D（Data）：受约束作用的数据对象，可以是列、元组或关系。
- O（Operation）：触发约束检查的数据库操作，如插入、更新或删除。
- A（Assertion）：数据对象应满足的断言或语义约束。
- C（Condition）：为检查断言所预测的选定命据对象条件。
- P（Procedure）：如果约束被违背时触发的过程。
示例 01
约束：“学生编号不能为空” 在学生表中。
- D（Data）：学生编号（Sno）【属性】
- O（Operation）：插入或更新
- A（Assertion）：Sno 不能为空
- C（Condition）：对所有元组的 Sno 为空
- P（Procedure）：拒绝执行该操作（插入或更新）
示例 02
约束：“传授的工资不能低于 3600” 在西席表中。
- D（Data）：工资【属性】
- O（Operation）：插入或更新
- A（Assertion）：工资不能低于 3600
- C（Condition）：职称为“传授”
- P（Procedure）：拒绝执行该操作（插入或更新）
6.4 完备性约束的实现
6.4.1 DBMS 提供的功能
- DBMS 提供界说和检查实体完备性约束、参照完备性约束和用户界说的完备性约束的功能。
- 违背实体完备性约束和用户界说完备性约束的数据库操作会被拒绝。
- 违背参照完备性约束的数据库操作可以被拒绝，也可以执行操作并添加一些额外的操作以确保数据库的有用性。
6.4.2 参照完备性约束的实现
数据库“员工-部门”包括两个关系：EMP 和 DEPT
1.“Deptno”是 DEPT 的主键
2.EMP 的主键是“Empno”，Deptno 是外键
3.参照关系：EMP；被参照关系：DEPT
需要考虑的四种情况
1.外键是否允许为空
2.在被参照关系中删除元组
3.在参照关系中插入元组
4.更新主键的值
6.4.2.1 外键是否允许为空
- 取决于应用情况的语义
- 示例 1：
- 关系“Dept”和“Student”
- Dept 是被参照关系，其主键为 Dno
- Student 是参照关系，其主键为 Sno，外键为 Dno
- Dno 是否允许为空
- 示例 2：
- 关系“Student”和“SC”
- Student 是被参照关系，其主键为 Sno
- SC 是参照关系，其外键为 Sno
- Sno 是否允许为空
6.4.2.2 在被参照关系中删除元组
- 违背情况：当要删除被参照关系中的元组时，参照关系中存在一些元组，其外键值即是被删除元组中的主键值。
- 违约处置惩罚：根据应用情况，三种计谋
1.级联删除：删除被参照关系中的元组及其在参照关系中的对应元组。
2.受限删除：体系拒绝删除被参照关系中的元组。
3.置空删除：删除被参照关系中的元组，并将参照关系中对应元组的外键值设为空。
6.4.2.3 在参照关系中插入元组
- 违背情况：当插入参照关系中的元组时，相应的元组在被参照关系中不存在。
- 违约处置惩罚：根据应用情况，两个计谋
1.受限插入：体系拒绝将元组插入参照关系中。
2.递归插入：起首插入被参照关系中的相应元组，然后再将元组插入参照关系中。
6.4.2.4 更新主键的值
- 违背情况：如果被参照关系中主键值发生变化，但参照关系中存在一些元组，其外键值即是被参照关系中旧的主键值。
- 违约处置惩罚：根据应用情况，三种计谋
1.级联更新：更新被参照关系中的主键值，并将参照关系中对应元组的外键值也进行相应更新。
2.受限更新：体系拒绝更改被参照关系中的主键值。
3.置空更新：更新被参照关系中的主键值，并将参照关系中对应元组的外键值设为空。
例子：主键值的更新
- 例子：将 Student 表中 Sno 从 '950001' 改为 '960123'，并且 SC 表中有 4 个元组的 Sno 为 '950001'。
1.级联更新：更新 SC 表中元组的 Sno 为 '960123'。
2.受限更新：体系拒绝更改 Student 表中的 Sno。
3.置空更新：将 Student 表中的 Sno 更新为 '960123'，并将 SC 表中对应元组的 Sno 设为空。
通过这些方法和计谋，可以确保在执行数据库操作时，参照完备性约束得以维持，从而保持数据的同等性和完备性。
好的，接下来我将对这些幻灯片的内容进行翻译并讲解。内容涉及SQL完备性约束和触发器的相关知识。我们按照每页进行讲解。
6.5 SQL语句用于完备性约束
SQL-DDL 提供了多种界说完备性约束的方法，包括如下的“创建表”语句：
CREATE TABLE tablename
(
colname datatype [DEFAULT {default value | NULL}] [Constraint {col_constr...}],
colname datatype [DEFAULT {default value | NULL}] [Constraint {col_constr...}],
...
table_constr
);
6.5.1 列约束
列约束（col_constr）：单列上的完备性约束
{ NOT NULL | CONSTRAINT constraintname { UNIQUE | PRIMARY KEY | CHECK (search_cond) | REFERENCES tablename [(colname)] [ON DELETE CASCADE] }}
列约束是在单个列上施加的限定条件。常见的列约束包括：
- NOT NULL：确保列不能包罗NULL值。
- UNIQUE：确保列中的所有值都是唯一的。
- PRIMARY KEY：界说列为主键，主键值必须唯一且不能为NULL。
- CHECK：界说列值必须满足的条件。
- REFERENCES：界说外键约束，确保列值存在于另一张表的特定列中，ON DELETE CASCADE表示当引用的行被删除时，当前行也会被删除。
Create Table Student
(
Sno char(8) primary key,
Sname char(10),
Gender char(2) constraint ctsGender check (Gender='M' or Gender='F'),
Age int check (Age>=12 and Age<=50),
Dno char(2) references Dept(Dno) on delete cascade,
Class char(6)
);
Create Table Course
(
Cno char(3) primary key,
Cname char(12),
Hours int,
Credit float(1) constraint ctcredit check (Credit>=0.0 and Credit<=5.0),
Tno char(3) references Teacher(Tno) on delete cascade
);
这里展示了怎样在创建表时利用列约束。Student表和Course表的示例展示了各种列约束的应用：
- Sno和Cno界说为主键。
- Gender列利用CHECK约束限定只能是'M'或'F'。
- Age列利用CHECK约束限定在12到50之间。
- Dno和Tno列利用REFERENCES约束引用另一个表，并界说了ON DELETE CASCADE行为。
6.5.2 表约束
表约束（table_constr）：表级别的完备性约束
[CONSTRAINT constraintname]
{
UNIQUE (colname {, colname...}),
PRIMARY KEY (colname {, colname...}),
CHECK (search_condition),
FOREIGN KEY (colname {, colname...}) REFERENCES tablename [(colname {, colname...})] [ON DELETE CASCADE]
}
表约束是在整个表级别施加的限定条件，常见的表约束包括：
- UNIQUE：确保指定的多列组合唯一。
- PRIMARY KEY：界说多列组合为主键。
- CHECK：界说表级别的条件限定。
- FOREIGN KEY：界说多列组合为外键，并且可以引用另一张表的多列组合，ON DELETE CASCADE表示当引用的行被删除时，当前表中的相关行也会被删除。
Create Table Student
(
Sno char(8),
Sname char(10),
Gender char(2) constraint ctsGender check (Gender='M' or Gender='F'),
Age int check (Age>=12 and Age<=50),
Dno char(2) references Dept(Dno) on delete cascade,
Class char(6),
primary key(Sno)
);
Create Table Course
(
Cno char(3),
Cname char(12),
Hours int,
Credit float(1) constraint ctcredit check (Credit>=0.0 and Credit<=5.0),
Tno char(3) references Teacher(Tno) on delete cascade,
primary key(Cno),
constraint ctcc check (Hours*Credit = 20)
);
这个示例展示了怎样在表级别利用约束条件。与列级别约束不同，表级别约束可以同时涉及多个列，比方：
- Student表的主键界说在表级别。
- Course表的CHECK约束ctcc界说了多列之间的条件，确保Hours乘以Credit即是20。
6.5.3复合约束示例
6.5.3.1 表约束（table_constr）：复合约束示例
Create Table SC
(
Sno char(8),
Cno char(3),
Score float(1) constraint ctscore check (Score>=0.0 and Score<=100.0),
primary key (Sno, Cno),
foreign key (Sno) references Student(Sno) on delete cascade,
foreign key (Cno) references Course(Cno) on delete cascade
);
这个示例展示了一个包罗复合主键和多个外键的表SC。该表：
- 界说了主键为Sno和Cno的组合。
- 利用外键约束分别引用Student和Course表，并且界说了ON DELETE CASCADE行为，确保数据同等性。
6.5.3.2 修改和删除约束
修改和删除约束
列约束和表约束可以利用Alter table语句添加、修改和删除。
ALTER TABLE tblname
[ADD ({colname datatype [DEFAULT {default_const | NULL}] [col_constr {col_constr...} | table_constr] {colname...}})]
[DROP {COLUMN colname | (colname {, colname...})}]
[ALTER COLUMN colname datatype [DEFAULT {default_const | NULL}] [NOT NULL] [col_constr {col_constr...} | table_constr]]
[ADD CONSTRAINT constr_name]
[DROP CONSTRAINT constr_name]
利用ALTER TABLE语句，我们可以在表创建后对表的结构进行修改，包括添加、删除和修改列约束和表约束。比方，可以添加新的列，删除现有的列，修改列的数据范例和约束条件，还可以添加和删除表级别的约束。
示例
修改和删除约束：示例
Alter Table SC
DROP CONSTRAINT ctscore;
Alter Table SC
ALTER COLUMN Score float(1) constraint ctscore check (Score>=1.0 and Score<=150.0);
这个示例展示了怎样利用ALTER TABLE语句删除和修改约束。在第一个语句中，删除了SC表上的ctscore约束。在第二个语句中，修改了Score列的数据范例，并重新界说了ctscore约束，使其范围变为1.0到150.0。
6.6 触发器概述
6.6.1 触发器用于动态完备性约束
-- 为什么我们需要触发器
典型应用：银行ATM体系
（标题：张三的账户余额没有自动变化）
触发器是一种特别的存储过程，它在某个事件（如插入、更新或删除）发生时自动执行。这里以银行ATM体系为例，当用户进行存取款操作时，账户余额需要自动更新。通过触发器，我们可以自动执行相关操作，确保数据的动态同等性。
- “创建表”用于界说静态完备性约束，触发器用于动态完备性约束。
- 触发器是一种由体系自动执行的语句，通常用于插入、更新和删除操作。
- 触发器是一个事务，它可以回滚。
要筹划一个触发器机制，我们必须：
触发器用于在数据操作发生时自动执行特定的操作。与静态完备性约束不同，触发器可以处置惩罚更复杂的逻辑。筹划触发器时，需要明确触发器触发的条件以及详细执行的操作，以确保数据的同等性和完备性。
6.6.2 触发器范例
触发器范例：
当在一个表上执行插入、删除、更新操作时，DBMS将自动执行触发器语句，并确保数据操作符合触发器的规则。
触发器根据触发的事件范例分为插入触发器、删除触发器和更新触发器。当相关操作发生时，DBMS会自动执行预界说的触发器逻辑，以确保数据操作的规则和约束得到遵守。
6.6.3 插入表和删除表
触发器用于动态完备性约束
-- 插入表和删除表
当触发器执行时，体系将在内存中自动创建插入表和/或删除表。
- 插入表是只读的，不能被更新。
- 插入表和删除表将在触发器完成时自动删除。
插入表：
- 在INSERT或UPDATE操作后存储暂时记录/元组。
删除表：
- 在DELETE或UPDATE操作前存储暂时记录/元组。
当触发器执行时，DBMS会创建暂时的插入表和删除表，用于存储相关的数据变更。插入表存储新插入的数据，而删除表存储将被删除的数据。这些表是只读的，并且在触发器执行完毕后会自动删除。
触发器用于动态完备性约束
-- 插入表和删除表
存储在插入表和删除表中的信息
| 操作
| 插入表
| 删除表
| | INSERT
| 存储新插入的记录
| -----
| | DELETE
| -----
| 存储删除前的记录
| | UPDATE
| 存储更新操作后的记录
| 存储更新操作前的记录
|
这张表详细展示了在不同操作下插入表和删除表中存储的信息。在插入操作中，插入表存储新插入的记录；在删除操作中，删除表存储将被删除的记录；在更新操作中，插入表存储更新后的记录，而删除表存储更新前的记录。
6.6.4 创建触发器语句
触发器用于动态完备性约束
-- 创建触发器语句
CREATE TRIGGER trigger_name
ON table_name
[WITH ENCRYPTION]
FOR {DELETE, INSERT, UPDATE}
AS
T-SQL Statement
GO
WITH ENCRYPTION：用于加密触发器界说的SQL文本。
这展示了怎样创建触发器。CREATE TRIGGER语句用于界说触发器，包括触发器名称、作用的表、触发的事件范例（DELETE, INSERT, UPDATE）以及触发后执行的T-SQL语句。WITH ENCRYPTION选项用于加密触发器的界说。
6.6.5 插入触发器的工作原理
-- 插入触发器工作原理
1.执行INSERT语句，插入新记录。
2.执行插入触发器，将新记录从transInfo表插入到inserted表。
3.触发器检查inserted表中的新记录，并决定回滚或执行相关操作。
这表明了插入触发器的工作流程。当执行INSERT语句时，新记录起首插入到transInfo表。然后，插入触发器将新记录复制到暂时的inserted表，并对新记录进行检查，决定是否回滚或执行其他操作。
-- 插入触发器示例
[需求] 为办理银行ATM体系中的标题，当向transInfo表插入新记录时，应自动更新bank表中的currentMoney值。
[分析]
1.在transInfo表上创建INSERT触发器。
2.从插入的暂时表中获取新记录的副本。
3.检查交易范例。
4.根据交易范例更新bank表中的currentMoney值。
这个示例详细表明了怎样筹划和实现一个插入触发器，以办理银行ATM体系中的自动更新标题。详细步骤包括创建触发器，获取新插入的数据，检查交易范例，并根据交易范例更新账户余额。
CREATE TRIGGER trig_transInfo
ON transInfo
FOR INSERT AS
DECLARE @type char(4), @transMoney MONEY
DECLARE @myCardID char(10)
SELECT @type=transType, @transMoney=transMoney, @myCardID=cardID FROM inserted
IF (@type='withdraw')
UPDATE bank SET currentMoney=currentMoney-@transMoney WHERE cardID=@myCardID
ELSE
UPDATE bank SET currentMoney=currentMoney+@transMoney WHERE cardID=@myCardID
GO
这个示例展示了一个完备的插入触发器代码。当在transInfo表上发生插入操作时，触发器会执行以下操作：
1.获取插入记录的交易范例、金额和卡号。
2.如果交易范例为提款，更新bank表，减少对应账户的余额。
3.如果交易范例为存款，更新bank表，增加对应账户的余额。
通过这个触发器，可以实现自动更新账户余额的功能，确保数据的同等性和准确性。
好的，继承对这些幻灯片内容进行翻译和讲解，主要讲述的是删除触发器和更新触发器的内容。我们按页进行讲解。
6.6.6 删除触发器的工作原理
-- 删除触发器
工作原理：
1.执行DELETE语句，删除一条记录。
2.执行删除触发器，将从transInfo表删除的记录插入到deleted表中。
3.触发器将检查deleted表中的已删除记录，并决定回滚或执行其他相关操作。
删除触发器用于在记录从表中删除时自动执行一些操作。比方，在删除记录时，触发器会将该记录复制到暂时的deleted表中，以便后续检查或恢复操作。这可以确保在删除操作中数据的同等性和完备性。
[需求] 当从transInfo表中删除记录时，应将已删除的记录备份到bk_transInfo表中。
[分析]
1.在transInfo表上创建DELETE触发器。
2.获取已删除记录的副本。
这个示例展示了删除触发器的应用。当从transInfo表中删除记录时，触发器会将删除的记录备份到另一个表中。这是一种常见的数据保护步伐，防止重要数据在删除时丢失。
CREATE TRIGGER trig_delete_transInfo
ON transInfo
FOR DELETE AS
BEGIN
PRINT 'Start to backup data, please wait....';
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='bk_transInfo')
SELECT * INTO bk_transInfo FROM deleted;
ELSE
INSERT INTO bk_transInfo SELECT * FROM deleted;
PRINT 'Backup data complete, the data in backup table is as following:';
SELECT * FROM bk_transInfo;
END
GO
这个触发器代码示例展示了怎样在删除操作时备份数据。触发器起首检查bk_transInfo表是否存在，如果不存在，则创建该表并插入已删除的记录。如果存在，则直接插入删除的记录。这样可以确保每次删除操作的记录都被备份。
6.6.7 更新触发器的工作原理
工作原理：
1.执行UPDATE语句。
2.执行更新触发器，将旧记录插入到deleted表中，将新记录插入到inserted表中。
3.触发器将检查旧记录和新记录，然后决定回滚或执行其他相关操作。
更新触发器在更新操作发生时自动执行。触发器会将更新前的旧记录保存到deleted表中，并将更新后的新记录保存到inserted表中。这种机制可以帮助追踪数据的变化，确保更新操作的完备性和同等性。
[需求] 追踪客户的交易，如果交易金额超过20000，则取消交易并给出通知。
[分析]
1.在bank表上创建UPDATE触发器。
2.获取旧记录的副本。
3.获取新记录的副本。
这个示例展示了更新触发器的现实应用。在银行体系中，如果客户的交易金额超过20000，触发器会自动取消交易并通知客户。这有助于防止大额错误交易，保护客户的资金安全。
CREATE TRIGGER trig_update_bank
ON bank
FOR UPDATE AS
BEGIN
DECLARE @beforeMoney MONEY, @afterMoney MONEY;
SELECT @beforeMoney=currentMoney FROM deleted;
SELECT @afterMoney=currentMoney FROM inserted;
IF ABS(@afterMoney - @beforeMoney) > 20000
BEGIN
PRINT 'Transaction Amount: ' + CONVERT(varchar(8), ABS(@afterMoney - @beforeMoney));
RAISERROR ('Each transaction will not be more than 20000, Transaction failed', 16, 1);
ROLLBACK TRANSACTION;
END
END
GO
这个代码示例展示了一个更新触发器，当交易金额超过20000时自动取消交易。触发器起首获取更新前后的金额，如果差额超过20000，则触发错误并回滚事务。这种方式有用地控制了单笔交易的最大金额。
6.6.8 列更新触发器
更新触发器还可以用于检查某一列的值是否被更新。
[需求] 交易日期由体系创建，且设置为当前日期时间，克制被更新。
[分析]
利用体系函数UPDATE(column_name)来测试column_name是否被更新。
列更新触发器用于确保某些特定列的值不能被更改。比方，交易日期通常由体系生成，不允许用户修改。通过列更新触发器，可以检测到任何对交易日期的修改，并采取相应的步伐。
CREATE TRIGGER trig_update_transInfo
ON transInfo
FOR UPDATE AS
BEGIN
IF UPDATE(transDate)
BEGIN
PRINT 'Transaction failed ....';
RAISERROR ('Security Alert: Transaction datetime cannot be updated', 16, 1);
ROLLBACK TRANSACTION;
END
END
GO
这个代码示例展示了怎样利用列更新触发器来防止交易日期被修改。触发器检测到transDate列被更新时，会触发错误并回滚事务，从而确保交易日期保持不变。
第七章：数据库安全
7.1 安全性的基本概念
7.1.1 标题的提出
- 数据库的一个重要特点是数据可以共享。
- 但是数据共享带来了安全标题。
- 数据库体系内的数据共享不能是无条件的共享。
例子:
- 戎机
- 国家机密
- 新产品实行数据
- 市场需求分析
- 营销计谋
- 销售方案
- 客户档案
- 医疗档案
- 银行数据等
7.1.2 非法利用数据库的方法:
- 用户编写合法程序绕过数据库管理体系，通过操作体系直接访问、修改或备份数据库中的数据。
- 编写应用程序执行非授权操作。
- 通过合法多次查询数据库推断出某些秘密数据。
安全性:
7.1.3 盘算机体系安全标题的范例
1.技术安全:
- 盘算机体系利用某些安全硬件和软件来保护其体系和数据。在攻击下体系正常运行且不丢失数据。
2.管理安全:
- 通过意外的软件或硬件故障、意外事故或不良管理造成的盘算机设备和数据介质的物理损害。
3.政策法律:
- 政府应创建关于盘算机犯罪和数据安全的政策和道德标准。
7.1.4 安全控制模型
7.2 访问控制（DAC 和 MAC）
访问控制:
- 数据库安全关注数据库管理体系的访问控制机制。数据安全的最重要一点是确保只有合格的用户才能访问数据库。
访问控制机制的组成:
- 界说访问权限:
- 在数据库体系中，确保用户只能访问其有权限访问的数据，必须为每个用户预先界说访问权限。
- 检察访问权限:
- 对合法用户，体系根据其访问权限控制其请求，确保其只能执行合法操作。
常用方法:
- 自主访问控制 (DAC):
- 同一用户对不同数据对象有不同的访问权限。
- 不同用户对同一对象有不同的权限。
- 用户还可以将权限转移给其他用户。
- 强制访问控制 (MAC):
- 每个数据对象都被标志为某种保密级别。
- 每个用户也被赋予一定的权限级别。
- 只有具有合法允许证的用户才能访问对象。
7.2.1 自主访问控制（DAC）
访问规则:
- 访问规则的界说包括：
- 用户 (S)
- 对象（授权粒度）(O)
- 访问权限 (t)
- 谓词 (P)
- 访问规则通常存储在数据字典中。
授权粒度:
访问权限有不同级别:
- 级别 1: 选择
- 级别 2: 修改
- 级别 3: 创建
- 高级权限自动包括低级权限（级别 3 > 级别 2 > 级别 1）。
示例:
- 员工（工号、姓名、生日、性别、薪水、部门号、部门负责人）有如下访问需求：
- 体系管理员：可以访问数据库中的所有数据，拥有所有权限。
- 保安：可以查询所有员工的“姓名”和“部门号”。
- 雇主：可以查询本身的所有数据。
- 部门经理：可以查询其部门内所有员工的数据。
- 高级经理：可以查询所有数据。
访问规则:
- 员工（工号、姓名、生日、性别、薪水、部门号、部门负责人）的访问规则如下：
- 体系管理员：读取、删除、插入、更新所有数据。
- 保安：读取“姓名”和“部门号”。
- 雇主：读取本身的数据。
- 部门经理：读取部门内员工的数据。
- 高级经理：读取所有数据。
在 SQL 中的授权下令:
- 授予所有权限或指定权限：
- GRANT {all PRIVILEGES | privilege [,privilege...]} ON 表名 | 视图名 TO {public | user-id [, user-id...]} [WITH GRANT OPTION]；
- WITH GRANT OPTION：将权限转移给他人。
实例：
- 用户A: 授予用户B在员工表上的选择权限，并允许转授。
- 用户A: 授予用户E在员工表上的选择权限，并允许转授。
- 用户B: 授予用户C在员工表上的选择权限。
- 用户E: 授予用户C在员工表上的选择权限。
在 SQL 中的取消下令
REVOKE {all privileges | priv [, priv...]}
ON tablename | viewname
FROM {public | user [, user...]};
示例:
- UserA: 取消 UserB 对 employee 表的选择权限；
- DBA → UserA → UserB → UserC；
- UserA → UserE。
7.2.2 强制访问控制 (MAC)
- 对于更高的安全性，体系根据 TDI/TCSEC 安全计谋要求利用强制访问方法。
- 用户不能感知或控制 MAC。
- MAC 适用于对数据有严格分类的部门：
主体与客体:
- 在 MAC 中，所有实体分为主体和客体。
- 主体：体系中的运动实体，包括现实利用数据库管理体系的用户和用户的线程。
- 客体：体系中的被动实体，由主体控制，包括文档、基本表、索引和视图。
敏感度标签:
- 数据库管理体系为每个主体和客体分配敏感度标签。
- 几个级别：绝密、机密、秘密、公开。
- 主体的敏感度标签称为允许级别，客体的敏感度标签称为分类级别。
- 通过比较主体和客体的标签，MAC 机制确认主体是否可以访问客体。
MAC 规则:
- 当用户（或主体）登录体系时，体系要求其在访问客体时遵循以下两条规则：
1.只有当主体的允许级别大于或即是客体的分类级别时，主体才能读取客体。
2.只有当主体的允许级别小于或即是客体的分类级别时，主体才能写入客体。
MAC 实现:
- 扩展关系模式：
- R (A1: D1, A2: D2, ..., An: Dn)
- R (A1: D1, C1, A2: D2, C2, ..., An: Dn, Cn, TC)
- C1, C2, Cn: 属性的敏感度级别
- TC: 元组的敏感度级别
示例:
[table] P#
C
Pname
C
Psalary
C
TC
Emp001
S
张三
U
10,000
S
S
Emp002
S
李四
U
8,000
S
C
Emp003
S
王五
U
4,000
C
C
Emp004
S
李六
U
2,000
C
C
Emp005
U
张四
U
1,000
S
U
7.3 视图和审计
- 视图
- 审计：
- 利用专用审计日记记录用户在数据库中的所有操作。
- 数据库管理员可以利用审计日记中的追踪信息找到非法访问数据的用户。
- 审计需要大量时间和存储空间。
7.4 统计数据库安全
统计数据库的特性:
示例:
- “程序员的匀称工资”是允许查询的。
- “程序员李强的工资”是不允许查询的。
标题:
- 怎样在利用数据进行统计的同时确保个人隐私（合法查询推导出非法信息）。
示例 1: 以下两个查询是合法的
- 公司有多少女性高级程序员？
- 女性高级程序员的总工资是多少？
标题:
- 如果第一个查询的效果是1，那么第二个查询的效果就是程序员的工资。
办理方案 1:
示例 2: 用户A合法查询如下:
- 用户A和其他N个程序员的总工资是多少？
- 用户B和其他N个程序员的总工资是多少？
标题:
- 如果第一个效果是X，第二个是Y，因为用户A知道他的工资是Z，那么他知道用户B的工资是Y-(X-Z)。
办理方案 2:
办理方案 3:
- 根据办理方案1和办理方案2，A想知道B的工资，他必须查询1+(N-2)/M次。
- 任意用户的查询次数不能超过1+(N-2)/M次。
- 但如果两个用户合作，这个办理方案将无效。
第八章：事务与并发控制
8.1 事务的基本概念
- 操作聚集形成一个单一逻辑工作单元称为事务。
- 示例：从账户A向账户B转账$50:
1.读取(A)
2.A := A - 50
3.写入(A)
4.读取(B)
5.B := B + 50
6.写入(B)
- 需要处置惩罚的两个主要标题:
- 各种故障，比方硬件故障和体系瓦解。
- 多个事务的并发执行。
8.1.1 事务的必要属性
示例：从账户A向账户B转账$50:
1.读取(A)
2.A := A - 50
3.写入(A)
4.读取(B)
5.B := B + 50
6.写入(B)
- 原子性要求:
- 如果在步骤3之后和步骤6之前事务失败，钱将“丢失”，导致数据库状态不同等。
- 故障可能是由于软件或硬件标题。
- 体系应确保部分执行的事务更新不会反映在数据库中。
- 持久性要求:
- 一旦用户被通知事务已完成（即$50的转账已发生），事务对数据库的更新必须持久存在，纵然存在软件或硬件故障。
- 同等性要求:
- 在上述示例中，A和B的总和不变。
- 一样平常来说，同等性要求包括:
- 显式指定的完备性约束，比方主键和外键。
- 隐式完备性约束，比方所有账户的总和减去贷款总额必须小于现金值。
- 当事务成功完成时，数据库必须同等。
- 错误的事务逻辑可能导致不同等。
- 隔离性要求:
- 如果在步骤3和步骤6之间允许另一个事务T2访问部分更新的数据库，它将看到不同等的数据库（A和B的总和将小于预期）。
- 隔离性可以通过串行运行事务来保证，即一个接一个地运行。
然而，并发执行多个事务具有显著的上风，这将在反面看到。
8.1.2 ACID 属性
- 事务是一个操作执行单元，访问并可能更新各种数据项。为了保持数据的完备性，数据库体系必须确保：
- 原子性: 事务的所有操作要么全部反映在数据库中，要么全部不反映。
- 同等性: 事务的执行保留数据库的同等性。
- 隔离性: 虽然多个事务可以并发执行，但每个事务必须对其他并发执行的事务一无所知。中间事务效果必须对其他并发执行的事务隐藏。
- 持久性: 事务成功完成后，其对数据库的更改必须持久存在，纵然出现体系故障。
8.1.3 事务状态:
- 运动状态：初始状态；事务在执行时保持这种状态。
- 部分提交：末了一个语句执行后。
- 失败：发现无法继承正常执行后。
- 中止：事务回滚后，数据库恢复到事务开始前的状态。
- 提交：事务成功完成。
SQL 中的事务示例:
Begin Transaction
exec sql ...
...
exec sql ...
exec sql commit work | exec sql rollback work
End Transaction
8.2 并发控制
8.2.1 多事务的执行
- 串行执行:
- 一次只运行一个事务，其他事务必须期待该事务结束。
- 不能完全共享体系资源。
- 交叉并发:
- 并发运行事务是将这些并发事务的并行操作交错进行。
- 单处置惩罚器的并发性可以减少处置惩罚器空闲时间，提高体系服从。
- 同时并发:
- 在多处置惩罚器体系中，因为每个处置惩罚器可以运行一个事务，所以可以同时执行多个事务。
- 完美的并发性，但受硬件限定。
- 更复杂的并发控制机制。
8.2.2 并发执行
- 允许多个事务在体系中并发运行。上风包括：
- 增加处置惩罚器和磁盘利用率，导致更好的事务吞吐量。比方，一个事务可以利用CPU，而另一个事务正在从磁盘读取或写入。
- 减少事务的匀称响应时间：短事务不必在长事务反面期待。
- 标题：隔离性可能不再得到保证。
- DBMS必须提供并发控制方案。
- 并发控制方案是DBMS性能的重要标志。
- 并发控制方案：实现隔离性的机制。即控制并发事务之间的相互作用，防止其粉碎数据库的同等性。
8.2.3 并发控制方案
- 并发控制方案的任务：
- 数据不同等的一个例子：
- 并发操作中的数据不同等性：
8.2.3.1 丢失更新
- 如果事务T1和事务T2读取相同的数据并修改它们。由于事务T2粉碎了事务T1的效果，事务T1的修改被丢失。
- 数据不同等的范例：
8.2.3.2 不可重复读
- 事务T2在事务T1读取数据后执行更新操作，因此事务T1无法获得相同的查询效果。
- 示例：
- T1: 读取(A)=50，读取(B)=100，总和(A+B)=150
- T2: 读取(B)=100，B:= B*2，写入(B)=200
- T1: 读取(A)=50，读取(B)=200，总和(A+B)=250
三种不可重复读
- 事务T1读取一些数据后，事务T2进行了以下操作：
- 修改这些数据，因此事务T1在下一次读取时获得不同的值。
- 删除这些数据，因此事务T1在下一次读取时找不到它们。
- 插入一些记录，因此事务T1发现了更多的记录。
- 后两种情况也称为幻影。
8.2.3.3 脏读
- 事务T1修改一些数据并写回。然后事务T2读取相同的数据。事务T1回滚，数据恢复到原来的值。因此，事务T2读取了错误的数据。
示例：
- T1读取(C)=100
T1修改(C)=200
- T2读取(C)=200
- T1回滚
T2读取(C)=100
并发调度的可串行化:
- 基本假设：每个事务保持数据库的同等性。
- 因此，串行执行一组事务保持数据库的同等性。
- 如果一个（可能并发的）调度与串行调度等价，则它是可串行化的。
怎样确保并发调度的可串行化？——锁
8.3 锁机制
锁:
- 锁是一种控制并发访问数据项的机制。
- 锁请求由并发控制管理器处置惩罚。事务只有在请求被授予后才能进行操作。
8.3.1 基本锁范例
- 排它锁（X锁）：数据项可以被读取和写入。X锁通过lock-X指令请求。
- 共享锁（S锁）：数据项只能被读取。S锁通过lock-S指令请求。
8.3.2 基本锁相容性矩阵
- 一个事务可以在请求的锁与其他事务已持有的锁相兼容时获得该数据项的锁。
- 矩阵允许多个事务在一个数据项上持有共享锁，但如果任何事务持有排它锁，则其他事务不能持有该数据项的任何锁。
- 如果无法授予锁，请求事务必须期待所有不兼容的锁被开释后才能获得锁。
8.3.3 基于锁的协议
- 当在数据对象上设置X锁或S锁时，有一些规则：锁协议
- 不同的锁协议在不同水平上保证并发操作的准确性。
- 常见的锁协议：三级锁协议（三级封锁协议）。
8.3.3.1 一级锁协议
- 事务T在修改数据R之前设置X锁，并在事务结束后开释锁。
- 它可以防止丢失更新。
- 在这个协议中，如果读取数据，不需要锁。因此，它不能防止不可重复读和脏读。
示例：
- T1读取(A)=16
- T2读取(A)=16
- T1修改(A)=15并写入
- T2修改(A)=15并写入
防备丢失更新。
- T1设置X锁并读取(A)=16
- T2期待
- T1修改(A)=15并提交，开释X锁
- T2读取(A)=15
防备丢失更新
- T1设置X锁并读取(A)=16
- T2读取(A)=16
- T1修改(A)=15并写入
- T1回滚，开释X锁
- T2读取(A)=15
产生脏读和不可重复读
8.3.3.2 二级锁协议
- 一级锁协议加上事务T在读取数据R之前设置S锁，并在读取后开释锁。
- 它可以防止丢失更新和脏读。
- 在这个协议中，因为读取后开释S锁，它不能防止不可重复读。
- T1设置X锁并读取(C)=100
- T1修改(C)=200并写入
- T2读取(C)=200
- T1回滚
- T2读取(C)=100
防备脏读
- T1设置S锁并读取(A)=50
- T2期待
- T1读取(B)=100并解锁
- T2设置X锁并修改(B)=200并写入，提交并解锁
- T1读取(A)=50和(B)=200，总和(A+B)=250
产生不可重复读
8.3.3.3 三级锁协议
- 一级锁协议加上事务T在读取数据R后开释锁的末尾设置S锁。
- 这个协议可以防止丢失更新、脏读和不可重复读。
- T1设置S锁并读取(A)=50
- T1读取(B)=100，总和(A+B)=150
- T2设置X锁并期待
- T1解锁S锁
- T2修改(B)=200并写入，提交并解锁X锁
- T1读取(A)=50和(B)=200，总和(A+B)=250
防备不可重复读
8.3.3.4 锁协议总结
- 三种协议的区别
- 哪些操作需要申请锁
- 哪种范例的锁
- 何时申请和开释锁
· 一级锁协议：只对修改操作加锁，防止丢失更新，但不能防止不可重复读和脏读。
· 二级锁协议：对读取操作加共享锁，防止丢失更新和脏读，但不能防止不可重复读。
· 三级锁协议：在读取数据后设置共享锁，并在末了开释锁，防止丢失更新、脏读和不可重复读。
8.3.4 饥饿与死锁
- 不幸的是，锁定可能导致不理想的情况：
- 在大多数锁协议中存在死锁的可能性。死锁是不可避免的。
- 如果并发控制管理器筹划不佳，可能发生饥饿。
- 可以筹划并发控制管理器以防止饥饿。
8.3.4.1 饥饿
- 当多个事务锁定同一个对象时，它们根据锁定序次列队。
- 第一个事务在锁开释后获得锁。
示例：
- T1锁定(R)
- T2期待
- T3期待
- T1解锁(R)
- T2锁定(R)
- T4期待
怎样防止饥饿：根据先来先服务计谋。
8.3.4.2 死锁
示例：
- T1锁定(R1)
- T2锁定(R2)
- T1期待锁定(R2)
- T2期待锁定(R1)
死锁防备:
- 如果一组事务中每个事务都在期待该组中另一个事务，则体系进入死锁状态。
- 死锁防备是消除死锁的条件。
- 死锁防备协议确保体系永世不会进入死锁状态。
一些防备计谋：
- 每个事务在开始执行之前锁定其所有数据项（一次封锁法）。
- 对所有数据项施加部分排序，并要求事务按指定序次锁定命据项（序次封锁法）。
预声明协议（一次封锁法）:
- 每个事务必须锁定其利用的所有数据，否则无法执行。
- 预声明协议的标题：
- 如果锁定所有利用的数据，则锁定范围必须扩大，从而低落并发性。
- 难以提前确认对象。
序次封锁法:
- 界说锁定序次，所有事务按此序次锁定命据项。
- 序次封锁法的标题：
结论:
- 广泛用于操作体系的死锁防备计谋不适用于数据库。
- 在数据库管理体系中通常利用死锁检测和解除。
死锁检测和解除:
- 允许死锁存在。
- 怎样办理死锁：
- 数据库管理体系的并发控制子体系检查体系中是否存在死锁。
- 一旦检测到死锁，应解除死锁。
解除方法：
超时法:
优点：
缺点：
- 判定可能堕落
- 如果限定过长，可能无法及时发现死锁。
优先图法:
- 利用优先图表示所有事务的期待状态：
- 图中的极点是事务。
- 从T1指向T2的箭头表示T1在期待T2开释锁。
- 如果图中存在循环，则出现死锁。
并发控制子体系定期检查优先图。如果图中存在循环，则体系中存在死锁。
选择本钱最低的事务解除，并开释该事务持有的所有锁，以使其他事务继承进行
并发控制的其他标题:
- 锁的粒度
- 逻辑单元：属性值、元组、关系、索引、数据库等。
- 物理单元：页面（数据页或索引页）、物理记录等。
- 并发控制的可串行化
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。 |
