《SQL Server 数据库技术及应用》-第4章 数据库的创建与管理

第4章数据库的创建与管理理解数据库的基本组成。掌握SSMS和T-SQL语句创建、查看、修改和删除数据库。掌握SSMS分离和附加数据数据库。掌握SSMS导入和导出数据库。第4章数据库的创建与管理

任务设计：4.1创建数据库4.2查看和修改数据库4.3删除数据库4.4分离、附加数据库4.5导出、导入数据库4.6收缩数据库4.7任务训练第4章数据库的创建与管理本章学习内容在数据库系统开发中的位置4.1任务1：创建数据库理解SQLServer2012数据库的存储方式1熟练掌握SSMS创建数据库的方法2熟练掌握T-SQL语句创建数据库的方法3任务目标：4.1.1认识数据库的组成数据库对象是存储、管理和使用数据的不同结构形式。数据库（DataBase）是数据库对象的容器，以操作系统文件的形式存储在磁盘上。数据库不仅可以存储数据，而且能够使数据存储和检索以安全可靠的方式进行。SQLServer2012中的数据库按模式级别分类，可分为物理数据库和逻辑数据库；按创建对象来分，则可分为系统数据库和用户数据库。4.1.1认识数据库的组成物理数据库构成单个数据库的实际文件，数据库文件存储的基本单元是页。数据库模式级别逻辑数据库SQLServer2012的数据库对象，包括表、视图、索引、约束、存储过程和触发器等。

4.1.1认识数据库的组成1.物理数据库（1）数据库文件：SQLServer2012数据库有3种物理文件：主数据文件、辅助数据文件和事务日志文件，见表4-1所示。4.1.1认识数据库的组成1.物理数据库（2）文件组为了方便管理和分配数据而将文件组织在一起就构成了文件组（FileGroup），通常可以为磁盘驱动器创建一个文件组，将多个数据库文件集合起来形成一个整体。每个文件组有一个组名，文件组可分两种。

主文件组：包含所有的系统表，当建立数据库时，主文件组包括主数据文件和未指定组的其它文件，是默认的数据文件组，一个数据库有一个主文件组。

次文件组：可以在次文件组中指定一个默认文件组，在创建数据库对象时如果没有指定将其放在哪个文件组中，将会将它放在默认文件组中。4.1.1认识数据库的组成2.逻辑数据库每个数据库节点又包含了一些子节点，它们代表该数据库不同类型的对象（数据库关系图、表、视图和存储过程等）。4.1.1认识数据库的组成3．系统数据库和用户数据库系统数据库是由系统创建维护的数据库，系统数据库中记录着SQLServer2012的配置情况、任务情况和用户数据库等系统管理信息。用户数据库是用户自己创建的数据库，用户可以数据库进行修改和删除等操作。4.1.2使用SSMS创建数据库使用SSMS图形界面创建数据库的方法使用T-SQL语句4.1.2使用SSMS创建数据库【例4-1】创建教务管理数据库，数据库名称EDUC。主数据文件保存路径“D:\教务管理数据文件”，日志文件保存路径“E:\教务管理日志文件”。主数据文件初始大小为4MB，最大大小为10MB，增长速度为10%；日志文件的初始大小为2MB，最大大小为4MB，增长速度为10%。创建后的数据库效果图如4-3所示。4.1.2使用SSMS创建数据库SSMS创建步骤：（1）分别在D盘和E盘根目录下创建文件夹教务管理数据文件和教务管理日志文件。（2）运行MicrosoftSQLServer2012程序，选择SQLServerManagementStudio进入连接到服务器窗口，选择默认设置（服务器类型：数据库引擎，服务器名称：本机名），选择Windows身份验证或SQLServer2012身份验证建立连接。提示：数据文件应该尽量不保存在系统盘上并与日志文件保存在不同的磁盘区域。对于D盘下的“教务管理数据库文件”和E盘下的“教务管理日志文件”两个文件夹应该在操作系统下事先创建好。4.1.2使用SSMS创建数据库（3）在【对象资源管理器】窗格中展开服务器，然后选择【数据库】节点并右击，从弹出菜单中选择【新建数据库】命令，如图4-4所示。4.1.2使用SSMS创建数据库（4）在【新建数据库】窗口的【选择页】窗格中，有3个选项，分别是【常规】、【选项】和【文件组】。选择【常规】选项，在窗口右侧的【数据库名称】文本框中输入数据库名称EDUC，所有者选择默认值。（5）在【数据库文件】列表中包含两行信息，前一行是数据文件信息，后一行是日志文件信息。单击【自动增长】列下的省略号按钮，打开【更改EDUC的自动增长设置】对话框，进行数据文件的自动增长设置，如图4-5所示。4.1.2使用SSMS创建数据库（6）单击【路径】下的省略号按钮，修改数据库文件存放的路径为“D：\教务管理数据文件”。同理，设置日志文件且保存路径为“E：\教务管理日志文件”（7）其它页面采用默认设置，完成操作后，单击【确定】按钮关闭窗口。成功创建了教务管理系统EDUC，在【对象资源管理器】窗格中按F5键刷新后可看到新建的数据库。

