四级数据库工程师重难点复习

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 2 DFD 图 数据流 标有名字的箭头 处理 数据存储 横圆柱 数据源 终点 棱形 3 3 IDEF0 图 更好地理解需求 ICOM 输入 控制 输出 机制 码 至少一个控制和输出箭 头 A 0 A0 顶层图 A1 A11 3 4 DFD 与 IDEF0 的比较 结构化分析思想 自顶而下逐级细化 1 DFD 图用箭头也叫数据流来描述数据移动的方向 数据处理之间的数据依赖关系 IDEF0 图也用箭头表示数据流 但不强调流或顺序 强调数据约束 箭头语义更丰富 2 DFD 模型有四种元素 IDEF0 图只有两种 箭头和活动 3 IDEF0 图更加规范 其概念 建模方法 画图规则等均有说明和规定 4 IDEF0 模型结构更清楚 便于理解和沟通 3 5 结构化分析及建模方法的优点 1 不过早陷入具体的细节 从整体或宏观入手分析问题 如业务系统的总体结构 系统及 子系统的关系 2 通过图形化的模型对象直观地表示系统完成什么功能 方便系统分析员理解和描述系统 3 模型对象不涉及太多技术术语 便于用户理解模型 第 4 章 数据库概念设计与数据建模 4 1 概念设计的目的和任务 面向现实世界 理解和描述应用领域中的数据需求 分析确 定系统需要存储和处理什么数据 过程 确定实体集 确定联系和联系类型 建立由信息模 型表示的业务模型 确定实体集属性 优化信息模型 4 2 实体集描述具有相同属性特征和性质的事物集合 属性描述实体集具有的某一特征或性 质 4 3 IDEF1X 标定型 子依父实例才标定 确定型 非标定型 确定型 分类 不确定 第 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 8 1NF R 的每一属性均不可再分 2NF 是 1NF 且每个非主属性完全依赖于主码 在 1 上消除非主属性对主码的部分函数依 赖 3NF 是 1NF 且每个非主属性只依赖于主码 在 2 上消除非主属对主码的传递函数依赖 5 9 ER 模型转换关系模型 实体集转为同名关系 联系转为一个关系 各实体集的码及联系 的属性转为关系的属性 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 5 DBMS 的三种完整性控制机制 CHECK 子句 断言 触发器 断言语句 Create assertion 断言约束名 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 6 C S 结构特点 数据管理和数据处理被分在客户端和服务器上 服务器可支持多个客户端 客 户端也可访问多个服务器 客户端 人机交互 数据处理 B S 结构特点 表示层 WEB 浏览器 功能层 WEB 应用服务器 数据层 DBMS 服务 优点 实现人面交互 应用业务逻辑处理 数据管理三层分离 提高了系统的可维护性 用 WEB 浏览器可访问多个异构应用平台 解决了跨平台数据管理问题 第 8 章 SQL 8 1 SQL Structured Query Language 结构化查询语言 8 2 二进制字符串是用十六进制表示的 0 x 前缀 8 3 创建表 CREATE TABLE Table Card CardID varchar 8 NOT NULL StartDate datetime NOT NULL EndDate datetime NOT NULL Score int NOT NULL CHECK Score 0 DEFAULT 0 State char 1 NOT NULL CHECK State in 0 1 2 Sex char 1 CHECK Sex in 男 女 Age int CHECK Age between 16 85 CustomerID varchar 8 CHECK EndDate StartDate PRIMARY KEY CardID FOREIGN KEY CustomerID REFERENCES Table Customer CustomerID 删除表 DROP TABLE Table Card 修改表 ALTER TABLE Table Card ADD CardType char 4 NULL ALTER TABLE Table Card ALTER COLUMN CardType char 8 ALTER TABLE Table Card DROP COLUMN CardType 8 4 SELECT Cname FROM Tablb Customer WHERE Address IN 海淀区 朝阳区 AND age BETWEEN 40 AND 50 AND Cname LIKE 王张李 王 ORDER BY StartDate ASC EndDate DESC 8 5 除 COUNT 不忽略空值外 其他函数 COUNT DISTINCT 列名 SUM AVG MAX MIN 均忽略空值 均返回单值 SELECT SUM Score MAX Age MIN Age AVG Age COUNT DISTINCT FROM Table 8 6 分组 GROUP BY 依据列不能是 text ntext image 和 bit 类型的列 先分组后计算 统计每种类别商品的商品数 只列出商品数大于 4 个的商品类别编号和商品数 SELECT GoodClassID as 商品类别编号 COUNT as 商品数 FROM Table Goods GROUP BY GoodClassID HAVING COUNT 4 8 7 自连接 查询与王晓在同一个区的顾客的姓名及地址 SELECT T2 Name T2 Address FROM Table Customer as T1 JION Table Customer as T2 ON T1 Address T2 Address WHERE T1 Name 王晓 AND T2 Name 王晓 子查询 一个 SELECT 语句嵌套在一个 SELECT UPDATE INSERT 或 DELETE 语句中 SELECT Name Address FROM Table Customer WHERE Address IN SELECT Address FROM Table Customer WHERE Name 王晓 AND Name 王晓 8 8 外连接 LEFT OUTER JOIN 左外连接 左表全输出 RIGHT OUTER JOIN 右外连接 8 9 子查询 NOT IN 集合 不相关子查询 先内后外 WHERE 列 比较 不相关子查询 先内后外 NOT EXIST SELECT 存在 相关子查询 先外后内 8 10 修改数据 INSERT INTO 表名 列名表 VALUES 值列表 UPDATE 表名 SET 列名 表达式 WHERE 子句 DELETE FROM 表名 WHERE 子句 分情况修改 UPDATE Table Goods SET SalePrice CASE GoodsClassName WHEN 家用电器 THEN SalePrice SalePrice 0 05 WHEN 服装 THEN SalePrice SalePrice 0 06 ELSE SalePrice END FROM Table Goods a JION Table GoodsClass b ON a ClassID b ClassID 8 11 创建索引 CREATE UNIQUE CLUSTERED NONCLUSTERED INDEX 索引名 ON 表名 列名 顺序 ASC DESC 8 12 视图 由从数据库基本表中选取出来的数据组成的逻辑窗口 是一个虚表 数据库只 存放视图的定义而不存放视图包括的数据 对视图的操作最终都会转换为对基本表的操作 CREATE VIEW 视图名 视图列名表 AS SELECT 语句 WITH CHECK OPTION 作用 简化数据查询语句 使用户从多角度观察同一数据 提高了数据安全性 屏蔽掉敏感数 据 提供一定程度的逻辑独立性 8 13 定点数 numeric p q 或 decimal p q p 为精度 数字位个数 不大于 38 q 为小数位个 数 bit 类型 只存储 1 和 0 不多于 8 个 bit 列则只用 1 个字节存储 8 14 Char n 代表的是普通字符编码按定长存储的字符串 n 的含义是字符的个数 固定占 用 n 个字节的空间 varchar n 代表的是普通字符编码按不定长存储的字符串 n 的含义 也是字符的个数 按字符的实际长度占用空间 第 9 章 事务高度与并发控制 9 1 调度 定义在多个事务上的调度是这些事务的所有操作的一个执行序列 代表了这些 操作的执行顺序 冲突操作 事务 Ti 的操作 Ii 与事务 Tj 的操作 Ij 是冲突的 当且仅当 Ii 和 Ij 访问数据库中同一个数据项 Q 并且 Ii 和 Ij 中至少有一个是写操作 write Q 冲突可 串行 一个并发调度冲突等价于某个串行调度 判断一个并行调度是否正确 死锁是指数据库系统中部分或全部事务由于无法获得对需要访问的数据项的控制权而处于 等待状态 并且将一直等待下去的一种系统状态 9 2 ACID Atomicity 原子性 Consistency 一致性 Isolation 隔离性 Durability 持久性 9 3 1 级加锁协议要求事务 T 在修改数据项 Q 之前必须先对 Q 加 X 锁 直到事务结束才释 放该锁 事务结束包括正常结束 commit 和非正常结束 rollback 但事务如果是只读 Q 而 不对其进行修改 是不需要对 Q 加锁的 2 级加锁协议是在 1 级加锁协议基础上 要求事务 T 在读取数据项 Q 之前必须先对其加 S 锁 读完 Q 后可以立即释放 S 锁 3 级加锁协议则是在 1 级加锁协议基础上 要求事务 T 在读取数据项 Q 之前必须先对其加 S 锁 但是需要等到事务结束时才释放该 S 锁 9 4 2 阶段锁协议将每个事务的执行过程分为加锁阶段和解锁阶段 在加锁阶段 事务可以 申请获得数据项上的任何类型的锁 但不允许释放任何锁 在解锁阶段 事务可以释放任 何数据项上的任何类型的锁 但不能再申请任何锁 每个事务开始执行后就进入了加锁阶 段 当第一次释放锁后 即转入解锁阶段 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 数据库查询优化 合理使用索引 避免或简化排序 Order by Group by 磁盘排序比内 存排序开销大速度慢 避免相关子查询 外连接 左右连接比内连接消耗大 存储过程 10 9 属于 Oracle 但不属于 SQL Server 的逻辑和物理空间结构 表空间 段 区 第 11 章 故障管理 11 1 故障种类 事务内部故障 事务回滚撤消修改 系统故障 影响事务不坏数据 介质 故障 软件容错 硬件容错 病毒 11 2 系统故障对策 重启 撤消 UNDO 未提交的事务 重做 REDO 已提交的事务 11 3 软件容错 备份 日志文件 利用恢复技术 硬件容错 双物理存储设备 11 4 恢复基本原理 冗余 即所有数据均可通过存储在别处的冗余数据来重建 11 5 对于经常进行数据操作的数据库 完全转储 差异转储 11 6 以记录为单位的日志文件 开始标记 Ti BEGIN TRANSACTION 结束标记 Ti COMMIT 或者 Ti ROLLBACK 每个事务的所有操作 Ti A 50 80 11 7 以数据块为单位的日志文件 存放更新前和更新后的整个数据块 只有事务标识和被 更新的数据块 没有操作类型和操作对象 11 8 日志的作用 用来进行业务故障和系统故障恢复 协助后备副本进行介质故障恢复 动 态转储必用 记录操作监视行为分析问题 登记原则 登记次序严格按并行事务执行次序 必须先写日志文件再写数据库 11 9 检查点 最大限度减少数据库完全恢复时所必须执行的日志部分 针对系统故障 11 10 数据库镜像 提高数据库可用性的解决方案 比如介质故障 两台服务器相互备份 优点 提供完整或几近完整的数据冗余 增强数据保护 发生介质故障时 数据不会丢失且 服务不会中断 提高数据库可用性 提高镜像数据库在升级期间的可用性 双机互备援模式 均为主 双机热备份模式 一主一备份机 数据库镜像可用于并发操作 11 11 RAID 廉价冗余磁盘阵列 镜像冗余 校验冗余 对成员磁盘上的数据执行异或 XOR 操作得到其校验值并存放在另外的校验磁盘上 当某个磁盘发生故障时 只须计算 其他磁盘上的校验数据和数据的异或便可重新得到该磁盘的值 第 12 章 SQL Server2000 数据库管理系统 12 1 四个服务 SQL Server 核心服务 SQL Server Agent 代理服务 代理定期进行的管理工作 DTC Distributed Transaction Coordinator 分布式事务协调器 同一事务访问多个服务器 Microsoft Search 全文检索服务 12 2 四个版本 企业版 全部功能 大型数据库 标准版 小部门 开发版 同企业版 作 开发测试系统用 不作生产服务用 个人版 移动环境 本地数据 12 3 服务帐户 使用本地系统帐户 自动取当前登录到 Windows 的用户 没有 Windows 的网络访问权限 适用于非网络服务器操作系统 如 XP 使用域用户帐户 使用 Windows 身份验证设置连接到 SQL Server 用户必是 Windows 系统管理员 适用于网络服务器 OS 12 4 网络库 在 SQL S 客户端和服务器间传递网络数据包 服务器可一次监听多个网络库 12 5 SQLServer 的两大类数据库 系统数据库 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 创建数据库 CREAT DATABASE jessymin ON 表示数据库按下面参数创建 NAME jessymin 逻辑文件名 FILENAME MSSQL Data jessymin Data mdf OS 下的物理文件名 SIZE 10 文件初始大小 单位默认为 MB 下同 MAXSIZE 30 文件最大大小 FILEGROWTH 5 文件增量 为 0 表示不自动增长 默认按当前 10 增长 LOG ON 表示该数据库日志文件按下面参数创建 同上 只是物理文件名为 jessymin LDF 12 10 删除数据库 DROP DATABASE jessymin 删除六种数据库对象均用 DROP 12 11 Transact SQL 非过程化高级语言 全司变量 局部变量 局部变量可以是自 定义类型但不能是 text 或 image 类型 12 12 Transact SQL 示例 计算 1 2 3 100 的和 DECLARE i int sum int SET i 1 sum 0 SET 可换为 SELECT WHILE i b TotalCharge BEGIN ROLLBACK PRINT 此商品库存量小于此次销售数量 END ELSE UPDATE T Goods SET TotalCharge TotalCharge SELECT Quanity From inserted 13 11 维护不同列之间取值完整性的后触发器示例 保证商品表中的单价与价格变动表中 一致 CREATE TRIGGER PriceConstraint ON T PriceHistory FOR INSERT UPDATE AS DECLARE newprice money SELECT newprice SalePrice From inserted UPDATE T Goods SET SaleUnitPrice newprice WHERE GoodsID IN SELECT GoodID From inserted 13 12 前触发器指定执行触发器而不执行引发触发器的 SQL 语句 因此 如果数据操作满 足完整性约束则在触发器中必须重新执行这些数据操作语句 前触发器示例 保证销售单据中的会员卡是有效日期内的会员卡 CREATE TRIGGER CardValid ON T SaleDetail INSTEAD OF INSERT UPDATE AS IF NOT EXISTS SElETCT From inserted a JOIN T Card b ON a CardID b CardID WHERE a SalDate NOT BETWEEN b StartDate AND b EndDate INSERT INTO T SaleDetail SELECT From inserted 若满足条件此语句重新执行 13 13 用 SQL 语句修改存储过程 用户自定义函数 触发器的语法与创建基本一致 只是 将 CREATE 改为了 ALTER 查询分析器中实现 第 14 章 安全管理 14 1 数据库的安全控制 在 DBMS 的不同层次提供对有意和无意损害行为的安全防范 有意的非法活动 加密存 取数据 有意的非法操作 用户身份验证 限制操作权 无意的损坏 提高系统的可靠性和数据备份 14 2 数据库权限的种类 对 DBMS 进行维护的权限 对数据库对象和数据进行操作的权限 SQL Server 权限种类 与数据库用户分类对应 隐含权限 预定义的内置权限 语句权限 DDL 语句权限 创建删除数据库对象 对象权限 DML 语句权限 操作数据库对象 14 3 数据库用户的分类 数据库系统管理员 SA 全部权限 数据库对象拥有者 创建数据 库对象的用户 对所拥有的对象具有一切权限 普通用户 只具有对数据的编辑查询功能 14 4 三个认证过程 身份认证 只认证用户是否有连接到数据库服务器的 连接权 合法用 户 验证是否是数据库的合法用户 权限认证 验证用户是否具有要进行的操作的操作权限 14 5 系统内置的登录账户 BUILTIN Administrator SA 域名 Administrator 均 DBMS 管理 员 14 6 创建 SQL Server 身份认证的登录账户 EXECUTE sp addlogin user3 123 jessymin User3 为登录账户 123 为密码 jessymin 为默认数据库 WINDOWS 认证 sp grantlogin 14 7 删除登录账户存储过程 EXEC droplogin user3 SQL Server 身份验证 EXEC revokelogin Server1 nt user WINDOWS 身份验证 14 8 登录账户可以连接到 SQL Server 服务器上但并不具有访问任何数据库的能力 必须再 成为数据库的合法用户 一个登录账户可以映射为多个数据库用户 管理数据库用户的过 程实际上就是建立登录账户与数据库用户之间的映射关系的过程 新建的数据默认只有一 个用户 dbo 它是数据库的拥有者 14 9 创建数据库用户 EXEC sp adduser U2 U2 user role 用户名与登录账户一致 并 让其成为 user role 角色的成员 删除 EXEC sp dropuser U2 14 10 合法用户除了对所属数据库系统表具有一些查询权限外并不对数据库中的用户数据 和对象具有任何权限 还得得到对数据库数据和对象的操作权限 14 11 收回权限 不允许用户或角色具有某种操作权 或者收回曾经授予的权限 置空标 记 拒绝访问 拒绝某用户或角色具有某种操作权 即使由于继承获得的权限也无效 叉叉标 记 14 12 用 Transact SQL 语句管理对象权限入管理语句权限 P245 14 13 角色 数据库中具有相同权限的一组用户 系统预定义的固定角色 自定义用户角色 14 14 固定的服务器角色 amdin dbcreator 权限最重要最高的是 sysadmin 角色成员 源均为系统的登录账户 EXEC sp addsrvrolemember Server1 nt user sysadmin 14 15 固定的数据库角色 db public 权限最高的是 db owner EXEC sp addrolemember db owner user3 注意用户和用色的顺序 14 16 public 角色 每个用户均自动为其成员 不具任何权限但可赋予权限 如果想让所有 数据库用户均具有某个特定权限则可将该权限授予 public 14 17 用户自定义的用户角色 EXEC sp addrole myrole 14 18 只要权限没有被拒绝过 角色中成员权限是角色的权限加上成员自己的权限 第 15 章 备份和恢复数据库 15 1 备份的两种方式 先创建备份设备 备份数据库的场所 再将数据库备份到备份设备 上 永久备份设备 直接将数据库备份到物理文件上 临时备份设备 15 2 创建磁盘备份设备 EXEC sp addumpdevice disk mydiskdump mydump bak 15 3 常用备份策略 完全备份加差异备份加日志备份 备份和恢复速度都比较快 而且当 系统出现故障时丢失的数据较少 15 4 Transact SQL 语句 BACKUP DATABASE LOG jessymin TO mydiskdump WITH INIT WITH INIT 表示覆盖掉原有内容 相当于 重写现有媒体 15 5 恢复的一般顺序 先恢复最近的完全数据库备份 再恢复完全备份之后的最近的差异备 份 最后按日志备份的先后顺序恢复自最近的完全备份或差异备份之后的所有日志备份 15 6 Transact SQL 语句恢复过程示例 1 首先恢复完全备份 RESTORE DATABASE jessymin FROM mydiskdump WITH FILE 1 NORECOVERY 2 然后恢复差异备份 如果有的话 RESTORE DATABASE jessymin FROM mydiskdump WITH FILE 2 NORECOVERY 3 最后恢复日志文件 RESTORE LOG jessymin FROM mydiskdump Norecovery 表示对数据库的恢复操作尚未完成 相当于 使数据不再运行 但能还原其他事 务的日志文件 默认为 RECOVERY 第 16 章 VB 开发环境与数据访问接口 鄙人此前未曾用过 VB 疏漏浅薄之处敬请见谅 16 1 标准数据绑定控件 TextBox CheckBox ListBox ComboxBox 等 外部 ActiveX 数据绑定控件 DataCombo DataList DataGrid MSHFGrid 等 16 2 DBMS 支持的两种数据接口 专用接口 与特定的 DBMS 有关 通用接口 屏蔽掉每个 DBMS 底层接口的差异 提供一种标准的访问方法 16 3 通用接口 ODBC OLE DB JDBC 让应用程序具有很好的适应性和可移植性 具备 同时访问多种 DBMS 系统的能力 16 4 ODBC 只访关系型 DB 开放数据库互连 Open DataBase Connectivity ODBC 应用系统 大致工作流程从开始配置数据源到回收各种句柄为此 句柄是 32 位整数值 代表一个指针 16 5 OLE DB 对象链接与嵌入的数据库 Object Linked and Embed DataBase 是 Microsof t OLE 对象标准的一个实现 是 COM 对象 是为数据访问而设计的一系列 COM 接口 16 6 ADO 动态数据对象 ActiveX Data Object 建在 OLE DB 之上的高层接口集 是介于 OLE DB 底层接口和应用程序之间的接口 它避免了开发人员直接使用 OLE DB 底层接口 的麻烦 16 7 ODBC 与 OLE DB 的主要区别 1 ODBC 只能访问关系型数据库 而 OLE DB 可以访问关系和非关系型甚至是无结构的数 据 2 OLE DB 克服了 ODBC 的一个主要缺点 一个 ODBC 驱动程序需要支持几乎所有的 DBMS 特征和功能 这需要大量的工作和初始投资 而 OLE DB 允许 DBMS 提供商只实现 他们产品的一部分功能 第 17 章 VB 数据库应用编程 17 1 CommandType 属性 adCmdUnknown 表示 RecordSource 中的命令类型未知 adCmdTable 表示 RecordSource 属性的内容来自一张表 adCmdText 表示 RecordSource 属性的内容来自一个查询语句 adCmdStoredType 表示 RecordSource 属性的内容来自一个存储过程 17 2 RecordSet 的 Move 方法组中 MovePrevios 和 MoveNext 没有自动检测记录的当前行 指针是否移出了结果集边界的功能 需编码实现 myadodc RecordSet MoveNext MovePrevious If myadodc RecordSet EOF BOF True Then myadodc RecordSet MoveLast MoveFirst End If 17 3 保存缓冲区中的记录 Update 方法 对当前记录指针作一个移动操作 17 4 CancelUpdate 方法 应在 Update 方法之前调用 调用了 Update 方法之后的修改是不 能撤销的 如果没有添加新记录也没有对当前记录做任何修改 调用 CancelUpdate 会出错 17 5 Find 方法 用于在当前结果集中查找满足条件的记录 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 删除记录提示窗口 Dim res As Integer res MsgBox 确实要删除此行记录吗 vbExclamation vbYesNo vbDefaultButton2 If res vbYes Then Myadodc RecordSet Delete End If 17 8 在 DataGrid 中显示全部列 Dim intCol As Integer With mydg For intCol 0 To Columns Count 1 Columns intCol Visible True Next End With 另一种 For 循环 For Each par IN adocm Parameters Next par 17 9 排序功能实现 mydg DataSource adodc With adodc RecordSet If optAsc Value True Then Sort Field intCol Name 执行一个数据库操作命令 利用 Error 对象检查数据源返回的出错信息 17 12 销毁内存中的对象 Set 对象名 Nothing 17 13 Command 对象的 CommandText 属性相当于 ADO 数据控件的 RecordSource 属性 Command 对象的 CommandType 属性相当于 ADO 数据控件的 CommandType 属性 17 14 RecordSet 对象的主要功能是建立记录集 并支持对记录集中各数据的各种操作 允 许用户直接获取数据 因此 RecordSet 对象与 ADO 的访问过程无关 17 15 CursorType 属性 adOpenDynamic 动态游标 反映所有用户对数据的修改 支持向前及向后移动 adOpenStatic 静态游标 不能反映其他用户的修改 支持向前及向后 当打开客户端 RecordSet 对象时 adOpenStatic 为唯一允许的游标类型 当打印报表和其他不需要即时完 成更新数据的应用程序来说很有用 adOpenFowardOnly 仅向前游标 默认值 仅支持向前移动 其他与 adOpenStatic 一致 adOpenKeyset 键集游标 介于动态和静态游标 只看到其他用户更改的看不到添加删除的 17 16 CursorLocation 属性 adUseClient 本地客户端游标 将整个结果集传给客户端 网络流量大但下载后浏览速度 快 adUseServer 默认值 仅传送客户端需要的数据 网络流量小但服务器资源消耗大 不支 持 BookMark 属性 书签 快速再定位 和 AbsolutePosition 属性 adUseNone 没有使用游标服务 17 17 RecordSet Open 方法 myRs Open Source ActiveConnection CursorType LockType Options 1 Source 支持的类别 5 Options 的可选项 一个返回记录的 Command 对象 adCmdFile SQL 语句 adCmdText 表名 adCmdTable 存储过程名 adComdStoredProc 2 ActiveConnection 已打开的 Connection 对象 一个连接字符串 示例 Dim myCnn As New ADODB Connection Dim myRs As New ADODB RecordSet myCnn Connection Provider SQLOLEDB 1 User ID sa Data Source local myCnn Open 别忘了 打开 myRs Source select from T Customer myRs ActiveConnection myCnn myRs CursorType adOpenDynamic myRs CursorLocation adUseClient myRs Open adCmdTalbe 17 18 创建 RecordSet 对象的三种方法 1 使用 Connection 对象 Set myRs myConn Execute select from T customer 2 使用 Command 对象 Set myRs myComm Execute 3 直接使用 Open 方法 myRs Open 17 19 Error 对象 如果最后一次的操作成功则这个集合为空 只有在 OLE DB 层产生错误 才会将每个错误被翻译成 Error 对象 如果指向一个不存在的提供者则 Errors 集合不会得 到任何信息 因为 ADO 不能发现这个指定的驱动程序 因此会将错误传递到 Visual Basic Errors 中 17 20 三对象结合使用的典型示例 Dim myCnn As New ADODB Connection Dim myCmm As New ADODB Command Dim myRs As New ADODB RecordSet myCnn ConnectionString Provider SQLOLEDB 1 User ID loginID Data Source local myCnn Open 别忘了 打开 Set myCmm ActiveConnection myCnn myCmm CommandText select from T Customer Set myRs myCmm Execute 17 21 VB 自定义函数示例 将字段空值转化为空字符串 Private Function convertNull val As Variant As Variant If IsNull val True Then convertNull Else convertNul