2022年计算机等级考试四级教程数据库工程师考试重难点整理

全国计算机等级考试四级教程数据库工程师考试重难点整理

第1章

引言

1.数据是描述现实世界事物的符号记录，是用物理符号记录下来的可以识别的信息。

数据是信息的符号表示，是载体；信息是数据的语义解释，是内涵。

2.数据模型是对现实世界数据特征的抽象，是数据库系统的形式框架，用来描述数据的一组概念和定义，包括描述数据、数据联系、数据操作、数据语义以及数据一致性的概念工具。

满足三条件：比较真实地模拟现实世界；易于人们理解；易于计算机实现

三个组成要素：数据结构（静态，数据对象本身结构及之间的联系）、数据操作（对数据对象操作及操作规则的集合）和完整性约束（语义约束：数据模型、数据内部及之间联系）

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

外模式是对数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述，是与某一应用有关的数据的逻辑表示，也称为子模式、用户模式或用户视图。

内模式是对数据库中数据的物理结构和存储方式的描述，也称为物理模式或存储模式。

当数据库模式发生变化时，通过调整外模式/模式间的映像关系，使得应用程序不必随之修改，从而保证数据与应用程序间的逻辑独立性，简称数据的逻辑独立性。

当数据库数据的物理存储结构改变时，通过调整模式/内模式映像关系，保持数据库模式不变，使数据库系统的外模式和应用程序不随之改变，保证数据与应用程序间的物理独立性，简称数据的物理独立性。

4.数据库是存储在计算机内的共享数据集合，数据库管理系统是一种数据管理系统软件。数据库系统则是在计算机系统中引入数据库后的软硬件系统构成，包括了数据库、数据库管理系统和数据库应用程序。

5.DBMS的主要功能有数据定义、数据操纵、数据库运行管理与控制、数据库建立与维护。

DBMS包括查询处理器和存储管理器。查询处理器实现面向用户的查询分析处理和优化功能。存储管理器为用户和应用程序提供了访问存储在数据库文件中的应用数据的接口。

6.关系模型

用二维表表示实体及实体之间的联系，现实世界中的每个客观对象对应表中的一行叫做一条记录，表中的每个列（属性）描述对象类的某一特征，列的值（属性的取值）刻画和描述客观对象的某一具体的特征。

优缺点：建立在“集合”“关系”上，严格成熟的理念基础；用关系表示实体及之间联系，简单易懂；存储路径透明，较好的数据独立性和安全保密性；查询效率低于非关系模型。

第2章

数据库应用系统生命周期

2.1

软件生命周期是指软件产品从考虑其概念开始，到该产品不再使用的整个时期。一般包括概念阶段、需求阶段、设计阶段、实现阶段、测试阶段、安装部署及交付阶段、运行阶段与维护阶段。数据库应用系统需求是指用户对数据库应用系统在功能、性能、行为、设计约束等方面的期望和要求：数据及数据处理、业务、性能、其他存储安全备份与恢复等。

2.2数据库应用系统生命周期模型

1.瀑布模型原理，项目规划、需求分析、系统设计、实现与部署、运行管理与维护五部分

2.快速原型模型和增量模型原理，允许渐进、迭代地开发DBAS。

3.根据DBAS的软件组成和各自功能，细化DBAS需求分析和设计阶段，引入了数据组织与存储设计、数据访问与处理设计、应用设计三条设计主线，分别用于设计DBAS中的数据库、数据库事务和应用程序。

4.设计阶段细分为概念设计、逻辑设计、物理设计三个步骤，每一步涵盖三条设计主线。

第3章

需求分析及功能建模方法

3.1数据元素（列）是数据处理中的最小单位。

3.2DFD图：数据流（标有名字的箭头）、处理、数据存储（横圆柱）、数据源/终点（棱形）

3.3IDEF0图：更好地理解需求；ICOM（输入、控制、输出、机制）码；至少一个控制和输出箭头。A-0；A0（顶层图）；A1；A11

3.4DFD与IDEF0的比较：结构化分析思想：自顶而下逐级细化

1.DFD图用箭头也叫数据流来描述数据移动的方向、数据处理之间的数据依赖关系，IDEF0图也用箭头表示数据流，但不强调流或顺序，强调数据约束，箭头语义更丰富。

2.DFD模型有四种元素，IDEF0图只有两种：箭头和活动

3.IDEF0图更加规范。其概念、建模方法、画图规则等均有说明和规定

4.IDEF0模型结构更清楚，便于理解和沟通

3.5结构化分析及建模方法的优点：

1）不过早陷入具体的细节，从整体或宏观入手分析问题，如业务系统的总体结构，系统及子系统的关系。

2）通过图形化的模型对象直观地表示系统完成什么功能，方便系统分析员理解和描述系统。

3）模型对象不涉及太多技术术语，便于用户理解模型。

第4章

数据库概念设计与数据建模

4.1概念设计的目的和任务：面向现实世界，理解和描述应用领域中的数据需求，分析确定系统需要存储和处理什么数据。过程：确定实体集；确定联系和联系类型；建立由信息模型表示的业务模型；确定实体集属性；优化信息模型。

4.2实体集描述具有相同属性特征和性质的事物集合；属性描述实体集具有的某一特征或性质

