实验3 选择查询的设计

1、数据库技术及应用实验指导操作本实验前应注意：l 本实验全部操作内容请在文件夹中的” 教学信息管理系统”数据库中操作l 全部需要保存的查询也是保存在“教学信息管理系统”数据库中！l 全部需要保存的查询请按题目给出的文件名保存，如果题目没有给出文件名，那么请按照本题得题目编号保存，比如，以“例2-1”、“例3-1”等等的名字来保存；l 本次实验提供的操作步骤仅供参考，部分题目的截图与题目有所出入，请同学们以实际操作为准；l 本次实验的解题方法不是唯一，只要能完成题目的查询就可保存该查询了。l 为了更快更好地解决本次查询操作，建议你在操作查询前，认真阅看本次实验所提供的“教学信息管理系统”的6个表以

2、及表间联系。查询（1）一、实验目的1. 掌握各种查询的创建方法2. 掌握查询条件的表示方法二、实验内容与要求1创建各种选择查询三、实验步骤案例一：利用“查询向导”创建选择查询例1-1单表选择查询要求：以“教师”表为数据源，查询教师的姓名和职称信息，所建查询命名为“教师情况”。操作步骤：（1）打开“教学信息管理系统.accdb”数据库，单击“创建”选项卡，“查询”组单击“查询向导”弹出“新建查询”对话框。如图2-1所示。图2-1创建查询（2）在“新建查询”对话框中选择“简单查询向导”，单击“确定”按钮，在弹出的对话框的“表与查询”下拉列表框中选择数据源为“表:教师”，再分别双击“可用字段”列表中

3、的“姓名”和“职称”字段，将它们添加到“选定的字段”列表框中，如图2-2所示。然后单击“下一步”按钮，为查询指定标题为“教师情况”，最后单击“完成”按钮。图2-2简单查询向导例1-2多表选择查询要求：查询学生所选课程的成绩，并显示“学号”、“姓名”、“课程名称”和“考分”字段。操作步骤：（1）打开“教学信息管理系统.accdb”数据库，在导航窗格中，单击“查询”对象，单击“创建”选项卡，“查询”组单击“查询向导”弹出“新建查询”对话框。（2）在“新建查询”对话框中选择“简单查询向导”，单击“确定”按钮，在弹出的对话框的“表与查询”。先选择查询的数据源为“学生”表，并将“学号”、“姓名”字段添加

4、到“选定的字段”列表框中，再分别选择数据源为“课程名称”表和“成绩”表，并将“课程名称”表中的“课程名称”字段和“成绩”表中的“考分”字段添加到“选定的字段”列表框中。选择结果如图2-3所示。图2-3多表查询（仅供参考，以实际操作为准）（3）单击“下一步”按钮，选“明细”选项。（4）单击“下一步”按钮，为查询指定标题“学生选课成绩”，选择“打开查询查看信息”选项。（5）单击“完成”按钮，弹出查询结果。注：查询涉及“学生”、“课程”和“选课成绩”3个表，在建查询前要先建立好三个表之间的关系。例1-3跨表选择查询要求：查询所有课室所上的所有课程，并显示“地点”和“课程名称”等字段。操作步骤请参考例

5、1-2：操作提示：要放入三个有关联的表：教室、课程安排和课程名称例1-4跨表选择查询要求：查询所有教师所担任的所有课程，并显示教师的“姓名”和“课程名称”等字段。操作步骤请参考例1-3：例1-5查找重复项查询向导要求：判断“籍贯”表中有2人以上是相同籍贯的各有多少人，并显示“籍贯”和重复多人的数目等字段。操作步骤：在“新建查询”对话框中选择“查找重复项查询向导”，单击“确定”按钮，在弹出的对话框的“表与查询”。先选择查询的数据源为“学生”表，点击“下一步”，再选择学生表中“籍贯”字段，下一步什么字段都不选，最后回答查询名即可。选择结果如下图所示。选择表对象中的“学生”在这里选择重复值字段为学生

