第5章数据库的概念和操作





SQL Server的数据库是有组织的数据的集合，这种数据集合具有逻辑结构并得到数据库系统的管理和维护。数据库由包含数据的基本表和对象(如视图、索引、存储过程和触发器等)组成，其主要用途是处理数据管理活动产生的信息。
对数据库进行操作是开发人员的一项重要的工作。本章首先介绍数据库的基本概念，然后以实例的形式介绍数据库的创建、修改和删除操作。
5.1数据库的基本概念
数据库是SQL Server 2008存放表和索引等数据库对象的逻辑实体。数据库的存储结构分为逻辑存储结构和物理存储结构两种。



视频讲解



5.1.1物理数据库
数据库的物理存储结构指的是保存数据库各种逻辑对象的物理文件是如何在磁盘上存储的，数据库在磁盘上是以文件为单位存储的，SQL Server 2008将数据库映射为一组操作系统文件。数据库中所有的数据和对象都存储在操作系统文件中。
1. SQL Server 2008中数据库文件的类型
SQL Server 2008的数据库具有下面3种类型的文件。
(1) 主数据文件： 主数据文件是数据库的起点，指向数据库中的其他文件。每个数据库都有且只有一个主数据文件。主数据文件的推荐扩展名是.mdf。
(2) 辅助数据文件： 除主数据文件以外的其他所有数据文件都是辅助数据文件。某些数据库可能不含有任何辅助数据文件，而有些数据库则含有多个辅助数据文件。辅助数据文件的推荐扩展名是.ndf。
(3) 事务日志文件： 日志文件包含了用于恢复数据库的所有日志信息。每个数据库必须至少有一个日志文件，当然也可以有多个。SQL Server 2008事务日志采用提前写入的方式，即将对数据库的修改先写入事务日志中，然后再写入数据库。日志文件的推荐扩展名是.ldf。
SQL Server 2008不强制使用.mdf、.ndf和.ldf扩展名，但使用它们有助于标识文件的类型和用途。
在SQL Server 2008中，数据库中所有文件的位置都记录在该数据库的主数据文件和系统数据库——master数据库中。
2. 数据库文件组
为了便于管理和分配数据而将文件组织在一起，通常可以为一个数据库创建多个文件组(File Group)，将多个数据库文件分配在不同的文件组内分组管理。
SQL Server中的数据库文件组分为主文件组(primary file group)和用户定义文件组(user_defined group)。
(1) 主文件组： 主文件组包含主数据文件和任何没有明确指派给其他文件组的其他文件。数据库的系统表都包含在主文件组中。
(2) 用户定义文件组： 用户定义文件组是在CREATE DATABASE或ALTER DATABASE语句中使用FILEGROUP关键字指定的文件组。
文件组的应用规则如下。
(1) 一个数据文件只能存在于一个文件组中，一个文件组也只能被一个数据库使用。
(2) 在主文件组中包含了所有的系统表。在建立数据库时，主文件组包括主数据文件和未指定组的其他数据文件。
(3) 在创建数据库对象(例如数据表)时，如果没有指定将其放在哪一个文件组中，就会将它放在默认文件组中。如果没有指定默认文件组，则主文件组为默认文件组。
(4) 事务日志文件不分组管理，即不属于任何文件组。



视频讲解



