项目2 SQL Server 数据库操作.docx
- 文档编号:9329952
- 上传时间:2023-05-18
- 格式:DOCX
- 页数:30
- 大小:1.29MB
项目2 SQL Server 数据库操作.docx
《项目2 SQL Server 数据库操作.docx》由会员分享,可在线阅读,更多相关《项目2 SQL Server 数据库操作.docx(30页珍藏版)》请在冰点文库上搜索。
项目2SQLServer数据库操作
项目2SQLServer2005数据库操作
目标规划:
(一)知识目标
Ø了解数据库的组成对象
Ø掌握创建数据库的两种方法
Ø熟悉基本的数据库管理操作
Ø学会如何附加与分离数据库
(二)能力目标
Ø掌握数据库的文件结构
Ø掌握使用T-SQL语句和SQLServerManagementstudio对象资源管理器创建、删除数据库和事务日志
Ø能够对数据库进行配置和管理
Ø掌握数据库的附加和分离操作
内容结构:
学习情境2.1数据库组成
●教学导航
本节首先讲述了数据库的组成,随后介绍了数据库文件的类型,事务的概念、文件组的作用,最后简单的介绍了四个系统数据库和几个相关示例数据库。
●课堂讲解
2.1.1数据库组成
SQLServer2005中数据库是数据库对象和数据的容器,这里的数据库对象包括表,视图,存储过程以及函数等等(如图2-1-2所示)。
每个数据库对象都有一个唯一标识,包括服务器名、数据库名、所有者名和对象名,分别表示成:
Server、Database、Owner、Object。
当服务器名、数据库名、所有者名取当前工作环境的默认值时,均可省略。
在SQLServer2005中数据库主要由文件和文件组组成,SQLServer的文件结构如图2-1-1所示。
图2-1-1数据库组成结构图
一个数据库至少有一个数据文件(.mdf)和一个事务日志文件(.ldf),当然一个数据库也可以多个数据文件和多个日志文件,数据文件用于存放数据库的数据和各种对象,而事务日志文件用于存放事务日志,一个数据库最多可以拥有32767个数据文件和32767个日志文件。
◆数据文件
数据文件可以分成主数据文件和次数据文件两种数据形式,主数据文件是数据库的起点,每一个数据库都有且只有一个主数据文件,其默认后缀是.mdf。
次数据文件是可选的,可以存储不在主数据文件的全部数据和对象。
数据库既可能没有次数据文件,也可能有多个次数据文件,次数据文件的默认后缀是.ndf。
图2-1-2数据库的组成
◆事务日志文件
每一个数据库都允许用户在创建数据文件的同时创建其事务日志文件,事务日志文件用于记录用户对数据库的操作情况,用户对数据库的操作都将被记录在事务日志文件内,如对表执行insert、delete、update等命令都将被记录在事务日志文件内,需要特别指出的是如果select查询命令不影响数据库的内容,该操作将不会被记录在事务日志内。
一个数据库中可以有一个或多个事务日志文件,其扩展名是“.ldf”。
其主要作用是在数据库损坏时用于恢复数据库的内容。
◆数据库文件组
为了便于数据文件的管理和数据文件的存储分配,SQLServer2005还提供了文件组的概念,即将有关联的几个数据文件集中成一个文件组,例如在某个数据库中,两个文件(data1.mdf、data2.mdf)分别创建在2个不同的磁盘上,然后为他们创建一个文件组group,以后在创建表时可以明确指定放在文件组group中,对该表中的数据的查询将分布在这2个磁盘上,因此可以通过执行并行访问而提高查询性能。
每个数据库都有一个默认的文件组在运行,数据库可以指定文件组,没有指定则默认是主文件组。
使用文件和文件组时应考虑以下原则:
(1)一个文件或文件组只能用于一个数据库,不能用于多个数据库。
(2)一个文件只能是一个文件组的成员,不能是多个文件组的成员。
(3)只有数据文件才能归属于某个文件组,日志文件永远也不能是任何文件组的一部分。
(4)数据库的数据信息和日志信息不能放在同一个文件或文件组中,数据文件和日志文件要分开存放。
3.1.2系统数据库和示例数据库
每个SQLServer都包含两种类型的数据库:
系统数据库和用户数据库。
系统数据库存储有关SQLServer的信息,SQLServer使用系统数据库来操作和管理数据库。
在安装SQLServer时,系统自动安装master,model,msdb,tempdb这4个系统数据库,并可安装AdventureWorks,AdventureWorksDW两个示例数据库,如图2-1-3所示。
用户数据库由用户来建立,SQLServer可以包含一个或多个用户数据库。
2-1-3系统数据库
在SQLServerManagementStudio中,每个实例的数据库节点都有一个系统数据库的子节点,每一个实例都拥有四个系统数据库用来存储基本信息,这些数据库既不能修改,也不能删除。
1.master数据库
master数据库记录了SQLServer系统的所有系统级别的信息,如果在计算机上安装了一个SQLServer系统,系统会建立一个master数据库来记录系统的有关登录账号,系统配置,数据库文件等初始化信息。
系统正是根据master数据库中的信息来管理系统和其他数据库的,因此如果master数据库不可用,用户数据库将不能被使用。
2.model数据库
model数据库作为在SQLServer实例上创建的所有数据库的模板数据库,必须存在于SQLServer系统中,其包含属于每个用户数据库的系统表,每当建立一个新的数据库时,即建立了model的一个副本。
3.msdb数据库
msdb数据库提供SQLServer代理程序在调度警报作业以及记录操作时使用,用于监控譬如数据不一致此类的非法操作等现象,并及时向系统管理员发出警报。
4.tempdb数据库
tempdb数据库是连接到SQLServer实例的所有用户的可用的全局资源,保存所有的临时表和临时存储过程及其他的临时存储内容。
该数据库在SQLServer每次启动时都重建连接,因此该数据库启动时总是空的。
5.示例数据库
SQLServer提供了多个示例数据库,如上文中提到的AdventureWorks,AdventureWorksDW数据库,此外还有pubs数据库(记录了一个虚构的出版公司的数据信息)和Northwind数据库(保存了一个虚构的贸易公司的数据信息)等等,可供学习SQLServer使用。
默认情况下,系统不安装AdventureWorks,AdventureWorksDW数据库,也不提供pubs和Northwind数据库。
如果需要,可以到Microsoft下载中心下载所需的示例数据库。
●课堂实践
进入SQLServerManagementStudio,自己动手打开对象资源管理器,观察数据库的组成结构,逐一了解四个系统数据库。
学习情境2.2创建数据库和事务日志
●教学导航
本节首先介绍了如何使用ManagementStudio创建数据库和事务日志,随后介绍了如何使用T-SQL创建数据库,最后介绍了使用系统存储过程查看数据库的信息的方法。
●课堂讲解
2.2.1创建数据库和事务日志
数据库的创建者必须是系统管理员,或者是被授权使用CreateDatabase语句的用户,在创建数据库之前要注意以下几点:
(1)创建该数据库的用户将成为该数据库的拥有者。
(2)数据库名要遵守SQLServer2005的标志符命名规则。
(3)在一个SQLServer服务器上,最多可以创建32767个数据库。
2.2.1.1使用ManagementStudio创建数据库和事务日志
【例2.1】创建一个名字为SC1的数据库,将数据库文件保存在C盘中,主数据文件名为SC1.mdf,大小为5MB;事务日志文件名为SC1_log.LDF,大小为2MB。
【解答】该题要求使用管理台控制程序创建一个数据库并放在在C盘中。
要创建的数据库并没有次数据文件。
未指定数据库的拥有者,则默认所有者为sa(SQLServer的系统管理员)。
使用ManagementStudio创建数据库和事务日志的具体步骤以下:
(1)在“对象资源管理器中”,右击“数据库”在弹出的快捷菜单中选择“新建数据库”选项,如图2-2-1所示。
图2-2-1新建数据库1
(2)在“数据库名称”一栏中输入数据库的名字“SC”,系统默认主数据文件名为“SC1.mdf”,日志文件名为“SC1.log.ldf”;如图2-2-2所示。
单击初始大小一栏将主数据文件默认大小3改为5,日志文件默认大小由1改为2。
单击数据库文件中的第一行的路径列的按钮
(注意图中光标位置),可以修改数据文件存放的位置。
图2-2-2新建数据库2
(3)将数据文件存放的位置修改为“C:
\”。
(4)同样的方法将日志文件存放的位置修改为“C:
\”。
(5)单击“确定”按钮,完成选课数据库SC1的创建。
【说明】在创建数据库时,系统自动将model数据库中的所有用户定义的对象都复制到新建的数据库中,用户可以在model系统数据库中创建希望自动添加到所有新建数据库的对象,例如表、视图以及存储过程等。
2.2.1.2使用T-SQL创建数据库
使用T-SQL创建数据库的与语句格式如下:
CREATEDATABASE数据库名/*创建数据库,数据库名为要创建的数据库名字*/
[ON/*定义用来存储数据库数据部分的磁盘文件(数据文件)*/
{[PRIMARY](NAME=logical_file_name,/*主数据文件的逻辑文件名*/
FILENAME=on_file_name /*数据库物理文件名要用半角状态的单引号括起来,给出路径*/
[,SIZE=size]/*主数据文件的容量*/
[,MAXSIZE=max_size]/*主数据文件的容量的上限*/
[,FILEGROWTH=growth_increment])/*主数据文件的增长量*/
}[,…,n]
]
[LOGON/*定义用来存储数据库日志的磁盘文件(日志文件)*/
{(NAME='log_file_name',/*日志文件的逻辑文件名*/
FILENAME=os_file_name /*日志文件的物理文件名,要用半角状态的单引号括起来,给出路径*/
[,SIZE=size])/*日志文件的容量*/
}[,…,n]
}
其中:
(1)数据库名:
要建立的数据库名称。
数据库名称在服务器中必须唯一并且符合标识符命名规则。
(2)PRIMARY:
指定为主数据库文件。
一个数据库只能有一个主文件。
如果没有使用PRIMARY关键字,默认列在语句中的第一个文件就是主文件。
(3)NAME:
指定数据或日志文件的文件名称。
(4)FILENAME:
指定文件的操作系统文件名和路径。
on_file_name中的路径必须指定为SQLServer所安装服务器上的某个文件夹。
(5)SIZE:
指定数据或日志文件的大小。
用户可以以MB为单位指定大小,也可以使用单位KB来指定大小。
数据库文件不能小于1MB。
在默认情况下数据文件的大小是3MB,事务日志的大小是1MB。
(6)MAXSIZE:
指定文件能够增长的最大长度(即最大容量)。
指定值的默认单位为MB,用户也可以使用KB来指定大小。
如果没有指定长度,文件将一直增长,直到磁盘满为止。
(7)FILEGROWTH:
指定文件的增长量,该选项指定的数据值为零时,表示文件不能增长。
文件增长可以指定MB、KB或百分比。
【例2.2】使用CREATEDATABASE命令创建名字为NEWSC的数据库,数据文件和日志文件存储在C盘下。
【解答】通过观察我们可以看到数据库的逻辑文件名“logical_file_name”为NEWSC,它是主数据文件,其物理文件名“on_file_name”为“C:
\NEWSC.mdf”;日志文件的逻辑文件名“logical_file_name”为“NEWSC_log”其物理文件名为“on_file_name”为“C:
\NEWSC_log.mdf”;在写语句时,要注意有无逗号。
在查询窗口中执行如下SQL语句:
CREATEDATABASENEWSC
ON
(NAME=NEWSC,
FILENAME='C:
\NEWSC.mdf')
LOGON
(NAME=NEWSC_log,
FILENAME='C:
\NEWSC_log.mdf')
GO
【注意】:
在CREATEDATABASE命令中没有指定主数据文件时,SQLServer则默认第一数据文件new.mdf为主数据文件。
【例2.3】创建一个名为MYSC数据库,其主文件MYSC1.mdf大小为30MB,初始文件大小为10MB,文件大小增长率为10%;次文件MYSC2.ndf大小为10MB,初始文件大小为5MB,文件大小增长率为10%;日志文件MYSC_log.ldf大小为5MB,初始文件大小为2MB,文件增长量为1MB,其中文件都存储于C:
\目录下。
【解答】本题要求创建的数据库,一共有三个文件,需要使用PRIMARY来指定主数据文件,SIZE分别是30、10、5,然后使用MAXSIZE和FILEGROWTH来指定文件最大长度和文件增长量。
在查询窗口中执行如下SQL语句:
CREATEDATABASEMYSC
ON
PRIMARY(NAME=MYSC1,
FILENAME='C:
\MYSC1.mdf',
SIZE=10,
MAXSIZE=30,
FILEGROWTH=10%),/*注意结尾处的逗号*/
(NAME=MYSC2,
FILENAME='C:
\MYSC2.ndf',
SIZE=5,
MAXSIZE=10,
FILEGROWTH=10%)/*注意结尾处无逗号*/
LOGON
(NAME=MYSC_log,
FILENAME='C:
\MYSC_log.ldf',
SIZE=2,
MAXSIZE=5,
FILEGROWTH=1)/*注意结尾无逗号*/
GO
2.2.2查看数据库的信息
使用sp_helpdb存储过程可以查看所有数据库或指定数据库的所有信息,包括数据库的名称、容量、所有者、创建时间等等。
如执行下面的代码即可看见SC数据库的信息:
sp_helpdbSC
go
查询结果如图2-2-4所示:
图3-2-4SC数据库信息
●课堂实践
使用上文中介绍的两种方法,在SQLServer2005中,创建一个数据库(数据库名不限),并放在D:
\下,创建成功后,使用sp_helpdb查看该数据库的信息。
学习情境2.3管理数据库
●教学导航
本节将要介绍数据库的相关管理搞作,其中包括如何打开数据库、如何修改数据库名、如何配置数据库,如何扩大和缩小数据库以及如何删除数据库。
●课堂讲解
2.3.1打开数据库
当用户登录SQLServer服务器并连接SQLServer后,需要连接SQLServer服务器中的一个数据库才能使用该数据库中的数据,如果用户没有预先指定连接那个数据库,SQLServer会自动替用户连接上master系统数据库。
可以用User命令来打开或切换不同的数据库。
User命令格式:
UseDatabase_Name
Database_Name:
需要打开或切换的数据库名称
2.3.2修改数据库名
在我们平常使用数据库的过程中,往往需要对数据库进行更名操作,需要注意的是,在修改数据库名之前,应该确认其他用户已断开与数据库的连接,并且需要将数据库配置为单任务模式。
新的数据库名必须遵守标志符的定义规则,并在数据库中不存在。
下面分别用SQLServerManagementstudio和T-SQL语句来修改数据库的名称。
1.使用用SQLServerManagementstudio更名
【例2.3】将SC数据库更名为StudentClass。
【解答】
(1)在SQLServerManagementstudio中选中要修改的数据库“SC”,然后右击鼠标,在弹出的快捷菜单中选择“重命名”选项。
如图2-3-1所示。
图2-3-1重命名数据库
(2)在数据库选项中,输入StudentClass,按下“确定”按钮即可。
2.使用Sp-Renamedb存储过程实现更名
格式:
ExceSp-Renamedb'原数据库名''新数据库名'
【例2.4】使用Sp-Renamedb命令,将StudentClass数据库重新命名为SC.
【解答】在查询窗口中执行如下T-SQL语句:
ExceSp-Renamedb'StudentClass','SC'
2.3.3配置数据库
数据库被创建以后,我们可以根据需要使用SQLServerManagementstudio和T-SQL语句重新配置一些数据库的选项,如将数据库配置为单用户模式,将数据库设置为只读或可写等等。
【例2.5】在SQLServerManagementstudio中将数据库SC置成单用户模式。
【解答】
(1)在SQLServerManagementstudio中选中要修改的数据库“SC”,然后右击鼠标,在弹出的快捷菜单中选中“属性”选项。
如图2-3-2所示。
图2-3-2数据库属性
(2)在属性对话框中,选择左上角选择页中的“选项”,在“选项”的状态栏中,选择“限制访问”行,单击该行的下拉按钮,选中Single项,单击右下角的确定按钮,在弹出的提示对话框中选择“是”,完成操作。
如图2-3-3所示。
图2-3-3设置数据库为单用户模式
【例2.5】使用T-SQL语句将SC配置为只读。
【解答】在查询窗口中,执行如下T-SQL语句:
sp_dboption'SC','readonly','TRUE'
Go
执行结果如图3-3-4所示。
图2-3-3配置数据库为只读模式
2.3.4扩大数据库
在SQLServer中,如果出现数据库或事务日志文件的容量不够时,可以根据需要扩充数据库或事务日志文件的容量。
有3种扩大数据库的方式。
第一种是设置数据库为自动增长方式,可以在创建数据库时设置;第二种方式是直接修改数据库的数据文件或日志文件的大小;第三种方式是在数据库中增加性的次要数据文件和日志文件;下面分别用两个例子来介绍一下后面两种方式;
【2.6】将SC数据库的数据文件SC.mdf的容量扩充为20MB,事务日志文件SC_log,ldf的容量扩充为15MB。
【解答】在查询窗口中执行如下T-SQL语句:
USEMASTER
GO
ALTERDATABASESC
MODIFYFILE(NAME=SC,/*给出要修改的数据库逻辑文件名*/
SIZE=20)
GO
ALTERDATABASESC
MODIFYFILE(NAME=SC_log, /*修改后的文件容量,必须大于原来的容量*/
SIZE=15MB)
GO
读者有兴趣的话可以使用sp_helpdbSC显示数据库SC的信息,如图2-3-4所示。
图2-3-4修改后的SC数据库信息
【例2.7】为SC数据库增加一个5MB的次数据文件SC2。
USEMASTER
GO
ALTERDATABASESC
ADDFILE
(NAME=SC2,
FILENAME='C:
\SC2.ndf',/*次数据文件的后缀名为.ndf*/
SIZE=5MB,/*此数据文件的初始容量*/
MAXSIZE=10MB,/*次数据文件的最大容量*/
FILEGROWTH=10%)/*文件增长量*/
GO
执行完以后我们使用sp_helpdbSC显示数据库信息,如图2-3-5所示,读者可以和图2-3-4进行比较,可以看到在数据库中增加了一个容量为10Mb的次数据文件。
图2-3-5增加了次数据文件的SC数据库信息
【例3.8】为SC数据库增加一个5MB的日志文件SC_log2。
【解答】在查询窗口中执行如下T-SQL语句:
ALTERDATABASESC
ADDLOGFILE
(
NAME=SC_log2,
FILENAME='C:
\SC_log2.ldf',
SIZE=5MB,
MAXSIZE=10MB,
FILEGROWTH=1MB)
GO
执行完以后,使用sp_helpdbSC显示数据库信息,如图2-3-6所示,可以看到,数据库增加了一个容量为5Mb的日志文件。
图2-3-6增加了新日志文件的SC数据库信息
2.3.5缩小数据库
当为数据库指派的存储空间过大时,可以通过缩减数据库容量来减少设备空间的浪费。
缩减数据库容量的方法有两种,即通过SQLServerManagementstudio和或T-SQL语句来实现。
但需要注意的是,不能将数据库缩小为小于model数据库的容量。
1.在SQLServerManagementstudio中缩减数据库容量
【例2.9】使用SQLServerManagementstudio缩减SC数据库。
【解答】
(1)进入“对象资源管理器”窗口,展开“据库”右击要修改的数据库“SC”,在弹出的菜单中选择“任务”——“收缩”——“数据库”选项,如图2-3-7所示。
图2-3-7收缩SC数据库1
(2)单击“确定”按钮,系统将自动收缩数据库到合适的大小,如图2-3-8所示。
图2-3-8收缩SC数据库2
2.使用T-SQL语句缩减数据库容量
使用DBCCSHRINKDATABASE命令可以缩减数据库容量,其格式如下:
DBCCSHRINKDATABASE(Database_Name[,New_Size])
其中:
Database_Name:
需要缩减的数据库名称。
New_Size:
缩减数据库后剩余多少容量,如果没有指定,那么数据库将缩减至最小容量。
【例2.10】将SC数据库的数据文件SC由10MB缩减为8MB。
【解答】在查询窗口中执行如下T-SQL语句:
UseSC
GO
DBCCSHRINKFILE(SC,8)
GO
执行成功以后,可以通过sp_helpdbSC查看修改后的数据库信息,可以看到数据文件SC.mdf已由10MB缩减为8MB。
如图2-3-9所示。
图2-3-9所示缩减后的数据库信息
2.3.6删除数据库
当不需要某个数据库时,为了节省空间,可以将其从SQLServer中删除,在SQLServer中只有系统管理员和数据库拥有者才有权删除数据库,删除数据库后,相应的数据库文件、日志文件和所有的数据都会被删除,并且不可恢复。
在删除数据库之前要确认是否要先备份数据库的所有数据文件、日志文件,或者在“对象资源管理器”下备份数据库。
【注意】
1.正在被恢复的数据库不能被删除。
2.有用户正在使用的数据库不能被删除。
3.作为SQLServer复制任何表的一部分的数据库不能被删除。
可以通过SQLServerManagementstudio和或T-SQL语句来删除数据库:
1.使用SQLServerManagementstudio删除数据库
【例2.11】使用SQLServerManagementstudio删除数据库“SC“
【解答】
(1)进入“对象资源管理器“窗口,展开“数据库”,右击“SC”,选择删除选项如图2-3-9所示.
图2-3-9删除数据库1
(2)在弹出的“删除对象”窗口中单击“确定”按钮,完成删除操作,如图2-3-10所示。
图2-3-10删除数据库2
【说明】
(1)选中“删除数据库备
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 项目2 SQL Server 数据库操作 项目 数据库 操作