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
[此贴子已经被作者于2006-3-27 20:54:17编辑过]
加好友 
发短信
管理员
Post By：2006/3/27 20:53:02 [只看该作者]
