数据库原理面试-核心概念-问题理解

目录

1.数据库、数据库系统与数据库管理系统

2.理解数据独立性

3.数据模型

4.模式、外模式和内模式

5.关系和关系数据库

6.主键与外键

7.SQL语言

8.索引与视图

9.数据库安全

10.数据库完整性

11.数据依赖和函数依赖

12.范式？三范式？为什么要遵循三范式

13.事务与程序？事务的特性

14.非关系型数据库

15.概念结构设计、逻辑结构设计和物理结构设计

16.数据库常用的存取方法

17.SQL语句总结

18.SQL语句实战

19.drop,delete 与 truncate 的区别

END！

1.数据库、数据库系统与数据库管理系统

数据库（Database）：
数据库是一个存储、检索和操作数据的系统。它通常包含多个相关数据表，这些表通过键来关联，以确保数据的一致性和完整性。
数据库系统（Database System）：
数据库系统是由数据库和数据库管理系统（DBMS）组成的整体。它不仅包括数据本身，还包括支持数据存储、检索和操作的软件和硬件。
数据库管理系统（Database Management System, DBMS）：
DBMS是一种软件，用于创建、维护和操作数据库。它提供了数据定义语言（DDL）来定义数据结构，数据操纵语言（DML）来操作数据，以及数据控制语言（DCL）来控制数据访问。
通俗易懂的讲解：
想象一下，数据库就像一个大型的仓库，里面存放着各种各样的商品（数据）。每个商品都有它自己的标签和位置，这样仓库管理员就可以快速找到它们。

数据库：这个仓库里的所有商品。它们被整齐地排列在货架上，每个商品都有它自己的标签，比如商品名称、价格等。这些标签帮助我们快速识别和找到需要的商品。
数据库系统：包括仓库本身（数据库）和仓库管理员（DBMS）。仓库管理员不仅负责管理仓库里的所有商品，还负责维护仓库的秩序，确保商品的安全和正确存储。
数据库管理系统（DBMS）：
就像仓库管理员，DBMS帮助我们管理仓库中的商品。它告诉我们如何添加新商品（插入数据），如何找到特定的商品（查询数据），如何更新商品信息（更新数据），以及如何删除不再需要的商品（删除数据）。
管理员还制定了一套规则，确保仓库中的所有商品都按照正确的方式存放，并且可以被正确地识别和访问。
在这个比喻中：

仓库 = 数据库系统
商品 = 数据
商品标签 = 数据字段
货架 = 数据表
仓库管理员 = 数据库管理系统（DBMS）
商品的添加、查找、更新和删除 = 数据的插入、查询、更新和删除操作

2.理解数据独立性

数据独立性：数据独立性是数据库系统中的一个概念，指的是数据的存储方式和结构的改变不会影响到应用程序或其他用户接口。这种独立性分为两种类型：
物理独立性：指的是数据库的物理存储结构（如数据文件的组织方式）的变化不会影响数据库的逻辑结构或用户视图。换句话说，改变数据库的存储方式（比如从文件系统改为数据库服务器）不会影响应用程序的运行。
逻辑独立性：指的是数据库的逻辑结构（如表结构、索引等）的变化不会影响最终用户或应用程序的视图。即使数据库的逻辑结构发生了变化，比如添加或删除了表，应用程序的接口和操作仍然保持不变。
数据独立性的重要性：数据独立性允许数据库管理员优化数据库的存储和访问方式，而不必担心破坏现有应用程序的功能。这提高了数据库的灵活性和可维护性。

可扩展性！！！

数据独立性主要分为两种类型：逻辑独立性和物理独立性。下面通过例子来说明这两种独立性的概念：

逻辑独立性（Logical Independence）：

例子： 假设你有一个图书馆管理系统，它使用一个数据库来存储图书信息和借阅记录。数据库中有一个Books表，包含书名、作者、ISBN号等字段。

• 原始设计：Books表的字段包括BookID, Title, Author, ISBN。
• 需求变更：后来，图书馆决定增加一个新的字段PublishYear（出版年份）。

在逻辑独立性的情况下，即使数据库表结构发生了变化（增加了一个字段），使用这个数据库的应用程序不需要做任何修改。应用程序仍然可以使用原有的查询和操作，比如查找书名或作者，这些操作不依赖于新增加的字段。

物理独立性（Physical Independence）：

例子： 继续使用上述的图书馆管理系统。假设最初数据库是存储在一台普通的硬盘驱动器上。

• 存储介质变更：随着图书馆藏书量的增加，数据库管理员决定将数据库迁移到更快的固态硬盘（SSD）上，以提高访问速度。

在物理独立性的情况下，即使数据库的存储介质发生了变化，这种变化对应用程序是透明的。应用程序并不关心数据是存储在硬盘上还是SSD上，它只关心能够正常地查询和操作数据。

总结：

• 逻辑独立性：数据库的逻辑结构（如表结构）变化时，应用程序不受影响。应用程序的代码不需要改变，因为它们与数据库的逻辑结构解耦。
• 物理独立性：数据库的物理存储方式变化时，如从一种存储介质迁移到另一种，应用程序同样不受影响。应用程序不需要关心数据是如何被物理存储的。

数据独立性的这两个方面使得数据库系统更加灵活和可维护，因为它们允许数据库管理员在不影响应用程序的情况下对数据库进行优化和升级。

3.数据模型

数据模型应满足三方面要求：一是能比较真实地模拟现实世界，二是容易被人理解，三是便于在计算机上实现。

数据模型通常由数据结构、数据操作和数据的完整性约束条件组成（三要素）。

第一类是概念模型，第二类是逻辑模型和物理模型。

