数据库的设计与管理

数据库的设计与管理第1页/共58页2.1数据库的存储结构

数据库的存储结构分为逻辑存储结构和物理存储结构两种。

数据库的逻辑存储结构指的是数据库是由哪些性质的信息所组成。实际上，SQLServer的数据库是由诸如表、视图、索引、约束、默认值、规则、触发器、用户定义数据类型、用户定义函数、存储过程等各种不同的数据库对象所组成。第1页/共57页第2页/共58页2.1数据库的存储结构数据库的物理存储结构是讨论数据库文件是如何在磁盘上存储的，数据库在磁盘上是以文件为单位存储的，由数据库文件和事务日志文件组成，一个数据库至少应该包含一个数据库文件和一个事务日志文件。第2页/共57页第3页/共58页2.1数据库的存储结构2.1.1数据库文件

1．主数据库文件（PrimaryDatabaseFile）

一个数据库可以有一个或多个数据库文件，一个数据库文件只能属于一个数据库。当有多个数据库文件时，有一个文件被定义为主数据库文件（简称为主文件），其扩展名为mdf。第3页/共57页第4页/共58页2.1数据库的存储结构主数据库文件用来存储数据库的启动信息以及部分或者全部数据，是所有数据库文件的起点，包含指向其它数据库文件的指针。一个数据库只能有一个主数据库文件。

第4页/共57页第5页/共58页2.1数据库的存储结构2．二级数据库文件（或辅助数据库文件）（SecondaryDatabaseFile）

用于存储主数据库文件中未存储的剩余数据和数据库对象，一个数据库可以没有二级数据库文件，但也可以同时拥有多个二级数据库文件。

二级数据库文件的扩展名为ndf。第5页/共57页第6页/共58页2.1数据库的存储结构3．事务日志文件

存储数据库的更新情况等事务日志信息，当数据库损坏时，管理员使用事务日志恢复数据库。每一个数据库至少必须拥有一个事务日志文件，而且允许拥有多个日志文件。事务日志文件的扩展名为ldf，日志文件的大小至少是512KB。

第6页/共57页第7页/共58页2.1数据库的存储结构创建数据库时，一个数据库至少由一个主数据文件和一个事务文件组成。默认时存放在\programfile\microsoftsqlserver\mssql\data\目录下。默认主数据文件名为“数据库名_DATA.MDF”的形式，事务日志文件名为“数据库名_LOG.LDF”的形式。第7页/共57页第8页/共58页2.1数据库的存储结构注意：SQLServer2000中的数据和事务日志文件不能存放在压缩文件系统或共享网络目录等远程的网络驱动器上。

SQLServer2000的文件拥有两个名称，即逻辑文件名和物理文件名。当使用Transact-SQL命令语句访问某一个文件时，必须使用该文件的逻辑名。物理文件名是文件实际存储在磁盘上的文件名，而且可包含完整的磁盘目录路径。

第8页/共57页第9页/共58页2.1数据库的存储结构2.1.2数据库文件组

利用文件组可以使服务器的性能得到提高。

主文件组中包含了所有的系统表，当建立数据库时，主文件组包括主数据库文件和未指定组的其他文件。用户定义文件组中可以指定一个缺省文件组，那么在创建数据库对象时如果没有指定将其放在哪一个文件组中，就会将它放在缺省文件组中。如果没有指定缺省文件组，则主文件组为缺省文件组。第9页/共57页第10页/共58页2.1数据库的存储结构

在一个数据库中可以创建多个文件组。一个数据文件只能存在于一个文件组中，一个文件组也只能被一个数据库使用；日志文件是独立的，它不能作为任何文件组的成员。

