四川天一学院信息工程系毛玉环

Size: px

Start display at page:

Download "四川天一学院信息工程系毛玉环"

臣珠邢
5 years ago
Views:

1 四川天一学院信息工程系毛玉环

教学目标 1 1 1 掌握 SQL Server 2005 数据库系统的数据库类型了解系统数据库的功能了解数据库中的基本对象的特点 1 1 1

2 教学目标掌握 SQL Server 2005 数据库系统的数据库类型了解系统数据库的功能了解数据库中的基本对象的特点掌握数据库文件及文件组的概念及配置策略掌握使用 Transact-SQL 创建数据库的方法掌握查看数据库的基本方法 1 1 掌握修改文件组增大数据库及收缩数据库方法掌握删除数据库的方法

3 3.1 SQL Server 2005 数据库和数据库对象系统数据库 master 数据库 : 记录系统的所有系统级的信息 model 数据库 : 模板数据库 msdb 数据库 : 记录了有关 SQL Server Agent 服务的信息 tempdb 数据库 : 临时数据库, 用于保存中间数据用户数据库

4 3.2 数据库文件数据库存储结构 : 每个数据库都至少由一个数据文件和一个事务日志文件组成数据库文件主数据文件 (.mdf): 包含数据库的启动信息数据信息唯一的事务日志 (.ldf): 包含恢复数据库的所有日志信息至少一个次数据文件 (.ndf): 包含除了主文件外的所有数据信息零个一个或多个物理文件 ( 操作系统中实际存在的文件 ) 和逻辑文件 ( 数据库中的标志 )

5 数据库文件组 (Filegroup): 主文件组 (Primary) 用户定义文件组可以提高数据库的查询性能设计文件和文件组的规则一个文件或文件组不能用于多个数据库, 只能用于一个数据库一个文件只能是一个文件组的成员一个数据库的数据信息和事务日志信息总是分开存放的事务日志文件不能成为任何文件组的成员

6 3.3 创建数据库创建方法 : 利用 SSMS 创建数据库 : 使用 SSMS 创建数据库利用 T-SQL 命令创建数据库 : 使用查询分析器

7 3.3.2 利用 T-SQL 命令创建数据库格式 :create database 数据库名称 [on 数据文件描述项 ] [log on 日志文件描述项 ] 例 1( 教材例 3-3): 创建数据库 Test CREATE DATABASE Test 例 2( 教材例 3-2) : 创建数据库 ExamDB 学校的机房里需要安装考试系统, 该系统使用 SQL Server 2005 数据库管理系统需要为考试系统创建一个数据库该数据库的数据访问量不大, 数据库系统相对简单一些答案 :

8 例 3( 教材例 3-1): 创建数据库 BlueSkyDB 数据库 BlueSkyDB 包含 4 个文件组, 其中主文件组中只存放一个主数据文件用户自定义文件组 1 中存放两个次数据文件用户自定义文件组 2 中存放一个次数据文件用户自定义文件组 3 中存放一个次数据文件系统包含一个日志文件对每个文件的配置见表 3-3 答案 :

9 3.4 数据库的管理查看数据库管理文件组增大数据库收缩数据库补充 : 增加 / 删除事务日志文件删除数据库

10 3.4.1 查看数据库 1. 使用系统目录视图查看数据库例 3-4 查看所有数据库的基本信息例 3-5 查看指定数据库的基本信息例 3-6 查看当前数据库文件组的基本信息例 3-7 查看当前数据库文件的基本信息 2. 使用存储过程查看数据库例 3-8 查看所有数据库的基本信息例 3-9 查看指定数据库的基本信息例 3-10 查看当前数据库空间使用情况

11 3.4.2 管理文件组 1. 增加文件组增加文件组的基本命令格式如下所示 ALTER DATABASE DatabaseName ADD FILEGROUP FilegroupName 例 3-11: 在 BlueSkyDB 中新增一个文件组 UserGroupX USE master ALTER DATABASE BlueSkyDB ADD FILEGROUP UserGroupX

12 2. 修改文件组修改文件组的基本命令格式如下所示 ALTER DATABASE DatabaseName MODIFY FILEGROUP FilegroupName FilegroupProperty 说明 : 文件组属性可取值 Readonly: 只读, 主文件组不能设为只读 Readwrite: 可读写文件组 Default: 设置默认文件组, 只能有一个数据库文件组是默认的例 3-12: 在 BlueSkyDB 中将文件组 UserGroup1 配置为默认文件组 USE master ALTER DATABASE BlueSkyDB MODIFY FILEGROUP UserGroup1 DEFAULT

