第3章查询


【实验目的】
(1) 掌握表达式的计算。
(2) 掌握SQL命令的操作。
(3) 掌握选择查询和动作查询。

【实验环境】
(1) 台式计算机或笔记本电脑。
(2) Access 2016软件环境。

【实验内容】
某学校设计“项目管理”系统，包括“学院”表、“专业”表、“学生”表、“教师”表、“项目”表和“项目分工”表，每个表包含的字段如下： 
学院(学院编号，学院名称，院长，办公电话)
专业(专业编号，专业名称，专业类别，学院编号)
学生(学号，姓名，性别，生日，民族，籍贯，专业编号，简历，登记照)
教师(工号，姓名，性别，职称，学院编号)
项目(项目编号，项目名称，项目类别，立项日期，完成年限，经费，是否完成，指导教师工号)
项目分工(学号，项目编号，分工)
(1) 完成不同类型数据的表达式运算。
(2) 使用SQL命令进行查询和操作。
(3) 利用查询设计视图创建选择查询和交叉表查询。
(4) 动作查询操作。
 实验3.1完成不同类型数据的表达式运算
【实验要求】
(1) 掌握Access 2016表达式和函数。
(2) 掌握SQL视图及在不同视图间切换。


图3.1视图列表

【实验步骤】
(1) 进入SQL视图及在不同视图间切换。
在Access中打开“项目管理”数据库。
单击“创建”选项卡“查询”组中的“查询设计”按钮，弹出查询设计窗口及“显示表”对话框。关闭“显示表”对话框，选择“设计”→“结果”→“SQL视图”命令，进入SQL视图窗口。
在SQL视图中输入SQL命令并单击“运行”按钮，就可以查看运行结果。如果需要在不同的视图之间切换，可单击“视图”的下拉按钮，则弹出所有视图列表，用户可在其中选择切换，如图3.1所示。
(2) 不同类型数据的表达式运算。
在SQL视图中分别输入以下命令，分别进入数据表视图查看结果。


SELECT-5.12^2+(17 mod 3);

SELECT"Hello "+",World!",TRIM(" 清华大学 "),LEFT("清华大学出版社",2)+RIGHT("清华大学出版社",3);

SELECT"你毕业的年份是", VAL(LEFT(［你的学号］,4))+4;

SELECT "现在是"+STR(YEAR(DATE()))+"年","现在是"+STR(MONTH(DATE()))+"月","现在的时间是： "+STR(TIME()));

