VFP6教程第4章 查询与视图.docx

1、VFP6教程第4章 查询与视图第4章 查询与视图在软件开发中经常用到数据的查询，如学生成绩管理，人事档案，图书检索等软件，查询的准确、速度直接影响软件的质量、效率、应用及维护，VFP开发工具用三个途径解决查询问题。第一个途径就是运用SQL语句查询，第二个途径就是用查询设计器建立快速查询，第三个途径是用视图设计器建立视图，实现快速查询。4.1 查询的一般概念4.1.1 查询VFP中的查询使用查询设计器，从数据库表或自由表中获取有用数据，经过对查询条件、查询要求的设置，形成一个*.QPR文件，通过DO命令来执行。4.1.2 视图VFP中的视图是用视图设计器从数据库表中获取有用数据，与查询一样经过查

2、询条件、查询要求的设置形成视图。视图是以视图名的形式存在数据库中。视图中数据的更新可以使源表相应数据更新。视图的执行要在数据库中执行。4.1.3 SQL查询SQL是关系数据库的国际标准语言。为此，世界上许多语言开发商都将SQL语言作为数据库对数据存取的共同标准接口。有的已将SQL语言嵌入到语言开发工具中，使得人们在软件开发时运用数据库是极为方便。之所以SQL能成为国际标准语言，主要原因它是结构化的查询语言。它的综合统一体现在它集数据定义语言、数据操纵语言、数据控制语言的功能为一体。再就是它高度的非过程化，无需一步步地告诉计算机如何做，只要指名怎么做系统就可以自动完成。SQL语言简单便于掌握，特

3、别是在查询复杂可视化工具无法解决时，SQL可以解决。4.2 SQL语句4.2.1 SELECT查询语句1基本语句格式：SELECT INTO DBF | TABLE FROM WHERE 功能：从表名列表的表中查找符合条件的，按的形式显示。若选INTO DBF | TABLE子句，查询结果存入新表。说明：格式：ALL | DISTINCT TOP PERCENT 别名. 列名 AS 栏目名, 别名. 列名 AS 栏目名(1)ALL | DISTINCT子句：ALL输出结果有重复记录，是子句默认值。DISTINCT输出结果无重复记录。(2)TOP PERCENT子句：此子句TOP 是符合条件的内

4、容中取前个记录。PERCENT是取前面分之个记录。(3)别名. 列名 AS 栏目名 , 别名. 列名 AS 栏目名子句：列名可以是字段、含字段的表达式或表达式。指定输出结果中的字段，此子句也可用*代替此时显示表中所有字段。例4.1 SELECT 基本语句应用USE 学生1SELECT * FROM 学生1OPEN DATABASE 学生 &打开学生数据库SELECT * FROM 学生 INTO TABLE 学生y &显示学生表中所有内容SELECT 姓名,入学成绩 FROM 学生 WHERE 入学成绩=500SELECT DISTINCT 姓名,入学成绩 FROM 学生 WHERE 入学成绩

5、=500SELECT 学生.学号,学生.姓名,学生成绩.数学 FROM 学生,学生成绩 WHERE 学生.性别=女 AND 学生成绩.数学=60 AND 学生.学号=学生成绩.学号SELECT TOP 2 * FROM 学生 ORDER BY 学号CLOSE DATABASE ALL2SELECT中常用的系统函数在实际应用中经常在查询结果进行统计、求平均值、汇总等基本要求。SQL提供了一些常用的系统函数。如表4.1所示。表4.1 常用系统函数函数说明AVG()求的平均值COUNT()统计记录个数MIN()求的最小值MAX()求的最大值SUM()求的和：为DISTINCT|ALL 表达式。说明：

6、DISTINCT在计算时去掉重复值，ALL在计算所有值是默认值，表达式可为*、字段名、数学函数、常量、函数或表达式。3SELECT分组子句格式：GROUP BY , , HAVING 功能：对查询结果进行分组。HAVING 为指定分组必须满足的条件。例4.2 分组查询应用。OPEN DATABASE 学生SELECT AVG(入学成绩) FROM 学生SELECT COUNT(*) FROM 学生SELECT SUM(入学成绩) FROM 学生 GROUP BY 性别SELECT COUNT(*) AS 学生人数 FROM 学生 GROUP BY 性别 HAVING (入学成绩)460SELE

