VF查询与视图设计

1、 数据查询是数据处理中最常用的操作之一，查询可以从一个或多个表中提取所需要的数据，可以通过设计相应的查询或视图来实现，是快速方便地从数据库中提取数据的一种方法。 查询实际上是指扩展名为.QPR的查询文件，其主体是SQL SELECT语句，视图则兼有表和查询的特点，是在数据库表的基础上建立的一个虚拟表，视图不能独立存在而是被保存在数据库中。查询和视图的设计可以用相应的设计器来实现，也可以使用SQL语言实现。 6 61 1 查询设计查询设计 查询可以实现对数据库中数据的浏览、筛选、排序、检索、统计及加工等操作；利用查询可以为其他数据库提供新的数据表，可以从单个表中提取有用的数据，也可以从多个表中提

2、取综合信息。 然后按需要定向输出查询的结果，查询结果输出类型有：浏览器、表、临时表、报表、标签、屏幕等。查询是以扩展名为QPR的文本文件保存在磁盘上，它的主体是SQL SELECT语句以及与定向输出有关的语句。 6 61 11 1 查询设计器查询设计器1 1启动查询设计器启动查询设计器 打开查询设计器 ，常用的有菜单操作和命令操作。方法一：CREATE QUERY方法二：选择“文件”“新建”选项，或单击“常用”工具栏上的“新建”按钮，打开“新建”对话框，然后选择“查询” 。方法三：在项目管理器中的“数据”选项卡下选择“查询”，然后单击“新建”命令按钮。 打开如图6-1“添加表或视图”对话框，选

3、择用于建立查询的表或视图。选中所需要的表或视图，单击“添加”按钮将表或视图添加到查询设计器中。当选择完表或视图后，单击“关闭”按钮进入查询设计器界面如图6-2。 图6-1 为查询添加表或视图添加“学生”、“选课成绩”、“课程”三个表图6-2 查询设计器窗口 查询基于多个表时，表之间必须有联系。查询设计器自动根据联系提取联接条件，否则还会打开一个指定联接条件的对话框，由用户来确定联接条件，如图6-3所示的联接条件对话框。 图6-3 联接条件对话框 2 2查询设计器的选项卡查询设计器的选项卡 查询设计器界面有6个选项卡，其功能和SQL SELECT语句的各个短语是相对应的。前面已经选择了设计查询的

4、表或视图，对应于FROM短语。 字段：在该选项卡中设置查询结果中要包含的字段，对应于SELECT短语中的输出字段，在“可用字段”列表框中选中要显示的字段，添加到“选定字段”列表框中；如果要输出全部字段，单击“全部添加”按钮；在“函数和表达式”编辑框中可以输入、编辑或由“表达式生成器”生成一个计算表达式，由此生成一个计算字段。 联接：该选项卡对应于JOIN ON短语，用于编辑联接条件，从多个表中查询字段信息。 筛选：该选项卡对应于WHERE短语，用于指定查询条件。 排序依据：该选项卡对应于ORDER BY短语，用于指定排序的字段（将需要排序的字段添加到排序条件框中）和排序方式（升序或降序）。 分

5、组依据：该选项卡对应于GROUP BY短语和HAVING短语，用于设置分组条件。将需要分组的字段添加到分组字段框中，如需要设置HAVING分组条件，单击“满足条件”按钮，打开“满足条件”窗口，设置分组条件。 杂项：在该选项卡中可以指定是否需要显示重复记录（对应于DISTINCE）以及是否显示全部记录或只显示前面有多少个记录（对应于TOP短语）等。 6 61 12 2 建立查询建立查询例6-1： 在学生、选课成绩和课程三个表中设计查询，要求：只查询出学生所修课程号是以“G”开头的课程，并以课程号分组，统计出各组的期末分的平均值，并且只显示期末分=80的记录，按课程名称的降序排列，最后输出学号、姓

6、名、课程号、课程名称等字段和期末平均分计算字段。1启动查询设计器，将学生、选课成绩和课程3个表添加到查询设计器中，并建立这3个表的联接，打开如图6-2建立查询的设计器窗口。 2 2选取查询所要显示的字段选取查询所要显示的字段 在“字段”选项卡中选择“学号”、“姓名”、“课程号”和“课程名称”字段；要显示各组课程的期末分平均值，这是一个计算字段，则在“函数和表达式”框中输入计算表达式或打开“表达式生成器”对话框，如图6-4所示，在该对话框中编辑计算表达式“AVG(选课成绩.期末分) AS 期末平均分”，其功能是求出各组课程的期末平均分，并以“期末平均分”为字段名输出。添加选定字段和计算字段如图6

