炫“库”行动——超全数据库疑难知识总结（解说+案例）建议收藏！

素颜马尾好姑娘i 2022-08-28 13:57 89阅读 0赞

**本文正在参与炫“库”行动-人大金仓有奖征文：**[快来点击活动链接参与投稿吧https://marketing.csdn.net/p/98bd30353e7cb998b6070a89e8b91edb![icon-default.png_t_L892][]https://marketing.csdn.net/p/98bd30353e7cb998b6070a89e8b91edb][https_marketing.csdn.net_p_98bd30353e7cb998b6070a89e8b91edb_icon-default.png_t_L892_https_marketing.csdn.net_p_98bd30353e7cb998b6070a89e8b91edb]

--------------------

**目录**

一、数据库分析与设计

二、UML与数据库应用系统

三、高级查询

四、数据库后台编程技术

五、数据库及数据库对象

六、安全管理

七、运行维护与优化

八、故障管理、备份与恢复

每文一语

--------------------

**话不多说，直接上干粮，各位C站的小伙伴，不要吃的太饱了哟，要记得消化，太撑了容易长肉肉哟！**

### 一、数据库分析与设计 ###

1、数据库应用系统的数据处理需求可以表示为事务规范。

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

2、**改变数据库应用系统体系结构**、**升级DBMS版本**、**修改程序以适应新需求**都属于数据库应用系统升级工作；而创建新的索引属于数据库应用系统优化内容。

3、**IDEF1X**侧重分析、抽象和概括应用领域中的数据需求，被称为**数据建模**方法，而**UML，DFD，IDEFO**都属于**需求分析建模**方法。

4、**数据持久层**负责保存和管理应用数据系统数据，而数据的完整性维护属于数据持久层功能中的数据管理的内容。

5、多个小事务合并成大事务，会增加占有锁的时间，可能会造成大量阻塞事务，甚至会产生死锁。

6、数据库**应用系统的实施工作**包括**创建数据库、装载数据、编写与调试应用程序、数据库试运行**。

7、关键字**unique**表示唯一索引，**clustered**表示聚集索引，

8、数据库应用系统**日常维护**包括：**备份与恢复、完整性维护、安全性维护、存储空间管理和并发控制**。

9、模式调整属于数据库应用系统的性能优化调整的范围。

10、在数据流图中，一个处理**至少**有一个输入流和一个输出流；一个存储必须有流入的数据流和流出的数据流 ；父图中描述过的数据流必须在相应的子图中出现；一个数据流至少**有一端是处理框**。

![20210918202037141.gif][]

11、通过数据库**概念设计**和**逻辑设计**已经得到了**规范化的关系模式**。尽管关系数据库模式是按照关系数据库管理系统支持的结构进行数据的描述和组织的，然而，这些符号表示并不能解释如何处理和存储数据。数据库**物理设计**的目的是将数据的逻辑描述转换为实现技术规范，其目标是设计数据存储方案，以便提供足够好的性能并确保数据库数据的完整性、安全性和可恢复性。

从功能角度数据库应用系统可以划分为四个层次来实现：  
①表示层：用于显示数据和接收用户输入的数据。（一般为Windows应用程序或Web应用程序)。  
 ②业务逻辑层：是表示层和数据访问层之间的桥梁，主要负责数据的传递和处理。  
 ③数据访问层：负责与DBMS系统进行交互，提取或存入应用系统所需的数据。  
 ④数据持久层：负责保存和管理应用系统数据。

12、在IDEF1X中一个**非确定联系**又称为**多对多关系**或m:n 联系，**非标定型联系** ：**一对多**

13、系统规划与定义的内容包括：**任务陈述、确定任务目标、确定系统范围和边界、确定用户视图**。

14、IDEF0需求建模方法，描述功能活动示意图中：

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA546L5bCP546LLTEyMw_size_10_color_FFFFFF_t_70_g_se_x_16][]

左边的输入箭头表示完成活动需要的数据；

矩形框上方的控制箭头描述了影响这个活动执行的事件或约束条件；

