SQL Server 数据库基础及应用-3

3.1数据库的存储结构

数据库是“按照数据结构来组织、存储和管理数据的仓库”。在经济管理和日常工作中，常常需要把某些相关的数据放进这样的“仓库”，并根据管理的需要进行相应的处理。例如，学校的学生管理部门需要把本校学生的基本情况(学号、系别、班级、姓名、年龄、性别、籍贯、联系方式)存放在表中，这张表就可以看成是一个数据库。有了这个“数据仓库”，我们就可以根据需要随时查询某学生的基本情况，也可以查询籍贯在某个地区的学生人数等。如果这些工作都能在计算机上自动进行，那我们的学生管理工作就可以达到极高的水平。下一页返回3.1数据库的存储结构

此外，在成绩管理、教务管理、寝室管理中也需要建立众多的这种“数据库”，使其可以利用计算机实现成绩、教务、寝室的自动化管理。在SQLServer中，所有的信息都存储在页(Page)上，页是数据库中使用的最小数据单元。在每个文件中，第一页是包含文件特性信息的文件首页。在文件开始处的其他几页也包含系统信息(如分配映射表)。系统页存储在主要数据文件和第一个日志文件中，其中有一个是包含数据库属性信息的数据库引导页。上一页下一页返回3.1数据库的存储结构文件数据库中的所有数据和对象(存储过程、触发器、视图等)都存储在文件或文件组中。SQLServer2012的数据库文件有三类:(1)主数据文件(Primary)主数据文件是数据库的关键文件，包含数据库启动的信息，并用于存储数据。每个数据库都有且只有一个主数据文件。主数据文件的扩展名是.mdf上一页下一页返回3.1数据库的存储结构

(2)次要数据文件(Secondary)次要数据文件又称辅数据文件，包含除主数据文件外的所有数据文件。次要数据文件是可选的，有些数据库没有次要数据文件，而有些数据库则有多个次要数据文件。次要数据文件的扩展名是.ndfo(3)事务日志文件(TransactionLog)事务日志文件包含恢复数据库所需的所有日志信息。每个数据库必须至少有一个事务日志文件，但可以不止一个。事务日志文件的扩展名是.ldf。说明:创建一个数据库后，该数据库应该至少有一个主数据文件和一个事务日志文件。上一页下一页返回3.1数据库的存储结构文件组SQLServer2012文件组就是文件的逻辑集合，它的目的是为了方便数据的管理和分配，使用文件组可以提高表中数据的查询性能。有三类文件组:(1)主文件组

主文件组包含主数据文件以及任何其他没有放入其他文件组的文件。系统表的所有页都从主文件组分配。上一页下一页返回3.1数据库的存储结构

(2)用户定义文件组用户定义文件组是指使用创建数据库CREATEDATABASE或修改数据库ALTERDATABASE语句中的FILECROUP关键字所指定的文件组。(3)默认文件组默认文件组包含在创建时没有指定文件组的所有表和索引的页。在每个数据库中，每次只能有一个文件组是默认文件组。如果没有指定默认文件组，则默认文件组是主文件组。说明:文件组的创建不能独立于数据库文件，一个文件组只能被一个数据库使用;文件组只能包含数据文件;事务日志文件不能属于文件组。上一页返回3.2创建数据库

在SQLServer2012中，创建数据库的用户必须是系统管理员，或者是被授权创建数据库的用户。创建数据库时必须指定数据库名、所有者、数据库大小(初始大小、最大大小、数据文件是否允许增长以及增长方式)和存储数据库的文件。

下面给出一个“xscj”数据库实例，通过这个实例讲解数据库的创建及其相关操作。下一页返回3.2创建数据库1.使用MicrosoftSQLServerManagementStudio创建数据库在SQLServer2012中创建数据库，按以下步骤进行:1)打开SQLServer2012工作界面。2)在SQLServerManagementStudio窗口中先展开SQLServer组，右击“数据库”图标，在弹出的快捷菜单中，选择“新建数据库”命令，如图3一1所示。3)弹出“新建数据库”对话框(图3-2)，数据库的所有设置将在此对话框中完成。在“数据库名称”文本框中输入数据库的名称(本例中数据库名为XSC)，并且该名称不能与现存的数据库的名称相同。“所有者”即创建数据库的用户。上一页下一页返回3.2创建数据库

