SELECT语句

一、基本语法

SELECT 字段列表 FROM {表名} WHERE 条件 GROUP BY 分组字段 ORDER BY 排序字段

其中"WHERE"、"GROUP BY"和"ORDER BY"是可选的 ,这三者的顺序是不能调换的。

再次提示:SQL语句中的表名必须用大括号括起来,这是Foxtable的一个非常特殊的要求。

示例一

SELECT 产品, 客户, 单价, 数量, 日期 FROM {订单}

字段列表不能包括表达式列,例如金额列是一个表达式列,是通过数量和单价计算得出的,所以金额列不能出现在字段列表中。
这是因为表达式列并没有存储在后台的数据源中。

示例二

如果你要包括所有字段,那么直接用*代替字段列表即可:

SELECT * FROM {订单}

示例三

如果需要排除重复的行,可以在字段列表前面加上关键词DISTINCT,例如获得所有产品名称:

SELECT DISTINCT 产品 FROM {订单}

再例如获得每个客户订购过的产品:

SELECT DISTINCT 客户, 产品 FROM {订单}

示例四

如果不需要获得所有的数据,可以使用TOP关键词指定行数,假定只需加载10行数据:

SELECT TOP 10 * FROM {订单}

如果要按百分数加载数据,那么请加上PERCENT关键词,例如加载10%的订单:

SELECT TOP 10 PERCENT * FROM {订单}

TOP 和 PERCENT关键词对于Oracle数据源是无效的,Oracle用ROWNUM,这是一个默认的列,表示加载行的序号,例如:

Select * From {EMPLOYEES} Where ROWNUM <= 50

表示从EMPLOYEES表加载前50行数据。

示例五

可以用ORDER BY指定排序方式,例如根据日期顺序加载数据:

SELECT * FROM {订单} ORDER BY 日期

默认的排序方式是升序,如果希望降序排序,可以在排序列后面加上DESC关键词,例如:

SELECT * FROM {订单} ORDER BY 日期 DESC

排序列可以是多列,例如首先根据产品排序,相同产品的行则根据日期排序:

SELECT * FROM {订单} ORDER BY 产品,日期

示例六

TOP关键词如果没有和ORDER BY配合使用,那么毫无意义,因为得到的只是随机提取的一些行。

如果你要获得最近的10个订单:

SELECT TOP 10 * FROM {订单} ORDER BY 日期 DESC

注意必须加上关键词DESC,否则你得到的将是最早的10个订单,而不是最近的10个订单

由于Oracle没有TOP关键词,所以要实现上述目标,须使用下面的语句:

SELECT * FROM {订单} Where ROWNUM <= 10 ORDER BY 日期 DESC

示例七

可以使用AS关键词重新命名列,例如:

SELECT 产品, 客户, 单价, 数量 AS 订购数量,日期 AS 订购日期 FROM {订单}

上述查询语句得到的结果中,"数量"列将被改名为"订购数量","日期"列被改名为"订购日期"

示例八

你还可以直接在SELECT语句中使用表达式,例如:

SELECT 产品, 客户, 单价, 数量, 数量 * 单价 AS 金额, 日期 FROM {订单}

上述的语句新生成一个金额列,该列的值等于数量乘以单价(数量*单价)

二、条件表达式

可以用WHERE关键词设置查询条件,多个条件可以用AND或者OR运算符连接。

例如加载产品为PD01和PD02订单:

SELECT * FROM {订单} WHERE 产品 = 'PD01' OR 产品 = 'PD02'

再例如加载1999年1月份的订单:

SELECT * FROM {订单} WHERE 日期 >= #1/1/1999# AND 日期 <= #1/31/1999#

如果数据源是SQl SERVER,日期常量用单引号而不是#号括起来,例如:

SELECT * FROM {订单} WHERE 日期 >= '1/1/1999' AND 日期 <= '1/31/1999'

如果是Oracle的数据源,必须用TO_DATE函数转换,例如加载1999年10月21日的订单:

SELECT * FROM {订单} WHERE 日期 = TO_DATE('10/21/1999','MM/DD/YYYY')

IN运算符

IN运算符是用来判断一个表达式的值是否属于一个指定列表中的值。

例如同样是加载产品PD01和PD02的订单,用IN运算符可以简化为:

SELECT * FROM {订单} WHERE 产品 IN ('PD01','PD02')

可以用NOT关键词来进行反向的操作,例如加载除产品PD01和PD02之外的订单:

SELECT * FROM {订单} WHERE 产品 NOT IN ('PD01','PD02')

显然,指定的值越多,IN运算符的优势越明显。

BETWEEN运算符

BETWEEN运算符用于判断一个表达式的值是否介于一个特定的范围之间,起始值和终止值用运算符AND连接起来

例如加载1999年1月份的订单:

SELECT * FROM {订单} WHERE 日期 BETWEEN #1/1/1999# AND #1/31/1999#

再例如加载折扣在0.1到0.2之间的订单

SELECT * FROM {订单} WHERE 折扣 BETWEEN 0.1 AND 0.2

可以用NOT关键词来进行反向的操作,例如加载折扣在0.1到0.2之外的订单:

SELECT * FROM {订单} WHERE 折扣 NOT BETWEEN 0.1 AND 0.2

LIKE运算符

LIKE运算符用于将指定表达式的值和一个模式字符串进行比较,判断两者是否匹配。

LIKE可以使用通配符,通配符包括:

_(下划线)
匹配单个字符

%(百分号)
匹配任意个数的字符

例如:

Like 'C%',

表示以字符C开始的值。

在例如:

Like 'A_e%'

表示第一个字母是A,第三个字母是e。

找出姓名为三个字,且第二个字是“文”的客户:

Select * From {客户} Where 姓名 Like '_文_'

找出客户表中的贸易公司:

SELECT * FROM {客户} WHERE 公司名称 LIKE '%贸易%'

可以利用NOT关键词进行反向操作,例如:

SELECT * FROM {客户} WHERE 公司名称 NOT LIKE '%贸易%'

找出所有非贸易公司。 

IS NULL 运算符

IS NULL操作符被用于判断一个表达式的值是否为空,例如:

SELECT * FROM {订单} WHERE 折扣 IS NULL

表示加载没有输入折扣的订单,注意0不是空,所以上面的语句不会加载折扣为0的订单。

SELECT * FROM {订单} WHERE 折扣 IS NULL OR 折扣 = 0

表示加载出没有输入折扣或者折扣为0的订单。

可以用NOT进行反向操作,例如:

SELECT * FROM {订单} WHERE 折扣 IS NOT NULL

表示加载出已经输入折扣的订单。

三、 数据统计和聚合函数

聚合函数用于统计数据,通常和GROUP BY关键词配合使用。

SUM

返回指定表达式的总计值。

AVG

返回指定表达式的平均值。

MAX

返回指定表达式的最大值。

MIN

返回指定表达式的最小值。

COUNT

返回符合指定条件的记录数

Var

返回指定表达式的总体方差

VarP

返回指定表达式的总体方差

StDev

返回指定表达式的总体标准偏差

StDevP

返回指定表达式的总体样本标准偏差

示例一

统计订单表总的订购数量:

SELECT SUM(数量) AS 总计 FROM {订单}

再例如统计总的订单数:

SELECT Count(*) AS 总订单数 FROM {订单}

可以同时进行多个统计:

SELECT SUM(数量) AS 总计, AVG(数量) AS 平均 , MAX(数量) AS 最大, MIN(数量) AS 最小 FROM {订单}

可以得到订单表数量列的累计值、平均值、最大值、最小值。

示例二

更多的时候,聚合函数是GROUP BY关键词配合使用的,用于进行分组统计。

例如统计每个客户订购产品的数量:

SELECT 客户,SUM(数量) AS 数量 FROM {订单} GROUP BY 客户

分组列必须同时包括在SELECT子句和GROUP BY子句中,例如上面的客户列。

示例三

统计每个客户的订单数、订购数量、订购金额:

SELECT 客户,Count(*) AS 订单数, SUM(数量) AS 数量, SUM(数量 * 单价 * (1 - 折扣)) AS 金额 FROM {订单} GROUP BY 客户

注意金额列是通过一个表达式计算得出的。

示例四

可以有多个分组列,例如统计每个客户订购不同产品的数量:

SELECT 客户, 产品, SUM(数量) AS 数量 FROM {订单} GROUP BY 客户,产品

四、HAVING字句

HAVING和WHERE有点类似,不同的是WHERE用于过滤记录,而HAVING用于过滤分组。

例如我要统计出定购PD01产品的客户以及其订购数量,而且只列出总订购数量超过500的客户:

SELECT 客户,SUM(数量) FROM {订单} WHERE 产品 = 'PD01' GROUP BY 客户 HAVING SUM(数量) > 500

五、UNION 运算符

UNION运算符用于组合两个查询的结果。
例如有一个客户表,一个供应商表,我需要得到所有在中国的客户和供应商的名称和地址。
因为数据位于不同的表中,显然,我们需要用两个查询才能完成任务:

SELECT 公司名称, 地址 FROM {客户} WHERE 国家 = '中国' UNION SELECT 公司名称, 地址 FROM {供应商} WHERE 国家 = '中国'

两个查询的字段名称、个数、类型必须完全一致才行。

默认情况下,UNION会自动排除重复的行,然后你可以用ALL关键词来确保返回所有的行,例如:

SELECT 公司名称, 地址 FROM {客户} WHERE 国家 = '中国' UNION ALL SELECT 公司名称, 地址 FROM {供应商} WHERE 国家 = '中国'

六、 子查询

例如希望得到数量大于平均订购数量的订单:

Select * From {订单} Where 数量 > (Select Avg(数量) From {订单})

上述语句中的绿色部分,是一个独立的SQL语句,用于得到平均订购数量,这就是子查询,子查询必须用括号括起来。

子查询可以针对另一个表,例如希望得到单价大于平均销售单价的产品:

Select * From {产品} Where 单价 > (Select Avg(单价) From {订单明细})

再例如希望得到订单数超过50个的产品:

Select * From {产品} Where (Select Count(*) From {订单明细} Where {订单明细}.产品ID = 产品.产品ID) > 50

子查询只能包括一列,多于一列会报错。

IN子查询

In子查询用于判断查询结果的某列,在另一个查询的某列是否有对应的值。

例如查询在1998年5月1日之后订购过产品的客户的详细资料:

SELECT * FROM {客户} WHERE 客户ID IN (SELECT 客户ID FROM {订单} WHERE 订购日期 > #1998-5-1#)

你可以用NOT进行反向操作,例如我们希望查询在1998年5月1日之后没有订购过产品的客户的详细资料:

SELECT * FROM {客户} WHERE 客户ID NOT IN (SELECT 客户ID FROM {订单} WHERE 订购日期 > #1998-5-1#)

EXISTS子查询

EXISTS用于判断子查询是否有记录。

例如要列出所有订购过产品的客户资料:

SELECT * FROM {客户} WHERE EXISTS (SELECT * FROM {订单} WHERE {订单}.客户ID = {客户}.客户ID)

请注意子查询的比较语句:

{订单}.客户ID = {客户}.客户ID

该语句明确了比较字段的表名,因为两个字段来源于不同的表。

你可以用NOT进行反向操作,例如列出从来没有订购过产品的客户资料:

SELECT * FROM {客户} WHERE NOT EXISTS (SELECT * FROM {订单} WHERE {订单}.客户ID = {客户}.客户ID)

、多表查询

一般用户可以忽略以下内容,因为Foxtable提供了SQLJoinTableBuilder,可以轻松解决这种多表查询问题。

有的时候,你可能希望查询表的列来自于多个表,可以通过JOIN语句来实现这样的功能。JOIN语句可以让你从已经定义了相互关系的工作表中检索记录,而不用管记录和工作表之间的关系是一对一、一对多还是多对多。

语法:

SELECT 字段列表 FROM {表1} INNER JOIN {表2} ON {表1}.列名 = {表2}.列名

ON关键词指定两个表通过哪一列进行连接,指定的字段必须具备相同的数据类型和长度。

如果某个字段在不止一个表中出现,必须在Select语句中明确表的名称,格式为: {表名}.列名

示例:

SELECT {订单明细}.*, 产品名称 FROM {订单明细} INNER JOIN {产品} ON {订单明细}.产品ID = {产品}.产品ID ORDER BY 订单ID

上述语句生成的查询表,包括订单明细的所有列,以及该订单明细对应的产品名称(来源于产品表)

INNER JOIN要求两个表必须都有对应的记录,才会在查询表中生成记录。你还可以用LEFT JOIN 或者 RIGHT JOIN,前者只要求左边的表有对应的记录即可,后者只要求右边的表有对应的记录即可。

数据不仅可以来自于两个表,还可以来自于更多的表,例如下面的语句:

SELECT 订购日期, {订单明细}.*, 产品名称 FROM ({订单明细} INNER JOIN {产品} ON {订单明细}.产品ID = {产品}.产品ID) INNER JOIN {订单} ON {订单明细}.订单ID = {订单}.订单ID ORDER BY 订购日期

上面的查询表的列来自于三个表,其中订购日期来自于订单表,产品名称来自于产品表,其余来自订单明细表。

下面的查询,列来自于四个表:

SELECT 订购日期,公司名称,产品名称, 数量, {订单明细}.单价, 折扣, (数量 * {订单明细}.单价 * (1-折扣)) AS 金额 FROM (({订单明细} INNER JOIN {产品} ON {订单明细}.产品ID = {产品}.产品ID) INNER JOIN {订单} ON {订单明细}.订单ID = {订单}.订单ID) INNER JOIN {客户} ON {订单}.客户ID = {客户}.客户ID

在上面的查询中,因为产品表和订单明细表都有单价列,所以字段列表中对于单价列的引用,要明确引用的是哪一个表的单价列,例如:订单明细.单价

此外,在有多个JOIN语句的情况下,靠前的JOIN语句要用圆括号括起来,如前面的例子所示。


本页地址:http://www.foxtable.com/webhelp/topics/0688.htm