第2章 数据库的建立及维护

第二章管理数据库主讲教师：卫琳2.1数据库组成mastermodeltempdbmsdbAdventureWorksAdventureWorksDW系统数据库用户数据库User1studentSQLServer系统数据库存储SQLSERVER的系统信息，它们是管理SQLSERVER的依据，安装SQLServer时，将创建4个系统数据库Master包含了SQLServer诸如登录账号、系统配置、数据库位置及数据库错误信息等，用于控制用户数据库和SQLServer的运行。Model为新创建的数据库提供模板。Msdb为SQLServerAgent调度信息和作业记录提供存储空间。Tempdb为临时表和临时存储过程提供存储空间，所有与系统连接的用户的临时表和临时存储过程都存储于该数据库中。2.1.1系统数据库2.1.2用户数据库

用户数据库与系统数据库在结构上是一样的。是用户根据自己的管理需求而创建的数据库，便于自己管理相应的数据。例如：图书馆可以针对图书的管理创建图书管理数据库。大型超市可以针对货物创建超市管理数据库2.1.3示例数据库

示例数据库是系统为了让用户学习和理解SQLServer而设计的。默认情况下，SQLServer2008版本跳会再安装示例数据库。但可以根据自己需要附加使用这些示例数据库。

数据库文件和事务日志文件2.1.4数据库文件及其文件组页：8KB表、索引数据数据库数据文件

.mdf或.ndf日志文件

.ldf最多=8060字节SQLserver的文件使用一组操作系统文件来存储数据库的各种逻辑成分，包括以下三类文件。（1）主数据文件。是数据库的关键文件，包含了数据库的启动信息，并且存储数据。每个数据库必需有且仅能有一个主文件，默认扩展名为.MDF（2）辅助数据文件。用于存储未包括在主文件内的其他数据。默认扩展名为.NDF。辅助文件是可选的，根据具体情况,可以创建多个辅助文件，也可以不用辅助文件。一般当数据库很大时，有可能需要创建多个辅助文件；而数据库较小时，则只要创建主文件文件不需要辅助文件。（3）日志文件。日志文件用于保存恢复数据库所需的事务日志信息。每个数据库至少有一个日志文件，也可能有多个。日志文件的扩展名为.LDF.

日志文件的存储与数据文件不同，它包含一系列记录，这些记录的存储不以页为存储单位。创建一个数据库后，该数据库中至少包含一个主数据文件和日志文件。这些文件是操作系统文件名，它们不是由用户直接使用的，则是由系统使用的，因此不同于数据库的逻辑名。4)文件组：允许将多个文件归纳为一组称文件组。Data1.mdf,data2.ndf,data3.ndf数据文件分别创建在3个物理磁盘上，组成一组。创建表时，指定一个表在文件组中。此表数据分布在3个物理磁盘上，对表查询，可并行操作，提高查询效率。说明：一个文件或一个文件组只能被一个数据库使用。一个文件只能隶属于一个文件组。数据库的数据信息和日志信息不能放在同一个文件或文件组中。日志文件不能隶属于任何一个文件组。文件组有两类：主文件组：包含主数据文件和任何没有明确指派给其他文件组的其他文件。用户定义文件组:T_SQL语句中用于创建和修改数据库的语句分别是createdatabase和alterdatabase,这两语句都可以用filegroup关键字指定文件组。用户定义文件组就是指使用这两个语句创建或修改数据库时指定的文件组。

