第3章 数据库创建与维护

李政伟数据库应用李政伟第3章

数据库的创建与管理3.1SQLServer数据库概述3.2创建数据库3.3查看和设置数据库选项3.4修改数据库3.5删除数据库3.6分离和附加数据库3.7复制数据库2/39数据库(Database，DB)：将数据按一定的数据模型组织、描述和储存，具有较小的冗余度，较高的数据独立性和易扩展性，并可被多用户、多应用程序共享的数据集合。（1）在计算机的存储设备上存放的，属于计算机领域的一个术语。（2）一个数据集合。（3）这个数据集合是有结构的，这是和文件系统相比最大的特点之一。（4）这个数据集合是指相关联的数据集合。（5）这个数据集合是合理存放的。3.1SQLServer数据库概述3.1SQLServer数据库概述两大类：系统数据库：存放维护系统正常运行的信息。用户数据库:存放用户自己的信息。4/39系统数据库Master：最重要数据库，用于记录所有系统级信息。包括所有的登录信息、系统设置信息、初始化信息和其他系统数据库及用户数据库的相关信息。如果master数据库不可用，则SQLServer无法启动。Model：用作所有数据库的模板。当用户创建数据库时，系统自动将model数据库中的内容复制到新建数据库中。当发出CREATEDATABASE（创建数据库）时，将通过复制model数据库的内容来创建数据库的第一部分，然后用空页填充新数据库的剩余部分。Msdb：保存关于报警、作业、操作员等信息。5/39系统数据库Tempdb：临时数据库，用于存储用户创建的临时表、用户声明的变量以及用户定义的游标数据等。Tempdb数据库由整个系统的所有数据库使用，不管用户使用哪个数据库，他们所建立的所有临时表和存储过程都存储在tempdb上。SQLServer每次启动时，tempdb数据库被重新建立。当用户与SQLServer断开连接时，其临时表和存储过程自动被删除。Resource：一个只读数据库，包含SQLServer包括的系统对象。系统对象在物理上保留在Resource数据库中，但在逻辑上显示在每个数据库的sys架构中。SQLSERVER样本数据库AdventureWorks、AdventureWorksDW等示例数据库，示例数据基于一个虚拟的公司AdventureWorksCycles，这是一个大型的跨国制造公司，生产金属和复合材料自行车，产品远销北美、欧洲和亚洲市场。AdventureWorks中的某些表的结构和内容与SQLServer2000中Pubs和Northwind数据库的表结构和内容相似，对照联机帮助上的对应关系可以很方便的将SQLServer2000中使用Northwind和Pubs数据库的相关查询转换到AdventureWorks中来。3.1.2SQLServer数据库的组成SQLServer将数据库映射为一组操作系统文件，这些文件被划分为两类：数据文件和日志文件。数据文件包含数据和对象，例如表、索引、存储过程和视图等。日志文件包含恢复数据库中的所有事务需要的信息。数据和日志信息不混合在同一个文件中，一个文件只由一个数据库使用。8/391.数据文件用于存放数据库数据。数据文件又分为：主要数据文件：推荐扩展名是.mdf。包含数据库的系统信息，也可存放用户数据。每个数据库都有且只能有一个主要数据文件。是为数据库创建的第一个数据文件。不能小于3MB。次要数据文件：推荐扩展名是.ndf。一个数据库可以包含0~n个次要数据文件。可与主要数据文件存放在不同地方。所有文件的使用对用户也是透明的。9/392.日志文件推荐扩展名为.ldf。用于存放恢复数据库的所有日志信息。每个数据库有1~n个日志文件。10/393．关于数据的存储分配数据库中数据的存储分配单位是数据页。一页是一块8KB（其中用8060个字节存放数据，另外132个字节存放系统信息）的连续磁盘空间。页是存储数据的最小空间分配单位。不允许表中的一行数据存储在不同页上（varchar(max)、nvarchar(max)、text、ntext、varbinary(max)和image数据类型除外），即行不能跨页存储。日志文件不包含页，而是包含一系列日志记录。11/39示例假设某数据表有10000行数据，每行3000字节，估算其需要的空间:

(10000/2)*8KB=40MB其中，每页中有6000字节用于存储数据，有2060个字节是浪费的。12/393.1.3数据库文件组为便于分配和管理数据文件，将相关的数据文件组织起来，放置到一个文件组中。文件组的概念类似于操作系统中的文件夹。在一个数据库中可以定义多个文件组，然后将数据文件放置到不同的文件组中。SQLServer有两种类型的文件组：主文件组用户定义的文件组13/39主文件组（PRIMARY）系统预先定义好的一个文件组，包含主要数据文件和任何没有明确分配给其他文件组的其他文件。系统表的所有页均分配在主文件组中。14/39用户定义文件组用户可以创建自己的文件组，以将相关数据文件组织起来，便于管理和数据分配。通过在CREATEDATABASE或ALTERDATABASE语句中使用FILEGROUP关键字定义。说明：日志文件不包括在文件组内，一个文件不可以是多个文件组的成员。文件组用途：例如，可以分别在三个磁盘驱动器上创建三个文件Data1.ndf、Data2.ndf和Data3.ndf，然后将它们分配给文件组fgroup1。然后，可以明确地在文件组fgroup1上创建一个表。对表中数据的查询将分散到三个磁盘上，从而提高了性能。15/39使用文件和文件组的一般建议使用文件组将对象放置在特定的物理磁盘上。为性能最大化，应在尽可能多的不同可用物理磁盘上创建文件和文件组，将争夺空间最激烈的对象置于不同的文件组中。将同一连接查询中使用的不同表置于不同文件组中。将最经常访问的表和属于这些表的非聚集索引置于不同的文件组中。不要将事务日志文件和其他文件或文件组置于同一磁盘上。3.1.4数据库文件的属性文件名及其位置：每个文件都有一个逻辑文件名和物理文件名及物理存储位置初始大小：可以指定每个文件的初始大小。增长方式：可以指定文件是否自动增长最大大小：可以指定文件增长的最大空间限制17/393.2创建数据库创建用户数据库前，须先确定数据库名、所有者、文件名、数据文件大小、数据库字符集、是否自动增长以及如何自动增长等信息。在一个SQLServer实例中，最多可创建32767个数据库。数据库名称必须满足标识符规则。在命名数据库时，一定要保证数据库名称简短并有一定的含义。3.2.1用图形化方法创建数据库3.2.2用T-SQL语句创建数据库18/393.2.1用图形化方法创建数据库19/393.2.2用T-SQL语句创建数据库CREATEDATABASEdatabase_name

[ON[PRIMARY][<filespec>[,...n][LOGON{<filespec>[,...n]}]]]

