SQL 第05章 数据库的创建与管理

1、第5章 数据库的创建和管理,5.1 数据库简介 5.2 估算数据库的空间需求 5.3 数据库的创建 5.4 数据库的管理 5.5 设置数据库选项,返回目录,2,5.1 数据库简介,数据库创建的过程就是数据库逻辑设计的物理实现过程。 数据库的创建主要包括数据库及其表、索引、视图、存储过程等对象的创建。 创建数据库时,系统会建立数据库的存储结构。 数据库的存储结构分为逻辑存储结构和物理存储结构两种。 数据库的逻辑存储结构指的是数据库是由哪些性质的信息所组成 。实际上，SQL Server的数据库是由诸如表、视图、索引等各种不同的数据库对象所组成。,3,数据库的物理存储结构是讨论数据库文件是如何在磁

2、盘上存储的，数据库在磁盘上是以文件为单位存储的，由数据库文件和事务日志文件组成，一个数据库至少应该包含一个主数据库文件和一个事务日志文件。 1主数据库文件（Primary Database File） 一个数据库可以有一个或多个数据库文件，一个数据库文件只能属于一个数据库。当有多个数据库文件时，有一个文件被定义为主数据库文件（简称为主文件），其扩展名为mdf。 主数据库文件用来存储数据库的启动信息以及部分或者全部数据，是所有数据库文件的起点，包含指向其它数据库文件的指针。一个数据库只能有一个主数据库文件。,4,2辅助数据库文件（Secondary Database File） 用于存储主数据库

3、文件中未存储的剩余数据和数据库对象，一个数据库可以没有辅助数据库文件，但也可以同时拥有多个辅助数据库文件。 辅助数据库文件的扩展名为ndf（简称为辅助文件）。 3事务日志文件（Log Database File） 事务日志文件存储数据库的更新情况等事务日志信息 ，当数据库损坏时，管理员使用事务日志恢复数据库。 每一个数据库至少必须拥有一个事务日志文件，而且允许拥有多个日志文件。事务日志文件的扩展名为ldf，日志文件的大小至少是512KB。,5,5.1.1 数据库的存储结构 1.盘区和页面 在创建数据库对象时,SQL Server会使用盘区和页面这种特殊的数据结构给数据库对象分配空间。 在SQL

4、中，数据存储的基本单位是页。页的大小是8KB，这样数据库上每一兆字节是128页。页有数据页、索引页、文本/图象页等8种（P80）。 盘区（区域）是连续8个页，可防止数据产生碎片，使SQL易于寻找。 事务日志不被分成页和区域，而是包含已修改数据的清单，按先来先服务的原则组织。,6,2.文件组 文件组是文件的集合。对文件进行分组，便于进行数据的管理和分配磁盘空间。例如有三个数据文件data1.ndf,data2.ndf,data3.ndf,分别位于不同的磁盘上，将这三个文件指派到文件组filegroup1中。假设在文件组filegroup1上创建一个表，对表中数据的查询将会分散到三个磁盘上，从而提

5、高系统的查询性能。 文件和文件组的设计规则: 文件和文件组不能由一个以上的数据库使用; 文件只能是一个文件组的成员; 事务日志文件文件不属于任何一个文件组。 注意：数据和事务日志不能属于同一文件或文件组。数据文件和日志文件总是分开存放的。,7,主文件组中包含了所有的系统表，当建立数据库时，主文件组包括主数据库文件和未指定组的其他文件。 用户定义文件组中可以指定一个缺省文件组，那么在创建数据库对象时如果没有指定将其放在哪一个文件组中，就会将它放在缺省文件组中。如果没有指定缺省文件组，则主文件组为缺省文件组。 一个文件只能存在于一个文件组中，一个文件组也只能被一个数据库使用；日志文件是独立的，它不