概念模型也叫信息模型，按用户的观点对数据和信息建模，主要用于数据库设计。

逻辑模型包括层次模型、网状模型、关系模型、面向对象模型等，按计算机系统的观点对数据建模，主要用于数据库管理系统的实现。

物理模型是对数据最底层的抽象，描述数据在系统内部的表示方法和存取方法，或在磁盘上的存储方法和存取方法，面向计算机系统。

先将现实世界抽象为信息世界，再将信息世界转换为机器世界。
 

4.模式、外模式和内模式

模式（Schema）：
模式是数据库中数据的逻辑结构的描述，它定义了数据的组织方式、数据类型、表之间的关系以及数据的完整性约束。模式是数据库的蓝图，是所有用户共享的全局视图。
外模式（External Schema）：
外模式是数据库用户的视图，它定义了用户与数据库交互时所看到的结构。外模式是从模式派生出来的，可以根据用户的需求定制，隐藏了数据库的复杂性。
内模式（Internal Schema）：
内模式是数据库的物理结构描述，它定义了数据在存储介质上的实际存储方式和存取路径。内模式是数据库管理员使用的视图，它涉及到数据的物理存储细节。
通俗易懂的讲解：
想象一下，你有一个大型图书馆，图书馆的组织方式可以类比为数据库的不同模式。

模式：
这相当于图书馆的整体布局图，它展示了图书馆的所有区域，比如历史书籍区、科学书籍区、儿童书籍区等。这个布局图是图书馆的基础，所有人都需要按照这个布局来查找书籍。
外模式：
这相当于为不同读者群体定制的局部地图，比如儿童地图可能只显示儿童书籍区和儿童阅读区，而忽略其他区域。这些地图是根据读者的需求定制的，让读者能够更容易地找到他们感兴趣的书籍。
内模式：
这相当于图书馆管理员使用的详细地图，它展示了书籍是如何在书架上具体排列的，书架是如何固定在地板上的，以及如何维护和更新这些书架。这个地图是图书馆内部运作的基础，对于普通读者来说是不可见的。
在这个比喻中：

图书馆的整体布局图 = 模式（总体结构）
为不同读者定制的局部地图 = 外模式（用户与数据库交互结构）
图书馆管理员使用的详细地图 = 内模式（内部存储实现结构）
         模式是数据库的全局视图，外模式是根据用户需求定制的视图，而内模式是数据库的物理存储细节。这些模式共同构成了数据库的多级抽象，使得数据库既能够满足不同用户的需求，又能够高效地存储和管理数据。

         数据库设计中的模式、外模式和内模式的概念与概念模型、逻辑模型和物理模型相对应。下面是它们之间的对应关系和简要解释：

概念模型（Conceptual Model） 对应 模式（Schema）：
概念模型是独立于任何特定数据库系统的，它从用户的角度对数据进行建模，反映了现实世界中的实体、实体之间的关系以及数据的语义。
模式则是数据库中数据的全局逻辑视图，是概念模型的具体实现，定义了数据的逻辑结构，如表结构、字段类型和约束等。
逻辑模型（Logical Model） 对应 外模式（External Schema）：
逻辑模型是概念模型向特定数据库系统模型的转换，它专注于数据的逻辑结构和关系，但不涉及数据的物理存储细节。
外模式是逻辑模型的一部分，它是数据库用户的视图，根据用户或应用的需要定制，定义了用户将与之交互的数据的子集和表现形式。
物理模型（Physical Model） 对应 内模式（Internal Schema）：
物理模型描述了数据在数据库内部的物理存储方式，包括数据的存储结构、索引、存储路径等。
内模式是物理模型的具体实现，详细描述了数据在存储介质上的实际存储细节，如记录的存储方式、页的组织、块的大小等。

5.关系和关系数据库

关系（Relationship）：
关系是数据表中数据行之间的逻辑联系。在数据库中，关系可以是一对一、一对多或多对多，它们定义了不同数据表之间如何相互关联。
关系数据库（Relational Database）：
关系数据库是一种数据库系统，它使用关系模型来组织数据。数据以表格的形式存储，每个表由行（记录）和列（字段）组成。关系数据库通过使用键（如主键和外键）来维护表之间的关系。
关系数据库的特点：
关系数据库具有以下特点：
结构化数据：数据以表格的形式存储，每行代表一个实体，每列代表一个属性。
数据独立性：数据的存储方式（物理结构）和用户视图（逻辑结构）是分离的。
ACID属性：支持原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）的事务处理。
SQL支持：使用SQL作为标准查询语言来操作数据。
通俗易懂的讲解：
想象一下，你有一个大型的文件柜，里面存放着许多文件夹，每个文件夹代表一个关系数据库中的表。

关系：
就像文件夹之间的标签和索引，它们告诉你哪些文件夹是相关的。比如，一个文件夹可能包含员工信息，另一个文件夹可能包含部门信息，它们之间通过员工的部门编号来关联。
关系数据库：
就像这个文件柜，它按照一定的规则（关系）来组织所有的文件夹。每个文件夹（表）都有明确的标签（列），里面存放着相关的文件（行）。你可以很容易地找到特定部门的所有员工信息，因为这些信息是通过部门编号（键）来关联的。
在这个比喻中：

文件柜 = 关系数据库
文件夹 = 数据表
文件夹内的文件 = 数据行（记录）
文件夹标签 = 数据列（字段）
文件夹之间的标签和索引 = 关系

6.主键与外键

