《SQL SERVER数据库管理系统》课程教案.docx
- 文档编号:30118214
- 上传时间:2023-08-05
- 格式:DOCX
- 页数:17
- 大小:42.60KB
《SQL SERVER数据库管理系统》课程教案.docx
《《SQL SERVER数据库管理系统》课程教案.docx》由会员分享,可在线阅读,更多相关《《SQL SERVER数据库管理系统》课程教案.docx(17页珍藏版)》请在冰豆网上搜索。
《SQLSERVER数据库管理系统》课程教案
《SQLSERVER2000数据库管理系统》课程教案
专业班级教师授课时间
教学课题
第五讲数据库的创建与管理
学时
2
教
学
目
的
1、熟悉数据库的文件组成
2、熟练创建、管理数据库
教
材
分
析
重
点
1、逻辑文件、物理文件和文件组的概念
2、创建数据库和维护数据库的两种方法
难
点
用T-SQL语句创建数据库和维护数据库
教
学
方
法
采用“项目教学法”
教
学
手
段
多媒体教学
教
学
过
程
及
时
间
分
配
★复习巩固,问题牵引、导入新课。
提问:
5min
(1)在开发数据库应用系统时,应该如何设计自己的数据库?
(2)设计数据库时要注意哪些什么问题
5.1数据库简介20min
主数据文件次主数据文件事务日志文件盘区和页面文件组
5.2估算数据库的空间需求10min
5.3数据库的创建20min
使用企业管理器创建;使用数据库向导创建;使用T-SQL语句创建;
5.4数据库的管理30min
查看数据库属性修改数据库大小数据库的收缩数据库的更名数据库的删除
分离数据库存附加数据库
★小结及作业布置5min
【教学内容】
第五章数据库的创建与管理
5.1数据库简介
5.1.1数据库文件的组成
SQL中数据保存在独立的数据库文件中,数据库文件是存放数据库数据和数据库对象的文件。
一个数据库通常有两个文件,一个用于存放数据,称为数据文件,另一个用于存放数据库的操作记录,称为事务日志文件。
数据库中所有的数据和对象,如表、存储过程、触发器和视图,都只保存在以下的操作系统文件中
1、主数据文件:
包含数据库的启动信息,以及存储数据库。
每个数据库只有一个主数据文件,文件后缀名为:
.MDF。
主数据文件是所有数据文件的起点,包含指向其他数据库文件的指针。
2、次数据文件:
当一个数据库数据量大到主数据文件(在一个磁盘上)容纳不下,需要多个次数据文件(可以在多个磁盘上)时,就可以采用次数据文件来保存所有主数据文件中容纳不下的数据。
一个数据库可以有多个次数据文件,后缀为.NDF
3、事务日志文件:
是用来记录数据库更新情况的文件,它保存了恢复数据库的所有日志信息,扩展名为.ldf。
例如:
update、insert、delete等更改操作会记录在此文件中,而select的操作不会更改数据库,则不会记录在案。
一个数据库可以有多个事务日志文件。
说明:
一个数据库可以有一个或多个数据库文件,但一个数据库文件只属于一个数据库。
例:
jwgl库可以包含jwgl_data.mdf和jwgl_log.ldf两个数据文件,但jwgl_data.mdf只能属于jwgl这一个数据库所有。
数据库文件可以不断扩充而不受操作系统文件大小的限制。
可以将数据库文件存储在不同的硬盘中,这样可以同时对几个硬盘做数据存取,提高了数据处理的效率。
5.1.2数据库的存储结构
1.盘区和页面的概念
页:
是sqlserver分配空间时使用的最小单位,用于存储数据库的实际数据。
一页的长度是8KB,页只能存储同一表中的数据,每页可包含至少一行数据。
页的分类:
盘区:
是表和索引分配空间的基本单位,由8个连续页面组成的数据结构称为盘区,大小为8*8KB=64KB。
盘区只能存储数据库中的一个对象,如:
表、视图、索引等。
例:
当在表中插入一个表时,即使表中只插入一条记录,也会占用64KB的空间。
2.数据库的存储组织。
数据库由数据库文件组成:
数据文件由盘区组成;
盘区由8个连续的页面组成;
页面的大小8KB,由连续的数据行组成,页首大小96B,用于存储系统信息。
说明:
所有的数据库都具有惟一的主数据文件、一个或多个次数据文件以及一个或多个事务日志文件。
数据页存放除text、ntext和image等数据外的所有数据行,text、ntext和image存放在单独的页中,行不能够跨越页,一行中所能够包含的最大数据量是8,060字节
数据存放在连续的页中;
日志文件不包含页,而是包含一系列日志记录;
所有新建的数据库都是Model数据库的副本
3.文件组
概念:
文件组是将多个数据库文件集合起来形成的一个整体。
分类:
主文件组(PrimaryFileGroup):
包含了所有的系统表,建立数据库时,其包含主数据库文件和未指定组的其他文件。
次文件组(SecondaryFileGroup):
可指定一个默认文件组,在创建数据库对象时,可用于将对象放置在默认文件组中;
说明:
一个文件只能存放在一个文件组中,一个文件组也只能被一个数据库所使用;
若未指定默认文件组,则主文件组为默认文件组;
事务日志文件不属于任何文件组,数据文件和日志文件总是分开存放的。
5.1.2事务日志
1.创建事务日志:
即为事务日志指定其在数据库中的存储文件,数据文件和日志文件是分开存放的,分开存放的好处有:
事务日志可以单独地备份;
有可能从服务器失效的事件中将服务器恢复到最近的状态;
事务日志不会抢占数据库的空间;
可以很容易地监测事务日志的空间;
在向数据文件和事务日志文件写入数据时会产生较少的冲突,有利于提高SQL性能。
2.事务日志的工作过程
SQLServer中的事务日志用来记录数据的更新情况,这些数据的更新是基于事务的。
事务是一次完成的操作的集合,主要用于维护数据的完整性,即在对数据库中的数据进行修改时,事务必须彻底被执行或根本不执行;若一个事务只是部分地执行,则会使数据库中的数据处于不一致状态。
SQL使用数据库的事务日志来防止没有完成的事务破坏数据。
应用程序发出一个修改数据库中对象的事务。
事务开始时,先在事务日志中记录一个事务开始标志,并将被影响的数据页从磁盘载入内存中;
事务处理过程中的任何数据更改都被记入事务日志,并保存在硬盘上;
数据修改是在内存中进行的,当事务处理完毕时,所有修改过的数据页会写入数据库中,并在事务日志中记录一个结束标志。
事务日志中开始和最后的记录就是SQLserver划定一个事务处理开始和完毕的标志。
注:
数据库数据会不断增大,因此事务日志文件也会不断增大,为了节省空间,因定期为事务日志文件做备份,备份时,事务日志文件会被删除(截断)。
5.2估算数据库的的空间
5.3数据库的创建
创建数据库的三种方法:
使用企业管理器创建;
使用数据库向导创建;
使用T-SQL语句创建;
注意事项:
-SQL语言创建时要指定逻辑文件名(是在SQLServer使用的名称,是数据库在SQLSERVER中的标识)和操作系统文件名(在操作系统中存储时使用的文件名)。
数据文件逻辑名后应加上_data后缀,事务日志文件逻辑名后加入_log后缀,以区别这两类文件;
缺省情况下,只有系统管理员可以创建新数据库,但系统管理员可赋予其他用户创建数据库的权限;
所以的新数据库都是以Model数据库为模板创建的,其大小可以被增大或收缩。
创建时文件名要遵循SQLServer命名规范,一台服务器最多可创建32767个数据库;
数据库信息保存在‘master’数据库的‘sysdatabases’系统表中
5.3.1使用企业管理器创建、删除数据库
(一)创建
例:
创建一个JWGL数据库,保存路径为D:
\,数据文件名为jwg1_data.mdf,为主文件,初始大小为2MB,最大值20兆;逻辑文件名为jwgl_log.ldf,初始容量为2mb,最大值为10MB,均以1MB增量增加。
1.启动“sqlserver服务”,并启动“企业管理器”
2.展开要创建数据库的服务器,切换到数据库属性窗口的三种方法:
单击‘数据库’目录,选择“操作”菜单中的“新建数据库”命令;
右击“数据库”,在快捷菜单中选择“新建数据库”命令;
在工具栏上单击“新建”按钮或者“新数据库”按钮
3.在“名称”文本框中输入要创建的数据库名称:
JWGL
;
4.单击“数据库文件”标签,指定该数据库的文件名、存放路径以及初始容量。
系统根据“名称”框指定的数据库名称“JWGL”,为数据库文件设置默认值:
主文件名:
为“JWGL_Data.mdf”,
存储路径:
为安装目录下的Data子目录,此例中双击“…”改变路径为“D:
\”;
初始容量:
为1MB,此例中改为2MB;
文件组:
默认为primary,表示此数据文件为主文件,只有一个。
(2)若所创建的数据库需用两个文件来存储,则在“数据库文件”列表框的第二行指定另一个文件的文件名。
但要注意的是,当一个数据库对应于多个文件时,第一行中的文件为主要文件,其扩展名为.Mdf,其他行中的文件都只能是非主要文件,其扩展名为.Ndf。
(3)如果希望数据库文件的容量按照实际需要自动增加,选中“文件自动增长”复选框,并选择下列增加方式之一:
Ø欲使数据库文件的容量以MB为单位自动增加,选择“按兆字节”单选按钮,并指定每次增加多少MB,此例指定1MB;
Ø欲使数据库文件的容量以某个百分比为单位自动增加,选择“按百分比”单选按钮,并指定每次增加百分之几
(4)设置数据库文件容量的增加是否有上限:
Ø欲使数据库文件容量的增加没有上限,选择“文件增长不受限制”选项按钮;
Ø欲使数据库文件容量的增加有一个上限(即最大值),选择“将文件增长限制为(MB)”选项按钮,并指定一个上限值,此例为20MB。
5.单击“事务日志”标签,对事务日志进行设置,定义方法同上,该例中的设置为:
文件名:
jwgl_log.ldf、存储位置:
D:
\、初始容量:
2MB、文件自动按兆字节增长,大小为1MB、最大文件大小:
按文件增长限制为10MB。
注:
事务日志不属于任何文件组。
6.单击“确定”按钮,完成数据库的创建。
(二)使用企业管理器删除数据库。
右键单击要删除数据库,在弹出菜单中单击删除命令。
例:
使用企业管理器创建书第79页例5-1和例5-2
5.3.2使用向导创建数据库
步骤如下:
启动企业管理器,单击“工具”/“向导”,或单击“控制台目录”工具栏中的“向导”,弹出“选择向导”窗口
单击“数据库”左边的“+”,展开数据库,选择“数据库向导”,出现欢迎窗口;
单击“下一步”,根据向导提示,完成数据库的创建过程。
5.3.3使用T-SQL语言创建数据库和事务日志
使用T-SQL语言创建数据库和事务日志前,需要打开查询分析器,打开时输入“服务器名”、身份验证方式、账号及口令;
1.语法格式:
CreateDatabase数据库文件名
[ON
{[PRIMARY](NAME=逻辑文件名,
FILENAME=‘操作系统下的文件名和路径’
[,SIZE=文件初始容量]
[,MAXSIZE={文件最大容量|UNLIMITED}]
[,FILEGROWTH=递增容量])
}[,…n]
]
[LOGON
{(NAME=逻辑文件名,
FILENAME=‘操作系统下的文件名和路径’
[,SIZE=文件初始容量]
[,MAXSIZE={文件最大容量|UNLIMITED}]
[,FILEGROWTH=递增容量])
}[,…n]
]
[FORLOAD|FORATTACH]
格式说明:
ON子句指定数据库数据文件的磁盘文件清单;n表明可指定多个文件;
Primary:
指定主文件组的文件,Createdatabase语句中列出的第一个数据文件为主文件。
LOGON子句指定存储数据库事务日志文件的磁盘表单;
SIZE:
指定数据库的初始容量大小,单位可为:
KB、MB、GB和TB,省略主文件SIZE则和模板数据库中的主文件大小一致,且不能小于模板数据库中的主文件大小。
省略其它数据库文件和事务日志文件SIZE则默认为1MB,其最小值为可设为512KB,不能用小数。
MAXSIZE:
指定文件最大容量,未指定及指定UNLIMITED则文件不断增长直到磁盘变满为止
FILEGrowth:
用于指定文件的增量,可使用MB、KB和百分比指定单位,为零时不增长,其设置不能超过SIZE的大小,省略时默认为10%.
2.几点说明:
创建用户数据库后,应及时备份数据库;
所有数据库都至少包含一个主文件组和用户定义的文件组。
创建数据库的用户(dbo:
databaseowner)是数据库的所有者,可通过系统存储过程sp_changedbowner改变数据库所有者。
创建数据库的权限默认地授予sysadmin和dbcreator固定服务器角色的成员。
例1、用CreateDatabase语句创建一个数据库,名称为“SPXX”,包含一个数据文件和一个事务日志文件。
数据文件为主要文件,其逻辑文件名为“SPXX_data”,实际文件名为“spxx_data.Mdf”,初始容量为10MB,最大容量为50MB,自动增长时的递增量为2MB。
事务日志文件的逻辑文件名为“spxx_log”,实际文件名为“spxx.Ldf”,初始容量为5MB,最大容量为25MB,自动增长时的递增量为1MB。
CreateDatabaseSPXX
ONPRIMARY--定义主数据文件
(NAME=spxx_data,--逻辑名称
FILENAME='d:
\sjk\spxx_data.Mdf',--操作系统文件路径及名称
SIZE=10MB,--数据文件的初始容量为10MB
MAXSIZE=50MB,--数据文件的最大容量为50MB
FILEGROWTH=2MB)--数据文件的增长容量为2MB
LOGON--定义事务日志文件
(NAME=spxx_log,--事务日志的逻辑名称
FILENAME='d:
\sjk\spxx_log.Ldf',--事务日志所在的操作系统文件路径及名称
SIZE=5MB,--事务日志的初始容量
MAXSIZE=25MB,--事务日志的最大容量
FILEGROWTH=1MB)--事务日志的增长容量
书第79页
上机:
1、用CreateDatabase语句创建一个数据库,名称为“Arch”,该库包含三个数据文件和二个事务日志文件。
主要数据文件的逻辑文件名为“Arch1”,实际文件名为“Archdat1.Mdf”;两个非主要文件的逻辑文件名分别为“Arch2”和“Arch3”,实际文件名分别为“Archdat2.Ndf”和“Archdat3.Ndf”。
两个事务日志文件的逻辑文件名分别为“Archlog1”和“Archlog2”,实际文件名分别为“Archlog1.Ldf”和“Archlog2.Ldf”。
上述文件的初始容量均为1MB,最大容量为10MB,递增量均为1MB。
2、创建名字为mydb的数据库,它有尺寸分别是10MB、8MB、6MB的3个数据文件,其中mydb_data1.mdf是主文件,使用PRIMARY关键字显示指定。
Mydb_data2.ndf、mydb_data3.ndf是次文件。
数据库有两个尺寸分别是7MB、9MB的事务日志文件的量大尺寸均是20MB,文件增量均为2MB。
5.3.5由已有库生成创建数据库的脚本
可以利用已有的数据库生成创建该数据库的脚本程序、编写数据库用户和数据库角色的脚本及SQLServer登录脚本等,具体步骤如下:
右键单击需生成脚本的数据库,选择‘所有任务’
选择‘生成SQL脚本’,打开‘生成SQL脚本’对话框
点击‘选项’标签,选择‘编写数据脚本’和‘编写数据库用户和数据库角色脚本’,单击‘确定’,输入脚本文件名后,即可生成脚本信息。
创建数据库时,可直接调用该脚本文件创建数据库。
5.4数据库的管理
5.4.1查看数据库属性
1.用企业管理器查看
右键单击要查看的数据库,在快捷菜单上单击‘属性’即可打开属性窗口查看相关信息。
2.用T-SQL语言查看
打开查询分析器
查看某个数据库或所有数据库的信息:
EXECsp_helpdb数据库名
查看有关数据库中所占用空间的报表:
use数据库名
EXECsp_spaceused
查看有关数据库中文件组的报表:
先打开相关数据库,然后输入:
EXECsp_helpfilegroup
查看数据库中文件的报表:
先打开数据库,然后输入:
EXECsp_helpfile
5.4.2修改数据库大小
建立一个数据库之后,可以根据需要对该数据库的结构进行修改,例如增加或删除数据文件或文件组,更改文件或文件组的属性等。
1.用企业管理器修改
右键单击要查看的数据库,在快捷菜单上单击‘属性’打开属性窗口,根据需要进入不同的选项卡修改数据库信息
2.用T-SQL语言修改
ALTERDatabase数据库名
{AddFile<文件格式>[,…n][TOFilegroup文件组名]/*指定要增加数据库文件到哪个文件组*/
|AddLog<文件格式>[,…n]/*指定要增加的事务日志文件*/
|RemoveFILE逻辑文件名/*从数据库系统表中删除指定空文件的定义,并且删除其物理文件*/
|AddFilegroup文件组名/*指定要增加的文件组*/
|RemoveFilegroup文件组名/*从数据库中删除指定空文件组的定义,并且删除其包括的所有数据库文件*/
|ModifyFile<文件格式>/*修改指定文件的文件名、容量大小、文件增容方式等属性,使用Size时,新的容量必须大于当前容量。
新文件名在SQLServer重新启动时才会生效。
*/
|ModifyFilegroup文件组名文件组属性/*指定将文件组属性应用于该文件组。
文件组属性可以是:
ReadOnly—指定文件组为只读(主要文件组不能设置为只读);
ReadWrite—取消只读属性;
Default—指定文件组为数据库中的默认文件组。
在一个数据库中,只有一个默认文件组。
*/
}
其中<文件格式>为:
(NAME=逻辑文件名
[,FILENAME=‘实际文件名和路径’]
[,SIZE=容量]
[,MAXSIZE={最大容量|UNLIMITED]
[,FILEGROWTH=递增量])
例1:
书第85页;
例2:
将JWGL数据库的数据文件JWGL_data由原来的2MB扩充为5MB,事务日志文件JWGL_log的大小由原来的2MB扩充为6MB。
例3:
为JWGL数据库增加一个4MB的次要数据文件JWGL2,次要文件后缀为.ndf
例4:
为JWGL数据库增加一个5MB容量的日志文件jwgllog2,并删除刚增加的次要文件JWGL2.ndf。
5.4.3数据库的收缩
在数据库属性对话框中可以重新设置数据库文件的容量,但在这里只能扩大不能缩小。
若要通过删除未用空间来缩小数据库文件的容量,必须要做如下如置:
1.用企业管理器收缩
(1)启动企业管理器,并在“树”窗格中单击“数据库”节点,使SQLServer所管理的数据库显示在右边的内容窗格中。
(2)右击待缩减数据库的图标,然后从弹出式菜单中选择“所有任务/收缩数据库”命令。
(3)在“收缩数据库”对话框中,显示数据库已分配的空间和可用空间的大小。
选中“在收缩前将页移到文件起始位置”复选框:
则使释放的文件空间保留在数据库文件中,并使包含数据的页移到数据库文件的起始位置;
如果希望缩减数据库,选中“根据本调度来收缩数据库”复选框。
还可单击“更改”按钮来设置具体的时间表。
单击“确定”,对数据库执行缩减操作。
2.用T-SQL语言收缩
(1)格式:
DBCCSHRINKDATABASE(数据库名[,收缩后的剩余空间百分比][,NOTRUNCATE|TRUNCATEONLY])
(2)说明:
NOTRUNCATE|TRUNCATEONLY:
为被释放空间的处理方法。
NOTRUNCATE:
命名释放的文件空间保留在数据库文件中;
TRUNCATEONLY:
将数据库文件中的任何未使用的空间释放给操作系统。
设置数据库为自动收缩:
格式:
ALTERDATABASE数据库名
SETAUTO_SHRINKON/OFF
说明:
ON:
设为自动收缩;OFF:
不设为自动收缩
5.4.4数据库的更名
若数据库被设为默认数据库,刚不允许改名。
格式:
SP_RENAMEDB‘旧数据库名’,’新数据库名’
5.4.5数据库的删除
格式:
DROPDATABASE数据库名
说明:
正在使用的数据库不能删除;
使用DROPDATABASE命令一次可删除多个,而企业管理器一次只能删除一个数据库;
4个系统数据库master、model、tempdb、msdb不能删除
删除前应备份数据库。
5.4.6分离数据库
SQLServer服务器在运行时,会维护其中所有数据库的信息。
如果一些数据库暂时不使用,则可将其从服务器分离,从而减轻服务器的负担。
1.在企业管理器中分离数据库
(1)使用鼠标右键单击要分离的数据库,在弹出的快捷菜单中选择‘所有任务’/‘分离数据库’菜单命令,或
单击选中要分离的数据库,再选择‘操作’/‘所有任务’/‘分离数据库’菜单命令,打开“分离数据库”对话框
(2)对话框显示了当前数据库状态,包括当前数据库的连接、是否要复制数据库以及当前状态。
如果当前数
据库的连接不为0,表示此数据库正在使用,此时可单击‘清除’按钮结束数据库事务以分离数据库。
如果数据库的状态显示“该数据库以就绪,可以分离”,则单击‘确定’按钮分离数据库。
对话框中的‘分离前更新统计信息’复选框用于选择在分离数据库前,是否更新数据库的统计信息。
(3)若单击‘清除’按钮,则系统会弹出‘确认’结束事务对话框,单击‘确定’后,则弹出对话框,用于选择是否通知数据库的当前用户,告诉他们在数据库中的操作将结束。
注:
在分离数据库后,如果修改了数据库的数据文件名,将导致该数据库不能重新附加到服务器。
如果出现这种问题,可将文件名修改为原来的名称,即可将附加到服务器。
2.用T-SQL语言分离数据库
格式:
EXECsp_detach_db数据库名
例:
EXECsp_detach_dbjwgl
5.4.7附加数据库
附加数据库是指将分离的数据库重新添加到服务器中,其具体操作如下。
1.用企业管理器附加数据库
(1)右键单击企业管理器目录树窗口中的“数据库”项目,在弹出的快捷菜单中选择‘所有任务’/‘附加数据库’命令;
(2)在弹出对话框的“要附加的数据库的MDF文件”文本框中输入已分离数据库的数据文件的完整路径和文件名,也可单击‘浏览’按钮,打开“浏览文件”对话框选择文件;
(3)单击‘验证’按钮,检验数据库的数据文件和事务日志文件是否正确,若数据文件错误则不能附加该数据库,若事务日志文件错误则可附加该数据库,并为该数据库创建一个新的事务日志文件;
(4)在文件通过验证后,在“附加为”文本框中显示了要附加的数据库的默认名称(与数据文件的文件名相同),可将其修改为其他名称。
(5)在“指定数据库所有者”下拉列表中指定该数据库的所有者;
(6)单击‘确定’执行附加数据库操
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL SERVER数据库管理系统 SQL SERVER数据库管理系统课程教案 SQL SERVER 数据库 管理 系统 课程 教案
![提示](https://static.bdocx.com/images/bang_tan.gif)