第2章-创建和管理数据库课件.ppt

1、第第2章章 创建和管理数据库创建和管理数据库创建数据库创建数据库创建文件组创建文件组管理数据库管理数据库分离和附加数据库分离和附加数据库数据结构介绍数据结构介绍推荐操作推荐操作创建数据库创建数据库定义数据库定义数据库事务日志的工作过程事务日志的工作过程设置数据库选项设置数据库选项检索数据库信息检索数据库信息2.1 创建数据库创建数据库 定义数据库定义数据库定义一个数据库定义一个数据库数据库的名字数据库的大小数据库所在的文件定义数据库的两种方法定义数据库的两种方法 SQL Server 企业管理器在查询分析器中输入 CREATE DATABASE 语句 2.1.1 定义数据库定义数据库 定义数据

2、库（续）定义数据库（续）2.1.1 定义数据库定义数据库CREATE DATABASE SampleON PRIMARY(NAME=SampleData,FILENAME=c:Program Files.DataSample.mdf,SIZE=10MB,MAXSIZE=15MB,FILEGROWTH=20%)LOG ON (NAME=SampleLog,FILENAME=c:Program Files.DataSample.ldf,SIZE=3MB,MAXSIZE=5MB,FILEGROWTH=1MB)COLLATE Chinese_PRC_CI_AS事务日志的工作过程事务日志的工作过程 应用

3、程序发出数据修改应用程序发出数据修改请求请求 磁磁 盘盘修改记录在磁盘上的事务修改记录在磁盘上的事务日志日志 在缓存中定位数据页，在缓存中定位数据页，或者将数据页读入缓存，或者将数据页读入缓存，并修改并修改 缓缓 存存磁盘磁盘检查点将已提交的检查点将已提交的事务写入数据库中事务写入数据库中 2.1.2 事务日志的工作过程事务日志的工作过程事务日志的工作过程（续）事务日志的工作过程（续）数据库恢复的三个阶段数据库恢复的三个阶段分析、恢复、撤销2.1.2 事务日志的工作过程事务日志的工作过程设置数据库选项设置数据库选项设置数据库选项的两种方式设置数据库选项的两种方式 企业管理器 ALTER DAT

4、ABASE 语句一共有五类数据库选项一共有五类数据库选项 自动选项AUTO_CREATE_STATISTICS,AUTO_UPDATE_STATISTICS 游标选项CURSOR_CLOSE_ON_COMMIT,URSOR_DEFAULT LOCAL|GLOBAL 恢复选项RECOVERY FULL|BULK_LOGGED|SIMPLE,TORN_PAGE_DETECTION SQL选项ANSI_NULL_DEFAULT,ANSI_NULLS 状态选项 READ_ONLY|READ_WRITE,SINGLE_USER|RESTRICTED_USER|MULTI_USER2.1.3 设置数据库选

5、项设置数据库选项检索数据库信息检索数据库信息检索数据库信息的两种方法检索数据库信息的两种方法使用 DATABASEPROPERTYEX 函数来查看数据库的属性SELECT DATABASEPROPERTYEX（数据库,属性）使用系统存储过程来显示数据库以及数据库参数的信息sp_helpdbsp_helpdb 数据库名sp_spaceused 对象名2.1.4 检索数据库信息检索数据库信息第第2章章 创建和管理数据库创建和管理数据库创建数据库创建数据库创建文件组创建文件组管理数据库管理数据库分离和附加数据库分离和附加数据库数据结构介绍数据结构介绍推荐操作推荐操作创建文件组创建文件组2.2 创建文

6、件组创建文件组l文件组的概念文件组的概念l文件组是 SQL Server 中一个或多个文件的命名集合，它构成分配或用于数据库管理的单个单元l文件组的使用场合文件组的使用场合l当有多个磁盘，希望把文件分布在这些磁盘上以提高性能l文件组的类型文件组的类型l两种文件组：主文件组和用户定义文件组l包含主文件的文件组为主文件组l用户定义的文件组是指在首次创建或以后更改数据库时，用户明确创建的任何文件组创建文件组（续）创建文件组（续）2.2 创建文件组创建文件组l指定默认的文件组指定默认的文件组l数据库建立时的默认文件组为主文件组l未指定文件组的对象都将放到默认文件组中l调整默认的主文件组的大小调整默认的

