数据库系统应用上机实验指导书

1、数据库系统应用上机实验指导书重庆邮电学院经济管理学院上 机 实 验 指 导 书 系 列数据库系统应用胡大权 编写计算机信息管理实验中心二五年八月- II -前 言数据库系统应用是非计算机类专业的一门基础课，在理论教学中，多数侧重于理论讲解，轻实践操作，鉴于此，特编写本书用于上机实验指导。选用Access作为软件平台是因为Access基本上能体现数据库的所有理论知识，而且，目前单位企业日常办公中广泛使用Access。本书结合数据库理论教学内容以一个数据库应用系统设计和完善贯穿全书，内容精简实用，语言简练，条理清晰，操作性强。由于编者水平和篇幅有限，虽几经修订改编，书中难免会有欠妥之处，恳请读者批

2、评指正。编者2008年5月目 录前 言I目 录II实验一 数据库设计1实验二 创建数据库5实验三 表及数据操作12实验四 查询设计21实验五 复杂查询26实验六 SQL语句练习30实验六 窗体37实验七 报表43实验八 宏48实验九 VBA （选做）53实验十 数据库安全 （选做）54实验十一 综合实验55- II -数据库系统应用上机实验指导书实验一 数据库设计实验目的1、 掌握数据库设计的方法和步骤实验内容1、 分析超市管理系统数据库；2、 进行概念结构设计，并画出E-R图；3、 进行逻辑结构设计，将E-R图转换为关系模型；4、 设计各关系中的属性实验步骤1 需求分析数据库需求分析是整个设

3、计过程的基础，在分析阶段，设计者通过调查、询问等方法了解业务流程、用户的实际要求，与用户达成共识；了解用户能提供哪些数据，要实现哪些功能，并以数据流图、数据字典描述出来，这需要用户密切配合合作。本实验假设性分析超市管理系统的主要功能为：根据收银台终端传送来的销售数据和库存数据进行分析以掌握销售情况、库存情况，为管理者提供决策依据。如图表 1 高层业务流程图进货管理销售分析用户数据库超市管理系统图表 1 高层业务流程图2 概念结构设计。超市管理系统主要是对柜台的销售数据和库存商品分析、统计，以掌握销售情况，为管理者提供决策依据。本系统是为便于教学而从现实中抽象简化的，省略了需求分析的详细过程，直

4、接给出了E-R图，如图表 2。售货单售货单明细组成商品参照1销售员供货商供应1n11n1n类别属于n1销售1n图表 2E-R图3 逻辑结构设计将概念结构设计的E-R图转换为DBMS产品（即Access）所支持的逻辑结构。Access支持关系模型，因此，要将上面E-R图转换为关系模型。加下划线的为主键。销售员（销售员ID，姓名，性别，职务，地址，城市，邮政编码，家庭电话，出生日期，雇佣日期，照片，备注）供应商（供应商ID，公司名称，联系人姓名，联系人职务，地址，城市，邮编，电话，传真）类别（类别ID，类别名称，说明，图片）商品（商品ID，商品名称，供应商ID，类别ID，单位数量，单价，库存量，警

5、戒库存量）售货单（售货单ID，销售员ID，日期）售货单明细（售货单ID，商品ID，单价，数量，折扣）根据实际情况分别确定以上关系中各个属性（字段）的数据类型、值域范围以及关键字、约束等。下面给出了在Access中各表的字段属性，仅供参考。表格 1销售员表字段名字段类型字段宽度小数位数索引说明销售员ID自动编号长整型主键销售员唯一编号姓名文本10性别文本1职务文本30地址文本50城市文本10邮政编码文本6家庭电话文本20出生日期日期/时间雇佣日期日期/时间照片文本存放照片文件名备注备注表格 2供应商表字段名字段类型字段宽度小数位数索引说明供应商ID自动编号长整型主键供应商唯一编号公司名称文本50

6、联系人姓名文本10联系人职务文本10地址文本50城市文本10邮编文本6电话文本15传真文本15表格 3类别表字段名字段类型字段大小小数位数索引说明类别ID自动编号长整型主键类别唯一编号类别名称文本30说明备注图片OLE 对象存放类别的图片表格 4商品表字段名字段类型字段大小小数位数索引说明商品ID自动编号主键商品唯一编号商品名称文本40供应商ID数字/长整型外键同供应商表类别ID数字/长整型外键同类别表单位数量文本20单价货币库存量数字/整型警戒库存量数字/整型表格 5售货单表字段名字段类型字段大小小数位数索引说明售货单ID自动编号主键售货单唯一编号销售员ID数字/长整型外键同销售员表日期日期

7、/时间表格 6售货单明细表字段名字段类型字段大小小数位数索引说明售货单ID数字主键一张销售单中有多种商品商品ID数字/长整型单价货币数量数字/整型折扣数字/单精度型实验二 创建数据库实验目的1、 熟悉Access的界面和主要功能2、 掌握在Access中创建数据库、表的方法实验内容1、 分析超市管理系统数据库；2、 进行概念结构设计，并画出E-R图；3、 进行逻辑结构设计，将E-R图转换为关系模型；4、 设计各关系中的属性实验步骤1 启动Access目前Access主要有2000，2002即XP，2003三种版本，这几种版本的主要功能一样，本实验指导书是用Access 2003在Windows

