第2章 数据库的创建和管理 学习要点： 2.1 基本概念 2.2 创建数据库 2.3 数据库的管理 2.4 删除数据库

Presentation on theme: "第2章 数据库的创建和管理 学习要点： 2.1 基本概念 2.2 创建数据库 2.3 数据库的管理 2.4 删除数据库"— Presentation transcript:

1 第2章 数据库的创建和管理 学习要点： 2.1 基本概念 2.2 创建数据库 2.3 数据库的管理 2.4 删除数据库
第2章 数据库的创建和管理 学习要点： SQL Server数据库的存储结构 事务日志的概念 数据库的创建、删除、管理 2.1 基本概念 创建数据库 2.3 数据库的管理 删除数据库

2 2.1 基本概念 主要数据文件 数据文件 数据库 次要数据文件 事务日志文件
2.1 基本概念 用于存储数据库中的所有对象 主要数据文件 数据文件 数据库 次要数据文件 事务日志文件 主要数据文件用来存储数据库的启动信息、部分或全部数据，扩展名为.mdf 每个数据库只能有一个主要数据文件 次要数据文件用于保存所有主要数据文件中容纳不下的数据，扩展名为.ndf 保存用于恢复数据库的日志信息

3 文件组 :将多个数据文件集合起来形成的一个整体
目的：便于进行管理和数据的分配 类型： 主文件组：包含了所有的系统表 用户自定义文件组 默认文件组：包含所有在创建时没有指定文件组的表、索引等数据库对象 注意：一个数据文件不能存在于两个或两个以上的文件组里，日志文件不属于任何文件组

4 2.1.1 数据库的物理存储结构

5 事务日志 事务日志文件，用以记录所有事务和每个事务对数据库所做的修改，当数据库破坏时可以用事务日志恢复数据库内容。 系统数据库和示例数据库 安装SQL Server2000时，系统自动创建4个系统数据库和2个示例数据库。

6 master数据库 master数据库记录了SQL Server系统的所有系统级别的信息，包括所有的登录帐户和密码、系统的配置信息以及所有用户定义数据库的存储位置、初始化信息。 master是SQL Server中重要的数据库，如果master数据库受到损坏，SQL Server将不能启动。 model数据库 model数据库是建立新数据库的模板，它包含了将复制到每个新建数据库中的系统表。

7 msdb数据库 该数据库主要被SQL Server Agent用于进行复制、作业调度以及管理报警等活动。 tempdb数据库
示例数据库 pubs数据库记录一个虚拟的图书出版公司的数据信息 northwind数据库保存了一个虚拟的贸易公司的数据信息

8 2.2 创建数据库 创建数据库是创建表及其它数据库对象的第一步。数据库的创建包括为数据库确定名称、决定数据库大小，及在数据库中用来存储数据的文件。 使用企业管理器创建数据库 （1）进入SQL Server企业管理器，在左边的目录树结构中选择“数据库”文件夹 。 （2）单击“操作”菜单中的“新建数据库”命令或右键单击“数据库”文件夹，在弹出的快捷菜单上选择“新建数据库”命令 。系统打开“数据库属性”对话框。

9 （3）在“常规”选项卡中输入数据库的名称，假设为Sales。
输入数据库名称

10 （4）选择“数据文件”选项卡，在该窗口中设置数据文件的属性。
数据文件的逻辑名 文件所在的文件组 文件位置 初始大小 文件否允许自动增长

11 （5）选择“事务日志”选项卡，在该窗口中可设置日志文件的属性。
（6）单击“确定”按钮，数据库创建完成。