4)在“数据库文件”选项的“逻辑名称”中设置数据文件的逻辑文件名，如图3-2所示。按上述步骤创建数据库时，包含一个主数据文件和一个日志文件。SQLServer会默认以数据库的名称作为主数据文件的逻辑文件名，以数据库的名称加上_log作为日志文件的逻辑文件名。如果需要更改默认文件名，可将光标移到“逻辑名称”下方的文本框中进行修改。5)指定初始大小。这是数据文件一开始创建时的大小，默认的主数据文件和日志文件的初始大小分别是5MB和1MB。若想设置初始大小，可将光标移到“初始大小”下方的文本框中输入一数值。上一页下一页返回3.2创建数据库

6)设置增长方式。主数据文件的大小可以是固定的，也可以自动增长。单击“自动增长/最大大小’，数据文件的按钮，打开图3-3所示。更改xscj的自动增长设置’，对话框，并在其中进行相关设置。如果希望随着数据库中数据的增加，主数据文件会在空间不足时自动增长，则选中复选框“启用自动增长”。还可以设置增长方式，如果希望空间不足时便增加特定数目，则选择“按MB"，并在右侧的数值微调器中输入一数值;如果希望空间不足时便增加特定比例大小，则选择“按百分比”，并在右侧的数值微调器中键入百分比数值即可。7)设置主数据文件的最大大小是否要有上限。上一页下一页返回3.2创建数据库8)设置路径。主数据文件和日志文件的默认存储位置是:SQLServer2012根目录\Data\数据库名.mdf和SQLServer2012根目录\Data\数据库名_log.ldf。本例中SQLServer2012的安装目录为:C:\ProgramFiles\MierosoftSQLServer\MSSQLII.MSSQLSERVER\MSSQL\DATA，故Mfreel建的xsej数据库的数据文件和日志文件的默认路径分别为C:\ProgramFiles\MicrosoftSQLServer\MSSQLII.MSSQLSERVER\MSSQL\DATA\xsej.mdf和C:\ProgramFiles\MicrosoftSQLServer\MSSQLII.MSSQLSERVER\MSSQL\DATA\xsej_log.ldf.。上一页下一页返回3.2创建数据库若想更改存储位置，可将光标移到“路径”下方的文本框中进行修改或者用鼠标单击按钮，打开图3-4所示衅定位文件夹’，对话框，并在其中选择存储目录。10)设置日志文件的相关属性。完成主数据文件的各项设置后，单击日志文件的匡二按钮，打开“更改xscj_log的自动增长设置”对话框，并对日志文件做进一步的设置。在此可以设置日志文件的是否自动增长、文件增长方式、是否限制最大文件大小，其设置方法和前面设置主数据文件的方法完全相同，因此不再一一赘述。上一页下一页返回3.2创建数据库10)添加次要数据文件。这一步骤可选也可不选，因为大多数数据库只需要一个主数据文件和一个日志文件就可以运行了。但如果主数据文件太大或者需要将数据分散到不同的物理磁盘上以提高输入输出效率时，就必须指定次要数据文件了。如图3-5所示，单击“添加”按钮，像设置数据文件一样进行设置即可。11)最后，单击“新建数据库”对话框的“确定”按钮完成数据库的创建，如图3-5所示。上一页下一页返回3.2创建数据库2.使用Transact-SQL命令除了使用图形界面方式创建数据库外，还可以使用Transact-SQL简称T-SQL)命令方式来创建数据库。与界面方式相比，命令方式更为常用也更加灵活。上一页下一页返回3.2创建数据库

参数解释如下:令，database_name:新数据库的名称。数据库名称在服务器中必须唯一，并且符合标识符的规则。database_name最多可以包含128个字符，除非没有为日志指定逻辑名。如果没有指定日志文件的逻辑名，则Microsoft⑧SQLServerT会通过向database_nam。追加后缀来生成逻辑名。令ON:指定显式定义用来存储数据库数据部分的磁盘文件(数据文件)。令LOCON:指定显式定义用来存储数据库日志的磁盘文件(日志文件)。上一页下一页返回3.2创建数据库