6、能作为任何文件组的成员。,8,5.3 创建数据库,创建数据库的三种方法： 1.使用SQL Server Management Studio创建数据库 2.使用Transact-SQL语言创建数据库 5.3.1 创建数据库的注意事项: 创建数据库需要一定许可，在默认情况下，只有系统管理员和数据库拥有者可以创建数据库。数据库被创建后，创建数据库的用户自动成为该数据库的所有者。 创建数据库的过程实际上就是为数据库设计名称、设计所占用的存储空间和存放文件位置的过程等，数据库名字必须遵循SQL Server命名规范。 所有的新数据库都是系统样本数据库model的副本。 单个数据库可以存储在单个文件上，也

7、可以跨越多外文件存储。,9,5.数据库的大小可以被增大或者收缩。 6.当新的数据库创建时，SQL Server自动更新“sysdatabases”系统表。 7.一台服务器上最多可能创建32767个数据库。 5.3.2 使用T-SQL语言创建数据库 说明：在Transact-SQL语言的命令格式中，用 括起来的内容表示是可选的；，n表示重复前面的内容；用括起来表示在实际编写语句时，用相应的内容替代；用 括起来表示是必选的；类似A|B的格式，表示A和B只能选择一个，不能同时都选。 1. 创建数据库的SQL语句的语法格式: CREATE DATABASE database_name ON PRIMA

8、RY ，n ， ，n LOG ON ，n FOR LOAD|FOR ATTACH,10,其中： := NAME=logical_file_name， FILENAME=os_file_name ，SIZE=size ，MAXSIZE=max_size|UNLIMITED ，FILEGROWTH=growth_increment ，n := FILEGROUP filegroup_name ，n 各参数的含义（如下）： database_name：新数据库的名称。数据库名称在服务器中必须唯一，最长为128个字符，并且要符合标识符的命名规则。每个服务器管理的数据库最多为32767个。 ON：指定存

9、放数据库的数据文件信息。列表用于定义主文件组的数据文件，列表用于定义用户文件组及其中的文件。,11,PRIMARY：用于指定主文件组中的文件。主文件组的第一个由指定的文件是主文件。如果不指定PRIMARY关键字，则在命令中列出的第一个文件将被默认为主文件。 LOG ON：指明事务日志文件的明确定义。如果没有本选项，则系统会自动产生一个文件名前缀与数据库名相同，容量为所有数据库文件大小1/4的事务日志文件。 FOR LOAD：表示计划将备份直接装入新建的数据库，主要是为了和过去的SQL Server版本兼容。 FOR ATTACH：表示在一组已经存在的操作系统文件中建立一个新的数据库。 NAME

10、：指定数据库的逻辑名称。 FILENAME：指定数据库所在文件的操作系统文件名称和路径，该操作系统文件名和NAME的逻辑名称一一对应。,12,SIZE：指定数据库的初始容量大小。如果没有指定主文件的大小，则SQL Server默认其与模板数据库中的主文件大小一致，其它数据库文件和事务日志文件则默认为1MB。指定大小的数字size可以使用KB、MB、GB和TB后缀，默认的后缀为MB。Size中不能使用小数，其最小值为512KB，默认值为1MB。主文件的size不能小于模板数据库中的主文件。 MAXSIZE：指定操作系统文件可以增长到的最大尺寸。如果没有指定，则文件可以不断增长直到充满磁盘。 FI

11、LEGROWTH：指定文件每次增加容量的大小，当指定数据为0时，表示文件不增长。增加量可以确定为以KB、MB作后缀的字节数或以%作后缀的被增加容量文件的百分比来表示。默认后缀为MB。如果没有指定FILEGROWTH，则默认值为10%，每次扩容的最小值为64KB。,13,2. 几点说明: 1）创建用户数据库后，要备份master数据库。 2）所有数据库都至少包含一个主文件组。所有系统表都分配在主文件组中。数据库还可以包含用户定义的文件组。 3）每个数据库都有一个所有者，可在数据库中执行某些特殊的活动。数据库所有者是创建数据库的用户，也可以使用sp_changedbowner更改数据库所有者。 4

