数据库管理系统MicrosoftSQLServer创建和管理数据库

数据库管理系统（Microsoft-SQL-Server）

课程号：

1090129

主讲教师：王淮生

1.作业提交邮箱：bbwhs@163.com

2.资料查询请登录邮箱：acjsjxwhs@126.com

密码：665426，在邮箱的网盘中查找《SQLServer2005数据库管理与开发教程》第3章创建和管理数据库2024/7/111精品课件.王淮生主要内容本章主要介绍使用Transact-SQL语句和使用企业管理器创建数据库、修改数据库和删除数据库的过程。通过本章的学习，熟悉SQLServer2005数据库的组成元素，并能够掌握创建和管理数据库的方法，可以调用sp_helpdb存储过程或使用企业管理器查看数据库参数等。2精品课件.王淮生.王淮生3.1认识数据库3.2创建数据库3.3修改数据库3.4删除数据库3.5查看和设置数据库参数3精品课件.王淮生.王淮生3.1认识数据库3.1.1数据库基本概念数据库（DataBase）是按照数据结构来组织、存储和管理数据的仓库，是存储在一起的相关数据的集合。SQLServer的数据库是有组织的数据的集合，这种数据集合具有逻辑结构并得到数据库系统的管理和维护。SQLServer数据库是数据库对象的容器，它以操作系统文件的形式存储在磁盘上。一般情况下它用一组文件映射DB。DB中所有的数据和对象都存储在OS文件中。4精品课件.王淮生.王淮生3.1.1SQLServer的数据库数据文件日志文件数据库图3.1数据库、数据库对象及文件关系图数据、表、存储过程、触发器、视图、规则、默认值、用户定义数据类型、索引、权限、数据库用户、数据库规则等对象5精品课件.王淮生.王淮生（1）数据库系统硬件软件OSDBMSDBD人员用户、DBA、数据库设计人员、应用程序员、其它研究人员6精品课件.王淮生.王淮生（2）数据库管理系统①数据存取的物理构建②数据操纵功能③数据定义功能④数据库的运行管理⑤数据库的建立和维护功能7精品课件.王淮生.王淮生

（3）关系数据库支持关系模型的数据库组成三要素：

①关系数据结构②关系操作集合③完整性约束8精品课件.王淮生.王淮生3.1.2数据库常用对象（1）表（2）字段（3）索引（4）视图（5）存储过程…………9精品课件.王淮生.王淮生3.1.3数据库组成

1.SQLServer的数据库文件的三种类型

1）主数据文件（Primaryfile）用来存储数据库的数据和数据库的启动信息。每个数据库必须并且只有一个主数据文件，其扩展名为.MDF。实际的文件都有两种名称：操作系统文件名和逻辑文件名（T-SQL语句中使用）。2）辅助数据文件（Secondaryfile）用来存储数据库的数据，使用辅助数据库文件可以扩展存储空间。辅助数据文件的扩展名为：.NDF如果数据库用一个主数据文件和多个辅助数据文件来存放数据，并将它们放在不同的物理磁盘上，数据库的总容量就是这几个磁盘容量的和。3）事务日志文件（Transactionlog）用来存放数据库的事务日志。每个数据库至少要有一个事务日志文件，事务日志文件的扩展名为：LDF。凡是对数据库进行的增、删、改等操作，都会记录在事务日志文件中。当数据库被破坏时可以利用事务日志文件恢复数据库的数据。10精品课件.王淮生.王淮生2文件组SQLServer的数据库文件组文件组是SQL另一种形式的容器，文件位置可以很灵活，你可以将数据文件存储在不同的地方，然后用文件组把他们作为一个单元来管理。例如，你可以将主数据文件放在一个地方然后将次要数据文件(aa1.ndf,aa2.ndf,aa3.ndf),如果需要，可将他们放在三个不同的磁盘（如D盘、E盘、F盘）上。然后创建一个文件组，将所有的文件指定到此文件组。11精品课件.王淮生.王淮生学生数据库d:\msql\data\student_data.mdfe:\msql\data\student_data2.ndff:\msql\data\student_data3.ndfd:\msql\data\student_log.ldfe:\msql\data\student_log2.ldff:\msql\data\student_log3.ldf数据库与操作系统文件之间的映射

