第8章-查询.pptx

单击此处编辑母版标题样式,2019-1-1,#,/24,第,8,章 查询,Click to edit Master title style,主编,:,向隅,2025/5/22 周四,Access2013,数据库技术及应用,配套电子课件,主编,:,向隅,第,8,章 查询,【,技能目标,】,了解查询的类型,掌握使用向导创建查询的方法,设计视图创建查询的方法,掌握参数查询和操作查询,掌握,SQL,查询,【,目录,】,8.1,选择查询,8.2,使用查询向导创建查询,8.3,使用查询设计视图创建查询,8.4,创建操作查询,8.5 SQL,高级查询,8.6,参数查询,8.1,查询的类型,查询是数据库处理和分析数据的工具，是对数据库中的数据进行查找，形成动态数据集。查询可以是在指定的,(,一个或多个,),表中，根据给定的条件从中筛选所需的信息，供使用者查看、更改和分析使用。可以使用查询回答简单问题、执行计算、合并不同表中的数据，甚至添加、更改或删除表中的数据。,查询是,Access 2013,数据库的一个重要对象，通过查询筛选出符合条件的记录，构成一个新的数据集合。,8.1,查询的类型,1,查询的功能,查询具有如下功能：,(1),查看、搜索和分析数据。,(2),用来追加、更改和删除数据。,(3),实现记录的筛选、排序、汇总和计算。,(4),用来作为报表和窗体的数据源。,(5),对一个或多个表中获取的数据实现联接。,8.1,查询的类型,2,查询的,类别,1),选择查询,2),参数查询,3),交叉表查询,4),操作查询,(1),更新查询,(2),删除查询,(3),追加查询,(4),生成表查询,(5)SQL,特定查询,8.2,使用查询向导创建查询,使用向导创建查询是通过“创建”选项卡“查询”组中的“查询向导”命令来完成的。“创建”选项卡“查询”组中的命令如,图所,示,。,8.2,使用查询向导创建查询,使用查询向导不仅可以依据单个表创建查询，也可以依据多个表创建查询。,使用,“查询向导”命令可以创建以下四种查询：,(1),简单,查询,(2),交叉,表查询,(3),查找,重复项查询,(4),查找,不匹配项查询,8.2,使用查询向导创建查询,1.,简单查询,简单查询可以是单表查询，也可以是多表查询。,(1),创建单表查询,查询结果中的数据来源于一个表。,【案例,8-1,】使用,cjgl,数据库中的,specialty,数据表，建立一个简单表查询，结果中只包括专业代码和专业名称。,8.2,使用查询向导创建查询,1.,简单查询,(2),创建多表查询,【案例,8-2,】从,cjgl,数据库的,student,、,course,和,grade,数据表中查询学生选课的具体信息，包括“学号”“姓名”和“课程名”等字段。,8.2,使用查询向导创建查询,2,使用向导创建交叉表查询,【案例,8-3,】使用,cjgl,数据库中的,department,数据表，建立一个交叉表查询，“负责人”字段为行标题，“院系名称”字段为列标题，表中数据填该负责人的电话，效果如,图所,示。,8.2,使用查询向导创建查询,3,使用向导创建查找重复项,查询,【,案例,8-4,】使用“查找重复项查询向导”创建查询，在,cjgl,数据库中对,specialty,数据表按院系统计各院系的专业数，如,图所,示,8.2,使用查询向导创建查询,4,使用向导创建“查找不匹配项查询”,“查找不匹配项查询”的作用是用户可以从指定的数据表中检索那些在另一个数据表中没有相关记录的数据。,【案例,8-5,】在,cjgl,数据库中找出未开课程的信息，即在,course,数据表和,teacher,数据表中找出不匹配的记录。如,图,所示,8.3,使用查询设计视图创建查询,对于比较简单的查询，使用查询向导创建查询比较方便，但对于需要设置查询条件的查询，则使用查询的设计视图来创建查询更加方便。,单击“创建”选项卡“查询”组中的“查询设计”按钮，打开如,图所,示的设计视图窗口,。,8.3,使用查询设计视图创建查询,1,单表查询,【案例,8-6,】使用,cjgl,数据库中的,department,表建立一个简单的单表查询，查询结果中只包括“院系名称”和“负责人”字段。,8.3.1,使用查询设计视图创建简单查询,8.3,使用查询设计视图创建查询,2,条件,查询,【案例,8-7,】建立一个简单查询，显示“,specialty,”表中“院系代码”为“,02,”的专业名称和专业代码,。,8.3.1,使用查询设计视图创建简单查询,8.3,使用查询设计视图创建查询,3,计算,查询,【案例,8-8,】建立一个简单查询，统计,teacher,表中各院系的教工人数。,8.3.1,使用查询设计视图创建简单查询,8.3,使用查询设计视图创建查询,涉及两个或两个以上表的查询称为联接查询。,【案例,8-9,】显示“交通运输学院”的所有专业名称。,案例,8-9,涉及,department,和,specialty,表。,8.3.2,使用查询设计创建联合查询,8.3,使用查询设计视图创建查询,【案例,8-10,】通过,course,表和,grade,表创建查询，设置联接属性，使查询结果包括,grade,表中的所有记录,。,8.3.3,设置联接属性创建查询,8.4,创建操作查询,保存查询的结果时，可以使用生成表查询。生成表查询既可以是从一个表中检索的数据，也可以是从多个表中检索的数据。生成的新表既可创建在当前数据库中，也可创建在其他数据库中。,【案例,8-11,】在,cjgl,数据库中建立名为“铁道运输,1501,”的表，表中保存着班级代码为,150121,的学生信息。,8.4.1,创建生成表查询,8.4,创建操作查询,【案例,8-12,】创建一个查询，它能使,cjgl,数据库中,student,数据表中的年龄增大两天，即出生年月减,2,。,8.4.2,创建更新查询,8.4,创建操作查询,【,案例,8-13,】创建一个追加查询，它能将“学生”表中不属于“铁道运输,1501,”表的记录添加到“铁道运输,1501,”表中。,8.4.3,创建追加查询,8.4,创建操作查询,【,案例,8-14,】创建一个删除查询，它能将不属于“铁道运输,1501,”班学生的信息删除。,(,班级代码为“,150121,”,),。,8.4.4,创建删除查询,8.5 SQL,高级查询,SQL(Structured,Query Language,，结构化查询语言,),是一种介于关系代数与关系演算之间的结构化语言，它已被用户和业界广泛接受，并被确定为关系数据库管理系统的标准语言。它不仅通用，而且功能强大，简单易学。目前，绝大多数流行的数据库管理系统都遵循,SQL,标准。,SQL,集数据查询,(Data Query),、数据操纵,(Data Manipulation),、数据定义,(Data Definition),和数据控制,(Data Control),功能于一体，充分体现了关系数据语言的特点和,优点。,8.5.1 SQL,概述,8.5 SQL,高级查询,SQL,语言的主要特点,(1),综合统一,(2),高度非过程化,(3),面向集合的操作方式,(4),以同一种语法结构提供两种使用方式,(5),语言简捷，易学易,用,8.5.1 SQL,概述,8.5 SQL,高级查询,SQL,语言分为四大类。,数据查询语言。按照指定的组合、条件表达式或排序检索数据库中已存在的数据，但并不改变数据库中的数据。由,SELECT,语句加上,FROM,子句、,WHERE,子句等构成。,数据定义语言。用于创建、删除或修改数据库中的各类对象，包括表、视图和索引等。使用,CREATE,、,DROP,、,ALTER,动词,8.5.1 SQL,概述,8.5 SQL,高级查询,常用的操作如下：,创建表,(CREATE TABLE),创建视图,(CREATE VIEW),创建索引,(CREATE INDEX),创建存储过程,(CREATE PROCEDURE),创建触发器,(CREATE TRIGGER),修改表,(ALTER TABLE),删除表,(DROP TABLE),删除视图,(DROP VIEW),删除索引,(DROP INDEX),删除存储过程,(DROP PROCEDURE),删除触发器,(DROP TRIGGER),8.5.1 SQL,概述,8.5 SQL,高级查询,数据操纵语言。对已经存在的数据库进行记录的插入、删除、修改等操作。使用,INSERT,、,UPDATE,、,DELETE,动词。,数据控制语言。用于数据的控制管理，如对表授权、回收授权、提交事务、回滚事务、加锁等。使用的动词分别是,GRANT,、,REVOKE,、,COMMIT,、,ROLLBACK,、,LOCK,。,8.5.1 SQL,概述,8.5 SQL,高级查询,完整的,SELECT,语句的语法结构比较复杂，这里介绍,SELECT,语句的主要语法结构。,SELECT,语句的语法结构如下：,SELECT ALL|DISTINCTTOP n PERCENT,字段列表,INTO,新表,FROM,数据源,WHERE,条件表达式,GROUP BY,分组表达式,HAVING,搜索表达式,ORDER BY,排序表达式,ASC|DESC,WITH OWNERACCESS OPTION,8.5.2 Select,查询,8.5 SQL,高级查询,SELECT,各子句的执行顺序及功能简介如下：,(1)SELECT,子句：用于输出查询结果集中的列及其属性，也可求值输出。,(2)INTO,子句：用于创建新表并将查询结果集插入新表中。,(3)FROM,子句：指定,SELECT,语句中使用的表和视图以及各表之间的逻辑关系。,(4)WHERE,子句：给出查询条件，指定选取记录子集的条件。,(5)GROUP,BY,子句：指定对检索到的记录进行分组的条件,。,8.5.2 Select,查询,8.5 SQL,高级查询,(6)HAVING,子句：在分组的基础上指定选取某些组的条件。必须与,GROUP BY,共用。,(7)ORDER,BY,子句：对检索的结果集进行排序；,ASC,关键字指定行按升序排序，,DESC,关键字指定行按降序排序，默认值为,ASC,，即升序。,(8)FOR,子句：指定数据的返回形式。,(9)OPTION,子句：指定应在整个查询中使用所指定的查询提示。,8.5.2 Select,查询,8.5 SQL,高级查询,1,SELECT,语句,SELECT,语句用于指定由查询返回的列。舍弃所有可选子句，最简单的访问数据库的,SELECT,语法格式如下：,SELECT ALL|DISTINCT TOP n PERCENT,*|,字段,1,或表达式,1 AS,列别名,1,字段,2,或表达式,2 AS,列别名,2,FROM,表名,|,视图名,|,表,别名,8.5.2 Select,查询,8.5 SQL,高级查询,参数说明：,ALL,：指定在结果集中可以显示重复的行。这是默认值。,DISTINCT,：指定在结果集中去掉重复的行，重复的行只显示一行。,TOP n PERCENT,：指定只从查询结果集中输出前,n,行。,N,是介于,04 294 967 259,的整数。如果还指定了,PERCENT,，则只从结果集中输出前百分之,n,行。当指定时带,PERCENT,，,n,必须是介于,0100,之间的整数。如果查询还包含,ORDER BY,子句，将输出由,ORDER BY,子句排序的前,n,行,(,或前百分之,n,行,),。如果查询没有,ORDER BY,子句，则行的顺序任意。,8.5.2 Select,查询,8.5 SQL,高级查询,选择列表：是一个逗号分隔的表达式列表，每个表达式定义结果集中的一列。表达式通常是对,FROM,子句中指定的数据源,(,表或视图,),的列的引用，但也可能是其他表达式。,*,：返回由,FROM,子句的数据源指定的所有列。,表名,|,视图名,|,表别名：将,*,的作用域限制为指定的表或视图。,列别名：是查询结果集内替换列名的可选名。,8.5.2 Select,查询,8.5 SQL,高级查询,(1),查询所有列,将表中的所有字段都在结果集中列出来，可以有两种方法：一种是将所有字段名在,SELECT,关键字后列出，另一种是在,SELECT,语句后使用一个“,*,”。,【案例,8-15,】查询,department,表中的所有记录。,8.5.2 Select,查询,8.5 SQL,高级查询,(,2),查询指定列,很多情况下，用户只希望显示表中的部分属性列，这时可在,SELECT,语句后指定需要显示的列名，滤掉表中不需要的列。所选列之间用逗号分开，查询结果集中数据的排列顺序与选择列表中所指定的列名排列顺序相同。,【案例,8-16,】查询部门负责人及其电话。,8.5.2 Select,查询,8.5 SQL,高级查询,(,3),查询经过计算的列,SELECT,语句后不仅可以是表中的属性列，而且可以是表达式，即可以是查询出来的属性列经过一定计算后得到的结果。,【案例,8-17,】显示,course,表中的信息，课时数在原有的基础上每学分增加一个学时。,8.5.2 Select,查询,8.5 SQL,高级查询,(,4),设置列别名,在显示选择查询的结果时，可为字段名指定一个更易于理解的名字。特别是查询结果集中的列不是表中现成的列，而是一个或多个计算的列，这时需要对计算列指定一个列别名。格式如下：,SELECT,表达式,AS,列别名,FROM,数据源,SELECT,表达式 列别名,FROM,数据源,SELECT,列别名,=,表达式,FROM,数据源,【案例,8-18,】查询学生姓名、性别及年龄信息。,8.5.2 Select,查询,8.5 SQL,高级查询,(,5),限制返回行数,若用户只想看一下表中记录的样式和内容，而表中的记录较多，这时没必要显示全部记录。此时可在,SELECT,语句中加入关键字，限制返回的行数。格式如下,：,SELECT TOP n PERCENT FROM,数据源,其中,TOP n,表示查询结果只显示表中前,n,条记录，,TOP n PERCENT,表示查询结果只显示表中前,n%,条记录。,【案例,8-19,】显示,student,表中前,5,行数据。,8.5.2 Select,查询,8.5 SQL,高级查询,(,6),消除重复的行,在实际的应用中查询指定列时，两个本来并不相同的记录，通过对指定列的投影，结果变成相同的行，而我们不希望出现重复的行，这时可在,SELECT,语句中的字段列表前加上,DISTINCT,关键字，去掉结果集中的重复行。,【案例,8-20,】查询入学年份。,8.5.2 Select,查询,8.5 SQL,高级查询,2,WHERE,子句,8.5.2 Select,查询,搜索条件,条件运算符,比较,=,、,、,=,、,、,!(,大于,),、,=(,大于或等于,),、,=(,小于或等于,),、,(,不等于,),、,!=(,不等于,),、,!(,不大于,),。其语法格式如下：,比较运算符,注意：,text,、,ntext,和,image,数据类型不能组成比较搜索条件。,【案例,8-21,】显示,grade,表中成绩高于,85,的学生信息。,WHERE,子句,8.5 SQL,高级查询,(,2),确定范围,范围运算符,BETWEEN AND,和,NOT BETWEENAND,用于查找属性值在,(,或不在,),指定范围内的记录，其效果类似于使用了,=,和,和,),逻辑表达式。其中,BETWEEN,后是范围的下限，,AND,后是范围的上限。语法格式如下：,列表达式,NOTBETWEEN,起始值,AND,终止值,【案例,8-22,】显示成绩在,7085,间学生的学号、课程号和分数。,WHERE,子句,8.5 SQL,高级查询,(,3),确定集合,IN,和,NOT IN,用来确定指定的属性值是否与子查询或列表中的值匹配。其语法格式为：,列表达式,NOT IN(,子查询,|,表达式,1,n),【案例,8-23,】显示部门代码为,01,和,04,的部门名称、负责人和电话。,WHERE,子句,8.5 SQL,高级查询,(,4),模式匹配,(LIKE,和,NOT LIKE),SQL,中可使用,LIKE,和,NOT LIKE,来实现不完全匹配查询。使用,LIKE,或,NOT LIKE,的一般格式为：,NOTLIKE ESCAPE,其含义是查找指定的属性列值与,相匹配的记录。,可以是一个完整的字符串，也可以含有通配符,WHERE,子句,8.5 SQL,高级查询,通配符,如,表所,示。,WHERE,子句,通配符,含义,示例,_(,下划线,),可与任意单个字符匹配,例如,a_b,表示以,a,开头，以,b,结尾的长度为,3,的任意字符串，如,acb,、,abb,等,%(,百分号,),可与任意个字符匹配,例如,a%b,表示以,a,开头，以,b,结尾的任意长度的字符串，如,accb,、,ab,等,匹配指定范围内的任何单个字符,例如,abcde,表示第一个字符是,a,，第二个字符是,b,、,c,、,d,、,e,中任意一个，也可以是字符范围，如,ab-e,与,abcde,的含义相同,匹配不在指定范围内的任何单个字符,例如,af,表示,af,范围以外的任意单个字符,8.5 SQL,高级查询,(,5),空值,一般情况下，表的每一列都有其存在的意义，但有时某些列可能暂时没有确定的值，这时可以不输入该列的值，该列的值为,NULL,。空值运算符,IS NULL,用来判断指定的列值是否为空。其语法格式如下：,NOT,列表达式,IS NULL,【案例,8-25,】某些学生选修某门课程后没有参加考试，所以有选课记录，没有考试成绩。现在需要了解缺考学生的学号和课程号,。,WHERE,子句,8.5 SQL,高级查询,(,6),多重条件查询,用户可使用逻辑运算符,AND,、,OR,、,NOT,连接多个查询条件，实现多重条件查询。逻辑运算符使用格式如下：,NOT,逻辑表达式,AND|OR NOT,逻辑表达式,【案例,8-26,】查询,student,表中出生日期在,19981999,年之间的女学生信息。,WHERE,子句,8.5 SQL,高级查询,ORDER,BY,子句用于根据查询结果按照一个或多个属性列的升序,(ASC),或降序,(DESC),对查询结果进行排列，默认为升序，用作排序依据的属性列总长度可达,8060,。如果不使用,ORDER BY,子句，则结果集按照记录在表中的顺序排列。,ORDER BY,子句的语法格式如下：,ORDER BY,列名,ASC|DESC,n,【案例,8-27,】对,teacher,表中的行进行排序，先按部门代码降序排列，然后在部门代码范围内按教工号升序排列。,ORDER,BY,子句,8.5 SQL,高级查询,在实际的数据库应用系统中，有时需要将查询结果保存成一个表。这可通过,SELECT,语句中的,INTO,子句实现。,INTO,子句的语法格式如下：,INTO,其中：,新表名是被创建的新表，符合标识符的定义。查询的结果集中的记录将被添加到此表中。,新表中的字段由结果集中的字段列表决定。,如果表名前加,#,，则创建的表为临时表。,用户必须拥有在数据库中创建表的权限。,【案例,8-28,】创建,department,表的一个副本，并显示。,用,查询结果生成新,表,8.5 SQL,高级查询,在,SELECT,语句中可使用统计函数、,GROUP BY,子句对查询结果进行分类汇总。下面分别进行介绍。,(1),分类汇总实例,【案例,8-29,】统计每门功课的平均成绩。,在成绩管理系统中，经常要统计每门功课的平均成绩。对于,grade,表的数据，,GROUP BY,子句按课程号将修同一门课程的同学成绩分组排在一起，然后求其平均值。,5.,分类汇总,8.5 SQL,高级查询,(2),常用统计函数,为方便用户，增强检索功能，有效地对数据集进行处理，如分类汇总、求平均值等统计，,Access 2013,提供了一系列统计函数,(,也称聚合函数或集合函数,),，如,SUM,、,AVG,等，通过它们可在查询结果集中生成汇总值。常用的统计函数如,表所,示。统计函数的使用方法几乎相同，通过,SELECT,语句来,运行,。,5.,分类汇总,8.5 SQL,高级查询,常用统计函数,5.,分类汇总,函数名,函数功能,SUM,计算某一数值列的和,AVG,计算某一数值列的平均值,MIN,求某一数值列的最小值,MAX,求某一数值列的最大值,COUNT,COUNT(*),统计满足,SELECT,语句中指定条件的记录数,统计符合查询限制条件的总行数,8.5 SQL,高级查询,在使用,GROUP BY,子句时，还可以使用,HAVING,子句为分组统计进一步设置统计条件，对结果再次进行筛选。,HAVING,子句用于指定一组或一个集合的搜索条件，若含有多个条件可通过逻辑运算符连接起来。,HAVING,子句必须与,GROUP BY,子句连用。,HAVING,子句的语法格式如下：,HAVING,其中,是一个表达式，用于指定组或集合所满足的搜索条件,。,【案例,8-35,】按班级、性别、学号、姓名对女生信息进行分组。,6,.,使用,HAVING,8.5 SQL,高级查询,SQL,的操纵功能是指对数据库的操作，包括数据的插入、数据的更新和数据的删除，下面分别介绍。,8.5.3,SQL,的数据操纵功能,8.5 SQL,高级查询,SQL,的操纵功能是指对数据库的操作，包括数据的插入、数据的更新和数据的删除，下面分别介绍,。,1,INSERT,语句,Access,的,SQL,使用,INSERT,语句可向数据表中追加新的数据记录。,【格式】,INSERT INTO(,),VALUES(,),【功能】根据已定义的表结构，在要插入数据的字段名表中插入数据。,8.5.3,SQL,的数据操纵功能,8.5 SQL,高级查询,说明：,(1)INSERT INTO,：指定要追加记录的表名，表名可包含路径。如果指定的表没有被打开，则将以独占方式在一个新的工作区将它打开，并追加进新记录。该新工作区并不会成为当前工作区，原当前工作区仍将是当前工作区。,(2)(,),：指定插入记录的字段名。若插入的数据与表结构定义字段的顺序一致，则字段名可以省略。,(3)VALUES(,),：指定插入记录的字段值。表达式数量、顺序、数据类型必须与所列字段一一对应。,(4),插入的部分数据可以不依照字段的顺序，但要求字段名和值相对应。,【案例,8-36,】向,class,表中加入班级数据,(,班级代码：,160121,；班级名称：铁道运输,1601,；专业代码：,520207,；入学年份：,201609),。,8.5.3,SQL,的数据操纵功能,8.5 SQL,高级查询,2,更新记录,Access,的,SQL,使用,UPDATE,命令更新表中的数据，更新操作适用于表或视图。,【格式】,UPDATE|SET=,=,WHERE,【功能】用指定字段,的值更新表中满足条件的字段值。,8.5.3,SQL,的数据操纵功能,8.5 SQL,高级查询,说明,：,(1)UPDATE|,：指定要更新的表名或视图名，若包含表的数据库不是当前数据库，必须用,选项指定这个数据库名。,(2)SET=,=,：指定更新的字段及字段值。,(3)WHERE,：指定更新的记录。只有满足条件的记录才被更新。若省略该项，则对表中所有的数据做相同的修改。,【案例,8-37,】将,course,表中的“学时”增加,4,学时。,8.5.3,SQL,的数据操纵功能,8.5 SQL,高级查询,3,删除记录,Access,的,SQL,使用,DELETE,命令删除表中的数据。,【格式】,DELETE FROM WHERE,【功能】删除数据表中符合,WHERE,条件的记录。,8.5.3,SQL,的数据操纵功能,8.6,参数查询,参数,查询不是一个单独种类的查询，它只是扩展了查询的灵活性。参数查询可以基于选择查询、交叉表查询、追加查询、生成表查询、更新查询、,SQL,联合查询中的任何一种创建参数查询。,【案例,8-39,】建立一个参数查询，它可根据输入的“班级代码”检索出其所在的专业及院系。,小结,本章主要讲述了以下内容：,1,查询的类型,Access 2013,中的查询有选择查询、交叉表查询、操作查询、,SQL,查询和参数查询。,2,使用向导创建查询,Access 2013,中使用查询向导可创建以下查询：,(1),使用,向导创建简单查询,(2),使用,向导创建交叉表查询,(3),使用,向导创建查找重复项查询,(4),使用,向导创建查找不匹配项查询,小结,3,使用查询设计视图创建查询,使用查询设计视图可创建单表查询、联接查询。,4,创建操作查询,操作查询不是一类查询，它包括在选择查询、交叉表查询、操作查询、,SQL,查询和参数查询。操作查询包括更新查询、生成表查询、删除查询和追加查询。除生成表查询外，所有操作查询都会更改基本表中的数据。,5,SQL,查询,SQL,查询就是用户使用,SQL,语句创建的一种查询。,SQL,是一种结构化查询语言，它包括联合查询、传递查询、数据定义查询和子查询等。,