第2章数据库的创建和管理

1、2022-5-71第二章 数据库的创建和管理 22022-5-7第二章第二章 数据库的创建和管理数据库的创建和管理 基本概念1创建数据库2数据库的管理3删除数据库432022-5-72.1 基本概念 数据库文件数据库文件 SQL Server2005将数据库数据库映射为一组操作系统文件一组操作系统文件，数据和日志信息分别存储在不同的文件中 。数据文件数据文件数据库文件数据库文件事务日志文件事务日志文件用于存储数据库中的所有对象，如表、视图、存储过程等 用于记录所有事务对数据库所做的修改42022-5-7例2.1 查看SQL Server2005安装成功后系统数据库master的逻辑名称与对应的

2、物理文件的存储情况。2.1 基本概念步骤步骤1 1：启动SQL Server Management Studio，在“对象资源管理器”中，依次展开“数据库”节点、“系统数据库”节点，查看到名为master的数据库。 52022-5-72.1 基本概念步骤步骤2 2：在操作系统下，找到SQL Server2005的安装路径 ，依次打开“MSSQL.1”、“MSSQL”和“Data”文件夹， “master.mdf”和“mastlog.ldf”为master数据库对应的物理文件 。 每个数据库都拥有自己的每个数据库都拥有自己的数据和日志信息文件数据和日志信息文件 62022-5-72.1 基本概念

3、 数据文件：数据文件用于存储数据库中的所有对象，如表、视图、存储过程等 。主要数据文件主要数据文件次要数据文件次要数据文件包含数据库的启动信息和数据库中其他文件的指针。每个数据库有且仅有一个主要数据文件，主要数据文件的建议文件扩展名为.mdf。 次要数据文件是可选的，由用户定义并存储主要数据文件未存储的其他数据和对象，建议文件扩展名为.ndf。 注意：次要数据文件不是必须的，如果主要数据文件足够大，能够容纳数据库中的所有数据，则该数据库不需要次要数据文件；但有些数据库可能非常大，超过了单个Windows文件的最大值，可以使用多个次要数据文件，这样数据库就能继续增长。72022-5-72.1 基

4、本概念 事务日志文件：用以记录所有事务及每个事务对数据库所做的修改 。 每个SQL Server2005数据库至少拥有一个事务日志文件，也可以拥有多个事务日志文件。 事务日志文件的大小最少是1MB。 事务日志是数据库的重要组件，当系统出现故障或数据库遭到破坏时，就需要使用事务日志恢复数据库内容。 日志文件的建议文件扩展名为.ldf。82022-5-72.1 基本概念 说明 SQL Server2005不强制使用.mdf、.ndf和.ldf文件扩展名，但使用它们有助于标识文件的各种类型和用户 。 QL Server2005的每个数据库文件都有一个逻辑文件名和一个物理文件名。逻辑文件名只在Tran

5、sact-SQL语句中使用，是实际磁盘文件名的代号。物理文件名是操作系统文件的实际名字，包括文件所在的路径。 92022-5-72.1 基本概念 数据库文件组数据库文件组 将多个数据文件集合起来形成的一个整体就是文件组。对文件进行分组的目的是便于进行管理和进行数据的分配。 每个文件组有一个组名。一个数据文件不能存在于两个或两个以上的文件组里，日志文件不属于任何文件组。 主文件组：主文件组：包含了所有的系统表。当建立数据库时，主文件组包括主要数据文件和所有没有被包含在其他文件组里的次要数据文件。 102022-5-72.1 基本概念 用户自定义文件组：用户自定义文件组：用户自定义文件组包含所有在

6、使用CREATE DATABASE或 ALTER DATABASE命令时使用FILEGROUP关键字来进行指定文件组的文件。 默认文件组默认文件组 ：默认文件组包含所有在创建时没有指定文件组的表、索引等数据库对象。在每个数据库中，每次只能有一个文件组是默认文件组。可以在用户自定义文件组中指定一个默认文件组；如果没有指定默认文件组，则主文件组为默认文件组。 112022-5-72.1 基本概念 数据库的物理存储结构数据库的物理存储结构 页面和盘区 SQL Server2005中数据存储的基本单位是页。 为数据库中的数据文件（.mdf或.ndf）分配的磁盘空间可以从逻辑上划分成页。 在SQL Se