12精品课件.王淮生.王淮生SQLServer的事务日志对文件进行分组时，一定要遵循文件和文件组的设计规则。①文件只能是一个文件组的成员。②文件或文件组不能由一个以上的数据库使用。③数据和事务日志信息不能属于同一文件或文件组。④日志文件不能作为文件组的一部分。日志空间与数据空间分开管理。13精品课件.王淮生.王淮生什么是事务？事务是一组T-SQL语句的集合，这组语句作为单个的工作与恢复的单元。事务作为一个整体来执行，对于其数据的修改，要么全都执行，要么全都不执行。

什么是事务日志？事务日志是数据库中已发生的所有修改和执行每次修改的事务的一连串记录。

事务日志记录更改数据的流程。2024/7/1114精品课件.王淮生事务日志工作流程数据库修改操作写入日志页调入内存写入磁盘2024/7/1115精品课件.王淮生3.1.4系统数据库

SQLserver2005的系统构成数据库分为两类：系统数据库用户数据库。当安装完成后系统会自动创建5个系统数据库。其中4个（master、model、tempb、msdb）在SQLServerManagementStudio环境中可见1个逻辑上不单独存在，隐藏的系统数据库Resource。16精品课件.王淮生.王淮生SQLserver20005系统构成master数据库：该数据库记录SQLserver2005的所有的服务器系统信息、注册账户和密码以及所有的系统设置信息等大量对对系统至关重要的信息，是系统的关键性所在，所以他一旦受到破坏，可能会导致这个系统的瘫痪。model数据库：为用户提供了模板和原型，包含了每一用户数据库所需要的系统表。它的定制结构可以被更改，因为每当用户创建新的数据库时，都是复制model数据库的模板，所以所作的所有更改都将反映到用户数据库当中。2024/7/1117精品课件.王淮生SQLserver20005系统构成tempdb数据库：此数据库保存所有的临时性表和临时存储过程，并满足任何其他的临时存储要求。tempdb数据库是全局资源，在每次启动时都重新创建，因此该数据库在系统启动时总是空白的。msdb数据库：此数据库供SQLserver代理程序调度报警和作业调度等活动。Resource数据库：是一个只读的数据库，它包含了SQLServer2005中的所有系统对象。系统对象在物理上保存在Resource数据库文件中，在逻辑上显示在每个数据库的sys架构中。2024/7/1118精品课件.王淮生3.2创建数据库

数据库是数据库系统最基本的对象，是存储过程、触发器、视图和规则等数据库对象的容器。若要创建数据库，需要确定数据库的名称、所有者、大小以及存储该数据库的文件和文件组。在SQLServer2005中创建数据库主要有两种方法：使用SQLServerManagementStudio使用T-SQL语言创建数据库。1、使用SQLServerManagementStudio创建数据库193.2创建数据库2、使用T-SQL语言创建数据库，常用语法格式(36)：CREATEDATABASEdatabase_name[ON{[PRIMARY]（NAME=logical_file_name,FILENAME=’os_file_name’,[,SIZE=size][,MAXSIZE={max_size|UNLIMITED}][,FILEGROWTH=grow_increment]）}[,…n]LOGON{（NAME=logical_file_name,FILENAME=’os_file_name’[,SIZE=size][,MAXSIZE={max_size|UNLIMITED}][,FILEGROWTH=growth_increment]）}[,…n]][COLLATEcollation_name]