提示：（1）逻辑名称是指SQLServer2012里所看到的文件名称，用name关键字指定，而文件的物理名称就是指在硬盘上的存储路径，用filename关键字指定。（2）如果数据库的大小不断增长，则可以指定其增长方式。如果数据的大小基本不变，为了提高数据的使用率，通常不指定其具有自动增长方式。4.1.3使用T-SQL语句创建数据库SQLServer的编程语言是Transact-SQL，简称T-SQL。T-SQL的语句书写时不区分大小写，一般系统保留字大写，用户自定义的名称可用小写。T-SQL语法说明：“[]”中的内容表示可以省略，省略时系统取默认值。“{}[,…,n]”表示花括号中的内容可以重复书写n次，必须用逗号隔开。“|”表示相邻前后两项只能任取一项。一条语句分成多行书写，但多条语句不允许写在一行。4.1.3使用T-SQL语句创建数据库T-SQL创建数据库的完整语法格式：CREATEDATABASE<数据库名称>--其它选项使用默认值[ON[PRIMARY]{<数据文件>}[,……,n][FILEGROUP]{<文件组名>}[,……,n]LOGON{<日志文件>}[,……,n]]4.1.3使用T-SQL语句创建数据库4.1.3使用T-SQL语句创建数据库【例4-2】创建图书管理数据库Library。该数据库的主要数据文件逻辑名称为Library，物理文件名为Library.mdf，物理文件路径为：D:\图书管理数据（事先在操作系统下建立相应的文件夹），初始大小为3MB，最大容量100MB，增长速度为10%；数据库的日志文件逻辑名称为Library_log，物理文件名为Library_log.ldf，物理文件路径为E:\图书管理日志（事先在操作系统下建立相应的文件夹），初始大小为1MB，最大容量为2MB，增长速度为10%。创建后的数据库效果图如4-6所示。4.1.3使用T-SQL语句创建数据库在SSMS中，单击工具栏中的“新建查询”按钮，打开【查询编辑器】，输入如下代码：4.1.3使用T-SQL语句创建数据库【例4-3】创建一个test数据库，该数据库的主数据文件逻辑名称为test_data，物理文件名为test.mdf，初始大小为10MB，最大容量为无限大，增长速度为10%；次要数据文件逻辑名为test1，物理文件名为test1.ndf，初始大小5MB，最大容量100MB，增长速度为5MB；数据库的日志文件逻辑名称为test_log，物理文件名为test.ldf，初始大小为1MB，最大容量为5MB，增长速度为1MB。数据文件和日志文件分别放在D盘测试数据和E盘测试日志文件夹中。创建后的数据库效果图如4-7所示。4.1.3使用T-SQL语句创建数据库提示：以上存储容量的单位默认是以MB为单位的，还可以用KB、MB、GB或TB（terabyte）作为文件大小的单位。4.2任务2：查看和修改数据库熟练掌握SSMS查看和修改数据库的方法1熟练掌握T-SQL语句修改数据库的方法2任务目标：4.2.1使用SSMS查看和修改数据库在【对象资源管理器】窗口中，展开“数据库”节点，用右键单击目标数据库（如EDUC）。从弹出的菜单中选择“属性”命令，弹出【数据库属性-EDUC】窗口，如图4-8所示。4.2.1使用SSMS查看和修改数据库可以分别在“常规”、“文件”、“文件组”、“选项”和“权限”页里根据要求来查看和修改数据库相应的设置。在“常规”页中可以查看数据库的基本信息，具体项目如下。数据库上次备份的日期：最后一次执行数据库日志备份操作的日期。数据库日志上次备份日期：最后一次执行数据库日志备份操作的日期。名称：数据库名称。状态：数据库当前状态。所有者：数据库的所有者名称。创建日期：数据库创建的日期。大小：数据库的所有数据文件和日志文件大小的总和。可用空间：数据库的所有数据文件和日志文件的可用空间总和。用户数：当前连接到数据库的用户数量。排序规则：数据库当前使用的排序规则。4.2.2使用T-SQL语句查看和修改数据库1．使用T-SQL语句查看数据库(1)使用系统存储过程sp_helpdb查看数据库信息语法格式：[EXECUTE]sp_helpdb[数据库名]EXECUTE可以缩写为EXEC，如果它是一个批处理中的第一条语句可全部省略。【例4-4】在查询分析器中用sp_helpdb语句查看所有数据库信息。EXECsp_helpdb单击【运行】按钮，运行结果如图4-9所示。4.2.2使用T-SQL语句查看和修改数据库(2)使用系统存储过程sp_databases查看所有可用数据库信息语法格式：[EXECUTE]sp_database(3)使用系统存储过程sp_helpfile查看当前数据库中某个文件信息语法格式：[EXECUTE]sp_helpfile[文件名]【例4-6】查看test数据库中所有文件的信息USEtestGOsp_helpfile运行结果如图4-11所示。4.2.2使用T-SQL语句查看和修改数据库(4)使用系统存储过程sp_helpfilegroup查看当前数据库中某个文件组的信息语法格式：[EXECUTE]sp_helpfilegroup[文件组名]省略文件组名则显示当前数据库中所有文件组的信息，用法同sp_helpfile。4.2.2使用T-SQL语句查看和修改数据库2．使用T-SQL语句修改数据库在应用中通常使用T-SQL语句修改数据库，其语法格式如下：ALTERDATABASE<数据库名称>ADDFILE<数据文件>[,…,n][toFILEGROUP文件组]--增加数据文件到文件组|ADDLOGFILE<日志文件>[,…,n]--增加日志文件|ADDFILEGROPU组文件名--增加文件组|REMOVEFILE逻辑文件名--移去文件|REMOVEFILEGROPU组文件名--删除文件组|MODIFYFILE<数据文件>--修改数据库文件属性|MODIFYNAME=新数据文件名--修改数据库文件名|MODIFYFILEGROUP组文件名--修改文件组属性4.2.2使用T-SQL语句查看和修改数据库【例4-7】将数据库“test”改名为“test1”ALTERDATABASEtestMODIFYNAME=test1提示：一般情况下，不建议用户在创建好的数据后再对数据库名进行修改。因为有许多应用程序可能已经使用了该数据库名称，在更改数据库名后，需要修改相应的应用程序。4.2.2使用T-SQL语句查看和修改数据库【例4-8】在test数据库中添加一个次要数据文件和一个事务日志文件。次要数据文件逻辑名为test2，物理文件名为test2.ndf，初始大小3MB，最大容量10MB，增长速度为1MB；日志文件逻辑名称为testlog1，物理文件名为testlog1.ldf，初始大小为5MB，最大容量为100MB，增长速度为5MB。4.2.2使用T-SQL语句查看和修改数据库4.2.2使用T-SQL语句查看和修改数据库【例4-9】添加文件组test_GroupALTERDATABASEtestADDFILEGROUPtest_Group【例4-10】将一个新的数据文件test3添加到test数据库的test_Group文件组。ALTERDATABASEtestADDFILE--添加次数据文件(NAME=test3,FILENAME='E:\测试数据\test3.ndf')--E盘下新增“测试数据”文件夹tofilegrouptest_group运行效果如图4-13所示。4.2.2使用T-SQL语句查看和修改数据库【例4-9】添加文件组test_GroupALTERDATABASEtestADDFILEGROUPtest_Group【例4-10】将一个新的数据文件test3添加到test数据库的test_Group文件组。ALTERDATABASEtestADDFILE--添加次数据文件(NAME=test3,FILENAME='E:\测试数据\test3.ndf')--E盘下新增“测试数据”文件夹tofilegrouptest_group运行效果如图4-13所示。【例4-11】将数据文件test3从test数据库中移去。ALTERDATABASEtestREMOVEFILEtest34.3任务3：删除数据库

