《第4章数据库的创建与管理》由会员分享，可在线阅读，更多相关《第4章数据库的创建与管理（42页珍藏版）》请在文档大全上搜索。
1、第第4章章 数据库的创建与管理数据库的创建与管理l本章导读： 数据库是SQL Server 系统中的最基本对象，从体系结构上说，它有物理和逻辑两种结构。物理数据库是由一系列数据文件和事务日志文件组成的文件系统，而每个文件又都有两种名称：逻辑名和物理名。逻辑数据库相当于一个包含表、视图等一系列数据库对象的容器。l知识要点：l数据库体系结构l数据库的创建l数据库的修改l数据库的删除l数据库的压缩l数据库的附加和分离4.1 数据库的体系结构数据库的体系结构 在SQL Server中，数据库由两部分组成：物理数据库和逻辑数据库。物理数据库是数据库面向操作系统的物理文件部分，由一系列文件组成，如数据文件
2、和事物日志文件；逻辑数据库是数据库面向用户的可视部分，由一系列数据库对象组成，如表、视图、存储过程、扩展存储过程、用户自定义函数、用户自定义数据类型、用户、角色、规则、默认值等。4.1.1 文件名l4.1.2 数据库文件l4.1.3 数据库文件组4.1.1 文件名文件名在SQL Server中，每个文件都有两种名称，分别称为逻辑文件名和物理文件名。1逻辑文件名简称逻辑名，是指在所有Transact-SQL语句中引用文件时必须使用的名称。逻辑文件名必须遵守SQL Server标识符的命名规则，且对数据库必须是唯一的。SQL Server中标识符规则如下：（1）长度不超过128个字符；（2）开头字
3、母为az或AZ、#、_、以及来自其它语言的字母字符；（3）后续字母可以是az或AZ、#、_、以及来自其它语言的字母字符、09、$；（4）不允许嵌入空格或其它特殊字符；（5）不允许与保留字同名；（6）在T-SQL语句使用标识符时，必须用双引号或方括号封装不符合规则的标识符；4.1.1 文件名文件名注意：以符号注意：以符号、#开头的标识符具有特殊的含义，以开头的标识符具有特殊的含义，以开头的标识符表开头的标识符表示局部变量，以示局部变量，以开头的标识符表示全局变量；以开头的标识符表示全局变量；以#开头的标识符表开头的标识符表示临时表或过程，以示临时表或过程，以#开头的标识符表示全局临时对象。开头的
4、标识符表示全局临时对象。2物理文件名物理文件名简称物理名，也称物理存储文件名，是指文件在磁盘上的存储路径及文件简称物理名，也称物理存储文件名，是指文件在磁盘上的存储路径及文件名称的统称，物理文件名遵从操作系统文件名的命名规则。名称的统称，物理文件名遵从操作系统文件名的命名规则。4.1.2 数据库文件数据库文件在SQL Server中，每个数据库都是由一系列文件组成，包括数据文件和事物日志文件两类，而数据文件又可分为主数据文件和辅数据文件两大类。1主数据文件（Primary Database file）简称主文件，用来存储数据库的启动信息和部分或全部数据，每个数据库有且仅有一个主数据文件。主数据
5、文件总是位于主文件组中，它代表数据库的起点，并且提供指针指向数据库中的其它文件。使用时，主数据文件包含两种名称：逻辑文件名和物理文件名，其中逻辑文件名无扩展名，物理存储文件名的扩展名默认为.mdf。2辅数据文件（Secondary Database File）简称次文件，用于存储主数据文件中未存储的剩余数据和数据库对象。一个数据库既可以没有辅数据文件，也可以有若干个辅数据文件。辅数据文件既可以位于主文件组，也可以位于辅文件组中。使用时，辅数据文件也包含两种名称：逻辑文件名和物理文件名，其中逻辑文件名无扩展名，物理存储文件名的扩展名默认为.ndf。3事务日志文件（Transaction Log
6、File）简称日志文件，用于存储对数据库任何操作过程的事务日志，以保证数据的一致性和完整性，有利于数据库的恢复。每个数据库都必须至少含有一个事务日志文件，也可以含有多个事务日志文件。事物日志文件不属于任何文件组。使用时，事务日志文件也包含两种名称：逻辑文件名和物理文件名，其中逻辑文件名无扩展名，物理存储文件名的扩展名默认为.ldf。注意：在SQL Server中，一个数据库可以包含多个文件，不同文件也可以分别存储在不同分区磁盘上，但是一个文件只能存储于一个数据库内。4.1.3 数据库文件组数据库文件组 为了管理和维护数据库，还可将相关数据文件集合起来形成一个逻辑整体，构成文件组。与数据文件相似
7、，文件组也分为主文件组和辅助文件组。一个文件只能属于一个文件组，一个文件组也只能属于一个数据库。1主文件组主文件组（primary）是数据库系统提供的，每个数据库有且仅有一个主文件组，主文件组中包含了所有系统表，主数据文件和未指定文件组的其它数据文件。2辅文件组辅文件组是用户自行定义的文件组，每个数据库既可以没有辅文件组，也可以包含若干个辅文件组，辅文件组可以存储用户指定的辅数据文件。3默认文件组默认文件组是没有分配文件组的用户自定义对象的首选文件组，其中包含在创建时没有分配文件组的所有表和索引的页。每个数据库只能有一个默认文件组。注意：默认文件组和主文件组不是同一概念，数据库建立初始时，主文
8、件组是默认文件组，db_owner固定数据库角色成员可以通过命令将用户定义的文件组指定为后续数据文件的默认文件组。4.1.4 SQL Server 2005系统数据库系统数据库 SQL Server 2005数据库分为：用户数据库、数据库快照和系统数据库。其中用户数据库是用户创建并存储用户数据的数据库；数据库快照是数据库（源数据）的只读、静态视图，每个数据库快照都与创建数据库快照时存在的源数据库在事物上一致；系统数据库存储了SQL Server的整体信息和使用规则，是SQL Server 2005的运行基础。SQL Server提供了4个系统数据库，分别是：master，tempdb，mode
9、l，msdb。1master数据库master数据库是SQL Server 2005中最重要的数据库，记录了SQL Server 2005服务器的系统信息，包括所有账户和密码，磁盘空间、文件分配和使用、系统级的配置参数、初始化信息、系统中其它系统数据库和用户数据库的相关信息等。因此，对master数据库的任何修改都可能影响系统的运行。2model数据库model数据库存储了所有用户数据库和tempdb数据库的模板。它包含将要复制到每个用户数据库中去的系统表。3msdb数据库msdb数据库主要为SQL Server 2005代理服务提供复制、任务调度以及管理警报等活动。该数据库常被用来存储所有备
10、份历史和通过调度任务排除故障。4tempdb数据库tempdb数据库是一个临时数据库，它为所有的临时表、临时存储过程以及临时操作提供存储空间，并允许所有连接SQL Server服务器的用户都能使用。服务器重启会重建tempdb数据库。也就是说，其中数据是暂时的，每次重启都会导致以前数据的丢失。4.2 数据库的创建数据库的创建在SQL Server中，数据行存放在页上，页存放在范围上，一个表有若干个范围组成，而若干个表组成了数据库。创建数据库的过程实际上是确定数据库的名称、数据库相关文件的存储路径、存储空间大小及其相关属性的设置。创建数据库既可以使用T-SQL语句，也可以使用SSMS管理工具。4
11、.2.1 使用SSMS创建数据库4.2.1 使用使用SSMS创建数据库创建数据库【例4-1】 使用SSMS管理工具创建数据库。操作步骤如下：（1）启功SSMS，在“对象资源管理器”窗格中展开根目录的树形结构，直至“数据库”，右击“数据库”，弹出“新建数据库”快捷菜单，如图4-1所示。 4.2.1 使用使用SSMS创建数据库创建数据库（2）单击释放后，打开“新建数据库”窗口的“常规”界面，在“数据库名称”文本框中输入数据库的名称为jxgl，“数据库文件”区域中显示了新建“数据库”对应的数据文件和事务日志文件的相关属性的默认值，如图4-2所示。图4-2 “新建数据库”对话框1 4.2.1 使用使用
12、SSMS创建数据库创建数据库（3）要继续增加数据文件（辅数据文件）或事务日志文件，单击“添加”按钮，“数据文件”中会新增一行，依次设置逻辑名称、文件类型、文件组（数据文件的用户自定义文件组）、初始大小、自动增长、路径，如图4-4和图4-5所示。图4-4 “新建数据库”对话框2 4.2.1 使用使用SSMS创建数据库创建数据库图4-5 “新建数据库”对话框3 4.2.1 使用使用SSMS创建数据库创建数据库（5）单击“确定”按钮，返回SSMS对话框，如图4-6所示。图4-6 SSMS对话框 4.2.2 使用使用T-SQL语句创建数据库语句创建数据库通过T-SQL语句中的create databa
13、se命令创建数据库，该命令的语法格式如下：create database on primary ,n ,n log on ,n for load|for attach其中，:= ( ,size=初始大小 ,maxsize=最大限制|unlimited ,filegrowth=文件增长量|增长百分比) ,n:= filegroup default ,n功能：创建一个指定数据库名称的数据库。4.2.2 使用使用T-SQL语句创建数据库语句创建数据库说明：（1）on：用于指明数据文件（组）的明确定义；（2）primary：指定主文件组；（3）name：指定逻辑文件名，必选属性；（4）filename
14、：指定物理文件（操作系统文件）的路径和文件名，必选属性；（5）size：指定文件的初始大小，如果没有指定，则默认值为3M；（6）maxsize：指定文件最大大小，其中关键字unlimited表示文件的大小不受限制；（7）filegrowth：指定文件增长方式，可以按兆字节增长也可以按百分比增长；（8）log on：指定数据库日志文件的属性，其定义格式与数据文件的格式定义相同；（9）filegroup：指定文件组的属性，default表示该文件组为数据库中默认文件组。 4.2.2 使用使用T-SQL语句创建数据库语句创建数据库【例4-3】 创建一个数据库mn4，主文件逻辑名为mn4_data，物
15、理名为d:mnmn4.mdf。create database mn4on (name=mn4_data,filename=d:mnmn4.mdf)【例4-4】 创建一个数据库mn5，其中：（1）主数据文件逻辑文件名为mn5_data，物理文件名为d:mnmn5.mdf，存储空间初始大小为10MB，最大存储空间为不限制，文件增长量为2MB；（2）日志文件逻辑名为mn5_log，物理名为d:mnmn5_log.ldf，存储空间初始大小为5MB，最大存储空间为10MB，文件增长量为5%。create database mn5on (name=mn5_data,filename=d:mnmn5.mdf
16、,size=10,maxsize=unlimited,filegrowth=2)log on (name=mn5_log,filename=d:mnmn5_log.ldf,size=5,maxsize=10,filegrowth=5%) 4.2.2 使用使用T-SQL语句创建数据库语句创建数据库【例4-5】 创建一个数据库mn6，其中：（1）主文件逻辑名为mn6a_data，物理名为d:mnmn6a.mdf，存储空间初始大小为默认值，最大存储空间为不限制，文件增长量为2MB；（2）次文件逻辑名为mn6b_data，物理名为d:mnmn6b.ndf，存储空间初始大小为默认值，最大存储空间为不限制
17、，文件增长量为2MB；（3）日志文件1逻辑名为mn6a_log，物理名为d:mnmn6a_log.ldf，存储空间初始大小为5MB，最大存储空间为10MB，文件增长量为5%；（4）日志文件2逻辑名为mn6b_log，物理名为d:mnmn6b_log.ldf，存储空间初始大小为5MB，最大存储空间为10MB，文件增长量为5%。create database mn6on (name=mn6a_data,filename=d:mnmn6a.mdf,maxsize=unlimited,filegrowth=2),(name=mn6b_data,filename=d:mnmn6b.ndf,maxsize
18、=unlimited,filegrowth=2)log on (name=mn6a_log,filename=d:mnmn6a_log.ldf,size=5,maxsize=10,filegrowth=5%),(name=mn6b_log,filename=d:mnmn6b_log.ldf,size=5,maxsize=10,filegrowth=5%) 4.2.2 使用使用T-SQL语句创建数据库语句创建数据库注意：（1）关键字on引导的是数据文件，而关键字log on 引导的是事务日志文件；（2）每一个文件的属性信息单独包含在一对括号（()）内，各属性之间用逗号（,）分割；（3）同类型的文
19、件之间用逗号（,）分割。 4.2.2 使用使用T-SQL语句创建数据库语句创建数据库【例4-6】 创建一个数据库mn7，包含一个主文件组和两个次文件组，其中：（1）主文件的逻辑名为mn7a_data，物理名为d:mnmn7a.mdf，其它取默认值；（2）次文件1的逻辑名为mn7b_data，物理名为d:mnmn7b.ndf，其它取默认值，存放于文件组group1；（3）次文件2的逻辑名为mn7c_data，物理名为d:mnmn7c.ndf，其它取默认值，存放于文件组group1；（4）次文件3的逻辑名为mn7d_data，物理名为d:mnmn7d.ndf，其它取默认值，存放于文件组group2
20、；（5）日志文件1的逻辑名为mn7a_log，物理名为d:mnmn7a_log.ldf，存储空间初始大小为5MB，最大存储空间为10MB，文件增长量为5%；（6）日志文件2逻辑名为mn7b_log，物理名为d:mnmn7b_log.ldf，存储空间初始大小为5MB，最大存储空间为10MB，文件增长量为5%。create database mn7on primary(name=mn7a_data,filename=d:mnmn7a.mdf),filegroup group1(name=mn7b_data,filename=d:mnmn7b.ndf),(name=mn7c_data,filenam
21、e=d:mnmn7c.ndf),filegroup group2(name=mn7d_data,filename=d:mnmn7d.ndf)log on (name=mn7a_log,filename=d:mnmn7a_log.ldf,maxsize=10,filegrowth=5%),(name=mn7b_log,filename=d:mnmn7b_log.ldf,maxsize=10,filegrowth=5%)4.2.2 使用使用T-SQL语句创建数据库语句创建数据库4.3 数据库的修改数据库的修改数据库创建后，用户可以自行查看和修改数据库相关属性，查看、修改数据库的方法有两种：使用SS
22、MS管理工具和使用T-SQL语句。4.3.1 使用SSMS修改数据库【例4-7】 通过SSMS查看、修改数据库。（1）启动SSMS，在“对象资源管理器”窗格中展开SHUJU（服务器实例）jxgl（目标数据库），右击jxgl，在弹出快捷菜单中选择“属性”命令，弹出“数据库属性-jxgl”对话框，如图4-8所示。在常规选项卡中，用户可以查看数据库的名称、状态、所有者、创建日期、大小等基本信息。 4.3.1 使用使用SSMS修改数据库修改数据库（2）单击“文件”选项卡，弹出如图4-9所示的“文件”对话框，用户可以查看、添加、删除文件，以及修改文件属性，但不能修改现有文件的路径及文件名。 图4-9 “
23、数据库”属性的“文件组”界面注意：数据库建好后，如果想修改数据库文件的初始大小属性，则只能变大，不能变小。4.3.1 使用使用SSMS修改数据库修改数据库（3）单击“文件组”选项卡，弹出如图4-10所示的“文件组”对话框。用户可以查看、添加、删除文件组。图4-10 “数据库”属性的“事务日志”界面 4.3.2 使用使用T-SQL语句修改数据库语句修改数据库使用T-SQL语句修改数据库的语法格式如下：alter database add file ,n to filegroup /*添加数据文件*/|add log file ,n/*添加日志文件*/|remove file /*删除文件*/|a
24、dd filegroup /*添加辅文件组*/|remove filegroup /*删除数据库*/|modify file /*修改文件属性*/|modify name = /*重命名数据库*/|modify filegroup |/*修改文件组*/其中，:= (name=逻辑文件名 ,size=初始大小 ,maxsize=最大限制|unlimited ,filegrowth=文件增长量) ,n4.3.2 使用使用T-SQL语句修改数据库语句修改数据库说明：（1）：是要更改的数据库名称；（2）add file：添加辅助数据文件，该文件属性由后面的指定；（3）add log file：添加新的
25、日志文件，该文件属性由后面的指定；（4）remove file：删除辅助数据文件和日志文件及其描述；（5）add filegroup：添加次要文件组；（6）remove filegroup：删除次要文件组，删除文件组之前，要保证这个文件组为空，否则先删除这个文件组中的文件；（7）modify file：修改name指定文件的相关属性，包括size，maxsize，filegrowth；（8）modify name：修改数据库名，即重命名数据库；（9）modify filegroup：修改文件组属性或文件组名称。文件组属性取值如表4-1所示。4.3.2 使用使用T-SQL语句修改数据库语句修改数
26、据库表4-1 文件组属性取值及其含义名称名称功能功能Readonly只读，不允许更改文件组中的对象，主文件组不能设置只读Readwrite读写，允许更改文件组中的对象，具有排他权限的用户才能设置文件组读写权限Default默认文件组，将文件组设置为默认文件组4.3.2 使用使用T-SQL语句修改数据库语句修改数据库【例4-8】 将数据库mn4的主文件最大大小改为10MB，增长方式改为2MB。alter database mn4 modify file (name=mn4_data,maxsize=10,filegrowth=2)go【例4-9】 添加一个包含两数据文件的文件组group1和一个
27、日志文件到数据库mn4中。alter database mn4 add filegroup group1goalter database mn4 add file(name=mn4a_data,filename=d:mnmn4a.ndf),(name=mn4b_data,filename=d:mnmn4b.ndf) to filegroup group1goalter database mn4 add log file (name=mn4a_log,filename=d:mnmn4a.ldf)4.3.2 使用使用T-SQL语句修改数据库语句修改数据库【例4-10】 从mn4中删除文件组grou
28、p1。alter database mn4 remove file mn4a_datagoalter database mn4 remove file mn4b_datagoalter database mn4 remove filegroup group1注意：主文件组和有数据文件的文件组不能删除。【例4-11】 从数据库mn4中删除一个日志文件，并将数据库改名moni4。alter database mn4 remove file mn4_loggoalter database mn4 modify name=moni4go注意：不能删除主日志文件。4.4 数据库的删除数据库的删除当数据库
29、及其中的数据失去利用价值后，可以删除数据库，以释放被占用的磁盘空间。删除数据库，会删除数据库中的所有数据和该数据库所使用的所有磁盘文件。删除数据库的方法有两种：使用SSMS管理工具和使用T-SQL语句。l4.4.1 使用SSMS删除数据库l4.4.2 利用T-SQL语句删除4.4.1 使用使用SSMS删除数据库删除数据库使用SSMS删除数据库的方法如下：启动SSMS，在“对象资源管理器”窗格中逐级展开控制台根目录，选择要删除的数据库。直接按键盘的“delete”键，或执行“操作”“删除”命令，或在右击后的快捷捷菜单中选择“删除”命令，打开“删除对象”对话框，单击“确定”按钮即可，如图4-11所
30、示。图4-11 “删除对象”对话框 4.4.2 利用利用T-SQL语句删除语句删除语法格式如下：drop database 【例4-12】 删除一个数据库moni3。drop database moni34.5 数据库的压缩数据库的压缩压缩数据库是指将分配给数据库多余的存储空间释放出来，以节约磁盘空间。压缩数据库通常也有两种方法：使用SSMS管理工具和使用T-SQL语句。T-SQL语句有2种压缩方法。（1）压缩数据库：dbcc shrinkdatabase；l（2）压缩数据库中的数据文件：dbcc shrinkfile；l4.5.1 压缩数据库l4.5.2 压缩数据库文件4.5.1 压缩数据库
31、压缩数据库压缩数据库的语法格式如下：格式：dbcc shrinkdatabase (数据库名,压缩目标百分比数值,notruncate | truncateonly)功能：将指定的数据库压缩到原来的百分比数。说明：（1）数据库名：指定要压缩的数据库名称；（2）压缩目标百分比数值：可选参数，指明被压缩的文件为原来的文件百分比大小；（3）notruncate：保留数据文件中的空闲空间；（4）truncateonly：将数据文件中的空闲空间释放给操作系统。【例4-13】 压缩数据库mn5为原来的10%。dbcc shrinkdatabase (mn5,10)4.5.2 压缩数据库文件压缩数据库文件压
32、缩数据库文件的语法格式如下：dbcc shrinkfile(file_name|file_id,target_size|,emptyfile|notruncate|truncateonly)功能：压缩当前数据库中的指定的数据文件到一具体目标大小。说明：（1）file_id：指定要压缩的文件的鉴别号（Identification number，即ID）。文件的ID号可以通过函数file_id()或系统存储过程Sp_helpdb得到；（2）target_size：指定文件压缩后的大小，以MB为单位。如果不指定此选项，SQL Server 会尽最大可能地缩减文件；（3）emptyfile：指明此文件
33、不再使用，将移动所有在此文件中的数据到同一文件组中的其它文件中去。执行带此参数的命令后，此文件就可以用alter database命令来删除了；（4）参数notruncate 和truncateonly 与dbcc shrinkdatabase 命令中的含义相同。【例4-14】 压缩数据库mn5中的数据库文件mn5_data的大小到5MB。use mn5dbcc shrinkfile (mn5_data,5)4.6 数据库附加和分离数据库附加和分离数据库文件既可以在服务器停止的状态下象普通文件一样实现转移，也可以在服务器启动的状态下实现转移，分离和附加就是在不停止服务器的基础上，将数据库从一台
34、计算机转移到另一台计算机的方法，从而实现在另一台计算机上使用和管理该数据库。4.6.1 分离4.6.2 附加4.6.1 分离分离分离是使数据库与当前SQL Server服务器脱离关系。与删除数据库的区别在于：数据库分离后，数据库文件（.mdf、.ndf和.ldf文件）仍然存储在当前服务器所在的计算机硬盘上，只不过用户无法在当前服务器上使用该数据库。使用SSMS分离数据库的方法如下：（1）启动SSMS，在“对象资源管理器”窗格中逐级展开控制台目录，直到出现要分离的数据库，如数据库名称“jxgl”，右击该数据库的名称，在弹出快捷菜单选择“所有任务”“分离数据库”，打开“分离数据库”对话框，如图4-
35、12所示。4.6.1 分离分离 图图4-12 “分离数据库分离数据库”对话对话框框4.6.2 附加附加数据库只有脱离了SQL Server服务器，才能被自由复制和转移到其它计算机上，然后通过附加的的方法将数据库文件附加到其它SQL Server服务器上。使用SSMS附加数据库的方法如下：（1）启动SSMS，在“对象资源管理器”窗格中展开SQL Server，右击“数据库”，弹出快捷菜单，如图4-13所示，选择“附加”命令，打开“附加数据库”对话框，如图4-14所示。图4-13 “附加数据库”对话框1 4.6.2 附加附加 图图4-14 “附加数据库附加数据库”对话框对话框4.6.2 附加附加
36、图4-28 “附加数据库”对话框（2）单击“添加”按钮，弹出“定位数据文件”对话框，如图4-15所示。搜索要附加数据库的.mdf文件，选中正确的.mdf文件（如jxgl_data.mdf）后，数据库中所有文件自动进入“附加数据库”对话框中，如图4-16所示。 图4-16 “附加数据库”对话框34.6.2 附加附加（3）单击“确定”按钮，返回SSMS，完成数据库jxgl的附加。注意：选错文件，则弹出另一对话框，给予提示信息：“无法为此请求检索数据”。图4-29 “控制台目录”对话框图4-30 “附加数据库顺利完成”对话框本章小结本章小结l本章主要介绍数据库的创建和管理，数据库的创建和管理是用户使用SQL Server 2005的最基本操作。创建和管理数据库既可以通过SSMS管理工具执行，也可以通过T-SQL语句执行。两种操作方法相比较而言，尤其是数据库的修改，使用T-SQL语句较为复杂。