12 2.2.2 使用T-SQL语句创建数据库 创建数据库的T-SQL语句的语法形式： CREATE DATABASE database_name
[ ON     [ < filespec > [ ,...n ] ] [ , < filegroup > [ ,...n ] ] ] [ LOG ON { < filespec > [ ,...n ] } ] <filespec>::=[ PRIMARY ] ( [ NAME = logical_file_name , ] FILENAME = 'os_file_name‘ [ , SIZE = size ] [ , MAXSIZE = { max_size | UNLIMITED } ] [ , FILEGROWTH = growth_increment ] ) [ ,...n ] < filegroup > ::=FILEGROUP filegroup_name < filespec > [ ,...n ]

13 database_name：新创建的数据库的名称。
ON：指出用来存储数据库中数据部分的磁盘文件（数据文件）。 PRIMARY：指定主文件组中的主文件。 LOG ON：指定用来存储数据库日志的磁盘文件。 NAME = logical_file_name：指定数据文件或日志文件的逻辑名。 FILENAME = 'os_file_name'：指定数据文件或日志文件的操作系统文件名，包括文件名和路径。 SIZE = size：指定数据文件或日志文件的初始大小，默认单位为MB。

14 MAXSIZE = { max_size | UNLIMITED }：指定数据文件或日志文件可以增长到的最大容量，默认单位为MB。
FILEGROWTH = growth_increment：指定数据文件或日志文件的增长幅度，默认单位为MB。 例2-1 创建一个数据库sample。主文件为sample_dat。 CREATE DATABASE sample ON PRIMARY ( NAME=sample_dat, FILENAME='d:\program files\microsoft sql server\mssql\data\sample_data.mdf', SIZE=5, MAXSIZE=50, FILEGROWTH=10 )

15 CREATE DATABASE Sales ON ( NAME=Sales_data, FILENAME='d:\program files\microsoft sql server\mssql\data\sales_data.mdf', SIZE=10MB, MAXSIZE=500MB, FILEGROWTH=10%) LOG ON ( NAME=Sales_log, FILENAME='d:\program files\microsoft sql server\mssql\data\sales_log.ldf', SIZE=5MB, MAXSIZE=100MB, FILEGROWTH=2MB) 例2-2 创建一个只包含一个数据文件和一个日志文件的数据库。该数据库名为Sales，数据文件的逻辑名为Sales_data，数据文件的操作系统名为sales_data.mdf，初始大小为10MB，最大可增加至500MB，增幅为10％；日志文件的逻辑名为Sales_log，操作系统名为sales_log.ldf，初始大小为5MB，最大值为100MB，日志文件大小以2MB增幅增加。

16 例2-3 创建一个包含多个数据文件和日志文件的数据库。该数据库名为student，含有三个初始大小为10MB的数据文件和两个8MB的日志文件。
CREATE DATABASE student ON PRIMARY ( NAME=std_dat1, FILENAME='d:\program files\microsoft sql server\mssql\data\student1.mdf', SIZE=10MB, MAXSIZE=200MB, FILEGROWTH=20), ( NAME=std_dat2, FILENAME='d:\program files\microsoft sql server\mssql\data\student2.ndf',

17 ( NAME=std_dat3, FILENAME='d:\program files\microsoft sql server\mssql\data\student3.ndf', SIZE=10MB, MAXSIZE=200MB, FILEGROWTH=20) LOG ON ( NAME=std_log1, FILENAME='d:\program files\microsoft sql server\mssql\data\stdlog1.ldf', SIZE=8MB, MAXSIZE=100MB, FILEGROWTH=10MB), ( NAME=std_log2, FILENAME='d:\program files\microsoft sql server\mssql\data\stdlog2.ldf', SIZE=8MB, MAXSIZE=100MB, FILEGROWTH=10MB)

18 例2-4 创建一个包含两个文件组的数据库。该数据库名为business，主文件组包含business_dat1和business_dat2两个数据文件。文件组business_group包含文件数据文件business_dat3。该数据库还包含一个日志文件business_log。 CREATE DATABASE business ON PRIMARY ( NAME=business_dat1, FILENAME='d:\program files\microsoft sql server\mssql\data\businessdat1.mdf', SIZE=10MB, MAXSIZE=50MB, FILEGROWTH=10), ( NAME=business_dat2, FILENAME='d:\program files\microsoft sql server\mssql\data\businessdat2.ndf', MAXSIZE=500MB,

19 FILEGROUP business_group
( NAME=business_dat3, FILENAME='d:\program files\microsoft sql server\mssql\data\businessdat3.ndf', SIZE=10MB, MAXSIZE=50MB, FILEGROWTH=10%) LOG ON ( NAME=business_log, FILENAME='d:\program files\microsoft sql server\mssql\data\businesslog.ldf', SIZE=8MB, MAXSIZE=100MB, FILEGROWTH=10MB)

20 2.3 数据库的管理 2.3.1 查看数据库 1．用企业管理器查看数据库的属性
2.3 数据库的管理 查看数据库 1．用企业管理器查看数据库的属性 进入SQL Server企业管理器，在左边的目录树结构中选中要查看的数据库Sales，单击右键，在弹出的快捷菜单上单击“属性”命令，即可进入Sales数据库的属性窗口 。 该窗口共有6个选项卡，包括“常规”、“数据文件”、“事务日志”、“文件组”、“选项”及“权限”，我们可以根据需要进入选择不同的选项卡查看数据库相应的信息。

21

22 2．用T-SQL语句查看数据库的属性 使用系统存储过程sp_helpdb可以查看某个数据库或所有数据库的属性。 格式：sp_helpdb database_name 该存储过程显示了Sales数据库的名称、大小、所有者、创建日期以及数据文件和日志文件等属性。 例2-5 查看数据库Sales的属性。 sp_helpdb Sales 例2-6 查看所有数据库的属性 sp_helpdb

23 2.3.2 修改数据库 1．用企业管理器修改数据库属性 进入数据库的属性窗口 ，在属性窗口中修改数据库属性。
修改数据库 1．用企业管理器修改数据库属性 进入数据库的属性窗口 ，在属性窗口中修改数据库属性。 注意：在删除文件组时要求该文件组中不包含任何数据文件，否则SQL Server会给出错误信息 2．用T-SQL语句修改数据库属性 其语法形式为：

24 ALTER DATABASE database
{ ADD FILE < filespec > [ ,...n ] [ TO FILEGROUP filegroup_name ] | ADD LOG FILE < filespec > [ ,...n ] | REMOVE FILE logical_file_name | ADD FILEGROUP filegroup_name | REMOVE FILEGROUP filegroup_name | MODIFY FILE < filespec > | MODIFY NAME = new_dbname | MODIFY FILEGROUP filegroup_name {filegroup_property | NAME = new_filegroup_name }

25 ADD FILE < filespec > [ ,
ADD FILE < filespec > [ ,...n ] [ TO FILEGROUP filegroup_name ]：表示向指定的文件组添加新的数据文件。 ADD LOG FILE < filespec > [ ,...n ]：添加新的事务日志文件。 REMOVE FILE logical_file_name：删除某一文件。 ADD FILEGROUP filegroup_name：添加一个文件组。 REMOVE FILEGROUP filegroup_name：删除某一文件组。 MODIFY FILE < filespec >：修改某个文件的属性。 MODIFY NAME = new_dbname：修改数据库的名字

26 例2-7 为数据库sample添加一个数据文件sample_dat2和一个日志文件sample_log2。
ALTER DATABASE sample ADD FILEGROUP USER1 ADD FILE ( NAME=sampuser_dat1, FILENAME='d:\program files\microsoft sql server\mssql\data\sampuser_dat1.ndf', SIZE=4, MAXSIZE=10, FILEGROWTH=1 ), ( NAME=sampuser_dat2, FILENAME='d:\program files\microsoft sql server\mssql\data\sampuser_dat2.ndf', FILEGROWTH=1 ) TO FILEGROUP USER1 MODIFY FILEGROUP filegroup_name {filegroup_property | NAME = new_filegroup_name }：修改某一文件组的属性。 ALTER DATABASE sample ADD FILE ( NAME=sample_dat2, FILENAME='d:\program files\microsoft sql server\mssql\data\sample_dat2.ndf', SIZE=4, MAXSIZE=10, FILEGROWTH=1 ) ADD LOG FILE ( NAME=sample_log2, FILENAME='d:\program files\microsoft sql server\mssql\data\sample_log2.ldf', 例2-7 为数据库sample添加一个数据文件sample_dat2和一个日志文件sample_log2。 例2-8 为数据库sample添加一个文件组USER1，并向该文件组添加一个二个数据文件sampuser_dat1和sampuser_dat2。

27 例2-9 从数据库sample中删除文件sampuser_dat2。
ALTER DATABASE sample REMOVE FILE sampuser_dat2 例2-10 删除数据库sample中的文件组USER1。 REMOVE FILE sampuser_dat1 REMOVE FILEGROUP USER1 注意：在删除文件组时必须先删除文件组中所包含的文件。

28 例2-11 修改数据库sample中数据文件sample_dat2的属性，将其初始大小改为10MB，最大容量改为80MB,增长幅度改为5MB。
ALTER DATABASE sample MODIF FILE ( NAME=sample_dat2, SIZE=10, MAXSIZE=80, FILEGROWTH=5 )

29 例2-12 修改数据库business中文件组business_group的属性，将其改名为group1，并设置为DEFAULT属性（即该文件组为默认文件组）。
ALTER DATABASE business MODIFY FILEGROUP business_group NAME=group1 MODIFY FILEGROUP group1 DEFAULT 例2-13 将数据库student改名为stud_teacher。 ALTER DATABASE student MODIFY NAME=stud_teacher

30 2.3.3 重命名数据库 语法形式为： sp_renamedb ‘old_name’,’new_name’
重命名数据库 语法形式为： sp_renamedb ‘old_name’,’new_name’ old_name：数据库的当前名称； new_name是数据库的新名称。 例2-14 将数据库business更名为company。 sp_renamedb 'business','company

31 2.3.4 数据库的收缩 SQL Server2000允许收缩数据库中的每个文件以删除未使用的页。数据文件和事务日志文件都可以收缩。
数据库的收缩 SQL Server2000允许收缩数据库中的每个文件以删除未使用的页。数据文件和事务日志文件都可以收缩。 1．自动收缩 （1）使用企业管理器设置自动收缩数据库 在数据库的“属性对话框”中，点击“选项”选项卡，选中“自动收缩”复选框，即可完成数据库自动收缩的设置。 设置自动收缩

32 （2）T-SQL语句自动收缩数据库 使用ALTER DATABASE语句可以将数据库设为自动收缩。当数据库中有足够的可用空间时，就会发生收缩。其语法形式为： ALTER DATABASE database_name SET AUTO_SHRINK on/off 例2-15 将数据库sales的收缩设为自动收缩。 ALTER DATABASE sales SET AUTO_SHRINK on 注意：不能将整个数据库收缩到比其原始大小还要小。

33 2．手工收缩 DBCC SHRINKDATABASE ( database_name [ , target_percent ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ) database_name：是要收缩的数据库名称。 target_percent：是数据库收缩后的数据库文件中所要的剩余可用空间百分比。 NOTRUNCATE：被释放的文件空间依然保持在数据库文件的范围内。如果未指定，则被释放的文件空间将被操作系统回收利用。

34 TRUNCATEONLY：将数据文件中的任何未使用的空间释放给操作系统。
例2-16 缩小sales数据库的大小，使得该数据库中的文件有20％的可用空间。 DBCC SHRINKDATABASE(sales,20) 还可以使用DBCC命令来缩小某一个操作系统文件的长度，其语法形式为： DBCC SHRINKFILE  ( file_name   { [ , target_size ] | [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ] }    )

35 file_name：是要收缩的操作系统文件名。
target_size：将文件缩小到指定的长度，以MB为单位。如果缺省该项，文件将尽最大可能进行缩小。 EMPTYFILE：将指定文件上的数据全部迁移到本文件组的其它文件上，以后的操作将不会再再该文件上增加数据。 例2-17 将数据库sales中名为sales_data的文件收缩至7MB。 USE sales GO DBCC SHRINKFILE(sales_data,7)

36 2.4 删除数据库 1．用企业管理器删除数据库 （1）打开SQL Server企业管理器窗口，在左边的目录树结构中选择要删除的数据库文件夹，如company。 （2）选择“操作”菜单中的“删除”命令或右键单击要删除的数据库，在弹出的快捷菜单中选择“删除”命令，系统会弹出“删除数据库”的确认对话框。 （3）单击“是”按钮就会删除该数据库 。 2．T-SQL语句删除数据库 语法形式：DROP DATABASE database_name

37 例2-18 删除数据库student DROP DATABASE student 例2-19 删除sample和student数据库。 DROP DATABASE sample,student 注意： 4个系统数据库master、tempdb、model、msdb不能删除。 正在使用的数据库不能删除。 数据库被删除之后，文件及其数据都从服务器上的磁盘中删除。一旦删除数据库，它即被永久删除，所以删除数据库时一定要谨慎。

38 小结： 介绍了SQL Server数据库创建、管理和删除的方法，学习了数据库的基本存储结构。并对系统数据库和示例数据库进行了讲解
通过对本章的学习，应该重点掌握根据需要创建数据库，并对其进行有效的管理