4.3IDEF1X：标定型（子依父实例才标定）（确定型）；非标定型（确定型）；分类；不确定

第5章

关系数据库逻辑设计

5.1关系数据库：按照二维表格组织和存储的相互关联的关系（表）的集合。

关系数据库采用关系模型在计算机中组织、存储、处理和管理数据。

5.2主码约束：惟一性；最小性，不能取空值，并且取值要唯一

外码约束：如果两个关系R和S，X是R的属性或属性组，且X不是R的码，但X是S的码，则称X是R的外码。或者取空值，或者等于S中某个元组的主码的值。

5.3逻辑设计的内容：定义和描述数据库的全局逻辑结构、数据之间的关系、数据的完整性等

目的：得到实际数据库管理系统可以处理的数据库模式。

步聚：ER图转化关系模式；优化关系模式；数据完整性设计；外模式设计

5.4关系模式描述关系的静态结构，由模式名、属性、完整性约束组成，关系是一个表中记录的集合，关注和强调的是值（模式实例）的集合，也是关系模式在某一时刻状态的反映。

5.5关系的完整性（完整性约束）：对关系所满足条件的定义。作用：限定和检查数据库所含实例的合法性和正确性。静态：主码、外码、域（用户定义）；动态：基于业务规则

5.6伪传递规则：X－Y，YW－Z，则XW－Z成立。

5.7规范化设计理论的主要内容是范式，即关系模式满足的条件，它是数据库逻辑设计的指南，也是衡量关系模式设计优劣的标准。目的：消除数据冗余及避免操作异常，基本过程：对关系进行分解。一个低级范式的关系模式通过分解（投影）方法可转换成多个高一级范式的关系模式的集合，这个过程叫规范化。

5.81NF：R的每一属性均不可再分；

2NF：是1NF且每个非主属性完全依赖于主码。在1上消除非主属性对主码的部分函数依赖；

3NF：是1NF且每个非主属性只依赖于主码。在2上消除非主属对主码的传递函数依赖。

5.9ER模型转换关系模型：实体集转为同名关系；联系转为一个关系，各实体集的码及联系的属性转为关系的属性（1：1均可、1：n取n、m：n取集）；合并具有相同码的关系。

第6章

存储技术与数据库物理设计

6.1物理设计是在具体的硬件环境、OS、DBMS约束下，基于逻辑设计，设计具体数据存储结构和存取方式。目的：占用空间少、访问效率高、维护代价低。主要步骤有数据库逻辑模式调整、文件组织与存取设计、数据分布设计、安全模式设计、确定系统配置、物理模式评估。

6.2索引技术（Indexing）是一种快速文件访问技术，它将文件记录在某个或某些域（或称为属性）上的取值与该记录的物理地址直接联系起来，提供了一种根据记录域的取值快速访问文件记录的机制。索引文件是一种利用索引技术支持快速文件访问的文件组织和存取方法。

索引加快了查询记录却减慢了数据更新速度，本身还占用一定的存储空间。

6.3文件组织：如何将关系数据库中的关系映射为操作系统中的数据库文件，及管理文件。

文件结构：如何将DB文件中的逻辑记录映射到物理文件的中磁盘块。

文件存取：针对某种结构的DB文件，如何查、添删改其中的逻辑记录

6.4数据字典：数据库各类对象的描述信息、数据库管理系统的控制信息。包括关系模式信息、与视图描述有关的信息、关系的存储结构和存取方法信息、完整性约束、安全性有关的信息、数据库运行统计信息。

作用：DBA用来监视DBMS的使用情况并协助完成管理工作；一般用户可用于查阅部分数据库结构信息；DBS运行时各子系统频繁使用以完成相应的存储和查询处理功能。

6.5DBMS的三种完整性控制机制：CHECK子句、断言、触发器

断言语句：Createassertion断言约束名check(…)

6.6堆文件：数据量少且操作频繁；批量加载数据（先选为堆文件再调整文件结构）

顺序文件：查询条件定义在查找码上；快速的二分查找

散列文件：基于散列域值的等值匹配，特别是访问顺序是随机的。非精确查询；非散列域

B－树和B+－树：大数据量基本表；聚焦文件：多表连接操作

6.7有序索引技术利用索引文件实现查找码取值到记录物理地址间的映射关系。索引文件由索引记录组成，每个记录中的索引项记录了某个特定的查找码值和具有该值的数据文件记录的物理地址。

当需要访问数据文件中某个数据记录时，先根据查找码值查阅索引文件，找到对应的索引项，然后从索引项中找出数据记录在数据文件中的物理地址．根据这个地址访问数据记录。

6.8散列技术是一种快速文件访问技术，它利用散列函数实现文件记录域取值到记录物理地址间的直接映射关系。当需要访问数据文件中查找码值为si的某个或某些文件记录时，将si作为散列函数h的输入计算得出的散列函数输出值h(si)就是文件记录在数据文件中的物理地址。

6.9权限：允许用户对一给定的数据库对象可执行的操作（查询、添删改、新建、备份等）。

第7章

数据库应用系统功能设计

7.1软件体系结构：软件架构＝{构件，连接件，约束}

7.2软件设计包括系统的总体结构设计、系统的过程设计、系统的数据设计三方面内容（+人机界面设计），从工程管理的角度，分为概要设计、详细设计

