数据库基础理论.docx

1、数据库基础理论内容提要本书从关系数据库的基本原理出发，以Microsoft SQL Server 7.0为实例，以公司自主开发的系列软件为具体应用示例介绍设计、开发、实现和管理一个关系数据库的整个过程。内容包括数据库的安装和配置、设计和管理、SQL语言基础、创建索引、设计数据完整性、存储过程、触发器、安全性、数据库的备份和恢复、复制方法、数据库的监视和优化等。本书为公司二次开发及服务人员的数据库基础理论培训教材。d序 言随着信息技术的普及，各种各样的应用系统投入到实际工作当中，这些系统大同小异，经常造成用户学习功能雷同的软件、重复采集信息、系统之间频繁转换数据等等，给用户带来相当大的负担和浪费

2、。于是，具备统一架构体系、开放式数据接口的集成化管理软件开发技术应运而生。公司自1998年成立以来，一直致力于先进管理理念、信息技术的引进、开发与推广，为各类行业信息化建设提供整体解决方案设计，包括信息化发展规划、管理制度、标准规范、数据库建设、应用软件开发、项目组织实施等，并自主开发了针对不同行业的架构式平台化的管理软件。由于软件采用了统一的软件架构设计和数据库体系设计，对软件系统的管理、信息动态维护、信息应用等方面均起到了促进作用。本书以公司自主开发的系列软件为具体应用示例介绍设计、开发、实现和管理一个关系数据库的整个过程，内容涉及到数据库的安装和配置、设计和管理、SQL语言基础、索引管理

3、、设计数据完整性、存储过程、触发器、安全性、数据库备份和恢复、复制方法、数据库的监视和优化等方面。本书旨在作为软件二次开发及服务人员的数据库基础理论学习教材，同时，对于软件的使用人员也有相当大的参考价值。第一部分 概述第一章 数据库简介1.1.1. 关系数据库产生的历史数据处理是现代计算机应用中的一个重要组成部分。数据处理是指对各种形式的数据进行分类、组织、编码、存储、检索和维护的一系列活动的总和。其目的是从大量的、原始的数据中抽取、推导出对人们有价值的信息以作为行动和决策的依据；也是为了借助计算机科学地保存和管理复杂的大量的数据，以便人们能方便而充分地利用这些宝贵的信息资源。数据处理随着计算

4、机硬件和软件的发展而不断发展。30多年来经历了三个阶段：人工管理阶段、文件系统阶段、数据库系统阶段。数据库系统又经历了三个阶段：网状数据库、层次数据库和关系数据库。自从IBM公司的E.F.Codd博士系统的提出关系数据库概念以来，出现了众多的关系型数据库系统产品，如：ORACLE、Informix、SYBASE、微软的SQL Server、IBM的DB2等。1.1.2. 关系数据库的定义E.F.Codd 在1976年6月发表了“关于大型共享数据库数据的关系模型”论文，首先概述了关系数据模型及其原理，并把它用于数据库系统中。他指出，关系型数据库是指一些相关的表和其他数据库对象的集合。这个定义表达

5、了三部分含义。第一，在关系数据库中，信息被存放在二维表格结构的表（table）中，一个关系数据库包含多个数据表，每一个表又包含行（记录）和列（字段、指标）。可以将表想象为一个电子表格，其中，和行对应的是记录，和列对应的是字段。大多数数据库都有多个表（请参见表1-1）；第二，这些表之间是相互关联的，表之间的这种关联性是由主键和外键所体现的参照关系实现的；第三，数据库不仅仅包含表，而且包含了其他数据库对象，如：视图、存储过程、索引等等。表1-1 联系方式编号姓名部门分机手机1王一交通80813*8082王二人事81013*810E.F.Codd把数学法则用于数据库领域，使关系模型成为数学化模型。关

6、系是表的数学术语，表是一个集合，因此，集合论、数理逻辑等知识可以引入到关系模型中来。E.F.Codd指出，关系模型与当时流行的网状模型和层次模型的最大差别是：关系模型用表格而不是通过指针链来表示和实现实体间的联系。另外，关系模型的数据结构简单灵活，易学易用，便于掌握和使用。因此，关系数据模型很快就流行起来，各个数据库厂商纷纷研制出自己的关系数据库管理系统及与之配套的应用开发工具，为关系数据库的应用提供了一种完整的解决方案。在计算机中的关系数据库元素的术语，同数学领域和日常数据处理领域中的术语之间，有一些区别。表1-2列出了关系数据库元素的3个术语集，及用于其它领域的相似术语。表1-2 关系数据

