《（农业）数据库应用》课程

《（农业）数据库应用》课程 （农业）数据库应用主讲：谭春茂上海农林职业技术学院农业信息工程系

《（农业）数据库应用》课程 任务二蔬菜数据库创建与管理上海农林职业技术学院农业信息工程系

任务描述： • 在上一次任务中，我们完成了数据库SQL Server的安装和配置，接下来我们就要对将要使用的数据库进行创建。数据库和数据表是SQL Server最基本的操作对象，与数据库相关的一些基本概念，数据库和数据表的创建、查看、修改、删除是SQL Server的最基本操作，是进行数据库管理与开发的基础。首先们我就来创建将要使用的数据库及数据库查看，修改，删除等相关操作以及数据库的附加和分离等基本操作。

1．SQL Server的数据库文件类型 • 根据数据库文件的作用不同，可以将数据库文件分为发下三类： • （1）主数据文件(Primary File) • 主数据文件是数据库的起点，用来存放数据库的数据和数据库的启动信息。每个数据库都有且必须仅有一个主数据文件。主数据文件的默认扩展名是.MDF。主数据文件都有两个名称，操作系统文件名（物理文件名）和逻辑文件名。 • （2）次数据文件(Secondary File) • 次数据文件也叫辅助数据文件。次数据文件是可选的，主要用来存放不在主数据文件中的其他数据和对象。使用次数据文件可以扩展存储空间。次数据文件的默认扩展名是.NDF。 • （3）事务日志文件(Transaction Log) • 事务是一个单元的工作，该单元的工作要么全部完成，要么全部不完成。Microsoft SQL Server 2005系统具有事务的功能，可以保证数据库操作的一致性和完整性。SQL Server 系统使用数据库的事务日志来实现事务的功能。凡是对数据库进行增、删、改等操作，都会记录在事务日志文件中。如果数据库被破坏时可以使用该日志文件来恢复数据库的数据。每个数据库至少要有一个事务日志文件，事务日志文件的扩展名是.LDF。

SQL Server的数据库文件组 • 文件组就是文件的逻辑集合。文件组可以把一些指定的文件组合在一起以方便管理和分配数据。以提高数据的查询速度，便于数据库的维护。例如：某个数据中，三个文件（data1.ndf、data2.ndf、data3.ndf）分别创建在三个不同的磁盘驱动器上，并且为他们指定了一个文件组fgroup1。创建表后可以明确的指定存放在fgroup1中。这样对该表中数据的查询将分布在这三个磁盘上同时进行，因此可以通过并行执行访问来提高查询的性能。 • 通过使用文件组还可以简化数据库的维护工作： • （1）备份和恢复单独的文件或文件组，而非整个数据库，可以提高效率。 • （2）将可维护性要求相近的表和索引分配到相同的文件组中。 • （3）为自己的文件组指定可维护性高的表。 • 使用文件和文件组时，应该考虑以下因素： • （1）一个文件或者文件组只能用于一个数据库，而不能用于多个数据库。 • （2）一个文件只能是某个文件组的成员，不能同时是多个文件给的成员。 • （3）数据库的数据信息和日志信息不能放在同一个文件或文件组中，数据文件和日志文件总是分开的。 • （4）日志文件永远也不能是任何文件组的一部分。

sql server2005 系统数据库 《（农业）数据库应用》课程 SQL Sever包含了6个系统数据库:Master 、Model 、MSDB 、TempDB 、Resource 、Distribution 。其实系统数据包含了Master, Model, MSDB, TempDB, 和一个隐藏的Resource 数据库。如果你的服务是一个分布式的据库服务，那就还有个系统的distribution数据库。所以我们一般安装完成后，可以看到前4个数据库上海农林职业技术学院农业信息工程系

Master数据库 • Master数据库记录了所有的SQL Server数据库系统的系统级信息。包括实例范围内的元数据，如登录帐号，终端，连接服务器和系统配置设置元数据。另外，Master是数据库，记录了所有其他数据库的基本信息，以及这些数据库文件的位置，并记录为SQL Server的初始化信息。因此，如果Master数据库不可用的话，SQL Server是无法启动的。但是在SQL Server 2005或者更高版本里并不是把系统信息存储在master数据库中，而是在Resource数据库。但是，系统的信息在逻辑上表现还是在Master数据库中。而且我们知道，这些系统数据库是存放在C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA。 • 对于Master数据库来说，数据文件和日子文件分别是在该路径下的master.mdf和mastlog.ldf。