7、CT 学生.学号,AVG(学生.入学成绩),学生成绩.VFP FROM 学生,学生成绩 GROUP BY 性别 HAVING (入学成绩)=500 WHERE 学生.学号=学生成绩.学号4排序子句格式：ORDER BY ASC | DESC , ASC | DESC功能：查询结果按关键字排序，ASC升序为默认值，DESC为降序。例4.3 查询排序应用OPEN DATABASE 学生SELECT 学号,姓名,入学成绩 FROM 学生 ORDER BY 入学成绩 DESCSELECT 学生.学号,学生.姓名,学生成绩.英语 FROM 学生,学生成绩 WHERE 学生.学号=学生成绩.学号 GROU

8、P BY 学生.学号 ORDER BY 学生成绩.英语SELECT AVG(入学成绩) AS 入学成绩平均分 FROM 学生 GROUP BY 性别 ORDER BY 入学成绩 DESCCLOSE DATABASE ALL5嵌套查询嵌套查询就是在SELECT语句中套有SELECT语句，被套在内的SELECT子句通常称为子查询。(1)IN等谓词及比较运算符结合使用。例4.4 IN与NOT IN在嵌套查询中的应用OPEN DATABASE 学生SELECT * FROM 学生 WHERE 学号 IN(SELECT 学号 FROM 学生成绩 WHERE 英语=90)SELECT * FROM 学生

9、WHERE 学号 NOT IN(SELECT 学号 FROM 学生成绩 WHERE 英语=500)CLOSE DATABASE ALL(2)使用量词和谓词的嵌套查询格式： ANY | ALL | SOME (子查询) NOT EXIST(子查询)说明：ANY、ALL、SOME为量词，ANY与SOME是同义词，在查询时，只要子查询中有一行能使结果为.T.，则结果就为.T.。ALL要求子查询中所有行结果为.T.时，结果才能为.T.。EXIST或NOT EXIST是检查在子查询中是否有结果返回。EXIST为有结果返回为真否则为假。NOT EXIST 刚好与EXIST 相反。例4.6 查询数学成绩高于

10、90分的学生的学号，姓名。OPEN DATABASE 学生SELECT 学号,姓名 FROM 学生 WHERE EXIST(SELECT * FROM 学生成绩 WHERE 学号=学生.学号 AND 数学=90)例4.7 查找所有入学成绩大于等于500分年龄都小的学生。OPEN DATABASE 学生SELECT * FROM 学生 WHERE 出生ALL (SELECT 出生 FROM 学生 WHERE 入学成绩500)CLOSE DATABASE ALL6几个特殊运算符(1)BETWEEN AND运算符格式：BETWEEN AND 说明：表示在的范围。例4.8 查询入学成绩480-600分

11、的学生情况，与不在500-600之间的学生。OPEN DATABASE 学生SELECT * FROM 学生 WHERE 入学成绩 BETWEEN 490 AND 600SELECT * FROM 学生 WHERE 入学成绩 NOT BETWEEN 500 AND 600CLOSE DATABASE ALL(2)LIKE 运算符说明：LIKE 后含有%或_通配符的字符型表达式。%表示0个或多个字符，_表示一个字符。例4.9 查询姓王的所有学生情况，查询姓名前两个字为王丽的学生情况。OPEN DATABASE 学生SELECT * FROM 学生 WHERE 姓名 LIKE 王%SELECT *

12、 FROM 学生 WHERE 姓名 LIKE 王丽_CLOSE DATABASE ALL7连接查询格式： INNER | LEFT OUTER | RIGHT OUTER | FULL OUTER JOIN ON 说明：(1)INNER为内联接。内连接是指按连接条件合并两个表，只要满足条件的记录出现在结果中，内联接可以省略INNER。例4.10 查找学生专业库中，学生情况及选修课成绩情况OPEN DATABASE 学生专业SELECT * FROM 学生专业 INNER JOIN 选修 ON 学生专业.学号=选修.学号CLOSE DATABASE ALL(2)LEFT JOIN为左连接，在满足

13、连接条件的记录出现在结果中基础上，第一个表不满足条件的记录也出现在结果中。若加OUTER只是除了同以上连接为外连接，OUTER表示强调，OUTER通常省略。例4.11用左联接查找学生情况与选修课情况。OPEN DATABASE 学生专业SELECT * FROM 学生专业 LEFT JOIN 选修 ON 学生专业.学号=选修.学号CLOSE DATABASE ALL(3)RIGHT JOIN为右连接，在满足连接条件的记录出现在结果中基础上，第二个表不满足条件的记录也出现在结果中。例4.12 用右连接查找学生情况与选修课情况OPEN DATABASE 学生专业SELECT * FROM 学生专业

