Access数据库技术及应用2010版第4章使用查询搜索信息

第4章

使用查询搜索信息主编：齐晖潘惠勇Access数据库技术及应用1第4章使用查询搜索信息4.1查询概述4.2使用向导创建查询4.3查询中的表达式4.4使用设计视图创建查询4.5创建操作查询4.6创建SQL查询第4章使用查询搜索信息24.1查询概述查询是向数据库提出询问，数据库按指定要求从数据源提取并返回一个数据集合查询是Access数据库的对象之一，其数据源可以是一张表，也可以是多个关联的表查询的结果可以供用户查看，也可作为创建其他查询、窗体、报表的数据源第4章使用查询搜索信息34.1.1一个查询的例子【例4.1】查询工设专业所有团员的学生信息。第4章使用查询搜索信息44.1.2查询的主要功能选择若干字段进行显示排序记录选择满足条件的记录进行显示对数据进行统计与计算如统计学生的平均成绩、不同性别员工的人数等也可以建立计算字段，如“学生”表中有学生的出生日期，可以在查询中建立“年龄”计算字段对源数据进行修改建立新表为其他数据库对象提供数据源第4章使用查询搜索信息54.1.3查询的类型选择查询参数查询交叉表查询操作查询SQL查询第4章使用查询搜索信息64.1.4查询的视图模式设计视图数据表视图SQL视图数据透视表视图数据透视图视图第4章使用查询搜索信息74.2使用向导创建查询4.2.1使用向导创建简单查询【例4.2】利用向导创建查询，要求输出学生的“学号”、“姓名”、“性别”和“班级名称”4个字段的信息。该查询以“查询向导—学生班级信息”保存。分析步骤小结第4章使用查询搜索信息8【例4.3】利用向导创建查询，要求输出学生的“学号”、“姓名”、“班级名称”、“课程名称”和“成绩”5个字段的信息。该查询以“查询向导—学生成绩信息”保存。分析步骤小结第4章使用查询搜索信息9【例4.4】利用向导创建查询，要求输出学生的“学号”、“姓名”、“班级名称”、“平均成绩”和“总成绩”5个字段的信息。该查询以“查询向导—学生成绩统计”保存。分析步骤小结第4章使用查询搜索信息104.2.2使用“查找重复项查询向导”创建查询【例4.5】利用向导创建查询，要求输出学生的“学号”、“姓名”、“性别”和“班级名称”4个字段的信息，要求将性别一致的信息放在一起。该查询以“查询向导—学生性别信息”保存。分析步骤小结思考：要求查询学生的“学号”、“姓名”、“班级名称”、“课程名称”和“成绩”信息，“课程名称”一致的显示在一起。第4章使用查询搜索信息114.2.3使用“查找不匹配项查询向导”创建查询【例4.6】利用“查找不匹配项查询向导”，将没有成绩信息的学生基本信息（“学号”、“姓名”、“性别”、“班级名称”）显示出来，保存名称为“查询向导—没有成绩的学生”。分析步骤小结第4章使用查询搜索信息12思考：创建查询，将没有成绩信息的课程基本信息显示出来，包括“课程号”、“课程名”和“学分”。查询运行结果如图4.27所示。第4章使用查询搜索信息134.2.4使用“交叉表查询向导”创建查询【例4.7】利用“交叉表查询向导”创建交叉表查询，结果要求显示学生的“姓名”位于结果的左侧，“课程名称”位于结果的顶部，在行与列的交叉点放置该学生的该门课程的成绩。查询名称为“查询向导—学生成绩信息_交叉表”。查询运行结果如图4.28所示。第4章使用查询搜索信息14思考：要求统计每个班级的男生、女生人数。班级名称在查询结果左侧，性别在查询结果上部。查询结果如图4.34所示。第4章使用查询搜索信息154.3查询中的表达式表达式指由运算符、操作数和函数构成的表示各种运算关系的式子常用表达式分为数学表达式、字符串表达式、关系表达式、逻辑表达式。此分类的依据是表达式中的运算符第4章使用查询搜索信息16常用的算术运算符运算符运算关系表达式实例运算结果+加法4+37-减法4-31*乘法2*50100/除法11/25.5\整除11\25^指数运算5^225Mod取模运算（求余）18Mod53-取负-a（设a=-8）8第4章使用查询搜索信息17连接运算符用于将两个文本型数据进行连接，形成一个新的字符串。“&”和“+”。“&”运算符用来强制两个表达式作字符串连接。最常用的是“&”运算符。例如：要将“学生”表的“班级”字段和“姓名”字段合成一个字段，将两个文本型字段连接即可，表达式可以写为[bjmc]&[xm]。第4章使用查询搜索信息18关系运算符运算符运算关系表达式实例运算结果=等于1=2False<>

