第4章_创建和使用查询

1、北京理工大学：姜增如北京理工大学：姜增如查询的定义和类型查询的定义和类型选择查询选择查询创建交叉表查询创建交叉表查询创建参数查询创建参数查询创建操作查询创建操作查询在查询中进行计算在查询中进行计算创建创建SQL查询查询主讲内容主讲内容 查询就是对数据库中的数据进行查找。查询就是对数据库中的数据进行查找。查询可以对数据库中一个表或多个表的数据进行浏览、查询可以对数据库中一个表或多个表的数据进行浏览、筛选、排序、检索、统计和加工等操作。筛选、排序、检索、统计和加工等操作。建立一个查询后，可以将它看作是一个临时表，称为建立一个查询后，可以将它看作是一个临时表，称为动态的数据集。动态的数据集。 查询的

2、数据来源是表或其他查询。每次使用查询查询的数据来源是表或其他查询。每次使用查询时，都是根据查询条件从数据源中创建动态的记录集时，都是根据查询条件从数据源中创建动态的记录集合，可以节约存储空间。合，可以节约存储空间。一、查询的定义和类型一、查询的定义和类型1 1）根据用户的选择生成动态的数据集。对数据进行统）根据用户的选择生成动态的数据集。对数据进行统计、排序、计算和汇总。计、排序、计算和汇总。2 2）设置查询参数，形成交互式的查询方式。）设置查询参数，形成交互式的查询方式。3 3）使用交叉表查询，进行分组汇总。）使用交叉表查询，进行分组汇总。4 4）使用操作查询对数据表进行追加、更新、删除操作

3、。）使用操作查询对数据表进行追加、更新、删除操作。5 5）查询可作为其他查询、窗体和报表的数据源。）查询可作为其他查询、窗体和报表的数据源。查询类型主要有选择查询、交叉表查询、参数查询、查询类型主要有选择查询、交叉表查询、参数查询、操作查询和操作查询和SQL查询。查询。建立查询主要有三种方法：建立查询主要有三种方法： (1) 利用查询向导利用查询向导 (2) 利用查询设计视图利用查询设计视图 (3) 使用使用SQL查询语句查询语句由查询向导和使用查询设计视图建立的查询实质是由查询向导和使用查询设计视图建立的查询实质是用用SQL语句编写的查询命令。语句编写的查询命令。 4、查询设计视图类型、查询

4、设计视图类型1）选择）选择【创建创建】下的下的“查询设计查询设计”选择表，如图选择表，如图：2）建立数据表）建立数据表视图视图在查询设计视图中，添加表，选择需要查询的字段，在查询设计视图中，添加表，选择需要查询的字段，再从再从“视图视图”菜单下选择菜单下选择“数据表视图数据表视图”，如图：，如图：3）SQL视图视图在查询设计视图中，可直接从在查询设计视图中，可直接从“视图视图”菜单下选择菜单下选择“SQL视图视图”，键入，键入sql语句即可，如图：语句即可，如图： 选择查询是最常见的查询类型，它可以从一个选择查询是最常见的查询类型，它可以从一个表或多个表中选择记录，对记录进行分组，还可以表或多

5、个表中选择记录，对记录进行分组，还可以对记录进行汇总、计数、求平均值以及进行其他类对记录进行汇总、计数、求平均值以及进行其他类型的总和计算。选择查询产生的结果是一组数据记型的总和计算。选择查询产生的结果是一组数据记录，这组数据记录称为录，这组数据记录称为“动态集动态集”。选择查询的目。选择查询的目的是用来挑选表中的内容，并组合成动态集合。的是用来挑选表中的内容，并组合成动态集合。1、 使用向导创建选择查询单击单击 【创建创建】下的下的“查询向导查询向导”，选择表或查询后，选择表或查询后，再选择查询的字段即可，如图：再选择查询的字段即可，如图： 2、通过设计视图创建选择查询 打开的查询设计视图中