令FORLOAD:支持该子句是为了与早期版本的MicrosoftSQLServer兼容。数据库在打开，dbouseonly数据库选项的情况下创建该子句，并且将其状态设置为正在装载。SQLServer7.0版中不需要该子句，因为RESTORE语句可以作为还原操作的一部分重新创建数据库。

令FORATTACH:指定从现有的一组操作系统文件中附加数据库。文件中必须有指定第一个主文件的<filespec>条目。至于其他<filespec>条目，只需要与第一次创建数据库或上一次附加数据库时路径不同的文件的那些条目，并且必须为这些文件指定<filespec>条目。上一页下一页返回3.2创建数据库创建附加的数据库时，必须使用与SQLServe:相同的代码页和排序次序。应使用sp_attach一db系统存储过程，而不直接使用CREATEDATABASEFORATTACH，因为只有在必须指定16个以上的<filespec>条目时，才需要使用CREATEDATABASEFORATTACH.令collation_name:指定数据库的默认排序规则。排序规则名称既可以是Windows排序规则名称，也可以是SQL排序规则名称。如果没有指定排序规则，则将SQLServe:实例的默认排序规则指定为数据库的排序规则。上一页下一页返回3.2创建数据库

令PRIMARY:指定关联的<filespec>列表定义主文件。主文件组包含所有数据库的系统表，还包含所有未指派给用户文件组的对象。主文件组的第一个<filespec>条目成为主文件，该文件包含数据库的逻辑起点及其系统表。一个数据库只能有一个主文件，如果没有指定PRIMARY,那么CREATEDATABASE语句中列出的第一个文件将成为主文件。

令NAME:为<filespec>定义的文件指定逻辑名称。如果指定了FORATTACH，则不需要指定NAME参数。上一页下一页返回3.2创建数据库

令logical_file_name:用来在建数据库后执行的Transact-SQL语句中引用文件的名称.logical_1e_name在数据库中必须唯一，并且符合标识符的规则。该名称可以是字符或Unicode常量，也可以是常规标识符或定界标识符.令FILENAME:为<filespec>定义的文件指定操作系统文件名。令os一1ename:操作系统创建<filespec>定义的物理文件时使用的路径名和文件名。os一1ename中的路径必须指定SQLServer实例上的目录。os_file_name不能指定压缩文件系统中的目录。如果文件在原始分区上创建，则os一1e_name必须只指定现有原始分区上的驱动器字母。上一页下一页返回3.2创建数据库每个原始分区上只能创建一个文件。原始分区上的文件不会自动增长，因此，在os_file_name指定原始分区时，不需要指定MAXSIZE和FILECROWTH参数令SIZE:指定<filespec>定义的文件的大小。如果主文件的<filespec>中没有提供SIZE参数，那么SQLServe:将使用model数据库中的主文件大小。如果次要文件或日志文件的<filespec>中没有指定SIZE参数，则SQLServer将使文件大小为3MB。上一页下一页返回3.2创建数据库

令size;<filespec>定义的文件的初始大小。可以使用千字节(KB)、兆字节(MB)、千兆字节(CB)作为后缀，一般默认为MB。指定一个整数且不包含小数位为Size的数值。如果没有指定size，则默认值为3MB。为主文件指定的大小至少应与model数据库的主文件大小相同。令MAXSIZE:指定<filespec>定义的文件可以增长到的最大大小。令max_size;<filespec>定义的文件可以增长到的最大大小。可以使用千字节(KB)、兆字节(MB)、千兆字节(CB)作为后缀，一般默认为MB。指定一个整数且不包含小数位为max_size的数值。如果没有指定max_size，那么文件将增长到磁盘变满为止。上一页下一页返回3.2创建数据库

令UNLIMITED:指定<filespec>定义的文件将增长到磁盘变满为止。令FILECROWTH:指定<filespec>定义的文件的增长量。文件的FILECROWTH设置不能超过MAXSIZE设置。令growth_increment:每次需要新的空间时为文件添加的空间大小。指定一个整数且不包含小数位为growth_increment的数值。0值表示不增长。该值可以以KB,MB,CB或百分比(%)为单位指定。如果未在数量后面指定KB,MB或%;则默认为MB。如果指定%，则增量大小为发生增长时文件大小的指定百分比。如果没有指定FILECROWTH，则默认值为10%，最小值为64KB.指定的大小舍入为最接近的64KB的倍数。上一页下一页返回3.2创建数据库