7.3应用软件分为数据库事务和应用程序。后者一方面可以与数据库事务协调合作，另一方面还可实现与数据库访问无关的功能，如通信、人机交互。

7.4事务：具有逻辑独立功能的一系列操作的集合，实现了某些特定的业务规则。

7.5事务概要设计的核心是辨识和设计事务自身的事务处理逻辑，采用面向数据流的程序设计方法设计事务内部的数据处理流程和结构。

7.6C/S结构特点：数据管理和数据处理被分在客户端和服务器上；服务器可支持多个客户端；客户端也可访问多个服务器；客户端＝人机交互+数据处理

B/S结构特点：表示层，WEB浏览器；功能层，WEB应用服务器；数据层，DBMS服务

优点：实现人面交互、应用业务逻辑处理、数据管理三层分离，提高了系统的可维护性；用WEB浏览器可访问多个异构应用平台，解决了跨平台数据管理问题。

第8章SQL

8.1SQL＝StructuredQueryLanguage，结构化查询语言

8.2二进制字符串是用十六进制表示的，0x前缀

8.3创建表：CREATETABLETable_Card

(CardIDvarchar(8)NOTNULL,

StartDatedatetimeNOTNULL,

EndDatedatetimeNOTNULL,

ScoreintNOTNULLCHECK(Score>=0)DEFAULT0,

Statechar(1)NOTNULLCHECK(Statein(0,1,2)),

Sexchar(1)CHECK(Sexin(‘男’，’女’)),

AgeintCHECK(Agebetween[16,85]),

CustomerIDvarchar(8),

CHECK(EndDate>StartDate),

PRIMARYKEY(CardID),

FOREIGNKEY(CustomerID)REFERENCESTable_Customer(CustomerID))

删除表：DROPTABLETable_Card

修改表：ALTERTABLETable_Card

ADDCardTypechar(4)NULL

ALTERTABLETable_Card

ALTERCOLUMNCardTypechar(8)

ALTERTABLETable_Card

DROPCOLUMNCardType

8.4SELECTCnameFROMTablb_CustomerWHERE

AddressIN(‘海淀区’,’朝阳区’)AND

ageBETWEEN40AND50AND

CnameLIKE‘[王张李]%’

(‘王__’)

ORDERBYStartDateASC,EndDateDESC

8.5除COUNT（*）不忽略空值外，其他函数COUNT（DISTINCT列名）、SUM（）、AVG（）、MAX（）、MIN（）均忽略空值。均返回单值。

SELECTSUM(Score),MAX(Age),MIN(Age),AVG(Age),COUNT(DISTINCT..)FROMTable

8.6分组（GROUPBY）依据列不能是text,ntext,image和bit类型的列。先分组后计算

统计每种类别商品的商品数，只列出商品数大于4个的商品类别编号和商品数：

SELECTGoodClassIDas商品类别编号，COUNT（*）as

商品数FROMTable_Goods

GROUPBYGoodClassIDHAVINGCOUNT(*)>4

8.7自连接：查询与王晓在同一个区的顾客的姓名及地址：

SELECTT2.Name,T2.AddressFROMTable_CustomerasT1JIONTable_CustomerasT2

ONT1.Address=T2.AddressWHERET1.Name=‘王晓’ANDT2.Name!=’王晓’

子查询：一个SELECT语句嵌套在一个SELECT、UPDATE、INSERT或DELETE语句中

SELECTName,AddressFROMTable_CustomerWHEREAddressIN

(SELECTAddressFROMTable_CustomerWHEREName=’王晓’)ANDName!=’王晓’

8.8外连接：LEFTOUTERJOIN左外连接（左表全输出）RIGHTOUTERJOIN右外连接

8.9子查询：

[NOT]IN（）集合

不相关子查询，先内后外

WHERE列

＝/</>()比较

不相关子查询，先内后外

[NOT]EXIST（SELECT*）存在

相关子查询，先外后内

8.10修改数据：INSERT[INTO]表名[列名表]VALUES（值列表）

UPDATE

表名SET

列名＝表达式[WHERE子句]/DELETE[FROM]表名[WHERE子句]

分情况修改：UPDATETable_Goods

SETSalePrice=CASEGoodsClassName

WHEN‘家用电器’THENSalePrice–SalePrice*0.05

WHEN‘服装’THENSalePrice+SalePrice*0.06

ELSESalePrice

END

FROMTable_GoodsaJIONTable_GoodsClassbONa.ClassID=b.ClassID

8.11创建索引：CREATE[UNIQUE][CLUSTERED][NONCLUSTERED]INDEX索引名

ON表名

（列名[顺序(ASC/DESC)]）

8.12视图：由从数据库基本表中选取出来的数据组成的逻辑窗口，是一个虚表，数据库只存放视图的定义而不存放视图包括的数据。对视图的操作最终都会转换为对基本表的操作。

CREATEVIEW视图名[视图列名表]

AS

SELECT语句

[WITHCHECKOPTION]

作用：简化数据查询语句；使用户从多角度观察同一数据；提高了数据安全性（屏蔽掉敏感数据）；提供一定程度的逻辑独立性

8.13定点数：numeric(p,q)或decimal(p,q),p为精度（数字位个数），不大于38，q为小数位个数；bit类型：只存储1和0，不多于8个bit列则只用1个字节存储

