查询设计器的使用

5．2．2　查询设计器的使用

在VFP中，数据保存在表中。数据库将多个表组织起来，并在表之间建立关系，使这些表在逻辑上成为一个整体。保存数据的目的是为了使用数据和快速查询数据。使用查询功能，可以方便地实现对各种数据的查询。

一、建立单表查询

例5－16：通过对学生表的查询，了解查询设计器的应用。试查询所有在18～20岁之间的学生的学号、姓名、年龄，并按学号升序排序。

1．启动查询设计器

启动查询设计器有下列方法：

（1）在“项目管理器”中选择“数据”选项卡，在项列表中选择“查询”，单击“新建”按钮，选择“新建查询”。

（2）选择“文件”菜单中“新建”命令或者“常用”工具栏“新建”按钮，在单选框中选择“查询”，选择“新建文件”按钮。

（3）使用CREATE　QUERY命令。

在新建查询时，系统会提示从当前数据库或自由表中选择表或视图，选择了相关表（本例为Sjk数据库中的xs表）或视图后，则打开查询设计器，查询设计器界面如图5－1所示。同时可打开查询设计器工具栏。系统还自动增加了一个“查询”主菜单，如图5－2所示。另外，单击鼠标右键，还可弹出快捷菜单。系统提供的多种操作方式的功能基本相同，都是为了方便查询操作。

图5－1　查询设计器

图5－2　查询主菜单

查询设计器对话框的上半部分为数据环境显示区，可看到所选择的工作表。下半部分包括6个选项卡，各个选项卡的功能及对应SELECT命令子句见表5－1。

表5－1　查询设计器与SQL查询语句的功能对照表

查询设计器工具栏的按钮说明如表5－2。

2．定义查询的输出内容

在“查询设计器”中，选择“字段”选项卡，在“可用字段”框中列出了数据环境中相关数据表的所有字段；在“选定字段”框中显示出需查询输出的字段或表达式：“函数和表达式”框用来建立查询结果中输出的表达式。“选定字段”框中行的顺序就是查询结果中列的顺序，可用左边按钮上下调整输出列的顺序。

表5－2　查询设计器工具栏的按钮说明

在“可用字段”框和“选定字段”框之间有4个按钮：“添加”、“全部添加”、“移去”和“全部移去”按钮，用于选择或取消选定字段。

在“函数和表达式”框中，可以用来输入一个表达式，或单击“…”按钮，打开“表达式生成器”对话框，用于生成一个表达式，单击“添加”按钮，表达式就出现在“选定字段”框中。还可以给选定的字段或表达式起一个列名，方法是在“函数和表达式”框中字段名或表达式后输入“AS列名”，查询结果中就以列名作为该列的标题。

本例中，为了输出年龄，可以在“函数和表达式”框中加入下列表达式：

YEAR（DATE（））－YEAR（Xs．csrq）　AS年龄

然后，按“添加”按钮，可见表达式出现在“选定字段”框中，通常情况下，若系统给输入的表达式自动加上双引号，则说明输入的表达式有错误。对于单独的字段也可以起列名，例如在“函数和表达式”框中输入“Xs．xsxm　as姓名”，这样查询表中标题将显示“姓名”字样。

3．设置查询的筛选条件

查询既可以查询所有记录，也可以查询满足某个条件的记录。指定选取记录的条件可以使用查询设计器的“筛选”选项卡，如图5－3所示。其中，“字段名”框用于选择要比较的字段；“条件”框用于设置比较的类型（见表5－3）；“实例”框用于指定比较的值；“大小写”框用于指定比较字符值时，是否区分大小写；“逻辑”框用于指定多个条件之间的逻辑运算关系。如果用逻辑与运算符“AND”连接两个条件组成筛选条件，则只有同时满足这两个条件的记录才能出现在查询结果中；如果用逻辑或运算符“OR”连接两个条件组成筛选条件，则满足这两个条件中的任何一个的记录就能出现在查询结果中。“筛选”卡中的一行就是一个关系表达式，所有的行构成一个逻辑表达式。

