《实验2、查询创建与操作》实验指导书.doc

计算机科学与技术教研室 数据库原理及其应用实验指导书实验二、查询创建与操作一、实验目的熟悉Access开发环境，理解Access查询的基本概念，熟悉各类筛选表达式的编写，掌握各种查询的创建与修改方法，能正确地运用查询筛选、维护数据表记录。二、实验内容本实验课时数为4课时，实验环境为教学管理数据库，实验内容主要包括：1. 创建选择查询2. 创建交叉表查询3. 创建操作查询4. 创建参数查询5. 创建SQL查询三、实验步骤基本操作步骤所有查询的创建都有着基本一致的操作步骤，为简化本实验文档，现将查询创建中所涉及的基本操作简介如下：1. 通过下列方法打开数据库文件（你在实验一中创建的教学管理数据库） 在“我的电脑”中双击你要打开的数据库文件 在计算机的【开始】选择启动Microsoft Office Access 2003数据库管理系统，然后依次单击【文件】【打开】菜单命令，从显示的对话框中选择你要打开的数据库文件 单击【文件】【数据库属性】菜单命令，将“作者”改为你的“完整学号+姓名”2. 在数据库主视窗左边的“对象”列表中单击选定“查询”页面，视窗右边列示用于创建查询的功能按钮及已经创建好的全部查询（如果已有查询的话）。参见图1：图1 数据库主视窗3. 创建查询的方法一： 在图1主视窗中右边的列表区域直接双击【在设计视图中创建查询】，系统显示图2所示的视窗：图2 查询设计视图（选择数据表） 在图2中选择你要用来创建查询的某个对象（可以是表，也可以是原来创建好的某个查询），然后点击【添加】按钮 如果要创建的查询涉及多个表或查询对象，重复上一步中的选择操作 选择好对象后点击【关闭】按钮，系统显示图3类似的视窗。如果你选择了适当的数据表或查询对象，该视窗的上部区域会显示这些对象（上图中的对象：班级，学生）。如需删除某个对象，可用右键单击该对象，再从快捷菜单中选择【删除表】命令。如需添加其他对象，可用右键单击该区域的空白位置，再从快捷菜单中选择【显示表】命令图3 查询设计视图（选择查询） 如图3所示，系统默认为创建“选择查询”，如果要创建的是其他类型的查询，请点击打开【查询】菜单（图4），从该菜单中选择适当的查询类型。视乎你所选择的是哪种查询，系统会调整图3下部区域的显示样式，甚至显示新的对话框要求你作出新的选择。在不同类型查询的具体设计中再详细解释这些操作步骤图4 查询菜单4. 创建查询的方法二： 在图1主视窗中右边的列表区域直接双击【使用向导创建查询】，系统显示图5所示的“简单查询向导”视窗图5 简单查询向导 在图5中左上角的“表/查询”下拉列表框中选定你要用来创建查询的某个对象（可以是表，也可以是原来创建好的某个查询），右下角的“可用字段”列表框会显示该对象中可用于创建查询的字段；通过中间的【】和【】两个按钮，可将“可用字段”列表框中当前或全部字段选入右边的“选定的字段”列表框；反之，通过【】和【】两个按钮，可将右边“选定的字段”列表框中的当前字段或全部字段去掉（不再选定）列表框通过然后点击【添加】按钮 如果要创建的查询涉及多个表或查询对象，重复上一步中的字段选择操作，结束后点击【下一步】或者【完成】按钮 图5中如直接点击【完成】按钮，系统将创建完成最简单的选择查询并显示“查询结果”浏览视窗。关闭该视窗后，应能看到已用默认的名称保存该选择查询 图5中如点击【下一步】按钮，系统将显示一系列的向导视窗，你可以在这些视窗上进一步定义如下内容： 是明细查询还是汇总查询 如果是汇总查询，应按哪些字段进行汇总及如何汇总 输入查询标题，即用什么名称保存该查询对象 选择是立即查看查询结果，还是通过图3所示的“查询设计视图”对该查询进行进一步的修改5. 创建查询的方法三： 在图1主视窗中上面的工具栏区域单击【新建】按钮，系统显示图6所示的“新建查询”视窗图6 新建查询 在图6中右边的列表框中选择适当的项目，然后点击【确定】按钮，系统将分别作出如下处理： 设计视图：接下来的步骤与“创建查询的方法一”中完全相同 简单查询向导：接下来的步骤与“创建查询的方法二”中完全相同 交叉表查询向导：系统显示一系列向导视窗，帮助你完成交叉表查询的设计 查找重复项查询向导：系统显示一系列向导视窗，帮助你完成查找重复项查询的设计 查找不匹配项查询向导：系统显示一系列向导视窗，帮助你完成查找不匹配项查询的设计 图6中无论选择哪种向导，最终都提供查询结果浏览或进入设计视图的选项任务一：选择查询实验2.11. 查询名称：01查询各班级学生2. 请使用“方法一”中描述的步骤开始查询设计3. 在图2中添加“班级”、“学生”两个数据表对象。值得注意的是：如果你的数据库中的数据表设计错误，或者你尚未设计好数据表之间的关系，将不能完成本查询的设计。因此，请务必保证你已按实验二中的步骤设计好数据库并确保已输入或导入适当的数据。在后面的系列查询设计中，这都是最基本的前提4. 按下图所示完成查询设计：5. 依次点击【文件】【保存】菜单命令或单击工具栏内的【保存】按钮，系统显示如下对话框：6. 如上图所示，在对话框中输入要保存的查询名称，然后单击【确定】按钮7. 依次单击【查询】【运行】菜单命令，系统将显示查询结果，如果必要，请对查询结果的显示格式（字段宽度、字体、颜色等）进行必要的调整8. 依次单击【视图】【设计视图】菜单命令，可回到查询设计视图，对刚才创建的查询作进一步修改9. 重复步骤6、7直至查询设计满足要求10. 保存该查询，关闭查询视图，回到主视窗应能看到刚设计好的“01查询各班级学生”查询。如果必要，你可以通过右键菜单运行该查询或重新修改该查询实验2.2 1. 查询名称：02查询李姓男生或张姓女生2. 请使用“方法一”中描述的步骤开始查询设计3. 设计步骤与实验2.1类似，具体要求见如下截图：实验2.3 1. 查询名称：03查询姓名中含有“丽”或“静”字的学生2. 请使用“方法一”中描述的步骤开始查询设计3. 设计步骤与实验2.1类似，具体要求如下： 查询字段：班级（班级号，班级名称），学生（学号，姓名，性别，身份证号） 排序字段：班级号（升序），姓名（升序） 查询条件：学生姓名中含有“丽”字或“静”字。注意：必须使用Like关键字实验2.4 1. 查询名称：04查询姓氏为陈、叶或黄的学生2. 请使用“方法一”中描述的步骤开始查询设计3. 设计步骤与实验2.1类似，具体要求如下： 查询字段：班级（班级号，班级名称），学生（学号，姓名，性别，家庭住址） 排序字段：姓名（升序），性别（降序） 查询条件：姓陈、姓叶或姓黄的学生。注意：必须使用Like关键字实验2.5 1. 查询名称：05查询姓名中第2个字是“嘉”或“美”的学生2. 请使用“方法一”中描述的步骤开始查询设计3. 设计步骤与实验2.1类似，具体要求如下： 查询字段：班级（班级号，班级名称），学生（学号，姓名，性别，身份证号） 排序字段：性别（降序），姓名（升序） 查询条件：学生姓名中第2个字是“嘉”字或“美”字。注意：必须使用Like关键字实验2.6 1. 查询名称：06查询非莞籍学生2. 请使用“方法一”中描述的步骤开始查询设计3. 设计步骤与实验2.1类似，具体要求如下： 查询字段：专业（专业名称），班级（班级名称），学生（学号，姓名，性别，家庭住址，身份证号） 排序字段：家庭住址（升序），性别（降序） 查询条件：家庭住址不在东莞市。注意：必须使用运算符实验2.7 1. 查询名称：07查询东莞、深圳、珠海籍少数民族男生2. 请使用“方法一”中描述的步骤开始查询设计3. 设计步骤与实验2.1类似，具体要求如下： 查询字段：班级（班级名称），学生（学号，姓名，性别，家庭住址，名族） 排序字段：家庭住址（升序），民族（降序） 查询条件：家在东莞、深圳、珠海的少数民族男生。注意：必须使用In关键字来创建条件实验2.81. 查询名称：08查询1991年出生的非汉族女生2. 请使用“方法一”中描述的步骤开始查询设计3. 设计步骤与实验2.1类似，具体要求如下： 查询字段：专业（专业名称），班级（班级名称），学生（学号，姓名，性别，家庭住址，出生日期，民族） 排序字段：出生日期（降序） 查询条件：1991年出生的不是汉族的女生。注意：必须使用BETWEEN AND来创建条件实验2.91. 查询名称：09查询3月份出生的学生2. 请使用“方法一”中描述的步骤开始查询设计3. 设计步骤与实验2.1类似，具体要求如下： 查询字段：班级（班级名称），学生（学号，姓名，性别，出生日期） 排序字段：出生日期（降序） 查询条件：3月份出生的全部学生。注意：必须使用Month函数来创建条件任务二：创建交叉表查询实验2.101. 查询名称：10查询各地男、女生人数2. 查询描述：本查询系相对于“学生”表来创建“家庭住址“与“性别”的交叉表。通过该交叉表，可以清晰地知道，来自每一个地区（东莞、梅州、汕头）的男生人数、女生人数及总人数。作为初学者，比起通过设计视图来创建交叉表查询，通过向导来创建交叉表查询相对来说要简单得多也更好理解3. 现在，请按照“方法三”中描述的步骤开始查询设计，然后在图6中选择“交叉表查询向导”，系统显示如下向导对话框，请在这里选择该查询的数据源。系统默认为基于数据表来创建查询。如果必要，也可以从中选择基于某个已有的查询来创建交叉表查询4. 在以上对话框中选择“学生”表，单击【下一步】按钮，系统显示如下向导对话框，以允许选择将作为行标题的是哪个（些）字段（最多可选3个字段）：5. 由于本查询希望统计各地区的男女生人数，因此，行标题以“家庭住址”为宜（我们设计的“学生”表中，“家庭住址”实际上保存的是学生所属的地市区域名称），因此，请按上图选定“家庭住址”作为行标题，然后单击【下一步】按钮，系统将显示另一个向导对话框，以便选择将作为列标题的字段：6. 毫无疑问，你应该在以上对话框中选择“性别”作为列标题，然后还是单击【下一步】按钮以便系统显示这样一个向导对话框：7. 以上对话框用于定义你希望在行、列交叉点显示的内容及其显示（统计）的依据。该对话框中，请按如下描述作出选择： 首先需要选定显示（统计）所依据的字段。这个实验中，由于每个“学号”唯一地代表一个学生，因此以“学号”来统计人数才不会出现问题。因为，按“姓名”来统计的话，重复的姓名（例如有两个学生都叫“王二麻子”，将作为一个而不是两个学生来统计。选择其他字段也可能存在同样的统计问题。因此，请选定“学号”字段作为统计依据 “字段”栏右边的“函数”栏也需要你仔细确定。“函数”栏用于定义你希望在行、列交叉点显示的内容。上图中提供的各个选项分别表示如下意义： 第一项：交叉点将显示所在区域男生、女生中最前面一名学生的学号 最后一项：交叉点将显示所在区域男生、女生中最后一名学生的学号 最大值：交叉点将显示所在区域男生、女生中学号值最大的那个学号 最小值：交叉点将显示所在区域男生、女生中学号值最小的那个学号 计数：交叉点将显示所在区域男生、女生的人数聪明如你，当然知道该对话框中的“函数”应选择“计数” 对话框左边的中部位置，有一个选项“请确定是否为每一行作小计”，该选项的默认值为已经勾选。一般情况下，你最好是保持该默认选择以上对话框中，每作出一个选择，其下部区域都会显示出最终交叉表的一个模拟效果，告诉你现在选定的行标题、列标题字段都是怎样的，然后交叉点又是按怎样的公式显示（统计）的按上图作出选择后，还得请你最后一次单击【下一步】按钮，系统亦不厌其烦地显示出最后一个向导对话框：8. 现在要做的，首先是按上图所示指定查询的名称（第1步中规定的名称），Access将以这个名称来保存该查询对象。此外，你还得在两个单选按钮之间作出自己的选择：选择“查看查询”可以进入查询的数据表视图以浏览该查询的运行结果，选择“修改设计”选项可以进入查询的设计视图对查询作细微调整。无论进入查询的数据表视图还是设计视图，你都可以再在两个视图之间来回切换。无论你最终作出何种选择，都需要单击【完成】按钮以结束向导9. 如上一对话框中选择的是“修改设计“，系统将显示该查询的设计视图：10. 以上对话框中，你需要作如下一些细微的调整： 字段：“总计 学号: 学号”请改为“总人数: 学号”，其中“总人数”将显示在列标题上，冒号后的“学号”表示将按“学号”字段统计总人数（男生人数 + 女生人数） 排序：应指定为按“家庭住址”作升序或降序排序11. 显然，如果你对照着以上向导过程，对以上设计视图中的各项内容都有了正确的认识，你可以直接进入设计视图来开始交叉表查询的设计。在设计视图中完成最后的设计后，你可以依次点击【视图】【数据表视图】菜单命令或【查询】【运行】菜单命令，进入数据表视图来查看该交叉表查询的运行结果（查询到的数据）并通过数据表视图调整列宽、字体及其他显示格式。这个交叉表查询的最终运行结果应类似（可以设置不同的字体、颜色）：实验2.111. 查询名称：11查询各地不同年份出生的学生人数2. 请使用“方法一”中描述的步骤开始查询设计3. 在图4中所示的菜单中选择“交叉表查询”，然后按如下截图完成查询设计4. 直接在设计视图中设计以上查询时，务必注意理解行标题、列标题、计算公式等各部分是如何处理的，哪些内容可以随意修改、哪些内容必须完全照抄无误！任务三：操作表查询实验2.121. 查询名称：12生成非莞籍学生表2. 请使用“方法一”中描述的步骤开始查询设计3. 在图4中所示的菜单中选择“生成表查询”4. 具体要求为，生成的新表： 保存到：当前数据库 名称为：非莞籍学生 依次包含字段：学生（家庭住址，学号，姓名，性别，民族），专业（专业名称），班级（班级名称） 排序字段：家庭住址（降序），性别（降序），姓名（升序） 查询条件：家庭住址不是东莞的全部学生5. 运行该查询以生成“非莞籍学生”数据表，最终该表的数据表视图类似：实验2.131. 查询名称：13更新非莞籍学生表2. 请使用“方法一”中描述的步骤开始查询设计3. 在图4中所示的菜单中选择“更新查询”4. 本实验将对实验2.12中生成的“非莞籍学生”表进行“批量”更新。具体要求为，将该表中计科1班全部男生的“民族”都更改为“月光族”！特别声明：出此选题，纯属好玩，并无歧意！5. 运行该查询，查看“非莞籍学生”数据表中“民族”字段的值是否按要求自动更新。，最终该表的数据表视图类似：实验2.141. 查询名称：14追加记录到非莞籍学生表2. 请使用“方法一”中描述的步骤开始查询设计3. 本实验将对实验2.12中生成的“非莞籍学生”表进行“批量”追加，即，将“学生”表中部分记录追加（自动插入）到“非莞籍学生”表。具体要求为，将“学生”表中的莞籍党员学生全部追加到“非莞籍学生”表。在设计视图中按下图完成基本设计：4. 在图4中所示的菜单中选择“追加查询”，系统显示对话框：5. 在以上对话框中选择数据记录要追加到的表为“非莞籍学生”，然后单击【确定】按钮6. 按要求的名称保存该查询，关闭设计视图7. 运行该查询，查看“非莞籍学生”数据表中是否已按要求自动追加若干学生记录：实验2.151. 查询名称：15删除非莞籍学生表中记录2. 请使用“方法一”中描述的步骤开始查询设计3. 在图4中所示的菜单中选择“删除查询”4. 本实验将对实验2.12中生成的“非莞籍学生”表进行“批量”删除。即，将“非莞籍学生”表中的莞籍学生记录全部删除掉。在设计视图中按下图完成查询的设计8. 按要求的名称保存该查询，关闭设计视图9. 运行该查询，查看“非莞籍学生”数据表中是否已按要求自动删除学生记录任务四：统计计算查询该类查询，是指在查询中加入计算公式或函数，以对数据表中的数据进行统计计算实验2.161. 查询名称：16查询教师应发奖金2. 检查“教师”表中是否已有“底薪”、“津贴”两个字段。如果尚无这两个字段，则先增加这两个字段（都是整数类型，无小数）3. 使用“方法一”中描述的步骤开始查询设计4. 本查询以“教师”表中的“底薪”、“津贴”两个字段为基础，按这两个字段之值的百分比计算应发奖金。该查询还展示了字符串、日期函数的用法。请按下图设计该查询：该查询中设计了四个计算字段，一个计算字段的名称为“奖金”，该字段的值通过公式“底薪*1.5+补贴*1.2”计算获得，该公式表示奖金额为底薪的150%再加补贴的120%。另外的计算字段包括：通过Left、Mid函数从姓名中获得教师的姓氏及名字；通过Year函数计算教师入职的年份。必须注意到：计算公式中的字段名必须放在内。5. 按要求的名称保存查询6. 运行查询实验2.171. 查询名称：17查询学生各课程最高分、最低分及平均分2. 请使用“方法一”中描述的步骤开始查询设计3. 选定“学生”、“课程”、“成绩”三个表4. 依次单击【视图】【总计】或直接点击工具栏中的按钮5. 按照以下截图完成查询设计：6. 按要求的名称保存查询7. 运行查询，查询结果应类似：实验2.181. 查询名称：18查询各班级党员、团员及群众人数2. 请使用“方法一”中描述的步骤开始查询设计3. 参照实验2.17的步骤及以下查询运行结果截图完成查询设计：4. 保存并运行查询任务五：SQL查询该类查询，是指直接通过编写SQL（结构化语言）语句来创建查询实验2.191. 查询名称：19通过SQL语句查询教师信息2. 请使用“方法一”中描述的步骤开始查询设计3. 系统显示“图2 查询设计视图（选择数据表）”所示的对话框时，不选择任何表，直接关闭该对话框4. 依次单击【视图】【SQL视图】，系统显示如下对话框：5. 在以上对话框中编写下图所示的SQL SELECT语句（选择语句）：该语句的功能为： SELECT子句（要查询哪些字段）：系部（系部名称），教师（教师编号，姓名，出生日期）。注意：两个表都存在的同名字段（例如“系部编号“）前必须通过“表名.字段名”的格式指明是查询哪个表中的字段，两个表中不同名的字段可仅写出字段名 FROM子句（从哪个表或哪些表查询，如果从多个表中查询，这两个表通过哪个、哪些字段连接）：“系部”、“教师”两表，通过这两个表都存在的“系部编号”字段来连接 WHERE子句（要满足的查询条件）：“性别”字段的值为“男” ORDER BY子句（如何排序）：按“系部名称”降序（DESC）、“教师编号”升序（ASC）排序。不指定DESC、ASC时，默认按升序排序6. SELECT语句编写完成后，切换到“数据表视图”（运行查询），你将看到查询结果为：7. 按要求的名称保存该查询实验2.201. 查询名称：20通过SQL语句统计学生分数2. 参照实验2.19的操作步骤及如下截图创建SQL查询：该SQL SELECT语句用于求陈姓、叶姓、黄姓学生各门课程的最高分、最低分及平均分。这个实验值得注意的地方有： SELECT子句中，由于仅“学生”表中有“姓名”字段（“成绩”表无此字段），因此“姓名”前不需要写“学生.” SELECT语句中还用到Max、Min和Avg三个函数，以分别计算当前学生各课程成绩中的最高分、最低分和平均分 每个学生所学课程的成绩都登记在“成绩”表中，因此FROM子句系通过“学号”字段连接“学生”、“成绩”两表 由于是按“学生”的“学号”、“姓名”分组统计每个学生的最高分、最低分、平均分，因此，该SELECT语句比实验2.19多了一个GROUP BY子句。你的英语还勉强的话，应该知道GROUP BY就是“按什么分组”的意思。这里，显然是按“学生”的“学号”、“姓名”分组 务必要注意的是：编写类似的分组统计语句时，进行分组的字段（学号，姓名）必须以相同的顺序同时出现在GROUP BY和SELECT这两个子句中。仔细看看这两个子句的写法：SELECT 学生.学号, 姓名GROUP BY 学生.学号, 姓名3. 运行该查询，查询结果类似：4. 以要求的名称保存并关闭查询任务六：参数查询该类查询，是指在各种查询中加入所谓的参数，查询运行时将提示用户输入参数的值，然后Access将参数的值嵌入查询语句中并运行查询、返回查询结果实验2.211. 查询名称：21通过参数查询某个学生各课程的成绩2. 请使用“方法一”中描述的步骤开始查询