查询的创建和使用

1、第5章 查询的创建和使用5.1查询概述5.2使用查询向导创建查询5.3设计视图的使用5.4高级查询的创建5.5SQL查询5.6查询优化1本章要点 了解查询的概念、功能和类型掌握使用查询向导创建查询的方法掌握查询视图的使用方法掌握参数查询、交叉表查询、操作查询等高级查询的操作方法掌握创建SQL查询的操作方法，掌握SQL的应用。25.1 查询概述5.1.1查询的基本概念查询是Access数据库中的一个对象，是按照一定的条件或要求对数据库中特定数据信息的查找。查询可以对一个数据表或多个数据表中存储的数据信息进行查找、统计、计算和排序等。查询实际上就是将这些分散的数据按一定的条件重新组织起来，形成一个

2、动态的数据记录集合，而这个记录集在数据库中并没有真正存在，只是在查询运行时从查询源表的数据中抽取创建，数据库中只是保存查询的方式。当关闭查询时，动态数据集会自动消失。35.1 查询概述5.1.1查询的基本概念 通过查询浏览表中的数据，分析数据或修改数据。 利用查询可以使用户的注意力集中在自己感兴趣的数据上，而将当前不需要的数据排除在查询之外。 将经常处理的原始数据或统计计算定义为查询，可大大简化处理工作。用户不必每次都在原始数据上进行检索，从而提高了整个数据库的性能。 查询的结果可以用于生成新的基本表，可以进行新的查询，还可以为窗体、报表、数据访问也提供数据。45.1 查询概述5.1.2 查询

3、的功能选择表、选择字段、选择记录、排序记录、执行计算、建立表、建立基于查询的报表和窗体建立基于查询的图表、使用查询作为子查询、修改表55.1 查询概述5.1.3查询的类型 Access有五种类型的查询：选择查询、参数查询、交叉表查询、 操作查询、SQL查询查询的创建方法：使用查询向导创建查询、使用查询设计器创建查询、使用SQL命令创建查询65.2 使用查询向导创建查询方法:打开库后,单击左边对象列表的”查询”单击”新建”按钮75.2 使用查询向导创建查询常用的查询向导有：简单查询向导、交叉表查询向导、 查找重复项查询向导、查找不匹配项查询向导85.2 使用查询向导创建查询5.2.1简单查询向导

4、 在Access中可以利用简单查询向导创建查询，可以在一个或多个表（或其他查询）指定的字段中检索数据。而且，通过向导也可以对记录组或全部记录进行总计、计数以及求平均值的运算，还可以计算字段中的最大值和最小值。95.2 使用查询向导创建查询例查找并显示“商品进销管理系统”中的商品基本情况。通过向导创建查询简单方便，但是更复杂的查询如带条件的查询、查询结果的排序、计算等等，不能使用向导来完成，必须使用查询的设计视图实现。105.2 使用查询向导创建查询5.2.2 交叉表查询向导 使用交叉表查询可以计算并重新组织数据的结构，这样可以更加方便地分析数据。交叉表查询计算数据的总计、平均值、计数或其他类型

5、的总和，这种数据可分为两组信息：一类在数据表左侧排列，另一类在数据表的顶端。115.2 使用查询向导创建查询5.2.2 交叉表查询向导 例:在”商品进销管理系统”数据库中创建:客户订购各种商品的数量统计125.2 使用查询向导创建查询5.2.3 查找重复项查询向导 根据“查找重复项”查询的结果，可以确定在表中是否有重复的记录，或记录在表中是否共享相同的值。135.2 使用查询向导创建查询5.2.3 查找重复项查询向导 例:创建”各类商品分别有几种不同型号”的查询145.2 使用查询向导创建查询5.2.4 查找不匹配项查询向导 使用“查找不匹配项查询向导”可以在一个表中查找与其另一个表中没有相关

6、记录的记录。155.2 使用查询向导创建查询5.2.4 查找不匹配项查询向导 例:在商品表中查找那些在订单表中没有订购记录的商品记录165.3 设计视图的使用 使用设计视图不仅可以创建各种类型的查询，也可以对已有的查询进行修改。175.3 设计视图的使用5.3.1查询的视图Access中，查询有三种视图：设计视图、数据表视图、SQL视图。1.设计视图 查询设计视图窗口分为两个部分，上部分显示查询所使用的表对象，下部分是定义查询设计的网格。查询设计网格每一列对应着查询结果数据集中的一个字段，每一行分别是字段的属性和要求。185.3 设计视图的使用字段：设置定义查询对象时要选择表对象的哪些字段。表

7、：设置字段的来源。排序：定义字段的排序方式。显示：设置选择字段是否在数据表视图中显示出来。条件：设置字段限制条件。192、数据表视图数据表视图主要用于在行和列格式下显示查询结果（即记录集合）。3、SQL视图Access能将设计视图中的查询翻译成SQL语句。用户可以在SQL视图中查看或改变SQL语句，从而改变查询。打开SQL视图的方法： 打开查询的数据表视图，然后选择“视图SQL视图”命令。205.3 设计视图的使用5.3.2查询条件使用条件时，如果在条件行写入多个条件，则条件之间是“与”的关系，如果写在不同行，表示条件之间是“或”的关系。21 “条件”是指在查询中用来限制检索记录的表达式，它是

8、算术运算符、逻辑运算符、常量、字段值和函数等的组合。1、简单条件表达式 例如，如果“zzmm”字段(政治面貌)的准则行输入“Is Null”表示查找该字段值为空的记录。如果输入“Is Not Null”表示查找该字段值为非空的记录。22 “条件”是指在查询中用来限制检索记录的表达式，它是算术运算符、逻辑运算符、常量、字段值和函数等的组合。、运算符 Access的表达式中，使用的运算符包括算术运算符、关系运算符、逻辑运算符和字符运算符等。 算术运算符 23关系运算符 运算符含义运算符含义大于=大于等于不等于“删除”菜单项或者按键盘上的“Delete”键。或右键单击要删除的表/查询,弹出的菜单选删

9、除325.3 设计视图的使用4.在查询设计视图中操作字段添加和删除字段添加方法:将指定字段从字段列中拖动到设计网格或者双击字段列表中的字段名删除方法:选中设计网格中的字段,然后按Delete键移动查询设计网格中的字段在查询中更改字段名方法:右键单击设计网格中要修改的字段,弹出的菜单选属性然后在标题后输入查询要显示的字段名称335.3 设计视图的使用4.在查询设计视图中操作字段在查询中插入或者删除条件行单击要插入行的下方行,插入-行在查询中添加和删除条件在查询设计视图中更改列宽使用查询设计网格排序记录使用星号”*”对字段进行计算 例:对商品表按商品名称计算平均出厂价格控制查询中显示的记录数345

10、.4 高级查询的创建5.4.1参数查询 参数查询是动态的，利用对话框提示用户输入参数，并检索符合所输入参数的记录或值。 在Access中用户可以创建单参数查询和多参数查询。355.4 高级查询的创建单参数查询在Access中创建参数查询是在创建查询时，在查询条件区域中输入用方括号“ ”括起来的提示文本信息。例:创建”按客户姓名查询订货情况”的查询365.4 高级查询的创建多参数查询 用户还可以建立多参数查询，提示用户输入多个条件来检索指定记录。例创建多参数查询，提示用户输入商品名称和生产厂家信息。375.4 高级查询的创建5.4.2 交叉表查询 创建交叉表查询的方法 一个表或者查询可以使用“交

11、叉表查询向导” 如果要从多个表或查询中创建交叉表查询,可以在查询设计视图中自己设计交叉表查询例创建交叉表查询，显示2010年3月每类商品订货情况统计,包括商品名称、客户姓名、订货数量和总数等信息。385.4 高级查询的创建5.4.3操作查询根据功能的不同，操作查询分为生成表查询、更新查询、追加查询和删除查询。395.4 高级查询的创建操作查询与其他查询的区别 选择查询、交叉表查询、参数查询的运行结果是从数据基本表中生成的动态记录集合，并没有物理存储，也没有修改基本数据表中的记录。用户可以直接在“数据表视图”中查看查询结果。 操作查询的运行结果是对数据表进行创建或更新，无法通过在“数据表视图”中

12、查看其运行的结果。 在“查询”对象窗口中每个操作查询图标都有一个感叹号。401、更新查询如果要对数据表中的某些数据进行有规律地、成批的更新替换操作，就可以使用更新查询来实现。例：在“商品管理系统”数据库中将“商品”表中的生产厂家为“青岛海尔集团”的记录改为“海尔集团”。411、更新查询例：根据用户输入的商品名称对出厂价格进行调整思考:对教务管理系统库的学生选课表创建更新查询,对根据输入的课程号调整相应课程的学生成绩.422、追加查询追加查询是将从一个或多个表中筛选出来的记录添加到其他表中。使用追加查询可以从其他的Access数据库甚至同一数据库的其他表中导入数据。与选择查询和更新查询类似，追加

13、查询的记录范围也可以利用条件加以限制。432、追加查询例:将订单表中未发货的商品记录追加到一个结构类似内空为空的表中思考:将教务管理系统库的学生选课表成绩不及格的学生记录追加到一个结构类似内容为空的表中443、删除查询删除查询可以从数据库的某个表中删除一组记录，删除查询将删除整个记录，而不只是记录中的所选字段。 应用删除查询，应该指定相应的删除条件，否则就会删除数据表中的全部数据。例:删除商品表中商品号为21000001的记录思考:删除学生选课表中所有不及格成绩的记录454、生成表查询生成表查询就是利用一个或多个表中的全部或部分数据创建新表，如果将查询结果保存在已有的表中，则该表中原有的记录将

14、被删除。例：创建用户“张磊”订购“空调”的信息的生成表查询。思考:创建由志强所选课程信息的生成表查询465.5 SQL查询在Access中，不是所有的查询都可以用“查询向导”和“查询设计视图”来完成，有些比较复杂的查询只能通过SQL语句来实现。SQL是Structured Query Language的缩写，即结构化查询语言，是关系数据库的标准查询语言。在Access中，能够使用SQL的数据定义、数据查询和数据操纵功能。475.5.1 SQL数据定义功能1、基本表的创建命令格式：CREATE TABLE ( (长度) NOT NULL , (长度) NOT NULL , )注意：表示里面的内容

