Access_CH03_查询

本章目标

• 查询概述
• 选择查询
• 参数查询
• 交叉表查询
• 操作查询
• SQL查询

查询概述

在数据库系统中，查询就是依据数据一定的查询条件，对数据库中的数据信息进行查找。

查询的功能

查询是数据处理和数据分析的工具，是在指定的（一个或多个）表中根据给定的条件筛选所需要的信息，供用户查看、更改和分析。利用查询可实现一下多种功能。

（1）选择字段：在查询中，可以只选择表中的部分字段。

（2）选择记录：根据指定的条件查找所需记录并显示。

（3）编辑记录：使用查询可以对数据表的记录进行追加、更新和删除等操作。

（4）实现计算：在建立查询的过程中进行各种统计计算。

（5）建立新表：利用查询的结果创建一个新表。

（6）作为其它对象的数据源：查询的运行结果可以作为窗体、报表的数据源，也可以作为其它查询的数据源。

查询的类型

（1）选择查询

​ A.简单查询：从一个或多个表中将符合条件的数据提取出来，并可以对这些数据进行编辑操作。

​ B.汇总查询：汇总查询不仅可以提取数据，还可以对数据进行各类统计和汇总统计。

​ C.重复项查询：能将数据库表中相同字段的信息内容集合在一起显示，主要用于对各种数据的对比分析。

​ D.不匹配查询：可以在一个表中查找那些在另一个表中没有相关记录的记录。

（2）参数查询

​ 参数查询是在执行时显示对话框以提示用户输入查询参数或准则。与其它查询不同，参数查询的查询准则是可以因用户的要求而改变的，而其它查询的准则是事先定义好的。

（3）交叉表查询

​ 交叉表查询可以计算并重新组织数据的结构，这样可以更加方便的分析数据。交叉表查询可以对数据进行统计、求平均值、计算等汇总。与显示相同数据的选择查询相比，交叉表查询的结构让数据更易于阅读。

（4）操作查询

​ A.生成表查询：从一个或多个表中选择数据建立一个新表。

​ B.删除查询：从一个或多个表中删除满足条件的记录。

​ C.更新查询：对一个或多个表中的一组记录进行更新。

​ D.追加查询：将一个或多个表中满足条件的一组记录追加到其它表的末尾。

（5）SQL查询

​ A.联合查询：使用UNION语句将多个查询结果合并在一起。

​ B.传递查询：基于远程数据库上的SQL语句进行的查询，这种查询可在建立了连接的情况下直接对服务器中的表进行操作。

​ C.数据定义查询：使用SQL的数据定义语句进行创建、删除、更改表等操作。

​ D.子查询：嵌套在其它查询中的SQL SELECT语句。

查询的结果

Access会接收从查询张返回的记录，并将这些数据在数据表中显示出来。这些记录的集合通常称为记录集。从物理上来说，记录集看起来与表非常类似。实际上，记录集是记录的一个动态集合。除非使用这些记录集直接构建一个表，否则查询所返回的记录集并不会村创建在数据库中。

运行查询时，Access会将返回的记录放到一个记录集中。保存查询时，只有查询结构会保存下来，所返回的记录并不会保存。

选择查询

通过向导创建选择查询

使用查询向导创建查询十分方便、简单，用户可以在向导提示下完成查询操作，但也有较大的局限性，如不能设置查询条件、排序等。

在设计视图中创建查询

在实际应用中，需要创建的选择查询多种多样，有些带条件，有些不带条件。使用“简单查询向导”虽然可以快速、方便的创建查询，但它只能创建不带条件的查询，而对于有条件的查询需要通过查询设计视图完成。

查询设计视图由两部分组成，上半部分窗口是表/查询列表区域，用于显示添加的表和查询；下半部分窗口是设计网格，它是查询的设计区域，由一些字段列和已命名的行组成。每一行分别是字段的属性和要求，其具体说明如下。

(1)字段：可以在此输入或添加字段名来选择所需表中的字段。

(2)表：设置字段的来源（在多表查询时非常有用）。

(3)排序：定义字段的排序方式。

(4)显示：利用复选框来确定字段是否在查询结果中显示。

