创建和管理数据库.ppt
第2章 创建和管理数据库,创建数据库创建文件组管理数据库分离和附加数据库数据结构介绍推荐操作,创建数据库,定义数据库事务日志的工作过程设置数据库选项检索数据库信息,2.1 创建数据库,定义数据库,定义一个数据库数据库的名字数据库的大小数据库所在的文件定义数据库的两种方法SQL Server 企业管理器在查询分析器中输入 CREATE DATABASE 语句,定义数据库,定义数据库（续）,定义数据库,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,事务日志的工作过程,2.1.2 事务日志的工作过程,事务日志的工作过程（续）,数据库恢复的三个阶段分析、恢复、撤销,2.1.2 事务日志的工作过程,设置数据库选项,设置数据库选项的两种方式企业管理器ALTER DATABASE 语句一共有五类数据库选项自动选项AUTO_CREATE_STATISTICS,AUTO_UPDATE_STATISTICS游标选项CURSOR_CLOSE_ON_COMMIT,URSOR_DEFAULT LOCAL|GLOBAL恢复选项RECOVERY FULL|BULK_LOGGED|SIMPLE,TORN_PAGE_DETECTIONSQL选项ANSI_NULL_DEFAULT,ANSI_NULLS状态选项 READ_ONLY|READ_WRITE,SINGLE_USER|RESTRICTED_USER|MULTI_USER,2.1.3 设置数据库选项,检索数据库信息,检索数据库信息的两种方法使用 DATABASEPROPERTYEX 函数来查看数据库的属性SELECT DATABASEPROPERTYEX（数据库,属性）使用系统存储过程来显示数据库以及数据库参数的信息sp_helpdbsp_helpdb 数据库名sp_spaceused 对象名,2.1.4 检索数据库信息,第2章 创建和管理数据库,创建数据库创建文件组管理数据库分离和附加数据库数据结构介绍推荐操作,创建文件组,2.2 创建文件组,文件组的概念文件组是 SQL Server 中一个或多个文件的命名集合，它构成分配或用于数据库管理的单个单元文件组的使用场合当有多个磁盘，希望把文件分布在这些磁盘上以提高性能文件组的类型两种文件组：主文件组和用户定义文件组包含主文件的文件组为主文件组用户定义的文件组是指在首次创建或以后更改数据库时，用户明确创建的任何文件组,创建文件组（续）,2.2 创建文件组,指定默认的文件组数据库建立时的默认文件组为主文件组未指定文件组的对象都将放到默认文件组中调整默认的主文件组的大小若主文件组没有空间，就不能再往系统表中写入任何信息查看文件组信息：使用函数和存储过程函数：FILE_NAME，FILE_ID，FILE_PROPERTY，FILEGROUP_NAME，FILEGROUP_ID 和 FILEGROUP_PROPERTY系统存储过程,第2章 创建和管理数据库,创建数据库创建文件组管理数据库分离和附加数据库数据结构介绍推荐操作,管理数据库,管理数据文件和日志文件的增长监视和扩展事务日志 收缩数据库或文件,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 管理数据文件和日志文件的增长,监视和扩展事务日志,监视日志 企业管理器DBCC SQLPERF（LOGSPACE）语句Windows 2000 系统监视器产生大量日志操作的情况往有索引的表中装入大量数据大事务进行日志记录的文本或图像操作在需要时扩展日志企业管理器或者 ALTER DATABASE 语句,2.3.2 监视和扩展事务日志,收缩数据库或文件,收缩整个数据库企业管理器DBCC SHRINKDATABASE 语句DBCC SHRINKDATABASE(数据库名,目标百分比,NOTRUNCATE|TRUNCATEONLY)收缩数据库中的一个数据文件企业管理器DBCC SHRINKFILE 语句DBCC SHRINKFILE(文件名|文件id,目标大小,EMPTYFILE|NOTRUNCATE|TRUNCATEONLY)自动收缩数据库设置数据库选项 autoshrink 为 true,2.3.3 收缩数据库或文件,收缩数据库或文件（续）,收缩语句中的选项因为 DBCC SHRINKDATABASE 逐个文件地收缩数据库，所以实际收缩所使用的机理与 DBCC SHRINKFILE 的相同NOTRUNCATE 选项导致在数据库文件中保留所释放的文件空间。SQL Server 只通过将数据移动到文件的前面压缩数据。默认情况是所有释放的文件空间返回给操作系统TRUNCATEONLY 选项导致文件中的所有未使用的空间释放给操作系统。不尝试将其重新分配到未分配页。当使用TRUNCATEONLY 时，参数 target_size 和 target_percent 被忽略EMPTYFILE 选项，只适用于 DBCC SHRINKFILE，将清空数据文件的内容并将数据移动到同一文件组中的其他文件,2.3.3 收缩数据库或文件,第2章 创建和管理数据库,创建数据库创建文件组管理数据库分离和附加数据库数据结构介绍推荐操作,分离数据库附加数据库,分离和附加数据库,2.4 分离和附加数据库,分离和附加数据库,SQL Server 2000 允许分离数据库的数据和事务日志文件，然后将其重新附加到另一台服务器，甚至同一台服务器上分离数据库分离数据库将从 SQL Server 删除数据库，但是保持组成该数据库的数据和事务日志文件中的数据库完好无损分离数据库语法：EXEC sp_detach_db 数据库名分离数据库要求没有用户正在使用数据库，数据库中没有未完成的事务，并且在内存中没有这个数据库的脏页一旦数据库被分离，其条目就从 master 数据库的 sysdatabases 表中被移除。从 SQL Server 的角度看，数据库似乎被删除了但是其物理文件仍然保存在磁盘上,2.4 分离和附加数据库,分离和附加数据库（续）,附加数据库当附加在数据库上时，必须指定主数据文件的名称和物理位置附加数据库语法EXEC sp_attach_db 数据库名=数据库名 文件名=文件名16只需要指定当前的文件名，无论当前的名字和从数据库分离时的名字是否相同。亦可以指定不同于原来数据库名字的新的数据库名字。只有当文件位置和在主文件首存储的文件原始位置不同的时候，才需要指定文件名若附加的文件超过了16个，可使用 CREATE DATABASE 语句并指定 for attach 选项,2.4 分离和附加数据库,删除数据库,删除数据库的方法企业管理器DROP DATABASE 语句DROP DATABASE 数据库名,n 可以一次删除多个数据库删除数据库的限制不能删除：正在被还原的数据库任何用户因为读或写而打开的数据库正在发布它的任何一张表作为 SQLServer 复制组成部分的数据库系统数据库,2.4.1删除数据库,第2章 创建和管理数据库,创建数据库创建文件组管理数据库分离和附加数据库数据结构介绍推荐操作,数据结构介绍,数据存储方式页和扩展盘区类型管理文件空间的页跟踪表和索引的页,2.5数据结构介绍,数据库,数据存储方式,扩展盘区(8个连续的 8KB页),页(8 KB),表，索引,数据,每行大小最多 8 060 字节,数据(文件).mdf 或.ndf,日志(文件).Idf,2.5.1 数据存储方式,页和扩展盘区类型,页的类型跟踪空间分配的页 包含了紧密压缩的信息，这样 SQLServer 就能有效地将其保留在内存中以简化跟踪包含用户和索引数据的页八种页类型 数据页、文本/图像页、索引页、页可用空间（PFS）页、全局分配映射表（GAM 和 SGAM）页、索引分配映射表（IAM）页、大容量更改映射表页和差异更改映射表页,2.5.2 页和扩展盘区类型,页和扩展盘区类型（续）,统一扩展盘区,空闲空间,混合扩展盘区,扩展盘区的类型混合扩展盘区：包含 2 个或多个对象的页的扩展盘区称为“混合扩展盘区”。每张表起始于一个混合扩展盘区。主要为跟踪空间的页及包含小对象的页使用混合扩展盘区统一扩展盘区：将所有八页分配给单个对象的扩展盘区称为“统一扩展盘区”。在表或索引需要超过 64 KB空间时使用,2.5.2 页和扩展盘区类型,管理文件空间的页,2.5.3 管理文件空间的页,每个文件的第一个扩展盘区是混合扩展盘区，包含一个文件的页首页和紧接着的三个分配页文件的页首页：包含该文件的特性信息PFS（页可用空间）页：包含有关文件中页可用空间信息的分配页 每个 PFS 页可以跟踪 8 000 个连续页，将近 64 MB 的数据。PFS 页对每一页都有一个相应的字节，跟踪：该页是否已分配 该页是在混合扩展盘区还是在统一扩展盘区 该页可用空间的近似数量,管理文件空间的页（续）,2.5.3 管理文件空间的页,GAM（全局分配映射表）和 SGAM（共享全局分配映射表）页SQLServer 使用 GAM 和 SGAM 页来确定未使用的扩展盘区或有未使用页的混合扩展盘区的位置GAM 和 SGAM 配合使用，每对管理 63 904 个扩展盘区,第2章 创建和管理数据库,创建数据库创建文件组管理数据库分离和附加数据库数据结构介绍推荐操作,推荐操作,2.6 推荐操作,目标创建数据库管理数据库的增长改变数据库选项以控制事务日志的清除频率练习1 创建 ClassNorthwind 数据库练习2 管理 ClassNorthwind 事务日志文件的增长练习3 设置数据库恢复模型,实验 创建和管理数据库,回顾,学习完本章后，将能够：创建数据库创建文件组管理数据库描述数据结构,随堂练习1,在公司人事管理数据库中使用表Employee记录员工信息，表EmployeeDemographics记录统计信息。公司大约有2000名员工，员工数量的变化率大约在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_EmployeeDemographics PRIMARY KEY CLUSTERED(EmployeeID)WITH FILLFACTOR=90GO假设所有的Varchar字段只用到50%，请问创建时SIZE和FILEGROWTH分别为多少？,随堂练习1（续）,SIZE=1GBSIZE=1MBSIZE=2048KFILEGROWTH=20FILEGROWTH=5%FILEGROWTH=0,随堂练习2,使用脚本SQL创建一个名为Employees的数据库。该数据库需要经常更新，数据库的大小为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 DATABASE 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阵列来放置数据文件，所有经常访问的表至少有一列有索引，应当采用何种文件放置策略来提高系统的I/O性能？将经常连接的表放在同一个文件组中将访问频率高的表及其索引放置在不同的文件组中 将经常连接的表放在不同一个文件组中将访问频率高的表及其索引放置在不同的文件组中 将经常连接的表放在同一个文件组中将访问频率高的表及其索引放置在不同的文件组中将经常连接的表放在不同的文件组中将访问频率高的表及其索引放置在同一个文件组中,随堂练习4,在构建一个SQL Server 2000的采购系统数据库时，服务器有两个磁盘控制器，一个控制器上连有两个硬盘。数据库中的两张数据表Order和OrderDetail经常需要连接查询。应采用什么方法来提高数据库的性能？（选择两个选项）为四个磁盘上分别创建文件组将Order表上创建索引将OrderDetail表及其索引放置在一个文件组中为两张表分别创建一个文件组将两个文件组分别放置在连接在一个磁盘控制器的磁盘上交叉放置两个文件组,随堂练习5,使用下列哪一个选项能够最快速地删除数据表中的数据？使用DELETE语句删除表中的数据使用TRUNCATE TABLE语句删除表中的数据使用DROP TABLE语句删除表中的数据使用可更新的游标访问并删除表中的所有记录,随堂练习6,某个数据库中的数据表Sales由于新添加了20000多条数据，数据库的性能大大下降了，为了提高对Sales表查询的性能，应当在SQL查询分析器中运行哪段脚本?EXEC sp_updatestats resampleCREATE STATISITCS Sales WITH FULLSCANSp_autostaits SalesUPDATE STATISITCS Sales WITH FULLSCAN ALL,
