Access教程 第三章 查询

本章内容

 

◆ 查询的概念、种类和作用。

◆ 各种查询的建立。

◆ 查询的应用。

 

 

一、查询的概念

 

1．什么是查询

 

查询就是依据一定的查询条件，对数据库中的数据信息进行查找。它与表一样，都是数据库的对象。它允许用户依据准则或查询条件抽取表中的记录与字段。Access 2003 中的查询可以对一个数据库中的一个或多个表中存储的数据信息进行查找、统计、计算、排序等。

 

有多种设计查询的方法，用户可以通过查询设计器或查询设计向导来设计查询。

 

 

查询结果将以工作表的形式显示出来。显示查询结果的工作表又称为结果集，它虽然与基本表有着十分相似的外观，但它并不是一个基本表，而是符合查询条件的记录集合。其内容是动态的。

 

2. 查询的种类

 

Access 2003 提供多种查询方式，查询方式可分为选择查询、汇总查询、交叉表查询、重复项查询、不匹配查询、动作查询、SQL特定查询、以及多表之间进行的关系查询。这些查询方式总结起来有4类：选择查询、特殊用途查询、操作查询和SQL专用查询。

 

3. 查询的作用和功能

 

查询是数据库提供的一种功能强大的管理工具，可以按照使用者所指定的各种方式来进行查询。查询基本上可满足用户以下需求：

◆ 指定所要查询的基本表。

◆ 指定要在结果集中出现的字段。

◆ 指定准则来限制结果集中所要显示的记录。

◆ 指定结果集中记录的排序次序。

◆ 对结果集中的记录进行数学统计。

◆ 将结果集制成一个新的基本表。

◆ 在结果集的基础上建立窗体和报表。

◆ 根据结果集建立图表。

◆ 在结果集中进行新的查询。

◆ 查找不符合指定条件的记录。

◆ 建立交叉表形式的结果集。

◆ 在其他数据库软件包生成的基本表中进行查询。

作为对数据的查找，查询与筛选有许多相似的地方，但二者是有本质区别的。查询是数据库的对象，而筛选是数据库的操作。

下表指出了查询和筛选之间的不同：

 

功能                                        查询        筛选

——————————————————————————————

用作窗体或报表的基础                        是          是

排序结果中的记录                            是          是

如果允许编辑，就编辑结果中的数据            是          是

向表中添加新的记录集                        是          否

只选择特定的字段包含在结果中                是          否

作为一个独立的对象存储在数据库中            是          否

不用打开基本表、查询和窗体就能查看结果      是          否

在结果中包含计算值和集合值                  是          否         

——————————————————————————————

 

 

二、创建查询

 

用户可以打开数据库窗口，选择【查询】对象，然后单击工具栏中的【新建】按钮，弹出【新建查询】对话框。

 

1. 简单选择查询

 

简单选择查询通过简单查询向导来快速完成。

 

 

 

如果要添加汇总，则进行下一步操作而不选择【明细】。

 

 

 

下面是汇总选项：

 

 

如果不用向导设计查询而用查询设计器进行查询设计，并且要在查询中添加汇总选项，则需要手工添加一些汇总函数：

Sum     求总和

Avg     平均值

Min     最小值

Max     最大值

Count   计数

StDev   标准差

Var     方差

First   第一条记录

Last    最后一条记录

 

2. 交叉表查询向导

 

交叉表查询以表的形式显示出摘要的数值，例如某一字段的总和、计数、平均等。并按照列在数据表左侧的一组标题和列在数据表上方的另一组标题，将这些值分组，在数据工作表中分别以行标题和列标题的形式显示出来，用于分析和比较。

例如：产品表如下

 

要从基本表中得到如下信息：某一类别产品的“库存量”及其“供应商”。

 

方法步骤图解如下：

 

 

 

 

3. 查找重复项查询向导

 

查找重要项查询向导，可以帮助用户在数据表中查找具有一个或多个字段内容相同的记录。此向导可以用来确定基本表中是否存在重复记录。

如果要得到如下面所示的结果集：

 

 

则可进行如下操作步骤：

 

 

 

 

 