图5－3　查询设计器的“筛选”选项卡

表5－3　条件类型

4．设置查询结果的排序依据

使用查询设计器，可以对查询结果中输出的记录排序。“排序条件”列表框中的顺序决定了排序的优先权。排序可以是升序，也可以是降序。本例中，选择学号（Xs．xsxh）排升序，如图5－4所示。

图5－4　查询设计器的“排序依据”选项卡

5．运行查询

运行查询的方法有多种：

（1）在项目管理器打开的情况下，选择要运行的查询文件，单击项目管理器上的“运行”按钮，即可运行查询。

（2）在查询文件打开的情况下，单击“常用”工具栏上的“运行”按钮或“查询”菜单（或者快捷菜单）中的“运行查询”项即可运行查询。

（3）在命令窗口或应用程序中用DO查询文件．QPR命令运行查询。

实质上运行查询是执行一条SELECT－SQL命令，本例中执行的SELECT命令，如图5－5所示。

图5－5　例5－16的SELECT－SQL语句

二、统计处理

例5－17：对教师表统计各部门教授、副教授的人数，要求输出部门、职称、人数，按部门代号排升序，部门代号相同的按职称排升序。（注意：教师工号的第1位表示部门。）

本例中，先按要求设置好“字段”选项卡，加入“部门”字段（Substr（Js．jsgh，1，1）as部门）、“职称”字段、“人数”（Count（＊））字段；设置“筛选”选项卡，字段名为zc，条件为In，实例为教授，副教授；设置“排序依据”选项卡，按部门升序、职称升序；然后设置“分组依据”选项卡。

1．设置查询结果的分组依据

在查询设计器中有一个“分组依据”选项卡。所谓分组就是将一组类似的记录压缩成一个结果记录，以便完成对这一组记录的计算。下面来看一下“分组依据”的使用。

本例中需按照部门和职称进行分组，利用COUNT（）、SUM（）和AVG（）函数可以对每一组记录进行计数、求和及求平均计算。另外还有MAX（）是求最大值函数，MIN（）是求最小值函数。

在“分组依据”选项卡中，把“可用字段”中的“Substr（Js．gh，1，1）as部门”字段和“Js．zc”字段添加到“分组字段”中，如图5－6所示。

图5－6　查询设计器“分组字段”选项卡

实现例5－17的SELECT－SQL语句及运行结果分别如图5－7、图5－8所示。

图5－7　例5－17的SELECT－SQL语句

图5－8　例5－17的运行结果

例5－18：对教师表各部门作统计处理，统计各部门的总人数、教授、副教授、讲师、助教的人数及高职所占比例，要求输出至少有2名高职人员的部门，将其中高职所占比例最高的前2个部门的相关数据输出至临时表jstotal。（注意：工号的第1位表示部门，高职所占比例＝（（教授＋副教授）／总人数）＊100%。）

本例中的统计和上例统计略有不同，我们按要求先设置好“字段”选项卡，在函数和表达式框处前后添加表达式：

①SUBSTR（Js．jsgh，1，1）AS部门；

②COUNT（＊）AS总人数；

③SUM（IIF（Js．zc＝"教授"，1，0））AS教授；

④SUM（IIF（Js．zc＝"副教授"，1，0））AS副教授；

⑤SUM（IIF（Js．zc＝"讲师"，1，0））AS讲师；

⑥SUM（IIF（Js．zc＝"助教"，1，0））AS助教；

⑦STR（SUM（IIF（zc＝"教授" OR　zc＝"副教授"，1，0））／COUNT（＊）＊100，5，1）＋"%" AS高职所占比例；

然后设置“排序依据”选项卡，按高职所占比例降序排序；再设置“分组依据”选项卡，按部门分组，同时设置满足条件。

