1 / 64

查询和视图

查询和视图. 第 4 章 查询和视图. 4.1 创建查询. 4.2 视 图 的创建和使用. 4.3 SELECT-SQL 语句. 4.4 小型案例实训. 4.5 习题. 教学目的与要求. 掌握 查询和视图的区别与联系. 掌握用查询设计器创建查询的方法. 掌握创建多表查询的方法. 了解视图的创建方法. 了解视图的使用. 掌握 SELECT-SQL 语句的使用. 4.1 创建查询. 4.1.1 查询概述. 类型上：可以是自由表、数据库表或视图 数量上：可以单张表或多张相关的表. 查询：

fritzi
Download Presentation

查询和视图

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 查询和视图

  2. 第4章 查询和视图 4.1 创建查询 4.2 视图的创建和使用 4.3 SELECT-SQL语句 4.4 小型案例实训 4.5 习题

  3. 教学目的与要求 掌握查询和视图的区别与联系 掌握用查询设计器创建查询的方法 掌握创建多表查询的方法 了解视图的创建方法 了解视图的使用 掌握SELECT-SQL语句的使用

  4. 4.1 创建查询 4.1.1 查询概述 • 类型上：可以是自由表、数据库表或视图 • 数量上：可以单张表或多张相关的表 查询： 是向一个数据源发出的检索信息的请求，它按照一些条件提取特定的数据，其运行结果是一个动态的数据集合。 • 实现查询的方法： • 利用查询向导创建查询 • 利用查询设计器创建查询 • 使用SELECT-SQL命令实现查询

  5. 4.1 创建查询 4.1.2 使用查询设计器创建单表查询 • 数据显示区 • 可以添加表或视图 • 利用菜单或快捷菜单中的“添加表”或“移去表”命令可以添加或移去表 查询设置区，共有6个选项卡

  6. 4.1 创建查询 使用查询设计器建立查询的步骤： ①打开“查询设计器”窗口。 ②指定被查询的数据表或视图。 ③选择出现在查询结果中的字段。 ④设置查询的筛选条件。 ⑤设置排序依据及对查询结果进行分组。 ⑥选择查询结果的输出类型。 ⑦运行查询。 ⑧保存查询，将查询文件保存为扩展名为.QPR的文件。 4、5、6步根据题目需要设置，不是每个查询必须有的步骤

  7. 例1：基于图书表（ts），查询出版社编号为B002、B003和B004的出版社出版的单价在20元到50元范围内的图书的书号(sh）、书名(sm)、出版社编号(cbsbh)和单价(dj)；按出版社编号升序排序，同一个出版社的图书按单价降序排序。 4.1 创建查询

  8. 添加表或视图 打开“查询设计器”时，系统会同时打开“添加表或视图”对话框，等待用户选择查询所基于的数据源，如果数据源是自由表，可先单击“其他”按钮，然后再来选择。 4.1 创建查询

  9. 4.1 创建查询 设置输出字段(在“字段”选项卡中设置) （1）如果输出的列不是来源于表的字段，可以定义关于字段的函数或表达式。 （2）若要给字段添加别名，可在表达式后输入 ‘AS’　和别名 （3）“选定字段”列表框中的字段顺序决定了查询结果中的字段顺序。

  10. 4.1 创建查询 设置筛选条件(在“筛选”选项卡中设置) （1）“条件”下拉列表框中条件的比较类型有：=、= =、Like、 >、 >=、<、<=、Is NULL、Between、In (部分条件含义见书P113) （2）”实例”文本框中输入比较条件时要注意实例常量的输入方法。 （3）备注、通用型字段不能作为选定条件。 （4）图中的一行构成一个关系表达式，通过“逻辑”下拉列表框，可以 指定与下一行表达式之间的逻辑运算符。 所有的行构成一个逻辑表达式。

  11. 4.1 创建查询 设置排序依据 (在“排序依据”选项卡中设置) （1）“排序条件”列表框中顺序决定了排序的优先权。 （2）用于排序的字段一定是已选定的输出字段。

  12. 4.1 创建查询 设置分组依据（在“分组依据”选项卡中设置） （１）所谓分组就是将多个类似的记录作为一组，压缩成一个 结果记录，完成基于一组记录的计算 。 例2：统计各职称人数。 教授 副教授 压缩统计 按职称分组 讲师 助教

  13. 4.1 创建查询 （2）分组一般与某些合计函数联合使用。 COUNT( )——用于计数的函数 SUM( )——用于求和的函数 AVG( )——用于求平均的函数 MAX( )——求最大值函数 MIN( )——求最小值函数 （3）用于分组的字段不一定是已选定的输出的字段。 （4）分组字段不能是一个计算字段，如平均工资。 （5）如果在输出字段中使用了合计函数，但在“分组”选项卡中并没有设置分组项，则在整个表上进行合计。 （6）选项卡中的“满足条件”按钮用于对查询结果进行筛选。要与“筛选”选项卡相区别。

  14. 4.1 创建查询 例3: 查询哪些出版社出版的图书数目在10本以上， 按出版社编号排序输出。 ① 打开查询设计器，添加ts表； ② 设置输出字段：cbsbh,COUNT(*) AS 图书数目 ③ 设置排序依据：cbsbh； 图书数目>10：筛选？，满足条件？ ④ 分组：？，依据：?

  15. 4.1 创建查询 例4 ：查询出版图书平均单价在100元以上，且出版的所有图书 单价都在50元以上的出版社。 选定字段：cbsbh, AVG(dj) AS 平均单价 分组： cbsbh 满足条件：min(dj)>=50 AND 平均单价>=100 分析一下,如果按照下面条件完成查询,查询到的是哪些出版社? 选定字段：cbsbh, AVG(dj) AS 平均单价 筛选： dj>=50 分组： cbsbh 满足条件：平均单价>=100

  16. 设置 结果 的记 录范 围 是否允许重复记录的存在 4.1 创建查询 查询结果的其他设置（在“杂项”选项卡设置）

  17. 4.1 创建查询 例5: 查询出版社编号为“B004”的出版社出版的图书单价最高 的5本，查询结果输出到表temp中。 操作步骤如下： 打开查询设计器，添加表ts； 字段：选择全部字段； 筛选条件：cbsbh=”B004”； 排序依据：dj，降序； 杂项：列在前面的记录：5个. 查询结果输出到表temp中?

  18. 4.1 创建查询 查询结果的输出类型 点击快捷菜单“查询去向”子菜单或查询工具栏中的“查询去向”按钮进行设置

  19. 4.1 创建查询 运行查询 ①单击“常用”工具栏上的“！”运行按钮； 或菜单【查询】→【运行查询】 ②在“项目管理器”中选择要运行的查询，单击“运行”按钮。 ③用DO命令来运行查询。 例如：DO chaxun1.qpr 注意: .qpr扩展名不能丢掉!

  20. 4.1 创建查询 4.1.3 使用查询设计器创建多表查询 联接（Jion）: 查询或视图的一个数据库操作。 两张表联接的结果是一张新表。 与单表创建查询的不同之处：  表的数目是两张或两张以上（超过2张，注意表的添加顺序， 纽带表要中间添加)；  多张表之间需建立联接条件。 数据库表、自由表或视图 两张表中仅满足联接条件的记录，这是最普通的联接类型 表中在联接条件左边的所有记录，和表中联接条件右边的且满足联接条件的记录 联接类型： 内联接(Inner Join) 左联接(Left Outer Join) 右联接(Right Outer Join) 完全联接(Full Join) 表中在联接条件右边的所有记录，和表中联接条件左边的且满足联接条件的记录 表中不论是否满足条件的所有记录

  21. 4.1 创建查询 例6: 查询“赵晗”和“徐超”两位读者所借阅的图书。查询结果按姓名排序，姓名相同再按书号排序，结果输出到屏幕。 jy表是纽带表，中间添加 操作步骤如下： 打开查询设计器，添加表dz、jy和ts 字段：dz.xm、ts.sh、ts.sm 联接： 筛选：dz.xm=”赵晗” OR dz.xm=”徐超” 排序依据：dz.xm，ts.sh 查询去向：屏幕

  22. 4.1 创建查询 例7：查询哪些读者没有借阅记录。 操作步骤如下： 打开查询设计器，添加表dz、jy 字段：dz.dzbh、dz.xm 联接：左联接，dz.dzbh=jy.dzbh 联接类型？联接条件？ 满足条件：jy.dzbh IS NULL 由于是对查询结果（左联接得到的结果）进行筛选，因此不能在“筛选”选项卡中设置。

  23. 4.1 创建查询 查询的实质 利用查询设计器所做的设计工作，实质上是在查询文件中生成并保存为一个SELECT -SQL命令。

  24. 4.2 视图的创建和使用 • 视图就是一种“虚表”类型，其数据来源于一张或多张表。 • 视图是数据库的一个组成部分。 • 视图兼有表和查询的特点。 与查询相似的地方：可以从一张或多张相关联的表中提取有用信 息； 与表相似的地方：可以用来更新其中的信息，并将更新结果反映到 源数据表中。 • 视图可以分为：本地视图和远程视图。

  25. 4.2视图的创建和使用 4.2.1 创建本地视图 创建本地视图的方法： 视图向导、视图设计器、CREATE SQL VIEW命令 视图设计器比查询设计器多一个“更新条件”选项卡。

  26. 4.2视图的创建和使用 1、使表可更新 指定视图所使用的哪些表是可以修改的 显示视图的所有字段 “钥匙”符号列为关键字段 “铅笔”符号列为可更新字段 字段中必须要有关键字段，否则源表中的字段都不能修改 设置是否允许对基表的更新

  27. 4.2视图的创建和使用 2、设置关键字段 VFP用关键字段来唯一标识那些已在视图中修改过的源表的更新记录。 设置关键字段可用来检查更新冲突。 恢复关键字段的初始设置 将除了关键字段以外的所有字段设置为可更新 管理多个用户访问同一数据时，如何更新记录。 在更新之前，检查源表中的相应字段在其数据被提取到视图之后，是否又发生了变化。如果源表中的这些数据在此期间已被修改，则不允许进行更新操作。

  28. 4.2视图的创建和使用 例8： 基于图书表（ts），建立单价（dj）100元以下的图书视图 ts_view，并修改书名(sm)为“支离破碎”的图书的单价为 33.6（原价为43.6），并且将修改后的单价反映在ts表中。 操作步骤： ① 打开视图设计器； ② 字段：ts表全部字段 ③ 筛选：dj<100 ④ 更新条件：关键字段(sh)，更新字段(zz,dj,bz)，发送SQL更新 ⑤ 保存视图（文件名ts_view），关闭视图设计器 ⑥ 浏览视图ts_view，同时打开ts表的浏览窗口 ⑦ 修改视图中图书“支离破碎”的单价为33.6, 移动记录指针 ⑧ 观察ts表浏览窗口中对应的记录

  29. 4.2视图的创建和使用 4.2.3 创建参数化视图 • 使用参数化视图可以用来限定视图的作用范围，可以避免每取一部分记录就需要单独创建一个视图的情况。 • 参数化视图在视图的创建时加入一个筛选条件，从而仅下载那些符合筛选条件的记录 • 参数值可以在运行时传递，也可以以编程方式传递。 例9：创建一个通用视图，根据提供的出版社名称下载该出版社 出版的图书信息。

  30. 4.2视图的创建和使用 4.2.4 创建远程视图 • 远程视图是通过ODBC基于远程数据源建立的视图 • ODBC(Open DataBase Connectivity,即开放式数据库互连)，是一种用于数据库服务器的标准协议，通过ODBC可访问多种数据库中的数据。 • 可安装多种数据库的ODBC驱动程序，从而使VFP可以与多种数据库相连接，访问数据库中的数据。

  31. 例10：有一Access数据库“教学管理.mdb”，现在需要在VFP中访问该数据库。例10：有一Access数据库“教学管理.mdb”，现在需要在VFP中访问该数据库。 1．使用“连接设计器”创建连接 4.2视图的创建和使用 选择数据源为：MS Access Database，单击“验证连接”按钮，提示”连接成功”后，保存连接为“acc_1”

  32. 2．创建远程视图 4.2视图的创建和使用 ①在项目管理器“数据”选项卡的某数据库中，选择“远程视图”，单击“新建”按钮,弹出“选择连接或数据源”对话框 ②选择连接Acc_1，单击“确定”按钮，弹出“选择数据库”对话框 ③选择“教学管理.mdb”数据库，单击“确定”按钮，打开视图设计器，同时弹出“打开”对话框 ④在“打开”对话框中添加要访问的表到视图设计器 ⑤在“视图设计器”中设计视图，方法与设计本地视图一样 ⑥保存视图,关闭远程视图设计器 ⑦浏览该视图，就可以看到要了解的信息了 如果要把这些信息存储为.dbf格式，可以基于该视图创建查询，并且指定查询去向为“表”

  33. 4.2视图的创建和使用 4.2.5 视图的使用 1. 视图的打开、浏览与关闭： • 与表的相应操作基本一致。需要说明的是： • （1）一个视图在使用时，其实质是作为临时表在自己的工作区打开。 • （2）如果视图是本地视图，则在打开视图的同时，在其他工作区中同时打开基表。 • （3）如果视图是远程视图，则只打开此视图，基表并不打开。 • （4）关闭视图时，随着视图的关闭，基表并不自动关闭，必须另外发出命令关闭基表。

  34. 4.2视图的创建和使用 2．定制视图 在视图设计器的“字段”选项卡中，单击“属性”按钮，弹出“视图字段属性”对话框 可以与数据库表一样，为视图设置注释、字段标题、字段注释、字段的默认值等属性

  35. 4.2.6 查询与视图的联系与区别 1．查询与视图的联系 （1）都是检索数据的方法。 （2）本质上都是SELECT-SQL命令。 4.2视图的创建和使用 2．查询与视图的区别 （1）视图可以更新数据并返回源表，而查询中的数据不能被修改。 （2）视图只能从属于某一个数据库，而查询是一个独立的文件。 （3）视图既可以访问本地数据源，又可以访问远程数据源，而查询只能访问本地数据源。 （4）视图的输出形式单一，而查询的输出形式多样。 （5）视图是数据库中的一个对象，可以作为数据源。而查询不能作为其他查询或视图的数据源。

  36. 3．视图的优点 4.2视图的创建和使用 • 视图提高了数据库应用的灵活性 • 视图减少了用户对数据库物理结构的依赖 • 视图可支持网络应用

  37. 4.3 SELECT-SQL语句 4.3.1 概述 SQL（Structured Query Language，结构化查询语言）是美国国家标准局ANSI确定的关系型数据库语言的标准。VFP支持SQL。 • VFP支持如下SQL命令： • CREATE TABLE-SQL • ALTER TABLE-SQL • INSERT-SQL • UPDATE-SQL • DELETE-SQL • SELECT-SQL

  38. 4.3 SELECT-SQL语句 4.3.2 SELECT-SQL 命令 SELECT-SQL命令的语法： 杂项选项卡 SELECT [ALL | DISTINCT] [TOP nExpr [PERCENT]][Alias.] Select_Item [AS Column_Name][, [Alias.] Select_Item [AS Column_Name] ...] FROM [FORCE][DatabaseName!]Table [[AS] Local_Alias][[INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] JOINDatabaseName!]Table [[AS] Local_Alias][ON JoinCondition …] [[INTO Destination]| [TO FILE FileName [ADDITIVE] | TO PRINTER [PROMPT] | TO SCREEN]] [WHERE JoinCondition [AND JoinCondition ...][AND | OR FilterCondition [AND | OR FilterCondition ...]]] [GROUP BY GroupColumn [, GroupColumn ...]] [HAVING FilterCondition] [UNION [ALL] SELECTCommand] [ORDER BY Order_Item [ASC | DESC] [, Order_Item [ASC | DESC] ...]] 字段选项卡 联接选项卡 查询去向 筛选选项卡 分组选项卡 分组选项卡中的满足条件 排序选项卡

  39. 4.3 SELECT-SQL语句 1、杂项 [ALL/DISTINCT]：设置是否允许重复记录。 [TOP nExpr[PERCENT]]：设置结果的记录范围。 2、查询内容：[Alias.] Select _Item [AS Column_Name]子句 (1) Alias.限定匹配项的名称。如果多个项具有相同的名称,则应在这些项名前加上表的别名和一个句点,以防止出现重复的列。 （2） Select _Item 指定包含在查询结果中的项，可以是字段、常量、表达式和用户自定义的函数。 注：当查询需要分组时，常需要用到一些合计函数AVG（）、 COUNT（）、SUM（）、MIN（）、MAX（）。 如查询结果中包含源表中的所有字段，可使用“*”符号。 （3） AS Column_Name 指定查询结果中的各项的标题。

  40. FILE 文本文件名 ARRAY 数组名 PRINTER INTO CURSOR临时表名 TO DBF 表名 SCREEN 4.3 SELECT-SQL语句 3、查询对象：FROM子句——列出所有从中检索数据的表。 注： （1）Local_Alias 为表指定一个本地别名。如果指定了本地别名，那么在整个语句中都必须用这个别名来代替表名。 （2） 如果是多表查询，要确保联接条件的格局应该是“链式”的，即第一个联接条件右边的表别名应该是第二个联接条件左边的表别名，以此类推。所以一个表别名最多出现两次（左一次，右一次）。 4、查询结果去向：INTO子句——指定在何处保存查询结果。 查询结果去向是可选项，缺省结果是查询浏览窗口。

  41. 4.3 SELECT-SQL语句 5、条件查询：WHERE子句—— 指定筛选条件。 （1） SELECT-SQL不遵守用SET FILTER指定的筛选条件。 （2）FILTERCONDITON筛选条件可以包含子查询，但嵌套不能太深。 （3）当多表查询时，也可以用WHERE子句实现多表之间的查询。 6、分组查询：GROUP BY子句—— 按列的值对查询结果进行分组。 （1）GROUPCOLUMN可以是字段名或表达式，也可以是该列在查询结果中的列位置（最左边的列编号为1） （2）用于分组的字段不一定是输出项。 （3）HAVING子句指定包括在查询结果中的组必须满足的筛选条件。

  42. 4.3 SELECT-SQL语句 7、查询结果的排序：ORDER BY子句——根据列的数据对查询结果进行排序。 （1）每个ORDER_ITEM必须对应查询结果中的一列。 （2） ORDER_ITEM可以是输出列的列名或别名，也可以是查询结果中的列位置（最左边的列编号为1） （3）ASC 指定以升序排列，是ORDER BY的默认选项。 DESC指定以降序排列。

  43. 4.3 SELECT-SQL语句 8、联合查询：UNION子句 —--把一个SELECT-SQL语句的查询结果和另一个SELECT-SQL语句的查询结果组合起来。 （1）默认情况下， UNION检查组合结果并排除重复的行；如需要重复的行，则加上[ALL]子句。 （2）要组合多个UNION子句，可以使用括号。 UNION子句要遵守下列的规则： • 不能使用UNION来组合子查询 • 两个SELECT-SQL命令的查询结果中的列数必须相同，对应列必须有相同的数据类型和宽度 • 只有最后的SELECT-SQL中可以包含ORDER BY子句，而且按编号指出所输出的列

  44. 4.3 SELECT-SQL语句 4.3.3 SELECT-SQL命令应用举例 1、单表查询 例11: 基于ts表，查询书名多于20个字符或者单价在百元以上 的图书的书号、书名和单价。 SELECT sh,sm,dj FROM ts ; WHERE LEN(ALLT(sm))>20 OR dj>100 例12：查询ts表中单价最高的3本图书的书号、书名和单价。 SELECT TOP 3 sh,sm,dj FROM ts ORDER BY dj DESC 例13：基于ts表，查询各出版社出版图书的数量和平均单价， 按出版社编号排序。 SELECT cbsbh,COUNT(*) AS 数量,AVG(dj) AS 平均单价; FROM ts GROUP BY cbsbh ORDER BY cbsbh

  45. 4.3 SELECT-SQL语句 2、多表查询 例14 : 基于借jy表和ts表，查询读者已经借阅的图书有哪些，输出书名，且每本书只输出一次。 SELECT DISTINCT sm FROM ts JOIN jy ON jy.sh=ts.sh 例15 : 基于jy表和ts表，查询哪些图书被借阅3次(含3次)以上，输出书名和借阅次数。 SELECT sm,COUNT(*) AS 借阅次数 FROM ts,jy ； WHERE jy.sh=ts.sh GROUP BY 1 HAVING 借阅次数>=3 例16：基于图书表ts和借阅表jy，查询2010年以后的借阅记录， 输出书名、书号、读者编号、借书日期和还书日期，按借书日 期降序排序，查询结果输出到表A1中。 SELECT sm,jy.* FROM ts INNER JOIN jy ON jy.sh=ts.sh; WHERE jsrq>={^2010-01-01} ORDER By jsrq DESC； INTO TABLE a1

  46. 4.3 SELECT-SQL语句 例17：基于读者表dz和借阅表jy，查询读者借书的情况，包括那些 没有借阅记录的读者。 SELECT dz.dzbh,xm,sh,jsrq,hsrq FROM dz LEFT JOIN jy; ON dz.dzbh=jy.dzbh 例18：基于dz表、jy表和ts表，查询每个读者所借书的书名和借书日期，按读者编号升序排序，同一读者按借书日期降序排序。 SELECT c.dzbh,xm,a.sh,sm,jsrq; FROM ts a JOIN jy b JOIN dz c ; ON b.dzbh=c.dzbh ON a.sh=b.sh ORDER BY 1,5 DESC

  47. 3.子查询 4.3 SELECT-SQL语句 • 子查询就是嵌套在另一个查询语句中的查询语句 • 子查询也可以用于构造其它SQL语句的筛选条件 子查询在语法上有以下一些限制： （1）子查询必须放在圆括号()中。 （2）外层查询的输出项不能来自于子查询的数据源表。 （3）子查询不能与TO、INTO子句及ORDER BY子句连用。 Visual FoxPro中的子查询： IN子查询、EXISTS子查询、运算符子查询

  48. 4.3 SELECT-SQL语句 • IN子查询： • 谓词IN用于判断列表达式的值是否是其右边集合的一个元素 • IN子查询的功能就是用其查询结果构成一个集合的元素列表 • IN子查询的内层查询的输出项只能是一项 • EXISTS子查询： • 谓词EXISTS用于判断一个子查询的查询结果中是否存在记录，如果有，运算结果为真，否则为假。 • EXISTS子查询具有以下特点： • （1）外层查询的WHERE子句不需列名、列表达式。 • （2）子查询的输出项常用“*”，因为EXISTS不返回具体的值， • 只返回真或假。

  49. 4.3 SELECT-SQL语句 比较运算符子查询 • 子查询结果必须返回单值，除非使用ANY、SOME、ALL等量词 • 子查询的输出项可以使用统计函数。 量词对子查询结果的影响

  50. 4.3 SELECT-SQL语句 例 19： 基于读者表dz和借阅表jy，查询没有借阅记录的读者。 SELECT * FORM dz WHERE dzbh NOT IN； (SELECT dzbh FROM jy) 或 SELECT * FROM dz WHERE NOT EXISTS ； (SELECT * FROM jy WHERE dzbh=dz.dzbh) 例20：查询出版了书号以“Ｂ”开头图书的所有出版社的名称。 SELECT cbsmc FROM cbs a WHERE EXISTS; (SELECT * FROM ts b ; WHERE sh LIKE ”B%” AND a.cbsbh=b.cbsbh) 例21： 基于图书表ts和出版社表cbs，查询一些图书的信息，要求其 单价高于 “中华书局”出版的任何一本图书的单价。 SELECT * FROM ts WHERE dj>; ANY(SELECT dj FROM ts a JOIN cbs b ； ON a.cbsbh=b.cbsbh WHERE cbsmc=”中华书局”)

More Related