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

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

《VFP6教程第4章 查询与视图.docx》由会员分享，可在线阅读，更多相关《VFP6教程第4章 查询与视图.docx（32页珍藏版）》请在冰点文库上搜索。

VFP6教程第4章查询与视图

第4章查询与视图

在软件开发中经常用到数据的查询，如学生成绩管理，人事档案，图书检索等软件，查询的准确、速度直接影响软件的质量、效率、应用及维护，VFP开发工具用三个途径解决查询问题。

第一个途径就是运用SQL语句查询，第二个途径就是用查询设计器建立快速查询，第三个途径是用视图设计器建立视图，实现快速查询。

4.1查询的一般概念

4.1.1查询

VFP中的查询使用查询设计器，从数据库表或自由表中获取有用数据，经过对查询条件、查询要求的设置，形成一个*.QPR文件，通过DO命令来执行。

4.1.2视图

VFP中的视图是用视图设计器从数据库表中获取有用数据，与查询一样经过查询条件、查询要求的设置形成视图。

视图是以视图名的形式存在数据库中。

视图中数据的更新可以使源表相应数据更新。

视图的执行要在数据库中执行。

4.1.3SQL查询

SQL是关系数据库的国际标准语言。

为此，世界上许多语言开发商都将SQL语言作为数据库对数据存取的共同标准接口。

有的已将SQL语言嵌入到语言开发工具中，使得人们在软件开发时运用数据库是极为方便。

之所以SQL能成为国际标准语言，主要原因它是结构化的查询语言。

它的综合统一体现在它集数据定义语言、数据操纵语言、数据控制语言的功能为一体。

再就是它高度的非过程化，无需一步步地告诉计算机如何做，只要指名怎么做系统就可以自动完成。

SQL语言简单便于掌握，特别是在查询复杂可视化工具无法解决时，SQL可以解决。

4.2SQL语句

4.2.1SELECT查询语句

1．基本语句

格式：

SELECT<行列限制表达式>[INTODBF|TABLE<新表名>]FROM<表名列表>[WHERE<条件>]

功能：

从表名列表的表中查找符合条件的，按<行列限制表达式>的形式显示。

若选[INTODBF|TABLE<新表名>]子句，查询结果存入新表。

说明：

<行列限制表达式>格式：