【例3-1】创建一个名为xscj的数据库，该数据库中包含一个主数据文件和一个日志文件，它们均采用系统默认存储路径和文件名，其中主数据文件为5MB，最大大小不受限制，按2MB增长;日志文件为1MB，最大大小10MB，按10%增长。以下是完成该任务的步骤:1)单击SQLServer2012工具栏上的“新建查询”按钮，进入“Transact-SQL语句输入窗口”，如图3一6所示。2)然后在该窗口中输入如下语句，如图3-7所示。3)在查询窗口执行创建数据库程序，并返回结果，如图3-8所示。上一页返回3.3修改数据库

数据库创建以后，在使用中常会因为各种原因需要修改某些属性。对已经存在的数据库可以进行如下修改:修改主数据文件或日志文件的大小和增长方式、增加或删除文件、增加或删除文件组。

说明:数据库创建后，数据文件和日志文件的物理存储位置和文件名就不能修改了。下一页返回3.3修改数据库1.使用MicrosoftSQLScrvcrManagcmcntStudio修改数据库在“对象资源管理器”中右击“xscj”节点，在弹出的快捷菜单中选择“属性”，弹出图3-9所示的“数据库属性一xscj”对话框，在左窗格中选择“文件”选项卡，修改数据库的大部分操作与创建数据库的操作相同，在此不再一一介绍。2.使用Transact-SQL命令ALTERDATABASE命令提供了更改数据库名称、文件组名称以及文件相关属性的功能。上一页下一页返回3.3修改数据库上一页下一页返回3.3修改数据库

参数解释如下:

令DATABASE:是要更改的数据库的名称。

令ADDFILE:指定要添加的文件。

令TOFILECROUP:指定要添加指定文件的文件组。

令filegrou}name:要添加指定文件的文件组名称。

令ADDLOCFILE:将日志文件添加到指定的数据库。

令REMOVEFILE:从数据库的系统表中删除文件描述并删除物理文件。只有在文件为空时才能删除。上一页下一页返回3.3修改数据库

令ADDFILECROUP:指定要添加的文件组。

令REMOVEFILECROUP:从数据库中删除文件组并删除该文件组中的所有文件。只有在文件组为空时才能删除。

令MODIFYFILE:指定要更改的文件。更改选项包括FILENAME,SIZE,FILECROWTH和MAXSIZE，并且一次只能更改这些属性中的一种。必须在<filespec>中指定NAME，以标识要更改的文件。如果指定了SIZE，那么新大小必须比文件当前大小要大。只能为tempdb数据库中的文件指定FILENAME，而且新名称只有在MicrosoftSQLServer重新启动后才能生效。上一页下一页返回3.3修改数据库若要更改数据文件或日志文件的逻辑名称，应在NAME选项中指定要更改的逻辑文件名称，并在NEWNAME选项中指定文件的新逻辑名称。如果指定了SIZE，那么新大小必须比文件当前大小要大。只能为tempdb数据库中的文件指定FILENAME，而且新名称只有在MicrosoftSQLServer重新启动后才能生效。若要更改数据文件或日志文件的逻辑名称，应在NAME选项中指定要更改的逻辑文件名称，并在NEWNAME选项中指定文件的新逻辑名称。上一页下一页返回3.3修改数据库

令MODIFYNAME=new_dbname:重命名数据库。

令MODIFYFILECROUPfilegrounamefilegroup_propertyNAME=new_filegroup_name指定要修改的文件组和所需的改动。如果指定filegrou}name和NAME=new一legrou}name，则将此文件组的名称改为new_filegrouname。如果指定filegrou}name和filegroup_property，则表示给定文件组属性将应用于此文件组。上一页下一页返回3.3修改数据库