7、rver2005中，页的大小是8KB，SQL Server2005数据库每兆字节有128页。 由8个连续页面（88KB=64KB）组成的数据结构称为一个盘区，SQL Server2005数据库每兆字节有16个盘区 。 122022-5-72.1 基本概念 数据库的存储结构 简单地说，一个数据库是由文件组成的，文件是由盘区组成，而盘区是由页面组成的。132022-5-72.1 基本概念 注意： 数据行存放在数据页中，但数据页只能包含除text、ntext和image数据外的所有数据，text、ntext和image数据存储在单独的页中。 行不能跨页存储，而每数据页是8KB，严格地说是8060B，

8、因此页中每一行最多包含的数据量是8060B 。 日志文件是由一系列日志记录组成，而不是页面。 142022-5-72.1 基本概念 SQL Server2005数据库的分类数据库的分类 1 系系 统统 数数 据据 库库 2 示示 例例 数数 据据 库库 3 用用 户户 数数 据据 库库 152022-5-72.1 基本概念系统数据库系统数据库是在SQL Server2005的每个实例中都存在的标准数据库，用于存储有关SQL Server的信息，SQL Server使用系统数据库来管理系统。 162022-5-72.1 基本概念 master数据库master数据库是SQL Server2005

9、中的总控数据库，是最重最重要要的系统数据库。系统是根据master数据库中的信息来管理系统和其他数据库。如果master数据库信息被破坏，整个SQL Server系统将受到影响，用户数据库将不能被使用。 model数据库 model（模板）数据库是为用户建立新数据库提供模板模板和原型和原型，它包含了将复制到每个新建数据库中的系统表。 172022-5-72.1 基本概念 msdb数据库 msdb数据库支持SQL Server代理 。当代理程序调度作业、记录操作时，系统要用到或实时产生很多相关信息，这些信息一般存储在msdb数据库中。 tempdb数据库 tempdb数据库是一个临时数据库，保存

10、所有的临时表、临时数据以及临时创建的存储过程。 resource数据库 resource数据库是一个只读和隐藏的数据库，包含SQL Server2005所有的系统对象，我们无法使用可以列出所有数据库的一般SQL命令来看到它。182022-5-72.1 基本概念 示例数据库 AdventureWorks和AdventureWorks DW是SQL Server2005中的示例数据库，是系统为了让用户学习和理解SQL Server2005而设计的。 用户数据库 用户数据库是用户根据事务管理需求创建的数据库，例如，图书信息管理数据库、sales数据库等。192022-5-72.1 基本概念 数据库对

11、象的结构数据库对象的结构 架构是一种允许我们对数据库对象进行分组的容器对象，是形成单个命名空间命名空间的数据库对象的集合。 命名空间命名空间是一个集合，其中每个元素的名称都是唯一的。 在SQL Server2005中，一个数据库对象通过由4个命名部分组成的结构来引用，即：server_name.database_name.schema_name.object_name服务器名 数据库名 架构名 对象名202022-5-72.1 基本概念 如果应用程序引用了一个没有限定架构的数据库对象，那么SQL Server2005将尝试在用户的默认架构（通常为默认架构（通常为dbo）中找出这个对象。例如，引

12、用服务器“HBSI”上的数据库“sales”中的销售员表“Seller”时，完整的引用为“HBSI.sales.dbo.Seller”。 在实际引用时，在能够区分对象的前提下，前三个部分是可以根据情况省略省略的。212022-5-72.2 创建数据库创建数据库创建数据库使用SQL Server Management Studio创建数据库 。使用Transact-SQL语句创建数据库优点：简单直观优点：可以将创建数据库的脚本保存下来，在其他计算机上运行以创建相同的数据库 222022-5-7使用使用SQL Server Management Studio创建数据库创建数据库例2.2 使用SQL

13、 Server Management Studio创建sales数据库。2.2 创建数据库步骤步骤1 1：启动SQL Server Management Studio，在“对象资源管理器”窗口中展开“SQL Server服务器”节点，右键单击“数据库”节点，在弹出的快捷菜单中选择“新建数据库”。 步骤步骤2 2：系统弹出“新建数据库”对话框，该对话框有三个选择页：常规、选项和文件组。232022-5-72.2 创建数据库步骤步骤3 3：在 “常规”页中根据提示输入该数据库的相关内容，如数据库名称、所有者、文件初始大小、自动增长值和保存路径等。 步骤步骤4 4：单击“常规”页下方的“添加”按钮还

14、可为该数据库增加数据文件和日志文件。单击“删除”按钮可将选定的数据文件或日志文件删除。 步骤步骤5 5：在“新建数据库”对话框的“选项”页中可以设置数据库的排序规则、恢复模式、兼容级别和其他一些选项。 242022-5-72.2 创建数据库 步骤步骤6：在“文件组”页中可设置或添加文件组的属性，如是否只读、是否为默认文件组等，单击“添加”和“删除”按钮即可添加或删除用户自定义文件组。注意：在删除文件组时会将文件组中包含的数据文件一起删除。步骤步骤7 7：单击“确定”按钮，系统开始创建数据库。创建成功后，刷新“对象资源管理器”窗口中的“数据库”节点的内容，再展开“数据库”节点，就会显示出新创建的