2．设置查询结果的满足条件

如果在分组的基础上，还要对查询结果进行筛选，可以单击在“分组依据”选项卡中的“满足条件”按钮，打开“满足条件”对话框，本例中要求一个部门至少有2名高职人员，就是说组中“教授＋副教授”的人数应大于等于2，可进行如图5－9设置。

图5－9　“分组依据”中的“满足条件”对话框

需要注意的是，“分组依据”选项卡的“满足条件”是对查询结果中的组进行的进一步筛选，“筛选”选项卡的“筛选条件”是对基表的记录进行的筛选。

最后，还需要设置查询杂项及输出去向。

3．设置查询杂项

在“查询设计器”的“杂项”选项卡中，去除“全部”前复选框，记录个数处输入2，如图5－10所示。在此选项卡中，可以进行下列选项的设置：

图5－10　查询设计器的“杂项”选项卡

（1）如果选择复选框“无重复记录”，则查询结果中将排除所有相同的记录；否则，将允许重复记录的存在。

（2）如果选择复选框“交叉数据表”，将把查询结果以交叉表格式传送给Microsoft Graph、报表或表。只有当“选定字段”刚好为3项时，才可以选择“交叉数据表”复选框，选定的3项代表X轴、Y轴和图形的单元值。

（3）如果选择复选框“全部”，则满足查询条件的所有记录都包括在查询结果中，这是查询设计器的默认设置。只有在取消对“全部”复选框的选择的情况下，才可以设置“记录个数”和“百分比”。“记录个数”用于指定查询结果中包含多少条记录。当没有选定“百分比”复选框时，“记录个数”微调框中的整数表示只将满足条件的前多少条记录包括到查询结果中；当选定“百分比”复选框时，“记录个数”微调框中的整数表示只将最先满足条件的百分之多少个记录包括到查询结果中。

4．选择查询结果的输出去向

从“查询”菜单中选择“查询去向”，或在“查询设计器”工具栏中单击“查询去向”按钮，屏幕上将出现“查询去向”对话框，如图5－11所示，本例中选择临时表，并输入表名为JSTOTAL。

图5－11　查询设计器的“查询去向”对话框

在“查询去向”对话框中，根据需要可以把查询结果输出到表5－4所示的不同的目的地。如果没有选定输出类型，系统默认值为查询结果显示在浏览窗口中。

表5－4　查询结果输出去向类型说明

在SELECT－SQL语句中使用INTO子句或TO子句来定向查询结果。表5－5列出了几种输出类型的示例：

表5－5　SELECT－SQL输出去向

5．查看SQL语句

无论是用查询向导，还是用查询设计器创建查询，其结果都是生成一条SELECT－SQL语句。通过选择“查询”菜单（或者快捷菜单）中的“查看SQL”项或单击查询设计器的工具栏上的“SQL”按钮，即可看到所生成的SELECT－SQL语句。本例中显示如图5－12所示的SELECT命令。

6．生成查询文件

查询创建完成以后，单击常用工具栏上的“保存”按钮或文件菜单中的“保存”命令，输入文件名，即生成了查询文件，查询文件默认后缀为．QPR。该文件中保存的就是SQL语句。

图5－12　例5－18的SELECT－SQL命令

本例运行查询后，表面上无反应，打开“数据工作期”窗口，可见一别名为“Jstotal”的表，选择此表浏览，如图5－13所示。

图5－13　例5－18中输出的临时表Jstotal

三、多表查询

例5－19：查询每个学生的班级、学号、姓名、总学分、最高成绩、最低成绩和平均成绩数据，并按班级、学号排升序。（注意：成绩＞＝60分，计学分；成绩＜60分，学分计0分）

一般来说，对于查询问题，首先要分析查询哪些字段？有没有需要计算的表达式？以及计算将涉及哪些字段？然后，找出这些字段分别在哪个表或视图中？如果涉及多个表，就需要找出表之间的联接关系。至此，查询所需要的数据环境、联接条件、输出字段或表达式已经确定。最后，看一看查询结果是否需要排序，是否需要分组等等。

