第7章 关系数据库管理系统SQL Server 2000.ppt

第7章关系数据库管理系统SQLServer2000 2 7 1SQLServer2000基础7 2SQLServer2000的主要组件7 3SQLServer2000数据库操作7 4SQLServer2000数据库备份和还原7 5数据转换服务DTS7 6SQLServer2000安全管理 3 7 1SQLServer2000基础 SQLServer是一个关系数据库管理系统 它最初是由Microsoft Sybase和Ashton Tate三家公司联合开发的 于1988年推出了第一个OS 2版本 后来 Ashton Tate公司退出了SQLServer的开发 而在WindowsNT推出后 Microsoft与Sybase在SQLServer的开发上就分道扬镳了 Microsoft将SQLServer移植到WindowsNT系统上 专注于开发推广SQLServer的WindowsNT版本 Sybase则较专注于SQLServer在UNIX操作系统上的应用 我们介绍的是MicrosoftSQLServer 4 SQLServer2000是Microsoft公司推出的SQLServer数据库管理系统的最新版本 该版本继承了SQLServer7 0版本的优点 同时又比它增加了许多更先进的功能 具有使用方便 可伸缩性好与相关软件集成程度高等优点 可跨越从运行MicrosoftWindows98的膝上型电脑到运行MicrosoftWindows2000的大型多处理器的服务器等多种平台使用 SQLServer2000包括4个常见版本 5 1 企业版 EnterpriseEdition 支持所有的SQLServer2000特性 可作为大型Web站点 企业OLTP 联机事务处理 以及数据仓库系统等的产品数据库服务器 2 标准版 StandardEdition 用于小型的工作组或部门 3 个人版 PersonalEdition 用于单机系统或客户机 4 开发者版 DeveloperEdition 用于程序员开发应用程序 这些程序需要SQLServer2000作为数据存储设备 此外 SQLServer2000还有DesktopEngine 桌面引擎 和WindowsCE版 用户可以根据实际情况选择所要安装的SQLServer2000版本 6 7 1SQLServer2000的新特性 7 1 1数据库增强1 支持扩展标示语言XML ExtensibleMarkupLanguage SQLServer2000对XML的支持表现在以下几个方面 可以通过URL UniformResourceLocator 访问SQLServer支持XML Data模式可检索编写XML数据SQLServer2000OLEDB增加了对XML文档的支持2 新的数据类型SQLServer2000中增加了3种新的数据类型 BIGINT SQL VARIANT和TABLE 3 数据行中的Text类型数据SQLServer2000中可以将TEXT和IMAGE类型的数据直接存放到表的数据行中 而不是存放到不同的数据页中 这就减少了用于存储TEXT和IMAGE类型的空间并相应减少了磁盘处理这类数据的I O数量 7 4 用户自定义函数SQLServer2000扩展了Transact SQL语言的可编程性 用户可以创建自己的Transact SQL函数 用户自定义函数可以返回一个数量值或表 5 索引增强可以在计算列上创建索引 这是一个很大的改进 6 全文检索增强全文检索中增加了改变跟踪和图形过滤的功能 其中 图形过滤功能允许对存储在IMAGE类型列中的文档数据进行查询和创建索引 7 索引化视图索引化视图允许在视图上创建索引 这就大大提高了需要频繁进行连接查询的程序的性能 8 8 分布式查询增强SQLServer2000引入了OPENROWSET 的函数 它可以指定在分布式查询中的一个特定的连接信息 分布式查询优化器的功能有了进一步的提高 授予了OLEDB数据源更多的SQL操作权 9 触发器类型创建触发器时可以通过FOR子句来指定触发器类型为INSTEADOF型或AFTER型 不同类型的触发器执行的时机不同 9 10 级联参考完整性约束级联参考完整性约束可以控制在删除或更新有外键约束的数据时所采取的操作 这种控制是通过在CREATETABLE或ALTERTABLE命令中的REFERENCES子句中加入ONDELETE或ONUPDATE子句来实现的 11 Collation增进SQLServer2000用Collation来替代Codepages和SortOrders 它比以前的版本提供了更多对Collation的支持 并引入了一个基于WindowsCollations的新的Collation集合 可以指定数据库级或列级的Collation 10 7 1 2联合数据库服务器SQLServer2000支持分布式的分区视图 可以跨越多个服务器水平地分割表 1 多个SQLServer实例SQLServer2000支持在同一计算机上同时运行多个关系数据库实例 每个实例有其独立的系统和用户数据库集合 应用程序采用与连接不同计算机上的SQLServer实例大致相同的方式连接同一计算机上的各个实例 11 7 1 2联合数据库服务器2 Failover群集增强对Failover群集的管理有了较大的改善 可以方便地安装 配置和管理一个SQLServer2000的Failover群集 3 网络库 Net Library 增强简化了客户机配置并支持同一计算机上的多实例连接 4 Kerberos和安全授权SQLServer2000使用Kerberos来支持客户机和服务器之间相互的身份验证 使用Kerberos和授权来支持复合认证以及SQLServer注册 12 5 备份 Backup 和还原 Restore 增强SQLServer2000引入了一个更容易理解的模型来指定备份和还原的选项 同时还支持使用事务日志标识来还原工作到指定点或进行数据库的部分还原 6 对公用操作的可伸缩性增强增强的公用操作包括快速差异备份并行的数据库一致性校验和并行扫描 13 7 复制增强SQLServer2000改进并增强了合并复制 快照复制和事务复制等功能 并在复制中增加了可变化的订阅功能 因而实施 监视和管理复制变得更加容易 8 数据转换服务增强 1 支持键和约束 2 DTS支持键和约束可以使用数据导入导出向导从源表向目标表移动主键 外键和约束 14 3 新的定制任务SQLServer2000DTSDesigner和DTS对象模型提供了新的自定义任务功能从而可以创建执行任务的包或设置基于实时环境属性的变量 包括从或向Internet以及FTP站点导入数据 发送数据 以异步方式运行包 创建互相发送消息的包 创建执行其它包的包以及在同一事务中包含多个包执行 4 将DTS包保存在VB文件中将DTS包保存到Microsoft VisualBasic文件中 可以允许将通过DTS导入向导 DTS导出向导或DTSDesigner创建的包与VisualBasic程序结合在一起或被需要引用DTS对象模型组件的VisualBasic开发者用作原型 15 9 SQLServerAnalysisServicesSQLServer7 0中的OLAP OnlineAnalyticalProcessing 服务转变为SQLServer2000中的分析服务 AnalysisServices 分析服务还包括了新的数据挖掘功能 10 SQLServerMetaDataServicesSQLServer7 0中的贮藏室 Repository 部分在SQLServer2000中转化为元数据服务 MetaDataServices 16 7 2SQLServer2000的主要组件 SQLServer2000提供了一整套的管理工具和实用程序 使用这些工具和程序 可以设置和管理SQLServer进行数据库管理和备份 并保证数据的安全和一致 下面 对这些组件做一个简单的介绍 1 企业管理器 EnterpriseManager 企业管理器是SQLServer中最重要的管理工具 在使用SQLServer的过程中大部分的时间都是和它打交道 通过企业管理器可以管理所有的数据库系统工作和服务器工作 也可以调用其它的管理开发工具 17 7 2SQLServer2000的主要组件 2 查询分析器 QueryAnalyzer 查询分析器用于执行Transaction SQL命令等SQL脚本程序 以查询分析或处理数据库中的数据 这是一个非常实用的工具 对掌握SQL语言 理解SQLServer的工作有很大帮助 使用查询分析器的熟练程度是衡量一个SQLServer用户水平的标准 3 服务管理器 ServiceManager 服务管理器用于启动 暂停或停止SQLServer的4种服务 DTC DistributedTransactionCoordinator分布式事务协调器 MSSQLServerOLAPserviceSQLServerSQLServerAgent 18 4 客户端网络实用工具 ClientNetworkUtility 客户端网络实用工具用于配置客户端的连接 测定网络库的版本信息以及设定本地数据库的相关选项 5 服务器网络实用工具 ServerNetworkUtility 服务器网络实用工具用于配置服务器端的连接 测定网络库的版本信息 6 导入和导出数据 ImportandExportData 导入和导出数据采用DTSImport Export向导来完成 此向导包含了所有的DTS DataTransformationServices数据转换服务 工具提供了在OLEDB数据源之间复制数据的最简捷的方法 19 7 在IIS中配置SQLXML支持 ConfigureSQLXMLSupportinIIS IIS InternetInformationServices因特网信息服务 此工具可以在运行IIS的计算机上定义 注册虚拟目录 并在虚拟目录和SQLServer实例之间创建关联 8 事件探查器 Profiler 事件探查器的功能是监视SQLServer数据库系统引擎事件 主要用于监听SQLServer系统的运行性能 9 联机丛书 BooksOnline SQLServer2000提供了大量的联机文档 用户可以便捷地查询到许多很有价值的信息 一个优秀的SQLServer管理员必然是使用联机文档的高手 20 7 3SQLServer2000数据库操作 7 3 1系统数据库SQLServer2000有四个系统数据库 Master Model Msdb Tempdb 这些系统数据库的文件存储在MicrosoftSQLServer默认安装目录的MSSQL子目录的Data文件夹中 1 Master数据库Master数据库记录了一个SQLServer系统的所有系统信息 这些系统信息主要有 1 所有的登录信息 2 系统设置信息 3 SQLServer初始化信息 4 系统中其它系统数据库和用户数据库的相关信息 包括其主文件的存放位置等 21 2 Model数据库Model数据库是所有用户数据库和Tempdb数据库的创建模板 当创建数据库时 系统会将Model数据库中的内容复制到新建的数据库中去 由此可见 利用Model数据库的模板特性 通过更改Model数据库的设置 并将时常使用的数据库对象复制到Model数据库中可以大大简化数据库及其对象的创建设置工作 为用户节省大量的时间 通常可以将以下内容添加到Model数据库中 1 数据库的最小容量 2 数据库选项设置 3 经常使用的数据库对象 如用户自定义的数据类型 函数规则 缺省值等 22 3 Msdb数据库SQLServerSQLServerEnterpriseManager和SQLServerAgent使用Msdb数据库来存储计划信息以及与备份和还原相关的信息 尤其是SQLServerAgent需要使用它来执行安排工作和警报记录操作者等操作 4 Tempdb数据库Tempdb数据库用作系统的临时存储空间 其主要作用有 1 存储用户建立的临时表和临时存储过程 2 存储用户说明的全局变量值 3 为数据排序创建临时表 4 存储用户利用游标说明所筛选出来的数据在Tempdb数据库中所做的存储不会被记录 因而在Tempdb数据库中的表上进行数据操作比在其它数据库中要快得多 23 实例数据库 SQLServer2000有两个实例数据库 pubs和Northwind 与系统数据库一样 实例数据库的文件也存储在MicrosoftSQLServer默认安装目录的MSSQL子目录的Data文件夹中 pubs和Northwind数据库可以作为SQLServer的学习工具 SQLServerBooksOnline中的实例基本上都是基于这两个数据库来讲解的 其中 24 实例数据库 pubs实例数据库存储了一个虚构的图书出版公司的基本情况 Northwind实例数据库包含了一个公司的销售数据 此公司名为Northwind商人 NorthwindTraders 是一个虚构的公司 从事食品的进出口业务 如果改变了实例数据库的内容或不小心删除了实例数据库想将其回复到初始状态可以用查询分析器运行安装目录 MSSQL Install中的Instpubs sql或Instnwnd sql程序来重建pubs或Northwind数据库 25 创建数据库 文件与文件组在SQLServer中数据库是由数据库文件和事务日志文件组成的 一个数据库至少应包含一个数据库文件和一个事物日志文件 26 1 数据库文件 DatabaseFile 数据库文件是存放数据库数据和数据库对象的文件 一个数据库可以有一个或多个数据库文件 一个数据库文件只属于一个数据库 当有多个数据库文件时有一个文件被定义为主数据库文件 PrimaryDatabaseFile 扩展名为mdf 它用来存储数据库的启动信息和部分或全部数据 27 1 一个数据库只能有一个主数据库文件 其它数据库文件被称为次数据库文件 SecondaryDatabaseFile 扩展名为ndf 用来存储主文件没存储的其它数据 采用多个数据库文件来存储数据的优点体现在 1 数据库文件可以不断扩充而不受操作系统文件大小的限制 2 可以将数据库文件存储在不同的硬盘中 这样可以同时对几个硬盘做数据存取 提高了数据处理的效率 这对于服务器型的计算机尤为有用 28 2 事务日志文件 TransactionLogFile 事务日志文件是用来记录数据库更新情况的文件 扩展名为ldf 例如 使用INSERT UPDATE DELETE等对数据库进行更改的操作都会记录在此文件中 而如SELECT等对数据库内容不会有影响的操作则不会记录在案 一个数据库可以有一个或多个事务日志文件 29 SQLServer中采用 Write Ahead 提前写 方式的事务 即对数据库的修改先写入事务日志中 再写入数据库 其具体操作是 系统先将更改操作写入事务日志中 再更改存储在计算机缓存中的数据 为了提高执行效率 此更改不会立即写到硬盘中的数据库 而是由系统以固定4的时间间隔执行CHECKPOINT命令 将更改过的数据批量写入硬盘 30 SQLServer有个特点 它在执行数据更改时会设置一个开始点和一个结束点 如果尚未到达结束点就因某种原因使操作中断 则在SQLServer重新启动时会自动还原已修改的数据使其返回未被修改的状态 由此可见 当数据库破坏时可以用事务日志还原数据库内容 31 3 文件组 FileGroup 文件组是将多个数据库文件集合起来形成的一个整体 每个文件组有一个组名 与数据库文件一样 文件组也分为主文件组和次文件组 一个文件只能存在于一个文件组中 一个文件组也只能被一个数据库使用 主文件组中包含了所有的系统表 当建立数据库时 主文件组包括主数据库文件和未指定组的其它文件 在次文件组中可以指定一个缺省文件组 那么在创建数据库对象时 如果没有指定将其放在哪一个文件组中 就会将它放在缺省文件组中 如果没有指定缺省文件组 则主文件组为缺省文件组 32 用QueryAnalyzer创建数据库createdatabase数据库名用EnterpriseManager创建数据库注 数据库的名称最长为128个字符 且不区分大小写 一个服务器在理论上可以管理32 767个数据库 33 7 3 3查看数据库信息 用EnterpriseManager查看数据库信息SQLServer提供了目录树的浏览方式 使得浏览数据库信息非常方便 快捷 在EnterpriseManager窗口中查看数据库信息的方法如下 方法1 在EnterpriseManager窗口中的左侧目录树窗口中 展开 数据库 文件夹 在某个数据库名称上单击右键 在出现的快捷菜单中单击 任务板 要浏览的数据库文件夹 34 方法2 在EnterpriseManager窗口中的左侧目录树窗口中 展开 数据库 文件夹 单击某个数据库名称 然后单击 查看 菜单 在其下拉菜单中单击 任务板 操作完成后 在EnterpriseManager窗口右侧的 任务板 窗口中看到数据库的 常规 信息 表 信息和 向导 信息 在打开数据库文件夹目录树后 可以选择各种数据库对象进行信息浏览 35 用系统存储过程显示数据库信息SQLServer提供了许多很有用的系统存储过程 可以用它们来得到许多从EnterpriseManager界面中所不易或不能看到的信息 可以把存储过程当作函数或命令来用 1 用系统存储过程显示数据库结构可以使用系统提供的系统存储过程Sp helpdb来显示数据库结构 其语法如下 sp helpdb dbname name 36 用系统存储过程显示数据库信息使用Sp helpdb系统存储过程可以显示指定数据库的信息 如果不指定 dbname name 子句则会显示在master dbo sysdatabases表中存储的所有数据库信息 命令执行成功会返回0 否则返回1 如 显示Northwind数据库的信息 execsp helpdbNorthwind 37 2 用系统存储过程显示文件信息可以使用系统提供的系统存储过程Sp helpfile来显示当前数据库中的文件信息 其语法如下 sp helpfile filename name 如果不指定文件名称 则会显示当前数据库中所有的文件信息 命令执行成功会返回0 否则返回1 如 显示Northwind数据库中northwind文件的信息 useNorthwindexecsp helpfilenorthwind 38 3 用系统存储过程显示文件组信息可以使用系统提供的系统存储过程Sp helpfilegroup来显示当前数据库中文件组信息 其语法如下 sp helpfilegroup filegroupname name 如果不指定文件组名称 则会显示当前数据库中所有的文件组 命令执行成功会返回0 否则返回1 如 显示Northwind数据库中的所有文件组信息 useNorthwindexecsp helpfilegroup 39 更改数据库 更改文件及其属性可以在EnterpriseManager中利用数据库属性设置更改数据库文件和事务日志文件 也可以用ALTERDATABASE命令来更改数据库 ALTERDATABASE命令可以增加或删除数据库中的文件也可以修改文件的属性 应注意的是只有数据库管理员 DBA 或具有CREATEDATABASE权限的数据库所有者才有权执行此命令 如 修改Northwind数据库中的Northwind文件增容方式为一次增加2MB alterdatabaseNorthwindmodifyfile name Northwind filegrowth 2mb 40 更改数据库名重命名数据库需要使用系统存储过程Sp renamedb 其语法如下 sp renamedb old name old name new name new name 如 假设study数据库已存在 更改study数据库的名称为Student execsp renamedb study Student 41 删除数据库 用EnterpriseManager删除数据库在EnterpriseManager中在所要删除的数据库上单击右键 从快捷菜单中选择 删除 选项即可删除数据库也可以选择数据库文件夹或图标后从工具栏中选择图标来删除数据库系统会提示确认是否要删除数据库删除数据库一定要慎重 因为删除数据库后 与此数据库有关联的数据库文件和事务日志文件都会被删除 存储在系统数据库中的关于该数据库的所有信息也会被删除 42 删除数据库 用DROPDATABASE命令删除数据库DROPDATABASE命令可以从SQLServer中一次删除一个或几个数据库 数据库所有者DBO和数据库管理员DBA才有权执行此命令 如 删除数据库study dropdatabasestudy 43 压缩数据库 数据库在使用一段时间后时常会出现因数据删除而造成数据库中空闲空间太多的情况 这时就需要减少分配给数据库文件和事务日志文件的磁盘空间 以免浪费磁盘空间 当数据库中没有数据时 可以修改数据库文件属性 直接改变其占用空间 但当数据库中有数据时这样做会破坏数据库中的数据 因此需要使用压缩的方式来缩减数据库空间 对数据库可以进行自动压缩 也可以进行人工压缩 44 自动压缩数据库在EnterpriseManager中左侧窗口中 右健单击某个数据库名称 在出现的快捷菜单中 单击 属性 菜单项 出现 数据库属性 对话框 单击对话框中的 选项 选项卡 在本页中选择 自动收缩 选项 让系统自动压缩数据库 人工压缩数据库人工压缩数据库有以下两种方式 1 用EnterpriseManager压缩数据库在EnterpriseManager中在所要压缩的数据库上单击右键 从快捷菜单中的 所有任务 中选择 收缩数据库 选项 并进行相应的配置 45 2 用Transact SQL命令压缩数据库可以使用DBCCSHRINKDATABASE和DBCCSHRINKFILE命令来压缩数据库 其中 DBCCSHRINKDATABASE命令对数据库进行压缩 DBCCSHRINKFILE命令对数据库中指定的文件进行压缩 46 2 用Transact SQL命令压缩数据库 1 DBCCSHRINKDATABASE命令如 压缩数据库study的未使用空间为数据库大小的20 dbccshrinkdatabase study 20 2 DBCCSHRINKFILE如 压缩数据库study中的数据库文件Study的大小到1MB usestudydbccshrinkfile Study 1 47 7 3 4视图查看修改视图在SQLSERVER中 通过EnterpriseManager查看和修改视图主要执行以下步骤 方法1 1 启动EnterpriseManager 登录到指定的服务器 2 打开要创建视图的数据库文件夹 选中 视图 图标 此时在右面的窗格中显示当前数据库的所有视图 48 7 3 4视图3 在右窗格中右键单击要查看的视图 在弹出菜单中选择 属性 菜单项 打开 视图属性 对话框 4 在该对话框内可浏览到该视图的SQL文本 也可以对该视图进行修改 然后单击 检查语法 按钮来对语句合法性进行检查 若要对视图的访问权限进行设置 请单击 权限 按钮 49 方法2 1 启动EnterpriseManager 登录到指定的服务器 2 打开要创建视图的数据库文件夹 选中 视图 图标 此时在右面的窗格中显示当前数据库的所有视图 3 在右窗格中右键单击要查看的视图 在弹出菜单中选择 设计视图 菜单项 即可进入到设计视图的窗口 4 在该窗口中可按照创建新视图的方法对原有的视图进行各种修改 最后存盘即可 50 使用存储过程检查视图在SQLSERVER中有三个关键存储过程有助于了解视图信息 它们分别为 1 sp depends2 sp help3 sp helptext存储过程sp depends返回系统表中存储的任何信息 该系统表指出该对象所依赖的对象 除视图外 这个系统过程可以在任何数据库对象上运行 其语法如下 sp depends数据库对象名称 51 使用存储过程检查视图系统过程sp help用来返回有关数据库对象的详细信息 如果不针对某一特定对象 则返回数据库中所有对象信息 其语法如下 sp help数据库对象名称系统过程sp helptext检索出视图 触发器 存储过程的文本其 语法为 sp helptext视图或触发器或存储过程 52 删除视图除了可以使用第3章中介绍的使用SQL命令来删除视图外 在SQLSERVER中 通过EnterpriseManager也可删除视图 主要执行以下步骤 1 启动EnterpriseManager 登录到指定的服务器 2 打开要创建视图的数据库文件夹 选中 视图 图标 此时在右面的窗格中显示当前数据库的所有视图 3 在右窗格中右键单击要查看的视图 在弹出菜单中选择 删除 菜单项 53 7 3 5存储过程 系统存储过程 扩展存储过程 用户自定义存储过程 一 存储过程的概念 优点及分类存储过程是一组为了完成特定功能的SQL语句集 存储过程的优点 存储过程的分类 模块化的程序设计高效率的执行减少网络流量可以作为安全机制使用 54 二 创建存储过程 当创建存储过程时 需要确定存储过程的三个组成部分 1 所有的输入参数以及传给调用者的输出参数 2 被执行的针对数据库的操作语句 包括调用其他存储过程的语句 3 返回给调用者的状态值以指明调用是成功还是失败 用CREATEPROCEDURE命令创建存储过程 55 CREATEPROCEDUREprocedure name number parameterdata type VARYING default OUTPUT n WITH RECOMPILE ENCRYPTION RECOMPILE ENCRYPTION FORREPLICATION ASsql statement n 例7 1 在Teach数据库中 创建一个名称为MyProc的不带参数的存储过程 该存储过程的功能是从数据表S中查询所有男同学的信息 CREATEPROCEDUREMyProcASSELECT FROMSWHEREsex 男 56 例7 2 定义具有参数的存储过程 在Teach数据库中 创建一个名称为InsertRecord的存储过程 该存储过程的功能是向S数据表中插入一条记录 新记录的值由参数提供 CREATEPROCEDUREInsertRecord snochar 6 snchar 20 agenumeric 5 sexchar 2 deptchar 10 ASINSERTINTOSVALUES sno sn age sex dept 57 例7 3 定义具有参数默认值的存储过程 在Teach数据库中 创建一个名称为InsertRecordDefa的存储过程 该存储过程的功能是向S数据表中插入一条记录 新记录的值由参数提供 如果未提供系别Dept的值时 由参数的默认值代替 CREATEPROCEDUREInsertRecordDefa snochar 6 snchar 20 agenumeric 5 sexchar 2 deptchar 10 无 ASINSERTINTOSVALUES sno sn age sex dept 58 例7 4 定义能够返回值的存储过程 在Teach数据库中 创建一个名称为QueryTeach的存储过程 该存储过程的功能是从数据表S中根据学号查询某一同学的姓名和系别 查询的结果由参数 sn和 dept返回 CREATEPROCEDUREQueryTeach snochar 6 snchar 20 OUTPUT deptchar 10 OUTPUT ASSELECT sn SN dept DeptFROMSWHERESNo sno 59 查看存储过程 重新命名存储过程 删除存储过程 执行存储过程 修改存储过程 EXECsp helptext存储过程名称 sp rename原存储过程名 新存储过程名 DROPPROCEDURE procedure n ALTERPROCEDUREprocedure name EXECMyProc 60 7 3 6触发器 1触发器的概念 分类与作用触发器是一种特殊类型的存储过程 触发器主要是通过事件进行触发而被执行的 而存储过程可以通过存储过程名字而被直接调用 触发器有4个要素 名称 触发器有一个符合标志符命名规则的名称 定义的目标 触发器必须定义在表或者视图上 触发条件 是UPDATE INSERT还是DELETE语句 触发逻辑 触发之后如何处理 61 强化约束 跟踪变化 存储过程的调用 级联运行 触发器的种类AFTER触发器AFTER触发器是告诉SQL语句执行了INSERT UPDATE或者DELETE操作后干什么 INSTEADOF触发器告诉当要执行INSERT UPDATE或DELETE操作时用什么别的操作来代替 触发器的作用 62 2触发器的工作原理 SQLServer在工作时为每个触发器在服务器的内存上建立两个特殊的表 插入表和删除表 1 插入表的功能一旦对该表执行了插入 INSERT 操作 那么对该表插入的所有行来说 都有一个相应的副本存放到Inserted表中 即Inserted表用来存储原表插入的内容 2 删除表的功能一旦对该表执行了删除 DELETE 操作 则将所有的删除行存放至Deleted表中 这样做的目的是 一旦触发器遇到了强迫它中止的语句被执行时 删除的那些行可以从Deleted表中得以还原 63 3创建触发器 在创建触发器以前必须考虑到以下几个方面 CREATETRIGGER语句必须是批处理的第一个语句 表的所有者具有创建触发器的缺省权限 表的所有者不能把该权限传给其他用户 触发器是数据库对象 所以其命名必须符合命名规则 尽管在触发器的SQL语句中可以参照其他数据库中的对象 但是触发器只能创建在当前数据库中 虽然触发器可以参照视图或临时表 但不能在视图或临时表上创建触发器 只能在基表或在创建视图的表上创建触发器 一个触发器只能对应一个表 这是由触发器的机制决定的 尽管TRUNCATETABLE语句如同没有WHERE从句的DELETE语句 但是由于TRUNCATETABLE语句没有被记入日志 所以该语句不能触发DELETE型触发器 WRITETEXT语句不能触发INSERT或UPDATE型的触发器 64 例7 5 创建一个触发器 当向S表中插入一条记录时 自动显示S表中的记录 CREATETRIGGERChangeDisplayONSFORINSERTASSELECT FROMS使用系统存储过程查看触发器EXECsp help 触发器名 了解触发器的一般信息 如触发器的名字 属性 类型 创建时间 65 EXECsp helptext 触发器名 查看触发器的正文信息EXECsp depends 触发器名 EXECsp depends 表名 查看指定触发器所引用的表或指定的表所涉及到的所有触发器修改触发器使用sp rename修改触发器的名字sp renameoldname newname 66 通过ALTERTRIGGER命令修改触发器正文ALTERTRIGGERtrigger nameON table view WITHENCRYPTION FOR AFTER INSTEADOF DELETE INSERT UPDATE NOTFORREPLICATION AS删除触发器用系统命令DROPTRIGGER删除指定的触发器DROPTRIGGER触发器名删除触发器所在的表时 SQLServer将自动删除与该表相关的触发器 67 7 4备份和还原 备份和还原备份是对SQLServer数据库或事务日志进行复制 数据库备份记录了在进行备份这一操作时数据库中所有数据的状态 如果数据库因意外而损坏 这些备份文件将在数据库还原时用来还原数据库 还原就是把遭受破坏 丢失的数据或出现错误的数据库还原到原来的正常状态 68 备份的类型 数据库备份 事务日志备份 差异备份 文件和文件组备份 数据库备份是指对所有的数据以及数据库对象备份 事务日志备份是指对数据库发生的事务进行备份 差异备份是指将最近一次数据库备份以来发生的数据变化备份起来 文件或文件组备份是指对数据库文件或文件组进行备份 69 备份和还原的策略SQLServer提供了几种方法来减少备份或还原操作的执行时间 1 使用多个备份设备来同时进行备份处理 2 综合使用完整数据库备份 差异备份或事务日志备份来减少每次需要备份的数据量 3 使用文件或文件组备份以及事务日志备份 这样可以只备份或还原那些包含相关数据的文件 而不是整个数据库 在SQLServer2000中有三种数据库还原模式简单还原完全还原批日志还原 70 7 4 2创建备份设备 使用存储过程管理备份设备sp addumpdevice devtype device type logicalname logical name physicalname physical name cntrltype controller type devstatus device status 71 例7 6 创建一个磁盘备份设备 EXECsp addumpdevice disk pubss c backdev backdevpubs bak 例7 7 创建远程磁盘备份设备 EXECsp addumpdevice disk networkdevice servername sharename path filename ext 例7 8 创建磁带备份设备 EXECsp addumpdevice tape tapedump1 tape0 使用sp dropdevice来删除备份设备sp dropdevice logicalname device delfile delfile 72 集成服务DTS 数据转换服务是一个功能非常强大的组件 其中 导入和导出向导提供了把数据从一个数据源转换到另一个数据目的地的简单方法 该工具可以在异构数据环境中拷贝数据 拷贝整个表或者查询结果 并且可以交互式地定义数据转换方式 SQLServer商务智能开发平台是一个图形工具 它使创建和编辑集成服务包 SSIS包 的工作变得更加简单和轻松 而且它提供了比导入 导出向导更为强大的功能 可以向SSIS包中添加控制流 数据流任务和事件处理程序 73 数据的导入和导出 7 5 1数据的导入7 5 2数据的导出 74 数据的导入 导入Access数据库利用导入 导出向导导入Access数据库的步骤如下 1 打开SQLServer管理平台 展开服务器和数据库 右击该数据库图标 从弹出的快捷菜单中选择 任务 导入数据 选项 如图所示 启动数据导入向导工具 就会出现欢迎使用向导对话框 对话框中列出了导入向导能够完成的操作 2 单击 下一步 按钮 则出现选择数据源对话框 如图所示 在该对话框中 可以选择数据源类型 文件名 用户名和密码等选项 图11 1打开导入向导 图11 2选择数据源对话框 75 导入Access数据库 3 单击 下一步 按钮 则出现选择导入的目标数据库类型对话框 如图所示 本例使用SQLServer数据库作为目标数据库 在目标对话框中选择SQLNativeClient 在服务器名称框中输入目标数据库所在的服务器名称 下方需要设定连接服务器的安全模式以及目标数据库的名称 设定完成后 单击 下一步 按钮 则出现指定表复制或者查询对话框 如图所示 图11 3选择目标对话框 图11 4指定表复制或查询对话框 76 导入Access数据库 4 单击 下一步 按钮 就会出现选择源表和视图对话框 如图所示 在该对话框中 可以设定需要将源数据库中的哪些表格传送到目标数据库中去 单击表格名称左边的复选框 可以选定或者取消对该表格的复制 如果想编辑数据转换时源表格和目标表格之间列的对应关系 可单击表格名称右边的 编辑 按钮 则出现列映射对话框 如图所示 图11 5选择源表和视图对话框 图11 6列映射对话框 77 导入Access数据库 5 在图中单击 下一步 按钮 则会出现 保存并执行包 对话框 如图所示 在该对话框中 可以指定是否希望保存SSIS包 也可以立即执行导入数据操作 图11 7 保存并执行包 对话框 78 导入Access数据库 6 单击 下一步 按钮 则出现 包保护级别 对话框 如图所示 点击 确定 按钮可完成包保护级别设定 并打开 保存SSIS包 页框 如图所示 图11 8 包保护级别 对话框 图11 9保存SSIS包对话框 79 导入Access数据库 7 单击 下一步 按钮 则出现向导完成确认对话框 如图所示 其中显示了在该向导中进行的设置 如果确认前面的操作正确 单击 完成 按钮后进行数据导入操作 否则 单击 上一步 按钮返回修改 图11 10完成向导对话框 80 2 导入文本文件 1 打开SQLServer管理平台 展开选定的服务器和数据库 右击该数据库图标 从弹出的快捷菜单中选择 任务 导入数据 选项 如图所示 启动数据导入向导工具 就会出现欢迎使用向导对话框 对话框中列出了导入向导能够完成的操作 2 单击 下一步 按钮 则出现选择数据源对话框 如图所示 这里在数据源栏中选择平面文件源 即文本文件 图11 11选择文本数据源对话框 81 2 导入文本文件 3 单击 下一步 按钮 就会出现选择目的数据库类型对话框 如图所示 这里选择为SQLServer 选定服务器名称和数据库名称后 单击 下一步 按钮 则出现选择源表和视图对话框 如图所示 图11 12选择目的数据库对话框 图11 13选择源表和视图对话框 82 2 导入文本文件 4 单击映射 编辑 按钮 则出现列映射对话框 如图所示 点击 确定 按钮保存所作设置 单击 下一步 按钮 则出现 保存并执行包 对话框 在该对话框中 可以设置立即执行或者保存SSIS包以备以后执行 5 单击 下一步 按钮 则出现确认导入数据对话框 如图所示 图11 14选择列映射对话框 图11 15确认导入数据对话框 83 2 导入文本文件 6 如果在向导中设定了立即执行 在向导结束后 则会出现数据导入对话框 如图所示 该对话框中执行向导中定义的复制操作 图11 16进行数据导入对话框 84 7 5 2数据的导出 1 导出数据库至Access 1 打开SQLServer管理平台 右击服务器图标 从弹出的快捷菜单中选择 所有任务 导出数据 选项 则会出现数据转换服务导入和导出向导对话框 它显示了导出向导所能完成的操作 2 单击 下一步 按钮 就会出现选择导出数据的数据源对话框 如图所示 这里在数据源栏中选择 MicrosoftOLEDBProviderforSQLServer 选项 然后选择身份验证模式以及数据库的名称 图11 17选择数据源对话框 85 1 导出数据库至Access 3 单击 下一步 按钮 则会出现选择目的对话框 如图所示 4 选定目标数据库后 单击 下一步 按钮 则出现指定表复制或查询对话框 如图所示 图11 19指定表复制或查询对话框 图11 18选择目的数据库对话框 86 1 导出数据库至Access 5 单击 下一步 按钮 则出现选择源表和视图对话框 如图所示 其中可以选定将源数据库中的哪些表格或视图复制到目标数据库中 只需单击表格名称左边的复选框 即可选定或者取消删除复制该表格或视图 单击 编辑 按钮 就会出现列映射对话框 如图所示 图11 21列映射对话框 图11 20选择源表和视图对话框 87 1 导出数据库至Access 6 选定某个表格后 单击 预览 按钮 就会出现查看数据对话框 如图所示 在该对话框中可以预览表格内的数据 单击 下一步 按钮 则会出现 保存并执行包 对话框 在该对话框中 可以设定立即执行还是保存包以备以后执行 7 单击 下一步 按钮 就会出现导出向导结束对话框 如图所示 图11 23确认导出数据对话框 图11 22查看数据对话框 88 2 导出数据库至文本文件 1 打开SQLServer管理平台 右击服务器图标 从弹出的快捷菜单中选择 所有任务 导出数据 选项 则会出现数据转换服务导入和导出向导对话框 它显示了该导出向导所能完成的操作 2 单击 下一步 按钮 则会出现选择数据源对话框 3 单击 下一步 按钮 就会出现选择目标对话框 如图 在 目标 栏中选择 平面文件目标 选项 单击 浏览 按钮 则会出现选择文件对话框 如图所示 可以设定目标文件的文件名 图11 25选择文件对话框 图11 24选择目标对话框 89 2 导出数据库至文本文件 4 单击 下一步 按钮 就会出现指定表复制或查询对话框 其中可以选定将源数据库中的表格或视图复制到文本文件 还是将满足查询结果的记录复制到文本文件 5 单击 下一步 按钮 则出现 配置平面文件目标 对话框 如图所示 如图所示在该对话框中单击 编辑转换 按钮 则出现列映射对话框 图11 27列映射对话框 图11 26配置平面文件目标对话框 90 2 导出数据库至文本文件 6 在该对话框中单击 预览 按钮 可查看要导出的表中的数据 如图11 28所示 单击 下一步 按钮 就会出现 保存并执行包 对话框 单击 下一步 按钮 则出现 完成该向导 对话框 如图 图11 28预览数据对话框 图11 29完成向导对话框 91 合理有效的数据库安全机制可以既保证被授权用户能够方便地访问数据库中的数据 又能够防止非法用户的入侵 SQLServer2000提供了一套设计完善 操作简单的安全管理机制 7 6SQLServer安全管理 92 7 6 1SQLServer2000的安全体系 SQLServer2000的安全性机制由四层构成 可以用下图表示 从用户的角度讲 要访问数据库 需经过如下步骤 93 SQLServer2000的安全性机制 94 1 一个数据库用户必须有权登录操作系统 即该用户在Windows2000Server操作系统中具有登录账户 在这个前提条件下 才有可能进入SQLServer2000系统 95 2 一旦登录了操作系统 登录者还必须得到数据库系统的通行证 数据库服务器的登录账户 才具有数据库服务器的连接权或登录权 SQLServer2000只有在验证了指定的登录账户有效后 才完成连接 这种对登录账户的验证称为身份验证 对用户而言 登录SQLServer2000需要使用以下两类身份验证之一 96 Windows身份验证 此种验证方式仅用于Windows2000Server NT操作系统 在这种方式下 用户只要通过Windows2000Server NT操作系统的登录验证 就可以连接到SQLServer2000数据库实例 SQLServer身份验证 在此种验证方式下 即使用户已经登录操作系统 也必须输入有效