【例3-2】在例3.1中已经创建了xscj数据库，现对该数据库做如下修改:1)将xscj数据库中的主数据文件的初始大小改为50MB，增长方式改为每次按5MB增长，如图3一10所示。2)增加数据文件xscj_dat，初始大小为10MB，最大大小为50MB，按10%增长，如图3一11所示。3)删除(2)中增加的数据文件xscj_dat，如图3一12所示。4)建一个文件组TestlFC1，并将一个数据文件(初始大小5MB，最大大小为100MB，按5MB增长)添加到该文件组，然后将TestlFC1设置为默认文件组，如图3一13所示。上一页返回3.4查看数据库1.使用MicrosoftSQLServerManagementStudio查看数据库在SQLServer2012中查看数据库，按以下步骤进行:1)打开SQLServer2012工作界面。2)在SQLServerManagementStudio窗口中，先展开数据库，再选中要查看的数据库。右击“xscj”数据库，在弹出的快捷菜单中，选择“报表”、“标准报表”、“磁盘使用情况”命令，如图3一14所示。下一页返回3.4查看数据库

在“磁盘使用情况”选项卡中会列出，数据库总空间使用量、数据文件的空间使用量、事物日志的空间使用量、数据文件使用的磁盘空间等信息，如图3一15所示。公使用Transact-SQL命令查看数据库语法格式:sphelpdb【@dbname]’name’]参数解释如下:上一页下一页返回3.4查看数据库@dbname='name':要为其提供信息的数据库名称。name的数据类型为sysname，无默认值。如果没有指定name，则sp_helpdb报告master.dbo.sysdatabases中的所有数据库，其返回结果集有两个(表3一1和表3一2).【例3-3查看xscj数据库的相关信息，如图3一16所示。上一页返回3.5删除数据库1.使用MicrosoftSQLScrvcrManagcmcntStudio删除数据库从SQLServer2012中删除数据库的过程是:在对象资源管理器中选择要删除的数据库，选中该数据库单击鼠标右键，在弹出的快捷菜单中选择“删除”命令(图3一17)，会弹出“删除对象”对话框(图3一18)，选择要删除的xscj数据库，单击“确定”按钮即可。下一页返回3.5删除数据库2.使用Transact-SQL命令从MicrosoftSQLServer中删除一个或多个数据库。删除数据库将删除数据库所使用的数据库文件和磁盘文件。语法DROPDATABASEdatabase参数解释如下:namedatabase_name:指定要删除的数据库名称。说明:无法删除系统数据库(msdb,model,master,tempdb).【例3-4」删除例3一1中创建的xscj数据库(图3一19).[例3-5]删除pubsnewpubs数据库(图3-20).上一页返回3.6数据库的备份及还原

备份和还原是SQLServer的重要组成部分。数据库的备份和还原就是创建完整数据库的副本，以便用于以后重新创建数据库。数据备份是为了防“万一”，所以即使硬件既可靠又有冗余，数据的备份和还原永远都是保护数据的重要手段之一。SQLServe:提供了“备份/还原”数据库、“分离/附加”数据库、复制数据库等多种数据库的备份和恢复方法。无论你的数据库有多大，改变是否频繁，都有满足你的要求的备份策略。

