第10章Access创建多表查询和交叉表查询

第10章创建多表查询和交叉表查询本章要点•介绍表的连接•使表的所有字段可访问•连接表以创建多表查询•在多个记录上计算•使用表中的查找字段•设计参数查询•向表或者查询添加子数据表•创建交叉表查询•外连接、自连接和theta连接•从其他数据库中的表创建查询•用查询更新表数据•疑难解答10.1介绍表的连接获取Access的目的无疑是为了利用其关系数据库管理能力。为此，你必须能够基于具有相同值的关键字字段链接相关的表—这个过程在数据库术语当中称之为连接。第8章“设计Access查询”和第9章“理解查询操作符和表达式”向你展示了如何创建基于单个表的简单查询。如果你曾试着运行了第9章中的例子，那么你可以算是已经见过多表查询了。当创建查询测试表达式时，你曾将OrderDetails表连接到Orders表，然后将Orders表连接到Customers表。本章的第一部分专门讨论从通过连接关联在一起的多个表创建的查询。本章提供了使用在查询设计视图中创建的四种基本连接类型的各种查询的例子，这四种连接为等值连接、外连接、自连接和theta连接。但是，注意有两种查询类型无法在Access的“查询设计”窗口中创建：联合查询和基于表的子查询。要创建这些查询类型，必须编写SQL语句—这是第23章“结构化查询语言”中的主题。本章中的某些示例查询使用的是在第4章“Access数据库和表”中创建的PersonnelActions表。如果你没有创建PersonnelActions表，则可以参考第4章“创建PersonnelActions表”一节中关于如何建立或者导入该表的指导。其他的示例查询建立在前面章节创建的查询之上。因此，你将发现，按照查询在本章的出现顺序，顺序地阅读本章和创建示例查询，将比采用随机的方法更为高效一些。本章还描述了可以用Access创建的五种查询中的四种和它们的一些例子，四种查询为：选择、合计、参数和交叉表查询。操作类的四种查询类型可以用来创建或者修改表中的数据：创建、追加、删除和更新。第11章“用操作查询修改数据”中提供了每种操作查询类型的典型应用和它们各自的一些例子。如下的Access2000新特征适用于本章的主题内容：•新的查询属性子数据表名称、链接子字段、链接主字段、子数据表高度和子数据表扩展可以在查询结果集中容纳子数据表。•现在已经可以打印“关系”窗口的内容当“关系”窗口拥有焦点时，选择“文件”，“打印关系”，从“关系”窗口的内容创建一个报表，然后在“打印预览”模式显示该报表，之后便可以打印出该报表。•将关系图表以“报表快照”格式作为电子邮件的一个附件进行发送接收者必须安装有第10章创建多表查询和交叉表查询215Access2000或者“报表快照”阅读器。10.2连接表以创建多表查询在创建表之间的连接之前，必须知道哪些或哪个字段具有相同的值，可以建立相关关系。正如在第4章中曾提到的，在包含相关数据的不同表中，为主键和外部键指定相同的名称是一个常见的设计习惯。Microsoft在创建Northwind示例数据库时也使用了这种方法，使得在确定表之间的关系和创建表之间的连接时显得更为容易。例如，Customers表的CustomerID字段和Orders表的CustomerID字段用于将订单和顾客建立连接。图10-1给出了Northwind数据库的结构，用图形方式显示了表之间的连接。Access查询设计表示连接的方法为在不同表的字段名之间连以线段。粗体指示的是主键字段。通常每个连接至少涉及到一个主键字段。图10-1Northwind示例数据库中表之间的连接显示Access2000Northwind数据库表之间的连接结构时，首先使“数据库”窗口具有焦点(按下F11)，然后单击工具栏上的“关系”按钮或者选择“工具”、“关系”。图10-1中显示两表之间连接的线段上的1指示的是一对多关系中“一”的一方;无穷大符号(∝)指示“多”的一方。在Access2000中，可以只显示某个表的直接关系(使用工具栏上的“显示直接关系”按钮)或者数据库所有表之间的关系(“显示所有关系”按钮)。在默认情况下Northwind.mdb的所有表，当打开Northwind示例数据库的“关系”窗口时将全部显示出来。这时，单击“显示直接关系”按钮将不起作用。提示只显示某个表的关系时，可单击工具栏的“清除布局”按钮，单击“显示表”按钮，显示“显示表”对话框，在“表”列表中选择要显示的表，然后单击“添加”并“关闭”该对话框。单击“显示直接关系”按钮，显示所选表的关系。清除“关系”窗口的布局不会对后台表之间的关系产生任何影响。“显示直接关系”的特征主要用于包含许多相关表的数据库。Access在“查询设计”窗口中支持四种类型的连接：•等值连接(也叫做内连接)是创建选择查询时最为常见的连接。等值连接显示某表中的全部记录与另一个表中相应记录。记录之间的对应关系是由连接表的字段中相同的值决定的(SQL语句：WHEREfield1=field2)。在大多数情况下，连接都是基于某个表中唯一的主键字段和一对多关系中其他表的外部键字段来完成的。如果担当关系中多方的表中没有记录的字段值与一方表中的记录相对应，则一方中相应的记录将不会出现在查询结果中。如果两个表之间共同的字段名称所对应的字段为其中某个表的主键，Access将自动地创建表之间的连接。如果在前面曾在关系窗口中指定过表之间的关系，这些连接也会自动地创建。216第二部分最大限度地利用查询•外连接可以用于数据库维护，通过创建一个包含具有唯一值记录的新表，从表中删除孤立记录和复制数据。外连接不管在连接的其他方是否存在与之对应的记录，将显示连接中某成员表中的全部记录。•自连接在单个表之内结合数据。在Access中创建自连接时，需要向查询添加一个表的复制件(Access将为复制件提供一个别名)，然后与复制的记录之间创建连接。•Theta连接在结合数据时使用的是比较操作符而不是=。Theta连接包含用于返回不具备某个特定关系的记录的查询所使用的不等号()。实现theta连接用的是WHERE准则而不是SQLJOIN保留字。“查询设计”窗口并不用字段名之间划线的形式指示theta连接，theta连接不会显示在“关系”窗口。本书所带光盘\Beckwith文件夹下的Beckwith.mdb数据库具有环状的关系集合。从CD-ROM或者从硬盘上的复制件打开Beckwith.mdb，然后单击“关系”按钮，打开“关系”窗口(见图10-2)。Sections和Courses之间为多对一的关系，Departments和Courses之间为一对多关系，Employees和Departments之间为多对一关系，Employees和Sections之间为一对多关系。你还可以看到Courses、Enrollments、Students、Grades和Courses之间也存在一个环形的关系集。当你想测试具有大量记录的查询性能时，Beckwith.mdb十分有用。这个虚构的BeckwithCollege位于Navasota、Texas，拥有30000名学生，2320名雇员，在14个系的590门课程中提供了1770班级。图10-2Beckwith示例数据库中表之间的关系注意Beckwith数据库的设计上存在小小的缺陷。一个教授同时属于多个系的情况是极为可能的，但是在图10-2所示的结构图中却不允许这种情况发生。允许教授隶属于多个系时，需要建立多对多关系和Employee-Departments关系表。10.2.1创建传统的单列等值连接基于每个表中的一列所建立的连接称为单列连接。下面详细给出了创建一个数据库、使所有查询都使用简单的单列连接时需要满足的基本规则：•关系中一方的每个表必须具有一个主键，并具有无重复索引来维护参照完整性。Access将自动地在一个表的主键字段上创建无重复索引。•多对多关系，例如Orders到Products的关系，是靠一个中间表来实现的(在这里为OrderDetails)，该中间表和其中某个表之间存在一对多关系(Orders到OrderDetails)，而和另一个表之间存在多对一关系(OrderDetails到Products)。第10章创建多表查询和交叉表查询217•表中存在的重复数据（假设允许重复）被提取到一个具有主键、无重复、与提取重复数据的表具有一对多关系的新表中。使用多列主键来标识提取的数据常常是必须的，因为单个关键字字段可能会包含重复的数据。但是，关键字字段值的结合(也称为串联)必须是唯一的。Access2000的“表分析器向导”可以自行定位和提取大部分重复数据。我们在第11章将描述如何手工地从表中提取重复数据。如果需要使用生成表查询的帮助，请参见11.2节“用生成表查询创建新表”。如果你对如何创建关系还不熟悉，请参见4.10.1节“建立表之间的关系”。Northwind数据库中的所有连接，在图10-1中用相邻表字段名之间的连线标识，都是具有一对多关系的表之间的单列等值连接。Access使用ANSISQL-92保留字INNERJOIN来标识传统的等值连接，用LEFTJOIN或者RIGHTJOIN指定外连接。如果想学习有关SQL连接的更多术语，请参见23.4.4节“用SQL创建连接”。在基于等值连接的查询中最为常见的用法便是用顾客名字和地址接收到的订单匹配。例如，你或许希望创建一个简单的报表，列出顾客名字、订单数目、订单日期和数量。遵循如下步骤，创建一个传统的一对多，单列等值连接查询，将Northwind的顾客和他们的订单关联起来，并按照公司和订单的日期进行排序：1)如果Northwind.mdb是打开的，关闭除了“数据库”窗口之外的所有窗口；否则打开Northwind.mdb。2)单击“数据库”窗口中的“查询”快捷方式，然后双击在“设计”视图中“创建查询”快捷方式。Access在空的“查询设计”窗口上显示“显示表”对话框。3)从显示表列表中选择Customers表，单击“添加”按钮。或者，也可以双击Customers表名字，将该表添加到查询中。Access将在“查询设计”窗口中添加Customers表的“字段名字”列表。4)双击“显示表”列表中的Orders表，然后单击“关闭”按钮。Access将Orders表的“字段名称”列表添加该窗口中，并在两个表的CustomerID字段之间添加了一条连线，指示两者之间存在的连接。Access是自动创建该连接的，因为CustomerID是Customers表的主键字段，而Access在Orders表中发现了具有相同名字的字段(外部键)。5)为了用顾客的名字标识每个订单，可选择Customers表CompanyName字段，并将字段符号拖到“查询设计”网格的第一列的“字段”行。6)选择Orders表的OrderID字段，将字段符号拖到第二列的“字段”行。将OrderDate字段拖到第三列。查询设计现在的状态如图10-3所示。图10-3该查询显示按公司名字和日期排序的顾客签订的订单7)单击“运行”或者“查询视图”按钮，显示查询结果，Recordset如图10-4所示。注意查询结果集的字段标题显示了表字段的标题（其中包含有空格），而不是实际的字段名，实际的字段名中是没有包含空格的。图10-4连接Customers和Orders表的图10-3所示的查询设计所产生的结果10.2.2为查询结果集指定排序次序Access显示查询结果集时是按照主键字段上的索引的次序进行显示的。如果代表主键字段的列不止一个，Access将按照关键字列出现的次序从左到右排序简单的查询结果集。因为CompanyName是最左边的主键字段，查询结果集将为单个公司按订单编号顺序显示所有订单。你可以改写主键显示的次序，只要向查询添加一个排序次序即可。例如，如果你想首先看到最近的订单，可以指定按订单日期的降序排序。遵循如下步骤，向查询添加这个排序次序：关于主键索引的更多信息，请参见4.10.4节“往表中添加索引”。1)单击“设计视图”按