sql数据库第五章SQLServer数据库管理.ppt

SQL Server 数据库程序设计,第5章 SQL Server 2008数据库管理,本章学习目标理解SQL Server 2008数据库结构和组成；熟练掌握数据库创建、删除和修改等操作；了解用户数据库的增缩、分离和附加等操作。,5.1.1 数据库的结构,数据被DBMS管理与数据存储在物理磁盘上是数据库中两种完全不同的数据组织形式，分别称之为数据库的物理结构和数据库的逻辑结构。SQL Server 2008 DBMS将数据组织成数据表、视图等逻辑对象，这是从逻辑角度来组织与管理数据。为了数据库管理员管理数据的方便和与操作系统实际情况相一致，SQL Server 2008又将数据库呈现为各种数据库的文件，这是从物理角度来组织与管理数据。,1数据库的逻辑结构对用户来讲，数据库由若干个用户可视的对象构成，如表、视图、角色等，由于这些对象都存在于数据库中，因此称为数据库对象。用户利用这些数据库对象存储，读取数据库中的数据，直接或间接地用于不同应用程序的存储、操作、检索等工作。SQL Server 2008的数据库对象主要包括表（table）、视图（view）、索引（index）、默认（default）、存储过程（stored procedure）、触发器（trigger）和约束（constraint）等。,2数据库的物理结构数据库物理结构主要应用于面向计算机的数据组织和管理。数据库的物理结构表现就是操作系统文件，一个数据库由一个或多个磁盘上的文件组成。SQL Server数据库的文件有两种类型：数据文件日志文件,主数据文件次数据文件,主数据文件主数据文件是数据库的起点，其中包含了数据库的初始信息，并记录数据库还拥有哪些文件。每个数据库有且只能有一个主数据文件。主数据文件是数据库必需的文件，Microsoft建议的主数据文件的扩展名是.mdf次要数据文件除主数据文件以外的所有其他数据文件都是次要数据文件。次要数据文件不是数据库必需的文件。Microsoft建议的次要数据文件的扩展名是.ndf,数据文件类型,数据文件结构,数据文件的结构按照层次可以划分为页和区，每个数据文件由若干个大小为64KB的区组成，每个区由8个8KB大小的连续空间组成，这些连续空间称为页。页是数据存储的基本单位。数据文件中的页有8种类型，用于存放不同种类的数据。区是SQL Server分配给表和索引的基本单位。区有统一区、混合区两种类型。为了提高存储空间的使用效率，不满8个页的对象放混合区，满8个页的放统一区。,文件组,为了便于分配和管理，SQL Server允许将多个文件归纳为同一组，并赋予此组一个名称，这就是文件组。主文件组（Primary FileGroup）：系统文件分配到此组，每一个数据库中的主文件和未被放入其他组的次要文件在此组中。用户定义文件组（Secondary FileGroup）默认文件组：未指定对象所属文件组的对象都被放入此组,日志文件用于保存恢复数据库所需的事务日志信息。每个数据库至少有一个日志文件，也可以有多个。日志文件的扩展名为.LDF。,5.1.3事物日志文件,5.2 SQL Server 2008数据库基本管理,在SQL Server 2008中，所有类型的数据库管理操作都包括两种方法：一种方法是使用SQL Server Management Studio的对象资源管理器，以图形化的方式完成对于数据库的管理；另一种方法是使用T-SQL语句或系统存储过程，以命令方式完成对于数据库的管理。,5.2.1创建用户数据库,在建立用户逻辑组件之前（如基本表）必须首先建立数据库。Sql server 2008中可以支持最多32767个数据库命名规则：名称长度不能超过128个字符，临时性表名不能超过116个字符名称第1个字符必须是英文字母、中文、下划线、和#（不建议用）除第1个字符外的其他字符可以包含数字和$符号名称中间不允许有空格和其他特殊字符,以学生-课程数据库为例，在Management Stuio中以图形界面的方式练习创建数据库，修改数据库。例1：创建一个学生-课程数据库（名称为student），要求只有一个数据文件和日志文件，文件名称分别为stu和stu_log，初始大小都为1MB，增长方式分别为10%和1MB，数据文件最大为500MB，日志文件大小不受限制。,图形化的方式创建数据库,逻辑名称：数据文件和日志文件名称文件类型：用于区别当前文件是数据文件还是日志文件文件组：显示当前数据库文件所属的文件组。一个数据库文件只能存在于一个文件组里。初始大小：制定该文件的初始容量自动增长：用于设置在文件的容量不够用时，文件根据何种增长方式自动增长。路径：指定存放该文件的目录。在创建大型数据库时，尽量把主数据文件和事务日志文件设置在不同路径下，这样能够提高数据读取的效率。,主文件必须至少3MB,将主数据初始文件修改为3MB或3MB以上,创建含有文件组的多数据文件和多日志文件的数据库,在【文件组】页面中的行对话框单击【添加】来添加文件组“stu_infoGroup1”，然后单击【确定】按钮完成含有文件组的多数据文件和多日志文件的数据库的创建。,界面方式修改数据库,可修改数据库的名称、大小和属性,界面方式删除数据库,命令方式下管理数据库,命令方式下创建数据库,在使用TSQL语句创建数据库中，其核心是CREATE DATABASE 语句。该语句基本语法格式是：CREATE DATABASE database_name/*指定数据库名*/ON file子句/*指定数据库文件和文件组属性*/LOG ON file子句/*指定日志文件属性*/,file子句 NAME=logical_file_name,/*文件的逻辑名*/FILENAME=os_file_name/*文件的物理名*/,SIZE=size/*文件的初始大小*/,MAXSIZE=max_size|UNLIMITED/*文件可增大的最大大小*/,FILEGROWTH=grow_increment)/*文件的自动增量*/,.n,例2：要求创建一个学生-课程数据库（名称为xskc）Create database student例3：要求在本地磁盘D创建一个学生-课程数据库（名称为student），只有一个数据文件和日志文件，文件名称分别为stu和stu_log，初始大小都为3MB，增长方式分别为10%和1MB，数据文件最大为500MB，日志文件大小不受限制。,CREATE DATABASE studentON(NAME=stu,FILENAME=D:stu.mdf,SIZE=3MB,MAXSIZE=500MB,FILEGROWTH=10%)LOG ON(NAME=stu_log,FILENAME=D:stu_log.ldf,SIZE=1MB,MAXSIZE=unlimited，FILEGROWTH=1MB),先按【分析】按钮再按【执行】按钮,分析,命令方式下选择数据库,选择需要修改的数据库在SQL Server服务器上，可能存在多个用户数据库，用户只有连接上所要使用的数据库，才能对该数据库中的数据进行操作。选择数据库语句的语法格式如下：USE database_name其中，database_name为选择的数据库名称。,命令方式下查看数据库属性,数据库的属性信息都保存在系统数据库和系统数据表中，可以通过系统提供的存储过程来获取有关数据库的属性信息。sp_helpdb 显示数据库和数据库参数信息。sp_spaceused 查看数据库空间信息。sp_options 查看数据库选项信息。注：执行存储过程需要进行过程调用，即使用exec任务（运行带有指定参数的指定程序或命令。）,【例4】查询数据库student的相关参数信息。Exec sp_helpdb student或Use studentExec sp_helpdb,命令方式下修改数据库,Alter database database_nameadd file,n to filegroup filegroupname|add log file,n|remove file logical_file_name with delete|modify file|modify name=new_databasename|add filegroup filegroup_name|remove filegroup filegroup_name|modify filegroup filegroup_name,filespec：file子句,语法格式,参数说明如下。ADD FILE：向数据库文件组添加新的数据文件。ADD LOG FILE：向数据库添加事务日志文件。REMOVE FILE：从SQL Server的实例中删除逻辑文件说明并删除物理文件。MODIFY FILE：修改某一文件的属性。ADD FILEGROUP：向数据库添加文件组。REMOVE FILEGROUP：从实例中删除文件组。MODIFY FILEGROUP：修改某一文件组的属性。,修改数据库名称ALTER DATABASE databaseName MODIFY NAME=newdatabaseName【例5】将“student”数据库更名为“学生信息管理系统”ALTER DATABASE student MODIFY NAME=学生信息管理系统,增加数据库文件【例6】对“student“数据库增加数据文件stu2，初始大小5MB，最大大小10MB，增长方式10%。ALTER DATABASE studentADD FILE(NAME=stu2,FILENAME=C:stu2.ndf,SIZE=5MB,MAXSIZE=10MB,FILEGROWTH=10%),修改数据库【例7】将“student“数据库主数据文件最大大小增加10MB。ALTER DATABASE studentMODIFY FILE(NAME=stu,MAXSIZE=10MB,),命令方式下删除数据库,语法格式：DROP DATABASE database_name,n/*database_name为要删除的数据库名*/例7：将数据库student删除，可使用下述语句。DROP DATABASE student,练习,1、在c盘根目录下创建一个Company数据库，该数据库的主数据文件逻辑名称为Company_data，物理文件为Company.mdf，初始大小为10MB，最大尺寸为无限大，增长速度为10%；数据库的日志文件逻辑名称为Company_log，物理文件名为Company.ldf，初始大小为1MB，最大尺寸为50MB，增长速度为1MB。,2、修改数据库student现有数据文件的属性，将主数据文件的最大大小改为50MB，增长方式改为每次2MB增长。3、对数据库student增加数据文件student2，初始大小为10MB，最大大小为50MB，按10%增长。4、删除新增的数据文件student25、将数据库student的名字改为DB。,SQL Server 采取预先分配空间的方法来建立数据库的数据文件或者日志文件，比如数据文件的空间分配了100MB，而实际上只占用了50MB空间，这样造成存储空间的浪费。为此，SQL Server 2008提供了收缩数据库的功能，允许对数据库中的每个文件进行收缩，删除已经分配但没有使用的页。,5.3.1 收缩用户数据库,1利用对象资源管理器收缩用户数据库1）自动收缩用户数据库2）手动收缩用户数据库3）手动收缩数据文件,自动收缩数据库,手动收缩数据库,手动收缩数据库文件,2利用T-SQL语句收缩用户数据库在SQL Server中，收缩用户数据库包括自动收缩数据库、手动收缩数据库和收缩指定数据文件3种方式。,1）自动收缩数据库使用ALTER DATABASE语句可以实现用户数据库自动收缩，其语法格式如下：ALTER DATABASE database_nameSET AUTO_SHRINK ON/OFF参数说明如下。ON：将数据库设为自动收缩；OFF：将数据库设为不自动收缩。,2）手动收缩数据库使用DBCC SHRINKDATABASE语句可以实现用户数据库手动收缩，其语法格式如下：DBCC SHRINKDATABASE(database_name|database_id|0,target_percent,NOTRUNCATE|TRUNCATEONLY),database_name：要收缩的数据库的名称或ID。id如果为0则指当前数据库,target_percent：数据库收缩后的数据库文件中所需的剩余可用空间百分比。,NOTRUNCATE：通过将已分配的页从文件末尾移动到文件前面的未分配页来压缩数据文件中的数据。,TRUNCATEONLY：将文件末尾的所有可用空间释放给操作系统，但不在文件内部执行任何页移动。,【例8】将数据库student的大小收缩，并保留数据库有10%的可用空间 DBCC SHRINKDATABASE(student,10),3）收缩指定数据文件使用DBCC SHRINKFILE语句可以实现收缩指定数据文件，其语法格式如下：DBCC SHRINKFILE(file_name|file_id,EMPTYFILE|,target_size,NOTRUNCATE|TRUNCATEONLY)WITH NO_INFOMSGS,file_name：要收缩的文件的逻辑名称或文件的标识（ID）号,target_size：用兆字节表示的文件大小（用整数表示）。如果未指定，则将文件大小减少到默认文件大小。默认大小为创建文件时指定的大小。,