8.14Char(n)代表的是普通字符编码按定长存储的字符串，“n”的含义是字符的个数，固定占用n个字节的空间。varchar(n)

代表的是普通字符编码按不定长存储的字符串，“n”的含义也是字符的个数，按字符的实际长度占用空间。

第9章

事务高度与并发控制

9.1调度：定义在多个事务上的调度是这些事务的所有操作的一个执行序列，代表了这些操作的执行顺序；冲突操作：事务Ti的操作Ii与事务Tj的操作Ij是冲突的，当且仅当Ii和Ij访问数据库中同一个数据项Q，并且Ii和Ij中至少有一个是写操作write(Q)；冲突可串行：一个并发调度冲突等价于某个串行调度（判断一个并行调度是否正确）

死锁是指数据库系统中部分或全部事务由于无法获得对需要访问的数据项的控制权而处于等待状态，并且将一直等待下去的一种系统状态。

9.2ACID：Atomicity原子性；Consistency一致性；Isolation隔离性；Durability持久性

9.31级加锁协议要求事务T在修改数据项Q之前必须先对Q加X锁，直到事务结束才释放该锁。事务结束包括正常结束（commit）和非正常结束（rollback）。但事务如果是只读Q而不对其进行修改，是不需要对Q加锁的。

2级加锁协议是在1级加锁协议基础上，要求事务T在读取数据项Q之前必须先对其加S锁，读完Q后可以立即释放S锁。

3级加锁协议则是在1级加锁协议基础上，要求事务T在读取数据项Q之前必须先对其加S锁，但是需要等到事务结束时才释放该S锁。

9.42阶段锁协议将每个事务的执行过程分为加锁阶段和解锁阶段。在加锁阶段，事务可以申请获得数据项上的任何类型的锁，但不允许释放任何锁。在解锁阶段，事务可以释放任何数据项上的任何类型的锁，但不能再申请任何锁。每个事务开始执行后就进入了加锁阶段。当第一次释放锁后，即转入解锁阶段。

9.5解决死锁主要采用死锁预防和死锁检测与恢复两类方法。

死锁预防利用死锁预防协议，通过破坏死锁产生的必要条件来避免系统进入死锁状态。

一次性加锁法；顺序加锁法

死锁检测与恢复则是允许系统进入死锁状态，并且定期检查系统是否发生死锁。当发现系统发生死锁后，采取相应的恢复机制使系统摆脱死锁状态。

9.6活锁产生的原因是在系统非死锁状态下，某些事务由于始终无法获得对所需访问的数据项的控制权而长期等待下去，无法继续执行。

9.7锁粒度大：被加锁数据项少、事务并发执行度低、系统开销小；锁粒度小则反之

9.8基于锁的并发控制技术的原理P166

第10章

数据库的实施、运行和维护

10.1试运行：功能测试；性能测试

10.2数据库维护：数据库的转储和恢复；数据库的安全性、完整性控制；数据库性能的检测和改善；数据库的重组和重构

10.3数据库安全：行政手段制定规范；权限管理、口令等；维护多个数据副本；防及除毒

10.4数据库重组：按照系统设计要求对数据库存储空间进行全面调整；数据库重构：业务小范围变化需对数据库逻辑结构作必要改变。

10.5数据库监控分析：DBA借助相应工具监测数据库系统的运行情况，对监测数据进行分析，评估整个系统的运行状态，为系统的安全运行和性能优化提供依据。

10.6数据库空间管理：数据量增加和碎片使性能降低；空间溢出会带来灾难性停机故障。

包括：创建修改删除数据库空间、新建移动关联数据文件等。

10.7数据库参数调整：外部调整：CPU、网络；调整内存分配(改善程度大)；调整磁盘I/O(I/O时间是响应时间的最大组成部分)；调整竞争

10.8数据库查询优化：合理使用索引；避免或简化排序（Orderby、Groupby，磁盘排序比内存排序开销大速度慢）；避免相关子查询、外连接（左右连接比内连接消耗大）；存储过程

10.9属于Oracle但不属于SQLServer的逻辑和物理空间结构：表空间、段、区

第11章

故障管理

11.1故障种类：事务内部故障（事务回滚撤消修改）、系统故障（影响事务不坏数据）、介质故障（软件容错、硬件容错）、病毒

11.2系统故障对策：重启，撤消（UNDO）未提交的事务，重做（REDO）已提交的事务

11.3软件容错：备份、日志文件，利用恢复技术；硬件容错：双物理存储设备

11.4恢复基本原理：冗余，即所有数据均可通过存储在别处的冗余数据来重建。

11.5对于经常进行数据操作的数据库：完全转储+差异转储

11.6以记录为单位的日志文件：开始标记（TiBEGINTRANSACTION）、结束标记(TiCOMMIT或者TiROLLBACK)、每个事务的所有操作（Ti，A，50，80）

11.7以数据块为单位的日志文件：存放更新前和更新后的整个数据块。只有事务标识和被更新的数据块，没有操作类型和操作对象。

11.8日志的作用：用来进行业务故障和系统故障恢复；协助后备副本进行介质故障恢复（动态转储必用）；记录操作监视行为分析问题

登记原则：登记次序严格按并行事务执行次序；必须先写日志文件再写数据库