Model数据库 • Model数据库是一个模板数据库。每当创建一个新的数据库（包括系统数据库的TempDB），会创建一个以Model数据库为副本数据库，并更改成你创建数据库时所用的名称。这样的的优点是可以通过模板数据库先前创建好基本对象，然后创建新数据库，这样新建立的数据库就有了该基本对象。

MSDB数据库 • MSDB数据库是SQL Server代理的数据库。这是因为SQL Server代理是通过使用MSDB数据库来做存储自动化作业定义，作业调度，操作定义，触发提醒定义。代理是负责几乎所有自动化操作和调度操作。MSDB还包含了所有的工作准备，比如对于开始任何工作，得到了状态或停止作业命令，这些都是运行在MSDB数据库中。 • MSDB数据库不仅仅是SQL Server代理来大量使用的唯一服务，比如Broker服务，数据库邮件服务和Reporting服务也是使用MSDB数据库来保存调度信息。除了之外，SQL Server集成服务（SSIS）也利用了MSDB数据库为SSIS包存储。

TempDB数据库 • TempDB数据库是由SQL Server用于暂时存储数据的。TempDB数据库被经常使用的在SQL Server操作中，所以认真的计划和评价它的大小和位置是至关重要的，以确保有效的SQL Server数据库操作。TempDB数据库被数据库引擎用来保存和存储临时对象（如临时表，视图，游标和表值变量），这些当然也可以由由数据库程序员创建使用。 • 此外，TempDB数据库被SQL Server数据库引擎用来保存中间查询结果，用于排序操作前或操作其他数据。例如，如果你写一个查询，返回100行，你想通过一个结果的日期值排序时，SQL Server可能首先把未排序的结果发送到一个临时工作表中，然后将执行排序操作，然后返回排序你的结果。如果联机索引操作执行中，tempdb数据库会保存这个索引。 • 另一个需要牢记的一点是，对于TempDB数据库，是所有用户可以访问的，可以创建和创造和修改临时对象。这种访问有可能会带来死锁和大小限制的问题，因此就像任何其他SQL Server数据库，对tempdb数据库的监测是很重要的。

Resource数据库 • 接着介绍下Resource系统数据库。Resource数据库是一个只读数据库，包含所有的SQL Server实例使用的系统对象。Resource数据库是无法访问作为一个正常的数据库，也不能像正常数据库那样操作。它在逻辑上被看作是系统模式上的。它不包含任何用户数据或元数据。然而，它包含了所有的系统对象的结构和说明。 • 这种设计使得能够快速建立一个新的服务包应用，只要更换一个新的现有Resource数据库的。而且，假如要恢复一个服务包，你也只要把旧的Resource数据库更换成新的Resource数据库。这样极大的方面了数据库服务要求删除或增加了新的系统对象后简单安全运行。Resource数据库的物理文件名是mssqlsystemresource.mdf和mssqlsystemresource.ldf。默认情况下，这些文件位于 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\下。每个SQL Server实例都只有一个关联的mssqlsystemresource.mdf文件，和其它实例不共享此文件。资源数据库依赖于master数据库的位置。注意，如果你移动master数据库，则必须同时移动资源数据库到相同的位置。

数据库文件和文件组 • 数据库文件 • 主文件用于存放数据，每个数据库都必须有一个主文件。扩展名为.MDF • 次要文件也用于存放数据，一个数据库可以没有也可以有多个Secondary文件。扩展名为.NDF • 事务日志文件包含用于恢复数据库的日志信息。每个数据库必须至少有一个日志文件。扩展名为.LDF • 文件组允许对文件分组以便对它们进行管理。 • 比如，将数据文件data1.mdf、data2.mdf和data3.mdf分别创建在三个盘上，这三个文件组成文件组fgroup1，在创建表的时候，就可以指定一个表创建在文件组fgroup1上。这样该表的数据就可以分布在三个盘上，在对该表执行查询时，可以并行操作，大大提高了查询效率。

