【软件测评师】11数据库基础知识

#用于个人笔记整理

一、数据库技术相关术语

1、数据（Data）

数据是信息的原始形式和载体，是未经处理的、未经解释的、原始的符号或数字集合。它可以是数字、文本、图像、声音等各种形式。

数据本身并不具备意义，直到它被解释或组织成某种形式，才能成为信息。

例如，一串数字“19900101”作为数据，它本身没有太多意义，但如果我们知道这是某个人的出生日期，那么它就成为了有意义的信息。

2、信息（Information）

信息是经过处理、组织、解释后的数据，它具有一定的意义和价值，能够减少不确定性或解决某个问题。

信息是对数据的解释或呈现，使人们能够理解并据此做出决策或行动。

例如，知道某人的出生日期（信息）后，我们可以计算出他的年龄，进而了解他是否符合某项活动的年龄要求。

3、数据库（Database）

数据库是一个存储、管理和检索数据的系统，它按照一定的数据模型和结构来组织数据，以便高效、准确地访问和更新数据。

数据库可以包含大量的数据记录，这些数据记录通过关系、索引等方式相互关联，以支持复杂的数据查询和处理。

数据库管理系统（DBMS）是操作数据库的软件，它提供了创建、维护、查询和更新数据库的功能。

例如，一个学校的学生信息管理系统就是一个数据库，它存储了所有学生的姓名、学号、年龄、成绩等数据，并允许教师和管理员通过数据库管理系统来查询、修改或添加学生信息。

4、关系数据库相关术语

1.关系（Relation）

关系是数据库中存储数据的一种逻辑表示，可以看作是一个二维表，其中表的行对应于元组，列对应于属性。

2.属性（Attribute）

属性是关系中的一列，它代表了元组的一个字段或特性。每个属性都有一个名字和一个数据类型。

3.元组（Tuple）

元组是关系中的一行，代表了关系中的一个实体或记录。元组中的每个值对应于该元组在对应属性上的值。

4.分量（Component）

分量是元组中的一个值，也就是元组在某一属性上的取值。

5.关系模式（Relation Schema）

关系模式是对关系的描述，它定义了关系的结构，包括关系名、属性名、属性类型以及属性间的完整性约束等。关系模式是静态的、稳定的，而关系是关系模式在某一时刻的状态或实例。

6.候选码/候选键（Candidate Key）

候选键是能够唯一标识关系中每一个元组的属性或属性组。在一个关系中，可以有多个候选键，但每个候选键都必须满足唯一性约束。

7.主码/主键（Primary Key）

主键是从候选键中选定的一个作为关系的主要标识。在一个关系中，主键是唯一的，且不允许为空（NULL）。

8.外码/外键（Foreign Key）

外键是关系中的一个属性或属性组，它不是该关系的主键，但它是另一个关系的主键。外键用于建立两个关系之间的联系或依赖。

9.主属性/非主属性（Prime Attribute/Non-Prime Attribute）

主属性是包含在任何一个候选键中的属性。非主属性则是关系中不属于任何候选键的属性。

10.全码（All-Key）

全码是一个特殊的概念，指的是一个关系中所有的属性都是候选键。这种情况在实际应用中较为罕见，因为它要求关系中的每一个属性组合都能唯一标识一个元组，这通常意味着关系中的元组数量非常少。

二、三级模型两级映像

1、三级模式

1.外模式（External Schema）

也称子模式（Subschema）或用户模式，是数据库用户（包括应用程序员和最终用户）能够看见和使用的局部数据的逻辑结构和特征的描述。

它是与某一应用有关的数据的逻辑表示，是数据库用户的数据视图。

一个数据库可以有多个外模式，以满足不同用户或不同应用程序的需求。

2.模式（Schema）

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

它定义了数据的全局逻辑结构，包括数据记录由哪些数据项构成，数据项的名字、类型、取值范围等，以及数据之间的联系。

一个数据库只有一个模式，它位于三级模式的中间层，是数据库系统模式结构的核心。

3.内模式（Internal Schema）

也称存储模式（Storage Schema），是数据物理存储和存储方式的描述，是数据在数据库内部的组织方式。

它定义了数据的物理存储结构，包括数据的存储记录结构、索引的组织方式、数据的压缩和加密等。

一个数据库只有一个内模式，它描述了数据在数据库内部的表示方式和存储方式。

