ACCESS数据库技术实验指导书2文档格式.docx
《ACCESS数据库技术实验指导书2文档格式.docx》由会员分享,可在线阅读,更多相关《ACCESS数据库技术实验指导书2文档格式.docx(23页珍藏版)》请在冰豆网上搜索。
![ACCESS数据库技术实验指导书2文档格式.docx](https://file1.bdocx.com/fileroot1/2023-1/5/f42b700b-ca8c-439c-856b-fc6eccc1b23d/f42b700b-ca8c-439c-856b-fc6eccc1b23d1.gif)
操作步骤:
(1)打开“教学管理.accdb”数据库,单击“创建”选项卡,“查询”组——单击“查询向导”弹出“新建查询”对话框。
如图2-1所示。
图2-1创建查询
(2)在“新建查询”对话框中选择“简单查询向导”,单击“确定”按钮,在弹出的对话框的“表与查询”下拉列表框中选择数据源为“表:
教师”,再分别双击“可用字段”列表中的“姓名”和“职称”字段,将它们添加到“选定的字段”列表框中,如图2-2所示。
然后单击“下一步”按钮,为查询指定标题为“教师情况”,最后单击“完成”按钮。
图2-2简单查询向导
2.多表选择查询
查询学生所选课程的成绩,并显示“学生编号”、“姓名”、“课程名称”和“成绩”字段。
(1)打开“教学管理.accdb”数据库,在导航窗格中,单击“查询”对象,单击“创建”选项卡,“查询”组——单击“查询向导”弹出“新建查询”对话框。
(2)在“新建查询”对话框中选择“简单查询向导”,单击“确定”按钮,在弹出的对话框的“表与查询”。
先选择查询的数据源为“学生”表,并将“学生编号”、“姓名”字段添加到“选定的字段”列表框中,再分别选择数据源为“课程”表和“选课成绩”表,并将“课程”表中的“课程名称”字段和“选课成绩”表中的“成绩”字段添加到“选定的字段”列表框中。
选择结果如图2-3所示。
图2-3多表查询
(3)单击“下一步”按钮,选“明细”选项。
(4)单击“下一步”按钮,为查询指定标题“学生选课成绩”,选择“打开查询查看信息”选项。
(5)单击“完成”按钮,弹出查询结果。
注:
查询涉及“学生”、“课程”和“选课成绩”3个表,在建查询前要先建立好三个表之间的关系。
案例二:
在设计视图中创建选择查询
1.创建不带条件的选择查询
(1)打开“教学管理.accdb”数据库,在导航窗格中,单击“查询”对象,单击“创建”选项卡,“查询”组——单击“查询设计”,出现”表格工具/设计”选项卡,如图2-4查询工具。
同时打开查询设计视图,如图2-5所示。
图2-4查询工具
(2)在“显示表”对话框中选择“学生”表,单击“添加”按钮,添加学生表,同样方法,再依次添加“选课成绩”和“课程”表。
(3)双击学生表中“学生编号”、“姓名”、课程表中“课程名称”和选课成绩表中“成绩”字段,将它们依次添加到“字段”行的第1~4列上。
图2-5查询设计器
(4)单击快速工具栏“保存”按钮,在“查询名称”文本框中输入“选课成绩查询”,单击“确定”按钮。
(5)选择“开始/视图”→“数据表视图”菜单命令,或单击“查询工具/设计”——“结果”上的“运行”按钮,查看查询结果。
查看查询结果。
2.创建带条件的选择查询
查找2008年9月1日入校的男生信息,要求显示“学生编号”、“姓名”、“性别”、“团员否”字段内容。
(1)在设计视图中创建查询,添加“学生”表到查询设计视图中。
(2)依次双击“学生编号”、“姓名”、“性别”、“团员否”、“入校日期”字段,将它们添加到“字段”行的第1~5列中。
(3)单击“入校日期”字段“显示”行上的复选框,使其空白,查询结果中不显示入校日期字段值。
(4)在“性别”字段列的“条件”行中输入条件“男”,在“入校日期”字段列的“条件”行中输入条件#2008-9-1#,设置结果如图2-6所示。
图2-6带条件的查询
(5)单击保存按钮,在“查询名称”文本框中输入“2008年9月1日入校的男生信息”,单击“确定”按钮。
(6)单击“查询工具/设计”——“结果”上的“运行”按钮,查看查询结果。
案例三:
创建计算查询
1.创建不带条件的统计查询
统计学生人数。
(1)在设计视图中创建查询,添加“学生”表到查询设计视图中。
(2)双击“学生编号”字段,添加到“字段”行的第1列中。
(3)单击“查询工具/设计”——“显示/隐藏”组上的“汇总”按钮,插入一个“总计”行,单击“学生编号”字段的“总计”行右侧的向下箭头,选择“计数”函数,如图2-7所示。
(4)单击“保存”按钮,在“查询名称”文本框中输入“统计学生人数”。
(5)运行查询,查看结果。
2.创建带条件的统计查询
统计2008年入学的男生人数。
(2)双击“学生编号”、“性别”和“入校日期”字段,将它们添加到“字段”行的第1~3列中。
(3)单击“性别”、“入校日期”字段“显示”行上的复选框,使其空白。
(4)单击“查询工具/设计”——“显示/隐藏”组上的“汇总”按钮,插入一个“总计”行,单击“学生编号”字段的“总计”行右侧的向下箭头,选择“计数”函数,“性别”和“入校日期”字段的“总计”行选择“where”选项。
(5)在“性别”字段列的“条件”行中输入条件“男”;
在“入校日期”字段列的“条件”行中输入条件Year([入校日期])=2008,如图2-8所示。
图2-8带条件的统计查询
(6)单击保存按钮,在“查询名称”文本框中输入“统计2008年入学的男生人数”。
(7)运行查询,查看结果。
3.创建分组统计查询
统计男、女学生年龄的最大值、最小值和平均值。
(2)字段行第1列选“性别”,第2列到第4列选“年龄”。
图2-9分组统计查询
(3)单击“查询工具/设计”——“显示/隐藏”组上的“汇总”按钮,插入一个“总计”行,设置“性别”字段的“总计”行为“GroupBy”,“年龄”字段的“总计”行分别设置成最大值、最小值和平均值,查询的设计窗口如图2-9所示。
(4)单击保存按钮,在“查询名称”文本框中输入“统计男女生年龄”。
4.创建含有IIF()函数的计算字段
修改查询“2008年9月1日入校的男生信息”,团员情况用“是”和“否”来显示,使显示结果更清晰。
(1)在导航格的“查询”对象下,选中“2008年9月1日入校的男生信息”查询,右键单击“设计视图”菜单,打开查询设计视图。
(2)将字段“团员否”修改为“团员情况:
IIF([团员否],"
是"
"
否"
)”,选中该列“显示”行上的复选框,设计结果如图2-10所示。
图2-10含有IIF()函数的计算字段查询
(3)单击“保存”按钮,保存查询,运行并查看结果。
5.新增含有Date()函数的计算字段
显示教师的姓名、工作时间和工龄。
(1)在设计视图中创建查询,添加“教师”表到查询设计视图中。
(2)在“字段”行第1列中选“姓名”字段,第2列选“工作时间”字段,第3列输入“工龄:
Year(Date())-Year([工作时间])”,并选中该列“显示”行上的复选框。
如图2-11所示。
(3)单击“保存”按钮,将查询命名为“统计教师工龄”,运行并查看结果。
图2-11含有Date()函数的计算字段查询
案例四:
创建交叉表查询
1.利用“交叉表查询向导”创建查询
查询每个学生的选课情况和平均成绩,行标题为“学生编号”,列标题为“课程编号”,计算字段为“成绩”。
注意:
交叉表查询不做各行小计。
(1)在数据库窗口中,选择“查询”对象,单击“新建”按钮,选择“交叉表查询向导”,单击“确定”按钮。
(2)选择“视图”选项中“表”选项,选择“选课成绩”表,如图2-12所示。
单击“下一步”按钮。
(3)将“可用字段”列表中的“学生编号”添加到其右侧的“选定字段”列表中,即将“学生编号”作为行标题,单击“下一步”按钮。
如图2-13所示。
图2-12指定包含交叉表查询字段的表
图2-13确定哪些字段的值作为行标题
(4)选择“课程编号”作为列标题,然后单击“下一步”按钮。
(5)在“字段”列表中,选择“成绩”作为统计字段,在“函数”列表中选“平均”选项,取消“是,包含各行小计”的选择,单击“下一步”按钮。
如图2-14所示。
图2-14确定行列交叉点计算出什么数字
(6)在“指定查询的名称”文本框中输入“选课成绩交叉查询”,选择“查看查询”选项,最后单击“完成”按钮。
2.使用设计视图创建交叉表查询
使用设计视图创建交叉表查询,用于统计各门课程男女生的平均成绩,要求不做各行小计。
(1)在设计视图中创建查询,并将“课程”、“选课成绩”和“学生”三个表添加到查询设计视图中。
图2-15设计视图创建交叉表查询
(2)双击“课程”表中的“课程名称”字段,“学生”表中的“性别”字段,“选课成绩”表中的“成绩”字段,将它们添加到“字段”行的第1~3列中。
(3)选择“查询类型”组→“交叉表”。
(4)在“课程名称”字段的“交叉表”行,选择“行标题”选项,在“性别”字段的“交叉表”行,选择“列标题”选项,在“成绩”字段的“交叉表”行,选择“值”选项,在“成绩”字段的“总计”行,选择“平均值”选项,设置结果如图2-15所示。
(5)单击“保存”按钮,将查询命名为“统计各门课程男女生的平均成绩”。
运行查询,查看结果。
案例五:
创建参数查询
1.创建单参数查询
以已建的“选课成绩”查询为数据源建立查询,按照学生“姓名”查看某学生的成绩,并显示学生“学生编号”、“姓名”、“课程名称”和“成绩”等字段。
(1)在导航窗格的“查询”对象中,选“选课成绩查询”,然后单击鼠标右键——“设计视图”菜单,打开查询设计视图。
(2)在“姓名”字段的条件行中输入“[请输入学生姓名]”,结果如图2-16所示。
图2-16创建单参数查询
(3)单击“查询/设计”——“结果”上的“运行”按钮,在“请输入学生姓名”文本框中输入要查询的学生的姓名,例如:
“江贺”,单击“确定”按钮,显示查询结果。
(4)单击“文件”→“另存为”菜单命令,将查询另存为“单参数查询-按姓名查询”。
2.创建多参数查询
建立一个多参数查询,用于显示指定范围内的学生成绩,要求显示“姓名”和“成绩”字段的值。
“选课成绩查询”参见案例二,不带参数。
(1)在设计视图中创建查询,在“显示表”对话框中,选择“查询”选项卡,并将“选课成绩查询”添加查询设计视图中。
(2)双击字段列表区中的“姓名”、“成绩”字段,将它们添加到设计网格中“字段”行的第1列和第2列中。
(3)在“成绩”字段的“条件”行中输入“Between[请输入成绩下限:
]And[请输入成绩上限:
]”,此时的设计视图如图2-11所示。
在“成绩”字段的“排序”行中设置“升序”。
如图2-17所示
图2-17创建多参数查询
(4)单击“运行”按钮,屏幕提示输入下限,例如:
80,确定后,输入上限,例如:
100,指定要查找的成绩范围,单击“确定”按钮,显示查询结果。
(5)保存查询为“多参数查询-按成绩范围查询”。
案例六:
创建操作查询
1.创建生成表查询
将成绩在90分以上学生的“学生编号”、“姓名”、“成绩”存储到“优秀成绩”表中。
(1)在设计视图中创建查询,并将“学生”表和“选课成绩”表添加到查询设计视图中。
(2)双击“学生”表中的“学生编号”、“姓名”字段,“选课成绩”表中的“成绩”字段,将它们添加到设计网格中“字段”行中。
(3)在“成绩”字段的“条件”行中输入条件“>
=90”。
图2-18创建生成表查询
(4)选择“查询类型”组→“生成表”命令,打开“生成表”对话框。
(5)在“表名称”文本框中输入要创建的表名称“优秀成绩”,并选中“当前数据库”选项,单击“确定”按钮。
(6)单击“结果”组——“视图”按钮,预览记录
(7)保存查询,查询名称为“生成表查询”
(8)单击“结果”组——单击“运行”按钮,屏幕上出现一个提示框,单击“是”按钮,开始建立“优秀成绩”表。
(9)在“导航窗格”中,选择“表”对象,可以看到生成的“优秀成绩”表,选中它,在数据表视图中查看其内容。
2.创建删除查询
创建查询,将“学生”表的备份表“学生表副本”中姓“张”的学生记录删除。
(1)在“导航窗格”——“表”对象,“文件”选项卡——“对象另存为”菜单命令,输入新的表名“学生表副本”。
(2)在设计视图中创建查询,并将“学生表副本”表添加到查询设计视图中。
(3)选择“查询类型”→“删除”菜单命令,设计网格中增加一个“删除”行。
(4)双击字段列表中的“姓名”字段,将它添加到设计网格中“字段”行中,该字段的“删除”行显示“Where”,在该字段的“条件”行中输入条件“Left([姓名],1)="
张"
”,如图2-19所示。
(5)单击工具栏上的“视图”按钮,预览要删除的一组记录。
(6)保存查询为“删除查询”。
(7)单击工具栏上的“运行”按钮,单击“是”按钮,完成删除查询的运行。
(8)打开“学生的副本”表,查看姓“张”的学生记录是否被删除。
3.创建更新查询
创建更新查询,将“课程编号”为“105”的“成绩”增加5分。
(1)在设计视图中创建查询,并将“选课成绩”表添加到查询设计视图中。
(2)双击“选课成绩”表中的“课程编号”、“成绩”字段,将它们添加到设计网格中“字段”行中。
(3)选择“查询类型”→“更新”命令,设计网格中增加一个“更新到”行。
(4)在“课程编号”字段的“条件”行中输入条件“105”,在“成绩”字段的“更新到”行中输入“[成绩]+5”,如图2-20所示。
(5)单击工具栏上的“视图”按钮,预览要更新的一组记录。
(6)保存查询为“更新查询”。
(7)单击工具栏上的“运行”按钮,单击“是”按钮,完成更新查询的运行。
(8)打开“选课成绩”表,查看成绩是否发生了变化。
4.创建追加查询
创建查询,将选课成绩在80~89分之间的学生记录添加到已建立的“优秀成绩”表中。
(2)单击“查询类型”,选择“追加查询”。
(3)在“追加到”选项中的“表名称”下拉列表框中选“优秀成绩”表,并选中“当前数据库”选项,单击“确定”按钮,这时设计网格中增加一个“追加到”行。
如图2-21所示。
图2-21追加对话框
(4)双击“学生”表中的“学生编号”、“姓名”字段,“选课成绩”表中的“成绩”字段,将它们添加到设计网格中“字段”行中,“追加到”行中自动填上“学生编号”、“姓名”和“成绩”。
(5)在“成绩”字段的“条件”行中,输入条件“>
=80And<
90”,结果如图2-14所示。
图2-22带条件的查询设计器
(6)单击工具栏上的“视图”按钮,预览要追加的一组记录。
(7)保存查询为“追加记录”。
(8)单击工具栏上的“运行”按钮,单击“是”按钮,完成记录的追加。
(9)打开“优秀成绩”表,查看追加的记录。
案例七:
创建SQL查询
对“教师”表进行查询,显示全部教师信息。
(1)在设计视图中创建查询,不添加任何表,在“显示表”对话框中直接单击“关闭”按钮,进入空白的查询设计视图。
(2)单击“查询类型”,单击“SQL视图”按钮(也可以鼠标右键单击查询1选项卡),进入SQL视图。
如图2-23所示
(3)在SQL视图中输入以下语句:
SELECT*FROM教师。
(4)保存查询“SQL查询”。
(5)单击“运行”按钮,显示查询结果。
案例八:
对教学管理数据库进行SQL查询,完善SQL语句
1.SQL简单查询
(1)对“课程”表进行查询,显示课程全部信息
SELECT______FROM课程
(2)列出前5个教师的姓名和工龄。
SELECT姓名,Year(Date())-Year(工作时间)AS工龄FROM教师
(3)求出所有教师的平均年龄。
SELECTAS平均年龄FROM教师
2.带条件查询
(1)列出成绩在80分以上的学生记录。
SELECT*FROM选课成绩WHERE
(2)求出福建住址的学生平均年龄。
SELECTAvg(年龄)AS平均年龄FROM学生WHERE
(3)列出北京海淀区和上海住址的学生名单。
SELECT学生编号,姓名,住址FROM学生WHERE住址In
(4)列出成绩在80分到100分之间的学生名单。
SELECT学生编号,成绩FROM选课成绩WHERE成绩Between
(5)列出所有的姓“张”的学生名单。
SELECT学生编号,姓名FROM学生WHERE姓名Like
(6)列出所有成绩为空值的学生编号和课程编号。
SELECT学生编号,课程编号FROM选课成绩WHERE成绩
3.排序
(1)按性别顺序列出学生编号、姓名、性别、年龄及住址,性别相同的再按年龄由大到小排序。
SELECT学生编号,姓名,性别,年龄,住址FROM学生ORDERBY
(2)将学生成绩降序排序,只显示前30%的记录。
SELECTfrom选课成绩ORDERBY成绩
4.分组查询
(1)分别统计“学生”表中男女生人数。
SELECT性别,Count(*)AS人数FROM学生
(2)按性别统计“教师”表中政治面目为非党员的人数。
SELECT性别,Count(*)AS人数FROM教师WHEREGROUPBY性别
(3)列出平均成绩大于75分的课程编号,并按平均成绩升序排序。
SELECT课程编号,Avg(成绩)AS平均成绩
FROM选课成绩
GROUPBY课程编号HAVING
ORDERBYAvg(成绩)ASC
(4)统计每个学生选修课程的门数(超过1门的学生才统计),要求输出学生编号和选修门数,查询结果按选课门数降序排列,若门数相同,按学生编号升序排列。
SELECT学生编号,Count(课程编号)AS选课门数
GROUPBY学生编号HAVING
ORDERBY2DESC,1
5.嵌套查询
(1)列出选修“高等数学”的所有学生的学生编号。
SELECT学生编号FROM选课成绩WHERE课程编号=
(SELECTFROM课程WHERE课程名称="
高等数学"
)
(2)列出选修“101”课的学生中成绩比选修“105”的最低成绩高的学生编号和成绩。
SELECT学生编号,成绩FROM选课成绩
WHERE课程编号="
101"
And成绩>
Any
(SELECTFROM选课成绩WHERE课程编号="
105"
(3)列出选修“101”课的学生,这些学生的成绩比选修“105”课的最高成绩还要高的学生编号和成绩。
And成绩>
All
(SELECTFROM选课成绩WHERE课程编号="
(4)列出选修“高等数学”或“英语”的所有学生的学生编号。
SELECT学生编号FROM选课成绩
WHERE课程编号In
(SELECT课程编号FROM课程WHERE)
6.联接查询
(1)输出所有学生的成绩单,要求给出学生编号、姓名、课程编号、课程名称和成绩。
SELECTa.学生编号,姓名,b.课程编号,课程名称,成绩
FROM学生a,选课成绩b,课程c
WHEREa.学生编号=b.学生编号And
(2)列出团员学生的选课情况,要求列出学生编号、姓名、课程编号、课程名称和成绩。
SELECTa.学生编号,a.姓名,b.课程编号,课程名称,成绩
WHEREa.学生编号=b.学生编号And
(3)求选修“101”课程的女生的平均年龄。
SELECTAvg(年龄)AS平均年龄FROM学生,选课成绩
WHERE学生.学生编号=选课成绩.学生编号And
7.联合查询
对“教学管理”数据库,列出选修“101”或“102”课程的所有学生的学生编号和姓名,要求建立联合查询。
SELECT学生.学生编号,学生.姓名FROM选课成绩,学生
And选课成绩.学生编号=学生.学生编号
UNIONSELECT学生.学生编号,学生.姓名FROM选课成绩,学生
102"
And___________________________
案例九:
SQL数据定义
1.建立表结构
在“教学管理”数据库中建立“教师情况”表结构:
包括编号,姓名,性别,基本工资,出生年月,研究方向字段,其中出生年月允许为空值。
SQL语句如下:
CREATETABLE教师情况(编号Char(7),姓名Char(8),性别Char
(2),
基本工资Money,出生年月DatetimeNull,研究方向Text(50))
2.修改表结构
对“课程”表的结构进行修改,完善SQL语句。
(1)为“课程”表增加一个整数类型的“学时”字段。
ALTERTABLE课程_____学时Smallint
(2)删除“课程”表中的“学时”字段。
ALTERTABLE课程
3.删除表
在“教学管理”数据库中删除已建立的“教师情况”表,完善SQL语句。
DROP______教师情况
4.插入记录
向“学生”表中添加记录,学生编号为“1101”,姓名为“张会”,入校日期为2012年9月1日,完善SQL语句。
INSERTINTO学生(学生编号,姓名,入校日期)
VALUES()
5.更新记录
完善对“教学管理”数据库进行如下操作的语句。
(1)将“学生”表中“叶飞”同学的住址改为“广东”。
UPDATE学生SET住址="
广东"
WHERE
(2)将所有团员学生的成绩加2分。
完善SQL语句。
UPDATE选课成绩SET
WHERE学生编号IN(SELECT学生编号FROM学生WHERE