5.1.2逻辑数据库
数据库是存储数据的容器，即数据库是一个存放数据的表和支持这些数据的存储、检索、安全性和完整性的逻辑成分所组成的集合。
组成数据库的逻辑成分称为数据库对象，SQL Server 2008中的逻辑对象主要包括数据表、视图、同义词、存储过程、函数、触发器、规则以及用户、角色、架构等。
每个SQL Server都包含两种类型的数据库，即系统数据库和用户数据库。
系统数据库存储有关SQL Server的信息，SQL Server使用系统数据库来管理系统，例如下面将要介绍的master数据库、model数据库、msdb数据库和tempdb数据库，而用户数据库由用户来建立，例如teaching数据库。SQL Server可以包含一个或多个用户数据库。
1. master数据库
顾名思义，master数据库是SQL Server 2008中的主数据库，它是最重要的系统数据库，记录系统中所有系统级的信息。它对其他的数据库实施管理和控制的功能，同时该数据库还保存了用于SQL Server管理的许多系统级信息。master数据库记录所有的登录账户和系统配置，它始终有一个可用的最新master数据库备份。
由此可知，如果在计算机上安装了一个SQL Server系统，那么系统首先会建立一个master数据库来记录系统的有关登录账户、系统配置、数据库文件等初始化信息。例如，如果用户在这个SQL Server系统中建立一个用户数据库(如teaching数据库)，系统马上将用户数据库的有关用户管理、文件配置、数据库属性等信息写入master数据库。系统正是根据master数据库中的信息来管理系统和其他数据库的。因此，如果master数据库信息被破坏，整个SQL Server系统将受到影响，用户数据库将不能被使用。
2. model数据库
model数据库为用户新创建的数据库提供模板，它包含了用户数据库中应该包含的所有系统表的结构。当用户创建数据库时，系统会自动把model数据库中的内容复制到新建的用户数据库中。用户在系统中新创建的所有数据库的内容最初都与该模板数据库具有完全相同的内容。

3. msdb数据库
msdb数据库记录备份及还原的历史信息、维护计划信息、作业信息、异常信息以及操作者信息等，所以它可以为SQL Server代理程序提供要调度的警报和作业等信息。
当很多用户使用一个数据库时，经常会出现多个用户对同一个数据修改而造成数据不一致的现象，或者用户对某些数据和对象的非法操作等。为了防止上述现象的发生，SQL Server中有一套代理程序能够按照系统管理员的设定监控上述现象的发生，及时向系统管理员发出警报。那么当代理程序调度警报和作业、记录操作者时，系统要用到或实时产生许多相关信息，这些信息一般存储在msdb数据库中。
4. tempdb数据库
在使用SQL Server系统时，经常会产生一些临时表和临时数据库对象等，例如用户在数据库中修改表的某一行数据时，在修改数据库这一事务没有被提交的情况下，系统内就会有该数据的新、旧版本之分，往往修改后的数据表构成了临时表，所以系统要提供一个空间来存储这些临时对象。tempdb数据库保存所有的临时表和临时存储过程。tempdb数据库是全局资源，所有连接到系统用户的临时表和存储过程都被存储在该数据库中。
tempdb数据库有一个特性，即它是临时的，tempdb数据库在SQL Server每次启动时都被重新创建，因此该数据库在系统启动时总是空的，上一次的临时数据都被清除掉了。临时表和存储过程在连接断开时自动清除，而且在系统关闭后将没有任何连接处于活动状态，因此tempdb数据库中没有任何内容会从SQL Server的一个启动工作保存到另一个启动工作之中。
默认情况下，在SQL Server运行时，tempdb数据库会根据需要自动增长。不过，与其他数据库不同，每次启动数据库引擎时它会重置初始大小。
master、model、msdb、tempdb这4个系统数据库都是在SQL Server系统安装时生成的。
5.2数据库的操作
在SQL Server 2008中，用户可以自己创建数据库(即用户数据库)，并且可以对数据库进行修改、删除等操作。
5.2.1创建数据库
若要创建数据库，必须确定数据库的名称、所有者、大小以及存储该数据库的文件和文件组。在创建数据库时，根据数据库中预期的最大数据量应创建尽可能大的数据文件。
在SQL Server 2008中创建数据库主要有两种方式，一是在SQL Server Management Studio中使用向导创建数据库； 二是通过查询窗口执行TSQL语句创建数据库。



视频讲解



1. 在SQL Server Management Studio中创建数据库
在SQL Server Management Studio中创建数据库的过程如下。
(1) 启动SQL Server Management Studio，在对象资源管理器的“数据库”结点上右击，选择快捷菜单中的“新建数据库”命令，如图51所示。