2、两级映像

1.外模式/模式映像

定义在外模式描述中，把描述局部逻辑结构的外模式与描述全局逻辑结构的模式联系起来。

当模式改变时（例如增加新的关系、新的属性或改变属性的数据类型等），只要对外模式/模式映像做出相应的改变，使外模式保持不变，则以外模式为依据的应用程序不受影响，从而保证了数据与程序之间的逻辑独立性。

2.模式/内模式映像

定义在模式描述中，把描述全局逻辑结构的模式与描述物理结构的内模式联系起来。

当数据库的物理存储结构改变时（例如选用了另一种存储结构），只要对模式/内模式映像做出相应的改变，使模式保持不变，则应用程序也不必改变，从而保证了数据与程序之间的物理独立性。

3、数据库管理系统

1.功能

数据定义（Data Definition）：

数据定义功能允许用户定义数据库中的数据结构，包括表、视图、索引等。这通常通过数据定义语言（DDL）实现，如SQL中的CREATE、ALTER和DROP语句。

数据操作（Data Manipulation）：

数据操作功能允许用户对数据库中的数据进行增、删、改、查等操作。这些操作通过数据操作语言（DML）实现，如SQL中的INSERT、UPDATE、DELETE和SELECT语句。

数据库运行管理（Database Operation Management）：

数据库运行管理涉及监控和优化数据库的性能，包括查询优化、事务管理、并发控制等。它还包括数据库的日常维护和升级。

数据组织、存储和管理（Data Organization, Storage, and Management）：

这包括如何高效地在物理存储介质上组织数据，以及如何管理数据的存储和访问。数据库管理系统（DBMS）通过提供适当的存储结构和访问方法来优化这些操作。

数据的建立和维护（Data Creation and Maintenance）：

这包括数据库的初始设置、数据备份、恢复以及数据的日常更新和维护。确保数据的准确性和一致性是这一功能的重要部分。

2.特征

数据结构化且统一管理：

数据库中的数据按照特定的数据结构存储，如关系模型中的表，这使得数据易于管理和访问。数据库管理系统提供统一的界面和工具来管理这些数据。

有较高的数据独立性：

数据独立性分为物理独立性和逻辑独立性。物理独立性指数据的物理存储结构与用户的应用程序相互独立；逻辑独立性指用户的应用程序与数据的逻辑结构（如表的结构）相互独立。这种独立性简化了应用程序的维护和数据库的修改。

数据库的安全性（Security）：

数据库的安全性涉及保护数据免受未授权访问、泄露、修改或销毁。这通常通过用户认证、访问控制、数据加密等手段实现。

数据库的完整性（Integrity）：

数据库的完整性确保存储在数据库中的数据是准确和一致的。这包括实体完整性（如主键约束）、参照完整性（如外键约束）和用户定义的完整性（如特定业务规则）。

并发控制（Concurrency Control）：

并发控制允许多个用户同时访问和修改数据库中的数据，而不会导致数据不一致或冲突。数据库管理系统通过锁机制、事务隔离级别等技术来实现并发控制。

故障恢复（Fault Recovery）：

故障恢复功能确保在数据库系统发生故障时能够恢复数据，减少数据丢失和损坏的风险。这通常通过定期备份数据、使用日志文件记录事务等操作来实现。

三、完整性约束

1、实体完整性

实体完整性是关系模型中的一项基本规则，它要求数据库表的每一行（或称为元组）在表中是唯一的，并且必须能够唯一地被识别。这通常通过主键（Primary Key）来实现，主键的值必须是唯一的，且不允许为空（NULL）。

特点：

唯一性：主键的值在表中必须是唯一的，用于唯一标识表中的每一行。

非空性：主键字段不允许有空值，确保每一行都有一个明确的标识。

作用：维护表中数据的唯一性和可识别性，防止数据冗余和冲突。

2、用户自定义完整性

用户自定义完整性是针对某一具体关系数据库的约束条件，它反映了某一具体应用所涉及的数据必须满足的语义要求。这些约束条件可以是非空约束、唯一约束、检查约束等，用于限制表中数据的取值范围和格式。

特点：

针对性：根据具体的应用场景和需求来定义约束条件。

灵活性：可以涵盖实体完整性和参照完整性之外的其他约束条件。

作用：确保数据库中存储的数据符合特定的业务规则和数据要求。

