第3章 数据库创建与维护

1、李政伟数据库应用数据库应用第第3章章 数据库的创建与管理数据库的创建与管理v3.1 SQL Server3.1 SQL Server数据库概述数据库概述v3.2 3.2 创建数据库创建数据库v3.3 3.3 查看和设置数据库选项查看和设置数据库选项v3.4 3.4 修改数据库修改数据库v3.5 3.5 删除数据库删除数据库v3.6 3.6 分离和附加数据库分离和附加数据库v3.7 3.7 复制数据库复制数据库2/39v数据库数据库(Database(Database，DB)DB)：将数据按一定的数：将数据按一定的数据模型组织、描述和储存，具有较小的冗据模型组织、描述和储存，具有较小的冗余度，较

2、高的数据独立性和易扩展性，并余度，较高的数据独立性和易扩展性，并可被多用户、多应用程序共享的可被多用户、多应用程序共享的数据集合数据集合。（1 1）在）在计算机的存储设备计算机的存储设备上存放的，属于计上存放的，属于计算机领域的一个术语。算机领域的一个术语。（2 2）一个）一个数据集合数据集合。（3 3）这个数据集合是）这个数据集合是有结构有结构的，这是和文件的，这是和文件系统相比最大的特点之一。系统相比最大的特点之一。（4 4）这个数据集合是指）这个数据集合是指相关联相关联的数据集合。的数据集合。（5 5）这个数据集合是）这个数据集合是合理存放合理存放的。的。3.1 SQL Server数据

3、库概述数据库概述3.1 SQL Server数据库概述数据库概述v两大类：两大类： 系统数据库系统数据库：存放维护系统正常运行的信息。：存放维护系统正常运行的信息。 用户数据库用户数据库: :存放用户自己的信息。存放用户自己的信息。4/39系统数据库系统数据库v Master：最重要数据库，用于记录所有系统级信息。包括：最重要数据库，用于记录所有系统级信息。包括所有的登录信息、系统设置信息、初始化信息和其他系统数所有的登录信息、系统设置信息、初始化信息和其他系统数据库及用户数据库的相关信息。如果据库及用户数据库的相关信息。如果 master 数据库不可数据库不可用，则用，则 SQL Serve

4、r 无法启动。无法启动。v Model：用作所有数据库的模板。当用户创建数据库时，：用作所有数据库的模板。当用户创建数据库时，系统自动将系统自动将model数据库中的内容复制到新建数据库中。数据库中的内容复制到新建数据库中。当发出当发出 CREATE DATABASE（创建数据库）时，将通过（创建数据库）时，将通过复制复制 model 数据库的内容来创建数据库的第一部分，然数据库的内容来创建数据库的第一部分，然后用空页填充新数据库的剩余部分。后用空页填充新数据库的剩余部分。v Msdb：保存关于报警、作业、操作员等信息。：保存关于报警、作业、操作员等信息。5/39系统数据库系统数据库vTemp

5、db：临时数据库，用于存储用户创建的临时：临时数据库，用于存储用户创建的临时表、用户声明的变量以及用户定义的游标数据等。表、用户声明的变量以及用户定义的游标数据等。Tempdb数据库由整个系统的所有数据库使用，不数据库由整个系统的所有数据库使用，不管用户使用哪个数据库，他们所建立的所有临时表和管用户使用哪个数据库，他们所建立的所有临时表和存储过程都存储在存储过程都存储在tempdb上。上。SQL Server每次每次启动时，启动时，tempdb数据库被重新建立。当用户与数据库被重新建立。当用户与SQL Server断开连接时，其临时表和存储过程自动断开连接时，其临时表和存储过程自动被删除。被删

6、除。vResource ：一个只读数据库，包含一个只读数据库，包含 SQL Server 包括的系统对象。系统对象在物理上保留在包括的系统对象。系统对象在物理上保留在 Resource 数据库中，但在逻辑上显示在每个数据数据库中，但在逻辑上显示在每个数据库的库的 sys 架构中。架构中。SQL SERVER样本数据库样本数据库v AdventureWorks、AdventureWorksDW等示例等示例数据库，示例数据基于一个虚拟的公司数据库，示例数据基于一个虚拟的公司Adventure Works Cycles，这是一个大型的跨国制造公司，生产，这是一个大型的跨国制造公司，生产金属和复合材料