6、表中的“籍贯”字段这个界面什么字段都不选，直接点击下一步。最后回答查询名称为“例1-5”例1-6查找重复项查询向导要求：判断“学生”表中是否有重名的学生，如果有，请显示这些学生的“姓名”、“学号”、“性别”和“籍贯”等字段。操作步骤请参考例1-5：案例二：在设计视图中创建选择查询例2-1创建不带条件的选择查询要求：查询学生所选课程的成绩，并显示“学号”、“姓名”、“课程名称”和“考分”字段。操作步骤：（1）打开“教学信息管理系统.accdb”数据库，在导航窗格中，单击“查询”对象，单击“创建”选项卡，“查询”组单击“查询设计”，出现”表格工具/设计”选项卡，如图2-4查询工具。同时打开查询设计

7、视图，如图2-5所示。图2-4查询工具（2）在“显示表”对话框中选择“学生”表，单击“添加”按钮，添加学生表，同样方法，再依次添加“成绩”和“课程”表。（3）双击学生表中“学号”、“姓名”、课程表中“课程名称”和成绩表中“考分”字段，将它们依次添加到“字段”行的第14列上。图2-5查询设计器（仅供参考，以实际操作为准）（4）单击快速工具栏 “保存”按钮，在“查询名称”文本框中输入“例2-1”，单击“确定”按钮。（5）选择“开始/视图”“数据表视图”菜单命令，或单击“查询工具/设计”“结果”上的“运行”按钮，查看查询结果。查看查询结果。例2-2创建带条件的选择查询条件与条件间的关系可以是“与关系

8、（And）”或者是“或关系（Or）”，在本例所建查询中，要求2个字段值均等于条件给定值，表示这2个条件的关系是“与关系”。此时，应将2个条件同时写在“条件”行上。要求：查找1972年出生的男生信息，要求显示“学号”、“姓名”、“性别”、“有否奖学金”字段内容。操作步骤：（1）在设计视图中创建查询，添加“学生”表到查询设计视图中。（2）依次双击“学号”、“姓名”、“性别”、“有否奖学金”、“生日”字段，将它们添加到“字段”行的第15列中。（3）单击“生日”字段“显示”行上的复选框，使其空白，查询结果中不显示生日字段值。（4）在“性别”字段列的“条件”行中输入条件“男”，在“生日”字段列的“条件”

9、行中输入条件“>=#1972-1-1# and <=#1972-12-31#”，设置结果如图2-6所示。图2-6带条件的查询（仅供参考，以实际操作为准）（5）单击保存按钮，在“查询名称”文本框中输入“例2-2”，单击“确定”按钮。（6）单击“查询工具/设计”“结果”上的“运行”按钮，查看查询结果。案例三：创建计算查询分组总计查询在实际应用中，不仅需要对所有的记录进行统计，还需要将记录分组，对每个组的数据进行统计。设置方法是：在“设计”视图中，把要进行分组的字段的“总计”单元格选择为“分组”，把要进行计算的每个字段的“总计”单元格选择为相应的总计函数，就可以实现分组统计查询了。例3-

10、1创建不带条件的统计查询要求：统计学生人数。操作步骤：（）在设计视图中创建查询，添加“学生”表到查询设计视图中。（）双击“学号”字段，添加到“字段”行的第1列中。（）单击“查询工具/设计”“显示/隐藏”组上的“汇总”按钮，插入一个“总计”行，单击“学生编号”字段的“总计”行右侧的向下箭头，选择“计数”函数，如图2-7所示。（4）单击“保存”按钮，在“查询名称”文本框中输入“例3-1”。（5）运行查询，查看结果。例3-2图2-7不带条件的统计查询创建带条件的统计查询要求：统计1972年出生的男生人数。操作步骤：（1）在设计视图中创建查询，添加“学生”表到查询设计视图中。（2）双击“学号”、“性别

11、”和“生日”字段，将它们添加到“字段”行的第13列中。（3）单击“性别”、“生日”字段“显示”行上的复选框，使其空白。（4）单击“查询工具/设计”“显示/隐藏”组上的“汇总”按钮，插入一个“总计”行，单击“学生编号”字段的“总计”行右侧的向下箭头，选择“计数”函数，“性别”和“生日”字段的“总计”行选择“where”选项。（5）在“性别”字段列的“条件”行中输入条件“男”；在“生日”字段列的“条件”行中输入条件Year(生日)=1972，如图2-8所示。图2-8带条件的统计查询（仅供参考，以实际操作为准）（6）单击保存按钮，在“查询名称”文本框中输入“例3-2”。（7）运行查询，查看结果。例3