3、参照完整性/引用完整性

参照完整性（也称为引用完整性）是关系模型中描述实体之间联系的一种完整性约束。它要求关系中的外键要么为空，要么必须引用另一个关系的主键的有效值。这确保了表与表之间的数据一致性。

特点：

表间关系：涉及两个或多个表之间的关联关系。

外键约束：通过外键字段来实现，外键的值必须对应于另一个表的主键的有效值或为空。

作用：维护表间数据的一致性和完整性，防止孤立记录和无效数据的产生。

4、分布式数据库的特征

1. 分片透明

定义：分片透明是分布式数据库系统提供的最高层次的透明性。它意味着用户或应用程序不需要知道逻辑上访问的表具体是如何分块存储的，即数据的分片对用户是透明的。

特点：

用户只需对全局关系进行操作，而不必关心数据的分片及存储场地。

当分片模式改变时，只需改变全局概念模式到分片模式之间的映像，而不会影响到全局概念模式和应用程序。

2. 复制透明

定义：复制透明是指采用复制技术的分布方法中，用户不需要知道数据是复制到哪些节点，以及如何复制的。这种透明性确保了数据的可靠性和可用性，同时简化了用户操作。

特点：

被复制的数据的更新都由系统自动完成，用户无需关心复制的细节。

提高了系统的容错能力和性能，因为数据可以在多个节点上同时访问和更新。

3. 位置透明

定义：位置透明是指用户或应用程序不必知道所操作的数据具体存放在哪个或哪些节点上。这种透明性使得数据的物理位置对用户来说是透明的。

特点：

用户编写的应用程序无需关心数据的存储位置。

当存储场地发生变化时，只需改变分片模式到分配模式之间的映像，而不会影响分片模式、全局概念模式和应用程序。

4. 局部映像透明性（逻辑透明）

定义：局部映像透明性，也称为逻辑透明，是分布式数据库系统提供的最低层次的透明性。它是指用户或应用程序无需知道局部场地使用的是哪种数据模型和数据操纵语言，这些转换工作由系统自动完成。

特点：

用户编写的应用程序不必关心各节点上数据库的数据模型和使用的数据操纵语言。

数据模型和操纵语言的转换是由系统完成的，这简化了用户操作并提高了系统的灵活性。

5、数据库管理员DBA

1.决定数据库中信息内容和结构

2.决定数据库的存储结构和存取策略

3.定义数据库的安全性要求和完整性约束条件

4.监控数据库的使用和运行

5.数据库的性能改进、数据库的重组和重构，以提高系统的性能

四、数据库设计

1、用户需求分析

用户需求分析是数据库设计的第一步，也是最重要的一步。它旨在准确了解并分析用户对系统的需求，包括数据和处理两方面的需求。

主要任务：

收集资料：通过调查、访谈、问卷等方式，收集用户关于数据库系统的需求信息。

分析整理：对所收集到的需求信息进行整理和分析，明确用户的信息要求、处理要求、安全性与完整性要求等。

数据流图：使用数据流图（DFD）来描述系统的功能，展示数据在系统内的流动和处理过程。

数据字典：建立数据字典（DD），详细描述数据库中的数据项、数据结构、数据流、数据存储和处理过程等。

用户确认：将需求分析的结果返回给用户，通过反复沟通，确保需求分析结果准确反映用户的实际要求。

重要性：

需求分析的结果将直接影响到后续的设计工作，包括概念结构设计、逻辑结构设计和物理结构设计等。因此，需求分析必须做到充分、准确和详细。

2、概念结构设计

概念结构设计是对用户需求进行综合、归纳与抽象，形成一个独立于具体数据库管理系统（DBMS）的概念模型的过程。

主要任务：

E-R 模型：采用实体-联系（E-R）模型来描述数据库的概念结构。E-R 模型通过E-R图来表示，图中包含实体、属性和联系等元素。

全局E-R模型：在局部E-R模型的基础上，通过合并和消除冲突，形成全局E-R模型。全局E-R模型是整个数据库系统的概念模型。

优化：对全局E-R模型进行优化，包括实体类型的合并、冗余属性的消除和冗余联系的消除等，以提高概念模型的合理性和效率。

重要性：

概念结构设计是数据库设计的关键阶段，它直接影响到逻辑结构设计和物理结构设计的质量和效率。

3、逻辑结构设计

