ACCESS实验数据查询.docx

1、ACCESS实验数据查询ACCESS实验查询一、实验目的1. 掌握各种查询的创建方法2. 掌握查询条件的表示方法3. 掌握应用SQL中SELECT语句进行数据查询的方法4. 理解SQL中数据定义和数据操纵语句二、实验容与要求1创建各种查询2使用SQL中SELECT语句进行数据查询3使用SQL语句进行数据定义和数据操纵三、实验步骤案例一：利用“简单查询向导”创建选择查询1单表选择查询要求：以“教师”表为数据源，查询教师的和职称信息，所建查询命名为“教师情况”。操作步骤：（1）打开“教学管理.accdb”数据库，单击“创建”选项卡，“查询”组单击“查询向导”弹出“新建查询”对话框。如图2-1所示。

2、图2-1创建查询（2）在“新建查询”对话框中选择“简单查询向导”，单击“确定”按钮，在弹出的对话框的“表与查询”下拉列表框中选择数据源为“表:教师”，再分别双击“可用字段”列表中的“”和“职称”字段，将它们添加到“选定的字段”列表框中，如图2-2所示。然后单击“下一步”按钮，为查询指定标题为“教师情况”，最后单击“完成”按钮。图2-2简单查询向导2多表选择查询要求：查询学生所选课程的成绩，并显示“学生编号”、“”、“课程名称”和“成绩”字段。操作步骤：（1）打开“教学管理.accdb”数据库，在导航窗格中，单击“查询”对象，单击“创建”选项卡，“查询”组单击“查询向导”弹出“新建查询”对话框。

3、（2）在“新建查询”对话框中选择“简单查询向导”，单击“确定”按钮，在弹出的对话框的“表与查询”。先选择查询的数据源为“学生”表，并将“学生编号”、“”字段添加到“选定的字段”列表框中，再分别选择数据源为“课程”表和“选课成绩”表，并将“课程”表中的“课程名称”字段和“选课成绩”表中的“成绩”字段添加到“选定的字段”列表框中。选择结果如图2-3所示。图2-3多表查询（3）单击“下一步”按钮，选“明细”选项。（4）单击“下一步”按钮，为查询指定标题“学生选课成绩”，选择“打开查询查看信息”选项。（5）单击“完成”按钮，弹出查询结果。注：查询涉及“学生”、“课程”和“选课成绩”3个表，在建查询前要

4、先建立好三个表之间的关系。案例二：在设计视图中创建选择查询1创建不带条件的选择查询要求：查询学生所选课程的成绩，并显示“学生编号”、“”、“课程名称”和“成绩”字段。操作步骤：（1）打开“教学管理.accdb”数据库，在导航窗格中，单击“查询”对象，单击“创建”选项卡，“查询”组单击“查询设计”，出现”表格工具/设计”选项卡，如图2-4查询工具。同时打开查询设计视图，如图2-5所示。图2-4查询工具（2）在“显示表”对话框中选择“学生”表，单击“添加”按钮，添加学生表，同样方法，再依次添加“选课成绩”和“课程”表。（3）双击学生表中“学生编号”、“”、课程表中“课程名称”和选课成绩表中“成绩”

5、字段，将它们依次添加到“字段”行的第14列上。图2-5查询设计器（4）单击快速工具栏“保存”按钮，在“查询名称”文本框中输入“选课成绩查询”，单击“确定”按钮。（5）选择“开始/视图”“数据表视图”菜单命令，或单击“查询工具/设计”“结果”上的“运行”按钮，查看查询结果。查看查询结果。2创建带条件的选择查询要求：查找2008年9月1日入校的男生信息，要求显示“学生编号”、“”、“性别”、“团员否”字段容。操作步骤：（1）在设计视图中创建查询，添加“学生”表到查询设计视图中。（2）依次双击“学生编号”、“”、“性别”、“团员否”、“入校日期”字段，将它们添加到“字段”行的第15列中。（3）单击“

6、入校日期”字段“显示”行上的复选框，使其空白，查询结果中不显示入校日期字段值。（4）在“性别”字段列的“条件”行中输入条件“男”，在“入校日期”字段列的“条件”行中输入条件#2008-9-1#，设置结果如图2-6所示。图2-6带条件的查询（5）单击保存按钮，在“查询名称”文本框中输入“2008年9月1日入校的男生信息”，单击“确定”按钮。（6）单击“查询工具/设计”“结果”上的“运行”按钮，查看查询结果。案例三：创建计算查询1创建不带条件的统计查询要求：统计学生人数。操作步骤：（）在设计视图中创建查询，添加“学生”表到查询设计视图中。（）双击“学生编号”字段，添加到“字段”行的第1列中。（）单