12、-3创建分组统计查询要求：统计男、女学生入学成绩的最大值、最小值和平均值。操作步骤：（1）在设计视图中创建查询，添加“学生”表到查询设计视图中。（2）字段行第1列选“性别”，第2列到第4列选“入学成绩”。图2-9分组统计查询（仅供参考，以实际操作为准）（3）单击“查询工具/设计”“显示/隐藏”组上的“汇总”按钮，插入一个“总计”行，设置“性别”字段的“总计”行为“Group By”， “入学成绩”字段的“总计”行分别设置成最大值、最小值和平均值，查询的设计窗口如图2-9所示。（4）单击保存按钮，在“查询名称”文本框中输入“例3-3”。（5）运行查询，查看结果。例3-4创建含有IIF()函数的计

13、算字段要求：修改“1972年出生的男生信息”（查询名为:例2-2），有否奖学金情况用“有”和“无”来显示，使显示结果更清晰。操作步骤：（1）在导航格的“查询”对象下，选中“例2-2”查询，右键单击“设计视图”菜单，打开查询设计视图。（2）将字段“有否奖学金”修改为 “奖学金情况:IIF(有否奖学金, "有","无")”，选中该列“显示”行上的复选框，设计结果如图2-10所示。图2-10含有IIF()函数的计算字段查询（仅供参考，以实际操作为准）（3）单击“另保存”按钮，给本次查询起名为“例3-4”保存，运行并查看结果。例3-5新增含有Date()函数的计

14、算字段要求：显示学生的姓名、生日和年龄。操作步骤：（1）在设计视图中创建查询，添加“学生”表到查询设计视图中。（2）在“字段”行第1列中选“姓名”字段，第2列选“生日”字段，第3列输入“年龄:Year(Date()-Year(生日)”，并选中该列“显示”行上的复选框。如图2-11所示。（3）单击“保存”按钮，将查询命名为“例3-5”，运行并查看结果。图2-11含有Date()函数的计算字段查询（仅供参考，以实际操作为准）案例四：创建交叉表查询例4-1.利用“交叉表查询向导”创建查询要求：查询每个学生的选课情况和平均成绩，行标题为“学号”，列标题为“课程ID”，计算字段为“考分”。注意：交叉表查

15、询不做各行小计。操作步骤：（1） 在数据库窗口中，选择“查询”对象，单击“新建”按钮，选择“交叉表查询向导”，单击“确定”按钮。（2）选择“视图”选项中“表”选项，选择“成绩”表，如图2-12所示。单击“下一步”按钮。（3）将“可用字段”列表中的“学号”添加到其右侧的“选定字段”列表中，即将“学号”作为行标题，单击“下一步”按钮。如图2-13所示。图2-12指定包含交叉表查询字段的表（仅供参考，以实际操作为准）图2-13确定哪些字段的值作为行标题（仅供参考，以实际操作为准）（4）选择“课程ID”作为列标题，然后单击“下一步”按钮。（5）在“字段”列表中，选择“考分”作为统计字段，在“函数”列表

16、中选“平均”(即是Avg)选项，取消“是，包含各行小计”的选择，单击“下一步”按钮。如图2-14所示。图2-14确定行列交叉点计算出什么数字（仅供参考，以实际操作为准）（6）在“指定查询的名称”文本框中输入“例4-1”，选择“查看查询”选项，最后单击“完成”按钮。当查询所需数据来自于不同的多个数据源时，则应先使用“简单查询向导”先将所需的数据放在一个查询里，然后才能使用“交叉表查询向导”来创建此查询，这样做显然有些麻烦。事实上，很多情况下可以直接使用查询设计视图来创建交叉表查询。例4-2使用设计视图创建交叉表查询要求：使用设计视图创建交叉表查询，用于统计各门课程男女生的平均成绩，要求不做各行小计，课程名称作为行标题，性别作为列标题。操作步骤：（1）在设计视图中创建查询，并将“课程名称”、“成绩”和“学生”三个表添加到查询设计视图中。图2-15设计视图创建交叉表查询（仅供参考，以实