主键（Primary Key）：
主键是数据库表中的一个或多个字段，用于唯一标识表中的每一行记录。主键的值必须是唯一的，并且不能为NULL。它确保了数据的实体完整性。
外键（Foreign Key）：
外键是数据库表中的一个字段或字段组合，它与另一个表的主键相关联。外键用于建立和维护两个表之间的链接，确保引用的数据的完整性。
作用：
主键的作用：
确保表中每条记录的唯一性。
作为表内其他字段的参照点，可以用于快速定位和访问特定的记录。
在表与表之间的关联中作为参照，确保数据的一致性。
外键的作用：
建立表与表之间的关联，表示一个表中的数据项引用了另一个表中的数据项。
维护数据的引用完整性，确保数据库中的数据项之间的依赖关系是有效的。
支持数据库的规范化，帮助减少数据冗余，提高数据的一致性和准确性。
通俗易懂的讲解：
想象一下，你有一个大型的图书馆，图书馆里有很多书架，每个书架上都摆满了书籍。

主键：
就像每本书的书脊上都有一个独特的图书编号，这个编号在整个图书馆中是唯一的。通过这个编号，你可以快速找到任何一本书，并且保证不会有两本书有相同的编号。
外键：
就像图书馆的目录系统，它告诉你哪些书属于哪个类别，或者哪些书是系列书籍中的下一本。比如，如果你正在查找关于历史的书籍，目录会告诉你这些书在哪个书架上。如果你正在查找系列书籍的下一本，目录会告诉你它的编号，你可以通过这个编号找到这本书。
在这个比喻中：

图书馆中的图书编号 = 主键
目录系统 = 外键
通过这个比喻，我们可以更容易地理解主键和外键在关系数据库中的作用。主键确保了每条记录的唯一性，而外键则建立了不同记录之间的联系，帮助我们维护数据的完整性和一致性。

7.SQL语言

SQL（Structured Query Language）：

SQL是一种标准化的语言，用于在关系数据库管理系统（RDBMS）中创建、修改、查询和操作数据。
SQL具有以下主要功能：
数据查询（Querying）：使用SELECT语句从数据库中检索数据。
数据操作（Manipulation）：使用INSERT、UPDATE和DELETE语句来添加、修改和删除数据。
数据定义（Definition）：使用CREATE和DROP语句来创建和删除数据库对象，如表、视图、索引等。
数据控制（Control）：使用GRANT和REVOKE语句来控制用户对数据库的访问权限。
通俗易懂的讲解：
想象一下，你有一个大型图书馆，而SQL就像是你在图书馆中使用的一套指令集，帮助你找到、整理和维护书籍。

查询数据（Querying）：
就像你向图书管理员询问一本特定的书，使用SELECT语句，你可以告诉数据库你想要查找什么信息。比如，你可能想要找到所有关于历史的书籍，或者找到某位特定作者的所有作品。
操作数据（Manipulation）：
使用INSERT语句，就像你在图书馆的书架上添加一本新书。
使用UPDATE语句，就像你更新图书馆目录系统中某本书的信息。
使用DELETE语句，就像你从书架上移除一本不再需要的书。
定义数据（Definition）：
使用CREATE语句，就像你在图书馆中创建一个新的书架或阅读区域。
使用DROP语句，就像你决定移除一个不再使用的书架。
控制数据（Control）：
使用GRANT和REVOKE语句，就像你给不同的读者或图书管理员分配不同的权限，比如谁可以借阅珍贵的书籍，谁可以进入特定的阅读区域。
在这个比喻中：

图书馆 = 数据库
图书管理员 = 数据库管理员
书籍和目录 = 数据和数据库对象
图书馆的指令集 = SQL语言

8.索引与视图

索引（Index）
抽象概念：

索引是数据库中用于提高数据检索速度的数据结构。它类似于书籍的目录，允许用户快速定位到特定的数据，而无需扫描整个表。
作用：

快速检索：通过索引，可以快速定位到表中的数据行，极大地提高查询效率。
排序优化：索引还可以用于快速排序数据，尤其是在执行ORDER BY操作时。
唯一性保证：索引可以强制字段的唯一性，如主键索引。
类型：

单列索引：基于单个字段的索引。
复合索引：基于多个字段的组合索引。
聚簇索引：索引的顺序与表中数据的物理存储顺序相同。
非聚簇索引：索引的顺序与表中数据的物理存储顺序不同。

视图（View）
抽象概念：

视图是数据库中的一个虚拟表，其内容由SQL查询定义。它是一个存储的查询，可以像普通表一样使用。
作用：

简化复杂的查询：视图可以简化复杂的SQL操作，用户可以像操作普通表一样操作视图。
安全性：视图可以隐藏数据的复杂性，只展示用户需要看到的数据，从而提高数据的安全性。
逻辑数据独立性：视图允许用户在不改变底层表结构的情况下更改数据的视图。
特点：

可更新性：某些视图可以根据底层表的结构进行更新。
可嵌套性：视图的定义中可以包含其他视图。
通俗易懂的讲解：
索引：
想象一下，你有一个大型的图书馆，图书馆中有很多书架，书架上摆满了书籍。索引就像是图书馆的目录系统，它允许你快速找到想要的书籍，而不需要沿着每个书架逐一查找。

快速检索：就像你可以通过目录快速找到历史类书籍的位置。
排序优化：就像目录按作者或标题排序，方便你找到特定顺序的书籍。
唯一性保证：就像每本书的ISBN号是唯一的，确保没有重复的书籍。
视图：
想象一下，你正在通过一个望远镜观察星空。望远镜（视图）给你的是一个特定角度的星空视图，它隐藏了你不需要看到的其他星星和星座。