SELECT "张三">"李四","ABCD"<"abcd",(DATE()-#1992-10-8#)>1000;












 实验3.2使用SQL命令进行查询和操作
【实验要求】
(1) 掌握SQL中SELECT语句的主要应用。
(2) 掌握SQL的插入、更新、删除操作命令的基本应用。
(3) 了解SQL定义数据表的基本方法。
(4) 理解查询对象的意义和建立方法。
【实验步骤】
(1) 练习SQL查询的SELECT语句。
打开“项目管理”数据库窗口，进入SQL视图。
在SQL视图中输入以下SELECT命令，查看执行结果，并仔细体会查询的实现。
① 查询“学院”“专业”“学生”表的完整数据。


SELECT * 

FROM ((学院 INNER JOIN 专业 ON 学院.学院编号=专业.学院编号) 

INNER JOIN 学生 ON 专业.专业编号=学生.专业编号);





② 查询“工商管理”专业所有女生信息。



SELECT 专业名称,学生.* 

FROM 专业 INNER JOIN 学生 ON 专业.专业编号=学生.专业编号

WHERE 专业.专业名称="工商管理" AND 学生.性别="女";




③ 查询作为“项目负责人”的学生的学号、姓名、性别。保存为“负责人”查询。



SELECT 学生.学号,姓名,性别

FROM 学生 INNER JOIN 项目分工 ON 学生.学号=项目分工.学号

WHERE 分工="负责人" ;




单击“保存”按钮，弹出“另存为”对话框，输入“负责人”，单击“确定”按钮。
④ 查询没有参与项目的学生的学号、姓名、专业名称。



SELECT 学号,姓名,专业名称

FROM 学生 INNER JOIN 专业 ON 学生.专业编号=专业.专业编号

WHERE 学号 NOT IN (SELECT 学号 FROM 项目分工);




⑤ 查询参与项目超过一项的学生的学号、姓名和参与项目数。



SELECT 学生.学号,姓名,COUNT(*)

FROM 学生 INNER JOIN 项目分工 ON 学生.学号=项目分工.学号

GROUP BY 学生.学号,姓名

HAVING COUNT(*)>1;




⑥ 查询参与项目最多的学生的学号、姓名和参与项目数。



SELECT TOP 1 学生.学号,姓名,COUNT(*) AS 参与项目数

FROM 学生 INNER JOIN 项目分工 ON 学生.学号=项目分工.学号

GROUP BY 学生.学号,姓名

ORDER BY COUNT(*) DESC;




⑦ 查询与农村或农业有关的项目及负责人姓名。



SELECT 项目.*,姓名

FROM (学生 INNER JOIN 项目分工 ON 学生.学号=项目分工.学号)

INNER JOIN 项目 ON 项目.项目编号=项目分工.项目编号

WHERE 分工="负责人" AND (项目名称 LIKE "*农业*" OR 项目名称 LIKE "*农村*");




(2) 练习SQL的创建表、插入、更新、删除操作命令。
① 在“教师”表中添加一个新教工信息。



INSERT INTO 教师 VALUES("Z09031","杨飞","男","讲师","09");




② 将“校级”项目的经费增加1000元。



UPDATE 项目

SET 经费=经费+1000WHERE 项目类别="校级";





③ 创建“已结项项目”表，包括项目编号、项目名称、项目类别、指导教师工号、负责人学号。



CREATE TABLE 已结项项目

(项目编号 TEXT(10) PRIMARY KEY,

项目名称 TEXT(60) NOT NULL,

指导教师工号 TEXT(8) REFERENCES 教师(工号),

负责人学号 TEXT(8) REFERENCES 学生(学号) );





④ 将已结项的项目转入“已结项项目”表，然后删除“已结项”的项目数据。
首先，执行如下语句。



INSERT INTO 已结项项目(项目编号,项目名称,指导教师工号,负责人学号)

SELECT 项目.项目编号,项目名称,指导教师工号,学号

FROM 项目 INNER JOIN 项目分工 ON 项目.项目编号=项目分工.项目编号

WHERE 分工="负责人" AND 是否结项;




然后，执行如下语句。



DELETE FROM 项目

WHERE 是否结项;






  实验3.3利用查询“设计视图”创建选择查询和交叉表查询


【实验要求】
(1) 掌握查询设计视图。
(2) 理解Access选择查询的意义和类别。
(3) 掌握一般选择查询的操作应用。
(4) 理解并掌握交叉查询的应用。
(5) 理解参数查询的意义。
【实验步骤】
(1) 设置选择查询。
打开“项目管理”数据库窗口，然后进入设计视图。
① 查询“专业”表，显示开设的所有专业涉及的学科门类。
通过“显示表”对话框将“专业”表加入设计视图。在设计网格中“字段”栏选择“专业类别”字段并选中“显示”复选框。因为该字段的值有重复，因此，进入“属性表”对话框，选择“唯一值”的属性值为“是”。
② 查询所有专业涉及的学科门类，以及每个学科门类开设的专业数，并仅显示开设专业数为2个以上的学科门类及其专业数信息，显示信息为专业类别、专业数。
通过“显示表”对话框将“专业”表加入设计视图。在设计网格中“字段”栏选择“专业类别”和“专业编号”字段并选中“显示”复选框。然后，单击工具栏中的“汇总”按钮增加“总计”栏，将“专业类别”字段设置为“Group By”，将“专业编号”字段设置为“计数”。在“专业编号”字段的“条件”栏输入“>2”。最后，在“专业编号”的“字段”栏的“专业编号”前面加上“专业数:”作为查询后的列名。设计完成，如图3.2所示。



图3.2选择查询设计视图1


③ 查询各专业学生的人数。
将“专业”表和“学生”表加入“设计视图”。在“字段”栏选中“专业编号”和“专业名称”字段并选中“显示”复选框，然后，单击工具栏中的“汇总”按钮增加“总计”栏。将“专业编号”和“专业名称”字段设置为“Group By”，然后选择“学生”表的“学号”字段，设置其为“计数”，最后，在“学号”前面加上“人数:”作为查询后的列名。设计完成，如图3.3所示。



图3.3选择查询设计视图2


④ 查询18岁以上男学生人数超过3人的各专业信息，显示输出信息为符合条件专业的专业名称和18岁以上男学生人数。
将“专业”表和“学生”表加入设计视图。在“字段”栏选中专业表的“专业编号”、学生表的“学号”和“性别”字段并选中“显示”复选框，然后单击工具栏中的“汇总”按钮增加“总计”栏。将“专业编号”字段设置为“Group By”，然后选择“学生”表的“学号”字段，设置其为“计数”。在“学号”字段的“条件”栏输入“>3”； 在“性别”字段的“总计”栏选择“Where”选项，并在“条件”栏输入"男"。并在“字段”栏中增加一个计算字段“年龄: Year(Date())-Year(［生日］)”，在该字段的“总计”栏选择“Where”选项, 并在“条件”栏输入“>18”。最后，在“学号”前面加上“18岁以上男学生人数:”作为查询后的列名。设计完成，如图3.4所示。



图3.4选择查询设计视图3


⑤ 查询没有参与项目的男学生学号、姓名，即“项目分工”表中没有记录的学生。
设计过程如图3.5所示。将“学生”表加入设计视图。选择“学号”“姓名”字段并选中“显示”复选框，在第3列处输入“注： ‘未参与项目’”，选中“显示”复选框。然后，在第4列中选择“学号”但不显示，只作为比较的对象。在“条件”栏输入一个子查询“NOT IN (SELECT 学号 FROM 项目分工)”。


图3.5选择查询设计视图设计子查询


设置完毕，运行结果如图3.6所示。


图3.6选择查询数据表视图


(2) 设置交叉表查询。
① 查询每位学生在各个项目中的分工情况。
两类实体多对多联系可设置交叉查询。将学生的“学号”和“姓名”作为行标题，“项目编号”作为列标题，“分工”作为交叉数据，生成交叉表。
在查询设计视图，添加“学生”和“项目分工”表。在设计窗格中添加“学号”“姓名”“项目编号”“分工”字段。
单击“交叉表”按钮，添加“总计”栏和“交叉表”栏。在“交叉表”栏设置“学号”和“姓名”作为行标题，“项目编号”作为列标题，“分工”作为“值”，在“总计”栏设置分工为“First”。这样交叉表查询就设计完毕，如图3.7所示。



图3.7查询设计视图中设计交叉表1


运行查询，可以看到交叉表查询的效果，如图3.8所示。



图3.8交叉表查询结果1


② 查询每位学生在各个项目中的分工情况，并对每位学生参与的项目数进行统计。
和上面类似，此处针对每个学生仅增加了一个统计信息——参与项目总数，那么在交叉表中如何进行设计呢？
在查询设计视图中设计交叉表，如图3.9所示。在上面的基础上，在查询设计视图中，在“字段”栏中增加“项目编号”字段，并在“总计”栏选择“计数”，“交叉表”栏设置为“行标题”。最后在“项目编号”前面加上“项目计数:”作为查询后的列名。设计完成，查询结果如图3.10所示。



图3.9查询设计视图中设计交叉表2




图3.10交叉表查询结果2


(3) 设置参数查询。
通过参数查询指定日期以后出生的某个民族的学生信息。
将“学生”表加入查询设计视图，在设计窗格中选择“*”表示输出“学生”表所有字段。然后，选中“生日”字段，不选中“显示”复选框，在条件行输入“>［SR］”； 同样设置“民族”字段。
接下来，单击“参数”按钮，弹出“查询参数”对话框，分别设置“SR”和“MZ”的类型，单击“确定”按钮，设置完毕，如图3.11所示。



图3.11在查询设计视图中设置查询参数


 实验3.4动作查询操作
【实验要求】
(1) 理解Access动作查询包含的查询类别。
(2) 理解并掌握动作查询的操作应用。
(3) 将动作查询与SQL命令进行对比。
【实验步骤】
(1) 生成表查询操作。
创建“已结项项目”表，包括项目编号、项目名称、项目类别、指导教师工号、负责人学号。
进入查询设计视图，添加“项目”和“项目分工”表。设置“项目编号”“项目名称”“指导教师工号”“学号”“是否结项”字段并设置相应条件。然后单击“生成表”按钮，弹出“生成表”对话框。操作如图3.12所示。输入生成表的名称，单击“确定”按钮。运行查询，结果被保存到当前数据库中。
由于生成表中有指导教师工号和学生学号，因此可到关系图窗口中建立相应的参照。



图3.12定义生成表查询


生成的新表如图3.13所示。



图3.13“已结项项目”表


(2) 删除查询操作。
删除“项目”表中已结项的项目数据。
进入查询设计视图，加入“项目”表。单击“删除”按钮，这时设计窗格栏目发生变化，出现“删除”栏。添加“［是否结项］”字段并设置“删除”栏为“Where”，对应“条件”为“True”，直接单击“运行”按钮即可，如图3.14所示。



图3.14删除查询设计操作


(3) 追加查询操作。
追加查询是将一个查询的结果追加插入到一个现有表中。将本实验的生成表查询操作通过追加查询完成。
在查询设计视图中添加“项目”和“项目分工”表。设置“项目编号”“项目名称”“指导教师工号”“学号”“是否结项”字段并设置相应条件，单击“追加”按钮，弹出“追加”对话框。输入“已结项项目2”，如图3.15所示，单击“确定”按钮。这时，设计窗格中增加“追加到”栏并显示相关的字段名。单击“运行”按钮，完成数据记录的追加。



图3.15“追加”对话框



(4) 更新查询操作。
将“校级”项目经费增加1000元，操作如下。
在设计视图中添加“项目”表。单击“更新”按钮，设计窗格增加“更新到”栏。
添加“经费”字段，然后在“更新到”栏中输入“［经费］+1000”。
添加“项目类别”字段，在“条件”栏中输入“校级”，设计完成，如图3.16所示。
单击“运行”按钮运行查询，即可完成“项目”表的更新操作。



图3.16更新查询操作