4. 查找不匹配项查询向导

 

查找不匹配项查询向导，是用来帮助用户在数据中查找不匹配记录的向导。如要查找【产品】表中的供应商ID与【供应商】表中的供应商ID不匹配的记录。步骤分解如下：

 

 

 

 

 

 

 

 

 

5. 用查询设计器创建查询

 

使用向导只能建立简单的、特定的查询。Access 2003 还提供了一个功能强大的查询设计器，通过它不仅可以从头设计一个查询，而且还可能对已有的查询进行编辑和修改。

 

下图即为查询设计器：

 

【设计器】主要分为上下两部分，上面放置数据库表、显示关系和字段；下面给出设计网格，网格中有如下行标题：

◆ 字段     查询工作表中所使用的字段名

◆ 表       该字段所来自的数据表

◆ 排序     是否按该字段排序

◆ 显示     该字段是否在结果集工作表中显示

◆ 条件     查询条件

◆ 或       用来提供多个查询条件

 

上面的工具栏上有如下按钮：

 

 

◆ 视图         每个查询有5种视图（设计、数据表、SQL、数据透视表、数据透视图表）

◆ 查询类型     选择、交叉表、更新、追加、生成表、删除。

◆ 运行         运行查询

◆ 显示表       显示所有可用的表

◆ 总计         在查询设计区中增加【总计】行，可用于求和、求平均等

◆ 上限值       用户可指定显示范围

◆ 属性         显示当前对象属性

◆ 生成器       弹出【表达式生成器】

◆ 数据库窗口   回到数据库窗口

◆ 新对象       建立数据库的新对象

 

6. 用查询设计器进一步设计查询

 

⑴ 添加表/查询

 

⑵ 更改表或查询间的关联

⑶ 删除表/查询

⑷ 添加插入查询的字段

⑸ 删除、移动字段

⑹ 设置查询结果的排序

⑺ 设置字段显示属性

 

 

7. 查询及字段的属性设置

 

 

 

8. 设置查询准则

 

查询设计视图中的准则就是查询记录应符合的条件。它与在设计表时设置字段的有效性规则的方法相似。

 

⑴ 准则表达式

And             与操作          “A” And “B”

Or              或操作          “A” Or “B”

Between…And    指定范围操作    Between “A” And “B”

In              指定枚举范围    In(“A,B,C”)

Like            指定模式字符串  Like “A?[A~f]#[!0~9]*”    如：A u D 3 q 98e32ww

 

⑵ 在表达式中使用日期与时间

在准则表达式中使用日期/时间时，必须要在日期值两边加上“#”。下面写法都是正确的：#Feb12,98#、#2/12/98#、#1221998#。

相关内部函数：

Date()      返回系统当前日期

Year()      返回日期中的年份

Month()     返回日期中的月份

Day()       返回日期中的日数

Weekday()   返回日期中的星期数

Hour()      返回时间中的小时数

Now()       返回系统当前的日期与时间

 

⑶ 表达式中的计算

A+B         两个数字型字段值相加，两个文本字符串连接

A-B         两个数字型字段值相减

A*B         两个数字型字段值相乘

A/B         两个数字型字段值相除

A/B         两个数字型字段值相除四舍五入取整

A^B         A的B次幂

Mod(A,B)    取余，A除以B得余数

A&B         文本型字段A和B连接

 

⑷ 使用准则表达式生成器

 

 

 

三、创建特殊用途查询

 

数据查询未必总是静态地提取统一信息。只要用户把搜索类别输入到一个特定的对话框中，就能在运行查询时对其进行修改。例如：当用户希望能够规定所需要的数据组进，就需要使用一个参数查询。

另一个特殊用途的查询就是把字段值自动填充到相关表中的“自动查询”查询。“自动查询”查询通过查找用户输入在匹配字段中的数值，并把用户指定的信息输入到相关表的字段中。

 

1. 参数查询

 

如用户想要查询价格在10～30元之间的各种产品，并想知道产品的供应商和产品的类别。这需要向查询设计器中添加【产品】、【供应商】、【类别】三个表。具体步骤如下：

 

首先打开查询设计器，将数据表添加到上面。

 

