vfp中select语句用法.docx
《vfp中select语句用法.docx》由会员分享，可在线阅读，更多相关《vfp中select语句用法.docx（34页珍藏版）》请在冰豆网上搜索。
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之前将其打开,如以下语句所示:
UseSalesINDEXidxcdxfile
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&&注意,条件从o
升级会员 