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

《SQL第05章数据库的创建与管理.ppt》由会员分享，可在线阅读，更多相关《SQL第05章数据库的创建与管理.ppt（62页珍藏版）》请在三一办公上搜索。

1、第5章 数据库的创建和管理,5.1 数据库简介5.2 估算数据库的空间需求5.3 数据库的创建5.4 数据库的管理 5.5 设置数据库选项,返回目录,2,5.1 数据库简介,数据库创建的过程就是数据库逻辑设计的物理实现过程。数据库的创建主要包括数据库及其表、索引、视图、存储过程等对象的创建。创建数据库时,系统会建立数据库的存储结构。数据库的存储结构分为逻辑存储结构和物理存储结构两种。,3,数据库的物理存储结构是讨论数据库文件是如何在磁盘上存储的，数据库在磁盘上是以文件为单位存储的，由数据库文件和事务日志文件组成。一个数据库至少应该包含一个主数据库文件和一个事务日志文件。,5.1 数据库简介,4

2、,1主数据库文件（Primary Database File）一个数据库可以有一个或多个数据库文件，一个数据库文件只能属于一个数据库。当有多个数据库文件时，有一个文件被定义为主数据库文件（简称为主文件），其扩展名为mdf。主数据库文件用来存储数据库的启动信息以及部分或者全部数据，是所有数据库文件的起点，包含指向其它数据库文件的指针。一个数据库只能有一个主数据库文件。,5.1 数据库简介,5,2辅助数据库文件（Secondary Database File）用于存储主数据库文件中未存储的剩余数据和数据库对象，一个数据库可以没有辅助数据库文件，但也可以同时拥有多个辅助数据库文件。辅助数据库文件的扩

3、展名为ndf（简称为辅助文件）。,5.1 数据库简介,6,5.1.1 数据库的存储结构1.盘区和页面在创建数据库对象时,SQL Server会使用盘区和页面的数据结构给数据库对象分配空间。数据存储的基本单位是页。页的大小是8KB，这样数据库上每一兆字节是128页。每页的开始部分是96B的页首，用于存放系统信息，如页的类型、页的可用空间、占据该页的数据库对象的对象标识等。页有数据页、索引页、文本/图象页等8种.,7,5.1.1 数据库的存储结构1.盘区和页面盘区（区域）是连续8个页，可防止数据产生碎片，使SQL易于寻找。SQL有两种盘区组织页面：单一区域和混合区域。单一区域用于一个对象，混合区域

4、用于多个小对象。事务日志不被分成页和区域，而是包含已修改数据的清单，按先来先服务的原则组织。,8,2.文件组文件组是文件的集合。对文件进行分组，便于进行数据的管理和分配磁盘空间。例如有三个数据文件data1.ndf,data2.ndf,data3.ndf,分别位于不同的磁盘上，将这三个文件指派到文件组filegroup1中。假设在文件组filegroup1上创建一个表，对表中数据的查询将会分散到三个磁盘上，从而提高系统的查询性能。,9,2.文件组文件和文件组的设计规则:文件和文件组不能由一个以上的数据库使用;文件只能是一个文件组的成员;事务日志文件文件不属于任何一个文件组。注意：数据和事务日志

5、不能属于同一文件或文件组。数据文件和日志文件总是分开存放的。,10,主文件组中包含了所有的系统表，当建立数据库时，主文件组包括主数据库文件和未指定组的其他文件。用户定义文件组中可以指定一个缺省文件组，那么在创建数据库对象时如果没有指定将其放在哪一个文件组中，就会将它放在缺省文件组中。如果没有指定缺省文件组，则主文件组为缺省文件组。一个文件只能存在于一个文件组中，一个文件组也只能被一个数据库使用；日志文件是独立的，它不能作为任何文件组的成员。,11,5.1.2 事务日志文件 SQL创建数据库的时候,会同时创建事务日志文件。事务日志文件存储数据库的更新情况等事务日志信息，当数据库损坏时，管理员使用

