Visual FoxPro在SQL方面支持数据定义、数据查询和数据操纵功能,

  SQL语言的核心是查询。SQL语言的查询命令也称作SELECT命令,它的基本形式由

SELECT-FROM-WHERE查询块组成,多个查询块可以嵌套执行。Visual FoxPro的SQL-SELECT

命令的语法格式如下:

    SELECT[ALL | DISTINCT][TOP nExpr[PERCENT]]

    [Alias]Select_Item[AS Column_Name][,[Alias]Select_Item[AS Column-Name]…]

    FROM  [FORCE][DatabaseName!] Table [[As]  Local_Alias]

    [[INNER | LEFT[OUTER]| RIGHT[OUTER]| FULL[OUTER] JOIN

    DatabaseName!]Table[[AS]Local_Alias]

    [ON JoinCondition…]

-    [[INTO Destination]

    |[TO FILE FileName[ADDITIVE] |  TO PRINTER[PROMPT]|  TO SCREEN]]

    [WHERE JoinCondition [AND JoinCondition…]

    [AND | OR FilterCondition [AND | OR FilterCondition…]]]

    [GROUP BY GroupColumn[,GroupColumn…]]

    [HAVING FilterCondition]

    [UNION [ALL] SELECT Command]

    [ORDER BY Order_Item [ASC | DESC][,Order_Item [ASC | DESC]…]]

从SELECT的命令格式来看似乎非常复杂,实际上只要理解了命令中各个短语的含义,SQL

SELECT还是很容易掌握的,其中主要短语的含义如下:

  ·SELECT说明要查询的数据；

  ·FROM说明要查询的数据来自哪个(些)表,可以基于单个表或多个表进行查询；

  ·WHERE说明查询条件,即选择元组的条件；

  ·GROUP BY短语用于对查询结果进行分组,可以利用它进行分组汇总；

  ·HAVING短语必须跟随GROUP BY使用,它用来限定分组必须满足的条件；

  ·ORDER BY短语用来对查询的结果进行排序。

  以上短语是学习和理解SQL SELECT命令必须要掌握的,还有一些短语是Visual FoxPro特有的。

  SELECT查询命令的使用非常灵活,用它可以构造各种各样的查询。本节将通过大量的实

例来介绍SELECT命令的使用,在例子中再具体解释各个短语的含义。

以下为订货仓库数据库:

仓库表:

职工表:

订购表

注:Null是空值,这里的意思是还没有确定供应商,自然也没有确定订购日期

供应商表:

5.2.1简单查询

    首先从几个最简单的查询开始,这些查询基于单个表,查询由SELECT和FROM短语构成

(无条件查询)或由SELECT、FROM和WHERE短语构成(条件查询)。

  例5.1从职工关系中检索所有工资值。

    SELECT 工资 FROM 职工

  结果是:

    1220

    1210

    1250

    1230

    1250

  可以看到,在结果中有重复值,如果要去掉重复值只需要指定DISTINCT短语:

    SELECT DISTINCT 工资 FROM 职工

  DISTINCT短语的作用是去掉查询结果中的重复值。

  例5.2检索仓库关系中的所有元组。

    SELECT  *  FROM 仓库

  结果是:

    WH1  北京  370

    WH2  上海  500

    WH3  广州  200

    WH4  武汉  400

  其中"*"是通配符,表示所有属性(字段),这里的命令等同于:

    SELECT 仓库号,城市,面积 FROM 仓库

  例5.3 检索工资多于1230元的职工号。

    SELECT 职工号 FROM 职工 WHERE 工资>1230

  结果是:

    E4

    E7

  这里用WHERE短语指定了查询条件,查询条件可以是任意复杂的逻辑表达式。

  例5.4检索哪些仓库有工资多于1210元的职工。

SELECT DISTINCT 仓库号 FROM 职工 WHERE 工资>1210

  结果是:

    WH1 

    WH2

    WH3

  在这个例子中,显然我们只对惟一的仓库号感兴趣。但在例5.1中我们也许对所有的工资值都感兴趣,因此不管是否有重复值。决定是否消除重复值是有一定实际意义的,也是SQL的重要一面。再回到例5.1,假设我们对所有职工的平均工资感兴趣,那么指定DISTINCT显然是错误的,我们将在计算检索中再来讨论这个问题。

  例5.5 给出在仓库"WHl"或"WH2"工作并且工资少于1250元的职工号。

    SELECT 职工号 FROM 职工;

      WHERE 工资<1250  AND  (仓库号="WHl" OR 仓库号="WH2")

  结果是:  

    SELECT 职工号,城市 FROM 职工,仓厍;

      WHERE  (工资>1230)  AND (职工.仓库号=仓库.仓库号)

  结果是:

    E4  上海

    E7  北京

  这里的"职工.仓库号=仓库.仓库号"是连接条件。

  如果在检索命令的FROM之后有两个关系,那么这两个关系之间肯定有一种联系(否则无法构成检索表达式)。从前面的讨论已经知道,仓库关系和职工关系之间存在着一个一对多的联系。

  当FROM之后的多个关系中含有相同的属性名时,这时必须用关系前缀指明属性所属的关系,如职工.仓库号,"."前面是关系名,后面是属性名

  例5.7找出工作在面积大于400的仓库的职工号以及这些职工工作的城市。

    select 职工号,城市 from 仓库,职工;

      Where  (面积>400) and  (职工.仓库号=仓库.仓库号)

  结果是:

    E1  上海

    E4  上海

5.2.3嵌套查询

    接着来讨论另一类基于多个关系的查询,这类查询所要求的结果出自一个关系,但相关的条件却涉及多个关系。在5.2.2节的例子中,WHERE之后是一个相对独立的参件,这个条件或者为真,或者为假。但是,有时我们需要用另外的方式来表达检索要求,例如,当检索关系x中的元组时,它的条件依赖于相关的关系y中的元组的属性值,这时使用SQL的嵌套查询功能将非常方便。下面就来看几个例子。

    例5.8哪些城市至少有一个仓库的职工的工资为1250元?

    这个例子要求查询仓库表中的城市信息,而查询条件是职工表的工资字段值,为此可以使用

如下的嵌套查询:

    SELECT 城市 FROM 仓库 WHERE 仓库号 IN;

      (SELECT 仓库号 FROM 职工WHERE 工资=1250)

.  结果是:

  .    北京

       上海

    我们看到,在这个命令中含有两个SELECT—FROM—WHERE查询块,即内层查询块和外层

查询块,内层查询块检索到的仓库号值是WH1和WH2,这样就可以写出等价的命令:

    SELECT 城市 FROM 仓库 WHERE 仓库号 IN ("WH1","WH2")

    这里IN相当于集合运算符∈。 

    例5.9 查询所有职工的工资都多于1210元的仓库的信息。

    这个检索要求也可以描述为:没有一个职工的工资少于或等于1210元的仓库的信息

    这样可以有SQL命令:

    SELECT  *  FROM 仓库 WHERE 仓库号 NOT IN;

       (SELECT 仓库号 FROM 职工 WHERE 工资<=1210)

    结果是:

    WH2  上海  500

    WH3  广州  200

    WH4  武汉  400

    内层SELECT—FROM—WHERE查询块指出所有职工的工资少于或等于1210元的仓库的

仓库号值的集合,在这里该集合只有一个值"WH1";然后再从仓库关系中检索元组的仓库号属性值不在该集合中的每个元组。

    有的读者也许已经注意到刚才的检索出现了错误,尽管在"武汉"的"WH4"仓库还没有职工,但该仓库的信息也被检索出来了。所以我们必须认真分析检索要求,写出正确的

    如果我们的检索要求要排除那些还没有职工的仓库,检索要求可以叙述为:检索所有职工的工资都多于1210元的仓库的信息,并且该仓库至少要有一名职工。这样描述就很清楚了,因为我们对没有职工的仓库不感兴趣。这样,写出的SQL命令也就复杂一些了:

    SELECT * FROM 仓库 WHERE 仓库号 NOT IN;

     (SELECT 仓库号 FROM 职工 WHERE 工资<=1210);

      AND 仓库号 IN (SELECT 仓库号 FROM 职工)

  这样,内层是两个并列的查询,在结果中将不包含没有职工的仓库信息。

  例5.10找出和职工E4挣同样工资的所有职工。

    SELECT 职工号 FROM 职工 WHERE工资=;

    (SELECT 工资 FROM 职工 WHERE 职工号="E4")

  结果是:

    E4   

    E7   

5.2.4几个特殊的运算符

  下面有几个特殊的运算符,它们是Between... And和Like等,下面通过例子来解释

  例5.11 检索出工资在1220元到1240元范围内的职工信息

  这个查询的条件是在什么范围之内,显然可以使用Between...And,谓词有如下查询语句

    Select * from 职工 where 工资 Between 1220 and 1240

  结果是:

    WH2  E1  1220

    WH3  E6  1230

  这里Between...And的意思是在"...和...之间",这个查询的条件等价于:

    (工资>=1220) And(工资<=1240)

  显然使用Between...And表达条件更清晰、更简洁

  例5.12从供应商关系中检索出全部公司的信息(不要工厂或其他供应商的信息)。

  这是一个字符串匹配的查询,显然应该使用LIKE运算符:

    SELECT * FROM 供应商 WHERE 供应商名 LIKE "%公司"

  结果是: 

    s4  华通电子公司  北京

  这里的LIKE是字符串匹配运算符,通配符"%"表示0个或多个字符,另外还有一个通配符

"-"(下划线)表示一个字符。

  例5.13找出不在北京的全部供应商信息。

    SELECT * FROM 供应商 WHERE 地址!="北京"

  结果是:

    s3  振华电子厂  西安

    S6  607厂       郑州 

    在SQL中,"不等于"用"!="表示。另外还可以用否定运算符NOT写出等价的命令:

    SELECT * FROM 供应商 WHERE NOT(地址="北京")

    NOT的应用范围很广,例如,可以有NOT IN(见例5.9)、NOT  BETWEEN等。假如提出

和例5.11相反的请求,找出工资不在1220元和1240元之间的全部职工信息,可以用命令

    SELECT * FROM 职工 WHERE 工资 NOT BETWEEN 1220 AND 1240

    结果是:

    WH1  E3  1210

    WH2  E4  1250

    WH1  E7  1250   

5.2.5排序

    使用SQL SELECT可以将查询结果排序,排序的短语是ORDER BY,格式如下:

    ORDER BY Order_Item [ASC | DESC][,Order_Item [ASC | DESC]...]

    可以看出,可以按升序(ASC)或降序(DESC)排序,可以按一列或多列排序。下面是两个查

询结果排序的例子。

    例5.14按职工的工资值升序检索出全部职工信息。

    SELECT * FROM 职工 ORDER BY 工资

    结果是:

    WH1    E3    1210

    WH2    E1    1220

    WH3    E6    1230

    WH2    E4    1250

    WHl    E7    1250

    这里ORDER BY是排序子句,如果要将结果按降序排列,只要加上DESC:

    SELECT  *  FROM 职工 ORDER BY 工资 DESC

    例5.15先按仓库号排序,再按工资排序输出全部职工信息。

    SELECT  *  FROM 职工 ORDER BY 仓库号,工资

    结果是:

    WH1    E3    1210

    WH1    E7    1250

    WH2    E1    1220

    WH2    E4    1250

    WH3    E6    1230

    这是一个按多列排序的例子。

    注意:ORDER BY是对最终的查询结果进行排序,不可以在子查询中使用该短语。

5.2.6简单的计算查询

    SQL语言是完备的,也就是说,只要数据是按关系方式存人数据库的,就能构造合适的SQL

命令把它检索出来。事实上,SQL不仅具有一般的检索能力,而且还有计算方式的检索,例如检索职工的平均工资、检索某个仓库中职工的最高工资值等。用于计算检索的函数有:COUNT(计数)、SUM(求和)、AVG(计算平均值)、MAX(求最大值)、MIN(求最小值)。

  这些函数可以用在SELECT短语中对查询结果进行计算。

  例5.16找出供应商所在地的数目。    .

    SELECT COUNT(DISTINCT 地址)  FROM 供应商

  参见前面给出的供应商的记录值,其中共有3个地址:北京、西安和郑州,所以结果为3。注意,除非对关系中的元组个数进行计数,一般COUNT函数应该使用DISTINCT。

    SELECT COUNT(*) FROM 供应商

将给出供应商关系中的记录数。

  例5.17求支付的工资总数。

    SELECT SUM(工资) FROM 职工

  结果是:

      6160

 这个结果是职工关系中的工资值的总和,它并不管是否有重复值,这时若使用命令:

    Select Sum(Distinct 工资) From 职工

将得出错误的结果是4910

例5.18  求北京和上海的仓库职工的工资总和

    Select Sum(工资) From 职工 Where 仓库号 in;

      (select 仓库号 from 仓库 where 城市="北京" or 城市="上海")

结果是:

   4930

例5.19 求所有职工的工资都多于1210元的仓库的平均面积

    Select Avg(面积) From 仓库 Where 仓库号 Not In;

       (Select 仓库号 From 职工 Where 工资<=1210)

    结果是:

     366.67

    这里要注意,以上结果的运算包含了尚没有职工WH4仓库,如果要排除没有职工的仓库,以上语句应该改为:

    SELECT AVG(面积) FROM 仓库 WHERE 仓库号 NOT IN;

      (SELECT 仓库号 FROM 职工 WHERE 工资<=1210);

    AND 仓库号 IN (SELECT 仓库号 FROM 职工)

  结果是:

    350

例5.20 求在WH2仓库工作的职工的最高工资值。

    SELECT MAX(工资) FROM 职工 WHERE 仓库号="WH2"

  结果是:

    1250

  与MAX函数相对应的是MIN函数(求最小值)。比如,求最低工资值可以有如下命令:

    SELECT MIN(工资) FROM 职工 WHERE 仓库号="WH2"

5.2.7分组与计算查询

    5.2.6节的几个例子是针对整个关系的计算查询,而利用GROUP BY子句进行分组计算查

询使用的更加广泛。GROUP BY短语的格式如下:

    GROUP BY GroupColumn[,GroupGolumn…][HAVING FilterCondition]

    可以按一列或多列分组,还可以用HAVING进一步限定分组的条件。下面是几个分组计算

查询的例子。

    例5.21求每个仓库的职工的平均工资。

    SELECT 仓库号,AVG(工资) FROM 职工;

      GROUP BY仓库号

    结果是:

    WH1    1230

    WH2    1235

    WH3    1230

    在这个查询中,首先按仓库号属性进行分组,然后再计算每个仓库的平均工资。Group By

子句一般跟在WHERE子句之后,没有WHERE子句时,跟在FROM子句之后。另外,还可以根

据多个属性进行分组。

   在分组查询时,有时要求分组满足某个条件时才检索,这时可以用HAVING子句来限定

分组。

    例5.22 求至少有两个职工的每个仓库的平均工资。

    SELECT 仓库号,COUNT(*),AVG(工资) FROM 职工;

      GROUP BY 仓库号 HAVING COUNT(*)>=2

    结果是:

    WH1  2    1230

    WH2  2    1235

    HAVING子句总是跟在GROUP BY子句之后,而不可以单独使用。HAVING子句和

WHERE子句不矛盾,在查询中是先用WHERE子句限定元组,然后进行分组,最后再用Having

子句限定分组。

5.2.8利用空值查询

    SQL支持空值,当然也可以利用空值进行查询。

    假设在订购单关系中,一名职工正在准备订购单,但尚未选定供应商,这样若把信息

据库,则供应商号和订购日期两个属性均为空值,在前面给出的订购单记录中有3条这样的

记录。

    例5.23  找出尚未确定供应商的订购单。

    SELECT * FROM 订购单 WHERE 供应商号 IS NULL

    结果是:

    E6    NULL    OR77    NULL

    E1    NULL    OR80    NULL

E3    NULL    OR90    NULL

注意:查询空值时要使用IS NULL,而=NULL是无效的,因为空值不是一个确定的值,所以不要用”=”这样的运算符进行比较

例:5.24列出已经确定了供应商的订购单的信息

Select * From 订购单 Where 供应商号 Is Not Null

    在新的SQL标准中还支持两个新的关系连接运算符,它们与原来我们所了解的等值连接和

自然连接不同。原来的连接是只有满足连接条件,相应的结果才会出现在结果表中；而这两个新

的连接运算是,首先保证一个表中满足条件的元组都在结果表中,然后将满足连接条件的元组与

另一个表的元组进行连接,不满足连接条件的则应将来自另一表的属性值置为空值。

5.2超连接查询

  Visual FoxPro有专门的连接运算语法格式,它支持超连接查询,我们从本节开始给出的SQL SELECT语句的完整语法格式中抽出的与连接运算有关的语法格式如下:

    SELECT…

    FROM Table INNER | LEFT | RIGHT | FULL JOIN Table

      ON JoinCondition

    WHERE ...

其中:

  •INNER JOIN等价于JOIN,为普通连接;

   Left   join为左连接

   Right  join为右连接

   Full   join为完全连接,即两个表中的记录不管是否满足连接条件将都在目标表或

查询结果中出现,不满足连接条件的记录的对应部分为Full

   On  指定连接条件

例5.31  普通连接(即只有满足连接条件的记录才出现在查询结果中)

     Select 仓库.仓库号,城市,面积,职工号,工资;

       From 仓库 Join 职工;

        ON仓库.仓库号=职工.仓库号

 和

    SELECT 仓库.仓库号,城市,面积,职工号,工资;

    FROM仓库,职工 WHERE 仓库.仓库号=职工.仓库号

  以上三种连接语句的结果都是:

    WH2  上海  500  E1  1220

    WH1  北京  370  E3  1210

    WH2  上海  500  E4  1250

    WH3  广州  200  E6  1230

    WH1  北京  370  E7  1250

  例5.32左连接(即除满足连接条件的记录出现在查询结果中外,第一个表中不满足连接

条件的记录也出现在查询结果中)。

    SELECT 仓库.仓库号,城市,面积,职工号,工资;

      FROM  仓库  LEFT JOIN  职工;

       ON 仓库.仓库号=职工.仓库号

  结果是:

    WH1  北京  370  E3  1210

    WH1  北京  370  E7  1250

    WH2  上海  500  E1  1220

    WH2  上海  500  E4  1250

    WH3  广州  200  E6  1230

    WH4  武汉  400  NULL NULL

    为了看到右连接和全连接的效果,假设在职工表中插入了如下一条记录:

    "WH8","E8",1200

    例5.33  右连接(即除满足连接条件的记录出现在查询结果中外,第二个表中不满足

条件的记录也出现在查询结果中)。

    SELECT 仓库.仓库号,城市,面积,职工号,工资;

    FROM 仓库 RIGHT JOIN 职工;

      ON仓库.仓库号=职工.仓库号

    结果是:

    WH2  上海  500  E1  1220

    WH1  北京  370  E3  1210  

    WH2  上海  500  E4  1250

    WH3  广州  200  E6  1230

    WH1  北京  370  E7  1250

    NULL NULL  NULL E8  1200

    实际上职工"E8"所在的仓库并不存在,这在实际应用中是不允许的。

    例5.34全连接(即除满足连接条件的记录出现在查询结果中外,两个表中不满足连接条

件的记录也出现在查询结果中)。

    SELECT 仓库.仓库号,城市,面积,职工号,工资;

    FROM 仓库 FULL JOIN 职工;

      ON  仓库.仓库号=职工.仓库号

    结果是:

    WH1  北京  370  E3  1210

    WHl  北京  370  E7  1250

    WH2  上海  500  E1  1220

    WH2  上海  500  E4  1250

    WH3  广州  200  E6  1230

    WH4  武汉  400  NULL NULL

    NULL NULL  NULL E8  1200

    注意:JOIN连接格式在连接多个表时的书写方法要特别注意,在这种格式中JOIN

 ON的顺序是很重要的,特别要注意JOIN的顺序要和ON的顺序(相应的连接条件)正好相反

5.2.13集合的并运算

  SQL支持集合的并(UNION)运算，即可以将两个SELECT语句的查询结果通过并运算合并

成一个查询结果。为了进行并运算，要求两个查询结果具有相同的字段个数，并且对应字段的值

要出自同一个值域(相同的数据类型和取值范围)

  例如,如下语句的结果是城市为北京和上海的仓库信息:

    Select * from 仓库 Where 城市="北京";

    Union;

    Select * from 仓库 Where 城市="上海"

5.2.14 Visual Foxpro 的几个特殊选项

1.只显示前几项纪录

  有时人们只需要满足条件的前几个记录,这时使用Top短语比较合适

例4.35  显示工资最高的3位职工的信息

   Select * Top 3 From 职工 Order by 工资 DESC

 结果是:

    WH2  E4  1250

    WH1  E7  1250

    WH3  E6  1230

例5.36显示工资最低的那30%职工的信息。

    SELECT * TOP 30 PERCENT FROM 职工 ORDER BY 工资

结果是：

    WH5  E8    1200

    WH1  E3    1210

2.将查询结果存放到数组中

可以使用INTO ARRAY ArrayName短语将查询结果存放到数组中，ArrayName可以是任意的

数组变量名。一般将存放查询结果的数组作为二维数组来使用，每行一条记录，每列对应于查询

结果的一列。查询结果存放在数组中，可以非常方便地在程序中使用。

如下语句将查询到的职工信息存放在数组tmp中:

    Select * from 职工 Into Array tmp

   tmp(1,1)存放的是第1条记录的仓库号字段值,tmp(1,3)存放的是第1条记录的工资字段

值等。 

    3.将查询结果存放在临时文件中

    使用短语INTO CURSOR CursName可以将查询结果存放到临时数据库文件,其中

CursorName是临时文件名,该短语产生的临时文件是一个只读的.dbf文件,当查询结束后该临时

文件是当前文件,可以像一般的.dbf文件一样使用(当然是只读),当关闭文件时该文件将自动

删除。   

    如下语句将查询到的职工信息存放在临时.dbf文件tmp中:

    SELECT * FROM 职工 INTO CURSOR tmp

    一般利用INTO CURSOR短语存放一些临时结果,比如一些复杂的汇总可能需要分阶段完

成,需要根据几个中间结果再汇总等,这时利用该短语存放中间结果就非常合适,使用完后这些

临时文件会自动删除。

    4.将查询结果存放到永久表中

    使用短语INTO DBF | TABLE TableName可以将查询结果存放到永久表中(.dbf文件),例

如将例5.35的查询结果存放在表highsal中可以使用如下语句:

    SELECT * TOP 3 FROM 职工 INTO TABLE highsal ORDER BY 工资 DESC

  5.将查询结果存放到文本文件中

    使用短语TO FILE FileName [ADDITIVE]可以将查询结果存放到文本文件中,其中

FileName给出了文本文件名(默认扩展名是.TXT),如果使用ADDITIVE则结果将追加在原文件

的尾部,否则将覆盖原有文件。

    如下语句将查询结果以文本的形式存储在文本文件tmp.txt中:

    SELECT * TOP 3  FROM 职工 TO FILE tmp ORDER BY工资 DESC

    如果TO短语和INTO短语同时使用,则TO短语将会被忽略。

    6.将查询结果直接输出到打印机

    使用短语TO PRINTER[PROMPT]可以直接将查询结果输出到打印机,如界使用了

PROMPT选项,在开始打印之前会打开打印机设置对话框。

    以上用大量的实例介绍了SQL SELECT语句的使用方法,这些实例都可以在Visual Foxpro下执行。掌握SQL SELECT语句不仅对学好、用好Visual FoxPro至关重要,也是以后使用其他数据库或开发数据库应用程序的基础。