徐红波vfp45章doc.docx
《徐红波vfp45章doc.docx》由会员分享,可在线阅读,更多相关《徐红波vfp45章doc.docx(85页珍藏版)》请在冰豆网上搜索。
徐红波vfp45章doc
第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)→下一步进入查询向导步骤4—排