SQLServer数据库管理实用教案

1、数据库文件数据库文件(wnjin) 主数据库文件 一个数据库可以有一个或多个数据库文件，一个数据库文件只能属于一个数据库。当有多个数据库文件时，有一个文件被定义为主数据库文件（简称为主文件），其扩展名为mdf。 主数据库文件用来存储数据库的启动(qdng)信息以及部分或者全部数据，是所有数据库文件的起点，包含指向其它数据库文件的指针。一个数据库只能有一个主数据库文件。 第1页/共41页第一页，共42页。数据库文件数据库文件(wnjin) 辅助数据库文件 一个用于存储主数据库文件中未存储的剩余数据和数据库对象(duxing)，一个数据库可以没有辅助数据库文件，但也可以同时拥有多个辅助数据库文件。

2、 辅助数据库文件的扩展名为ndf（简称为辅助文件）。 第2页/共41页第二页，共42页。数据库文件数据库文件(wnjin) 事务日志文件 一个存储数据库的更新情况等事务日志信息 ，当数据库损坏时，管理员使用(shyng)事务日志恢复数据库。 每一个数据库至少必须拥有一个事务日志文件，而且允许拥有多个日志文件。事务日志文件的扩展名为ldf，日志文件的大小至少是512KB。 SQL Server事务日志采用提前写入的方式 。第3页/共41页第三页，共42页。1.创建创建(chungjin)数据库数据库 1使用向导创建(chungjin)数据库 2使用企业管理器创建(chungjin)数据库 3使用

3、Transact-SQL语言创建(chungjin)数据库第4页/共41页第四页，共42页。创建(chungjin)数据库 谁可以(ky)创建数据库（即数据库所有者）？创建数据库的权限(qunxin)默认授予sysadmin和dbcreator固定服务器角色的成员，但可以授予其它用户。创建数据库需要包括哪些内容？数据库的名称数据库命名必须符合规范数据库的大小数据文件的存放位置文件组的设置第5页/共41页第五页，共42页。使用使用(shyng)T-SQL语言创建数据库语言创建数据库CREATE DATABASE SampleON PRIMARY ( NAME=SampleData, FILENA

4、ME=c:Program Files.DataSample.mdf, SIZE=10MB, MAXSIZE=15MB, FILEGROWTH=20%) LOG ON ( NAME=SampleLog, FILENAME= c:Program Files.DataSample.ldf, SIZE=3MB, MAXSIZE=5MB, FILEGROWTH=1MB)主数据库文件(wnjin)事务(shw)日志文件逻辑文件名物理文件名文件增长量 数据库的名称、大小、数据文件位置第6页/共41页第六页，共42页。CREATE DATABASE database_nameON PRIMARY (NAME

5、= logical_file_name, FILENAME = os_file_name , SIZE = size , MAXSIZE = max_size | UNLIMITED , FILEGROWTH = growth_increment ) , nLOG ON ( NAME = logical_file_name, FILENAME = os_file_name , SIZE = size , MAXSIZE = max_size | UNLIMITED , FILEGROWTH = growth_increment ) , n使用使用(shyng)Transact-SQL语言创建数

6、据库语言创建数据库第7页/共41页第七页，共42页。 Transact-SQL语言的命令格式说明： 用 括起来(q li)的内容表示是可选的； ，n表示重复前面的内容； 用括起来(q li)表示在实际编写语句时，用相应的内容替代； 用 括起来(q li)表示是必选的； 类似A|B的格式，表示A和B只能选择一个，不能同时都选。使用使用(shyng)Transact-SQL语言创建数据库语言创建数据库第8页/共41页第八页，共42页。 database_name：数据库的名称。数据库名称在服务器中必须唯一，最长为128个字符，并且要符合标识符的命名规则。每个服务器管理的数据库最多为32767个。

7、PRIMARY：用于指定主文件组中的文件。如果不指定PRIMARY关键字，则在命令中列出的第一个文件将被默认为主文件。 SIZE：指定数据库的初始容量大小。如果没有指定主文件的大小，则SQL Server默认其与模板数据库中的主文件大小一致，其它(qt)数据库文件和事务日志文件则默认为1MB。指定大小的数字size可以使用KB、MB、GB和TB后缀，默认的后缀为MB。Size中不能使用小数，其最小值为512KB，默认值为1MB。主文件的size不能小于模板数据库中的主文件。使用使用(shyng)Transact-SQL语言创建数据库语言创建数据库第9页/共41页第九页，共42页。 MAXSIZ