14、 RIGHT JOIN 选修 ON 学生专业.学号=选修.学号CLOSE DATABASE ALL(4)FULL JOIN为全连接。即满足连接条件的记录出现在结果上，两表不满足连接条件的记录也出现在结果上。例4.13 用全连接查找学生情况与选修课情况OPEN DATABASE 学生专业SELECT * FROM 学生专业 FULL JOIN 选修 ON 学生专业.学号=选修.学号CLOSE DATABASE ALL8UNION子句也称集合并运算，用此子句可以将多个表的查询结果，合并成一个结果，要求查询结果具有相同字段与个数，且对应字段类型要兼容。例4.14 建立三个表，计算机应用、计算机软件、

15、计算机硬件它们的结构为专业 C(3)，学号C(9)，姓名C(6)，数学 C(N, 3)，英语C(N, 3)，C语言C(N, 3)然后建立一个数据库，计算机并将三个表添入库中，然后用UNION查看所有内容。3个表内容如表4.2所示表4.2 计算机专业学生成绩专业学号姓名数学英语C语言计算机应用105010101刘晓华809060计算机应用105010102李立明9010070计算机软件105020101王雪梅9070100计算机软件105020102李德勇608068计算机硬件105030101方芳8090100计算机硬件105030102王良909260CREATE 计算机应用INDEX ON

16、 学号 TAG t1CREATE 计算机软件INDEX ON 学号 TAG t2CREATE 计算机硬件INDEX ON 学号 TAG t3CREATE DATABASE 计算机ADD TABLE 计算机应用ADD TABLE 计算机软件ADD TABLE 计算机硬件SELECT * FROM 计算机应用 UNION ALL;SELECT * FROM 计算机软件 UNION ALL;SELECT * FROM 计算机硬件 CLOSE DATABASE ALL9关于查询结果的存放(1)用INTO DBF | TABLE可将查询结果存放表中。(2)用INTO ARRAY 将查询结果存入数组。(3

17、)用INTO CURSOR 将查询结果存入临时表。(4)用TO FILE ADDTIVE将结果存入文本文件，用ADDITIVE将结果追加到由指定的文本文件尾部。否则将覆盖原有文件。(5)用TO PRINT PROMPT将查询结果输出到打印机，若选PROMPT 选项在打印前打开打印机设置对话框。例4.15 关于结果存放的应用。OPEN DATABASE 学生SELECT * FROM 学生 INTO TABLE 新学生SELECT * FROM 学生 INTO ARRAY aSELECT * FROM 学生 INTO CURSOR 临时学生SELECT * FROM 学生 TO FILE 学生文

18、本CLOSE DATABASE ALL10SELECT 完整语句格式格式：SELECT ALL | DISTINCT TOP PERCENT 别名. 列名 AS 栏目名 , 别名. 列名 AS 栏目名FROM FORCE 数据库名! AS INNER | LEFT OUTER | RIGHT OUTER | FULL OUTER JOIN ! AS ON INTO | TO FILE ADDITIVE | TO PRINT PROMPT | TO SCREENPREFERENCE 参数名 NOCONSOLE PLAIN NOWAITWHERE AND AND | OR GROUP BY , H

19、AVING UNION ALL SELECT 命令ORDER BY ASC | DESC , ASC | DESC从以上SELECT 格式看很长也很复杂，但在前面已将常用的重要的部分介绍过了。下面对不常用的子句的作用、形式作一介绍。说明：(1)FORCE用来在按连接条件来连接表查询时，VFP 不进行优化。(2)PREFERENCE子句：用于记载浏览窗口的配置参数，再次使用该子句时可用参数名引用此配置参数。(3)NOCONSOLE子句：禁止将查询结果在屏幕显示，若选INTO子句忽略它的设置。(4)PLAIN子句：输出时省略字段名，若选用INTO子句忽略它的设置。(5)NO WAIT子句：显示结果

20、后，程序继续执行。(6)！表名是指非当前数据库的表。4.2.2操作功能（插入、更新、删除）1插入格式1：INSERT INTO 字段名1, 字段名2, VALUES (表达式1, 表达式2)格式2：INSERT INTO FROM ARRAY 数组名 | MEMVAR功能：在表尾添加一个指定字段的记录。说明：(1)格式1中字段名1，字段名2， 是用于指定添加记录的字段名，若省略，说明所有字段，表达式1，表达式2，是添加字段1，字段2，的具体值。(2)格式2 ARRAY 数组名是从指定的数组中添加记录的值，MEMVAR 是从与字段同名的内存变量中添加记录的值。例4.16 插入应用。OPEN DA

