第3章讲义sqlserver

3.6SQLServer2005数据库架构一、数据库是SQLServer用以存放数据和数据库对象的容器。数据库包括表、索引、存储过程、视图、触发器、用户、角色、类型、函数等对象。二、数据库的组成及存储结构数据库的存储结构分为逻辑存储结构和物理存储结构两种。数据库的逻辑存储结构指的是数据库是由哪些性质的信息所组成，实际上，SQLServer的数据库是由诸如表、视图、索引等各种不同的数据库对象所组成。数据库的物理存储结构是讨论数据文件是如何在磁盘上存储的，数据库在磁盘上是以文件为单位存储的，由数据文件和事务日志两类文件组成，存储数据的文件叫数据文件,存储日志的文件叫做日志文件。数据库对象关系图表视图存储过程用户角色规则默认值用户定义的数据类型用户定义的函数全文目录组成逻辑结构逻辑数据库逻辑数据库描述数据结构，定义数据之间的联系。例如，“学生选课”数据库的数据结构定义如下。学生基本信息表S（学号，姓名，性别，出生日期，系，电话）课程数据表C(课程号，课程名，学分，预选课程号，教师)学生选课数据表SC(学号,课程号,成绩)物理数据库根据这些文件的作用不同，可以分为三类，具体如下。（1）主数据文件：每个数据库有且仅有一个主数据文件，它包含数据及数据库的启动信息，是数据库和其他数据文件的起点。主数据文件的扩展名为.mdf。（2）次数据文件：这些数据文件用于存储不能存在主数据文件中的数据和数据库对象，默认扩展名为.ndf。一个数据库中可以有多个次数据文件，如果主文件可以包含数据库中的所有数据，那么就可以没有次数据文件。如果数据库很大，可以设置多个次数据文件，次数据文件可以位于不同磁盘驱动器上。（3)日志文件：用于存储所有事务对数据库执行修改的记录，利用事务日志备份可以恢复数据库，一个数据库可以有一个或多个日志文件，扩展名为.ldf。日志文件最小为512KB。数据和事务日志信息不能存储在同一文件中数据库文件主数据文件.MDF•唯一的•包含数据库的启动信息、数据信息事务日志文件.LDF•至少一个•包含恢复数据库的所有日志信息次要数据文件.NDF•零个、一个或多个•主数据文件中容纳不下的数据•使用次要数据文件可以将数据存储到不同的磁盘上,提高数据处理的效率.一个数据库至少应该包含一个主数据文件和一个事务日志文件2．文件组每个数据库都有一个PRIMARY文件组。可以为数据库创建多个数据文件，并将这些数据文件组织成文件组。文件组是文件的集合。对文件进行分组，便于进行数据的管理和分配磁盘空间。例如有三个数据文件data1.ndf,data2.ndf,data3.ndf,分别位于不同的磁盘上，将这三个文件指派到文件组filegroup1中。假设在文件组filegroup1上创建一个表，对表中数据的查询将会分散到三个磁盘上，从而提高系统的查询性能。数据库文件组主文件组、用户定义文件组可以提高数据库的查询性能可通过管理器--数据库属性查看文件和文件组的设计规则:文件和文件组不能由一个以上的数据库使用;文件只能是一个文件组的成员;事务日志文件不属于任何一个文件组。数据库的存储结构1.盘区和页面在创建数据库对象时,SQLServer会使用盘区和页面这种特殊的数据结构给数据库对象分配空间。•在SQL中，数据存储的基本单位是页。页的大小是8KB，这样数据库上每一兆字节是128页。每页的开始部分是96B的页首，用于存放系统信息，如页的类型、页的可用空间、占据该页的数据库对象的对象标识等。页有数据页、索引页、文本/图象页等8种（P80）。•盘区（区域）是连续8个页，可防止数据产生碎片，使SQL易于寻找。SQL有两种盘区组织页面：单一区域和混合区域。单一区域用于一个对象，混合区域用于多个小对象。•事务日志不被分成页和区域，而是包含已修改数据的清单，按先来先服务的原则组织。2.数据库的存储组织方式数据行存放在数据页中，但数据页只能存放除text、ntext和image数据外的所有数据，text、ntext和image数据存储在单独的页中，行不能垮页存储，页中的每一行最多包含的数据量是8060B。数据存放在连续的页中。日志文件不包含页，而是包含一系列日志记录。所有新建的数据库都是model数据库的副本3.事务日志文件SQL创建数据库的时候,会同时创建事务日志文件。事务日志文件存储数据库的更新情况等事务日志信息，当数据库损坏时，管理员使用事务日志恢复数据库。每一个数据库至少必须拥有一个事务日志文件，而且允许拥有多个日志文件。事务日志文件的扩展名为ldf，日志文件的大小至少是512KB。事务日志文件和数据文件必须分开存放，优点如下：事务日志文件可以单独备份；有可能从服务器失效的事件中将服务器恢复到最近的状态；事务日志不会抢占数据库的空间；可以很容易地监测到事务日志的空间；在向数据文件和事务日志文件写入数据时会产生较少的冲突，这有利于提高SQL的性能。在SQL中，事务是一次完成的操作的集合。虽然一个事务中可能包含了很多SQL语句，但在处理上就象他们是同一个操作似的。为了维护数据的完整性，事务必须彻底完成或者根本不执行。如果一个事务只是部分地被执行，并作用于数据库，那么数据库将可能被损坏或数据的一致性遭到破坏。SQLServer使用数据库的事务日志来防止没有完成的事务破坏数据随着数据库数据的不断变化，事务日志文件不断增大。因此，必须把它们备份出来，为更多的事务提供空间。备份时，事务日志文件会被截断。2.事务日志的工作过程事务日志的工作过程：应用程序发出一个修改数据库中的对象的事务；当这个事务开始时，事务日志会记录一个事务开始标志，并将被影响的数据页从磁盘读入缓冲区；事务中每个数据更改语句都被记录在日志文件中，日志文件将记录一个提交事务的标记。每一个事务都会以这种方式记录在事务日志中并被立即写到硬盘上。在缓冲区中修改相应的数据。这些数据一直在缓冲区中，在检查点进程发生时，检查点进程把所有修改过的数据页写到数据库中，并在事务日志中写入一个检查点标志，这个标志用于在数据库恢复过程中确定事务的起点和终点。以及哪些事务已经作用于数据库了事务日志文件包含有在系统故障下恢复数据库需要的所有信息。一般来说，事务日志文件的初始大小是以数据文件大小的10%到25%为起点的，根据数据增长的情况和修改的频率进行调整。注意：SQLServer2005中的数据和事务日志文件不能存放在压缩文件系统或象共享网络目录等远程的网络驱动器上。SQLServer2005的文件拥有两个名称，即逻辑文件名和物理文件名。当使用Transact-SQL命令语句访问某一个文件时，必须使用该文件的逻辑名。物理文件名是文件实际存储在磁盘上的文件名，而且可包含完整的磁盘目录路径。三、系统数据库和示例数据库master数据库记录系统的所有系统级的信息:登录帐户、系统配置信息、服务器配置信息、SQLSERVER的初始和化信息、其他系统数据库及用户数据库的相关信息master数据库不可用，则SQLSERVER数据库无法启动。model数据库为所有用户提供数据库模板，当用户创建数据库时，系统自动把该模版数据库的所有信息复制到新建的数据库中，然后用空页填充新数据库的剩余部分。是必备数据库，系统必须保留，不能删除，新数据库至少与model数据库一样大。如果修改了数据库，之后创建的所有数据库都将继承这些修改。msdb数据库是代理服务数据库，是系统数据库记录有关作业、报警、操作员和调度等信息，是SQLServerAgent处理存贮系统任务的地方。tempdb数据库临时数据库，用于保存中间数据，tempdb数据库在SQLServer每次启动时都重新创建，因此该数据库在系统启动时总是空的。tempdb数据库的大小可以影响系统性能.如果tempdb数据库空间太小,每次启动时,系统处理可能忙于数据库的自动增长,而不能支持工作负荷要求。Resource数据库是只读数据库，对用户是透明的。该数据库包含了SQLServer中的所有系统对象。SQLServer的系统对象在物理上持续存在于Resource数据库中,在逻辑上,它们出现在每个数据库的SYS架构中。不包含用户数据和用户元数据。数据库文件物理文件名master主数据Master.mdfmaster日志Mastlog.ldftempdb主数据Tempdb.mdftempdb日志Templog.ldfmodel主数据Model.mdfmodel日志Modellog.ldfmsdb主数据Msdbdata.mdfmsdb日志Msdblog.ldf2.示例数据库Northwind数据库pubs数据库AdventureWorksAdventureWorksDW3.7数据库的创建与管理3.7.2数据库的创建1．准备创建数据库(P58)1）确定数据库的名称、所有者（创建数据库的用户）数据库名称：mydb2）确定存储该数据库的数据文件的大小及文件空间增长方式，确定关系、索引，及系统存储参数的配置，确定数据库的存取方法.主数据文件：逻辑名称mydb；物理文件名：C:\Data\mydb.mdf，初始大小：3MB，最大空间：UNLIMITED，空间增加量：1MB，属于文件组primary。次数据文件：逻辑名称secondmydb；物理文件名：C:\Date\secondmydb.ndf，初始大小：3MB，最大空间：50MB，空间增加量：1MB，属于文件组group1。日志文件：逻辑名称mydb_log；物理文件名：C:\Log\mydb_log.ldf，始初大小：1MB，最大空间：20MB，空间增加量：10%索引：每一数据表关于主关键字建立索引文件。2．使用SQLServerManagementStudio创建数据库3．使用Transact-SQL语言创建数据库1）命令格式CREATEDATABASE数据库名[ON[PRIMARY][Filespec[，…N]][，Filegroupspec[，…N]]][LOGON{Filespec[，…N]}]其中，[Filespec[，…N]]表示设置文件属性，格式如下。([NAME=逻辑文件名，]FILENAME=‘物理文件名称’[，SIZE=数据库文件的初始容量值][，MAXSIZE={物理文件的最大容量值|UNLIMITED}][，FILEGROWTH=增加容量值])[，…N]其中[Filegroupspec[，…N]]表示设置文件组属性，格式如下。FILEGROUP文件组名称[DEFAULT]Filespec[，…N]2）参数说明（1）放在“[]”中的“”表示整个“[]”括起来的选项都可省略，如果不省，则“”括起的选项不能省。使用“｜”分隔的多个选项，表示只能选择其中一个。（2）数据库的名称必须符合标识符规则，最长为128个字符。数据库名称在SQLServer的实例中必须唯一。数据库的逻辑文件名是数据库在SQLServer中的标识符。FILENAME指定数据库物理文件名称和路径，它和数据库逻辑名称一一对应。文件组的逻辑名称必须在数据库中唯一，不能是系统提供的名称PRIMARY和PRIMARY_LOG。（3）“ON”定义数据文件；“PRIMARY”定义主文件组中的文件；“LOGON”定义日志文件。一个数据库只能有一个主文件，如果没有定义主文件，列在数据文件项的第一个文件就是主文件。（6）数据库文件容量单位可以是KB，MB，GB，TB，缺省值为MB，长度必须为整数，主文件的，最小容量是Model数据库的主文件长度；对于其他类型文件，最小长度为512KB。（7）MAXSIZE：指定物理文件的最大容量。如果不设置文件的最大尺寸，那么文件的增长最大值将是磁盘的所有空间。UNLIMITED选项允许文件增长到磁盘已满。（8）FILEGROWTH：指定文件每次增加容量的大小或百分比，当FILEGROWTH=0时，表示文件不增长。（9）DEFAULT：指定命名文件组为数据库中的默认文件组。