<filespec>::={(NAME=logical_file_name,FILENAME={'os_file_name'|'filestream_path'}[,SIZE=size[KB|MB|GB|TB]][,MAXSIZE={max_size[KB|MB|GB|TB]|UNLIMITED}][,FILEGROWTH=growth_increment[KB|MB|GB|TB|%]])[,...n]}20/39示例1(P66)例1．创建一个名为“mytest”的数据库，其他选项均采用默认设置。 CREATEDATABASEmytest21/39示例2(P66)CREATEDATABASERShDBON(NAME=RShDB,FILENAME='D:\RShDB_Data\RShDB.mdf',SIZE=10,MAXSIZE=30,FILEGROWTH=5)LOGON(NAME=RShDB_log,FILENAME='D:\RShDB_Data\RShDB_log.ldf',SIZE=3,MAXSIZE=12,FILEGROWTH=2)22/39示例3(P67)CREATEDATABASEstudentsONPRIMARY(NAME=students,FILENAME='F:\Data\students.mdf',SIZE=3MB,MAXSIZE=UNLIMITED),(NAME=students_data1,FILENAME='D:\Data\students_data1.ndf',SIZE=5MB,MAXSIZE=10MB,FILEGROWTH=1MB)LOGON(NAME=students_log,FILENAME='F:\Data\students_log.ldf',SIZE=2MB,MAXSIZE=6MB,FILEGROWTH=10%)23/393.3查看和设置数据库选项展开“数据库”节点，在要查看属性的数据库上右击鼠标，然后在弹出的快捷菜单中选择“属性”命令。24/393.4修改数据库3.4.1扩大数据库空间3.4.2收缩数据库空间3.4.3添加和删除数据库文件3.4.4创建和更改文件组25/393.4.1扩大数据库空间ALTERDATABASEdatabase_name{

<add_or_modify_files>}<add_or_modify_files>::={

ADDFILE<filespec>[,...n]

[TOFILEGROUP{filegroup_name

|DEFAULT}]

|ADDLOGFILE<filespec>[,...n]

|MODIFYFILE<filespec>26/39示例例1．为“RShDB”数据库添加一个新的数据文件，逻辑文件名为：RShDB_Data2，物理存储位置为：E:\Data文件夹下，物理文件名为：RShDB_Data2.ndf,初始大小为6MB，不自动增长。ALTERDATABASERShDBADDFILE(NAME=RShDB_Data2,FILENAME='E:\Data\RShDB_Data2.ndf',SIZE=6MB,FILEGROWTH=0)27/39示例例2．修改Students数据库中主数据文件Students的初始大小，将其初始大小改为8MB。ALTERDATABASEStudentsMODIFYFILE(NAME=students,SIZE=8MB)28/39示例例3．为Students数据库加添加一个新的日志文件，逻辑文件名为：students_log1，物理存储位置为：D:\Data文件夹下，物理文件名为：students_log1.ldf,初始大小为2MB，每次增加1MB，最多增加到10MB。ALTERDATABASEStudentsADDLOGFILE(NAME=students_log1,FILENAME='D:\Data\students_log1.ldf',SIZE=2MB,FILEGROWTH=1MB,MAXSIZE=10MB)29/393.4.2收缩数据库空间自动收缩手工收缩用图形化方法用T-SQL语句30/39收缩数据库DBCCSHRINKDATABASE('database_name'|0[,target_percent][,{NOTRUNCATE|TRUNCATEONLY}])31/39收缩文件DBCCSHRINKFILE('file_name'{[,EMPTYFILE]|[[,target_size][,{NOTRUNCATE|TRUNCATEONLY}]]})32/39示例例4．收缩Students数据库，使该数据库中所有的文件都有20%的可用空间。 DBCCSHRINKDATABASE（students,20）例5．将Students数据库中的students_data1文件收缩到4MB。DBCCSHRINKFILE(students_data1,4)33/39数据库文件使用情况34/39添加和删除数据库文件添加数据库文件使用ALTERDATABASE语句。删除数据库文件ALTERDATABASEdatabase_nameREMOVEFILElogical_file_name只有当文件中没有数据或事务日志信息，文件完全为空时，才可以从数据库中删除该文件。35/39示例例6．删除Students数据库中的Students_data1文件。ALTERDATABASEStudentsREMOVEFILEStudents_data1例7．删除Students数据库中的Students_log1文件。ALTERDATABASEStudentsREMOVEFILEStudents_log136/393.4.4创建和更改文件组用图形化方法：在数据库属性窗口中；用T-SQL语句。37/39创建和更改文件组语句ALTERDATABASEdatabase_name

{|ADDFILEGROUPfilegroup_name

|REMOVEFILEGROUPfilegroup_name

|MODIFYFILEGROUPfilegroup_name{<filegroup_updatability_option>|DEFAULT|NAME=new_filegroup_name

}}<filegroup_updatability_option>::={{READ_ONLY|READ_WRITE}}38/39示例例1．为students数据库定义一个新文件组，文件组名为：NewFileGroup1，同时在该文件组中新添加两个数据文件，逻辑名分别为：students_dat1和students_dat2，初始大小分别为：4MB和6MB，均存放在D:\Data文件夹下，不自动增长。（1）创建文件组ALTERDATABASEStudentsADDFILEGROUPNewFileGroup1（2）添加新数据文件ALTERDATABASEstudentsADDFILE(NAME=students_dat1,FILENAME='D:\Data\students_dat1.ndf',SIZE=4MB,FILEGROWTH=0),(NAME=students_dat2,FILENAME='D:\Data\students_dat2.ndf',SIZE=6MB,FILEGROWTH=0)TOFILEGROUPNewFileGroup139/39示例例2．将Students数据库中的NewFileGroup1文件组设为默认文件组。ALTERDATABASEStudentsMODIFYFILEGROUPNewFileGroup1DEFAULT40/393.5删除数据库DROPDATABASEdatabase_name[,…n]例1．删除单个数