6、，选择表和字段，如图：1）选择数据源）选择数据源 2、建立查询条件条件的作用是对所选择的记录作进一步的限定。例如查询性别为“女”，“数据库应用技术”课程成绩80分以上（含80）的学生，在查询设计的条件下键入查询条件即可，如图：多个条件可以使用“and”、“or”、“not”连接，例如，查询student表中1996年1月1日到12月31日出生的女生，从【创建】下的“查询设计”视图中加入条件 “=#1996-1-1# And 80 的数据记录生成一个新表。单击单击“查查询设计询设计”再选择再选择 “生成表查询生成表查询”按钮如图：按钮如图：单击菜单上单击菜单上 “查询查询”命令的命令的“运行运行

7、”按钮，可按钮，可弹出一个提示框，如图：弹出一个提示框，如图：此时，在数据库表中菜可看到一个此时，在数据库表中菜可看到一个 “Access数数据库应用技术据库应用技术80分以上名单分以上名单”表。表。单击该表，预览结果如图：2、更新查询1)选择”设计视图”创建查询2)选择数据源3)添加更新字段和准则字段4)添加更新条件和目标5)单击工具栏的运行按钮 若在弹出的对话框内单击”是”按钮,更新后改变原来表内容.例如将成绩表例如将成绩表score中中80分到分到90分的评为分的评为“良良好好”。是添加查询条件，单击是添加查询条件，单击“更新查询更新查询”按钮，再单击按钮，再单击“运行运行”按钮如图：按

8、钮如图：此时，在此时，在score表表中80分分-90分的等级添加了分的等级添加了“良好良好”。 通过追加查询将某个表中符合条件的记录添加到通过追加查询将某个表中符合条件的记录添加到另一个表中。另一个表中。注意：在追加查询与被追加记录的表时，只有匹配的注意：在追加查询与被追加记录的表时，只有匹配的字段才被追加。一般，追加的表机构应与被追加的表字段才被追加。一般，追加的表机构应与被追加的表机构基本一致。机构基本一致。创建追加查询与创建追加查询与创建追加查询与创建追加查询与将成绩表score1表追加到score2表后面此时，在此时，在score2表表中包含了score1内容。 删除查询就是利用该查

9、询一次删除符合条件的一删除查询就是利用该查询一次删除符合条件的一批记录。删除查询可以从一个表中删除记录，也可以批记录。删除查询可以从一个表中删除记录，也可以从多个相互关联的表中删除记录。若要从多个表中删从多个相互关联的表中删除记录。若要从多个表中删除相关记录，必须已经建立了相关表之间的关系；并除相关记录，必须已经建立了相关表之间的关系；并且建立关系对话框中分别选择且建立关系对话框中分别选择“实施参照完整性实施参照完整性”和和“级联删除相关记录级联删除相关记录”复选框；这样选择窗口中关联复选框；这样选择窗口中关联的表中删除记录。的表中删除记录。 说明：当使选中说明：当使选中“级联删除相关记录级联

10、删除相关记录”复选框来删复选框来删除窗体或数据表中的记录时，除窗体或数据表中的记录时，Microsoft Access Microsoft Access 将发将发出出相关记录也将删除的警告消息。但是，当使用相关记录也将删除的警告消息。但是，当使用删除查询来删除记录时，删除查询来删除记录时， Access 将自动删除将自动删除相关表中的记录，而不显示任何警告消息。步相关表中的记录，而不显示任何警告消息。步骤为骤为:1)在查询视图中选择表在查询视图中选择表2)单击工具栏单击工具栏加入删除条件，单击 “删除”按钮，再选择“运行”，如图：此时，在此时，在score表表中60分以下的记录删除了。分以下的

11、记录删除了。 在实际应用中，常常需要对记录或字段进行汇在实际应用中，常常需要对记录或字段进行汇总统计，总统计，AccessAccess查询提供了利用函数建立总计查询的查询提供了利用函数建立总计查询的方式，总计查询可以对查询中的某列进行总和方式，总计查询可以对查询中的某列进行总和（SumSum）、平均（）、平均（AvgAvg）、计数（）、计数（CountCount）、最小值）、最小值（MinMin）和最大值（）和最大值（MaxMax）等计算。）等计算。例如：汇总服装表例如：汇总服装表CLOTHING “连衣裙连衣裙”的平的平均进价是多少钱，如图：均进价是多少钱，如图：1、汇总、汇总创建总计查询是

