ACCESS查询的设计与使用课件.ppt

第3章 查询的设计与使用,讲授4学时、上机4学时,Access数据库应用技术,总体要求：,掌握查询的作用和分类熟练掌握查询的各种视图掌握各类查询的设计和作用掌握SQL语言的基础掌握SQL查询的设计和使用,Access数据库应用技术,学习重难点：,熟练掌握各类查询视图和各类查询的设计掌握SQL语句基本格式和SQL查询的应用,Access数据库应用技术,主要内容：,3.1 查询的概念3.2 查询的创建3.3 SQL语言基础,Access数据库应用技术,3.1 查询的概念,查询是具有条件检索和计算功能的数据库对象。查询是以表或查询为数据源的再生表。查询对象不是数据的集合，而是操作的集合。查询的运行结果是一个动态数据集合。,Access数据库应用技术,3.1.1 查询的作用与功能,查询按一定的条件生成一个动态数据集：通过使用查询可按不同的方式来查看、更改和分析数据；利用查询对选择的记录组执行多种类型的计算。通过使用查询生成新的数据表。通过使用查询实现数据源表数据的删除、更新或追加；查询也可以作为窗体、报表或数据访问页的数据源，实现多个表作为数据源,Access数据库应用技术,3.1.2 查询的分类,选择查询参数查询操作查询交叉表查询SQL查询,Access数据库应用技术,3.1.3、查询视图,查询视图主要用于设计、修改查询或按不同方式查看查询结果，在Access中提供了三种常用视图，分别是数据表视图、设计视图、SQL视图。,Access数据库应用技术,1查询的数据表视图,查询的数据表视图是以行和列的格式显示查询结果数据的窗口。在这个视图中，可以进行字段编辑、添加和删除数据、查找数据、排序、筛选等操作，还可以进行视图的显示风格的设计。查询的数据表视图具体的操作方法和数据表操作一样。,Access数据库应用技术,2查询的设计视图,查询的设计视图是用来设计查询的窗口，是查询设计器的图形化表示。查询设计视图由上、下两个窗口构成，即表/查询显示窗口和查询设计网格窗口（也称为QBE网格）,Access2003数据库应用技术,（1）表/查询显示窗口它是当前查询所包含的数据源（表和查询）以及表间关系。在这个窗口里可以添加或删除表，可以建立表间关系。,(2)查询设计网格它用于设计显示查询字段、查询准则等。其中每一行都包含查询字段的相关信息，列是查询的字段列表。,Access数据库应用技术,在查询的设计视图下，Access2003还提供了查询属性设置，可以方便地控制查询的运行要设置查询属性，可以在表/查询窗口内右击，在弹出菜单中单击【属性】命令或直接单击数据库工具栏中的【属性】按钮,Access数据库应用技术,常用的查询属性设置主要包括下列几项：说明：对查询的相关说明输出所有字段：该属性用来控制查询中字段的输出，只有当用户设计的查询用于窗体并希望查询中表的所有字段也适用于窗体时才可以设置为“是”，没有特别要求时请使用默认的“否”。上限值：当用户希望查询返回“第一个”或“上限”记录时，可以使用该选择项。唯一的记录：运用该选项可以来消除查询中重复行的目的。运行权限：当在网络上与其他用户一起共享时，从安全的角度出发点，可以使用该选项来设置用户的查看数据和修改数据的权限。记录锁定：对于网络中共享的查询来说，可以使用该选项来控制查询编辑的整体层次。,Access数据库应用技术,3查询的SQL视图,查询的SQL视图是用来显示或编辑打开查询的SQL语句的窗口。,Access数据库应用技术,3.2 查询的创建,在Access 2003中，可以通过三种方式创建查询通过向导创建查询通过查询的设计视图创建查询通过SQL视图创建查询,Access数据库应用技术,3.2.1 创建选择查询,选择查询是可以按照一定的准则从一个或多个表中获取数据，并按照所需的次序进行排列显示。可以使用选择查询对记录进行分组，并且对记录作总计、计数、平均以及其他类型的计算。【例3-1】在“学籍管理”数据库中的“学生基本情况表”中查询出所有女生信息。【例】在“罗斯文”数据库的“客户”表中筛选公司名称分别为“华科”、“悦海”的所有客户记录。【例】在“罗斯文”数据库的“客户”表中找出城市为“大连”、联系人为“王先生”的记录。【例】在“罗斯文”数据库的“产品”表中查询所有中止销售的产品记录。并按类别的降序排列。,Access数据库应用技术,操作要点：,在数据库窗口中，选择“查询”对象“在设计视图中创建查询”选项在“显示表”窗口中添加被查询的表或查询要查询网格中设置查询准则,Access数据库应用技术,3.2.2 创建交叉表查询,交叉表查询是一类特殊的查询，允许用户在行列交叉处查看计算的结果。交叉表查询主要用来计算数据总和、平均值、计数或其他类型的总值。【例3-2】在“学籍管理”数据库中从“学生基本情况表”、“选课表”查询学生各科成绩录入次数。【例】创建交叉表查询，统计“学籍管理”数据库“学生基本情况表”中各专业的男女生人数。【例】对“学籍管理”数据库创建交叉表查询，统计每位同学的选课成绩。,Access2003数据库应用技术,操作要点：,在数据库窗口中选择“在设计视图中创建查询”在显示表窗口中添加被查询的表或查询通过【查询】的【交叉表查询】命令，将选择查询转换成交叉表查询在查询网格中设置查询准则,Access2003数据库应用技术,3.2.3 创建参数查询,参数查询也是一类特殊的查询，参数特指查询准则中的变量，在查询运行时会显示对话框提示用户输入变量的值。【例3-3】在“学籍管理”数据库中，创建一个“学生年份查询”查询，输入年份后从“学生基本情况表”表中查询出生在本年份的学生姓名与出生日期。【例】在“罗斯文”数据库中以折扣为参数创建“订单明细”表的参数查询。【例】在“学籍管理”数据库中，查询“学生基本情况表”中入学成绩在某一分数段的学生名单。,Access2003数据库应用技术,操作要点：,在数据库窗口中选择“在设计视图中创建查询”在“显示表”窗口 添加表设置参数,Access2003数据库应用技术,3.2.4 创建操作查询,操作查询又称为动作查询，就是通过查询完成某些动作。根据动作操作查询又可分为:追加查询更新查询删除查询生成表查询,Access2003数据库应用技术,1创建生成表查询,生成表查询实际上是将查询的动态数据集转化成了新的数据表。【例3-4】在“学籍管理”数据库中，由“学生基本情况表”表和“选课表”表查询创建“2005学年成绩表”表，表包含“学号”、“姓名”、“课程编码”、“成绩”、“学年”字段。【例】将“学籍管理”数据库中“学生基本情况表”表中所有专业为“计算机科学”的记录生成一张新表，名为“专业表”。,Access2003数据库应用技术,操作要点：,在数据库窗口中选择“在设计视图中创建查询”在“显示表”窗口 添加表【查询】【生成表查询】菜单命令 设置查询准则,Access2003数据库应用技术,2创建追加查询,追加查询是从一个或多个表中将一组记录追加到另一个表的尾部的查询方式。【例3-5】在“学籍管理”数据库中，以“教师基本情况表”复制一个“教师表”，只复制结构，建立一个追加查询，将“教师基本情况表”中“职称”是“讲师”的记录追加到“教师表”中。【例】将“学籍管理”数据库中“学生基本情况”表中专业为“电子技术”的记录追加到前面例题创建的“专业表”中。,Access2003数据库应用技术,操作要点：,选择“在设计视图中创建查询”在“显示表”窗口 添加表【查询】【追加查询】菜单命令 设置查询准则,Access2003数据库应用技术,3创建更新查询,更新查询就是用从一个或多个表中查询出来的结果去更新一个或多个表中的数据内容的查询。【例3-6】在“学籍管理”数据库中，从“教师基本情况表”中查询出“教师表”中教师的相对应的“学历”去更新“教师表”中的“学历”。【例】将“罗斯文”数据库的“订单明细”表中的所有产品的单价打8折。,Access2003数据库应用技术,操作要点：,选择在设计视图中创建查询 在“显示表”窗口 添加表【查询】【更新查询】菜单命令 设置查询准则,Access2003数据库应用技术,Access2003数据库应用技术,4创建删除查询,删除查询是指从一个或多个表中删除一组记录的查询，使用删除查询是删除整条记录，而不是记录中的相应查询中所选择的字段。【例3-7】在“学籍管理”数据库中创建一个删除查询，把“2005学年成绩表”中成绩低于60分的记录删除。【例】将“学籍管理”数据库中“学生基本情况表”中专业为“计算机科学”和“电子技术”的所有记录删除。,Access2003数据库应用技术,操作要点：,在数据库窗口中复制表结构选择在设计视图中创建查询 在“显示表”窗口 添加表【查询】【删除查询】菜单命令 设置查询准则,Access2003数据库应用技术,设置查询准则,逻辑运算符,比较运算符,Access2003数据库应用技术,设置查询准则,其它运算符,Access2003数据库应用技术,例：对“学籍管理”数据库中的“学生基本情况”表创建查询，查询所有非电子技术系的女生情况。“电子技术”（not“电子技术”)例：查询“罗斯文商贸”数据库中的“客户”表中所有“传真”字段为空的记录。Is null例:查询“罗斯文商贸”数据库的“客户”表中所有姓谢的“联系人”的记录。Like“谢*”,设置查询准则,Access2003数据库应用技术,3.3 SQL语言基础,SQL（Structured Query Language）结构化查询语言，是一种通用的数据库操作语言。SQL语句最主要的功能就是查询功能。使用SQL语句不需要在不同的工作区打开不同的表，只需将要连接的表、查询所需的字段、筛选记录的条件、记录分组的依据、排序的方式以及查询结果的显示方式，写在一条SQL语句中，就可以完成特定的工作。,Access2003数据库应用技术,3.3.1 数据定义,数据定义语言(Data Definition Language 简称DDL)是SQL语言的重要组成部分，它包括有数据表的创建、删除、更改或在当前数据库中表的索引的建立等，Access2003中常用的语句有Create、Drop、Alter三种。,Access2003数据库应用技术,1CREATE语句,CREATE TABLE语句用于定义一个新表及其字段和字段约束。其格式为：CREATE TABLE table(field1 type(size)NOT NULL WITH COMPRESSION|WITH COMP index1,field2 type(size)NOT NULL index2,.,CONSTRAINT multifieldindex,.)【例3-8】在“学籍管理”数据库中，用数据定义查询创建一个“示例表”（姓名 文本（6），性别 是/否）。create table 示例表(姓名 varchar(6)，性别 bit),Access2003数据库应用技术,2ALTER语句,ATLTER TABLE 语句用来来增加、删除表的字段和索引以及修改字段属性的语句。语句的格式为：ALTER TABLE table ADD COLUMN field type(size)NOT NULL CONSTRAINT index|ALTER COLUMN field type(size)|CONSTRAINT multifieldindex|DROP COLUMN field I CONSTRAINT indexname 例：在“学籍管理”数据库的“教师授课表”中添加（删除）字段“教师姓名”。alter table 教师授课表 ADD column 教师姓名 Varchar(6)alter table 教师授课表 Drop column 教师姓名,Access2003数据库应用技术,3DROP语句,DROP语句用于删除表或表的索引所用。语句格式为：DROP TABLE table|INDEX index ON table注意：在删除表或从表中删除索引之前，必须先关闭该表。例：删除前面在“教师授课表”中创建的“教班编号索引”。DROP index 教班编号索引 on 教师授课表例：删除“学籍管理”数据库下的“选课表”。DROP table 选课表,Access2003数据库应用技术,3.3.2 SQL查询,SQL查询是利用SELECT语句来书写的查询，也就是在查询的SQL视图下来完成的查询SQL命令的所有子句既可以写在同一行上，也可以分行书写；大小写字母的含义相同，命令用分号“;”结束(也可以不写),Access2003数据库应用技术,【例3-16】在“学籍管理”数据库中的“学生基本情况表”中查询出年龄大于23岁的女生。SELECT*from 学生基本情况表 WHERE 性别=女 and(date()-出生日期)/36523,Access2003数据库应用技术,【例3-17】在“学籍管理”数据库中“学生基本情况表”中查询入学成绩在450分到600分之间且专业属于“电子技术”或“计算机科学”的学生。SELECT 姓名,专业,入学成绩 FROM 学生基本情况表 WHERE(入学成绩 BETWEEN 450 AND 600)AND 专业 IN(电子技术,计算机科学),Access2003数据库应用技术,4GROUP BY子句,使用GROUP BY 子句进行分组时，显示的字段只能是参与分组的字段以及基于分组字段的合计函数计算结果。【例3-18】从“学籍管理”数据库中“学生基本情况表”中查询各专业的入学成绩平均分。SELECT 专业,AVG(入学成绩)as 入学成绩平均分 FROM 学生基本情况表 GROUP BY 专业,Access2003数据库应用技术,5ORDER BY 子句,RDER BY子句一般在SELECT语句最后，是用来指定查询结果以什么顺序返回。其格式为：ORDER BY 字段1 ASC|DESC,字段2 ASC|DESC,.【例3-20】从“学籍管理”数据库中的“学生基本情况表”中查询出学生学号、姓名并按姓名降序返回结果。SELECT DISTINCT 学号，姓名 FROM 学生基本情况表 ORDER BY 姓名 DESC,Access2003数据库应用技术,3.3.3 数据操纵,在SQL中数据操纵是指数据的更新、删除、追加，数据操纵的SQL语句有UPDATE、DELETE、INSERT INTO三种,Access2003数据库应用技术,1UPDATE语句,UPDATE语句用于基于特定条件更改指定表中的字段值，当需要更改多个记录或者需要更改的记录存在于多个表中时，UPDATE 语句是最有用的。UPDATE语句的格式：UPDATE table SET newvalue WHERE criteriaUPDATE对表中记录的更改不得违反所有的数据完整性约束。,Access2003数据库应用技术,【3-24】将“学籍管理”数据库中的“教师基本情况表”中政治面貌为空的记录用“非党员”去更改。UPDATE 教师基本情况表 SET 政治面貌=非党员 WHERE 政治面貌 IS NULL,Access2003数据库应用技术,2DELETE语句,DELETE语句用于从一个或多个表中删除满足条件的记录，使用 DELETE 语句时，只删除其中的数据，表的结构和所有表属性（如字段属性和索引）依然保持原样。格式如下：DELETE table.*FROM table WHERE criteria,Access2003数据库应用技术,DELETE语句删除时，无论DELETE 后table.*指不指定内容，删除均以整条记录为单位，不会是字段的数据。【例3-25】从“学籍管理”数据库的“学生基本情况表”中删除入学成绩低于450的记录。DELETE FROM 学生基本情况表 WHERE 入学成绩450,3INSERT INTO语句,INSERT INTO语句用于将一个记录或多个记录追加到表中，而且一个记录的追加和多个记录的追加格式有所不同。多记录追加：INSERT INTO target(field1，field2，.)SELECT source.field1，field2，.FROM tableexpression单记录追加：INSERT INTO target(field1，field2，.)VALUES(value1，value2，.),Access2003数据库应用技术,【例3-26】将“教师基本情况表”中的全部记录追加到“教师表”中。INSERT INTO 教师表(教师编号,教师编号,性别,工作时间,政治面貌,政治面貌,学历,职称,系别,联系电话)SELECT 教师基本情况表.教师编号,教师基本情况表.姓名,教师基本情况表.性别,教师基本情况表.工作时间,教师基本情况表.政治面貌,教师基本情况表.政治面貌,教师基本情况表.学历,教师基本情况表.职称,教师基本情况表.系别,教师基本情况表.联系电话 FROM 教师基本情况表;,Access2003数据库应用技术,【例3-27】向“学籍管理”数据库的“用户表”中追加一条用户名为“USER”、用户密码为“SYSTEM”的记录。INSERT INTO 用户表(用户名,用户密码)VALUES(USER,SYSTEM)数据操纵用在查询中就是更新查询、删除查询、追加查询。在查询的SQL视图中输入相应的语句就可以，实现三种查询。,Access2003数据库应用技术,