右边的输出箭头说明由活动产生的结果及信息，

下方进入的机制箭头表示实施该活动的物理手段或完成活动需要的资源。

15、**降低事务隔离级别**可以提高事务的吞吐量，提高活锁的可能性，降低发生死锁、发生阻塞的可能性。

事务的隔离性是指多个并发事务同时访问一个数据库时，一个事物不应被另一个事物所干扰，即保证并发读取数据的正确性，降低发生数据不一致的可能性。

16、在进行DBAS设计时，为了**避免死锁**，可以考虑以下原则：  
（1）按**同一顺序访问资源**。  
（2）避免事务中的用户交互。  
（3）采用**小事务模式**，尽量缩短事务的长度，减少占有锁的时间。  
（4）尽量使用**记录级别的锁**（行锁），少用表级别的锁。  
（5）使用**绑定连接**，使同一应用程序所打开的两个或多个连接可以相互合作。

17、完整性约束条件的作用对象分为列、元组和关系三种级别

其中**对列的约束**主要指对其值类型、范围、精度、排序等的约束条件。对**元组的约束**主要指对记录中各个属性之间的联系的约束条件。对关系的约束是指对若干纪录间、关系集合上以及关系之间联系的约束条件。

18、论证是否具备数据库应用系统开发所需的人力资源，这属于数据库应用系统的操作可行性分析。

19、两个实体集间存在多对多联系，利用引入的第三个实体集，转化后得到的两个一对多的联系，符合标定型联系的定义。

20、**关系模式个数=实体集数+多对多关系数**

21、业务逻辑层概要设计

1、一个构件或模块只负责完成一项任务，也就是单一责任原则。  
2、各个构件均应具有独立的功能，并最大限度地减少与其他构件功能重叠。  
3、构件之间的接口应尽量简单明确。  
4、如果两个构件间的关系比较复杂，应考虑进一步进行模块划分。  
5、如果构件过于复杂，可以考虑将其细分。

22、**关系模式的设计、规范化处理**属于数据库**逻辑结构**设计的内容  
![2021091821162742.gif][]

### 二、UML与数据库应用系统 ###

1、**状态图**中只能有**一个起始状态**，**可以有多个结束状态**。

2、对象图主要用于解释类图以及验证所设计的类图是否符合实际状况，也可用于协助程序设计人

员理解复杂的类图结构。

3、一般情况下，时间图作为状态机图的辅助说明工具，一张时间图通常会对应至少一张状态图。

4、**角色**是与系统进行交互的**外部实体**，可以是系统用户也可以是其他系统或者硬件设备。

5、在UML中，用**类图**来描述**系统的静态结构**，而用**顺序图和通信图来表示系统的动态结构**。顺序图和通信图都描述交互，但是顺序图强调的是**时间**，通信图强调的是**空间**。

6、UML中，椭圆表示用例，位于系统边界内部。

7、根据事务-基本表交叉引用矩阵可以确定哪些事务对哪些表列执行了何种操作，基于该矩阵可以帮助数据库设计人员建立合适的索引。

8、 数据库安全性保护的主要保护方式有用户身份鉴别、权限控制、视图机制。

### 三、高级查询 ###

1、在SQLserver2008版本的查询语法之中，我们的限制查询top，应该放在\*的前面。

2、使用**FOR**或者**AFTER**选项定义的触发器为后触发型触发器，即只有在引发触发器执行的语句中的操作都已成功执行，并且所有的约束检查也成功完成后，才执行该触发器。

3、使用**INSTEAD OF**选项定义的触发器为前触发型触发器，在这种模式的触发器中，指定执行触发器而不是执行引发触发器执行的SQL语句，从而替代触发的操作。

4、创建分区函数的sql语句是：

CREATE PARTITION FUNCTION partition_function_name(input_parameter_type)
    AS RANGE [LEFT|RIGHT]
    FOR VALUES([boundary_value[,…n]])
    [;]

