复习 创建Access中的查询对象(一)课件.ppt

第4章 创建Access中的查询对象,学习目标,知道查询对象的作用。知道查询对象的类型。知道查询的准则。使用设计器创建查询对象。使用查询向导创建查询。在查询中进行计算。,查询对象的作用,在数据库中创建了表并在表中存放了数据，就像在盖好的图书馆中在书架上分门别类摆好了图书。准备好图书，只是图书馆的基础工作，管理图书资源，即如何将这些图书借阅给读者，让更多的读者看到图书，使图书发挥最大的作用才是图书馆的主要工作。同样，建立数据库、在库中创建表、向表中输入数据、维护表是数据库的基础工作，更重要的工作是管理、加工数据，让数据资源发挥最大的作用，成为人们需要的信息、创造企业财富的依据。,查询是数据库中最重要并且是最常见的应用 ，利用查询可以让用户根据选择条件对数据库进行检索，筛选出一组满足指定条件的记录，从而构成一个新的数据集合，以方便用户对数据库进行查看和分析。,查询对象的作用,查询对象就是用来对表中数据进行加工并输出信息的数据库对象，它以一个或多个表及查询对象为基础，重组并加工这些表或查询中的数据，提供一个新的数据集合。,查询对象的作用,在设计数据库时，为了节省存储空间，为了数据不冲突，要将数据按主题（实体）分类，并分别存放在不同的表里，通过表关系可将关联表联结起来，但如何从多个表中提取数据呢？,尽管在数据库中可以进行一些管理数据的操作，例如浏览、查找、排序、替换、筛选和更新，但没有加工数据的功能，如何通过现有数据加工生成新数据呢？,查询对象正是为了解决这两个问题而配备的工具。,动态的数据空间,查询对象可以从多个联结的表中提取出需要的数据，它将分散在不同表或查询中的数据集中在一起，为这些数据提供了一个动态的数据空间。 动态的含义是说，它不是一个真正存在的数据集合，只是在使用查询对象时它才存在。,查询对象的类型,在Access将查询对象按功能分为：选择查询交叉表查询参数查询操作查询SQL查询,查询的功能,Access的查询功能非常强大，一般可分为选择查询和操作查询两大类。Access的选择查询可以从一个或多个相关表中筛选出所需的记录数据，也可以从已有的查询对象中进一步筛选所需的数据。 Access的操作查询可以对指定的数据表进行记录的添加、更新和删除等操作。 可使用查询向导或查询设计视图以交互方式创建查询对象，也可用 SQL 命令创建查询对象。,查询的功能,1、选择字段和记录2、统计和计算3、数据更新4、产生新的表5、作为其他对象的数据源,选择查询,选择查询是最常用的一种查询，它可以指定查询准则，从一个或多个表中选择满足要求的数据，可提供分组、总计、计数、求平均等计算数据，并将这些数据显示在数据表视图中。,选择查询,查询条件的设置使用常量作为查询条件在查询条件中使用普通运算符在查询条件中使用特殊运算符包括 Between、Like、In、Not In、Is Null和Is Not Null 等。在查询条件中使用函数包括 算术运算函数、字符处理函数、日期时间函数、转换函数和统计函数等。,建立查询准则,关系运算符：=、=。逻辑运算符： And 、Or、Not特殊运算符：In、Between、Like、Is null、Is not null。,In: 用于指定一个字段值的列表，列表中的任意一个值都可以与查询的字段相匹配。Between: 用于指定一个值的范围。指定的范围之间用And连接。Like: 用于指定查找文本字段的字符模式。可以用？、*、#、 通配符。Is null: 用于指定一个字段为空。Is not null: 用于指定一个字段为非空。,建立查询准则,1.数值函数：Abs( )、Int( )、Sqr( )、Sgn( )等。2.字符函数：Space( )、String( )、Left( )/Right( )、Len( )、Ltrim( )、Rtrim( )、Mid( )等。例如：3.日期时间函数：Day(date)、Month(date)、Year(date)、Weekday(date)、Hour(date)、Date( )等。例如：4.统计函数：Sum( )、Avg( )、Count( )、Max( )、Min( )等。例如：,建立查询准则,建立查询准则,建立查询准则,注意：空字符串是用双引号括起来的字符串，且双引号中间没有空格。,建立查询准则,概 念,所创建的查询对象不是数据的集合，而是查询操作定义的集合。查询结果是在内存中形成一个动态数据集，它很像一个表，但并未存储在数据库中。只有在运行查询对象时，才会从查询数据源中抽取所需数据形成一个动态集；只要关闭查询，这个动态集就会自动消失。,创建查询的方法,使用查询向导创建查询使用设计视图创建查询,选择查询用得最多，因为它可以提供来自多个表中的数据和加工的数据。,参数查询,参数查询是一种利用对话框提示用户输入查询要求的选择查询。它具有选择查询的功能，能随时按用户输入的要求查询数据。根据用户输入的不同要求，会出现不同的查询结果。,交叉表查询,交叉表查询是对表或查询的行和列数据进行统计输出的一种查询。交叉表查询的数据来源于一个表或查询，在数据表视图中可显示两个分组字段，分组字段名来自表字段的值。,操作查询,操作查询对象可以通过运行查询对数据库中的表进行数据操作。有四种不同的操作查询：生成表查询，运行查询可生成一个新表。删除记录查询，运行查询将删除表中一条或多条记录。更新记录查询，运行查询可更新表中一条或多条记录。追加查询，运行查询可在表的尾部追加一组新记录。,SQL查询,SQL查询是使用SQL语句来创建查询，需要用户了解SQL语言，它更灵活、功能更强大，使用SQL查询可以创建以上类型的查询。,查询的准则,查询对象的主要工作是查出需要的数据。为了让数据库帮你查找或计算出需要的数据，需要制定一个让数据库懂得的描述用户查询要求的规则，我们将它称为查询准则。查询准则由数据库定义的运算符、常数值、字段变量、函数组成的表达式来描述。查询准则一般分为两种：简单准则和复杂准则。,简单准则,简单准则通过关系运算符、字段变量与常数值组成 的关系表达式来描述，一般描述用户的一个查询条件。 例如，查询90分以上学生记录的查询准则可以写成：课程成绩90。,关系运算符的符号及含义,注 意,文本值要使用半角的双引号”括起来。日期值要使用半角的#号括起来。,复杂准则（总结以上）,要描述用户的多个查询条件需要使用复杂准则。复杂准则是使用特殊运算符、逻辑运算符、函数以及它们的组合（包括关系运算符）连接常数、字段变量组成的条件表达式。 例如，查询1990年4月参加工作的男老师的记录，复杂准则的条件表达式为： Year(参加工作时间)=1999 and month(参加工作时间)=4 and 性别 =”男”,特殊运算符的符号及含义,注 意,字符模式中？匹配一个字符；* 匹配零或多个字符；# 匹配一个数字；方括号 可匹配一个字符范围。,逻辑运算符的符号及含义,时间函数名及含义,注 意,条件表达式中表的字段名称，最好使用 括起来，例如日期。,创建查询对象,怎样才能多快好省地创建出查询对象呢？ Access提供有查询向导和查询设计器工具。它们可以帮助你多快好省地完成创建不同类型的查询对象。,使用设计器创建查询对象,在查询设计视图下创建查询可以帮助读者理解数据库中表之间的关系，看到查询字段之间是如何联系的，它们对建立一个优秀的数据库非常有帮助。,通过表创建选择查询,创建“员工表中查询工资高于2000的女员工”,在Access中打开“商品销售管理数据库.mdb ”数据库。打开查询设计器。,创建查询,从上图可以看到查询设计视图分为两个窗口：上面是“表/查询显示窗口”；下面是“查询设计器窗口”。,“表/查询显示窗口”用来显示查询所用到的数据来源的表或查询的字段。“查询设计器窗口”用来显示和定义查询中所用到的目标字段和查询准则。在“查询设计器窗口”中有很多的网格，称它们为查询设计表格，查询字段要放在该表格里，查询对象中的字段称为“目标字段”。表格左面的文字说明表格每行是什么，例如“字段”表示这行为查询中出现的字段。“表”说明同列字段的来源表。,从上图还可以发现在Access主窗口中菜单栏、工具栏发生了变化，菜单栏添加了【查询】菜单，它还包含一些查询操作专用的子菜单，如图所示。在工具栏上，也新增加了一些按钮。,注意,在Access数据库窗口中选择不同的对象后，菜单栏都会发生一些变化，出现不同的菜单，以便在使用这种对象时能更加方便，操作更加快捷。,保存查询,预览查询对象,在工具栏单击【运行】按钮，可在数据表视图中看到查询对象查询到的数据表。,操作练习 1,使用表创建选择查询请同学自己完成！,组合条件查询,查询销售二部或1972年以前出生的记录，要求显示员工号、姓名、出生日期、部门四个字段。查询部门是销售一部或销售二部的员工查询商品号为000002且销售数量大于10的商品号、商品名、数量、和销售日期。,自定义计算查询,用销售明细创建查询，计算并显示价格*数量，即小计。在员工表中计算并显示年龄的内容。,汇总查询（预定义计算查询）,概念 汇总查询是在查询的同时对查询结果进行计数、求和、求平均值、标准差、最大值和最小值等。操作打开选择查询设计视图，单击工具栏上的“总计”按钮，Access会自动在视图下方添加一个“总计”行。对于视图中的相关字段，根据需要在“总计”行网格的下拉列表中选择一个汇总计算选项。,建立一个查询，统计员工表中的记录数。建立一个查询，统计每个部门的人数。创建查询，统计员工表中每个部门的工资最大值、最小值、和平均值。,汇总查询（预定义计算查询）,排序查询结果,查询员工表中所有员工的员工号、姓名、性别、工资信息，并先按性别的降序、再按工资的升序显示查询结果。在员工表中，显示年龄最小的前三名员工的员工号、姓名、性别、出生日期的信息。,创建参数查询,参数查询是在选择查询的基础上增加了人机交互的功能，运行参数查询对象时，用户可以根据提示输入参数，查询对象能根据用户输入参数自动修改查询准则为不同用户查找不同数据。参数查询两种形式：单参数查询和多参数查询。,参数查询,概念所谓参数查询，是在查询过程中，允许用户在弹出的对话框内即时输入查询条件或参数，便可查询并显示出与输入参数相关的记录数据。可以建立单个参数的查询，也可以建立多个参数的查询,参数查询,单参数查询示例 创建一个单参数查询，只需输入某个学生的学号，即可显示出该学生的相关信息。,参数查询,多参数查询示例 创建多参数查询，依次输入某个学生的“性别”和“籍贯”，即可显示出该学生的相关信息。,操作练习 2,创建单参数查询请同学自己完成！,单参数查询,在员工表中，创建按员工号查询员工的所有信息的参数查询。,多参数查询,以“员工”、 “商品”、 “销售单”、 “销售明细”为数据源，创建一个名为“扩展销售明细”的查询，在该查询中，可显示出销售的详细信息包括：销售号、员工号、姓名、销售日期、商品号、商品名、价格和数量。再以“扩展销售明细”为数据源，以销售号及商品号作为参数创建查询销售号、员工号、销售日期、商品号、商品名信息的多参数查询。,交叉表查询,概念 交叉表查询类似 Excel 中的数据透视表，是将来源于数据库中的字段分成两组，一组以行标题的形式显示在表格的左侧，另一组以列标题的形式显示在表格的顶端，然后在行与列交叉的地方对数据进行求和、求平均值、计数或者其他类型的统计计算，并将结果显示在交叉点上。,使用查询向导创建查询,使用查询向导创建查询与使用其他向导创建对象类似，需要三个步骤：启动向导，回答向导提问，自动创建对象。,启动查询向导,以前面创建的查询“扩展销售明细”为数据源，统计每个员工销售各种商品的数量。“员工号”和“姓名”字段为行标题，以“商品名”字段为列标题，对“数量”字段进行求和和统计，使用交叉表查询向导创建一个名为“员工销售统计”的交叉表查询，通过该交叉表查询可以 查阅各个员工所销售各种商品的数量,启动“交叉表查询向导”,注 意,交叉表查询的数据源只能来自于一个表或查询，如果数据来自于多个表和查询可先创建一个来自多表的查询，然后再根据这个查询创建交叉表查询对象。,使用设计视图创建交叉表查询,复习,操作查询,概念操作查询是依据查询条件和检索结果，在数据库中完成追加记录、更新数据、删除记录等操作的查询。利用操作查询，还可以将检索结果作为一个新表添加到数据库中。操作查询可分为追加查询、更新查询、删除查询和生成表查询。,操作查询,生成表查询功能创建一个生成表查询，不仅能够从一个或多个数据表（或查询）对象中获取所需数据，而且还能由查询所得的结果生成一个新的数据表。示例将员工表中女员工的记录保存到新表中，要求显示员工号、姓名、性别、出生日期、和工资五个字段。,操作查询,更新查询功能创建一个更新查询，可以方便地实现对数据表中符合指定条件的所有记录数据，按要求进行成批更新修改。 示例创建一个带参数的更新查询，根据用户输入的金额增加员工的工资。,操作查询,追加查询功能创建一个追加查询，可方便地实现将数据源中符合指定条件的所有记录，添加到指定的数据表中。示例创建“员工”表结构的副本，已“员工”表为数据源，创建一个追加查询，将所有部门为“销售一部”的员工追加到该副本中。副本名称为“销售一部员工”。,操作查询,删除查询功能创建删除查询，可以方便地从一个表或多个相关的表中删除符合指定条件的所有记录。注意，删除查询只能删除符合条件的整行记录。示例以“员工备份”为数据源，创建一个删除查询，删除部门为“销售一部”的记录。,SQL查询,概念SQL（Structured Query Language，结构化查询语言），是关系型数据库的标准操纵语言。所有关系数据库管理系统都支持SQL。在以各种交互方式创建查询对象时，Access 会在后台自动构造等效的SQL语句。创建某个查询对象的结果就是生成其对应的SQL语句；执行某个查询对象也就是执行其对应的SQL语句。,SQL查询,SQL查询简介SQL的主要优点 提供完整的数据定义、操作和控制功能具有完备的查询功能语法简洁，易学易用是一种高度非过程化的语言执行方式多样，既能以交互命令方式直接执行，也能嵌入到各种高级语言中执行。,SQL查询,查询设计视图与对应的SQL视图,SQL查询,数据定义查询建立表结构 SQL语句格式CREATE TABLE ( (字段长度) , (字段长度) ) 示例CREATE TABLE 通讯录 (姓名 TEXT(8), 生日 DATE, 婚否 LOGICAL, 联系电话 TEXT(12);,SQL查询,修改表结构 SQL语句格式ALTER TABLE ADD | ALTER | DROP (字段长度) , (字段长度) 示例ALTER TABLE 通讯录 ADD 称呼 TEXT(16); ALTER TABLE 通讯录 ALTER 姓名 TEXT(10); 删除表DROP TABLE ,SQL查询,数据维护查询插入纪录 SQL语句格式INSERT INTO (,) VALUES(,) 示例INSERT INTO 通讯录 VALUES(李明, #1978-8-6#, 16291384568, 博士);,SQL查询,更新纪录 SQL语句格式UPDATE SET =,=WHERE 示例UPDATE 学生 SET 年龄=年龄+1;UPDATE 学生 SET 籍贯=河南 WHERE 姓名=马丽;删除记录DELETE FROM WHERE ,SQL查询,SELECT查询概念 SELECT语句是SQL中功能最为强大、最常用的语句，不仅能够从一个或多个表中检索出符合各种条件的数据，并且能够进行嵌套查询、分组汇总查询以及各种特殊查询。如果需要，还能够将查询结果保存为新的数据表，或以其他方式输出。,SQL查询,SELECT语句语法格式 SELECT ALL | DISTINCT AS , AS FROM INTO WHERE GROUP BYHAVING ORDER BY ASC | DESC;,SQL查询,简单查询示例：查询“学生”表中所有外地（非北京籍）女生的姓名、出生日期与籍贯：SELECT 姓名, 出生日期, 籍贯 FROM 学生 WHERE 性别=女 AND NOT 籍贯=北京; 示例：查询“学生”表中所有“财务管理”和“注册会计”班的学生记录 ：SELECT * FROM 学生 WHERE 所在班级 IN(财务管理, 注册会计);,SQL查询,多表查询示例：依据“学生”表和“成绩”表中的数据，查询成绩不及格学生的学号、姓名、所在班级、不及格课程的课程号及该课程成绩：SELECT 学生.学号, 学生.姓名, 学生.所在班级, 成绩.课程号, 成绩.成绩 FROM 学生 INNER JOIN 成绩 ON 学生.学号 = 成绩.学号 WHERE 成绩.成绩60;,SQL查询,嵌套查询嵌套查询是在一个SELECT语句的WHERE子句中，包含另一个SELECT语句。 示例：查询“成绩”表中无任何记录的学生，并按所在班级排序输出这些学生的学号、姓名和所在班级等信息：SELECT 学号, 姓名, 所在班级 FROM 学生 WHERE 学号 NOT IN (SELECT 学号 FROM 成绩)ORDER BY 所在班级;,SQL查询,分组汇总查询将检索到的记录依据某个字段的值进行分组，该字段值相同的多条记录被合并为一条记录输出。示例SELECT 学生.学号, 姓名, Count(成绩) AS 考试次数, Max(成绩) AS 最高分, Min(成绩) AS 最低分, Avg(成绩.成绩) AS 平均分 FROM 学生 INNER JOIN 成绩 ON 学生.学号 = 成绩.学号 GROUP BY 学生.学号, 学生.姓名；,小结：,1. 选择查询 2. 参数查询 3. 交叉表查询,查询准则,准则中的运算符及表达式 算术运算符,87,用户的查询经常需要指定一定的条件，这就需要设置准则来实现。查询准则也称为查询条件，是运算符、常量、字段值、函数以及字段名和属性等的任意组合，能够计算出一个结果。, 关系运算符 用关系运算符连接的两个表达式构成关系表达式，结果为一个逻辑值True、False或者Null,88, 连接运算符 可以使用连接运算符将两个文本值合并成一个值。,89,90, 逻辑运算符 逻辑运算符用于连接两个关系表达式，对表达式进行真、假判断。, 特殊运算符 与比较运算有关，这些运算符根据字段中的值是否符合这个运算符的限定条件返回True或False。,91,Like ：查找匹配的文字 可与以下4 个通配符配合使用：“*”匹配0或多个字符，“?” 匹配一个字符，“#” 匹配一个数字，“ ”匹配一个字符范围 。如：like “李*”，指姓李的名字。In：指定一系列值的列表如：In（”山东”，”浙江”，”安徽”）。Between a1 and a2：指位于a1 和a2之间的值（包括端值）如：Between 75 and 90。Is Null、Is Not Null ：判断某一值是否为Null值字段，即不包括任何数据，为空值。,92,补充-通配符, ？：代表任意一个字符 *：代表任意字符串（0或多个字符） ：代表单一数字 字符表：字符表中的单一字符 ！字符表：不在字符表中的单一字符【例】Like “pb-g#” 通配以字母p开头，后根bg之间的1个字母和3个数字的字符串。【例】Like “f?a-f!6-10*” 通配第一个字符为f ，第2个为任意字符，第3个为非6-10的任意字符，其后为任意字符串的字符串。,93,准则中的标准函数,常用的统计函数 求和函数：Sum()求平均函数：Avg() 统计记录个数函数：Count()最大、最小值函数：Max() Min()例如：显示“运费”字段中各值的总和，即为Sum(运费)；Avg(运费)为平均运费；显示“单价”字段和“数量”字段乘积的总和，即为Sum(单价*数量)；按姓名统计人数，即为Count（姓名）；统计包括空值Null在内的所有记录数为Count(*)；设有一个“成绩”字段，可以用Max(成绩)求该字段中的最大值，用Min(成绩)求该字段中的最小值。,94,准则中的标准函数,日期函数 在包含日期的表达式中，须将日期型数据的两端加上“”号（此处#不是通配符），以区别于其它数字。例如：1990年1月2日以后出生的学生，表达式可为：#1990/1/2# 或#/1/2/1990#。另外，系统还提供了以下时间函数：Date( )：返回系统当前日期Year( )：返回日期中的年份Month( )：返回日期中的月份Day( )：返回日期中的日数Weekday( )：返回日期中的星期几Hour( )：返回时间中的小时数Now( )：返回系统当前的日期和时间,95,【例】时间函数的应用1. 构造查询条件表达式，把7月份出生的学生查找出来。 Month（csrq）=72. 构造计算表达式，根据出生日期计算学生年龄。 Year（Date（）-Year（出生日期）3. 构造表达式，表示出系统日期10天前的日期。 Date（）-10说明：在任何计算表达式中的符号都必须在英文半角输入状态下输入，否则无效。出生日期和csrq为字段标识符，方括号内是字段名。如果要明确表示“学生信息”表中的“出生日期”字段，表达式为：学生信息!出生日期,96,