《0Access创建多表查询和交叉表查询》由会员分享，可在线阅读，更多相关《0Access创建多表查询和交叉表查询（26页珍藏版）》请在金锄头文库上搜索。
1、第10章创建多表查询和交叉表查询本章要点 介绍表的连接 使表的所有字段可访问 连接表以创建多表查询 在多个记录上计算 使用表中的查找字段 设计参数查询 向表或者查询添加子数据表 创建交叉表查询 外连接、自连接和t h e t a连接 从其他数据库中的表创建查询 用查询更新表数据 疑难解答 10.1 介绍表的连接 获取A c c e s s的目的无疑是为了利用其关系数据库管理能力。为此，你必须能够基于具有相同值的关键字字段链接相关的表这个过程在数据库术语当中称之为连接。第8章“设计Access 查询”和第9章“理解查询操作符和表达式”向你展示了如何创建基于单个表的简单查询。如果你曾试着运行了第9
2、章中的例子，那么你可以算是已经见过多表查询了。当创建查询测试表达式时，你曾将Order Details表连接到O r d e r s表，然后将O r d e r s表连接到C u s t o m e r s表。本章的第一部分专门讨论从通过连接关联在一起的多个表创建的查询。本章提供了使用在查询设计视图中创建的四种基本连接类型的各种查询的例子，这四种连接为等值连接、外连接、自连接和t h e t a连接。但是，注意有两种查询类型无法在A c c e s s的“查询设计”窗口中创建：联合查询和基于表的子查询。要创建这些查询类型，必须编写S Q L语句这是第2 3章“结构化查询语言”中的主题。本章中的
3、某些示例查询使用的是在第4章“A c c e s s数据库和表”中创建的Personnel Actions表。如果你没有创建Personnel Actions表，则可以参考第4章“创建Personnel Actions表”一节中关于如何建立或者导入该表的指导。其他的示例查询建立在前面章节创建的查询之上。因此，你将发现，按照查询在本章的出现顺序，顺序地阅读本章和创建示例查询，将比采用随机的方法更为高效一些。本章还描述了可以用A c c e s s创建的五种查询中的四种和它们的一些例子，四种查询为：选择、合计、参数和交叉表查询。操作类的四种查询类型可以用来创建或者修改表中的数据：创建、追加、删除和
4、更新。第11章“用操作查询修改数据”中提供了每种操作查询类型的典型应用和它们各自的一些例子。如下的Access 2000新特征适用于本章的主题内容： 新的查询属性子数据表名称、链接子字段、链接主字段、子数据表高度和子数据表扩展可以在查询结果集中容纳子数据表。 现在已经可以打印“关系”窗口的内容当“关系”窗口拥有焦点时，选择“文件”，“打印关系”，从“关系”窗口的内容创建一个报表，然后在“打印预览”模式显示该报表，之后便可以打印出该报表。 将关系图表以“报表快照”格式作为电子邮件的一个附件进行发送接收者必须安装有第10章创建多表查询和交叉表查询215215Access 2000或者“报表快照”阅
5、读器。 10.2 连接表以创建多表查询 在创建表之间的连接之前，必须知道哪些或哪个字段具有相同的值，可以建立相关关系。正如在第4章中曾提到的，在包含相关数据的不同表中，为主键和外部键指定相同的名称是一个常见的设计习惯。M i c r o s o f t在创建Northwind 示例数据库时也使用了这种方法，使得在确定表之间的关系和创建表之间的连接时显得更为容易。例如， C u s t o m e r s表的C u s t o m e r I D字段和O r d e r s表的C u s t o m e r I D字段用于将订单和顾客建立连接。图1 0 - 1给出了N o r t h w i n
6、 d数据库的结构，用图形方式显示了表之间的连接。A c c e s s查询设计表示连接的方法为在不同表的字段名之间连以线段。粗体指示的是主键字段。通常每个连接至少涉及到一个主键字段。图10-1 Northwind 示例数据库中表之间的连接显示Access 2000Northwind数据库表之间的连接结构时，首先使“数据库”窗口具有焦点(按下F 11 )，然后单击工具栏上的“关系”按钮或者选择“工具”、“关系”。图1 0 - 1中显示两表之间连接的线段上的1指示的是一对多关系中“一”的一方;无穷大符号() 指示“多”的一方。在Access 2000中，可以只显示某个表的直接关系(使用工具栏上的“
7、显示直接关系”按钮)或者数据库所有表之间的关系(“显示所有关系”按钮)。在默认情况下N o r t h w i n d . m d b的所有表，当打开Northwind 示例数据库的“关系”窗口时将全部显示出来。这时，单击“显示直接关系”按钮将不起作用。提示只显示某个表的关系时，可单击工具栏的“清除布局”按钮，单击“显示表”按钮，显示“显示表”对话框，在“表”列表中选择要显示的表，然后单击“添加”并“关闭”该对话框。单击“显示直接关系”按钮，显示所选表的关系。清除“关系”窗口的布局不会对后台表之间的关系产生任何影响。“显示直接关系”的特征主要用于包含许多相关表的数据库。A c c e s s在
8、“查询设计”窗口中支持四种类型的连接： 等值连接(也叫做内连接)是创建选择查询时最为常见的连接。等值连接显示某表中的全部记录与另一个表中相应记录。记录之间的对应关系是由连接表的字段中相同的值决定的( S Q L语句：WHERE field1 = field2 )。在大多数情况下，连接都是基于某个表中唯一的主键字段和一对多关系中其他表的外部键字段来完成的。如果担当关系中多方的表中没有记录的字段值与一方表中的记录相对应，则一方中相应的记录将不会出现在查询结果中。如果两个表之间共同的字段名称所对应的字段为其中某个表的主键， A c c e s s将自动地创建表之间的连接。如果在前面曾在关系窗口中指定
9、过表之间的关系，这些连接也会自动地创建。 216216 第二部分最大限度地利用查询 外连接可以用于数据库维护，通过创建一个包含具有唯一值记录的新表，从表中删除孤立记录和复制数据。外连接不管在连接的其他方是否存在与之对应的记录，将显示连接中某成员表中的全部记录。 自连接在单个表之内结合数据。在A c c e s s中创建自连接时，需要向查询添加一个表的复制件( A c c e s s将为复制件提供一个别名)，然后与复制的记录之间创建连接。 T h e t a连接在结合数据时使用的是比较操作符而不是=。Theta 连接包含用于返回不具备某个特定关系的记录的查询所使用的不等号( )。实现theta
10、连接用的是W H E R E准则而不是SQL JOIN 保留字。“查询设计”窗口并不用字段名之间划线的形式指示theta 连接，theta 连接不会显示在“关系”窗口。本书所带光盘 B e c k w i t h文件夹下的B e c k w i t h . m d b数据库具有环状的关系集合。从C D - R O M 或者从硬盘上的复制件打开B e c k w i t h . m d b，然后单击“关系”按钮，打开“关系”窗口(见图1 0 - 2 )。S e c t i o n s和C o u r s e s之间为多对一的关系， D e p a r t m e n t s和C o u r s
11、e s之间为一对多关系，E m p l o y e e s和D e p a r t m e n t s之间为多对一关系，E m p l o y e e s和S e c t i o n s之间为一对多关系。你还可以看到C o u r s e s、E n r o l l m e n t s、S t u d e n t s、G r a d e s和C o u r s e s之间也存在一个环形的关系集。当你想测试具有大量记录的查询性能时， B e c k w i t h . m d b十分有用。这个虚构的Beckwith College位于N a v a s o t a、Te x a s，拥有30 0
12、00名学生，2 320名雇员，在1 4个系的5 9 0门课程中提供了1 770班级。图10-2 Beckwith 示例数据库中表之间的关系注意Beckwith数据库的设计上存在小小的缺陷。一个教授同时属于多个系的情况是极为可能的，但是在图1 0 - 2所示的结构图中却不允许这种情况发生。允许教授隶属于多个系时，需要建立多对多关系和Employee-Departments关系表。10.2.1 创建传统的单列等值连接基于每个表中的一列所建立的连接称为单列连接。下面详细给出了创建一个数据库、使所有查询都使用简单的单列连接时需要满足的基本规则： 关系中一方的每个表必须具有一个主键，并具有无重复索引来维
13、护参照完整性。A c c e s s将自动地在一个表的主键字段上创建无重复索引。 多对多关系，例如O r d e r s到P r o d u c t s的关系，是靠一个中间表来实现的(在这里为O r d e rD e t a i l s )，该中间表和其中某个表之间存在一对多关系( O r d e r s到Order Details)，而和另一个表之间存在多对一关系(Order Details到P r o d u c t s )。第10章创建多表查询和交叉表查询217217 表中存在的重复数据（假设允许重复）被提取到一个具有主键、无重复、与提取重复数据的表具有一对多关系的新表中。使用多列主键来
14、标识提取的数据常常是必须的，因为单个关键字字段可能会包含重复的数据。但是，关键字字段值的结合(也称为串联)必须是唯一的。Access 2000的“表分析器向导”可以自行定位和提取大部分重复数据。我们在第11章将描述如何手工地从表中提取重复数据。如果需要使用生成表查询的帮助，请参见11 . 2节“用生成表查询创建新表”。如果你对如何创建关系还不熟悉，请参见4 . 1 0 . 1节“建立表之间的关系”。N o r t h w i n d数据库中的所有连接，在图1 0 - 1中用相邻表字段名之间的连线标识，都是具有一对多关系的表之间的单列等值连接。A c c e s s使用ANSI SQL-92 保
15、留字INNER JOIN来标识传统的等值连接，用LEFT JOIN或者RIGHT JOIN指定外连接。如果想学习有关S Q L连接的更多术语，请参见2 3 . 4 . 4节“用S Q L创建连接”。在基于等值连接的查询中最为常见的用法便是用顾客名字和地址接收到的订单匹配。例如，你或许希望创建一个简单的报表，列出顾客名字、订单数目、订单日期和数量。遵循如下步骤，创建一个传统的一对多，单列等值连接查询，将N o r t h w i n d的顾客和他们的订单关联起来，并按照公司和订单的日期进行排序：1) 如果N o r t h w i n d . m d b是打开的，关闭除了“数据库”窗口之外的所有
16、窗口；否则打开N o r t h w i n d . m d b。2) 单击“数据库”窗口中的“查询”快捷方式，然后双击在“设计”视图中“创建查询”快捷方式。Access 在空的“查询设计”窗口上显示“显示表”对话框。3) 从显示表列表中选择C u s t o m e r s表，单击“添加”按钮。或者，也可以双击C u s t o m e r s表名字，将该表添加到查询中。A c c e s s将在“查询设计”窗口中添加C u s t o m e r s表的“字段名字”列表。4) 双击“显示表”列表中的O r d e r s表，然后单击“关闭”按钮。A c c e s s将O r d e r s表的“字段名称”列表添加该窗口中，并在两个表的C u s t o m e r I D字段之间添加了一条连线，指示两者之间存在的连接。A c c e s s是自动创建该连接的，因为C u s t o m e r I D是C u s t o m e r s表的主键字段，而A c c e s s在O r d e r s表中发现了具有相同名字的字段
