第3章-查询(2010版).

第三章查询3.1查询一、查询的特点查询是将表中满足条件的记录生成一个临时表(动态集)Access允许用户在前台（选择查询设计视图窗口）构造查询，系统自动在后台（SQL视图窗口）生成对应的SQL语句。查询对象仅仅保存查询的结构——查询所涉及到的表名和字段名、排序准则、筛选条件等。运行查询所生成的动态集具有很大的灵活性，适合作为报表和窗体的数据源。建立查询之前,每张表要建主码及表与表之间的关系.二、查询的类型选择查询参数查询交叉表查询动作查询(操作查询)SQL查询3.2选择查询是最常见、最简单的查询类型，它从一个或多个表中检索数据，并以数据表形式显示结果。使用向导使用设计视图例1:从教师表中只查询教师的姓名和职称等。下面是对应的SQL语句：select姓名,职称from教师表1.查询设计视图的结构：上半部分---表/查询输入区显示查询要使用的表或其它查询；下半部分---设计网格确定动态集所拥有的字段和筛选条件.【字段】行：设置查询所涉及到的字段;【表】行：指明字段所归属的表;【排序】行：设置查询的排序准则;【显示】行：确定相关字段是否在动态集中出现。当复选框选中时，该字段将在动态集中出现;【条件】行：设置查询的筛选条件;【或】行：对同一字段的可选条件使用“或”2.运行查询单击工具栏上的【运行】按钮!单击工具栏上的【数据表视图】按钮在【数据库】窗口，①双击要运行的查询②或选中该查询,单击【打开】按钮3.修改查询撤消字段---按delete键插入字段---拖动或双击该字段移动字段---拖动命名字段---右击字段名→属性→列标题3.3查询条件一、输入查询条件查询条件：即条件表达式；表达式：是算术或逻辑运算符、常数、函数和字段名称、控件和属性的任意组合，计算结果为单个值。二、用逻辑运算符组合条件And:在同一行的不同单元格中设置条件，筛选满足所有条件的记录Or:在不同行中设置条件，筛选满足任何一个条件的记录Not:可以设置字段的不匹配值注意：输入逻辑运算符时，字母大小写均可例2：查询学历是“硕士”或“本科”的教师记录学历=“硕士”or学历=“本科”学历in(”硕士”,”本科”)同：例3：查询学历是“硕士”同时职称是“副教授”的教师记录学历=”硕士”and职称=”副教授”学历=”硕士”or职称=”副教授”例4：查询学历是“硕士”或职称是“副教授”的教师记录(学历=“硕士”and职称=“副教授”)or(学历=“本科”and职称=“教授”)例5：查询学历是“硕士”同时职称是“副教授”的教师记录，或者学历是“本科”同时职称是“教授”的记录三、用关系运算符组合条件关系运算符：,,,=,=,=例6:查“高级语言程序设计”课程不及格学生的学号和姓名学生学生选课课程赵丽3张三2王明1姓名学号50c3390c1285c2170c11成绩课程编号学号计算机c4VBc3高级语言程序设计c2英语c1课程名称课程编号80c33赵丽390c12赵丽385c21赵丽370c11赵丽380c33张三290c12张三285c21张三270c11张三280c33王明190c12王明185c21王明170c11王明1成绩课程编号学生选课.学号姓名学生.学号学生×学生选课学生.学号姓名学生选课.课程编号成绩1王明c1701王明c2852张三c1903赵丽c380学生∞学生选课SELECT学生.学号,学生.姓名FROM学生,学生选课,课程WHERE学生.学号=学生选课.学号AND课程.课程编号=学生选课.课程编号AND课程名称=高级语言程序设计AND成绩60;SQL语句如下：（学生∞学生选课）SELECT学生.学号,学生.姓名,学生选课.课程编号,成绩FROM学生,学生选课WHERE学生.学号=学生选课.学号例6的SQL语句如下：例7：查1985-1-1到1986-12-31出生的学生的学号,姓名,出生年月SELECT学号,姓名,出生年月FROM学生WHERE出生年月=#1985-1-1#And出生年月=#1986-12-31#;或：WHERE出生年月between#1985-1-1#And#1986-12-31#;四、用Between运算符组合条件例如：查询成绩在80～90分之间的记录。五、用In运算符组合条件例如,查询学历是”硕士”或者学历是”本科”的记录六、用Like运算符组合条件Like运算符用于查找字段的部分值格式：Like字符模式“?”----任何一个字符“*”----可匹配零或多个字符“#”----可匹配一个数字“[]”---方括号描述一个范围注:参见如下“Access中通配符参考”*(星号)匹配任意数目的字符，包括空格。可在字符串中的任意位置使用星号wh*可找到what、white和why，但不会找到watch或awhile。s*d可找到“sad”和“started”?匹配任意单个字母字符b?ll可找到“ball”、“bell”和“bill”[]匹配方括号内的任意单个字符，包括其他通配符。需要查找通配符时可使用方括号b[ae]ll可找到“ball”和“bell”，但不会找到“bill”!匹配不包含在方括号内的任何字符b[!ae]ll可找到“bill”和“bull”，但不会找到“ball”或“bell”-匹配某一字符范围内的任意一个字符。必须按升序指定范围(a到z，而不是z到a)b[a-c]d可找到“bad”、“bbd”和“bcd”#匹配任何单个数字字符1#3可找到“103”、“113”和“123”3.4查询中的计算如果用户关心的是每一个学生的总成绩、平均成绩等汇总结果，要在查询中执行计算。预定义计算自定义计算一、预定义计算对查询中的分组记录或全部记录进行“总计”计算，比如：求总和、平均值、计数、最小值、最大值、标准偏差或方差，Access通过聚合函数来完成这些计算。在查询设计视图中，单击工具栏上的【总计】按钮，则在设计网格中增加【总计】行。例8：统计全校教师的人数SELECTCount(姓名)AS姓名之计数FROM教师;单击“总计”按钮例9：统计教师中男、女各多少人？SELECT性别,Count(姓名)AS姓名之计数FROM教师GROUPBY性别;例10:统计教师不同职称的人数是多少？SELECT职称,Count(姓名)AS姓名之计数FROM教师GROUPBY职称;例11：统计“高级语言程序设计”课程的平均分、最高分、最低分SELECT课程名称,Avg(成绩)AS平均值,Max(成绩)AS最大值,Min(成绩)AS最小值FROM课程,学生选课where课程.课程编号=学生选课.课程编号and课程名称=高级语言程序设计GROUPBY课程名称嵌套查询SELECT高级语言程序设计AS课程名称,Avg(成绩)AS平均值,Max(成绩)AS最大值,Min(成绩)AS最小值FROM学生选课WHERE((学生选课.课程编号)In(select课程编号from课程where课程名称=高级语言程序设计));如：统计“ACCESS8”班的“计算机基础与操作（下）”课程的平均成绩？如：统计“ACCESS8”班“女生”的“计算机基础与操作（下）”课程的平均成绩？二、自定义计算1、在设计网格中创建新的计算字段2、创建计算字段的方法：将表达式输入到查询设计网格中的空“字段”单元格中，如表达式包含字段名，要用括号将字段名称括起来。列标题P89例3-10利用“选课成绩表”计算班级的平均成绩。例12:按”性别”统计不同”职称”的人数是多少？3.5交叉表查询选择查询SELECT性别,职称,Count(职称)FROM教师GROUPBY性别,职称;性别职称职称之计数女副教授3女讲师2女教授3女助教1男副教授5男讲师1男教授1选择查询的结果：将表中的某个字段进行分组并分别显示在数据表的左侧和上部，然后在数据表的行列交叉处显示其各种计算值。(类似Excel中的“数据透视表”)⑴行标题:如“性别”⑵列标题:如“职称”⑶值：位于行列交叉位置上的内容，即要指定一个统计项，如Sum或Avg或Count等交叉表查询需指定三种字段：例：交叉表反映每个学生各科成绩？交叉表查询结构3.6参数查询运行查询时，再按提示输入筛选条件。在【条件】行或【或】行中输入一个放在方括号中的短语。如：[请输入您要查询的学院名称：]between[输入起始日期]and[输入终止日期]3.7操作查询(ActionQuery)操作查询:是在选择查询的基础上进行的数据更新特点：一个操作可以更改许多记录类型：◆生成表查询◆追加查询◆更新查询◆删除查询一、生成表查询将从表或查询中筛选出来的记录集生成一个新表。目的是便于永久保存。注意：●新表将从源表中继承字段名称、数据类型以及【字段大小】属性,不继承其它的字段属性以及表的主键。●一定要运行后，方能生成新表。例13：将2003-2004年度第二学期“不及格”的学生内容生成到一新表。二、追加查询将从表或查询中筛选出来的记录添加到另一个表中去。该表是已经存在的表，还可以是另外一个数据库的。三、更新查询在指定的表中对筛选出来的记录进行更新操作。可一次更新许多记录。如:每个人的基本工资都上调10%；例14:将“高级语言程序设计”课程成绩小于90分的增加2分例15:将姓名为“宋思思”的学生姓名改为“宋丝丝”四、建立删除查询从表中删除筛选出来的记录将永久和不可逆地从表中删除记录删除整条记录，而不是指定字段中的数据3.8SQL查询SQL是结构化查询语言（StructuredQueryLanguage）的缩写，其功能包括数据查询、数据操纵、数据定义和数据控制四个部分。9个命令动词：数据定义：CREATE(创建)、DROP(删除)、ALTER(修改)数据操纵：INSERT(插入)、UPDATE(更新)、DELETE(删除)数据查询：SELECT(选择)数据控制：GRANT(授权)、REVOKE(回收)说明：在“设计”视图中创建查询时，系统在后台构造等效的SQL语句。有的查询功能只能通过SQL语句来实现。一、SELECT语句1、格式：SELECT[ALL/DISTINCT]*/选择列表FROM表名[WHERE〈条件表达式〉][GROUPBY列名1[HAVING条件表达式]][ORDERBY列名2[ASC/DESC]];SELECT语句的执行过程:根据WHERE子句的检索条件，从FROM子句指定的基本表中选取满足条件的元组(记录)，再按照SELECT子句中指定的列，投影得到结果表。如果有GROUP子句，则将查询结果按照列名1相同的值进行分组。如果GROUP子句后有HAVING短语，则只输出满足HAVING条件的元组。如果有ORDER子句，查询结果还要按照列名2的值进行排序在何处编写SELECT语句？双击“在设计视图中创建查询”→关闭“显示表”对话框→单击“SQL视图”命令如何运行SQL查询:“查询”菜单→“运行!”命令例16查询学生的学号、姓名和性别。SELECTALL学号,姓名,性别FROM学生;例17查询学生的全部信息。SELECT*FROM学生;例18查询教师授课详细情况。SELECT教师.教师编号,姓名,教师授课.课程编号,教师授课.班级ID,授课时间,授课地点from教师,教师授课where教师.教师编号=教师授课.教师编号;2、多重条件查询WHERE子句可指定多查询条件,常用的运算符:运算符含义=,,,=,=,!=比较大小多重条件AND,ORBETWEENAND确定范围IN确定集合LIKE字符匹配ISNULL空值例19:查询所有职称是“讲师”的教师的编号和姓名SELECT教师编号,姓名FROM教师WHERE职称=“讲师”例20:查询1970年出生的学生的基本情况SELECT*FROM学生WHERE出生年月=#1970-01-01#and出生年月=#1970-12-31#或Whereyear(出生年月)=1970或where出生年月between#1970-01-01#and#1970-1