简化复杂的查询：就像望远镜让你专注于观察某个星座，而不是整个星空。
安全性：就像望远镜只让你看到允许观察的部分星空，保护了星空的秘密。
逻辑数据独立性：就像即使星空变化，望远镜的视图可以保持不变，直到你调整望远镜。
在这个比喻中：

图书馆的目录系统 = 索引
望远镜 = 视图
通过目录查找书籍 = 使用索引检索数据
通过望远镜观察星空 = 使用视图查询数据

9.数据库安全

数据库安全涉及保护数据库免受未授权访问、数据泄露、篡改和其他安全威胁。它包括多种策略和技术，以确保数据的完整性、机密性和可用性。

关键方面：

访问控制：限制谁可以访问数据库以及他们可以执行哪些操作。
认证：验证用户或系统的身份。
授权：根据用户的身份和权限分配对数据库资源的访问。
加密：使用加密算法保护数据，防止未授权读取。
审计和监控：跟踪和记录对数据库的所有访问和操作。
数据备份和恢复：确保在数据丢失或损坏时可以恢复数据。
安全配置：确保数据库系统配置得当，减少安全漏洞。
通俗易懂的讲解：
想象一下，你的数据库就像是一个存放重要文件的保险箱。

访问控制：就像保险箱上的锁，只有拥有正确钥匙的人才能打开它。
认证：就像要求每个人在打开保险箱前出示身份证明，比如输入密码或使用指纹。
授权：就像保险箱内的不同区域存放不同类型的文件，只有特定的人可以访问特定的区域。
加密：就像将文件内容转换成一种只有特定人能解读的代码，即使有人打开了保险箱，也无法理解文件内容。
审计和监控：就像安装在保险箱附近的摄像头，记录谁在什么时候打开了保险箱，以及他们做了什么。
数据备份和恢复：就像定期制作文件的副本，并将副本存放在另一个安全的地方，以防原文件丢失或损坏。
安全配置：就像确保保险箱放置在安全的地方，并且没有明显的漏洞或缺陷。
在这个比喻中：

保险箱 = 数据库
钥匙和密码 = 访问控制和认证机制
文件的不同区域 = 数据库中的不同数据集
摄像头 = 审计和监控系统
文件副本 = 数据备份

10.数据库完整性

数据库完整性是指数据库管理系统（DBMS）确保数据满足特定的要求和规则，以保证数据的准确性和可靠性。数据库完整性分为三种主要类型：

实体完整性（Entity Integrity）：
实体完整性确保表中的每行都是唯一的，并且具有唯一标识符，通常是主键。它保证了表中的每一行都可以被唯一地区分。
参照完整性（Referential Integrity）：
参照完整性确保数据库中不同表之间的引用关系是有效的。它通过外键约束来实现，确保一个表中的外键值必须在相关联的表的主键中存在。
用户定义完整性（User-Defined Integrity）：
用户定义完整性是基于特定应用需求的约束，它由数据库管理员或设计者定义。这些约束可以包括数据格式、取值范围、字段之间的逻辑关系等。
通俗易懂的讲解：
想象一下，你有一个大型图书馆，图书馆有一套严格的规则来确保书籍的正确管理和借阅。

实体完整性：
就像每本书都有一个唯一的ISBN编号，这个编号确保了图书馆中的每一本书都可以被唯一地区分，没有两本书是完全相同的。
参照完整性：
就像图书馆的目录系统，它记录了每本书属于哪个类别。如果目录中说某本书属于“历史”类别，那么这本书必须在“历史”类别的书架上找到。这确保了目录信息的准确性和书架上书籍的一致性。
用户定义完整性：
就像图书馆有一些特定的规则，比如“每本书的借阅期限不得超过30天”，或者“每名读者一次最多只能借5本书”。这些规则是根据图书馆的具体需求和政策来设定的，它们帮助图书馆维护秩序和效率。
在这个比喻中：

图书馆 = 数据库
每本书的ISBN编号 = 实体完整性（主键）
目录系统 = 参照完整性（外键）
图书馆的借阅规则 = 用户定义完整性（特定约束）

11.数据依赖和函数依赖

数据依赖是数据库理论中的一个概念，描述了数据项之间的一种关系，其中一个数据项的值依赖于另一个数据项的值。数据依赖主要分为两种类型：

函数依赖（Functional Dependency）：
函数依赖是一种特定的数据依赖，其中数据库表中一个字段（或字段集合）的值能够唯一确定另一个字段（或字段集合）的值。例如，如果学生的学号能够唯一确定学生的姓名和课程成绩，那么这里就存在一个函数依赖。
其他类型的数据依赖：
除了函数依赖外，还有例如多值依赖（Multivalued Dependency）和连接依赖（Join Dependency）等其他类型的数据依赖，它们描述了更复杂的数据项之间的关系。
通俗易懂的讲解：
想象一下，你有一个大型的学生信息系统，这个系统存储了学生的个人信息和成绩。

函数依赖：
就像在这个系统中，每个学生都有一个唯一的学号。如果你知道了一个学生的学号，你就可以查找到这个学生的姓名、年龄、课程和成绩等所有信息。这里，学号就是“能够唯一确定”其他信息的关键字段，它与这些信息之间存在函数依赖。
其他类型的数据依赖：
比如，如果一个课程编号不仅能够确定课程名称，还能够确定所有选修这门课程的学生列表，这就可能表示存在一种多值依赖。又或者，如果你需要通过连接两个表（比如学生表和课程表）来获取学生选课的完整信息，这可能涉及到连接依赖。
在这个比喻中：

学生信息系统 = 数据库
学号、姓名、课程成绩 = 数据库表中的字段
学号与姓名、课程成绩之间的关系 = 函数依赖