添加字段。并给出条件：Between [输入最低值] And [输入最高值]

 

 

然后运行，输入两参数：

 

 

可查看到结果：

 

 

如要改变参数类型，可打开【查询】|【参数】对话框来解决：

 

2. 自动查找查询

 

自动查询查询使用具有一对多关系的两个表，若要创建一个自动查找查询，首先把两个相关表添加到查询设计窗口，然后把匹配字段从“多”方拖到网格上。这种查询是查找“一”方中的相关记录并从匹配记录中的其他字段检索数值。

下面创建一个在【供应商】列中选择一个供应商时，自动填充【联系人姓名】、【地址】、【邮政编码】、【电话】的自动查找查询。具体操作如下：

 

 

运行，在底部通过选择供应商而添加记录，其中“一”方表中的公司名称、联系人姓名、地址、邮政编码、电话等字段将自动加上。而“多”方的产品名称则须人工加上。

 

 

3. 交叉表查询

 

交叉表查询是一种特殊的合计查询类型，可以使数据按电子表格的方式显示查询结果集，这种方式在水平与垂直方向同时对数据进行分组，使数据的显示更为紧凑。这一点在前面已讨论过了。

 

下面我们再创建一个雇员销售订单金额汇总表。由于每一个定单中有多个订单明细产品，所以金额必须求和。所以按订单分组，利用表达式[数量]*[单价]求和即可，并按雇员分列之。

 

 

 

 

 

四、操作查询

 

    操作查询用于同时对一个或多个表执行全局数据管理操作。操作查询可以对数据表中原有的数据内容进行编辑，对符合条件的数据进行成批的修改。因此，应该备份数据库。

 

1. 更新查询

 

更新查询用于同时更改许多记录中的一个或多个字段值，用户可以添加一些条件，这些条件除了更新多个表中的记录外，还筛选要更改的记录。大部分更新查询可以用表达式来规定更新规则。

如下表实例：

 

字段类型    表达式          结果

————————————————————————————————————

货币        [单价]*1.05     把“单价”增加5%

日期        #4/25/01#       把日期更改为2001年4月25日

文本        “已完成”      把数据更改为“已完成”

文本        “总”&[单价]   把字符“总”添加到“单价”字段数据的开头

是/否       Yes             把特定的“否”数据更改为“是”

————————————————————————————————————

 

 

 

 

2. 追加查询

 

当用户要把一个或多个表的记录添加到其他表时，就会用到追加查询。追加查询可以从另一个数据库表中读取数据记录并向当前表内添加记录，由于两个表之间的字段定义可能不同，追加查询只能添加相互匹配的字段内容，而那些不对应的字段将被忽略。

 

 

 

 

 

3. 删除查询

 

删除查询是所有查询操作中最危险的一个。删除查询是将整个记录全部删除而不只是删除查询所使用的字段。查询所使用的字段只是用来作为查询的条件。可以从单个表删除记录，也可以通过级联删除相关记录而从相关表中删除记录。

 

4. 生成表查询

   

生成表查询可以从一个或多个表/查询的记录中制作一个新表。在下列情况下使用生成表查询：

◆ 把记录导出到其数据库。如创建一个交易已完成的订单表，以便送到其它部门。

◆ 把记录导出到Excel/Word之类的非关系应用系统中。

◆ 对被导出的信息进行控制。如筛选出机密或不相干的数据。

◆ 用作在一特定时间出现的一个报表的记录源。

◆ 通过添加一个记录集来保存初始文件，然后用一个追加查询向该记录集中添加新记录。

◆ 用一个新记录集替换现有的表中的记录。

例如，要以【产品】表为基础，查询出【产品名称】、【类别】、【单价】、【库存量】并生成一个新表：

 

 

 

 

 

 

五、SQL专用查询

 

结构化查询语言（即SQL语言）是最重要的关系数据库操作语言，在过去的几年中，SQL语言已经发展成为标准的计算机数据库语言。