每个数据库中都有一个文件组作为默认文件组运行。若SQLSERVER创建表或索引时没有为其指定文件组，那么将从默认文件组中进行存储页分配、查询等操作。可以指定默认文件组，如果没有指定默认文件组，则主文件组是默认文件组。2.1.5数据库对象1.表2.视图3.存储过程2.2.1使用SSMS图形界面创建数据库1.创建数据库例1：创建数据库xscj,初始大小为5MB,最大长度为50MB，数据库自动增长，增长方式是按10%比例增长；日志文件初始为2MB,最大可增长到5MB（为不限制）,按1MB增长（默认是按10%比例增长）2.2数据库的维护2.2.2使用T-SQL命令创建数据库例1：创建数据库xscj1,初始大小为5MB,最大长度为50MB，数据库自动增长，增长方式是按10%比例增长；日志文件初始为2MB,最大可增长到5MB（为不限制）,按1MB增长（默认是按10%比例增长）；所有者是Adimistrator.1.创建数据库createdatabasexscj1 on (name='xscj1_data', filename='e:\sql\xscj1_data.mdf', size=5MB, maxsize=50Mb, filegrowth=10% ) logon (name='xscj1_log', filename='e:\sql\xscj1_log.ldf', size=2mb, maxsize=5MB, filegrowth=1MB )gocreatedatabasetest1 on (name='test1', filename='e:\sql\test1.mdf' )go例2创建TEST1数据库说明：TEST1数据库只包含一个主数据文件和一个主日志文件，它们均采用系统默认文件名，其大小分别为model数据库中主数据文件和日志文件的大小。例3创建一个名为TEST2的数据库，它有2个数据文件，其中主数据文件为100MB，最大大小为200MB，按20MB增长；1个辅助数据文件为20MB，最大大小不限，按10%增长；有2个日志文件，大小均为50MB，最大大小均为100MB,按10MB增长。createdatabasetest2 on (name='test2_data1', filename='e:\sql\test2_data1.mdf', size=100mb, maxsize=200mb, filegrowth=20mb ), (name='test_data2', filename='e:\sql\test2_data2.ndf', size=20mb, maxsize=unlimited, filegrowth=10% ) logon (name='test2_log1', filename='e:\sql\test2_log1.ldf', size=50mb, maxsize=100mb, filegrowth=10mb ), (name='test2_log2', filename='e:\sql\test2_log2.ldf', size=50mb, maxsize=100mb, filegrowth=10mb )go例4：创建一个有3个文件组的数据库test3。主文件组包括文件test3_data1,test3_data2,文件初始大小均为20MB，最大为60MB，按5MB增长；第2个文件组名为testgoup1,包括文件test3_data3,test_data4,文件初始大小为10MB,最大为30MB，按10%；第3个文件组名为testgroup3，包括文件test3_data5,文件初始大小为10MB，最大为50MB，按15%增长。该数据库只有一个日志文件，初始大小为20MB，最大为50MB，按5MB增长。createdatabasetest3onprimary (name='test3_data1', filename='e:\学号姓名\test3_data1.mdf', size=20mb, maxsize=60mb, filegrowth=5mb ), (name='test3_data2', filename='e:\学号姓名\test3_data2.mdf', size=20mb, maxsize=60mb, filegrowth=5mb ),filegrouptest3group1 (name='test3_data3', filename='e:\学号姓名\test3_data3.mdf', size=10mb, maxsize=30mb, filegrowth=10% ), (name='test3_data4', filename='e:\学号姓名\test3_data4.mdf', size=10mb, maxsize=30mb, filegrowth=10% ),filegrouptest3group2 (name='test3_data5', filename='e:\学号姓名\test3_data5.mdf', size=10mb, maxsize=50mb, filegrowth=15% )logon (name='test3_log', filename='e:\学号姓名\test3_log.ldf', size=20mb, maxsize=50mb, filegrowth=5mb )2.2.3数据库的查看和修改1.使用SSMS图形界面查看和修改1).改变数据文件的大小和增长方式例5.将xscj数据库的主数据文件xscj.mdf的最大大小由50MB修改为不限制.2).增加数据文件例6在xscj数据库中增加数据文件xscjbak,其属性均取系统默认值.