8、E：指定操作系统文件可以增长到的最大尺寸。如果没有指定，则文件可以不断增长直到充满(chngmn)磁盘。 FILEGROWTH：指定文件每次增加容量的大小，当指定数据为0时，表示文件不增长。增加量可以确定为以KB、MB作后缀的字节数或以%作后缀的被增加容量文件的百分比来表示。默认后缀为MB。如果没有指定FILEGROWTH，则默认值为10%，每次扩容的最小值为64KB。 使用Transact-SQL语言(yyn)创建数据库第10页/共41页第十页，共42页。创建创建(chungjin)(chungjin)数据库（举例）数据库（举例） 例1：使用CREATE DATABASE创建一个studen

9、t数据库，所有参数均取默认值。 例2：创建一个Student1数据库，该数据库的主文件逻辑名称(mngchng)为Student1_data，物理文件名为Student1.mdf，初始大小为10MB，最大尺寸为无限大，增长速度为10%；数据库的日志文件逻辑名称(mngchng)为Student1_log，物理文件名为Student1.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为1MB。 第11页/共41页第十一页，共42页。 例3：创建一个指定多个数据文件(wnjin)和日志文件(wnjin)的数据库。该数据库名称为students，有1个10MB和1个20MB的数据文件(wnjin

10、)和2个10MB的事务日志文件(wnjin)。数据文件(wnjin)逻辑名称为student1和student2，物理文件(wnjin)名为student1.mdf和student2.ndf。主文件(wnjin)是student1，由primary指定，两个数据文件(wnjin)的最大尺寸分别为无限大和100MB，增长速度分别为10%和1MB。事务日志文件(wnjin)的逻辑名为studentlog1和studentlog2，物理文件(wnjin)名为studentlog1.ldf和studentlog2.ldf，最大尺寸均为50MB，文件(wnjin)增长速度为1MB。 创建创建(chung

11、jin)(chungjin)数据库（举例）数据库（举例）第12页/共41页第十二页，共42页。创建创建(chungjin)文文件组件组文件组的概念文件组是 SQL Server 中一个或多个文件的命名集合(jh)，它构成分配或用于数据库管理的单个单元文件组的使用场合当有多个磁盘，希望把文件分布在这些磁盘上以提高性能文件组的类型两种文件组：主文件组和用户定义文件组主文件组包含主文件的文件组用户定义的文件组在首次创建或以后更改数据库时，用户明确创建的任何文件组第13页/共41页第十三页，共42页。Northwind 数据库默认文件组默认文件组OrderHistory组组 Northwind.mdf

12、C:D:OrdHist1.ndfOrdHist2.ndfNorthwind.IdfE:创建(chungjin)文件组（续）第14页/共41页第十四页，共42页。创建创建(chungjin)文件组文件组（续）（续）ALTER DATABASE NorthwindADD FILEGROUP OrderHistoryGroupGOALTER DATABASE NorthwindADD FILE ( NAME = OrdHistYear1, FILENAME = c: HistoryDB OrdHist1.ndf, SIZE = 5MB )TO FILEGROUP OrderHistoryGroupG

13、O第15页/共41页第十五页，共42页。2. 管理(gunl)数据库 查看数据库信息(xnx) 管理数据库和日志文件的增长 数据库的分离与附加 删除数据库 数据库的备份与还原第16页/共41页第十六页，共42页。检索检索(jin su)数据库信息数据库信息 使用系统存储(cn ch)过程来显示数据库以及数据库参数的信息 sp_helpdb sp_helpdb 数据库名第17页/共41页第十七页，共42页。 使用Transact-SQL语言修改数据库 只有数据库管理员或具有CREATE DATABASE权限的数据库所有者才有权执行该语句。 ALTER DATABASE语句的语法(yf)格式如下：

14、 修改修改(xigi)(xigi)数据库数据库 第18页/共41页第十八页，共42页。Alter database databasenameadd file,n to filegroup filegroupname|add log file ,n|remove file logical_file_name |remove filegroup filegroup_name |modify file |modify name=new_databasename|add filegroup filegroup_name|modify filegroup filegroup_namefilegroup_

