创建和管理数据库.ppt

1,第四章 创建与管理数据库,2,本章主要内容,3,4.1 数据库的逻辑结构,数据库是针对数据库对象的存储结构。 数据库的存储结构分为逻辑存储结构和物理存储结构两种。 逻辑存储结构:指的是数据库是由哪些性质的信息所组成 物理存储结构:讨论数据库文件是如何在磁盘上存储的,4,SQL Server数据库的逻辑结构,数据库不仅是数据的存储之处，所有与数据处理操作相关的信息都存放在数据库中 SQL Server数据库是由各种不同的对象所组成，这些不同的对象分别用来存储特定信息并支持特定功能。,5,数据库对象(Database Object),表(Tables) 视图(views) 约束(constraints) 规则(Rules) 默认(Defaults) 索引(Indexes) 索引视图(Indexed Views) 键(Keys) 用户定义的数据类型(User-Defined Data Types) 用户定义的函数(User-Defined Functions) 别名数据类型(Alias Data Types) 存储过程(Stored Procedures) 触发器(Triggers),6,SQL Server数据库的物理结构,几个概念 页 扩展盘区 数据库文件,7,页,页(Page)是SQL Server中存储数据的最小单位，每一个页的大小是8KB SQL Server的数据库每1MB有128个页 数据库中的每一页只存储来自某一个对象的数据 每一个页的前96个字节是页首,8,页,SQL Server数据库的数据文件中的页有8种类型 索引页:存储索引数据 文本图像页：text、ntext和image数据 全局分配映射表页与共享全局分配映射表页： 可用空间页 索引分配映射表页 大容量更改映射表页 差异更改映射表页,9,页,10,扩展盘区,扩展盘区(Extent)是SQL Server分配给表和索引的单位空间。 一个扩展盘区是由8个连续的页构成，大小是64KB，SQL Server的数据库每1MB有16个扩展盘区。,11,扩展盘区,SQL Server 2005的扩展盘区分为下列两种类型 统一扩展盘区：区只由某单一对象所拥有 混合扩展盘区：区最多可被8个对象共享，其8个页都可以被不同的对象所拥有,12,数据库文件,数据库中的数据对象、数据记录是以数据库文件为单位进行组织与管理的 数据库文件则是由数据文件和事务日志文件组成 一个数据库文件至少应该包含一个数据文件和一个事务日志文件 数据库是由下列三种类型的文件所构成. 主要数据文件(Primary Database File) 次要数据文件(Secondary Database File) 事务日志文件(Log File),13,主要数据文件,主要数据文件包含数据库的初始信息 每个数据库有且只有一个 主要数据文件的默认扩展名是MDF,14,次要数据文件,用来存储数据库中的数据记录等信息，用户可自行决定是否要使用次要数据文件，默认扩展名“NDF” 使用次要数据文件的主要原因： 通过在不同的物理磁盘上创建次要数据文件并将数据存储其中，可将数据横跨存储在多块物理磁盘上 数据非常庞大时，使得主要数据文件的大小已超过Windows单一文件大小的限制的话，可使用次要数据文件来帮助存储数据,15,事务日志文件,事务日志文件包含用来恢复数据库的日志信息 每一个数据库至少必须拥有一个日志文件 日志文件的大小至少是1MB 日志文件的默认扩展名是LDF 日志文件是用来记录数据库更新情况,16,数据库文件名,SQL Server 2005数据库文件有两个文件名：逻辑文件名和物理文件名 逻辑文件名 当在Transact-SQL命令语句中存取某一个文件时，必须使用该数据库文件的逻辑文件名 各个数据库的逻辑文件名绝不能相同,17,数据库文件名,物理文件名 物理文件名是指数据库文件实际存储在磁盘上的文件名称 区别： 逻辑文件名是在Transact-SQL语言中引用的，用于存取某一数据库文件的标识。 物理文件名则是在操作系统环境或用户程序中引用的数据库文件标识。,18,文件组,SQL Server允许将多个文件归纳为同一组并赋予该组一个名称，这就是“文件组”(Filegroup)。 文件组的作用： 有些系统可以借助于将数据和索引存诸在特定的物理磁盘上以提高效率，文件组正好能帮助用户或数据库管理员来完成该项操作。 系统管理员可以在每一个物理磁盘上创建文件组，然后将表、索引、或表中的大型对象教据指最给特定的文件组。 文件组使得用户能方便地将新文件添加到新的磁盘上。,19,文件组,设计文件与文件组时应遵循的原则： 一个文件或文件组只能被与其对应的惟一数据库所使用。 一个文件只能隶属于一个文件组，它不能同时隶属于两个或两个以上的文件组。 文件组只能包含数据文件。 每个数据库最多只能创建32767个文件组。 数据库快照集(Snapshots)不能位于文件组中。,20,文件组,SQL Server 2005文件组分为3种类型 主要文件组(Primary Filegroup) 用户定义文件组(User-Defined Filegroup) 默认文件组(Default Filegroup),21,文件组,文件、文件组及其创建与使用，要注意： 大多数的数据库只需要一个主要数据文件和一个日志文件就可以运行得非常好。 如果确实需要使用多个文件，请为次要数据文件等附加文件创建第二个文件组并将此文件组设置为默认文件组 要想获得最佳性能，应该尽量将各个文件与文件组分别存储在不同的物理磁盘上，并将需要占用大量空间的对象放置在不同的文件组中。 若需要将对象存储在特定的物理磁盘上，请使用文件组来完成。,22,4.2 建数据库,创建数据库的本质是为数据库设计名称、定义所需占用的存储空间和存放文件位置 数据库创建时的注意点： 只有具有DDL语句权限的用户才能创建数据库 创建数据库的用户将成为数据库的所有者(Owner) 最多可以在一个SQL实例上创建32767个数据库 系统数据库model是所有数据库的模板，因此当创建一个新的数据库时，会自动复制系统数据库model中的所有用户定义对象 创建一个新数据库后，需立即备份系统数据库master 每个数据库至少包含两个文件(主要文件和日志文件)以及一个默认文件组,23,使用DDL语句创建数据库,使用Transact-SQL语句创建数据库： CREATE DATABASE database_name ON PRIMARY ，n ， ，n LOG ON ，n WITH ;,24,创建数据库的语法中有关参数的定义： := （NAME=logical_file_name， FILENAME=os_file_name ，SIZE=size ，MAXSIZE=max_size|UNLIMITED ，FILEGROWTH=growth_increment ） ，n := FILEGROUP filegroup_name ，n,25,使用DDL语句创建数据库,示例：创建数据库名称为studentinfo，该数据库由一个20MB的数据文件和一个2MB的事务日志文件组成。 数据文件逻辑名称为studentinfo_data，物理文件名为studentinfo_data.mdf。主文件由primary指定，数据文件的最大容量分100MB，增长速度为10%。 事务日志文件的逻辑名为studentinfo_log，日志文件的物理文件名为studentinfo_log.ldf。最大容量为10MB，文件增长速度为10%。物理文件存储在D盘名称为sqldata的文件夹下名为mydb的目录中。,26,使用DDL语句创建数据库,CREATE DATABASE studentinfo ON( NAME=studentinfo_data, FILENAME=D:sqldatamydbstudentinfo_data.mdf, SIZE=10MB, MAXSIZE=100MB, FILEGROWTH=10% ),27,使用DDL语句创建数据库,LOG ON ( NAME=studentinfo_log, FILENAME=D:sqldatamydbstudentinfo_log.ldf, SIZE=1MB, MAXSIZE=10MB, FILEGROWTH=10% ) GO,28,使用DDL语句创建数据库,29,使用SSMS创建数据库,30,4.3 修改数据库,可以用ALTER DATABASE命令或SSMS工具来更改数据库。 只有数据库管理员(DBA)或具有CREATE DATABASE权限的数据库所有者才有权执行此命令。,31,使用T-SQL语句修改数据库,语法格式： ALTER DATABASE database_name (对数据库文件的添加、删除或修改) | (添加或修改文件组) | (修改数据库状态设置) | MODIFY NAME = new_database_name (更改数据库名称) | COLLATE collation_name (修改数据库排序规则) ;,32,使用T-SQL语句修改数据库,对数据库文件的添加、删除或修改 := ADD FILE ,.n TO FILEGROUP filegroup_name | DEFAULT | ADD LOG FILE ,.n | REMOVE FILE logical_file_name | MODIFY FILE ,33,使用T-SQL语句修改数据库,示例：添加一个2MB的数据文件到Studentinfo 数据库。 USE master; GO ALTER DATABASE studentinfo ADD FILE ( NAME = studentinfo_data2, FILENAME = D:sqldatamydbstudentinfo_data2.ndf, SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 1MB ); GO,34,使用T-SQL语句修改数据库,35,使用T-SQL语句修改数据库,示例：修改刚才添加的数据文件目录。 USE master; GO ALTER DATABASE studentinfo MODIFY FILE ( NAME = studentinfo_data2, FILENAME = D:sqldatastudentinfo_data2.ndf ); GO,36,使用T-SQL语句修改数据库,示例：删除以上示例中添加的次要数据文件studentinfo_data2.ndf。 USE master; GO ALTER DATABASE studentinfo REMOVE FILE studentinfo_data2 GO,37,使用T-SQL语句修改数据库,38,使用T-SQL语句修改数据库,添加或修改文件组 := | ADD FILEGROUP filegroup_name | REMOVE FILEGROUP filegroup_name | MODIFY FILEGROUP filegroup_name | DEFAULT | NAME = new_filegroup_name ,39,例：添加文件组，并向文件组中添加文件 ALTER DATABASE studentinfo ADD FILEGROUP fgroup GO ALTER DATABASE studentinfo ADD FILE ( NAME=studentinfo_data2, FILENAME=.studentinfo_data2.ndf, SIZE=10MB, FILEGROWTH=1MB ) TO FILEGROUP fgroup GO,40,使用T-SQL语句修改数据库,修改数据库状态设置 := SET ,.n WITH | ALLOW_SNAPSHOT_ISOLATION ON | OFF | READ_COMMITTED_SNAPSHOT ON | OFF WITH ,41,使用T-SQL语句修改数据库,更改数据库名称 MODIFY NAME = new_database_name 语句用于使用指定的名称 new_database_name 以重新命名数据库。 修改数据库排序规则 在修改数据库的语法中，项用于指定数据库的排序规则。,42,使用SSMS修改数据库属性,43,4.4 删除数据库,对于由于各种原因，已不再需要的数据库，可通过删除它们来释放所占用的磁盘空间。 删除数据库的操作，同样可以通过Transact-SQL命令语句或使用SSMS管理控制台来完成。,44,使用T-SQL语句删除数据库,删除数据库的命令亦属于DDL语句，在SQL Server 2005中可以使用Transact-SQL命令来实现，语法格式如下： DROP DATABASE database_name,n 可一次删除一个或多个数据库。,45,使用T-SQL语句删除数据库,示例：删除studentinfo数据库。 DROP DATABASE studentinfo 操作结果如下： - 命令已成功完成。,46,使用SSMS删除数据库,47,4.5 数据库的扩展与收缩,在SQL Server 2005中，数据库可以自动扩展，也可以手工扩展。 扩展的方法有两种：增加现有文件长度和增加新文件。 如同扩展数据库一样，SQL Server也可缩小数据库。,48,数据库的扩展与收缩,扩展或收缩数据库，可使用Transact-SQL语句来完成，也可以使用SQL Server Management Studio管理工具来实现。 使用T-SQL语句扩展数据库 其实是修改数据库的操作，语法格式与修改数据库的语法相同。,49,扩展数据库,例如，要将studentinfo数据库初始容量扩大到50MB，最大容量为200MB，同时，在另一磁盘上增加新的次要数据文件studentinfo1，Transact-SQL语句实现如下： USE MASTER GO ALTER DATABASE studentinfo MODIFY FILE ( NAME=studentinfo_data, SIZE=50MB, MAXSIZE=200MB ); GO,50,扩展数据库,ALTER DATABASE studentinfo ADD FILE( NAME=studentinfo1, FILENAME=E:downtempstudentinfo1_data.ndf, SIZE=2MB, MAXSIZE=5MB, FILEGROWTH=10% ); GO,51,扩展数据库,使用SQL Server Management Studio工具扩展数据库,52,收缩数据库,使用T-SQL语句缩小数据库、压缩数据库中的文件，语法格式如下： DBCC SHRINKDATABASE (database_name,target_percent ,NOTRUNCATE|TRUNCATEONLY) DBCC SHRINKFILE (file_name | file_id , target_size | , EMPTYFILE | NOTRUNCATE |TRUNCATEONLY ),53,收缩数据库,使用SQL Server Management Studio管理工具收缩数据库,54,4.6 附加和分离数据库,在SQL Server中创建一个新的数据库时，其实质是将该数据库附加(Attach)到该SQL Server数据库引擎上。 因此，可以将数据库从SQL Server分离(Detach)出来，使其所有的数据文件和日志文件独立存在。,55,附加和分离数据库,分离和附加数据库的主要目的是移动数据库的位置。 若将数据库从SQL Server中分离后，则可再将此数据库附加到其他的SQL Server上，或是附加到它原先的SQL Server上。,56,分离数据库,使用Transact-SQL语句分离数据库 语法格式如下： sp_detach_dbdbname=database_name ,skipchecks=skipchecks ,keepfulltextindexfile=kee