第4章 数据查询

第4章数据查询,4.1查询概述,查询是Access数据库的7种对象之一，它能够把一个或多个表中的数据抽取出来，供用户查看、更改和分析，还可以作为窗体、报表或数据访问页的记录源。利用查询可以提高处理数据的效率。Access提供了选择查询、交叉表查询、参数查询、操作查询和SQL查询等多种类型的查询，查询的实质就是使用SQL命令进行查询。,4.1.1查询的作用,Access查询可以对数据库中一个或多个表的数据进行浏览、筛选、排序、检索和统计等操作。我们可以把查询看做一个“表”，只不过是以表或查询作为数据来源的一个临时表，称为动态数据集。也就是说，查询的数据集实际上并不存在，它是在运行查询时动态生成的。,查询主要有以下用途：（1）以一个、多个表或查询为数据源，根据用户的选择生成动态的数据集。（2）对数据进行统计、排序、计算和汇总。（3）设置查询参数，形成交互式的查询。（4）使用交叉表查询，进行分组汇总。（5）使用操作查询，对数据表进行追加、更新、删除等操作。（6）查询作为其它查询、窗体、报表或数据访问页的记录源。,4.1.2查询的类型,查询的类型主要有:选择查询交叉表查询参数查询操作查询SQL查询,4.1.3创建查询的方法,1.使用查询向导.2.使用查询设计视图3.使用SQL查询语句,4.1.4查询的5种视图,设计视图SQL视图数据表视图数据透视表视图数据透视图视图。,4.2创建选择查询,4.2.1使用查询向导创建选择查询【例4.1】创建一个名为“教师基本情况”的查询，显示“教师编号”、“姓名”、“工作时间”、“职称”。【例4.2】创建一个名为“学生成绩统计”的查询，显示学生的“学号”、“总成绩”和“平均成绩”。【分析】此查询为基于单表的查询，但需要在向导中使用“汇总”。,4.2.2使用设计视图创建选择查询,【例4.3】创建一个名为“教师授课信息”的查询，查询每位教师所授课程的课程名，显示“教师编号”、“姓名”和“课程名”。【分析】“教师信息表”、“课程表”表之间没有直接的联系，需要借助第3张表“授课信息表”将两者联系起来。,4.2.3查询条件中的运算符、函数和表达式,算术运算符,关系运算符及含义,逻辑运算符,字符串连接运算符,【提示】,（1）在运算符“,【例4.28】创建一个查询，按“性别”的升序和“职称”的降序显示“教师编号”、“姓名”、“性别”和“职称”。Select教师编号,姓名,性别,职称From教师信息表OrderBy性别,职称Desc;【提示】OrderBy子句必须是SQL-Select命令中的最后一个子句。,【例4.29】显示学生的学号、姓名、班级和年龄。Select学号,姓名,班级,Year(Date()-Year(出生日期)As年龄From学生信息表;,【例4.30】查询课程号为101的成绩从高到低排序的前3名学生的“学号”、“课程号”和“成绩”。【分析】Top谓词用于输出排列在前面的若干条记录。要查找前3名学生的学生成绩需要按成绩的降序排列，排序后使用Top3显示前3名学生。SelectTop3学号,课程号,成绩From学生成绩表Where课程号=101OrderBy成绩Desc,【例4.31】查找选修101或301课程的学生的“学号”、“课程号”和“成绩”【分析】此查询需要使用特殊运算符In，检查一个属性值是否属于一组值。Select学号,课程号,成绩From学生成绩表Where课程号In(101,301);【提示】“课程号In(101,301)”指课程号等于“101”或者“301”。,【例4.32】查找学号的前4位为“0975”的学生的基本情况。Select*From学生信息表Where学号Like0975*“【提示】还可以将Where子句改写为：WhereLeft(学号,4)=0975。,2使用聚合函数,聚合函数也叫做合计函数，在SQL语句中使用聚合函数可以实现数据统计等功能。【例4.33】统计各门课程的平均分、最高分和最低分。【分析】根据题意需要按课程号对记录分组，相同课程放在一组，在同一组中使用聚合函数进行计算。,Select课程号,Avg(成绩)As平均分,Max(成绩)As最高分,Min(成绩)As最低分From学生成绩表GroupBy课程号;,【提示】如果使用了分组子句，则查询列表中的每个字段要么是分组依据的字段（GroupBy后边的字段），要么是聚合函数。如果将上述语句改写为：Select学号,课程号,Avg(成绩)As平均分,Max(成绩)As最高分,Min(成绩)As最低分From学生成绩表GroupBy课程号;,查询结果出错。原因是Select子句中的“学号”字段不是GroupBy子句的字段。平均分、最高分、最低分只有一个，而“学号”的个数可以有多个，数量不匹配，因此不能得到正确的查询结果。,【例4.34】统计各班的男同学的人数。,Select班级,Count(班级)As男同学人数From学生信息表Where性别=男GroupBy班级,【提示】,如果将上面的Select子句改写为：SelectCount(班级)As男同学人数则运行时无语法错误，但显示结果不完整，没有班级字段，看不出男同学人数是哪个班的。,【例4.35】设计一个查询，显示最低分大于等于90，且最高分小于等于100的学生“学号”。【分析】此题需要按学生的“学号”进行分组，但不是所有的学号都参加分组，只有当同一学号中的成绩在90和100之间时，才符合分组的条件，因此在查询中需要使用Having子句对分组后的结果作进一步的约束。,Select学号From学生成绩表GroupBy学号HavingMin(成绩)=90AndMax(成绩)=90AndMax(成绩)Avg(工资)执行后Access提示“Where子句(工资)Avg(工资)中不能有合计函数”的错误，这是因为在不适当的地方使用了合计函数（没有为Avg函数提供数据来源）。解决的办法是先使用子查询求出平均工资。,【说明】,（1）其中“SelectAvg(工资)From教师信息表”首先被执行（称内嵌套），计算出平均工资，结果将暂存；然后执行“Select教师编号,姓名,工资From教师信息表Where工资暂存的平均工资”，这层称为外嵌套。（2）内嵌套是一个完整的、独立的查询，不要遗漏内嵌套的记录源。,查询结果中“平均工资”的小数点位数比较多。可以使用Round进行限制。Round函数的格式为：Round（X,n），其中X为输出的值，n是需保留的小数位数。如果将平均工资保留两位小数，改进后的SQL查询为：Select教师编号,姓名,工资,Round(SelectAvg(工资)From教师信息表),2)As平均工资From教师信息表Where工资(SelectAvg(工资)From教师信息表),【例4.48】查找同时选修了课程号为“101”和“301”的学生的学号。