当原有数据库的存储空间不够用时，除了可以采用扩大原有数据文件的存储量的方法之外，还可以增加新的数据文件；或者从系统管理的需求出发，采用多个数据文件来存储数据，以免数据文件过大，此时，也会用到向数据库中增加数据文件的操作。增加的数据文件是辅助文件。2.2.3数据库的查看和修改3).删除数据文件例7将xscj数据库中刚增加的辅助xscjbak删除。当数据库中的某些数据文件不再需要时，应及时删除，在SQL中只能删除辅助数据文件，而不能删除主数据文件。因为在主数据文件中存放着数据库的启动信息，删除后，数据库将无法启动。2.2.3数据库的查看和修改4).增加或删除文件组例8在xscj数据库中增加一个名为Fgroup的文件组.2.2.3数据库的查看和修改例9将刚才新增的Fgroup文件组删除。说明：不能删除主文件组(PRIMARY)。可以删除用户定义的文件组。例10：设已经创建了数据库xscj,它只有一个主数据文件，其逻辑文件名为xscj_data,物理文件名为e:\sql\xscj_data.mdf,大小为5MB，最大大小为50MB，按10%增长；有一个日志文件，逻辑名为xscj_log,物理名为e:\sql\xscj_log.ldf,大小为2MB,最大大小为5MB,每次增长1MB.修改数据库xscj现有数据文件的属性，将主数据文件的最大大小修改为不限制，增长方式修改为每次5MB增长。2.修改数据库(使用T_SQL命令)createdatabasexscj on (name='xscj_data', filename='e:\sql\xscj_data.mdf', size=5MB, maxsize=50MB, filegrowth=10% ) logon (name='xscj_log', filename='e:\sql\xscj_log.ldf', size=2MB, maxsize=5MB, filegrowth=1MB )go alterdatabasexscj modifyfile (name='xscj_data', maxsize=unlimited )goalterdatabasexscj modifyfile (name='xscj_data', filegrowth=5MB )go例11：修改数据库xscj现在数据文件的属性，将主数据文件的最大大小修改为不限制，增长方式修改为按每次5MB增长。说明：AlterdatabaseAlter

database语句一次只能修改数据文件的一个属性，若修改主数据文件的两个属性，需执行两次alterdatabase命令例12：先为数据库xscj增加数据文件xscjbak,初始大小为10MB，最大为50MB，增长方式为5%。然后删除xscjbak.alterdatabasexscj addfile (name='xscjbak', filename='e:\sql\xscjbak.ndf', size=10MB, maxsize=50MB, filegrowth=5% )Goalterdatabasexscj removefilexscjbakgo例13：为数据库xscj添加文件组Fgroup,并为文件组添加两个大小均为10MB,最大大小為30MB，增长方式为5MB的数据文件。alterdatabasexscj addfilegroupFgroupgoalterdatabasexscj addfile (name='xscj_data2', filename='e:\sql\xscj_data2.ndf', size=10MB, maxsize=30MB, filegrowth=5MB ), (name='xscj_data3', filename='e:\sql\xscj_data3.ndf', size=10MB, maxsize=30MB, filegrowth=5MB ) tofilegroupFgroupgoalterdatabasexscj removefilexscj_data2goalterdatabasexscj removefilexscj_data3goalterdatabasexscj removefilegroupFgroupGo说明：使用T_SQL命令删除文件组时必须为空，使用菜单可一同删除。例14：从数据库中删除文件组，将xscj数据库中的文件组Fgroup删除，同时删除其中的数据文件。例15：为数据库添加一个日志文件，初始大小5MB，最大大小为10MB，文件按1MB方式增长。alterdatabasexscj addlogfile (name=xscj_log2, filename='e:\sql\xscj_log2.ldf', size=5MB, maxsize=10MB, filegrowth=1MB )go

例16：从数据库xscj中删除一个xscj_Log2日志文件，注意不能删除主日志文件alterdatabasexscj removefilexscj_log2go

例17：将数据库xscj中更名为just_test,注意进行此操作时保证该数据库不能被其他任何用户使用。alterdatabasexscj modifyname=just_testgo

1.使用SSMS图形界面例18删除数据库xscj切记：当前数据库不能删除2.使用T-SQL语句例19Dropdatabasexscj2.2.4.删除数据库1.分离数据库可以将数据库从SQLserver实例中删除，同时确保数据库在其数据文件和事务日志文件中保持不变。除了系统数据库外，其余的数据库都可以从服务器的管理中分离出来。分离数据库不是删除数据库，只是从服务器中分离出来，保证了数据库的数据文件和日志文件完整无损。2.2.5数据库操作分离注意事项：数据库存在数据库快照时不能分离，在分离前，必须删除所有快照。数据库正在被镜像时，不能被分离分离数据库列表中各选项功能：删除链接：表示是否断开与指定数据库的连接。更新统计信息：表示在分离数据库之前是否更新过时的优化信息。保留全文目录：表示是否与数据库相关联的所有全文目录，以用于全文索引。2.附加数据库

