2022年2022年罗斯文数据库的查询设计 .pdf

《2022年2022年罗斯文数据库的查询设计 .pdf》由会员分享，可在线阅读，更多相关《2022年2022年罗斯文数据库的查询设计 .pdf（16页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、1 罗斯文数据库的查询设计一般的在表设计完成阶段以后就可以进行窗体的设计，然后再根据需要完善报表功能。在窗体与报表设计过程中会较多的使用到查询，一般的可以要据需要随时建立。不过为了先让大家都查询有个系统的了解，所以我们把罗斯文数据库中的查询放到前面来看。查询一般可以分为五种基本类型：选择查询， 参数查询， 交叉表查询， 操作查询和SQL 查询。用得最多的应是选择查询。可以结合罗斯文中的实例一起来学习一下。罗斯文数据库中共有个查询，这些查询分别为窗体和报表提供了数据源，我们挑选其中的一些来学习。订单查询：订单查询是为“订单”窗体提供数据的，包含订单表中的全部字段和客户表中的部分字段。一般我们在设

2、计查询时用的最多的是用设计视图创建查询。由于订单查询是一个比较简单的查询，所以我们通过该查询来学习一下怎样通过简单查询向导来创建最简单的查询，然后再利用设计视图来查看和修改。在数据库的查询对象窗口，双击“使用向导创建查询”或者单击“新建”按钮，在跳出的“新建查询”的窗口中选择“简单查询向导”在“表 /查询”中选择“表：订单”，选中该表中的所有字段加入到“选定的字段”中名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 16 页 - - - - - - - - - 2 再从“表

3、：客户”中选择字段公司名称，地址，城市，地区，邮政编码，国家单击“下一步” ，选择“明细”；如果要在查询中实现将记录进行分组，计数、求和、求平均值等计算，则要在这一步选择“汇总”进入“汇总选项”进行相应的设置。选择“下一步” ，给查询起名“订单查询” ，单击“完成” ，自动打开查询，显示查询结果。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 16 页 - - - - - - - - - 3 如果要对查询进行查看或修改可以进入设计视图中，从菜单“视图”中选择“设计视图”，

4、即可进入设计视图界面。当然工具栏也有“视图”按钮设计视图分为上下两上窗口，在上面显示查询中要用到的数据源来自于哪些表或查询，在下面列出的是查询结果中需要用到的字段，该字段来自哪个表，哪个字段需要设置排序方式，是否需要显示该字段，以及查询条件等。很多查询只需查询出满足条件的记录，并不需要全部的记录，这样就需要在 “条件” 中输入条件，一般用表达式来表示，只有满中表达式的记录才被显示出来。因此我们需要了解一下表达式。表达式 : 表达式是许多Microsoft Access 运算的基本组成部分。表达式是可以生成结果的运算符号和操作数的组合。例如，可以在窗体或报表的控件中使用下列表达式来显示“ 小计”

5、 和“ 运货费 ” 控件的数值总和： = 小计 + 运货费 常见的运算符如算术运算符“=” ， “+” ， “-” ， “ *” ， “ /” ；比较运算符“” ， “=” ， “=” ，“” ， “=” ；逻辑运算符“and” ， “or” ， “not” ；连接运算符“&” ， “ +” ；及常用的！和.（点）运算符。常见的操作数如字符串，日期/时间值，常量，变量，函数及引用窗体或报表中的字段值，控件值或属性等。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 16 页

6、 - - - - - - - - - 4 常见表达式示例：数学及比较运算表达式表达式说明=数量 * 价格 计算数量与价格的乘积，可以得出总价=到货日期 - 发货日期 计算两个日期之间的天数2500比 2500 大的数日期表达式表达式说明Between #1997-01-01# And #1997-12-31#在 1997-01-01 各 1997-12-31 之间的数据#2007-12-30#2007-12-30 以前的数据30大于 30 岁的男性数据= “ 中国 ” & “ 北京 ”值为“ 中国北京 ”通配符表达式表达式说明Like “ 张* ”以张开头的数据Like “ 张?”以张开头后面

7、还有一个字的数据聚合函数表达式表达式说明Sum(数量 * 价格 )求数量和价格相乘后的和Avg( 费用 )求费用的平均值Count(*)计算记录条数“ 当前产品列表 ” 查询当前产品列表统计出未被中止的产品。在数据库的查询对象窗口，双击“ 在设计视图中创建查询” 或单击 “ 新建” 按钮，在跳出的“ 新建查询 ” 的窗口中选择 “ 设计视图 ” ，进入设计视图界面，从显示表中添加“ 产品 ” 表。选中 “ 产品 ID” ，“ 产品名称 ” ， “ 中止 ” 三个字段（注：选中字段可以把字段从设计视图界面的上方拖到下方，也可以双击字段名称），去掉“ 中止 ” 下面 “ 显示 ” 的勾，在条件一栏

