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

1、1第六章 SQL Server 数据库管理系统的高级应用主要知识点：数据库和表；约束；默认；规则；SQL Server 的备份和恢复；SQL Server的安全性重 点：数据库和表；约束难 点：约束教学学时 ：理论 6 学时，实践 6 学时教学方式 ：讲授教学内容：6.1 数据库和表6.1.1 数据库存储结构1数据库文件和文件组SQL Server 2000 用文件来存储数据库，数据库文件有三类： 主数据文件（Primary）：存放数据。每个数据库都必须有且仅有一个主数据文件。以.mdf 为默认扩展名。包含的系统表格记载数据库中对象及其他文件的位置信息。 次要数据文件（Secondary）：存

2、放数据。以.ndf 为默认扩展名。可有可无。主要在一个数据库跨多个硬盘驱动器时使用。 事务日志文件（Transaction Log）：存放事务日志。每个数据库必须有一个或多个日志文件。以.ldf 为默认扩展名。记录数据库中已发生的所有修改和执行每次修改的事务。注意：每个数据库的：主数据库文件数=1；次要数据文件数=0；事务日志文件数=1文件允许多个数据库文件组成一个组，即文件组，是文件的逻辑集合，SQL Server 2000 通过对文件进行分组，以便于管理数据的分配或配置。文件组对组内的所有文件都使用按比例填充策略。SQL Server 2000 有三种类型的文件组：主文件组（primary

3、 ） ；用户定义的文件组；默认的文件组（default） 。2注意：这里默认的文件组用来存放任何没有指定文件组的对象；主文件组包含主数据文件，存放系统表格等；事务日志文件不能属于文件组；SQL Server2000 至少包含一个文件组，即主文件组。2系统数据库SQL Server 2000 内部创建和提供了一组数据库，有 4 个系统数据库（master、msdb、model 、 tempdb）和两个附带的示例数据库（pubs、northwind ） 。master 数据库：记录了所有系统信息，包括所有的其他数据库、登录帐号和系统配置。是最主要的系统数据库。msdb 数据库：是 SQL Serv

4、er Agent 服务使用的数据库，用来执行预定的任务，如数据库备份和数据转换、警报和作业等。model 数据库：样板数据库。为用户数据库提供样板。tempdb 数据库：也是从 model 拷贝而来。存储了 SQL Server 实例运行期间 SQL Server 需要的所有临时数据。pubs 和 northwind 数据库：是两个用户数据库，系统附带的，可以删除，也可以恢复。其中 pubs 示例数据库以一个图书出版公司为模型， northwind 示例数据库以一个名为 Northwind Traders 的虚构公司为模型，存放了一些公司的销售数据，该公司从事世界各地的特产食品进出口贸易。示例

5、数据库的恢复：可以使用 SQL Server 安装中 Install 目录下的文件重新进行安装恢复。3数据库文件的空间分配在创建数据库前需估算所建数据库的大小及增幅。定义一个恰当的数据库大小。计算依据：数据库的最小尺寸必须等于或大于 model 数据库的大小。估算数据库的大小，在 SQL Server 2000 中最基本的数据存储单元是页，每页的大小为 8KB（8192 字节） ，每页除去 96 字节的头部（用来存储有关的页信息，如页类型、可用空间、拥有页的对象的对象 ID 等） ，剩下的 8096 字节（8192-96=8096）用来存储数据。3默认情况下事务日志文件的大小是数据库文件大小的

6、 25%。SQL Server 2000 数据库的数据文件中的八种页类型： 数据页：存储数据库数据，包含数据行中除 text、ntext 和 image 数据外的所有数据。 索引页：用于存储索引数据。 文本/图像页：用于存储 text、ntext 和 image 数据。 全局分配页：用于存储扩展盘区分配的信息。 页面剩余空间页：用于存储页剩余空间的信息。 索引分配页：用于存储页被表或索引使用的扩展盘区信息。 大容量更改映射表：有关自上次执行 BACKUP LOG 语句后大容量操作所修改的扩展盘区的信息。 差异更改映射表：自上次执行 BACKUP DATABASE 语句后更改的扩展盘区的信息。数

7、据页包含数据行中除 text、ntext 和 image 数据外的所有数据， text、ntext 和image 数据存储在单独的页中。在数据页上，数据行紧接着页首按顺序放置。在页尾有一个行偏移表。在行偏移表中，页上的每一行都有一个条目，每个条目记录那一行的第一个字节与页首的距离。行偏移表中的条目序列与页中行的序列相反。扩展盘区是一种基本单元，可将其中的空间分配给表和索引。一个扩展盘区是 8 个邻接的页（或 64KB） 。这意味着 SQL Server 2000 数据库每兆字节有 16 个扩展盘区。例：假设某个数据库中只有一个表，该表的每行记录是 500 字节，共有 10000 行数据。试估计

