第5章数据库的创建和管理优秀课件.ppt

《第5章数据库的创建和管理优秀课件.ppt》由会员分享，可在线阅读，更多相关《第5章数据库的创建和管理优秀课件.ppt（40页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、第第5章数据库的创建章数据库的创建和管理和管理第1页，本讲稿共40页5.1 SQL SERVER 2005的体系结构的体系结构5.1.1SQL Server 2005服务器的组成服务器的组成SQLServer2005客户机网络存储引擎关系引擎Master系统数据库MSDB系统数据库Model系统数据库Tempdb系统数据库MSsqlsystemresource系统数据库用户数据库RDBMS第2页，本讲稿共40页1.数据库引擎的体系结构数据库引擎的体系结构数据库引擎的工作流程数据库引擎的工作流程第3页，本讲稿共40页用户界面处理查询处理事务处理事务调度内存缓冲管理存储管理故障恢复创建和维护SQL

2、 Server 2005客户机SQL Server 2005数据库引擎SQL Server 2005数据库SQL查询结果用户管理维护命令结果DBA数据字典第4页，本讲稿共40页2.SQL Server 2005数据库的体系结构数据库的体系结构从物理角度和逻辑角度两个方面考察从物理角度和逻辑角度两个方面考察SQL Server 2005数据库分为系统数据库和用户数据库数据库分为系统数据库和用户数据库两大类两大类系统数据库：系统数据库：master、model、msdb、mssqlsystemresource和和tempdb用户数据库：用户根据需要在用户数据库：用户根据需要在SQL Server

3、2005服务服务器上创建的数据管理单位器上创建的数据管理单位第5页，本讲稿共40页从逻辑角度看从逻辑角度看SQL Server 2005数据库数据库从逻辑角度看从逻辑角度看SQL Server 2005数据库呈现为各种数据对数据库呈现为各种数据对象。这些数据对象用于编写数据库应用系统和管理数据库的单象。这些数据对象用于编写数据库应用系统和管理数据库的单位。位。SQL Server 2005的数据库对象主要包括：表、视图、索引、数据的数据库对象主要包括：表、视图、索引、数据类型、存储过程、触发器。类型、存储过程、触发器。第6页，本讲稿共40页2.从物理角度看从物理角度看SQL Server 20

4、05数据库数据库物理角度的物理角度的SQL Server 2005数据库呈现为各种操作系统文件数据库呈现为各种操作系统文件默认的安装路径下默认的安装路径下SQL Server 2005的每个数据库有两类文件，缺一不可。的每个数据库有两类文件，缺一不可。数据文件：用于存放所有的数据和数据库中所建立的所有逻数据文件：用于存放所有的数据和数据库中所建立的所有逻辑数据对象辑数据对象 主数据文件数据库的关键文件，每个数据库必须且仅能有一个主数据文件数据库的关键文件，每个数据库必须且仅能有一个主数据文件，默认扩展名为主数据文件，默认扩展名为.mdf 辅数据文件用于存储未包含在主文件内的其他数据辅数据文件用

5、于存储未包含在主文件内的其他数据.ndf 日志文件日志文件:记录了用户对数据库进行的所有操作，每个数据库至记录了用户对数据库进行的所有操作，每个数据库至少有一个日志文件，少有一个日志文件，.ldf第7页，本讲稿共40页3.SQL Server 2005的数据文件的数据文件()数据文件是如何存储的数据文件是如何存储的数据文件可看成由若干标准数据文件可看成由若干标准64KB大小的区组成，每个区是由大小的区组成，每个区是由个大小的标准页组成的个大小的标准页组成的()数据页的类型数据页的类型 SQL Server 2005每个数据文件的基本存储单位为的连每个数据文件的基本存储单位为的连续空间，称为一个

6、数据页续空间，称为一个数据页 数据页数据页索引页索引页文本图象页文本图象页全局、共享全局分配映射页全局、共享全局分配映射页 可用空间页可用空间页 索引分配映射表页索引分配映射表页 大容量更改映射表页大容量更改映射表页 差异更改映射表页差异更改映射表页第8页，本讲稿共40页4.SQL Server 2005的日志文件的日志文件主要作用主要作用:(1).恢复个别事务恢复个别事务(2).启动时恢复尚未完成的事务启动时恢复尚未完成的事务(3).将数据库将数据库文件回滚到故障点文件回滚到故障点(4).事务复制事务复制(5).备用服务器备用服务器第9页，本讲稿共40页 文件组是文件组是SQL Server

7、 2005数据文件的一种逻辑管理单位数据文件的一种逻辑管理单位,若干若干个分布在不同的磁盘驱动器上的数据文件可以组织成一个文件组个分布在不同的磁盘驱动器上的数据文件可以组织成一个文件组.建立文件组的目的：建立文件组的目的：（1）可以更好地分配和管理存储空间，通过控制在特定磁盘驱）可以更好地分配和管理存储空间，通过控制在特定磁盘驱动器上单独放置数据和索引来提高数据库的性能动器上单独放置数据和索引来提高数据库的性能 （2）扩充存储空间）扩充存储空间第10页，本讲稿共40页 五、五、SQL Server 2005系统数据库系统数据库1.Master系统数据库系统数据库 最重要的数据库最重要的数据库,