12、）创建数据库的权限默认地授予sysadmin和dbcreator固定服务器角色的成员。,14,例5-1：创建一个只含一个数据文件和一个事务日志文件的数据库，数据库名为JWGL1，主数据库文件逻辑名称为jwgl1_data，数据文件的操作系统名称jwgl1.mdf，数据文件初始大小为5MB，最大值为500MB，数据文件大小以10%的增量增加。日志逻辑文件名称jwgl1_log.ldf，事务日志的操作系统名称jwgl1.ldf，日志文件初始大小为5MB，最大值100MB，日志文件以2MB增量增加。 程序清单见下页：,15,CREATE DATABASE jwgl1 ON PRIMARY (NAME

13、=jwgl1_data, FILENAME= c:Program FilesMicrosoft SQLServerMSSQL DATAjwgl1.mdf, SIZE=5MB, MAXSIZE=500MB， FILEGROWTH=10%) LOG ON （NAME=jwgl1_log, FILENAME=c:Program FilesMicrosoft SQLServerMSSQL DATAjwgl1.ldf, SIZE=5MB, MAXSIZE=100MB， FILEGROWTH=2MB) GO,16,例5-2：创建一个指定多个数据文件和日志文件的数据库。该数据库名称为jwgl2，有2个10M

14、B的数据文件和2个10MB的事务日志文件。主文件是列表中的第一个文件，并使用PRIMARY关键字显式指定。事务日志文件在LOG ON关键字后指定。注意FILE_NAME选项中所用的文件扩展名：主数据文件使用.mdf，次数据文件使用.ndf，事务文件使用.ldf。 程序清单 CREATE DATABASE jwgl2 ON PRIMARY (NAME = jwgl20_data, FILENAME = c:Program FilesMicrosoft SQLServerMSSQL DATAjwgl20.mdf, SIZE = 10MB, MAXSIZE = 200, FILEGROWTH = 2

15、0),17,( NAME = jwgl21_data, FILENAME = c:Program FilesMicrosoft SQLServerMSSQL DATAjwgl21.ndf, SIZE = 10MB, MAXSIZE = 200, FILEGROWTH = 20) LOG ON ( NAME = jwgl20_log, FILENAME = c:Program FilesMicrosoft SQLServerMSSQL DATAjwgl20.ldf, SIZE = 10MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = jwgl21_log

16、, FILENAME = c:Program FilesMicrosoft SQLServerMSSQL DATAjwgl21.ldf,18,SIZE = 10MB, MAXSIZE = 200, FILEGROWTH = 20) GO 例5-3创建一个包含2个文件组的数据库。该数据库名为 jwgl3，主文件组包含文件 jwgl30_data和 jwgl31_data。文件组jwgl3_group包含文件jwgl32_data和 jwgl33_data。两个文件组数据文件的FILEGROWTH 增量为 15%，数据文件的初始大小为10 MB。事务日志文件的文件名为jwgl3_log，FILEG

17、ROWTH 增量为 15%，日志文件的初始大小为5 MB。 CREATE DATABASE jwgl3 ON PRIMARY ( NAME = jwgl30_data, FILENAME = d:Program FilesMicrosoft SQLServerMSSQL DATAjwgl30.mdf, SIZE = 10MB,19,FILEGROWTH = 15% ), ( NAME = jwgl31_data, FILENAME = d:Program FilesMicrosoft SQLServerMSSQL DATAjwgl31.ndf, SIZE = 10MB, FILEGROWTH

18、= 15% ), FILEGROUP jwgl3_Group ( NAME = jwgl32_data, FILENAME = d:Program FilesMicrosoft SQLServerMSSQL DATAjwgl32.ndf, SIZE = 10MB, FILEGROWTH = 15% ),20,( NAME = jwgl33_data, FILENAME = c:Program FilesMicrosoft SQLServerMSSQL DATAjwgl33.ndf, SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 15% ) LOG ON (

19、 NAME = jwgl3_log, FILENAME = c:Program FilesMicrosoft SQLServerMSSQL DATAjwgl3.ldf , SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 15% ) GO,21,5.3.3 使用SQL Server Management Studio创建数据库 使用SQL Server Management Studio创建数据库比使用T-SQL语句来得更容易; 具体步骤如下: 1）进入SQL Server Management Studio; 2）点击SQL服务器组,进入已经配置注册的服务器,