SQL Server的文件和文件组必须遵循以下规则： • (1)一个文件和文件组只能被一个数据库使用。 • (2)一个文件只能属于一个文件组。 • (3)数据和事务日志不能共存于同一文件或文件组上。 • (4)日志文件不能属于任何文件组。

数据库对象

利用SSMS创建数据库 • 教师演示操作 • 利用SSMS创建蔬菜销售系统数据库 • 数据库名称：VegtablesDB • 创建蔬菜销售系统数据库，查看，修改，删除

演示操作 • 创建名为"Vegetables"的数据库,数据库包含一个主数据文件和一个事务日志文件。主数据文件的逻辑名为"Vegetables"，初始容量大小为3MB，最大容量为20MB，文件增长量为1MB；日志文件的逻辑名为"Vegetables_log" , 初始容量大小为1MB，最大容量为10MB，文件增长量为10% ，数据文件和日志文件都保存在默认目录下。操作步骤如下：

（1）启动SQL Server Management Studio，在“对象资源管理器”窗口中，右击数据库节点，在弹出的快捷菜单中选择“新建数据库”命令，如图所示。

单击“新建数据库”命令，打开“新建数据库”对话框，如图所示，在这个对话框中包含3个选择页：“常规”、“选项”和“文件组”。单击“新建数据库”命令，打开“新建数据库”对话框，如图所示，在这个对话框中包含3个选择页：“常规”、“选项”和“文件组”。

使用T-SQL创建数据库 • SQL（Structured Query Language）语言，又称作结构化查询语言。它是一种用户操作关系数据库的通用语言。标准SQL语言提供支持，各大型DBMS厂商对标准SQL做了扩充，如微软的Transaction-SQL，甲骨文的PL/SQL。 • Transact-SQL（T-SQL）是ANSI标准SQL数据库查询语言的一个强大的实现，是Microsoft公司在关系型数据库管理系统SQL Server中对SQL的扩展，具有SQL的主要特点，同时增加了变量、运算符、函数、流程控制和注释等语言元素。

使用T-SQL创建数据库 • 在SQL Server 2005中，除使用SQL Server Management Studio以图形界面创建数据库外,还可以在查询编辑器中用T-SQL语句中的CREATE DATABASE语句创建数据库,下面介绍如何使用 CREATE DATABASE语句创建数据库。

创建数据库语法 CREATE DATABASE 数据库名 ON ( NAME = 数据文件逻辑名称, FILENAME = '存取路径\数据文件名', SIZE = 数据文件初始大小, FILEGROWTH = 数据文件增长率% ) LOG ON ( NAME = 日志文件逻辑名称, FILENAME = '存取路径\日志文件名', SIZE = 日志文件初始大小, FILEGROWTH = 日志文件增长率% )

实例 • 创建名为“Vegetablest”的数据库,数据库包含一个主数据文件和一个事务日志文件。主数据文件的逻辑名为“Vegetablest”，操作系统文件名为“Vegetablest.mdf”，初始容量大小为5MB，最大容量为20MB，文件增长量为15%；日志文件的逻辑名为“Vegetablest” ，操作系统文件名为“Vegetablest_log.ldf”, 初始容量大小为3MB，最大不受限制，文件增长量为1MB ，数据文件和日志文件都保存在C盘的根目录下。 CREATE DATABASE Vegetablest ON PRIMARY (NAME=' Vegetablest ', FILENAME='c:\Vegetablest_data.mdf', SIZE=5MB, MAXSIZE=20MB, FILEGROWTH=15%) LOG ON (NAME=' Vegetablest_log', FILENAME='c:\ Vegetablest_log.ldf', SIZE=3MB, FILEGROWTH=1MB) GO

使用SSMS管理数据库 • 数据库信息查看 • 数据库的信息包括：数据库基本信息、状态、空间大小、已经使用空间等信息。

