SQL第章数据库的创建与管理

第5章

数据库的创建和管理

5.1数据库简介5.2估算数据库的空间需求5.3数据库的创建5.4数据库的管理

5.5设置数据库选项

返回目录1第1页，共37页。5.1数据库简介数据库创建的过程就是数据库逻辑设计的物理实现过程。如果能根据ERA模型并结合单位的实际情况创建出一个性能优异、结构合理并具有良好扩充性能的数据库，将会为后在数据库基础上开发应用程序奠定坚实的基础，这是数据库管理最重要、最核心的问题。数据库的创建主要包括数据库及其表、索引、视图、存储过程等对象的创建。创建数据库时,系统会建立数据库的存储结构。数据库的存储结构分为逻辑存储结构和物理存储结构两种。

数据库的逻辑存储结构指的是数据库是由哪些性质的信息所组成。实际上，SQLServer的数据库是由诸如表、视图、索引等各种不同的数据库对象所组成。2第2页，共37页。数据库的物理存储结构是讨论数据库文件是如何在磁盘上存储的，数据库在磁盘上是以文件为单位存储的，由数据库文件和事务日志文件组成，一个数据库至少应该包含一个主数据库文件和一个事务日志文件。

1．主数据库文件（PrimaryDatabaseFile）

一个数据库可以有一个或多个数据库文件，一个数据库文件只能属于一个数据库。当有多个数据库文件时，有一个文件被定义为主数据库文件（简称为主文件），其扩展名为mdf。主数据库文件用来存储数据库的启动信息以及部分或者全部数据，是所有数据库文件的起点，包含指向其它数据库文件的指针。一个数据库只能有一个主数据库文件。

2．辅助数据库文件（SecondaryDatabaseFile）

用于存储主数据库文件中未存储的剩余数据和数据库对象，一个数据库可以没有辅助数据库文件，但也可以同时拥有多个辅助数据库文件。

辅助数据库文件的扩展名为ndf（简称为辅助文件）。

3第3页，共37页。5.1.1数据库的存储结构1.盘区和页面在创建数据库对象时,SQLServer会使用盘区和页面这种特殊的数据结构给数据库对象分配空间。在SQL中，数据存储的基本单位是页。页的大小是8KB，这样数据库上每一兆字节是128页。每页的开始部分是96B的页首，用于存放系统信息，如页的类型、页的可用空间、占据该页的数据库对象的对象标识等。页有数据页、索引页、文本/图象页等8种（P80）。盘区（区域）是连续8个页，可防止数据产生碎片，使SQL易于寻找。SQL有两种盘区组织页面：单一区域和混合区域。单一区域用于一个对象，混合区域用于多个小对象。事务日志不被分成页和区域，而是包含已修改数据的清单，按先来先服务的原则组织。4第4页，共37页。2.文件组文件组是文件的集合。对文件进行分组，便于进行数据的管理和分配磁盘空间。例如有三个数据文件data1.ndf,data2.ndf,data3.ndf,分别位于不同的磁盘上，将这三个文件指派到文件组中。假设在文件组上创建一个表，对表中数据的查询将会分散到三个磁盘上，从而提高系统的查询性能。文件和文件组的设计规则:文件和文件组不能由一个以上的数据库使用;文件只能是一个文件组的成员;事务日志文件文件不属于任何一个文件组。注意：数据和事务日志不能属于同一文件或文件组。数据文件和日志文件总是分开存放的。5第5页，共37页。主文件组中包含了所有的系统表，当建立数据库时，主文件组包括主数据库文件和未指定组的其他文件。用户定义文件组中可以指定一个缺省文件组，那么在创建数据库对象时如果没有指定将其放在哪一个文件组中，就会将它放在缺省文件组中。如果没有指定缺省文件组，则主文件组为缺省文件组。一个文件只能存在于一个文件组中，一个文件组也只能被一个数据库使用；日志文件是独立的，它不能作为任何文件组的成员。

5.1.2事务日志文件

SQL创建数据库的时候,会同时创建事务日志文件。事务日志文件存储数据库的更新情况等事务日志信息，当数据库损坏时，管理员使用事务日志恢复数据库。每一个数据库至少必须拥有一个事务日志文件，而且允许拥有多个日志文件。事务日志文件的扩展名为ldf，日志文件的大小至少是512KB。