7、击“查询工具/设计”“显示/隐藏”组上的“汇总”按钮，插入一个“总计”行，单击“学生编号”字段的“总计”行右侧的向下箭头，选择“计数”函数，如图2-7所示。（4）单击“保存”按钮，在“查询名称”文本框中输入“统计学生人数”。（5）运行查询，查看结果。创建带条件的统计查询要求：统计2008年入学的男生人数。操作步骤：（1）在设计视图中创建查询，添加“学生”表到查询设计视图中。（2）双击“学生编号”、“性别”和“入校日期”字段，将它们添加到“字段”行的第13列中。（3）单击“性别”、“入校日期”字段“显示”行上的复选框，使其空白。（4）单击“查询工具/设计”“显示/隐藏”组上的“汇总”按钮，插入一

8、个“总计”行，单击“学生编号”字段的“总计”行右侧的向下箭头，选择“计数”函数，“性别”和“入校日期”字段的“总计”行选择“where”选项。（5）在“性别”字段列的“条件”行中输入条件“男”；在“入校日期”字段列的“条件”行中输入条件Year(入校日期)=2008，如图2-8所示。图2-8带条件的统计查询（6）单击保存按钮，在“查询名称”文本框中输入“统计2008年入学的男生人数”。（7）运行查询，查看结果。3创建分组统计查询要求：统计男、女学生年龄的最大值、最小值和平均值。操作步骤：（1）在设计视图中创建查询，添加“学生”表到查询设计视图中。（2）字段行第1列选“性别”，第2列到第4列选“

9、年龄”。图2-9分组统计查询（3）单击“查询工具/设计”“显示/隐藏”组上的“汇总”按钮，插入一个“总计”行，设置“性别”字段的“总计”行为“Group By”，“年龄”字段的“总计”行分别设置成最大值、最小值和平均值，查询的设计窗口如图2-9所示。（4）单击保存按钮，在“查询名称”文本框中输入“统计男女生年龄”。（5）运行查询，查看结果。案例四：创建SQL查询要求：对“教师”表进行查询，显示全部教师信息。操作步骤：（1）在设计视图中创建查询，不添加任何表，在“显示表”对话框中直接单击“关闭”按钮，进入空白的查询设计视图。（2）单击“查询类型”，单击“SQL视图”按钮（也可以鼠标右键单击查询1