逻辑结构设计是将概念结构转换为某个DBMS所支持的数据模型，并对其进行优化的过程。

主要任务：

数据模型转换：将E-R模型转换为关系模型、网状模型或层次模型等DBMS所支持的数据模型。在关系数据库中，通常将E-R模型转换为关系模式。

关系模式优化：对转换得到的关系模式进行优化，包括关系的规范化、索引的创建和查询的优化等，以提高数据库的性能和效率。

设计用户子模式：根据用户的实际需求，设计用户子模式，即用户视图。用户子模式是对全局模式的部分抽取和重新组织，以满足用户的特定需求。

重要性：

逻辑结构设计是数据库设计的重要阶段，它直接影响到数据库的存储结构、查询性能和维护成本等。

4、物理结构设计

物理结构设计是为逻辑数据模型选取一个最适合应用环境的物理结构的过程，包括存储结构和存取方法等。

主要任务：

确定物理结构：根据DBMS的特性、存储设备的性能和存储需求等因素，确定数据的存储结构和存取方法。这包括选择索引类型、设计聚簇策略、确定数据块的存储方式等。

物理结构评价：对选定的物理结构进行评价，包括时间和空间效率、维护代价和可扩展性等方面的评估。如果评价结果不满足要求，则需要重新设计或修改物理结构。

重要性：

物理结构设计是数据库设计的最后阶段，它直接影响到数据库的性能、可靠性和成本等关键因素。因此，在进行物理结构设计时，需要充分考虑各种因素，以选择最合适的物理结构。

五、E-R模型

1、三要素

1.实体（Entity）

实体是客观存在并可相互区分的事物。它可以是一个具体的人、事、物，也可以是抽象的概念或事物之间的联系。在数据库中，实体是能够独立存在并且可被识别的基本对象，通常对应现实世界中的一个具体事物。

每个实体都有一个唯一的标识符（如ID）来区分该实体与其他实体。此外，实体还可以拥有多个属性来刻画其特性。在E-R图中的表示：在E-R图中，实体通常用矩形框来表示。

2.属性（Attribute）

属性是实体所具有的某一特性。一个实体可以由若干个属性来刻画，这些属性描述了实体的各种性质或特征。属性可以是单值属性（只包含单个数值），也可以是复合属性（由多个子属性组合而成），还可以是多值属性（包含多个数值）。在E-R图中，属性通常用椭圆形框来表示，椭圆内列出属性的名称。

3.联系（Relationship）

联系是指实体之间的联系，它描述了实体之间的相互作用和关联。现实世界中的事物之间是有联系的，这些联系在信息世界中反映为实体内部的联系和实体之间的联系。实体之间的联系可分为三种类型：一对一联系（1:1）、一对多联系（1:n）和多对多联系（m:n）。在E-R图中的表示：在E-R图中，联系通常用菱形框来表示，菱形内写明关系的名称。

2、实体的属性

1. 简单属性

简单属性是指不能再被进一步分解为更小部分的属性。它代表了实体的一个基本、独立的特征。

在“学生”实体中，“学号”和“姓名”通常被视为简单属性，因为它们各自代表了一个独立的信息点，无法再被细分为更小的属性。

2. 复合属性

复合属性是指可以进一步划分成更小部分的属性。这些更小的部分（子属性）共同构成了复合属性的整体。

在“学生”实体中，“家庭住址”可以视为一个复合属性，因为它可以进一步细分为“省份”、“城市”和“街道”等子属性。

3. 单值属性

单值属性是指对于某个特定的实体，该属性在任何时候都只能有一个值。

在“学生”实体中，“学号”是一个单值属性，因为每个学生只有一个学号。

4. 多值属性

多值属性是指对于某个特定的实体，该属性可以对应多个值。

在“学生”实体中，“电话号码”可以是一个多值属性，因为学生可能有多个电话号码（如家庭电话、手机号码等）。

5. NULL属性

NULL属性用于表示某个实体在某个属性上没有值或属性值未知的情况。

在“学生”实体中，如果某个学生的“紧急联系人电话”未知，则该属性可以设置为NULL。

6. 派生属性

派生属性是指其值可以从其他相关属性或实体中派生出来的属性。派生属性的值通常不需要在数据库中单独存储，而是可以在需要时通过计算得到。

