第3章创建和使用数据库.docx

1、第3章 创建和使用数据库第3章 创建和使用数据库在SQL Server 2000中，数据库由包含数据的表集合和其他对象(如视图、索引、存储过程和触发器)组成，目的是为执行与数据有关的活动提供支持。存储在数据库中的数据通常与特定的主题或过程(如学生的相关信息)相关。SQL Server能够支持许多数据库。每个数据库可以存储来自其他数据库的相关或不相关数据。例如，服务器可以有一个数据库存储职工数据，另一个数据库存储与产品相关的数据。企业管理器是用于查看、建立和删除数据库的主要工具。本章主要介绍使用企业管理器查看数据库属性、创建和删除数据库等内容。3.1查看数据库SQL Server数据库由以下几个

2、部分组成：关系图、表、视图、存储过程、用户、角色。在建立一个实际的数据库之前，需要了解这些组成部分。本节介绍使用企业管理器查看数据库的属性，包括表的结构和内容、关联图、视图、存储过程和用户角色等。3.1.1查看数据库打开SQL Server的企业管理器，如图3.1所示。可看到其操作界面和Windows的资源管理器类似，左侧窗口为层状的树型结构，右侧窗口显示左侧窗口中选择对象的相关信息。整个结构的最上层为Microsoft SQL Servers，表示所有的SQL Server；下面划分为组，称为SQL Server组，每一组可以包含多台计算机。但是对于新安装的SQL Server而言，只包含一

3、个SQL Server组和一个SQL Server服务器(其中的NO1为当前计算机名称)。提示：可以通过在“SQL Server组”上或者在NO1上面右击鼠标，在打开的快捷菜单上，选择“新建SQL Server组”来添加一个SQL Server组，也可以选择“新建SQL Server注册”来向组中添加一个SQL Server服务器。图3.1企业管理器展开选择的服务器的“数据库”选项，可以看到系统提供的数据库，用户自己建立的数据库也会放置在此处。新安装SQL Server后，默认有下面几个内置的数据库(school是用户所建的数据库，不属于内置的数据库)： master数据库 该数据库记录了SQ

4、L Server系统的所有系统级别信息。它记录所有的登录帐户和系统配置设置。它还记录所有其他的数据库，其中包括数据库文件的位置。master数据库记录SQL Server的初始化信息，它始终有一个可用的最新master数据库备份。注意：强烈建议不要在master数据库中创建任何用户对象(如表、视图、存储过程或触发器)。master 数据库包含系统表，这些系统表存储 SQL Server 所用的系统信息(如配置选项设置)。 tempdb数据库 该数据库保存所有的临时表和临时存储过程。它还满足任何其他的临时存储要求，例如存储SQL Server生成的工作表。该数据库是全局资源，所有连接到系统的用户

5、的临时表和存储过程都存储在该数据库中。该数据库在SQL Server每次启动时都重新创建，因此该数据库在系统启动时总是干净的。临时表和存储过程在连接断开时自动除去，而且当系统关闭后将没有任何连接处于活动状态，因此tempdb数据库中没有任何内容会从SQL Server的一个会话保存到另一个会话。注意：默认情况下，在SQL Server运行时，tempdb数据库会根据需要自动增长。不过，与其他数据库不同，每次启动数据库引擎时，它会重置为其初始大小。如果为tempdb数据库定义的大小较小，则每次重新启动SQL Server时，将tempdb数据库的大小自动增加到支持工作负荷所需的大小这一工作可能会

6、成为系统处理负荷的一部分。为避免这种开销，可以使用ALTER DATABASE增加tempdb数据库的大小。ALTER DATABASE将在后面介绍。 model数据库 该数据库用作在系统上创建的所有数据库的模板。当创建数据库时，新数据库的第一部分通过复制model数据库中的内容创建，剩余部分由空页填充。由于SQL Server每次启动时都要创建tempdb数据库，model数据库必须一直存在于SQL Server系统中。 msdb数据库 该数据库提供SQL Server代理程序调度警报和作业以及记录操作员时使用。 pubs数据库和Northwind数据库 这是SQL Server提供的两个范

7、例数据库，前者是一个书籍出版公司的数据库范例，后者是一个称为NorthWind Trdaders公司的销售数据库，该数据库包含从世界各地进出口各种食物的记录。利用企业管理器可以查看数据库的内容，包括数据库的所有者、建立时间、大小、可用空间、表和索引等。下面以Northwind数据库为例，来介绍数据库内容的查看。具体操作步骤如下：(1) 在左侧窗口中，选择Northwind数据库，并在上面右击鼠标，将鼠标指向“查看”进而选择“任务板”。右侧窗口即可显示出数据库的“常规”内容，如图3.2所示。提示：将鼠标移到每一个选项的图标上面，即可弹出一个莱单，利用上面的命令即可完成常见的操作任务，例如新建数据