6、事务日志恢复数据库。每一个数据库至少必须拥有一个事务日志文件，而且允许拥有多个日志文件。事务日志文件的扩展名为ldf，日志文件的大小至少是512KB。,12,1.事务日志文件和数据文件必须分开存放，优点如下：事务日志文件可以单独备份；有可能从服务器失效的事件中将服务器恢复到最近的状态；事务日志不会抢占数据库的空间；可以很容易地监测到事务日志的空间；在向数据文件和事务日志文件写入数据时会产生较少的冲突，这有利于提高SQL的性能。SQL Server事务日志采用提前写入的方式。,13,2.事务日志的工作过程在SQL中，事务是一次完成的操作的集合。虽然一个事务中可能包含了很多SQL语句，但在处理上就

7、象他们是同一个操作似的。为了维护数据的完整性，事务必须彻底完成或者根本不执行。如果一个事务只是部分地被执行，并作用于数据库，那么数据库将可能被损坏或数据的一致性遭到破坏。SQL Server使用数据库的事务日志来防止没有完成的事务破坏数据。,14,事务日志的工作过程：应用程序发出一个修改数据库中的对象的事务；当这个事务开始时，事务日志会记录一个事务开始标志，并将被影响的数据页从磁盘读入缓冲区；事务中每个数据更改语句都被记录在日志文件中，日志文件将记录一个提交事务的标记。每一个事务都会以这种方式记录在事务日志中并被立即写到硬盘上。在缓冲区中修改相应的数据。这些数据一直在缓冲区中，在检查点进程发生

8、时，检查点进程把所有修改过的数据页写到数据库中，并在事务日志中写入一个检查点标志，这个标志用于在数据库恢复过程中确定事务的起点和终点。以及哪些事务已经作用于数据库 了。,15,事务日志文件包含有在系统故障下恢复数据库需要的所有信息。一般来说，事务日志文件的初始大小是以数据文件大小的10%到25%为起点的，根据数据增长的情况和修改的频率进行调整。SQL Server 2005的文件拥有两个名称，即逻辑文件名和物理文件名。当使用Transact-SQL命令语句访问某一个文件时，必须使用该文件的逻辑名。物理文件名是文件实际存储在磁盘上的文件名，而且可包含完整的磁盘目录路径。,16,5.2 估算数据库

9、的空间需求,作为数据库管理员，主要任务之一就是创建数据库，并且需要为每个文件指定容量。必须尽可能准确地估算数据库容量，以免浪费磁盘空间资源或者因估计不足造成数据库的空间不够。,17,5.2 估算数据库的空间需求,许多因素会影响数据库最终的大小，在估算数据库容量时要考虑如下因素：每行记录的大小；记录数量；表的数量；索引的数量及索引大小；数据库的对象的数量和大小；事务日志的大小；数据库的计划增加量；,18,5.3 创建数据库,创建数据库的两种方法：1.使用SQL Server Management Studio创建数据库 2.使用Transact-SQL语言创建数据库,19,5.3 创建数据库,5

10、.3.1 创建数据库的注意事项:创建数据库需要一定许可，在默认情况下，只有系统管理员和数据库拥有者可以创建数据库。创建数据库的过程实际上就是为数据库设计名称、设计所占用的存储空间和存放文件位置的过程等，数据库名字必须遵循SQL Server命名规范。,20,5.3 创建数据库,5.3.1 创建数据库的注意事项:所有的新数据库都是系统样本数据库model的副本。单个数据库可以存储在单个文件上，也可以跨越多外文件存储。5.数据库的大小可以被增大或者收缩。6.当新的数据库创建时，SQL Server自动更新“sysdatabases”系统表。7.一台服务器上最多可能创建32767个数据库。,21,5

11、.3.2 使用T-SQL语言创建数据库1.创建数据库的SQL语句的语法格式:CREATE DATABASE database_name ON PRIMARY，n，n LOG ON，n FOR LOAD|FOR ATTACH 括起来的内容表示是可选的，n表示重复前面的内容用括起来表示在实际编写语句时，用相应的内容替代；用 括起来表示是必选的，A|B的格式，表示A和B只能选择一个,22,其中：:=NAME=logical_file_name，FILENAME=os_file_name，SIZE=size，MAXSIZE=max_size|UNLIMITED，FILEGROWTH=growth_in

12、crement，n:=FILEGROUP filegroup_name，n,23,各参数的含义（如下）：database_name：新数据库的名称。数据库名称在服务器中必须唯一，最长为128个字符，并且要符合标识符的命名规则。每个服务器管理的数据库最多为32767个。ON：指定存放数据库的数据文件信息。列表用于定义主文件组的数据文件，列表用于定义用户文件组及其中的文件。,24,PRIMARY：用于指定主文件组中的文件。主文件组的第一个由指定的文件是主文件。如果不指定PRIMARY关键字，则在命令中列出的第一个文件将被默认为主文件。LOG ON：指明事务日志文件的明确定义。如果没有本选项，则系统