在“学生”实体中，“年龄”可以是一个派生属性，它的值可以通过当前日期和“出生日期”属性计算得到。

3、两个不同实体之间联系

1. 一对一（1:1）

一对一关系指的是两个实体集之间，一个实体集中的每一个实体在另一个实体集中有且仅有一个对应的实体，反之亦然。

示例：

员工与其身份证号：每个员工都有一个唯一的身份证号，而每个身份证号也只对应一个员工。

学生与学号：在某些情况下，一个学生对应一个唯一的学号，且该学号也只属于该学生。

实现：可以在任一方的表中包含对方的唯一标识符作为外键。另一种方式是将两个表合并为一个表，但如果它们之间在某些场景下需要独立操作，则保持分开更为合适。

2. 一对多（1:n）

一对多关系指的是一个实体集中的每一个实体在另一个实体集中有零个、一个或多个对应的实体，但第二个实体集中的每一个实体最多只能对应第一个实体集中的一个实体。

示例：

部门与员工：一个部门可以有多名员工，但每名员工只属于一个部门。

课程与学生注册：一门课程可以被多名学生注册，但每个学生对于某门课程来说，其注册记录只属于该课程。

实现：在“多”的一方表中添加外键，该外键是“一”的一方表的主键。

3. 多对多（m:n）

多对多关系指的是两个实体集中的实体都可以与对方实体集中的多个实体相关联。即第一个实体集中的每个实体可以与第二个实体集中的多个实体相关联，同时第二个实体集中的    每个实体也可以与第一个实体集中的多个实体相关联。

示例：

学生与课程：一个学生可以选修多门课程，同时一门课程也可以被多名学生选修。

作者与书籍：一个作者可以写多本书，而一本书也可以由多个作者共同撰写。

实现：引入第三个表（关联表或交叉引用表），该表包含两个外键，分别指向两个原始表的主键。通过这种方式，可以表示多对多的关系。

4、E-R模型转关系数据库表

一对一（1:1）关系：

可以在两个表中的任意一个表中添加另一个表的主键作为外键。

或者，可以将两个表合并为一个表。

一对多（1:N）关系：

在“多”的一方表中添加“一”的一方表的主键作为外键。

多对多（M:N）关系：

创建一个新的表来表示这种关系，该表至少包含两个外键，分别指向两个参与关系的表的主键。

定义表的约束：

为主键列设置主键约束，确保数据的唯一性和完整性。

为外键列设置外键约束，维护表之间的关联关系。

根据需要设置其他约束，如非空约束、唯一约束等。

优化表结构：

审核所有的表，确保它们符合数据库设计的最佳实践。

对常用的查询条件字段添加索引，以提高查询性能。

六、函数依赖公理系统

1、函数依赖

1. 函数依赖（Functional Dependency, FD）

设R(U)是属性集U上的一个关系模式，X和Y是U的子集。如果对于R(U)的每一个可能的关系r，r中不存在两个元组，它们在X上的属性值相等，但在Y上的值不等，则称X函数决定Y，或Y函数依赖于X，记作X → Y。

简而言之，如果两个元组在X属性集上的值相同，那么它们在Y属性集上的值也必须相同。

2. 完全函数依赖（Full Functional Dependency）

如果X → Y，且对于X的任何一个真子集X'，都有X' ↛ Y，则称Y完全函数依赖于X，记作X ↠ Y。

这意味着，Y完全依赖于X的整体，而不是X的某个真子集。如果去掉X中的任何一个属性，Y的函数依赖关系就不复存在。

3. 部分函数依赖（Partial Functional Dependency）

如果X → Y，但Y不完全函数依赖于X（即存在X的真子集X'使得X' → Y），则称Y部分函数依赖于X。

这表明Y不仅依赖于X的整体，还部分依赖于X的某个真子集。在数据库设计中，部分函数依赖通常被视为不良设计，因为它可能导致数据冗余和更新异常。

4. 传递函数依赖（Transitive Functional Dependency）

如果X → Y且Y ⊈ X（Y不是X的子集），Y → Z，则称Z传递函数依赖于X，记作X → Y → Z。

这种依赖关系表明，通过中间属性集Y，X可以间接决定Z的值。在数据库设计中，传递函数依赖也可能导致数据冗余和更新问题，因为Z的值可以通过X和Y两种路径来更新。

2、函数依赖公理系统