7、自行车，产品远销北美、欧洲和亚洲市场金属和复合材料自行车，产品远销北美、欧洲和亚洲市场。v AdventureWorks中的某些表的结构和内容与中的某些表的结构和内容与SQL Server 2000中中Pubs和和Northwind数据库的表结构数据库的表结构和内容相似，对照联机帮助上的对应关系可以很方便的将和内容相似，对照联机帮助上的对应关系可以很方便的将SQL Server 2000中使用中使用Northwind和和Pubs数据库数据库的相关查询转换到的相关查询转换到AdventureWorks中来。中来。3.1.2 SQL Server数据库的组成数据库的组成vSQL Server将数据

8、库映射为一组操作系将数据库映射为一组操作系统文件，这些文件被划分为两类：数据文件统文件，这些文件被划分为两类：数据文件和日志文件。和日志文件。v数据文件数据文件包含数据和对象，例如表、索引包含数据和对象，例如表、索引、存储过程和视图等。、存储过程和视图等。v日志文件日志文件包含恢复数据库中的所有事务需包含恢复数据库中的所有事务需要的信息。要的信息。v数据和日志信息不混合在同一个文件中，数据和日志信息不混合在同一个文件中，一个文件只由一个数据库使用。一个文件只由一个数据库使用。8/391.数据文件数据文件v 用于存放数据库数据。数据文件又分为：用于存放数据库数据。数据文件又分为：v 主要数据文件

9、主要数据文件： 推荐扩展名是推荐扩展名是.mdf。 包含数据库的系统信息，也可存放用户数据。包含数据库的系统信息，也可存放用户数据。 每个数据库都有且只能有一个主要数据文件。每个数据库都有且只能有一个主要数据文件。 是为数据库创建的第一个数据文件。是为数据库创建的第一个数据文件。 不能小于不能小于3MB。v 次要数据文件次要数据文件： 推荐扩展名是推荐扩展名是.ndf。 一个数据库可以包含一个数据库可以包含0n个次要数据文件。个次要数据文件。 可与主要数据文件存放在不同地方。可与主要数据文件存放在不同地方。v 所有文件的使用对用户也是透明的。所有文件的使用对用户也是透明的。9/392.日志文件

10、日志文件v推荐扩展名为推荐扩展名为.ldf。v用于存放恢复数据库的所有日志信息。用于存放恢复数据库的所有日志信息。v每个数据库有每个数据库有1n个日志文件。个日志文件。10/393关于数据的存储分配关于数据的存储分配v数据库中数据的存储分配单位是数据页。数据库中数据的存储分配单位是数据页。一页是一块一页是一块8KB（其中用（其中用8060个字节存个字节存放数据，另外放数据，另外132个字节存放系统信息）个字节存放系统信息）的连续磁盘空间。的连续磁盘空间。v页是存储数据的最小空间分配单位。页是存储数据的最小空间分配单位。v不允许表中的一行数据存储在不同页上（不允许表中的一行数据存储在不同页上（v

11、archar(max)、nvarchar(max)、text、ntext、varbinary(max)和和image数据类型除外），即数据类型除外），即行不能跨页存行不能跨页存储储。v日志文件不包含页，而是包含一系列日志日志文件不包含页，而是包含一系列日志记录记录。11/39示例示例v假设某数据表有假设某数据表有10000行数据，每行行数据，每行3000字节，估算其需要的空间字节，估算其需要的空间: (10000/2) * 8KB = 40MBv其中，每页中有其中，每页中有6000字节用于存储数据，有字节用于存储数据，有2060个字节是浪费的。个字节是浪费的。12/393.1.3 数据库文件组