第10页/共57页第11页/共58页2.1数据库的存储结构1.2.3页面页面是数据库中使用的最小的数据单元。每个页面存储8K的信息，其中有包括96字节的头文件。数据页面、索引页面、文本/图象页面、全局分配页面、页面剩余空间页面、索引分配页面。第11页/共57页第12页/共58页2.1数据库的存储结构1.2.4盘区盘区由8个连续的页面组成的数据结构。它是SQLServer中为表和索引分配空间的最小单位。单一盘区：被单一的数据库对象对象使用。混合盘区：最多被8个对象共同使用。第12页/共57页第13页/共58页2.1数据库的存储结构2.1.3系统数据库（1）master数据库记录了SQLServer系统级的信息。系统表名称用途Sysconfigures保存服务器选项的代码，名称，当前设置。Sysdatabases保存所有数据库的代码，名称，创建日期Sysdevices保存系统数据库设备和备份的信息Sysxlogins保存系统所有登录帐号Sysservers保存服务器及安装信息第13页/共57页第14页/共58页2.1数据库的存储结构(2)tempdb数据库临时数据库tempdb用于存储用户创建的临时表，存储过程或用户声明的全局变量，以及用户通过游标筛选出来的数据，并为数据排序提供一个临时性工作空间。在启动时，数据库引擎会重置临时数据库的初始大小；在运行时，临时数据库会根据需要自动增长。在用户离开后，临时表及临时存储过程将会被删除。第14页/共57页第15页/共58页2.1数据库的存储结构（3）model数据库Model数据库是SQLServer2000中的模板数据库，其中包含的各个系统表为每个用户数据库所共享。系统表名称用途Sysobjects用于保存数据库中所有的数据库对象的信息Systypes记录系统数据类型和用户定义的数据类型Sysfiles记录数据库的数据文件和日子文件的信息Sysdevices用于保存数据库的文件组信息Sysusers记录数据库中所有的用户和角色第15页/共57页第16页/共58页2.1数据库的存储结构(4)msdb数据库SQLServer代理使用msdb数据库来安排报警、作业、并记录操作员。第16页/共57页第17页/共58页2.2创建数据库

2.2.1创建数据库

创建数据库需要一定许可，在默认情况下，只有系统管理员和数据库拥有者可以创建数据库。数据库被创建后，创建数据库的用户自动成为该数据库的所有者。

创建数据库的过程实际上就是为数据库设计名称、设计所占用的存储空间和存放文件位置的过程等。第17页/共57页第18页/共58页2.2创建数据库1．使用向导创建数据库

2．使用企业管理器创建数据库

3．使用Transact-SQL语言创建数据库第18页/共57页第19页/共58页2.2创建数据库说明：在Transact-SQL语言的命令格式中，用[]括起来的内容表示是可选的；[，…n]表示重复前面的内容；用<>括起来表示在实际编写语句时，用相应的内容替代；用{}括起来表示是必选的；类似A|B的格式，表示A和B只能选择一个，不能同时都选。