6第6页，共37页。1.事务日志文件和数据文件必须分开存放，优点如下：事务日志文件可以单独备份；有可能从服务器失效的事件中将服务器恢复到最近的状态；事务日志不会抢占数据库的空间；可以很容易地监测到事务日志的空间；在向数据文件和事务日志文件写入数据时会产生较少的冲突，这有利于提高SQL的性能。SQLServer事务日志采用提前写入的方式。2.事务日志的工作过程在SQL中，事务是一次完成的操作的集合。虽然一个事务中可能包含了很多SQL语句，但在处理上就象他们是同一个操作似的。为了维护数据的完整性，事务必须彻底完成或者根本不执行。如果一个事务只是部分地被执行，并作用于数据库，那么数据库将可能被损坏或数据的一致性遭到破坏。7第7页，共37页。SQLServer使用数据库的事务日志来防止没有完成的事务破坏数据。事务日志的工作过程：应用程序发出一个修改数据库中的对象的事务；当这个事务开始时，事务日志会记录一个事务开始标志，并将被影响的数据页从磁盘读入缓冲区；事务中每个数据更改语句都被记录在日志文件中，日志文件将记录一个提交事务的标记。每一个事务都会以这种方式记录在事务日志中并被立即写到硬盘上。在缓冲区中修改相应的数据。这些数据一直在缓冲区中，在检查点进程发生时，检查点进程把所有修改过的数据页写到数据库中，并在事务日志中写入一个检查点标志，这个标志用于在数据库恢复过程中确定事务的起点和终点。以及哪些事务已经作用于数据库了。8第8页，共37页。随着数据库数据的不断变化，事务日志文件不断增大。因此，必须把它们备份出来，为更多的事务提供空间。备份时，事务日志文件会被截断。事务日志文件包含有在系统故障下恢复数据库需要的所有信息。一般来说，事务日志文件的初始大小是以数据文件大小的10%到25%为起点的，根据数据增长的情况和修改的频率进行调整。注意：SQLServer2005中的数据和事务日志文件不能存放在压缩文件系统或象共享网络目录等远程的网络驱动器上。

SQLServer2005的文件拥有两个名称，即逻辑文件名和物理文件名。当使用Transact-SQL命令语句访问某一个文件时，必须使用该文件的逻辑名。物理文件名是文件实际存储在磁盘上的文件名，而且可包含完整的磁盘目录路径。

本章首页9第9页，共37页。5.2估算数据库的空间需求作为数据库管理员，主要任务之一就是创建数据库，并且需要为每个文件指定容量。必须尽可能准确地估算数据库容量，以免浪费磁盘空间资源或者因估计不足造成数据库的空间不够。许多因素会影响数据库最终的大小，在估算数据库容量时要考虑如下因素：每行记录的大小；记录数量；表的数量；索引的数量及索引大小；数据库的对象的数量和大小；事务日志的大小；数据库的计划增加量；本章首页10第10页，共37页。5.3创建数据库

创建数据库的三种方法：1.使用SQLServerManagementStudio创建数据库

2.使用Transact-SQL语言创建数据库5.3.1创建数据库的注意事项:创建数据库需要一定许可，在默认情况下，只有系统管理员和数据库拥有者可以创建数据库。数据库被创建后，创建数据库的用户自动成为该数据库的所有者。

创建数据库的过程实际上就是为数据库设计名称、设计所占用的存储空间和存放文件位置的过程等，数据库名字必须遵循SQLServer命名规范。所有的新数据库都是系统样本数据库model的副本。单个数据库可以存储在单个文件上，也可以跨越多外文件存储。11第11页，共37页。5.数据库的大小可以被增大或者收缩。6.当新的数据库创建时，SQLServer自动更新“sysdatabases”系统表。7.一台服务器上最多可能创建32767个数据库。5.3.2使用T-SQL语言创建数据库说明：在Transact-SQL语言的命令格式中，用[]括起来的内容表示是可选的；[，…n]表示重复前面的内容；用<>括起来表示在实际编写语句时，用相应的内容替代；用{}括起来表示是必选的；类似A|B的格式，表示A和B只能选择一个，不能同时都选。1.创建数据库的SQL语句的语法格式:

CREATEDATABASEdatabase_name[ON[PRIMARY][<>[，…n][，<>[，…n]]][LOGON{<>[，…n]}][FORLOAD|FORATTACH]12第12页，共37页。其中：<>::=

[NAME=logical_，]=‘os_’[，SIZE=size][，MAXSIZE={max_size|UNLIMITED}][，=growth_increment][，…n]<>::=

<>[，…n]各参数的含义（如下）：database_name：新数据库的名称。数据库名称在服务器中必须唯一，最长为128个字符，并且要符合标识符的命名规则。每个服务器管理的数据库最多为32767个。ON：指定存放数据库的数据文件信息。<>列表用于定义主文件组的数据文件，<>列表用于定义用户文件组及其中的文件。13第13页，共37页。PRIMARY：用于指定主文件组中的文件。主文件组的第一个由<>指定的文件是主文件。如果不指定PRIMARY关键字，则在命令中列出的第一个文件将被默认为主文件。LOGON：指明事务日志文件的明确定义。如果没有本选项，则系统会自动产生一个文件名前缀与数据库名相同，容量为所有数据库文件大小1/4的事务日志文件。FORLOAD：表示计划将备份直接装入新建的数据库，主要是为了和过去的SQLServer版本兼容。FORATTACH：表示在一组已经存在的操作系统文件中建立一个新的数据库。NAME：指定数据库的逻辑名称。：指定数据库所在文件的操作系统文件名称和路径，该操作系统文件名和NAME的逻辑名称一一对应。14第14页，共37页。SIZE：指定数据库的初始容量大小。如果没有指定主文件的大小，则SQLServer默认其与模板数据库中的主文件大小一致，其它数据库文件和事务日志文件则默认为1MB。指定大小的数字size可以使用KB、MB、GB和TB后缀，默认的后缀为MB。Size中不能使用小数，其最小值为512KB，默认值为1MB。主文件的size不能小于模板数据库中的主文件。MAXSIZE：指定操作系统文件可以增长到的最大尺寸。如果没有指定，则文件可以不断增长直到充满磁盘。：指定文件每次增加容量的大小，当指定数据为0时，表示文件不增长。增加量可以确定为以KB、MB作后缀的字节数或以%作后缀的被增加容量文件的百分比来表示。默认后缀为MB。如果没有指定，则默认值为10%，每次扩容的最小值为64KB。15第15页，共37页。2.几点说明:1）创建用户数据库后，要备份master数据库。2）所有数据库都至少包含一个主文件组。所有系统表都分配在主文件组中。数据库还可以包含用户定义的文件组。3）每个数据库都有一个所有者，可在数据库中执行某些特殊的活动。数据库所有者是创建数据库的用户，也可以使用sp_changedbowner更改数据库所有者。4）创建数据库的权限默认地授予sysadmin和dbcreator固定服务器角色的成员。16第16页，共37页。[例5-1]：创建一个只含一个数据文件和一个事务日志文件的数据库，数据库名为JWGL1，主数据库文件逻辑名称为jwgl1_data，数据文件的操作系统名称jwgl1.mdf，数据文件初始大小为5MB，最大值为500MB，数据文件大小以10%的增量增加。日志逻辑文件名称jwgl1_log.ldf，事务日志的操作系统名称jwgl1.ldf，日志文件初始大小为5MB，最大值100MB，日志文件以2MB增量增加。程序清单见下页：17第17页，共37页。CREATEDATABASEjwgl1ON

PRIMARY(NAME=jwgl1_data,=‘c:\ProgramFiles\MicrosoftSQLServer\MSSQL\DATA\jwgl1.mdf’,

SIZE=5MB,

MAXSIZE=500MB，

%)LOGON（NAME=jwgl1_log,=‘c:\ProgramFiles\MicrosoftSQLServer\MSSQL\DATA\jwgl1.ldf’,

SIZE=5MB,

MAXSIZE=100MB，=2MB)GO18第18页，共37页。[例5-2]：创建一个指定多个数据文件和日志文件的数据库。该数据库名称为jwgl2，有2个10MB的数据文件和2个10MB的事务日志文件。主文件是列表中的第一个文件，并使用PRIMARY关键字显式指定。事务日志文件在LOGON关键字后指定。注意选项中所用的文件扩展名：主数据文件使用.mdf，次数据文件使用.ndf，事务文件使用.ldf。程序清单CREATEDATABASEjwgl2ONPRIMARY(NAME=jwgl20_data,

=‘c:\ProgramFiles\MicrosoftSQLServer\MSSQL\DATA\jwgl20.mdf’,SIZE=10MB,

MAXSIZE=200,=20),19第19页，共37页。(NAME=jwgl21_data,

=‘c:\ProgramFiles\MicrosoftSQLServer\MSSQL\DATA\jwgl21.ndf’,

SIZE=10MB,

MAXSIZE=200,=20)LOGON

