vfp中select语句用法.docx

1、vfp中select语句用法Visual FoxPro 中的 SQL Select 查询基本的 SQL SelectFrom 子句 在 FROM 子句中指定要查询的表,若指定的表未打开,FoxPro 将自动打开指定的表,但在查询完成后,FoxPro并不自动关闭它所打开的表,若要在查询完成后关闭表,则必须以手动方式关闭它们。若在执行 SQL SELECT 时,FROM 子句中指定的表已经打开,则 FoxPro 以 USE AGAIN 的方式重新打开此表。Select 子句SELECT 子句中指定要查询的结果字段,这些结果可以就是以下基本元素: 通配符 * 表中的字段名 FoxPro 函数 SEL

2、ECT 中可用的列函数 用户自定义函数 常数(常用于作为占位符)以上各元素在SELECT子句中以逗号分开,例如:SELECT 销售地区,销售部门,业务员 ; FROM 销售表若 SELECT 子句中的字段名就是唯一的,则不必在字段名前加别名修饰,SQL 能自动找到这些字段。若字段名就是非唯一的(即同一字段名出现在 FROM 子句中的两个或两个以上表中),FoxPro 返回以下错误信息: | 不唯一,必须加以限定要纠正这一错误,用字段所在表的别名修饰该字段。若在 SELECT 子句中加入了 FoxPro 函数,则意味着该语句不具备可移植性。SQL 与用户自定义函数FoxPro 支持在 Selec

3、t 子句中使用用户定义函数。但就是由于在其它系统中不支持用户定义函数。因此在开发可能移植的系统时不要使用用户自定义函数。Select 子句中的关键字与其它子句Select 子句中的通配符1、(星号)只能使用在字段串列参数中。代表来源数据库中所有的字段。如果 select 就是在二个以上的数据库中进行查询的,则要在号码前加上数据库别名。 * 就是 SQL 中的三个通配符之一(其它两个就是 ? 与 _ ,将在以后讨论),* 只能出现在 Select 子句中,通配符前可以用别名修饰,在此情况下表示要查询指定工作区中的全部字段、同时还可以给出其它字段名、如:Select销售表、*,客户表、客户代码,

4、客户表、客户名称 ; FROM 销售表, 客户表 ; WHERE销售表、客户代码 =客户表、客户代码2、%(百分号)只能使用在 Where 参数中,代表一连串任意字符类似于 DOS 下的 IT*、PRG 中的 “*”。通常与运算符 LIKE 搭配使用:SELECT 客户名 FROM客户表 WHERE客户名 LIKE “成都%公司”以上查询选择所有公司名以”成都”开始,以”公司”结尾的客户,如:成都东方公司、成都西部发展公司等。3、_(下划线)也只能使用在 where 参数中,代表一个任意字符。与DOS 下的 DIR IT?、PRG中的”?”一样。例如 _a% 表示第二个字符为 a 的字串。通常

5、与 LIKE 搭配使用:SELECT 客户名 FROM客户表 WHERE客户名 LIKE “成都_公司”以上查询选择类似于”成都分公司”, ”成都总公司”这样的公司名字。另一种情况就是文字数据中正好包含有星号、百分号、下划线(例如:软件部_业务组),此时可使用关键字 ESCAPE 告诉 SQL 哪一个字符就是常数字符而非通配符。例如:select company from customer where company like%_% escape 此时 escape 所指定字符(即)后的那一个字段(此处为下划线_)将被视为常数字符而非通配符。字段别名(AS) FoxPro 提供了给 Selec

6、t 子句中的字段起别名的方法(这种方法不同于 ANSI/ISO 标准):即可用 AS 子句给出 Select 子句中字段的别名,在多当需要直接浏览查询结果或查询结果中含有计算列时,该方法很有用处、例如:SELECT “物资部” AS 部门,SUM(工资) AS 工资汇总 ;FROM 工资表 ;WHERE 部门编号 = “0013”用 DISTINCT 消去重复的记录SELECT 中的 DISTINCT 关键字,可以消去查询结果中的重复行。例如要字段出所有购买了商的客户,可以用以下 Select 语句:SELECT DISTINCT客户名称 ;FROM 销售细节表注意,这里消去的重复记录就是指

