《ACCESS实验-数据查询.docx》由会员分享，可在线阅读，更多相关《ACCESS实验-数据查询.docx（12页珍藏版）》请在三一办公上搜索。
1、ACCESS实验查询一、实验目的1。掌握各种查询的创建方法2. 掌握查询条件的表示方法3. 掌握应用SQL中SELECT语句进行数据查询的方法4。理解SQL中数据定义和数据操纵语句二、实验内容与要求1. 创建各种查询2. 使用SQL中SELECT语句进行数据查询3. 使用SQL语句进行数据定义和数据操纵三、实验步骤案例一：利用“简单查询向导创建选择查询1. 单表选择查询要求：以“教师”表为数据源，查询教师的姓名和职称信息，所建查询命名为“教师情况”。操作步骤：(1) 打开“教学管理。accdb数据库，单击“创建”选项卡，“查询”组单击“查询向导”弹出“新 建查询对话框。如图2-1所示。倒建蛙库
2、二具文件查商向导查商设计呈序部，表设计 SharePoint列表图21创建查询(2) 在“新建查询对话框中选择“简单查询向导”，单击“确定”按钮，在弹出的对话框的“表与查询” 下拉列表框中选择数据源为“表：教师”，再分别双击“可用字段”列表中的“姓名”和“职称”字段，将 它们添加到“选定的字段列表框中，如图22所示。然后单击“下一步按钮，为查询指定标题为“教师 情况”，最后单击“完成”按钮。图22简单查询向导2. 多表选择查询要求：查询学生所选课程的成绩，并显示“学生编号”、“姓名”、“课程名称”和“成绩字段。操作步骤：(1) 打开“教学管理accdb数据库，在导航窗格中，单击“查询”对象，单
3、击“创建”选项卡，“查询” 组单击“查询向导”弹出“新建查询”对话框。(2) 在“新建查询对话框中选择“简单查询向导”，单击“确定按钮，在弹出的对话框的“表与查询 .先选择查询的数据源为“学生表，并将“学生编号”、“姓名”字段添加到“选定的字段”列表框中，再 分别选择数据源为“课程表和“选课成绩表，并将“课程”表中的“课程名称”字段和“选课成绩”表中 的“成绩”字段添加到“选定的字段”列表框中。选择结果如图23所示。图2-3多表查询(3) 单击“下一步按钮，选明细选项.(4) 单击“下一步”按钮，为查询指定标题“学生选课成绩”，选择“打开查询查看信息选项.(5) 单击“完成”按钮，弹出查询结果
4、。(完整)ACCESS实验-数据查询 注：查询涉及“学生”、“课程”和“选课成绩”3个表，在建查询前要先建立好三个表之间的关系。案例二:在设计视图中创建选择查询1. 创建不带条件的选择查询要求：查询学生所选课程的成绩，并显示“学生编号”、“姓名”、“课程名称和“成绩”字段。操作步骤：(1) 打开“教学管理。accdb”数据库，在导航窗格中，单击“查询”对象，单击“创建选项卡，“查 询”组-单击“查询设计”，出现表格工具/设计”选项卡，如图24查询工具。同时打开查询设计视图， 如图25所示。卧心I萩字冒理：(Access 2007) - Microsofil Accew创注 外部散据游茎工兵扣我
5、项W 冲! J s! +! A H对皿卧 媛 运行 曲兰抨 Siir再并交受袁N野*左直遂乒皿行甘敬y 誓毗表软件厅S砂.汇白喟表必#生础勘凌回AJI .图2-4查询工具(2) 在“显示表”对话框中选择“学生”表，单击“添加”按钮，添加学生表，同样方法，再依次添 加“选课成绩”和“课程”表.(3) 双击学生表中“学生编号”、“姓名、课程表中“课程名称”和选课成绩表中“成绩”字段，将 它们依次添加到“字段”行的第14列上.学生探程r*8学瞄年柴入校日期 冒败ID学/ftE课程名称浬 1114/1字段：学生编号姓名课程名称成箴-表：学生学生课程选课成绩排序：曰,BHB条件：或：图25查询设计器(4
6、) 单击快速工具 .，栏“保存按钮，在“查询名称”文本框中输入“选课成绩查询”，单击“确定”按钮。(5) 选择“开始/视图”T“数据表视图”菜单命令，或单击“查询工具/设计“结果”上的“运 行按钮，查看查询结果。查看查询结果。2. 创建带条件的选择查询要求：查找2008年9月1日入校的男生信息，要求显示“学生编号”、“姓名、“性别”、“团员否 字段内容。操作步骤：(1) 在设计视图中创建查询，添加“学生表到查询设计视图中.(2) 依次双击“学生编号”、“姓名”、“性别”、“团员否、“入校日期”字段，将它们添加到“字 段行的第15列中。(3) 单击“入校日期”字段“显示”行上的复选框，使其空白，
7、查询结果中不显示入校日期字段值。(4) 在“性别字段列的“条件”行中输入条件“男，在入校日期”字段列的“条件行中输入条件# 2008-91#,设置结果如图2-6所示.茸1查询1.学生%毋学尝海惆！1年镣入校日期团走5|_| 1字段：学生编号姓名性别团员否入校日期表：学生学生学生学生学生排序：显示：凰条件:穿#2006-9-1#或：图2-6带条件的查询(5) 单击保存按钮，在“查询名称文本框中输入“2008年9月1日入校的男生信息，单击“确定按钮。(6) 单击“查询工具/设计”“结果上的“运行”按钮，查看查询结果。字段：学生编号学生LJ 1 士 显示,BrP 值值值 皿计均小大 Gr合平最景计数
8、变星FirstLastEwpr tiEEi ciTlWhere按钮，插 头，选择生人数.案例三：创建计算查询1. 创建不带条件的统计查询要求：统计学生人数。操作步骤：(1) 在设计视图中创建查询，添加“学生”表到查询设计视图中。(2) 双击“学生编号”字段，添加到“字段”行的第1列中.(3) 单击“查询工具/设计”“显示/隐藏组上的“汇总” 入一个“总计”行，单击“学生编号”字段的“总计”行右侧的向下箭“计数”函数，如图27所示。(4) 单击“保存”按钮，在“查询名称”文本框中输入“统计学(5) 运行查询，查看结果。2. 创建带条件的统计查询要求：统计2008年入学的男生人数。图27不带条件的
9、统计查询操作步骤：(1) 在设计视图中创建查询，添加“学生”表到查询设计视图中.(2) 双击“学生编号”、“性别”和“入校日期字段，将它们添加到“字段”行的第13列中.(3) 单击“性别、“入校日期”字段“显示行上的复选框，使其空白。(4) 单击“查询工具/设计”-“显示/隐藏”组上的“汇总”按钮，插入一个“总计行，单击“学生 编号”字段的“总计”行右侧的向下箭头，选择“计数”函数，“性别和“入校日期”字段的“总计”行 选择“ where选项。(5) 在“性别”字段列的“条件行中输入条件“男”；在“入校日期”字段列的“条件”行中输入条 件Year(入校日期)二2008，如图2-8所示.亨舞计励
10、年入学的男生人数学生%1 3珞学-年疑u入校日期宇段学生编号性别入校日期表学生学生学生总计t十数隹Where排序显示S口条件丫*京校日期)壬。明或图2-8带条件的统计查询(6) 单击保存按钮，在“查询名称”文本框中输入“统计2008年入学的男生人数”。(7) 运行查询，查看结果。3. 创建分组统计查询要求：统计男、女学生年龄的最大值、最小值和平均值.操作步骤：(1) 在设计视图中创建查询，添加“学生”表到查询设计视图中。(2) 字段行第1列选“性别”，第2列到第4列选“年龄”.茸1查跳学生*学=年龄入校日期团员否4匝字段:性别学生总计：Group By最尢值最小值平均值排序：1=1 l .21
11、E 小.u圈凰凰条件：图29分组统计查询(3) 单击“查询工具/设计”“显示/隐藏”组上的“汇总”按钮，插入一个“总计行，设置“性 别”字段的“总计”行为“Group By, “年龄”字段的“总计行分别设置成最大值、最小值和平均值，查 询的设计窗口如图29所示。(4) 单击保存按钮，在“查询名称”文本框中输入“统计男女生年龄”。(5) 运行查询，查看结果.案例四：创建SQL查询要求：对“教师表进行查询，显示全部教师信息。操作步骤：(1) 在设计视图中创建查询，不添加任何表，在“显示表”对话框中直接单击“关闭”按钮，进入空白 的查询设计视图。(2) 单击“查询类型，单击“SQL视图”按钮(也可以
12、鼠标右键单击查询1选项卡)，进入SQL视图。如 图2-10所示(3) 在SQL视图中输入以下语句：SELECT * FROM教师.(4) 保存查询“SQL查询”。(5) 单击“运行”按钮，显示查询结果。全部矢闭CQ案例五：对教学管理数据库进行SQL查询，完善SQL语句1. SQL简单查询(1) 对“课程”表进行查询，显示课程全部信息图2-10 SQL查询菜单SELECT FROM 课程SELECT * FROM 课程(2) 列出前5个教师的姓名和工龄.FROM教师SELECT 姓名，Year (Date ()Year (工作时间)AS 工龄from教师;SELECT top 5 姓名，year
13、 (date () )-year (工作时间)as 工龄(3) 求出所有教师的平均年龄。SELECT AS平均年龄FROM教师SELECT avg (年龄)AS平均年龄FROM教师2。带条件查询(1) 列出成绩在80分以上的学生记录。SELECT 大 FROM 选课成绩 WHERE SELECT大FROM选课成绩WHERE成绩80(2) 求出四川住址的学生平均年龄。SELECT AVG(年龄)AS平均年龄FROM学生WHERE SELECT AVG (年龄)AS平均年龄FROM学生WHERE住址LIKE ”四川大”(3) 列出四川和上海住址的学生名单。SELECT学生编号，姓名，住址FROM学
14、生WHERE住址IN SELECT学生。学生编号，学生.姓名，学生.住址FROM学生WHERE (学生.住址)LIKE 四川*”)OR (学生。住址)LIKE 北京*”)； ?WHERE LEFT (住址，2) IN (四川，北京”)(4) 列出成绩在80分到100分之间的学生名单。SELECT学生编号，成绩FROM选课成绩WHERE成绩BETWEEN SELECT学生编号，成绩FROM选课成绩WHERE成绩BETWEEN 80 AND 100(5) 列出所有的姓“张的学生名单。SELECT学生编号，姓名FROM学生WHERE姓名LIKE SELECT学生编号，姓名FROM学生WHERE姓名L
15、IKE 张*(6) 列出所有成绩为空值的学生编号和课程编号.SELECT学生编号，课程编号FROM选课成绩WHERE成绩 SELECT学生编号，课程编号FROM选课成绩WHERE成绩IS NULL3. 排序(1) 按性别顺序列出学生编号、姓名、性别、年龄及住址，性别相同的再按年龄由小到大排序。SELECT学生编号，姓名，性别，年龄，住址FROM学生ORDER BY SELECT学生编号，姓名，性别，年龄，住址FROM学生ORDER BY性别desc,年龄ASC（2）将学生成绩降序排序，只显示前30%的记录。SELECT FROM 选课成绩 ORDER BY 成绩 SELECT TOP 30 p
16、ercent 大 from 选课成绩 ORDER BY 成绩 desc4。分组查询（1）分别统计“学生”表中男女生人数.SELECT性别，COUNT （大）AS人数FROM学生 SELECT 性别，COUNT（*） AS 人数 FROM 学生 GROUP BY 性别（2）按性别统计“教师”表中政治面目为非党员的人数。SELECT 性别，COUNT（*） AS 人数 FROM 教师 WHERE GROUP BY 性别SELECT性别，COUNT（大）AS人数FROM教师WHERE政治面貌党员GROUP BY性别（3）列出平均成绩大于75分的课程编号，并按平均成绩升序排序。SELECT课程编号,A
17、VG（成绩）AS平均成绩FROM 选课成绩GROUP BY 课程编号 HAVING ORDER BY AVG（成绩）ASCSELECT课程编号,AVG（成绩）AS平均成绩FROM选课成绩GROUP BY 课程编号 HAVING AVG（成绩）75ORDER BY AVG（成绩）ASC（4）统计每个学生选修课程的门数（超过1门的学生才统计），要求输出学生编号和选修门数，查询结果 按选课门数降序排列，若门数相同，按学生编号升序排列。SELECT学生编号，COUNT （课程编号）AS选课门数FROM 选课成绩GROUP BY 学生编号 HAVING ORDER BY 2 DESC, 1SELECT学
18、生编号，COUNT （课程编号）AS选课门数FROM选课成绩GROUP BY学生编号HAVING COUNT（课程编号）1ORDER BY 2 DESC,15. 嵌套查询（1）列出选修“高等数学”的所有学生的学生编号。SELECT学生编号FROM选课成绩WHERE课程编号二（SELECT FROM课程WHERE课程名称二高等数学）SELECT学生编号FROM选课成绩WHERE课程编号二（SELECT课程编号FROM课程WHERE课程名称二高等数学）（2）列出选修“101 ”课的学生中成绩比选修“105”的最低成绩高的学生编号和成绩。SELECT学生编号，成绩FROM选课成绩WHERE课程编号二
19、101And成绩Any（SELECT FROM选课成绩WHERE课程编号二”105）SELECT学生编号，成绩FROM选课成绩WHERE课程编号二”101 And成绩Any（SELECT成绩FROM选课成绩WHERE课程编号二”105”）（3）列出选修“101 ”课的学生，这些学生的成绩比选修“ 105”课的最高成绩还要高的学生编号和成 绩。SELECT学生编号，成绩FROM选课成绩WHERE课程编号二”101” And成绩All（SELECT FROM选课成绩WHERE课程编号二”105”）SELECT学生编号，成绩FROM选课成绩WHERE课程编号二”101” And成绩all（SELEC
20、T成绩FROM选课成绩WHERE课程编号二”105” ）（4）列出选修“高等数学”或“英语”的所有学生的学生编号。SELECT学生编号FROM选课成绩WHERE课程编号IN（SELECT课程编号FROM课程WHERE ）SELECT学生编号FROM选课成绩WHERE课程编号IN（SELECT课程编号FROM课程WHERE课程名称二”高等数学”or课程名称二”英语”）order by学生编号6。联接查询（1）输出所有学生的成绩单，要求给出学生编号、姓名、课程编号、课程名称和成绩。SELECT a.学生编号，姓名，b .课程编号，课程名称，成绩FROM学生a,选课成绩b,课程cWHERE a.学生
21、编号二b。学生编号And SELECT a。学生编号，姓名，b。课程编号，课程名称，成绩FROM学生a,选课成绩b,课程cWHERE a.学生编号二b.学生编号（2）列出团员学生的选课情况，要求列出学生编号、姓名、课程编号、课程名称和成绩.SELECT a.学生编号，a。姓名，b.课程编号，课程名称，成绩FROM学生a,选课成绩b,课程cWHERE a.学生编号二学生编号AndSELECT a.学生编号，姓名，b。课程编号，课程名称，成绩FROM学生a,选课成绩表b,课程cWHERE a.学生编号二b。学生编号and a.团员否（3）求选修“ 101”课程的女生的平均年龄。SELECT AVG
22、 （年龄）AS平均年龄FROM学生，选课成绩WHERE学生.学生编号二选课成绩。学生编号AND SELECT AVG （年龄）AS平均年龄FROM学生，选课成绩表WHERE学生。学生编号二选课成绩表.学生编号AND 学生。性别二”女” and选课成绩表.课程编号二”101”7。联合查询对“教学管理”数据库，列出选修“101 ”或“102”课程的所有学生的学生编号和姓名，要求建立联合 查询.SELECT学生。学生编号，学生.姓名FROM选课成绩，学生WHERE课程编号二”101” AND选课成绩。学生编号二学生.学生编号UNION SELECT学生。学生编号，学生.姓名FROM选课成绩，学生WH
23、ERE 课程编号二102” ANDSELECT学生。学生编号，学生.姓名FROM选课成绩表，学生WHERE课程编号二”101” AND选课成绩表。学生编号二学生。学生编号UNION SELECT学生.学生编号，学生。姓名FROM选课成绩表，学生WHERE课程编号二”102” AND 选课成绩表.学生编号二学生。学生编号案例六:SQL数据定义1. 建立表结构要求：在“教学管理”数据库中建立“教师情况”表结构：包括编号，姓名，性别，基本工资，出生年 月，研究方向字段，其中出生年月允许为空值.SQL语句如下：CREATE TABLE 教师情况(编号 Char(7),姓名 Char(8),性别 Cha
24、r(2),基本工资Money，出生年月Datetime Null,研究方向Text (50)2. 修改表结构要求：对“课程”表的结构进行修改，完善SQL语句。(1) 为“课程”表增加一个整数类型的“学时”字段。ALTER TABLE 课程 学时 SmallintALTER TABLE 课程 add 学时 Smallint(2) 删除“课程”表中的“学时字段。ALTER TABLE 课程 ALTER TABLE 课程 drop 学时3. 删除表要求：在“教学管理”数据库中删除已建立的“教师情况”表，完善SQL语句。DROP 教师情况drop TABLE教师情况4. 插入记录要求：向“学生”表中添
25、加记录，学生编号为“ 1101”,姓名为“张会”，入校日期为2012年9月1日， 完善SQL语句。INSERT INTO学生(学生编号，姓名，入校日期)VALUES()INSERT INTO学生(学生编号，姓名，入校日期)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成绩=成绩+2WHERE学生编号IN (SELECT学生编号FROM学生WHERE团员否)6。删除记录要求：完善对“教学管理”数据库进行如下操作的语句。(1) 删除“学生”表所有男生的记录.DELETE FROM 学生 WHERE DELETE FROM学生WHERE学生。性别二男”(2) 删除“选课成绩”表中成绩小于60的记录.DELETE.DELETE FROM选课成绩表WHERE成绩60