8、库、新建用户、修改数据库大小、导入导出数据库等。(2) 要查看数据库中表的信息，可在右侧窗口中单击“表信息”选项卡，右侧窗口中即可显示出表的信息，如图3.3所示。在右侧窗口的底部，显示了不同图标所表示的含义。例如，PK_Categories就是一个聚集索引，而CategoryName就是一个普通的非聚集索引。在右侧窗口中，还显示出了表的大小和表所包含的记录数目。例如，表dbo.Categories包含8笔记录，大小为160K；而表dbo.Customers包含91笔记录，大小为168K。(3) 在右侧窗口中，单击“向导”选项卡，可打开用于对数据库进行操作的各种向导。使用这些向导，可以创建数据库

9、，创建视图，打开查询分析器等。图3.2 数据库的“常规”内容图3.3显示数据库包含表的信息3.1.2查看关系图关系图是用来记录数据库中表之间的相互关联情况的。在SQL Server中，一个数据库可以有多个关系图。查看关系图的步骤如下：(1) 在“企业服务器”对话框左侧的窗口中选择“关系图”，此时，右侧窗口中将出现己有关系图的图标(如果没有图标，说明该数据库没有建立关系，有关建立数据库中各表之间关系的方法在后面介绍)。在上面右击鼠标，在弹出的快捷菜单上，选择“设计关系图”，如图3.4所示。图3.4 打开关系表(2) 此时，会出现Northwind数据库中的关系图窗口，如图3.5所示。其中每一个方

10、框就代表一个表，而它们之间像链子一样的连接表示两个表间的关系。其中，钥匙图标表示该字段为主键。该图中表示Employees表与EmployeeTerritor表之间的关系。(3) 在关系上右击鼠标，弹出一快捷菜单，选择“属性”，可查看关系的详细信息，如图3.6所示。提示：在关系上右击鼠标弹出的快捷菜单中，如果执行“显示关系标签”命令，则可以在图3.5所示的窗口中显示出关系的名字。如果执行“从数据库中删除关系”命令，则可以删除关系。图3.5关系图窗口图3.6关系的详细信息3.1.3查看表的结构和内容表是数据库中存放数据的地方，在企业管理器中，可以很方便地查看表的结构和表的内容。具体操作步骤如下：

11、(1) 在企业管理器中，展开Northwind数据库，选择“表”，在右侧的窗中列出Northwind数据库包含的所有表，类型为“系统”的表为系统表，在数据库被新建立时就自动产生，类型为“用户”的表为用户表，是由用户建立的表。为了查看表Customers的结构，将鼠标指针移到Customers表名称上，单击鼠标右击，在出现的快捷菜单中选择“设计表”命令，如图3.7所示。图3.7单击“设计表”命令(2) 此时，会打开Customers表的结构窗口，如图3.8所示，上方窗口中列出该表各字段(列)的字段名、数据类型、长度和字段是否可以为空等属性，在下方列出当前字段的其他属性设置。图3.8 Employ

12、ees表的结构提示：新建一个表也是通过这个窗口来完成的，也可以在建立一个表后，通过此窗口来修改表的结构。(3) 要查看表的记录，在图3.7所示的快捷菜单上，将鼠标移到“打开表”，进而选择“返回所有行”命令，即可查看选项表的内容。Customers表中的记录如图3.9所示。图3.9 Customers表的记录3.1.4查看视图视图(Views)是一种虚拟表，它的所有数据均来自表，本身并不存储数据。视图的记录数据由某些表(一般是多个)的某些字段组成。视图的查看和表的查看类似，具体步骤如下：(1) 在企业管理器中，展开“数据库”文件夹。选择“视图”，然后在右侧窗口中选择要查看的视图(这里查看Orde

13、rs Qry视图)，并右击鼠标。在弹出的快捷菜单上选择“设计视图”命令，如图3.10所示。图3.10查看视图结构(2) 此时，可打开视图的结构(设计)窗口，如图3.11所示。上面部分显示了视图所包含的表，表中每一个字段的前面有一个复选框，处于选中状态表示视图包含该字段，该视图包含两个表字段所在的表。中间部分是该视图包含的字段名。下面部分是建立该视图的SQL语句。图3.11视图的结构(3) 如果要查看视图的内容，关闭上面的视图结构窗口，然后在图3.10所示的快捷菜单上，将鼠标指向“打开视图”，然后选择“返回所有行”命令，即可看到视图的内容，如图3.12所示。图3.12视图内容注意：由图3.12可