不等于"1"<>"2"True>

大于"a">"b"False<

小于"a"<"b"True>=大于或等于"ab">="ac"False<=小于或等于"ab"<="ac"True第4章使用查询搜索信息19逻辑运算符运算符运算关系表达式实例运算结果Not非Not（1>0)值为False，由真变假或由假变真，即进行取“反”操作And与(4>5)And（3<4)值为False，两个表达式的值均为True，结果才为True，否则为FalseOr或(4>5)Or（3<4)值为True，两个表达式中只要有一个值为True，结果就为True，只有两个表达式的值均为False，结果才为False第4章使用查询搜索信息20常用的特殊运算符运算符功能说明In用于指定匹配列表，只要一个列表值与查询值一致，表达式返回为TrueBetween用于指定数值或字符范围，查询值在范围内，表达式返回为TrueIsNull如果字段值为Null（空值），表达式返回为TrueIsNotNull如果字段值不是Null（空值），表达式返回为True第4章使用查询搜索信息21“Like”运算符在表达式中可以使用的通配符统配符含义表达式实例可匹配字符串*可匹配任意多个字符M*Max，Money？可匹配任何单个字符M？Me，My#可匹配单个数字字符123#1234，1236[charlist]可匹配列表中的任何单个字符[b-f]b,c,d,e,f[!charlist]不允许匹配列表中的任何单个字符[!b-f]除b,c,d,e,f以外的字母第4章使用查询搜索信息224.3.2函数Access提供了数百个标准函数如聚合函数、数值函数、字符串函数、日期/时间函数、类型转换函数等用户也可以通过VBA建立用户自定义函数函数的使用格式为：函数名[(参数表)]第4章使用查询搜索信息23常用聚合函数函数名功能Avg（字段名）计算指定字段中的一组值的平均值Sum（字段名）计算指定字段中的一组值的总和Count（字段名）计算指定字段中的一组值的个数，与字段记录的数值无关Max（字段名）计算指定字段中的一组值的最大值Min（字段名）计算指定字段中的一组值的最小值第4章使用查询搜索信息24常用的数值函数函数名功能示例Abs(x)求x的绝对值Abs(-3.3)结果为3.3Int(x)取不大于x的最大整数Int(3.6)结果为3Int(-3.6)结果为-4Fix(x)取x的整数部分Fix(3.6)结果为3Fix(-3.6)结果为-3Round(x,n)对x进行四舍五入，保留n位小数Round(3.1415926,2)结果为3.14Sgn(x)判断x的符号，若x>0，返回值为1；若x<0，返回值为-1；若x=0，返回值为0Sgn(6)结果为1Sgn(-6)结果为-1Sqr(x)求x的平方根Sqr(25)结果为5Exp(x)求以e为底的指数（ex）Exp(1)结果为2.718Log(x)求x的自然对数（Lnx）Log(10)结果为2.303第4章使用查询搜索信息25常用的字符串函数函数名功能示例Len(s)求字符串s的长度（字符数）Len("人数1234")结果为6Left(s,n)截取字符串s左端的n个字符，生成子串Left("ABC123",4)结果为"ABC1"Right(s,n)截取字符串s右端的n个字符，生成子串Right("ABC123",4)结果为"C123"Mid(s,m,n)从字符串s的第m个字符位置开始，取出n个字符Mid("ABC123",2,3)结果为"BC1"LTrim(s)删除字符串s左端的空格LTrim("ABC")结果为"ABC"RTrim(s)删除字符串s右端的空格RTrim("ABC")结果为"ABC"Trim(s)删除字符串s两端的空格Trim("123")结果为"123"第4章使用查询搜索信息26常用的日期/时间函数函数名功能示例Date()返回当前系统日期

Time()返回当前系统时间