(5)条件：对所返回的记录进行筛选的条件。

(6)或：可以添加多个查询条件，这是指定多行条件的第一行。

添加计算字段：

带条件的查询：

说明：在查询设计视图中，“条件”行与“或”行都是用来表示条件的。“条件”行中的所有条件，都是并且的关系；“或”行中的所有条件，也是并且的关系；但“条件”行与“或”行的条件是或者的关系。

设置查询条件

查询条件又称查询准则，是描述用户查询需求的主要途径，也是查询设计的一个重要内容。

1.表达式

​ 表达式是使用各类运算符将各类操作数连接起来，具有唯一运算结果的运算式，其运算结果称为表达式的值。也就是说，表达式是由运算符和操作数组成的运算式。

​ (1)表达式的操作数有常量、变量和函数。

​ (2)表达式的运算符有算术运算符、关系运算符、字符运算符和逻辑运算符。

​ (3)表达式值的类型有数字型、文本型、日期型、是/否型（又称逻辑性）等。

2.常量与变量的表示

类型	表示方法	示例
数字型常量	直接输入数据	3.14
文本型常量	用””括起来	“计算机”
日期型常量	用一对#括起来	# 2010-1-1 #
是/否型常量	使用专用字符表示，只有两个选项	True/False;Yes/No;On/Off

3.运算符

​ (1)算术运算符

算术运算符	说明
+	求和，还可以连接两个字符串
-	求差
*	求积
/	求商
\	整除，求商的整数部分
Mod	取模运算符，除法计算后求余数
^	乘方运算符

​ (2)关系运算符

关系运算符	说明
=	等于
<>	不等于
<	小于
>	大于
<=	小于等于
>=	大于等于

​ (3)逻辑运算符

逻辑运算符	说明
And	与
Or	或
Not	非

​ (4)字符串运算符

字符串运算符	说明
&	连接运算符
Like	像 xxxx
Not Like	不像 xxxx

​ (5)通配符

通配符	说明
?	匹配单个字符(A-Z,0-9)或汉子
*	任何数量的字符(0个或多个)
#	任何单个数字
[]	统配方括号内列出的任一单个字符

​ (6)其它常用运算符

其它常用运算符	说明
Between …And	用于指定一个字段值的范围
In	用于指定一个字段值的列表，列表中的任意一个值都可以与查询的字段相匹配
Is Null	指定一个字段值为空
Is Not Null	指定一个字段值不为空

4.函数

​ (1)数值函数

函数	功能	说明
Abs(x)	求x的绝对值	x为实数
Sgn(x)	符号函数	x大于0返回1 x小于0返回-1 x等于0返回0
Sqr(x)	求x的平方根	x>=0
Int(x)	求x的整数部分	取整为小于等于它的整数

​ (2)字符函数

函数	功能	说明
Left(x,n)	取出字符串x左边的n个字符
Right(x,n)	取出字符串x右边的n个字符
Mid(x.n1,n2)	对x字符串从n1个字符开始取n2个字符
Len(x)	字符串的长度
LenB(x)	z字符串所占的字节数
LTrim(x)	去掉字符串左边的空格
RTrim(x)	去掉字符串右边的空格
Trim(x)	去掉字符串左右两边的空格
Space(n)	产生n个空格组成的字符串
Instr(x1,x2)	返回字符串x2在字符串x1中首次出现的位置，找不到则为0

​ (3)日期/时间函数

函数	说明
Day(date)	返回给定日期1-31的值
Month(date)	返回给定日期1-12的值
Year(date)	返回给定日期是哪一年
Weekday(date)	表示给定日期是一周中的哪一天
Hour(date)	给定日期的小时值0-23
Date()	返回当前系统时间
DateSerial(yyyy.mm,dd)	返回一个由3个整数指定年、月、日的日期

​ (4)统计函数

函数	说明
Sum(表达式)	求和
Avg(表达式)	求平均值
Count(表达式)	求记录数
Max(表达式)	求最大值
Min(表达式)	求最小值

5.条件表达式示例

数值条件表达式：

条件	说明
studentresult = 88
studentresult <> 88
studentresult <= 88
studentresult >= 88
studentresult Between 66 and 88

