4-数据库管理(SSMS).ppt

创建和管理数据库（SQL Server2005 SSMS）5/3/2024主要内容n1 SQL Server 数据库的一些基本术语和概念n2 创建数据库 n3 管理数据库n4 应用举例 1 SQL Server 数据库的一些基本术语和概念n1.1 SQL Server的数据库 n1.2 SQL Server的事务日志 n1.3 SQL Server 数据库文件及文件组 n1.4 SQL Server 的系统数据库 1.1 SQL Server的数据库 1.SQLServer的数据库的数据库是有组织的数据的集合，这种数据集合具是有组织的数据的集合，这种数据集合具有逻辑结构并得到数据库系统的管理和维护。有逻辑结构并得到数据库系统的管理和维护。SQLServer数据库是数据库对象的容器，它以数据库是数据库对象的容器，它以操作系统文件的形式存储在磁盘上。一般情况操作系统文件的形式存储在磁盘上。一般情况下它用一组文件映射下它用一组文件映射DB。DB中所有的数据和对中所有的数据和对象都存储在象都存储在OS文件中。文件中。数据文件日志文件学生管理数据库数据、表、存储过程、触发器、视图、规则、默认值、用户定义数据类型、索引、权限数据库用户、数据库规则等对象图图1 1 数据库、数据库对象及文件数据库、数据库对象及文件 关系图关系图1.1SQLServer的数据库的数据库1.2 SQL Server的事务日志 1.事事务务是是一一组组T-SQL语语句句的的集集合合，这这组组语语句句作作为为单单个个的的工工作作与与恢恢复复的的单单元元。事事务务作作为为一一个个整整体体来来执执行行，对对于于其其数数据据的的修修改改，要要么全都执行，要么全都不执行。么全都执行，要么全都不执行。2.事事务务日日志志是是数数据据库库中中已已发发生生的的所所有有修修改改和和执行每次修改的事务的一连串记录。执行每次修改的事务的一连串记录。3.事务日志记录更改数据的流程事务日志记录更改数据的流程。1.2 SQL Server的事务日志事务日志工作流程数据库修改操作写入日志页调入内存写入磁盘1.3 SQL Server 数据库文件及文件组 1.SQL Server的数据库文件的三种类型的数据库文件的三种类型 1）主数据文件（）主数据文件（Primaryfile）用用来来存存储储数数据据库库的的数数据据和和数数据据库库的的启启动动信信息息。每每个个数数据据库库必必须须并并且且只只有有一一个个主主数数据据文文件件，其其扩扩展展名名为为.MDF。实实际际的的文文件件都都有有两两种种名名称：操作系统文件名和逻辑文件名（称：操作系统文件名和逻辑文件名（T-SQL语句中使用）。语句中使用）。2）辅助数据文件（）辅助数据文件（Secondaryfile）用用来来存存储储数数据据库库的的数数据据，使使用用辅辅助助数数据据库库文文件件可可以以扩扩展展存存储储空空间间。如如果果数数据据库库用用一一个个主主数数据据文文件件和和多多个个辅辅助助数数据据文文件件来来存存放放数数据据，并并将将它它们们放放在在不不同同的的物物理理磁磁盘盘上上，数数据据库库的的总总容容量量就就是是这这几几个个磁磁盘盘容容量的和。辅助数据文件的扩展名为：量的和。辅助数据文件的扩展名为：.NDF3）事务日志文件（事务日志文件（Transactionlog）用来存放数据库的事务日志。凡是对数据库进行的增、删、改用来存放数据库的事务日志。凡是对数据库进行的增、删、改等操作，都会记录在事务日志文件中。当数据库被破坏时可以利用等操作，都会记录在事务日志文件中。当数据库被破坏时可以利用事务日志文件恢复数据库的数据。每个数据库至少要有一个事务日事务日志文件恢复数据库的数据。每个数据库至少要有一个事务日志文件，事务日志文件的扩展名为：志文件，事务日志文件的扩展名为：LDF1.3 SQL Server 数据库文件及文件组2SQL Server的数据库文件组的数据库文件组 文件组是文件组是SQL另一种形式的容器，文件位置可以很另一种形式的容器，文件位置可以很灵活，你可以将数据文件存储在不同的地方，然后用文件灵活，你可以将数据文件存储在不同的地方，然后用文件组把他们作为一个单元来管理。例如，你可以将主数据文组把他们作为一个单元来管理。例如，你可以将主数据文件放在一个地方然后将次要数据文件件放在一个地方然后将次要数据文件(aa1.ndf,aa2.ndf,aa3.ndf),如果需要，可将他们放在如果需要，可将他们放在三三个不同的磁盘（如个不同的磁盘（如D盘、盘、E盘、盘、F盘）上。然后创建一个文盘）上。然后创建一个文件组，将所有的文件指定到此文件组。如图件组，将所有的文件指定到此文件组。如图2所示。所示。学生数据库d:msqldatastudent_data.mdfe:msqldatastudent_data2.ndff:msqldatastudent_data3.ndfd:msqldatastudent_log.ldfe:msqldatastudent_log2.ldff:msqldatastudent_log3.ldf图图2数据库与操作系统文件之间的映射数据库与操作系统文件之间的映射 1.4 SQL Server 的系统数据库vSQL server 2005的系统构成 数据库分为两类有系统数据库和用户数据库。数据库分为两类有系统数据库和用户数据库。当安装完成后系统会自动创建当安装完成后系统会自动创建5个系统数据库个系统数据库,其中其中4个在个在SQLServerManagementStudio环境中可见环境中可见（master、model、tempb、msdb）和）和1个逻辑个逻辑上不单独存在，隐藏的系统数据库上不单独存在，隐藏的系统数据库Resource。nmaster数据库nmsdb数据库nmodel数据库nResource数据库ntempdb数据库1.4 SQL Server 的系统数据库master数据库n“数据库的数据库”，n记录SQL Server系统的所有系统级信息。nSQL Server的初始化信息。master数据库：该数据库记录数据库：该数据库记录SQL server 2005的所的所有的服务器系统信息、注册账户和密码以及所有的系统有的服务器系统信息、注册账户和密码以及所有的系统设置信息等大量对对系统至关重要的信息，是系统的关设置信息等大量对对系统至关重要的信息，是系统的关键性所在，所以他一旦受到破坏，可能会导致这个系统键性所在，所以他一旦受到破坏，可能会导致这个系统的瘫痪。的瘫痪。msdb数据库n存储计划信息n此数据库供SQL server代理程序调度报警和作业调度等活动n存储备份和恢复相关的信息，尤其是SQL Serve代理需要使用它来执行安排工作和警报，记录操作者等操作。model数据库n创建的所有数据库的模板。n当执行CREATE DATABASE语句时，将通过复制 model 数据库中的内容来创建数据库的第一部分，然后用空页填充新数据库的剩余部分。n如果修改model数据库，之后创建的所有数据库都将继承这些修改。u为用户提供了模板和原型，包含了每一用户数据库所需要的系统表。它的定制结构可以被更改，因为每当用户创建新的数据库时，都是复制model数据库的模板，所以所作的所有更改都将反映到用户数据库当中。Resource数据库nResource数据库是只读数据库，它包含了SQL Server 2005中的所有系统对象。n系统对象在物理上保存在Resource 数据库文件中，在逻辑上显示在每个数据库的sys架构中。nResource数据库是隐藏的，通常应该由MicroSoft客户服务专家来打开，用于查找问题和进行客户支持。Tempdb数据库 ntempdb 保存所有临时表和临时存储过程，并满足任何其他的临时存储要求。ntempdb数据库是全局资源，在每次启动时都重新创建，因此该数据库在系统启动时总是空白的。2 创建数据库n数据库是数据库系统最基本的对象，是存储过程、触发器、视图和规则等数据库对象的容器。因此，创建数据库是创建其它数据库对象的基础。若要创建数据库，需要确定数据库的名称、所有者、大小以及存储该数据库的文件和文件组。n在SQL Server 2005中创建数据库主要有两种方法：n使用SQL Server Management Studion使用T-SQL语言创建数据库。2.1 数据库文件每个SQL Server 2005数据库至少具有两个操作系统文件：一个数据文件和一个日志文件。n数据文件n日志文件n物理文件n文件大小n文件组数据文件n数据库文件是存放数据库数据和数据库对象的文件。一个数据库可以有一个或多个数据库文件。n一个数据库只能有一个主数据库文件，扩展名为mdf。n其它数据库文件被称为次数据库文件，扩展名为 ndf。日志文件 n保存用于恢复数据库的日志信息。n每个数据库必须至少有一个日志文件。也可以为多个n事务日志的建议文件扩展名是.ldf。物理文件物理文件n每个数据库文件有两个名称：n逻辑文件名（logical_file_name）：是在所有 Transact-SQL 语句中引用物理文件时所使用的名称。n物理文件名（os_file_name）：是包括目录路径的物理文件名。文件大小 n指定数据和日志文件的初始大小，或采用默认大小。n如果已经超过了初始数据文件的大小并且文件开始自动增长，则重新计算预期的数据库大小最大值。n例例1 在master数据库下，执行存储过程sys.sp_helpfile，则得到结果如图所示，逻辑文件名在name列，物理文件名在filename列。最大大小在Maxsize列，增长在growth列。文件组在filegroup列。2.2 使用SSMS创建数据库 nSSMS：SQL Server Management Studion在对象资源管理器中数据库节点上右击,在弹出菜单中单击“新建数据库”，则出现“新建数据库”对话框。3 管理数据库n查看数据库属性 n修改数据库 n收缩数据库 n删除数据库 1、打开数据库、打开数据库 在在SQL Server Management Studio中打开数据库的步骤是：在中打开数据库的步骤是：在“对象资源对象资源管理器管理器”窗口中，展开窗口中，展开“数据库数据库”节点，节点，单击要打开的数据库，此时右边单击要打开的数据库，此时右边“摘要摘要”窗口中列出当前打开数据库的数据库窗口中列出当前打开数据库的数据库对象。对象。3 管理数据库查看数据库属性 对象资源管理器是Management Studio的一个组（1）启动）启动SQL Server Management Studio，在在“对象资源管理器对象资源管理器”窗口中，展开窗口中，展开“数据库数据库”节节点，选择要查看信息的数据库，单击右键，在弹出点，选择要查看信息的数据库，单击右键，在弹出的快捷菜单中，单击的快捷菜单中，单击“属性属性”命令，打开命令，打开“数据库数据库属性属性”对话框。对话框。（2）在）在“数据库属性数据库属性”对话框中，包含常规、文对话框中，包含常规、文件、文件组、选项、权限、扩展属性、镜像和事务件、文件组、选项、权限、扩展属性、镜像和事务日志传送日志传送8个选择页。单击其中任意的选择页，可个选择页。单击其中任意的选择页，可以查看到与之相关的数据库信息。以查看到与之相关的数据库信息。查看数据库属性 使用SSMS查看数据库属性 使用各种视图、系统函数和系统存储过程来查看数据库属性。视图视图函数函数存储过程和其他语句存储过程和其他语句sys.databasesDATABASE_PRINCIPAL_IDsp_databasessys.database_filesDATABASEPROPERTYEXsp_helpdbsys.data_spacesDB_IDsp_helpfilesys.filegroupsDB_NAMEsp_helpfilegroupsys.allocation_unitsFILE_IDsp_spaceusedsys.master_filesFILE_IDEXDBCCSQLPERFsys.partitionsFILE_NAMEsys.partition_functionsFILEGROUP_IDsys.partition_parametersFILEGROUP_NAMEsys.partition_range_valuesFILEGROUPPROPERTYsys.partition_schemesFILEPROPERTYsys.dm_db_partition_statsfn_virtualfilestats例 显示master数据库信息。sp_helpdb master (1)在在“对象资源管理器对象资源管理器”窗口中，展开窗口中，展开“数据库数据库”节点，选择要设置选项的数据库，单击右键，在节点，选择要设置选项的数据库，单击右键，在弹出的快捷菜单中，单击弹出的快捷菜单中，单击“属性属性”命令，打开命令，打开“数数据库属性据库属性”对话框。对话框。（2）在）在“数据库属性数据库属性”对话框中，选择对话框中，选择“选项选项”页，出现数据库的各个选项，在此，可以根据管理页，出现数据库的各个选项，在此，可以根据管理需要对数据库选项进行重新设定。需要对数据库选项进行重新设定。设定修改数据库选项更改数据库名称更改数据库名称（1）启动）启动SQL Server Management Studio，在，在“对象资对象资源管理器源管理器”窗口中，展开窗口中，展开“数据库数据库”节点，选择要更名节点，选择要更名的数据库，单击右键，在弹出的快捷菜单中，单击的数据库，单击右键，在弹出的快捷菜单中，单击“属属性性”命令，打开命令，打开“数据库属性数据库属性”对话框，选择对话框，选择“选项选项”页，将数据库选项中的页，将数据库选项中的“限制访问限制访问”设为设为“Single”用用户模式，设置成功后，在对象资源管理器中该数据库名户模式，设置成功后，在对象资源管理器中该数据库名称旁边有单个用户标志。称旁边有单个用户标志。（2）选择要更名的数据库，单击右键，在弹出的快捷菜单）选择要更名的数据库，单击右键，在弹出的快捷菜单中选择中选择“重命名重命名”，数据库名称变为可编辑状态，输入，数据库名称变为可编辑状态，输入新的数据库名称即可。新的数据库名称即可。（3）更名后，将数据库选项中的）更名后，将数据库选项中的“限制访问限制访问”设为设为“Multipe”用户模式。用户模式。收缩数据库n使用一段时间后，时常会出现因数据删除而造成数据库中空闲空间太多的情况，需要使用收缩的方式来缩减数据库空间。n可在数据库属性选项中选择“Auto shrink”选项，让系统自动自动收缩数据库，也可用人工的方法来收缩。修改数据库n在SSMS利用数据库属性设置更改数据库参数。（1）增加数据库容量：在“对象资源管理器”窗口中，展开“数据库”节点，选择要增加容量数据库，单击右键，在弹出的快捷菜单中单击“属性”命令，打开“数据库属性”对话框。从“选择页”中选择“文件”页，在这里可以修改数据库文件的初始大小和增长方式，其修改方法与创建数据库时相同。（2）收缩数据库容量：在“对象资源管理器”窗口中，展开“数据库”节点，选择要收缩容量的数据库“，单击右键，在弹出的快捷菜单中执行“任务”“收缩”“数据库”命令，打开“收缩数据库”对话框。根据需要，可以选择“在释放未使用的空间前重新组织文件”复选框，如果选择该项，必须为“收缩后文件中的最大可用空间”输入值，允许输入的值介于0和99之间。设置完成后，单击“确定”命令按钮，完成数据库收缩。n在在“对象资源管理器对象资源管理器”窗口中，展开窗口中，展开“数据库数据库”节点，选择要删除的数据库，节点，选择要删除的数据库，单击右键，在弹出的快捷菜单中单击单击右键，在弹出的快捷菜单中单击“删除删除”命令，打开命令，打开“删除对象删除对象”对话框，对话框，单击单击“确定确定”命令按钮即可完成数据库命令按钮即可完成数据库删除操作。删除操作。删除数据库 分离和附加数据库分离和附加数据库（1）分离数据库）分离数据库 在在“对象资源管理器对象资源管理器”窗口中，展开窗口中，展开“数据库数据库”节点，选择要分节点，选择要分离的数据库，单击右键，在弹出的快捷菜单中执行离的数据库，单击右键，在弹出的快捷菜单中执行“任务任务”“分离分离”命令，打开命令，打开“分离数据库分离数据库”对话框。在对话框。在“分离数据库分离数据库”对对话框中右侧是话框中右侧是“要分离的数据库要分离的数据库”窗格，在窗格，在“状态状态”为就绪时，为就绪时，单击单击“确定确定”按钮，将数据库与按钮，将数据库与SQL Server服务器分离。服务器分离。（2）附加数据库）附加数据库 在在“对象资源管理器对象资源管理器”窗口中，选择窗口中，选择“数据库数据库”节点，单击右键，节点，单击右键，在弹出的快捷菜单中，单击在弹出的快捷菜单中，单击“附加附加”命令，打开命令，打开“附加数据库附加数据库”对话框。单击对话框。单击“添加添加”命令按钮，打开命令按钮，打开“定位数据库文件定位数据库文件”对话对话框，在该对话框中选择数据文件所在的路径，选择扩展名为框，在该对话框中选择数据文件所在的路径，选择扩展名为.MDF数据文件，如数据文件，如BVTC_DB_DATA.MDF，单击，单击“确定确定”命令命令按钮返回按钮返回“附加数据库附加数据库”对话框。最后，单击对话框。最后，单击“确定确定”按钮，完按钮，完成数据库附加。成数据库附加。本章小结本章介绍了系统数据库、数据库的创建与管理，强大的数据库管理功能是SQL Server的特点，掌握本章内容是对数据库管理员的基本要求。