(NAME=jwgl20_log,=‘c:\ProgramFiles\MicrosoftSQLServer\MSSQL\DATA\jwgl20.ldf’,

SIZE=10MB,

MAXSIZE=200,=20),(NAME=jwgl21_log,=‘c:\ProgramFiles\MicrosoftSQLServer\MSSQL\DATA\jwgl21.ldf’,

20第20页，共37页。SIZE=10MB,

MAXSIZE=200,=20)GO[例5-3]创建一个包含2个文件组的数据库。该数据库名为jwgl3，主文件组包含文件jwgl30_data和jwgl31_data。文件组jwgl3_group包含文件jwgl32_data和jwgl33_data。两个文件组数据文件的增量为15%，数据文件的初始大小为10MB。事务日志文件的文件名为jwgl3_log，增量为15%，日志文件的初始大小为5MB。CREATEDATABASEjwgl3ONPRIMARY(NAME=jwgl30_data,=‘d:\ProgramFiles\MicrosoftSQLServer\MSSQL\DATA\jwgl30.mdf’,

SIZE=10MB,

21第21页，共37页。=15%),(NAME=jwgl31_data,=‘d:\ProgramFiles\MicrosoftSQLServer\MSSQL\DATA\jwgl31.ndf’,

SIZE=10MB,=15%),

jwgl3_Group(NAME=jwgl32_data,=‘d:\ProgramFiles\MicrosoftSQLServer\MSSQL\DATA\jwgl32.ndf’,

SIZE=10MB,=15%),22第22页，共37页。(NAME=jwgl33_data,

=‘c:\ProgramFiles\MicrosoftSQLServer\MSSQL\DATA\jwgl33.ndf’,

SIZE=10MB,

MAXSIZE=50MB,=15%)LOGON(NAME=jwgl3_log,=‘c:\ProgramFiles\MicrosoftSQLServer\MSSQL\DATA\jwgl3.ldf‘,SIZE=5MB,

MAXSIZE=25MB,

=15%)GO23第23页，共37页。5.3.3使用SQLServerManagementStudio创建数据库使用SQLServerManagementStudio创建数据库比使用T-SQL语句来得更容易;具体步骤如下:1）进入SQLServerManagementStudio;2）点击SQL服务器组,进入已经配置注册的服务器,点击”数据库”项;3）点击“操作”，再选择“新建数据库”或右击数据库，在快捷菜单中选择“新建数据库”4）该窗口有三个选项卡：常规选项卡、数据文件选项卡、事务日志选项卡。5）点击“数据文件”页面标签，进行相关设置；6）点击“事务日志”选项卡，进行相关设置；7）点击“确定”。24第24页，共37页。5.3.4由已有库生成创建数据库的脚本由已建数据库JWGL生成该库的SQL脚本的步骤:1)选择数据库JWGL，按右键，选择“所有任务”；2）选择“生成SQL脚本”，便会产生一个“生成SQL脚本”的对话窗口；3）点击“选项”标签，点击不同的选项，可生成不同的脚本；4）点击“编写数据库脚本”复选框和“编写数据库用户和数据库角色脚本”复选框，再击点“确定”，输入脚本的文件名db_cre_text后便会生成创建数据库JWGL及JWGL库的用户和角色的脚本文件db_cre_text.sql。如要验证脚本的正确性，可进入查询分析器，调入该脚本程序执行即可。本章首页25第25页，共37页。5.4数据库的管理5.4.1数据库属性的查看1.用T-SQL语言查看数据库的属性使用系统存储过程可以查看数据库的属性。常用的存储过程有sp_helpdb，sp_spaceused，sp_，sp_help。查看某个数据库或所有数据库的信息：

EXECsp_helpdbjwgl1查看有关数据库中所占用空间的报表；

EXECsp_spaceused显示有关数据库中文件组的报表：

EXECsp_help

显示数据库中文件的报表：

EXECsp_helpfile2.用SQLServerManagementStudio查看数据库的属性（P89）进入SQLServerManagementStudio，指向数据库右击，选择“属性”选项即可查看数据库相关信息。26第26页，共37页。5.4.2修改数据库大小用T-SQL语言进行数据库属性的设定[例5-4]:修改数据库JWGL1日志文件的最大值由100MB改为现在的80MB。程序清单：USEjwgl1GOALTERDATABASEjwgl1MODIFY