釆用了LEFT关键字，在指定分区间隔值是由数据库引擎按升序从左到右排列时，指定边界值间隔处于左侧分区；而关键字RIGHT是指定边界值间隔在右侧分区。

如果：分区的边界值分别是**100和200**，那么将被分为3个区间，分别是**<=100、>=101 and <=200、>200。**

### **四、数据库后台编程技术** ###

1、使用**FOR或者AFTER**选项定义的触发器为**后触发型触发器**，即只有在引发触发器执行的语句中的操作都已成功执行，并且所有的约束检查也成功完成后，才执行该触发器。

2、使用**INSTEAD OF**选项定义的触发器为**前触发型触发器**，在这种模式的触发器中，指定执行触发器而不是执行引发触发器执行的SQL语句，从而替代触发的操作。

3、 在对游标数据进行提取的过程中，可以使用@@FETCH\_STATUS全局变量判断数据提取的状态。由于@@FETCH\_STATUS对于在一个连接上的所有游标是全局性的，不管是对哪个游标，只要执行一次FETCH语句，系统都会对@@FETCH\_STATUS赋一次值，以表明该FETCH语句的执行情况。

4、对于前触发器，在一个表上针对同一个数据操作**只能定义一个前触发器**；对于后触发器，可以在同一种操作上建立**多个触发器**。

5、

**NEXT**关键字的作用是返回紧跟在当前行之后的数据行，并且当前行递增为结果行。

**PRIOR**关键字返回紧临当前行前面的数据行，并且当前行递减为结果行。

ABSOLUTE n和RELATIVE n关键字用于指定特定行。

FIRST关键字返回游标中的第一行并将其作为当前行。

LAST关键字返回游标中的最后一行并将其作为当前行。

6、执行有多个输入参数的存储过程时，参数的传递方式有两种：  
 **①按参数位置传递值**  
按参数位置传递值指执行存储过程的EXEC语句中的实参的排列顺序必须与定义存储过程时定义的参数的顺序一致。  
 **②按参数名传递值**  
按参数名传递值指的是执行存储过程的EXEC语句中要指明定义存储过程时指定的参数的名字以及此参数的值，而不关心参数的定义顺序。

7、数据库创建后可以手工缩小数据文件和日志文件的空间

8、db\_datawriter角色属于数据库级角色，具有插入、删除和更改数据库中所有用户数据的权限。

8、在T1表上定义了一个由插入或删除操作引发的**后触**发型触发器

CREATE TRIGGER tri_1
    ON T1 FOR INSERT,DELETE
    AS ……

### 五、数据库及数据库对象 ###

1、model数据库用作SQL Server实例上创建的所有数据库的模板。对model数据库进行的修改（如数据库大小、排序规则、恢复模式和其他数据库选项）将应用于以后创建的所有用户数据库，当用户创建一个数据库时，系统自动将model数据库中的全部内容复制到新建数据库中。

2、日志文件不包括在文件组内，日志文件和数据文件是分开管理的。

3、 tempdb是临时数据库，用于保存临时对象或中间结果集，并为数据的排序等操作提供一个临时工作空间。每次启动SQL Server时都会重新创建tempdb数据库。

4、唯一索引的作用是保证索引键值的不重复。在多列唯一索引的情况下，可以确保索引列中每个值的组合都是唯一的。唯一索引和非聚集索引之间没有限定关系。

5、**一个表上只能有一个聚集索引，在表上建立聚集索引会引起表中数据的物理排序**、**在一个表上可以同时有聚集索引和非聚集索引。**

**6、**唯一索引既可以是聚集的也可以是非聚集的

7、一个表或视图只允许同时有一个聚集索引。

![20210920162009935.gif][]

### 六、安全管理 ###

1、 guest是SQL Server中一个特殊的数据库用户。

2、GRANT UPDATE ON T1 TO U1语句的作用是用户U1具有对表T1各列数据进行修改的权利，在SQL Server中，拥有UPDATE权限的用户并不意味着一定拥有SELECT权限。