12、通过使用查询创建总计查询是通过使用查询“设计网格设计网格”中的中的“总总计计”行上的总计项来实现的。总计项共有行上的总计项来实现的。总计项共有1212个选项，个选项，1212个选项可分为分组个选项可分为分组 (Group By)(Group By)、合计函数、表达、合计函数、表达式（式（ExpressionExpression）和限制条件（）和限制条件（WhereWhere）4 4类。类。SumSum：计算组中该字段所有值的和；：计算组中该字段所有值的和；AvgAvg：计算组中该字段的算术平均值最小值：计算组中该字段的算术平均值最小值MinMin、MaxMax：返回组中字段的最小值和最大值：返

13、回组中字段的最小值和最大值CountCount：返回行的合计；：返回行的合计；StDevStDev：计算组中字段所有值的统计标准差；：计算组中字段所有值的统计标准差；VarVar：计算组中字段所有值的统计方差：计算组中字段所有值的统计方差FirstFirst：返回该字段的第一个值：返回该字段的第一个值LastLast：返回该字段的最后一个值。：返回该字段的最后一个值。 分组（分组（Group ByGroup By） 对记录分组。例如按性别将学生对记录分组。例如按性别将学生分成分成2 2组组 表达式（表达式（ExpressionExpression）字段框内设置的是表达式，它在）字段框内设置的是

