第3章 数据库操作

《数据库基础与应用》

第3章数据库操作本章学习目标掌握SQLServer2000数据库的组成了解SQLServer2000存储结构掌握使用T-SQL语句操纵数据库熟练掌握使用“企业管理器”操纵数据库本章核心知识点SQLServer2000数据库的组成创建、修改数据库和删除数据库的方法本章重点难点SQLServer2000数据管理技术使用T-SQL操纵数据库3.1示例数据库介绍以“网络论坛”数据库作为讲授示例，通过论坛系统中的用户管理、版块管理、发帖管理等对数据库相关概念和SQLServer2000数据管理技术进行解释。在示例“论坛管理系统”中包含一个数据库（bbsDB），内有四个关系表，表结构和记录如表3-1，表3-2，表3-3，表3-4所示。

1．用户信息表用户信息表Users用来保存已经在论坛注册的用户基本信息。各字段分别为：用户ID（主键）、用户呢称、密码、Email、生日、性别、用户级别、用户备注、注册日期、用户状态、用户积分。2．版块表论坛设有版块表Section，每个版块主要针对某个主题并设定版主来进行管理。各字段分别为：版块号（主键）、版块名称、版主编号、版块描述、点击率、帖子数量。3．发帖表论坛设有发帖表Topic，用于保存用户发帖。各字段分别为：发帖编号（主键）、版块号、版主编号、跟帖数、发帖表情、主题、内容、发帖时间、点击数、标志、最后回复时间。4．跟帖表论坛设有跟帖表Reply，用于保存用户对主题帖的回复。各字段分别为：回帖编号（主键）、主帖编号、版块号、用户编号、回帖表情、题目、内容、回帖时间、点击数。3.2SQLServer2000数据库概述3.2.1

任务一熟悉SQLServer数据库组成

1．概述在SQLServer2000中，数据库由包含数据的基本表和其它对象（如视图、索引、存储过程和触发器）组成。SQLServer2000能够支持多个数据库，每个数据库可以存储相关或不相关数据。在创建数据库之前，我们需要理解数据库的各组成部分的功能及其设计方法，这样就可以确保实现数据库的正常运行。2．文件和文件组

SQLServer2000使用一组操作系统文件映射数据库。数据库中的所有数据和对象（如表、存储过程、触发器和视图）都存储在文件中主数据文件次要数据文件事务日志文件（1）主数据库文件主数据库文件包括数据库对象，可用作存储系统表和对象。该文件包含数据库的启动信息，并用于存储数据。每个数据库都有一个主数据文件，主数据库文件的扩展名为.mdf。（2）次数据库文件次数据库文件可用作存储用户数据和对象。这些文件含有不能置于主要数据文件中的所有数据，如果主文件可以包含数据库中的所有数据，那么数据库就不需要次要数据文件。有些数据库可能比较大，所以需要多个次要数据文件，或使用位于不同磁盘驱动器上的辅助文件将数据扩展到多个磁盘。并不是所有的数据库都需要次要数据库文件，次要数据库文件的扩展名为.ndf。一个数据库可以有0个或多个次数据库文件（3）日志文件这些文件包含用于恢复数据库的日志信息。每个数据库都必须至少有一个日志文件，事务日志文件最小为512K，事务日志文件的扩展名为.ldf。一个数据库可有一个或多个日志文件。一个数据库至少应该包括哪些文件？文件组文件组允许对文件进行分组，这样，就可以方便的管理数据的分配／放置。文件组类似于Windows下的文件夹4．系统数据库（1）master数据库master数据库是最重要的一个系统数据库，其中记录所有其它的数据库及相关信息，其中包括数据库文件的位置。同时，master数据库记录了所有SQLServer服务器特定的配置信息，包括授权用户、数据库、系统配置设置及远程服务器等。注意：建议不要在master数据库中创建任何用户对象（如表、视图、存储过程或触发器）。master数据库包含系统表，这些系统表存储SQLServer所用的系统信息（如配置选项设置）。打开master数据库，查询sysdatabases表的信息。（2）tempdb

数据库tempdb

数据库保存所有的临时表和临时存储过程。tempdb

数据库在SQLServer每次启动时都重新创建，因此该数据库在系统启动时总是空的。默认情况下，在SQLServer在运行时tempdb

数据库会根据需要自动增长。不过，与其它数据库不同，每次启动数据库引擎时，它会重置为其初始大小。（3）model数据库model数据库为模型数据库，是系统创建新数据库的模板或原型。当发出CREATEDATABASE语句时，新数据库的第一部分通过复制model数据库中的内容创建，剩余部分由空页填充。由于SQLServer每次启动时都要创建tempdb