11.9检查点：最大限度减少数据库完全恢复时所必须执行的日志部分（针对系统故障）。

11.10数据库镜像：提高数据库可用性的解决方案（比如介质故障，两台服务器相互备份）

优点：提供完整或几近完整的数据冗余，增强数据保护；发生介质故障时，数据不会丢失且服务不会中断，提高数据库可用性；提高镜像数据库在升级期间的可用性。

双机互备援模式（均为主）；双机热备份模式（一主一备份机）。数据库镜像可用于并发操作。

11.11RAID廉价冗余磁盘阵列：（镜像冗余、）校验冗余：对成员磁盘上的数据执行异或（XOR）操作得到其校验值并存放在另外的校验磁盘上。当某个磁盘发生故障时，只须计算其他磁盘上的校验数据和数据的异或便可重新得到该磁盘的值。

第12章SQLServer2000数据库管理系统

12.1四个服务：SQLServer核心服务；

SQLServerAgent：代理服务，代理定期进行的管理工作；

DTC:DistributedTransactionCoordinator，分布式事务协调器，同一事务访问多个服务器

MicrosoftSearch:全文检索服务

12.2四个版本：企业版（全部功能、大型数据库）、标准版（小部门）、开发版（同企业版，作开发测试系统用，不作生产服务用）个人版（移动环境、本地数据）

12.3服务帐户：使用本地系统帐户：自动取当前登录到Windows的用户，没有Windows的网络访问权限，适用于非网络服务器操作系统（如XP）；使用域用户帐户：使用Windows身份验证设置连接到SQLServer，用户必是Windows系统管理员，适用于网络服务器OS

12.4网络库：在SQLS客户端和服务器间传递网络数据包。服务器可一次监听多个网络库

12.5SQLServer的两大类数据库：系统数据库：DBMS自动创建及维护，存放维护系统正常运行的信息，master（系统级信息）、msdb（支持自动执行任务）、model（所有用户数据库的公共信息）、tempdb（临时数据库）,示例Pubs、Northwind；用户数据库

12.6估算存储空间：SQLServer数据存储单位为页（Page），一个数据页是一块8KB的连续磁盘空间，行不能跨页存储，一行数据的大小不能超过一页的大小。一个表10000行数据，每行3000字节，则需要（10000/2）*8KB=40MB的空间。

12.7主数据文件：扩展名为.mdf，包含数据库系统信息并可存放用户数据库数据，每个数据库只有一个主数据文件。辅助数据文件：扩展名为.ndf，数据量很大时使用，可存放在不同的磁盘驱动器上，以便得利用多个磁盘上的存储空间并提高数据存取的并发性。

12.8每个数据文件及日志文件（.ldf）初始大小均不得小于512KB，主数据文件大小不得小于model数据库主数据文件，日志文件最好不小于1MB

12.9创建数据库：CREATDATABASEjessymin

ON

――表示数据库按下面参数创建