14、表达式，它在来源表中不存在，字段值则由表达式计算得到。来源表中不存在，字段值则由表达式计算得到。 可以在条件（可以在条件（WhereWhere）字段的条件框内设置条件表达式。）字段的条件框内设置条件表达式。 2、创建计算字段查询v计算字段的值由表达式计算而得，不保存在表中v若表达式中引用的字段或值发生了变化，必须重新执行查询 利用出生日期计算利用出生日期计算年龄年龄: Year(Now()-Year(出生日期出生日期)根据根据“出生日期出生日期”计算年龄计算年龄说明：时间函数的使用：说明：时间函数的使用：vNow()-表示取当前的日期和时间表示取当前的日期和时间vdate()-表示取当前的日期

15、表示取当前的日期vyear出生年月出生年月-表示取年表示取年vmonth出生年月出生年月-表示取月表示取月vday出生年月出生年月-表示取日表示取日已知出生已知出生日期计算年龄日期计算年龄:year(Now()-year(出生日期出生日期) 或或Year(date()-year(出生日期出生日期) 1 1、SQLSQL基本规则基本规则SQL SQL （Structure Query Language Structure Query Language ，结构化查询，结构化查询语言语言 ）是在数据库系统中应用广泛的数据库查询语）是在数据库系统中应用广泛的数据库查询语言，它包括了数据定义、查询、操纵

16、和控制言，它包括了数据定义、查询、操纵和控制4 4种功能。种功能。 SQL SQL 查询是使用查询是使用SQLSQL语句创建的查询。可以用结构语句创建的查询。可以用结构化查询语言（化查询语言（Structure Query Language Structure Query Language ，SQL) SQL) 来查询、更新和管理来查询、更新和管理 Microsoft Access Microsoft Access 这样的关系这样的关系数据库。数据库。七、使用七、使用SQL命令查询命令查询 vSQL意为结构化查询语言，可用于定义、查询、更意为结构化查询语言，可用于定义、查询、更新、管理关系型数

17、据库系统。新、管理关系型数据库系统。vSQL是一种非过程语言，易学易用，语句由近似自是一种非过程语言，易学易用，语句由近似自然语言的英语单词组成然语言的英语单词组成 例例1 显示显示“student”表中所有男生的学号、姓名、表中所有男生的学号、姓名、入学分数入学分数 Select 学号学号,姓名姓名,入学分数入学分数 From student Where 性别性别=男男 vSQL语言不涉及数据库内部细节，通用性好。语言不涉及数据库内部细节，通用性好。v不能设计出与用户交互的图形界面，需用不能设计出与用户交互的图形界面，需用VB、VC等程序语言作为客户端开发工具，同时在程序中嵌等程序语言作为客

18、户端开发工具，同时在程序中嵌入入SQL命令。命令。v SQL命令的所有子句既可以写在同一行上，也可以命令的所有子句既可以写在同一行上，也可以分行书写；大小写字母的含义相同；命令用分号分行书写；大小写字母的含义相同；命令用分号“;”结束结束(也可以不写也可以不写)。 vSQL的查询语句格式：的查询语句格式：SELECT 字段列表字段列表 INTO 新表新表 FROM 记录源记录源 WHERE GROUP BY HAVING ORDER BY 字段列表字段列表ASC|DESC 中的内容为可选项中的内容为可选项DISTINCT:表示输出无重复记录表示输出无重复记录AS: 后表示要输出一个新的字段名后

19、表示要输出一个新的字段名选项：字段名，表达式或函数选项：字段名，表达式或函数FROM结果排序结果排序 ORDER BY ASC|DESC ,ASC|DESC WHEREWHERE是条件语句的关键字，是可选项是条件语句的关键字，是可选项格式为：格式为：WHERE WHERE AND/ORAND/ORAND/ORAND/OR.升序升序降序降序创建创建SQLSQL查询的操作步骤如下：查询的操作步骤如下：1 1）在数据库窗口中的）在数据库窗口中的“查询查询”对象下，双击对象下，双击“在设计视图中创建查询在设计视图中创建查询”项，并关闭弹出的项，并关闭弹出的“显示表显示表”对话框；对话框；2 2）选择）

20、选择“查询查询”菜单中的菜单中的“SQLSQL特定查询特定查询”命令；命令；3 3）在弹出的）在弹出的“SQLSQL查询查询”编辑器框中输入编辑器框中输入SQLSQL语语句。句。 查询视图的的实质是查询视图的的实质是SQL命令，在查询设计命令，在查询设计中，单击中，单击SQL视图，即可输入查询语句，如视图，即可输入查询语句，如图。图。选取记录源的全部字段或部分字段选取记录源的全部字段或部分字段例例1 输出输出“student”表的全部字段表的全部字段 *表示记录源的全部字段表示记录源的全部字段 3、基于单记录源的查询 SELECT * FROM student例例2以以 “student”表为

21、记录源，显示部分字段表为记录源，显示部分字段 SELECT 学号学号,姓名姓名,性别性别 FROM student说明：说明：（1）字段名之间的逗号）字段名之间的逗号“,”必须是英文字符必须是英文字符（2）如果重命名数据库源，则本查询中引用的记录）如果重命名数据库源，则本查询中引用的记录源名字将自动更新。源名字将自动更新。（3）作为记录源的）作为记录源的“student”表不能删除表不能删除（4）当真正的记录源）当真正的记录源“student”表中的数据更新表中的数据更新时，查询的执行结果也自动更新。时，查询的执行结果也自动更新。用用TOP显示前面的若干条记录显示前面的若干条记录 语法格式：语

22、法格式：TOP n （n为指定记录数）为指定记录数） 例例5 显示显示“student”表中的前表中的前4条记录条记录 SELECT TOP 4 * FROM studdent 例例3 将将student表按照学号降序表按照学号降序排列。排列。Select * From student order by 学号学号用用DISTINCT消除重复记录消除重复记录 例例4 SELECT DISTINCT 性别性别 FROM student则：相同的字段只出现一个则：相同的字段只出现一个 用用WHERE子句对记录进行选择子句对记录进行选择 v语法格式：语法格式： WHERE 例例6 找年龄不低于找年龄不

23、低于20岁的学生，并显示部分字段岁的学生，并显示部分字段 SELECT 姓名姓名,性别性别,年龄年龄 FROM student WHERE year(Now()-year(出生日期）出生日期）=20 v可用逻辑运算符形成逻辑表达式可用逻辑运算符形成逻辑表达式,逻辑运算符的优先逻辑运算符的优先级依次为：级依次为：NOTANDOR 例例7 查找查找student 表中表中1996年前出生的女生信息年前出生的女生信息例例8查找查找student表中表中1996年前出生的女生信息或是党员信息年前出生的女生信息或是党员信息 SELECT 姓名姓名,性别性别,年龄年龄 FROM studentWHERE

24、year(出生年月出生年月)1996 AND 性别性别=女女 SELECT 姓名姓名,性别性别,入学分数入学分数,研究方向研究方向 FROM student WHERE WHERE year(出生年月出生年月)19)199 95 5 and and 班级班级 inin（20130212013021, ,2013022013022) )SELECT SELECT 姓名姓名, ,出生年月出生年月 from from studentstudent; ; WHERE year( WHERE year(出生年月出生年月)19)199 95;5; and (and (班级班级= =201302120130

25、21oror班级班级= =20130222013022) )格式格式： 设有设有三个表，分别三个表，分别是是student、score和和course表。表。4、基于多个记录源的查询student表表score表表course表表例15 输出输出student表所有表所有学生的考试成绩，要求给出学生的考试成绩，要求给出学号、姓名、学号、姓名、课程号课程号、课程名称和考试成绩。、课程名称和考试成绩。Select a.学号学号, a.姓名姓名, b.课程号课程号, c.课程名称课程名称 , b.考试成考试成绩绩 order by 成绩成绩;From student a, score b, cour