8、存储的是存储的是SQL Server 2005的系统级信息的系统级信息,包括包括实例范围的元数据实例范围的元数据(如登录帐户如登录帐户)端点、链接服务器和系统配置设置端点、链接服务器和系统配置设置2Model系统数据库系统数据库存储了所有用户数据库和存储了所有用户数据库和tempdb数据库的模板数据库的模板3.Msdb系统数据库系统数据库SQL Server 2005代理服务使用的数据库，为警报、作业、任务代理服务使用的数据库，为警报、作业、任务调度和记录操作员的操作提供存储空间调度和记录操作员的操作提供存储空间第11页，本讲稿共40页4.Tempdb系统数据库系统数据库临时数据库，为所有的临

9、时表、临时存储过程，以及其临时数据库，为所有的临时表、临时存储过程，以及其他的临时操作系统的空间。他的临时操作系统的空间。5.Mssqlsystemresource(资源资源)系统数据库系统数据库只读数据库，它包含了只读数据库，它包含了SQL server 2005的所有系统对象。的所有系统对象。第12页，本讲稿共40页5.2 界面创建用户数据库数据库预算数据库预算数据库用户：单用户、多用户，用户权限数据库用户：单用户、多用户，用户权限数据库使用中的数据容量：数据库的初始数据库使用中的数据容量：数据库的初始大小、增长方式、增长极限大小、增长方式、增长极限数据库性能数据库性能:数据库的大小与硬件

10、的平衡，数据库的大小与硬件的平衡，是否使用文件组、索引是否使用文件组、索引数据库文件的存储位置、路径和属性数据库文件的存储位置、路径和属性数据库的维护数据库的维护;数据库的备份与恢复数据库的备份与恢复第13页，本讲稿共40页用户数据库的命名规则：用户数据库的命名规则：名称长度为名称长度为1128 名称的第一个字符必须是一个字母或者名称的第一个字符必须是一个字母或者“”、“”和和“”中的任意一个字符。中的任意一个字符。在中文版在中文版SQL SERVER 2005中，可以直接使中，可以直接使用中文名称用中文名称 名称中不能有空格，不允许使用名称中不能有空格，不允许使用SQL SERVER 200

11、5的保留字的保留字第14页，本讲稿共40页一、数据库的创建、修改和删除1.创建数据库（1）通过管理器2.修改数据库（1）改变数据文件的大小和增长方式（2）增加数据文件（3）删除数据文件（4）增加和删除文件组3 删除数据库第15页，本讲稿共40页 5.3 命令方式创建数据库 除了使用管理器的 图形界面 创建数据库和表外，还可以使用T-SQL语句来创建数据库和表，命令方式更为灵活。一、使用CREATE DATABASE 创建数据库 一个示例：创建一个名为XSCJ1的数据库，其初始大小为5MB，最大大小为50MB，允许数据库自动增长，增长方式为按10%比例增长，日志文件初始大小为2MB，最大可增长到

12、5MB，按1MB增长。启动查询分析器，进入查询分析器界面，将如下T-SQL语句输入：第16页，本讲稿共40页 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 第17页，本讲稿共40页2.CREATE DATABASE 的语法：CREATE DATABAS

13、E 数据库名 /*指定数据库名*/ON 子句 /*指定数据库文件和文件组属性*/LOG ON 子句 /*指定日志文件属性*/第18页，本讲稿共40页ON 子句的格式：ON PRIMARY,n,n PRIMARY 用来指定主文件，如不指定，则诸文件中的第一个文件将成为 主文件，数据文件的描述主要给出文件的逻辑名、存储路径、大小及增长特性。LOG ON 子句的格式同ON 子句例：创建一个名为TEST1的数据库。它有两个数据文件，其中主数据文件为10MB，最大大小为20MB，按2MB增长；一个辅数据文件为2MB，最大大小不限，按10%比例增长。它还有两个日志文件，大小均为5MB，最大大小均为10MB

14、，按1MB增长。第19页，本讲稿共40页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%)第20页，本讲稿共40页Log on(name=test1_log1,filename=D:LJYdatatest1_log1.

15、ldf,size=5mb,maxsize=10mb,filegrowth=1mb),(name=test1_log2,filename=D:LJYdatatest1_log2.ldf,size=5mb,maxsize=10mb,filegrowth=1mb)Go第21页，本讲稿共40页例：例：创建一个具有创建一个具有3个文件组的数据库个文件组的数据库TEST3.主文件组包括主文件组包括TEST3-dat1和和TEST3-dat2，文，文件初始大小都为件初始大小都为20MB，最大大小为，最大大小为60MB，按，按5MB增长；第二个文件组名为增长；第二个文件组名为TEST3Group1，包括文件，

