第3章管理数据库资料

1、第3章管理数据库（4课时）主要内容：SQLServer数据库概述数据库的文件和文件组数据库的管理SQLServer数据库概述数据库类型（p6）SQLServer系统的数据库有两种，一种是系统数据库，另一种是用户数据库。系统数据库存放系统级信息，是系统的数据字典，例如存放系统配置信息、各种对象信息、数据库的属性、登录账户、数据库文件等信息。有五个系统数据库，分别是master、model、msdb、resource、tempdb。Master：用于跟踪所有的SQLServer系统级信息，也用来跟踪其他数据库信息。Master数据库使用的数据文件是Data子目录下的master.mdf,使用的事务

2、日志文件是Data子目录下的masterlog.ldf。Msdb:被SQLServer代理服务用来跟踪作业、警报和操作员信息。Msdb数据库使用的数据文件是Data子目录下的msdbdata.mdf,使用的事务日志文件是Data子目录下的msdblog.ldf。Model：为创建新的用户数据库提供模板（数据库字典表）。Model数据库使用的数据文件是Data子目录下的model.mdf,使用的事务日志文件是Data子目录下的modellog.ldf。Tempdb:用于存放SQLServer运行期间创建的所有临时表和其它临时对象。Tempdb数据库使用的数据文件是Data子目录下的tempdb.

3、mdf,使用的事务日志文件是Data子目录下的templog.ldf。Resource数据库：Resource数据库是一个很特殊的系统数据库，是一个隐藏的只读的系统数据库，存储了SQLServer系统中的所有系统对象。隐藏了，表示该数据库不在ManagementStudio工具中显示出来；只读的，表示该系统数据库不能被修改。实际上，系统对象在物理上都是存储在Resource数据库中，但在逻辑上则出现在其他数据库中。优点之一是便于系统升级处理。Resource数据库使用的数据文件是bin子目录下的mssqlsystemresourcer.mdf,使用的事务日志文件是bin子目录下的mssqlsy

4、stemresource.ldf。数据库对象（p8）SQLServer2008中，数据库对象包含数据库关系图、表、视图、同义词、存储过程、函数、触发器、程序集、类型、规则、默认值、用户、角色等。数据库关系图。数据库关系图节点中包含了数据库中的关系图对象。关系图对象用来描述数据库中表和表之间的对应关系，是数据库设计的常用方法。是可视的ER图。表。表节点中包含了数据库中的基本表。视图。视图节点中包含了数据库中的视图对象。同义词。同义词节点中包含了数据库中的同义词对象。同义词是数据库对象的别名，使用同义词对象可以大大简化对复杂数据库对象名称的引用方式，还能提供一个抽象层保护底层对象的改变。可编程性。

5、可编程性节点是一个逻辑组合，它包括存储过程、函数、数据库触发器、程序集、类型、规则、默认和计划指南等对象。存储过程。存储过程节点中包含了数据库中的存储过程对象信息。存储过程是存储在服务器数据库中的一个预编译的SQL语句集，这些语句在一个名称下存储并作为一个单元进行处理，是重复执行封装语句的一种有效方法。存储过程可以接受输入参数，通过输出参数向用户返回数据，存储过程的返回值是整数类型。在SQLServer2008中，可以使用T-SQL语言或CLR（CommenLanguageRuntime,公共语言运行时，是.NET提供的一种运行时环境，能够使跨语言交互的组件和应用程序的设计变得很容易，用不同语

6、言编写的对象可以互相通讯。）语言定义存储过程。函数。函数节点包含了数据库中的函数对象。函数可进一步分为表值函数、标量函数、聚合函数和系统函数。数据库触发器。数据库触发器节点包含了数据库中定义的触发器对象。触发器是一种特殊的存储过程，在数据库服务器中发生指定的事件后自动执行。在SQLServer中触发器分为DMlL发器和DDL触发器两大类。程序集。程序集节点包含了数据库中的程序集对象。程序集是在SQLServer系统中使用的DDL文件，用于部署CLR编写的函数、存储过程、触发器等。类型。类型节点包含系统数据类型、用户定义数据类型、用户定义表类型、用户定义类型和XML架构集合对象类型。系统数据类型