[ALL|DISTINCT][TOP<数值表达式>[PERCENT]][别名.][列名[AS栏目名]][,[别名.][列名AS[栏目名]…]

（1）[ALL|DISTINCT]子句：

ALL输出结果有重复记录，是子句默认值。

DISTINCT输出结果无重复记录。

（2）[TOP<数值表达式>[PERCENT]]子句：

此子句TOP<数值表达式>是符合条件的内容中取前<数值表达式>个记录。

PERCENT是取前面分之<数值表达式>个记录。

（3）[别名.][列名[AS栏目名]][,[别名.][列名AS[栏目名]…]子句：

列名可以是字段、含字段的表达式或表达式。

指定输出结果中的字段，此子句也可用*代替此时显示表中所有字段。

例4.1SELECT基本语句应用

USE学生1

SELECT*FROM学生1

OPENDATABASE学生&&打开学生数据库

SELECT*FROM学生INTOTABLE学生y&&显示学生表中所有内容

SELECT姓名,入学成绩FROM学生WHERE入学成绩>=500

SELECTDISTINCT姓名,入学成绩FROM学生WHERE入学成绩>=500

SELECT学生.学号,学生.姓名,学生成绩.数学FROM学生,学生成绩WHERE学生.性别=’女'AND学生成绩.数学>=60AND学生.学号=学生成绩.学号

SELECTTOP2*FROM学生ORDERBY学号

CLOSEDATABASEALL

2．SELECT中常用的系统函数

在实际应用中经常在查询结果进行统计、求平均值、汇总等基本要求。

SQL提供了一些常用的系统函数。

如表4.1所示。

表4.1常用系统函数

函数

说明

AVG（）

求的平均值

COUNT（）

统计记录个数

MIN（）

求的最小值

MAX（）

求的最大值

SUM（）

求的和

：

为[DISTINCT|ALL]表达式。

说明：

DISTINCT在计算时去掉重复值，ALL在计算所有值是默认值，表达式可为*、字段名、数学函数、常量、函数或表达式。

3．SELECT分组子句

格式：

[GROUPBY<表达式1>[,<表达式2>[,…][HAVING<筛选条件>]

功能：

对查询结果进行分组。

[HAVING<筛选条件>]为指定分组必须满足的条件。

例4.2分组查询应用。

OPENDATABASE学生

SELECTAVG（入学成绩）FROM学生

SELECTCOUNT（*）FROM学生

SELECTSUM（入学成绩）FROM学生GROUPBY性别

SELECTCOUNT（*）AS'学生人数'FROM学生GROUPBY性别HAVING（入学成绩）>460

SELECT学生.学号,AVG（学生.入学成绩）,学生成绩.VFPFROM学生,学生成绩GROUPBY性别HAVING（入学成绩）>=500WHERE学生.学号=学生成绩.学号

4．排序子句

格式：

[ORDERBY<关键字表达式>[ASC|DESC][,<关键字表达式>[ASC|DESC]…]]

功能：

查询结果按关键字排序，ASC升序为默认值，DESC为降序。

例4.3查询排序应用

OPENDATABASE学生

SELECT学号,姓名,入学成绩FROM学生ORDERBY入学成绩DESC

SELECT学生.学号,学生.姓名,学生成绩.英语FROM学生,学生成绩WHERE学生.学号=学生成绩.学号GROUPBY学生.学号ORDERBY学生成绩.英语

SELECTAVG（入学成绩）AS"入学成绩平均分"FROM学生GROUPBY性别ORDERBY入学成绩DESC

CLOSEDATABASEALL

5．嵌套查询

嵌套查询就是在SELECT语句中套有SELECT语句，被套在内的SELECT子句通常称为子查询。

（1）IN等谓词及比较运算符结合使用。

例4.4IN与NOTIN在嵌套查询中的应用

OPENDATABASE学生

SELECT*FROM学生WHERE学号IN（SELECT学号FROM学生成绩WHERE英语>=90）

SELECT*FROM学生WHERE学号NOTIN（SELECT学号FROM学生成绩WHERE英语<90）

CLOSEDATABASEALL

例4.5比较运算符在嵌套查询中的应用。

OPENDATABASE学生

SELECT*FROM学生a1WHERE入学成绩=（SELECT入学成绩FROM学生a2WHEREa1.学号=a2.学号AND入学成绩>=500）

CLOSEDATABASEALL

（2）使用量词和谓词的嵌套查询

格式：

<表达式><比较表达式>[ANY|ALL|SOME]（子查询）[NOT]EXIST（子查询）

说明：

ANY、ALL、SOME为量词，ANY与SOME是同义词，在查询时，只要子查询中有一行能使结果为.T.，则结果就为.T.。

ALL要求子查询中所有行结果为.T.时，结果才能为.T.。

EXIST或NOTEXIST是检查在子查询中是否有结果返回。

EXIST为有结果返回为真否则为假。

NOTEXIST刚好与EXIST相反。

例4.6查询数学成绩高于90分的学生的学号，姓名。

OPENDATABASE学生

SELECT学号,姓名FROM学生WHEREEXIST（SELECT*FROM学生成绩WHERE学号=学生.学号AND数学>=90）

例4.7查找所有入学成绩大于等于500分年龄都小的学生。

OPENDATABASE学生

SELECT*FROM学生WHERE出生>ALL（SELECT出生FROM学生WHERE入学成绩<500）

CLOSEDATABASEALL

6．几个特殊运算符

（1）BETWEEN…AND…运算符

格式：

BETWEEN<表达式1>AND<表达式2>

说明：

表示在…的范围。

例4.8查询入学成绩480-600分的学生情况，与不在500-600之间的学生。

OPENDATABASE学生

SELECT*FROM学生WHERE入学成绩BETWEEN490AND600

SELECT*FROM学生WHERE入学成绩NOTBETWEEN500AND600

CLOSEDATABASEALL

（2）LIKE运算符

说明：

LIKE后含有%或_通配符的字符型表达式。

%表示0个或多个字符，_表示一个字符。

例4.9查询姓王的所有学生情况，查询姓名前两个字为王丽的学生情况。

OPENDATABASE学生

SELECT*FROM学生WHERE姓名LIKE"王%"

SELECT*FROM学生WHERE姓名LIKE"王丽_"

CLOSEDATABASEALL

7．连接查询

格式：

<表名1>[INNER|LEFT[OUTER]|RIGHT[OUTER]|FULL[OUTER]JOINON<连接条件><表名2>]

说明：

（1）INNER为内联接。

内连接是指按连接条件合并两个表，只要满足条件的记录出现在结果中，内联接可以省略INNER。

例4.10查找学生专业库中，学生情况及选修课成绩情况

OPENDATABASE学生专业

SELECT*FROM学生专业INNERJOIN选修ON学生专业.学号=选修.学号

CLOSEDATABASEALL

（2）LEFTJOIN为左连接，在满足连接条件的记录出现在结果中基础上，第一个表不满足条件的记录也出现在结果中。

若加OUTER只是除了同以上连接为外连接，OUTER表示强调，OUTER通常省略。

例4.11用左联接查找学生情况与选修课情况。

OPENDATABASE学生专业

SELECT*FROM学生专业LEFTJOIN选修ON学生专业.学号=选修.学号

CLOSEDATABASEALL

（3）RIGHTJOIN为右连接，在满足连接条件的记录出现在结果中基础上，第二个表不满足条件的记录也出现在结果中。

例4.12用右连接查找学生情况与选修课情况

OPENDATABASE学生专业

SELECT*FROM学生专业RIGHTJOIN选修ON学生专业.学号=选修.学号

CLOSEDATABASEALL

（4）FULLJOIN为全连接。

即满足连接条件的记录出现在结果上，两表不满足连接条件的记录也出现在结果上。

例4.13用全连接查找学生情况与选修课情况

OPENDATABASE学生专业

SELECT*FROM学生专业FULLJOIN选修ON学生专业.学号=选修.学号

CLOSEDATABASEALL

8．UNION子句

也称集合并运算，用此子句可以将多个表的查询结果，合并成一个结果，要求查询结果具有相同字段与个数，且对应字段类型要兼容。

例4.14建立三个表，计算机应用、计算机软件、计算机硬件它们的结构为专业C（3），学号C（9），姓名C（6），数学C（N,3），英语C（N,3），C语言C（N,3）然后建立一个数据库，计算机并将三个表添入库中，然后用UNION查看所有内容。

3个表内容如表4.2所示

表4.2计算机专业学生成绩

专业

学号

姓名

数学

英语

C语言

计算机应用

105010101

刘晓华

80

90

60

计算机应用

105010102

李立明

90

100

70

计算机软件

105020101

王雪梅

90

70

100

计算机软件

105020102

李德勇

60

80

68

计算机硬件

105030101

方芳

80

90

100

计算机硬件

105030102

王良

90

92

60

CREATE计算机应用

INDEXON学号TAGt1

CREATE计算机软件

INDEXON学号TAGt2

CREATE计算机硬件

INDEXON学号TAGt3

CREATEDATABASE计算机

ADDTABLE计算机应用

ADDTABLE计算机软件

ADDTABLE计算机硬件

SELECT*FROM计算机应用UNIONALL;

SELECT*FROM计算机软件UNIONALL;

SELECT*FROM计算机硬件

CLOSEDATABASEALL

9．关于查询结果的存放

（1）用INTO[DBF|TABLE]<表名>可将查询结果存放表中。

（2）用INTOARRAY<数组名>将查询结果存入数组。

（3）用INTOCURSOR<临时表名>将查询结果存入临时表。

（4）用TOFILE<文件名>[ADDTIVE]将结果存入文本文件，用ADDITIVE将结果追加到由<文件名>指定的文本文件尾部。

否则将覆盖原有文件。

（5）用TOPRINT[PROMPT]将查询结果输出到打印机，若选PROMPT选项在打印前打开打印机设置对话框。

例4.15关于结果存放的应用。

OPENDATABASE学生

SELECT*FROM学生INTOTABLE新学生

SELECT*FROM学生INTOARRAYa

SELECT*FROM学生INTOCURSOR临时学生

SELECT*FROM学生TOFILE学生文本

CLOSEDATABASEALL

10．SELECT完整语句格式

格式：

SELECT[ALL|DISTINCT][TOP<数值表达式>[PERCENT]][别名.]列名[AS栏目名][,[别名.]列名[AS栏目名]…]

[FROM[FORCE][数据库名!

]<表名>[[AS]<本地名>]

[INNER|LEFT[OUTER]|RIGHT[OUTER]|FULL[OUTER]JOIN<数据库>!

]<表名>[[AS]<本地名>][ON<连接条件>…]

[[INTO<目标>|TOFILE<文件名>[ADDITIVE]|TOPRINT[PROMPT]|TOSCREEN]]

[PREFERENCE参数名][NOCONSOLE][PLAIN][NOWAIT]

[WHERE<联接条件>[AND<联接条件>…][AND|OR<筛选条件>…]]

[GROUPBY<表达式1>[,<表达式2>…]]

[HAVING<筛选条件>]

[UNION[ALL]SELECT命令]

[ORDERBY<关键字表达式>[ASC|DESC][,<关键字表达式>[ASC|DESC]…]]

从以上SELECT格式看很长也很复杂，但在前面已将常用的重要的部分介绍过了。

下面对不常用的子句的作用、形式作一介绍。

说明：

（1）FORCE用来在按连接条件来连接表查询时，VFP不进行优化。

（2）PREFERENCE<参数名>子句：

用于记载浏览窗口的配置参数，再次使用该子句时可用参数名引用此配置参数。

（3）[NOCONSOLE]子句：

禁止将查询结果在屏幕显示，若选INTO子句忽略它的设置。

（4）[PLAIN]子句：

输出时省略字段名，若选用INTO子句忽略它的设置。

（5）[NOWAIT]子句：

显示结果后，程序继续执行。

（6）<数据库>！

表名是指非当前数据库的表。

4.2.2操作功能（插入、更新、删除）

1．插入

格式1：

INSERTINTO<表名>[字段名1[,字段名2,…]]VALUES（表达式1[,表达式2…]）

格式2：

INSERTINTO<表名>FROMARRAY数组名|MEMVAR

功能：

在表尾添加一个指定字段的记录。

说明：

（1）格式1中字段名1，字段名2，…是用于指定添加记录的字段名，若省略，说明所有字段，表达式1，表达式2，…是添加字段1，字段2，…的具体值。

（2）格式2ARRAY数组名是从指定的数组中添加记录的值，MEMVAR是从与字段同名的内存变量中添加记录的值。

例4.16插入应用。

OPENDATABASE学生

COPYFILES学生成绩.*TO学生成绩1.*

USE学生成绩1

LIST

INSERTINTO学生成绩1VALUE（"10501012",90,80,72）

LIST

GO1

SCATTERMEMVAR&&将当前记录读到内存变量

INSERTINTO学生成绩1FROMMEMVAR

GO2

SCATTERTOa&&将当前记录读到a数组

INSERTINTO学生成绩1FROMARRAYa

USE

2．更新

格式：

UPDATE<表名>SET字段名1=<表达式1>[,字段名2=<表达式2>…][WHERE<条件>]

功能：

更新满足条件的记录的值，若省略WHERE,更新全部记录的值。

例4.17更新应用。

Use学生成绩1

UPDATE学生成绩1SET数学=数学+10WHERE数学<80

UPDATE学生成绩1SET英语=英语+10

USE

3．删除

格式：

DELETEFROM<表名>[WHERE<条件>]

功能：

删除符合条件的记录，若省略WHERE将删除全部记录。

例4.18删除应用。

USE学生成绩1

DELETEFROM学生成绩1WHERE英语>100

USE

4.2.3定义功能

SQL语句也可以定义表、视图。

现在简单加以介绍。

1．定义表

在2.1.2中用命令创建表，此格式为SQL创建表的基本语句，现给出它的完整形式。

格式：

CREATETABLE|DBF<表名1>（<字段名1>,类型,（<宽度>）[NULL|NOTNULL]

[CHECK<逻辑表达式1>[ERROR<出错信息>]][DEFAULT<表达式1>]

[PRIMARYKEY|UNIQUE][REFERENCE<表名2>[TAG<索引标识符名1>]][NOCPTRANS][,<字段名2>…]|[FROMARRAY<数组名>]

[,PRIMARYKEY<表达式2>TAG<索引标识符名2>

[,UNIQUE<表达式3>TAG<索引标识符3>]

[,FOREIGNKEY<表达式4>TAG<索引标识符名4>[NODUP]

REFERNCES<表名3>[TAG<索引标识符名5>]]

[,CHECK<逻辑表达式>[ERROR<出错信息2>]]]

说明：

（1）[NULL|NOTNULL]子句说明字段是否可取空值（NULL）。

（2）[CHECK<逻辑表达式>[ERROR<出错信息>]子句用于说明字段的有效性规则。

<逻辑表达式>是有效性规则，<出错信息>是为字段有效性规则检查出错时给出的提示信息。

（3）[DEFAULT<表达式1>]子句使用表达式值给出字段的默认值。

（4）[PRIMARYKEY|UNIQUE]子句是以该字段创建索引，取PRIMARYKEY创建的是主索引，取UNIQUE创建的是候选索引。

（5）[REFERENCE<表名2>][TAG<索引标识名>]子句用于指定与建立永久关系的父表名。

<表名2>为父表名，若省略[TAG<索引标识符名>]就在父表已存在的索引标识上建立联系。

（6）[NOCPTRANS]子句用于指定C或M型。

主字段不进行代码页转换。

只能用于C、M型字段。

（7）PRIMARYKEY<表达式2>TAG<索引标识符2>子句指定要创建的主索引。

<表达式2>为表中字段组合。

一个表只能有一个主索引，如已建立主索引就不可以用此子句。

（8）UNIQUE<表达式3>TAG<索引标识符名3>创建候选索引。

<表达式3>为表中字段组合。

（9）[FOREIGNKEY<表达式4>TAG<索引标识符名4>[NODUP]子句，用于建立普通索引。

并与父表建立联系。

（10）[CHECK<逻辑表达式>[ERROR<出错信息2>]]子句，与2的意义相同。

（11）[REFERNCES<表名3>[TAG<索引标识符名5>]]子句与5含义同。

（12）[FROMARRAY<数组名>]子句用于从一个已存在数组中取出字段名、类型、宽度。

例4.19建表应用。

OPENDATABASE学生

CREATETABLE学生成绩x（学号C（9）PRIMARYKEY,编译N（3）CHECK（编译>=0AND编译<=100）ERROR"编译成绩再0-100之间"DEFAULT0,FOREIGNKEY学号tag学号REFERENCES学号）

CLOSEDATABASEALL

2．定义视图

格式：

CREATEVIEW视图名ASSELECT语句

功能：

建立视图。

用SELECT语句限定视图数据。

例4.20建立视图

OPENDATABASE学生

CREATEVIEW学生视图ASSELECT*FROM学生WHERE入学成绩>=500

CLOSEDATABASEALL

3．删除视图

格式：

DROPVIEW视图名

功能：

删除视图。

例4.21视图的删除。

OPENDATABASE学生

DROPVIEW学生视图

CLOSEDATABASEALL

4.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）→下一步进入查询向导步骤