13、会自动产生一个文件名前缀与数据库名相同，容量为所有数据库文件大小1/4的事务日志文件。FOR LOAD：表示计划将备份直接装入新建的数据库，主要是为了和过去的SQL Server版本兼容。FOR ATTACH：表示在一组已经存在的操作系统文件中建立一个新的数据库。,25,NAME：指定数据库的逻辑名称。FILENAME：指定数据库所在文件的操作系统文件名称和路径，该操作系统文件名和NAME的逻辑名称一一对应。SIZE：指定数据库的初始容量大小。Size中不能使用小数，其最小值为512KB，默认值为1MB。主文件的size不能小于模板数据库中的主文件。,26,MAXSIZE：指定操作系统文件可以

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

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

16、ABASE jwgl1 ON PRIMARY(NAME=jwgl1_data,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQLDatajwgl1.mdf,SIZE=5MB,MAXSIZE=500MB,FILEGROWTH=10%)LOG ON(NAME=jwgl1_log,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQLDatajwgl1.ldf,SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=2MB)GO,30,例5-2：创建一个指定多个数据文件和日志文件的数据库。该数据库

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

18、WTH=20),(NAME=jwgl21_data,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQLDatajwgl21.ndf,SIZE=10MB,MAXSIZE=200,FILEGROWTH=20),32,LOG ON(NAME=jwgl20_log,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQLDatajwgl20.ldf,SIZE=10MB,MAXSIZE=200,FILEGROWTH=20),(NAME=jwgl21_log,FILENAME=C:Program FilesMicro

19、soft SQL ServerMSSQLDatajwgl21.ldf,SIZE=10MB,MAXSIZE=200,FILEGROWTH=20)GO,33,例5-3创建一个包含2个文件组的数据库。该数据库名为 jwgl3，主文件组包含文件 jwgl30_data和 jwgl31_data。文件组jwgl3_group包含文件jwgl32_data和 jwgl33_data。两个文件组数据文件的FILEGROWTH 增量为 15%，数据文件的初始大小为10 MB。事务日志文件的文件名为jwgl3_log，FILEGROWTH 增量为 15%，日志文件的初始大小为5 MB。,34,CREATE DA

20、TABASE jwgl3ON PRIMARY(NAME=jwgl30_data,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQLDatajwgl30.mdf,SIZE=10MB,FILEGROWTH=15%),(NAME=jwgl31_data,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQLDatajwgl31.ndf,SIZE=10MB,FILEGROWTH=15%),35,FILEGROUP jwgl3_Group(NAME=jwgl32_data,FILENAME=C:Program F

21、ilesMicrosoft SQL ServerMSSQLDatajwgl32.ndf,SIZE=10MB,FILEGROWTH=15%),(NAME=jwgl33_data,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQLDatajwgl33.ndf,SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=15%),36,LOG ON(NAME=jwgl3_log,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQLDatajwgl3.ldf,SIZE=5MB,MAXSIZE=25MB

22、,FILEGROWTH=15%)GO,37,5.3.3 使用SQL Server Management Studio创建数据库具体步骤如下:1）进入SQL Server Management Studio;2）点击SQL服务器组,进入已经配置注册的服务器,点击”数据库”项;3）点击“操作”，再选择“新建数据库”或右击数据库，在快捷菜单中选择“新建数据库”4）该窗口有三个选项卡：常规选项卡、数据文件选项卡、事务日志选项卡。5）点击“数据文件”页面标签，进行相关设置；6）点击“事务日志”选项卡，进行相关设置；7）点击“确定”。,38,5.3.4 由已有库生成创建数据库的脚本由已建数据库JWGL生成