12.范式？三范式？为什么要遵循三范式

范式（Normal Form，简称NF）**是数据库设计中用来减少数据冗余和提高数据完整性的一组规则。范式理论基于函数依赖的概念，用于指导数据库表的设计。遵循范式可以确保数据库结构的规范化，从而提高数据的一致性和减少数据操作的复杂性。

三范式是数据库设计中最基本的三个范式，分别是：

第一范式（1NF）：
要求数据库表的每一列都是不可分割的基本数据项，即表中的所有字段都应该只包含原子性的值，没有重复的组或数组。每一列不可分！
第二范式（2NF）：
在1NF的基础上，要求表中的每一列都完全依赖于主键。如果存在部分依赖（即某些列只依赖于主键的一部分），则需要对表进行拆分。非主属性依赖主属性！
第三范式（3NF）：
在2NF的基础上，要求表中的每一列都不依赖于其他列，即没有传递依赖。这意味着非主键列之间不能相互决定对方的值。非主属性不依赖其他主属性！

为什么要遵循三范式：
减少数据冗余：范式化可以减少数据存储中的重复，从而降低存储成本和提高数据一致性。
提高数据完整性：范式化通过限制数据的存储方式，确保数据的准确性和可靠性。
简化数据维护：规范化的数据库结构更易于理解和维护，因为数据的逻辑结构更加清晰。
改善查询性能：虽然范式化可能会导致一些查询变复杂，但总体上可以提高查询性能，因为减少了数据的扫描范围。
通俗易懂的讲解：
想象一下，你有一个大型的图书馆，图书馆的书籍需要按照一定的规则来排列和分类。

第一范式（1NF）：
就像图书馆中每本书的标签上只包含这本书的基本信息，如书名、作者和ISBN号，而不是包含重复的信息或者一个标签上有多本书的信息。
第二范式（2NF）：
就像图书馆的分类系统，每个类别（如历史、科学）下的书籍都是根据整个类别编号来组织的，而不是只根据类别编号的一部分（如只有历史的一部分）。
第三范式（3NF）：
就像图书馆中的历史类别下，没有一本书的标签会显示另一本书的信息，比如“这本书旁边是关于古埃及的书”，每本书的标签都是独立的。
遵循三范式就像是图书馆管理员按照一套严格的规则来组织图书馆，这样可以确保图书馆的秩序，减少混乱，提高查找效率，并且使得图书馆的维护变得更加简单。虽然有时候为了查询的方便，我们可能需要牺牲一些规范化（比如反规范化），但总体上，规范化的数据库设计是提高数据管理和使用效率的关键。

13.事务与程序？事务的特性

事务（Transaction）：

事务是数据库管理系统执行过程中的一个单元，由一系列的数据库操作组成，这些操作要么全部成功，要么全部失败。事务是数据库维护数据一致性的基本单位。
事务的特性，通常被称为ACID属性，包括：

原子性（Atomicity）：
事务中的所有操作要么全部完成，要么全部不完成，不会结束在中间某个点。这保证了事务的不可分割性。
一致性（Consistency）：
事务必须保证数据库从一个一致的状态转移到另一个一致的状态。这意味着在事务开始之前和提交之后，数据库的完整性约束都应该得到满足。
隔离性（Isolation）：
并发执行的事务之间不会互相影响。每个事务都应该像它是在独立运行一样，不受其他事务的干扰。
持久性（Durability）：
一旦事务提交，它对数据库的改变就是永久性的，即使系统发生故障也不会丢失。
程序（Program）：

程序是一系列指令的集合，由编程语言编写，用于执行特定的任务或操作。在数据库环境中，程序可能包含对数据库的查询和事务处理。
通俗易懂的讲解：
想象一下，你在餐厅用餐，事务就像是你点餐的过程。

原子性：
就像你点的整个套餐，要么全部上齐，要么不上。你不会接受只有部分菜品上桌的情况。
一致性：
就像餐厅确保你的订单在厨房制作前后都是一致的，你点的是牛排，厨房不会给你做鱼。
隔离性：
就像你在用餐时，旁边桌子的客人不会吃到你的餐点，每个客人的用餐体验都是独立的。
持久性：
就像你用餐结束后，账单已经被餐厅记录，即使餐厅突然停电，你的账单信息也不会丢失。
在这个比喻中：

点餐过程 = 事务
套餐全部上齐 = 原子性
订单一致性 = 一致性
独立用餐 = 隔离性
账单记录 = 持久性
事务的ACID特性确保了数据库操作的可靠性和安全性。就像餐厅确保每位客人的用餐体验一样，数据库管理系统确保每个事务都能正确、独立且持久地执行。程序则像是客人的点餐指令，告诉餐厅（数据库）需要准备什么样的菜品（执行哪些操作）。

14.非关系型数据库

非关系型数据库（NoSQL）
抽象概念：

非关系型数据库是一种不依赖传统关系模型的数据库，它不使用结构化查询语言（SQL）进行数据管理。NoSQL数据库的设计目标是提供更高的可扩展性、灵活性和性能，特别是在处理大规模数据集和高并发请求时。
特点：

模式自由：NoSQL数据库通常不需要预先定义数据模式，可以存储结构不同的数据。
数据模型多样性：包括键值对存储、文档存储、列族存储和图形数据库等。
可扩展性：NoSQL数据库通常设计为易于水平扩展，通过增加更多的服务器来处理更大的数据量。
性能：在某些情况下，NoSQL数据库能够提供比关系型数据库更快的读写性能。
关系型数据库（RDBMS）
抽象概念：

关系型数据库是一种基于关系模型的数据库，使用SQL作为查询语言。它通过行和列的形式组织数据，数据存储在表中，表之间通过关系进行连接。
特点：

