加载中…
个人资料
  • 博客等级:
  • 博客积分:
  • 博客访问:
  • 关注人气:
  • 获赠金笔:0支
  • 赠出金笔:0支
  • 荣誉徽章:
正文 字体大小:

ExecuteSQL函数详解

(2012-12-31 18:00:33)
标签:

filemaker

it

分类: FileMaker函数

FileMaker Pro 12中的ExecuteSQL是一个很重要的函数,使用它可以简化很多关系的建立,比如原来某个值列表需要建立关系从关联表获取,现在只需要一个ExecuteSQL语句就可以得到了。之前我写了一篇博文简要的介绍了一下它的语法(http://blog.sina.com.cn/s/blog_495e704c01012bmn.html),并根据W3C School的SQL部分对每个基本的SQL语句进行了测试性的验证。今天我准备更加详细的把ExecuteSQL的用法介绍一下。可以说FileMaker Pro支持的SQL语句主要是依据FileMaker 12 ODBC and JDBC Guide文档的第7章(http://www.filemaker.com/support/product/docs/12/fmp/fm12_odbc_jdbc_guide_en.pdf),请结合该文档的第七章阅读本文。之前也介绍过了,FileMaker Pro 12支持的SQL语句仅限于Select。下面先看看我们的实例表和测试数据:

我们有3张表T1,T2,T3:

http://s8/mw690/495e704cgd2231aa06ce7&690

他们的表摹本名分别为First1,Second2和Third3:

http://s4/mw690/495e704cgd2231b9c6073&690

这是T1表的测试数据:

http://s5/mw690/495e704cgd2231c99d764&690
T2表的测试数据:

http://s15/mw690/495e704cgd284be71123e&690

T3表的测试数据:

http://s7/mw690/495e704cgd2231caee6c6&690

首先看最基本的查询,查找一张表的全部数据,需要提一下的就是在ExecuteSQL中表名应该使用表摹本名,在上面的实例数据中应该这么写:

ExecuteSQL("Select * from First1";" ";"@")

而不能写成Select * from T1,否则会返回问号。另外SQL表达式是不区分大小写的,即SELECT * FROM FIRST1也正确。

下面看选择特定的某列数据,如果要选择T1表的data列,使用下面的表达式:

ExecuteSQL("Select data from First1";" ";"@")

如果需要对结果进行条件选择,需要使用Where字句,

ExecuteSQL("Select data from First1 Where data>?";" ";"@";"200")

使用Where进行比较是区分大小写的,a是不等于A的。where支持的比较运算符请参见FileMaker 12 ODBC and JDBC Guide文档的第49页。

举个稍微复杂的例子: ExecuteSQL("Select data from First1 Where (id IS NOT NULL AND data Between ? and ?)";" ";"@";"10";"50")

使用LIKE关键字可以进行模糊查询(%表示任意字符串,_表示一个字符串,这两个字符可以放在任何地方构成模糊查询):

ExecuteSQL("Select data from First1 where id LIKE ?"; " ";"@";"a%")

使用IN关键字可以对特定集合中的元素进行匹配:

ExecuteSQL("Select data from First1 where id IN ('aa','Cd')"; " ";"@")

可以在IN关键字的匹配条件中使用子查询:

ExecuteSQL("Select data from First1 where id IN (Select DISTINCT fk from Second2 where nn>4)"; " ";"@")

上面这个例子出现了DISTINCT关键字,它的意思是返回不重复的结果,即在上例中,本来子查询是要求Second2表中nn大于4的记录中fk的值,符合条件的有5,6,7这3条记录,而6,7的fk值重复,则返回的结果只有“ab Cc”而不是“ab Cc Cc”。

如果需要对结果进行排序,需要使用Order By字句:

ExecuteSQL("Select id from First1 Order by id DESC;" ";"@")

可以在Select语句中使用连接符:

ExecuteSQL("Select ‘hello-’+id, data*2 from First1";" ";"@")

可以用AS关键字为Select语句中的元素设置副本名:

ExecuteSQL("Select ‘hello-’+id AS name, data*2 from First1";" ";"@")


可以在ExecuteSQL中使用SQL函数:

ExecuteSQL("Select MONTH.(F.birthday) from First1 AS F where data>?";" ";"@";"200")

这里使用了MONTH系统函数,ExecuteSQL支持的其他关于时间的系统函数请参见FileMaker 12 ODBC and JDBC Guide文档的第53页。

关于字符串的系统函数请参见FileMaker 12 ODBC and JDBC Guide文档的第51页。

举个例子:ExecuteSQL("Select UPPER(F.id) from First1 AS F where data>?";" ";"@";"200")

关于聚集(aggregate functions)的函数请参见FileMaker 12 ODBC and JDBC Guide文档的第46页

举个例子:ExecuteSQL("Select SUM(F.data) from First1 AS F where id LIKE ?";" ";"@";"C%")

在了解了聚集函数后,就可以看看Select语句中的Group By关键字了,Group By是对Select出的初步结果按照by后面的字段分类,计算聚集函数的结果,所以需要注意的是Select的字段必须包含聚集函数:

ExecuteSQL("Select SUM(nn) from Second2 Group By fk"; " "; "@")

如果写为ExecuteSQL("Select nn from Second2 Group By fk"; " "; "@")则返回?

在了解了Group By后,就可以看看Having子句了,Having子句的作用是进一步限制Group By后跟的条件,例如:

ExecuteSQL("Select SUM(nn) from Second2 Group By fk,Having SUM(nn)>6 "; " "; "@")

这里注意到Having子句中可以有聚集函数,而Where子句是不可以的,例如:

ExecuteSQL("Select SUM(nn) from Second2 Where SUM(nn)>6 Group By fk"; " "; "@") 这样会返回问号。

这个例子其实举得不是特别好,关于Having的用法,推荐看看这篇文章:http://www.5iphp.com/node/62

当语句中既有Where又有Group By又有Having时,执行的顺序应该是这样的:

先通过Where筛选出所有符合条件的数据集,然后再用Group By后的字段分类,最后用Having后的条件对分类的数据集再过滤。

关于数学计算的函数请参见FileMaker 12 ODBC and JDBC Guide文档的第52页。

举个例子:ExecuteSQL("Select SQRT(F.data) from First1 AS F";" ";"@")

最后来看看用SQL查询关联表的用法,首先可以直接用Where语句进行关系搭建,例如:

ExecuteSQL("Select S.nn from First1 AS F, Second2 AS S where F.id=S.fk";" ";"@")

或者使用JOIN和ON关键字:

ExecuteSQL("Select S.nn from First1 AS F JOIN Second2 AS S ON F.id=S.fk";" ";"@")

或者使用INNER JOIN和ON关键字:

ExecuteSQL("Select S.nn from First1 AS F INNER JOIN Second2 AS S ON F.id=S.fk";" ";"@")

如果不使用Where也不使用JOIN等关键字,直接Select两张表上的字段,则会返回一个笛卡尔积关系:

ExecuteSQL("Select F.birthday, S.nn from First1 AS F, Second2 AS S";" ";"@")

如果有多张表关联,则需要写多个JOIN条件:

ExecuteSQL("Select T.Tdata from First1 AS F JOIN Second2 AS S ON F.id=S.fk JOIN Third3 AS T ON S.SID=T.TID where F.id=?";" ";"@";"aa")

ExecuteSQL仅支持Left Outer Join(关于Left Outer Join的具体含义请自行百度之,很多帖子讲解很详细),举个例子:

ExecuteSQL("Select F.id, F.data, S.fk, S,nn from First1 AS F LEFT OUTER JOIN Second2 AS S ON F.id=S.fk";" ";"@")

ExecuteSQL还支持UNION 和 UNION ALL(具体用法请百度之),举个例子:

ExecuteSQL("Select F.id, F.data from First1 AS F UNION Select S.fk, S.nn from Second2 AS S";" ";"@")

ExecuteSQL("Select F.id, F.data from First1 AS F UNION ALL Select S.fk, S.nn from Second2 AS S";" ";"@")

ExecuteSQL不支持RIGHT OUTER JOIN,FULL OUTER JOIN,INTERSECT(在两个集合中都存在的数据) 和 EXCEPT(第一个集合中存在,但是不存在于第二个集合中的数据),所以以下表达式将会得出问号结果:

ExecuteSQL("
Select F.id, F.data, S.fk, S.nn from First1 AS F RIGHT OUTER JOIN Second2 AS S ON F.id=S.fk";" ";"@")

ExecuteSQL("
Select F.id, F.data, S.fk, S.nn from First1 AS F FULL OUTER JOIN Second2 AS S ON F.id=S.fk";" ";"@")

ExecuteSQL("
Select F.data from First1 AS F INTERSECT Select S.nn from Second2 AS S";" ";"@")

ExecuteSQL("
Select F.data from First1 AS F EXCEPT Select S.nn from Second2 AS S";" ";"@")

ExecuteSQL还支持SQL的逻辑分支功能,比如CASE:

ExecuteSQL("Select id, CASE WHEN id='aa' THEN 1 WHEN id='ab' THEN 2 ELSE 0 END from First1 where data<?";" ";"@";"36")

以上基本把ExecuteSQL所有的简单用法包括了,如果是初学者建议按我的例子表和数据,把每一条ExecuteSQL语句执行一遍,看看结果,体会用法。

0

阅读 收藏 喜欢 打印举报/Report
  

新浪BLOG意见反馈留言板 欢迎批评指正

新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 产品答疑

新浪公司 版权所有