13 3. 重命名文件组重命名文件组的基本命令格式为 ALTER DATABASE DatabaseName MODIFY FILEGROUP FilegroupName NAME=NewFilegroupName 例 3-13 将刚刚增加的文件组 UserGroupX 重命名为 UserGroup4 USE master ALTER DATABASE BlueSkyDB MODIFY FILEGROUP UserGroupX NAME=UserGroup4

14 4. 删除文件组删除文件组的基本命令格式为 ALTER DATABASE DatabaseName REMOVE FILEGROUP FilegroupName 例 3-14 因为文件组 UserGroup4 中不包含任何数据文件, 将该文件组删除 USE master ALTER DATABASE BlueSkyDB REMOVE FILEGROUP UserGroup4

15 3.4.3 增大数据库 1. 修改数据库文件大小修改数据库文件属性的命令格式为 ALTER DATABASE DatabaseName MODIFY FILE (NAME=LogicalFileName, Property=Value) 例 3-15: 数据库 BlueSkyDB 的大小是 12MB, 现在希望将该数据库的大小增大到 14MB 通过将主数据文件 BSPrimaryFile1 从原来的 4MB 增大到 6MB 的方法来实现数据库的增大例 3-16: 将数据库 BlueSkyDB 的文件 BSGroup3File1 设置为增长到磁盘满为止

16 2. 增加数据库文件使用下面的命令格式可以增加数据文件 ALTER DATABASE DatabaseName { ADD FILE <FileSpec> [1,,n][TO FILEGROUP FileGroupName] ADD LOG FILE <FileSpec> [1,,n] } <filespec>::= ([NAME=logicalFileName], FILENAME= osfilename, [SIZE=size], [MAXSIZE={maxSize UNLIMITED}],[FILEGROWTH=growthIncrement] ) 例 1( 教材例 3-17): 在数据库 BlueSkyDB 的主文件组 UserGroup1 中增加一个次数据文件

17 3.4.4 收缩数据库含义数据库和数据库文件可通过删除未使用的空间的方法来缩小当数据文件和日志文件有大量自由空间时自动缩小默认禁用自动收缩, 以避免产生磁盘碎片, 若用建议采用手工方式收缩 1. 自动收缩数据库设置数据库 AUTO_SHRINK 选项的语法为 ALTER DATABASE DatabaseName SET AUTO_SHRINK ON OFF 2. 手动收缩数据库手动收缩数据库的语法格式为 DBCC SHRINKDATABASE ( 'DatabaseName' DatabaseID 0 ) [, TargetPercent ] [, { NOTRUNCATE TRUNCATEONLY } ] 例 3-18 在本例中, 首先查看数据库 BlueSkyDB 空间使用情况, 然后在该数据库中创建一个表并填写大量数据, 再一次观察数据库空间使用情况删除表 ( 包括表里的数据 ), 然后收缩数据库

18 3. 手动收缩数据库文件注 : 手动收缩数据库的语法格式为 DBCC SHRINKFILE ( { ' FileName ' FileID } { [, EMPTYFILE ] [ [, TargetSize ] [, { NOTRUNCATE TRUNCATEONLY } ] ]} ) 例 3-19 : 将数据库 BlueSkyDB 数据库中的次数据文件 BSGroup1File1 的大小从 2MB 收缩到 1MB 1 收缩操作是在后台运行的 2 持续不断地收缩并重新生成数据库会造成大量的文件碎片, 所以不宜经常执行收缩操作 3 通常在执行了清空表或删除表等大数据量的操作以后执行数据库或数据库文件收缩操作更有效

19 补充 : 增加 / 删除事务日志文件增加日志文件格式 :alter database 数据库名 add log file ( 事务日志文件属性说明项 ) 例 : 向 test1 数据库添加两个 5MB 大小的日志文件 Alter database test1 Add log file (name=test1_log2, Filename= e:\sql\test1_log2.ldf, Size=5MB, Maxsize=100MB, Filegrowth=5MB), (name=test1_log3, Filename= e:\sql\test1_log3.ldf, Size=5MB, Maxsize=100MB, Filegrowth=5MB)