8、 XP系统中进行演示讲解的，对于其他版本的Access和操作系统，操作方法类似。方法一 菜单启动：点击“开始”菜单，在“所有程序”中找到“Microsoft Office”，“Microsoft Access 2003”就在“Microsoft Office”菜单下。简洁的描述方式为：开始所有程序Microsoft OfficeMicrosoft Access 2003，本书为了叙述简洁明了，凡多重菜单，均使用此描述方式。例如，在Windows 98下打开Access 2000的步骤为：开始程序Microsoft Access 2000。点击“Microsoft Access 2003”菜单项

9、，即可启动Access。方法二 桌面快捷方式启动：在桌面上找到图标，双击这个图标，即可启动Access。如果桌面上还没有创建此快捷方式，必须先创建该快捷方式。方法三 直接在Access的安装目录下启动：找到并打开Access的安装目录，双击MSACCESS.exe文件。ACCESS启动后如图所示状态栏工具栏工作区菜单栏任务窗口图表 3：ACCESS主界面2 新建空白数据库菜单方式：文件新建工具栏方式：点击工具栏左上角的按钮任务窗口方式：点击“开始工作”任务窗口中下方的“新建文件”使用上面三种方式中任何一种，任务窗口就变成如下“新建文件”窗口。图表 4：新建文件窗口点击“空数据库”，出现如下图所

10、示对话框，选择保存数据库的位置，默认是“我的文档”，在文件名地方输入空数据库的名称，如“超市管理系统”，点击“创建”按钮，空白数据库就创建了。2 输入数据库的文件名1 选择保存数据库的位置3 创建数据库图表 5：创建数据库对话框 空白数据库创建后如下图所示， Access数据库包括表、查询、窗体、报表、宏等对象，注意：这些对象全部保存在数据库文件中，而不是分别保存在不同的文件中。Access数据库扩展名为MDB，数据库的文件信息可以从“文件”“属性”知道，如文件名、位置、大小等。图表 6：新建的数据库3 创建表表是数据库中实际保存数据库的地方。如上图所示，可用多种方法创建，通常使用的是第一种“

11、使用设计器创建表”，点击“设计”按钮或双击“使用设计器创建表”，出现表设计器窗口，按照实验一所设计的各表的字段属性，分别输入字段名称、数据类型、字段大小，本次实验只要求设置这三项，说明是对字段的一个注解，是一个可选部分，下图演示了“销售员表”的创建。选择“销售员ID”，点击工具栏中的按钮，或者用鼠标右击“销售员ID”所在的行，在弹出菜单中点击“主键”菜单，即可将“销售员ID”字段设置为主键。关闭表设计器，系统提示输入表的名称，输入“销售员”，点击“确定”按钮，“销售员”表创建完毕。图表 7：表设计器同样的方法，再将实验一所设计的其他表在Access中创建。创建完后如图所示。2 关闭当前数据库3

12、 关闭Access1 双击某个表，输入数据图表 8：创建完的表现在，双击某个表，进入“数据表视图”，就可以给表输入一些数据。4 表的其他创建方法在“表”对象中，还有两种创建表的方法：“使用向导创建表”和“通过输入数据创建表”。“使用向导创建表”是通过向导一步步的引导，从系统自带的示例表中选择一些字段以组成符合自己需要的表。“通过输入数据创建表”是通过向数据表视图中输入一些数据（一条一条的记录），系统自动识别每个字段中的数据类型、大小以确定表的结构。这两种方法就非常简单，也减少一些工作量，但系统自动创建的表的字段属性往往不符合我们的需求，有可能字段的数据类型不合适，也有可能字段的大小不合适，这还

13、需要结合表设计器进行修改完善。5 数据库及Access的关闭如上图所示，关闭数据库窗口就关闭了当前数据库，可再打开其他数据库。如果不再使用Access，可以关闭Access窗口，同时也就关闭了当前数据库。实验任务1 将实验一设计的所有的表在Access中创建实验报告l 总结设计一个数据库的具体步骤l 依照实验一中的内容，自己设计一个学生成绩管理系统或运动会赛事管理系统（体育专业），先进行相应的需求分析，画出对应得E-R图，并将给出的E-R图转换为关系模型，给出详细的表结构（包括表及每个表中需要的字段的详细信息）l 写出试验二中建立数据库及表的过程l 写出实验过程中遇到的问题及心得体会实验三 表

