资源描述
项目 3-2 数据库的创建和管理 3-2.1 数据库 简介 3-2.2 估算数据库的空间需求 3-2.3 数据库的 创建 3-2.4 数据库的管理 3-2.5 设置 数据库选项 2 目标: 1. 掌握创建数据库的两种方法, 理解数据库 的逻辑文件名和物理文件名的含义 ; 2. 掌握数据库管理的常用操作:查看数据库 属性、修改数据库大小、收缩数据库、数据 库的更名和删除; 3. 掌握设置数据库选项的常用操作。 项目 3-2 数据库的创建和管理 3 3-2.1 数据库简介 数据库创建的过程就是数据库逻辑设计的物理实现过程。如果能根据 ER模型并结合单位的实际情况创建出一个性能优异、结构合理并具有 良好扩充性能的数据库,将会为后在数据库基础上开发应用程序奠定坚 实的基础,这是数据库管理最重要、最核心的问题。 数据库的创建主要包括数据库及其表、索引、视图、存储过程等对象的 创建。 创建数据库时,系统会建立数据库的存储结构。 数据库的存储结构分为逻辑存储结构和物理存储结构两种。 数据库的逻辑存储结构指的是数据库是由哪些性质的信息所组成 。 实际 上, SQL Server的数据库是由诸如表、视图、索引等各种不同的数据库 对象所组成。 4 数据库的物理存储结构是讨论数据库文件是如何在磁盘上存储的,数据 库在磁盘上是以文件为单位存储的,由 数据库文件和事务日志文件 组成, 一个数据库至少应该包含一个数据文件和一个事务日志文件 。 1主数据文件 ( Primary Database File) 一个数据库可以有一个或多个数据文件,一个数据文件只能属于一个数 据库。当有多个数据文件时, 有一个文件被定义为主数据文件(简称为 主文件),其扩展名为 mdf。 主数据文件用来存储数据库的启动信息以及部分或者全部数据,是所有 数据库文件的起点,包含指向其它数据库文件的指针。 一个数据库只能 有一个主数据文件。 2次数据文件 ( Secondary Database File) 用于存储主数据文件中未存储的剩余数据和数据库对象,一个数据库可 以没有次数据文件,但也可以同时拥有多个次数据文件。 次数据文件的扩展名为 ndf(简称为次文件)。 5 3-2.1.1 数据库的存储结构 1.盘区和页面 在创建数据库对象时 ,SQL Server会使用盘区和页面这种特殊的数据结 构给数据库对象分配空间。 在 SQL Server中,数据存储的基本单位是 页 。页的大小是 8KB,这样数 据库上每 MB是 128页。每页的开始部分是 96B的页首,用于存放系统信 息,如页的类型、页的可用空间、占据该页的数据库对象的对象标识 等。 页有数据页、索引页、文本 /图象页等 8种 。 盘区(区域)是连续 8个页,可防止数据产生碎片,使 SQL Server易于 寻找。每个盘区只能包含一个数据库对象。盘区是表和索引分配空间 的基本单位。 盘区是表和索引分配空间的基本单位 。 一个数据库是由文件组成的,文件是由盘区组成的,盘区是由页面组 成的。 6 2. 数据库的存储组织 数据库 数据文件 .mdf 或 .ndf 日志文件 .ldf 表、索引 盘区( 8个连续页面) 数据 页( 8KB) 每行大小 8060B 图 5-1 数据库的存储组织 7 注意: 所有的数据库都有唯一的主数据文件和一个或多个事务日 志文件,可能还有次数据文件; 数据行存放在数据页中,但数据页只能存放除 text、 ntext和 image类型数据外的所有数据, text、 ntext和 image类型数 据存储在单独的页中,行不能跨页存储,页中每一行最多 包含的数据量是 8060B; 数据存放在连续的页中; 日志文件不包含页,而是包含一系列日志记录 ; 所有新建的数据库都是 model数据库的副本。 8 3.文件组 文件组是文件的集合。对文件进行分组,便于进行数据的 管理和分配磁盘空间。例如有三个数据文件 data1.ndf,data2.ndf,data3.ndf,分别位于不同的磁盘上,将 这三个文件指派到文件组 filegroup1中。假设在文件组 filegroup1上创建一个表,对表中数据的查询将会分散到三 个磁盘上,从而提高系统的查询性能。 文件和文件组的设计规则 : 文件和文件组不能由一个以上的数据库使用 ; 文件只能是一个文件组的成员 ; 事务日志文件不属于任何一个文件组。 数据和事务日志不能属于同一文件或文件组。数据文件 和日志文件总是分开存放的。 9 主文件组中包含了所有的系统表 , 当建立数据库时 , 主 文件组包括主数据库文件和未指定组的其他文件 。 用户定义文件组中可以指定一个缺省文件组 , 那么在创 建数据库对象时如果没有指定将其放在哪一个文件组中 , 就会将它放在缺省文件组中 。 如果没有指定缺省文件组 , 则主文件组为缺省文件组 。 3-2.1.2 事务日志文件 SQL创建数据库的时候 ,会同时创建事务日志文件。 事务日志文件存储数据库的更新情况等事务日志信息 , 当 数据库损坏时,管理员使用事务日志恢复数据库。 每一个数据库至少必须拥有一个事务日志文件, 而且允许 拥有多个日志文件。 事务日志文件的扩展名为 ldf。 10 1. 事务日志文件和数据文件必须分开存放,优点如下: 事务日志文件可以单独备份; 有可能从服务器失效的事件中将服务器恢复到最近的状态; 事务日志不会抢占数据库的空间; 可以很容易地监测到事务日志的空间; 在向数据文件和事务日志文件写入数据时会产生较少的冲突, 这有利于提高 SQL Server的性能。 2. 事务日志的工作过程 在 SQL Server中,事务是一次完成的操作的集合。虽然一个事 务中可能包含了很多 SQL语句,但在处理上就象他们是同一个 操作似的。 为了维护数据的完整性,事务必须彻底完成或者根本不执行。 如果一个事务只是部分地被执行,并作用于数据库,那么数据 库将可能被损坏或数据的一致性遭到破坏。 11 SQL Server使用数据库的事务日志来防止没有完成的事务破坏 数据。 事务日志的工作过程 : 应用程序发出一个修改数据库中的对象的事务; 当这个事务开始时,事务日志会记录一个事务开始标志,并将被 影响的数据页从磁盘读入缓冲区; 事务中每个数据更改语句都被记录在日志文件中,日志文件将记 录一个提交事务的标记。每一个事务都会以这种方式记录在事务 日志中并被立即写到硬盘上。 在缓冲区中修改相应的数据。这些数据一直在缓冲区中,在检查 点进程发生时,检查点进程把所有修改过的数据页写到数据库中, 并在事务日志中写入一个检查点标志,这个标志用于在数据库恢 复过程中确定事务的起点和终点。以及哪些事务已经作用于数据 库 了。 12 随着数据库数据的不断变化,事务日志文件不断增大。因此,必须 把它们备份出来,为更多的事务提供空间。备份时,事务日志文件 会被截断。 事务日志文件包含有在系统故障下恢复数据库需要的所有信息。 一 般来说,事务日志文件的初始大小是以数据文件大小的 10%到 25% 为起点的,根据数据增长的情况和修改的频率进行调整。 关于文件名: SQL Server 2005的文件拥有两个名称,即逻辑文件名和物 理文件名。当使用 Transact-SQL命令语句访问某一个文件 时,必须使用该文件的逻辑名 。 物理文件名是文件实际存储在磁盘上的文件名,而且可包 含完整的磁盘目录路径。 回到首页 13 3-2.2 估算数据库的空间需求 作为数据库管理员,主要任务之一就是创建数据库,并且需 要为每个文件指定容量。必须尽可能准确地估算数据库容量, 以免浪费磁盘空间资源或者因估计不足造成数据库的空间不 够。 许多因素会影响数据库最终的大小, 在估算数据库容量时要 考虑如下因素: 每行记录的大小; 记录数量; 表的数量; 索引的数量及索引大小; 数据库对象的数量和大小; 事务日志的大小; 数据库的计划增加量; 回到首页 14 3-2.3 创建数据库 创建数据库的两种方法: 1.使用 SQL Server Management Studio创建数据库 2.使用 Transact-SQL语言创建数据库 创建数据库的过程实际上就是为数据库设计名称、设 计所占用的存储空间和存放文件位置以及相关特性的 过程。 新建数据库的信息放在系统数据库 master中,属于系 统级信息。 建议:创建新数据库时,数据文件逻辑名加 入 ” _data”后缀,形如: jwgl_data;事务日志文件逻 辑名加入 ” _log”后缀,形如: jwgl_log 。 15 3-2.3.1 创建数据库的注意事项 : 1. 创建数据库需要一定许可,在默认情况下,只有系统管 理员和被赋予权限的用户可以创建数据库。数据库被创 建后,创建数据库的用户自动成为该数据库的所有者。 2. 给数据库指定的名字遵循 SQL Server 命名规范。 3. 所有的新数据库都是系统样本数据库 model的副本。 4. 单个数据库可以存储在单个文件上,也可以跨越多个文 件存储。 5. 数据库的大小可以被增大或者收缩。 6. 当新的数据库创建时, SQL Server自动更新 master系 统数据库中的 “ sysdatabases”系统表。 7. 一台服务器上最多可能创建 32767个数据库。 16 3-2.3.2 使用 T-SQL语言创建数据库 说明:在 Transact-SQL语言的命令格式中, 用 括起来的内容表示是可选的; , n 表示重复前面的内容; 用 括起来表示在实际编写语句时,用相应的内容替代; 用 括起来表示是必选的; 类似 A|B的格式,表示 A和 B只能选择一个,不能同时都选。 1. 创建数据库的 SQL语句的语法格式 : CREATE DATABASE database_name ON PRIMARY , n , , n LOG ON , n 17 其中: = NAME=logical_file_name, FILENAME=os_file_name , SIZE=size , MAXSIZE=max_size|UNLIMITED , FILEGROWTH=growth_increment , n = FILEGROUP filegroup_name , n 各参数的含义 ( 如下 ) : database_name:新数据库的名称 。 数据库名称在服务器中必须唯一 , 最 长为 128个字符 , 并且要符合标识符的命名规则 。 ON:指定存放数据库的数据文件信息 。 列表用于定义主文件组 的数据文件 , 列表用于定义用户文件组及其中的文件 。 18 PRIMARY:用于指定主文件组中的文件 。 主文件组的第一 个由 指定的文件是主文件 。 如果不指定 PRIMARY 关键字 , 则在命令中列出的第一个文件将被默认为主文件 。 LOG ON:指明事务日志文件的明确定义 。 如果没有本选项 , 则系统会自动产生一个文件名前缀与数据库名相同 , 容量为 所有数据库文件大小 1/4的事务日志文件 。 NAME:指定数据库文件的逻辑名称 。 FILENAME:指定数据库文件的物理名称 , 即数据库所在文 件的操作系统文件名称和路径 , 该操作系统文件名和 NAME 的逻辑名称一一对应 。 19 SIZE: 指定数据库的初始容量大小 。 如果没有指定主文件 的大小 , 则 SQL Server默认其与模板数据库中的主文件大 小一致 , 其它数据库文件和事务日志文件则默认为 1MB。 指定大小的数字 size可以使用 KB、 MB、 GB和 TB后缀 , 默 认的后缀为 MB。 Size最小值为 1MB, 默认值为 1MB。 MAXSIZE: 指定操作系统文件可以增长到的最大尺寸 。 如 果没有指定 , 则文件可以不断增长直到充满磁盘 。 UNLIMITED: 指定文件可以增长到磁盘变满为止 。 FILEGROWTH: 指定文件每次增加容量的大小 , 当指定数 据为 0时 , 表示文件不增长 。 文件的增长有两种方式:以指 定的 MB数量增长和按百分比增长 。 默认后缀为 MB。 如果没 有指定 FILEGROWTH, 则默认值为 10%。 20 2. 几点说明 1)创建用户数据库后,要备份 master数据库。 2) 所有数据库都至少包含一个主文件组 。所有系统表都分配 在主文件组中。数据库还可以包含用户定义的文件组。 3)每个数据库都有一个所有者,可在数据库中执行某些特殊 的活动。数据库所有者是创建数据库的用户,也可以使 用 sp_changedbowner更改数据库所有者。 4)创建数据库的权限默认地授予 sysadmin和 dbcreator固定 服务器角色的成员。 Sysadmin和 securityadmin固定服 务器角色的成员可以将创建数据库的权限授予其他登录, 且必须显示授予,不能通过 GRANT ALL语句授予。 21 例 5-1:创建一个 只含一个数据文件和 一个事务日志文件的 数据库,数据库名为 JWGL1。 主数据库文件逻辑名 称为 jwgl1_data,数 据文件的操作系统名 称 jwgl1.mdf,数据 文件大小为 5MB,最 大值 500MB,数据文 件大小以 10%的增量 增加。 日志逻辑文件名称 jwgl1_log.ldf,事 务日志的操作系统名 称 jwgl1.ldf,日志 文件初始大小为 5MB, 最大值 100MB,日志 文件以 2MB增量增加。 程序清单为: CREATE DATABASE jwgl1 ON PRIMARY (NAME=jwgl1_data, FILENAME=c:Program FilesMicrosoft SQL ServerMSSQLDATAjwgl1.mdf, SIZE=5MB, MAXSIZE=500MB, FILEGROWTH=10%) LOG ON (name=jwgl1_log, FILENAME=c:Program FilesMicrosoft SQL ServerMSSQLDATAjwgl1.ldf, SIZE=5MB, MAXSIZE=100MB, FILEGROWTH=2MB) GO 22 例 5-2: 创建一个指定多个数据文件和日志文件的数据库。 该数据库名称为 jwgl2,有 2个 10MB的数据文件和 2个 10MB 的事务日志文件。主文件是列表中的第一个文件,并使用 PRIMARY关键字显式指定。事务日志文件在 LOG ON关键字后 指定。注意 FILE_NAME选项中所用的文件扩展名:主数据文 件使用 .mdf,次数据文件使用 .ndf,事务文件使用 .ldf。 程序清单: CREATE DATABASE jwgl2 ON PRIMARY (NAME = jwgl20_data, FILENAME =c:program filesmicrosoft sql servermssqldatajwgl20.mdf, SIZE = 10MB, MAXSIZE = 200, FILEGROWTH = 20), 23 ( NAME = jwgl21_data, FILENAME = c:program filesmicrosoft sql servermssqldatajwgl21.ndf, SIZE = 10MB, MAXSIZE = 200, FILEGROWTH = 20) LOG ON ( NAME = jwgl20_log, FILENAME =c:program filesmicrosoft sql servermssqldatajwgl20.ldf, SIZE = 10MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = jwgl21_log, FILENAME =c:program filesmicrosoft sql servermssqldatajwgl21.ldf, SIZE = 10MB, MAXSIZE = 200, FILEGROWTH = 20) GO 24 例 5-3创建一个包含 2个文件组的数据库。该数据库名为 jwgl3, 主文件组包含文件 jwgl30_data和 jwgl31_data。文件组 jwgl3_group包含文件 jwgl32_data和 jwgl33_data。两个文件 组数据文件的 FILEGROWTH 增量为 15%,数据文件的初始 大小为 10 MB。事务日志文件的文件名为 jwgl3_log, FILEGROWTH 增量为 15%,日志文件的初始大小为 5 MB。 程序清单: CREATE DATABASE jwgl3 ON PRIMARY ( NAME = jwgl30_data, FILENAME = c:program filesmicrosoft sql servermssqldatajwgl30.mdf, SIZE = 10MB, 25 MAXSIZE = 50MB, FILEGROWTH = 15% ), ( NAME = jwgl31_data, FILENAME = c:program filesmicrosoft sql servermssqldatajwgl31.ndf, SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 15% ), FILEGROUP jwgl3_Group ( NAME = jwgl32_data, FILENAME = c:program filesmicrosoft sql servermssqldatajwgl32.ndf, SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 15% ), 26 ( NAME = jwgl33_data, FILENAME =c:program filesmicrosoft sql servermssqldatajwgl33.ndf, SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 15% ) LOG ON ( NAME = jwgl3_log, FILENAME =c:program filesmicrosoft sql servermssqldatajwgl3.ldf, SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 15% ) GO 27 3-2.3.3 使用 SQL Server Management Studio创建数据库 使用 SQL Server Management Studio创建数据库比使用 T-SQL语句更容易。 具体步骤如下 : 1)进入 SQL Server Management Studio; 2)右击对象资源管理器的”数据库”项,在快捷菜单中选择“新建数据 库”,将打开如图 5-2所示的数据库属性窗口; 3)该窗口有三个选择页:常规、选项、文件组; 4)分别进行相关设置; 5)点击“确定”。 28 3-2.3.4 由已有库生成创建数据库的脚本 由已建数据库 JWGL生成该库的 SQL脚本的步骤 : ( 1)选择数据库 JWGL,按右键,选择“编写数据库教本 为 (S)” CREATE(C) ; ( 2)选择数据库脚本文件生成的目的地:“新查询编辑窗 口 (N)”、 “文件 (F)”或“剪贴板 (C)”; ( 3)单击“文件 (F)”,便会产生一个“选择文件”的对话 窗口,在其中输入文件名,单击“保存”按钮。 如要验证脚本的正确性,可进入查询编辑器,调入该脚本 程序执行即可 。 回到首页 29 3-2.4 数据库的管理 3-2.4.1 数据库属性的查看 1.用 T-SQL语言查看数据库的属性 使用系统存储过程可以查看数据库的属性。 常用的存储过程有 sp_helpdb, sp_spaceused, sp_filehelp, sp_helpfilegroup。 查看某个数据库或所有数据库的信息: EXEC sp_helpdb jwgl1 查看有关数据库中所占用空间的信息: EXEC sp_spaceused 显示有关数据库中文件组的信息: EXEC sp_helpfilegroup 显示数据库中文件的信息: EXEC sp_helpfile jwgl1_data 2. 用 SQL Server Management Studio查看数据库的属性 进入 SQL Server Management Studio,右击数据库,选择“属性”选 项即可查看数据库相关信息。 30 3-2.4.2 修改数据库大小 1. 用 T-SQL语言进行数据库大小的设定 例 5-4:修改数据库 JWGL1日志文件的最大值,由 100MB 改为 80MB。 程序清单: USE jwgl1 GO ALTER DATABASE jwgl1 MODIFY FILE(NAME=jwgl1_log,MAXSIZE=80MB) 31 例 5-5:修改 jwgl1_data数据文件的初始值,由 5MB改为 10MB。 程序清单: USE jwgl1 GO ALTER DATABASE jwgl1 MODIFY FILE(NAME= jwgl1_data, SIZE=10MB) 应该注意 ,对于数据库数据文件和事务日志文件 初始空间大小的 修改,新指定的空间大小值 不能小于 当前文件空间大小值 。 2. 用 SQL Server Management Studio修改数据库属性 进入 SQL Server Management Studio,展开树形结构的“数据库” 节点,选中要修改的数据库 JWGL1,按右键,在系统弹出快捷菜 单上点击“属性”,进入 JWGL1 数据库的属性窗口,可以根据需 要进入不同的选项卡修改数据库相应的信息。 32 3-2.4.3 数据库的收缩 SQL允许收缩数据库中的每个文件以删除未使用的页。数据和事务日志 文件都可以收缩。 数据库文件可以单独地进行手工收缩,也可以设置为按给定的时间间隔 进行自动收缩。 注意: 不能将整个数据库收缩到比原始大小还要小。 缩小数据库 有两种方法 : 1使用 SQL Server Management Studio可以缩小数据库 (教材 P91)。 2使用 Transact-SQL语言缩小数据库。 ( 1)使用 DBCC SHRINKDATABASE命令 收缩指定数据库中的数据文件。 DBCC SHRINKDATABASE (database_name,target_percent,NOTRUNCATE |TRUNCATEONLY) 33 database_name:要收缩的数据库名称 。 Target_percent:当数据库收缩后 , 数据库文件中剩余可用空间的百 分比 。 NOTRUNCATE:被释放的文件空间依然保持在数据库文件中 。 如果 未指定 , 将所释放的文件空间被操作系统回收 。 TRUNCATEONLY:将数据文件中未使用的空间释放给操作系统, 并将文件收缩到上一次所分配的大小。使用 TRUNCATEONLY 时, 将忽略 target_percent的限制。 将数据库设为自动收缩 使用 ALTER DATABASE语句可以将数据库设为自动收缩。设定数据 库是否自动收缩的 SQL 语句的语法形式: ALTER DATABASE database_name SET AUTO_SHRINK on/off on :数据库设为自动收缩, off :数据库设为不自动收缩。 34 【 例 5-7】 : 将数据库 JWGL1的收缩设为自动收缩, SQL脚本程序如下: ALTER DATABASE jwgl1 SET AUTO_SHRINK on 3-2.4.4 数据库的更名 一般情况下不要做数据库的改名。 如要更名,需将数据库属性“选项”中 的“限制访问”项设为“ single_user”,更名后再将“限制访问”项改回来。 两种给数据库更名的方法: ( 1)进入 SQL Server Management Studio,展开对象资源管理器,右击要 更名的数据库,选择 “ 重命名 ” 。 ( 2)数据库的更名的 SQL 语句的语法形式: sp_renamedb old_name , new_name 其中: old_name: 是数据库的当前名称 ; new_name: 是数据库的新名称。 【 例 5-8】 : 将数据库 JWGL1更名为 JWXT, SQL脚本程序如下: sp_renamedb jwgl1,jwxt 35 3-2.4.5 删除数据库 1利用 SQL Server Management Studio删除数据库 (教材 P92) 2利用 Drop语句删除数据库 Drop database database_name,n 说明:当数据库处于以下状态时不能被删除:数据库正在使用, 数据库正在恢复,系统数据库 master、 tempdb、 model、 msdb。 例 5-9 使用 DROP DATABASE 命令将数据库 JWXT删除。 DROP database jwxt 回到首页 36 3-2.5 设置 数据库的选项 数据库选项就是数据库的属性。这些选项将决定数据库的不同部分如何工 作,可以使用 SQLSQL Server Management Studio或者“ sp_dboption”存 储过程来修改数据库选项。 3-2.5.1 使用 Transact-SQL语句设置数据库的选项 使用存储过程 sp_dboption可以显示或更改数据库选项。但不能对 master或 tempdb数据库使用 sp_dboption。 设置数据库的选项的 SQL语句的语法形式如下: sp_dboption database option_namevalue 其中: database:在其中设置指定选项的数据库的名称。 option_name:要设置的选项的名称。该选项常用的 4个参数及含义如下: autoshrink: 当 value为 true时,数据库文件将成为自动周期性收缩的 候选文件。 dbo use only: 当 value为 true时,只有数据库所有者可以使用数据库。 37 read only: 当 value为 true时,用户仅能读取数据库中的数据而无 法对其修改。 single use:当 value为 true时,每次只能有一个用户访问数据库。 value: option_name的新设置。如果省略此参数, sp_dboption 将返回当前设置。 value 可以是 true、 false、 或 on、 off。 【 例 5-10】 将数据库 JWGL设置为只有数据库所有者可以使用。 USE jwgl EXEC sp_dboption jwgl , dbo use only , true 38 3-2.5.2 利用 SQL Server Management Studio设置数据库的选项 ( 1)进入 SQL Server Management Studio,展开对象资源管理器,右击要查看的 数据库,单击“属性”,弹出如图 5-3所示的窗口。 ( 2)单击该 数据库属性窗口“选项”选项卡 ,出现数据库的各个选项。用户可以进 行如下常用选项的设置: 排序规则 :通过从列表中进行选择来指定数据库的排序规则。 兼容级别 :制定数据库所支持的 Microsoft SQL Server的最新版本。 恢复模式 :制定数据库的恢复模型。 数据库为只读 :使这个数据库处于只读状态。如数据库设为只读状态,用户就 不能修改数据库中的任何记录。 限制访问: 制定哪些用户可以访问该数据库,可能的值有: MULTI_USER:允许多个用户同时访问该数据库。 SINGLE_USER:表示该数据库在同一时间只能供一个用户使用。 RESTRICTED_USER:指定只有 db_owner, dbcreator或 sysadmin的成员才有权使 用该数据库。 自动关闭 :当数据库中无用户时,自动关闭该数据库,并将所占用的资源交还 给 操作系统 。 自动收缩 :指定数据库是否自动缩减。 ( 3) 单击“确定”按钮,完成选项设置 。 回到首页 39
展开阅读全文