14、看到，视图看起来和表一样，但是它的数据是来自所使用的表，本身并不存储数据。3.1.5查看存储过程存储过程是预先使用SQL语言编写的，经过SQL Server编译后，存储在SQL Server中的程序，因此执行效率比较高，而且可以重复调用。下面仍以Northwind数据库为例，介绍存储过程的查看。具体操作步骤如下：(1) 在企业管理器中，展开Northwind数据库，选择“存储过程”选项。(2) 在右侧窗口中双击要查看的存储过程，例如SalesByCategory存储过程，即可打开该存储过程的属性对话框，如图3.13所示。图3.13 存储过程SalesByCategory的属性对话框提示：系统内

15、置了很多的存储过程以及扩充存储过程，可以使用这些存储过程来协助维护数据库，也可以使用自己创建的存储过程存储过程的创建、使用，将在第9章详细介绍。3.1.6查看用户和角色用户(User)是对数据库有存取权限的使用者。角色(Roles)是指一组数据库用户的集合(和Windows中的用户组类似)。数据库中的角色可以根据需要添加。用户如果被加入到某一角色，则将具有该角色所拥有的权限。查看数据库的用户和角色的具体操作步骤如下：(1) 在企业管理器中，展开Northwind数据库，选择“用户”选项，此时，右侧窗口中出现数据库中的用户，如图3.14所示。图3.14 Northwind数据库的用户由该图中可以

16、看到，Northwind数据库目前只有两个用户：guest和dbo。其中dbo的登录名为sa。(2) 双击dbo用户，即可打开dbo用户的属性对话框，如图3.15所示。上部显示了用户使用的登录名以及用户名，在下面的“数据库角色成员”列表框中，可以设置用户所具有的权限。提示：在“数据库角色成员”列表框中，显示的是数据库中所包含的角色，每一角色前面有一复选框，如果选中，表示用户具有该角色所拥有的权限。(3) 要查看数据库的角色，可在左侧窗口中选择“角色”选项。右侧窗口中即可显示出数据库所包含的角色，如图3.16所示，图中列出了默认的10个数据库角色。(4) 双击要查看的数据库角色，例如public

17、，可显示角色的属性，如图3.17所示。图3.15 用户dbo的属性图3.16 Northwind数据库的角色图3.17 public角色的属性对话框(5) 单击“权限”按钮，可打开对话框，用来设置角色的权限，如图3.18所示，列出对象名称、对象的所有者和对象所有者设置对此对象的权限。 图3.18设置角色权限3.1.7数据库的其他组成部分除了上面介绍的数据库组成部分外，还包含下面几个部分： 规则(Rules) 用来限制表字段的数据范围，例如限制的字段的值只能在50100之间。 默认(Defaults) 用来设置默认值，当此默认值与字段绑定时，若字段没有指定数据时，则使用该默认值作为字段的数据。

18、用户定义的数据类型(User Defined Data Type) 除了系统给定的数据类型外，用户可以根据自己的需要定义数据类型。 用户定义的函数(User Defined Function) 用户自定义的函数。 全文目录(Full-Text Catalogs) 存放数据库中表字段的全文索引目录，可方便搜索较长的字符串字段数据。3.2 SQL Server数据库和文件在讨论创建数据库之前，先介绍SQL Server数据库和文件的一些基本概念，它们是理解和掌握创建数据库过程的基础。3.2.1 文件和文件组1数据库文件SQL Server 2000采用操作系统文件来存放数据库，数据库文件可分为主数

19、据文件、二级数据文件和事务日志文件共3类。(1) 主数据文件(Primary)主数据文件用来存放数据，它是所有数据库文件的起点(包含指向其他数据库文件的指针)。每个数据库都必须包含也只能包含一个主数据文件。主数据文件的默认扩展名为.MDF。例如，学生管理系统的主数据文件名为“school_Data.MDF”。(2) 二级数据文件(Secondary)二级数据文件也用来存放数据。一个数据库中，可以没有二级数据文件，也可以拥有多个二级数据文件。二级数据文件的默认扩展名为.NDF。以上两种文件在以后的章节中统一称为数据文件。数据文件是SQL Server 2000中实际存放所有数据库对象的地方。正确

