ACCESS实验-数据查询.doc

完整)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）运行查询查看结果。
图2—7不带条件的统计查询
２．创建带条件的统计查询
要求:统计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）单击“查询工具/设计”——“显示/隐藏”组上的“汇总”按钮，插入一个“总计"行设置“性别”字段的“总计”行为“Group By"， “年龄”字段的“总计"行分别设置成最大值、最小值和平均值,查询的设计窗口如图2—9所示。
（4)单击保存按钮，在“查询名称”文本框中输入“统计男女生年龄”。
（5）运行查询,查看结果.

案例四：创建SQL查询
要求：对“教师"表进行查询，显示全部教师信息。
操作步骤:
（1)在设计视图中创建查询，不添加任何表，在“显示表”对话框中直接单击“关闭”按钮，进入空白的查询设计视图。
(2）单击 “查询类型",单击“SQL视图”按钮(也可以鼠标右键单击查询1选项卡），进入SQL视图。如图2-10所示
图2-10 SQL查询菜单
(3）在SQL视图中输入以下语句：SELECT * FROM 教师.
（4）保存查询“SQL查询”。
（5)单击“运行”按钮，显示查询结果。
案例五：对教学管理数据库进行SQL查询，完善SQL语句
1．SQL简单查询
(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 NULL
3.排序
（1)按性别顺序列出学生编号、姓名、性别、年龄及住址,性别相同的再按年龄由小到大排序。
SELECT 学生编号,姓名,性别,年龄,住址 FROM 学生 ORDER BY
SELECT 学生编号,姓名，性别，年龄，住址 FROM 学生 ORDER BY 性别 desc,年龄 ASC
（2）将学生成绩降序排序，只显示前30%的记录。
SELECT FROM 选课成绩 ORDER BY 成绩
SELECT TOP 30 percent ＊ from 选课成绩 ORDER BY 成绩 desc
4。分组查询
(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 平均成绩
FROM 选课成绩
GROUP BY 课程编号 HAVING
ORDER BY AVG(成绩) ASC

SELECT 课程编号,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，1

SELECT 学生编号,COUNT（课程编号) AS 选课门数
FROM 选课成绩
GROUP BY 学生编号 HAVING COUNT(课程编号)>1
ORDER BY 2 DESC,1

5.嵌套查询
（1)列出选修“高等数学”的所有学生的学生编号。
SELECT 学生编号 FROM 选课成绩 WHERE 课程编号=
（SELECT FROM 课程 WHERE 课程名称="高等数学")

SELECT 学生编号 FROM 选课成绩 WHERE 课程编号=
(SELECT 课程编号 FROM 课程 WHERE 课程名称="高等数学"）

（2）列出选修“101”课的学生中成绩比选修“105”的最低成绩高的学生编号和成绩。
SELECT 学生编号,成绩 FROM 选课成绩
WHERE 课程编号="101"And 成绩〉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
(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,课程 c
WHERE a.学生编号=b。学生编号 And

SELECT a。学生编号，姓名，b。课程编号,课程名称,成绩
FROM 学生 a，选课成绩 b，课程 c
WHERE a.学生编号=b.学生编号

（2）列出团员学生的选课情况，要求列出学生编号、姓名、课程编号、课程名称和成绩.
SELECT a.学生编号,a。姓名，b.课程编号,课程名称,成绩
FROM 学生 a,选课成绩 b，课程 c
WHERE a.学生编号=b.学生编号 And

SELECT a.学生编号，姓名,b。课程编号，课程名称，成绩
FROM 学生 a，选课成绩表 b,课程 c
WHERE 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 课程 _____ 学时 Smallint
ALTER 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