16、包括文件TEST3_dat3和和TEST3_dat4，文件初始大小均为，文件初始大小均为10MB，最大大小为，最大大小为30MB，按，按10%增长，第增长，第 3个文件组名为个文件组名为TEST3Group2，包括文件，包括文件TEST3_dat5，文件，文件 初始大小为初始大小为10MB,最大大小为最大大小为30MB，按，按15%增长。该数据库增长。该数据库只有一个日志文件，初始大小为只有一个日志文件，初始大小为5MB，最大大小为，最大大小为15MB，按，按2MB增长增长第22页，本讲稿共40页Create database TEST3 ONPRIMARY(NAME=TEST3_dat1,F

17、ILENAME=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第23页，本讲稿共40页(name=test3_dat3,filename=E:SQLdatat3TEST3_dat3.ndf,size=10MB,maxsize=30mb,filegrowth=10%),(name=test3_

18、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%)第24页，本讲稿共40页Log on(name=test3_log,filename=E:SQLdatat3TEST3_log.ldf,size=5MB,maxsize=15mb,filegrowth=2MB)GO第25

19、页，本讲稿共40页二、使用 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第26页，本讲稿共40页1.为数据库增加数据文件:A

20、DD FILE 例：为数据库XSCJ增加数据文件XSCJBAK ALTER DATABASE XSCJ ADD FILE （name=xscjbak,filename=e:sqldatamssqldataxscjbak_bak.ndf,size=1mb,maxsize=2mb,filegrowth=5%）go第27页，本讲稿共40页例：修改数据文件的两个属性例：修改数据文件的两个属性Alter database xscj modify file (name=XSCJ_data maxsize=unlimited)goAlter database xscj modify file (name=X

21、SCJ_data filegowth=5MB)go第28页，本讲稿共40页2 删除数据文件:remove file 例：删除数据文件XSCJBAK:alter database xscj remove file xscjbak go第29页，本讲稿共40页3 添加文件组，并向文件组中添加文件添加文件组，并向文件组中添加文件 alter database xscj add filegroup fgroupGoAlter database xscj add file(name=xscj_data2,filegname=.xscj_data2.ndf,size=10mb,filegrowth=1mb

22、)To filegroup fgroupgo第30页，本讲稿共40页4添加日志文件添加日志文件alter database xscj add log file(name=,Filename=,Size=,Maxsize=,Filegrowth=)go 第31页，本讲稿共40页三、删除数据库：DROP DATABASE例：删除数据库TEST1 DROP DATABASE TEST1 GO 第32页，本讲稿共40页5.4 管理管理SQL Server 2005数据库数据库一、数据库状态一、数据库状态查看数据库状态：查看数据库状态：Select name,state,state_desc from

23、sys.databasesONLINEOFFLINERESTORINGRECOVERINGRECOVERING PENDINGSUSPECTEMERGENCY第33页，本讲稿共40页二、二、数据库文件的状态数据库文件的状态 1.查看数据库文件的状态查看数据库文件的状态select name,physical_name,type,type_desc,state,state_desc from sys.master_files2.含义含义ONLINEOFFLINERECOVERINGRECOVERING PENDINGSUSPECTDEFUNCT第34页，本讲稿共40页5.5 配置用户数据库配置用

24、户数据库1.查看数据库的基本信息查看数据库的基本信息2.更改数据库的所有者更改数据库的所有者第35页，本讲稿共40页3.调整数据库文件的增长属性调整数据库文件的增长属性4.限制用户对数据库的访问限制用户对数据库的访问第36页，本讲稿共40页5.配置数据库的故障恢复模型配置数据库的故障恢复模型6.配置数据库的排序规则配置数据库的排序规则7.设置数据库的统计信息选项设置数据库的统计信息选项 数据库的统计信息是指表和索引的存储数据的信息。数据库的统计信息是指表和索引的存储数据的信息。第37页，本讲稿共40页5.6 分离和附加用户数据库分离和附加用户数据库5.7 收缩用户数据库和文件收缩用户数据库和文件第38页，本讲稿共40页 写出产品销售数据库（库名为CPXS）和表T-SQL 语句：数据库初始大小为10MB，最大大小为20MB，数据自动增长，增长方式是按10%比例增长；日志文件初始大小为2MB，最大大小为5MB，按1MB增长，其余参数自定，数据库中所包含的表如下：产品表（CP）：产品编号，产品名称，价格，库存量销售商表（XSS）：客户编号，客户名称，地区，负责人，电话产品销售表（CPXS）：销售日期，产品编号，客户编号，数量，销售额创建数据库及相应数据表后，在产品表中增加“产品简介”列 第39页，本讲稿共40页第40页，本讲稿共40页