SQL Server数据库应用开发技术 第四章

第4章数据库的创建与管理知识技能目标:1．理解数据库文件与文件组的概念2．熟练掌握数据库的创建与管理3．掌握数据库的分离与附加第4章数据库的创建与管理引导案例：如何在计算机上创建教务管理数据库？如何对教务管理数据库进行查看？修改？删除？如何将教务管理数据库移到另一台计算机上去？第4章数据库的创建与管理4.1数据库文件与文件组4.2用SQLServerManagementStudio创建和管理数据库4.3用T-SQL语句创建和管理数据库4.4数据库分离与附加小结4.1数据库文件与文件组4.1.1文件与文件组SQLServer2005使用一组操作系统文件映射数据库。数据库中的所有数据都将存储在这组文件中。这组文件包括:主文件、次要文件、事务日志文件返回目录（1）主文件主文件是数据文件，用于存储数据。每个数据库都必须有一个主数据文件。主文件的建议扩展名为.MDF。（2）次要文件次要文件也是数据文件，用于辅助主文件存储数据，即存储主文件中容纳不下的所有数据。一个数据库可以有多个次要文件，也可以没有次要文件。次要文件的建议扩展名为.NDF。4.1.1文件与文件组返回目录（3）事务日志文件事务日志文件用于保存事务日志信息。每个数据库都必须至少有一个事务日志文件。事务日志文件的建议扩展名为.LDF。4.1数据库文件与文件组4.1.1文件与文件组返回目录（4）文件组文件组是组织文件的一种管理机制，用于简化数据存放和管理任务。每个数据库有一个PRIMARY文件组，其中包含主要数据文件和不属于其它文件组的所有次要文件。可以创建用户定义的文件组，用于将数据文件集合起来，以便于管理、数据分配和放置。4.1数据库文件与文件组4.1数据库文件与文件组4.1.1文件与文件组返回目录（4）文件组例如，数据文件data1.ndf、data2.ndf、data3.ndf分别位于不同的三个磁盘上，将这三个文件指派到文件组fgroup1中，然后在文件组fgroup1上创建一个表，该表的数据分布就在三个磁盘上，在对该表执行查询时，可以并行操作，从而提高查询效率。如果在创建数据库时没有指定某个数据文件所属的文件组，则该数据文件将被分配给默认文件组。PRIMARY文件组是默认文件组。默认文件组可以更改。4.1.2文件与文件组的设计规则在设计数据库文件和文件组时须遵循以下规则：（1）一个文件和文件组只能被一个数据库使用。（2）一个文件只能属于一个文件组。（3）数据和事务日志信息不能共存于同一文件或文件组上。（4）事务日志文件不能属于任何文件组。返回目录4.1数据库文件与文件组4.2用SSMS创建和管理数据库4.2.1创建数据库在创建数据库之前要确定：数据库的名称数据库的所有者数据库的大小存储该数据库的文件和文件组返回目录4.2用SSMS创建和管理数据库4.2.1创建数据库步骤：（1）启动SQLServerManagementStudio；（2）展开服务器，右键单击“数据库”，选“新建数据库”；（3）在“新建数据库”窗口的“常规”页的数据库名称栏中输入数据库的名称；（4）在“新建数据库”窗口的数据库文件栏各列中，指定数据库文件的逻辑名称、初始大小、自动增长方式、存储位置（包括路径和物理文件名）；（5）若需添加数据文件或日志文件，可单击“添加”按钮；（6）若需添加文件组，可在选择页选择“文件组”，然后单击“添加”按钮；（7）单击“确定”按钮，完成数据库的创建。返回目录4.2用SSMS创建和管理数据库4.2.1创建数据库注意：数据库名称必须遵循SQLServer的标识符命名规则。所有的新数据库都是系统数据库model的副本，model数据库中的所有用户定义对象都将复制到新创建的数据库中。可以向model数据库中添加对象（例如表、视图、存储过程和数据类型），以将这些对象包含到所有新创建的数据库中。创建数据库的用户将成为该数据库的所有者。对于一个SQLServer实例，最多可以创建32767个数据库。返回目录4.2用SSMS创建和管理数据库4.2.2查看、修改数据库属性查看数据库属性：（1）启动SQLServerManagementStudio；（2）展开服务器，展开“数据库”；（3）右键单击要查看的数据库，在快捷菜单中选“属性”；（4）在“数据库属性”窗口中查看数据库属性返回目录4.2用SSMS创建和管理数据库4.2.2查看、修改数据库属性修改数据库：（1）启动SQLServerManagementStudio，展开服务器，展开“数据库”；（2）右键单击要查看的数据库，在快捷菜单中选“属性”，打开“数据库属性”窗口；（3）若要修改数据库文件属性，则选择“文件”页；（4）若要修改文件组属性，则选择“文件组”页；（5）若要设置数据库选项，则选择“选项”页；（6）在“数据库属性”窗口中单击“确定”按钮，完成数据库的修改。返回目录4.2用SSMS创建和管理数据库4.2.2查看、修改数据库属性重命名数据库：（1）启动SQLServerManagementStudio，展开服务器，展开“数据库”；（2）右键单击要重命名的数据库，在快捷菜单中选“重命名”；（3）输入新的数据库名称。注意：在重命名数据库之前，应该确保没有人使用该数据库，而且该数据库设置为单用户模式。返回目录4.2用SSMS创建和管理数据库4.2.3删除数据库当一个数据库不再需要使用时，或者已将其移到其他服务器上时，即可将它删除。一个数据库一旦被删除，则该数据库中所有文件及其数据就都被删除，该数据库在系统中所占用的空间被释放。返回目录步骤：（1）启动SQLServerManagementStudio，展开服务器，展开“数据库”；（2）右键单击要删除的数据库，在快捷菜单中选“删除”，则打开“删除对象”窗口。（3）确认选择的是要删除的数据库后，单击“确定”按钮，完成删除。4.2用SSMS创建和管理数据库4.2.3删除数据库注意：不能删除系统数据库。当有用户正在使用某个数据库时，该数据库不能被删除。数据库一旦被删除，即被永久删除，因此删除数据库应谨慎。删除数据库后应备份master数据库。返回目录4.3.1创建数据库语法格式：CREATEDATABASE数据库名[ON[PRIMARY]（NAME=逻辑文件名,FILENAME=‘物理文件名’,SIZE=初始文件大小,MAXSIZE=最大文件大小,FILEGROWTH=文件自动增量）[,...n][,文件组[,...n]][LOGON（NAME=逻辑文件名,FILENAME=‘物理文件名’,SIZE=初始文件大小,MAXSIZE=最大文件大小,FILEGROWTH=文件自动增量）[,...n]]返回目录4.3用T_SQL语句创建和管理数据库4.3.1创建数据库【例4.1】创建“教务管理数据库”。【例4.2】用简便方法创建mytest数据库。【例4.3】创建数据库Sales。该数据库具有主文件组和SalesGroup1文件组。主文件组包含文件Spri1_dat和Spri2_dat。SalesGroup1文件组包含文件SGrp1Fi1和SGrp1Fi2。返回目录4.3用T_SQL语句创建和管理数据库4.3.1创建数据库注意：（1）一个数据库只能有一个主文件。（2）数据库名称在SQLServer的实例中必须唯一，并且必须符合标识符规则。（3）如果没有指定PRIMARY，那么CREATEDATABASE语句中列出的第一个文件将成为主文件。（4）如果没有指定LOGON，将自动创建一个日志文件，其大小为该数据库的所有数据文件大小总和的25%或512KB，取两者之中的较大者。（5）在SQLServer2005中，数据文件的默认增量已从10%更改为1MB。返回目录4.3用T_SQL语句创建和管理数据库4.3.2查看数据库属性（1）使用sp_helpdb查看数据库信息格式：sp_helpdb[数据库名]显示指定数据库的信息，若不给出指定数据库，则显示服务器中所有数据库的信息。【例4.4】查看教务管理数据库的信息。【例4.5】查看服务器中所有数据库的信息。返回目录4.3用T_SQL语句创建和管理数据库4.3.2查看数据库属性（2）使用sp_databases查看可使用的数据库的信息格式：sp_databases显示所有可以使用的数据库的名称和大小【例4.6】查看有哪些数据库可以使用。返回目录4.3用T_SQL语句创建和管理数据库4.3用T_SQL语句创建和管理数据库4.3.2查看数据库属性（3）使用sp_helpfile查看数据库文件信息格式：sp_helpfile[文件名]显示与当前数据库关联的指定逻辑文件的物理文件名及属性。若不指出文件名，则显示数据库的所有文件的信息。【例4.7】查看教务管理数据库中教学管理文件的信息【例4.8】查看教务管理数据库的所有文件的信息返回目录4.3.2查看数据库属性（4）使用sp_helpfilegroup查看文件组信息格式：sp_helpfilegroup[文件组名]显示与当前数据库关联的指定文件组的名称与属性。若不指定文件组名，则显示当前数据库的所有文件组的信息。【例4.9】显示教务管理数据库的所有文件组的信息【例4.10】显示Sales数据库的SalesGroup1文件组的信息返回目录4.3用T_SQL语句创建和管理数据库4.3.3修改数据库大小语法格式：ALTERDATABASE数据库名ADDFILE(数据文件属性描述)[,...n]|ADDLOGFILE(日志文件属性描述)[,...n]|REMOVEFILE逻辑文件名|MODIFYFILE(文件属性描述)其中：ADDFILE增加数据文件ADDLOGFILE增加日志文件REMOVEFILE删除文件MODIFYFILE修改文件返回目录4.3用T_SQL语句创建和管理数据库4.3.3修改数据库大小【例4.11】修改教务管理数据库，增加数据文件jwgl_dat1。【例4.12】修改教务管理数据库【例4.13】删除教务管理数据库中次要数据文件jwgl_dat1【例4.14】修改数据库test，添加由两个文件testdat1、testdat2组成的文件组Test1FG1。【例4.15】修改数据库test，在test数据库中添加两个日志文件返回目录4.3用T_SQL语句创建和管理数据库4.3.3修改数据库大小注意：（1）用REMOVEFILE删除文件时，必须保证文件为空，否则无法删除文件。（2）用MODIFYFILE修改文件时，一次只能更改一个属性。每次修改都必须指定要修改的文件的逻辑名称，以标识要修改的文件。（3）修改文件大小时，新指定的SIZE必须大于文件当前大小。返回目录4.3用T_SQL语句创建和管理数据库4.3.4设置数据库选项可以为每个数据库设置若干决定数据库特征的数据库选项。当创建数据库时这些数据库选项设置为默认值。语法格式：ALTERDATABASE数据库名SET选项[,...n]【例4.16】设置教务管理数据库自动收缩【例4.17】将Sales数据库设置为只读数据库。返回目录4.3用T_SQL语句创建和管理数据库4.3.5数据库更名更改数据库名语法格式：ALTERDATABASE数据库名MODIFYNAME=新数据库名【例4.18】将数据库test更名为mytest。4.3用T_SQL语句创建和管理数据库返回目录4.3.5数据库更名更改数据库文件名（1）修改数据库文件的逻辑文件名语法格式：ALTERDATABASE数据库名MODIFYFILE(NAME=逻辑文件名,NEWNAME=新逻辑文件名)4.3用T_SQL语句创建和管理数据库返回目录4.3.5数据库更名更改数据库文件名（1）修改数据库文件的逻辑文件名【例4.19】将数据库mytest的主文件的逻辑名test改为mytest，日志文件的逻辑名test_log改为mytest_log4.3用T_SQL语句创建和管理数据库返回目录4.3.5数据库更名更改数据库文件名（2）修改数据库文件的物理文件名语法