3、Oracle的安全控制机制可分为数据库级的**安全控制、表级、行级和列级的安全控制**。数据库级的安全性通过用户身份认证和授予用户相应系统权限来保证，而表级、行级、列级的安全性是通过授予或回收对象特权保证的。Oracle数据库中的用户按其操作权限大小可分**DBA用户和普通用户**。

4、REVOKE SELECT, INSERT ON T1 FROM U1 ：要收回用户U1对T1表的查询和插入权限

5、SQL Server 2008的身份验证模式有"**Windows身份验证模式**"和"**混合身份验证模式**"两种。混合身份验证模式是指SQL Server允许Windows授权用户和SQL授权用户登录到SQL Server数据库服务器。

6、 数据库级角色，db\_datereader具有查询数据库中所有用户数据的权限。

![20210920163912729.gif][]

### 七、运行维护与优化 ###

1、数据库**重组**是指按照系统设计要求对数据库存储空间进行全面调整，如调**整磁盘分区方法和存储空间、重新安排数据的存储、整理回收碎块**等，以提高数据库性能。

2、数据库的**重构**是指由于数据库应用环境的不断变化，增加了新的应用或新的实体，取消了某些应用，有的实体与实体间的联系也发生了变化等，使得原有的数据库设计不能满足新的需求，此时需要调整数据库的模式和内模式。

3、数据库的重组并不修改数据库原有设计的逻辑结构和物理结构，而数据库的重构则不同，它可以部分修改数据库的模式和内模式。

4、增加数据库内存数量可以在一定程度上**降低**数据库服务器**IO操作**

5、 派生性冗余列是指表中增加的列由表中的一些数据项经过计算生成。它的作用是在查询时减少连接操作，避免使用聚合函数。由于增加了派生性冗余字段，破坏了规范化规则。

6、 除了删除或创建索引，以及非聚簇索引和聚簇索引的相互转换外，通过**重建索引**可以**改进系统的性能**。

7、 水平分割是根据数据行的使用特点进行分割，分割之后所得的所有表的结构都相同，而存储的数据不同。

水平分割会给应用增加复杂度，特别在查询所有数据时需要Union（并）操作；大部分情况是局部使用，只有很少情况才全局查询时适合使用此方法。