结构化数据：数据以表格的形式存储，具有固定的模式。
ACID事务：支持原子性、一致性、隔离性和持久性的事务处理。
数据完整性：通过主键、外键等机制确保数据的引用完整性。
标准化：遵循SQL标准，便于跨平台使用。
区别：
数据模型：
非关系型数据库使用各种数据模型，如键值对、文档、列族或图形，而关系型数据库使用表格模型。
查询语言：
非关系型数据库不使用SQL，可能使用自定义的查询语言或API。
关系型数据库使用标准的SQL语言。
可扩展性：
非关系型数据库通常设计为易于水平扩展，适合大规模分布式系统。
关系型数据库更倾向于垂直扩展，通过增加单个服务器的资源来提高性能。
一致性：
非关系型数据库可能采用最终一致性模型，允许短暂的数据不一致。
关系型数据库通常保证强一致性，每个事务都保证数据的一致性。
事务处理：
非关系型数据库可能提供有限的事务支持或不提供事务支持。
关系型数据库提供完整的ACID事务支持。
适用场景：
非关系型数据库适合于大数据应用、实时 web 应用、分布式系统等场景。
关系型数据库适合于需要复杂查询、事务处理和数据完整性的应用。
通俗易懂的讲解：
想象一下，你有一家图书馆和一家大型仓库。

非关系型数据库就像是一家大型仓库，它可以存放各种形状和大小的货物，易于扩展，可以快速添加更多的货架来存放更多的货物。但是，仓库的管理系统可能不像图书馆那样严格分类和有序。
关系型数据库就像是一家图书馆，每本书都有固定的位置和分类编号，图书馆有一套严格的管理系统来确保每本书都能被正确地找到和归还。图书馆可能不像仓库那样容易扩展，但它在管理大量书籍和维护秩序方面非常出色。
在这个比喻中：

图书馆 = 关系型数据库
仓库 = 非关系型数据库
书籍的分类编号 = 关系型数据库的表格和索引
货物的存放方式 = 非关系型数据库的数据模型

15.概念结构设计、逻辑结构设计和物理结构设计

概念结构设计
抽象概念：

概念结构设计是数据库设计过程中的第一阶段，它关注的是理解用户需求并将其转化为一个概念模型。这个模型通常是独立于特定数据库管理系统（DBMS）的。
过程：

需求收集：与用户沟通，了解他们的业务流程、数据需求和操作需求。
需求分析：分析收集到的需求，识别实体、实体之间的关系以及数据的特征。
概念模型构建：使用高级抽象工具，如实体-关系图（E-R图），来表示数据和它们之间的关系。
特点：

独立性：概念模型不依赖于任何特定的技术实现。
易于理解：通常使用图形化表示，易于非技术用户理解。
逻辑结构设计
抽象概念：

逻辑结构设计是将概念模型转化为特定DBMS能够理解和操作的逻辑模型的过程。
过程：

概念模型转换：将E-R图中的实体和关系转换为逻辑模型中的表和关系。
规范化：应用规范化理论，消除数据冗余，提高数据一致性。
逻辑模型优化：根据逻辑模型的使用场景进行优化，比如索引设计。
特点：

适应性：逻辑模型适应于特定的DBMS。
优化：考虑查询性能和数据访问模式进行优化。
物理结构设计
抽象概念：

物理结构设计是确定数据在存储介质上的实际存储方式和访问方法的过程。
过程：

存储结构选择：确定数据的存储格式，如行存储或列存储。
存取方法设计：设计数据的访问路径，如索引、哈希表或B树。
性能调优：根据预期的工作负载调整物理层面的参数。
特点：

性能关注：物理设计关注I/O性能、存储效率等。
环境适应性：物理设计考虑硬件和操作系统的特性。
通俗易懂的讲解：
想象一下，你在设计一座图书馆。

概念结构设计：
就像在设计图书馆之前，你需要了解读者的需求，比如他们需要多少阅读区、多少书架、是否需要儿童区等。然后，你根据这些需求画出一个图书馆的草图，这个草图就是概念模型。
逻辑结构设计：
有了草图后，你需要进一步细化设计，决定每个区域的具体布局，书架的排列方式，以及如何分类图书。这个阶段，你开始考虑如何将草图中的概念转化为实际可操作的布局。
物理结构设计：
最后，你需要决定书架的材质、图书馆的照明系统，以及如何布置电线和网络。这个阶段，你关注的是图书馆的内部结构和系统，以确保图书馆运行高效且维护成本低。
在这个比喻中：

读者需求 = 数据库用户需求
图书馆草图 = 概念模型
区域布局和分类 = 逻辑模型
书架材质和照明系统 = 物理存储结构和存取方法

16.数据库常用的存取方法

数据库的存取方法决定了数据在存储介质上的组织方式和检索数据的途径。以下是三种常用的存取方法：

索引方法（Indexing）：
索引是一种数据结构，通常用于加速数据检索。它类似于书籍的目录，允许快速定位到数据存储位置，而无需扫描整个数据集。
聚簇方法（Clustering）：
聚簇是一种存储机制，它将相关的数据记录存储在物理上相邻的位置。这通常基于数据访问模式，将频繁一起访问的记录放在一起。（类似高速缓存）
HASH方法（Hashing）：
HASH方法使用一个HASH函数将数据项映射到存储位置。这种方法特别适用于快速查找，因为它可以提供接近常数时间的检索性能。
特点：
索引方法：
可以是单列或多列索引。
支持快速的数据检索和排序。
可能需要额外的存储空间和维护成本。
聚簇方法：
减少了磁盘I/O操作，因为相关数据已经物理上聚集。
可能需要处理数据倾斜和更新性能问题。
HASH方法：
提供快速的数据插入和查找。
可能在数据量大时面临扩展性问题。
通俗易懂的讲解：
想象一下，你在管理一个大型图书馆的藏书。