20、设置数据文件是创建SQL Server数据库过程中最为关键的一个步骤，一定要仔细处理。由于所有的数据库对象都存放在数据文件中，所以数据文件的容量更要仔细斟酌。设置数据文件容量的时候，要考虑到未来数据库使用中可能产生的对数据容量的需求，以便为后来增加存储空间留有余地。但另一方面，由于数据文件越大，就需要SQL Server腾出越多的空间去管理它，因此数据文件也不宜设置过大。(3) 事务日志文件(Transation Log)事务日志文件用来存放事务日志。每个数据库都有一个相关的事务日志事务日志记录了SQL Server所有的事务和由这些事务引起的数据库的变化。由于SQL Server遵守先写日志

21、再进行数据库修改的规则，所以数据库中数据的任何变化在写到磁盘之前，这些改变先在事务日志中做了记录。每个数据库至少有一个日志文件，也可以拥有多个日志文件。日志文件的默认扩展名为.LDF。例如，学生管理系统的日志文件名为“School_Log.LDF”。日志文件是维护数据完整性的重要工具。如果某一天，由于某种不可预料的原因使得数据库系统崩溃，但仍然保留有完整的日志文件，那么数据库管理员仍然可以通过日志文件完成数据库的恢复与重建。2数据库文件组为了更好地实现数据库文件的组织，从SQL Server 7.0开始引入了文件组(FileGroup)的概念，即可以把各个数据库文件组成一个组，对它们整体进行管

22、理。通过设置文件组，可以有效地提高数据库的读写速度。例如，有3个数据文件分别存放在3个不同的物理驱动器上(C盘、D盘、E盘)，将这3个文件组成一个文件组。在创建表时，可以指定将表创建在该文件组上，这样该表的数据就可以分布在3个盘上。当对该表执行查询操作时，可以并行操作，大大提高了查询效率。SQL Server 2000提供3种文件组类型，分别是主文件组(Primary )、自定义文件组(user_defined)和默认文件组(default)： 主文件组 包含主数据文件和所有没有被包含在其他文件组里的文件。数据库的系统表都被包含在主文件组里。 自定义文件组 包含所有在使用CREATE DATA

23、BASE或ALTER DATABASE时使用FileGroup关键字来进行约束的文件。 默认文件组 容纳所有在创建时没有指定文件组的表、索引，以及text、ntext和image数据类型的数据。在创建数据库文件组时，必须要遵循以下规则。(1) 一个文件或文件组只能被一个数据库使用。(2) 一个文件只能属于一个文件组。(3) 数据和事务日志不能共存于同一个文件或文件组上。(4) 日志文件不能属于文件组。3.2.2 数据库存储结构一个数据库创建在物理介质的NTFS分区或者FAT分区的一个或多个文件上。在创建数据库时，同时会创建事务日志。事务日志是在一个文件上预留的存储空间，在修改写入数据库之前，事

24、务日志会自动记录对数据库对象所做的所有修改。存储数据的文件称为数据文件(Data File)，存储日志的文件称为日志文件(Log File)。在创建一个数据库时，只是创建了一个空壳，必须在这个空壳中创建对象，然后才能使用这个数据库。在创建数据库对象时，SQL Server会使用一些特定的数据结构给数据对象分配空间，即盘区和页面。它们和数据库及其文件间的关系如图3.19所示。图3.19 数据库的存储结构数据库的物理存储对象是页面和盘区，这两个概念可以用来估算数据库所占用的空间，因此作为一个数据库管理员，了解这方面的知识还是很有必要的。1页面SQL Server中的所有信息都存储在页面(page)

25、上，页面是数据库中使用的最小数据单元。每一个页面存储8KB(8192字节)的信息。所有的页面都包含一个132个字节的页面头，这样就留下8060字节存储数据。页面头被SQL Server用来惟一地标识存储在页面中的数据。SQL Server使用如下几种类型的页面： 分配页面 用于控制数据库中给表和索引分配的页面。 数据和日志页面 用于存储数据库数据和事务日志数据。数据存储在每个页面的数据行中，每一行的最大值为8060个字节。SQL Server不允许跨页面存储。 索引页面 用于存储数据库中的索引数据。 分发页面 用于存储数据库中有关索引的信息。 文本/图像页面 用于存储大量的文本或者二进制大对象

26、(BLOB)，例如图像。2盘区盘区(extent)是由8个连续的页面组成的数据结构，大小为8 X 8KB=64KB。当创建一个数据库对象时，SQL Server会自动以盘区为单位给它分配空间。每一个盘区只能包含一个数据库对象。盘区是表和索引分配空间的单位，如果在一个新建的数据库中创建一个表和两个索引，并且表中只包含一笔记录，则总共占用364KB =192KB的空间。提示：所有的SQL Server数据库都包含这些数据库结构，简单地说，一个数据库是由文件组成，文件是由盘区组成，盘区是由页面组成。3.2.3事务日志在创建数据库的时候，事务日志也会随着被创建。事务日志存储在一个单独的文件上。在修改写