1. 自反律（Reflexivity Rule）：如果Y是X的子集，那么X→Y

2. 增广律（Augmentation Rule）：如果X→Y成立，那么对于任意的属性集Z，XZ→YZ也成立。

3. 传递律（Transitivity Rule）：如果X→Y且Y→Z，那么X→Z。

4. 合并规则（The Union Rule）：如果已知X→Y和X→Z，则可以推导出X→YZ。

5. 伪传递率（The Pseudo Transitivity Rule）：如果A→B且CB→D，则可能推导出AC→D

6. 分解规则（The Decomposition Rule）：如果X→Y，且Z是Y的子集，那么可以推导出X→Z。

3、确定候选码

1.将左边的所有元素放入集合中

2.将左右都没有的元素放入集合中

3. 避免重复和冗余，优先将集合中里面在右边出现的元素排查

4.使用Armstrong公理系统及推论来寻找候选码，尝试所有可能的属性组合

5. 右侧先拆单，依赖依次删。还原即可删，再拆左非单。

4、范式

1.范式判断

七、视图

1、视图

在数据库系统中，视图是一种虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

视图的作用主要包括：

简单性：视图可以简化用户对数据的理解，也可以简化他们的操作。通过视图，用户不必为复杂的查询每次都指定全部的条件。

安全性：通过视图，用户只能查询和修改他们所能见到的数据，从而增强了数据的安全性。

逻辑数据独立性：视图可以帮助用户屏蔽真实表结构变化带来的影响，使得用户只关心他们感兴趣的特定数据。

此外，视图还可以作为一种安全机制，通过限制用户对数据的访问来保护数据的安全性。同时，视图还可以用于数据汇总、复杂查询的封装等场景。

2、创建视图语句

CREATE VIEW 视图名AS SELECT 子句 WITH CHECK OPTION

WITH CHECK OPTION 的作用是在通过视图插入或更新数据时，确保这些操作不违反定义视图时使用的 WHERE 子句的条件。

示例：创建一个名为 VWCS 的视图，该视图包含计算机科学系（CS）学生的 Sno（学号）、Sname（姓名）和 Sage（年龄）

CREATE VIEW VWCS AS SELECT Sno, Sname, Sage FROM student WHERE Sdept = 'CS'  WITH CHECK OPTION;

八、SQL语句

1、select选择语句

SELECT column1, column2, ...  FROM table_name  WHERE condition  GROUP BY column_name(s)  HAVING condition  ORDER BY column_name(s) ASC|DESC  LIMIT number;

SELECT：指定要从表中检索的列名。如果要检索所有列，可以使用星号（*）代替列名。

FROM：指定要从中检索数据的表名。

WHERE（可选）：指定用于过滤记录的条件。只有满足条件的记录才会被检索出来。

GROUP BY（可选）：根据一个或多个列对结果集进行分组。通常与聚合函数（如SUM, AVG, MAX, MIN, COUNT等）一起使用。

HAVING（可选）：对GROUP BY的结果进行过滤。HAVING与WHERE类似，但HAVING用于过滤聚合函数的结果。

ORDER BY（可选）：指定结果集的排序方式。可以使用ASC（升序）或DESC（降序）关键字来指定排序顺序。如果未指定，默认为升序。

LIMIT（可选）：限制返回的记录数。这对于仅获取结果集的前N条记录非常有用。

示例：

1.检索所有列

SELECT * FROM employees;

2. 检索特定列

SELECT name, age FROM employees;

3.带条件的检索

SELECT * FROM employees WHERE department = 'IT';

4.按列排序

SELECT * FROM employees ORDER BY age DESC;

5. 分组和聚合

SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department;

6. 分组后过滤

SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department HAVING COUNT(*) > 5;

7. 限制结果数量

SELECT * FROM employees ORDER BY age DESC LIMIT 5;

2、聚集函数

1.COUNT()

计数函数，用于计算行数或特定列中非NULL值的数量。

使用COUNT(*)计算表中的总行数，包括所有行，不论列值是否为NULL。

使用COUNT(column)计算特定列中非NULL值的数量。

2.SUM()

求和函数，用于计算数值列中所有值的总和。

忽略NULL值。

3.AVG()

平均值函数，用于计算数值列中所有非NULL值的平均值。

忽略NULL值。

4.MAX()

最大值函数，用于找出某列中的最大值。