14、及数据操作实验目的1、 进一步学习表设计器2、 掌握表关系的建立和编辑3、 掌握在表中数据的各种操作方法4、 了解数据及数据表视图格式设置5、 掌握数据的导入、导出和链接实验内容1、 修改完善超市管理系统数据库中的表结构及属性；2、 建立和编辑表之间的关系3、 进行记录的添加、修改、删除、复制等操作；4、 进行数据的选择、删除、剪切、复制、粘贴等操作；5、 设置数据表视图的行高、列宽、字体、字号，单元格效果等格式6、 删除、冻结、隐藏列7、 数据的查找、替换、排序8、 练习数据导入、导出和链接实验步骤1 表字段属性的修改在上一次实验中创建表时，我们只简单的设计了字段的字段名称、数据类型和字段大

15、小三个主要部分。但在字段属性中，还有很多的内容。对于不同的数据类型，“常规”标签里会显示不同的属性，常用的有：格式，输入掩码，标题，默认值，有效性规则，有效性文本，必填字段，索引，输入法模式等。各属性的功能和意义如下：格式：数据显示格式，比如：时间类型的字段，显示为长时间还是短时间，是中国习惯的“年月日”还是英美国家习惯的“月日年”等输入掩码：限制数据输入的模式标题：在窗体中显示的名称，如果为空，则显示字段名，通常用于给用户友好的界面显示默认值：在添加新记录时，系统自动赋的值有效性规则：用于限制输入的逻辑表达式，返回值为真才接受有效性文本：当违反有效性规则时系统提示给用户的信息必填字段：如果该

16、值为真，该字段必填，不能为空索引：用于加快查找的速度和性能输入法模式：当输入该字段时是否需要输入法以及何种输入法举例 销售员表中出生日期的属性如下图：图表 9：出生日期属性设置从上面表中可以看出，销售员的出生日期字段输入格式按中国人熟悉的”年-月-日”格式，销售员的出生日期不能晚于当前日期（当天的日期），如果输入的日期比当前日期还晚，则不能输入，系统显示提示信息：“出生日期不能是将来”在表设计器的字段属性中，“查阅”功能也特别有用，比如销售单表中的“销售员ID”和销售员表中的“销售员ID”相同，在原始的输入方式中，在输入“销售员ID”时，输入的是一个个数字，很容易搞错，如左图，实际我们是要确定

17、该销售单是哪个销售员销售的，而销售单中的“销售员ID”的值只能输入销售员表中已经存在的“销售员ID”；如果在输入“销售员ID”时能根据销售员“姓名”确定就好了，如右图，直接点击输入框右边的下拉箭头，选择“销售员”，虽然看似选择的是销售员姓名，但实际输入的是“销售员ID”。这就是“查阅”的功能，也即“参照完整性”。 图表 10：原始的数据输入方式和改进后的数据输入方式方法一、首先将“销售员ID”的标题设置为“销售员”，再将查阅属性作如下图设置即可。方法二、在数据类型的下拉列表中选择“查阅向导”，让向导自动完成对查阅属性的设置。（这种方式必须是在两表之间未建关系之前做，建立关系后不能用此种方法）图

18、表 11：“销售员ID”字段查阅属性的设置（方法一）说明：绑定列：表示以查询结果的第几列作为返回给组合框的值用来为本字段输入内容；eg.值为1表示将查询结果的第1列即“销售员ID”的值返回作为本字段的输入内容列数：表示在组合框中表示待显示的列数，即显示的查询结果的列数；eg.值为2表示在组合框中显示查询结果的前两列。列宽：用于设置每列的显示宽度，每列之间以分号分隔；例如：0cm;2cm分别表示第一列和第二列在组合框中的显示宽度，第1列显示宽度为0cm，表示在组合框中不显示第1列的内容，也就是上图中改进后输入效果。（可以自己修改该项设置，分别设置列宽为0及1，比较两种情况下展开组合框的显示区别）

19、如果某字段的取值只是固定的有限几个不同取值，如：性别字段的“男”、“女”，学历字段的“专科”、“本科”、“硕士”等；可以进行字段查阅属性的设置，“显示控件”设为“列表框”，“行来源类型”可以设置为“值列表”，行来源中应输入所有可能的值，并以英文的分号隔开，如：销售员的性别采用值列表，行来源里应输入： "男""女"注意：这里的双引号和分号均为英文标点符号，不能用中文标点符号，系统会将中文标点符号等同于中文汉字对待，今后凡是在表达式中都只能用英文标点符号2 建立和编辑表之间的关系表之间的关系就是表的外键与其他表的主键之间的关系，建立关系的方法如下：选择“工具