7、是系统提供了数据类型。用户定义数据类型是用户基于系统数据类型定义的数据类型。用户定义表类型是指用户所定义的表示表结构定义的类型。可以使用用户定义表类型为存储过程或函数声明表值参数，或者声明要在批处理中或在存储过程或函数的主体中使用的表变量。用户定义类型扩展了SQLServer的类型系统，是用户通过CLR定义的数据类型。XML架构集合是一个类似数据库表的元数据实体，用于存储导入的XML架构，是SQLServer系统支持XML数据的手段。ServiceBroker。在ServiceBroker节点中，包含了用来支持异步通信机制的对象。这些对象包括消息类型、约定、队列、服务、路由、远程服务绑定、Br

8、oker优先级等对象。安全性。在安全性节点中，包括与数据库安全有关的数据库对象。这些对象包括用户，角色、架构、非对称密钥、证书、对称密钥。用户。用户节点包含数据库用户对象，是数据库级主体。角色。角色节点包含了数据库级角色对象。数据库角色是一组具有相同权限用户的逻辑组合。架构。架构结点包含了架构对象。架构是形成单个命名空间的数据库实体的集合。非对称密钥。非对称密钥节点包含了非对称密钥对象。非对称密钥中的加密密钥和解密密钥是不同的。证书。证书节点包含了证书对象。证书对象是可以用于加密数据的公钥，是公钥证书的简称。证书是由CA（证书认证中心，CertificateAuthority）机构颁发和签名的

9、。【通常，数字证书采用公钥体制，即利用一对互相匹配的密钥进行加密、解密。每个用户自己设定一把特定的仅为本人所有的私有密钥（私钥），用它进行解密和签名；同时设定一把公共密钥（公钥）并由本人公开，为一组用户所共享，用于加密和验证签名。当发送一份保密文件时，发送方使用接收方的公钥对数据加密，而接收方则使用自己的私钥解密，这样信息就可以安全无误地到达目的地了。】【数字证书颁发过程一般为：用户首先产生自己的密钥对，并将公共密钥及部分个人身份信息传送给认证中心。认证中心在核实身份后，将执行一些必要的步骤，以确信请求确实由用户发送而来，然后，认证中心将发给用户一个数字证书，该证书内包含用户的个人信息和他的公

10、钥信息，同时还附有认证中心的签名信息。用户就可以使用自己的数字证书进行相关的各种活动。数字证书由独立的证书发行机构发布。数字证书各不相同，每种证书可提供不同级别的可信度。可以从证书发行机构获得您自己的数字证书。】对称密钥。对称密钥节点包含了对称密钥对象。对称密钥中的加密密钥和解密密钥是相同的。数据库的文件和文件组SQLServer中，数据库用到一系列操作系统文件，所有的数据及数据库对象都存放在这些操作系统文件中。SQLServer用文件组（逻辑上）来管理这些操作系统文件。SQLServer的一个数据库至少有一个数据文件（主文件）和一个日志文件。一个数据库最多可以拥有32767个数据文件和327

11、67个日志文件。文件种类SQLServe对应的磁盘文件包括数据文件和日志两种，数据文件又分为主文件和从属文件两种主文件：一个数据库有唯一的主文件，扩展名为MDF。从属文件：和主文件一起存储数据库中的数据及数据库对象（一般地，仅在主数据文件不够存放全部数据时，才使用从属文件，例如磁盘空间不足时）。一个数据库可以有一个或多个从属文件，也可以没有，主要决定于数据库的规模和复杂程度。扩展名为NDF。日志文件：用来存放数据库的事务日志信息。事物日志记录了在数据库中发生的所有操作，这些事务日志在恢复数据库时是十分关键的。每个数据库至少有一个日志文件，扩展名为LDF。为了方便，在SQLServer内部每一个

12、物理文件都对应一个逻辑名称，使用逻辑名是非常方便的。文件组及文件分组管理为了便于管理文件，方便数据的管理和分配，SQLServer中引入了文件组的概念。在SQLServer中，一个或多个文件的命名集合，就是文件组，它构成分配或数据库管理的单个单元。有两种类型的文件组：主文件组（Primary）:主文件组包含主文件和任何没有明确指派给其它文件组的从属文件。系统表的所有页（虚拟存储系统中固定长度的邻接虚拟地址块，在分页操作中作为一个单元从内存复制到磁盘和从磁盘复制回内存。SQLServer以页为单位分配数据库空间。在SQLServer中，一页的大小为8千字节）均分配在主文件组中。用户定义文件组：用

13、户定义文件组是在定义数据库时创建的，用户定义的文件组只能包含从属文件。文件组设计规则：文件或文件组不能由一个以上的数据库使用文件只能是一个文件组的成员事务日志文件不能属于任何文件组数据库的管理创建数据库创建数据库就是确定数据库名称、文件名称、数据文件大小、数据库的字符集、是否自动增长以及如何自动增长等信息的过程。在一个MicrosoftSQLServer实例中，最多可以创建32767个数据库。创建数据库有两种方法：SQLServerManagementStudio工具创建数据库CREATEDATABASE语句创建数据库SQLServerManagementStudio工具创建数据库（演示）CR

14、EATEDATABASE语句创建数据库确定数据库名确定主文件的逻辑名（name）、物理名（filename）、初始大小（size）、最大值（maxsize）、filegrowth）等。确定从属文件的逻辑名（name）、物理名（filename）、初始大小（size）、最大值（maxsize）、filegrowth）、文件组（filegroup）等。确定日志文件的逻辑名（name）、物理名（filename）、初始大小（size）、最大值（maxsize）、filegrowth）等。例1，创建默认属性的数据库CREATETATABASE数据库名例2，创建具有一个主文件和一个日志文件的数据库USE

15、masterGOcreatedatabasemydb1onprimary（name=mydb1_data,filename=c:datamydb1_data.mdf,size=3,maxsize=10MB,filegrowth=10%）logon（name=mydb1_log,filename=c:datamydb1_log.ldf,size=1mb,maxsize=5,filegrowth=1mb）GO例3创建具有从属文件的数据库，并且同时创建文件组USEmasterGOCREATEDATABASEmydb2ONPRIMARY（NAME=mydb2_data,FILENAME=c:Datam

16、ydb2Dat.mdf,SIZE=5,MAXSIZE=50,FILEGROWTH=10%）,FILEGROUPSG1（NAME=mydb2_dat_1,FILENAME=c:Datamydb2Dat_1.ndf,SIZE=5,MAXSIZE=50,FILEGROWTH=5）,（NAME=mydb2_dat_2,FILENAME=c:Datamydb2Dat_2.ndf,SIZE=5,MAXSIZE=50,FILEGROWTH=5),FILEGROUPSG2(NAME=mydb2_dat_3,FILENAME=c:Datamydb2Dat_3.ndf,SIZE=5,MAXSIZE=50,FILE

17、GROWTH=5)LOGON(NAME=mydb2_log1,FILENAME=c:Datamydb2log_1.ldf,SIZE=3MB,MAXSIZE=25MB,FILEGROWTH=1MB),(NAME=mydb2_log2,FILENAME=c:Datamydb2log_2.ldf,SIZE=3MB,MAXSIZE=25MB,FILEGROWTH=1MB)GO3.3.2查看数据库查看数据库有两种方法：SQLServerManagementStudio工具命令1使用SQLServerManagementStudio工具查看数据库（演示）2使用命令查看数据库在SQLServer中可以使用一

18、些系统视图、函数、存储过程来查看数据库查看数据库元数据的常用系统视图、系统函数、和系统存储过程视图函数存储过程sys.databasesdatabase_principal_idsp_databasesys.database_filesdatabasepropertyexsp_helpdbsys.data_spacesdb_idsp_helpfilesys.filegroupsdb_namesp_helpfilegroupsysallocation_unitsfile_idsp_spaceusedsys.master_filesfile_idexsys.partitionsfile_names

19、ys.partition_functionsfilegroup_idsys.partition_parametersfilegroup_namesys.partition_range_valuesfilegrouppropertysys.partition_schemesfilepropertysysdm_db_partition_stats3.3.3修改数据库数据库创建之后，可以根据需要使用ALTERDATABASE语句对数据库进行修改。修改操作还包括更改数据库名称、扩大数据库、收缩数据库、修改数据库文件、管理数据库文件组以及修改字符排列规则等。1更改数据库名称有两种方法可以更改数据库名称:

20、SQLServerManagementStudio工具命令（1）通过SQLServerManagementStudio工具更改数据库名称（演示）（2）通过命令更改数据库名称ALTERDATABASEdatabase_nameMODIFYNAME=new_database_name或sp_renamedb存储过程2扩大数据库在SQLServer系统中，如果数据库的数据量不断膨胀，可以根据需要扩大数据库的大小。有三种方式：设置数据库为自动增长方式直接修改数据库的数据文件或日志文件的大小在数据库中增加新的从属数据文件或日志文件有两种方法可扩大数据库：SQLServerManagementStudio

21、工具命令（1）通过SQLServerManagementStudio工具扩大数据库（演示）（2）通过命令扩大改数据库例1，直接修改数据库的数据文件大小USEmasterGOALTERDATABASEmydb2MODIFYFILE（NAME=mydb2_DATA,SIZE=8）GO例2，增加日志文件扩大数据库USEmasterGOALTERDATABASEmydb2ADDlogFILE（NAME=mydb2_log3,FILENAME=c:Datamydb2log_3.ldf,SIZE=1,MAXSIZE=10,FILEGROWTH=10%）GO例3，增加从属文件扩大数据库USEmasterGO

22、ALTERDATABASEmydb2ADDFILE（NAME=mydb2_dat_4,FILENAME=c:Datamydb2dat4.ndf,SIZE=3,MAXSIZE=10,FILEGROWTH=10%)tofilegroupsg2GO收缩数据库如果数据库的设计尺寸过大了，或者删除了数据库中的大量数据，可以收缩数据库的大小。不能在ALTERDATABASE语句中直接修改文件大小来收缩数据库，在SQLServerManagementStudio工具中可以直接修改文件大小。有四种方式：设置数据库为自动收缩，可以通过设置AUTO_SHRINK数据库选项实现收缩整个数据库的大小，可以通过使用DB

23、CCSHRINKDATABASE命令完成收缩指定的数据文件，可以使用DBCCSHRIKNFILE命令实现在数据库中删除从属数据文件或日志文件有两种方法可收缩数据库：SQLServerManagementStudio工具命令(1)通过SQLServerManagementStudio工具收缩数据库(演示)(2)通过命令收缩改数据库例1，设置数据库为自动收缩ALTERDATABASEdatabase_nameSETAUTO_SHRINKON例2，通过DBCCSHRINKDATABASE命令收缩数据库DBCCSHRINKDATABASE(database_name，target_percent)us

24、emastergoDBCCSHRINKDATABASE(northwind,10)go例3，通过DBCCSHRIKNFILE命令收缩数据库文件DBCCSHRINKFILE(file_name，target_size)usemydb2goDBCCSHRINKFILE(mydb2_dat_4,2)gousemydb2goDBCCSHRINKFILE(mydb2_dat_4,EMPTYFILE)go将指定文件中的所有数据迁移到同一文件组中的其他文件中。该文件就可以通过alterdatabase删除了。例4，删除数据库的从属文件或日志文件usemastergoalterdatabasemydb2rem

25、ovefilemydb2_log3go更改数据库的所有者有时要更改数据库的所有者，可以使用ALTERAUTHORIZATION命令。例：将数据库adventureworkslt2008r2的所有者更改为sa。ALTERAUTHORIZATIONONdatabase:adventureworkslt2008r2tosa.3.4管理数据库快照(express版本不支持)相片是被照对象在照像时刻的静态图像表示，数据库快照与此类似。数据库快照提供了源数据库在创建快照时刻的只读、静态视图，可以有效地支持报表数据汇总和数据分析等只读操作。如果源数据库中包含了未提交事务，那么这些事务不包含在数据库快照中。需要说明的是，数据库快照必须与源数据库在同一个服务器实例上。数据库快照是在数据页级上进行的。与源数据库相比，快照存在一些限制(参见教材p95)。创建数据库快照的基本语法：CREATEDATABASEdatabase_shapshot_nameON(NAME=source_database_logical_file_name,FILENAME=os_file_name)ASSNAPSHOTOFsource_datab