1986年美国国家标准协会ANSI（American National Standards Institute）和国际标准化组织ISO（International Standards Organization）颁布了SQL正式标准，同是时确认SQL语言为数据库操作的标准语言，现在已有100多种遍布在从微机到大型机上的数据库产品SQL产品。SQL语言基本上独立于数据库本身及其使用的机器、网络、操作系统，基于SQL的DBMS开发商所提供的产品一般都具有良好的可移植性。

SQL语言最初由IBM的研究人员在70年代提出，最初的名称为SEQUEL（结果），从80年代开始改名为SQL，看似是SEQUEL的缩写，但一般又理解为结构化查询语言Structure Query Language。

下面讨论1991年ANSI制定的SQL语言的核心。VFP的SELECT-SQL可以据此理解之。

 

1．对单个表进行查询

下面考虑SQL对单个表进行简单的查询。为了进行实例分析，下面我们先建立三个表：

 

学生信息表ST：

学号 *

姓名

主修

年龄

100

JONES

HISTORY

21

150

PARKS

ACCOUNTING

19

200

BAKER

MATH

50

250

GLASS

HISTORY

50

300

BAKER

ACCOUNTING

41

350

RUSSELL

MATH

20

400

RYE

ACCOUNTING

18

450

JONES

HISTORY

24

 

学生注册表EN：

学号 *

班名

注册号

100

A100

1

150

B200

1

200

A100

2

200

C200

1

300

C100

1

400

B200

2

400

B100

1

400

C200

2

450

B200

3

 

面授安排表CL：

班名 *

时间

教室

B200

MTH8

R001

A100

MWF3

R002

B100

MWF8

R002

C100

MWT3

R003

C200

MWF8

R004

 

⑴ 使用SQL进行投影

投影是指取表的某些列的字段值。下面是使用SQL语句进行投影的例子，从ST表中列出需要的学号、姓名和主修：

 

SELECT 学号,姓名,主修 FROM ST

学号   姓名         主修

100    JONES        HISTORY

150    PARKS        ACCOUNTING

200    BAKER        MATH

250    GLASS        HISTORY

300    BAKER        ACCOUNTING

350    RUSSELL      MATH

400    RYE          ACCOUNTING

450    JONES        HISTORY

 

 

SELECT 主修 FROM ST

主修

  HISTORY       

  ACCOUNTING    

  MATH          

  HISTORY       

  ACCOUNTING    

  MATH          

  ACCOUNTING    

  HISTORY 

 

 

SELECT DISTINCT 主修 FROM ST

主修

  ACCOUNTING    

  HISTORY       

  MATH 

 

 

⑵ 使用SQL进行选择

选择是指到表的某些行的记录值。请看下面的例子：

 

SELECT 学号,姓名,主修,年龄 FROM ST WHERE 主修=‘MATH’

SELECT * FROM ST WHERE 主修=‘MATH’

 

学号 姓名         主修             年龄

  200   BAKER        MATH              50

  350   RUSSELL      MATH              20

 

上述两条件命令的结果是一样的。我们可以将投影和选择进行合并如下：

 

SELECT 姓名,主修,年龄 FROM ST WHERE 主修=‘MATH’

 姓名        主修            年龄

    BAKER        MATH              50

    RUSSELL      MATH              20

 

SELECT 姓名,主修,年龄 FROM ST WHERE 主修=‘MATH’AND 年龄>21

 姓名         主修             年龄

    BAKER        MATH              50

 

 

SELECT 姓名,主修,年龄 FROM ST WHERE 主修 IN(‘MATH’，‘ACCOUNTING’)

 姓名         主修             年龄

   PARKS        ACCOUNTING         19

   BAKER        MATH               50

   BAKER        ACCOUNTING         41

   RUSSELL      MATH               20

   RYE          ACCOUNTING         18

 

SELECT 姓名,主修,年龄 FROM ST WHERE 主修 NOT IN(‘MATH’,‘ACCOUNTING’)

 姓名         主修             年龄

   JONES        HISTORY            21

   GLASS        HISTORY            50

   JONES        HISTORY            24

 

⑶ SQL排序

SELECT 姓名,主修,年龄 FROM ST WHERE 主修=‘ACCOUNTING’ ORDER BY 姓名

 姓名         主修             年龄

   BAKER        ACCOUNTING         41

   PARKS        ACCOUNTING         19

   RYE          ACCOUNTING         18

