Server数据库管理系统的高级应用.doc

第六章 SQL Server数据库管理系统的高级应用主要知识点：数据库和表；约束；默认；规则；SQL Server的备份和恢复；SQL Server的安全性重 点：数据库和表；约束难 点：约束教学学时 ：理论6学时，实践6学时教学方式 ：讲授教学内容：6.1 数据库和表6.1.1数据库存储结构1数据库文件和文件组SQL Server 2000用文件来存储数据库，数据库文件有三类：l 主数据文件（Primary）：存放数据。每个数据库都必须有且仅有一个主数据文件。以.mdf为默认扩展名。包含的系统表格记载数据库中对象及其他文件的位置信息。l 次要数据文件（Secondary）：存放数据。以.ndf为默认扩展名。可有可无。主要在一个数据库跨多个硬盘驱动器时使用。l 事务日志文件（Transaction Log）：存放事务日志。每个数据库必须有一个或多个日志文件。以.ldf为默认扩展名。记录数据库中已发生的所有修改和执行每次修改的事务。注意：每个数据库的：主数据库文件数=1；次要数据文件数=0；事务日志文件数=1文件允许多个数据库文件组成一个组，即文件组，是文件的逻辑集合，SQL Server 2000通过对文件进行分组，以便于管理数据的分配或配置。文件组对组内的所有文件都使用按比例填充策略。SQL Server 2000有三种类型的文件组：主文件组（primary）；用户定义的文件组；默认的文件组（default）。注意：这里默认的文件组用来存放任何没有指定文件组的对象；主文件组包含主数据文件，存放系统表格等；事务日志文件不能属于文件组；SQL Server2000至少包含一个文件组，即主文件组。2系统数据库SQL Server 2000 内部创建和提供了一组数据库，有4个系统数据库（master、msdb、model、tempdb）和两个附带的示例数据库（pubs、northwind）。master数据库：记录了所有系统信息，包括所有的其他数据库、登录帐号和系统配置。是最主要的系统数据库。msdb数据库：是SQL Server Agent服务使用的数据库，用来执行预定的任务，如数据库备份和数据转换、警报和作业等。model 数据库：样板数据库。为用户数据库提供样板。tempdb数据库：也是从model拷贝而来。存储了SQL Server实例运行期间SQL Server需要的所有临时数据。pubs和northwind数据库：是两个用户数据库，系统附带的，可以删除，也可以恢复。其中pubs示例数据库以一个图书出版公司为模型，northwind示例数据库以一个名为Northwind Traders的虚构公司为模型，存放了一些公司的销售数据，该公司从事世界各地的特产食品进出口贸易。示例数据库的恢复：可以使用SQL Server安装中Install目录下的文件重新进行安装恢复。3数据库文件的空间分配在创建数据库前需估算所建数据库的大小及增幅。定义一个恰当的数据库大小。计算依据：数据库的最小尺寸必须等于或大于model数据库的大小。估算数据库的大小，在SQL Server 2000中最基本的数据存储单元是页，每页的大小为8KB（8192字节），每页除去96字节的头部（用来存储有关的页信息，如页类型、可用空间、拥有页的对象的对象ID等），剩下的8096字节（8192-96=8096）用来存储数据。默认情况下事务日志文件的大小是数据库文件大小的25%。SQL Server 2000 数据库的数据文件中的八种页类型：l 数据页：存储数据库数据，包含数据行中除text、ntext和image数据外的所有数据。l 索引页：用于存储索引数据。l 文本/图像页：用于存储text、ntext和image数据。l 全局分配页：用于存储扩展盘区分配的信息。l 页面剩余空间页：用于存储页剩余空间的信息。l 索引分配页：用于存储页被表或索引使用的扩展盘区信息。l 大容量更改映射表：有关自上次执行BACKUP LOG语句后大容量操作所修改的扩展盘区的信息。l 差异更改映射表：自上次执行BACKUP DATABASE语句后更改的扩展盘区的信息。数据页包含数据行中除text、ntext和image数据外的所有数据，text、ntext和image数据存储在单独的页中。在数据页上，数据行紧接着页首按顺序放置。在页尾有一个行偏移表。在行偏移表中，页上的每一行都有一个条目，每个条目记录那一行的第一个字节与页首的距离。行偏移表中的条目序列与页中行的序列相反。扩展盘区是一种基本单元，可将其中的空间分配给表和索引。一个扩展盘区是8个邻接的页（或64KB）。这意味着 SQL Server 2000 数据库每兆字节有16个扩展盘区。例：假设某个数据库中只有一个表，该表的每行记录是500字节，共有10000行数据。试估计此数据库的大小。分析：由于一个数据页最多可存放8096字节的数据，按行顺序存放，可知这时，一个数据页上最多只能容纳的行数是：809650016（行）。此表共有10000行，那么该表将占用的页数是：1000016=625（页）。因此该数据库的大小估计为（6258KB）10245MB。4数据库规划数据库规划的过程包括：l 确定系统的范围；l 确定开发工作所需的资源（人员、硬件和软件）；l 估计软件开发的成本；l 确定项目进度。6.1.2数据库的创建、修改和删除1创建数据库（1）用创建数据库向导创建数据库（操作演示）（2）用企业管理器创建数据库（操作演示）（3）用T-SQL语句创建数据库T-SQL语句格式：CREATE DATABASE 数据库名ON （数据文件文件组定义（FILEGROUP）LOG ON （日志文件定义）在这里，数据日志文件定义格式包括：逻辑文件名, 物理文件名, 容量 （初始、最大、增长幅度）NAME , FILENAME , SIZE , MAXSIZE, FILEGROWTH定义主数据文件加：PRIMARY定义文件组加：FileGroup 文件组名例：创建一个student数据库，操作系统文件名为：student_dat.mdf，数据文件大小为2MB，以10%速度增长，日志文件大小为1MB。T-SQL语句如下：create database studenton(name=student_dat,filename=d:student_dat.mdf,size=2MB,filegrowth=10% )log on (name=student_log,filename=d:studnet_log.ldf,size=1MB,filegrowth=10%)2修改数据库可以使用不同的方法来修改数据库。（1）在企业管理器中修改数据库（操作演示）（2）用T-SQL语句修改数据库ALTER DATABASE 数据库名ADD FILE 数据文件定义 TO FILEGROUP ADD LOG FILE 事务日志文件定义REMOVE FILE 逻辑文件名ADD FILEGROUP 文件组名REMOVE FILEGROUP 文件组名MODIFY FILE 数据文件定义MODIFY NAME = 新数据库名MODIFY FILEGROUP 文件组名3删除数据库（1）在企业管理器中删除数据库（操作演示）（2）用T-SQL语句删除数据库语法：DROP DATABASE 数据库名称注：不能除去当前正在使用的数据库；不能除去系统数据库；在除去用于复制的数据库之前，首先删除复制；若要使用DROP DATABASE，连接的数据为上下文必须在master数据库中。 6.1.3管理数据库1查看数据库信息（1）查看数据库定义信息使用系统存储过程sp_helpdb查看数据库或所有数据库的信息。语法格式：sp_helpdb 数据库名称。如果不指定数据库名称，将返回所有数据库的信息。（2）查看数据库数据、日志空间使用sp_spaceused查看数据库数据空间。语法格式：sp_spaceused 数据库对象2数据库的分离与附加（1）分离数据库 点击“程序Microsoft SQL Server企业管理器”，打开企业管理器；展开服务器组，然后展开服务器，选中要分离的数据库；点击鼠标右键“所有任务分离数据库”；点击确定，该选定的数据库就被分离。应注重，只有“使用本数据库的连接”数为0时，该数据库才能分离。所以分离数据库时尽量断开所有对要分离数据库操作的连接，假如还有连接数据库的程序，会出现数据库的连接状态窗口，显示正在连接此数据库的机器以及名称，点击清除按钮将从服务器强制断开现有的连接。 （2）附加数据库 复制移动数据库文件在附加数据库之前，您必须将与数据库关联的 .MDF（主数据文件）.LDF（事务日志文件） 这两个文件复制到目标服务器上，或是同一服务器的同一文件目录下。这两个文件一般位于C：Program FilesMicrosoft SQL ServerMSSQLData目录下。 点击“程序Microsoft SQL Server企业管理器”，打开企业管理器 展开服务器组，然后展开服务器 右击数据库，然后选择“所有任务附加数据库”，弹出窗口单击确定按钮。新附加的数据库的数据库节点即创建在数据库文件夹中。 6.1.4 数据表的建立、修改和删除表是数据库中最主要的数据对象，用于存储和操作数据的一种逻辑结构。表存储在数据库文件中。SQL Server 2000中有两类表： 永久表 在创建后，除非用户删除，否则将一直存储在数据库文件中。 临时表 存储在tempdb中，当不再使用时会自动删除。而临时表有有两类n 本地临时表：名称以单个数字符号(#)打头；它们仅对当前的用户连接是可见的；当用户从SQL Server 2000实例断开连接时被删除。n 全局临时表：名称以数学符号(#)打头，创建后对任何用户都是可见的，当所有引用该表的用户从SQL Server断开连接时被删除。注意：l 每个数据库最多可存储20亿个表，每个表至多可定义1024列，每行最多可以存储8060字节。l 表和列的命名要遵守标识符的规定，在特定表中必须是唯一的，但同一数据库中的不同表可使用相同的列名。必须为每列指定数据类型。l 尽管对于每一个所有者在一个数据库内表的名称必须是唯一的，但如果为每张表指定不同的所有者，则可以创建多个具有相同名称的表。当您要使用此表时，可以通过指定表的使用者以及表名称来区分两个表。1创建数据表（1）使用企业管理器创建表（2）使用T-SQL语句创建表语法格式：CREATE TABLE 表名称（ 列定义, 表级完整性定义）ON 指定存储表的数据库文件组TEXTIMAGE_ON 说明text、ntext、image列数据的文件组名列定义格式：列名 列数据类型 列的约束例6.1： student数据库中创建表stu（学号，姓名，年龄，课程，成绩），T-SQL语句如下：create table stu(学号 char(8) primary key, 姓名 char(10) not null,年龄 tinyint null,课程 varchar(30) null, 成绩 numeric(4,1) default 0 )2修改数据表（1） 在企业管理器中修改表（2） 用T-SQL语句修改表3删除数据表（1） 在企业管理器中删除表（2） 用T-SQL语句删除表6.2约束、默认和规则6.2.1约束建立和使用约束的目的：保证数据的完整性，约束是SQL server强制实行的应用规则，它是通过限制列中的数据、行中的数据和表之间的数据来保证数据的完整性。约束是独立于表结构，在CREATE TABLE中声明，在ALTER TABLE中添加和删除。当表删除时，自带的所有约束定义自动被删除。约束总共包括以下几种：1）CHECK 约束；2）PRIMARY KEY约束；3）UNIQUE 约束；4）DEFAULT 约束5）FOREIGN KEY 约束；一、CHECK 约束CHECK 约束的作用：通过限制输入到一列或多列的值的范围，从而实现数据中的域完整性。即CHECK约束通过使用一个返回TRUE或FALASE的逻辑表达来检查存入字段中值是否符合指定的要求。如果逻辑表达式为TURE，则数据值可以写入字段；否则，无法写入字段。 注意：1）对于TineStamp和Identity两种数据类型不能定义CHECK约束； 2）CHECK约束的逻辑表达式中不能包含查询；1用企业管理器创建和删除CHECK约束（参见 实训指导书）。2用T-SQL语句创建和删除CHECK约束（1）利用SQL语句在创建表时创建CHECK约束创建CHECK约束的语法格式：CONSTRAINT 约束名 CHECK（逻辑表达式）例题6.2创建表成绩（学号，课程号，成绩）的同时定义CHECK约束要求成绩的范围为0100。T-SQL语句如下：CREATE TABLE 成绩 (学号char(8) NOT NULL， 课程号char(3) NOT NULL， 成绩tinyint NULL CONSTRAINT ck_cj CHECK（成绩=0 and成绩=0 and成绩=0 and cj=100二、绑定规则与默认对象一样，规则创建后，并不能直接使用，必须绑定到表中的某一列或用户定义数据类型上。1使用企业管理器绑定规则对象（参见实训指导书）。2使用sp_bindrule语句绑定规则对象语法格式：sp_bindrule rulename= 规则对象名 objname= 绑定的列名注释：绑定的列名要采用“表名.字段名”的格式，且要用单引号括起来。例6.20：将规则对象cj_rl绑定到XSCJ表的成绩列。use 学生成绩goexec sp_bindrule cj_rl，XSCJ.成绩三、 删除绑定1使用企业管理器删除绑定（参见实训指导书）。2使用sp_unbindrule语句解除绑定。语法格式：sp_unbindrule objname= 绑定的列名例6.21：删除XSCJ表成绩列和规则对象cj_rl的绑定。use 学生成绩exec sp_unbindrule XSCJ.成绩四、 删除规则对象1使用企业管理器删除规则对象（参见实训指导书）。2使用T-SQL语句DROP RULE删除规则对象语法格式： DROP RULE 规则对象名例6.22：删除规则对象cj_rl 。use 学生成绩 go DROP DEFAULT cj_rl 注：删除规则对象时，首先要解除规则对象的绑定，然 后才能删除规则对象。6.3 SQL Server的备份和恢复6.3.1概述1.什么叫备份?就是指对SQL Server数据库或事务日志进行复制,数据库备份记录在进行备份这一操作时数据库中所有数据的状态。如果数据库因意外而损坏,这些备份文件将在数据库恢复时被用来恢复数据库.2.什么叫恢复？就是把遭破坏或丢失数据或出现错误的数据库恢复到原来的正常状态.3.两者目的：尽最大可能以最快速度减少或消灭数据丢失.6.3.2 备份类型数据库备份(完全备份) 事务日志备份差异备份文件和文件组备份1.什么叫数据库备份(完全备份)数据库备份指对数据库的完整备份,包括所有的数据以及数据库对象.特点:速度慢,占用空间大,最好在晚间进行采用条件:数据不是非常重要,容易恢复,变化频率不大2.事务日志备份指对数据库发生的事务进行备份,包括从上次进行事务日志备份差异备份和数据库完全备份之后所有已经完成的事务.特点:速度快占用空间小,恢复复杂采用条件:完全备份后不允许发生数据丢失或损坏现象；存储备份文件的磁盘空间小；准备把数据库恢复到发生失败前一点3.差异备份指将最近一次数据库备份以来发生的数据变化备份起来,因此差异备份实际上是一种增量数据库备份.特点:数据量小,速度快采用条件:降低丢失数据的风险,减小恢复的复杂性4.文件和文件组备份指对数据库文件或文件夹进行备份特点:恢复速度快,只需恢复受损的文件采用条件:只需恢复受损的文件6.3.3备份和恢复的策略1. 备份方案1)通常我们按以下三个条件来决定备份方案：恢复能力、备份文件大小不一、备份时间2)常用备份方案:仅仅进行数据库备份;或在进行数据库备份同时进行日志备份;或用完整数据库备份和差异备份3)在选择备份方案时必须认识以下两点:如果只进行数据库备份,那么将无法完全恢复当前的数据库如果进行数据库备份同时进行日志备份,那么可以数据库恢复到失败点,但失败前提交的事务将无法2.恢复类型简单恢复：指在进行数据库恢复时仅使用了数据库备份或差异备份,而不涉及事务日志备份。策略(完全+差异)完全恢复：指通过使用数据库备份和事务日志备份将数据库恢复。策略(完全+差异+日志)批日志恢复：在性能上要优于上面两种,能尽最大努力减少批操作所需要的存储空间。3.策略(完全+差异+日志)6.3.4数据库备份1.步骤:1)创建数据库设备2)制定好备份策略3)检查要备份的数据库的一致性4)备份数据库2.备份设备1)概念用来存储数据库事务日志或文件和文件组备份的存储介质2)创建和删除 l 使用企业管理器创建和删除l 使用系统存储过程管理备份设备创建语法格式：sp_addumpdevice 介质类型,备份设备逻辑名,备份设备实际物理路径删除语法格式：sp_dropdevice备份设备逻辑名,备份设备实际物理路径例:创建一个逻辑名为stuback,实际文件名为c:backback1.baksp_addumpdevice disk,stuback, c:backback1.bak例:删除刚才创建的备份设备sp_dropdevice stuback, c:backback1.bak3.备份先检查数据库一致性（dbcc checkdb）,制定好备份策略企业管理器备份注意：简单模型的数据库不能进行日志备份检查刚才备份设备的内容（restore headeronly from stuback）6.3.5 数据库恢复1.恢复工作步骤：1)对数据库进行完全性检查2)了解备份设备中的内容3)针对数据库恢复类型及备份内容选择不同的恢复策略2.恢复策略不同恢复类型的数据库采用不同的恢复方案，对于完全恢复模型的数据库恢复按如下次序进行：1)恢复最近的一次完全备份2)恢复最后一个差异备份（有的话）3)从最后一个差异备份时刻后按时间的先后恢复4)每一个事务日志备份（有的话）对容量日志记录恢复模型恢复方法也是如此如：对一个简单型数据库星期一进行了完全数据库备份，星期二进行了差异备份，星期三进行了完全数据库备份，星期四进行了差异备份，星期五进行了差异备份，星期六出现故障。恢复策略应为：恢复星期三的完全数据库备份和星期五的差异备份3.数据库恢复使用企业管理器恢复6.4 SQL SERVER的安全性6.4.1 安全认证方式SQL SERVER 2000可以通过两种方式来进行身份验证：Windows身份验证、 SQL SERVER身份验证。Windows身份验证：由Windows系统确认用户的登录帐号和密码， Windows系统的登录帐号可以直接访问SQL SERVER系统，不必提供SQL SERVER的登录帐号和密码。SQL SERVER身份验证：由SQL SERVER系统确认用户的登录帐号和口令。相应的，SQL SERVER 2000可以在两种安全模式（身份验证）下工作：1.Windows身份验证模式：用Windows用户帐号进行连接2.混合模式：用Windows身份验证或SQL SERVER身份验证与SQL SERVER实例连接。6.4.2 登录管理1 登录帐号的概念用来和SQL SERVER连接。有了登录号才能连接上SQL SERVER，才有使用SQL SERVER的入门资格，但登录帐号没有使用数据库对象的权力。为了要存取SQL SERVER内某一数据库内的数据库对象，每一登录帐号必须在该数据库对应一个用户名。2.登录的管理的两种方法：使用企业管理器 (1)选择要建立登录的SQL SERVER服务器(2)展开安全性，选择登录这种办法既可以创建登录帐号还可以删