ACCESS数据库技术实验指导书2文档格式.docx

ACCESS数据库技术实验指导书2文档格式.docx

《ACCESS数据库技术实验指导书2文档格式.docx》由会员分享，可在线阅读，更多相关《ACCESS数据库技术实验指导书2文档格式.docx（23页珍藏版）》请在冰豆网上搜索。

操作步骤：

（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-7所示。

（4）单击“保存”按钮，在“查询名称”文本框中输入“统计学生人数”。

（5）运行查询，查看结果。

２．创建带条件的统计查询

统计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