按照备份数据库的大小，数据库的备份有四种类型，分别应用于不同的场合，下面简要介绍一下。下一页返回3.6数据库的备份及还原1.完全备份这是大多数人常用的方式，它可以备份整个数据库，包含用户表、系统表、索引、视图和存储过程等所有的数据库对象。但它需要花费更多的时间和空间，所以，一般推荐一周做一次完全备份。2.事务日志备份事务日志是一个单独的文件，它记录数据库的改变，备份的时候只需要复制自上次备份以来对数据库所做的改变，所以只需要很少的时间。上一页下一页返回3.6数据库的备份及还原3.差异备份差异备份也叫增量备份。它是只备份数据库一部分的另一种方法，它不使用事务日志，相反，它使用整个数据库的一种新映象。它比最初的完全备份小，因为它只包含自上次完全备份以来所改变的数据库。它的优点是存储和恢复速度快。推荐每天做一次差异备份。4.文件备份数据库可以由硬盘上的许多文件构成。如果这个数据库非常大，并且一个晚上也不能将它备份完，那么可以使用文件备份，每晚备份数据库的一部分。由于一般情况下数据库不会大到必须使用多个文件存储，所以这种备份不是很常用。上一页下一页返回3.6数据库的备份及还原1.使用MicrosoftSQLServerManagementStudio备份数据库使用SQLServer2012界面方式进行数据库备份的步骤为:1)启动MicrosoftSQLServerManagementStudioo2)打开数据库文件夹，右击要进行备份的数据库图标，在弹出的快捷菜单中选择“任务”，再选择“备份”，然后弹出“备份数据库”对话框，如图3一21所示。上一页下一页返回3.6数据库的备份及还原3)选项设置。①“备份类型”选择“完整”。②“备份组件”选择“数据库”。③“目标”备份到“磁盘”，可将备份文件存于当前默认路径。如果要更改路径，则请先删除当前路径，再单击“添加”，选择备份文件的存放路径。4)单击“确定”按钮立即开始备份，如图3-22所示。上一页下一页返回3.6数据库的备份及还原3使用MicrosoftSQLServerManagementStudio还原数据库使用SQLServer2012界面方式进行数据库还原的步骤为:1)启动MicrosoftSQLServerManagementStudio.2)打开数据库文件夹，单击鼠标右键选择“数据库”、“还原数据库”，即出现图3-23所示的对话框。上一页下一页返回3.6数据库的备份及还原3)选项设置。①在“目标数据库”文本框中键入数据库名称或新数据库名称。②“源”指定用于还原的备份集的源和位置。选择“数据库”，选中一条备份集记录，如果备份集列表中没有记录，则不可用。要选择“源设备”。③单击‘。设备”区二按钮，弹出【选择备份设备】对话框，如图3-24所示。④单击“添加”按钮，从windows中指定文件路径及文件名(图3一25)，然后单击“确定”按钮。上一页下一页返回3.6数据库的备份及还原4)此时，备份文件路径将被添加到“选择备份设备”、“备份介质”文本框中。单击“确定”按钮返回“还原数据库一xscj”对话框，如图3-26所示。5)在“要还原的备份集”中勾选要还原的数据库，单击“确定”按钮，则开始还原数据库。数据库成功还原后，单击“确定”按钮，自动退出还原状态，如图3-27所示。上一页下一页返回3.6数据库的备份及还原4使用Transact-SQL命令还原整个数据库语法格式RESTOREDATABASE{databasename}已databasenamevar}/*databasename或已databasenamevar指定被还原的目标数据库。*/【FROM<backupdevice>/*指定备份设备。参见RESTOREHEADONLY语句中backupdeviceo*/上一页下一页返回3.6数据库的备份及还原3.6.2分离/附加数据库“分离/附加”方法，类似于大家熟悉的“文件拷贝”方法，即把数据库文件(.MDF)和对应的日志文件(.LDF)拷贝到其他磁盘上作备份，然后把这两个文件再拷贝到任何需要这个数据库的系统之中。但由于数据库管理系统的特殊性，需要利用SQLServer提供的工具才能完成以上工作，而简单的文件拷贝会导致数据库根本无法正常使用。

这个方法涉及SQLServer分离数据库和附加数据库这两个操作。上一页下一页返回3.6数据库的备份及还原1)分离数据库就是将某个数据库(如xsej)从SQLServer数据库列表中删除，使其不再被SQLServer2012管理和使用，但该数据库的文件(.MDF)和对应的日志文件(.LDF)不会受到损坏。分离成功后，我们就可以把该数据库文件(.MDF)和对应的日志文件(.LDF)拷贝到其他磁盘中作为备份保存。2)附加数据库就是将一个备份磁盘中的数据库文件(.MDF)和对应的日志文件(.LDF)，添加到SQLServer2012中，由该服务器来管理和使用这个数据库。上一页下一页返回3.6数据库的备份及还原使用MicrosoftSQLServerManagementStudio分离数据库使用SQLServer2012界面方式进行分离数据库的步骤为:1)启动MicrosoftSQLServerManagementStudioo2)打开数据库文件夹，右击要进行分离的数据库xscj，在弹出的快捷菜单中选择“任务”，再选择“分离”，然后弹出“分离数据库”对话框，如图3一28所示。上一页下一页返回3.6数据库的备份及还原3)在图3-28所示的“分离数据库”对话框中出现要分离的数据库名称。请勾选“