7、主文件组的大小l若主文件组没有空间，就不能再往系统表中写入任何信息l查看文件组信息：使用函数和存储过程查看文件组信息：使用函数和存储过程l函数：FILE_NAME，FILE_ID，FILE_PROPERTY，FILEGROUP_NAME，FILEGROUP_ID 和 FILEGROUP_PROPERTYl系统存储过程第第2章章 创建和管理数据库创建和管理数据库创建数据库创建数据库创建文件组创建文件组管理数据库管理数据库分离和附加数据库分离和附加数据库数据结构介绍数据结构介绍推荐操作推荐操作管理数据库管理数据库管理数据文件和日志文件的增长管理数据文件和日志文件的增长监视和扩展事务日志监视和扩展事

8、务日志 收缩数据库或文件收缩数据库或文件2.3 管理数据库管理数据库管理数据文件和日志文件的增长管理数据文件和日志文件的增长ALTER DATABASE Sample MODIFY FILE(NAME=SampleLog,SIZE=15MB)GOALTER DATABASE SampleADD FILE (NAME=SampleData2,FILENAME=c:Program Files.DataSample2.ndf,SIZE=15MB,MAXSIZE=20MB)GO使文件自动增长使文件自动增长手动扩充数据库文件手动扩充数据库文件添加次要数据库文件添加次要数据库文件2.3.1 管理数据管理数

9、据文件文件和日志文件的增长和日志文件的增长监视和扩展事务日志监视和扩展事务日志监视日志监视日志 企业管理器 DBCC SQLPERF（LOGSPACE）语句 Windows 2000 系统监视器产生大量日志操作的情况产生大量日志操作的情况 往有索引的表中装入大量数据 大事务 进行日志记录的文本或图像操作在需要时扩展日志在需要时扩展日志 企业管理器或者 ALTER DATABASE 语句2.3.2 监视和扩展事务日志监视和扩展事务日志收缩数据库或文件收缩数据库或文件收缩整个数据库收缩整个数据库 企业管理器 DBCC SHRINKDATABASE 语句DBCC SHRINKDATABASE(数据库

10、名,目标百分比,NOTRUNCATE|TRUNCATEONLY)收缩数据库中的一个数据文件收缩数据库中的一个数据文件 企业管理器 DBCC SHRINKFILE 语句DBCC SHRINKFILE(文件名|文件id,目标大小,EMPTYFILE|NOTRUNCATE|TRUNCATEONLY)自动收缩数据库自动收缩数据库 设置数据库选项 autoshrink 为 true2.3.3 收缩数据库或文件收缩数据库或文件收缩数据库或文件（续）收缩数据库或文件（续）收缩语句中的选项收缩语句中的选项 因为 DBCC SHRINKDATABASE 逐个文件地收缩数据库，所以实际收缩所使用的机理与 DBCC

11、 SHRINKFILE 的相同 NOTRUNCATE 选项导致在数据库文件中保留所释放的文件空间。SQL Server 只通过将数据移动到文件的前面压缩数据。默认情况是所有释放的文件空间返回给操作系统 TRUNCATEONLY 选项导致文件中的所有未使用的空间释放给操作系统。不尝试将其重新分配到未分配页。当使用TRUNCATEONLY 时，参数 target_size 和 target_percent 被忽略 EMPTYFILE 选项，只适用于 DBCC SHRINKFILE，将清空数据文件的内容并将数据移动到同一文件组中的其他文件2.3.3 收缩数据库或文件收缩数据库或文件第第2章章 创建和