7、-5所示。 创建计算字段“期末平均分”图6-4 表达式生成器添加字段以及计算字段单击“查询设计器”工具栏上的“SQL”按钮，可以显示和该操作相对应的SQL命令：SELECT 学生.学号, 学生.姓名, 课程.课程号, 课程.课程名称, AVG(选课成绩.期末分) AS 期末平均分 图6-5 选定的字段及计算字段 3 3设置筛选条件设置筛选条件 选中“筛选”选项卡，筛选出所有的课程中以“G”开头的课程，如图6-6所示。 SQL SELECT语句中相当于： WHERE 课程.课程号 LIKE “G%” 图6-6 设置筛选条件 4 4设置排序查询设置排序查询 选中“排序依据”选项卡，将“课程名称”字

8、段添加到“排序条件”列表框中，并选择单选按钮“降序”，按“课程名称”的降序排序。 在SQL SELECT语句中相当于： ORDER BY 课程.课程名称 DESC 图6-7 设置排序依据5 5设置分组依据设置分组依据 在设置“筛选”条件基础上筛选出所有以“G”开头课程号后，再以“课程号”进行分组，分组的目的是统计各组期末平均分。将“课程号”添加到“分组字段”列表框中，以“课程号”分组。同时要对期末平均分再作一个筛选，既筛选出某组的期末平均分大于等于80的课程信息，因此单击“满足条件”按钮，打开“满足条件”窗口，在该窗口中设置筛选条件。如图6-8 ，图6-9 。图6-8 设置分组依据图6-9 设

9、置分组后的筛选条件图6-10 没有设置筛选条件的显示结果图6-11 设置筛选条件的显示结果 SQL SELECT语句中相当于： GROUP BY 选课成绩.课程号 HAVING AVG(选课成绩.期末分)=80 6设置杂项 在上述设置的基础上，如只想显示查询结果的前两条记录，则在“杂项”选项卡中取消“全部”复选项前的“”，并在“记录个数”数据显示框中输入“2”，如图6-12所示。运行后显示结果如图6-13所示。 图6-12 设置显示记录的个数图6-13 设置杂项后运行结果单击“SQL”按钮，则显示出相应的SQL SELECT查询代码： SELECT TOP 2 学生.学号, 学生.姓名, 课程

10、.课程号, 课程.课程名称,AVG(选课成绩.期末分) AS 期末平均分; FROM 学生 INNER JOIN 选课成绩; INNER JOIN 课程 ; ON 选课成绩.课程号 = 课程.课程号 ; ON 学生.学号 = 选课成绩.学号; WHERE 课程.课程号 LIKE G%; GROUP BY 选课成绩.课程号; HAVING AVG(选课成绩.期末分) = 80; ORDER BY 课程.课程名称 DESC 6 61 13 3 查询文件的操作查询文件的操作 使用查询设计器设计查询时，可以边设计、边运行，边查看运行结果，再修改、再运行，直至达到满意的效果。 1 1运行查询文件运行查询

11、文件 在查询设计器中运行 当查询设计器窗口处于打开或选中状态下，系统菜单上增加了一个“查询”菜单，选择“查询”菜单中的“运行查询”选项，或单击常用工具栏上的“运行”按钮，显示查询结果。 利用菜单选项运行 当在查询设计器中保存查询文件后，或保存查询文件后关闭查询设计器，单击“程序”菜单中的“运行”命令，打开“运行”对话框，选中要运行的查询文件，单击“运行”按钮，可显示查询结果。 利用命令方式运行 在命令窗口中输入运行查询文件的命令， 其格式为：DO 注意：查询文件名不能省略扩展名.QPR。 2 2修改查询文件修改查询文件 当查询设计完后，在任何时候都可以使用查询设计器来修改已建立的查询文件。打开

12、查询设计器方法有： 选择“文件”“打开”选项，在“打开”对话框中指定文件类型为“查询”，选择要打开的查询文件，单击“确定”按钮。 打开查询设计器 在命令窗口中输入打开查询设计器命令， 命令格式为：MODIFY QUERY 举例来对例61建立的查询文件进行修改，修改为：使其统计的是所有课程号的第1个字符不是“G”的各组课程的平均分，按各组平均分的降序排列，并显示查询的所有记录。 设置“筛选”查询条件：如图6-14所示。 图6-14 修改筛选条件的设置 修改排序顺序 将以“课程名称”为排序依据的字段移到右边的“选定字段”列表框内，再将“AVG（选课成绩期末分）”这个计算字段添加到“排序条件”列表框

