vfp中select语句用法.docx

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