8、输入“no”，代表只查询出未被中止的产品，但只要显示产品的ID 和名称就行了，是否中止的状态不用显示出来。我们还想将输出的结果按照产品名称进行排序，在“ 产品名称 ” 下面的 “ 排序 ” 栏里选中升序。这样查询就完成了，点保存，给查询起个名字就可以了。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 16 页 - - - - - - - - - 5 我们打开罗斯文数据库中的当前产品列表查询，会发现数据源是“ 产品列表 ” ，不是 “ 产品 ”表，这里是用到了表的别名，一般

9、的我们在查询中为了方便，经常会给比较长的表名取个短一些的名称，更方便引用表。方法是在显示的表上点击鼠标右键，选择属性，在别名中填上合适的别名就行了。本例中其实完全没有这个必要，可能只是想让大家了解这样一个用法而已. “ 各类产品 ” 查询各类产品查询为各类产品报表提供数据源，在查询对象中选中“ 各类产品 ” ， 单击“ 设计 ” 按钮，我们发现默认的是按SQL 视图显示的， 这时从 “ 视图 ” 中选择 “ 设计视图 ” 即可显示设计视图，当然要再切换到SQL 视图，再从 “ 视图 ” 中选择 “SQL视图 ” 即可。通过在 “ 设计视图 ” 中设计查询，再切换到 “SQL视图 ” 中查看 S

10、QL 语句也是我们学习SQL 语句的方法。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 16 页 - - - - - - - - - 6 查询的数据来自“ 类别 ” 表和 “ 产品 ” 表， “ 类别名称 ” 字段和 “ 产品名称 ” 字段的 “ 排序 ” 都是升序，查询结果将先按类别名称排序，当类别名称相同时再按照产品名称排序。中止条件为“no”，结果只包含未被中止的产品。“按汉语拼音顺序的产品列表”查询这个查询是为“按汉语拼音顺序的产品列表”报表提供数据源的。双击查

11、询即可运行并显示查询的结果，在查询结果中并未按拼音顺序来排列，这个功能是在报表中实现的。进入设计视图这个查询的数据来自两个表，“类别”表和“产品”表。查询的结果中只包含未被中止的产品，因此在产品字段上设置了条件，这里代表“否”，在ACCESS 中用代表“否”，FALSE，“假”，而用-1 代表“是”， TRUE ，“真”十种最贵的产品进入设计视图：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 16 页 - - - - - - - - - 7 查询的数据来自于表“产品”，

12、选用的字段只有产品名称和单价两个，如果要给选择的字段另外取个名称，可以给这个字段前面添加名称，中间用英文状态的冒号“:”隔开，如上图。要查询出十种最贵的产品，只要将产品按产品单价降序来排列再取前十种即可。在单价字段上设好“降序”排列，在工具栏上的上限值列表框中输入 10 即可。双击查询查看运行结果，体会在设计视图中设置的作用。季度订单这个查询列出了在1997 年度有订单的客户， 不是某个季节的记录，而是含盖了全年度的记录，这个结果用于给“季度订单”窗体提供数据源，在窗体中再具体实现按四个季度显示统计结果。进入设计视图：这个查询的数据来自于“客户”表和“订单”表，“订单”表中用到的订购日期只是作

13、为一个条件项，不用被显示出来，条件里是一个表达式，代表的含义是订购日期介于1997 年 1月 1日到 12 月 31 日之间的，这里用到“betweenand ”这种语法。日期型的数据应该在两侧加上“#”号。接下来我们还要考虑到有些客户可能在1997 年度有多次订货，而我们希望的结果是每个客户只出现一次。这时可以在查询视图的上半部分窗口的空白处右击鼠标，从浮动菜单中选择“属性”，也可直接在工具栏上选择“属性”名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 16 页 - -

14、 - - - - - - - 8 设置查询属性中的“唯一值”属性为“是”即可。设置了这个属性就能保证每组中的重复数据只会列出一个。在这里还要提一下的是要注意一下选择表的联接类型，在联接线上双击可以显示联接属性，联接属性分为三种，可以根据要求选择。一般默认的联接类型为，只包含两个表中联接字段相等的行，在本例中改成联接类型为，包含订单表中的所有记录，在SQL 语句方面分别体现为内联接，左联接和右联接，这是有区别的，可以参考运行结果体会联接的作用。扩展订单明细扩展订单明细为窗体“客户订单子窗体2”提供数据源，查询中带有计算字段，查询出每个订单每种产品的一些明细信息。进入设计视图：名师资料总结 - -