13、内，表明是按期末分平均值的降序排列。如图6-15所示。 图6-15 修改排序依据的设置 修改杂项选项卡 单击“杂项”选项卡，单击“全部”复选框，显示满足条件的所有记录。如图6-16所示。 图6-16 显示满足条件的所有记录3 3定向输出查询结果定向输出查询结果 在查询设计器中可以根据需要为查询输出确定查询去向。如果不选择查询结果的去向，系统默认将查询结果显示在“浏览”窗口中；如果要将查询结果指定某个地点，可以选择输出目的地。选择“查询”菜单下的“查询去向”，或在“查询设计器”工具栏中单击“查询去向”按钮，则将打开一个“查询去向”对话框，如图6-18所示。 图6-18 查询去向对话框查询去向的具

14、体含义如下： 浏览：在浏览（BROWSE）窗口中显示查询结果，此选项为默认方式。 临时表：将查询结果保存到一个临时的只读表中。 表：将查询结果保存到一个指定的表（.DBF）中。图形：将查询结果输出到图形文件（Microsoft Graph）中，Graph是包含在Visual FoxPro中的一个独立的应用程序。屏幕：将查询结果输出到Visual FoxPro主窗口或当前活动窗口中。报表：将查询结果输出到一个报表文件（.FRX）中。标签：将查询结果输出到一个标签文件（.LBX）中。 6 61 14 4 查询设计器的局限性查询设计器的局限性 如果用户对SQL SELECT语句非常熟悉和了解，是可以

15、直接用各种文本编辑器来编写SQL SELECT语句建立查询，只是将文件的扩展名保存为.QPR即可。而使用查询设计器相对来说比较容易一些。但必须注意的是，查询设计器只能建立一些比较有规则的查询，对于复杂的查询它是无能为力的。比如建立一个内外层相互关联的子查询。 查询出选修了“G0002”课的学生，这些学生的期末成绩比选修了“G0006”课的最高期末成绩还要高的学生的学号和期末成绩。SQL SELECT语句如下： SELECT 学号, 期末分 FROM 选课成绩 WHERE 课程号=G0002 AND 期末分ALL(SELECT 期末分 FROM 选课成绩 WHERE 课程号=G0006) 上述的

16、查询涉及到嵌套查询（子查询），利用查询设计器是设计不出来的。 6 62 2 视图设计视图设计 6 62 21 1 视图的概念视图的概念 视图是一种虚表，兼有“表”和“查询”两者的特点。视图具有表的属性，视图的打开与关闭、设置属性、修改结构以及删除等，都与表的操作相同。视图又具有查询的特点，可以从一个或多个相关联的表中提取信息，与查询不同之处在于视图可以更新数据源表，而查询不能。视图可以用来更新其记录的值，并将更新结果送回到基本表（即源表）中并永久保存在磁盘上。 可以从本地表、其他已建立的视图、储存在服务器上的表或远程数据源中的表创建视图，因此视图可以分为本地视图和远程视图。 本节主要讨论本地视

17、图。 视图可以查询表中信息，也可以更新表中的记录值。然而视图是数据库中的一个特有功能，只有当包含视图的数据库打开时，视图才可以使用。 6 62 22 2 视图设计器视图设计器1 1打开视图设计器打开视图设计器 与查询是一个独立的文件不同，视图不能单独存在，它是数据库中的一部分，因此在打开视图设计器创建视图时，或者某视图已创建，打开视图设计器修改该视图时，必须首先打开某一个数据库，在数据库中建立视图。打开视图设计器的方法有： “文件”“新建”“视图”“新建文件”按钮，打开“添加表或视图”对话框（图6-1）,确定要创建的视图所需要的表或另一个视图，如果所需要的表不在数据库中，则单击“其他”按钮，添

18、加自由表。添加表后，打开视图设计器，如图6-20所示。 如果数据库设计器打开，在该窗口中单击右建，选择“新建本地视图”，如图6-21，打开“新建本地视图”对话框，如图6-22所示。单击“新建视图”按钮，同样可以打开图6-20窗口。 使用命令：CREATE VIEW 也可以启动视图设计器。图6-20 视图设计器图6-22 新建本地视图对话框图6-21 数据库中创建本地视图打开“新建本地视图”对话框2 2视图设计器的使用视图设计器的使用 视图设计器选项卡有7个，多了一个“更新条件”，如图6-23所示，其它的6个选项卡的功能和用法与查询设计器完全相同。图6-23 视图设计器“更新条件”选项卡 “更新