熟练掌握SSMS删除数据库的方法1熟练掌握T-SQL语句删除数据库的方法2任务目标：4.3.1使用SSMS删除数据库利用SSMS删除数据库的操作步骤：（1）在【对象资源管理器】窗口中，展开“数据库”节点，用右键单击要删除的数据库，在弹出的菜单中选择“删除”命令，如图4-14所示。（2）在弹出的【删除对象】窗口中，确认显示的数据库是否为目标数据库，并通过选择复选框决定是否要删除备份及关闭已存在的数据库连接，如图4-15所示。（3）单击“确定”按钮，完成数据库删除操作。数据删除成功后，在【对象资源管理器】窗口中将不会再出现被删除的数据库，相应的数据库文件会在磁盘上的物理位置消失。4.3.2使用T—SQL语句删除数据库使用DROPDATABASE命令删除数据库，其语法格式如下：DROPDATABASE<数据库名>使用DROP语句可以从SQLServer中一次删除一个或多个数据库。【例4-12】删除创建的数据库test。DROPDATABASEtest4.4任务4：分离、附加数据库熟练掌握SSMS分离数据库的方法1熟练掌握SSMS附加数据库的方法2任务目标：4.4.1分离数据库分离数据库的具体步骤如下：（1）确保没有任何用户登录到数据库中。（2）启动SSMS并连接到数据库实例。（3）在【对象资源管理器】窗口中，展开“数据库”节点，用右键单击需要分离的数据库，在弹出的菜单中选额“任务”“分离”命令，如图4-16所示。（4）打开的【分离数据库】窗口中的“数据库名称”栏中显示了所选择的数据库名称，如图4-17所示。要分离数据库选项说明：删除连接：删除所有用户连接。更新统计信息：默认情况下，分离操作将在分离数据库时保留过期的优化统计信息；若要更新现有的优化统计信息，可选中【更新统计信息】复选框。保留全文目录：若选中，可能确保数据库被分离时，不会丢失一起的的全文索引信息。（5）设置完成后，单击“确定”按钮。4.4.1分离数据库图4-17【分离数据库】窗口图4-16选择“任务”-“分离”命令4.4.2附加数据库附加数据库的方法：（1）启动SSMS并连接到数据库实例。（2）在【对象资源管理器】窗口中，展开“数据库”节点，从弹出的菜单中选择“附加”命令，如图4-18所示。（3）在打开的【附加数据库】窗口中，单击“添加”按钮，会弹出【定位数据库文件】窗口，选择数据库所在的磁盘驱动器并展开目录树定位到数据库的.mdf文件，如E:\数据库文件\教务管理数据文件\EDUC.mdf，如图4-19所示。（4）单节【定位数据库文件】窗口中的“确定”按钮，回到【附件数据库】窗口，可以为附加的数据库指定不同的名称和物理位置，如图4-20所示。（5）设置完毕后，单击“确定”按钮。数据库引擎将执行附加数据库任务。如果附加成功，在【对象资源管理器】窗口中将会出现被附加的数据库。4.4.2附加数据库4.5任务5：导出、导入数据库熟练掌握数据库导出的方法1熟练掌握数据库导入的方法2任务目标：4.5任务5：导出、导入数据库通过导入和导出操作可以在SQLServer2012和其它数据源（例如Excel表、Access和Oracle数据库）之间轻松移动数据。“导出”是指将数据从SQLServer表复制到数据文件；“导入”是指将数据文件加载到SQLServer2012表。例如，可以将数据从Excel文件导入到数据库表中，也可以将数据库表中的数据库导出到Excel文件。4.5.1导出数据库在SQLServer2012中，可以在SSMS中将数据库表数据导出。下面以EDUC数据库为例，介绍导出为Excel表格的方法。1．直接导出数据（详见P87）【例4-12】在SSMS中将EDUC数据库中的数据全部导出为Excel表格。2