Year(D)返回日期中的年份数Year(#2012-1-21#)结果为数值2012Month(D)返回日期中的月份数Month（#2012-1-21#)结果为数值1Day(D)返回日期中的一个月的某一天Day（#2012-1-21#)结果为数值21Hour(D)返回时间中的钟点数Hour(#13:01:01#)结果为数值13DateAdd(S1,x,D)返回添加指定时间间隔的日期，参数S1可以是"yyyy"，"q"，"m"，"d"分别表示年数、季度数、月数、天数。DateAdd("yyyy",2,#2012-1-1#)结果为#2014-1-1#DateAdd("q",2,#2012-1-1#)结果为#2012-7-1#DateAdd("m",2,#2012-1-1#)结果为#2012-3-1#DateAdd("d",2,#2012-1-1#)结果为#2012-1-3#第4章使用查询搜索信息27常用的日期/时间函数（续）函数名功能示例DateDiff(S1,D1,D2)返回两个指定日期之间的间隔数，参数S1可以是"yyyy"，"q"，"m"，"d"分别表示年数、季度数、月数、天数。DateDiff("yyyy",#2012/7/1#,#2013/8/1#)结果为1DateDiff("q",#2013/1/1#,#2013/8/1#)结果为2DateDiff("m",#2013/1/1#,#2013/8/1#)结果为7DateDiff("d",#2013/1/1#,#2013/8/1#)结果为212Dateserial(year,month,day)返回包含指定的年、月、日数字的日期。DateSerial(2013,1,2)结果为#2013-1-2#第4章使用查询搜索信息28常用类型转换函数函数名功能示例Asc(x)将字符转换为相应的ASCII码值Asc("A")结果为65Chr(x)将ASCII码值转换为相应的字符Chr(97)结果为"a"Str(x)将数值转换成对应的字符串Str(123.45)结果为"123.45"Val(x)将字符串x转换成对应的数值Val("-123.45")结果为-123.45CDate(x)将x的值强制转换为Date类型CDate("2012-1-1")结果为#2012-1-1#第4章使用查询搜索信息294.4使用设计视图创建查询第4章使用查询搜索信息304.4.1基本查询基本查询是指从一个或多个表或查询中选择若干字段进行显示的查询，是最简单的查询。【例4.16】创建一个如图4.36所示的学生班级信息基本查询。第4章使用查询搜索信息31【例4.17】创建一个如图4.38所示的学生课程开课学期基本查询。第4章使用查询搜索信息324.4.2含新字段查询含新字段查询是指查询结果的新字段是表达式的计算结果，而不是直接来源于表或查询。【例4.18】创建一个如图4.40所示的显示学生的年级和政治面貌的查询。第4章使用查询搜索信息33【例4.19】创建一个如图4.45所示的学生出生日期的年月日查询。第4章使用查询搜索信息344.4.3选择查询选择查询是指选择满足指定条件的内容进行显示的查询。其中，条件表达式是创建这种查询的重点。【例4.20】创建一个如图4.48所示的男生信息查询。第4章使用查询搜索信息35【例4.21】创建一个如图4.50所示的1990年9月1日以前出生的学生信息查询。第4章使用查询搜索信息36【例4.22】创建一个出生日期在1990年9月1日和1992年9月1日之间的学生信息查询（含1990年9月1日和1992年9月1日出生的），如图4.52所示。第4章使用查询搜索信息37【例4.23】创建一个出生日期在1989年9月1日之前或者是男性的学生信息查询，如图4.54所示。第4章使用查询搜索信息384.4.4参数查询参数查询是指查询运行时由用户在对话框内书写参数（查询关键字），然后根据用户的输入选择数据进行显示，是一种特殊的选择查询参数查询具有极大的灵活性，常作为窗体、报表等Access数据库对象的数据源【例4.24】创建一个课程信息查询，用户输入课程名关键字，查询结果输出课程名中含关键字的所有课程信息。查询运行后出现如图4.57所示对话框，如果用户填写“设计”，则运行结果如图4.58所示。第4章使用查询搜索信息39课程名关键字参数查询结果—输入“设计”：第4章使用查询搜索信息40如果用户填写的是“动画”，则查询的运行结果如图4.59所示。第4章使用查询搜索信息414.4.5排序查询排序查询是指按照一个或多个字段的值排序后显示的查询结果。【例4.25】创建学生选课信息查询，要求结果按照课程号的升序排列，结果如图4.62所示。第4章使用查询搜索信息42【例4.26】创建学生选课信息查询，要求结果先按照课程号的升序排列，再按照开课学期的降序排列，结果如图4.64所示。第4章使用查询搜索信息434.4.6分组统计查询分组统计查询指的是对一列当中的若干数据做诸如平均、求和等聚合运算的查询如果不进行分组，聚合运算的对象是一列中的所有数据，最终返回一个结果如果进行分组，则聚合运算的对象是一列中同一组的数据，数据被分为几组就有几个运算结果【例4.28】统计学生中的男生、女生人数，结果如图4.68所示。第4章使用查询搜索信息44第4章使用查询搜索信息45【例4.29】统计每个院系的学生人数，结果如图4.72所示。第4章使用查询搜索信息46【例4.30】统计各个学期不同课程的选课学生数，结果如图4.74所示。第4章使用查询搜索信息474.4.7交叉表查询交叉表查询是一种特殊的分组查询，其结果形式不同于以上所有查询交叉表查询根据用户的指定进行两次分组，对分组后的数据做统计计算交叉表查询的结果既有行标题又有列标题，而行、列标题的内容均来源于源数据的数据内容。而其余种类查询结果的列标题一般来源于源数据的数据结构图4.77所示为一个交叉表查询结果第4章使用查询搜索信息48【例4.31】创建一个如图4.77所示的学生开课学期的交叉表查询。第4章使用查询搜索信息49【例4.32】创建一个如图4.79所示的教师开课人次数查询的交叉表查询。第4章使用查询搜索信息504.5创建操作查询Access2010能根据查询结果进行的操作包括：生成新表、在已经存在的表中追加新记录、更新数据表中相关数据（列）、删除数据表中相关数据（行）。第4章使用查询搜索信息514.5.1生成表查询生成表查询根据查询结果生成一个新的数据表，需要先建立查询然后设置查询结果将要存放的表的名称。查询运行后，查询结果会被系统存入指定表中。【例4.33】创建一个将学生按年级归档的生成表查询。第4章使用查询搜索信息52第4章使用查询搜索信息534.5.2追加查询追加查询将查询结果追加到一个已经存在的表的尾部。遵循数据库范式规则，查询结果的结构和被追加的表的结构必须一致。【例4.34】在例4.33中生成了表“2009年级学生归档”，现在查询2010年级的党员的基本信息，将其追加到“2009年级学生归档”表尾部。第4章使用查询搜索信息544.5.3更新查询如果需要对表中的某列数据成批地修改，可以选择更新查询。更新查询会根据查询条件，选择满足条件的记录按照更新规则，更新数据列的内容。如果没有更新条件，更新查询会更新所有记录在某一列的值。【例4.35】在“学生”表中为所有少数民族学生的高考成绩加上10分的校内附加分。第4章使用查询搜索信息554.5.4删除查询删除查询用来删除表中满足查询条件的记录。【例4.36】删除“选课成绩”表中课程号是“990801”的记录。第4章使用查询搜索信息564.6创建SQL查询Access2010中的查询最终会被翻译成SQL语言，由系统执行。SQL是用于访问和处理数据库的标准的计算机语言，比如一般查询会被翻译成SQL查询语句，操作查询会被翻译成SQL数据操作语句。SQL的全称是StructuredQueryLanguage，即结构化查询语言，是一种数据库查询和程序设计语言。SQL语言的主要功能包括数据定义、操作和维护。第4章使用查询搜索信息57Access中的查询可以通过SQL视图查看其对应的SQL语句，也可以新建一个查询，然后选择SQL视图，直接书写SQL语句。第4章使用查询搜索信息584.6.1SQL语句简介SQL包括3种主要程序设计语言类别的语句：数据定义语言、数据操作语言及数据控制语言。数据定义语言（DataDefinitionLanguage，DDL），用来建立数据库和表。例如，CREATE、DROP、ALTER等语句。数据操作语言（DataManipulationLanguage，DML），用来增加、修改、删除和查询数据库中的数据。例如，INSERT（插入）、UPDATE（修改）、DELETE（删除）、SELECT（查询）语句。数据控制语言（DataControllingLanguage，DCL），用来控制数据库组件的存取许可、存取权限等。例如，GRANT、REVOKE、COMMIT、ROLLBACK等语句。第4章使用查询搜索信息594.6.2SQL查询语句SELECTselect_listFROMtable_source[WHEREsearch_condition][GROUPBYgroup_by_expression][HAVINGsearch_condition][ORDERBYorder_expression[ASC|DESC]]第4章使用查询搜索信息60SELECT语句的核心SELECTselect_listFROMtable_source其中，select_list指字段名表，是查询需要显示字段的名称的集合，各字段名中间使用逗号间隔，字段可以是直接来源于表或查询，或者是一个表达式；table_source指查询的数据来源——表或查询。例如，从学生表中选择学号、姓名、性别、班级名称显示，对应的SQL语句如下：SELECT学生.xh,学生.xm,学生.xb,学生.bjmcFROM学生第4章使用查询搜索信息61WHERE子句用来设置查询的条件search_condition指条件表达式，可以进行筛选或表连接操作。例如，从“学生”表中选择所有女生的学号、姓名、性别、班级名称显示，对应的SQL语句如下：SELECT学生.xh,学生.xm,学生.xb,学生.bjmcFROM学生WHERE学生.xb="女"还可以进行连接操作。例如，从学生表中选择学号、姓名、班级名称，从选课成绩表选择对应学生的课程号和开课日期显示，对应的SQL语句如下：SELECT学生.xh,学生.xm,学生.bjmc,选课成绩.kch,选课成绩.kkxqFROM学生,选课成绩WHERE学生.xh=选课成绩.xh第4章使用查询搜索信息62GROUPBY子句用来设置查询的分组依据例如，统计每个学生的选课门数的SQL语句是：SELECT选课成绩.xh,Count(选课成绩.kch)ASkch之计数FROM选课成绩GROUPBY选课成绩.xh第4章使用查询搜索信息63HAVING子句用来指定查询分组统计值的条件例如，显示学生选课门数低于2门的学生选课门数统计信息，对应的