19、条件”选项卡用于设置更新数据的条件，其各选项的含义与用法如下： 表：列出添加到视图设计器中的所有表。其下拉列表中选择该视图允许更新的表。如果选择“全部表”，则在“字段名”列表框中显示在视图设计器中“字段”选项卡中选取的全部字段；如果只选择其中的一个表，则在“字段名”列表框中只显示该表中被选择的字段。 字段名：该列表框列出了可以更新的字段，但要设置每个表的关键字段后才可以设置该表中要修改的字段。钥匙符号含义为指定某字段是否为关键字段，如在字段前打上“”标志，表明该字段为关键字段；铅笔符号含义为指定某字段是否可以更新，如在字段前打上“”标志，表明该字段的值可以更新。一个表只能设置一个关键字段。 发

20、送SQL更新：指定是否将视图中的更新结果发送回源表中。 SQL WHERE子句：一般在多用户的环境下会出现多个人同时修改一个表的情况，“SQL WHERE子句”是用来检测更新冲突的，其选项是检测视图操作的数据在更新前是否被别的用户修改过，各项含义为： 关键字段：当源数据表中的关键字段被修改时，更新失败。 关键字和可更新字段：当源数据表的关键字段和可更新字段被修改时，更新失败。 关键字和已修改字段：当源数据表的关键字段和表中任一已修改过的字段被修改时，更新失败。 使用更新：指定后台服务器更新的方法。选项“SQL DELETE然后INSERT”含义： 在修改源数据表时，先将要修改的记录删除，再根据

21、视图中修改的结果插入一新记录； 选项“SQL UPDATE”含义： 根据视图中的修改结果直接修改源数据表中的记录。 6 62 23 3 建立视图建立视图 1 1建立单表视图建立单表视图例6-3 对“学生”表建立视图，要求输出学号、姓名、性别、出生日期和专业等字段。并且按学号的降序排列，可以更新学生的学号，同时设置学号的字段有效性，即更新时学生的学号必须是以“07”或“08”开头，如果不是，则弹出提示框，显示提示信息“学号以”07”或”08”开头”。操作步骤如下： 打开“学生管理”数据库，再打开视图设计器，将“学生”表添加到视图设计器窗口。 选中 “字段”选项卡，将学号、姓名、性别、出生日期和专

22、业等字段添加到“选定字段”列表框中。 单击“字段”选项卡中的“属性”按钮，打开如图6-25所示的“视图字段属性”对话框设置字段属性。比如可以进行字段有效性、显示格式等属性设置，但是不能修改数据类型、宽度和小数位数等属性。在图6-25中进行字段属性的设置： 字段：选择字段“学生.学号”。 规则：在文本框或表达式生成器中输入表达式：SUBSTR(学号,1,2)=”07” OR SUBSTR(学号,1,2)=”08” ，学号开头字符必须是“07”或“08” 信息：输入错误时显示信息：“学号以07或08开头”。 图6-25 设置视图字段属性 排序依据设置：按“学号”字段的降序排列。与建立查询的设置方法

23、相同。 更新设置：单击“更新条件”选项卡，设置更新，如图6-26所示。 图6-26 设置更新视图字段条件 保存视图：保存视图名为“视图1”，在“学 生管理”数据库中生成视图如图6-27。 单击“视图设计器”工具栏上的“SQL”按钮，代码：SELECT 学生.学号, 学生.姓名, 学生.性别, 学生.出生日期, 学生.专业 FROM 学生; ORDER BY 学生.学号 DESC 由此可见，视图实际上就是一条SQL命令。 图6-27 在数据库中建立视图“视图1” 在“学生管理”数据库中，双击建立的视图“视图1”，显示由视图查询到的数据。当在该视图中修改某个学生的“学号”数据时，如果“学号”不是以

24、“07”或“08”开头，则弹出出错信息框；当正确修改某个学生的学号时，运行该视图后，则在相应的“学生”表中，该学生的学号也作了相应的修改。 2 2建立多表视图建立多表视图例6-4 在“学生管理”数据库中建立视图，显示教师的教师代号、姓名、职称以及教师所教课程的课程号、课程名称和课时。并且在视图中将课程名称是“大学计算机基础”的课时数由64学时修改为72学时，将“叶明珠”教师的职称由“讲师”修改为“副教授”。修改后可以检测到建立该视图的源表的数据也作相应的修改。与创建单表查询步骤一样，不同的是： 添加三个表，并建立3表之间的关联。 图6-29 多表视图的设计 选择输出字段 设置更新：设置“教师代