数据库，model数据库必须一直存在于SQLServer系统中。注意：每当创建数据库时，model数据库的内容就被拷贝到新的数据库中。（4）msdb

数据库msdb

数据库供SQLServer代理程序调度警报、作业以及记录操作员时使用。msdb数据库包含任务调度、异常处理和报警处理等，msdb

还包含历史记录表，例如备份和还原历史记录表。此外，SQLServer还自带了两个示例数据库Pubs和Notrhwind。数据库Pubs是一个图书出版公司的数据库模型，包含了大量的样本表和样本数据。数据库Northwind是一个贸易公司的数据库模型，包含了与公司经营有关的大多数数据。3.2.2任务二了解SQLServer数据库存储结构

。1．数据库存储结构SQLServer数据库包括数据库文件和日志文件，在创建数据库时，SQLServer会使用特定的数据结构给数据对象分配空间。SQLServer的数据库文件由盘区组成，而盘区由页面组成，盘区与页面、数据库与文件间的关系如图3-1所示。盘区是由8个连续页面组成的数据存储结构，大小为64KB。创建数据库对象时，SQLServer会自动以盘区为单位分配空间，并且每个盘区只能包含一个数据库对象。同时，盘区也是表或索引分配空间的单位，每个表或索引占有一个或多个盘区的空间（64KB）。

SQLServer中所有的信息都存储在页面上，页面是数据库中使用的最小数据存储单元。每一个页面存储8KB（8192字节）的信息。因为所有的页面都包含一个132字节的页面头，页面头用来地唯一标识存储在页面中的数据,因此每个页面存储的信息不超过8060字节。由于SQLServer不允许跨页面存储数据信息，因此，每一数据行的最大值也为8060个字节。2．数据库文件存储位置在缺省情况下，SQLServer用户创建的数据库存储在应用程序文件夹下的MSSQL\Data文件下，如master文件的存储位置为：数据文件：c:\ProgramFiles\MicrosoftSQLServer\MSSQL\Data\master.mdf日志文件：c:\ProgramFiles\MicrosoftSQLServer\MSSQL\Data\mastlog.ldf用户可以在创建数据库时指定数据库对应的物理文件的位置。了解SQLServer2000数据库文件的位置对于SQLServer2000数据库应用程序非常重要。3.3创建和修改数据库3.3.1任务一应用企业管理器创建和修改数据库

1．在“企业管理器”中创建数据库要创建数据库，必须先确定数据库的名称、所有者（创建数据库的用户）、大小，以及用于存储该数据库的文件和文件组。在创建数据库时最好指定文件的最大允许增长的大小，这样做可以防止文件在添加数据时无限制增大，以至用尽整个磁盘空间。要指定文件的最大大小，在使用SQLServer企业管理器内的“属性”对话框创建数据库时，使用CREATEDATABASE语句的MAXSIZE参数或者“将文件增长限制为（MB）”选项。在企业管理器中创建数据库的步骤如下：（1）启动SQLServer2000企业管理器，依次展开“SQLServer组”和“SQLServer注册”。右击“数据库”文件夹，在弹出的快捷菜单上选择“新建数据库”命令；（2）在打开的“数据库属性”对话框中的“名称”文本框中键入新数据库的名称。（3）更改新建主数据库文件的默认值，单击【数据文件】选项卡。在此选项卡中，可以设置数据文件的名称、位置和大小。也可以设置文件的增长方式。（4）若要更改新建事务日志文件的默认值，单击【事务日志】选项卡。在此选项卡上可以设置事务日志文件的名称、位置和大小。（5）单击【确定】按钮，即可创建“论坛数据库”。注意：

数据库大小的最大值由可用磁盘空间量决定，许可限制则由所使用的MicrosoftSQLServer版本决定。可以在创建数据库改变其存储位置，但一旦数据库创建以后，存储位置不能被修改。上机练习：利用企业管理器，创建一个名为“XXX”（自己姓名的拼音首字母）的数据库：要求：1、数据库存储在D:\Data文件夹下；

2、主数据文件命名为“xxx_main.mdf”;3、次数据文件命名为“xxx_ciyao.ndf”；