(NAME=jessymin,――逻辑文件名

FILENAME=‘…\MSSQL\Data\jessymin_Data.mdf’,――OS下的物理文件名

SIZE＝10，――文件初始大小，单位默认为MB，下同

MAXSIZE＝30，――文件最大大小

FILEGROWTH＝5，――文件增量，为0表示不自动增长，默认按当前10%增长）

LOGON

――表示该数据库日志文件按下面参数创建

（……..同上，只是物理文件名为jessymin.LDF）

12.10删除数据库：DROPDATABASEjessymin。删除六种数据库对象均用DROP

12.11Transact-SQL：非过程化高级语言，全司变量@@，局部变量@，局部变量可以是自定义类型但不能是text或image类型。

12.12Transact-SQL示例：计算1+2+3+…+100的和：

DECLARE@iint,@sumint

SET@i=1,@sum=0

--SET可换为SELECT

WHILE@i<=100

BEGIN

SET@sum=@sum+@i

SET@i=@i+1

END

PRINT@sum

12.13DTS(DataTransformationService)数据转换服务。注意区别DTC（分布式事务协调器）。导出数据时用户必须是要连接的数据库服务器的合法用户，且对要导出的表具有查询权限

第13章

数据库对象

13.1存储过程的：SQL语句和控制流语句的预编译集合，应用程序可通过调用方法来执行

优点：模块化程序设计；提高性能；减少网络流量；可作为安全机制使用

13.2带有多个参数并有默认值及输出参数的存储过程示例：

CREATEPROCEDUREp_Example

@areavarchar(20)=‘武汉大学’，@Pricemoney，@Sumintoutput

AS

SELECT/UPDATE/INSERT/DELETE……

SET@Sum=…..

应用程序中执行的SQL语句：

Declare@resint

EXECUTEp_Example‘武汉大学信息学部’，1000，@resoutput

或者EXECUTEp_Example@Price=1000,@resoutput

13.3用户自定义函数：标量函数（返回单值，非text、Image类型，任何允许出现表达式的地方）、内嵌表值函数（返回一个表，放在查询语句的From子句中）、多语句表值函数（返回一个可自定义的表，也放在查询语句的From子句中，视图和存储过程的结合）

13.4标量函数救示例：根据指定的商品类别查询该类的商品个数。

CREATFUNCTIONdbo.f_GoodsCount(@classvarchar(10))

RETURNSint

AS

BEGIN

DECLARE@xint

SELECT@x=count(*)FromT_GoodsClassaJIONT_Goodsb

ONa.GoodClassID=b.GoodClassID

WHEREGoodClassName=@class

RETURN@x

END

调用：SELECTdbo.f_GoodsCount(‘服装’)或者

SELECTGoodsNameAS商品名，dbo.f_GoodsCount(‘服装’)AS种类数From….

13.5内嵌表值函数的不同之处在于RETURNS后只能是table,RETURN后面只能是单个的SELECT语句，没有相关联的返回变量也没有函数体。调用时放在查询语句的FROM子句中。

13.6触发器是一种不需要由用户来调用的存储过程，当用户对表进行UPDATE、INSERT或DELETE操作时自动触发执行。作用：保证业务规则和数据完整性。优点：用编程方法来实现复杂的处理逻辑和业务规则，增强数据完整性约束。

13.7触发器适用场合：比CHECK语句更复杂的数据约束（可引用其他表中的列）；为保证数据库性能而维护的非规范化数据（如增加统计总值的列）；实现复杂的业务规则

13.8AFTER/FOR：后触发型触发器，可在同一操作上建立多个；INSTEADOF：前触发型，在同一操作上只能建立一个。所有的涉及对数据库对象操作的语句均不允许出现在触发器中。

13.9DELETED表：存储UPDATE和DELETED操作语句所影响行的更新前的旧数据；

INSERTED表：存储UPDATE和INSERT操作语句所影响行的更新后的新数据。

13.10维护数据操作完整性的后触发器示例：销售量大于库存量则撤销当前销售，小于时则在插入销售单据明细时同时修改库存量。

CreateTriggerOperatonCon

ONT_SaleDetailFORINSERT

AS

IFEXISTS(Select*FrominsertedaJionT_Goodsb

ONa.GoodsID=b.GoodsIDWHEREa.Quanity>b.TotalCharge)

BEGIN

ROLLBACK

PRINT‘此商品库存量小于此次销售数量’

END

ELSE

UPDATET_GoodsSETTotalCharge=TotalCharge–

(SELECTQuanityFrominserted)

13.11维护不同列之间取值完整性的后触发器示例：保证商品表中的单价与价格变动表中一致

CREATETRIGGERPriceConstraint

ONT_PriceHistoryFORINSERT,UPDATE

AS

DECLARE@newpricemoney

SELECT@newprice=SalePriceFrominserted

UPDATET_GoodsSETSaleUnitPrice=@newprice

WHEREGoodsIDIN(SELECTGoodIDFrominserted)

13.12前触发器指定执行触发器而不执行引发触发器的SQL语句，因此，如果数据操作满足完整性约束则在触发器中必须重新执行这些数据操作语句。

前触发器示例：保证销售单据中的会员卡是有效日期内的会员卡：

CREATETRIGGERCardValid

ONT_SaleDetailINSTEADOFINSERT,UPDATE

AS

IFNOTEXISTS(SElETCT*FrominsertedaJOINT_CardbONa.CardID=b.CardID

WHEREa.SalDateNOTBETWEENb.StartDateANDb.EndDate)

INSERTINTOT_SaleDetailSELECT*Frominserted(若满足条件此语句重新执行)

13.13用SQL语句修改存储过程、用户自定义函数、触发器的语法与创建基本一致，只是将CREATE改为了ALTER。（查询分析器中实现）

第14章

安全管理

14.1数据库的安全控制：在DBMS的不同层次提供对有意和无意损害行为的安全防范。

有意的非法活动：加密存、取数据；有意的非法操作：用户身份验证、限制操作权；

无意的损坏：提高系统的可靠性和数据备份

14.2数据库权限的种类：对DBMS进行维护的权限；对数据库对象和数据进行操作的权限

SQLServer权限种类（与数据库用户分类对应）：隐含权限（预定义的内置权限）；语句权限（DDL语句权限，创建删除数据库对象）；对象权限（DML语句权限，操作数据库对象）

14.3数据库用户的分类：数据库系统管理员（SA，全部权限）；数据库对象拥有者（创建数据库对象的用户，对所拥有的对象具有一切权限）；普通用户：只具有对数据的编辑查询功能

14.4三个认证过程：身份认证，只认证用户是否有连接到数据库服务器的“连接权”；合法用户，验证是否是数据库的合法用户；权限认证，验证用户是否具有要进行的操作的操作权限

14.5系统内置的登录账户：BUILTIN\Administrator;SA;域名\Administrator，均DBMS管理员

14.6创建SQLServer身份认证的登录账户：EXECUTEsp_addlogin‘user3’,’123’,’jessymin’

User3为登录账户，123为密码，jessymin为默认数据库；WINDOWS认证：sp_grantlogin

14.7删除登录账户存储过程：EXECdroplogin‘user3’（SQLServer身份验证）；EXECrevokelogin‘Server1\nt_user’（WINDOWS身份验证）

14.8登录账户可以连接到SQLServer服务器上但并不具有访问任何数据库的能力，必须再成为数据库的合法用户。一个登录账户可以映射为多个数据库用户，管理数据库用户的过程实际上就是建立登录账户与数据库用户之间的映射关系的过程。新建的数据默认只有一个用户：dbo,它是数据库的拥有者。

14.9创建数据库用户：EXECsp_adduser‘U2’,’U2’,’user_role’，用户名与登录账户一致，并让其成为“user_role”角色的成员。删除：EXECsp_dropuser‘U2’

14.10合法用户除了对所属数据库系统表具有一些查询权限外并不对数据库中的用户数据和对象具有任何权限，还得得到对数据库数据和对象的操作权限

14.11收回权限：不允许用户或角色具有某种操作权，或者收回曾经授予的权限，置空标记；

拒绝访问：拒绝某用户或角色具有某种操作权，即使由于继承获得的权限也无效，叉叉标记

14.12用Transact-SQL语句管理对象权限入管理语句权限：P245

14.13角色：数据库中具有相同权限的一组用户。系统预定义的固定角色；自定义用户角色

14.14固定的服务器角色：*amdin+dbcreator，权限最重要最高的是sysadmin，角色成员源均为系统的登录账户：EXECsp_addsrvrolemember‘Server1\nt_user’,‘sysadmin’

14.15固定的数据库角色：db_*+public，权限最高的是db_owner：EXECsp_addrolemember‘db_owner’,’user3’(注意用户和用色的顺序)

14.16public角色：每个用户均自动为其成员，不具任何权限但可赋予权限。如果想让所有数据库用户均具有某个特定权限则可将该权限授予public

14.17用户自定义的用户角色：EXECsp_addrole‘myrole’

14.18只要权限没有被拒绝过，角色中成员权限是角色的权限加上成员自己的权限。

第15章

备份和恢复数据库

15.1备份的两种方式：先创建备份设备（备份数据库的场所），再将数据库备份到备份设备上（永久备份设备）；直接将数据库备份到物理文件上（临时备份设备）

15.2创建磁盘备份设备：EXECsp_addumpdevice‘disk’,’mydiskdump’,’..\mydump.bak’

15.3常用备份策略：完全备份加差异备份加日志备份，备份和恢复速度都比较快，而且当系统出现故障时丢失的数据较少。

15.4Transact-SQL语句：BACKUPDATABASE\LOGjessyminTOmydiskdumpWITHINIT

WITHINIT表示覆盖掉原有内容，相当于“重写现有媒体”

15.5恢复的一般顺序：先恢复最近的完全数据库备份；再恢复完全备份之后的最近的差异备份；最后按日志备份的先后顺序恢复自最近的完全备份或差异备份之后的所有日志备份。

15.6Transact-SQL语句恢复过程示例：

1）首先恢复完全备份

RESTOREDATABASEjessyminFROMmydiskdumpWITHFILE=1,NORECOVERY

2）然后恢复差异备份（如果有的话）

RESTOREDATABASEjessyminFROMmydiskdumpWITHFILE=2,NORECOVERY

3）最后恢复日志文件

RESTORELOGjessyminFROMmydiskdump

Norecovery表示对数据库的恢复操作尚未完成，相当于“使数据不再运行，但能还原其他事务的日志文件”。默认为RECOVERY。

第16章VB开发环境与数据访问接口

（鄙人此前未曾用过VB，疏漏浅薄之处敬请见谅…）

16.1标准数据绑定控件：TextBox、CheckBox、ListBox、ComboxBox等

外部（ActiveX）数据绑定控件：DataCombo、DataList、DataGrid、MSHFGrid等

16.2DBMS支持的两种数据接口：专用接口（与特定的DBMS有关）；通用接口（屏蔽掉每个DBMS底层接口的差异，提供一种标准的访问方法）

16.3通用接口：ODBC、OLEDB、JDBC，让应用程序具有很好的适应性和可移植性；具备同时访问多种DBMS系统的能力。

16.4ODBC（只访关系型DB）：开放数据库互连OpenDataBaseConnectivity,ODBC应用系统大致工作流程从开始配置数据源到回收各种句柄为此。句柄是32位整数值，代表一个指针。

16.5OLEDB：对象链接与嵌入的数据库ObjectLinkedandEmbedDataBase,是MicrosoftOLE对象标准的一个实现，是COM对象，是为数据访问而设计的一系列COM接口。

16.6ADO：动态数据对象ActiveXDataObject,建在OLEDB之上的高层接口集，是介于OLEDB底层接口和应用程序之间的接口，它避免了开发人员直接使用OLEDB底层接口的麻烦。

16.7ODBC与OLEDB的主要区别：

1）ODBC只能访问关系型数据库，而OLEDB可以访问关系和非关系型甚至是无结构的数据。

2）OLEDB克服了ODBC的一个主要缺点：一个ODBC驱动程序需要支持几乎所有的DBMS特征和功能，这需要大量的工作和初始投资，而OLEDB允许DBMS提供商只实现他们产品的一部分功能。

第17章VB数据库应用编程

17.1CommandType属性：

adCmdUnknown,表示RecordSource中的命令类型未知；

adCmdTable表示RecordSource属性的内容来自一张表；

adCmdText表示RecordSource属性的内容来自一个查询语句；

adCmdStoredType表示RecordSource属性的内容来自一个存储过程

17.2RecordSet的Move方法组中，MovePrevios和MoveNext没有自动检测记录的当前行指针是否移出了结果集边界的功能，需编码实现：

myadodc.RecordSet.MoveNext/MovePrevious

Ifmyadodc.RecordSet.EOF/BOF=TrueThen

myadodc.RecordSet.MoveLast/MoveFirst

EndIf

17.3保存缓冲区中的记录：Update方法；对当前记录指针作一个移动操作

17.4CancelUpdate方法：应在Update方法之前调用，调用了Update方法之后的修改是不能撤销的；如果没有添加新记录也没有对当前记录做任何修改，调用CancelUpdate会出错。

17.5Find方法：用于在当前结果集中查找满足条件的记录

myadodc.RecordSet.Find(“查找条件表达式”)

与

RecordSet对象名.Filter“选择表达式”

相似（.Filter=adFilterNone还原）

17.6更新记录：

mydocdc.RecordSet.Fields(“CustomID”)=Trim(txtCID.Text)

mydocdc.RecordSet.Fields(“Age”)=CInt(txtAge.Text)

mydocdc.RecordSet.Update

17.7删除记录提示窗口：

DimresAsInteger

res=MsgBox(“确实要删除此行记录吗？”，vbExclamation+vbYesNo+vbDefaultButton2)

Ifres=vbYesThen

Myadodc.RecordSet.Delete

EndIf

17.8在DataGrid中显示全部列：

DimintColAsInteger

Withmydg

ForintCol=0To.Columns.Count–1

.Columns(intCol).Visible=True

Next

EndWith

另一种For循环：ForEachparINadocm.Parameters……….

Nextpar

17.9排序功能实现：

(mydg.DataSource=adodc)

Withadodc.RecordSet

IfoptAsc.Value=TrueThen

.Sort=.Field(intCol).Name&“ASC”

Else

.Sort=.Field(intCol).Name&“DESC”

EndIf

EndWith

mydg.Refresh

17.10ADO对象模型

Connection对象ErrorS集合Error对象

Command对象Parameters集合Parameter对象

RecordSet对象Fields集合Field对象

17.11Connection对象可以完成的操作：连接数据源打开数据库；执行一个数据库操作命令；利用Error对象检查数据源返回的出错信息。

17.12销毁内存中的对象：Set对象名=Nothing

17.13Command对象的CommandText属性相当于ADO数据控件的RecordSource属性；

Command对象的CommandType属性相当于ADO数据控件的CommandType属性

17.14RecordSet对象的主要功能是建立记录集，并支持对记录集中各数据的各种操作。允许用户直接获取数据，因此RecordSet对象与ADO的访问过程无关。

17.15CursorType属性：

adOpenDynamic动态游标：反映所有用户对数据的修改，支持向前及向后移动；

adOpenStatic静态游标：不能反映其他用户的修改，支持向前及向后，当打开客户端RecordSet对象时，adOpenStatic为唯一允许的游标类型。当打印报表和其他不需要即时完成更新数据的应用程序来说很有用。

adOpenFowardOnly仅向前游标：默认值。仅支持向前移动，其他与adOpenStatic一致。

adOpenKeyset键集游标：介于动态和静态游标。只看到其他用户更改的看不到添加删除的

17.16CursorLocation属性：

adUseClient：本地客户端游标。将整个结果集传给客户端，网络流量大但下载后浏览速度快

adUseServer：默认值，仅传送客户端需要的数据，网络流量小但服务器资源消耗大。不支持BookMark属性(书签，快速再定位)和AbsolutePosition属性

adUseNone：没有使用游标服务。

17.17RecordSet.Open方法：

myRs.OpenSource,ActiveConnection,CursorType,LockType,Options

1)Source支持的类别：

