Access查询的设计与创建解析课件.ppt
数据库程序设计项目组,Access数据库程序设计,嘉兴职业技术学院2012年4月,第10讲 项目四 Access查询的设计与创建SQL查询的创建,内容简介教学目标4.1 认识查询 4.2 创建选择查询 4.3 创建参数查询 4.4 创建交叉表查询4.5 创建操作查询4.6 SQL查询本讲小结 习题四,Access数据库程序设计,3,内容简介,查询是Access数据库的主要组件之一，也是Access数据库中最强的功能之一。它是Access处理和分析数据的工具，能够把多个表中的数据抽取出来，供用户查看、更改和分析使用。本章主要介绍查询的五种类型及根据具体使用目的选择查询的类别，学会创建查询的方法及设计准则。,4,教学目标,了解查询的概念和类型熟练掌握创建查询的多种方法熟练掌握操作各类已建查询的技巧,5,4.1 认识查询,4.1.1 查询的功能4.1.2 查询的类型4.1.3 查询的3种视图 4.1.4 查询的准则,6,4.1.1 查询的功能,1选择字段2选择记录3分级和排序记录4实现计算5创建新表6建立基于查询的报表和窗体,7,4.1.2 查询的类型,1选择查询2交叉表查询3操作查询4参数查询5SQL查询,8,4.1.3 查询的三种视图,Access 2000中查询具有三种视图，分别是：“设计视图”、“数据表视图”和“SQL视图”。,9,4.1.4 查询的准则,1准则中的运算符运算符是组成准则的基本元素。Access中提供了算术运算符、关系运算符、逻辑运算符和特殊运算符等。（1）算术运算符算术运算符可进行常见的算术运算，按运算的优先级次序排列为：（乘方）、-（负号）、*（乘）、/（除）、MOD（求余）、+（加）、-（减）。（2）关系运算符关系运算符用来比较两个值或者两个表达式之间的关系，包括：（大于）、=（大于等于）、（不等于）、=（等于）。关系运算的值为TRUE或FALSE。（3）逻辑运算符逻辑运算符用来实现逻辑运算，按优先级次序排序：NOT（非）、AND（并且）、OR（或）。逻辑运算符通常与关系运算符一起使用，构成复杂的用于判断比较的表达式，运算的值为TRUE或FALSE。（4）特殊运算符（5）统计函数统计函数常用于对数据的统计分析。,10,4.1.4 查询的三种视图,2准则中的函数Access提供了大量的标准函数，如：数值函数、字符函数、日期时间函数、统计函数等。这些函数为用户更好地构造查询准则提供了极大的便利，也为用户更准确地进行统计计算、实现数据处理提供了有效的方法。（1）数值函数数值函数用于数值的计算，常用的数值函数。（2）字符函数字符函数又称文本处理函数，用于处理字符串。（3）日期和时间日期和时间函数常用于处理字段中的日期/时间值，可以通过日期和时间函数抽取日期的一部分及时间的一部分。,11,4.1.4 查询的三种视图,3使用文本作为准则在Access中建立查询，经常使用文本值作为查询准则。使用文本值作为查询的准则可以方便地限定查询范围和查询的条件，实现一些相对简单的查询。4使用处理日期作为准则在Access中建立查询，经常使用以计算或处理日期作为查询准则。使用计算或处理日期作为查询的准则可以方便地限定查询时间范围5使用字段的部分值作为准则在Access中建立查询，有时需要使用字段的部分值作为查询的准则。使用字段的部分值作为查询的准则可以方便地限定查询范围。6使用空字段值作为准则空值是使用Null或空白来表示字段的值；空字符串是用双引号括起来的字符串，且双引号中间没有空格。,12,4.2 创建选择查询,4.2.1 简单查询向导查询向导一般用来创建相对比较简单的查询，或者用来初建基本查询，以后再用设计视图进行修改。使用“简单查询向导”可以创建一个简单的选择查询。,13,在“学生成绩管理系统”数据库中，创建一个“学生基本情况查询”，查找学生的姓名、性别、专业和班级。 演示具体操作：,【例4.1】,14,【例4.2】,创建“学生成绩查询”，查找学生的姓名，课程名称和成绩。 演示具体操作：注意：在创建多表间的查询时，前提条件是多个表间设置了“关系”，否则会提示报错。,15,4.2.2 在设计视图中创建条件查询,在日常工作中，用户的查询并非只是简单的查询，往往是带有一定的条件。这就需要通过“设计”视图来建立查询。1认识设计视图2单个条件查询【例4.3】创建“成绩在80-90间的学生信息” 查询，查找成绩在80-90之间的学生姓名，课程名称和成绩。,16,3多条件查询【例4.4】创建“95年前工作的副教授信息” 查询，查找工作时间在1995年之前（不包括1995年）且职称为副教授的教师信息。,4.2.2 在设计视图中创建条件查询,17,4.2.3 在设计视图中创建总计查询,1在查询中创建计算字段Access数据库为经常用到的数值汇总提供了丰富的“总计”选项 ，即对查询中的记录组或全部数据进行的计算，它包括总和、平均值、计数、最大值、最小值、标准偏差或方差等。这12个选项分为4类：分组（Group By）、聚合函数（Sum, Avg, Min, Max, Count, SrDev, Var, First, Last）、表达式（Expression）和限制条件（Where）使用时，只需要选择或输入这12个选项，即可完成相应功能。2总计查询建立查询时，可能我们更关心的是记录的统计结果，而不是表中的记录。因此为了获取这些数据，就需要使用Access提供的总计查询功能，完成一定的计算查询。,18,例题,【例4.5】创建“各职称教师人数”查询，统计各类职称教师人数。演示具体操作：【例4.6】创建“学生平均成绩”查询，统计每个学生的各门课程的平均成绩，并将结果按平均成绩的降序排列。演示具体操作：,19,4.3 创建参数查询,4.3.1 单参数查询创建单参数查询，就是在字段中指定一个参数，在执行参数查询时，由用户输入一个参数值。当需要统计的数据在表中没有相应的字段，或者用于计算数据值来源于多个字段时，需要在“设计网格”中添加一个计算字段。计算字段是指根据一个或多个表中的一个或多个字段并使用表达式建立的新字段，通常计算字段都包含计算公式或函数条件。,20,例题,【例4.7】在“学生成绩管理系统”数据库中，创建一个“教师基本信息查询”，按姓名查询某教师的全部信息。演示具体操作：【例4.8】创建一个“某专业年龄小于24岁学生信息”，按用户输入的所属专业查询年龄小于24岁学生的姓名、性别、年龄和所属专业。演示具体操作：,21,4.3.2 多参数查询,用户不仅可以建立单个参数查询，如果需要也可以创建多参数查询。在执行多参数查询时，用户依次输入多个参数值。【例4.9】创建一个“某课程成绩区间学生信息”，按课程名称和成绩区间查询学生成绩信息。演示具体操作：,22,4.4 创建交叉表查询,使用交叉表查询计算和重构数据，可以简化数据分析。交叉表查询计算数据的总和、平均值、计数或其他类型的总计值，而这种数据又分为两部分信息：一部分在数据表左侧向下，另一部分在数据表顶端。,23,在创建交叉表查询时，需要指定三类数据：一是在数据表左端的行标题，它是把某一个字段或相关的数据放入指定的一行中；一是在数据表最上面的列标题，它是把每一列指定的字段或表进行统计，并将统计结果放入列中；三是放在数据表行与列交叉处的字段，用户需要指定总计项。对于交叉表查询，用户只能指定一个总计类型的字段。,4.4.1 认识交叉表查询,24,4.4.2 创建交叉表查询,创建交叉表查询有两种方法：“交叉表查询向导”和查询“设计”视图。1使用“交叉表查询向导” 【例4.10】在“学生成绩管理系统”数据库中，创建一个“统计各班男女生人数查询”，显示每个班级的男女生人数。2使用“设计”视图【例4.11】创建一个“每位学生每门课成绩查询”，显示学生姓名、课程名称和成绩。,25,4.5 创建操作查询,4.5.1 认识操作查询操作查询是指仅在一个操作中更改或移动记录的查询，操作查询共有4种类型：生成表查询、删除查询、更新查询和追加查询。操作查询与选择查询、参数查询及交叉表查询有所不同。选择查询、参数查询和交叉表查询只是根据要求选择数据，并不对表中的数据进行修改，而操作查询除了从表中选择数据外，还对表中数据进行修改。,26,4.5.2 生成表查询,生成表查询就是将查询的结果存在一个新表中，这样就可以使用已有的一个或多个表中的数据创建表。【例4.12】创建一名为“生成60分以下学生信息查询”，将成绩小于60分的学生的“姓名”、“性别”、“所属专业”、“课程名”和“成绩”存储到一个新表中，新表名为“成绩60以下学生信息”。演示具体操作：,27,4.5.3 追加查询,追加查询就是将一组记录追加到一个或多个表原有记录的后面。追加查询的结果是向有关表中自动添加记录。【例4.13】创建一名为“追加电商成绩60-70间学生信息”，将电子商务专业的成绩在60-70之间的学生的“姓名”、“性别”、“所属专业”、“课程名”和“成绩”追加到“成绩60以下学生信息”表中。演示具体操作：,28,更新查询就是对一个或多个表中的一组记录作全局的更改。使用更新查询，可以更改已有表中的数据。【例4.14】创建一名为“成绩加5分”更新查询，将“成绩60以下学生信息”表中的高等数学课程成绩都增加5分。演示具体操作：,4.5.4 更新查询,29,删除查询就是从已有的一个或多个表中删除满足查询条件的记录。【例4.15】创建一个名为“删除60分以下学生信息”查询，该查询将“成绩60以下学生信息”表中成绩低于或等于60分的记录删除。演示具体操作：注意：删除查询运行一定要慎重，记录删除后不能撤消所做的更改。,4.5.5 删除查询,30,SQL查询是用户使用SQL语句自定义创建的查询，它是一个用于显示当前查询的SQL语句窗口，在这个窗口里用户可以查看和改变SQL语句，从而达到查询的目的。SQL是Structured Query Language（结构化查询语言）的缩写。SQL是专为数据库而建立的操作命令集，是一种功能齐全的数据库语言。SQL意为结构化查询语言，可用于定义、查询、更新、管理关系型数据库系统。SQL是一种非过程语言，易学易用，语句由近似自然语言的英语单词组成,4.6 SQL查询,31,SQL语句按其功能的不同可以分为以下三大类：1数据定义语句（DDL，Data-Definition Language）定义数据库的逻辑结构，包括定义数据库、基本表、视图和索引4部分。2数据操作语句（DML，Data-Manipulation Language）包括数据查询和数据更新两大类操作，其中数据更新又包括插入、删除和更新三种操作。3数据控制语句（DCL，Data-Control Language） 对用户访问数据的控制有基本表和视图的授权、完整性规则的描述，事务控制语句等。,4.6.1 SQL基础知识,32,使用SQL语句，可以直接在SQL视图中修改已建查询中的准则。1数据查询的一般格式SELECT AS 别名FROM WHERE 条件表达式GROUP BY 列名集合 HAVING 组条件表达式ORDER BY 列名ASC|DESC在SELECT语法格式中，大写字母为SQL保留字，尖括号所括部分为必须选的内容，方括号所括部分为可选择的内容。,4.6.2 使用SQL修改查询中的准则,33,2常用数据查询【例4.16】用SQL修改“95年前工作的副教授信息”查询，使查询的结果显示为：“95年前工作的教授信息”。演示具体操作：,4.6.2 使用SQL修改查询中的准则,34,SQL查询分为联合查询、传递查询、数据定义和子查询四种。1创建联合查询 联合查询功能是由UNION子句实现的，其含义是将两个SELECT命令的查询结果合并成一个查询结果。子句格式：UNIONALL其中ALL表示结果全部合并。若没有ALL，则重复的记录将被自动取掉。合并的规则是：（1）不能合并子查询的结果。（2）两个SELECT命令必须输出同样的列数。（3）两个表各相应列出的数据类型必须相同，数字和字符不能合并。（4）仅最后一个SELECT命令中可以用ORDER BY子句，且排序选项必须用数字说明。,4.6.2 创建SQL查询,35,创建名为“合并学生信息”的查询，查询“成绩60以下学生信息”表中学生信息和“学生”表中营销与策划专业学生信息，显示学生的姓名、性别和所属专业字段。演示具体操作：,【例4.17】,36,2创建传递查询 传递查询是SQL特定查询之一，Access传递查询是自己并不执行而传递该另一个数据库来执行的查询。传递查询可直接将命令发送到ODBC数据库服务器中。使用传递查询时，不必与服务器上的表链接，就可以直接使用相应的表。3建立数据定义查询 数据定义查询与其他查询不同，利用它可以直接创建、删除或更改表，或者在当数据库中创建索引。在数据定义查询中要输入SQL语句，每个数据定义查询只能由一个数据定义语句组成。,4.6.2 创建SQL查询,37,（1）表的定义create table （字段类型NOT NULL，（2）修改表结构ALTER TABLE 表名 ADD字段名 类型（3）索引的建立和删除CREAT UNIQUEINDEX 索引名 ON 表名（字段名ASC/DESC，字段名ASC/DESC）DROP INDEX 索引名（4）删除表DROP TABLE 表名,38,例题,【例4.18】使用CREATE TABLE语句创建“STUD”表，它由以下字段组成：学号 （C，10）；姓名（C，8）；性别（C，2）；班级名（C，10）；系别代号（C，2）；地址（C，50）；是否团员（L）；备注（M）。【例4.19】在课程成绩表SC中，增加一个出生日期（D）字段。,39,4.6.2 创建SQL查询,4使用子查询 在对Access表中的字段进行查询时，可以利用子查询的结果进行下一步的查询，不能将子查询作为单独的一个查询，必须与其他查询相结合。【例4.20】创建查询名为“成绩大于平均分”，显示“学生课程成绩”表中成绩高于平均成绩的学生课程成绩记录。,40,UPDATE 表名 SET 字段=表达式，字段=表达式WHERE 条件表达式例：UPDATE 导师 SET 工资=导师!工资+1000,4.6.3 数据修改语句,41,DELETE FROM 表名WHERE 条件表达式例： DELETE FROM 研究生 WHERE入学分数340,4.6.4 删除记录语句,42,INSERT INTO 表名（字段名，字段名）values （常量，常量）例： INSERT INTO 研究生(学号,姓名,性别,入学日期,入学成绩,研究方向,导师编号) values(205,张三,男,1985-9-1,计算机,101),4.6.3 插入记录语句,43,本讲小结,查询是Access数据库的主要组件之一，它包括选择查询、参数查询、交叉表查询、操作查询和SQL查询，其中操作查询又包括更新查询、生成表查询、追加查询、删除查询。每种不同的类型实现了不同的功能，需灵活掌握它们的创建方法和运行方式。,44,习题四,一、单项选择题(自己做在教材上)二、填空题(自己做在教材上)三、简答题(上交),