7、SELECT 子句中的所有字段值均相等的记录、当 SQL 遇到 DISTINCT 关键字时,即建立一个中间表。然后以 SELECT 子句中的所有字段建立一个唯一索引,然后将索引用于中间表,并把索引中的记录放入查询结果中。这样就消去了重复记录,但就是当 SELECT 子句中的字段很多时,这一过程会很慢。用 WHERE 子句说明选择记录的条件通常查询并不希望取出所有记录,而只就是希望查出满足一定条件的记录,要做到这一点可以使用 WHERE 子句。WHERE 子句把满足条件的记录作为查询结果。选择条件在 WHERE 子句中可以包含多个选择条件,各条件间用 AND 或 OR 连接而构成较长的逻辑表达式

8、。连接条件当一个查询涉及到多个表时,需要一个特殊类型的 WHERE 条件,称为连接条件,连接条件建立了 FROM 子句中的两表间的一对一或一对多的联系。SQL 与宏SELECT 子句中可以方便快捷地处理宏,在 SELECT 语句中,宏替换只能替换一次,在以下查询之前,需要由用户给出表名与相应的表达式Select * ;FROM (UserTable) ;Where 公司代码 = &Company_code宏还可以用在 LIKE 的模式匹配串:Select Company,CompanyId,SaleName,SaleId ; From Company,SalesMan ; Where Comp

9、any、SaleId = SalesMan、SaleId ; AND Description LIKE %TCL%以上查询中,用户输入一字符串以便在备注字段(Description)中作匹配查找、查询求出的就是包含该字符串的所有公司的住处SQL 与字母大小写SQL 中就是区分字母大小写的,为使查询结果有效,要注意区分查询条件中的大小写、即表中的内容就是大写的查询时必须将它们转换为大写、若表中内容就是大小写混杂的,则以下 Select 语句将难以返回全部结果:Select CompanyId ; From Company ; Where CompanyId = ACME以上查询只返回名为 ACM

10、E 的记录,对于 CompanyId 为 Acme 的记录则查不到,因此安全的方法就是用 UPPER() 函数将所有字符串都转换为大写、以下就是正确的查询:Select CompanyId ; From Company ; Where UPPER(CompanyId) = ACMESQL 与索引WHERE 条件可用 Rushmore 与索引加速查找、SQL 可用任何类型的索引:结构化的 、CDX ,外部的 、CDX 及外部的 、IDX、若要使 SQL 使用外部索引,必须在执行 Select 之前将其打开,如以下语句所示:Use Sales INDEX idx cdxfileSQL 可以以同样的

11、方式作为标准过程代码使用 Rushmore、这时选择条件必须就是可优化的,即选择表达式必须与建立索引的表达式精确匹配、用 Order By 子句对结果排序由于 SQL 优化器就是按它认为的最佳方案去存取数据,因些查询结果中的记录顺序就是动态不定的、许多应用程序需要查询结果就是按一个或多个字段排好序的、为此可用 ORDER BY 子句、ORDER BY 子句可以包含 Select 子句中一样多的字段、例如:Select Company,State,CompanyId ; From Client ; Where State IN(NY,NJ,PA,CA) ; Order By State,Comp

12、any在以上查询中,按所在州及公司名称顺序给出结果、缺省情况下,记录就是升序排字段,也可用 DESCENDING 来说明按降序排序、当然也可以升降序并用,例如:Select Company,State,CompanyId,SaleNo ; From Company,Sales ; Where Company、SaleId = SalesMan、SaleId ; AND State IN(NY,NJ,PA,CA) ; ORDER BY State,Company,SaleNo DESCENDING以上查询结果首先按州及公司名称升序排字段,然后按 SaleNo 降序排序、由于排序就是对查询结果的排

13、序,因此 Order By 子句中的字段必须就是 Select 子句中出现的字段、另外, Order By 子句允许用位标号给出排序字段,这些位标号就是指 Select 子句中出现的序号、例如上一个查询可以写为:Select Company,State,CompanyId,SaleNo ; From Company,Sales ; Where Company、SaleId = SalesMan、SaleId ; AND State IN(NY,NJ,PA,CA) ; ORDER BY 2,1,3 DESCENDING当 Select 子句包含了用户定义函数或 FoxPro 函数(如 UPPER

14、() 与 LEFT() )时,用位标号来给出排序字段显得十分方便、列函数在 Select 子句中,可以用 SQL 提供的五个列函数,如下表所示:SQL 的列函数函数 描述AVG(字段名) 求出指定字段上的平均值,只适用于数值型的字段SUM(字段名) 求出指定字段上的与,只适用于数值型的字段MIN(字段名) 求出指定字段上的最小值,字段可以就是数值型,字符型或日期型MAX(字段名) 求出指定字段上的最大值,字段可以就是数值型,字符型或日期型COUNT(字段名) 求出指定字段上的值的出现个数,可以用 COUNT(*)求出记录数所有列函数的结果都就是单值关于列函数,别有以下需要注意的地方:列函数中的