20精品课件.王淮生.王淮生3.2创建数据库3、案例创建数据库名为“BVTC_DB”的数据库，其包含一个主数据文件和一个事务日志文件。主数据文件的逻辑名为“BVTC_DB_DATA”，操作系统文件名为“BVTC_DB_DATA.MDF”，初始容量大小为5MB，最大容量为20MB，文件的增长量为20%；事务日志文件的逻辑文件名为“BVTC_DB_LOG”，操作系统文件名为“BVTC_DB_LOG.LDF”，初始容量大小为5MB，文件增长量为2MB，最大不受限制。数据文件与事务日志文件都保存在D盘根目录。

21精品课件.王淮生.王淮生3.2创建数据库CREATEDATABASEBVTC_DBONPRIMARY(NAME='BVTC_DB_DATA',FILENAME='D:\BVTC_DB_DATA.MDF',SIZE=5MB,MAXSIZE=20MB,FILEGROWTH=20%)LOGON(NAME='BVTC_DB_LOG',FILENAME='D:\BVTC_DB_LOG.LDF',SIZE=5MB,FILEGROWTH=2MB)

COLLATEChinese_PRC_CI_ASGO22精品课件.王淮生.王淮生1使用默认值创建数据库用户在使用CREATEDATABASE命令创建数据库时，除了指定数据库名称外，可以不用设定任何参数，所有参数均取默认值。所以新创建的数据库是由Model模板数据库复制过来的，其文件大小与Model中的设置完全相同。所创建的数据文件和日志均存放在SQLServer安装路径下。23精品课件.王淮生.王淮生例如，在“master”数据库中，使用CREATEDATABASE命令创建一个名称为“STU”的数据库。SQL代码如下：CREATEDATABASESTU24精品课件.王淮生.王淮生图3.1创建一个名称为“mrgwh”的数据库【例】使用CREATEDATABASE命令创建一个数据库。25精品课件.王淮生.王淮生2．自定义选项创建数据库自定义选项创建数据库是比较常用的创建数据库的一种方法。创建的数据文件（后缀名为.mdf）存储于SQLServer安装路径“D:\ProgramFiles\Sqlserver\MSSQL\Data”下，而日志则存放在相同目录下的日志（后缀名为.ldb）中。26精品课件.王淮生.王淮生【例3-2】自定义选项创建数据库。图3.2自定义选项创建数据库27精品课件.王淮生.王淮生3.2.2使用企业管理器创建数据库在SQLServer2005中，通过SQLServerManagementStudio可以创建数据库，用于存储数据及其他对象（如视图、索引、存储过程和触发器等）。28精品课件.王淮生.王淮生

图3.3选择新建数据库29精品课件.王淮生.王淮生

图3.4“常规”选项卡30精品课件.王淮生.王淮生3.3修改数据库3.3.1使用Transact-SQL修改数据库40T-SQL中修改数据库的命令为ALTERDATABASE。

语法格式如下：ALTERDATABASEDATABASE{ADDFILE<filespec>[,…n][TOFILEGROUPfilegroup_name]|ADDLOGFILE<filespec>[,…n]|REMOVEFILElogical_file_name|ADDFILEGROUPfilegroup_name|REMOVEFILEGROUPfilegroup_name|MODIFYFILE<filespec>|MODIFYNAME=new_dbname|MODIFYFILEGROUPfilegroup_name{filegroup_property|NAME=new_filegroup_name}|SET<optionspec>[,…n][WITH<termination>]|COLLATE<collation_name>}31精品课件.王淮生.王淮生1．修改数据库时修改数据库大小可以使用ALTERDATABASE命令修改数据库的大小。【例3-3】使用ALTERDATABASE…MODIFY命令删除数据库的文件。图3.5修改数据库的大小32精品课件.王淮生.王淮生2．修改数据库时缩小数据库（1）可以调用T-SQL的命令DBCC

SHRINKDATABASE缩小过于庞大的数据库，以回收没有使用的数据页。DBCCSHRINKDATABASE的语法格式如下：DBCCSHRINKDATABASE(database_name|[,target_percent][,{NOTRUNCATE|TRUNCATEONLY}])33精品课件.王淮生.王淮生【例3-4】使用DBCCSHRINKDATABASE命令缩小数据库的文件。