10、选项卡），进入SQL视图。如图2-10所示（3）在SQL视图中输入以下语句：SELECT * FROM 教师。（4）保存查询“SQL查询”。（5）单击“运行”按钮，显示查询结果。案例五：对教学管理数据库进行SQL查询，完善SQL语句1SQL简单查询（1）对“课程”表进行查询，显示课程全部信息SELECT_FROM 课程SELECT * FROM 课程（2）列出前5个教师的和工龄。SELECT ,Year(Date()-Year(工作时间) AS 工龄 FROM 教师SELECT top 5 ,year(date()-year(工作时间) as 工龄 from 教师;（3）求出所有教师的平均年龄

11、。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 学生 WHERE 住址 In SELECT 学生.学生编号, 学生., 学生

12、.住址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 LIKE *（6）列出所有成绩为空值的学生编号和课程编号。SELEC

13、T 学生编号,课程编号 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 percent * from 选课成绩 ORDER B

14、Y 成绩 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 课程编号,Avg(成绩) AS 平均成绩

15、FROM 选课成绩GROUP BY 课程编号 HAVING ORDER BY Avg(成绩) ASCSELECT 课程编号,AVG(成绩) AS 平均成绩FROM 选课成绩GROUP BY 课程编号 HAVING AVG(成绩)75 ORDER BY AVG(成绩) ASC（4）统计每个学生选修课程的门数（超过1门的学生才统计），要求输出学生编号和选修门数，查询结果按选课门数降序排列，若门数相同，按学生编号升序排列。SELECT 学生编号,Count(课程编号) AS 选课门数FROM 选课成绩GROUP BY 学生编号 HAVING ORDER BY 2 DESC,1SELECT 学生编号,

16、COUNT(课程编号) AS 选课门数FROM 选课成绩GROUP BY 学生编号 HAVING COUNT(课程编号)1 ORDER BY 2 DESC,15.嵌套查询（1）列出选修“高等数学”的所有学生的学生编号。SELECT 学生编号 FROM 选课成绩 WHERE 课程编号=(SELECT FROM 课程 WHERE 课程名称=高等数学)SELECT 学生编号 FROM 选课成绩 WHERE 课程编号=(SELECT 课程编号 FROM 课程 WHERE 课程名称=高等数学)（2）列出选修“101”课的学生中成绩比选修“105”的最低成绩高的学生编号和成绩。SELECT 学生编号,成绩

17、 FROM 选课成绩WHERE 课程编号=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 学生编号,成绩 F

18、ROM 选课成绩WHERE 课程编号=101 And 成绩all (SELECT 成绩 FROM 选课成绩 WHERE 课程编号=105)（4）列出选修“高等数学”或“英语”的所有学生的学生编号。SELECT 学生编号 FROM 选课成绩WHERE 课程编号In(SELECT 课程编号 FROM 课程 WHERE )SELECT 学生编号 FROM 选课成绩WHERE 课程编号 IN(SELECT 课程编号 FROM 课程 WHERE 课程名称=高等数学 or 课程名称=英语)order by 学生编号6.联接查询（1）输出所有学生的成绩单，要求给出学生编号、课程编号、课程名称和成绩。SELE

19、CT a.学生编号,b.课程编号,课程名称,成绩FROM 学生 a,选课成绩 b,课程 cWHERE a.学生编号=b.学生编号 And SELECT a.学生编号,b.课程编号,课程名称,成绩FROM 学生 a,选课成绩 b,课程 cWHERE a.学生编号=b.学生编号（2）列出团员学生的选课情况，要求列出学生编号、课程编号、课程名称和成绩。SELECT a.学生编号,a.,b.课程编号,课程名称,成绩FROM 学生 a,选课成绩 b,课程 cWHERE a.学生编号=b.学生编号 AndSELECT a.学生编号,b.课程编号,课程名称,成绩FROM 学生 a,选课成绩表 b,课程 cW

20、HERE a.学生编号=b.学生编号 and a.团员否（3）求选修“101”课程的女生的平均年龄。SELECT Avg(年龄) AS 平均年龄 FROM 学生,选课成绩WHERE 学生.学生编号=选课成绩.学生编号And SELECT AVG(年龄) AS 平均年龄 FROM 学生,选课成绩表WHERE 学生.学生编号=选课成绩表.学生编号 AND 学生.性别=女 and 选课成绩表.课程编号=1017.联合查询对“教学管理”数据库，列出选修“101”或“102”课程的所有学生的学生编号和，要求建立联合查询。SELECT 学生.学生编号,学生. FROM 选课成绩,学生WHERE 课程编号=

21、101 And 选课成绩.学生编号=学生.学生编号UNION SELECT 学生.学生编号,学生. FROM 选课成绩,学生WHERE 课程编号=102 And _SELECT 学生.学生编号,学生. FROM 选课成绩表,学生WHERE 课程编号=101 AND 选课成绩表.学生编号=学生.学生编号UNION SELECT 学生.学生编号,学生. FROM 选课成绩表,学生WHERE 课程编号=102 AND 选课成绩表.学生编号=学生.学生编号案例六：SQL数据定义1.建立表结构要求：在“教学管理”数据库中建立“教师情况”表结构：包括编号，性别，基本工资，出生年月，研究方向字段，其中出生年

22、月允许为空值。SQL语句如下：CREATE TABLE 教师情况(编号 Char(7), Char(8),性别 Char(2),基本工资 Money,出生年月 Datetime Null,研究方向 Text(50)2.修改表结构要求：对“课程”表的结构进行修改，完善SQL语句。（1）为“课程”表增加一个整数类型的“学时”字段。ALTER TABLE 课程 _ 学时 SmallintALTER TABLE 课程 add 学时 Smallint（2）删除“课程”表中的“学时”字段。ALTER TABLE 课程ALTER TABLE 课程 drop 学时3.删除表要求：在“教学管理”数据库中删除已建

23、立的“教师情况”表，完善SQL语句。DROP _ 教师情况drop TABLE 教师情况4.插入记录要求：向“学生”表中添加记录，学生编号为“1101”，为“会”，入校日期为2012年9月1日，完善SQL语句。INSERT INTO 学生(学生编号,入校日期) VALUES()INSERT INTO 学生(学生编号,入校日期) VALUES(1101,会,#2012/9/1#)5.更新记录要求：完善对“教学管理”数据库进行如下操作的语句。（1）将“学生”表中“叶飞”同学的住址改为“”。UPDATE 学生 SET 住址= WHERE UPDATE 学生 SET 学生.住址 = WHERE (学生

24、.)=叶飞);（2）将所有团员学生的成绩加2分。完善SQL语句。UPDATE 选课成绩 SET WHERE 学生编号 IN (SELECT 学生编号 FROM 学生 WHERE 团员否)UPDATE 选课成绩表 SET 成绩=成绩+2 WHERE 学生编号 IN (SELECT 学生编号 FROM 学生 WHERE 团员否)6.删除记录要求：完善对“教学管理”数据库进行如下操作的语句。（1）删除“学生”表所有男生的记录。DELETE FROM 学生 WHERE DELETE FROM 学生WHERE 学生.性别=男（2）删除“选课成绩”表中成绩小于60的记录。Delete _。DELETE FROM 选课成绩表 WHERE 成绩60