8、此数据库的大小。分析：由于一个数据页最多可存放 8096 字节的数据，按行顺序存放，可知这时，一个数据页上最多只能容纳的行数是：809650016（行） 。此表共有 10000 行，那么该表将占用的页数是：1000016=625（页） 。因此该数据库的大小估计为（6258KB）1024 5MB 。4数据库规划数据库规划的过程包括： 确定系统的范围；4 确定开发工作所需的资源（人员、硬件和软件） ； 估计软件开发的成本； 确定项目进度。6.1.2 数据库的创建、修改和删除1创建数据库（1）用创建数据库向导创建数据库（操作演示）（2）用企业管理器创建数据库（操作演示）（3）用 T-SQL 语句创建

9、数据库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 da

10、tabase studenton(name=student_dat,filename=d:student_dat.mdf,size=2MB,filegrowth=10% )log on (name=student_log,filename=d:studnet_log.ldf,5size=1MB,filegrowth=10%)2修改数据库可以使用不同的方法来修改数据库。（1）在企业管理器中修改数据库（操作演示）（2）用 T-SQL 语句修改数据库ALTER DATABASE 数据库名ADD FILE 数据文件定义 TO FILEGROUP ADD LOG FILE 事务日志文件定义REMOVE

11、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）查看数据库定义信息使用系

12、统存储过程 sp_helpdb 查看数据库或所有数据库的信息。6语法格式：sp_helpdb 数据库名称。如果不指定数据库名称，将返回所有数据库的信息。（2）查看数据库数据、日志空间使用 sp_spaceused 查看数据库数据空间。语法格式：sp_spaceused 数据库对象2数据库的分离与附加（1）分离数据库 点击“程序Microsoft SQL Server企业管理器” ，打开企业管理器；展开服务器组，然后展开服务器，选中要分离的数据库；点击鼠标右键“所有任务分离数据库” ；点击确定，该选定的数据库就被分离。应注重，只有“使用本数据库的连接”数为 0 时，该数据库才能分离。所以分离数据

13、库时尽量断开所有对要分离数据库操作的连接，假如还有连接数据库的程序，会出现数据库的连接状态窗口，显示正在连接此数据库的机器以及名称，点击清除按钮将从服务器强制断开现有的连接。 （2）附加数据库 复制移动数据库文件在附加数据库之前，您必须将与数据库关联的 .MDF（主数据文件）.LDF （事务日志文件） 这两个文件复制到目标服务器上，或是同一服务器的同一文件目录下。这两个文件一般位于 C：Program FilesMicrosoft SQL ServerMSSQLData 目录下。 点击“程序Microsoft SQL Server企业管理器 ”，打开企业管理器 展开服务器组，然后展开服务器 右

