查询的创建和使用

第4章查询的创建和使用4.1查询概述4.1.1查询的概念根据给定的条件从数据库的一个或多个表中筛选出符合条件的记录，构成一个数据集合，供使用者查看、更改和分析使用。查询从中获取数据的表称为查询的数据源。查询的结果是一个动态的数据集合，会随着数据源的变化而变化。查询的主要功能:1.提取数据可以从一个或多个表或查询中重新组合、检索出用户需要的数据。可以从一个或多个表中选择部分或全部字段，也可以从一个或多个表中将符合条件的记录选取出来。2.数据更新更新操作包括添加、修改、删除记录。查询的主要功能:3.实现计算在查询中对相关数据进行统计、计算，还可以建立新的字段来保存计算的结果，这样可以简化处理工作，用户不必每次都在原始数据上进行检索，从而提高了整个数据库的性能。4.产生新的表并可作为其他对象的数据源可以建立一个新表来保存查询的结果。查询的结果还可作为窗体、报表等对象的数据源。4.1.2查询的类型1.选择查询最常用、最基本的一种查询。它可以根据用户提供的条件，从一个或多个表中检索数据，并且在数据表中显示结果。2.参数查询是一种交互式查询，在建立查询时为用户留有动态参数接口。在执行参数查询时会显示一个对话框，提示用户根据需要输入查询条件，然后再检索相应的记录。参数查询具有很强的灵活性。3.交叉表查询可以计算并重新组织数据的结构，更加方便地分析数据。交叉表查询可以计算数据的平均值、总计、最大值、最小值或其他类型的总和。4.1.2查询的类型4.操作查询操作查询用于添加、更改或删除数据。操作查询有以下4种类型：删除查询：删除查询可以从一个或多个表中删除一组记录。追加查询：追加查询可将一组记录添加到一个或多个表的尾部。运行追加查询的结果是向相关表中自动添加记录，增加表的记录数。更新查询：更新查询可根据指定条件对一个或多个表中的记录进行更改。生成表查询：生成表查询是利用一个或多个表中的全部或部分数据创建新表。4.1.2查询的类型5.SQL查询SQL是一种结构化查询语言，是数据库操作的工业化标准语言，所有的数据库管理系统都支持这种语言。SQL查询就是使用SQL语句创建的查询。4.1.3查询视图1.设计视图设计视图用于对查询设计进行编辑，通过设计视图可以创建SQL之外的各种类型查询。2.数据表视图数据表视图用于查看查询运行的结果，是查询的数据浏览器。3.SQL视图SQL视图是查看和编辑SQL语句的窗口，可以查看用设计视图创建的查询所产生的SQL语句，也可以对SQL语句进行编辑和修改。4.1.3查询视图4.数据透视表和数据透视图视图在数据透视表和数据透视图视图中，可以根据需要生成数据透视表和数据透视图，从而对数据进行分析，得到直观的分析结果。4.2选择查询的创建选择查询的概念：根据用户提供的条件，从一个或多个表中检索数据，并且在数据表中显示结果。1.建立单表查询【例4-1】查询学生的基本信息，要求显示学生的学号、姓名、性别、出生日期和院系等信息，所建查询命名为“学生基本信息查询”。4.2.1使用简单查询向导2.建立多表查询当所需要查询的信息来自两个或两个以上的表或查询时就需要建立多表查询。建立多表查询的各个表必须要有关联字段，并且事先应通过这些关联字段建立了表间关系。【例4-2】查询学生的课程成绩，要求显示“学号”、“姓名”、“课程号”、“课程名称”和“成绩”。4.2.2使用查询设计视图打开查询设计视图的方法查询设计视图的结构【例4-3】查询院系为“物流学院”的学生信息，要求显示“学号”、“姓名”、“出生日期”及“籍贯”并按出生日期升序显示。【例4-4】查询选修了“大学计算机基础”课程的学生信息，要求显示“学号”、“姓名”、“院系”及“课程名称”和“成绩”，并按成绩降序显示。4.2.3查询条件打开表达式生成器的方法表达式生成器的结构表达式就是运算符、常量、函数和字段名称、控件和属性的任意组合，计算结果为单个值。查询条件是指在创建查询时为了查询到所需要的记录，通过对字段添加限制条件，使查询结果中只包含满足条件的记录。1.常量（1）数字型常量：如分数“650”、“590”等。（2）文本型常量：如“信息学院”、“物流学院”等，可使用“*”和“？”通配符。（3）日期型常量：如出生日期#1992-7-1#。（4）是否型常量：Yes、No、True、False。（5）空字段值：“IsNull”表示字段值为空，“IsNotNull”表示字段值不为空。表达式中常用的运算符有算术运算符、关系运算符、逻辑运算符、标识符和特殊操作符。算术运算符：如+、-、*、/等。关系运算符：如、=、、=、=、。逻辑运算符：如AND、OR、NOT。标识符：通常是一个对象的名字，一般指的是字段的名字。引用字段名称时需要用“[]”将其名称括起来。2.表达式中的运算符（1）用逻辑运算符组合条件And表示当两个操作数的值都为TRUE时，表达式的值才为TRUE。Or表示两个操作数的值有一个为TRUE时，表达式的值就为TRUE。用Or运算符可将条件进行扩展。Not则生成操作数的相反值。（2）用关系运算符组合条件（3）用Between运算符组合条件Between运算符也可用于指定字段的取值范围，范围之间用And连接。（4）用like组合条件Like运算符用于查找文本型字段的部分值，格式为：Like字符模式在所定义的字符模式中，可用“？”表示该位置可匹配任何一个字符；用“*”表示该位置可匹配零个或多个字符；用“#”表示该位置可匹配一个数字；用“[]”描述一个范围。（5）用In组合条件In运算符用于指定字段的一系列的值，与like不同，in运算中不允许使用通配符。3.函数常用的数值函数函数功能Abs（数值表达式）返回数值表达式值的绝对值Int（数值表达式）返回数值表达式值的整数部分Sqr（数值表达式）返回数值表达式值的平方根Sgn（数值表达式）返回数值表达式值的符号值常用的文本函数函数功能Left（字符串表达式，n）从字符串表达式左侧第1个字符开始截取n个字符Right（字符串表达式，n）从字符串表达式右侧第1个字符开始截取n个字符Len（字符串表达式）返回字符串表达式中字符的个数Mid（字符串表达式，n1[,n2]）从字符串表达式左边n1位置开始，截取连续n2个字符常用的日期时间函数函数功能Now（）返回系统当前的日期时间Date（）返回系统当前的日期Time（）返回系统当前的时间Day（日期表达式）返回日期中的日Month（日期表达式）返回日期中的月份Year（日期表达式）返回日期中的年份4.2.4编辑查询1.增加字段2.删除字段3.修改字段4.移动字段5.调整设计网格的列宽4.3在查询中进行计算4.3.1查询中的计算功能预定义计算自定义计算查询中的常用计算计算名功能合计计算一组记录中某个字段值的总和平均值计算一组记录中某个字段值的平均值最大值计算一组记录中某个字段值的最大值最小值计算一组记录中某个字段值的最小值计数计算一组记录中记录的个数First一组记录中某个字段的第一个值Last一组记录中某个字段的最后一个值Expression创建一个由表达式产生的计算字段Where设定分组条件以便选择记录4.3.2总计查询总计查询是通过对查询设计视图窗口中的总计行进行设置实现的，用于对查询中的全部记录进行总和、平均值、最大值、最小值的计算。【例4-5】查询全校学生的入学成绩情况：平均入学成绩、最高成绩和最低成绩，并定义查询字段依次为平均分、最高分、最低分。4.3.3分组总计查询分组是指在查询设计窗口中指定某一字段为分组字段，将该字段值相同的所有记录组合在一起，并对分组进行统计计算。【例4-6】查询全校各院系学生的入学成绩情况：即查询各院系的平均入学成绩、最高成绩和最低成绩，定义查询字段依次为平均分、最高分、最低分，并要求平均分保留小数点后1位。4.3.4添加计算字段当需要统计的数据在表中没有相应的字段，或者用于计算的数据值来源于多个字段时，应在设计网格中添加一个计算字段，计算字段是指根据一个或多个表中的一个或多个字段并使用表达式建立的新字段。【例4-7】计算所有学生的年龄，要求显示每个学生的“学号”、“姓名”和“年龄”。【例4-8】查询每个学生的奖学金级别，要求显示的字段有：“学号”、“姓名”、“院系”、“入学成绩”和“奖学金等级”。奖学金等级的划分标准是：入学成绩=650的为“一级”，620=入学成绩650的为“二级”，590=入学成绩620的为“三级”，否则没有。iif()函数的格式如下：iif(条件表达式1，表达式2，表达式3）功能：首先计算条件表达式1，若条件表达式1的值为真返回表达式2的值否则返回表达式3的值。奖学金等级:IIf([入学成绩]=650,一级,IIf([入学成绩]=620,二级,IIf([入学成绩]=590,三级)))4.4其他查询的设计4.4.1交叉表查询交叉表查询以一种紧凑的、类似于电子表格的形式显示查询字段，并且可以计算该字段的合计或平均值等。使用交叉表查询计算和重构数据，可以简化数据分析。1.交叉表查询向导【例4-9】建立学生与课程成绩交叉表查询，统计每名学生选修的课程及成绩。2.设计视图中的交叉表查询【例4-10】利用交叉表查询，统计每名学生选修课程情况。要求显示学生“姓名”、选课数量、平均成绩以及所学课程的成绩。4.4.2参数查询创建这种参数的查询只要在查询设计窗口中，在要定义为参数的字段的条件行中输入用“[]”括起来的提示信息，在运行查询时这些提示信息出现在“输入参数值”输入框的上方，然后保存查询文件。【例4-11】创建一个带有参数的查询，要求先输入学生的学号，之后显示该学生的“学号”、“姓名”、“性别”、“课程名称”及“成绩”。练习:设计一带参数查询：要求输入某一院系名称，显示该院系所有学生的基本信息,包括学号、姓名、性别、出生日期和籍贯。三．实验内容1．创建一个查询，计算每位读者已办理借书证的年限。要求显示“读者编号”、“姓名”、“性别”、“类别”、“部门”和已办理年限，所建查询命名为“读者借书证的时间”。已办理年限:year(date())-year([办证日期])实验3查询中的计算2．利用总计查询统计每类读者，即“教工”、“本科”和“研究生”的平均借书数量，所建查询命名为“各类读者的平均借书量”。以“类别”字段为分类字段，对“借阅日期”字段进行计数。3．利用总计查询统计每个部门的平均借书数量及所借图书的总价格，所建查询命名为“各部门平均借书量及价格”。4．利用总计查询统计每个部门的罚款总额，所建查询命名为“各部门的罚款额”。实验4创建参数查询1．设计一个参数查询，要求输入读者姓名，可显示该读者的借书记录，包括“读者编号”、“姓名”、“性别”、“部门”及所借阅的图书的“书名”、“出版社”、“作者”和“借阅日期”、“还书日期”，所建查询命名为“按读者姓名查询”。2．设计一个参数查询，要求输入图书的ISBN，可显示该图书的“书名”、“出版社”、“作者”及借阅过该图书的“读者编号”、“姓名”、“性别”、“部门”及“借阅日期”、“还书日期”，所建查询命名为“按书号查询”。3．设计一个多参数查询，要求输入一个开始日期和结束日期，显示在该时间段内借书的读者信息，包括“读者编号”、“姓名”、“性别”、“部门”及所借阅的图书的“书名”、“出版社”、“作者”和“借阅日期”、“还书日期”，所建查询命名为“按日期查询”。在“借阅日期”字段的条件行中输入：=[开始日期]and=[结束日期]4.4.3操作查询Access2010的操作查询包括以下几种查询：生成表查询。利用从一个或多个表获得的数据创建一个新的表。更新查询。对一个或多个表中的多个记录的某些字段的值进行修改。追加查询。为一个或多个表添加从一个或多个表中获得的一组记录。删除查询。对一个或多个表中满足条件的一组记录进行删除操作。1.生成表查询【例4-12】在“新教务管理数据库”中创建一个“不及格学生名单”的表。要求该表中要有：“学号