图51选择“新建数据库”命令


(2) 弹出“新建数据库”对话框，在“常规”选择页的“数据库名称”文本框中输入要创建的数据库的名称，如图52所示。


图52“新建数据库”对话框


其中，SQL Server 2008的数据库文件拥有两个名称，即逻辑文件名和物理文件名。
 逻辑文件名： 逻辑文件名是在所有TSQL语句中引用物理文件时所使用的名称。逻辑文件名必须符合SQL Server标识符规则，每一个数据库的逻辑文件名只有一个。
 物理文件名： 物理文件名是包括目录路径的物理文件名，它必须符合操作系统文件的命名规则。通过上面的介绍我们知道，数据库中至少包含一个主数据文件和一个事务日志文件，其存储路径和文件名都可以在图52所示对话框中修改，当然也可以利用“添加”按钮添加多个辅助数据文件和日志文件。


图53创建数据库成功

(3) 在“常规”选择页中数据文件的“初始大小”处可以设置文件的初始大小(MB)； 单击“自动增长”后的按钮可设置自动增长方式和最大文件大小； 单击“路径”后的按钮可设置文件的存放路径。
(4) 在“选项”选择页中设置数据库的属性，在“文件组”选择页中增加或删除文件组。
在对象资源管理器中展开“数据库”，可以看到新建的数据库，如图53所示。
【例51】创建数据库——teaching教学库，主数据文件的初始大小为5MB，增长方式是按10%的比例自动增长； 日志文件初始为8MB，按1MB增长(默认是按10%的比例增长)。两个文件都不限制增长，存储位置分别为“E:＼DATA”和“F:＼DATA”。



视频讲解



2. 使用TSQL语句创建数据库
在SQL Server 2008中可以利用TSQL语句创建数据库。TSQL语言提供的数据库创建语句为CREATE DATABASE，其语法格式如下： 

CREATE DATABASEdatabase_name

［ON ［PRIMARY］［<filespec> ［,…n］］ ［,<filegroupspec> ［,…n］］］

［LOG ON {<filespec> ［,…n］}］ 

［FOR LOAD|FOR ATTACH］ 

<filespec>∶∶=(［NAME=logical_file_name,］

FILENAME='os_file_name'

［,SIZE=size］

［,MAXSIZE={max_size|UNLIMITED}］

［,FILEGROWTH=growth_increment］ )［,…n］ 

说明： 在TSQL语言的语法格式中，用［ ］括起来的内容表示是可选的； ［,…n］表示重复前面的内容； 用< >括起来的内容表示在实际编写语句时用相应的内容代替； 用{ }括起来的内容表示是必选的； 类似A|B的格式，表示A和B只能选择一个，不能同时都选。
对其中的参数说明如下。
 database_name： 新数据库的名称。数据库名称在服务器中必须唯一，最长为128个字符，并且要符合标识符的命名规则。每个服务器管理的数据库最多为32767个。
 ON： 指定存放数据库的数据文件信息。该关键字后面可以包含用逗号分隔的<filespec>列表，<filespec>列表用于定义主文件组的数据文件。在主文件组的文件列表后可以包含用逗号分隔的<filegroupspec>列表，<filegroupspec>列表用于定义用户文件组及其中的文件。
 PRIMARY： 用于指定主文件组中的文件。主文件组不仅包含数据库系统表中的全部内容，而且包含用户文件组中没有包含的全部对象。一个数据库只能有一个主文件，在默认情况下，如果不指定PRIMARY关键字，则在命令中列出的第一个文件将被默认为主文件。
 LOG ON： 指明事务日志文件的明确定义。如果没有该选项，则系统会自动产生一个文件名前缀，与数据库名相同，容量为所有数据库文件大小的1/4的事务日志文件。
 NAME： 指定数据库的逻辑名称，这是在SQL Server系统中使用的名称，是数据库在SQL Server中的标识符。
 FILENAME： 指定数据库所在文件的操作系统文件名称和路径，该操作系统文件名称和NAME的逻辑名称一一对应。
 SIZE： 指定数据库的初始容量大小。如果没有指定主文件的大小，则SQL Server默认其与模板数据库中的主文件大小一致，其他数据库文件和事务日志文件则默认为1MB。指定大小的数字size可以使用MB、GB和TB后缀，默认的后缀为MB。在size中不能使用小数，默认值为1MB。主文件的size不能小于模板数据库中的主文件。
 MAXSIZE： 指定操作系统文件可以增长到的最大尺寸。如果没有指定，则文件可以不断增大直到充满磁盘。
 FILEGROWTH： 指定文件每次增加容量的大小，当指定数据为0时，表示文件不增长。增加量可以确定为以MB、GB等做后缀的字节数或以%做后缀的被增加容量文件的百分比来表示，默认后缀为MB。如果没有指定FILEGROWTH，则默认值为1MB(数据文件)或10%(日志文件)。