23、该库的SQL脚本的步骤:1）选择数据库JWGL，按右键，选择“所有任务”；2）选择“生成SQL脚本”，便会产生一个“生成SQL脚本”的对话窗口；3）点击“选项”标签，点击不同的选项，可生成不同的脚本；4）点击“编写数据库脚本”复选框和“编写数据库用户和数据库角色脚本”复选框，再击点“确定”，输入脚本的文件名db_cre_text后便会生成创建数据库JWGL及JWGL库的用户和角色的脚本文件 db_cre_text.sql。如要验证脚本的正确性，可进入查询分析器，调入该脚本程序执行即可。,39,5.4 数据库的管理,5.4.1 数据库属性的查看1.用T-SQL语言查看数据库的属性使用系统存储过程

24、可以查看数据库的属性。常用的存储过程有sp_helpdb，sp_spaceused，sp_filehelp，sp_helpfilegroup。查看某个数据库或所有数据库的信息：EXEC sp_helpdb jwgl1查看有关数据库中所占用空间的报表；EXEC sp_spaceused显示有关数据库中文件组的报表：EXEC sp_helpfilegroup 显示数据库中文件的报表：EXEC sp_helpfile,40,5.4 数据库的管理,5.4.1 数据库属性的查看2.用SQL Server Management Studio查看数据库的属性（P89）进入SQL Server Managem

25、ent Studio，指向数据库右击，选择“属性”选项即可查看数据库相关信息。,41,5.4.2 修改数据库大小用T-SQL语言进行数据库属性的设定例5-4:修改数据库JWGL1日志文件的最大值由100MB改为现在的80MB。程序清单：USE jwgl1GOALTER DATABASE jwgl1MODIFY FILE(NAME=jwgl1_log,MAXSIZE=80MB),42,例5-5：修改jwgl1_data数据文件的初始值由5MB改为10MB。程序清单见（P90）USE jwgl1GOALTER DATABASE jwgl1MODIFY FILE(NAME=jwgl1_data,SI

26、ZE=10MB)应该注意，对于数据库数据文件和事务日志文件初始空间大小的修改，新指定的空间大小值不能小于当前文件初始空间大小值。,43,2.用SQL Server Management Studio修改数据库属性进入SQL Server Management Studio，展开树形结构的“数据库”节点，选中要修改的数据库JWGL，按右键，在系统弹出快捷菜单上点击“属性”，进入JWGL 数据库的属性窗口，你可以根据需要进入不同的选项卡修改数据库相应的信息。,44,5.4.3 数据库的收缩SQL允许收缩数据库中的每个文件以删除未使用的页。数据和事务日志文件都可以收缩。数据库文件可以单独地进行手工收

27、缩，也可以设置为按给定的时间间隔进行自动收缩。注意：不能将整个数据库收缩到比原始大小还要小。,45,5.4.3 数据库的收缩缩小数据库有两种方法：1使用SQL Server Management Studio可以缩小数据库。2使用Transact-SQL语言缩小数据库。（1）使用DBCC SHRINKDATABASE命令收缩指定数据库中的数据文件。DBCC SHRINKDATABASE(database_name,target_percent,NOTRUNCATE|TRUNCATEONLY),46,database_name：要收缩的数据库名称。Target_percent：当数据库收缩后，数

28、据库文件中剩余可用空间的百分比。NOTRUNCATE：被释放的文件空间依然保持在数据库文件中。如果未指定，将所释放的文件空间被操作系统回收。TRUNCATEONLY：将数据文件中未使用的空间释放给操作系统，并将文件收缩到上一次所分配的大小。使用 TRUNCATEONLY 时，将忽略 target_percent的限制。,47,将数据库设为自动收缩使用ALTER DATABASE语句可以将数据库设为自动收缩。设定数据库是否自动收缩的SQL 语句的语法形式：ALTER DATABASE database_name SET AUTO_SHRINK on/offon：数据库设为自动收缩，off：数据库

29、设为不自动收缩。,48,【例5-7】将数据库JWGL1的收缩设为自动收缩，SQL脚本程序如下：ALTER DATABASE jwgl1 SET AUTO_SHRINK on,49,5.4.4 数据库的更名一般情况下不要做数据库的改名。如要更名，设置数据库并发用户的方法是：右点击要改名的数据库，选择“属性”再点击“选项”“状态”、“限制访问”项设为“single”更改数据库名称注意：更改完名称后，需将“限制访问”改为原来的值,50,5.4.4 数据库的更名数据库的更名的SQL 语句的语法形式：sp_renamedb old_name,new_name 其中：old_name:是数据库的当前名称;

