SQL项目3-2数据库的创建与管理.ppt

项目3-2数据库的创建和管理,3-2.1数据库简介3-2.2估算数据库的空间需求3-2.3数据库的创建3-2.4数据库的管理3-2.5设置数据库选项,2,目标：1.掌握创建数据库的两种方法，理解数据库的逻辑文件名和物理文件名的含义；2.掌握数据库管理的常用操作：查看数据库属性、修改数据库大小、收缩数据库、数据库的更名和删除；3.掌握设置数据库选项的常用操作。,项目3-2数据库的创建和管理,3,3-2.1数据库简介,数据库创建的过程就是数据库逻辑设计的物理实现过程。如果能根据ER模型并结合单位的实际情况创建出一个性能优异、结构合理并具有良好扩充性能的数据库，将会为后在数据库基础上开发应用程序奠定坚实的基础，这是数据库管理最重要、最核心的问题。数据库的创建主要包括数据库及其表、索引、视图、存储过程等对象的创建。创建数据库时，系统会建立数据库的存储结构。数据库的存储结构分为逻辑存储结构和物理存储结构两种。数据库的逻辑存储结构指的是数据库是由哪些性质的信息所组成。实际上，SQLServer的数据库是由诸如表、视图、索引等各种不同的数据库对象所组成。,4,数据库的物理存储结构是讨论数据库文件是如何在磁盘上存储的，数据库在磁盘上是以文件为单位存储的，由数据库文件和事务日志文件组成，一个数据库至少应该包含一个数据文件和一个事务日志文件。1主数据文件（PrimaryDatabaseFile）一个数据库可以有一个或多个数据文件，一个数据文件只能属于一个数据库。当有多个数据文件时，有一个文件被定义为主数据文件（简称为主文件），其扩展名为mdf。主数据文件用来存储数据库的启动信息以及部分或者全部数据，是所有数据库文件的起点，包含指向其它数据库文件的指针。一个数据库只能有一个主数据文件。2次数据文件（SecondaryDatabaseFile）用于存储主数据文件中未存储的剩余数据和数据库对象，一个数据库可以没有次数据文件，但也可以同时拥有多个次数据文件。次数据文件的扩展名为ndf（简称为次文件）。,5,3-2.1.1数据库的存储结构1.盘区和页面在创建数据库对象时,SQLServer会使用盘区和页面这种特殊的数据结构给数据库对象分配空间。在SQLServer中，数据存储的基本单位是页。页的大小是8KB，这样数据库上每MB是128页。每页的开始部分是96B的页首，用于存放系统信息，如页的类型、页的可用空间、占据该页的数据库对象的对象标识等。页有数据页、索引页、文本/图象页等8种。盘区（区域）是连续8个页，可防止数据产生碎片，使SQLServer易于寻找。每个盘区只能包含一个数据库对象。盘区是表和索引分配空间的基本单位。盘区是表和索引分配空间的基本单位。一个数据库是由文件组成的，文件是由盘区组成的，盘区是由页面组成的。,6,2.数据库的存储组织,图5-1数据库的存储组织,7,注意：所有的数据库都有唯一的主数据文件和一个或多个事务日志文件，可能还有次数据文件；数据行存放在数据页中，但数据页只能存放除text、ntext和image类型数据外的所有数据，text、ntext和image类型数据存储在单独的页中，行不能跨页存储，页中每一行最多包含的数据量是8060B；数据存放在连续的页中；日志文件不包含页，而是包含一系列日志记录；所有新建的数据库都是model数据库的副本。,8,3.文件组文件组是文件的集合。对文件进行分组，便于进行数据的管理和分配磁盘空间。例如有三个数据文件data1.ndf,data2.ndf,data3.ndf,分别位于不同的磁盘上，将这三个文件指派到文件组filegroup1中。假设在文件组filegroup1上创建一个表，对表中数据的查询将会分散到三个磁盘上，从而提高系统的查询性能。文件和文件组的设计规则:文件和文件组不能由一个以上的数据库使用;文件只能是一个文件组的成员;事务日志文件不属于任何一个文件组。数据和事务日志不能属于同一文件或文件组。数据文件和日志文件总是分开存放的。,9,主文件组中包含了所有的系统表，当建立数据库时，主文件组包括主数据库文件和未指定组的其他文件。用户定义文件组中可以指定一个缺省文件组，那么在创建数据库对象时如果没有指定将其放在哪一个文件组中，就会将它放在缺省文件组中。如果没有指定缺省文件组，则主文件组为缺省文件组。3-2.1.2事务日志文件SQL创建数据库的时候,会同时创建事务日志文件。事务日志文件存储数据库的更新情况等事务日志信息，当数据库损坏时，管理员使用事务日志恢复数据库。每一个数据库至少必须拥有一个事务日志文件，而且允许拥有多个日志文件。事务日志文件的扩展名为ldf。,10,1.事务日志文件和数据文件必须分开存放，优点如下：事务日志文件可以单独备份；有可能从服务器失效的事件中将服务器恢复到最近的状态；事务日志不会抢占数据库的空间；可以很容易地监测到事务日志的空间；在向数据文件和事务日志文件写入数据时会产生较少的冲突，这有利于提高SQLServer的性能。2.事务日志的工作过程在SQLServer中，事务是一次完成的操作的集合。虽然一个事务中可能包含了很多SQL语句，但在处理上就象他们是同一个操作似的。为了维护数据的完整性，事务必须彻底完成或者根本不执行。如果一个事务只是部分地被执行，并作用于数据库，那么数据库将可能被损坏或数据的一致性遭到破坏。,11,SQLServer使用数据库的事务日志来防止没有完成的事务破坏数据。事务日志的工作过程：应用程序发出一个修改数据库中的对象的事务；当这个事务开始时，事务日志会记录一个事务开始标志，并将被影响的数据页从磁盘读入缓冲区；事务中每个数据更改语句都被记录在日志文件中，日志文件将记录一个提交事务的标记。每一个事务都会以这种方式记录在事务日志中并被立即写到硬盘上。在缓冲区中修改相应的数据。这些数据一直在缓冲区中，在检查点进程发生时，检查点进程把所有修改过的数据页写到数据库中，并在事务日志中写入一个检查点标志，这个标志用于在数据库恢复过程中确定事务的起点和终点。以及哪些事务已经作用于数据库了。,12,随着数据库数据的不断变化，事务日志文件不断增大。因此，必须把它们备份出来，为更多的事务提供空间。备份时，事务日志文件会被截断。事务日志文件包含有在系统故障下恢复数据库需要的所有信息。一般来说，事务日志文件的初始大小是以数据文件大小的10%到25%为起点的，根据数据增长的情况和修改的频率进行调整。关于文件名：SQLServer2005的文件拥有两个名称，即逻辑文件名和物理文件名。当使用Transact-SQL命令语句访问某一个文件时，必须使用该文件的逻辑名。物理文件名是文件实际存储在磁盘上的文件名，而且可包含完整的磁盘目录路径。,回到首页,13,3-2.2估算数据库的空间需求,作为数据库管理员，主要任务之一就是创建数据库，并且需要为每个文件指定容量。必须尽可能准确地估算数据库容量，以免浪费磁盘空间资源或者因估计不足造成数据库的空间不够。许多因素会影响数据库最终的大小，在估算数据库容量时要考虑如下因素：每行记录的大小；记录数量；表的数量；索引的数量及索引大小；数据库对象的数量和大小；事务日志的大小；数据库的计划增加量；,回到首页,14,3-2.3创建数据库,创建数据库的两种方法：1.使用SQLServerManagementStudio创建数据库2.使用Transact-SQL语言创建数据库创建数据库的过程实际上就是为数据库设计名称、设计所占用的存储空间和存放文件位置以及相关特性的过程。新建数据库的信息放在系统数据库master中，属于系统级信息。建议：创建新数据库时，数据文件逻辑名加入”_data”后缀，形如：jwgl_data;事务日志文件逻辑名加入”_log”后缀，形如：jwgl_log。,15,3-2.3.1创建数据库的注意事项:创建数据库需要一定许可，在默认情况下，只有系统管理员和被赋予权限的用户可以创建数据库。数据库被创建后，创建数据库的用户自动成为该数据库的所有者。给数据库指定的名字遵循SQLServer命名规范。所有的新数据库都是系统样本数据库model的副本。单个数据库可以存储在单个文件上，也可以跨越多个文件存储。5.数据库的大小可以被增大或者收缩。6.当新的数据库创建时，SQLServer自动更新master系统数据库中的“sysdatabases”系统表。7.一台服务器上最多可能创建32767个数据库。,16,3-2.3.2使用T-SQL语言创建数据库说明：在Transact-SQL语言的命令格式中，用括起来的内容表示是可选的；，n表示重复前面的内容；用括起来表示在实际编写语句时，用相应的内容替代；用括起来表示是必选的；类似A|B的格式，表示A和B只能选择一个，不能同时都选。1.创建数据库的SQL语句的语法格式:CREATEDATABASEdatabase_nameONPRIMARY，n，nLOGON，n,17,其中：=NAME=logical_file_name，FILENAME=os_file_name，SIZE=size，MAXSIZE=max_size|UNLIMITED，FILEGROWTH=growth_increment，n=FILEGROUPfilegroup_name，n各参数的含义（如下）：database_name：新数据库的名称。数据库名称在服务器中必须唯一，最长为128个字符，并且要符合标识符的命名规则。ON：指定存放数据库的数据文件信息。列表用于定义主文件组的数据文件，列表用于定义用户文件组及其中的文件。,18,PRIMARY：用于指定主文件组中的文件。主文件组的第一个由指定的文件是主文件。如果不指定PRIMARY关键字，则在命令中列出的第一个文件将被默认为主文件。LOGON：指明事务日志文件的明确定义。如果没有本选项，则系统会自动产生一个文件名前缀与数据库名相同，容量为所有数据库文件大小1/4的事务日志文件。NAME：指定数据库文件的逻辑名称。FILENAME：指定数据库文件的物理名称，即数据库所在文件的操作系统文件名称和路径，该操作系统文件名和NAME的逻辑名称一一对应。,19,SIZE：指定数据库的初始容量大小。如果没有指定主文件的大小，则SQLServer默认其与模板数据库中的主文件大小一致，其它数据库文件和事务日志文件则默认为1MB。指定大小的数字size可以使用KB、MB、GB和TB后缀，默认的后缀为MB。Size最小值为1MB，默认值为1MB。MAXSIZE：指定操作系统文件可以增长到的最大尺寸。如果没有指定，则文件可以不断增长直到充满磁盘。UNLIMITED：指定文件可以增长到磁盘变满为止。FILEGROWTH：指定文件每次增加容量的大小，当指定数据为0时，表示文件不增长。文件的增长有两种方式：以指定的MB数量增长和按百分比增长。默认后缀为MB。如果没有指定FILEGROWTH，则默认值为10%。,20,2.几点说明1）创建用户数据库后，要备份master数据库。2）所有数据库都至少包含一个主文件组。所有系统表都分配在主文件组中。数据库还可以包含用户定义的文件组。3）每个数据库都有一个所有者，可在数据库中执行某些特殊的活动。数据库所有者是创建数据库的用户，也可以使用sp_changedbowner更改数据库所有者。4）创建数据库的权限默认地授予sysadmin和dbcreator固定服务器角色的成员。Sysadmin和securityadmin固定服务器角色的成员可以将创建数据库的权限授予其他登录，且必须显示授予，不能通过GRANTALL语句授予。,21,例5-1：创建一个只含一个数据文件和一个事务日志文件的数据库，数据库名为JWGL1。主数据库文件逻辑名称为jwgl1_data，数据文件的操作系统名称jwgl1.mdf，数据文件大小为5MB，最大值500MB，数据文件大小以10%的增量增加。日志逻辑文件名称jwgl1_log.ldf，事务日志的操作系统名称jwgl1.ldf，日志文件初始大小为5MB，最大值100MB，日志文件以2MB增量增加。,程序清单为：CREATEDATABASEjwgl1ONPRIMARY(NAME=jwgl1_data,FILENAME=c:ProgramFilesMicrosoftSQLServerMSSQLDATAjwgl1.mdf,SIZE=5MB,MAXSIZE=500MB,FILEGROWTH=10%)LOGON(name=jwgl1_log,FILENAME=c:ProgramFilesMicrosoftSQLServerMSSQLDATAjwgl1.ldf,SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=2MB)GO,22,例5-2：创建一个指定多个数据文件和日志文件的数据库。该数据库名称为jwgl2，有2个10MB的数据文件和2个10MB的事务日志文件。主文件是列表中的第一个文件，并使用PRIMARY关键字显式指定。事务日志文件在LOGON关键字后指定。注意FILE_NAME选项中所用的文件扩展名：主数据文件使用.mdf，次数据文件使用.ndf，事务文件使用.ldf。程序清单：CREATEDATABASEjwgl2ONPRIMARY(NAME=jwgl20_data,FILENAME=c:programfilesmicrosoftsqlservermssqldatajwgl20.mdf,SIZE=10MB,MAXSIZE=200,FILEGROWTH=20),23,(NAME=jwgl21_data,FILENAME=c:programfilesmicrosoftsqlservermssqldatajwgl21.ndf,SIZE=10MB,MAXSIZE=200,FILEGROWTH=20)LOGON(NAME=jwgl20_log,FILENAME=c:programfilesmicrosoftsqlservermssqldatajwgl20.ldf,SIZE=10MB,MAXSIZE=200,FILEGROWTH=20),(NAME=jwgl21_log,FILENAME=c:programfilesmicrosoftsqlservermssqldatajwgl21.ldf,SIZE=10MB,MAXSIZE=200,FILEGROWTH=20)GO,24,例5-3创建一个包含2个文件组的数据库。该数据库名为jwgl3，主文件组包含文件jwgl30_data和jwgl31_data。文件组jwgl3_group包含文件jwgl32_data和jwgl33_data。两个文件组数据文件的FILEGROWTH增量为15%，数据文件的初始大小为10MB。事务日志文件的文件名为jwgl3_log，FILEGROWTH增量为15%，日志文件的初始大小为5MB。程序清单：CREATEDATABASEjwgl3ONPRIMARY(NAME=jwgl30_data,FILENAME=c:programfilesmicrosoftsqlservermssqldatajwgl30.mdf,SIZE=10MB,25,MAXSIZE=50MB,FILEGROWTH=15%),(NAME=jwgl31_data,FILENAME=c:programfilesmicrosoftsqlservermssqldatajwgl31.ndf,SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=15%),FILEGROUPjwgl3_Group(NAME=jwgl32_data,FILENAME=c:programfilesmicrosoftsqlservermssqldatajwgl32.ndf,SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=15%),26,(NAME=jwgl33_data,FILENAME=c:programfilesmicrosoftsqlservermssqldatajwgl33.ndf,SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=15%)LOGON(NAME=jwgl3_log,FILENAME=c:programfilesmicrosoftsqlservermssqldatajwgl3.ldf,SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=15%)GO,27,3-2.3.3使用SQLServerManagementStudio创建数据库使用SQLServerManagementStudio创建数据库比使用T-SQL语句更容易。具体步骤如下:1）进入SQLServerManagementStudio;2）右击对象资源管理器的”数据库”项，在快捷菜单中选择“新建数据库”，将打开如图5-2所示的数据库属性窗口；3）该窗口有三个选择页：常规、选项、文件组；4）分别进行相关设置；5）点击“确定”。,28,3-2.3.4由已有库生成创建数据库的脚本由已建数据库JWGL生成该库的SQL脚本的步骤:（1）选择数据库JWGL，按右键，选择“编写数据库教本为(S)”CREATE(C)；（2）选择数据库脚本文件生成的目的地：“新查询编辑窗口(N)”、“文件(F)”或“剪贴板(C)”；（3）单击“文件(F)”，便会产生一个“选择文件”的对话窗口，在其中输入文件名，单击“保存”按钮。如要验证脚本的正确性，可进入查询编辑器，调入该脚本程序执行即可。,回到首页,29,3-2.4数据库的管理,3-2.4.1数据库属性的查看1.用T-SQL语言查看数据库的属性使用系统存储过程可以查看数据库的属性。常用的存储过程有sp_helpdb，sp_spaceused，sp_filehelp，sp_helpfilegroup。查看某个数据库或所有数据库的信息：EXECsp_helpdbjwgl1查看有关数据库中所占用空间的信息：EXECsp_spaceused显示有关数据库中文件组的信息：EXECsp_helpfilegroup显示数据库中文件的信息：EXECsp_helpfilejwgl1_data2.用SQLServerManagementStudio查看数据库的属性进入SQLServerManagementStudio，右击数据库，选择“属性”选项即可查看数据库相关信息。,30,3-2.4.2修改数据库大小用T-SQL语言进行数据库大小的设定例5-4:修改数据库JWGL1日志文件的最大值，由100MB改为80MB。程序清单：USEjwgl1GOALTERDATABASEjwgl1MODIFYFILE(NAME=jwgl1_log,MAXSIZE=80MB),31,例5-5：修改jwgl1_data数据文件的初始值，由5MB改为10MB。程序清单：USEjwgl1GOALTERDATABASEjwgl1MODIFYFILE(NAME=jwgl1_data,SIZE=10MB)应该注意，对于数据库数据文件和事务日志文件初始空间大小的修改，新指定的空间大小值不能小于当前文件空间大小值。2.用SQLServerManagementStudio修改数据库属性进入SQLServerManagementStudio，展开树形结构的“数据库”节点，选中要修改的数据库JWGL1，按右键，在系统弹出快捷菜单上点击“属性”，进入JWGL1数据库的属性窗口，可以根据需要进入不同的选项卡修改数据库相应的信息。,32,3-2.4.3数据库的收缩SQL允许收缩数据库中的每个文件以删除未使用的页。数据和事务日志文件都可以收缩。数据库文件可以单独地进行手工收缩，也可以设置为按给定的时间间隔进行自动收缩。注意：不能将整个数据库收缩到比原始大小还要小。缩小数据库有两种方法：1使用SQLServerManagementStudio可以缩小数据库(教材P91）。2使用Transact-SQL语言缩小数据库。（1）使用DBCCSHRINKDATABASE命令收缩指定数据库中的数据文件。DBCCSHRINKDATABASE(database_name,target_percent,NOTRUNCATE|TRUNCATEONLY),33,database_name：要收缩的数据库名称。Target_percent：当数据库收缩后，数据库文件中剩余可用空间的百分比。NOTRUNCATE：被释放的文件空间依然保持在数据库文件中。如果未指定，将所释放的文件空间被操作系统回收。TRUNCATEONLY：将数据文件中未使用的空间释放给操作系统，并将文件收缩到上一次所分配的大小。使用TRUNCATEONLY时，将忽略target_percent的限制。将数据库设为自动收缩使用ALTERDATABASE语句可以将数据库设为自动收缩。设定数据库是否自动收缩的SQL语句的语法形式：ALTERDATABASEdatabase_nameSETAUTO_SHRINKon/offon：数据库设为自动收缩，off：数据库设为不自动收缩。,34,【例5-7】：将数据库JWGL1的收缩设为自动收缩，SQL脚本程序如下：ALTERDATABASEjwgl1SETAUTO_SHRINKon3-2.4.4数据库的更名一般情况下不要做数据库的改名。如要更名，需将数据库属性“选项”中的“限制访问”项设为“single_user”，更名后再将“限制访问”项改回来。两种给数据库更名的方法：（1）进入SQLServerManagementStudio，展开对象资源管理器，右击要更名的数据库，选择“重命名”。（2）数据库的更名的SQL语句的语法形式：sp_renamedbold_name,new_name其中：old_name:是数据库的当前名称;new_name:是数据库的新名称。【例5-8】：将数据库JWGL1更名为JWXT，SQL脚本程序如下：sp_renamedbjwgl1,jwxt,35,3-2.4.5删除数据库1利用SQLServerManagementStudio删除数据库(教材P92）2利用Drop语句删除数据库Dropdatabasedatabase_name,n说明：当数据库处于以下状态时不能被删除：数据库正在使用，数据库正在恢复，系统数据库master、tempdb、model、msdb。例5-9使用DROPDATABASE命令将数据库JWXT删除。DROPdatabasejwxt,回到首页,36,3-2.5设置数据库的选项,数据库选项就是数据库的属性。这些选项将决定数据库的不同部分如何工作，可以使用SQLSQLServerManagementStudio或者“sp_dboption”存储过程来修改数据库选项。3-2.5.1使用Transact-SQL语句设置数据库的选项使用存储过程sp_dboption可以显示或更改数据库选项。但不能对master或tempdb数据库使用sp_dboption。设置数据库的选项的SQL语句的语法形式如下：sp_dboptiondatabaseoption_namevalue其中：database：在其中设置指定选项的数据库的名称。option_name：要设置的选项的名称。该选项常用的4个参数及含义如下：autoshrink:当value为true时，数据库文件将成为自动周期性收缩的候选文件。dbouseonly:当value为true时，只有数据库所有者可以使用数据库。,37,readonly:当value为true时，用户仅能读取数据库中的数据而无法对其修改。singleuse：当value为true时，每次只能有一个用户访问数据库。value：option_name的新设置。如果省略此参数，sp_dboption将返回当前设置。value可以是true、false、或on、off。【例5-10】将数据库JWGL设置为只有数据库所有者可以使用。USEjwglEXECsp_dboptionjwgl,dbouseonly,true,38,3-2.5.2利用SQLServerManagementStudio设置数据库的选项（1）进入SQLServerManagementStudio，展开对象资源管理器，右击要查看的数据库，单击“属性”，弹出如图5-3所示的窗口。（2）单击该数据库属性窗口“选项”选项卡，出现数据库的各个选项。用户可以进行如下常用选项的设置：排序规则：通过从列表中进行选择来指定数据库的排序规则。兼容级别：制定数据库所支持的MicrosoftSQLServer的最新版本。恢复模式：制定数据库的恢复模型。数据库为只读：使这个数据库处于只读状态。如数据库设为只读状态，用户就不能修改数据库中的任何记录。限制访问：制定哪些用户可以访问该数据库，可能的值有：MULTI_USER：允许多个用户同时访问该数据库。SINGLE_USER：表示该数据库在同一时间只能供一个用户使用。RESTRICTED_USER：指定只有db_owner,dbcreator或sysadmin的成员才有权使用该数据库。自动关闭：当数据库中无用户时，自动关闭该数据库，并将所占用的资源交还给操作系统。自动收缩：指定数据库是否自动缩减。（3）单击“确定”按钮，完成选项设置。,回到首页,39,