12、数据库文件组v为便于分配和管理为便于分配和管理数据文件数据文件，将相关的数据，将相关的数据文件组织起来，放置到一个文件组中。文件组织起来，放置到一个文件组中。v文件组的概念类似于操作系统中的文件夹。文件组的概念类似于操作系统中的文件夹。v在一个数据库中可以定义多个文件组，然后在一个数据库中可以定义多个文件组，然后将数据文件放置到不同的文件组中。将数据文件放置到不同的文件组中。vSQL Server有两种类型的文件组：有两种类型的文件组： 主文件组主文件组 用户定义的文件组用户定义的文件组13/39主文件组（主文件组（PRIMARY）v系统预先定义好的一个文件组，系统预先定义好的一个文件组，v包

13、含主要数据文件和任何没有明确分配给其他包含主要数据文件和任何没有明确分配给其他文件组的其他文件。文件组的其他文件。v系统表的所有页均分配在主文件组中。系统表的所有页均分配在主文件组中。14/39用户定义文件组用户定义文件组v用户可以创建自己的文件组，以将相关数据文件组织起用户可以创建自己的文件组，以将相关数据文件组织起来，便于管理和数据分配。来，便于管理和数据分配。v通过在通过在CREATE DATABASE或或ALTER DATABASE语句中使用语句中使用FILEGROUP 关键字定义。关键字定义。v说明：说明： 日志文件不包括在文件组内，日志文件不包括在文件组内， 一个文件一个文件不不可

14、以是多个文件组的成员。可以是多个文件组的成员。v 文件组用途文件组用途：例如，可以分别在：例如，可以分别在三个磁盘驱动器三个磁盘驱动器上创建三个上创建三个文件文件 Data1.ndf、Data2.ndf 和和 Data3.ndf，然后将，然后将它们分配给文件组它们分配给文件组 fgroup1。然后，可以明确地在文件组。然后，可以明确地在文件组 fgroup1 上创建一个表。对表中数据的查询将分散到三个上创建一个表。对表中数据的查询将分散到三个磁盘上，从而提高了性能。磁盘上，从而提高了性能。15/39使用文件和文件组的一般建议使用文件和文件组的一般建议v使用文件组将对象放置在特定的物理磁盘使用文

15、件组将对象放置在特定的物理磁盘上。上。v为性能最大化，应在尽可能多的不同可用为性能最大化，应在尽可能多的不同可用物理磁盘上创建文件和文件组，将争夺空物理磁盘上创建文件和文件组，将争夺空间最激烈的对象置于不同的文件组中。间最激烈的对象置于不同的文件组中。v将同一连接查询中使用的不同表置于不同将同一连接查询中使用的不同表置于不同文件组中。文件组中。v将最经常访问的表和属于这些表的非聚集将最经常访问的表和属于这些表的非聚集索引置于不同的文件组中。索引置于不同的文件组中。v不要将事务日志文件和其他文件或文件组不要将事务日志文件和其他文件或文件组置于同一磁盘上。置于同一磁盘上。3.1.4 数据库文件的属

16、性数据库文件的属性v文件名及其位置文件名及其位置：每个文件都有一个逻辑文件：每个文件都有一个逻辑文件名和物理文件名及物理存储位置名和物理文件名及物理存储位置v初始大小初始大小：可以指定每个文件的初始大小。：可以指定每个文件的初始大小。v增长方式增长方式：可以指定文件是否自动增长：可以指定文件是否自动增长v最大大小最大大小：可以指定文件增长的最大空间限制：可以指定文件增长的最大空间限制17/393.2 创建数据库创建数据库v创建用户数据库前，须先确定创建用户数据库前，须先确定数据库名数据库名、所有者所有者、文件名文件名、数据文件大小数据文件大小、数据库数据库字符集字符集、是否自动增长是否自动增长

17、以及以及如何自动增长如何自动增长等信息。在一个等信息。在一个SQL Server实例中，最实例中，最多可创建多可创建32767个数据库。数据库名称必个数据库。数据库名称必须满足标识符规则。在命名数据库时，一须满足标识符规则。在命名数据库时，一定要保证数据库名称简短并有一定的含义定要保证数据库名称简短并有一定的含义。v3.2.1 用图形化方法创建数据库用图形化方法创建数据库v3.2.2 用用T-SQL语句创建数据库语句创建数据库18/393.2.1 用图形化方法创建数据库用图形化方法创建数据库19/393.2.2 用用T-SQL语句创建数据库语句创建数据库CREATE DATABASE data