修改数据库 • 数据库创建成功后，可以根据需要随时修改数据库信息，除上节介绍的修改数据选项外，还包括：数据库更名、扩大数据库容量、收缩数据库容量等操作。 1．数据库更名数据库一旦创建成功后，一般情况下不要轻易的更改数据库的名称。因为可能有一些应用程序已经使用了该数据库。如果要更改了数据库名，相应的在其他使用了该数据库的地方也要做修改。使用SQL Server Management Studio修改库数据库名的方法和步骤如下：（1）启动SQL Server Management Studio，在“对象资源管理器”窗口中，展开“数据库”节点，右击要修改的"Vegetables"数据库，在弹出的快捷菜单中选择“属性”命令，打开“数据库属性”对话框中。（2）在弹出的“数据库属性”对话框中,单击“选项”选择项，将数据库选项中的“限制访问”设置为“Single”用户模式，此时，"Vegetables"数据库旁边就有个单用户标志。（3）右击"Vegetables"数据库，在弹出的快捷菜单中，选择“重命名”命令，此时，数据库名称变成可编辑状态，输入新的数据库名称即可。（4）更名后，将数据库选项中的“限制访问”设置为“Multipe”用户模式。

扩大数据库容量 • 在创建数据时，用户都会指定容量。如果数据库的数据量不断膨胀，超过指定的容量时，必须为它增加容量。可以通过3种方式来扩大数据库，第一种：设置数据库自动增长，可以在创建数据库时设置；第二种：直接修改数据库的数据文件和日志文件的大小；第三种：在数据库中增加辅助数据库文件和日志文件。使用SQL Server Management Studio修改库数据库名的方法和步骤如下：（1）启动SQL Server Management Studio，在“对象资源管理器”窗口中，展开“数据库”节点，右击要修改的"Vegetables"数据库，在弹出的快捷菜单中选择“属性”命令，打开“数据库属性”对话框中。（2）在弹出的“数据库属性”对话框中,单击“选项”选择项，单击数据库选项中的“文件”选项，此时，就可以修改"Vegetables"数据库的初始大小和增长方式，操作方法与创建数据库时的方法相同。若要通过增加数据辅助文件和日志文件来增加数据库的容量，可以执行如下操作：在弹出的“数据库属性”对话框中,单击“选项”选择项，单击数据库选项中的“文件”选项，对话框中显示当前数据库的数据文件和日志文件信息，单击“添加”按钮，就可以增加需要的辅助数据文件和日志文件，操作方法与创建数据库时的方法相同。（3）设置完成后，单击“确定”按钮，所操作的设置立即生效。

收缩数据库容量 • 当在数据库的使用过程中，发现创建数据库时指定的容量过大，为减少存储空间的浪费，我们可以通过操作来减少数据库的容量，称“收缩数据库”。

附加、分离和删除数据库 • 除上面所介绍的数据库操作外，数据库管理操作还包括附加数据库、分离数据库和删除数据库。操作的原因： • 转移存放位置 • 程序移植中，放到另外一台服务器中

附加数据库 启动SQL Server Management Studio，在【对象资源管理器】窗口中，右击【数据库】节点，在弹出的快捷菜单中选择【附加】命令，打开【附加数据库】对话框中

分离数据库 • 分离数据库是指将数据库从Microsoft SQL Server实例中删除，但是该数据库的数据文件和日志文件仍保存在该服务器的硬盘上。使用SQL Server Management Studio分离数据库名的方法和步骤如下： • （1）启动SQL Server Management Studio，在【对象资源管理器】窗口中，展开【数据库】节点，右击要分离的【Vegetables】数据库，在弹出的快捷菜单中选择【任务】→【分离】命令，打开【分离数据库】对话框， • （2）在弹出的【分离数据库】对话框中,，显示要分离数据库的选项，如图

删除数据库 • 使用SQL Server Management Studio删除库数据库的方法和步骤为：启动SQL Server Management Studio，在【对象资源管理器】窗口中，展开【数据库】节点，右击要删除的数据库，在弹出的快捷菜单中选择【删除】命令，打开【删除数据库】单击【确定】按钮，完成数据库的删除操作。 • 使用T-SQL语句删除数据库 • 使用SQL语句删除数据库需要使用DROP DATABASE语句。使用DROP DATABASE语句可以一次删除多个数据库。语法格式为： • DROP DATABASE 数据库名列表

任务小结 • 本节主要介绍了利用SQL Server2005的SSMS和使用SQL语句来创建数据库，并利用SSMS管理数据库，同时介绍了数据库的附加分离和删除操作。这些技能掌握后，为后续学习构造一个数据库环境，下节将在我们这次任务的数据库基础上创建数据库的相关表。

学生技能操作 • 实训二：蔬菜数据库创建与管理

《 （农业）数据库应用 》 课程