FILE(NAME=jwgl1_log,MAXSIZE=80MB)27第27页，共37页。[例5-5]：修改jwgl1_data数据文件的初始值由5MB改为10MB。程序清单见（P90）USEjwgl1GOALTERDATABASEjwgl1MODIFYjwgl1_data,SIZE=10MB)应该注意，对于数据库数据文件和事务日志文件初始空间大小的修改，新指定的空间大小值不能小于当前文件初始空间大小值。2.用SQLServerManagementStudio修改数据库属性进入SQLServerManagementStudio，展开树形结构的“数据库”节点，选中要修改的数据库JWGL，按右键，在系统弹出快捷菜单上点击“属性”，进入JWGL数据库的属性窗口，你可以根据需要进入不同的选项卡修改数据库相应的信息。28第28页，共37页。5.4.3数据库的收缩SQL允许收缩数据库中的每个文件以删除未使用的页。数据和事务日志文件都可以收缩。数据库文件可以单独地进行手工收缩，也可以设置为按给定的时间间隔进行自动收缩。注意：不能将整个数据库收缩到比原始大小还要小。缩小数据库有两种方法：1．使用SQLServerManagementStudio可以缩小数据库。

2．使用Transact-SQL语言缩小数据库。

（1）使用DBCCSHRINKDATABASE命令收缩指定数据库中的数据文件。

DBCCSHRINKDATABASE(database_name[,target_percent][,{NOTRUNCATE|TRUNCATEONLY}])29第29页，共37页。database_name：要收缩的数据库名称。Target_percent：当数据库收缩后，数据库文件中剩余可用空间的百分比。

NOTRUNCATE：被释放的文件空间依然保持在数据库文件中。如果未指定，将所释放的文件空间被操作系统回收。TRUNCATEONLY：将数据文件中未使用的空间释放给操作系统，并将文件收缩到上一次所分配的大小。使用TRUNCATEONLY时，将忽略target_percent的限制。

⑵将数据库设为自动收缩使用ALTERDATABASE语句可以将数据库设为自动收缩。设定数据库是否自动收缩的SQL语句的语法形式：ALTERDATABASEdatabase_nameSETAUTO_SHRINKon/offon：数据库设为自动收缩，off：数据库设为不自动收缩。30第30页，共37页。【例5-7】将数据库JWGL1的收缩设为自动收缩，SQL脚本程序如下：ALTERDATABASEjwgl1SETAUTO_SHRINK

on5.4.4数据库的更名一般情况下不要做数据库的改名，如要更名需将数据库的并发用户数设为1，更名后再将数据库的并发用户数改回来。设置数据库并发用户的方法是：先点击要改名的数据库，再依次点击“工具”、“SQLServer配置属性”、“连接”，在“并发用户连接的最大数目”输入框中输入相应的数字即可。数据库的更名的SQL语句的语法形式：

sp_renamedb

‘old_name’,

‘new_name’其中：•old_name:是数据库的当前名称;•

new_name:是数据库的新名称。31第31页，共37页。【例5-8】将数据库JWGL1更名为JWXT，SQL脚本程序如下：sp_renamedbjwgl1,jwxt5.4.5删除数据库

1．利用SQLServerManagementStudio删除数据库（P92）2．利用Drop语句删除数据库

Dropdatabase

database_name[,…n]说明：只有处于正常状态下的数据库，才能使用DROP语句删除。当数据库处于以下状态时不能被删除：数据库正在使用；数据库正在恢复；系统数据库master、tempdb、model、msdb。

[例5-9]使用DROPDATABASE命令将数据库JWXT删除。

DROPdatabasejwxt本章首页32第32页，共37页。5.5设置数据库的选项数据库选项就是数据库的属性。这些选项将决定数据库的不同部分如何工作，可以使用SQLSQLServerManagementStudio或者“sp_dboption”存储过程来修改数据库选项。5.5.1使用Transact-SQL语句设置数据库的选项使用存储过程sp_dboption可以显示或更改数据库选项。但不能对master或tempdb数据库使用sp_dboption。设置数据库的选项的SQL语句的语法形式如下：

sp_dboption[‘database’][‘option_name’][‘value’]其中：•database：在其中设置指定选项的数据库的名称。•option_name：要设置的选项的名称。该选项常用的4个参数及含义如下：autoshrink:当value为true时，数据库文件将成为自动周期性收缩的候选文件。dbouseonly:当value为true时，只有数据库所有者可以使用数据库。33第33页，共37页。readonly:当value为true时，用户仅能读取数据库中的数据而无法对其修改。singleuse：当value为true时，每次只能有一个用户访问数据库。•value：option_name的新设置。如果省略此参数，sp_dboption将返回当前设置。value可以是true、false、或on、off。【例5-