26、se c;Where a.学号学号=b.学号学号.and.b.课程号课程号=c.课程号课程号1）用）用where实现表与表的连接实现表与表的连接abc学生成绩授课关系连接图d课程对照输出学生成绩在输出学生成绩在8090之间的姓名，性别，课号、之间的姓名，性别，课号、课程名称和成绩。课程名称和成绩。例例 16输出输出student所有学生的姓名、课程号、课程名所有学生的姓名、课程号、课程名称和成绩称和成绩 在在80到到90之间的学生信息。之间的学生信息。 Select 姓名姓名,性别性别,score.课程号课程号, 课程名称，成绩课程名称，成绩; From student, score, cou

27、rse Where student.学号学号=score.学号学号 .and.score.课程号课程号=课程课程.课程号课程号.and.考试考试成绩成绩 BETWEEN 80 AND 902）用内连接）用内连接INNER JOIN实现表与表的连接实现表与表的连接 INNER JOIN出现在出现在FROM子句中，格式为：子句中，格式为：FROM INNER JOIN ON SELECT 姓名姓名,成绩成绩 FROM student INNER JOIN score ON student.学号学号=score.学号学号例例17 输出全体学生的姓名及选修课的成绩情况输出全体学生的姓名及选修课的成绩情

28、况 在较长的查询命令中可用别名代替表名。在较长的查询命令中可用别名代替表名。 例例18 18 查找查找学号为学号为2013010220130102学生的选课及成绩情学生的选课及成绩情况况t t 作为作为“scorescore”表的别名，表的别名，s s 作为作为“课程课程”表的别表的别名名SELECT 学号，学号，课程号课程号，课程名称、成绩，课程名称、成绩 FROM score AS t INNER JOIN 课程课程 AS s ON t.课课程编号程编号=s.课程号课程号 WHERE t.学号学号=201301023）用左外连接实现表与表的连接）用左外连接实现表与表的连接 FROM LEF

29、T JOIN ON 将左侧表将左侧表()中连接字段的值全部显示中连接字段的值全部显示4）用右外连接实现表与表的连接）用右外连接实现表与表的连接 FROM RIGHT JOIN ON 将右侧表将右侧表()中连接字段的值全部显示中连接字段的值全部显示例19 查询student的姓名和他的选课考试成绩 SELECT 姓姓名，考试成绩名，考试成绩 FROM student LEFT JOIN score ON 学生学生.学号学号 = score.学号学号例20 查询student的姓名和他的选课考试成绩SELECT 姓姓名名，成绩，成绩 FROM student RIGHT JOIN score ON

30、 学生学生.学号学号 = score.学号学号例例21查询查询student表中姓表中姓张学生及其选修课张学生及其选修课程的情况。程的情况。SELECT SELECT student.student.* *,score.,score.* *; ; FROM FROM student, score;student, score;WHERE WHERE student.student.学号学号= =score.score.学号学号 and and 姓名姓名 like like 张张%SELECT 学号学号 FROM score ，course;WHERE 课程名称课程名称=数据库数据库AND; s