SELECT 姓名,主修,年龄 FROM ST WHERE 主修 IN(‘MATH’,‘ACCOUNTING’)  ORDER BY 姓名 DESC,年龄 ASC

 姓名        主修            年龄

   RYE          ACCOUNTING         18

   RUSSELL      MATH               20

   PARKS        ACCOUNTING         19

   BAKER        ACCOUNTING         41

   BAKER        MATH               50

 

⑷ SQL内置函数

SQL主要提供了前面我们提到的五个内置函数：COUNT、SUM、AVG、MAX、MIN。

SELECT COUNT（*） FROM ST

  CNT

     8

上述语句计算表ST中的行数，并用一行一列表示出来。

注意：除非和GROUP BY相连，在查询的项中SELECT后内置函数一般不和字段名一起使用。如下面的查询语句虽然不是非法的，但结果的含义不清：

SELECT 姓名，COUNT（*）

 姓名        CNT

    JONES        8

请思考下面的两个查询语句的结果：

SELECT COUNT（主修） FROM ST

  Cnt_主修

     8

 

?

SELECT COUNT（DISTINCT 主修） FROM ST

  DCnt_主修

     3

 

⑸ SQL内置函数和分组

为了增强统计内置函数的功能，内置函数可以和分组函数合用，将源表中的数据分组，再对每一分组生成一个汇总行。例如：学生可以按照主修专业进行分组，这意味着每一个专业将形成一个分组，然后可以对每一组进行一定的统计。

 

SELECT 主修,COUNT（*） FROM ST GROUP BY 主修

 主修              Cnt

ACCOUNTING         3

HISTORY            3

MATH               2

 

有时，我们不需要得到每一个分组的值，例如，我们对学生按照专业进行分组，然后只需要具有两个以上的行数的分组，在这种情况下，我们需要用到SQL中的HAVING关键字对不符合条件的分组进行过滤。

 

下面的SQL语句可以统计出具有两个以上学生的专业，并统计该专业的学生数。

 

SELECT 主修，COUNT（*） FROM ST GROUP BY 主修 HAVING COUNT（*）>2

 主修              Cnt

ACCOUNTING         3

HISTORY            3

 

在上面的查询中，还可以对查询的学生进行条件选择，使用关键字WHERE进行查询，但是这样会产生一定的模糊性，请看下例的结果，分析之：

 

SELECT 主修,AVG(年龄) FROM ST WHERE 姓名='JONES'

GROUP BY 主修 HAVING COUNT(*)>1

 主修              Avg_年龄

HISTORY            22.5

 

SELECT 主修,AVG(年龄) FROM ST  GROUP BY 主修 HAVING COUNT(*)>2

 主修              Avg_年龄

ACCOUNTING         26.00

HISTORY            31.67

 

SELECT 姓名,主修,AVG(年龄) FROM ST WHERE 姓名 IN('JONES','BAKER')

GROUP BY 主修 HAVING COUNT(*)>0

 姓名      主修              Avg_年龄

JONES    HISTORY            22.50

 

SELECT 姓名,主修,AVG(年龄) FROM ST WHERE 姓名 IN('JONES','BAKER')

GROUP BY 主修 HAVING COUNT(*)>1

 姓名      主修              Avg_年龄

BAKER    ACCOUNTING         41.00

JONES    HISTORY            22.50

BAKER    MATH               50.00

 

从上述结果来看，首先选择适合条件的学生，对选择出来的学生进行分组，去除不适合HAVING条件的分组，显示得到结果。

 

2．对多个表进行查询

 

下面我们将讨论基于两个或更多的表的SQL查询语句。下面的例子基本上针对ST、CL和EN三个表而言。

学生信息表ST：

学号 *

姓名

主修

年龄

100

JONES

HISTORY

21

150

PARKS

ACCOUNTING

19

200

BAKER

MATH

50

250

GLASS

HISTORY

50

300

BAKER

ACCOUNTING

41

350

RUSSELL

MATH

20

400

RYE

ACCOUNTING