12、管理数据库创建和管理数据库创建数据库创建数据库创建文件组创建文件组管理数据库管理数据库分离和附加数据库分离和附加数据库数据结构介绍数据结构介绍推荐操作推荐操作分离数据库分离数据库附加数据库附加数据库分离和附加数据库分离和附加数据库2.4 分离和附加数据库分离和附加数据库分离和附加数据库分离和附加数据库SQL Server 2000 允许分离数据库的数据和事务日志允许分离数据库的数据和事务日志文件，然后将其重新附加到另一台服务器，甚至同文件，然后将其重新附加到另一台服务器，甚至同一台服务器上一台服务器上分离数据库分离数据库 分离数据库将从 SQL Server 删除数据库，但是保持组成该数据库的

13、数据和事务日志文件中的数据库完好无损 分离数据库语法：EXEC sp_detach_db 数据库名 分离数据库要求没有用户正在使用数据库，数据库中没有未完成的事务，并且在内存中没有这个数据库的脏页 一旦数据库被分离，其条目就从 master 数据库的 sysdatabases 表中被移除。从 SQL Server 的角度看，数据库似乎被删除了但是其物理文件仍然保存在磁盘上2.4 分离和附加数据库分离和附加数据库分离和附加数据库（续）分离和附加数据库（续）附加数据库附加数据库 当附加在数据库上时，必须指定主数据文件的名称和物理位置 附加数据库语法EXEC sp_attach_db 数据库名=数据

14、库名 文件名=文件名16 只需要指定当前的文件名，无论当前的名字和从数据库分离时的名字是否相同。亦可以指定不同于原来数据库名字的新的数据库名字。只有当文件位置和在主文件首存储的文件原始位置不同的时候，才需要指定文件名 若附加的文件超过了16个，可使用 CREATE DATABASE 语句并指定 for attach 选项2.4 分离和附加数据库分离和附加数据库删除数据库删除数据库删除数据库的方法删除数据库的方法 企业管理器 DROP DATABASE 语句DROP DATABASE 数据库名,n 可以一次删除多个数据库删除数据库的限制删除数据库的限制不能删除：正在被还原的数据库 任何用户因为读

15、或写而打开的数据库 正在发布它的任何一张表作为 SQL Server 复制组成部分的数据库 系统数据库2.4.1删除数据库删除数据库第第2章章 创建和管理数据库创建和管理数据库创建数据库创建数据库创建文件组创建文件组管理数据库管理数据库分离和附加数据库分离和附加数据库数据结构介绍数据结构介绍推荐操作推荐操作数据结构介绍数据结构介绍数据存储方式数据存储方式页和扩展盘区类型页和扩展盘区类型管理文件空间的页管理文件空间的页跟踪表和索引的页跟踪表和索引的页2.5数据结构介绍数据结构介绍数据存储方式数据存储方式扩展盘区(8个连续的 8KB页)页(8 KB)表，索引 数据每行大小最多 8 060 字节数据

16、数据(文件文件).mdf 或 .ndf 日志日志(文件文件).Idf 2.5.1 数据存储方式数据存储方式 页和扩展盘区类型页和扩展盘区类型l页的类型页的类型l跟踪空间分配的页l 包含了紧密压缩的信息，这样 SQL Server 就能有效地将其保留在内存中以简化跟踪l包含用户和索引数据的页l八种页类型l 数据页、文本/图像页、索引页、页可用空间（PFS）页、全局分配映射表（GAM 和 SGAM）页、索引分配映射表（IAM）页、大容量更改映射表页和差异更改映射表页2.5.2 页和扩展盘区类型页和扩展盘区类型页和扩展盘区类型（续）页和扩展盘区类型（续）统一扩展盘区统一扩展盘区空闲空间空闲空间混合扩

17、展盘区混合扩展盘区 n扩展盘区的类型扩展盘区的类型l混合扩展盘区：包含 2 个或多个对象的页的扩展盘区称为“混合扩展盘区”。每张表起始于一个混合扩展盘区。主要为跟踪空间的页及包含小对象的页使用混合扩展盘区l统一扩展盘区：将所有八页分配给单个对象的扩展盘区称为“统一扩展盘区”。在表或索引需要超过 64 KB空间时使用2.5.2 页和扩展盘区类型页和扩展盘区类型管理文件空间的页管理文件空间的页 2.5.3 管理文件空间的页管理文件空间的页l每个文件的第一个扩展盘区是混合扩展盘区，包每个文件的第一个扩展盘区是混合扩展盘区，包含一个文件的页首页和紧接着的三个分配页含一个文件的页首页和紧接着的三个分配页