可以应用于数值、字符串或日期时间数据类型，并返回相应的数据类型。

忽略NULL值。

5.MIN()

最小值函数，功能与MAX()相反，用于找出某列中的最小值。

同样可以应用于数值、字符串或日期时间数据类型，并忽略NULL值。

示例：

-- 计算表中总行数  SELECT COUNT(*) FROM table_name;  -- 计算某列的总和  SELECT SUM(column_name) FROM table_name;  -- 计算某列的平均值  SELECT AVG(column_name) FROM table_name;  -- 找出某列的最大值  SELECT MAX(column_name) FROM table_name;  -- 找出某列的最小值  SELECT MIN(column_name) FROM table_name;  -- 与GROUP BY结合使用，计算每个分组的最大值  SELECT group_column, MAX(column_name) FROM table_name GROUP BY group_column;  -- 与HAVING结合使用，过滤分组后的结果  SELECT group_column, MAX(column_name) FROM table_name GROUP BY group_column HAVING MAX(column_name) > some_value;

3、授权语句

1. GRANT 语句语法

GRANT 权限列表 ON 对象类型 对象名 TO 用户名/角色名 [WITH GRANT OPTION];

权限列表：一个或多个权限名称，用逗号分隔。权限类型取决于对象类型，例如对于表，可以是 SELECT, INSERT, UPDATE, DELETE 等。

对象类型：数据库对象的类型，如 TABLE, VIEW, PROCEDURE 等。

对象名：要授予权限的数据库对象的名称。

用户名/角色名：要授予权限的数据库用户名或角色名。

WITH GRANT OPTION：可选。如果指定此选项，则接收权限的用户或角色还可以将相同的权限授予其他用户或角色。

示例：

-- 授予用户'john'对表'employees'的SELECT和UPDATE权限  GRANT SELECT, UPDATE ON TABLE employees TO john;  -- 授予角色'admins'对所有表的SELECT权限，并允许他们将权限授予其他用户  GRANT SELECT ON ALL TABLES IN SCHEMA public TO admins WITH GRANT OPTION;

2. REVOKE 语句语法

REVOKE 权限列表 ON 对象类型 对象名 FROM 用户名/角色名 [CASCADE | RESTRICT];

权限列表、对象类型、对象名、用户名/角色名：与GRANT语句相同。

CASCADE | RESTRICT：这两个关键字用于控制当从用户或角色收回权限时，如果其他用户或角色通过被收回权限的用户或角色间接获得了权限，应该如何处理。CASCADE 表示从所有间接获取权限的用户或角色中也收回权限；RESTRICT 表示如果存在间接权限，则不允许收回权限（即，如果会违反RESTRICT条件，则REVOKE操作会失败）。如果未指定，大多数数据库默认使用RESTRICT。

示例：

-- 收回用户'john'对表'employees'的UPDATE权限  REVOKE UPDATE ON TABLE employees FROM john;  -- 使用CASCADE收回角色'admins'对所有表的SELECT权限，包括所有间接授予的权限  REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM admins CASCADE;

九、关系代数运算

1.、关系代数运算

2、笛卡尔积

1. 笛卡尔积

设A和B是两个集合，则A和B的笛卡尔积记作A × B，是一个集合，该集合的元素是所有形如(a, b)的有序对，其中a属于A，b属于B。

假设A = {1, 2}，B = {x, y}，则A × B = {(1, x), (1, y), (2, x), (2, y)}。

2.广义笛卡尔积

设关系R和S分别具有r个和s个属性列，那么R和S的广义笛卡尔积是一个包含(r+s)个属性列的元组的集合。在这个集合中，每一个元组的前r个分量来自R的一个元组，而后s个分量则来自S的一个元组。这种运算结果通常记作R×S。

3、投影

数据库中的投影是指从一个表（或关系）中选择出指定的列（或字段），并可能形成一个新的表或视图的过程。投影操作的主要目的是减少数据量、提高查询效率、简化数据结构，以及方便用户进行数据分析和查询。

4、选择

又称限制，是从一个关系（即数据库中的表）中选择满足给定条件的元组（行）的过程。选择运算是以行的角度进行的，即根据指定的条件从表中筛选出符合条件的行。

5、连接

数据库连接关系是指在关系型数据库中，通过特定的连接操作将两个或多个表中的数据关联起来，以便在查询时能够同时访问这些数据。连接操作是SQL查询的重要组成部分，它允许用户从多个表中检索出相互关联的数据。

