Visual FoxPro程序设计幻灯片.徐红波.第4章

VisualFoxPro程序设计教程讲师：徐红波E-mail:[e-mail]在软件开发中经常用到数据的查询，如学生成绩管理、人事档案、图书检索等软件，查询的准确性、速度直接影响软件的质量、效率、应用及维护。VFP开发工具用三种途径解决查询问题：用查询设计器建立查询；用视图设计器建立视图；用SQL语句查询。第4章查询与视图4.1概念4.1.1查询使用查询设计器，从数据库表或自由表中获取有用数据，经过对查询条件、查询要求的设置，形成一个后缀为qpr的文件，通过do命令来执行。用视图设计器从数据库表中获取有用数据，与查询一样经过查询条件、查询要求的设置形成视图。视图是以视图名的形式存在数据库中。视图中数据的更新可以使源表相应数据更新。视图的执行要在数据库中执行。4.1.2视图SQL语言是关系数据库的标准语言。它是结构化的查询语言。它集数据定义语言、数据操纵语言、数据控制语言的功能为一体。4.1.3SQL查询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基本语句应用opendatabase学生select*from学生select*from学生intotable学生yselect姓名,入学成绩from学生where入学成绩=500selectdistinct性别from学生select学生.学号,学生.姓名,学生成绩.数学from学生,学生成绩where学生.性别=‘女’and学生成绩.数学=60and学生.学号=学生成绩.学号selecttop2*from学生orderby学号closedatabaseall2、SELECT中常用的系统函数在实际应用中经常有统计、求平均值、汇总等操作。SQL提供一些常用的系统函数。函数说明AVG(表达式)求表达式的平均值COUNT(*)统计记录个数MIN(表达式)求表达式的最小值MAX(表达式)求表达式的最大值SUM(表达式)求表达式的和3、SELECT分组子句格式：[GROUPBY表达式1[,表达式2[,……]]][HAVING筛选条件]功能：对查询结果进行分组。[HAVING筛选条件]为指定分组必须满足的条件例4.2分组查询应用opendatabase学生selectavg(入学成绩)from学生selectcount(*)from学生selectsum(入学成绩)from学生groupby性别having性别=‘女’4、排序子句格式：[ORDERBY关键字表达式[ASC|DESC][,关键字表达式[ASC|DESC]……]]功能：查询结果按关键字排序，ASC升序为默认值，DESC为降序例4.3查询排序应用opendatabase学生select学号,姓名,入学成绩from学生orderby入学成绩descselect学生.学号,学生.姓名,学生成绩.英语from学生,学生成绩where学生.学号=学生成绩.学号orderby学生成绩.英语selectavg(入学成绩)as“入学成绩平均分”from学生groupby性别orderby入学成绩descclosedatabaseall5、嵌套查询嵌套查询就是在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)closedatabaseall例4.7查找比所有入学成绩小于500分学生的年龄都小的学生opendatabase学生select*from学生where出生all(select出生from学生where入学成绩500)closedatabaseall6、几个特殊运算符（1）BETWEENAND运算符格式：BETWEEN表达式1AND表达式2说明：表示在一个区间范围内例4.8查询入学成绩480-600分学生情况，与不在500-600之间的学生opendatabase学生select*from学生where入学成绩between490and600select*from学生where入学成绩notbetween490and600closedatabaseall（2）LIKE运算符LIKE后含有%或_通配符的字符型表达式。%表示0个或多个字符，_表示一个字符。例4.9查询姓王的所有学生情况，查询姓名前两个字为王丽的学生情况opendatabase学生select*from学生where姓名like“王%”select*from学生where姓名like“王丽_”closedatabaseall7、连接查询格式：表名1[LEFT|RIGHT|FULL]JOIN表名2ON连接条件(1)LEFTJOIN为左连接，在满足连接条件的记录出现在结果中基础上，第一个表不满足条件的记录也出现在结果中。例4.11用左连接查找学生表与学生成绩表opendatabase学生select*from学生leftjoin学生成绩on学生.学号=学生成绩.学号closedatabaseall(2)RIGHTJOIN为右连接，在满足连接条件的记录出现在结果中基础上，第二个表不满足条件的记录也出现在结果中例4.12用右连接查找学生表与学生成绩表opendatabase学生select*from学生rightjoin学生成绩on学生.学号=学生成绩.学号closedatabaseall(3)FULLJOIN为全连接。即满足连接条件的记录出现在结果上，两表不满足连接条件的记录也出现在结果上4.13用全连接查找学生表与学生成绩表opendatabase学生select*from学生fulljoin学生成绩on学生.学号=学生成绩.学号closedatabaseall8、UNION子句用此子句可以将多个表的查询结果，合并成一个结果，要求查询结果具有相同字段与个数，且对应字段类型要兼容。例如：select*from学生where性别=‘女’unionallselect*from学生where性别=‘男’9、查询结果的存放（1）用INTO[DBF|TABLE]表名可将查询结果存放表中（2）用INTOARRAY数组名将查询结果存入数组（3）用INTOCURSOR临时表名将查询结果存入临时表（4）用TOFILE文件名[ADDITIVE]将结果存入文本文件，用ADDITIVE将结果追加到由文件名指定的文本文件尾部。否则将覆盖原有文件例4.15关于结果存放的应用opendatabase学生select*from学生intotable新学生select*from学生intoarrayaselect*from学生intocursor临时学生select*from学生tofile学生文本closedatabaseall4.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学生use学生成绩insertinto学生成绩values(“105010120”,90,80,72)go1scattermemvarinsertinto学生成绩frommemvargo2scattertoainsertinto学生成绩fromarraya2、更新格式：UPDATE表名SET字段名1=表达式1[,字段名2=表达式2……][WHERE条件]功能：更新满足条件的记录的值，若省略WHERE，更新全部记录的值例4.17更新应用use学生成绩update学生成绩set数学=数学+10where数学80update学生成绩set英语=英语+10use3、删除格式：DELETEFROM表名[WHERE条件]功能：删除符合条件的记录，若省略WHERE将删除全部记录例4.18删除应用use学生成绩deletefrom学生成绩where英语100use4.3查询4.3.1建立查询1、用向导建立查询2、用查询设计器建立查询4.3.2执行查询1、用界面执行查询2、用命令执行查询DO查询文件名.qpr4.3.3查询设计器的局限性用查询设计器建立的查询简单、易学，但在使用中有一定的局限性，它适用于比较规范的查询，而对较复杂的查询是无法实现的。例4.22查询入学成绩最高学生的信息opendatabase学生selecta1.学号,a1.姓名,a1.入学成绩from学生asa1where入学成绩=(selectmax(入学成绩)from学生asb1wherea1.学号=b1.学号)closedatabaseall4.4视图查询视图与查询均是从表中获取数据。视图与查询的区别：视图是一张虚表，数据库中只存放视图的定义，数据库不存放视图的对应数据，这些数据仍然存放在表中。而查询是以qpr文件形式存放在磁盘中。更新视图的数据同时更新表的数据，这一点与查询是完全不同的。4.4.1建立本地视图1、向导建立视图2、视图设计器建立视图4.4.4运行视图在数据库打开基础上运行视图用