18、l 文件的页首页：文件的页首页：包含该文件的特性信息l PFSPFS（页可用空间）页：页可用空间）页：包含有关文件中页可用空间信息的分配页l 每个 PFS 页可以跟踪 8 000 个连续页，将近 64 MB 的数据。PFS 页对每一页都有一个相应的字节，跟踪：l 该页是否已分配l 该页是在混合扩展盘区还是在统一扩展盘区l 该页可用空间的近似数量管理文件空间的页（续）管理文件空间的页（续）2.5.3 管理文件空间的页管理文件空间的页lGAM（全局分配映射表全局分配映射表）和和 SGAM（共享全局分配映射表共享全局分配映射表）页页lSQL Server 使用 GAM 和 SGAM 页来确定未使用的

19、扩展盘区或有未使用页的混合扩展盘区的位置lGAM 和 SGAM 配合使用，每对管理 63 904 个扩展盘区第第2章章 创建和管理数据库创建和管理数据库创建数据库创建数据库创建文件组创建文件组管理数据库管理数据库分离和附加数据库分离和附加数据库数据结构介绍数据结构介绍推荐操作推荐操作推荐操作推荐操作指定文件的最大大小指定文件的最大大小更改默认文件组更改默认文件组备份备份 Master 数据库数据库指定大的自动增长增量指定大的自动增长增量2.2.6 6 推荐操作推荐操作30分钟分钟n目标目标l创建数据库l管理数据库的增长l改变数据库选项以控制事务日志的清除频率n练习练习1 创建创建 ClassN

20、orthwind 数据库数据库n练习练习2 管理管理 ClassNorthwind 事务日志文件的增长事务日志文件的增长n练习练习3 设置数据库恢复模型设置数据库恢复模型实验实验 创建和管理数据库创建和管理数据库回顾回顾学习完本章后，将能够：学习完本章后，将能够：创建数据库创建数据库创建文件组创建文件组管理数据库管理数据库描述数据结构描述数据结构随堂练习随堂练习1在公司人事管理数据库中使用表在公司人事管理数据库中使用表Employee记录员工信息，表记录员工信息，表EmployeeDemographics记记录统计信息。公司大约有录统计信息。公司大约有2000名员工，员名员工，员工数量的变化率

21、大约在工数量的变化率大约在2%。员工离职后，。员工离职后，记录会保留。统计信息每年变化记录会保留。统计信息每年变化9%。下面。下面是数据库的逻辑设计和脚本设计：是数据库的逻辑设计和脚本设计：随堂练习随堂练习1（续）（续）ALTER TABLE dbo.Employee WITH NOCHECK ADDCONSTRAINT pk_Employee PRIMARY KEY CLUSTERED(EmployeeID)WITH FILLFACTOR=90GOALTER TABLE dbo.EmployeeDemographics WITH NOCHECK ADDCONSTRAINT pk_Employ

22、eeDemographics PRIMARY KEY CLUSTERED(EmployeeID)WITH FILLFACTOR=90GO假设所有的假设所有的Varchar字段只用到字段只用到50%，请问创建时，请问创建时SIZE和和FILEGROWTH分别为多少？分别为多少？随堂练习随堂练习1（续）（续）SIZE=1GBSIZE=1MBSIZE=2048KFILEGROWTH=20FILEGROWTH=5%FILEGROWTH=0随堂练习随堂练习2使用脚本使用脚本SQL创建一个名为创建一个名为Employees的数据库。该的数据库。该数据库需要经常更新，数据库的大小为数据库需要经常更新，数据库