14、击“数据库“，然后选择“所有任务附加数据库” ，弹出窗口7单击“确定“按钮。新附加的数据库的数据库节点即创建在“数据库“文件夹中。 6.1.4 数据表的建立、修改和删除表是数据库中最主要的数据对象，用于存储和操作数据的一种逻辑结构。表存储在数据库文件中。SQL Server 2000 中有两类表： 永久表 在创建后，除非用户删除，否则将一直存储在数据库文件中。 临时表 存储在 tempdb 中，当不再使用时会自动删除。而临时表有有两类 本地临时表：名称以单个数字符号(#)打头；它们仅对当前的用户连接是可见的；当用户从 SQL Server 2000 实例断开连接时被删除。 全局临时表：名称以数

15、学符号(#)打头，创建后对任何用户都是可见的，当所有引用该表的用户从 SQL Server 断开连接时被删除。注意： 每个数据库最多可存储 20 亿个表，每个表至多可定义 1024 列，每行最多可以存储 8060 字节。 表和列的命名要遵守标识符的规定，在特定表中必须是唯一的，但同一数据库中的不同表可使用相同的列名。必须为每列指定数据类型。 尽管对于每一个所有者在一个数据库内表的名称必须是唯一的，但如果为每张表指定不同的所有者，则可以创建多个具有相同名称的表。当您要使用此表时，可以通过指定表的使用者以及表名称来区分两个表。1创建数据表（1）使用企业管理器创建表（2）使用 T-SQL 语句创建表

16、语法格式：CREATE TABLE 表名称（ 列定义, 表级完整性定义）ON 指定存储表的数据库文件组TEXTIMAGE_ON 说明 text、ntext、image 列数据的文件组名列定义格式：列名 列数据类型 列的约束例 6.1： student 数据库中创建表 stu（学号，姓名，年龄，课程，成绩） ，T-SQL语句如下：create table stu(学号 char(8) primary key, 8姓名 char(10) not null,年龄 tinyint null,课程 varchar(30) null, 成绩 numeric(4,1) default 0 )2修改数据表（1

17、） 在企业管理器中修改表（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

18、）FOREIGN KEY 约束；一、CHECK 约束CHECK 约束的作用：通过限制输入到一列或多列的值的范围，从而实现数据中的域完整性。即 CHECK 约束通过使用一个返回 TRUE 或 FALASE 的逻辑表达来检查存入字段中值是否符合指定的要求。如果逻辑表达式为 TURE，则数据值可以写入字段；否则，无法写入字段。注意：1）对于 TineStamp 和 Identity 两种数据类型不能定义 CHECK 约束；92）CHECK 约束的逻辑表达式中不能包含查询；1用企业管理器创建和删除 CHECK 约束（参见 实训指导书） 。2用 T-SQL 语句创建和删除 CHECK 约束（1）利用 S

19、QL 语句在创建表时创建 CHECK 约束创建 CHECK 约束的语法格式：CONSTRAINT 约束名 CHECK（逻辑表达式）例题 6.2 创建表成绩（学号，课程号，成绩）的同时定义 CHECK 约束要求成绩的范围为 0100。T-SQL 语句如下：CREATE TABLE 成绩(学号 char(8) NOT NULL，课程号 char(3) NOT NULL，成绩 tinyint NULLCONSTRAINT ck_cj CHECK（成绩=0 and 成绩=0 and 成绩=0 and cj=100二、绑定规则与默认对象一样，规则创建后，并不能直接使用，必须绑定到表中的某一列或用户16定

20、义数据类型上。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= 绑定的列名例

21、6.21：删除 XSCJ 表成绩列和规则对象 cj_rl 的绑定。use 学生成绩exec sp_unbindrule XSCJ.成绩四、 删除规则对象1使用企业管理器删除规则对象（参见实训指导书） 。2使用 T-SQL 语句 DROP RULE 删除规则对象语法格式： DROP RULE 规则对象名例 6.22：删除规则对象 cj_rl 。use 学生成绩goDROP DEFAULT cj_rl 注：删除规则对象时，首先要解除规则对象的绑定，然 后才能删除规则对象。6.3 SQL Server 的备份和恢复6.3.1 概述171.什么叫备份?就是指对 SQL Server 数据库或事务日志进

22、行复制,数据库备份记录在进行备份这一操作时数据库中所有数据的状态。如果数据库因意外而损坏,这些备份文件将在数据库恢复时被用来恢复数据库.2.什么叫恢复？就是把遭破坏或丢失数据或出现错误的数据库恢复到原来的正常状态.3.两者目的：尽最大可能以最快速度减少或消灭数据丢失.6.3.2 备份类型 数据库备份(完全备份) 事务日志备份 差异备份 文件和文件组备份1.什么叫数据库备份(完全备份)数据库备份指对数据库的完整备份,包括所有的数据以及数据库对象.特点:速度慢,占用空间大,最好在晚间进行采用条件:数据不是非常重要,容易恢复,变化频率不大2.事务日志备份指对数据库发生的事务进行备份,包括从上次进行事

23、务日志备份差异备份和数据库完全备份之后所有已经完成的事务.特点:速度快占用空间小,恢复复杂采用条件:完全备份后不允许发生数据丢失或损坏现象；存储备份文件的磁盘空间小；准备把数据库恢复到发生失败前一点3.差异备份指将最近一次数据库备份以来发生的数据变化备份起来,因此差异备份实际上是一种增量数据库备份.特点:数据量小,速度快18采用条件:降低丢失数据的风险,减小恢复的复杂性4.文件和文件组备份指对数据库文件或文件夹进行备份特点:恢复速度快,只需恢复受损的文件采用条件:只需恢复受损的文件6.3.3 备份和恢复的策略1. 备份方案1)通常我们按以下三个条件来决定备份方案：恢复能力、备份文件大小不一、备

24、份时间2)常用备份方案:仅仅进行数据库备份;或在进行数据库备份同时进行日志备份;或用完整数据库备份和差异备份3)在选择备份方案时必须认识以下两点:如果只进行数据库备份,那么将无法完全恢复当前的数据库如果进行数据库备份同时进行日志备份,那么可以数据库恢复到失败点,但失败前提交的事务将无法2.恢复类型 简单恢复：指在进行数据库恢复时仅使用了数据库备份或差异备份,而不涉及事务日志备份。策略(完全+差异) 完全恢复：指通过使用数据库备份和事务日志备份将数据库恢复。策略(完全+差异+日志)批日志恢复：在性能上要优于上面两种,能尽最大努力减少批操作所需要的存储空间。3.策略(完全+差异+日志)6.3.4

