vfp中select语句用法.docx
《vfp中select语句用法.docx》由会员分享，可在线阅读，更多相关《vfp中select语句用法.docx（43页珍藏版）》请在冰豆网上搜索。
vfp中select语句用法
VisualFoxPro中的SQLSelect查询
基本的SQLSelect
From子句
在FROM子句中指定要查询的表，假设指定的表未打开，FoxPro将自动打开指定的表，但在查询完成后,FoxPro并不自动关闭它所打开的表，假设要在查询完成后关闭表，则必须以手动方式关闭它们。
假设在执行SQLSELECT时，FROM子句中指定的表已经打开，则FoxPro以USE…AGAIN的方式重新打开此表。
Select子句
SELECT子句中指定要查询的结果字段，这些结果可以是以下基本元素：
通配符*
表中的字段名
FoxPro函数
SELECT中可用的列函数
用户自定义函数
常数（常用于作为占位符）
以上各元素在SELECT子句中以逗号分开，例如：
SELECT销售地区,销售部门,业务员;
FROM销售表
假设SELECT子句中的字段名是唯一的，则不必在字段名前加别名修饰，SQL能自动找到这些字段。
假设字段名是非唯一的（即同一字段名出现在FROM子句中的两个或两个以上表中），FoxPro返回以下错误信息：
<字段>|<变量>不唯一,必须加以限定
要纠正这一错误，用字段所在表的别名修饰该字段。
假设在SELECT子句中加入了FoxPro函数，则意味着该语句不具备可移植性。
SQL和用户自定义函数
FoxPro支持在Select子句中使用用户定义函数。
但是由于在其它系统中不支持用户定义函数。
因此在开发可能移植的系统时不要使用用户自定义函数。
Select子句中的关键字和其它子句
Select子句中的通配符
1、＊（星号）
只能使用在字段串列参数中。
代表来源数据库中所有的字段。
如果select是在二个以上的数据库中进行查询的，则要在号码前加上数据库别名。
*是SQL中的三个通配符之一（其它两个是?
和_,将在以后讨论）,*只能出现在Select子句中,通配符前可以用别名修饰,在此情况下表示要查询指定工作区中的全部字段.同时还可以给出其它字段名.如:
Select销售表.*,客户表.客户代码,客户表.客户名称;
FROM销售表,客户表;
WHERE销售表.客户代码=客户表.客户代码
2、%（百分号）
只能使用在Where参数中，代表一连串任意字符类似于DOS下的IT*.PRG中的“*”。
通常与运算符LIKE搭配使用:
SELECT客户名FROM客户表WHERE客户名LIKE“成都%公司”
以上查询选择所有公司名以”成都”开始,以”公司”结尾的客户,如:
成都东方公司、成都西部发展公司等。
3、_（下划线）
也只能使用在where参数中，代表一个任意字符。
与DOS下的DIRIT?
?
.PRG中的”?
”一样。
例如_a%表示第二个字符为a的字串。
通常与LIKE搭配使用:
SELECT客户名FROM客户表WHERE客户名LIKE“成都_公司”
以上查询选择类似于”成都分公司”,”成都总公司”这样的公司名字。
另一种情况是文字数据中正好包含有星号、百分号、下划线（例如：
软件部__业务组），此时可使用关键字ESCAPE告诉SQL哪一个字符是常数字符而非通配符。
例如：
selectcompanyfromcustomer
wherecompanylike"%\_%"escape"\"
此时escape所指定字符（即\）后的那一个字段（此处为下划线_）将被视为常数字符而非通配符。
字段别名（AS）
FoxPro提供了给Select子句中的字段起别名的方法（这种方法不同于ANSI/ISO标准）:
即可用AS子句给出Select子句中字段的别名,在多当需要直接浏览查询结果或查询结果中含有计算列时,该方法很有用处.例如:
SELECT“物资部”AS部门,SUM（工资）AS工资汇总;
FROM工资表;
WHERE部门编号=“0013”
用DISTINCT消去重复的记录
SELECT中的DISTINCT关键字,可以消去查询结果中的重复行。
例如要字段出所有购买了商的客户,可以用以下Select语句:
SELECTDISTINCT客户名称;
FROM销售细节表
注意,这里消去的重复记录是指SELECT子句中的所有字段值均相等的记录.
当SQL遇到DISTINCT关键字时,即建立一个中间表。
然后以SELECT子句中的所有字段建立一个唯一索引，然后将索引用于中间表,并把索引中的记录放入查询结果中。
这样就消去了重复记录，但是当SELECT子句中的字段很多时,这一过程会很慢。
用WHERE子句说明选择记录的条件
通常查询并不希望取出所有记录,而只是希望查出满足一定条件的记录，要做到这一点可以使用WHERE子句。
WHERE子句把满足条件的记录作为查询结果。
选择条件
在WHERE子句中可以包含多个选择条件，各条件间用AND或OR连接而构成较长的逻辑表达式。
连接条件
当一个查询涉及到多个表时，需要一个特殊类型的WHERE条件，称为连接条件，连接条件建立了FROM子句中的两表间的一对一或一对多的联系。
SQL和宏
SELECT子句中可以方便快捷地处理宏,在SELECT语句中,宏替换只能替换一次,在以下查询之前,需要由用户给出表名和相应的表达式
Select*;
FROM（UserTable）;
Where公司代码=&Company_code
宏还可以用在LIKE的模式匹配串:
SelectCompany,CompanyId,SaleName,SaleId;
FromCompany,SalesMan;
WhereCompany.SaleId=SalesMan.SaleId;
ANDDescriptionLIKE"%TCL%"
以上查询中,用户输入一字符串以便在备注字段（Description）中作匹配查找.查询求出的是包含该字符串的所有公司的住处
SQL和字母大小写
SQL中是区分字母大小写的,为使查询结果有效,要注意区分查询条件中的大小写.即表中的内容是大写的查询时必须将它们转换为大写.假设表中内容是大小写混杂的,则以下Select语句将难以返回全部结果:
SelectCompanyId;
FromCompany;
WhereCompanyId="ACME"
以上查询只返回名为ACME的记录,对于CompanyId为Acme的记录则查不到,因此安全的方法是用UPPER（）函数将所有字符串都转换为大写.以下是正确的查询:
SelectCompanyId;
FromCompany;
WhereUPPER（CompanyId）="ACME"
SQL和索引
WHERE条件可用Rushmore和索引加速查找.SQL可用任何类型的索引:
结构化的.CDX,外部的.CDX及外部的.IDX.假设要使SQL使用外部索引,必须在执行Select之前将其打开,如以下语句所示:
UseSalesINDEXidxfile,externalcdxfile
SQL可以以同样的方式作为标准过程代码使用Rushmore.这时选择条件必须是可优化的,即选择表达式必须与建立索引的表达式精确匹配.
用OrderBy子句对结果排序
由于SQL优化器是按它认为的最正确方案去存取数据,因些查询结果中的记录顺序是动态不定的.许多应用程序需要查询结果是按一个或多个字段排好序的.为此可用ORDERBY子句.
ORDERBY子句可以包含Select子句中一样多的字段.例如:
SelectCompany,State,CompanyId;
FromClient;
WhereStateIN（"NY","NJ","PA","CA"）;
OrderByState,Company
在以上查询中,按所在州及公司名称顺序给出结果.缺省情况下,记录是升序排字段,也可用DESCENDING来说明按降序排序.当然也可以升降序并用,例如:
SelectCompany,State,CompanyId,SaleNo;
FromCompany,Sales;
WhereCompany.SaleId=SalesMan.SaleId;
ANDStateIN（"NY","NJ","PA","CA"）;
ORDERBYState,Company,SaleNoDESCENDING
以上查询结果首先按州及公司名称升序排字段,然后按SaleNo降序排序.
由于排序是对查询结果的排序,因此OrderBy子句中的字段必须是Select子句中出现的字段.
另外,OrderBy子句允许用位标号给出排序字段,这些位标号是指Select子句中出现的序号.例如上一个查询可以写为:
SelectCompany,State,CompanyId,SaleNo;
FromCompany,Sales;
WhereCompany.SaleId=SalesMan.SaleId;
ANDStateIN（"NY","NJ","PA","CA"）;
ORDERBY2,1,3DESCENDING
当Select子句包含了用户定义函数或FoxPro函数（如UPPER（）和LEFT（））时,用位标号来给出排序字段显得十分方便.
列函数
在Select子句中,可以用SQL提供的五个列函数,如下表所示:
SQL的列函数
函数描述
AVG（字段名）求出指定字段上的平均值,只适用于数值型的字段
SUM（字段名）求出指定字段上的和,只适用于数值型的字段
MIN（字段名）求出指定字段上的最小值,字段可以是数值型,字符型或日期型
MAX（字段名）求出指定字段上的最大值,字段可以是数值型,字符型或日期型
COUNT（字段名）求出指定字段上的值的出现个数,可以用COUNT（*）求出记录数
所有列函数的结果都是单值
关于列函数,别有以下需要注意的地方:
列函数中的DISTINCT
可以在任一个列函数中（除了MIN（）和MAX（））使用DISTINCT,这样在列函数的求值中将不计重复值.例如:
SELECTAVG（DISTINCTSalesPrice）;
FROMSales
在以上查询中,求出商品的销售单价,对于重复的SalesPrice,只计算一次.注意在Select子句中只能使用一次DISTINCT.以下查询是不正确的:
SELECTDISTINCTAVG（DISTINCTSalesPrice）,SalesMan;
FROMSales
还有一点要注意的是,在COUNT（）函数中,*不能与DISTINCT连用（如COUNT（DISTINCT*））,假设在COUNT（）中使用DISTINCT,需给出字段名.
列函数的嵌套
以下查询是列函数嵌套的情况:
SelectSUM（AVG（SalesPrice））,SalesMan;
FromSales;
GroupByProduct_ID
该查询是想求出每一产品的平均单价的和,但在运行时FoxPro会给出如下错误:
文件"AVG.prg"不存在
这说明列函数是不能嵌套使用的.
例一：
人员资料表ryb，内容如下：
科室
职称
政治面貌
办公室
awfw
助工
党员
通讯科
wafawe
高工
党员
机械科
afawe
技术员
团员
财务科
2wraw
无
无
人事科
afwe
工程师
无
…
现要统计每个科室各有几名技术员、助工、工程师、高工、党员、团员,以以下表的形式打印出来
科室
技术员
助工
工程师
高工
党员
团员
财务科
2
2
3
3
4
3
命令如下：
Select科室,;
sum（iif（职称="技术员",1,0））,;
sum（iif（职称=";助工",1,0））,;
sum（iif（职称="工程师",1,0））,;
sum（iif（职称="高工",1,0））,;
sum（iif（政治面貌="党员",1,0））,;
sum（iif（政治面貌="团员",1,0））;
fromrybgroupby科室
例二：
表recdbf
〖性质〗分一般、重大、特大；
〖日期〗
要统计出某年的12个月内每个月每种性质的记录各有多少，假设该月没有记录就为零。
结果：
月份 一般 重大特大
1 0 13
2 2 123
......
12 3 05
这跟上面那个例子有点不同，上例只是按表中科室来分类，而这里就不同，因为不是每个月都有案件的，但在报表里没有案件的月也要占一行。
所以这里要建立一个辅助表:
tempyf（yfN
（2））,内有十二个记录，值为1至12，代表1－12月。
我先是老规则：
Selectmonth（日期）,;
iif（性质="一般",1,0）as一般,;
iif（性质="重大",1,0）as重大,;
iif（性质="特大",1,0）as特大;
fromrecdbf;
whereyear（日期）=?
年份;
intocurstemp1
再用tempyf左联接临时表temp1，根据tempyf.yf分组统计。
但一看，结果好象不对，没有记录的月份不在结果当中，而且这两条select好象可以合而为一。
以上查询可以更简洁地使用以下命令:
SELECTtempyf.*,;
SUM（IIF（ISNULL（recdbf.日期）ORAT（"一般",recdbf.性质）=0,0,1））AS一般,;
SUM（IIF（ISNULL（recdbf.日期）ORAT（"重大",recdbf.性质）=0,0,1））AS重大,;
SUM（IIF（ISNULL（recdbf.日期）ORAT（"特大",recdbf.性质）=0,0,1））AS特大;
FROMtempyfLEFTOUTERJOINrecdbf;
ONtempyf.yf=MONTH（recdbf.日期）ANDYEAR（日期）=?
yy;&&注意这里，on后面是可以加上其它条件的
GROUPBYtempyf.yf
在上例中,yy是指具体那一年，如果不指定的话，那就是把表中所有的记录算在一起。
而且如果要指定具体那一年的话，那这个YEAR（日期）=?
yy的条件是不能放在where或者having里的。
以上查询的另一个有趣的地方是:
sum命令是可以直接累加iif的结果，而且过滤条件从where移到on后会发生这么大的差异！
在where时，是先联接统计后再过滤，结果把那些没有记录的月份也过滤掉了；而在on是先过滤再联接、统计，过滤结果中虽然没有记录的月份也不在其中，但因是左联接，也一样会以null代替，但给iif中的isnull给变成了0.
注：
其中那个ONtempyf.yf=MONTH（recdbf.日期）ANDYEAR（日期）=?
yy在视图生成器中是不能直接做的，而只能用Createsqlviewas以命令的方式生成。
用GROUPBY求分组小计
GROUPBY的意思是按给定字段进行分组,所谓分组就是将一组类似的记录压缩成一个结果记录，这样就可以完成基于一组记录的计算。
例如，假设想找到某一特定地区所有订货的总和，不用单独查看所有的记录，可以把来自相同地区的所有记录合成为一个记录，并获得来自该地区的所有订货的总和。
分组在与某些合计函数联合使用时效果最好，诸如SUM、COUNT、AVG等等。
例如，假设想看到订单表中具有特定ID号的客户订货的金额总值，只需将具有相同CustomerID号的订货记录合成为一个记录，同时寻找订货总量即可。
GROUPBY和DISTINCT之间的选择
如果在字段串列中没使用字段函数，则groupby参数与关键字distinct的效果相同。
不过使用groupby参数的效果显然要比使用关键字distinct来得快，而且在字段串列中的字段数越多，速度的差距会越大。
如果在字段串列中使用字段函数且使用groupby参数，则仍会显示出多个数据记录。
但是如果于字段串列中使用字段且使用关键字distinct，则只有一个数据记录会显示出来。
显然有字段时应使用groupby参数。
例子：
1、selectname,max（salary）fromtestgroupbyname
2、selectdistctname,max（salary）fromtest
结果：
grouybydistinct
namesalarynamesalarynamesalary
alex20alex90mary95
alex10mary44
alex50tom95
alex90
alex30
tom45
tom55
tom15
tom95
mary33
mary44
HAVING子句的使用
我们已经可以求分组小计,但另一问题是：
如何控制或筛选分组?
这里不能用WHERE子句,因为它只能对基表中的记录作筛选。
而我们要筛选的是中间表的分组记录。
HAVING子句可用于控制从中间表到最终结果的过滤。
虽然在HAVING子句中可以用任何合法的字段或表达式,但一般是用列函数。
原因是HAVING子句要跟在GROUPBY子句后使用,这意味着SELECT子句中有列函数,例如:
SELECTSALE_NO,SUM（SALE_AMOUNT）ASAMOUNT;
FROMSALEITEM,SALES;
WHERESALES.SALE_NO=SALEITEM.SALE_NO;
GROUPBYSALE_NO;
HAVINGSUM（SALE_AMOUNT）>1000
以上查询求出销售金额超过1000元的销售单及金额。
HAVING子句中的列函数不必与SELECT子句中的列函数相同,例如
SELECTSALE_NO,SUM（SALE_AMOUNT）ASAMOUNT;
FROMSALEITEM,SALES;
WHERESALES.SALE_NO=SALEITEM.SALE_NO;
GROUPBYSALE_NO;
HAVINGAVG（SALE_AMOUNT）>100
此查询求出平均商品金额在100元以上的销售记录,结果中要求给出销售单的总金额。
WHERE和HAVING都为查询提供过滤条件,经常有人问哪一个要好些。
答案是,为了获取更好的性能,应该用WHERE子句来过滤一般表的记录,而用HAVING过滤GROUPBY后的分组结果。
另外,使用HAVING子句时容易犯的一个错误是:
SELECT*FROMSALESHAVINGSALESMAN_ID=““001”
虽然以上查询也可运行,但不能利用RUSHMORE,因此要比以下查询慢数百到数千倍:
SELECT*FROMSALESWHERESALESMAN_ID=““001”
例如:
对于HAVING子句，select的条件过滤的先后顺序是这样的：
先对join中的on表达式进行过滤，再到where，中间结果出来后再用having进行过滤，最后才把结果显示出来。
所以说having是对select结果的最后一次过滤。
它与where的分别就是where能够事先把不要的数据过滤掉，这样select里头就不用处理那么多的数据。
但有些数据事先不知道要不要过滤，要根据结果才能确定，这时才用having这个事后诸葛亮。
这里用例子来比较一下on、where、having的不同之处
表recdbf内容如下：
还有一个tempyf的辅助表，记录12个月
日期性质 yf
2000年7月3日特大1
2000年7月9日特大2
2000年9月3日特大3
1999年3月2日一般4
1999年3月4日一般5
2000年1月3日一般6
2000年2月1日一般7
2000年2月3日一般8
2000年3月4日一般9
2000年8月7日一般10
2000年11月2日一般11
1999年2月3日重大12
2000年2月3日重大
2000年5月2日重大
2000年8月9日重大
on的命令如下
SELECTtempyf.*,;
SUM（IIF（ISNULL（recdbf.日期）.OR.AT（"一般",recdbf.性质）=0,0,1））AS一般,;
SUM（IIF（ISNULL（recdbf.日期）.OR.AT（"重大",recdbf.性质）=0,0,1））AS重大,;
SUM（IIF（ISNULL（recdbf.日期）.OR.AT（"特大",recdbf.性质）=0,0,1））AS特大;
FROMtempyfLEFTOUTERJOINrecdbf;
ONtempyf.yf=MONTH（recdbf.日期）.AND.YEAR（日期）=?
yy;
GROUPBYtempyf.yf
其中yy=2000，表示统计2000年的数据
用where的命令如下：
SELECTtempyf.*,;
SUM（IIF（ISNULL（recdbf.日期）.OR.AT（"一般",recdbf.性质）=0,0,1））AS一般,;
SUM（IIF（ISNULL（recdbf.日期）.OR.AT（"重大",recdbf.性质）=0,0,1））AS重大,;
SUM（IIF（ISNULL（recdbf.日期）.OR.AT（"特大",recdbf.性质）=0,0,1））AS特大;
FROMtempyfLEFTOUTERJOINrecdbf;
ONtempyf.yf=MONTH（recdbf.日期）;
GROUPBYtempyf.yf;
whereYEAR（日期）=?
yy&&注意，条件从on移到这里来了
用having的命令如下：
SELEC
升级会员 