本例中，班级名称来自于“班级（BJ）”表，学号、姓名来自“学生（XS）”表，成绩数据来自“成绩（CJ）表”，学分来自“课程（KC）”表，且需分组统计计算，因此本查询涉及4张表中的数据，首先需要定义数据环境和设置联接条件。

1．定义数据环境

查询设计器的对话框的上半部分为数据环境显示区，用来显示所选择的表或视图，在新建查询时，系统会提示从当前数据库或自由表中选择表或视图。当“查询设计器”处于打开时，可以使用“查询设计器”工具栏的“添加表”按钮或“查询”菜单中的“添加表”项，或选择快捷菜单中的“添加表”项，打开“添加表或者视图”对话框，添加多张表或视图。同样，也可以使用“移去表”功能从数据环境中移去表。

图5－14　“联接条件”对话框

2．设置联接条件

进行多表查询时，需要把所有有关的表或视图添加到查询设计器的数据环境中，并为这些表建立联接。这些表可以是数据表、自由表或视图的任意组合。

当向查询设计器中添加多张表时，如果新添加的表与已存在的表之间在数据库中已经建立永久性关系，则系统将以该永久性关系作为默认的联接条件。否则，系统会打开“联接条件”对话框，并以两张表的同名字段作为默认的联接条件，如图5－14所示。

在该对话框中有4种联接类型：内部联接（Inner Join）、左联接（Left Outer Join）、右联接（Right Outer Join）和完全联接（Full Join），其意义见表5－6。系统默认的联接类型是“内部联接”，可以在“联接条件”对话框中更改表之间的联接类型。

对于已存在于查询中的表或视图，可以拖动一张表中的字段到另一张表中的字段上建立联接条件。也可以从“查询设计器”工具栏上选择“添加联接”按钮，打开“联接条件”对话框，建立联接条件。或者在“查询设计器”对话框的“联接”选项卡中，修改、添加或删除联接条件。

表5－6　联接类型

对于已存在的联接，在“查询设计器”对话框的数据环境显示区中将看到表之间的联接线；在“联接”选项卡中将看到一行对应的条件。

联接不必基于完全匹配的字段，可使用“Like”、“＝＝”、“＞”或“＜”等运算符，设置灵活的联接条件。联接两表的字段名可以同名，也可以不同名，甚至还可以为表达式，但含义必须相同。

查询或视图设计器中生成的SELECT－SQL语句，格式比较特殊，ON子句集中在一起。因此要设计出可行的查询，要确保联接条件的格局应该是“链式”的，即第一个联接条件右边的表别名应该是第二个联接条件左边的表别名，依此类推。一般具体应用时，需按上述要求顺序添加各表。

本例中，可以按顺序添加“班级”表、“学生”表、“成绩”表和“课程”表，并设置它们相应字段之间的联接，设置后“联接”选项卡如图5－15所示；另外，也可以通过设置筛选条件来进行联接班级表、学生表、成绩表和课程表。这样，4张表就可以有效地联接起来。

图5－15　查询设计器“联接”选项卡

然后，根据要求设置查询输出的各字段或表达式，如图5－16所示。

图5－16　查询设计器“字段”选项卡

再设置“分组依据”选项卡，根据班级和学号进行分组。

最后，设置查询结果的排序条件：先按班级名称排序，再按学号排升序。

本例查询的SELECT－SQL语句，如图5－17所示。

图5－17　例5－19的SELECT－SQL语句

本例中，若添加表的顺序不正确，则链接就会有问题，将得不到正确结果，但可以删除联接条件或不联接，通过添加筛选条件实现，查询的SELECT－SQL语句，如图5－18所示。

图5－18　例5－19通过筛选条件实现的SELECT－SQL语句