文本条件表达式：

条件	说明
studentname =”孙悟空”	等于
studentname =”孙悟空” or studentname=”猪八戒”	或者
Left(Studentname,1)=”孙”	左边开始截取1位
studentname in(“孙悟空”,”猪八戒”)	显示指定范围的值
studentname like “孙*”	姓孙的
Len(studentname)>=3	长度大于等于3
Mid(StudentName,2,1)=”悟”	从位置2开始，截取1位

日期/时间表达式：

条件	说明
ExamDate=#2020-1-1#	考试日期为2020-1-1
ExamDate Between Date()-31 and Date()	查询前31天的考试成绩
Year(Birthday)=1989	查询1989年出生的学生
Year(Birthday)=Year(Date())	查询今年出生的学生

在查询中进行汇总统计

在Access中，可以执行许多类型的计算。在表达式中使用计算既可以减少存储空间，也可以避免在更新数据时产生不同步带来的错误。计算包括了总和、平均值、计算、最大值、标准偏差和方差等。用户也可以自定义计算，如可以用一个或多个字段的值进行数值、日期和文本计算。

参数查询

前面所建立的查询，无论是内容还是条件，都是固定的。如果用户希望根据不同的条件来查找记录，就需要不断修改或建立查询，这样做很麻烦。为了方便用户，Access提供了参数查询。参数查询是动态的，它利用对话框提示用户输入参数并检索符合所输入参数的记录值。

(1)单参数查询

在指定字段的条件行单元格中输入”[请输入xx:]”

(2)多参数查询

交叉表查询

交叉表查询以水平方式和垂直方式对记录进行分组，并计算和重构数据，可以简化数据分析。交叉表查询可以对数据求和、平均值、最大值、计数等计算。

在创建交叉表查询时，用户需要指定三种字段。

(1)行标题：放在查询数据表最左侧的分组字段构成行标题。

(2)列标题：放在查询数据表最上面的分组字段构成列标题。

(3)值字段：放在行与列交叉位置上的字段为值字段，用于计算。

其中，列标题和值字段只能有一个，行标题可以有多个。

使用“交叉表查询向导”创建查询

案例1：查询学生表中每个年级、各个性别的学生人数。

案例2：查询每个学生各个科目的最高分

使用设计视图创建查询

案例1：查询每个学生每个科目的最高分

操作查询

前面介绍的几种查询，都是根据特定的查询规则，从数据源中提取符合条件的动态记录集，但对数据源的内容并不进行任何的改动。操作查询，就是能进行操作的查询，主要用于对表数据进行操作，如删除记录、更新记录、追加记录等。

生成表查询

生成表查询就是将查询的结果保存到一个表中，这个表可以是一个新表，也可以是已存在的表。但如果将查询结果保存在已有的表中，则该表达式中原有的内容被删除。

案例1：查询所有男生，并保存到一个新表中

删除查询

如果需要从数据库的某个表中有规律的删除一些记录，可以使用删除查询来解决。

如果要从多个表中删除相关记录，必须同时满足以下条件：

(1)已经定义了表间关系

(2)在“编辑关系”对话框中已选中“实施参照完整性”复选框

(3)在“编辑关系”对话框中已选中“级联删除相关记录”复选框

案例1：删除科目编号为10的科目记录

更新查询

当需要根据指定条件更改一个或多个表中的记录时，可以采用更新查询。

案例1：查询所有不及格的成绩记录，并且+5分。

追加查询

维护数据库时常常需要将某个表中符合一定条件的记录添加到另一个表中。追加查询很容易就能实现这种操作，该查询可以用于将各表中的数据整合到一个表中。

SQL查询

在Access中，有些查询用查询向导和查询设计器无法实现，此时只能使用SQL查询才可以完成。SQL语言作为一种通用的数据库操作语言，实际工作中有时必须使用这种方式才能完成一些复杂的查询工作。

SQL 简介

SQL(Structured Query Language,结构化查询语言)是关系型数据库的标准语言，当今所有关系数据库管理系统都是以SQL为核心的。SQL语言建立于1974年，随着SQL的发展，ISO、ANSI等国际权威标准化组织都为其指定了标准，从而建立了SQL在数据库领域的核心地位。