21、TABASE 学生COPY FILES 学生成绩.* TO 学生成绩1.*USE 学生成绩1LISTINSERT INTO 学生成绩1 VALUE (10501012,90,80,72)LISTGO 1SCATTER MEMVAR &将当前记录读到内存变量INSERT INTO 学生成绩1 FROM MEMVARGO 2SCATTER TO a &将当前记录读到a数组INSERT INTO 学生成绩1 FROM ARRAY aUSE2更新格式：UPDATE SET 字段名1= , 字段名2= WHERE 功能：更新满足条件的记录的值，若省略WHERE,更新全部记录的值。例4.17 更新应用。U

22、se 学生成绩1UPDATE 学生成绩1 SET 数学=数学+10 WHERE 数学80UPDATE 学生成绩1 SET 英语=英语+10USE3删除格式：DELETE FROM WHERE 功能：删除符合条件的记录，若省略WHERE将删除全部记录。例4.18 删除应用。USE 学生成绩1DELETE FROM 学生成绩1 WHERE 英语100USE4.2.3定义功能SQL语句也可以定义表、视图。现在简单加以介绍。1定义表在2.1.2中用命令创建表，此格式为SQL创建表的基本语句，现给出它的完整形式。格式：CREATE TABLE|DBF （, 类型, () NULL | NOT NULLC

23、HECK ERROR DEFAULT PRIMARY KEY | UNIQUE REFERENCE TAG NOCPTRANS, | FROM ARRAY , PRIMARY KEY TAG , UNIQUE TAG , FOREIGN KEY TAG NODUPREFERNCES TAG , CHECK ERROR 说明：(1)NULL | NOT NULL子句说明字段是否可取空值（NULL）。(2)CHECK ERROR 子句用于说明字段的有效性规则。是有效性规则，是为字段有效性规则检查出错时给出的提示信息。(3)DEFAULT 子句使用表达式值给出字段的默认值。(4)PRIMARY KE

24、Y|UNIQUE子句是以该字段创建索引，取PRIMARY KEY 创建的是主索引，取UNIQUE创建的是候选索引。(5)REFERENCE TAG 子句用于指定与建立永久关系的父表名。为父表名，若省略TAG 就在父表已存在的索引标识上建立联系。(6)NOCPTRANS子句用于指定C或M型。主字段不进行代码页转换。只能用于C、M型字段。(7)PRIMARY KEY TAG 子句指定要创建的主索引。为表中字段组合。一个表只能有一个主索引，如已建立主索引就不可以用此子句。(8)UNIQUE TAG 创建候选索引。为表中字段组合。(9)FOREIGN KEY TAG NODUP子句，用于建立普通索引。

25、并与父表建立联系。(10)CHECK ERROR 子句，与2的意义相同。(11)REFERNCES TAG 子句与5含义同。(12)FROM ARRAY 子句用于从一个已存在数组中取出字段名、类型、宽度。例4.19 建表应用。OPEN DATABASE 学生CREATE TABLE 学生成绩x(学号 C(9) PRIMARY KEY ,编译 N(3) CHECK (编译=0 AND 编译=500CLOSE DATABASE ALL3删除视图格式：DROP VIEW 视图名功能：删除视图。例4.21 视图的删除。OPEN DATABASE 学生DROP VIEW 学生视图CLOSE DATABA

26、SE ALL4.3查询在4.2节中介绍了SELECT语句，它适合于复杂条件的查询。VFP提供了用查询设计器进行查询，它的每一个查询都对应一个SELECT语句。由于已学完SELECT 语句，现在再学查询设计器，就会感到它的方便简单。它很形象，可以将查询结果用浏览器、报表、表、图形等来表示出来。4.3.1建立查询1用向导建立查询(1)建立文件菜单新建或常用工具栏新建按钮，选文件类型为查询向导，打开向导选取对话框（见图4.1），选择查询向导确定，打开向导对话框步骤1字段选取（见图4.2）3个点按钮，打开打开对话框，文件类型选数据库，选学生库确定，在数据库和表列表框中选学生表将可用字段中字段选入选定字段中，再在数据库和表列表框中选学生成绩表将可用字段中字段选入选定字段中下一步，进入查询向导步骤2为表建立关系（见图4.3）。添加下一步进入查询向导步骤2a字段选取（见图4.4）下一步进入查询向导步骤3筛选记录（见图4.5）下一步进入查询向导步骤