索引方法：
就像图书馆的目录系统，每本书都有一个索引号，你可以通过这个索引号快速找到书在书架上的准确位置。
聚簇方法：
就像图书馆将同一主题或作者的书籍放在一起，这样当读者查找特定主题或作者的书籍时，可以快速找到一系列相关书籍。
HASH方法：
就像使用一个自动化的图书检索系统，你输入书名或作者，系统会告诉你一个特定的书架号，你直接去那个位置就能找到书。
在这个比喻中：

目录系统 = 索引方法
按主题或作者排列的书籍 = 聚簇方法
自动化图书检索系统 = HASH方法

17.SQL语句总结

SQL语句中常用关键词及其解释如下：
1）SELECT
将资料从数据库中的表格内选出，两个关键字：从 (FROM) 数据库中的表格内选出 (SELECT)。语法为
SELECT "栏位名" FROM "表格名"。
2）DISTINCT
在上述 SELECT 关键词后加上一个 DISTINCT 就可以去除选择出来的栏位中的重复，从而完成求得这个表格/栏位内有哪些不同的值的功能。语法为
SELECT DISTINCT "栏位名" FROM "表格名"。
3）WHERE
这个关键词可以帮助我们选择性地抓资料，而不是全取出来。语法为
SELECT "栏位名" FROM "表格名" WHERE "条件" 
4）AND OR
上例中的 WHERE 指令可以被用来由表格中有条件地选取资料。这个条件可能是简单的 (像上一页的例子)，也可能是复杂的。复杂条件是由二或多个简单条件透过 AND 或是 OR 的连接而成。语法为：
SELECT "栏位名"  FROM "表格名"  WHERE "简单条件"  {[AND|OR] "简单条件"}+
5）IN
在 SQL 中，在两个情况下会用到 IN  这个指令；这一页将介绍其中之一：与 WHERE 有关的那一个情况。在这个用法下，我们事先已知道至少一个我们需要的值，而我们将这些知道的值都放入 IN  这个子句。语法为：
SELECT "栏位名"  FROM "表格名"  WHERE "栏位名" IN ('值一', '值二', ...)  
6）BETWEEN
IN 这个指令可以让我们依照一或数个不连续 (discrete)的值的限制之内抓出资料库中的值，而 BETWEEN 则是让我们可以运用一个范围 (range)  内抓出资料库中的值，语法为：
SELECT "栏位名"  FROM "表格名" WHERE "栏位名" BETWEEN '值一' AND '值二' 
7）LIKE
LIKE 是另一个在 WHERE  子句中会用到的指令。基本上， LIKE  能让我们依据一个模式(pattern) 来找出我们要的资料。语法为：
SELECT "栏位名"  FROM "表格名"  WHERE "栏位名" LIKE {模式} 
8）ORDER BY
我们经常需要能够将抓出的资料做一个有系统的显示。这可能是由小往大 (ascending)  或是由大往小(descending)。在这种情况下，我们就可以运用 ORDER BY 这个指令来达到我们的目的。语法为：
SELECT "栏位名"  FROM "表格名 [WHERE "条件"] ORDER BY "栏位名" [ASC, DESC] 
9）函数
函数允许我们能够对这些数字的型态存在的行或者列做运算，包括 AVG (平均)、COUNT (计数)、MAX (最大值)、MIN (最小值)、SUM (总合)。语法为：
SELECT "函数名"("栏位名") FROM "表格名"  
10）COUNT
这个关键词能够帮我我们统计有多少笔资料被选出来，语法为：
SELECT COUNT("栏位名") FROM "表格名"
11）GROUP BY
GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。语法为：
SELECT "栏位1", SUM("栏位2")  FROM "表格名"  GROUP BY "栏位1" 
12）HAVING
该关键词可以帮助我们对函数产生的值来设定条件。语法为：
SELECT "栏位1", SUM("栏位2")  FROM "表格名"  GROUP BY "栏位1"  HAVING (函数条件)  
13）ALIAS
我们可以通过ALIAS为列名称和表名称指定别名，语法为：
SELECT "表格别名"."栏位1" "栏位别名"  FROM "表格名" "表格别名"  

18.SQL语句实战

通过它我们应该能很好地掌握以上关键词的使用方法。

Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表

问题：
查询“001”课程比“002”课程成绩高的所有学生的学号

SELECT a.S#
FROM SC a
JOIN SC b ON a.S# = b.S#
WHERE a.C# = '001' AND b.C# = '002' AND a.score > b.score;

