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关键词配合使用。
返回指定表达式的总计值。
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语句要用圆括号括起来,如前面的例子所示。