20、”“关系”或点击工具栏中的按钮，出现如图所示关系窗口。图表 12：关系窗口然后，选择“关系”“显示表”，或在关系窗口中点击鼠标右键，在弹出菜单中选择“显示表”，如图。图表 13：显示表窗口将需要建立关系的表添加进去，如：“供应商”表和“商品”表，然后用鼠标拖动联系两个表的字段中主键字段例如：“供应商”表中的“供应商ID”字段到对应的外键字段：“商品”表中的“供应商ID”字段上，当鼠标变成一个长方条时，松开鼠标，这时出现如图所示“编辑关系”对话框。图表 14：编辑关系对话框 如果要使有关系的表之间数据统一，应该设置“实施参照完整性”，(例如： 若未实施参照完整性，当在输入商品信息时，如果输入的供

21、应商ID是一个在供应商表中不存在的值，系统不会提出反对意见，会允许输入，但若实施了参照完整性，遇到这种供应商不存在的情况，系统会给出相关提示信息，并且不允许输入)级联更新相关字段 当主表的主键值更改时，自动更新相关表中的对应数值；级联删除相关字段 当删除主表中的记录时，自动删除相关表中的有关记录（例如：当删除供应商表某一供应商时，会自动同时删除掉商品表中由此供应商提供的所有商品）。设置完后，点击“确定”按钮，完成关系的创建。按照同样的方法，我们可以依次建立其他的几个关系。如图，显示了“超市管理系统”中所有关系，建立了关系的表之间有一条连线连接起来，并指向相应字段。图表 15：表之间的关系关闭“

22、关系”窗口，保存关系布局。当需要修改关系时，可重新打开“关系”窗口，用鼠标双击或右击需要修改关系连线进行修改或删除（在表设计过程中如果进行了“字段查阅”属性的设置，则会自动建立对应的表的关系，可以修改此关系，设置其“实施参照完整性”）。3记录的操作添加新记录 打开表，将光标置于最后一行有“*”的记录内，输入一条记录即可，也可以直接在任一条记录前点击鼠标右键，在弹出菜单中选择“新记录”，光标就自动移到输入新记录的位置。修改记录 先定位于要删除的记录，直接移到要修改的字段进行修改即可。删除记录 先定位于要删除的记录，可以在上面所述的弹出菜单中选择删除记录即可，也可以用“编辑”“删除记录”。在删除记

23、录时有一对话框提示，以防误操作。复制记录 和上面的操作类似，需要说明的是，可以在表内、表之间以及向Word、Excel等复制，在表内复制要注意记录的唯一性，在表之间复制要注意表的字段顺序、类型、大小要一致，至少要兼容。4数据的选择、删除、剪切、复制、粘贴等操作这些操作和Word，记事本等文字操作一样，不再赘述。5数据的导入、导出和链接导入是一种将数据从其他Access文件复制Microsoft Access 中或将数据从不同格式转换并复制到 Microsoft Access 中的方法。该方法可以利用已有的数据，减少数据录入工作量。“文件”-“获取外部数据”-“导入”；导出是一种将数据和数据库对

24、象输出到其他数据库、电子表格或文件格式中，以便其他数据库、应用程序或程序可以使用该数据或数据库对象的方法。如：导出到Word、Excel对数据进一步处理。“文件”-“导出”；导出在功能上与复制和粘贴相似。链接是一种连接到其他应用程序中的数据但不将数据导入的方法，这样在原始应用程序和 Access 文件中都可以查看并编辑这些数据。导入、导出和链接功能可以通过“文件”菜单或数据库窗口的弹出菜单中相关菜单项操作。6设置数据表视图的行高、列宽、字体、字号、单元格效果等格式，删除、冻结、隐藏列A. 这些设置可以在“格式”菜单中找到，也可以在“数据表视图”的标题栏或相应的列上的弹出菜单中找到。非精确的行高

25、、列宽可直接通过鼠标拖动行列之间的缝隙调整。删除列就删除相应的字段B. 冻结列用于当表的字段太多，在一个显示屏显示不完，需要拖动滚动条显示时，容易记录错位，通过将关键的一列固定，来解决记录错位问题。C. 当某些列暂时不使用并影响记录定位等情况下使用隐藏列将这些列隐藏起来。相对应有“取消隐藏列”、“取消冻结列”。7 数据的查找、替换、排序、筛选当表中有成千上万条记录时，查找某条记录或将某些内容替换修改，就要使用“查找和替换”工具。可以在“编辑菜单”和相应的弹出菜单中找到该菜单，也可用工具栏中的按钮。点击“查找”后出现如下对话框，可以在指定的字段内，也可在整个表中进行查找和替换。替换时，还需在替换

26、框中输入替换的内容。图表 16：查找替换排序是将表中的记录按选定的列以升序或降序排列，先选定排序的列，可以直接点击工具栏里的按钮，也可通过“记录”“排序”或相应的弹出菜单来排序。筛选是按给定的条件从记录中筛选出符合条件的记录，可以直接点击工具栏里的按钮，也可通过“记录”“筛选”或相应的弹出菜单来筛选。实验任务：（1-5题必做）1. 从172.22.4.2上下载超市管理数据库（其中不包括销售明细表；）2. 根据字段含义，修改完善各字段的属性： 将商品表中的警戒库存量字段默认值设为：10；A. 雇员的雇佣日期不能晚于当前日期，违反时给出相应提示：“雇佣日期晚于了当前日期”；B. 输入雇员性别字段时

