Database之SQLSever：SQLSever数据库管理人员国家职业资格证书中级、高级考试知识点总结
SQLSever数据库管理人员中级、高级考试知识点总结
基础概念
SQL语言具有数据定义语句、数据操纵语句、数据控制语句的功能。交互式SQL、嵌入式SQL两种使用方式。其中between and表示包括两边参数。
- 数据操作语言DML：增删改查
- 数据控制语言DCL：
- 数据模型的三要素：数据结构、数据操作、数据的约束条件。数据模型是现实世界数据特征的抽象。
数据库用户与服务器角色、数据库角色的关系：如果数据库用户User1是固定服务器角色sysadmin(若db_owner)的成员，并创建表T1，则T1属于dbo，并dbo.T1(则User1.T1)进行限定；
触发器：触发器的特点与表相关联、自动激活、不能直接调用和作为事务的一部分；触发器不需要人工激活而是自动激活；
数据库中的封锁机制：数据库中的封锁机制是并发控制的主要方法；对并发操作若不加以控制，可能会带来数据的死锁问题；数据库防止死锁的方法是禁止两个用户同时操作数据库；预防死锁通常有一次封锁法和顺序封锁法；死锁的诊断和解除方法是超时法和等待图法。
安全级别：B1级强制存取控制(MAC)；C1级是非常初级的自主存取，C2是安全产品的最低档次(DAC)，
数据库安全性控制常用的方法和技术：实现数据库安全性控制常用的方法和技术有用户标识和鉴别、存取控制、视图机制、审计、数据加密，还有授权与回收。
流式文件：构成文件的基本单位是字符，这一类文件称为流式文件。文件系统的多级目录结构是树形结构。
数据库的完整性和实体完整性：数据库的完整性是指数据的正确性和相容性；实体完整性要求主属性不能取空值，可以通过定阿义主码保证；实体完整性和参照完整性属于静态关系约束；
DBMS：数据库的核心是数据库管理系统DBMS(DB+User)；从模块来看，DBMS由查询处理器和存储管理器两大部分组成；对数据库经常性维护工作DBA；RDBMS查询处理的4个阶段查询分析、查询检查、查询优化(包括代数优化、物理优化)、查询执行。为了提高效率，关系数据库必须进行查询优化处理。
- (1)、基于DBMS的数据库完整性设计大体分为需求分析阶段、概念结构设计阶段(最常用的数据模型是实体联系模型ER图、椭圆代表属性类型)、逻辑结构设计阶段(如将ER图转为关系模式模型、选择某种索引方法等)。
- (2)、事务是DBMS进行的基本工作单位；事务的原子性指事务中包括所有的操作要么都做、要么都不做。事务日志的用途是数据恢复。
数据库系统的数据独立性：指物理独立性、逻辑独立性。数据库系统达到了数据独立性是因为采用了三级模式结构。在数据库三级模式间引入二级映像的主要作用是提高数据与程序的独立性。
常考知识点
- 注册一个SQLServer数据库服务时，需要指定其各方面的属性包括所要连接的数据库服务器名称、网络协议。
- 索引是为了加速对数据表中数据行的检索而创建的一种分散的存储结构。在select语句中使用group by 字句(或外键约束)时，SQLServer不会自动创建索引；但是创建unique约束(或主键约束)时，SQLServer会自动创建索引。
- 安全控制技术的审计，分为用户级审计和系统级审计。
- 数据加密的方法有替换方法和置换方法。
- 数据库中只存放视图的定义。
- 数据库镜像可用于进行数据库恢复或并发操作。镜像既能提高安全性也能提高速度。
- 数据库镜像需要两个数据库，主体数据库、镜像数据库，两个数据库驻留在不同的服务器上，可同时进行不同的事务处理。
- %取余，/取商
数据库的三种模型
1、网状模型数据库
用有向图表示实体和实体之间的联系的数据结构模型称为网状数据模型。 可以存在两个或者多个节点(即一个以上的结点)没有父节点、允许单个节点存在多于一个父节点；
2、层次模型数据库
数据库中有且仅有一个记录型无双亲，称为根节点。其他记录型有且仅有一个双亲。在层次模型中从一个节点到其双亲的映射是惟一的，所以对每一个记录型（除根节点外）只需要指出它的双亲，就可以表示出层次模型的整体结构。
3、关系模型数据库
网状数据库和层次数据库已经很好地解决了数据的集中和共享问题，但是在数据独立性和抽象级别上仍有很大欠缺。用户在对这两种数据库进行存取时，仍然需要明确数据的存储结构，指出存取路径。而后来出现的关系数据库较好地解决了这些问题。关系数据库理论出现于60年代末到70年代初。关系数据模型提供了关系操作的特点和功能要求，但不对DBMS的语言给出具体的语法要求。对关系数据库的操作是高度非过程化的，用户不需要指出特殊的存取路径，路径的选择由DBMS的优化机制来完成。 1970年，IBM的研究员E.F.Codd博士发表《大型共享数据银行的关系模型》一文提出了关系模型的概念，论述了范式理论和衡量关系系统的12条标准，如定义了某些关系代数运算，研究了数据的函数相关，定义了关系的第三范式，从而开创了数据库的关系方法和数据规范化理论的研究，他为此获得了1981年的图灵奖。
- (1)、同一个关系模型的任意两个元祖值不能全同。
- (2)、关系模型中的候选码，若关系中的某一属性组的值能唯一标识一个元组,则称该属性组为候选码。外码不一定要与相应的主码同名。
- (3)、关系模型数据库管理系统能实现的专门的关系运算包括选择、投影、连接。
- (4)、关系是关系模型的一个实例。
- (5)、关系模型的参照完整性规则，要求关系中不允许引用不存在的元祖，即元祖非空。
- (6)、关系数据库规范化理论主要解决是如何构造处一个合适的数据逻辑结构。
- (7)、关系数据库设计中的陷阱是指信息重复和不能表示特定的信息。
- (8)、关系演算是用谓词来表达查询要求的方式。
传统的集合运算、专门的关系运算
| 传统的集合运算 | 传统的集合运算是二目运算，包括并、交、差、广义笛卡尔积四种运算，其中笛卡儿积用时最长。 |
| 专门的关系运算 | 专门的关系运算(Specific relation operations)，包括选择、投影、连接、除等。 σF(R) = {t|t∈R ∧ F(t)='真'} ΠA(R) = { t[A] | t∈R } R— (R—S) R÷S={t_r [X]|t_r∈R?π_r （S）?Y_x} |
在数据库三级模式
- 模式：在数据库三级模式(所以数据独立性)结构中，对数据库中全部的数据逻辑结构、特征进行描述。
- 内模式：最接近于物理存储设备一级的结构，是对数据库物理存储方式的描述。
- 外模式：模式的逻辑子集通常称为外模式。对数据库不同用户能够看得见和使用的局部数据的逻辑结构和特征的描述。
(1)、要保证数据库的数据逻辑独立性：需要修改模式与外模式的映射。
数据依赖
指在程序引用数据之前处理过的数据的状态；在编译学中，数据依赖是数据分析的一部分；数据依赖有三种即流依赖、反依赖、输出依赖。
- 在关系模式R中，函数依赖X→Y的语义是在R的每一个关系中，若两个元祖的X值相等，则Y值也相等。
- 多值依赖毛病在于插入异常、删除异常、数据冗余太大。
数据库范式1NF、2NF、3NF、BCNF、4NF、5NF
数据库范式分为1NF2NF3NFBCNF4NF5NF，因为从1NF开始向右条件是要消除函数，故第一个最内容最大。
1、模式分解的算法包括合成法(3NF，保持函数依赖)、分解法(BCNF无损；4NF无损)、
- 范式NF：“符合某一种级别的关系模式的集合，表示一个关系内部各属性之间的联系的合理化程度”，实际上粗略地理解为一张数据表的表结构所符合的某种设计标准的级别，但数据的规范化不会提高数据库的安全性。就像家里装修买建材，最环保的是E0级，其次是E1级，还有E2级等等。符合高一级范式的设计，必定符合低一级范式，例如符合2NF的关系模式，必定符合1NF。
- 1NF：所有的属性均有原子性 ；符合1NF的关系中的每个属性都不可再分。 1NF是所有关系型数据库的最基本要求，你在关系型数据库管理系统（RDBMS），例如SQL Server，Oracle，MySQL中创建数据表的时候，如果数据表的设计不符合这个最基本的要求，那么操作一定是不能成功的。也就是说，只要在RDBMS中已经存在的数据表，一定是符合1NF的。 但是仅仅符合1NF的设计，仍然会存在数据冗余过大，插入异常，删除异常，修改异常的问题
- 2NF：合更高一级的范式（2NF），这就是所谓的“规范化”。满足1NF的前提上，如果依赖于主键，则需要依赖于所有主键，不能存在依赖部分主键的情况 。1NF→2NF，，消除了非主属性对于码的部分函数依赖。根据2NF的定义，判断的依据实际上就是看数据表中是否存在非主属性对于码的部分函数依赖。若存在，则数据表最高只符合1NF的要求，若不存在，则符合2NF的要求。
- 3NF：满足2NF，非主键外的所有字段必须互不依赖，非键属性不能依赖于非键属性。2NF→3NF，消除了非主属性对于码的传递函数依赖。某些特殊情况下，即使关系模式符合 3NF 的要求，仍然存在着插入异常，修改异常与删除异常的问题，仍然不是 ”好“ 的设计。
(1)、当关系模式R中的属性全部是主属性，则R的最高范式必定是3NF。
(2)、若函数依赖集中所有候选关键字都是决定因素，则R的最高范式是4NF。 - BCNF范式： 所有属性（包括非键属性与键鼠性）不能依赖于非键属性。3NF→BCNF，部分函数依赖与传递函数依赖。在函数依赖中，已消除了插入和删除异常。
- 4NF：删除了不必要的数据结构：多值依赖。要求分解具有无损连接性，可以达到4NF。
多种数据库恢复模式：简单恢复、批日志恢复、完全恢复
SQL Server2005支持多种数据库恢复模式，如简单恢复、批日志恢复、完全恢复，不包含增量恢复。
- 简单恢复：数据恢复性能最好的模式。仅使用数据库备份将数据恢复到上一次备份的时刻模式；仅使用了数据库备份或差异备份，而不涉及事物日志备份的模式。
- 批日志恢复：批量日志恢复模式类似完整恢复模式，都预期会有大批量的数据修改操作(例如，创建索引，SELECT INTO，INSERT SELECT，BCP，BULKINSERT)，在这种情况下可以最小化日志记录量。
- 完全恢复：通过使用数据库备份和事物日志备份将数据恢复到发生失败的时刻，从而不造成数据丢失的模式。
启动SQLServer数据库服务时，可设定一些启动选项
参考文章：https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms190737(v=sql.105)?redirectedfrom=MSDN
- -n 记忆no
- 不要使用 Windows 应用程序日志来记录 SQL Server 事件。如果使用 -n 启动 SQL Server 实例，则建议您同时使用 -e 启动选项。否则，将不会记录 SQL Server 事件。
- -c 记忆命令command
- 缩短从命令提示符启动 SQL Server 时的启动时间。通常，SQL Server 数据库引擎通过调用服务控制管理器作为服务启动。由于从命令提示符启动时，SQL Server 数据库引擎不作为服务启动，因此请使用 -c 跳过此步骤。
- -m 记忆Single user mode
- 在单用户模式下启动 SQL Server 实例。在单用户模式下启动 SQL Server 实例时，只能连接一个用户，并且不启动 CHECKPOINT 进程。CHECKPOINT 保证将已完成的事务定期从磁盘缓存写入数据库设备。
- -s 记忆Naming instance
- 用于启动 SQL Server 的命名实例。如果 -s 参数未设置，则将尝试启动默认实例。必须在命令提示符下切换到实例相应的 BINN 目录，然后才能启动 sqlservr.exe
- -e 记忆error
- error_log_path，错误日志文件的完全限定路径
- -I 记忆maser主人即我I
- master_log_path，master 数据库日志文件的完全限定路径
bcp 实用工具
bcp 实用工具可以在 Microsoft SQL Server 实例和用户指定格式的数据文件间大容量复制数据。 使用 bcp 实用工具可以将大量新行导入 SQL Server 表，或将表数据导出到数据文件。
bcp用法: bcp {dbtable | query} {in | out | queryout | format} 数据文件
[-m 最大错误数] [-f 格式化文件] [-e 错误文件]
[-F 首行] [-L 末行] [-b 批大小]
[-n 本机类型] [-c 字符类型] [-w 宽字符类型]
[-N 将非文本保持为本机类型] [-V 文件格式版本] [-q 带引号的标识符]
[-C 代码页说明符] [-t 字段终止符] [-r 行终止符]
[-i 输入文件] [-o 输出文件] [-a 数据包大小]
[-S 服务器名称] [-U 用户名] [-P 密码]
[-T 可信连接] [-v 版本] [-R 允许使用区域设置]
[-k 保留空值] [-E 保留标识值]
[-h"加载提示"] [-x 生成xml 格式化文件]