4、日志文件命名为“xxx_log.ldf”；数据库名称、数据文件名、日志文件名有何关系？通过向导功能建立数据库，其步骤如下：（1）在企业管理器中，选择【工具】，再选择【向导】，即可打开“选择向导”对话框，展开【数据库】文件夹，然后选择【创建数据库向导】；（2）单击【确定】按钮，打开“创建数据库向导”对话框。（3）依次单击【下一步】按钮，并依照提示输入数据库名、数据库文件名、数据库文件的增长信息、事务日志文件信息及其增长信息，即可创建一个新的数据库。2．修改数据库在创建数据库后，可以根据数据库管理的实际需要调整数据库的属性。调整的内容包括以下几个方面：扩充或收缩分配给数据库的数据或事务日志空间。添加或删除数据和事务日志文件。创建文件组。创建默认文件组。更改数据库名称。更改数据库的所有者。在企业管理器中设置数据库属性的步骤如下：（1）打开企业管理器，展开服务器组，然后展开SQLServer服务器；（2）在“数据库”文件夹中，右击要更改的数据库（如：bbsDB），如图3-8所示，然后单击【属性】命令，打开数据库属性对话框；打开“文件组”选项卡，可以进行文件组相关属性的设置；打开“选项”选项卡，该选项卡中的各选项影响着数据库的工作方式和性能；打开“权限”选项卡，可用于设置用户/角色的权限。3.3.2任务二应用T-SQL创建数据库

