第5章数据库的创建和管理精选文档.ppt

第第5章数据库的创建章数据库的创建和管理和管理本讲稿第一页，共四十页5.1 SQL SERVER 2005的体系结构的体系结构5.1.1SQL Server 2005服务器的组成服务器的组成SQLServer2005客户机网络存储引擎关系引擎Master系统数据库MSDB系统数据库Model系统数据库Tempdb系统数据库MSsqlsystemresource系统数据库用户数据库RDBMS本讲稿第二页，共四十页1.数据库引擎的体系结构数据库引擎的体系结构数据库引擎的工作流程数据库引擎的工作流程本讲稿第三页，共四十页用户界面处理查询处理事务处理事务调度内存缓冲管理存储管理故障恢复创建和维护SQL Server 2005客户机SQL Server 2005数据库引擎SQL Server 2005数据库SQL查询结果用户管理维护命令结果DBA数据字典本讲稿第四页，共四十页2.SQL Server 2005数据库的体系结构数据库的体系结构从物理角度和逻辑角度两个方面考察从物理角度和逻辑角度两个方面考察SQL Server 2005数据库分为系统数据库和用户数据库数据库分为系统数据库和用户数据库两大类两大类系统数据库：系统数据库：master、model、msdb、mssqlsystemresource和和tempdb用户数据库：用户根据需要在用户数据库：用户根据需要在SQL Server 2005服务器上创服务器上创建的数据管理单位建的数据管理单位本讲稿第五页，共四十页从逻辑角度看从逻辑角度看SQL Server 2005数据库数据库从逻辑角度看从逻辑角度看SQL Server 2005数据库呈现为各种数据对数据库呈现为各种数据对象。这些数据对象用于编写数据库应用系统和管理数据库的单象。这些数据对象用于编写数据库应用系统和管理数据库的单位。位。SQL Server 2005的数据库对象主要包括：表、视图、索引、的数据库对象主要包括：表、视图、索引、数据类型、存储过程、触发器。数据类型、存储过程、触发器。本讲稿第六页，共四十页2.从物理角度看从物理角度看SQL Server 2005数据库数据库物理角度的物理角度的SQL Server 2005数据库呈现为各种操作系统文件数据库呈现为各种操作系统文件默认的安装路径下默认的安装路径下SQL Server 2005的每个数据库有两类文件，缺一不可。的每个数据库有两类文件，缺一不可。数据文件：用于存放所有的数据和数据库中所建立的所有逻辑数据文件：用于存放所有的数据和数据库中所建立的所有逻辑数据对象数据对象 主数据文件数据库的关键文件，每个数据库必须且仅能主数据文件数据库的关键文件，每个数据库必须且仅能有一个主数据文件，默认扩展名为有一个主数据文件，默认扩展名为.mdf 辅数据文件用于存储未包含在主文件内的其他数据辅数据文件用于存储未包含在主文件内的其他数据.ndf 日志文件日志文件:记录了用户对数据库进行的所有操作，每个数据库至记录了用户对数据库进行的所有操作，每个数据库至少有一个日志文件，少有一个日志文件，.ldf本讲稿第七页，共四十页3.SQL Server 2005的数据文件的数据文件()数据文件是如何存储的数据文件是如何存储的数据文件可看成由若干标准数据文件可看成由若干标准64KB大小的区组成，每个区大小的区组成，每个区是由个大小的标准页组成的是由个大小的标准页组成的()数据页的类型数据页的类型 SQL Server 2005每个数据文件的基本存储单位为的每个数据文件的基本存储单位为的连续空间，称为一个数据页连续空间，称为一个数据页 数据页数据页索引页索引页文本图象页文本图象页全局、共享全局分配映射页全局、共享全局分配映射页 可用空间页可用空间页 索引分配映射表页索引分配映射表页 大容量更改映射表页大容量更改映射表页 差异更改映射表页差异更改映射表页本讲稿第八页，共四十页4.SQL Server 2005的日志文件的日志文件主要作用主要作用:(1).恢复个别事务恢复个别事务(2).启动时恢复尚未完成的事务启动时恢复尚未完成的事务(3).将数据库将数据库文件回滚到故障点文件回滚到故障点(4).事务复制事务复制(5).备用服务器备用服务器本讲稿第九页，共四十页 文件组是文件组是SQL Server 2005数据文件的一种逻辑管理单位数据文件的一种逻辑管理单位,若干个分若干个分布在不同的磁盘驱动器上的数据文件可以组织成一个文件组布在不同的磁盘驱动器上的数据文件可以组织成一个文件组.建立文件组的目的：建立文件组的目的：（1）可以更好地分配和管理存储空间，通过控制在特定磁）可以更好地分配和管理存储空间，通过控制在特定磁盘驱动器上单独放置数据和索引来提高数据库的性能盘驱动器上单独放置数据和索引来提高数据库的性能 （2）扩充存储空间）扩充存储空间本讲稿第十页，共四十页 五、五、SQL Server 2005系统数据库系统数据库1.Master系统数据库系统数据库 最重要的数据库最重要的数据库,存储的是存储的是SQL Server 2005的系统级信的系统级信息息,包括实例范围的元数据包括实例范围的元数据(如登录帐户如登录帐户)端点、链接服务器和端点、链接服务器和系统配置设置系统配置设置2Model系统数据库系统数据库存储了所有用户数据库和存储了所有用户数据库和tempdb数据库的模板数据库的模板3.Msdb系统数据库系统数据库SQL Server 2005代理服务使用的数据库，为警报、作业、代理服务使用的数据库，为警报、作业、任务调度和记录操作员的操作提供存储空间任务调度和记录操作员的操作提供存储空间本讲稿第十一页，共四十页4.Tempdb系统数据库系统数据库临时数据库，为所有的临时表、临时存储过程，以及临时数据库，为所有的临时表、临时存储过程，以及其他的临时操作系统的空间。其他的临时操作系统的空间。5.Mssqlsystemresource(资源资源)系统数据库系统数据库只读数据库，它包含了只读数据库，它包含了SQL server 2005的所有系统对的所有系统对象。象。本讲稿第十二页，共四十页5.2 界面创建用户数据库数据库预算数据库预算数据库用户：单用户、多用户，用户权限数据库用户：单用户、多用户，用户权限数据库使用中的数据容量：数据库的初始数据库使用中的数据容量：数据库的初始大小、增长方式、增长极限大小、增长方式、增长极限数据库性能数据库性能:数据库的大小与硬件的平衡，数据库的大小与硬件的平衡，是否使用文件组、索引是否使用文件组、索引数据库文件的存储位置、路径和属性数据库文件的存储位置、路径和属性数据库的维护数据库的维护;数据库的备份与恢复数据库的备份与恢复本讲稿第十三页，共四十页用户数据库的命名规则：用户数据库的命名规则：名称长度为名称长度为1128 名称的第一个字符必须是一个字母或者名称的第一个字符必须是一个字母或者“”、“”和和“”中的任意一个字符。中的任意一个字符。在中文版在中文版SQL SERVER 2005中，可以直接使用中中，可以直接使用中文名称文名称 名称中不能有空格，不允许使用名称中不能有空格，不允许使用SQL SERVER 2005的保留字的保留字本讲稿第十四页，共四十页一、数据库的创建、修改和删除1.创建数据库（1）通过管理器2.修改数据库（1）改变数据文件的大小和增长方式（2）增加数据文件（3）删除数据文件（4）增加和删除文件组3 删除数据库本讲稿第十五页，共四十页 5.3 命令方式创建数据库 除了使用管理器的 图形界面 创建数据库和表外，还可以使用T-SQL语句来创建数据库和表，命令方式更为灵活。一、使用CREATE DATABASE 创建数据库 一个示例：创建一个名为XSCJ1的数据库，其初始大小为5MB，最大大小为50MB，允许数据库自动增长，增长方式为按10%比例增长，日志文件初始大小为2MB，最大可增长到5MB，按1MB增长。启动查询分析器，进入查询分析器界面，将如下T-SQL语句输入：本讲稿第十六页，共四十页 CREATE DATABASE XSCJ1 ON (NAME=XSCJ_DATA,FILENAME=d:ljyDATAXSCJ.mdf,SIZE=5MB,MAXSIZE=50MB,FILEGROWTH=10%)LOG ON(NAME=XSCJ_LOG,FILENAME=d:ljyDATAXSCJ _LOG.ldf,SIZE=2MB,MAXSIZE=5MB,FILEGROWTH=1MB)GO 本讲稿第十七页，共四十页2.CREATE DATABASE 的语法：CREATE DATABASE 数据库名 /*指定数据库名*/ON 子句 /*指定数据库文件和文件组属性*/LOG ON 子句 /*指定日志文件属性*/本讲稿第十八页，共四十页ON 子句的格式：ON PRIMARY,n,n PRIMARY 用来指定主文件，如不指定，则诸文件中的第一个文件将成为 主文件，数据文件的描述主要给出文件的逻辑名、存储路径、大小及增长特性。LOG ON 子句的格式同ON 子句例：创建一个名为TEST1的数据库。它有两个数据文件，其中主数据文件为10MB，最大大小为20MB，按2MB增长；一个辅数据文件为2MB，最大大小不限，按10%比例增长。它还有两个日志文件，大小均为5MB，最大大小均为10MB，按1MB增长。本讲稿第十九页，共四十页CREATE DATABASE TEST1 ON PRIMARY(NAME=TEST1_data1,FILENAME=D:LJYDATATEST1_data.mdf,size=10MB,MAXSIZE=20MB,FILEGROWTH=2MB),(NAME=TEST1_data2,filename=D:LJYDATAtest1_data2.ndf,size=2mb,maxsize=unlimited,filegrowth=10%)本讲稿第二十页，共四十页Log on(name=test1_log1,filename=D:LJYdatatest1_log1.ldf,size=5mb,maxsize=10mb,filegrowth=1mb),(name=test1_log2,filename=D:LJYdatatest1_log2.ldf,size=5mb,maxsize=10mb,filegrowth=1mb)Go本讲稿第二十一页，共四十页例：例：创建一个具有创建一个具有3个文件组的数据库个文件组的数据库TEST3.主文件组包括主文件组包括TEST3-dat1和和TEST3-dat2，文件初，文件初始大小都为始大小都为20MB，最大大小为，最大大小为60MB，按，按5MB增长；第二个文件组名为增长；第二个文件组名为TEST3Group1，包括文件，包括文件TEST3_dat3和和TEST3_dat4，文件初始大小均为，文件初始大小均为10MB，最大大小为，最大大小为30MB，按，按10%增长，第增长，第 3个文件组名为个文件组名为TEST3Group2，包括文件，包括文件TEST3_dat5，文件，文件 初始初始大小为大小为10MB,最大大小为最大大小为30MB，按，按15%增长。该数据库只有一个日志文件，初始大小为增长。该数据库只有一个日志文件，初始大小为5MB，最大大小为，最大大小为15MB，按，按2MB增长增长本讲稿第二十二页，共四十页Create database TEST3 ONPRIMARY(NAME=TEST3_dat1,FILENAME=E:SQLdatat3TEST3_dat1.mdf,size=20MB,MAXSIZE=60MB,filegrowth=5mb),(name=test3_dat2,filename=E:SQLdatat3TEST3_dat2.ndf,size=20mb,maxsize=60mb,filegrowth=5mb),Filegroup test3group1本讲稿第二十三页，共四十页(name=test3_dat3,filename=E:SQLdatat3TEST3_dat3.ndf,size=10MB,maxsize=30mb,filegrowth=10%),(name=test3_dat4,filename=E:SQLdatat3TEST3_dat4.ndf,size=10MB,maxsize=30mb,filegrowth=10%),Filegroup test3group2(name=test3_dat5,filename=E:SQLdatat3TEST3_dat5.ndf,size=10MB,maxsize=30mb,filegrowth=15%)本讲稿第二十四页，共四十页Log on(name=test3_log,filename=E:SQLdatat3TEST3_log.ldf,size=5MB,maxsize=15mb,filegrowth=2MB)GO本讲稿第二十五页，共四十页二、使用 ALTER DATABASE 修改数据库使用ALTER DATABASE语句一次只能修改数据文件的一个属性。ALTER DATABASE database_name add file,nTO FILEGROUP filegroup_name|add LOGFILE,n|remove file logical_file_name|add filegroup filegroup_name|remove filegroup filegroup_name|modify file|modify name=new_dbname本讲稿第二十六页，共四十页1.为数据库增加数据文件:ADD FILE 例：为数据库XSCJ增加数据文件XSCJBAK ALTER DATABASE XSCJ ADD FILE （name=xscjbak,filename=e:sqldatamssqldataxscjbak_bak.ndf,size=1mb,maxsize=2mb,filegrowth=5%）go本讲稿第二十七页，共四十页例：修改数据文件的两个属性例：修改数据文件的两个属性Alter database xscj modify file (name=XSCJ_data maxsize=unlimited)goAlter database xscj modify file (name=XSCJ_data filegowth=5MB)go本讲稿第二十八页，共四十页2 删除数据文件:remove file 例：删除数据文件XSCJBAK:alter database xscj remove file xscjbak go本讲稿第二十九页，共四十页3 添加文件组，并向文件组中添加文件添加文件组，并向文件组中添加文件 alter database xscj add filegroup fgroupGoAlter database xscj add file(name=xscj_data2,filegname=.xscj_data2.ndf,size=10mb,filegrowth=1mb)To filegroup fgroupgo本讲稿第三十页，共四十页4添加日志文件添加日志文件alter database xscj add log file(name=,Filename=,Size=,Maxsize=,Filegrowth=)go 本讲稿第三十一页，共四十页三、删除数据库：DROP DATABASE例：删除数据库TEST1 DROP DATABASE TEST1 GO 本讲稿第三十二页，共四十页5.4 管理管理SQL Server 2005数据库数据库一、数据库状态一、数据库状态查看数据库状态：查看数据库状态：Select name,state,state_desc from sys.databasesONLINEOFFLINERESTORINGRECOVERINGRECOVERING PENDINGSUSPECTEMERGENCY本讲稿第三十三页，共四十页二、二、数据库文件的状态数据库文件的状态 1.查看数据库文件的状态查看数据库文件的状态select name,physical_name,type,type_desc,state,state_desc from sys.master_files2.含义含义ONLINEOFFLINERECOVERINGRECOVERING PENDINGSUSPECTDEFUNCT本讲稿第三十四页，共四十页5.5 配置用户数据库配置用户数据库1.查看数据库的基本信息查看数据库的基本信息2.更改数据库的所有者更改数据库的所有者本讲稿第三十五页，共四十页3.调整数据库文件的增长属性调整数据库文件的增长属性4.限制用户对数据库的访问限制用户对数据库的访问本讲稿第三十六页，共四十页5.配置数据库的故障恢复模型配置数据库的故障恢复模型6.配置数据库的排序规则配置数据库的排序规则7.设置数据库的统计信息选项设置数据库的统计信息选项 数据库的统计信息是指表和索引的存储数据的信息。数据库的统计信息是指表和索引的存储数据的信息。本讲稿第三十七页，共四十页5.6 分离和附加用户数据库分离和附加用户数据库5.7 收缩用户数据库和文件收缩用户数据库和文件本讲稿第三十八页，共四十页 写出产品销售数据库（库名为CPXS）和表T-SQL 语句：数据库初始大小为10MB，最大大小为20MB，数据自动增长，增长方式是按10%比例增长；日志文件初始大小为2MB，最大大小为5MB，按1MB增长，其余参数自定，数据库中所包含的表如下：产品表（CP）：产品编号，产品名称，价格，库存量销售商表（XSS）：客户编号，客户名称，地区，负责人，电话产品销售表（CPXS）：销售日期，产品编号，客户编号，数量，销售额创建数据库及相应数据表后，在产品表中增加“产品简介”列 本讲稿第三十九页，共四十页本讲稿第四十页，共四十页