ACCESS实验数据查询.docx
《ACCESS实验数据查询.docx》由会员分享，可在线阅读，更多相关《ACCESS实验数据查询.docx（14页珍藏版）》请在冰豆网上搜索。
ACCESS实验数据查询
ACCESS实验查询
一、实验目的
1.掌握各种查询的创建方法
2.掌握查询条件的表示方法
3.掌握应用SQL中SELECT语句进行数据查询的方法
4.理解SQL中数据定义和数据操纵语句
二、实验容与要求
1．创建各种查询
2．使用SQL中SELECT语句进行数据查询
3．使用SQL语句进行数据定义和数据操纵
三、实验步骤
案例一：
利用“简单查询向导”创建选择查询
1．单表选择查询
要求：
以“教师”表为数据源，查询教师的和职称信息，所建查询命名为“教师情况”。
操作步骤：
（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年入学的男生人数。
操作步骤：
（1）在设计视图中创建查询，添加“学生”表到查询设计视图中。
（2）双击“学生编号”、“性别”和“入校日期”字段，将它们添加到“字段”行的第1～3列中。
（3）单击“性别”、“入校日期”字段“显示”行上的复选框，使其空白。
（4）单击“查询工具/设计”——“显示/隐藏”组上的“汇总”按钮，插入一个“总计”行，单击“学生编号”字段的“总计”行右侧的向下箭头，选择“计数”函数，“性别”和“入校日期”字段的“总计”行选择“where”选项。
（5）在“性别”字段列的“条件”行中输入条件“男”；在“入校日期”字段列的“条件”行中输入条件Year（[入校日期]）=2008，如图2-8所示。
图2-8带条件的统计查询
（6）单击保存按钮，在“查询名称”文本框中输入“统计2008年入学的男生人数”。
（7）运行查询，查看结果。
3．创建分组统计查询
要求：
统计男、女学生年龄的最大值、最小值和平均值。
操作步骤：
（1）在设计视图中创建查询，添加“学生”表到查询设计视图中。
（2）字段行第1列选“性别”，第2列到第4列选“年龄”。
图2-9分组统计查询
（3）单击“查询工具/设计”——“显示/隐藏”组上的“汇总”按钮，插入一个“总计”行，设置“性别”字段的“总计”行为“GroupBy”，“年龄”字段的“总计”行分别设置成最大值、最小值和平均值，查询的设计窗口如图2-9所示。
（4）单击保存按钮，在“查询名称”文本框中输入“统计男女生年龄”。
（5）运行查询，查看结果。
案例四：
创建SQL查询
要求：
对“教师”表进行查询，显示全部教师信息。
操作步骤：
（1）在设计视图中创建查询，不添加任何表，在“显示表”对话框中直接单击“关闭”按钮，进入空白的查询设计视图。
（2）单击“查询类型”，单击“SQL视图”按钮（也可以鼠标右键单击查询1选项卡），进入SQL视图。
如图2-10所示
（3）在SQL视图中输入以下语句：
SELECT*FROM教师。
（4）保存查询“SQL查询”。
（5）单击“运行”按钮，显示查询结果。
案例五：
对教学管理数据库进行SQL查询，完善SQL语句
1．SQL简单查询
（1）对“课程”表进行查询，显示课程全部信息
SELECT______FROM课程
SELECT*FROM课程
（2）列出前5个教师的和工龄。
SELECT,Year（Date（））-Year（工作时间）AS工龄FROM教师
SELECTtop5,year（date（））-year（工作时间）as工龄from教师;
（3）求出所有教师的平均年龄。
SELECTAS平均年龄FROM教师
SELECTavg（年龄）AS平均年龄FROM教师
2.带条件查询
（1）列出成绩在80分以上的学生记录。
SELECT*FROM选课成绩WHERE
SELECT*FROM选课成绩where成绩>80
（2）求出住址的学生平均年龄。
SELECTAvg（年龄）AS平均年龄FROM学生WHERE
SELECTAVG（年龄）AS平均年龄FROM学生WHERE住址like"*"
（3）列出和住址的学生。
SELECT学生编号,,住址FROM学生WHERE住址In
SELECT学生.学生编号,学生.,学生.住址
FROM学生
WHERE（（（学生.住址）Like"*"））OR（（（学生.住址）Like"*"））;
?
WHEREleft（住址,2）IN（"",""）
（4）列出成绩在80分到100分之间的学生。
SELECT学生编号,成绩FROM选课成绩WHERE成绩Between
SELECT学生编号,成绩FROM选课成绩WHERE成绩BETWEEN80and100
（5）列出所有的姓“”的学生。
SELECT学生编号,FROM学生WHERELike
SELECT学生编号,FROM学生WHERELIKE"*"
（6）列出所有成绩为空值的学生编号和课程编号。
SELECT学生编号,课程编号FROM选课成绩WHERE成绩
SELECT学生编号,课程编号FROM选课成绩WHERE成绩isnull
3.排序
（1）按性别顺序列出学生编号、、性别、年龄及住址，性别相同的再按年龄由小到大排序。
SELECT学生编号,,性别,年龄,住址FROM学生ORDERBY
SELECT学生编号,,性别,年龄,住址FROM学生ORDERBY性别desc,年龄ASC
（2）将学生成绩降序排序，只显示前30%的记录。
SELECTfrom选课成绩ORDERBY成绩
SELECTTOP30percent*from选课成绩ORDERBY成绩desc
4.分组查询
（1）分别统计“学生”表中男女生人数。
SELECT性别,Count（*）AS人数FROM学生
SELECT性别,COUNT（*）AS人数FROM学生groupby性别
（2）按性别统计“教师”表中政治面目为非党员的人数。
SELECT性别,Count（*）AS人数FROM教师WHEREGROUPBY性别
SELECT性别,COUNT（*）AS人数FROM教师WHERE政治面貌<>"党员"GROUPBY性别
（3）列出平均成绩大于75分的课程编号，并按平均成绩升序排序。
SELECT课程编号,Avg（成绩）AS平均成绩
FROM选课成绩
GROUPBY课程编号HAVING
ORDERBYAvg（成绩）ASC
SELECT课程编号,AVG（成绩）AS平均成绩
FROM选课成绩
GROUPBY课程编号HAVINGAVG（成绩）>75
ORDERBYAVG（成绩）ASC
（4）统计每个学生选修课程的门数（超过1门的学生才统计），要求输出学生编号和选修门数，查询结果按选课门数降序排列，若门数相同，按学生编号升序排列。
SELECT学生编号,Count（课程编号）AS选课门数
FROM选课成绩
GROUPBY学生编号HAVING
ORDERBY2DESC,1
SELECT学生编号,COUNT（课程编号）AS选课门数
FROM选课成绩
GROUPBY学生编号HAVINGCOUNT（课程编号）>1
ORDERBY2DESC,1
5.嵌套查询
（1）列出选修“高等数学”的所有学生的学生编号。
SELECT学生编号FROM选课成绩WHERE课程编号=
（SELECTFROM课程WHERE课程名称="高等数学"）
SELECT学生编号FROM选课成绩WHERE课程编号=
（SELECT课程编号FROM课程WHERE课程名称="高等数学"）
（2）列出选修“101”课的学生中成绩比选修“105”的最低成绩高的学生编号和成绩。
SELECT学生编号,成绩FROM选课成绩
WHERE课程编号="101"And成绩>Any
（SELECTFROM选课成绩WHERE课程编号="105"）
SELECT学生编号,成绩FROM选课成绩
WHERE课程编号="101"And成绩>Any
（SELECT成绩FROM选课成绩WHERE课程编号="105"）
（3）列出选修“101”课的学生，这些学生的成绩比选修“105”课的最高成绩还要高的学生编号和成绩。
SELECT学生编号,成绩FROM选课成绩
WHERE课程编号="101"And成绩>All
（SELECTFROM选课成绩WHERE课程编号="105"）
SELECT学生编号,成绩FROM选课成绩
WHERE课程编号="101"And成绩>all
（SELECT成绩FROM选课成绩WHERE课程编号="105"）
（4）列出选修“高等数学”或“英语”的所有学生的学生编号。
SELECT学生编号FROM选课成绩
WHERE课程编号In
（SELECT课程编号FROM课程WHERE）
SELECT学生编号FROM选课成绩
WHERE课程编号IN
（SELECT课程编号FROM课程WHERE课程名称="高等数学"or课程名称="英语"）
orderby学生编号
6.联接查询
（1）输出所有学生的成绩单，要求给出学生编号、、课程编号、课程名称和成绩。
SELECTa.学生编号,,b.课程编号,课程名称,成绩
FROM学生a,选课成绩b,课程c
WHEREa.学生编号=b.学生编号And
SELECTa.学生编号,,b.课程编号,课程名称,成绩
FROM学生a,选课成绩b,课程c
WHEREa.学生编号=b.学生编号
（2）列出团员学生的选课情况，要求列出学生编号、、课程编号、课程名称和成绩。
SELECTa.学生编号,a.,b.课程编号,课程名称,成绩
FROM学生a,选课成绩b,课程c
WHEREa.学生编号=b.学生编号And
SELECTa.学生编号,,b.课程编号,课程名称,成绩
FROM学生a,选课成绩表b,课程c
WHEREa.学生编号=b.学生编号anda.团员否
（3）求选修“101”课程的女生的平均年龄。
SELECTAvg（年龄）AS平均年龄FROM学生,选课成绩
WHERE学生.学生编号=选课成绩.学生编号And
SELECTAVG（年龄）AS平均年龄FROM学生,选课成绩表
WHERE学生.学生编号=选课成绩表.学生编号AND学生.性别="女"and选课成绩表.课程编号="101"
7.联合查询
对“教学管理”数据库，列出选修“101”或“102”课程的所有学生的学生编号和，要求建立联合查询。
SELECT学生.学生编号,学生.FROM选课成绩,学生
WHERE课程编号="101"And选课成绩.学生编号=学生.学生编号
UNIONSELECT学生.学生编号,学生.FROM选课成绩,学生
WHERE课程编号="102"And__________________________
SELECT学生.学生编号,学生.FROM选课成绩表,学生
WHERE课程编号="101"AND选课成绩表.学生编号=学生.学生编号
UNIONSELECT学生.学生编号,学生.FROM选课成绩表,学生
WHERE课程编号="102"AND选课成绩表.学生编号=学生.学生编号
案例六：
SQL数据定义
1.建立表结构
要求：
在“教学管理”数据库中建立“教师情况”表结构：
包括编号，，性别，基本工资，出生年月，研究方向字段，其中出生年月允许为空值。
SQL语句如下：
CREATETABLE教师情况（编号Char（7）,Char（8）,性别Char
（2）,
基本工资Money,出生年月DatetimeNull,研究方向Text（50））
2.修改表结构
要求：
对“课程”表的结构进行修改，完善SQL语句。
（1）为“课程”表增加一个整数类型的“学时”字段。
ALTERTABLE课程_____学时Smallint
ALTERTABLE课程add学时Smallint
（2）删除“课程”表中的“学时”字段。
ALTERTABLE课程
ALTERTABLE课程drop学时
3.删除表
要求：
在“教学管理”数据库中删除已建立的“教师情况”表，完善SQL语句。
DROP______教师情况
dropTABLE教师情况
4.插入记录
要求：
向“学生”表中添加记录，学生编号为“1101”，为“会”，入校日期为2012年9月1日，完善SQL语句。
INSERTINTO学生（学生编号,,入校日期）
VALUES（）
INSERTINTO学生（学生编号,,入校日期）
VALUES（"1101","会",#2012/9/1#）
5.更新记录
要求：
完善对“教学管理”数据库进行如下操作的语句。
（1）将“学生”表中“叶飞”同学的住址改为“”。
UPDATE学生SET住址=""WHERE
UPDATE学生SET学生.住址=""
WHERE（（（学生.[]）="叶飞"））;
（2）将所有团员学生的成绩加2分。
完善SQL语句。
UPDATE选课成绩SET
WHERE学生编号IN（SELECT学生编号FROM学生WHERE团员否）
UPDATE选课成绩表SET成绩=成绩+2
WHERE学生编号IN（SELECT学生编号FROM学生WHERE团员否）
6.删除记录
要求：
完善对“教学管理”数据库进行如下操作的语句。
（1）删除“学生”表所有男生的记录。
DELETEFROM学生WHERE
DELETEFROM学生WHERE学生.性别="男"
（2）删除“选课成绩”表中成绩小于60的记录。
Delete__。
DELETEFROM选课成绩表WHERE成绩<60
升级会员 