15、 -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 16 页 - - - - - - - - - 9 查询的数据来自于“产品”表和“订单明细”表，查询结果按订单ID 升序排列。查询中的字段总价是个计算字段，总价为该字段的名称，计算表示用 “订单明细” 表中的 “单价”乘以“数量”后再乘以（1-折扣 ），相当于算出了打过折后的总价。字段中用到一个CCur（）转换函数，它的作用是将数据转换为货币类型，另外表达式中用了先除以100，再乘以 100，相当把数据还原，同时小数点后面保留两位小数。不过我试了

16、直接用CCur( 订单明细 .单价 * 数量 *(1- 折扣 )，得到的结果也是两位的？欢迎共同讨论。订单小计订单小计统计出每个订单上各种产品的销售金额的总计金额，为汇总销售额等多个查询提供数据。进入设计视图：查询的数据来自于“订单明细”表，选用的字段只有一个“订单ID”，小计字段是个计算字段，小计是这个字段的名称，后面是计算表达式。计算方法同 “扩展订单名细”查询中的 “总价”字段，这里由于只有一个表，所以订单明细 . 单价 可以直接写成单价 ，该字段为：小计: CCur(单价 * 数量 *(1- 折扣 )/100)*100 。本例到这里只是计算出了每个订单ID 中每种产品的总价，而我们要统

17、计出的是每个订单ID 中所有产品总价的和，所以我们要对订单ID 进行分组，要用到“总计” 行的功能，“总计” 行默认是不显示的，可在设计窗体的下半部分右击鼠标，选择“总计” ，或者单击工具栏上的按钮，这样都会多出一行总计来，在总计行，可从下拉框中对每个字段选择相应的操作，可以作为分组依据或条件字段也可以选择聚合函数或其他函数来对字段名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 16 页 - - - - - - - - - 10 进行计算，如果要自己写表达式，则选择表达式

18、。在本例可以把“订单ID”字段设成分组字段，对“小计”字段要进行求和，只要选成“总计”就可以了。罗斯文的示例中并没有选用“总计”的功能，而是对“小计”字段设成了“表达式”，而在表达式中加上了求和函数Sum()，大家可以比较一下，用这两种方法生成的查询运行结果都是一样的，而SQL 查询的语句也是一样的。对于表达式如果一开始觉得写起来有些困难的话，可以借助于生成器来生成，生成器中有数据库中的各种对象， 操作符以及函数， 只要进行选择再修改一下就可以了。方法是在字段上右击鼠标，从中选择“生成器” ，在生成器的下方选择相关参数，计算符等，表达式生成在上方可进行修改。按年度汇总销售额“ 按年度汇总销售额

19、” 查询为 “ 按年度汇总销售额” 报表提供数据， 另外“ 按季度汇总销售额” 查询与本查询完全一样，也就是本查询也能为“ 按季度汇总销售额” 报表提供数据源，按季度的功能是在报表中实现的。本例要统计出已经发货的各订单的计单ID 号及各订单小计金额。进入设计视图：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 16 页 - - - - - - - - - 11 查询的数据来自于查询“ 订单小计 ” 及“ 订单 ” 表，取“ 订单” 表中的发货日期字段，并按该字段升序排列

20、，另外该字段还设了条件，为“Is Not Null”，这个表达式代表数据不为空，没有填写过发货日期的记录则为空，代表尚未发货，并排除在外。10按金额汇总销售额“ 按年度汇总销售额” 查询统计了1997 年订单的小计金额在2500 元以上的订单， 为“ 按金额汇总销售额 ” 报表提供数据。进入设计视图：查询的数据来自于“ 客户 ” ，“ 订单 ” 表及 “ 订单小计 ” 查询。对小计字段另取名称为“ 销售金额 ” ，加上条件 “2500” ，表示只要订单小计在2500 元以上的记录；发货日期加上条件“Between #1997-1-1# and #1997-12-31# ” 表示只要发货日期在1

21、997 年的记录。 两个条件在同一行表示要求同求满足。11各国雇员销售额“ 各国雇员销售额” 查询统计各国雇员的销售情况，为“ 各国雇员销售额” 报表提供数据。进入设计视图：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 16 页 - - - - - - - - - 12 查询的数据来自于“ 雇员 ” ， “ 订单 ” 表及 “ 订单小计 ” 查询，发货日期字段的条件为“Between 起始日期 And 终止日期 ”，因为表中并没有起始日期 字段，所以运行时，ACCES

22、S 会将它看作一个参数，并跳出提示框，终止日期 同理，在运行时输入正确的参数，并会得到查询结果。12各年销售额“ 各年销售额 ” 查询统计某一年的每个订单的销售情况，为“ 各年销售额 ” 报表提供数据。进入设计视图：查询的数据来自于“ 订单 ” 表及 “ 订单小计 ” 查询，年份是个计算字段， 用了一个Format() 函数，这是一个文本格式函数，具体参数及用法请查看帮助。重点来看发货日期的条件设置，这个条件由两部分组成，中间用“And”联结，表示要同时满足前后两个条件，前面一半为“Is Not Null”代表发货日期不能为空，后面一半为“Between Forms! 各年销售额对话框!起始日

23、期 And Forms! 各年销售额对话框!终止日期 ” ，这也是一个 “Between and ”的用法，只不过里面引用了两个窗体中的参数，Forms! 各年销售额对话框!起始日期 代表“ 各年销售额对话框” 窗体中的“ 起始日期 ” 中的值， 终止日期 同理，在运行中，如果“ 各年销售额对话框” 窗体是打开的并能读取到相关参数， 则直接给出运行结果，否则象 “ 各国雇员销售额” 查询中一样， 跳出提示框要求输入。事实上好多报表都会从窗体或报表中读取一些数据作为报表的计算依据，我们要熟悉这种参数的设置方法。13发货单“ 发货单 ” 查询详细统计出每个订货单的详细信息，包括订单情况，货主情况等

24、，为“ 发货单 ”报表提供数据。进入设计视图：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 12 页，共 16 页 - - - - - - - - - 13 这个查询选择的表很多，其实在设计时方法还是一样的，分别选择如上图的6 个表，根据需要选择相应的字段，其中销售人字段是个计算字段，把姓氏 和名字 联在一起显示，用到一个联接运算符， 总价字段也是一个计算字段，与前面例中的计算方法相同。对于表中设置的一些属性会在查询中直接带过来，如在相联接的两个表“ 客户” 和“ 订单 ” 表中有

25、相同的两个字段“ 客户ID” ，如选择 “ 客户 ” 表中的 “ 客户 ID” 显示的是 ID 号，而选择 “ 订单” 表中的 “ 客户 ID” 显示的是客户名称，这是由于 “ 订单 ” 表中的该字段设了查阅属性的缘故，不过字段中实际存储的值都是一样的，只是影响显示，可根据需要从相关表中选择相应字段。在这个查询中在“ 总计 ” 行对每个字段都采用了 “ 分组 ” ，其实这样的效果和排序是一样的，如不用“ 分组 ” 而对每个字段依次进行排序，运行的结果也是一样的。当然如果需要分组计算时，则是不可替代的。14各类销售额“ 各类销售额 ” 查询为 “ 各类销售额 ” 报表提供数据，统计了1997 年

26、各类产品的销售情况。进入设计视图：这个查询的数据来自于个表及一个“ 扩展订单明细 ” 查询，查询中对类别及产品名称进行了多级分类，再对分类后的产品求出销售总金额。所以在查询中“ 类别 ID” ，“ 类别名称 ” 及“ 产品名称” 字段的 “ 总计 ” 行都设为分组字段，总价字段是计算字段要进行求和，所以设成“ 总计 ” ，另取名称为 “ 产品销售额 ” 。在这种多级分类统计的查询中一样可以设置条件，本例中的订购日期，不要显示，只是作为条件字段，所以 “ 总计 ” 行设为 “ 条件 ” ，条件为 “Between and ”的名型， 查询出 1997年的数据。15 1997 年产品销售额“199

27、7 年产品销售额 ” 查询统计出不同类别的产品在不同季度的销售额，为“1997 年各类销售总额 ” 查询提供数据。进入设计视图：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 13 页，共 16 页 - - - - - - - - - 14 查询的数据来自于上图中的四个表，其中“ 发货季度 ” 字段是个计算字段，用到了一个日期函数 DatePart() ，具体的参数和用法可以查看帮助，在本例中取日期中的季度，结果为1-4 中的一个值，后面加上一个联接字符串，现加上季度，可以形成“ 季

28、度 ” 这样的字符串。在“ 总计 ” 行：需要分组的字段“ 类别名称 ” ，“ 产品名称 ” ，“ 发货季度 ” 设成 “ 分组 ” ，而且分类应是有先后的，大类放在前面； 需要设置条伯的字段“ 发货日期 ” 设成 “ 条件 ” ；需要计算的计算的字段“ 产品销售额 ” 设成“ 表达式 ” 。16 1997 年各类销售总额“1997年各类销售总额” 查询以 “1997年的总销售额 ” 查询为数据，进一步查询出每种类别的销售总额，比较简单，详见设计视图：其实本例也不一定非要通过“199 7 年的总销售额 ” 查询来产生。也可以如下图设置生成，运行结果也是完全一样的。名师资料总结 - - -精品资

29、料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 14 页，共 16 页 - - - - - - - - - 15 17高于平均价格的产品“ 高于平均价格的产品” 查询，以产品平均价格作为衡量依据，统计出比平均价格高的产品。进入设计视图：这个查询的数据来自于“ 产品 ” 表，选择的字段也很简单，只有两个“ 产品名称 ” 与“ 单价 ” ，这个查询的关键在于设置“ 单价 ” 字段的条件，并不是所有的产品都显示出来，单价大于平均价的才显示出来，条件中用到比较运算符“”，平均价格哪里来呢，在本例中用了一个SQL 查询语句

30、，也叫子查询。象这种在条件中包含SQL 语句的查询叫嵌套查询，可以进行多层嵌套。用“Select AVG( 单价 From 产品 ” 来求出产品表中所有单价的平均值。本例的条件中如果不用子查询，改用函数也是可以的，可以改成“DAvg( 单价 ,产品 ) ”。18各城市的客户和供应商“各城市的客户和供应商”查询是一个联合查询，它将“客户”表和“供应商”表的信息联合在一起， 查询出每个城市的客户及供应商。联合查询由多个Select 语句组成， 各语句之间用关键字“UNION ”联接起来。要求在第一个查询语句中就要列出所有的字段，字段名也以第一个查询中的为准，后面各查询语句中所用的字段的个数，数据类

31、型，顺序都要与第一个查询中相同。本例中的查询语句如下：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 15 页，共 16 页 - - - - - - - - - 16 Select 城市 , 公司名称, 联系人姓名 , 客户 AS 关系 FROM 客户UNION Select 城市 , 公司名称 , 联系人姓名 , 供应商 FROM 供应商ORDER BY 城市 , 公司名称 ;前两行中是第一个查询语句：从“客户”表中选择“城市”、“公司名称”、“联系人姓名”，另加一个字段“关系”，

32、以字符串“客户”作为“关系”字段的值。第三每四行中包含“UNION”关键字及第二个查询语句：从“供应商” 表中选择 “城市” 、“公司名称” 、“联系人姓名” ，另加一个字段，以字符串“客户”作为字段的值，这里字段名省略，因为默认会取第一个查询语句中的字段名。最后一行是个将查询出来的结果按照城市和公司名称的升顺来进行排序。19各种产品的季度订单“各种产品的季度订单”查询是一个交叉表查询，它以行列交叉的行形列出不同产品，不同客户在 1997 年的各个季度中所有的订购额。为“季度订单”子窗体提供数据。一般简单的交叉表查询可以利用“交叉表查询向导”来生成，不过本例中的数据来源于多个表，还有计算字段，

33、因此需要在设计视图中创建。进入设计视图：查询的数据来自于“订单”，“订单明细”，“产品”表。要实现交叉表查询，需要从菜单“查询”中选择“交叉表查询”，这样就会在设计视图的下半部分多出“总计”行及“交叉表”行而少了一行“显示”。因为交叉表需要在行列的交叉处显示汇总结果，而这个结果则是根据行列的多重分组计算出来的。然后选择相应的字段，在“交叉表”行及“合计”行中对各字段进行设置：本例中“产品名称”，“客户ID”，“订购年份”作为行标题，其中“订购年份”字段中用到 “ Year()”函数， 代表取订购日期中的年份的值；订购年份后面一个字段用来作为列标题，该字段也是一个计算字段，用到一个“DatePa

34、rt()”函数，及“&”联合运合符。显示的结果为第 1 季度 ,第 2 季度 ,第 3 季度 , 第 4 季度 四个中的一个；“产品金额”设置为“值”是个计算字段，计算方法同前面的查询；对于分组计算来说，交叉表中的行与列是分组依据，值是计算结果，即总计行的设置是：“产品名称等前4 个字段设成“分组”，“产品金额”设为表达式。“订购日期”是个条件这段，并不显示，条件为1997 年的数据。可双击查询查看运行结果，对照生成的结果来查看设置的具体效果。到这里查询也全部结束了，只有少数几个没看，没有新的知识点，所以略过了。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 16 页，共 16 页 - - - - - - - - -