【例52】使用CREATE DATABASE创建一个新的数据库，名称为“STUDENT1”，其他所有参数均取默认值。
实现的步骤如下： 
(1) 打开SQL Server Management Studio，在窗口上部的工具栏的左侧找到“新建查询”按钮。
(2) 单击“新建查询”按钮，在SQL Server Management Studio的窗口右侧会建立一个新的查询页面，默认的名称为“SQLQuery1.sql”，在这个页面中可以输入要让SQL Server执行的TSQL语句。
(3) 在这里输入下面列出的创建数据库的TSQL语句： 

CREATEDATABASESTUDENT1
(4) 单击工具栏中的“！执行”按钮，当系统给出的提示信息为“命令已成功完成”时，说明此数据库创建成功，如图54所示。


图54创建STUDENT1数据库





视频讲解



【例53】创建数据库名为“STUDENT2”的数据库，包含一个主数据文件和一个事务日志文件。主数据文件的逻辑名为“STUDENT2_DATA”，操作系统文件名为“STUDENT2_DATA.MDF”，初始容量大小为5MB，最大容量为20MB，文件的增长量为20%。事务日志文件的逻辑文件名为“STUDENT2_LOG”，物理文件名为“STUDENT2_LOG.LDF”，初始容量大小为5MB，最大容量为10MB，文件增长量为2MB。数据文件与事务日志文件都放在F盘的DATA文件夹中。
首先在F盘创建一个新的文件夹，名称是“DATA”。然后在SQL Server Management Studio窗口中单击“新建查询”按钮，在打开的窗口中输入图55所示的内容，单击“执行”按钮，可创建数据库。



图55创建STUDENT2数据库



CREATE DATABASE STUDENT2

ON PRIMARY

 (NAME = 'STUDENT2_DATA', 

FILENAME = 'F:＼DATA＼STUDENT2_DATA.MDF' , 

SIZE = 5MB, 

MAXSIZE = 20MB, 

FILEGROWTH = 20%)

LOG ON 

(NAME ='STUDENT2_LOG', 

FILENAME = 'F:＼DATA＼STUDENT2_LOG. LDF', 

SIZE = 5MB, 

MAXSIZE = 10MB,

FILEGROWTH = 2MB)
注意： 在一个数据库中最多可以创建32767个文件组，文件组不能独立于数据库文件而建立，文件组是管理数据库中一组数据文件的机制。
【例54】创建一个指定多个数据文件和日志文件的数据库。该数据库名称为STUDENTS，有一个5MB和一个10MB的数据文件以及两个5MB的事务日志文件。数据文件的逻辑名称为STUDENTS1和STUDENTS2，物理文件名为STUDENTS1.mdf和STUDENTS2.ndf。主数据文件STUDENTS1属于PRIMARY文件组，辅助数据文件STUDENTS2属于新建文件组FG1，两个数据文件的最大大小分别为无限大和100MB，增长速度分别为10%和1MB。事务日志文件的逻辑名为STUDENTSLOG1和STUDENTSLOG2，物理文件名为STUDENTSLOG1.ldf和STUDENTSLOG2.ldf，最大大小均为50MB，文件增长速度为1MB。要求数据库文件和日志文件的物理文件都存放在E盘的DATA文件夹下。
实现的步骤如下。
(1) 在E盘创建一个新的文件夹，名称是DATA，然后在SQL Server Management Studio中新建一个查询页面。
(2) 输入以下程序段，并执行此查询。