15、为必填项；表示里面的内容为可选项，书写命令时不要输入和。主键后加primary48说明：（1）字段名与数据类型、数据类型与长度之间必须有空格隔开，各个字段定义之间用逗号分开。（2）常用的数据类型有：文本（CHAR(n)或TEXT）数字（单精度REAL、双精度FLOAT、长整型INT或INTEGER）、日期/时间（DATE、TIME、DATETIME）、货币(MONEY)、 备注(memo)是/否(BIT)、 OLE对象(IMAGE)等。49举例： 设已经存在一个student数据库，在其中创建一个学生表，表结构为：学号 (char(13)，不能为空值,主键)、姓名(char(8)、性别(cha

16、r(2)、出生日期(date)、 成绩(float)、简历(memo)。create table 学生表(学号 char(13) not null primary,姓名 char(8),性别 char(2),出生日期 date,成绩 float 、简历 memo )50在Access中书写及运行SQL创建表命令的操作步骤如下：（1）打开数据库；（2）打开“查询设计视图”窗口；（3）选择“查询SQL特定查询数据定义”命令；（4）在弹出的“数据定义查询”窗口中输入SQL创建表命令（5）单击工具栏上的“运行”按钮；（6）保存查询。512、创建索引命令格式：CREATE INDEX ON ( ASC|

17、DESC ， ASC|DESC，.)WITH PRIMARY说明：（1）该命令可为表创建一个单字段索引或多字段索引。 （2） 选择ASC表示建立升序索引；选择DESC表示建立降序索引；省略时系统默认升序索引。 （3） 使用WITH PRIMARY短语可将该索引字段指定为主键。52举例：1.为学生表的性别字段创建一个索引。CREATE INDEX XB ON 学生表(性别) 2.为学生表按性别升序和出生日期降序创建一个多字段索引CREATE INDEX XBSR ON 学生表(性别,出生日期 DESC) 533、表的修改（1）修改字段属性命令格式：ALTER TABLE ALTER (长度) ,

18、 (长度), 例：将“学生表”的“姓名”字段的长度从8改为10。ALTER TABLE 学生表 ALTER 姓名 char(10)54（2）增加字段命令格式：ALTER TABLE ADD (长度) , (长度), 例：在“学生表”中添加“奖励否”字段(BIT)ALTER TABLE 学生表 ADD 奖励否 BIT55（3）删除字段命令格式：ALTER TABLE DROP , , 例：删除“学生表”中的“奖励否”字段和“简历”字段。ALTER TABLE 学生表 DROP 奖励否 ,简历565.5.2 SELECT 语句SQL查询数据最简单的格式为： SELECT FROM例：查询“学生基本

19、情况表”中学生的姓名、专业信息。 SELECT xm, zy FROM 学生基本情况表57一、单表查询命令格式：SELECT ALL | DISTINCT | TOP PERCENT AS , AS FROM WHERE GROUP BY HAVING ORDER BY ASC|DESC,ASC | DESC58说明：1、ALL | DISTINCT中，ALL表示显示全部查询结果，DISTINCT表示查询结果相同的只显示一个。 缺省时默认ALL。例：查询学生基本情况表中学生的性别。 SELECT xb FROM 学生基本情况表与 SELECT DISTINCT xb FROM 学生基本情况表查

20、询结果不同。592、 AS 可以是表中的一个字段、一个表达式，还可以使用函数： SUM()、 AVG ()、 COUNT (*)、 MIN ()、 MAX ()例：1、查询“学生基本情况表”中学生的姓名、专业。 SELECT xm, zy FROM 学生基本情况表 2、查询“学生基本情况表”中学生的全部信息。 SELECT * FROM 学生基本情况表 3、查询“学生选课表”中学生成绩的最大值。 SELECT MAX(xscj) FROM 学生选课表60为该列规定了列名。例：查询学生基本情况表中所有学生的姓名和年龄。SELECT xm, YEAR(DATE()-YEAR(csrq) AS 年龄

21、FROM 学生基本情况表613、WHERE 表示条件查询。例：查询学生基本情况表中1985年1月1日以后出生的学生的姓名、性别和出生日期。 4、GROUP BY 表示分组查询。例：按性别分组查询学生基本情况表中男、女学生的人数。SELECT xb, COUNT(*) as 人数 FROM 学生基本情况表GROUP BY xbSELECT xm, xb, csrq FROM 学生基本情况表WHERE csrq=#1985-1-1#625、 HAVING ：与 GROUP BY 子句一起使用，用于指定分组后各组必须满足的条件。 例：查询学生选课表中至少选修了3门课程的学生的学生号和成绩的平均分。S

22、ELECT xsh, avg(xscj) as 平均分FROM 学生选课表GROUP BY xsh HAVING COUNT(*)=3636、ORDER BY ASC | DESC 表示对查询结果按指定字段值的升序或降序排序，默认升序。例1：按基本工资的降序查询教师基本情况表中所有教师的姓名、基本工资。例2：按职称升序和基本工资的降序查询教师基本情况表中所有教师的姓名、职称和基本工资。SELECT jsxm, jbgz FROM 教师基本情况表ORDER BY jbgz DESCSELECT jsxm, zc, jbgz FROM 教师基本情况表ORDER BY zc, jbgz DESC64

23、二、多表查询命令格式： SELECT ALL | DISTINCT | TOP PERCENT. AS ,. AS FROM INNER JOIN |LEFT JOIN | RIGHT JOIN ON WHERE GROUP BY HAVING ORDER BY ASC|DESC,ASC | DESC65举例：按成绩降序查询“学生基本情况表”和“学生选课表”中学号为“2”的学生的姓名、课程号和成绩。select 学生基本情况表.xm,学生选课表.kch,学生选课表.xscjFROM 学生基本情况表 INNER JOIN 学生选课表ON 学生基本情况表.xsh=学生选课表.xshWHERE 学生

24、基本情况表.xsh=2ORDER BY 学生选课表.xscj DESC665.5 SQL查询5.5.3联合查询 联合查询将两个或更多个表或查询中的字段合并到查询结果的一个字段中。使用联合查询可以合并两个表中的数据。675.5 SQL查询5.5.4传递查询 传递查询使用服务器能接受的命令直接将命令发送到 ODBC 数据库，如 Microsoft SQL Server。例如，可以使用传递查询来检索记录或更改数据。使用传递查询，可以不必链接到服务器上的表而直接使用它们。传递查询对于在 ODBC 服务器上运行存储过程也很有用。685.6 查询优化5.6.1查询优化的规则1.如果窗体或报表的“记录源”属性设置为SQL语句，可以将SQL语句另存为查询，然后将“记录源”属性设置为查询的名称。2.如果要对ODBC数据源进行大量更新查询，可将“出错中止”属性设置为“是”以优化服务器的性能。3.如果数据库不经常改动，则使用生成表查询，通过查询结果来创建