18、base_name ON PRIMARY ,.n LOG ON ,.n := ( NAME = logical_file_name , FILENAME = os_file_name | filestream_path , SIZE = size KB | MB | GB | TB , MAXSIZE =max_sizeKB|MB|GB|TB| UNLIMITED , FILEGROWTH =growth_incrementKB|MB|GB|TB|% ) ,.n 20/39示例示例1(P66)v例例1创建一个名为创建一个名为“mytest”的数据库，的数据库，其他选项均采用默认设置。其他选项均

19、采用默认设置。CREATE DATABASE mytest21/39示例示例2(P66)CREATE DATABASE RShDBON ( NAME = RShDB, FILENAME = D:RShDB_DataRShDB.mdf , SIZE = 10, MAXSIZE = 30, FILEGROWTH = 5 )LOG ON( NAME = RShDB_log, FILENAME = D:RShDB_DataRShDB_log.ldf , SIZE = 3, MAXSIZE = 12, FILEGROWTH = 2 )22/39示例示例3(P67)CREATE DATABASE stud

20、entsON PRIMARY ( NAME = students, FILENAME = F:Datastudents.mdf, SIZE = 3MB, MAXSIZE = UNLIMITED ), ( NAME = students_data1, FILENAME = D:Datastudents_data1.ndf, SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 1MB )LOG ON ( NAME = students_log, FILENAME = F:Datastudents_log.ldf, SIZE = 2MB, MAXSIZE = 6MB,

21、FILEGROWTH = 10% )23/393.3 查看和设置数据库选项查看和设置数据库选项v展开展开“数据库数据库”节点，在要查看属性的数据库节点，在要查看属性的数据库上右击鼠标，上右击鼠标，v然后在弹出的快捷菜单中选择然后在弹出的快捷菜单中选择“属性属性”命令。命令。24/393.4 修改数据库修改数据库v3.4.1 扩大数据库空间扩大数据库空间v3.4.2 收缩数据库空间收缩数据库空间v3.4.3 添加和删除数据库文件添加和删除数据库文件v3.4.4 创建和更改文件组创建和更改文件组25/393.4.1 扩大数据库空间扩大数据库空间ALTER DATABASE database_nam

22、e := ADD FILE ,.n TO FILEGROUP filegroup_name | DEFAULT | ADD LOG FILE ,.n | MODIFY FILE 26/39示例示例v 例例1为为“RShDB”数据库添加一个新的数据文件，逻辑数据库添加一个新的数据文件，逻辑文件名为：文件名为：RShDB_Data2，物理存储位置为：，物理存储位置为：E:Data文件夹下，物理文件名为：文件夹下，物理文件名为：RShDB_Data2.ndf,初始大小为初始大小为6MB，不自动增长。，不自动增长。ALTER DATABASE RShDBADD FILE ( NAME = RShDB_

23、Data2, FILENAME = E:DataRShDB_Data2.ndf, SIZE = 6MB, FILEGROWTH = 0)27/39示例示例v例例2修改修改Students数据库中主数据文件数据库中主数据文件Students的初始大小，将其初始大小改为的初始大小，将其初始大小改为8MB。ALTER DATABASE Students MODIFY FILE( NAME = students, SIZE = 8MB)28/39示例示例v 例例3为为Students数据库加添加一个新的日志文件，逻辑数据库加添加一个新的日志文件，逻辑文件名为：文件名为：students_log1，物理

24、存储位置为：，物理存储位置为：D:Data文件夹下，物理文件名为：文件夹下，物理文件名为：students_log1.ldf,初始大小为初始大小为2MB，每次增加，每次增加1MB，最多增加到最多增加到10MB。ALTER DATABASE Students ADD LOG FILE ( NAME = students_log1, FILENAME = D:Datastudents_log1.ldf, SIZE = 2MB, FILEGROWTH = 1MB, MAXSIZE = 10MB )29/393.4.2 收缩数据库空间收缩数据库空间v自动收缩自动收缩v手工收缩手工收缩 用图形化方法用图