31、core.课程号课程号=course.课程号课程号)例例22 查询查询选修了数据库课的学生学号。选修了数据库课的学生学号。例例23 查询查询选修了数据库课的选修了数据库课的学生学号，姓名学生学号，姓名及成绩。及成绩。SELECT 姓名姓名,学学号号 ,成绩成绩 FROM student，score，course WHERE 课程名称课程名称=“数据库应用技术数据库应用技术” student.学号学号=score.学号学号 AND; score.课程号课程号=course.课程号课程号)5、 常用函数常用函数SUM AVG MAX MIN COUNT 1 格式：格式：select sum(字段名

32、字段名) as 别名别名 from 数据数据表表 where 条件表达式条件表达式select avg(入学成绩入学成绩) as 入学成绩平均入学成绩平均 ；from student where 入学成绩入学成绩600例例24求入学成绩大于求入学成绩大于600分的平均分的平均AVG(字段名字段名) 得出一个表格栏平均值得出一个表格栏平均值例例25求入学成绩大于求入学成绩大于600分的平均分的平均select avg(入学成绩入学成绩) as 入学成绩平均入学成绩平均 ；from student where 入学成绩入学成绩600例例26查询选修了查询选修了01号课的学生的平均成绩。号课的学生的

33、平均成绩。 SELECT AVG(成绩成绩) FROM score WHERE 课课程号程号=01MAX(字段名字段名) 取得一个表格栏最大的值取得一个表格栏最大的值 例例27 求求最高入学成绩的学生姓名最高入学成绩的学生姓名,籍贯籍贯,班级班级select 姓名姓名,籍贯籍贯,班级班级,MAX(入学成绩入学成绩) as 最高入学成绩最高入学成绩 ；from studentMIN（字段名（字段名) 取得一个表格栏最小的值取得一个表格栏最小的值例例28 求求最低入学成绩姓名最低入学成绩姓名,籍贯籍贯,班级班级select 姓名姓名,籍贯籍贯,班级班级,MIN(入学成绩入学成绩) as 最低入学成

34、绩最低入学成绩 ；from student例例29 按按学号统计学生人数。学号统计学生人数。 SELECT COUNT(学号学号) FROM score COUNT (*|字段名字段名) 对数据行数的统计或对某一栏对数据行数的统计或对某一栏有值的数据行数统计有值的数据行数统计 。MAX和MIN函数：分别用于在指定的记录范围内找出具有最大值和最小值的字段。例30 找出男生中的最高分和最低分 SELECT MAX(入学分数入学分数) AS 最高分最高分, MIN(入学分数入学分数) AS 最最低分低分 FROM student WHERE 性别性别=男男6、操作查询的、操作查询的SQL语句语句v作

35、用：将作用：将SELECT命令执行的结果形成一个表保存命令执行的结果形成一个表保存在数据库中。在数据库中。v操作：操作：SQL代码：在代码：在SELECT命令的字段名列表后加上命令的字段名列表后加上子句子句INTO 在查询设计视图中完成：打开查询设计视图在查询设计视图中完成：打开查询设计视图 执行执行“查询设计查询设计”/“生成表查询生成表查询”菜单命令菜单命令 1)生成表查询生成表查询例例31 将将student表中所有女生的全部信息按学号降表中所有女生的全部信息按学号降序、入学分数升序生成序、入学分数升序生成Female表。表。 SQL代码：代码：SELECT * INTO Female FROM student WHERE 性别性别=女女 ORDER BY 学号学号 DESC,入学分数入学分数 32 将将student中男生的信息插入到对应的中男生的信息插入到对应的Female对对应的表中。应的表中。 INSERT INTO Female SELECT * FROM student WHERE 性别性别=男男例例33 将新来的张三同学信息插入到将新来的张三同学信息插入到student中中Insert into student(学号学号,姓名姓名,性别性别,出生年月出生年月