1、对基本数据类型的查询 VFP： select * from stu where number="000003" select * from stu where sex=.t. 或者 select * from stu where sex select * from stu where birthday=ctod("11/23/1976") 或者 select * from stu where birthday={^1976-11-23} select * from stu where height=1.76

Access： select * from stu where number="000003" select * from stu where sex=true select * from stu where birthday=#11/23/1976# select * from stu where height=1.76

Sql Server； select * from stu where number=\'000003\' select * from stu where sex=1 select * from stu where birthday=\'1980-1-1\' select * from stu where height=1.76

2、投影（VFP和Access皆可） select name,height from stu select name as \'姓名\',height as \'身高\' from stu select name as \'姓名\',height*100 as \'身高\' from stu select name as \'姓名\',height*100 as \'身高\',"厘米" as "单位" from stu select name as \'姓名\',int(height*100) as \'身高\',"厘米" as "单位" from stu

Sql Server； select name as \'姓名\',convert(int,height*100) as \'身高\',\'厘米\' as \'单位\' from stu

3、选择 select * from stu where name<"李南"（Access中可以调节排序依据） select * from stu where name="张"（set ansi on下不可以，它是强行在短字串后添加空格） select * from stu where name=="张" select * from stu where name like "张%"（标准做法！） select * from stu where name not like "张%" select * from stu where name like "张_" select * from stu where name like "张__" select * from stu where name like "_明" select * from stu where name like "%明" select * from stu where name like "%明_" select * from stu where name like "%明%" select * from stu where name like "%\'s%" select * from stu where name like "%x_%" escape \'x\' 注意在Access中用*，?来表示，而且不支持escape

Sql Server； select * from stu where name like \'%明%\' select * from stu where name like \'%x_%\' escape \'x\' select * from stu where name = \'李%\'

4、排序 select * from stu order by name select * from stu order by name descend 注意在Access中可以调节排序依据 select * from stu order by 2 descend 注意在Access中必须select * from stu order by 2 desc

Sql Server； select * from stu order by 2 desc select * from stu order by 4 desc,number desc

5、多表查询 select * from stu,grade where stu.number=grade.number select * from stu as a1,stu as a2 where a1.height==a2.height .and. a1.number<>a2.number select * from stu as a1,stu as a2 where a1.height==a2.height .and. a1.number>a2.number select a1.name,a2.name from stu as a1,stu as a2 where a1.height==a2.height .and. a1.number>a2.number select * from stu where sex=.t. union select * from stu where height>1.76（并受支持）

Sql Server； select a1.name,a2.name from stu as a1,stu as a2 where a1.height=a2.height and a1.number>a2.number

VFP中的连接——join clos all sele 1 use stu sele 2 use grade sele 1 join with grade to newfile for number=b->number clos all use newfile brow

VFP中的连接——relation（亦可手工制作，而且可以动态观察父子表的变化） sele 2 grade use grade sele 1 use stu sele 2 inde on number tag xhtemp sele 1 set relation to stu.number into grade brow field number,name,b->course,b->grade set skip to grade brow field number,name,b->course,b->grade

6、子查询 select name,teacher from stu,grade,course where stu.number=grade.number .and. grade.course=course.course select teacher from stu,grade,course where stu.number=grade.number .and. grade.course=course.course .and. name="黎明" select teacher from course where course in (select course from grade where number=(select number from stu where name="黎明"))（VFP不支持嵌套三重的查询） 相关子查询 select s1.name from stu as s1,stu as s2 where s1.name=s2.name .and. s1.number<s2.number select number,name from stu as old where number< any(select number from stu where name=old.name) 比至少一个值小 select number,name from stu as old where number> any(select number from stu where name=old.name) select number,name from stu as old where number<> any(select number from stu where name=old.name) select number,name from stu as old where number< all(select number from stu where name=old.name) select number,name from stu as old where number<= all(select number from stu where name=old.name)

7、交叉表查询 Access表 TRANSFORM Sum(grade.grade) AS gradeSum SELECT grade.number FROM grade GROUP BY grade.number PIVOT grade.course;

VFP表 SELECT Grade.course, Grade.number, SUM(Grade.grade); FROM stu!grade; GROUP BY Grade.course, Grade.number; ORDER BY Grade.course, Grade.number; INTO CURSOR SYS(2015) DO (_GENXTAB) WITH \'Query\',.t.,.t.,.t.,,,,.t.,0,.t. BROWSE NOMODIFY

Excel表

8、副本 select distinct substr(name,1,2) from stu select * from stu where sex=.t. union all select * from stu where height>1.76

9、聚合运算/分组/分组条件 select count(*) from stu select count(distinct sex) from stu select sex,count(*) from stu group by sex select gender(sex),count(*) from stu group by sex －－－－－－－－－ gender： param a if a=.t. return \'男\' else return \'女\' endif －－－－－－－－－ select gender(sex),sum(grade) from stu,grade where stu.number=grade.number group by sex 注意having和where的区别 select gender(sex),sum(grade) from stu,grade where stu.number=grade.number .and. height>1.75 group by sex select gender(sex),sum(grade) from stu,grade where stu.number=grade.number group by sex having height>1.75 注意having与where的相似 select height,sum(grade) from stu,grade where stu.number=grade.number group by height having height>1.75 select height,sum(grade) from stu,grade where stu.number=grade.number .and. height>1.75 group by height 注意having的特殊之处 select name,avg(grade) from stu,grade where stu.number=grade.number group by stu.number select name,avg(grade) from stu,grade where stu.number=grade.number group by stu.number having avg(grade)>80

select * from stu as a1,stu as a2 where a1.height==a2.height .and. a1.number>a2.number ‘==’应该能用吗？

“＝＝”是VFP的语法，不同的数据库管理系统都有不同的语法，如SQL Server就没有

老师：

怎样通过ODBC将access表转换成VFP 表？

老师：

access中的查询设计器在哪儿？不知道在哪儿输入语句？

1、将access导入到vfp的做法有很多，可以在sql server中使用导入和导出来进行，数据源选择“Microsoft Access”，目的选择“dbaseIV”，此时选择好路径，将会自动生成VFP表格。

2、Access有查询分析器，在数据库界面中打开“查询”对象，选择“新建”——“设计视图”即可，此时右击查询设计器窗体，选择“SQL视图”，即可切换为SQL视图，要想再切换回来，点击菜单下“查询设计”工具栏最左边的“视图”按钮，即可返回查询分析器。