图3.6缩小gwh数据库的大小34精品课件.王淮生.王淮生（2）可以使用DBCCSHRINKFILE命令收缩相关数据库的指定数据文件或日志文件大小。DBCCSHRINKFILE的语法格式如下：DBCCSHRINKFILE(file_name{[,target_size]|[,EMPTYFILE|NOTRUNCATE|TRUNCATEONLY]]})35精品课件.王淮生.王淮生【例3-5】使用DBCCSHRINKFILE命令缩小joy文件。

图3.7使用DBCCSHRINKFILE命令缩小joy文件36精品课件.王淮生.王淮生3．修改数据库时将数据库更名系统存储过程sp_renamedb可以修改数据库的名字。语法格式如下：sp_renamedb[@dbname=]'old_name',[@newname=]'new_name'37精品课件.王淮生.王淮生【例3-6】将数据库名称“mr”更名为“mrsoft”。图3.8将数据库名称“mr”更名为“mrsoft”38精品课件.王淮生.王淮生3.3.2使用企业管理器修改数据库数据库创建完成以后，用户在使用过程中可以根据需要对其原始定义进行修改。修改的内容主要包括以下几项：（1）扩展数据库。（2）收缩数据库。（3）添加和删除文件（数据文件和事务日志文件）。（4）创建文件组。（5）创建默认文件组。（6）更改数据库所有者。39精品课件.王淮生.王淮生图3.9选择数据库属性40精品课件.王淮生.王淮生图3.10“文件”选项卡41精品课件.王淮生.王淮生

图3.11选择数据库所有者42精品课件.王淮生.王淮生图3.12“查找对象”对话框43精品课件.王淮生.王淮生3.4删除数据库

3.4.1使用Transact-SQL删除数据库DROPDATABASE命令可以删除一个或多个数据库。当某一个数据库被删除后，这个数据库的所有对象和数据都将被删除，所有日志文件和数据文件也都将删除，所占用的空间将会释放给操作系统。语法格式如下：DROPDATABASEdatabase_name[,...n]44精品课件.王淮生.王淮生图3.13删除正在使用的数据库，系统会报错45精品课件.王淮生.王淮生1．使用DROPDATABASE命令删除一个数据库

【例3-7】使用DROPDATABASE命令删除数据库。图3.14删除“teacher”数据库46精品课件.王淮生.王淮生2．使用DROPDATABASE命令批量删除数据库

DROPDATABASE学生管理,住房信息管理DROPDATABASEstudent,stu,goods,shopping47精品课件.王淮生.王淮生【例3-8】使用DROPDATABASE命令将“hello”、“every”和“person”这3个数据库批量删除。图3.15批量删除数据库48精品课件.王淮生.王淮生3.4.2使用企业管理器删除数据库如果用户不再需要某一数据库时，只要满足一定的条件即可将其删除，删除之后，相应的数据库文件及其数据都会被删除，并且不可恢复。49精品课件.王淮生.王淮生删除数据库时必须满足以下条件：（1）如果数据库涉及日志传送操作，在删除数据库之前必须取消日志传送操作。（2）若要删除为事务复制发布的数据库，或删除为合并复制发布或订阅的数据库，必须首先从数据库中删除复制。如果数据库已损坏，不能删除复制，可以先将数据库设置为脱机状态，然后再删除数据库。（3）如果数据库上存在数据库快照，必须首先删除数据库快照。50精品课件.王淮生.王淮生

图3.16删除数据库51精品课件.王淮生.王淮生

图3.17除去对象52精品课件.王淮生.王淮生分离数据库语法格式如下：sp_detach_db'database_name'例1：将“BVTC_DB1”数据库从SQLServer服务器中分离。代码如下：USEmasterGOsp_detach_db'BVTC_DB1'GO例2，分离名为“练习”的数据库sp_detach_db练习

2024/7/1153精品课件.王淮生附加数据库

语法格式如下：CREATEDATABASEdatabase_nameON(FILENAME