ACCESS实验-数据查询.docx

ACCESS实验-数据查询ACCESS实验-数据查询 编辑整理：尊敬的读者朋友们：这里是精品文档编辑中心，本文档内容是由我和我的同事精心编辑整理后发布的，发布之前我们对文中内容进行仔细校对，但是难免会有疏漏的地方，但是任然希望（ACCESS实验-数据查询）的内容能够给您的工作和学习带来便利。同时也真诚的希望收到您的建议和反馈，这将是我们进步的源泉，前进的动力。本文可编辑可修改，如果觉得对您有帮助请收藏以便随时查阅，最后祝您生活愉快 业绩进步，以下为ACCESS实验-数据查询的全部内容。ACCESS实验 查询一、实验目的1. 掌握各种查询的创建方法2. 掌握查询条件的表示方法3. 掌握应用SQL中SELECT语句进行数据查询的方法4。 理解SQL中数据定义和数据操纵语句二、实验内容与要求1创建各种查询2使用SQL中SELECT语句进行数据查询3使用SQL语句进行数据定义和数据操纵三、实验步骤案例一：利用“简单查询向导”创建选择查询1单表选择查询要求：以“教师”表为数据源，查询教师的姓名和职称信息，所建查询命名为“教师情况”。操作步骤：（1)打开“教学管理。accdb”数据库，单击“创建”选项卡，“查询组-单击“查询向导”弹出“新建查询对话框。如图21所示。图21创建查询(2）在“新建查询”对话框中选择“简单查询向导”，单击“确定按钮，在弹出的对话框的“表与查询下拉列表框中选择数据源为“表：教师,再分别双击“可用字段”列表中的“姓名”和“职称”字段，将它们添加到“选定的字段”列表框中，如图22所示。然后单击“下一步按钮，为查询指定标题为“教师情况”，最后单击“完成”按钮.图2-2简单查询向导2多表选择查询要求：查询学生所选课程的成绩,并显示“学生编号”、“姓名”、“课程名称”和“成绩字段。操作步骤:(1)打开“教学管理.accdb”数据库，在导航窗格中，单击“查询对象，单击“创建”选项卡,“查询组-单击“查询向导”弹出“新建查询对话框。(2）在“新建查询”对话框中选择“简单查询向导，单击“确定”按钮，在弹出的对话框的“表与查询”。先选择查询的数据源为“学生”表，并将“学生编号”、“姓名”字段添加到“选定的字段”列表框中,再分别选择数据源为“课程”表和“选课成绩”表，并将“课程”表中的“课程名称”字段和“选课成绩”表中的“成绩”字段添加到“选定的字段”列表框中。选择结果如图2-3所示。图2-3多表查询（3）单击“下一步按钮,选“明细”选项。（4）单击“下一步”按钮,为查询指定标题“学生选课成绩”，选择“打开查询查看信息”选项。（5）单击“完成按钮，弹出查询结果。注：查询涉及“学生”、“课程”和“选课成绩”3个表，在建查询前要先建立好三个表之间的关系。案例二:在设计视图中创建选择查询1创建不带条件的选择查询要求：查询学生所选课程的成绩,并显示“学生编号”、“姓名”、“课程名称”和“成绩”字段。操作步骤：（1）打开“教学管理.accdb”数据库，在导航窗格中,单击“查询”对象,单击“创建”选项卡，“查询组单击“查询设计”,出现”表格工具/设计”选项卡，如图2-4查询工具。同时打开查询设计视图,如图25所示.图24查询工具(2)在“显示表”对话框中选择“学生”表，单击“添加按钮，添加学生表，同样方法，再依次添加“选课成绩”和“课程”表。（3)双击学生表中“学生编号”、“姓名”、课程表中“课程名称”和选课成绩表中“成绩”字段,将它们依次添加到“字段行的第14列上。图25查询设计器(4）单击快速工具栏 “保存”按钮，在“查询名称”文本框中输入“选课成绩查询”,单击“确定”按钮.（5)选择“开始/视图”“数据表视图菜单命令，或单击“查询工具/设计”“结果”上的“运行按钮，查看查询结果。查看查询结果。2创建带条件的选择查询要求：查找2008年9月1日入校的男生信息，要求显示“学生编号”、“姓名”、“性别”、“团员否字段内容。操作步骤:(1)在设计视图中创建查询，添加“学生”表到查询设计视图中。(2）依次双击“学生编号、“姓名、“性别、“团员否、“入校日期”字段，将它们添加到“字段”行的第15列中。(3）单击“入校日期”字段“显示”行上的复选框，使其空白，查询结果中不显示入校日期字段值.（4）在“性别”字段列的“条件”行中输入条件“男”，在“入校日期”字段列的“条件行中输入条件#2008-9-1，设置结果如图2-6所示。图2-6带条件的查询（5)单击保存按钮,在“查询名称”文本框中输入“2008年9月1日入校的男生信息，单击“确定”按钮。(6）单击“查询工具/设计”“结果”上的“运行”按钮,查看查询结果.案例三：创建计算查询1创建不带条件的统计查询要求：统计学生人数。操作步骤：(）在设计视图中创建查询，添加“学生”表到查询设计视图中。()双击“学生编号”字段，添加到“字段”行的第1列中。（）单击“查询工具/设计”-“显示/隐藏”组上的“汇总按钮，插入一个“总计”行，单击“学生编号”字段的“总计”行右侧的向下箭头,选择“计数”函数，如图27所示。（4）单击“保存”按钮,在“查询名称”文本框中输入“统计学生人数”。(5）运行查询，查看结果。图2-7不带条件的统计查询创建带条件的统计查询要求:统计2008年入学的男生人数.操作步骤：（1）在设计视图中创建查询，添加“学生”表到查询设计视图中。(2）双击“学生编号”、“性别和“入校日期字段，将它们添加到“字段”行的第13列中.(3）单击“性别、“入校日期”字段“显示”行上的复选框，使其空白。（4）单击“查询工具/设计”“显示/隐藏”组上的“汇总”按钮，插入一个“总计行，单击“学生编号”字段的“总计”行右侧的向下箭头,选择“计数函数,“性别”和“入校日期”字段的“总计”行选择“where选项。（5）在“性别”字段列的“条件”行中输入条件“男”；在“入校日期”字段列的“条件”行中输入条件Year（入校日期)=2008，如图2-8所示。图28带条件的统计查询（6）单击保存按钮，在“查询名称”文本框中输入“统计2008年入学的男生人数”.（7）运行查询，查看结果。3创建分组统计查询要求：统计男、女学生年龄的最大值、最小值和平均值。操作步骤：（1)在设计视图中创建查询，添加“学生”表到查询设计视图中.(2）字段行第1列选“性别”，第2列到第4列选“年龄”。图29分组统计查询(3）单击“查询工具/设计”“显示/隐藏组上的“汇总按钮，插入一个“总计”行,设置“性别”字段的“总计”行为“Group By”， “年龄字段的“总计行分别设置成最大值、最小值和平均值，查询的设计窗口如图29所示。（4）单击保存按钮，在“查询名称”文本框中输入“统计男女生年龄”。(5)运行查询，查看结果。案例四:创建SQL查询要求：对“教师”表进行查询，显示全部教师信息。操作步骤：(1）在设计视图中创建查询，不添加任何表,在“显示表”对话框中直接单击“关闭”按钮，进入空白的查询设计视图。（2)单击 “查询类型”,单击“SQL视图”按钮(也可以鼠标右键单击查询1选项卡），进入SQL视图.如图210所示（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）求出所有教师的平均年龄。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 学生.学生编号， 学生。姓名, 学生。住址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）列出所有成绩为空值的学生编号和课程编号。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 percent 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 政治面貌Any （SELECT 成绩 FROM 选课成绩 WHERE 课程编号=”105”）（3）列出选修“101”课的学生，这些学生的成绩比选修“105”课的最高成绩还要高的学生编号和成绩。SELECT 学生编号,成绩 FROM 选课成绩WHERE 课程编号=101 And 成绩All （SELECT FROM 选课成绩 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 课程名称=”英语”）order by 学生编号6.联接查询（1）输出所有学生的成绩单，要求给出学生编号、姓名、课程编号、课程名称和成绩。SELECT 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。学生编号 And SELECT a.学生编号,姓名，b.课程编号，课程名称,成绩FROM 学生 a,选课成绩表 b,课程 cWHERE a。学生编号=b。学生编号 and a.团员否（3）求选修“101”课程的女生的平均年龄。SELECT AVG(年龄) AS 平均年龄 FROM 学生,选课成绩WHERE 学生.学生编号=选课成绩.学生编号 AND SELECT AVG(年龄） AS 平均年龄 FROM 学生,选课成绩表WHERE 学生。学生编号=选课成绩表.学生编号 AND 学生.性别=”女 and 选课成绩表。课程编号=”101”7.联合查询对“教学管理数据库，列出选修“101”或“102课程的所有学生的学生编号和姓名，要求建立联合查询。SELECT 学生。学生编号，学生.姓名 FROM 选课成绩,学生WHERE 课程编号=”101” AND 选课成绩。学生编号=学生。学生编号UNION SELECT 学生.学生编号，学生。姓名 FROM 选课成绩，学生WHERE 课程编号=”102 AND _SELECT 学生.学生编号，学生。姓名 FROM 选课成绩表，学生WHERE 课程编号=101” AND 选课成绩表.学生编号=学生。学生编号UNION SELECT 学生。学生编号,学生。姓名 FROM 选课成绩表，学生WHERE 课程编号=”102” AND 选课成绩表.学生编号=学生。学生编号案例六：SQL数据定义1.建立表结构要求：在“教学管理数据库中建立“教师情况”表结构：包括编号，姓名,性别,基本工资，出生年月，研究方向字段,其中出生年月允许为空值.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.删除表要求:在“教学管理”数据库中删除已建立的“教师情况”表,完善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 (学生。姓名）=”叶飞）);(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