CREATEDATABASESTUDENTS

ON

(NAME=STUDENTS1,

FILENAME='E:＼DATA＼STUDENTS1.MDF',

SIZE=5,

MAXSIZE=unlimited,

FILEGROWTH=10%),

FILEGROUP FG1

(NAME= STUDENTS2,

FILENAME='E:＼DATA＼STUDENTS2.NDF',

SIZE=10,

MAXSIZE=100,

FILEGROWTH=1)

LOG ON 

(NAME=STUDENTSLOG1,

FILENAME='E:＼DATA＼STUDENTSLOG1.LDF',

SIZE=5,

MAXSIZE=50,

FILEGROWTH=1),

(NAME=STUDENTSLOG2,

FILENAME='E:＼DATA＼STUDENTSLOG2.LDF',

SIZE=5,

MAXSIZE=50,

FILEGROWTH =1)
5.2.2修改数据库
在建好数据库之后，可以对其修改。修改数据库包括增减数据文件和日志文件、修改文件属性(包括更改文件名和文件大小)、修改数据库选项等。



视频讲解



1. 增加数据库空间
1) 增加已有数据库文件的大小
在SQL Server Management Studio的“对象资源管理器”窗口中展开“数据库”，然后右击要修改的数据库的名称，在快捷菜单中选择“属性”命令，打开“数据库属性”窗口，选择“文件”选择页，如图56所示，修改“初始大小”选项以及“自动增长”中的“文件增长”和“最大文件大小”选项。


图56“数据库属性”窗口


用户也可以使用TSQL语句增加已有数据库文件的初始大小，语法格式如下： 

ALTER DATABASE数据库名

MODIFY FILE

(NAME=逻辑文件名,

SIZE=文件大小)
【例55】为STUDENT2数据库增加容量，原来数据库文件STUDENT2_DATA的初始分配空间为5MB，现在将STUDENT2_DATA的分配空间增加至20MB。

ALTER DATABASE STUDENT2

MODIFY FILE

(NAME=STUDENT2_DATA,

SIZE=20MB)
2) 增加数据库文件
在SQL Server Management Studio中选择“数据库属性”窗口中的“文件”选择页，单击“添加”按钮，为新的数据库文件指定逻辑文件名、初始大小、文件增长方式等属性，然后单击“确定”按钮完成增加数据库文件数目的操作。
用户也可以使用TSQL语句增加数据库文件的数目，语法格式如下： 

ALTER DATABASE数据库名

ADD FILE|ADD LOG FILE

<filespec>
【例56】为数据库STUDENT2增加数据文件STUDENT2_DATA1，初始大小为10MB，最大为50MB，按照5%的比例增长。

ALTER DATABASE STUDENT2

ADD FILE

(NAME='STUDENT2_DATA1'，

FILENAME='F:＼DATA＼ STUDENT2_DATA1.NDF',

SIZE=10MB,

MAXSIZE=50MB,

FILEGROWTH=5%)



视频讲解