15、property|name=new_filegroup_name 修改修改(xigi)(xigi)数据库数据库 第19页/共41页第十九页，共42页。add file,n to filegroup filegroupname：表示向指定的文件(wnjin)组中添加新的数据文件(wnjin)。add log file ,n：增加新的日志文件(wnjin)。remove file logical_file_name：删除指定的操作系统文件(wnjin)。remove filegroup filegroup_name：删除指定的文件(wnjin)组。modify file ：修改某个操作系统文件(w

16、njin)。modify name=new_databasename：重命名数据库。add filegroup filegroup_name：增加一个文件(wnjin)组。modify filegroup filegroup_name：修改某个指定文件(wnjin)组的属性。第20页/共41页第二十页，共42页。修改(xigi)(xigi)数据库（举例） ALTER DATABASE Sample MODIFY FILE ( NAME = SampleLog, SIZE = 15MB)GOALTER DATABASE SampleADD FILE (NAME = SampleData2, FI

17、LENAME=c:Program Files. DataSample2.ndf, SIZE=15MB, MAXSIZE=20MB)GO第21页/共41页第二十一页，共42页。管理数据文件管理数据文件(wnjin)和日志文和日志文件件(wnjin)的增长的增长ALTER DATABASE Sample MODIFY FILE ( NAME = SampleLog, SIZE = 15MB)GOALTER DATABASE SampleADD FILE (NAME = SampleData2, FILENAME=c:Program Files.DataSample2.ndf, SIZE=15MB,

18、 MAXSIZE=20MB)GO 使文件(wnjin)自动增长 手动扩充数据库文件(wnjin) 添加次要数据库文件(wnjin)第22页/共41页第二十二页，共42页。3. 管理(gunl)数据库 收缩数据库或数据文件 当为数据库分配的空间过大，或者(huzh)对空间的需求降低时，可以收缩整个数据库或数据库中的某个数据文件的大小。 有以下 2 种收缩数据库方法： 自动收缩 手动收缩第23页/共41页第二十三页，共42页。3. 管理(gunl)数据库 收缩(shu su)数据库 自动收缩(shu su) 第24页/共41页第二十四页，共42页。3. 管理(gunl)数据库 手动收缩(shu s

19、u) 收缩(shu su)数据库 DBCC SHRINKDATBASE命令 收缩(shu su)数据文件 DBCC SHRINKFILE命令DBCC SHRINKDATABASE (DBCC SHRINKDATABASE (数据库名数据库名 , , 目标目标(mbio)(mbio)百分百分比比 , NOTRUNCATE | TRUNCATEONLY) , NOTRUNCATE | TRUNCATEONLY) DBCC SHRINKFILE (DBCC SHRINKFILE (文件名文件名 | | 文件文件idid , , 目标大小目标大小 , , EMPTYFILE | NOTRUNCATE

20、| TRUNCATEONLY)EMPTYFILE | NOTRUNCATE | TRUNCATEONLY) 第25页/共41页第二十五页，共42页。收缩收缩(shu su)数据库或文件（续）数据库或文件（续）有一个小型(xioxng)的酒店管理系统，其营业数据为150MB，对其中的50%的数据进行备份并删除后，要把数据库立即缩小为初始的大小（即100MB）。下面哪条语句能够实现该功能？ A. DBCC SHRINKFILE(JiuDianData,NOTRUNCATE) B. DBCC SHRINKDATABASE(JiuDianData,25) C. DBCC SHRINKDATABASE(

21、JiuDianData,100) D. ALTER DATABASE JiuDianData SET AUTO_SHRINK ON第26页/共41页第二十六页，共42页。3. 管理(gunl)数据库 数据库的分离与附加 当磁盘空间使用殆尽，或硬件需要升级等情况(qngkung)下，需要将数据库移动到其它的物理驱动器上，这时需要先将数据库分离。当硬件整理完成后，再将数据库附加到数据库服务器中，继续运行。注意事项（1）确定没人在使用(shyng)此数据库；（2）确定数据库里没有未完成的任务。第27页/共41页第二十七页，共42页。3. 管理(gunl)数据库 数据库的分离(fnl)与附加 分离(f

22、nl)数据库第28页/共41页第二十八页，共42页。3. 管理(gunl)数据库 数据库的分离(fnl)与附加 附加数据库第29页/共41页第二十九页，共42页。3. 管理(gunl)数据库 删除(shnch)数据库 使用企业管理器删除(shnch)数据库 在查询分析器中删除(shnch)数据库 删除(shnch)数据库的T-SQL语句DROP DATABASE Demo, HongWenSoft 第30页/共41页第三十页，共42页。删除删除(shnch)(shnch)数据库数据库 1利用企业管理器删除数据库 2利用Drop语句删除数据库 Drop database database_name,n 说明(shumng)：只有处于正常状态下的数据库，才能使用DROP语句删除。当数据库处于以下状态时不能被删除：数据库正在使用；数据库正在恢复；数据库包含用于复制的已经出版的对象。 DROP DATABASE Northwind, pubs第31页/共41页第三十一页，共42页。4. 数据库的备份(bi fn)与还原 在企业管理器中备份(bi fn)数据库 在企业管理器中还原数据库第32页/共41页第三十二页，共42页。4. 数据库的备份(bi fn)与还原 在企业管理器中备份数据库 选择(xunz)备份命令第33页/共41页第三十三页，共42页。