18

450

JONES

HISTORY

24

学生注册表EN：

学号 *

班名

注册号

100

A100

1

150

B200

1

200

A100

2

200

C200

1

300

C100

1

400

B200

2

400

B100

1

400

C200

2

450

B200

3

面授安排表CL：

班名 *

时间

教室

B200

MTH8

R001

A100

MWF3

R002

B100

MWF8

R002

C100

MWT3

R003

C200

MWF8

R004

 

⑴ 带有子查询的SQL查询语句

假设我们需要知道入学到A100班的学生的名字，则来通过两个步骤：首先通过条件班名为‘A100’在EN表中找到学生的学号，然后通过上面找到的学号‘100’和‘200’在ST表中找出这些学生的姓名。

如下面的两个查询语句：

SELECT 学号 FROM EN WHERE 班名=‘A100’

学号

100

200

SELECT 姓名 FROM ST WHERE 学号 IN（‘100’，‘200’）

姓名

JONES

BAKER

我们可以将上述两条语句进行结合就可能直接得到我们需要的结果：

SELECT 姓名 FROM ST WHERE 学号 IN（SELECT 学号 FROM EN WHERE 班名=‘A100’）

姓名

JONES

BAKER

我们将第二个查询语句称为子查询（SUBQUERY），它嵌套在主查询的条件中。这种查询方式是很有用的，但要注意子查询的结果与主查询的条件的类型匹配问题。

对于一般的SQL查询语句而言，子查询可以嵌套二层以上甚至更多，但VFP对子查询的深度进行了限制。请看下面的语句：

SELECT CL.班名 FROM CL WHERE 时间=‘MWF8’

班名

B100

C200

SELECT EN.学号 FROM EN WHERE EN.班名 IN（SELECT CL.班名 FROM CL WHERE 时间=‘MWF8’）

学号

200

400

400

SELECT ST.姓名 FROM ST WHERE ST.学号 IN（SELECT EN.学号 FROM EN WHERE EN.班名 IN（SELECT CL.班名 FROM CL WHERE 时间=‘MWF8’））

结果将提示：SQL：Subquery nesting is too deep

按正常的理解，其结果应是：

姓名

BAKER

RYE

也就是下面的语句的结果：

SELECT ST.姓名 FROM ST WHERE ST.学号 IN（‘200’，‘400’，‘400’）

同时通过上述查询，我们得到一个重要的结论，RYE的报名有问题，或要将面授时间作调整，大家可以思考为什么？

 

⑵ SQL联接查询

子查询的结果总是来自一个表，如果同时提供来自不同的表的数据（如每一个学生的学号、姓名、所在班名等），则必须将多个表（ST、EN）相联接。

请看下面的语句：

 

SELECT ST.学号，ST.姓名，EN.班名，EN.注册号 FROM ST，EN WHERE ST.学号=EN.学号

 

学号   姓名         班名        注册号

100    JONES        A100         1

150    PARKS        B200         1

200    BAKER        A100         2

200    BAKER        C200         1

300    BAKER        C100         1

400    RYE          B200         2

400    RYE          B100         1

400    RYE          C200         2

450    JONES        B200         3

当然，在WHERE条件中，可以加上其它的查询结果的限定：

SELECT ST.学号，ST.姓名，EN.班名，EN.注册号 FROM ST，EN WHERE ST.学号=EN.学号 AND ST.姓名=‘RYE’ AND EN.注册号=‘2’

学号   姓名         班名        注册号

400    RYE          B200         2

400    RYE          C200         2

 

如果结果来自于两个以上的表，我们可以使用相仿的方法，如下例：

SELECT ST.学号，CL.班名，CL.时间，EN.注册号 FROM ST，EN，CL WHERE ST.学号=EN.学号 AND EN.班名=CL.班名 AND ST.姓名=‘BAKER’

学号   班名         时间        注册号

200    A100         MWF3         2

200    C200         MWF8         1

300    C100         MWT3         1

 

3. 回顾专用查询

 

在专用查询中，使用了除SELECT以外的其它几个操作语句：UPDATE、INSERT INTO、DELETE、SELECT INTO等等。