15、数据库sales。252022-5-72.2 创建数据库 使用使用Transact-SQL语句创建数据库语句创建数据库 CREATE DATABASE database_name ON ,.n , ,.n LOG ON ,.n := PRIMARY ( NAME = logical_file_name , FILENAME = os_file_name , SIZE = size , MAXSIZE = max_size | UNLIMITED , FILEGROWTH = growth_increment ) ,.n :=FILEGROUP filegroup_name ,.n 数据库的名称

16、指明主要数据文件、次要数据文件和文件组事务日志文件的明确定义 262022-5-7例例2.3 2.3 创建一个数据库创建一个数据库samplesample。主文件为。主文件为sample_datsample_dat。CRREATE DATABASE sampleCRREATE DATABASE sampleON PRIMARYON PRIMARY(NAME=sample_dat,(NAME=sample_dat, FILENAME=d:datasample_data.mdf, FILENAME=d:datasample_data.mdf, SIZE=5, SIZE=5, MAXSIZE=50,

17、 MAXSIZE=50, FILEGROWTH=10 FILEGROWTH=10) )2.2 创建数据库该语句需在“查询编辑器”中输入并执行272022-5-72.2 创建数据库 说明如果没有为主要数据文件指定单位，默认为MB。如果在创建时没有指定日志文件，系统将自动创建一个初始容量为1.25MB的日志文件并且没有最大容量限制。如果在查询语句编辑区域选定了语句，则只执行选定的语句，否则执行所有语句。2.2 创建数据库例2.4 创建数据库sales ，包含一个数据文件和一个日志文件。数据文件的逻辑名为sales_data，操作系统名为sales_data.mdf，初始大小为10MB，最大可增加至

18、500MB，增幅为10；日志文件的逻辑名为sales_log，操作系统名为sales_log.ldf，初始大小为5MB，最大值为100MB，以2MB增幅增加。2.2 创建数据库CREATE DATABASE salesCREATE DATABASE salesON ( NAME=sales_data,ON ( NAME=sales_data,FILENAME=d:datasales_data.mdf,FILENAME=d:datasales_data.mdf,SIZE=10MB,SIZE=10MB,MAXSIZE=500MB,MAXSIZE=500MB,FILEGROWTH=10% )FILE

19、GROWTH=10% )LOG ONLOG ON( NAME=sales_log,( NAME=sales_log,FILENAME=d:datasales_log.ldf,FILENAME=d:datasales_log.ldf,SIZE=5MB,SIZE=5MB,MAXSIZE=100MB,MAXSIZE=100MB,FILEGROWTH=2MB )FILEGROWTH=2MB )302022-5-72.3 数据库的管理 查看数据库查看数据库查看数据库的属性查看数据库的属性使用SQL Server Management Studio用Transact-SQL语句 对于已有的数据库对于已有的

20、数据库 312022-5-72.3 数据库的管理 使用SQL Server Management Studio查看数据库启动SQL Server Management Studio，在“对象资源管理器”窗口中选中要查看的数据库sales，单击鼠标右键，在弹出的快捷菜单上单击“属性”命令，即可进入sales数据库的属性窗口。该窗口共有8个选择页，包括“常规”、“文件”、“文件组”、“选项”、“权限”等，我们可以根据需要选择不同的选择页查看数据库相应的信息 322022-5-72.3 数据库的管理 使用Transact-SQL语句查看数据库的属性系统存储过程sp_helpdb查看数据库的属性。例2

21、.7查看数据库sales的属性。 语句格式：sp_helpdb sales 例2.8 查看所有数据库的属性语句格式：sp_helpdb332022-5-72.3 数据库的管理 修改数据库修改数据库 使用SQL Server Management Studio修改数据库属性： 在“对象资源管理器”窗口中选中要修改的数据库sales，单击鼠标右键右键，在弹出的快捷菜单上选择“属属性性”命令，即可进入sales数据库的属性窗口。在该窗口中对数据库的相关属性进行修改。 使用Transact-SQL语句修改数据库属性342022-5-72.3 数据库的管理ALTER DATABASE database

22、ALTER DATABASE database ADD FILE ,.n TO FILEGROUP ADD FILE ,.n TO FILEGROUP filegroup_name filegroup_name | ADD LOG FILE ,.n | ADD LOG FILE ,.n | REMOVE FILE logical_file_name | REMOVE FILE logical_file_name | ADD FILEGROUP filegroup_name | ADD FILEGROUP filegroup_name | REMOVE FILEGROUP filegroup_n