2. 缩减已有数据库文件的大小
数据库文件的大小可以缩减，具体步骤如下： 
(1) 在SQL Server Management Studio的“对象资源管理器”窗口的数据库名上右击，选择快捷菜单中的“任务”→“收缩”→“数据库”命令，在出现的“收缩数据库”对话框中保持默认设置，单击“确定”按钮，数据库收缩完毕。
(2) 如果要收缩特定的数据文件或日志文件，可选择快捷菜单中的“任务”→“收缩”→“文件”命令。
(3) 数据库的自动收缩可以在“数据库属性”窗口中的“选项”选择页中设置，只要将选项中的“自动收缩”设为“True”即可。
注意： 为了避免存储空间的浪费，可以进行数据库的手动收缩或设置自动收缩。但是无论怎么收缩，数据库的大小也不会小于其初始大小，所以在创建数据库时对初始大小的选择应尽可能合理。
3. 删除数据库文件
在SQL Server Management Studio中选择“数据库属性”窗口中的“文件”选择页，指定要删除的文件，然后单击“删除”按钮就可以删除对应的文件，从而缩减了数据库的空间。
使用ALTER DATABASE的REMOVE FILE子句可以删除指定的文件，其语法格式如下： 

ALTER DATABASE数据库名

REMOVE FILE逻辑文件名
【例57】将数据库STUDENT2中增加的数据文件STUDENT2_DATA1删除。

ALTER DATABASE STUDENT2

REMOVE FILE STUDENT2_DATA1
4. 数据库的更名
在数据库建好后可以更改其名称，在重命名数据库之前应该确保没有用户正在使用该数据库。
常用的更名方法有下面两种。
方法一： 在SQL Server Management Studio中选择此数据库，然后右击，在弹出的快捷菜单中选择“重命名”命令。
方法二： 在查询窗口中执行系统存储过程sp_renamedb更改数据库的名称。系统存储过程sp_renamedb的语法如下： 

sp_renamedb ［@dbname=］'old_name' ,［@newname=］'new_name'
【例58】将已存在的数据库STUDENT2重命名为STUDENT_BACK。

sp_renamedb'STUDENT2' , ' STUDENT_BACK'



视频讲解



5.2.3删除数据库
对于不再使用的数据库可以删除，删除数据库的方法如下： 
1. 使用SQL Server Management Studio删除数据库
打开SQL Server Management Studio，选择“数据库”，然后右击要删除的数据库，在弹出的快捷菜单中选择“删除”命令，在随后出现的“删除对象”对话框中单击“确定”按钮，即可完成对指定数据库的删除操作。
2. 使用TSQL语言中的DROP DATABASE语句删除数据库
其语法格式如下： 

DROP DATABASE数据库名
【例59】删除已创建的数据库STUDENTS。

DROP DATABASE STUDENTS
说明： 用户只能根据自己的权限删除用户数据库，不能删除当前正在使用(正打开供用户读/写)的数据库，不能删除系统数据库(msdb、model、master、tempdb)。
习题5
1. 简述数据库的两种存储结构。
2. 数据库由哪几种类型的文件组成？其扩展名分别是什么？
3. 简述SQL Server 2008中文件组的作用和分类。
4. 使用SQL Server Management Studio创建名为inventory（仓库库存）的数据库，并设置数据库主文件名为inventory_data，初始大小为10MB，日志文件名为inventory_log，初始大小为2MB。所有的文件都放在目录“E:＼DATA”中。
5. 删除习题4创建的数据库，使用TSQL语句再次创建该数据库，主文件和日志文件的文件名及存放位置同上。要求： inventory_data最大为无限大，增长速度为20%； 日志文件初始大小为2MB，最大为5MB，增长速度为1MB。
6. 分别使用SQL Server Management Studio和TSQL语句创建数据库Student，要创建的数据库的要求如下： 
数据库名称为Student，包含3个20MB的数据文件、两个10MB的日志文件，创建使用一个自定义文件组，主文件为第一个文件，主文件的扩展名为.mdf，次要文件的扩展名为.ndf； 要明确地定义日志文件，日志文件的扩展名为.ldf； 自定义文件组包含后两个数据文件，所有的文件都放在目录“E:＼DATA”中。