20、点击”数据库”项; 3）点击“操作”，再选择“新建数据库”或右击数据库，在快捷菜单中选择“新建数据库” 4）该窗口有三个选项卡：常规选项卡、数据文件选项卡、事务日志选项卡。 5）点击“数据文件”页面标签，进行相关设置； 6）点击“事务日志”选项卡，进行相关设置； 7）点击“确定”。,22,5.4 数据库的管理,5.4.1 数据库属性的查看 1.用T-SQL语言查看数据库的属性 使用系统存储过程可以查看数据库的属性。常用的存储过程有sp_helpdb，sp_spaceused，sp_filehelp，sp_helpfilegroup。 查看某个数据库或所有数据库的信息： EXEC sp_help

21、db jwgl1 查看有关数据库中所占用空间的报表； EXEC sp_spaceused 显示有关数据库中文件组的报表： EXEC sp_helpfilegroup 显示数据库中文件的报表： EXEC sp_helpfile 2. 用SQL Server Management Studio查看数据库的属性（P89） 进入SQL Server Management Studio，指向数据库右击，选择“属性”选项即可查看数据库相关信息。,23,5.4.2 修改数据库大小 用T-SQL语言进行数据库属性的设定 例5-4:修改数据库JWGL1日志文件的最大值由100MB改为现在的80MB。 程序清单：

22、 USE jwgl1 GO ALTER DATABASE jwgl1 MODIFY FILE(NAME=jwgl1_log, MAXSIZE=80MB),24,例5-5：修改jwgl1_data数据文件的初始值由5MB改为10MB。 程序清单见（P90） USE jwgl1 GO ALTER DATABASE jwgl1 MODIFY FILE(NAME= jwgl1_data, SIZE=10MB) 应该注意，对于数据库数据文件和事务日志文件初始空间大小的修改，新指定的空间大小值不能小于当前文件初始空间大小值。 2. 用SQL Server Management Studio修改数据库属性

23、进入SQL Server Management Studio ，展开树形结构的“数据库”节点，选中要修改的数据库JWGL，按右键，在系统弹出快捷菜单上点击“属性”，进入JWGL 数据库的属性窗口，你可以根据需要进入不同的选项卡修改数据库相应的信息。,25,5.4.3 数据库的收缩 SQL允许收缩数据库中的每个文件以删除未使用的页。数据和事务日志文件都可以收缩。 数据库文件可以单独地进行手工收缩，也可以设置为按给定的时间间隔进行自动收缩。 注意：不能将整个数据库收缩到比原始大小还要小。 缩小数据库有两种方法： 1使用SQL Server Management Studio可以缩小数据库。 2使用

24、Transact-SQL语言缩小数据库。 选学,26,5.4.4 数据库的更名 一般情况下不要做数据库的改名，如要更名需将数据库的并发用户数设为1，更名后再将数据库的并发用户数改回来。设置数据库并发用户的方法是：先点击要改名的数据库，再依次点击“工具”、“SQL Server 配置属性”、“连接”，在“并发用户连接的最大数目”输入框中输入相应的数字即可。 数据库的更名的SQL 语句的语法形式： sp_renamedb old_name , new_name 其中： old_name: 是数据库的当前名称; new_name: 是数据库的新名称。 【例5-8】将数据库JWGL1更名为JWXT，SQL脚本程序如下： sp_renamedb jwgl1,jwxt,