5）Options的可选项

一个返回记录的Command对象

///adCmdFile

SQL语句

――adCmdText

表名

――adCmdTable

存储过程名

――adComdStoredProc

2)ActiveConnection：已打开的Connection对象；一个连接字符串

示例：

DimmyCnnAsNewADODB.Connection

DimmyRsAsNewADODB.RecordSet

myCnn.Connection=“Provider=SQLOLEDB.1;UserID=sa;”_

&“InitialCatalog=商品经营管理数据库;DataSource=(local)”

myCnn.Open

‘别忘了“打开”

myRs.Source=“select*fromT_Customer”

myRs.ActiveConnection=myCnn

myRs.CursorType=adOpenDynamic

myRs.CursorLocation=adUseClient

myRs.Open,,,,adCmdTalbe

17.18创建RecordSet对象的三种方法：

1）使用Connection对象：SetmyRs=myConn.Execute(“select*fromT_customer”)；

2)使用Command对象：SetmyRs=myComm.Execute；

3)直接使用Open方法：myRs.Open,,,,

17.19Error对象：如果最后一次的操作成功则这个集合为空。只有在OLEDB层产生错误才会将每个错误被翻译成Error对象，如果指向一个不存在的提供者则Errors集合不会得到任何信息，因为ADO不能发现这个指定的驱动程序，因此会将错误传递到VisualBasicErrors中。

17.20三对象结合使用的典型示例：

DimmyCnnAsNewADODB.Connection

DimmyCmmAsNewADODB.Command

DimmyRsAsNewADODB.RecordSet

myCnn.ConnectionString=“Provider=SQLOLEDB.1;UserID=loginID;”_

&“InitialCatalog=jessymin;DataSource=(local)”

myCnn.Open

‘别忘了“打开”

SetmyCm