[所有分类]第3章 创建和管理数据库.ppt

SQL Server 2005 数据库管理与开发教程,第3章 创建和管理数据库,本章主要介绍使用Transact-SQL语句和使用管理控制器创建数据库、修改数据库和删除数据库的过程。 通过本章的学习，可以熟悉SQL Server 2005数据库的组成元素，并能够掌握创建和管理数据库的方法，可以调用sp_helpdb存储过程或使用管理控制器查看数据库参数等。,3.1 认识数据库,3.1.1 数据库基本概念 数据库（Data Base）是按照数据结构来组织、存储和管理数据的仓库，是存储在一起的相关数据的集合。,其优点主要体现在以下几方面： （1）减少数据的冗余度，节省数据的存储空间。 （2）具有较高的数据独立性和易扩充性。 （3）实现数据资源的充分共享。,下面介绍一下与数据库相关的几个概念。 （1）数据库系统 （2）数据库管理系统, 数据存取的物理构建 数据操纵功能 数据定义功能 数据库的运行管理 数据库的建立和维护功能,（3）关系数据库, 关系数据结构 关系操作集合 完整性约束,3.1.2 数据库常用对象,（1）表 （2）字段 （3）索引：一个单独、物理的数据库结构。 （4）视图：一个或多个表中导出的表（虚表），是用户查看数据表中数据的一种方式。 （5）存储过程：一组为了完成特定功能的SQL语句集合，经编译后以名称的形式存储在SQL Server服务器端的数据库中，由用户通过指定存储过程的方式来执行。,3.1.3 数据库组成,（1）文件 主要数据文件 ：存放数据和数据库的初始化信息。每个数据库有且只有一个主数据文件，默认扩展名为.mdf。 次要数据文件：存放除主要数据文件之外的所有数据文件，可以有0至多个，默认扩展名为.ndf。 事务日志文件 ：存放用于恢复数据库的所有日志信息。一个或多个，默认扩展名为.ldf,（2）文件组, 主文件组 ：包含主要数据文件和任何没有明确指派给其他文件组的文件。系统表的所有页都分配在主文件组中。 用户定义文件组 ：主要是在CREATE DATABASE或ALTER DATABASE语句中，使用FILEGROUP关键字指定的文件组。 每个数据库都有一个文件组作为默认文件组运行。没有指定情况下，主文件组就为默认文件组。 优点： 系统管理员进行备份时，可以备份或恢复个别的文件或文件组，不用涉及整个数据库。,对文件进行分组时，一定要遵循文件和文件组的设计规则。 文件只能是一个文件组的成员。 文件或文件组不能由一个以上的数据库使用。 数据和事务日志信息不能属于同一文件或文件组。 日志文件不能作为文件组的一部分。 日志空间与数据空间分开管理。,3.1.4 系统数据库,（1）Master数据库：master 数据库记录SQL Server 2005实例的所有系统级信息。 master数据库还记录所有其他数据库是否存在以及这些数据库文件的位置。另外，master还记录了SQL Server的初始化信息。 （2）Tempdb数据库：tempdb数据库是连接到SQL Server 2005实例的所有用户都可用的全局资源，它保存所有临时表和临时存储过程。另外，它还用来满足所有其他临时存储要求。,（3）Model数据库：model数据库用作SQL Server 2005实例上创建的所有数据库的模板。 如果修改model数据库，之后创建的所有数据库都将继承这些修改。 （4）Msdb数据库：用于代理计划警报和作业。 （5）Mssqlsystemresource（资源）系统数据库，只读数据库，包含SQL Server中的所有系统对象，不包含用户数据或元数据。物理文件名为Mssqlsystemresource.mdf。 不能移动或重命名该数据库的数据文件，否则SQL Server无法启动。,3.2 创建数据库,T-SQL用于创建数据库的语句是CREATE DATABASE。 语法格式如下：,CREATE DATABASE DATABASE_NAME ON PRIMARY ,n ,n LOG ON,n COLLATE collation_name FOR LOAD（RESTORE)|FOR ATTACH :=,(NAME=logical_file_name, FILENAME=os_file_name , SIZE=size KB|MB|GB|TB , MAXSIZE=max_size KB|MB|GB|TB | UNLIMITED , FILEGROWTH =growth_increment KB|MB|GB|TB|% ),n := FILEGROUP filegroup_name,n,其中各参数的含义如下： database_name: 新数据库的名称。数据库名称在SQL Server 2005的实例中必须唯一，并且必须符合标识符规则。要求不能超过128个字符，实际能指定的字符为123. ON:指明数据库文件和文件组的明确含义 PRIMARY： 指定主数据文件或主文件组。主文件组中的第一个文件被认为是主文件。没有该项，则命令中列出的第一个文件是主文件。 Filespec:文件说明，用来说明文件的用途,LOG ON：指定日志文件的文件属性，没有该选项，可自动产生与数据库名相同的容量为数据库文件1/4大小的事务日志文件 COLLATE：校验方式。可以是Windows也可以是SQL方式，缺省此项，则使用当前的SQL Server校验方式 FOR LOAD(RESTORE)：为了和SQL Server 7.0以前版本兼容而设定的。 FOR ATTACH：用于附加已存在的数据库文件到新的数据库中，而不用重新创建数据库文件。使用该选项必须指定主文件。 NAME=logical_file_name： 指定文件的逻辑名称。当使用FOR ATTACH，就不用使用该选项。 FILENAME= os_file_name ： 指定操作系统（物理）文件名称。,SIZE： 指定文件的初始大小。默认为MB.SIZE中不能使用小数，最小值512KB，默认1MB. MAXSIZE：指定文件可增大到的最大大小。 FILEGROWTH： 指定文件的自动增量。默认为MB，没有此选项，默认值为10%,每次扩大的容量为64KB. ： 控制文件组属性。,1使用默认值创建数据库,用户在使用CREATE DATABASE命令创建数据库时，除了指定数据库名称外，可以不用设定任何参数，所有参数均取默认值。 所以新创建的数据库是由Model模板数据库复制过来的，其文件大小与Model中的设置完全相同。 所创建的数据文件和日志均存放在SQL Server安装路径下。,例如，在“master”数据库中，使用CREATE DATABASE命令创建一个名称为“STU”的数据库。 SQL代码如下： CREATE DATABASE STU,【例3-1】 使用CREATE DATABASE命令创建一个数据库。,图3.1 创建一个名称为“mrgwh”的数据库,2自定义选项创建数据库,自定义选项创建数据库是比较常用的创建数据库的一种方法。 创建的数据文件（后缀名为.mdf）存储于SQL Server安装路径“D:Program FilesSql serverMSSQLData”下，而日志则存放在相同目录下的日志（后缀名为.ldb）中。,【例3-2】 自定义选项创建数据库。,图3.2 自定义选项创建数据库,3.2.2 使用管理控制器创建数据库,在SQL Server 2005中，通过SQL Server Management Studio可以创建数据库，用于存储数据及其他对象（如视图、索引、存储过程和触发器等）。,图3.3 选择新建数据库,图3.4 “常规”选项卡,3.3 修改数据库,3.3.1 使用Transact-SQL修改数据库 T-SQL中修改数据库的命令为ALTER DATABASE。,语法格式如下： ALTER DATABASE DATABASE ADD FILE,nTO FILEGROUP filegroup_name |ADD LOG FILE,n |REMOVE FILE logical_file_name |ADD FILEGROUP filegroup_name |REMOVE FILEGROUP filegroup_name,|MODIFY FILE |MODIFY NAME=new_dbname |MODIFY FILEGROUP filegroup_namefilegroup_property|NAME=new_filegroup_name |COLLATE ,参数说明： filegroup_property：文件组属性，分为只读READONLY，可读写READWRITE和默认DEFAULT。其中主文件组不能为只读。默认的功能是把指定文件组设置为默认文件组，一个数据库只能有一个默认文件组。,例：将两个数据文件和一个事务日志文件添加到test数据库中。 ALTER DATABASE Test ADD FILE (NAME = Test1, FILENAME=c:Program FilesMicrosoft SQL ServerMSSQLDatatest1.ndf, SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB), (NAME = Test2, FILENAME=c:Program FilesMicrosoft SQL ServerMSSQLDatatest2.ndf, SIZE = 3MB, MAXSIZE = 10MB, FILEGROWTH = 1MB),GO ALTER DATABASE Test ADD LOG FILE ( NAME = testlog1, FILENAME=c:Program FilesMicrosoft SQL ServerMSSQLDatatestlog1.ldf, SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) GO,1修改数据库时修改数据库大小,可以使用ALTER DATABASE命令修改数据库的大小。 【例3-3】 使用ALTER DATABASEMODIFY命令修改数据库的大小。 注意：只能增大，不能减少,图3.5 修改数据库的大小,2修改数据库时缩小数据库,（1）可以调用T-SQL的命令DBCC SHRINKDATABASE缩小过于庞大的数据库，以回收没有使用的数据页。该操作与备份与恢复不能同时进行。,DBCC SHRINKDATABASE的语法格式如下： DBCC SHRINKDATABASE ( database_name|,target_percent ,NOTRUNCATE|TRUNCATEONLY ),参数说明： target_percen：缩减后剩下的自由空间。 NOTRUNCATE|TRUNCATEONLY：NOTRUNCATE表示被释放的空间保留在数据库文件的范围中。 TRUNCATEONLY表示被释放的空间被OS回收利用。使用该选项，忽略target_percen的限制。,【例3-4】 使用DBCC SHRINKDATABASE命令缩小数据库的文件。,图3.6 缩小gwh数据库的大小,（2）可以使用DBCC SHRINKFILE命令收缩相关数据库的指定数据文件或日志文件大小。,DBCC SHRINKFILE的语法格式如下： DBCC SHRINKFILE (file_name , target_size | ,EMPTYFILE | NOTRUNCATE | TRUNCATEONLY ),参数说明： target_size：缩减到指定的长度，以MB为单位。如果没该选项，则尽可能 缩小。 EMPTYFILE：将指定文件上数据全部转移到文件组内其他文件上，以后的操作将不再向该文件增加数据。,【例3-5】 使用DBCC SHRINKFILE命令缩小joy文件。,图3.7 使用DBCC SHRINKFILE命令缩小joy文件,3修改数据库时将数据库更名,系统存储过程sp_renamedb可以修改数据库的名字。 语法格式如下： sp_renamedb dbname = old_name , newname = new_name,【例3-6】 将数据库名称“mr”更名为“mrsoft”。,图3.8 将数据库名称“mr”更名为“mrsoft”,3.3.2 使用管理控制器修改数据库,数据库创建完成以后，用户在使用过程中可以根据需要对其原始定义进行修改。,修改的内容主要包括以下几项： （1）扩展数据库。 （2）收缩数据库。 （3）添加和删除文件（数据文件和事务日志文件）。 （4）创建文件组。 （5）创建默认文件组。 （6）更改数据库所有者。,图3.9 选择数据库属性,图3.10 “文件”选项卡,图3.11 选择数据库所有者,图3.12 “查找对象”对话框,3.4 删除数据库,3.4.1 使用Transact-SQL删除数据库 DROP DATABASE命令可以删除一个或多个数据库。 当某一个数据库被删除后，这个数据库的所有对象和数据都将被删除，所有日志文件和数据文件也都将删除，所占用的空间将会释放给操作系统。,语法格式如下： DROP DATABASE database_name ,.n ,图3.13 删除正在使用的 数据库，系统会报错,1使用DROP DATABASE命令删除一个数 据库,【例3-7】 使用DROP DATABASE命令删除数据库。,图3.14 删除“teacher”数据库,2使用DROP DATABASE命令批量删除数据库,DROP DATABASE 学生管理, 住房信息管理 DROP DATABASE student,stu,goods,shopping,【例3-8】 使用DROP DATABASE命令将“hello”、“every”和“person”这3个数据库批量删除。,图3.15 批量删除数据库,3.4.2 使用管理控制器删除数据库,如果用户不再需要某一数据库时，只要满足一定的条件即可将其删除，删除之后，相应的数据库文件及其数据都会被删除，并且不可恢复。,删除数据库时必须满