数据库Access查询.ppt

第三章 查询,第一节 认识查询第二节 创建选择查询第三节 创建交叉表查询第四节 创建参数查询第五节 创建操作查询第六节 创建SQL查询,第一节 认识查询,一、什么是查询二、查询的功能三、查询的分类四、查询的视图,返回,一、查询的定义,查询是能够将存储于一个或多个表中符合要求的数据挑选出来，并对挑选的结果按照某种规则进行运算的对象,返回,二、查询的功能,选择字段；选择记录；数据集中；实现计算；编辑记录；建立新表；7作为其它数据库对象的数据源,返回,三、查询的分类,选择查询：根据某规则选取表中数据，并对选取的数据进行简单计算。交叉表查询：对表中数据进行分类并显示分类后某数据的明细值。操作查询：自动执行对表中数据进行某个特定功能的操作。参数查询：在执行时临时指定查询条件。SQL查询：通过直接输入SQL语句创建的查询,返回,四、查询视图,显示查询的不同内容的窗口。设计视图：用于设计（创建和编辑）查询。数据表视图：以表的形式显示查询执行的结果。SQL视图：用于查看或编辑查询时所用到的SQL语句,返回,第二节 创建选择查询,无条件选择查询有条件选择查询查询准则计算型选择查询,返回,.无条件选择查询,将表中某些字段的内容挑选出来。若查询字段只来源于一个表，则属于典型的投影运算，否则就是联接运算。主要有以下两种创建方法：（1）查询向导（2）设计视图,返回,（1）查询向导,例1：查询学生年龄，要求只显示学生编号、姓名和年龄，查询名 L01。启动简单查询向导：新建简单查询向导（或双击使用向导创建查询）；指定数据源(学生表)及字段（学生编号、姓名、年龄）；指定查询标题（L01）。举例：例2：查询学生的选课成绩，要求只显示学生编号、姓名、所选课程名和成绩,查询名L02。注意：若查询数据源为多个表，则多个表之间必须已建立关系。,返回,（2）设计视图,例3:查询学生年龄，显示学生编号、姓名和年龄，年龄升序 排序，查询名L03。打开查询设计视图：新建设计视图(或双击在设计视图中创建查询）；显示表中选择数据源；字段行指定查询字段（三种方法：拖动，双击，选择）,排序行指定排序方式；保存并指定查询名称。举例：例4：查询学生选课情况，显示学生编号、姓名、所选课程编号、课程名、课程类别和学分，查询名L04。注意：字段列表中的“”表示所有字段,返回,有条件选择查询,将表中符合条件的记录的某些字段挑选出来。只能用设计视图创建,需设置查询条件。查询条件在“条件”和“或”行中设置。,返回,有条件选择查询,举例：例5：查询女学生年龄信息，显示学生编号、姓名和年龄，年龄降序排序，查询名L05。注意：“显示”行作用指定所选字段是否在查询结果中显示。例6：查询选修“ACCESS”并及格的学生成绩信息，显示学生编号、姓名和成绩，成绩升序排序，查询名L06例7：查询18和23岁的学生信息，查询名L07。注：多个条件是逻辑“与”的关系时只在“条件”行设置，是“或”的关系时分别在“条件”和“或”两行设置。,返回,查询条件,用来从表中选取所需记录的限制条件，又称查询条件，一般是一个表达式，称为条件表达式。条件表达式是一个由运算符、数据和函数组合而成且能够计算出一个结果的式子。）数据的表示）Access运算符）Access内部函数,返回,）数据的表示,（1）数字型：直接表示。例：28、-3.287（2）文本值：用双引号（）或单引号（）括起。例：陈江川、男（3）日期型：用#括起来。例:#2006-10-25#例如：查询2008年9月1日入校的学生信息。（4）是否型：真为True/On/Yes，假为False/Off/No。例如：查询学生表里团员的信息。（5）空值：is Null 非空值：is Not Null例如：查询无职称的教师信息。（6）对象名和字段名：用方括号（）括起，且对象名与字段名用感叹号（!）分隔。例：年龄、学生!姓名,返回,）Access运算符,运算符是表达式的灵魂，系统主要提供了以下几类运算符：（1）算术运算符（2）文本运算符（连接运算符）（3）比较运算符（关系运算符）（4）逻辑运算符（5）特殊运算符,返回,3）运算符优先级,（1）算术运算符,+、-加减 Mod mod(10,3)=1 mod(4,2)=0 整除*、/乘除-（取负）23=8 幂指数,返回,练习,【1】计算表达式3*33/3的结果 结果：9【2】计算5+2*10 Mod 10 9/3+2 2的结果 结果：11,（2）文本运算符,即字符串运算符，将两个字符串连接成一个字符串又称字符串连接运算符，共有两个：+、&。+要求两个运算量必须是文本型&会将非文本型运算量转换为文本型。例：“计算机”+“原理”结果：“计算机原理”“年龄：”&23 结果：“年龄：23”,返回,示例,练习,（1）“计算机”+“原理”结果：“计算机原理”（2）“年龄：”&23 结果：“年龄：23”,（3）比较运算符,比较运算符是判断两个数据间的某种关系是否成立，又称关系运算符。、=、（不等于）关系表达式结果为是否型（逻辑型）。例8:查询1990年参加工作的教师信息,查询名L08例9:查询年龄不是18岁的学生信息，查询名为L09,返回,（4）逻辑运算符,运算量为是否型，常用的逻辑运算符：and（与）两侧都为真，结果为真 23 or 24 not（非）逻辑取反 逻辑表达式运算结果为是否型（逻辑型）。例9：查询20世纪90年代参加工作的教师信息，查询名L09。例10：查询不是2008年入校的男学生信息，查询名L10。,范围匹配：between and 例：between 20 and 30between#2007-1-1#and#2007-1-31#例11：查询20至22岁女学生信息列表匹配：in（数据列表）in(18,20,21,25)in(“教授”,”副教授”，“讲师”)例12：查询18、20、21和23岁的非团员信息，查询名L12,返回,（5）特殊运算符,模式匹配：like 模式字符串例：like“王*”like“*授”*多个字符，？一个字符例13：查询姓张的教师信息，查询名L13。例14：查询姓名中包含“小”字的教师信息，查询名L14,返回,函数是事先设计的一个程序，其功能是对数据进行某种处理，并返回处理的结果。其引用格式：函数名（参数列表）参数是处理的数据常用的系统内部函数有以下几类：（1）数值函数（2）字符函数（3）日期时间函数,返回,）Access内部函数,（1）数值函数,绝对值：Abs（数值）平方根：Sqr（数值）符号：Sgn（数值）正数返回1,返回0,负数返回-1取整：Int（数值）,返回,（2）字符函数,左子串截取：Left（字符串，数值）letf(“计算机班级”，3)结果：计算机例15：查询200803班学生信息，查询名L15。右子串截取：Right（字符串，数值）Right(“计算机班级”，2)结果：班级例16：查询姓名的末尾是“明”字教师信息，查询名L16。right(教师!名字,1)=“明”,返回,（2）字符函数,子串截取：Mid（字符串，数值1，数值）Mid(“能做吗?”,1,2)例17：查询姓名的第二个字是“小”的教师信息，查询名L17。Mid(教师!名字,2,1)=“小”求字符串长度：Len（字符串）例18：查询姓名只有两个字的教师信息，查询名L18。Len(教师!名字)=2,返回,第三节 创建交叉表查询,交叉表的功能 将来源于某个表中的字段进行分组，一组列在数据表的左侧，一组列在数据表的上部，然后在数据表行与列的交叉处显示表中某个字段的各种计算值。创建时，需指定三种字段：行标题字段（左侧）列标题字段（上部）总计项字段（行列交叉处）,返回,（1）交叉查询,返回,（1）交叉查询,创建方法 1.使用向导创建（注意：该方法只能创建单数据源的交叉表查询。）2.使用设计视图创建,返回,（1）交叉查询,数据库（进销存）例1.查询“sell”表中每一种商品每个季度的销量数据库（教学管理）练习：统计不同年龄男女学生人数的交叉表查询，查询名L38。,返回,（1）交叉查询,返回,练习：统计各个班级选修各门课程的人数，查询名L39。注意：行标题和列标题字段可以是计算字段。,第四节 创建参数查询,运行时临时指定查询条件的查询。该查询运行时将弹出一个对话框，提示输入查询参数（条件）。用 条件提示信息 替代查询条件,返回,（1）参数查询,例41：按性别查询学生基本信息，运行查询时提示“请输入性别：”，要求显示姓名，性别，年龄和入校日期，查询名L41。例42：按班级和课程名称查询学生成绩信息，提示“请输入班级：”和“请输入课程名称：”，显示姓名和成绩，查询名L42。注意：条件提示信息也可以是多个。,返回,第五节 创建操作查询,可以自动完成对表中的数据执行一个特定操作的查询。根据所执行操作的不同，可分为以下四类：生成表查询删除查询更新查询追加查询,返回,生成表查询,将查询结果生成一个新表。查询类型设置为生成表查询，指定新表名称及保存位置。,返回,生成表查询,例43：创建一个生成表查询，新表名称为“90分以上学生情况”，包括学生编号，姓名，性别，年龄和成绩字段，查询名为“L43”例44：根据教师表生成tOffice表（系别，联系电话），查询名分别为L44。,返回,删除查询,自动删除表中符合条件的记录；（1）设计视图，添加数据源；（2）设置查询类型为删除查询；（3）字段行添加构成删除准则的字段，并指定其“删除”行值为“Where”；（4）设置删除准则；（5）保存。,返回,更新查询,返回,根据某种规则自动对表中符合条件的数据进行更新。（1）设计视图，添加数据源；（2）设置查询类型为更新查询；（3）添加需要更新的字段，并在更新到行指定更新值；（4）添加更新准则字段，并在“准则”行设置其更新准则；（5）保存。,更新查询,返回,例47：创建一个将所有学生年龄增加一岁的查询，查询名L47。例48：创建一个将所有教师的联系电话前加“8”的查询，查询名L48。例49：创建一个将200804班改为200802班的查询，查询名为L49。注意：行标题和列标题字段可以是计算字段。,删除查询,返回,例45：创建一个删除经济系讲师信息的查询，查询名L45。例46：创建一个删除200804班不是团员的女学生信息的查询，查询名L46。,追加查询,返回,将查询结果添加到另一表尾部。（1）设计视图，添加数据源；（2）添加查询（所要追加的）字段；（3）设置查询条件（所追加记录的条件）（4）设置查询类型为追加查询,并指定追加到的表；（5）保存。,追加查询,返回,例50：创建将学生基本信息（学生编号、姓名、性别、年龄、入校日期）备份到t2表的查询，查询名L50。例51：创建将选数据结构课程并及格的学生信息备份到t1表（学生编号、姓、名、性别）的查询，查询名L51。注意：所要追加的字段可以来源于多个表或是计算字段，但必须是目标表中已经存在的字段。,（2）交叉查询,返回,例39：统计各个班级选修各门课程的人数，查询名L39。注意：行标题和列标题字段可以是计算字段。,计算型选择查询,能够将挑选出的数据按照某种规则进行统计和计算的查询。（1）总计查询（2）分组总计查询（3）添加计算字段,返回,（1）总计查询,对表中符合条件的某些字段的值进行计算与统计 总计项类型：总 计平均值最大值 最小值计 数分组表达式条件第一条记录最后一条记录例23：统计学生人数和平均年龄，查询名L23。,返回,（1）总计查询,例23：统计学生人数和平均年龄，查询名L23。重新指定字段标题方法：新标题:原标题,返回,（1）总计查询,例24：统计女学生人数、最大年龄、最小年龄和平均年龄，查询名L24。注意：只用于构成查询条件的字段，其总计方式为条件。,返回,（2）分组总计查询,根据表中某些字段的值对表中记录进行分类汇总，用于分组字段的“总计”方式设置成“分组”。例25：分别统计男女学生人数，查询名L25。例26：统计选修各门课程的人数和平均成绩，查询名L26。,返回,（2）分组总计查询,例26（拓展）：统计选修各门课程的男女学生的人数和平均成绩，查询名L26。注：分组字段可以多个，按从左到右的顺序排定优先级，先按第一分组字段分组，第一分组字段值相同的记录再按第二分组字段分组，依次类推。,返回,（2）分组总计查询,例27：统计学生的平均成绩，显示姓名、性别和平均成绩，查询名L27。,返回,分组总计查询,注意：只用于显示其值的字段，总计方式可为第一条记录或最后一条记录。例28：统计各同学修得的总学分，显示学生编号、姓名、总学分，查询名L28。,返回,分组总计查询,例29：查询两年后学生的年龄，显示学生编号、姓名和新年龄，查询名L29。,返回,分组总计查询,下一节例30：根据学生年龄，统计学生的出生年，显示姓名和出生年，查询名L30。,返回,分组总计查询,计算字段是表中并不存在的字段，它的值是通过对表中某些字段进行运算而得到的，故其实质是一个表达式。添加方法：在设计视图中的“字段”行中直接添加一个内容为表达式的新字段。,返回,分组总计查询,例31：查询教师工龄信息，要求显示教师编号、姓名和工龄，查询名L31。例32：统计各班学生人数和平均年龄，查询名L32。例33：统计学生最大年龄与最小年龄差值，显示标题为s_data，查询名L33。例34：分别统计各班男女同学的平均成绩，查询名L34。,返回,分组总计查询,当数据源为多个表或查询，而计算表达式中的某一字段在多个表中都存在时，引用时必须指定来源于哪个表。例35：分别统计男女学生最大年龄与最小年龄的差值，显示性别和s_data,查询名L35。表达式中可以包含统计函数，此时该字段的总计方式为表达式。,返回,分组总计查询,例36：查询学生基本信息，要求姓和名分开显示，显示学生编号、姓、名、性别、年龄，查询名L36。（注：不考虑复姓）例37：查询学生基本信息，要求包含学生编号、姓名、性别、年龄、入校日期和修得的总学分，但结果中只有两个字段为学生基本信息和总学分，查询名L37。,返回,交叉表的功能,将来源于某个表中的字段进行分组，一组列在数据表的左侧，一组列在数据表的上部，然后在数据表行与列的交叉处显示表中某个字段的各种计算值。创建时，需指定三种字段：行标题字段（左侧）列标题字段（上部）总计项字段（行列交叉处）,使用设计视图创建,例39：统计不同年龄男女学生人数的交叉表查询L39。设计视图选数据源；字段行添加行标题、列标题和总计项字段；添加交叉表和总计行（查询菜单交叉表查询或查询类型按钮交叉表查询)；总计行中行标题和列标题字段的总计方式为分组，总计项为一统计函数；交叉表行设置行标题、列标题和总计项字段的相应值为行标题、列标题、值；保存指定查询名。例40：统计各个班级选修各门课程的人数，查询名L40。注意：行标题和列标题字段可以是计算字段。,第四节 创建参数查询,运行时临时指定查询条件的查询。该查询运行时将弹出一个对话框，提示输入查询参数（条件）。用 条件提示信息 替代查询条件,第六节 创建SQL查询,通过直接输入SQL语句所创建的查询。SQL语句的语法规则相对较复杂，我们只需要掌握以下两个问题即可：SQL查询语句的一般语法SQL子查询,SQL动词,关键词1.Create语句 新建2.Alter语句 修改3.Drop语句 删除 4.Insert语句 插入5.Update语句 更新6.Delete语句 删除7.Select语句 查询,1 文本型 text 2数字型 number 3 日期型 datetime 4是否型Boolean 5货币型Currency 6 OLE型 ole 7备注型 Memo,字段类型文本 text(n)/char n为字段大小，char相当于text(255)备注：text/memo数字（字节）byte数字（整型）short数字（长整型）long/integer数字（单精度）single数字（双精度）double/float数字（小数）decimal(m,n)m为精度，n为数值范围日期/时间 datetime/date/time货币 currency/money自动编号 Autolncrement是/否 bit/Yesno/BooleanOLE image,1.Create语句,1定义基本表Create Table的一般格式为：CREATE TABLE（,）说明：所要定义的基本表的名字。：表中所包含的属性列的名字：指明列的数据类型,创建表,例1：创建一张表，表名称为Team，包含两列，第一列名称为No，类型为数字型，第二列名称为Name，类型为文本型,字段大小为20。解答：CREATE TABLE Team(Nonumber,Nametext(20),创建表,练习：创建一张表，表名称为Test3，包含一列，名称为Column1，类型为日期时间类型。,创建表,练习：创建一张表，表名称为Test1，包含三列，第一列名称为C1，类型为文本型，字段大小为10，第二列名称为C2，类型为备注型，第三列名称为C3，类型为时间型。解答：CREATE TABLE Test1(C1 text(10),C2memo,C3 datetime）,（1）交叉查询,返回,2.删除表,删除表的语句格式为:DROP TABLE,例1：删除test表的语句为:DROP TABLE test例2：删除学生表的语句为:DROP TABLE 学生,3.修改表结构,ALTER TABLE ALTER COLUMN-修改列 ADD-添加列 DROP COLUMN-删除列,修改表结构,例1.向学生表添加新列C4，类型text(3)。ALTER TABLE 学生 ADD COLUMN C4 text(3)例2将新添加的C4的类型改为text(6)。ALTER TABLE 学生 ALTER COLUMN C4 text(6)例3删除学生表的C4列 ALTER TABLE 学生 DROP COLUMN C4,创建一个“雇员”表，包括雇员号、姓名、性别、出生入期、部门、备注字段。CREATE TABLE 雇员(雇员号 char(8),姓名 CHAR(4)Not Null,性别 CHAR(1),出生日期 DATE,部门 CHAR(20),备注 MEMO),4.Insert 语句,INSERT 语句实现数据的插入功能，可以将一条新记录插入到指定表中。其语句格式为：INSERT INTO 表名（列名表）VALUES（值列表）其中：中的列名必须是表定义中有的列名，值列表中的值可以是常量也可以是NULL值，各值之间用逗号分隔。注意：文本数据应用双引号/单引号括起来，日期数据应用“#”号括起来。,例55：把以下新记录插入到“雇员”表中。（“0001”,“张磊”,“男”,#1960-1-1#,“办公室”,“无”）解答1：INSERT INTO 雇员(雇员号,姓名,性别,出生入期,部门,备注)VALUES(0001,张磊,男,#1960-1-1#,办公室,无)解答2：INSERT INTO 雇员 VALUES(0001,张磊,男,#1960-1-1#,办公室,无),例56：将一条新记录插入到“雇员”表中，其中“雇员号”为“0002”，“姓名”为“王宏”，“性别”为“男”。解答：INSERT INTO 雇员(雇员号,姓名,性别)VALUES(0002,王宏,男),思考：下面语句能否执行成功？INSERT INTO 雇员(雇员号,姓名,性别)VALUES(0002,男“,王宏)INSERT INTO 雇员(雇员号,姓名,出生日期)VALUES(0002,男“,王宏),例：在雇员表中插入一新记录，部门、备注暂缺。解答1、INSERT INTO 雇员(雇员号,姓名,性别,出生日期)VALUES(001,李明,男,#1980-3-4#)解答2、INSERT INTO 雇员 VALUES(001,李明,男,#1980-3-4#,NULL，NULL),Insert语句,小结：1、Insert语句注意字段名需要跟插入的值对应起来，并且数据类型也要一致。2、如果数据恰好与表的列数以及各列的顺序保持一致，“列名列表”可以省略.3、值列表中的值可以是常量也可以是NULL值,5.Update 语句,UPDATE语句实现数据的更新功能，能够对指定表所有记录或满足条件的记录进行更新操作。该语句的格式为：UPDATE SET=,=WHERE；,5.Update 语句,例57：将“雇员”表张磊的出生日期改为“1960-1-11”UPDATE 雇员 SET 出生日期=#1960-1-11#WHERE 姓名=张磊,练习：将李明的性别改为女,雇员号改为0003update 雇员 set 性别=“女”,雇员号=“0003”where 姓名=“李明”,6.Delete 语句,DELETE 语句实现数据的删除功能，能够对指定表所有记录或满足条件的记录进行删除操作。该语句的格式为：DELETE FROM WHERE；,例58：将“雇员”表中雇员号为“0002”的记录删除 DELETE FROM 雇员 WHERE 雇员号=0002“例59：将“雇员”表中性别为“女”的记录删除。DELETE FROM 雇员 WHERE 性别=“女“例60：删除雇员表（无条件 删除）DELETE FROM 雇员,7.Select语句,SQL语言只提供了一个查询语句：SELECT语句功能是根据指定的条件对表中数据进行查找或计算。其一般语法规则：SELECT ALL|DISTINCT*|字段列表FROM 表名列表WHERE 条件表达式GROUP BY 分组字段列表ORDER BY 排序字段列表ASC|DESC,（1）字段列表:欲查询的字段,包含计算字段。*:表示所有字段（2）ALL:所有符合条件的记录，包括重复记录，DISTINCT:去掉重复记录（3）FROM子句：指定查询数据源（4）WHERE子句：设置查询准则（5）GROUP BY子句：指定分组依据（6）ORDER BY子句:指定排序字段及方式（ASC为升序（默认），DESC为降序）（7）各表名及字段之间必须用逗号（,）分隔（8）FROM子句必选，其它子句可选（9）SELECT必须位于语句开头，其他子句次序无关紧要。,简单查询,例59：查找并显示“教师”表中所有的字段（所有字段）Select*from 教师例60：查找并显示“教师”表中“姓名”、“性别”、“工作时间”和“系别”4个字段。（部分字段）Select 姓名,性别,工作时间,系别 from 教师,条件查询,条件关键字 where例61：查找1992年以后（包含）参加工作的男教师，并显示“姓名”，“性别”，“学历”，“职称”，“系别”和“联系电话”（条件查询）Select 姓名,性别,学历,职称,系别,联系电话 from 教师 where year(工作时间)=1992,计算字段,例62：计算教师人数，并将计算字段命名为“教师人数”，显示教师人数。Select count(老师编号)From 教师 重新指定字段标题方法：原标题As新标题Select count(老师编号)as 教师人数 From 教师,练习：查询学生的平均年龄，显示平均年龄字段，保存查询名为“nl”,分组查询,分组关键字 group by例62：计算各类职称的教师人数，并将计算字段命名为“各类职称人数”，显示职称和各类职称人数。Select count(老师编号)as 各类职称人数,职称 From 教师 group by 职称,练习：计算男女的人数，显示性别、人数字段，查询名为rs.Select count(学生编号)as 人数,性别 from 学生 group by 性别,升序、降序,Order by 升序 ASC降序 desc例63：计算男女学生的平均年龄，并按平均年龄降序显示。例64：计算每名学生的平均成绩，并按平均成绩降序显示。,多表查询,表与表之间通过相同字段建立关系。连接 关键字 where、inner Join on 例：where(学生.学生编号=选课成绩.学生编号)学生 inner join 选课成绩 on 学生.学生编号=选课成绩.学生编号,多表查询,例：查询每名学生所选的课程名，显示学生编号，课程名称字段。select 学生编号,课程名称 from 选课成绩,选课where(选课成绩.课程编号=选课.课程编号),多表查询,例：统计每门课程所选的人数。select 课程名称,count(学生编号)as 课程人数 from 选课成绩,选课 where(选课成绩.课程编号=选课.课程编号)group by 课程名称例64：查找学生的选课成绩，并显示“学生编号”，“姓名”，“课程名称”和“成绩”。,计算字段,练习：统计每名学生的平均成绩,显示姓名，学生编号，平均成绩字段。SELECT 学生.姓名,Avg(成绩)AS 平均,学生.学生编号FROM 学生,选课成绩 where(学生.学生编号=选课成绩.学生编号)GROUP BY 学生.姓名,学生.学生编号,计算字段,例64：查找学生的选课成绩，并显示“学生编号”，“姓名”，“课程名称”和“成绩”。SELECT 姓名,学生.学生编号,课程名称,成绩 FROM 学生,选课成绩,选课 where(学生.学生编号=选课成绩.学生编号)and(选课成绩.课程编号=选课.课程编号),计算字段,Select 学生.学生编号,学生.姓名,sum(学分)as 总学分 from 学生 inner join(选课 inner join 选课成绩 on 选课成绩.课程编号=选课.课程编号)on 学生.学生编号=选课成绩.学生编 group by 学生.学生编号,学生.姓名,4.创建SQL子查询,所谓子查询就是包含在另一个Select语句中的Select语句。（查询嵌套）,计算字段,计算字段,计算字段,