1. θ连接

定义：θ连接也称为条件连接，是从两个关系的笛卡尔积中选取属性间满足一定条件（由θ表示的比较运算符，如>、<、=等）的元组形成一个新的连接。这里的θ是一个占位符，代表任何有效的比较运算符。

特点：θ连接的关键在于指定了连接的条件，这个条件可以是任何有效的比较表达式，从而允许用户根据具体需求灵活地选择连接的方式。

2. 等值连接

定义：等值连接是θ连接在连接运算符为"="号时的一个特例，即从两个关系的笛卡尔积中选取属性组之间相等的元组。具体来说，如果关系R和S在属性a和b上进行等值连接，那么连接的结果将包含所有在R.a=S.b上相等的元组。

特点：

等值连接要求比较的分量（即属性组）之间必须相等。

等值连接的结果中可能包含重复的属性列，这些列在原始的两个关系中都有出现。

等值连接是关系运算中非常常用的一种连接方式，因为它能够直接根据属性值的相等性来合并两个关系。

3. 自然连接

定义：自然连接是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组（即属性名相同），并且在结果中把重复的属性列去掉，只保留一次。自然连接可以看作是等值连接后再进行了一个投影操作，以去除重复的属性列。

特点：

自然连接一定是等值连接，但等值连接不一定是自然连接。

自然连接要求比较的分量必须是相同的属性组，这意味着属性名必须相同。

自然连接的结果中不包含重复的属性列，这使得结果关系更加紧凑和易于理解。

自然连接是关系运算中最为直观和常用的一种连接方式之一，因为它能够自动去除重复的属性列，并直接根据公共属性来合并两个关系。

6、关系代数查询优化准则

1. 提早执行选取运算

解释：选取运算（也称为选择运算）是从关系中选择满足给定条件的元组。提早执行选取运算可以显著减少后续操作需要处理的数据量，因为后续操作只需要在已经筛选过的、较小的数据集上进行。

应用：在构造查询计划时，应尽可能将选取运算放在前面，特别是在连接和笛卡尔积等开销较大的操作之前。

2. 合并乘积与其后的选择运算为连接运算

解释：这里的“乘积”通常指的是笛卡尔积，它会生成两个关系所有可能组合的元组集合，结果集通常很大。如果其后紧跟着一个选择运算，那么可以考虑将这两个操作合并为一个连接运算，以减少中间结果的大小和计算量。

应用：当选择运算的条件涉及到两个关系的属性时，应优先考虑使用连接运算代替笛卡尔积后接选择运算的组合。

3. 将投影运算与其后的其他运算同时进行，以避免重复扫描关系

解释：投影运算是从关系中选取指定的属性列。如果投影运算后还有其他运算（如选择、连接等），则可以将这些运算与投影运算同时进行，以减少对关系的重复扫描次数。

应用：在优化查询计划时，应尽可能将投影运算与其他必要的运算结合起来，形成一个紧凑的操作序列。

4. 将投影运算和其前后的二目运算结合起来，使得没有必要为去掉某些字段再扫描一遍关系

解释：这个准则进一步强调了投影运算与其他运算的结合。在关系代数中，二目运算指的是涉及两个关系的运算（如连接、并、差等）。通过将投影运算与这些二目运算结合起来，可以避免在去掉某些不需要的字段后再对关系进行额外的扫描。

应用：在构造查询计划时，应关注投影运算与前后二目运算的结合点，以最小化数据扫描和处理的成本。

5. 在执行连接前对关系做适当的预处理，就能快速地找到要连接的元组

解释：连接运算是关系代数中开销较大的操作之一。在执行连接之前，对参与连接的关系进行适当的预处理（如排序、索引等），可以加快连接过程的执行速度。

应用：在数据库设计中，应考虑为经常参与连接操作的属性建立索引；在查询优化时，应利用这些索引来加速连接操作。

6. 存储公共子表达式

解释：在复杂的查询中，可能会出现多个子表达式被多次计算的情况。通过存储这些公共子表达式的计算结果，可以避免重复计算，从而提高查询效率。

应用：在查询优化过程中，应识别出查询中的公共子表达式，并在适当的位置存储其计算结果以供后续使用。这通常是通过查询优化器的自动优化来实现的。