创建数据库的基本语句格式如下：CREATEDATABASE<数据库文件名>[ON

<数据文件>]([NAME=<逻辑文件名>,]

FILENAME='<物理文件名>'

[,SIZE=<大小>]

[,MAXSIZE=<可增长的最大大小>]

[,FILEGROWTH=<增长比例>])[LOGON<日志文件>]([NAME=<逻辑文件名>,]

FILENAME='<物理文件名>'

[,SIZE=<大小>]

[,MAXSIZE=<可增长的最大大小>]

[,FILEGROWTH=<增长比例>])1．创建数据库到默认位置【例3-1】创建论坛数据库。CREATEDATABASEbbsDB本例创建名为“bbsDB”的数据库，并指定单个文件。指定的文件成为主文件，并会自动创建一个1MB的事务日志文件。创建数据库的过程分两步完成：（1）SQLServer使用Model数据库的副本初始化数据库及其元数据。（2）SQLServer使用空页填充数据库的剩余部分，除了包含记录数据库中空间使用情况以外的内部数据页。2．创建数据库到指定位置【例3-2】创建数据库到指定文件夹。CREATEDATABASEbbsDBON(NAME=bbsDB,FILENAME=‘d:\project\bbsDB.mdf')本例在d:\project文件夹中创建名为“bbsDB”的论坛数据库。创建过程如图3-13所示。第一个bbsDB为数据库名，name=bbsDB

中的bbsDB为数据文件名（也可以取不同的文件名）。事务日志文件（bbsDB_log）会自动创建。数据文件与日志文件都存放在用户指定的文件夹中（如例中的d:\project，文件夹应预先建立）。因为没有指定MAXSIZE，文件可以增长到填满所有可用的磁盘空间为止。3．指定文件属性【例3-3】创建带文件属性的数据库bbsDB。CREATEDATABASEbbsDBON(NAME=bbsDB_dat,FILENAME='e:\project\bbsDB_dat.mdf',SIZE=10,MAXSIZE=50,FILEGROWTH=5)LOGON(NAME='bbsDB_log',FILENAME='e:\project\bbsDB_log.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB)本例创建名为bbsB的数据库。创建过程如图3-14所示。因为没有使用关键字PRIMARY，第一个文件(bbsDB_dat)成为主文件。“bbsDB_dat”文件的SIZE参数没有指定MB或KB，因此默认为MB，以兆字节为单位进行分配。“bbsDB_log”日志文件以兆字节为单位进行分配，因为SIZE参数中显式声明了MB后缀。3.3.3任务三应用T-SQL修改数据库

使用ALTERDATABASE命令可以在数据库中添加或删除文件和文件组。也可以更改文件和文件组的属性，例如更改文件的名称和大小。ALTERDATABASE提供了更改数据库名称、文件组名称以及数据文件和日志文件的逻辑名称的能力，但不能改变数据库的存储位置。修改数据库的基本语句格式如下：ALTERDATABASE<数据库名称>{ADDFILE<数据文件>|ADDLOGFILE<日志文件>|REMOVEFILE<逻辑文件名>|ADDFILEGROUP<文件组名>|REMOVEFILEGROUP<文件组名>|MODIFYFILE<文件名>|MODIFYNAME=<新数据库名称>|MODIFYFILEGROUP<文件组名>|SET<选项>}1．添加文件和文件组【例3-4】在数据库“bbsDB”中增加数据文件“bbsDB_dat2”。本例创建数据库，并更改该数据库以添加一个5MB大小的新数据文件。打开数据库属性中“数据文件”选项可以看到如图3-15的结果。ALTERDATABASEbbsDB

ADDFILE(NAME=bbsDB_dat2,FILENAME='e:\project\bbsDB_dat2.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)2．修改文件【例3-5】更改数据库bbsDB中的“bbsDB_dat2”文件的分配空间。ALTERDATABASEbbsDBMODIFYFILE(NAME=bbsDB_dat2,SIZE=20MB)本例将bbsDB_dat2”文件增加大小到20MB（注意：文件分配空间只能增加，不能减少）。修改过程如图3-16所示。3．删除文件【例3-6】删除数据库bbsDB中的“bbsDB_dat2”文件。ALTERDATABASEbbsDBREMOVEFILEbbsDB_dat2本例将添加到数据库bbsDB中的“bbsDB_dat2”文件删除，删除过程如图3-17所示。4．修改数据库名称通过使用系统存储过程sp_renamedb可以更改数据库名称。存储过程sp_renamedb基本语句格式：sp_renamedb[当前数据库名称],[数据库新名称]【例3-8】将数据库bbsDB改名为“bbsDB_bak”。EXECsp_renamedb'bbsDB','bbsDB_bak'3.4查看和删除数据库3.4.1任务一应用企业管理器查看和删除数据库

1．查看数据库数据库建立以后，如果需要查看所建数据库的相关信息，可以通过以下步骤实现：（1）打开企业管理器，展开服务器组，然后展开SQLServer服务器。（2）在“数据库”文件夹中，右击要更改的数据库（如：bbsDB），然后单击“属性”命令，打开数据库属性对话框，可以查看数据库的相关属性。2．删除数据库当不再需要数据库或它被移到另一数据库服务器时，即可删除该数据库。数据库删除之后，文件及其数据都从服务器上的磁盘中删除。一旦删除数据库，它即被永久删除，并且不能进行检索，除非使用以前的备份。系统数据库msdb、master、model和tempdb不能进行删除与分离操作。使用“企业管理器”删除数据库的步骤：（1）打开企业管理器，展开服务器组，然后展开SQLServer服务器。（2）展开“数据库”文件夹，右击要删除的数据库，然后单击“删除”命令。（3）确认删除。3.4.2任务二应用T-SQL查看和删除数据库

数据对任何企事业单位来说是极为重要的资源，保证数据库的安全以防止数据遭到有意或无意的泄露、盗取、篡改与破坏是数据库管理员的责任。SQLServer2000提供了有效的数据安全性机制。1．应用“查询分析器”查看数据库使用系统存储过程sp_helpdb查看指定数据库或所有数据库的信息。存储过程sp_helpdb基本语句格式如下：sp_helpdb[数据库名称]【例3-9】查看所有数据库的信息。使用该命令可以查看所有数据库的信息，如图3-19所示。【例3-10】查看指定数据库bbsDB的信息。sp_helpdb

bbsDB使用该命令可以查看指定数据库bbsDB的信息2．删除数据库删除数据库的基本语句格式如下：DROPDATABASE<数据库名称>【例3-11】删除数据库bbsDB。DROPDATABASEbbsDB3.5综合实例数据库的迁移【例3-12】数据库中数据的迁移在数据库管理中，有时需要将某个数据库从一台机迁移到另一台机，通常可以采用以下两种办法。3.5.1任务一使用数据库复制文件

（1）停掉源数据库，可以将SQLServer服务管理器中的SQLServer服务停止，如图3-21所示，也可以在企业管理器中通过分离数据库功能将源数据库分离出来，如图3-22所示。（2）将要迁移的数据库对应的物理文件（MDF、LOG）拷贝到目的地。这两个物理文件一般保存在SQLServer的Data文件夹中。（3）打开目的SQLServer数据库的企业管理器，将该数据库文件附加为本地数据库，如图3-23所示。3.5.2任务二使用数据库备份文件

（1）在SQLServer企业管理器里选中要转移的数据库，按鼠标右键，选所有任务->备份数据库。（2）在图3-25中设置以下选项：“备份：选数据库——完全”，

“目的——备份到——按添加按钮”输入自定义的目的文件名(后缀一般是bak)，“重写：重写现有媒体”。（3）使用SQL

Server还原数据库功能，通过上述“示例”可以看出，对于数据库的操作，是一个综合性的应用。此外，还可以通过SQLServer企业管理器的“导入数据”、“导出数据”功能来实现SQLServer数据库服务器之间、SQLServer服务器与其他数据库（如Access、Oracle等）之间进行数据库转换库。数据导入/导出任务：将“pubs”数据库导出为ACCESS数据库，命名为pubsaccess.mdb数据导入/导出(续）数据库迁移的三种方法比较1、“分离数据库”－－“复制”－－“粘贴”－－“附加数据库”；

SQL数据库SQL数据库2、“备份”－－“还原”

SQL数据库SQL数据库3、“DTS导入/导出向导”－－“