23、的大小为1G，假设，假设C盘有盘有10G的空间。下面哪段的空间。下面哪段SQL脚本语言正确？脚本语言正确？CREATE DATABASE Employees ON(NAME=Employees_dat,FILENAME=C:DATAEmployees.mdf,SIZE=10,MAXSIZE=1GB,FILEGROWTH=5)CREATE DATABASE Employees ON(NAME=Employees_dat,FILENAME=C:DATAEmployees.mdf,SIZE=10,MAXSIZE=1GB,FILEGROWTH=10%)随堂练习随堂练习2（续）（续）CREATE DAT

24、ABASE Employees ON(NAME=Employees_dat,FILENAME=C:DATAEmployees.mdf,SIZE=100,MAXSIZE=1GB,Maxsize=UNLIMITED)CREATE DATABASE Employees ON(NAME=Employees_dat,FILENAME=C:DATAEmployees.mdf,SIZE=1GB)随堂练习随堂练习3在构建一个在构建一个SQL Server 2000的网上数据库时，使用的网上数据库时，使用两个两个RAID阵列来放置数据文件，所有经常访问的阵列来放置数据文件，所有经常访问的表至少有一列有索引，应当

25、采用何种文件放置策表至少有一列有索引，应当采用何种文件放置策略来提高系统的略来提高系统的I/O性能？性能？将经常连接的表放在同一个文件组中将经常连接的表放在同一个文件组中将访问频率高的表及其索引放置在不同的文件将访问频率高的表及其索引放置在不同的文件组中组中 将经常连接的表放在不同一个文件组中将经常连接的表放在不同一个文件组中将访问频率高的表及其索引放置在不同的文件将访问频率高的表及其索引放置在不同的文件组中组中 将经常连接的表放在同一个文件组中将经常连接的表放在同一个文件组中将访问频率高的表及其索引放置在不同的文件将访问频率高的表及其索引放置在不同的文件组中组中将经常连接的表放在不同的文件组

26、中将经常连接的表放在不同的文件组中将访问频率高的表及其索引放置在同一个文件将访问频率高的表及其索引放置在同一个文件组中组中随堂练习随堂练习4在构建一个在构建一个SQL Server 2000的采购系统数据库时，的采购系统数据库时，服务器有两个磁盘控制器，一个控制器上连有两服务器有两个磁盘控制器，一个控制器上连有两个硬盘。数据库中的两张数据表个硬盘。数据库中的两张数据表Order和和OrderDetail经常需要连接查询。应采用什么方法来经常需要连接查询。应采用什么方法来提高数据库的性能？（选择两个选项）提高数据库的性能？（选择两个选项）为四个磁盘上分别创建文件组为四个磁盘上分别创建文件组将将O

27、rder表上创建索引表上创建索引将将OrderDetail表及其索引放置在一个文件组中表及其索引放置在一个文件组中为两张表分别创建一个文件组为两张表分别创建一个文件组将两个文件组分别放置在连接在一个磁盘控将两个文件组分别放置在连接在一个磁盘控制器的磁盘上制器的磁盘上A.交叉放置两个文件组交叉放置两个文件组随堂练习随堂练习5使用下列哪一个选项能够最快速地删除数使用下列哪一个选项能够最快速地删除数据表中的数据？据表中的数据？使用使用DELETE语句删除表中的数据语句删除表中的数据使用使用TRUNCATE TABLE语句删除表中的语句删除表中的数据数据使用使用DROP TABLE语句删除表中的数据语

28、句删除表中的数据A.使用可更新的游标访问并删除表中的使用可更新的游标访问并删除表中的所有记录所有记录随堂练习随堂练习6某个数据库中的数据表某个数据库中的数据表Sales由于新添加了由于新添加了20000多条数据，数据库的性能大大下降多条数据，数据库的性能大大下降了，为了提高对了，为了提高对Sales表查询的性能，应当表查询的性能，应当在在SQL查询分析器中运行哪段脚本查询分析器中运行哪段脚本?EXEC sp_updatestats resampleCREATE STATISITCS Sales WITH FULLSCANSp_autostaits SalesA.UPDATE STATISITCS Sales WITH FULLSCAN ALL