30、new_name:是数据库的新名称。【例5-8】将数据库JWGL1更名为JWXT，SQL脚本程序如下：sp_renamedb jwgl1,jwxt,51,5.4.5 删除数据库 1利用SQL Server Management Studio删除数据库（P92）2利用Drop语句删除数据库 Drop database database_name,n 说明：只有处于正常状态下的数据库，才能使用DROP语句删除。当数据库处于以下状态时不能被删除：数据库正在使用；数据库正在恢复；系统数据库master、tempdb、model、msdb。,52,5.4.5 删除数据库 例5-9 使用DROP DATA

31、BASE 命令将数据库JWXT删除。DROP database jwxt,53,5.5 设置数据库的选项,数据库选项就是数据库的属性。这些选项将决定数据库的不同部分如何工作，可以使用SQLSQL Server Management Studio或者“sp_dboption”存储过程来修改数据库选项。,54,5.5 设置数据库的选项,5.5.1 使用Transact-SQL语句设置数据库的选项使用存储过程sp_dboption可以显示或更改数据库选项。但不能对master或tempdb数据库使用sp_dboption。设置数据库的选项的SQL语句的语法形式如下：sp_dboption datab

32、ase option_namevalue 其中：database：在其中设置指定选项的数据库的名称。option_name：要设置的选项的名称。,55,5.5 设置数据库的选项,5.5.1 使用Transact-SQL语句设置数据库的选项option_name该选项的参数及含义如下：autoshrink:当value为true时，数据库文件将成为自动周期性收缩的候选文件。dbo use only:当value为true时，只有数据库所有者可以使用数据库。read only:当value为true时，用户仅能读取数据库中的数据而无法对其修改。single use：当value为true时，每次只

33、能有一个用户访问数据库。value：option_name的新设置。如果省略此参数，sp_dboption 将返回当前设置。value 可以是 true、false、或 on、off。,56,【例5-10】下面的示例将数据库JWGL设置为只有数据库所有者可以使用。USE jwgl EXEC sp_dboption jwgl,dbo use only,true,57,5.5.2 利用SQL Server Management Studio修改数据库属性 展开服务器组，然后展开服务器。展开“数据库”文件夹，出现这个SQL Server上当前所有数据库的列表。右击你打算设置的数据库，选择“属性”，出

34、现如图5-3(P94)的数据库的属性窗口;单击该数据库属性窗口“选项”选项卡，出现数据库的各个选项。,58,5.5.2 利用SQL Server Management Studio修改数据库属性用户可以进行如下常用选项的设置：访问限制：指定只有属于db_owner、dbcreator或sysadmin的成员才能访问数据库。单用户：只能有一个用户使用数据库。这个选项通常用于在执行一些诸如“DBCC SHRINKDB”的命令时阻止用户访问这个数据库。只读：使这个数据库处于只读状态。如数据库设为只读状态，用户就不能修改数据库中的任何记录。模型：指定数据库的恢复模型ANSI NULL默认设置：允许在数

35、据库表的列中输入空（NULL）值。,59,限制访问包含两个选项：“db_owner,dbcreator或sysadmin的成员”选项表示只有数据库的所有者、数据库创建者和系统管理员才有权使用数据库；“单用户”选项表示数据库在同一时间只能供一个用户使用。递归触发器：指定是否允许触发器递归调用。自动更新统计信息：允许使用SELECT INTO或BCP、WRITETEXT、UPDATETEXT命令向表中大量插入数据。在开发数据库时常常将此选项设置为真。,60,残缺页检测：允许自动检测有损坏的页。，果写完第一个扇区后发生突发事件，导致写入中断，就会产生有损坏的页，需要通知备份来恢复数据库。自动关闭：当数据库中无用户时，自动关闭该数据库，并将所占用的资源交还给操作系统。对那些不间断使用的数据库不要使用此选项。,61,自动收缩：允许定期对数据库进行检查，当数据库文件或日志文件中未用空间超过其大小的25%时，系统将会自动缩减文件，使其未用空间等于25%。当文件大小没有超过其建立时的初始大小时，不会缩减文件。缩减后的文件也必须大于或等于其初始大小。自动创建统计信息：在优化查询时，根据需要自动创建统计信息。使用被引用的标识符：标识符必须用双引号括起来，且可以不遵循Transact-SQL命名标准。,62,小结了解数据库的简介掌握数据库的创建与管理学会设置数据库的选项,