27、，直接选则男和女（值列表）；C. 设置销售员的姓名字段及供应商表的公司名称字段不允许为空D. 为商品表中的供应商ID字段、类别ID字段、售货单表中的销售员ID字段及售货单明细表中的商品ID字段设置其查阅功能，使其在显示记录时，虽然各字段实际存放的是其ID，但却能显示其对应的名称（例如：）销售员姓名、类别名称、供应商公司名称、商品名称而不是ID。3. 建立各表之间的关系，并且在各关系间实施参照完整性4. 练习导入、导出功能A. 从172.22.4.2上下载excel文件“收货单.xls”，将其中的数据导入到“超市管理系统.mdb”的对应表中。B. 从172.22.4.2上下载数据库文件db1.m

28、db，将其中的售货单明细表导入到销售管理系统数据库中。C. 将”销售管理系统.mdb中的商品表导出到db1.mdb数据库中，导出时表名称不变。5在各表中进行数据操作：为各数据表输入相应的记录（在下载的数据库基础上，往系统中增加以自己名字命名的销售员信息，并为该销售员添加2-3条销售单记录，每个销售单记录对应3-5条销售单明细记录）6添加、删除、冻结、隐藏列A. 在供应商表的视图中，在“联系人姓名”字段后添加新的一列，并将新添加的列的列名重新命名为“特长”；B. 将新添加的“特长”字段删除C. 将销售员表中“姓名”字段冻结，比较冻结前后在显示的不同7.*练习查找、替换、排序功能A. 对销售员表中

29、的记录按姓名排序；B. 将销售员表中城市字段中的“重庆”替换为“重庆直辖市”， C. 查找出商品表中商品名称为“*”的商品记录（自己确定具体商品名称）8.*对表中记录进行选择、剪切、复制、粘贴操作 分别选择系统中各原始表中的记录，将其进行复制并粘贴到任务3中导入的对应空备份数据表中。9.*设置数据表视图的格式将数据表视图的所有行高设为18；供应商表中的地址字段列宽设为25；所有表视图中的字体设为：华文仿宋，字号设为：小四；实验报告l 写出试验过程1-5题的具体操作步骤l 写清楚系统中各表之间的关系，及关系所代表的具体含义，以配对的形式写出关系中主键及外键所在的表及字段名称l 写出实验过程中遇到

30、的问题及心得体会实验四 查询设计实验目的1、 掌握用向导设计查询2、 掌握用设计视图设计查询实验内容1、 用向导设计查询2、 用设计视图设计简单查询、带条件的查询、对结果排序的查询3、 设计操作查询实验步骤1 使用向导设计查询在数据库窗口的左边“对象”栏中选择“查询”，双击“使用查询向导创建查询”选项，出现如图所示向导窗口。选择相应的表和字段，按照向导的一步一步提示即可完成简单查询的创建。图表 17：查询创建向导2 使用设计视图创建查询在数据库窗口中双击“在设计视图中创建查询”，出现如图所示：显示表和设计视图两个窗口。图表 18：显示表对话框图表 19：查询设计视图选择查询所涉及的表或查询，例

31、如“销售员”表，点击“添加”按钮添加到查询设计视图中，关闭“显示表”对话框。设计视图中上部显示表和字段列表，下部为设计网格，用于设置查询字段及选项等。在“设计视图”字段列表中双击“*”，则在设计网格的字段中出现“销售员.*”，表示选择了表中所有字段，如果只需要其中的部分字段，则在相应字段名上双击，或用鼠标将字段名拖到下面的字段中即可。下图演示了查询“销售员”表中所有销售员的姓名。图表 20：添加表的查询设计视图点击工具栏上的运行按钮即可查看查询结果，如下图。如果查询以设计完毕，可关闭设计视图或查询结果窗口，此时系统提示是否保存查询以及保存的名称。图表 21：查询结果3 带条件和排序的查询在上面

32、设计视图中，在相应字段的“条件”中输入一个表达式，用来限制查询返回的结果集，例如：输入“赵军”，则结果集中只返回“赵军”这一条记录。表达式也可以“生成器”产生复杂的条件，也支持通配符“*”、“?”，其中“*”匹配任意多个的任意字符而“*”匹配单个任意字符。如“Like "张*"”表示返回所有姓张销售员姓名，而“Like "张?"”则返回所有姓张但姓名只有两个字的销售员姓名，如“张颖”、“张欢”。在上面设计视图中，在相应字段的“排序”中选择“升序”或“降序”，则结果集就按该字段升序或降序排列。4 操作查询注意：以下操作查询执行后是不可撤销的，执行前要慎重考

33、虑。更新查询 将表中符合查询条件的记录的相应字段值批量更改为新的值，如所有商品的单价提高0.5元，先用设计视图设计好选择查询（默认），然后在“查询”菜单中或在工具栏的按钮中选择“更新查询”，然后在设计视图的“单价”字段的“更新到”框中输入“单价+0.5”，如下图，点击工具栏运行按钮即可。如果要将单价提高10%，则需在“更新到”框中输入“单价*(1+0.1)”。图表 22：更新查询删除查询 将表中符合查询条件的记录集批量删除，操作方法同更新查询类似。生成表查询 将查询结果保存在表中，可以是新表，也可以是原有的表，可以是当前数据库的表，也可以是其他数据库的表，操作方法同更新查询类似，系统会提示选择

