摘要
第3章数据检索与查询文件实验
本章内容本章介绍了查询的基本概念、查询的功能分类,以及使用Access 2016数据库查询工具创建各种不同查询文件的一般过程。内容主要包括:
？ 查询的概念、功能和分类。
？ 选择查询的功能和创建方法。
？ 参数查询的功能和创建方法。
？ 操作查询的功能和创建方法。
？ 其他特殊查询的功能和创建方法。
实验3.1设计选择、参数查询
实验要点？ 熟悉Access 2016创建查询文件的操作界面,掌握创建查询的一般过程。
？ 熟练掌握各种选择查询的创建方法,会正确设置查询设计网格来解决实际问题。
？ 学会正确使用表达式和函数。
？ 熟练掌握汇总查询结果的方法。
？ 掌握参数查询的设计方法和运行方法。
实验内容与操作提示
本章所有实验均在“图书销售.accdb”数据库和“教学管理.accdb”数据库中实现。
【例3？1】创建一个查询文件,查询图书的订购信息,包括书号、书名、单价、顾客号、订购日期和数量,并将查询文件命名为查询1。
(1) 打开查询文件设计视图,在“显示表”对话框中选择查询数据源: “图书”和“销售”表。
(2) 在查询设计视图设计网格中的“字段”网格里,依次添加所需字段,如图3？1所示。
图3？1查询设计
(3) 保存并将查询文件命名为“查询1”。单击“查询工具”选项卡上的“设计”,单击“结果”组中的“运行”按钮,结果如图3？2所示。
图3？2查询结果
第3章数据检索与查询文件实验数据库系统设计实践指导与测试（Access 2016版）【例3？2】在“查询1”的查询结果中,添加顾客名,保存为“查询2”。
(1) 在导航窗格的“查询1”上单击右键,选择设计视图。
(2) 单击“开始”选项卡→“另存为”→“对象另存为”,在弹出的“另存为”对话框中将新查询命名为“查询2”,如图3？3所示。
图3？3“另存为”对话框
(3) 在查询设计视图上方数据源区域的空白位置单击右键,选择“显示表”,弹出“显示表”对话框。
(4) 在“显示表”对话框中选择需要添加的数据源: “顾客”表。
(5) 在设计网格中的“字段”网格里,添加顾客表的“顾客名”字段,用鼠标选中设计网格的“顾客名”列,将这一列拖动至“顾客号”列之后,如图3？4所示。
图3？4添加查询字段
(6) 保存并运行查询,结果如图3？5所示。 图3？5查询结果
【例3？3】在“查询2”的查询结果中,只显示“小说”或“百科”类图书的订购信息,查询结果按照图书号升序排列,同一种图书的订购记录按照订购数量降序排列,将该查询保存为“查询3”。
(1) 将“查询2”另存为“查询3”。
(2) 在设计网格中的“字段”网格里,添加图书表的“书类”字段,在该字段下方的“条件”网格中输入查询条件: “小说 Or 百科”。
注意: 可以不必为表达式中的“小说”和“百科”单独加引号,系统在检测到这两个字段是短文本类型后,会自动添加引号。
(3) 将设计网格中“书类”字段的“显示”复选框取消选定状态,表示在查询结果中,书类的条件虽然起作用,但是该字段并不显示。
(4) 将设计网格中“书号”字段的“排序”网格设置为“升序”,将“数量”字段的“排序”网格设置为“降序”,如图3？6所示。
图3？6查询设计
(5) 保存并运行查询。结果如图3？7所示。
图3？7查询结果
【例3？4】在例3？3创建的“查询3”的查询结果中,添加一个新的查询字段“货款”,显示每条记录订购图书的总价,货款=单价×数量。将该查询保存为“查询4”。
(1) 将“查询3”另存为“查询4”。
(2) 在查询设计视图设计网格中添加新字段。在空白的“字段”网格里单击右键,选择“生成器”,输入表达式: “货款: ［单价］？［数量］”,如图3？8所示,单击“确定”按钮。
注意: 表达式中的冒号必须使用英文格式,否则系统将提示“输入的表达式包含无效语法”。
图3？8生成字段表达式
(3) 查询设计网格如图3？9所示。
图3？9查询设计
(4) 保存并运行查询。
【例3？5】对例3？4创建的“查询4”的查询结果进行分类汇总,统计所有图书在2016年以后产生的订购总量和总货款,要求显示总货款在5000～10 000元(包含5000和10 000)的订购信息,结果字段包括书号、书名、单价、订购总量和货款总计,并按照书号升序排列。将该查询保存为“查询5”。
(1) 将“查询4”另存为“查询5”。
(2) 删除设计网格中“顾客号”“顾客名”“书类”列。
(3) 在设计网格上单击右键,在菜单中选择“汇总”,设计网格中多出一个“总计”行。
(4) 在“数量”和“货款”两列的“总计”网格中选择“合计”,表示对这两列求和。在“订购日期”字段的“总计”网格中选择Where,表示该字段将设置查询条件。其余字段的“总计”网格保持默认选项Group By。
(5) 为“货款”字段设置条件。在设计网格“货款”字段的“条件”网格中输入条件: “Between 5000 And 10000”。
(6) 为“订购日期”字段设置条件。在设计网格“订购日期”字段的“条件”网格中输入条件: “>#2016？1？1#”。
注意: 日期表达式中的“#”也可以不输入,当Access 2016检测到该数据为日期类型时,会自动添加井号。
(7) 取消“数量”字段下方的“降序”排序设置。查询设计如图3？10所示。
(8) 保存并运行查询。
图3？10查询设计
思考: 例3？5中,汇总图书订购信息时,为什么要删除设计网格中“顾客号”和“顾客名”列？
【例3？6】按照用户输入的图书类别参数,统计该类图书的个数。将该查询保存为“查询6”。
(1) 创建“查询6”,在“显示表”对话框中选择数据源: “图书”表。
(2) 在设计网格的“字段”网格里,添加所需字段“书类”和“书号”。
(3) 在设计网格中单击右键,在弹出的菜单中选择“汇总”,设计网格中多出一个“总计”行。
(4) 在“书号”字段的“总计”网格中选择“计数”表示统计书号的个数。“书类”字段的“总计”网格保持默认选项Group By。
(5) 为“书类”字段的“条件”网格中输入参数提示信息: ［请输入图书类别: 小说、百科或生活］,查询设计如图3？11所示。
注意: 提示信息必须使用方括号全部括起来,这样,在执行书类字段的条件筛选时,将弹出包含该提示信息的对话框,等待用户输入参数。
(6) 运行查询,将弹出如图3？12所示的参数输入对话框。
图3？11查询设计
图3？12参数输入对话框
(7) 此时可以输入参数进行查询,例如输入“小说”。如果输入小说、百科、生活之外的参数,查询不报错,但没有查询结果。
实验3.2设计操作、特殊查询
实验要点？ 了解操作查询的概念,学会熟练使用生成表查询、追加查询、更新查询和删除查询修改数据库内容。
？ 会使用交叉表查询、查找重复项和不匹配项的方法。
实验内容与操作提示
【例3？7】创建生成表查询。为图书销售数据库生成新数据表,表字段包括书名、出版社、书类、顾客名和数量,表中只包含“小说”类的图书数据。生成的新表命名为“图书销售明细表”。将该查询保存为“查询7”。
(1) 创建查询文件,添加查询数据源“图书”“销售”和“顾客”。
(2) 在设计网格的“字段”网格里,添加所需字段: 书名、出版社、书类、顾客名和数量。
(3) 为“书类”字段的“条件”网格输入查询条件“小说”,查询设计如图3？13所示。
图3？13查询设计
(4) 选择功能区中“查询工具”中的“设计”,在“查询类型”组中单击“生成表”,弹出如图3？14所示的“生成表”对话框。
图3？14“生成表”对话框
(5) 为新表命名,选择将新表保存在当前数据库。此时新表还没有生成,还必须运行查询。
(6) 保存并运行查询,此时在左侧导航区中出现新表的名称,双击表格名,图书销售明细表内容如图3？15所示。可以看到查询结果中保留了书类字段,这是为了和今后追加的其他图书加以区分。
图3？15图书销售明细表
【例3？8】创建追加查询。将“生活”类图书的销售记录追加到例3？7生成的“图书销售明细表”中。将该查询保存为“查询8”。
(1) 将“查询7”另存为“查询8”。
(2) 为“书类”字段的“条件”网格输入查询条件“生活”。
(3) 选择功能区中“查询工具”中的“设计”,在“查询类型”组中单击“追加”,弹出如图3？16所示的“追加”对话框。
图3？16“追加”对话框
(4) 指定要追加的表是“图书销售明细表”,单击“确定”按钮。此时追加还没有实现,还必须运行查询。
(5) 保存并运行查询,查看图书销售明细表内容的变化。
【例3？9】创建更新查询。在“图书销售明细表”中,将顾客“李倩玉”的订购数量增加10%。将该查询保存为“查询9”。
(1) 创建查询,添加数据源“图书销售明细表”
(2) 在设计网格的“字段”网格里,依次添加所需字段: “顾客名”和“数量”。
(3) 为“顾客名”字段的“条件”网格输入查询条件“李倩玉”。
(4) 选择功能区中“查询工具”中的“设计”,在“查询类型”组中单击“更新”,设计网格中的“排序”和“显示”网格消失,出现一个“更新到”网格。
(5) 为“数量”字段的“更新到”网格输入更新表达式: ［数量］？1.1。注意,表达式中的字段名“数量”要加方括号,查询设计如图3？17所示。 (6) 保存并运行查询,查看图书销售明细表内容的变化。
图3？17更新查询设计视图
【例3？10】创建删除查询。在“图书销售明细表”中,将上海译文出版社的图书订购记录删除。将该查询保存为“查询10”。
(1) 创建查询,添加数据源“图书销售明细表”。
(2) 在查询设计视图设计网格中的“字段”网格里,依次添加所需字段: “图书销售明细表.？”和“出版社”。
(3) 为“出版社”字段的“条件”网格输入查询条件“上海译文出版社”。
(4) 选择功能区中“查询工具”中的“设计”,在“查询类型”组中单击“删除”,设计网格中的“排序”和“显示”网格消失,出现一个“删除”网格。
(5) 为“图书销售明细表.？”字段的“删除”网格选定From,为“出版社”字段的“删除”网格选定Where,查询设计如图3？18所示。
(6) 保存并运行查询,查看图书销售明细表内容的变化。
图3？18删除查询设计视图
交叉表是一种不同于数据库二维表结构的数据表,它有行、列两个系列的字段名,行、列字段的交叉项中存储数据。有的课程表用行标题表示第几节课,用列标题表示星期几,交叉项是课程,这就是一种典型的交叉表。
利用向导方式建立交叉表的重要前提是: 行标题、列标题和交叉项数据必须同处在一个基本表或查询中,因此,用向导创建交叉表查询往往要事先组织数据源。
【例3？11】用查询向导创建交叉表查询,统计学生的姓名、选修课程名和考试成绩。
(1) 创建选择查询,查询所有学生的姓名、选修课程名和成绩,命名为“考试情况查询”,查询设计如图3？19所示。
图3？19查询设计
(2) 打开“创建”选项卡的“查询向导”工具,选择“交叉表查询向导”。
(3) 选择用查询作为交叉表的数据源,选定“查询: 考试情况查询”,如图3？20(a)所示。
(4) 选择姓名为行标题,课程名为列标题,如图3？20(b)和图3？20(c)所示。选择成绩为交叉项,因为交叉项的成绩是专享的,因此选用汇总函数“平均值”“第一个”“最后一个”等都可以,如图3？20(d)所示。
(5) 确定标题,保存该查询,如图3？20(e)所示。
(6) 运行查询,查询结果如图3？20(f)所示。
图3？20用向导创建交叉表查询
图3？20(续)
查找重复项查询向导,可以在表中找到一个或多个字段接近相同的记录数。【例3？12】分别查找每个系男生、女生的人数。 (1) 打开“创建”选项卡中的“查询向导”工具,选择“查找重复项查询向导”,如图3？21(a)所示。
(2) 在数据源选择面板中选择“表”单选项,并在组合框中选择“表: 学生”,如图3？21(b)所示。
图3？21查找重复项查询向导1
图3？21(续)
(3) 选择包含重复信息的字段,在本例中应为系号和性别,如图3？21(c)所示。
(4) 选择要显示的其他字段,如果没有请不要选择,直接单击“下一步”按钮,如图3？21(d)所示。
(5) 确定该查询的标题,保存查询,如图3？21(e)所示。运行该查询,查询结果如图3？21(f)所示。该表第三列显示了每个系男女生的人数。
查找不匹配项查询向导,可以在表中找到与其他表中的信息不匹配的记录。
【例3？13】查找哪些课程没有人选修过。
(1) 打开“创建”选项卡中的“查询向导”工具,选择“查找不匹配项查询向导”,如图3？22(a)所示。
(2) 选择数据源,即查询的结果在哪一个表中,这里选择“表: 课程”,如图3？22(b)所示。
(3) 选择要和哪一个表比较不匹配项,在本例中应为选课成绩表,如图3？22(c)所示。
(4) 选择两张表要按照什么标准比较,在本例中比较的是“课程表的课程号在选课成绩表中存不存在”,如图3？22(d)所示。
(5) 选择查询结果中要显示什么字段,如图3？22(e)所示。
图3？22查找重复项查询向导2图3？22（续）
(6) 确定该查询的标题,保存查询,如图3？22(f)所示。运行该查询,查询结果如图3？22(g)所示。该结果显示的四门课程都没有人选修。第4章结构化查询语言实验
本章内容本章介绍了关系型数据库通用的语言SQL(Structured Query Language),直译为结构化查询语言。它是所有关系型数据库管理系统都支持的标准语言。内容主要包括:
？ SQL的历史发展,SQL和查询文件的关系,SQL的特点和主要功能。
？ SQL的数据定义功能,相关命令和语法。
？ SQL的数据查询功能,相关命令和语法。
？ SQL的数据操作功能,相关命令和语法。
实验4.1用SQL实现简单查询、连接查询
实验要点？ 掌握使用SQL数据定义语言创建数据表的数据结构。
？ 使用SQL对表格结构进行插入、删除、修改等操作。
？ 掌握删除数据表的SQL命令。
？ 掌握SQL简单查询的语法。
？ 掌握SQL连接查询的基本语法,学会使用连接查询解决实际问题。 ？ 掌握SQL连接查询中连接条件的设计,区分连接条件和查询条件。
实验内容与操作提示
【例4？1】练习使用SQL的CREATE命令,创建三张表格“图书1”“销售1”和“顾客1”。
(1) 打开Access 2016数据库“图书销售.accdb”。
(2) 创建查询文件,为了调试SQL命令要关闭自动弹出的“显示表”对话框。
(3) 在查询设计视图上单击右键,在弹出的快捷菜单中选择“SQL视图”。
(4) 输入SQL命令: CREATE TABLE 图书1 (书号 CHAR(5) PRIMARY KEY, 书名 CHAR(20),
出版社 CHAR(20),书类 CHAR(10), 作者 CHAR(10), 出版日期 DATE,
库存 REAL,单价 REAL)(5) 运行查询,左侧导航区中将出现新的数据表“图书1”。
(6) 保存查询文件,将SQL命令保存在查询文件中。
(7) 仿照上述过程,创建数据表“销售1”和“顾客1”,也分别保存在两个查询文件中。SQL语句如下: CREATE TABLE 销售1 (订单号 CHAR(5), 顾客号 CHAR(5), 书号 CHAR(5), 订购日期 DATE, 数量 REAL, PRIMARY KEY(订单号))
CREATE TABLE 顾客1 (顾客号 CHAR(5) PRIMARY KEY, 顾客名 CHAR(20),电话 CHAR(15))思考与练习
请观察以上语句中主键的声明方式有什么不同。
以上表格创建的时候仅定义了数据结构,并没有定义数据联系,你能修改SQL语句,为数据表创建一对多联系吗？
以上每个SQL语句都必须保存在一个独立的查询文件中,可以把多条SQL语句写入一个查询文件吗？
第4章结构化查询语言实验数据库系统设计实践指导与测试（Access 2016版）【例4？2】添加新字段。练习使用SQL语句,为表“顾客1”添加一个新的“供货地址”字段。
创建查询文件,在SQL视图中输入SQL语句(下面每条SQL命令前均执行此操作): ALTER TABLE 顾客1 ADD COLUMN 供货地址 CHAR(30)【例4？3】修改字段宽度。练习使用SQL语句,将表“顾客1”中的“供货地址”字段大小改为35。ALTER TABLE 顾客1 ALTER 供货地址 CHAR(35)【例4？4】删除字段。练习使用SQL语句,将表“顾客1”中的“供货地址”字段及其中所有数据删除。ALTER TABLE 顾客1 DROP COLUMN 供货地址注意: 以上SQL数据定义语句的结果都应该在表设计视图中查看。
以下SQL数据查询语句的结果都应该在查询文件的数据表视图中查看。
【例4？5】简单查询。使用SQL语句查询图书的书号、书名、单价，结果如图4？1所示。SELECT 图书.书号, 图书.书名, 图书.单价
FROM 图书 打开该查询文件的设计视图,可以看到这条SQL语句对应的设计网格,说明使用SQL语句或者查询文件都能实现本例的查询要求。
【例4？6】简单查询。使用SQL语句查询所有图书的库存总量和平均单价，结果如图4？2所示。SELECT "图书汇总" AS 图书汇总, SUM(图书.库存) AS 库存总量, AVG(图书.单价) AS 平均单价 FROM 图书图4？1查询结果
图4？2查询结果
【例4？7】简单查询。使用SQL语句查询销售表中所有订购日期在2018年以后,且订购数量在100～500册的订购信息,查询结果按照订购数量降序排列，如图4？3所示。SELECT ？
FROM 销售
WHERE 订购日期>=#2018-1-1# AND 数量 BETWEEN 100 AND 500
ORDER BY 数量 DESC图4？3查询结果
【例4？8】简单查询。按照用户输入的顾客号参数,用SQL语句查询某个顾客订购图书的次数。图4？4SQL语句中的参数查询SELECT COUNT(书号) AS 订购次数
FROM 销售
WHERE顾客号=\[请输入要查询的顾客号:\] 运行SQL语句时,弹出如图4？4所示的对话框,提示输入参数。
【例4？9】连接查询。用SQL语句查询B0007号和B0003号图书的订购信息,要求包含顾客名、书名、订购日期和货款,其中，货款=单价×数量,结果如图4？5所示。SELECT 顾客名, 书名, 订购日期, 单价？数量 AS 货款
FROM 图书, 销售, 顾客
WHERE 图书.书号 = 销售.书号 AND 顾客.顾客号 = 销售.顾客号 AND 销售.书号 IN("B0007", "B0003")图4？5查询结果
【例4？10】连接查询。用SQL语句查询同时订购了B0007号和B0003号图书的顾客信息。SELECTa.？, b.？
FROM 销售 a, 销售 b
WHERE a.顾客号= b.顾客号 AND a.书号="B0007" AND b.书号="B0003"该SQL语句将每个顾客订购的图书,按照两两组合的形式输出,查询结果表明,C004号顾客具备B0007号和B0003号图书这种组合，如图4？6所示。
图4？6顾客购书的两两组合
实验4.2用SQL实现嵌套查询、操作查询
实验要点？ 掌握SQL嵌套查询的基本语法,了解嵌套查询和连接查询的异同。
？ 熟练掌握内外层嵌套查询的连接关键词的使用方法。
？ 掌握SQL查询汇总的基本语法和GROUP BY短语和HAVING短语的使用方法;区分WHERE短语和HAVING短语处理条件表达式的区别。
？ 掌握SQL数据操作语句的基本语法。
？ 掌握在SQL数据操作语句中嵌套SELECT语句的方法。
实验内容与操作提示
【例4？11】嵌套查询。用SQL语句查询顾客“赵鸣”订购的图书书名和出版社信息,并保证查询结果没有重复记录，如图4？7所示。SELECT DISTINCT 书名, 出版社
FROM 图书
WHERE 书号 IN(SELECT 书号
FROM 销售
WHERE 顾客号 =(SELECT 顾客号
FROM 顾客
WHERE 顾客名="赵鸣"))图4？7查询结果
思考: 为什么例4？11中两层嵌套的连接关键词,一个用了“IN”而另一个却用了“=”?能不能都用“IN”？能不能都用“=”？能不能交换？
【例4？12】用嵌套查询改写例4？10,查询同时订购了B0007号和B0003号图书的顾客号。SELECT 顾客号
FROM 销售
WHERE 书号="B0007" AND 顾客号 IN(SELECT 顾客号
FROM 销售
WHERE 书号="B0003")或者SELECT 顾客号
FROM 销售
WHERE 书号="B0003" AND 顾客号 IN(SELECT 顾客号
FROM 销售
WHERE 书号="B0007")查询结果表明,C004号顾客同时订购了两种图书。
【例4？13】嵌套查询。用SQL语句查询单价最贵的图书,如图4？8所示。SELECT 图书.？
FROM 图书
WHERE 单价>=ALL(SELECT 单价
FROM 图书)或者SELECT 图书.？
FROM 图书
WHERE 单价=(SELECT MAX(单价)
FROM 图书)图4？8查询结果
思考与练习
本例的基本思想是先算出图书的优选价格,再查找哪本图书的价格与之相等。请练习,要查询单价大力度优惠的图书,SQL语句该怎样写?有几种写法？
【例4？14】嵌套查询。用SQL语句查询从没被订购过的图书,如图4？9所示。SELECT 图书.？
FROM 图书
WHERE 书号 NOT IN (SELECT DISTINCT 书号
FROM 销售)图4？9查询结果
【例4？15】用SQL语句改写例3？5,要求统计所有图书在2016年以后产生的订购总量和总货款,要求显示总货款为5000～10 000元(包含5000和10 000)的订购信息,结果字段包括书号、书名、单价、订购总量和货款总计,并按照书号升序排列。SELECT 图书.书号, 书名, 单价, SUM(数量) AS 数量之合计, SUM(单价？数量) AS 货款
FROM 图书, 销售, 顾客
WHERE 图书.书号 = 销售.书号 AND 顾客.顾客号 = 销售.顾客号 AND 订购日期>#2016-1-1#
GROUP BY 图书.书号, 书名, 单价