查询平均成绩大于60分的同学的学号和平均成绩
sql
SELECT S#, AVG(score) AS average_score
FROM SC
GROUP BY S#
HAVING AVG(score) > 60;
查询所有同学的学号、姓名、选课数、总成绩
sql
SELECT Student.S#, Student.Sname, COUNT(SC.C#) AS course_count, SUM(SC.score) AS total_score
FROM Student
LEFT JOIN SC ON Student.S# = SC.S#
GROUP BY Student.S#, Student.Sname;
查询姓“李”的老师的个数
sql
SELECT COUNT(DISTINCT Tname)
FROM Teacher
WHERE Tname LIKE '李%';
查询没学过“叶平”老师课的同学的学号、姓名
sql
SELECT S#, Sname
FROM Student
WHERE S# NOT IN (
  SELECT DISTINCT SC.S#
  FROM SC
  JOIN Course ON SC.C# = Course.C#
  JOIN Teacher ON Course.T# = Teacher.T#
  WHERE Teacher.Tname = '叶平'
);
查询学过“001”并且也学过编号“002”课程的同学的学号、姓名
sql
SELECT Student.S#, Student.Sname
FROM Student
JOIN SC ON Student.S# = SC.S#
WHERE SC.C# = '001'
AND EXISTS (
  SELECT 1
  FROM SC AS SC_2
  WHERE SC_2.S# = Student.S# AND SC_2.C# = '002'
);
查询学过“叶平”老师所教的所有课的同学的学号、姓名
sql
SELECT Student.S#, Student.Sname
FROM Student
WHERE Student.S# IN (
  SELECT SC.S#
  FROM SC
  JOIN Course ON SC.C# = Course.C#
  JOIN Teacher ON Course.T# = Teacher.T#
  WHERE Teacher.Tname = '叶平'
  GROUP BY SC.S#
  HAVING COUNT(DISTINCT SC.C#) = (
    SELECT COUNT(DISTINCT Course.C#)
    FROM Course
    JOIN Teacher ON Course.T# = Teacher.T#
    WHERE Teacher.Tname = '叶平'
  )
);
查询所有课程成绩小于60分的同学的学号、姓名
sql
SELECT S#, Sname
FROM Student
WHERE S# NOT IN (
  SELECT DISTINCT S#
  FROM SC
  WHERE score >= 60
);
查询没有学全所有课的同学的学号、姓名
sql
SELECT S#, Sname
FROM Student
WHERE S# NOT IN (
  SELECT S#
  FROM SC
  GROUP BY S#
  HAVING COUNT(DISTINCT C#) = (SELECT COUNT(*) FROM Course)
);
查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名
sql
SELECT S#, Sname
FROM Student
WHERE S# IN (
  SELECT DISTINCT SC.S#
  FROM SC
  WHERE SC.C# IN (
    SELECT C#
    FROM SC
    WHERE S# = '1001'
  )
) AND S# != '1001';
删除学习“叶平”老师课的SC表记录
sql
DELETE FROM SC
WHERE C# IN (
  SELECT C# FROM Course
  WHERE T# = (
    SELECT T# FROM Teacher WHERE Tname = '叶平'
  )
);
查询各科成绩最高和最低的分
sql
SELECT C# AS 课程ID,
       MAX(score) AS 最高分,
       MIN(score) AS 最低分
FROM SC
GROUP BY C#;
查询学生平均成绩及其名次
sql
SELECT RANK() OVER (ORDER BY AVG(score) DESC) AS 名次,
       S# AS 学生学号,
       AVG(score) AS 平均成绩
FROM SC
GROUP BY S#;
查询各科成绩前三名的记录
sql
SELECT S# AS 学生ID, C# AS 课程ID, score AS 分数
FROM SC
WHERE score IN (
  SELECT TOP 3 score
  FROM SC AS SC_inner
  WHERE SC_inner.C# = SC.C#
  ORDER BY score DESC
)
ORDER BY C#;
查询每门课成绩最好的前两名

SELECT S# AS 学生ID, C# AS 课程ID, score AS 分数
FROM SC
WHERE score IN (
  SELECT TOP 2 score
  FROM SC AS SC_inner
  WHERE SC_inner.C# = SC.C#
  ORDER BY score DESC
)
ORDER BY C#;

19.drop,delete 与 truncate 的区别

在SQL中，DROP、DELETE和TRUNCATE都是用来删除数据库表中数据的操作，但它们之间存在一些关键的区别：

1. DROP 语句：

   • DROP用于删除整个表或数据库对象，包括表的结构和所有数据。
   • 一旦执行，DROP操作是不可逆的，即数据无法恢复。
   • DROP通常用于彻底移除不再需要的表。

   示例：DROP TABLE table_name;

2. DELETE 语句：

   • DELETE用于从表中删除满足特定条件的行。
   • DELETE操作是可逆的，如果启用了事务，可以在同一个事务中回滚。
   • DELETE操作可能需要更多的处理时间，特别是当删除大量数据时，因为它需要逐行检查条件。
   • DELETE可以删除表中的全部或部分数据，但保留表结构。

   示例：DELETE FROM table_name WHERE condition;

3. TRUNCATE 语句：

   • TRUNCATE用于删除表中的所有行，但保留表结构。
   • TRUNCATE操作是不可逆的，且通常不能回滚。
   • TRUNCATE通常比DELETE更快，因为它不需要逐行检查，而是直接释放整个表的数据空间。
   • TRUNCATE不能用于有外键约束引用的表。

   示例：TRUNCATE TABLE table_name;

主要区别：

• 操作范围：

  • DROP删除整个表或数据库对象。
  • DELETE删除满足条件的行。
  • TRUNCATE删除表中的所有行。

• 可逆性：

  • DROP和TRUNCATE通常是不可逆的。
  • DELETE在事务中可以回滚。

• 性能：

  • TRUNCATE通常提供最快的删除性能，因为它不逐行操作。
  • DELETE可能需要更多时间，尤其是当涉及大量数据时。

• 外键约束：

  • TRUNCATE不能用于有外键约束的表。
  • DELETE可以用于有外键约束的表，但删除操作可能会受到约束的限制。

• 日志记录：

  • TRUNCATE操作通常不被记录在事务日志中，因为它是一个删除整个表数据的低级别操作。
  • DELETE操作会被记录在事务日志中，因为它逐行删除数据。

END！

丑的才谈恋爱，美的卖空调。

2024-8-10 晴 40摄氏度