将分离的数据库重新附加服务器中，但在附加数据库时必须指定主数据文件（MDF）的名称和物理位置。示例：usemastergosp_detach_db'abc'createdatabaseabcon (filename='C:\ProgramFiles\MicrosoftSQLServer\MSSQL10.MSSQLSERVER\MSSQL\DATA\abc.mdf')forattach说明：路径被写入一行上usemastergosp_detach_db'abc‘createdatabaseabcon (filename='C:\ProgramFiles\MicrosoftSQLServer\MSSQL10.MSSQLSERVER\MSSQL\DATA\abc.mdf')forattach2.附加数据库

将分离的数据库重新附加服务器中，但在附加数据库时必须指定主数据文件（MDF）的名称和物理位置。3.脱机与联机数据库如果需要暂时关闭某个数据库的服务，用户可以通过选择脱机方式来实现，脱机后在需要的时候可以暂时关闭数据库。1.快照复制数据库快照是一个只读、静态数据库视图，它是数据库恢复到特定时间点的有效途径。一个数据库可能有多个数据库快照。数据库快照一直保持和源数据库快照被创建的时刻一致。通过快照首先，可将数据库恢复到创建快照时刻，比其他方法速度快，占用系统资源少。其次，快照可作为用户保留一份可供读取的历史数据，如历年的客户订单信息。第三，因为是只读的，可替代源数据库实现某些只需查询而不需写入服务。2.2.6复制和移动数据库

数据库快照在创建初始是一个空的数据库壳，并未包含源数据库中的数据，但保包含了一个指向源数据库页面的指针。

如果此时，要从空的快照数据库读取数据，SQLServer会从源数据库中读取数据。只有当源数据库发生数据变化时，如需要将数据写到数据页时，SQLServer才会执行复制。如：“页面A”复制到快照数据库文件对应位置。源数据库中未发生变化的数据页面不会被复制到快照数据库文件中。这种模式在快照中称“copy_on_write”。即先将数据页面复制到快照文件中，然后再将更新数据写入以源数据库文件的数据页中。由于源数据库的更改可能只是其中的几页，所以复制到快照数据库文件的也只是对应的页，而源数据库中其他未修改的页不会被复制。因此，快照数据库数据文件比源数据库的数据文件小很多，称‘稀疏文件’。源数据库快照数据库页面页面页面A页面页面页面………页面页面页面A’页面页面页面………页面A稀疏文件数据文件

稀疏文件会因为源数据库中复制数据增加而扩大。2008版会以每次64KB（即8个页）大小为稀疏文件分配空间，这种机制可以有效地提高空间使用效益，避免相同数据存储两份。但有时会增加很快。如源数据库重建索引，因索引会改变源数据库中的数据页很多，造成很多数据页复制到快照中。快照复制注意事项：如果源数据库中有多个数据文件，需在文件列表中，列出这些文件在快照中对应的逻辑名称(与源数据文件可以不同名)，物理名称(与源数据文件可以不同名)及路径。快照只有数据文件，没有事务日志文件。原因是快照是只读的，而要记录写入操作。因些不需要事务日志文件。快照文件必须建立在NTFS盘区，否则无法创建’稀疏文件而失败。快照数据库与源数据库有关，必须位于同一服务器实例上。一个源数据库可以建立多个快照数据库，反映不同时间点状态，若源数据库不能使用，所有快照数据库将不能使用。快照不需要时，需要手工删除。不能对master,temdb,model系统数据库创建快照。createdatabasetest_data_8_10on (name=test, filename='E:\db\test_data_8_10.mdf' ) assnapshotoftestGo查看快照方法：对象资源管理器T-SQL命令SSMS不支持创建快照，只能使用T-sql命令（企业版），为快照命名时，建议名称中含有数据库名及快照创建时间。createdatabasetest_2011820on (name='test', filename='e:\test\test_2011820.mdf'), (name='sale2009', filename='e:\test\sale2009_2011820.ndf'), (name='sale2010', filename='e:\test\sale2010_2011820.ndf'), (name='sale2011', filename='e:\test\sale2011_2011820.mdf'), (name='sale2012', filename='e:\test\sale2012_2011820.ndf'), (name='sale2012_2', filename='e:\test\sale2012_2_2011820.ndf'), (name='sale2013', filename='e:\test\sale2013_2011820.ndf')assnapshotoftestrestore