27、入数据库之前，事务日志会自动记录对数据库对象所做的修改。这是SQL Server的一个重要的容错特性，它可以有效地防止数据库的损坏，维护数据库的完整性。1事务日志的创建在创建一个数据库的时候，必须指定存储这个数据库的文件。对于事务日志同样也需要指定存储该事务日志的文件。从SQL Server 7.0开始，日志和数据必须分开存储，这样做有下面几个优点： 事务日志可以单独备份。 在服务器失效的事件中有可能将服务器恢复到最近的状态。 事务日志不会抢占数据库的空间。 可以很容易地检测事务日志的空间。 在向数据库和事务日志中写入时会较少产生冲突，有利于提高性能。2事务日志提供容错的机制在SQL Serv

28、er中，事务是指一次完成的操作的集合，虽然一个事务中可能包含了很多的SQL语句，但是在处理上，它们就像是一个操作一样。为了维护数据库的完整性，它们必须彻底完成或者根本不执行。如果一个事务只是部分执行，则数据库将受到损坏。SOL Server使用数据库的事务日志来防止没有完成的事务破坏数据。具体步骤如下：(1) 用户执行修改数据库对象的任务。(2) 当这个事务开始时，在事务日志中会记录一个事务开始标志，并将与此操作相关的数据读入缓冲区。(3) 在日志中记录每一个操作，然后在日志中记录一个提交事务的标志。每一个事务都会以这种方式记录在事务日志中，这些记录立即写到硬盘上。(4) 在缓冲区中修改响应的

29、数据。这些数据一直在缓冲区中，直到检查点进程发生(定期发生)，才会写到硬盘上。同时，也会在事务日志中写入“所有己经完成的事务已经作用于数据库”，即在事务日志中写入一个检查点标志。这个标志用于在数据库恢复过程中确定哪个事务已经作用于数据库了。如果服务器在己经完成了这个事务(这些事务的操作信息己经写入事务日志中)但还没有将缓冲区中的数据写入物理硬盘的情况下(检查点进程尚未触发)失效了，或者在服务器恰好处理了部分事务的情况下数据库服务器失效了，那么在这两种情况下，数据库都不会被破坏。在服务器恢复正常后，SQL Server会开始一个恢复过程，检查数据库和事务日志，如果事务日志中的事务还没有在数据库中

30、生效，则会在此时作用于数据库(前滚)；如果发现部分事务还没有完成，则将这个事务在数据库中的作用去掉(回滚)。这个过程是自动进行的。数据库完整性信息都由事务日志来完成，从而从本质上增强了SQL Server容错性能。注意：由于要保留所有的事务，因此事务日志会不断地增长备份事务日志会截断，从而为更多的事务提供空间。3.3 创建数据库在SQL Server中，创建一个数据库，仅仅是创建了一个空壳，它是以model数据库为模板创建的，因此其初始大小不会小于model数据库的大小。建立数据库的方法不只一种，可以使用企业管理器直接建立，也可以使用SQL Server提供的向导来创建。至于如何使用Trans

31、act-SQL命令来创建数据库，则在后面第5章中介绍。3.3.1直接建立数据库直接建立数据库是在企业管理器中进行的，大多数情况下，应该使用这种方式来创建一个数据库，因为图形化界面比Transact-SQL更容易使用。创建新数据库的具体操作步骤如下：(1) 打开企业管理器，依次展开Microsoft SQL Servers、SQL Server组和NO1。选择“数据库”文件夹，在上面右击鼠标，在弹出的快捷菜单上选择“新建数据库”命令，如图3.20所示。或者，选择“数据库”选项后，执行“操作”菜单中的“新建数据库”命令，亦可新建数据库。(2) 此时，会打开“数据库属性”对话框。在“名称”文本框中输

32、入新建数据库的名字，例如company，如图3.21所示。要求在此输入的数据库名称必须符合SQL Server命名规范。在“排序规则名称”下拉列表框中，可以选择要使用的排序规则。不过，大多数情况下，选择“(服务器默认设置)”即可满足要求。(3) 单击“数据文件”选项卡，打开“数据文件”对话框。其中可以设置数据文件的名称、位置及大小，如图3.22所示。数据库名称默认是“数据库名_Data”(这里为company_Data)，用户可以修改这个名称，而且，可以指定多个文件。在“位置”一栏中，可以通过单击“.”按钮来指定文件所在的位置。在“初始大小”一栏中，以MB为单位输入数据库文件的大小。该选项卡的下面，可以选择文件是否自动增长和是否有最大