25、号”和“课程号”为关键字段，确定要修改字段值的字段“职称”和“课时”；以及进行其它选项的设置如图6-30所示。 图6-30 多表字段更新条件设置 当将某一记录的课程名称为“大学计算机基础”的课时改为72学时，以及将某一记录的教师姓名为“叶明珠”的职称字段值改为“副教授”时，再次运行该视图，打开该视图的源表“教师”表和“课程”表后，可以看到相应的记录值作了修改。即“大学计算机基础”的课时改为72学时，“叶明珠”的职称改为“副教授”。 3 3视图参数视图参数 在利用视图进行信息查询时可以设置查询参数，在运行视图时通过用户输入的参数值来显示查询结果。例6-5 在“学生管理”数据库中建立视图，显示学号

26、、姓名、课程号、课程名称和期末分，并且在运行视图时根据输入学生的学号而任意进行查询。并且将学号为“07170102”学生的“大学英语”期末成绩改为“98”。 创建过程与上述例4讲解的一样，不同是： 设置筛选条件：在“筛选”卡上设置如图6-32所示。表明在运行该视图后，弹出“视图参数”对话框，要求输入某学生的学号后，再显示该学生的查询信息。图6-32 “视图参数”设置设置更新条件：要更新期末分，则先设定“选课成绩.课程号”为关键字段，再设定“选课成绩.期末分”为可修改的字段，以及其它设定如图6-33所示。 图6-33 设置更新条件 运行该视图：由于设置条件参数，运行后出现“视图参数”对话框，要求

27、输入需要查询某学生的学号，比如输入学号“07170102”，如图6-34。显示查询结果，如图6-35。 图6-34 输入“视图参数”对话框图6-35 视图运行结果6 62 24 4 视图的视图的SQLSQL语句语句 视图是一个从表中派生出来的虚表，视图可以引用一个或多个表，也可以引用其他已建立的视图，它依赖于表，但不能独立存在，必须依赖于某个数据库。 视图是根据对表的查询而定义的，其命令格式为：CREATE SQL VIEW REMOTE CONNECTION SHARE | CONNECTION AS 1 1从单个表派生出的视图从单个表派生出的视图例6-6 从教师表中查询出教师的姓名和职称。

28、定义视图名为shtu1。 视图一旦定义，就可以和基本表一样进行各种查询或进行一些修改，比如查询出教师表中的姓名和职称信息。2 2从多个表中派生出的视图从多个表中派生出的视图例6-8 从“选课成绩”表中查询出每个学生所修课程中的最高分，要求输出“学号”、“课程号”、“期末分”。定义视图名为shtu3。 MODIFY DATA MODIFY DATA 学生管理学生管理CREATE VIEW shtu1 AS SELECT CREATE VIEW shtu1 AS SELECT 姓名姓名, ,职称职称 FROM FROM 教师教师 SELECT SELECT * * FROM shtu1 FROM

29、shtu1或：或：SELECT SELECT 姓名姓名, ,职称职称 FROM shtu1FROM shtu1或：或：SELECT SELECT 姓名姓名, ,职称职称 FROM FROM 教师教师 CREATE VIEW shtu3 AS SELECT a.CREATE VIEW shtu3 AS SELECT a.学号学号,a.,a.课程号课程号,a.,a.期末分期末分 FROM FROM 选课成绩选课成绩 a WHERE a WHERE 期末分期末分=(SELECT MAX(=(SELECT MAX(期末分期末分) FROM ) FROM 选课成绩选课成绩 b b WHERE a.WHERE a.学号学号=b.=b.学号学号) ) 3 3视图中的计算字段视图中的计算字段 用查询来建立一个视图中的SELECT子句也可以包含算术表达式或函数，它们和视图的其他字段一样对待，由于这些算术表达式或函数是计算而来的，所以可以称之为计算字段。例6-10 统计修了“体育”课的男女生人数。定义视图名为shtu5。 CREATE VIEW shtu5 AS SELECT a.CREATE VIEW shtu5 AS SELECT a.性别性别,COUNT(,COUNT(* *) ) AS AS 男女生人数男女生人数; ;FROM FROM 学生学生 a, a,选课成绩选课成绩