7、库术语与其它术语的比较数据库数学日常数据处理表（Table） 关系（Relation） 文件（File） 行（Row） 元组（Tuple） 记录（Record） 列（Column） 属性（Attribute） 字段（Field） 1.1.3. 规范化理论对于一个数据库而言，如果关系建立不好的话，不能很好地反映现实世界，容易引起各种异常。为了消除这些异常，人们采用分解的方法，力求使关系的语义单纯化，这就是所谓关系的规范化。由于关系的规范化的要求不同，出现了不同的范式（规范模式），分别为1NF、2NF、3NF、BCNF、4NF各5NF，其实质就是一个关系“拆”的过程。从实际的应用上看，一个数据库只

8、要能够满足3级范式即可。第一范式(1NF)定义为关系R是1NF，当且仅当所有的基础域仅包含原子值，即：要求所有属性的数据均为不可再分的数据项，实体中的某个属性不能有多个值或者不能有重复的属性。如果出现这种情况，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。第二范式(2NF)定义为关系R是2NF，当且仅当R是1NF，并且所有非PK属性完全依赖于PK，即：要求实体的属性完全依赖于唯一标识符。所谓完全依赖是指不能存在仅依赖PK一部分的属性，如果存在，那么这个属性和PK的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。第三范式(3NF)定

9、义为关系R是3NF，当且仅当R是2NF，并且所有非PK属性都是非传递地依赖于PK，即：要求属性之间不存在传递依赖。传递依赖是指实体中的某个属性a依赖于另外一个属性b，而且b并不是PK的一部分。1.1.4. 客户/服务器系统结构SQL Server既能够满足2层客户/服务器系统数据库结构，又能够适合3层客户/服务器系统数据库结构。我公司自主开发的各类软件基本以2层为主（简称C/S结构，如图1-1）。图1-1 2层客户/服务器系统数据库结构客户/服务器系统又被称作分布式计算系统，它的含义是程序的数据处理并不像通常在基于小型机或基于主机的计算机系统（终端方式）中那样在单个的计算机上发生，而是把程序的

10、不同部分在多台计算机上同时运行。例如，对于SQL Server来讲，将数据存放在服务器计算机上，客户端界面作为程序的另一部分（完成商业逻辑和显示逻辑）存在于客户端桌面计算机上。客户/服务器系统的这两个部件通过网络连接相互通信，并且可以扩展到任意规模。客户端通过网络向服务器发送SQL语句，服务器返回客户端结果集（见图1-2）。图1-2 客户/服务器模式在2层次结构中，桌面客户机应用程序对在局域网上的数据库服务器发出数据请求。这种结构适用于决策支持应用程序。在决策支持应用程序中，由客户机向数据库服务器发出的单个查询请求可以得到大量的数据用于桌面客户机随后的分析、处理和展示。这种结构也适用于局部化低

11、事务处理率的OLTP程序。两层次结构这个名字也反映了一个事实：只牵扯到两个参与者应用程序客户机和数据服务器。在3层次结构中（见图1-3），每一级都支持应用程序的一个独立部分。应用程序客户机完成显示逻辑（仅仅显示数据或接收用户输入的信息），应用程序服务器完成商业处理逻辑，而数据服务器则完成数据存取逻辑。在一个事务处理过程中，每一个客户机只向应用程序客户机发出一个请求，这就减少了网络通信和竞争。每个应用程序的商业逻辑部分是由该应用程序的所有用户共享的，这样就能更好地控制商业处理，能够极大地简化变化的实现。另外，应用程序服务器和数据库之间是一定数目的常连接（用户不必做连接数据库和断开数据库操作）。而

12、且3层次结构模型的特性是客户机请求应用程序服务而不是数据请求。所以，随着应用程序和用户的增加，3层次结构更加有效并且容易扩展。图1 - 8简单比较了2层次和3层次结构的扩展性区别。3层次结构中的应用服务器产品很多，如：微软公司的MTS、IBM公司的CICS、BEA公司的TUXEDO等。具体这方面的知识可参见其它相关书籍。图1-3 3层客户/服务器系统数据库结构图1-4 2层和3层的区别1.1.5. 习题1、 请简述“数据”与“信息”的区别。第二章 安装、配置1.2.1. 安装我公司使用的数据库为Microsoft允许应用程序开发人员随其应用程序分发的SQL Server 7.0关系数据引擎的副