25、形化方法 用用T-SQL语句语句30/39收缩数据库收缩数据库DBCC SHRINKDATABASE ( database_name | 0 ,target_percent , NOTRUNCATE | TRUNCATEONLY )31/39收缩文件收缩文件DBCC SHRINKFILE ( file_name , EMPTYFILE | , target_size , NOTRUNCATE | TRUNCATEONLY )32/39示例示例v例例4收缩收缩Students数据库，使该数据库中数据库，使该数据库中所有的文件都有所有的文件都有20%的可用空间。的可用空间。DBCC SHRINKD

26、ATABASE（students, 20）v例例5将将Students数据库中的数据库中的students_data1文件收缩到文件收缩到4MB。 DBCC SHRINKFILE (students_data1, 4)33/39数据库文件使用情况数据库文件使用情况34/39添加和删除数据库文件添加和删除数据库文件v添加数据库文件添加数据库文件 使用使用ALTER DATABASE语句。语句。v删除数据库文件删除数据库文件ALTER DATABASE database_name REMOVE FILE logical_file_namev只有当文件中没有数据或事务日志信息，文件只有当文件中没有数

27、据或事务日志信息，文件完全为空时，才可以从数据库中删除该文件。完全为空时，才可以从数据库中删除该文件。35/39示例示例v例例 6 删 除 删 除 S t u d e n t s 数 据 库 中 的数 据 库 中 的Students_data1文件。文件。 ALTER DATABASE Students REMOVE FILE Students_data1v例例 7 删 除 删 除 S t u d e n t s 数 据 库 中 的数 据 库 中 的Students_log1文件。文件。 ALTER DATABASE Students REMOVE FILE Students_log136/3

28、93.4.4 创建和更改文件组创建和更改文件组v用图形化方法：在数据库属性窗口中；用图形化方法：在数据库属性窗口中；v用用T-SQL语句。语句。37/39创建和更改文件组语句创建和更改文件组语句ALTER DATABASE database_name | ADD FILEGROUP filegroup_name | REMOVE FILEGROUP filegroup_name | MODIFY FILEGROUP filegroup_name | DEFAULT | NAME = new_filegroup_name := READ_ONLY | READ_WRITE 38/39示例示例v例

29、例1为为students数据库定义一个新文件组，文件组名为：数据库定义一个新文件组，文件组名为：NewFileGroup1，同时，同时在该文件组中新添加两个数据文件，逻辑名分别为：在该文件组中新添加两个数据文件，逻辑名分别为：students_dat1和和students_dat2，初始大小分别为：，初始大小分别为：4MB和和6MB，均存放在，均存放在D:Data文件夹下，不文件夹下，不自动增长。自动增长。v（1）创建文件组）创建文件组ALTER DATABASE StudentsADD FILEGROUP NewFileGroup1v（2）添加新数据文件）添加新数据文件ALTER DATAB

30、ASE students ADD FILE ( NAME = students_dat1, FILENAME = D:Datastudents_dat1.ndf, SIZE = 4MB, FILEGROWTH = 0 ),( NAME = students_dat2, FILENAME = D:Datastudents_dat2.ndf, SIZE = 6MB, FILEGROWTH = 0 ) TO FILEGROUP NewFileGroup139/39示例示例v例例 2 将 将 S t u d e n t s 数 据 库 中 的数 据 库 中 的NewFileGroup1文件组设为默认文

31、件组。文件组设为默认文件组。ALTER DATABASE Students M O D I F Y F I L E G R O U P NewFileGroup1 DEFAULT40/393.5 删除数据库删除数据库DROP DATABASE database_name , n v例例1删除单个数据库：删除删除单个数据库：删除Sudnets数据库数据库。 DROP DATABASE studentsv例例2同时删除多个数据库：删除同时删除多个数据库：删除“学生数据库学生数据库”和和“RShDB”数据库。数据库。 DROP DATABASE 学生数据库学生数据库, RShDB41/393.6 分离和附加数据库分离和附加数据库v分离和附加数据库可以将数据库从一台计算机分离和附加数据库可以将数据库从一台计算机移动到另一台计算机。移动到另一台计算机。v数据库被分离后，其所包含的数据文件和日志数据库被分离后，其所包含的数据文件和日志文件不再受数据库管理系统的管理，因此用户文件不再受数据库管理系统的管理