34、或输入表的名称，运行后，查询结果才保存到表中。追加查询 将符合查询条件的结果集追加到另一个表中，同生成表查询类似，要求被追加的表必须包含查询结果的相应字段。实验任务设计如下查询：1. 进货查询：查询出系统中哪些商品应该进货（库存量<戒库存量的商品）2. 存货查询：查询系统中各商品的库存信息（商品名称、供应商id、单价、数量）3. 查询所有出生日期在1965-1-1到1975-12-30之间出生的销售员4. 查询所有姓张的且性别为“女”销售员信息5. 将商品表中库存量字段中的值批量更新，所有海鲜类商品的库存量提高10%（更新查询）6. 将售货单明细表中售货单ID字段为“xxx”（xxx为售

35、货单表中存在的值）的记录集批量删除（删除查询）7. （选做）对商品表进行生成表查询，生成单价>=100元的商品表，新产生的表名为：贵重商品（生成表查询）8. （选做）对商品表进行追加查询，将商品单价在50<=单价<100元范围内的商品信息追加到“贵重商品”表（追加查询）实验五 复杂查询实验目的1、 掌握多表查询的设计2、 熟悉参数查询和统计查询设计实验内容1、 创建多表查询2、 设计带参数的查询3、 创建统计查询实验步骤1 多表查询打开查询设计视图，添加查询所依赖的表。如：要查询“佳佳乐”公司供应的商品，则要将“供应商”表和“商品”表添加到查询设计视图。添加查询结果所需的字段

36、，如：将“公司名称”和“商品名称”作为查询结果字段。设置查询条件，如：在“公司名称”字段的“条件”中输入“佳佳乐”，如图。点击工具栏中的运行按钮即可看到查询结果。多于两个表的查询创建方法类似。图表 23：多表查询设计2 设计带参数的查询带参数的查询实际上就是条件查询，只不过参数查询的条件更灵活，每次运行查询时由用户动态输入参数来确定查询的条件。比如，上面设计的多表查询每次运行时都是查询的“佳佳乐”公司供应的商品，而如果我们将设计网格中“公司名称”字段的条件中输入“输入要查询的公司名称：”，则每次运行该查询时，先出现如图所示参数对话框（只要在某字段的条件中输入“”中括号就表示参数查询，中括号中间

37、的文字表示输入提示，如图22）。输入参数，点击“确定”按钮后，查询按输入值进行查询。图表 24：查询参数对话框3创建统计查询（聚合函数的使用）查询还可以对字段统计次数，对数值型字段汇总，查找最大、最小值，计算平均值、标准差、方差等，即查询时经常要对一些字段进行汇总。比如，每张销售单中有多种商品，每种商品的销售额计算方法为：数量×单价×（1折扣），汇总销售单中所有商品的销售额即为该销售单的总额。要查询每张销售单的总额，操作方法如下：打开查询设计视图，添加“售货单明细”表，添加“售货单ID”字段，在紧邻“售货单ID”字段位置输入表达式“总额: 单价*数量*(1-折扣)”（注：冒

38、号前为计算字段的显示标题，冒号后为计算表达式），然后点击工具栏中的总计按钮，在设计网格中出现新的一栏选项“总计”，各字段默认值为“分组”，对应“总额”计算机字段的位置点击下拉列表，选择“总计”，如图。运行查询，每张销售单的销售总额便计算统计出来。图表 25：统计查询设计注意：在“统计查询”中，对应“总计”行，一般新建字段（表中原本不存在而在查询中创建的字段:在本例中为“总额”字段）为总计（具体选项根据查询要求选择），其余字段为分组。实验任务1 查询出指定商品在规定日期的销售详情（包括商品名称、销售单号、销售数量、销售单价、折扣、销售日期）（多表之间的条件查询及参数查询）2 销售单的销售额统计：

39、按姓名查询某个销售员所销售的所有售货单并统计出每个售货单的总金额（在同一个查询中完成；提示：销售员姓名、销售单ID为分组字段，新产生一个“总额”字段为总计字段）。3 销售员月销售额统计：统计出1998年所有销售员各月的销售总额(具体年份可通过参数传递或条件输入)；4 按商品月销售额统计：查出每类商品1998年每个月的销售额，包括商品名称、销售月份、销售额；5 交叉查询：根据实验任务中3中的查询结果，建立一交叉查询，按月份对各销售员销售额进行对比及汇总，以方便同时按月份分析某月不同销售员业绩及按销售员分析某销售员不同月份的销售业绩。试验报告：1. 分析作业1-5的具体实现步骤（包括分析从“显示表