13、本（简称“MSDE”），主要由SQL Server 7.0桌面引擎和Microsoft数据引擎组成，与其它版本最明显的区别是没有提供跟踪器、企业管理器、查询分析器等用于进行数据库开发的实用工具。由于SQL Server 7.0桌面引擎中数据库引擎要求，桌面引擎数据库的大小不能超过2GB。有关安装的详细操作请查阅软件使用手册。1.2.2. 配置MSDE初次安装到机器上时，一般情况可以直接进行使用而不需要进行任何的配置，除此之外，以下的几种特殊情况需要注意：1、当SQL Server在Windows 98上运行时，它不支持服务器命名管道Net-Library。如果SQL Server运行在 Win

14、dows 98 服务器上，则必须将客户端的默认Net-Library更改为TCP/IP套接字或多协议。2、当使用来自SQL Server 7.0或其早期版本的SQL Server客户端连接组件时，在连接到SQL Server 2000的命名实例之前，必须使用客户端网络实用工具设置别名。例如，若要在SQL Server 7.0客户端上连接SQL Server 2000命名实例，必须添加指向 computernamepipeMSSQL$instancenamesqlquery 的别名。如果使用别名 computernameinstancename，客户端可按SQL Server 2000客户端所做

15、的那样，通过指定该别名进行连接。对于 TCP/IP 套接字和 NWLink IPX/SPX Net-Library，则必须使用客户端网络实用工具在客户端定义别名，该客户端指定了端口地址，在该端口地址上可以监听命名实例。1.2.3. 习题第二部分 编程技术第一章 创建和管理数据库2.1.1. 数据库存储结构2.1.1.1. 数据库和文件一个数据库创建在物理介质（如硬盘）的NTFS分区或者FAT分区的一个或多个文件上，它预先分配了将要被数据库和事务日志所使用的物理存储空间。存储数据的文件叫做数据文件或物理文件，存储日志的文件叫做日志文件，SQL Server默认文件扩展名分别为mdf、ldf，我公

16、司的分别为DAT、LOG。这些文件用来存储数据库对象和数据（见图2-1）。在您创建一个新的数据库的时候，仅仅是创建了一个空壳，必须在这个空壳中创建对象（如表等），然后才能使用这个数据库。在创建数据库对象时，SQL Server会使用一些特定的数据结构给数据库对象分配空间，即盘区和页面（见图2-1）。图2-1 数据库是由页面和盘区组成1、什么是页面？SQL Server中的所有信息都存储在页面（page）上，页面是数据库中使用的最小数据单元。每一个页面存储8KB（8192字节） 的信息，所有的页面都包括一个132字节的页面头，这样就留下了8060字节存储数据。页面头被SQL Server用来唯一

17、地标识存储在页面中的数据。SQL Server使用如下类型的页面： 分配页面：用于控制数据库中给表和索引分配的页面。 数据和日志页面：用于存储数据库数据和事务日志数据。数据存储在每个页面的数据行中。每一行大小的最大值为8060个字节。SQL Server不允许记录跨页面存储。 索引页面：用于存储数据库的索引数据。 分发页面：用于存储数据库中有关索引的信息。 文本/图像页面：用于存储大量的文本或二进制大对象（BLOB）。2、什么是盘区？一个盘区（extent）是由8个连续的页面（88KB=64KB）组成的数据结构。当创建一个数据库对象（如一个表）时，SQL Server会自动地以盘区为单位给它分

18、配空间。每一个盘区只能包含一个数据库对象。盘区是表和索引分配空间的单位。假设我们在一个新建的数据库中，创建了一个表和两个索引，并且表中只插入了一条记录，那么，总共占用364KB=192KB空间。所有的SQL Server数据库都包含这些数据结构。简单地讲，你只需记住一个数据库是由文件组成的，文件是由盘区组成的，盘区是由页面组成的即可。2.1.1.2. 事务日志使用SQL Server创建一个数据库的时候，同时也会创建事务日志（见图2-1）。事务日志存储在一个单独的日志文件上。在修改写入数据库之前，事务日志会自动地记录对数据库对象所作的所有修改。这是SQL Server的一个重要的容错特性，它可

19、以有助于防止数据库损坏。创建一个数据库的时候，必须要指定存储这个数据库的文件，这一点对于事务日志也同样适用。SQL Server从7.0 版本开始，日志和数据必须分开存放，这样存储的好处有如下几点： 事务日志可以单独地备份。 在服务器失效的事件中有可能将服务器恢复到最近的状态。 事务日志不会抢占数据库的空间。 可以很容易地监测事务日志的空间。 在向数据库和事务日志中写入时会产生较少的冲突，这有利于提高SQL Server的性能。 在SQL Server中，事务是一次完成的操作的集合。虽然一个事务中可能包含了很多SQL语句，但是在处理上，就像它们是同一个操作一样。为了维护数据的完整性，事务必须彻