23、ame | REMOVE FILEGROUP filegroup_name | MODIFY FILE | MODIFY FILE | MODIFY NAME = new_dbname | MODIFY NAME = new_dbname | MODIFY FILEGROUP filegroup_name filegroup_property | | MODIFY FILEGROUP filegroup_name filegroup_property | NAME = new_filegroup_name NAME = new_filegroup_name 2.3 数据库的管理例2.9 为数据

24、库sample添加数据文件和日志文件。ALTER DATABASE sampleADD FILE( NAME=sample_dat2, FILENAME=d:datasample_dat2.ndf, SIZE=4, MAXSIZE=10, FILEGROWTH=1 )ALTER DATABASE sampleADD LOG FILE( NAME=sample_log2, FILENAME=d:datasample_log2.ldf, SIZE=4, MAXSIZE=10, FILEGROWTH=1 )2.3 数据库的管理例2.10 为数据库sample添加一个文件组USER1，然后向该文件组添

25、加两个数据文件。ALTER DATABASE sample ADD FILEGROUP USER1ALTER DATABASE sampleADD FILE ( NAME=sampuser_dat1, FILENAME=d:datasampuser_dat1.ndf, SIZE=4, MAXSIZE=10, FILEGROWTH=1 ),( NAME=sampuser_dat2, FILENAME=d:datasampuser_dat2.ndf, SIZE=4, MAXSIZE=10, FILEGROWTH=1 )TO FILEGROUP USER12.3 数据库的管理例2.10 从数据库sa

26、mple中删除文件sampuser_dat2。ALTER DATABASE sampleREMOVE FILE sampuser_dat2例2.11 删除数据库sample中的文件组USER1。ALTER DATABASE sampleREMOVE FILE sampuser_dat1ALTER DATABASE sampleREMOVE FILEGROUP USER1注意：使用Transact-SQL语句删除文件组时必须先删除文件组中所包含的文件，否则报错。2.3 数据库的管理例2.12 修改数据库sample中数据文件sample_dat2的属性，将其初始大小改为10MB，最大容量改为80

27、MB,增长幅度改为5MB。ALTER DATABASE sampleMODIFY FILE( NAME=sample_dat2, SIZE=10, MAXSIZE=80, FILEGROWTH=5 )2.3 数据库的管理例2.13 修改数据库business中文件组business_group的属性，将其改名为group1，并设置为DEFAULT属性（即该文件组为默认文件组）。ALTER DATABASE businessMODIFY FILEGROUP business_group NAME=group1ALTER DATABASE businessMODIFY FILEGROUP grou

28、p1 DEFAULT例2.14 将数据库student改名为stud_teacher。ALTER DATABASE studentMODIFY NAME=stud_teacher402022-5-72.3 数据库的管理 重命名数据库重命名数据库 使用系统存储过程sp_renamedb更改某个数据库的名字，其语法格式为： sp_renamedb old_name,new_name例如，sp_renamedb business,company数据库的当前名称数据库的新名称注意：后续版本的SQL Server 将删除该功能，最好使用ALTER DATABASE MODIFY NAME进行重命名数据库

29、。正在使用的数据库是不能进行重命名的。412022-5-72.3 数据库的管理 数据库的收缩数据库的收缩 自动收缩 使用对象资源管理器设置自动收缩数据库在sales数据库 属性窗口的“选项”选择页，将“自动收缩”选项设置为true，即可完成自动收缩的设置。 Transact-SQL语句自动收缩数据库使用ALTER DATABASE语句可以将数据库设为自动收缩。当数据库中有足够的可用空间时，就会发生收缩。ALTER DATABASE database_name SET AUTO_SHRINK on/off注意：不能将整个数据库收缩到比其原始大小还要小。 422022-5-72.3 数据库的管理

30、手工收缩DBCC SHRINKDATABASE( database_name , target_percent , NOTRUNCATE | TRUNCATEONLY )说明说明target_percent：是数据库收缩后的数据库文件中所要的剩余可用空间百分比。 NOTRUNCATE：被释放的文件空间依然保持在数据库文件的范围内。收缩的数据库名称432022-5-72.3 数据库的管理 TRUNCATEONLY：将数据文件中的任何未使用的空间释放给操作系统。使用 TRUNCATEONLY 时，忽略 target_percentis例2.17 缩小sales数据库的大小，使得该数据库中的文件有20的可用空间。DBCC SHRINK