15、 DISTINCT 可以在任一个列函数中(除了 MIN() 与 MAX()使用 DISTINCT,这样在列函数的求值中将不计重复值、例如:SELECT AVG(DISTINCT SalesPrice) ; FROM Sales在以上查询中,求出商品的销售单价,对于重复的 SalesPrice,只计算一次、注意在 Select 子句中只能使用一次 DISTINCT 、以下查询就是不正确的:SELECT DISTINCT AVG(DISTINCT SalesPrice),SalesMan ; FROM Sales还有一点要注意的就是,在 COUNT() 函数中,* 不能与 DISTINCT 连用(

16、如 COUNT(DISTINCT *),若在 COUNT() 中使用 DISTINCT,需给出字段名、列函数的嵌套以下查询就是列函数嵌套的情况:Select SUM(AVG(SalesPrice),SalesMan ; From Sales ; Group By Product_ID该查询就是想求出每一产品的平均单价的与,但在运行时 FoxPro 会给出如下错误:文件 AVG、prg 不存在这说明列函数就是不能嵌套使用的、例一:人员资料表 ryb,内容如下:科室姓名职称政治面貌办公室awfw助工党员通讯科wafawe高工党员机械科afawe技术员团员财务科2wraw无无人事科afwe工程师无

17、现要统计每个科室各有几名技术员、助工、工程师、高工、党员、团员,以下列表的形式打印出来科室技术员助工工程师高工党员团员财务科223343 命令如下: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) ; from ryb group by 科室例二:表 recdbf性质分一般、重大、特大;日期要统计出某年的 12 个月内每个月每种性质的记录各有多少,若该月

18、没有记录就为零。结果:月份一般重大 特大101 32212 3、12 30 5这跟上面那个例子有点不同,上例只就是按表中科室来分类,而这里就不同,因为不就是每个月都有案件的,但在报表里没有案件的月也要占一行。所以这里要建立一个辅助表:tempyf(yf N(2),内有十二个记录,值为 1 至 12,代表 112 月。我先就是老规则:Select month(日期),; iif(性质=一般,1,0) as 一般,; iif(性质=重大,1,0) as 重大,; iif(性质=特大,1,0) as 特大 ; from recdbf ; where year(日期)=?年份 ; into curs

19、temp1再用 tempyf 左联接临时表 temp1,根据 tempyf、yf 分组统计。但一瞧,结果好象不对,没有记录的月份不在结果当中,而且这两条select好象可以合而为一。以上查询可以更简洁地使用以下命令:SELECT tempyf、*,;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

20、 特大; FROM tempyf LEFT OUTER JOIN recdbf ;ON tempyf、yf = MONTH(recdbf、日期) AND YEAR(日期) = ?yy; &注意这里,on后面就是可以加上其它条件的GROUP BY tempyf、yf在上例中, yy 就是指具体那一年,如果不指定的话,那就就是把表中所有的记录算在一起。而且如果要指定具体那一年的话,那这个 YEAR(日期) = ?yy 的条件就是不能放在 where 或者 having 里的。以上查询的另一个有趣的地方就是:sum 命令就是可以直接累加 iif 的结果,而且过滤条件从 where 移到 on 后会发

21、生这么大的差别！在 where 时,就是先联接统计后再过滤,结果把那些没有记录的月份也过滤掉了;而在 on 就是先过滤再联接、统计,过滤结果中虽然没有记录的月份也不在其中,但因就是左联接,也一样会以 null 代替,但给 iif 中的 isnull 给变成了 0、 注:其中那个 ON tempyf、yf = MONTH(recdbf、日期) AND YEAR(日期) = ?yy 在视图生成器中就是不能直接做的,而只能用 Create sql view as 以命令的方式生成。用 GROUP BY 求分组小计GROUP BY 的意思就是按给定字段进行分组,所谓分组就就是将一组类似的记录压缩成一个

22、结果记录,这样就可以完成基于一组记录的计算。例如,若想找到某一特定地区所有订货的总与,不用单独查瞧所有的记录,可以把来自相同地区的所有记录合成为一个记录,并获得来自该地区的所有订货的总与。分组在与某些合计函数联合使用时效果最好,诸如 SUM、COUNT、AVG 等等。例如,若想瞧到订单表中具有特定 ID 号的客户订货的金额总值,只需将具有相同 Customer ID 号的订货记录合成为一个记录,同时寻找订货总量即可。GROUP BY 与 DISTINCT 之间的选择如果在字段串列中没使用字段函数,则 group by 参数与关键字 distinct 的效果相同。不过使用 group by 参数

23、的效果显然要比使用关键字 distinct 来得快,而且在字段串列中的字段数越多,速度的差距会越大。如果在字段串列中使用字段函数且使用 group by 参数,则仍会显示出多个数据记录。但就是如果于字段串列中使用字段且使用关键字 distinct,则只有一个数据记录会显示出来。显然有字段时应使用 group by 参数。例子:1、select name,max(salary) from test group by name2、select distct name,max(salary) from test结果:grouy by distinct name salary name salary

24、name salary alex 20 alex 90 mary 95 alex 10 mary 44 alex 50 tom 95 alex 90 alex 30 tom 45 tom 55 tom 15 tom 95 mary 33 mary 44HAVING 子句的使用我们已经可以求分组小计,但另一问题就是:如何控制或筛选分组?这里不能用 WHERE 子句,因为它只能对基表中的记录作筛选。而我们要筛选的就是中间表的分组记录。HAVING 子句可用于控制从中间表到最终结果的过滤。虽然在 HAVING 子句中可以用任何合法的字段或表达式,但一般就是用列函数。原因就是 HAVING 子句要跟在

25、 GROUP BY 子句后使用,这意味着 SELECT 子句中有列函数,例如:SELECT SALE_NO,SUM(SALE_AMOUNT) AS AMOUNT ; FROM SALEITEM,SALES ; WHERE SALES、 SALE_NO = SALEITEM、 SALE_NO ; GROUP BY SALE_NO ; HAVING SUM(SALE_AMOUNT)1000以上查询求出销售金额超过 1000 元的销售单及金额。HAVING 子句中的列函数不必与 SELECT 子句中的列函数相同,例如SELECT SALE_NO,SUM(SALE_AMOUNT) AS AMOUNT

26、; FROM SALEITEM,SALES ; WHERE SALES、 SALE_NO = SALEITEM、 SALE_NO ; GROUP BY SALE_NO ; HAVING AVG(SALE_AMOUNT)100此查询求出平均商品金额在100 元以上的销售记录,结果中要求给出销售单的总金额。WHERE 与 HAVING 都为查询提供过滤条件,经常有人问哪一个要好些。答案就是,为了获取更好的性能,应该用 WHERE 子句来过滤一般表的记录,而用HAVING 过滤 GROUP BY 后的分组结果。另外,使用 HAVING 子句时容易犯的一个错误就是:SELECT * FROM SALE

27、S HAVING SALESMAN_ID = “001”虽然以上查询也可运行,但不能利用 RUSHMORE,因此要比以下查询慢数百到数千倍:SELECT * FROM SALES WHERE SALESMAN_ID = “001”示例:对于 HAVING 子句,select 的条件过滤的先后顺序就是这样的:先对join 中的 on 表达式进行过滤,再到 where,中间结果出来后再用 having 进行过滤,最后才把结果显示出来。所以说 having 就是对 select 结果的最后一次过滤。它与 where 的分别就就是 where 能够事先把不要的数据过滤掉,这样 select 里头就不用

28、处理那么多的数据。但有些数据事先不知道要不要过滤,要根据结果才能确定,这时才用 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日 一

29、般 11 1999年2月3日 重大 12 2000年2月3日 重大 2000年5月2日 重大 2000年8月9日 重大 on 的命令如下SELECT tempyf、*,;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 特大; FROM tempyf LEFT OUTER JOIN recdbf

30、;ON tempyf、yf = MONTH(recdbf、日期)、AND、YEAR(日期) = ?yy;GROUP BY tempyf、yf其中 yy=2000,表示统计 2000 年的数据用 where 的命令如下:SELECT tempyf、*,;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 特大;FROM tempyf LEFT OUTER JOIN recdbf ;ON tempyf、yf = MONTH(recdbf、日期);GROUP BY tempyf、yf ;where YEAR(日期) = ?yy & 注意,条件从 o