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

第5章 数据库的创建和管理,5.1 数据库简介 5.2 估算数据库的空间需求 5.3 数据库的创建 5.4 数据库的管理 5.5 设置数据库选项,返回目录,2,5.1 数据库简介,数据库创建的过程就是数据库逻辑设计的物理实现过程。 数据库的创建主要包括数据库及其表、索引、视图、存储过程等对象的创建。 创建数据库时,系统会建立数据库的存储结构。 数据库的存储结构分为逻辑存储结构和物理存储结构两种。,3,数据库的物理存储结构是讨论数据库文件是如何在磁盘上存储的，数据库在磁盘上是以文件为单位存储的，由数据库文件和事务日志文件组成。 一个数据库至少应该包含一个主数据库文件和一个事务日志文件。,5.1 数据库简介,4,1主数据库文件（Primary Database File） 一个数据库可以有一个或多个数据库文件，一个数据库文件只能属于一个数据库。 当有多个数据库文件时，有一个文件被定义为主数据库文件（简称为主文件），其扩展名为mdf。 主数据库文件用来存储数据库的启动信息以及部分或者全部数据，是所有数据库文件的起点，包含指向其它数据库文件的指针。 一个数据库只能有一个主数据库文件。,5.1 数据库简介,5,2辅助数据库文件（Secondary Database File） 用于存储主数据库文件中未存储的剩余数据和数据库对象，一个数据库可以没有辅助数据库文件，但也可以同时拥有多个辅助数据库文件。 辅助数据库文件的扩展名为ndf（简称为辅助文件）。,5.1 数据库简介,6,5.1.1 数据库的存储结构 1.盘区和页面 在创建数据库对象时,SQL Server会使用盘区和页面的数据结构给数据库对象分配空间。 数据存储的基本单位是页。页的大小是8KB，这样数据库上每一兆字节是128页。 每页的开始部分是96B的页首，用于存放系统信息，如页的类型、页的可用空间、占据该页的数据库对象的对象标识等。 页有数据页、索引页、文本/图象页等8种.,7,5.1.1 数据库的存储结构 1.盘区和页面 盘区（区域）是连续8个页，可防止数据产生碎片，使SQL易于寻找。 SQL有两种盘区组织页面：单一区域和混合区域。单一区域用于一个对象，混合区域用于多个小对象。 事务日志不被分成页和区域，而是包含已修改数据的清单，按先来先服务的原则组织。,8,2.文件组 文件组是文件的集合。对文件进行分组，便于进行数据的管理和分配磁盘空间。 例如有三个数据文件data1.ndf,data2.ndf,data3.ndf,分别位于不同的磁盘上，将这三个文件指派到文件组filegroup1中。假设在文件组filegroup1上创建一个表，对表中数据的查询将会分散到三个磁盘上，从而提高系统的查询性能。,9,2.文件组 文件和文件组的设计规则: 文件和文件组不能由一个以上的数据库使用; 文件只能是一个文件组的成员; 事务日志文件文件不属于任何一个文件组。 注意：数据和事务日志不能属于同一文件或文件组。数据文件和日志文件总是分开存放的。,10,主文件组中包含了所有的系统表，当建立数据库时，主文件组包括主数据库文件和未指定组的其他文件。 用户定义文件组中可以指定一个缺省文件组，那么在创建数据库对象时如果没有指定将其放在哪一个文件组中，就会将它放在缺省文件组中。如果没有指定缺省文件组，则主文件组为缺省文件组。 一个文件只能存在于一个文件组中，一个文件组也只能被一个数据库使用；日志文件是独立的，它不能作为任何文件组的成员。,11,5.1.2 事务日志文件 SQL创建数据库的时候,会同时创建事务日志文件。 事务日志文件存储数据库的更新情况等事务日志信息 ，当数据库损坏时，管理员使用事务日志恢复数据库。 每一个数据库至少必须拥有一个事务日志文件，而且允许拥有多个日志文件。事务日志文件的扩展名为ldf，日志文件的大小至少是512KB。,12,1. 事务日志文件和数据文件必须分开存放，优点如下： 事务日志文件可以单独备份； 有可能从服务器失效的事件中将服务器恢复到最近的状态； 事务日志不会抢占数据库的空间； 可以很容易地监测到事务日志的空间； 在向数据文件和事务日志文件写入数据时会产生较少的冲突，这有利于提高SQL的性能。 SQL Server事务日志采用提前写入的方式 。,13,2. 事务日志的工作过程 在SQL中，事务是一次完成的操作的集合。虽然一个事务中可能包含了很多SQL语句，但在处理上就象他们是同一个操作似的。 为了维护数据的完整性，事务必须彻底完成或者根本不执行。 如果一个事务只是部分地被执行，并作用于数据库，那么数据库将可能被损坏或数据的一致性遭到破坏。 SQL Server使用数据库的事务日志来防止没有完成的事务破坏数据。,14,事务日志的工作过程： 应用程序发出一个修改数据库中的对象的事务； 当这个事务开始时，事务日志会记录一个事务开始标志，并将被影响的数据页从磁盘读入缓冲区； 事务中每个数据更改语句都被记录在日志文件中，日志文件将记录一个提交事务的标记。每一个事务都会以这种方式记录在事务日志中并被立即写到硬盘上。 在缓冲区中修改相应的数据。这些数据一直在缓冲区中，在检查点进程发生时，检查点进程把所有修改过的数据页写到数据库中，并在事务日志中写入一个检查点标志，这个标志用于在数据库恢复过程中确定事务的起点和终点。以及哪些事务已经作用于数据库 了。,15,事务日志文件包含有在系统故障下恢复数据库需要的所有信息。一般来说，事务日志文件的初始大小是以数据文件大小的10%到25%为起点的，根据数据增长的情况和修改的频率进行调整。 SQL Server 2005的文件拥有两个名称，即逻辑文件名和物理文件名。当使用Transact-SQL命令语句访问某一个文件时，必须使用该文件的逻辑名 。 物理文件名是文件实际存储在磁盘上的文件名，而且可包含完整的磁盘目录路径。,16,5.2 估算数据库的空间需求,作为数据库管理员，主要任务之一就是创建数据库，并且需要为每个文件指定容量。 必须尽可能准确地估算数据库容量，以免浪费磁盘空间资源或者因估计不足造成数据库的空间不够。,17,5.2 估算数据库的空间需求,许多因素会影响数据库最终的大小，在估算数据库容量时要考虑如下因素： 每行记录的大小； 记录数量； 表的数量； 索引的数量及索引大小； 数据库的对象的数量和大小； 事务日志的大小； 数据库的计划增加量；,18,5.3 创建数据库,创建数据库的两种方法： 1.使用SQL Server Management Studio创建数据库 2.使用Transact-SQL语言创建数据库,19,5.3 创建数据库,5.3.1 创建数据库的注意事项: 创建数据库需要一定许可，在默认情况下，只有系统管理员和数据库拥有者可以创建数据库。 创建数据库的过程实际上就是为数据库设计名称、设计所占用的存储空间和存放文件位置的过程等，数据库名字必须遵循SQL Server命名规范。,20,5.3 创建数据库,5.3.1 创建数据库的注意事项: 所有的新数据库都是系统样本数据库model的副本。 单个数据库可以存储在单个文件上，也可以跨越多外文件存储。 5. 数据库的大小可以被增大或者收缩。 6. 当新的数据库创建时，SQL Server自动更新“sysdatabases”系统表。 7. 一台服务器上最多可能创建32767个数据库。,21,5.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_increment ，n := FILEGROUP filegroup_name ，n,23,各参数的含义（如下）： database_name：新数据库的名称。数据库名称在服务器中必须唯一，最长为128个字符，并且要符合标识符的命名规则。每个服务器管理的数据库最多为32767个。 ON：指定存放数据库的数据文件信息。列表用于定义主文件组的数据文件，列表用于定义用户文件组及其中的文件。,24,PRIMARY：用于指定主文件组中的文件。主文件组的第一个由指定的文件是主文件。如果不指定PRIMARY关键字，则在命令中列出的第一个文件将被默认为主文件。 LOG ON：指明事务日志文件的明确定义。如果没有本选项，则系统会自动产生一个文件名前缀与数据库名相同，容量为所有数据库文件大小1/4的事务日志文件。 FOR LOAD：表示计划将备份直接装入新建的数据库，主要是为了和过去的SQL Server版本兼容。 FOR ATTACH：表示在一组已经存在的操作系统文件中建立一个新的数据库。,25,NAME：指定数据库的逻辑名称。 FILENAME：指定数据库所在文件的操作系统文件名称和路径，该操作系统文件名和NAME的逻辑名称一一对应。 SIZE：指定数据库的初始容量大小。Size中不能使用小数，其最小值为512KB，默认值为1MB。主文件的size不能小于模板数据库中的主文件。,26,MAXSIZE：指定操作系统文件可以增长到的最大尺寸。如果没有指定，则文件可以不断增长直到充满磁盘。 FILEGROWTH：指定文件每次增加容量的大小，当指定数据为0时，表示文件不增长。 增加量可以确定为以KB、MB作后缀的字节数或以%作后缀的被增加容量文件的百分比来表示。默认后缀为MB。 如果没有指定FILEGROWTH，则默认值为10%，每次扩容的最小值为64KB。,27,2. 几点说明: 1）创建用户数据库后，要备份master数据库。 2）所有数据库都至少包含一个主文件组。所有系统表都分配在主文件组中。数据库还可以包含用户定义的文件组。 3）每个数据库都有一个所有者，可在数据库中执行某些特殊的活动。数据库所有者是创建数据库的用户，也可以使用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 DATABASE 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：创建一个指定多个数据文件和日志文件的数据库。 该数据库名称为jwgl2，有2个10MB的数据文件和2个10MB的事务日志文件。 主文件是列表中的第一个文件，并使用PRIMARY关键字显式指定。 事务日志文件在LOG ON关键字后指定。 注意FILE_NAME选项中所用的文件扩展名：主数据文件使用.mdf，次数据文件使用.ndf，事务文件使用.ldf。 程序清单,31,程序清单 CREATE DATABASE jwgl2 ON PRIMARY (NAME = jwgl20_data, FILENAME = C:Program FilesMicrosoft SQL ServerMSSQLDatajwgl20.mdf, SIZE = 10MB, MAXSIZE = 200, FILEGROWTH = 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 FilesMicrosoft 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 DATABASE jwgl3 ON 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 FilesMicrosoft 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, 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生成该库的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语言查看数据库的属性 使用系统存储过程可以查看数据库的属性。常用的存储过程有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 Management Studio，指向数据库右击，选择“属性”选项即可查看数据库相关信息。,41,5.4.2 修改数据库大小 用T-SQL语言进行数据库属性的设定 例5-4:修改数据库JWGL1日志文件的最大值由100MB改为现在的80MB。 程序清单： USE jwgl1 GO ALTER DATABASE jwgl1 MODIFY FILE(NAME=jwgl1_log, MAXSIZE=80MB),42,例5-5：修改jwgl1_data数据文件的初始值由5MB改为10MB。 程序清单见（P90） USE jwgl1 GO ALTER DATABASE jwgl1 MODIFY FILE(NAME= jwgl1_data, SIZE=10MB) 应该注意，对于数据库数据文件和事务日志文件初始空间大小的修改，新指定的空间大小值不能小于当前文件初始空间大小值。,43,2. 用SQL Server Management Studio修改数据库属性 进入SQL Server Management Studio ，展开树形结构的“数据库”节点，选中要修改的数据库JWGL，按右键，在系统弹出快捷菜单上点击“属性”， 进入JWGL 数据库的属性窗口，你可以根据需要进入不同的选项卡修改数据库相应的信息。,44,5.4.3 数据库的收缩 SQL允许收缩数据库中的每个文件以删除未使用的页。数据和事务日志文件都可以收缩。 数据库文件可以单独地进行手工收缩，也可以设置为按给定的时间间隔进行自动收缩。 注意：不能将整个数据库收缩到比原始大小还要小。,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：当数据库收缩后，数据库文件中剩余可用空间的百分比。 NOTRUNCATE：被释放的文件空间依然保持在数据库文件中。如果未指定，将所释放的文件空间被操作系统回收。 TRUNCATEONLY：将数据文件中未使用的空间释放给操作系统，并将文件收缩到上一次所分配的大小。使用 TRUNCATEONLY 时，将忽略 target_percent的限制。,47, 将数据库设为自动收缩 使用ALTER DATABASE语句可以将数据库设为自动收缩。设定数据库是否自动收缩的SQL 语句的语法形式： ALTER DATABASE database_name SET AUTO_SHRINK on/off on ：数据库设为自动收缩，off ：数据库设为不自动收缩。,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: 是数据库的当前名称; 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 DATABASE 命令将数据库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 database 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时，每次只能有一个用户访问数据库。 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上当前所有数据库的列表。 右击你打算设置的数据库，选择“属性”，出现如图5-3(P94)的数据库的属性窗口; 单击该数据库属性窗口“选项”选项卡，出现数据库的各个选项。,58,5.5.2 利用SQL Server Management Studio修改数据库属性 用户可以进行如下常用选项的设置： 访问限制：指定只有属于db_owner、dbcreator或sysadmin的成员才能访问数据库。 单用户：只能有一个用户使用数据库。这个选项通常用于在执行一些诸如“DBCC SHRINKDB”的命令时阻止用户访问这个数据库。 只读：使这个数据库处于只读状态。如数据库设为只读状态，用户就不能修改数据库中的任何记录。 模型：指定数据库的恢复模型 ANSI NULL默认设置：允许在数据库表的列中输入空（NULL）值。,59,限制访问包含两个选项：“db_owner, dbcreator或sysadmin的成员”选项表示只有数据库的所有者、数据库创建者和系统管理员才有权使用数据库；“单用户” 选项表示数据库在同一时间只能供一个用户使用。 递归触发器：指定是否允许触发器递归调用。 自动更新统计信息：允许使用SELECT INTO或BCP、WRITETEXT、UPDATETEXT命令向表中大量插入数据。在开发数据库时常常将此选项设置为真。,60,残缺页检测：允许自动检测有损坏的页。，果写完第一个扇区后发生突发事件，导致写入中断，就会产生有损坏的页，需要通知备份来恢复数据库。 自动关闭：当数据库中无用户时，自动关闭该数据库，并将所占用的资源交还给操作系统。对那些不间断使用的数据库不要使用此选项。,61,自动收缩：允许定期对数据库进行检查，当数据库文件或日志文件中未用空间超过其大小的25%时，系统将会自动缩减文件，使其未用空间等于25%。当文件大小没有超过其建立时的初始大小时，不会缩减文件。缩减后的文件也必须大于或等于其初始大小。 自动创建统计信息：在优化查询时，根据需要自动创建统计信息。 使用被引用的标识符：标识符必须用双引号括起来，且可以不遵循Transact-SQL命名标准。,62,小结 了解数据库的简介 掌握数据库的创建与管理 学会设置数据库的选项,