25、数据库备份1.步骤:1)创建数据库设备2)制定好备份策略3)检查要备份的数据库的一致性194)备份数据库2.备份设备1)概念用来存储数据库事务日志或文件和文件组备份的存储介质2)创建和删除 使用企业管理器创建和删除 使用系统存储过程管理备份设备创建语法格式：sp_addumpdevice 介质类型,备份设备逻辑名,备份设备实际物理路径删除语法格式：sp_dropdevice 备份设备逻辑名,备份设备实际物理路径例:创建一个逻辑名为 stuback,实际文件名为 c:backback1.baksp_addumpdevice disk,stuback, c:backback1.bak例:删除刚才创

26、建的备份设备sp_dropdevice stuback, c:backback1.bak3.备份先检查数据库一致性（dbcc checkdb）,制定好备份策略企业管理器备份注意：简单模型的数据库不能进行日志备份检查刚才备份设备的内容（restore headeronly from stuback）6.3.5 数据库恢复1.恢复工作步骤：1)对数据库进行完全性检查2)了解备份设备中的内容3)针对数据库恢复类型及备份内容选择不同的恢复策略2.恢复策略不同恢复类型的数据库采用不同的恢复方案，对于完全恢复模型的数据库恢复按如下次序进行：1)恢复最近的一次完全备份202)恢复最后一个差异备份（有的话）3

27、)从最后一个差异备份时刻后按时间的先后恢复4)每一个事务日志备份（有的话）对容量日志记录恢复模型恢复方法也是如此如：对一个简单型数据库星期一进行了完全数据库备份，星期二进行了差异备份，星期三进行了完全数据库备份，星期四进行了差异备份，星期五进行了差异备份，星期六出现故障。恢复策略应为：恢复星期三的完全数据库备份和星期五的差异备份3.数据库恢复使用企业管理器恢复6.4 SQL SERVER 的安全性6.4.1 安全认证方式SQL SERVER 2000 可以通过两种方式来进行身份验证：Windows 身份验证、 SQL SERVER 身份验证。Windows 身份验证：由 Windows 系统确

28、认用户的登录帐号和密码， 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

29、SERVER，才有使用 SQL SERVER 的入门资格，但登录帐号没有使用数据库对象的权力。为了要存取 SQL SERVER 内某一数据库内的数据库对象，每一登录帐号必须在该数据库对应一个用户名。2.登录的管理的两种方法：21 使用企业管理器(1)选择要建立登录的 SQL SERVER 服务器(2)展开安全性，选择登录这种办法既可以创建登录帐号还可以删除登录帐号及修改帐号6.4.3 数据库用户的管理1.数据库用户的概念数据库用户：简称用户，作为数据库对象，SQL SERVER 用它来设定数据库存取的许可权。2.数据库用户同登录帐号的联系及区别(1)登录帐号是用来连接 SQL SERVER 的

30、，但登录帐号没有使用数据库对象的权力(2)用户是数据库对象，定义和修改时必须选择对应的数据库，而登录不是数据库对象，它的定义和修改在 SQL SERVER 服务器下的安全性里进行。(3)用户的定义必须指定对应的登录名，一个登录名可以对应多个用户，但一个登录名在一个数据库内只能有一个用户。(4)用户名与数据库相关。sales 数据库中的 xyz 用户帐户不同于 inventory 数据库中的 xyz 用户帐户，即使这两个帐户有相同的用户名。用户名由 db_owner 固定数据库角色成员定义。3.数据库用户的管理使用企业管理器(1)选择要建立用户的 SQL SERVER 服务器(2)展开数据库，选

31、择用户注：在管理用户的时候必须选择对应的数据库 （use 要建立用户的数据库名）6.4.4 角色管理1.角色的概念及分类也称为安全性角色，对数据具有相同的访问权限，是数据库一个对象。包括系统内建角色和自建角色二类角色，其中，系统内建角色又分为服务器角色和数据库角色。2 登录、数据库用户、角色三者联系(1)服务器角色和登录名相对应；22(2)数据库角色是和用户对应的，数据库角色和用户都是数据库对象，定义和删除的时候必须选择所属的数据库(3)一个数据库角色中可以有多个用户，一个用户也可以属于多个数据库角色3.创建角色(1)先创建一个角色(2)对这个角色赋予权限(3)将这些用户添加到该角色中（使它们成为角色中的成员）4.角色分类角色分为系统内建角色（也就是固定角色）和自建角色，同时系统内建角色分为服务器角色和数据库角色，自建角色都是数据库角色。权限最大的固定服务器角色是 sysadmin，可在 SQL Server 中进行任何活动。该角色的权限跨越所有其它固定服务器角色权限最大的数据库角色 db_owner, 可进行所有数据库角色的活动，以及数据库中的其它维护和配置活动。该角色的权限跨越所有其它固定数据库角色。