40、”中选用哪些表、从选择表中选用哪些字段及创建查询的整个过程），将各查询的设计试图截图附在实验报告中。2. 写出试验过程中遇到的问题及你觉得的难点3. 写出实验过程中的心得体会实验六 SQL查询语句练习一、实验目的1. 掌握数据库操作语句的使用2. 掌握查询分析器的使用方法。3. 掌握SELECT语句在单表查询中的应用。4. 掌握SELECT语句在多表查询中的应用。5. 掌握SELECT语句在嵌套查询中的应用。二、实验内容直接编写SQL语句查询前面设计的查询是通过向导或设计视图等图形界面方式创建的，我们还不知道其真正的SQL语句是什么。其实，我们可以通过鼠标点击设计视图的空白区域，在弹出菜单中选

41、择“SQL视图”或直接点击工具栏左上角的中的“SQL视图”，即可查看查询背后的SQL语句。实际上，我们可以修改已经创建好的查询，甚至在空白的设计视图中徒手编写SQL语句创建查询。不过，这需要对SQL比较熟悉才行，而且要注意的是Access是一个具体的软件环境，并不一定支持所有SQL语句，使用教材上的示例SQL语句时要结合Access的在线帮助。1Select查询语句SQL语言提供了Select语句进行数据库的查询，该语句具有灵活的使用方式和丰富的功能。其一般格式为：SELECT select_list INTO new_table FROM table_source WHERE search_

42、condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression ASC | DESC Select语句的功能是：根据Where子句的条件表达式，从From子句指定的基本表的全部元组中找出满足条件的部分元组，再按Select子句中的目标列表达式，挑选出这些元组中的某几个特定的属性列值形成结果表。如果有Group by子句，则表示要将查询结果按照Group by子句后<属性列名>所表示的属性列的不同值进行某种方式的汇总，具体的汇总方式有求和、计数、求最大值、求最小值、求平均

43、值等。如果有Order by子句，则还要将查询结果按Order by子句后的<属性列名>的值的升序(ASC)或降序(DESC)进行排序。1）单表查询单表查询是指仅设计一个表的查询。很多情况下，用户只对表中的一部分属性列感兴趣。可以通过在select字句的目标列表达式中指定要查询的属性。A选择某个表中的若干属性列（投影）即从数据库的一个表中挑选出某几个特定的属性列值。例2-1 查询所有供应商的公司名称、联系人姓名、联系人职务、地址和电话号码。Select 公司名称, 联系人姓名, 联系人职务, 地址,电话From 供应商注意：Select子句后面的<目标列表达式>中各个属

44、性列的先后顺序可以和其在表中的顺序不一致。例如，可以调整本例中Select后面的属性列的顺序，改为先列出公司名称和电话号码，再列出联系人姓名和客户地址，调整后的Select语句为：Select 公司名称, 地址, 联系人姓名, 联系人职务,电话 From 供应商例2-2 查询所有产品的详细记录。Select 商品ID,商品名称,供应商id,类别id,单位数量,单价,库存量,警戒库存量 From 商品本查询列出了产品表的所有属性列值，而且属性列的顺序与其在表中的顺序完全一致，因此在Select子句中可以简单地用（*）代替全部的属性列名。Select * From 商品注意：如果列的顺序与其在表中

45、的顺序不完全一直，就不可以用这种方法。例2-3 查询被客户订购了的商品ID。Select 商品IDFrom 售货单明细注意：在上面的查询结果中包含了许多重复的行。这是因为有许多产品被重复订购了，而Select子句的缺省含义是Select all，即保留重复行，这样就出现了上面的查询结果。如希望去掉重复的行，需要在Select子句中使用Distinct短语，修改后的语句为：Select Distinct 商品ID From售货单明细B选择某个表中符合适当条件的记录（选择）在实际的查询过程中，有时我们不是简单地从数据库的某个表中挑选一些列，而是需要挑选出符合自己规定的某个（或某些）条件的部分记录，

46、这时就需要在Select语句中加入条件子句（Where子句）。例2-4 查询所有“北京”和“上海”的客户的公司名称、联系人姓名、联系人头衔和电话号码。Select 公司名称, 联系人姓名, 联系人职务, 城市,电话 From 供应商 Where 城市= '北京' or 城市='上海'本例中使用了两个查询条件：“城市= '北京' or City='上海'”，并用“or”运算符将其连接，表示希望查询北京客户或上海客户的信息。Where子句中常用的查询条件及运算符(或谓词)有很多，具体如下：查询条件运算符(或谓词)比较=，>，&l

47、t;，>=，<=，<>确定范围BETWEEN AND，NOT BETWEEN AND确定集合IN，NOT IN字符匹配LIKE，NOT LIKE空值IS NULL，IS NOT NULL多重条件AND，OR，NOT下面我们再通过一些例子来说明一下这些常用的查询条件及运算符(或谓词)的用法。例2-5 查询单价“不”介于30到50之间的产品的产品名称及其单价。Select 商品名称, 单价From 商品Where 单价 NOT between 30 and 50运算符“betweenand”和“not betweenand”可以用来限定某个值的范围，其中between后面是