20、底完成或者根本不执行。如果一个事务只是部分地执行，并作用于数据库（例如由服务器失效导致），那么数据库将被损坏。SQL Server使用数据库的事务日志来防止没有完成的事务破坏数据。下面这个列表将向您讲述SQL Server执行数据修改等操作的步骤，也解释了如何使用事务日志来防止数据被破坏（见图2-2）： 图2-2 数据库系统执行一个修改操作的步骤1)一个用户执行了一个修改数据库中的对象的任务，如：Begin transaction AAdelete insert delete commit transaction AA2)当这个事务开始时，在事务日志中会记录一个事务开始标志。并将与此操作相关的

21、数据读入缓冲。3)在日志中记录每个操作。然后在日志中记录一个提交事务的标记。每一个事务都会以这种方式记录在事务日志中。这些记录立即写到硬盘上。4)在缓冲区中修改相应的数据。这些数据一直在缓冲区中，直到检查点进程的发生，才会写到硬盘上。在检查点进程发生时，也会在事务日志中记录“所有已经完成的事务已经作用于数据库”，即在事务日志中写入一个检查点标志，这个标志用于在数据库恢复过程中确定哪个事务已经作用于数据库了。事务日志不断地增长，保存所有的事务，直到把它备份出来为止。备份时，事务日志会被删除（截断），来为更多的事务提供空间。如果在服务器已经完成了这个事务（这些事务的操作信息已经写入事务日志中），但

22、还没有将缓冲区中的数据写入物理硬盘的情况下（检查点进程尚未触发）失效了，或者在服务器恰好处理了部分事务的情况下数据库服务器失效了，这时，数据库并不会被破坏。当服务器恢复正常后，Server会开始一个恢复过程，它会检查数据库和事务日志，查找还没有作用于数据库的事务以及部分地作用于数据库，但是还没有完成的事务。如果事务日志中的事务还没有在数据库中生效，那么它们会在此时作用于数据库（前滚）；如果发现部分事务还没有完成，就会将这个事务的影响从这个数据库中去除（回滚）。这个恢复的过程是自动进行的，所有用于数据库完整性的信息都由事务日志来维护，这种能力从实质上增强了SQL Server的容错性。2.1.2

23、. 创建和管理数据库2.1.2.1. 系统数据库SQL Server 7.0版在安装完后自动包含6个数据库：master、model、tempdb、pubs、Northwind及msdb，其中pubs、Northwind两个数据库在MSDE版中没有提供。Master数据库是由将服务器安装版本作为一个整体进行跟踪的系统表和随后创建的其他数据库组成的。虽然每个数据库都有一组维护它后含的对象有关信息的系统目录，但是master数据库具有一些系统目录，它们保持着关于磁盘空间、文件分配、使用情况、系统范围的配置设置、登录帐户、其他数据库的存在情况，以及其他SQL服务器（对于分布式操作）的存在情况等信息。

24、Model就是一个模板库。每当您创建新的数据库时，SQL Server就会制作一个model副本，作为新数据库的基础。Tempdb是一个临时工作空间，SQL Server每次重新启动后都会重新创建。它主要用于存储用户明确创建的临时表，用于保持在查询处理和排序期间由SQL Server内部创建的中间结果的工作表，以及用于静态游标的具体表示。Tempdb中的操作都会被记录下来，但是日志中的记录将只包含足够用来回滚事务而不是恢复它的信息，这可以潜在地提高insert语句的性能。Msdb数据库由SQL Server Agent服务使用，该服务将执行象备份和复制任务等这样的计划活动。pubs、North

25、wind是SQL Server提供的示例库。2.1.2.2. 创建数据库利用公司软件创建数据库的方法是运行相关软件的数据恢复，将公司已有的模板库恢复（关于备份及恢复的详细说明见下节）即可。在此有几点需要说明：一是不要轻意的认为执行软件中数据库初始化即可建立一个空的数据库，实际上数据库初始化执行的也是数据库的恢复操作，恢复的是已开发完成的供用户使用的数据库；二是这里提到“空”是相对公司其他已成熟软件而言的，相对于SQL Server这个“空”数据库已经在model库的基础上加入一些特殊的对象。在创建非公司软件使用的数据库时也可通过SQL Server提供的开发工具或通过SQL语句完成，二者的主要

26、别是前者有直观的界面，实际内部工作机制都是一样的，都是简单执行了如下语句：Create database XXX（大括号内为可选的各类参数，详细说明可查看SQL Server帮助）2.1.2.3. 管理数据库1、 备份、恢复无论您对您的数据库系统执行多大的容错级别，都无法取代常规的备份。备份可以为无意或恶意的数据库修改、程序设计错误和自然灾害等提供一种解决方法。如果您选择在损失容错性的情况下为您的数据文件提供最快的速度，那么备份可以保证，以防您的数据文件遭到损坏。从备份重新创建数据库的过程被称为“恢复”，即将系统恢复到备份时的状态。公司软件中提供了可视化的、操作简单的备份及恢复工具，分别为数据

27、备份和数据恢复，其执行的方法与原理与SQL Server提供的企业管理器基本相同，可以通过跟踪器查看具体的后台服务器的执行过程，基本原理分别为“backup database XXX to URL”和“restore database XXX from URL”。此外，公司软件中还提供了信息上报与信息接收两个工具，也可以临时充当备份与恢复的工具。在此说为“临时”的主要原因是二者原理不同：数据备份和数据恢复是对整个数据库的备份和恢复，包括数据库中的所有对象；信息上报与信息接收是对数据库中的各个信息表中的数据进行备份和恢复，而且前提条件是“上报”与“接收”时的信息表结构不能有任何改动。在执行备份与

28、恢复操作时，有一点需要特别的注意：数据库名、物理文件名与逻辑文件名的区别。数据库名是SQL Server对数据库进行区分的名称，这个名称在一个SQL Server系统中是唯一的；物理文件名是指数据库物理文件在驱动器上存储的实际名称；逻辑文件名是指数据库第一次创建时所给定的数据库的内部标识。例如，新建一个数据库，命名为A，此时数据库名为“A”，物理文件名为“A.mdf”，逻辑文件名为“A”（与数据库名相同），同时，在A数据库中建有表a。一周后，为了进行测试，需要再建一个与数据库A相同的数据库B，执行的操作是将数据库A备份，然后利用备份的文件重新恢复了一个数据库B，由于操作人员的失误，数据库B的物

29、理文件名仍然使用了“A.mdf”，此时数据库A和数据库B的物理文件名与逻辑文件名相同（备份、恢复不会改变逻辑文件名）。在进行测试的过程中，在数据库B的a表中输入了的大量的数据，结果发现这些数据也出现在了数据库A的a表中。产生的这个错误的根本原因就在于两个数据库的物理文件名相同，物理文件是实际存储数据的地方，SQL Server是从实际的物理文件中读取数据的。这其中，逻辑文件名对两个数据库没有产生任何的影响，而且工作的很好，即两个不同的数据库可以有相同逻辑文件名。为避免此类事情的发生，在执行备份或恢复操作时建议使用公司软件提供的相关工具。2、 分离、附加数据库您可以使用存储过程sp_detach

30、_db（分离）和sp_attach_db（附加）将数据库移动到新的物理驱动器例如，当您升级硬件时。您可能使用这些存储过程制作一个数据库副本，以用于测试或开发，或者作为备份和恢复的替代做法。一旦数据被分离，就会从master数据库的sysdatabases表中删除它的条目，并且从SQL Server的角度来看，就像您删除了数据库一样。当然，您可以使用drop database命令来删除数据库，但是，使用这个命令有些严重的后果，SQL Server确保在删除数据库之前没有任何人与它连接，但是不会进行任何脏页或打开事务的检查，删除数据库还会从操作系统中删除物理文件，因此，除非您制作了备份，否则数据库

31、将会一去不复返。公司软件控制台中提供有详细的关于附加数据库的操作说明及步骤。2.1.3. 安全性2.1.3.1. 安全构架安全系统的构架建立在用户和用户组的基础上。下图说明Microsoft Windows NT 4.0和Windows 2000中的用户和本地组及全局组可以如何映射到Microsoft SQL Server 中的安全帐户，以及 SQL Server 可以如何独立于Windows NT 4.0和Windows 2000中的帐户来处理安全帐户。CORPUSERS本地组中含有两个用户和一个全局组 Mktg，这个全局组中又含有两个用户。SQL Server允许直接使用Windows N

32、T 4.0和Windows 2000本地组和全局组来组织SQL Server的用户帐户。此外，Windows NT 4.0用户Fred和Jerry虽不属于Windows NT 4.0组，但可以作为 Windows NT 4.0 用户（如 Fred）或作为SQL Server用户（Jerry）直接添加到SQL Server实例中。通过使用角色，SQL Server进一步扩展了上面的模型。角色是为了进行管理而组织的用户组，与 Windows NT 4.0或Windows 2000组相同，但是当等价的Windows NT 4.0或Windows 2000组不存在时，将在SQL Server中创建这些角色。例如Managers角色中包含Windows NT 4.0 Mktg