而**垂直分割是根据列的特点进行分割**，分割之后所得的所有表中除了都含有主码列外其余列都不同，**其查询时会减少I/O次数**，但其缺点是查询所有数据时需要Join (连接）操作。

8、在数据库设计阶段，主要强调的是高效率利用存储空间，减少数据的冗余，减少数据的不一致，这个过程也就是规范化的过程。

规范化关系解决了数据维护的异常，并使冗余最小化，然而，可能会出现数据处理性能下降的问题。

在大多数情况下，数据库系统需要考虑的第二个目标是高效率的数据处理，反规范化处理，如增加派生冗余列，增加冗余列，重新组表、分割表和新增汇总表可以有效地提高查询效率。

9、 数据库运行维护工作主要包括数据库的**转储和恢复；数据库的安全性和完整性控制；数据库性能的监控分析和改进；数据库的重组和重构**。

10、数据库设计过程中，提高数据库安全性的同时不一定能提高数据库性能。数据库应用系统正式投入运行后，开发团队还要参与此应用后期工作，如数据库的优化需要开发团队的配合。

数据库维护的主要工作内容是日常维护、系统监控与分析、系统性能优化调整、系统进化（升级）等工作。数据库数据备份只是属于数据库日常维护的一小部分。

11、维护工作主要包括：**数据库的转储和恢复；数据库的安全性和完整性控制；数据库性能的监控分析和改进；数据库的重组和重构**。

12、将数据文件和索引文件放置在同一磁盘上不利于提高查询效率。将数据文件与日志文件放在不同的磁盘有利于查询效率。

13、当数据库表更新大量数据后，删除并重建索引可以提高查询速度。

14、SQL Server提供了一套综合的工具，**用于监视SQLServer**中的事件和优化物理数据库的设计。

15、集中式数据库的查询处理中，主要代价是CPU代价和I/O代价。在分布式数据库系统中，由于数据分布在多个不同的场地上，需要在各站点中传输数据，所以通信代价尤为关键。

16、水平分割是对一张表在**水平方向**分割，分割后得到的表结构相同，查询时需要使用**UNION**运算；

垂直分割则是对一张表在**垂直方向**分隔，分割后每张表中除主码列外，其他列均不同，查询时需要使用**JOIN**运算。

分割的目的是提高性能

### 八、故障管理、备份与恢复 ###

1、在制定**备份策略**时，除考虑使用备份恢复时丢失的数据量外，还需考虑数据库备份所需的时间。

2、**检查点技术**大幅度**减少**了数据库完全恢复时所必须执行的**日志部分**。

3、**静态转储**虽然保证了数据的**有效性**，但是却是以**降低**数据库的**可用性**为代价；**动态**转出虽然提高了数据库的**可用性**，但数据的**有效性**却可能得不到保障。

4、**差量转储**和增量转储相比，**速度慢，占用空间较多**，但恢复速度比增量转储快。

5、创建备份设备的 T-SQL存储过程为sp\_addumpdevice。

6、事务日志备份仅用于完整恢复模式和大容量日志恢复模式，它并不备份数据库本身，只备份日志记录，而且只备份从上次备份之后到当前备份时间发生变化的日志内容。

但是不允许对**大容量操作日志备份进行时点恢复**。结尾日志备份在出现故障时进行，用于防止丢失数据，**可以包含纯日志记录或者大容量操作日志记录**。

7、**日志文件**记录每个事务对数据库的**修改操作**，主要用来实现数据恢复。

8、大容量日志恢复模式只对大容量操作进行最小记录，一般只作为完整恢复模式的附加模式。

9、在SQL Server 2008系统数据库中，只有**tempdb**数据库不需要备份，其他的都需要备份。

10、事务日志备份并不备份数据库本身，它只备份日志记录，而且只备份从上次备份之后到当前备份时间发生变化的日志内容。

11、简单恢复模式不支持事务日志备份。

12、对于**大型数据库**，**差异备份**的间隔通常比完整数据库备份的**时间更短**，这可**降低**数据**丢失风险**。

13、比如对**master**数据库的备份，通常在执行了创建、修改或删除数据库的操作，或是更改了服务器或数据库的配置、建立或更改登录账户等操作后，都应对它进行备份。

14、简单恢复模式可最大限度的减少事务日志的管理开销，因为这种模式不备份事务日志。

15、事务日志备份仅用于完整恢复模式和大容量日志恢复模式，不备份数据库本身，只备份日志记录，而且只备份从上次备份之后到当前备份时间发生变化的日志内容。

1、聚集函数的并行化计算可以釆取"先分后合"的方法。

2、位置独立性、数据分片独立性和数据复制独立性是使分布式数据库具有分布式透明性的主要因素。

分布式数据库最基本特征是本地自治、非集中式管理及高可用性。

3、SaaS，即Software as a Service，中文意思是软件即服务。

###    每文一语 ###

> **准备是机会的前提**

[icon-default.png_t_L892]: /images/20220828/bf09795cf6ea499099295992155599d3.png
[https_marketing.csdn.net_p_98bd30353e7cb998b6070a89e8b91edb_icon-default.png_t_L892_https_marketing.csdn.net_p_98bd30353e7cb998b6070a89e8b91edb]: https://marketing.csdn.net/p/98bd30353e7cb998b6070a89e8b91edb
[20210918202037141.gif]: /images/20220828/df7ae4d357064d8183644dbed95b578f.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA546L5bCP546LLTEyMw_size_10_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/adf83753e1fb4a0e90fbf1fcd76abe02.png
[2021091821162742.gif]: /images/20220828/3d933c242f0c416a86bc030ef968e65a.png
[20210920162009935.gif]: /images/20220828/137095689d234b7aa1cddff721e1e5ee.png
[20210920163912729.gif]: /images/20220828/7a88ee1d07b24a5f8e4ae2c8779dd665.png