20 删除日志文件格式 : alter database 数据库名 remove file 数据文件事务日志文件逻辑名例 :alter database test1 remove file test1_log

21 3.4.5 删除数据库删除数据库的语法为 DROP DATABASE DatabaseName 例 3-20 删除数据库 BlueSkyDB USE master DROP DATABASE BlueSkyDB

26 例 3-4 查看所有数据库的基本信息 SELECT * FROM sys.databases 结果如图 3-3 所示

27 例 3-5 查看指定数据库的基本信息 SELECT * FROM sys.databases WHERE name='blueskydb' 结果如图 3-4 所示

28 例 3-6 查看当前数据库文件组的基本信息 USE BlueSkyDB SELECT * FROM sys.filegroups 结果如图 3-5 所示

29 例 3-7 查看当前数据库文件的基本信息 USE BlueSkyDB SELECT * FROM sys.database_files 结果如图 3-6 所示

30 例 3-8 查看所有数据库的基本信息 EXEC sp_helpdb 结果如图 3-7 所示

31 例 3-9 查看指定数据库的基本信息 EXEC sp_helpdb BlueSkyDB 结果如图 3-8 所示

32 例 3-10 查看当前数据库空间使用情况 USE BlueSkyDB EXEC sp_spaceused

33 例 3-15 数据库 BlueSkyDB 的大小是 12MB, 现在希望将该数据库的大小增大到 14MB 通过将主数据文件 BSPrimaryFile1 从原来的 4MB 增大到 6MB 的方法来实现数据库的增大 USE master ALTER DATABASE BlueSkyDB MODIFY FILE (NAME=BSPrimaryFile1, SIZE=6)

34 例 3-16 将数据库 BlueSkyDB 的文件 BSGroup3File1 设置为增长到磁盘满为止 USE master ALTER DATABASE BlueSkyDB MODIFY FILE (NAME= BSGroup3File1, MAXSIZE=UNLIMITED)

35 例 3-17 在数据库 BlueSkyDB 的主文件组 UserGroup1 中增加一个次数据文件 USE master ALTER DATABASE BlueSkyDB ADD FILE (NAME=BSGroup1File3, FILENAME='E:\BlueSky\Group1File3Data.ndf', SIZE=2MB, MAXSIZE=50MB, FILEGROWTH=1MB) TO FILEGROUP UserGroup1

36 例 3-18 在本例中, 首先查看数据库 BlueSkyDB 空间使用情况, 然后在该数据库中创建一个表并填写大量数据, 再一次观察数据库空间使用情况删除表 ( 包括表里的数据 ), 然后收缩数据库步骤 1 第一次观察数据库 BlueSkyDB 的空间使用情况 USE BlueSkyDB SELECT name,size FROM sys.database_files

37 步骤 2 创建表并向表中插入大量数据 CREATE TABLE T1 (col1 char(8000)) integer BEGIN INSERT INTO T1 VALUES('A') END 步骤 3

38 步骤 3 观察插入数据后数据库文件尺寸的变化 SELECT name,size FROM sys.database_files 步骤 4 5

39 步骤 4 删除表, 包括表中的数据 DROP TABLE T1 步骤 5 观察删除表后数据库文件的尺寸 SELECT name,size FROM sys.database_files 步骤 6 7

40 步骤 6 收缩数据库 DBCC SHRINKDATABASE(0,0) 步骤 7 观察收缩数据库以后数据库文件的尺寸 SELECT name,size FROM sys.database_files

41 例 3-19 将数据库 BlueSkyDB 数据库中的次数据文件 BSGroup1File1 的大小从 2MB 收缩到 1MB USE BlueSkyDB DBCC SHRINKFILE ( 'BSGroup1File1', 1 )

01 SQL Server SQL Server 2008 SQL Server 6-1 SSIS SQL Server ( master ) ( msdb ) SQL Server ( master ) master 6-1 DTS sysadmin 6-1 sysa

6 01 SQL Server SQL Server 2008 SQL Server 6-1 SSIS 6-1 06 228 6-1 SQL Server ( master ) ( msdb ) SQL Server ( master ) master 6-1 DTS sysadmin 6-1 sysadmin 6-1 SQL Server 2008 SSIS SQL Server (dbo) master