vfp中select语句用法.docx
《vfp中select语句用法.docx》由会员分享,可在线阅读,更多相关《vfp中select语句用法.docx(34页珍藏版)》请在冰豆网上搜索。
![vfp中select语句用法.docx](https://file1.bdocx.com/fileroot1/2022-11/27/4f3a53fe-1cc6-4c67-8442-754fd6e76961/4f3a53fe-1cc6-4c67-8442-754fd6e769611.gif)
vfp中select语句用法
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的命令如下: