课外天地 李树青学习天地数据库系统原理课件 → 关于SELECT查询语句学习的资料


  共有26491人关注过本帖树形打印复制链接

主题:关于SELECT查询语句学习的资料

帅哥哟,离线,有人找我吗?
admin
  1楼 博客 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信 管理员
等级:管理员 帖子:1945 积分:26736 威望:0 精华:34 注册:2003/12/30 16:34:32
关于SELECT查询语句学习的资料  发帖心情 Post By:2006/3/27 20:53:02 [只看该作者]

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编辑过]

 回到顶部
美女呀,离线,留言给我吧!
eva
  2楼 博客 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:论坛游民 帖子:43 积分:2049 威望:0 精华:0 注册:2006/9/18 17:29:55
  发帖心情 Post By:2006/11/15 23:09:55 [只看该作者]

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


 回到顶部
帅哥哟,离线,有人找我吗?
admin
  3楼 博客 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信 管理员
等级:管理员 帖子:1945 积分:26736 威望:0 精华:34 注册:2003/12/30 16:34:32
回复  发帖心情 Post By:2006/11/16 6:39:40 [只看该作者]

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


 回到顶部
美女呀,离线,留言给我吧!
silvan
  4楼 博客 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:论坛游民 帖子:14 积分:457 威望:0 精华:0 注册:2006/9/15 22:11:21
  发帖心情 Post By:2006/11/26 14:44:02 [只看该作者]

老师:

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


 回到顶部
美女呀,离线,留言给我吧!
silvan
  5楼 博客 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:论坛游民 帖子:14 积分:457 威望:0 精华:0 注册:2006/9/15 22:11:21
  发帖心情 Post By:2006/11/26 14:54:48 [只看该作者]

老师:

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


 回到顶部
帅哥哟,离线,有人找我吗?
admin
  6楼 博客 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信 管理员
等级:管理员 帖子:1945 积分:26736 威望:0 精华:34 注册:2003/12/30 16:34:32
回复  发帖心情 Post By:2006/11/26 21:53:59 [只看该作者]

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

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


 回到顶部