第19页/共57页第20页/共58页2.2创建数据库CREATEDATABASEdatabase_name[ON[PRIMARY][<filespec>[，…n][LOGON{<filespec>[，…n]}]第20页/共57页第21页/共58页2.2创建数据库<filespec>::=（[NAME=logical_file_name，]FILENAME=‘os_file_name’[，SIZE=size][，MAXSIZE={max_size|UNLIMITED}][，FILEGROWTH=growth_increment]）

[，…n]

第21页/共57页第22页/共58页2.2创建数据库database_name：新数据库的名称。数据库名称在服务器中必须唯一，最长为128个字符，并且要符合标识符的命名规则。每个服务器管理的数据库最多为32767个。ON：指定存放数据库的数据文件信息。<filespec>列表用于定义主文件组的数据文件，<filegroup>列表用于定义用户文件组及其中的文件。PRIMARY：用于指定主文件组中的文件。主文件组的第一个由<filespec>指定的文件是主文件。如果不指定PRIMARY关键字，则在命令中列出的第一个文件将被默认为主文件。第22页/共57页第23页/共58页2.2创建数据库LOGON：指明事务日志文件的明确定义。如果没有本选项，则系统会自动产生一个文件名前缀与数据库名相同，容量为所有数据库文件大小1/4的事务日志文件。NAME：指定数据库的逻辑名称。FILENAME：指定数据库所在文件的操作系统文件名称和路径，该操作系统文件名和NAME的逻辑名称一一对应。第23页/共57页第24页/共58页2.2创建数据库SIZE：指定数据库的初始容量大小。如果没有指定主文件的大小，则SQLServer默认其与模板数据库中的主文件大小一致，其它数据库文件和事务日志文件则默认为1MB。指定大小的数字size可以使用KB、MB、GB和TB后缀，默认的后缀为MB。Size中不能使用小数，其最小值为512KB，默认值为1MB。主文件的size不能小于模板数据库中的主文件。MAXSIZE：指定操作系统文件可以增长到的最大尺寸。如果没有指定，则文件可以不断增长直到充满磁盘。第24页/共57页第25页/共58页2.2创建数据库CREATEDATABASE数据库名[ON[PRIMARY]{（[NAME=数据文件的逻辑名称，]FILENAME=‘数据文件的物理名称’[，SIZE=数据文件的初始大小][，MAXSIZE=数据文件的最大容量][，FILEGROWTH=数据文件的增长量]）}[，…n]第25页/共57页第26页/共58页2.2创建数据库[LOGON{（[NAME=事务日志文件的逻辑名称，FILENAME=‘事务日志文件的物理名称’[，SIZE=事务日志文件的初始大小][，MAXSIZE=事务日志文件的最大值][，FILEGROWTH=事务日志文件的增长量]）}[，…n]第26页/共57页第27页/共58页2.2创建数据库例2-2-1：使用CREATEDATABASE创建一个student数据库，所有参数均取默认值。

例2-2-2：创建一个Student1数据库，该数据库的主文件逻辑名称为Student1_data，物理文件名为Student1_data.mdf，初始大小为10MB，最大尺寸为无限大，增长速度为10%；数据库的日志文件逻辑名称为Student1_log，物理文件名为Student1_log.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为1MB。

第27页/共57页第28页/共58页2.2创建数据库例2-2-3：创建一个指定多个数据文件和日志文件的数据库。该数据库名称为teacher，有1个10MB和1个20MB的数据文件和2个10MB的事务日志文件。数据文件逻辑名称为teacher1和teacher2，物理文件名为teacher1_data.mdf和teacher2_data.ndf。主文件是teacher1，由primary指定，两个数据文件的最大尺寸分别为无限大和100MB，增长速度分别为10%和1MB。事务日志文件的逻辑名为teacherlog1和teacherlog2，物理文件名为teacher_log1.ldf和teacher_log2.ldf，最大尺寸均为50MB，文件增长速度为1MB。

第28页/共57页第29页/共58页2.2创建数据库2.2.2查看数据库信息数据库信息主要包括三方面：基本信息、维护信息和空间使用情况。1、使用企业管理器查看数据库信息。2、使用sp_helpdb查看数据库信息.其语法格式为：[EXECUTE]sp_helpdb[数据库名]EXECUTE可缩写为EXEC第29页/共57页第30页/共58页2.2创建数据库2.2.3自动生成数据库SQL脚本即产生出能够创建现有数据库中各个数据库对象SQL命令语句。第30页/共57页第31页/共58页2.2创建数据库2.2.4数据库维护计划1、创建数据库维护计划2、修改数据库维护计划3、删除数据库维护计划第31页/共57页第32页/共58页2.3设置数据库选项

数据库选项是指一些在某个数据库范围内有效的参数，可以用于控制数据库的某些特性和行为。数据库的选项决定了一个数据库的特性。在一个数据库上设置的选项不会影响其他数据库。第32页/共57页第33页/共58页2.3设置数据库选项1、使用企业管理器查看/设置数据库选项。限制访问：表示仅允许特殊用户访问数据库。a:db_owner,dbcreator和sysadmin的成员访问数据库。b:单用户表示数据库同一时间仅能被一个用户所使用。即前一个用户退出后，下一个用户才能登录。只读：表示数据库中的数据只能读取。第33页/共57页第34页/共58页2.3设置数据库选项自动关闭：表示当最后一个用户退出，SQLServer将自动关闭该数据库，并释放它所占的系统资源。自动收缩：表示当数据库中的数据被大量删除，造成数据库文件中未使用空间超过文件大小的25%，SQLServer将自动缩小数据库文件的大小直到未使用的空间为25%或缩小至创建时的大小，缩小后的数据库文件的大小不能小于初始大小。第34页/共57页第35页/共58页2.3设置数据库选项ANSINULL默认设置：允许在数据库表的列中输入空（NULL）值。递归触发器：指定是否允许触发器递归调用。自动更新统计信息：允许使用SELECTINTO或BCP、WRITETEXT、UPDATETEXT命令向表中大量插入数据。在开发数据库时常常将此选项设置为真。使用被引用的标识符：标识符必须用双引号括起来，且可以不遵循Transact-SQL命名标准。第35页/共57页第36页/共58页2.3设置数据库选项残缺页检测：允许自动检测有损坏的页。页是数据库内容的基本存储单位，每个页的大小为8KB。由于SQLServer对页的读写单位是大小为512字节的扇区，当每个页的第一个扇区被成功写入后，SQLServer就认为此页已经被成功写入。因此，如果写完第一个扇区后发生突发事件，导致写入中断，就会产生有损坏的页，需要通知备份来恢复数据库。自动创建统计信息：在优化查询时，根据需要自动创建统计信息。第36页/共57页第37页/共58页2.3设置数据库选项2、使用系统存储过程sp_dboption（1）查看数据库选项在查询分析器中输入命令：sp_dboption数据库名第37页/共57页第38页/共58页2.3设置数据库选项(2)设置数据库选项sp_dboption[[@dbname=]’database’[,[@optname=]’option_name][,[@optvalue=]’value’][@dbname=]’database’用来指定想更改其配置设置的数据库名称。[@optname=]’option_name]用来指定想要更改其设置值的配置设置选项名称。[@optvalue=]’value’]用来指定配置选项的设置值。TURE/FALSE第38页/共57页第39页/共58页2.3设置数据库选项数据库选项共有5类：自动选项，游标选项，恢复选项，SQL选项和状态选项。AUTOCLOSE：该选项用来设置数据库使用完成后是否自动关闭。AUTOUPDATESTATISTICS：该参数用于设置是否自动更新现有的统计。AUTOSHRINK：该选项用于在文件中超过25%的部分包含未使用的空间时收缩文件大小第39页/共57页第40页/共58页2.3设置数据库选项DBOUSEONLY:该选项来设置使用数据库的用户的类型。QUOTEDIDENTIFIER：该选项用来设置字符串是否必须包含在单引号中。SINGLEUSER：该选项用来设置同时可以访问数据库的人数。READONLY：该选项用来设置数据库是否可读写。第40页/共57页第41页/共58页2.3设置数据库选项TRUNCATE.LOGONCHKPT：当数据库处于日志截断模式时，是否会截断日志中非活动的部分。TORNPAGEDETECTION：是否允许SQLServer检测由于电源故障或其他系统停歇导致的未完成的输入输出操作。第41页/共57页第42页/共58页2.4修改数据库2.4.1利用企业管理器修改数据库；2.4.2使用Transact-SQL语言修改数据库

只有数据库管理员或具有CREATEDATABASE权限的数据库所有者才有权执行该语句。ALTERDATABASE语句的语法格式如下：第42页/共57页第43页/共58页2.4修改数据库ALTERDATABASE数据库名{ADDFILE<文件格式>[TOFILEGROUP文件组]ADDLOGFILE<文件格式>|REMOVEFILE逻辑文件名|ADDFILEGROUP文件组名|REMOVEFILEGROUP文件组名|MODIFYFILE<文件格式>|MODIFYNAME=新的数据库名}第43页/共57页第44页/共58页2.4修改数据库其中“文件格式”的格式为：（NAME=数据文件的逻辑名，[，NEWNAME=数据文件的逻辑名称][，FILENAME=数据文件的物理路径][，SIZE=初始大小][，MAXSIZE=文件的最大容量|UNLIMITED][，FILEGROWTH=数据文件的增长量]第44页/共57页第45页/共58页2.4修改数据库2.4.3删除数据库

1．利用企业管理器删除数据库

2．利用Drop语句删除数据库

Dropdatabasedatabase_name[,…n]说明：只有处于正常状态下的数据库，才能使用DROP语句删除。当数据库处于以下状态时不能被删除：数据库正在使用；数据库正在恢复；数据库正在参与复制。

第45页/共57页第46页/共58页2.5备份和还原数据库数据备份是制作数据库结构、对象和数据的拷贝，以便在数据库遭到破坏的时候能够修复数据库。数据库还原指将数据库备份加载到服务器中去。何时需要备份：1、存储媒体损坏；2、用户操作错误；3、整个服务器崩溃；4、需要在不同服务器之间移动数据库时。第46页/共57页第47页/共58页2.5备份和还原数据库数据库备份的方法：全库备份：即制作数据库中所有内容的副本。差异备份：只备份最后一次全库备份后被修改的数据页。事务日志备份：只备份最后一次日志备份后所有的事务日志记录。文件或文件组备份：备份某个数据库文件或数据库文件组。第47页/共57页第48页/共58页2.5备份和还原数据库2.5.1创建备份设备1、使用企业管理器；2、使用T-SQL语句：

sp_addumpdevice备份设备的类型，备份设备的逻辑名称，备份设备的物理名称只有sysadmin和diskadmin固定服务器角色的成员才可以执行该过程。第48页/共57页第49页/共58页2.5备份和还原数据库2.5.2使用企业管理器进行备份和还原2.5.3使用查询分析器进行备份和还原1、备份数据库BACKUPDATABASE数据库名TO备份设备名[WITH[[，]DESCRIPTION=自由格式文本][[，]PASSWORD=备份密码][[，]INIT（覆盖）|NOINIT（添加）][[，]NAME=备份集名称][[，]DIFFERENTIAL]]第49页/共57页第50页/共58页2.5备份和还原数据库（1）、全库备份BACKUPDATABASE数据库名TO备份设备名[WITHoptions]（2）、差异备份BACKUPDATABASE数据库名TO备份设备名WITHDIFFERENTIAL[option]第50页/共57页第51页/共58页2.5备份和还原数据库（3）、日志备份BACKUP