48、范围的下限，而and后面是范围的上限。例2-6 查询所有“不”位于北京、上海、重庆的客户的公司名称、联系人姓名、联系人头衔和电话号码等数据。Select 公司名称, 联系人姓名, 联系人职务,电话From 供应商Where 城市 not in ('北京','上海','重庆')有时候我们希望在表中查找字符型属性列值具有某种规律的记录，这时就需要用谓词LIKE进行字符匹配，格式如下：not LIKE '匹配串' escape '换码字符'其中，匹配串是包含通配符“*”和“？”的特殊字符串。匹配串中的普通字符只和属性列值

49、中完全相同的字符匹配，而通配符“*”可以和属性列值中的0个或多个任意字符相配，“？”则可以与属性列值中的1个任意字符相匹配。例2-7 查询供应商表中所有(不)以林开头的联系人的姓名与头衔。Select 公司名称,联系人姓名, 联系人职务From 供应商Where 联系人姓名 not like '林*'例2-8 查询所有没有填写传真的供应商的记录。Select *From 供应商Where 传真 is null如果要查询所有输入了传真的供应商记录，只需将where子句改为：Where 传真 is not null2）. 多表查询由于存放在数据库中的各个表不是孤立的，而是相互联系的

50、，因此，有时我们会对多个表的数据同时进行查询以组成一个综合性的结果集。A简单条件连接查询简单条件连接查询是指仅涉及一个连接条件的连接查询。例2-9查询所有产品的名称、类别名称和库存量等数据。Select 商品.商品名称,类别.类别名称,商品.库存量From 商品, 类别Where 商品.类别ID=类别.类别ID在本例的select子句和where子句中都用到了<表名>.<列名>这种格式来表示某一列属于哪个表，以消除属性列的二义性。但是如果某一列名(如商品名称)在参加连接的各表中是唯一的话，该列名前的表名是可以省略的。在例2-10中,商品名称和库存量两个字段都只在表商品中

51、唯一存在,类别名称字段只在类别表中存在，所以在select语句中，此几个字段前的表名都可以省略掉，如例2-11。例2-10Select 商品名称,类别名称,库存量From 商品, 类别Where 商品.类别ID=类别.类别ID还要注意的是，在进行表的连接是必须在where子句中指明条件，否则就是广义迪卡尔积，其连接结果一般是无意义的。B复合条件连接查询复合条件连接查询是指具有多个条件的连接查询。例2-11查询所有单价介于20和50之间的产品的名称、类别名称、单价和库存量等数据。Select 商品名称, 类别名称, 单价, 库存量From 商品, 类别Where 商品.类别ID=类别.类别ID

52、and 商品.单价 between 20 and 503）嵌套查询嵌套查询是指将一个SelectFromWhere查询块嵌套在另一个查询块的Where或Having短语的条件中的查询。A带有比较运算符的子查询当子查询返回的结果是单值时，可以使用比较运算符连接父查询和子查询。例2-12.1查询与供应商“为全”有相同城市的所有供应商的公司名称及电话Select 公司名称,电话From 供应商Where 城市=(select 城市 From 供应商 Where 公司名称="为全")例2-12.2查询与商品ID为“1”的商品类别相同，单价却不同的产品的商品ID、商品名称及其单价。S

53、elect 商品ID, 商品名称, 单价From 商品Where 单价<> (Select 单价 From 商品 Where 商品ID=1) and 类别ID= (Select 类别ID From 商品 Where 商品ID=1)B带有IN谓词的子查询当子查询的查询结果包含多个值时，经常会使用谓词IN来连接子查询和父查询。例2-13查询购买了单价比“1”号产品大的那些产品所在的销售单ID。Select distinct 售货单IDFrom 售货单明细Where 商品ID in (Select 商品ID From 商品 Where 单价> (Select 单价 From 商品

54、Where 商品ID=1) C带有ANY或ALL谓词的子查询当子查询的查询结果包含多个值时，光用谓词“IN”来连接子查询和父查询是不够的，有时会用到前置了比较运算符的谓词“ANY”或“ALL”。其中，“ANY”代表子查询结果中的某个值，“ALL”代表子查询结果中的所有值。例2-14查询单价价格大于所有 “3”号类产品的产品名称及其价格。Select 商品名称,单价From 商品Where 单价>all (Select 单价 From 商品 Where 类别ID=3)实验任务： （第一题必做，做完还有时间可以完成后面的题目）1. 将实验步骤中的所有例题在SQL视图中执行2. 在销售员表中增加一个联系人为自己名字的供应商记录，其他字段自己随便设置。3. 查询出姓名为“金正鹏”的销售员的销售员ID、姓名、职务、雇用日期、家庭电话、地址4. 查询出不姓张的所有销售员的姓名、职务、雇用日期5. 查询出库存量超过警戒库存量但又小于50的所有商品的商