1.SQL语言特点

(1)综合统一

(2)高度非过程化

(3)共享性

(4)语言简洁、易学易用

2.SQL语言功能

(1)数据定义语言DDL:

​ 用于定义和修改表、定义视图和索引。包括CREATE(创建)、DROP(删除)和ALTER(修改)。

(2)数据操作语言DML:

​ 用于对表或视图的数据进行添加、删除和修改等操作。包括INSERT(插入)、DELETE(删除)和UPDATE(修改)。

(3)数据查询语言DQL:

​ 用于检索数据库中的数据。包括SELECT(选择)。

(4)数据控制语言DCL:

​ 用于控制用户对数据库的存取权限。包括GRANT(授权)和REVOTE(回收权限)。

3.SQL视图

在Access中，所有通过查询设计器设计出的查询，系统在后台都自动生成了相应的SQL语句。用户在SQL视图中可以看到相关的SQL命令。

SQL的数据定义功能

常用数据类型对照表

英文	中文
Text	文本
Memo	备注
Integer	整数
Decimal	小数
Boolean	是否
Date/Time	日期

1.建立表结构

CREATE TABLE tb1
(
  uname Text(10),
  upwd  Text(10),
  uage  int
)

2.修改表结构

ALTER TABLE tb1
ADD birthday Date

ALTER TABLE tb1
ALTER upwd Text(6)

ALTER TABLE tb1
DROP uage

3.删除表

DROP TABLE tb1

定义带自动编号的主键

create table table1 (id autoincrement(1,1) primary key) 

SQL的数据操纵功能

SQL语言的数据操纵功能主要包括插入记录、更新记录和删除记录。SQL数据操纵功能的核心命令动词有INSERT、UPDATE和DELETE.

1.插入记录

INSERT INTO <表名>[字段1,字段2,字段3…]

VALUES(值1,值2,值3…)

INSERT INTO Subject(SubjectName,ClassHour,GradeId)
VALUES("HTML",285,1)

2.修改记录

UPDATE <表名> SET 字段1=值1,字段2=值2,字段3=值3

WHERE 条件表达式

UPDATE Subject
SET ClassHour=380
WHERE SubjectNo=6

3.删除记录

DELETE FROM <表名> [where 条件表达式]

DELETE FROM Subject
WHERE SubjectNo=6

数据查询SELECT语句

数据查询是数据库的核心功能，SQL语言提供了SELECT 语句进行数据查询。SELECT 语句的主要功能是实现对数据源数据的选择、投影和连接，对筛选字段的重命名，对记录的分组、汇总、排序等操作。SELECT 语句在数据库系统中是功能最强、最常用也是最灵活的语句，掌握好SELECT语句是进行数据库开发的基础。

1.SELECT 语句结构

SELECT [ALL | DISTINCT | TOP N] * |<字段列表>

FROM <表名>

[WHERE 条件表达式]

[GROUP BY 字段 [HAVING 筛选条件]]

[ORDER BY 字段 [ASC|DESC]]

2.SELECT 语句操作示例

(1)检索表中所有的所有字段

SELECT * FROM Student

(2)查询满足条件的记录和指定的字段

SELECT StudentName,Sex 
FROM Student
WHERE Sex='男'

(3)进行分组统计

SELECT Sex,Count(*) AS 数量
FROM Student
GROUP BY Sex

(4)对查询结果进行排序

SELECT *
FROM Result
ORDER BY StudentResult DESC

(5)查询数据来自多个表或查询

SELECT Student.StudentName,Result.*
FROM Result 
INNER JOIN Student ON Result.StudentNo=Student.StudentNo

(6)嵌套查询

SELECT *
FROM Student
WHERE GradeId=
(
    SELECT GradeId FROM Grade WHERE GradeName="初一"
)

思考题

1.查询与数据表中的筛选操作有什么相似和不同之处。

2.Access 提供的常用查询有哪几类

3.简述选择查询与操作查询的区别。

4.在查询向导中，明细与汇总的含义和区别是什么

5.参数查询在查询准则的确定上有什么特点

6.使用SQL语句可以实现所有查询设计视图的操作吗？