FileMaker Pro12中的ExecuteSQL是一个很重要的函数,使用它可以简化很多关系的建立,比如原来某个值列表需要建立关系从关联表获取,现在只需要一个ExecuteSQL语句就可以得到了。之前我写了一篇博文简要的介绍了一下它的语法(http://blog.sina.com.cn/s/blog_495e704c01012bmn.html),并根据W3CSchool的SQL部分对每个基本的SQL语句进行了测试性的验证。今天我准备更加详细的把ExecuteSQL的用法介绍一下。可以说FileMakerPro支持的SQL语句主要是依据FileMaker 12 ODBC and JDBC Guide文档的第7章(http://www.filemaker.com/support/product/docs/12/fmp/fm12_odbc_jdbc_guide_en.pdf),请结合该文档的第七章阅读本文。之前也介绍过了,FileMakerPro 12支持的SQL语句仅限于Select。下面先看看我们的实例表和测试数据:
我们有3张表T1,T2,T3:
他们的表摹本名分别为First1,Second2和Third3:
这是T1表的测试数据:
T2表的测试数据:
T3表的测试数据:
首先看最基本的查询,查找一张表的全部数据,需要提一下的就是在ExecuteSQL中表名应该使用表摹本名,在上面的实例数据中应该这么写:
ExecuteSQL("Select * from First1";" ";"@")
而不能写成Select * from T1,否则会返回问号。另外SQL表达式是不区分大小写的,即SELECT * FROMFIRST1也正确。
下面看选择特定的某列数据,如果要选择T1表的data列,使用下面的表达式:
ExecuteSQL("Select data from First1";" ";"@")
如果需要对结果进行条件选择,需要使用Where字句,
ExecuteSQL("Select data from First1 Wheredata>?";" ";"@";"200")
使用Where进行比较是区分大小写的,a是不等于A的。where支持的比较运算符请参见FileMaker 12 ODBC andJDBC Guide文档的第49页。
举个稍微复杂的例子:ExecuteSQL("Select data from First1Where (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 DISTINCTfk from Second2 where nn>4)"; " ";"@")
上面这个例子出现了DISTINCT关键字,它的意思是返回不重复的结果,即在上例中,本来子查询是要求Second2表中nn大于4的记录中fk的值,符合条件的有5,6,7这3条记录,而6,7的fk值重复,则返回的结果只有“abCc”而不是“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 wheredata>?";" ";"@";"200")
这里使用了MONTH系统函数,ExecuteSQL支持的其他关于时间的系统函数请参见FileMaker 12 ODBC andJDBC Guide文档的第53页。
关于字符串的系统函数请参见FileMaker 12 ODBC and JDBC Guide文档的第51页。
举个例子:ExecuteSQL("Select UPPER(F.id) from First1 AS F wheredata>?";" ";"@";"200")
关于聚集(aggregate functions)的函数请参见FileMaker 12 ODBC and JDBCGuide文档的第46页
举个例子:ExecuteSQL("Select SUM(F.data) from First1 AS F where idLIKE ?";" ";"@";"C%")
在了解了聚集函数后,就可以看看Select语句中的Group By关键字了,GroupBy是对Select出的初步结果按照by后面的字段分类,计算聚集函数的结果,所以需要注意的是Select的字段必须包含聚集函数:
ExecuteSQL("Select SUM(nn) from Second2 Group By fk"; " ";"@")
如果写为ExecuteSQL("Select nn from Second2 Group By fk"; " ";"@")则返回?
在了解了Group By后,就可以看看Having子句了,Having子句的作用是进一步限制GroupBy后跟的条件,例如:
ExecuteSQL("Select SUM(nn) from Second2 Group By fk,HavingSUM(nn)>6 "; " "; "@")
这里注意到Having子句中可以有聚集函数,而Where子句是不可以的,例如:
ExecuteSQL("Select SUM(nn) from Second2 WhereSUM(nn)>6 Group By fk"; " "; "@") 这样会返回问号。
这个例子其实举得不是特别好,关于Having的用法,推荐看看这篇文章:http://www.5iphp.com/node/62
当语句中既有Where又有Group By又有Having时,执行的顺序应该是这样的:
先通过Where筛选出所有符合条件的数据集,然后再用GroupBy后的字段分类,最后用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 whereF.id=S.fk";" ";"@")
或者使用JOIN和ON关键字:
ExecuteSQL("Select S.nn from First1 AS F JOIN Second2 AS S ONF.id=S.fk";" ";"@")
或者使用INNER JOIN和ON关键字:
ExecuteSQL("Select S.nn from First1 AS F INNER JOIN Second2 AS SON F.id=S.fk";" ";"@")
如果不使用Where也不使用JOIN等关键字,直接Select两张表上的字段,则会返回一个笛卡尔积关系:
ExecuteSQL("Select F.birthday, S.nn from First1 AS F, Second2 ASS";" ";"@")
如果有多张表关联,则需要写多个JOIN条件:
ExecuteSQL("Select T.Tdata from First1 AS F JOIN Second2 AS S ONF.id=S.fk JOIN Third3 AS T ON S.SID=T.TID where F.id=?";"";"@";"aa")
ExecuteSQL仅支持Left Outer Join(关于Left OuterJoin的具体含义请自行百度之,很多帖子讲解很详细),举个例子:
ExecuteSQL("Select F.id,F.data, S.fk, S,nn from First1 AS F LEFT OUTER JOIN Second2 AS S ONF.id=S.fk";" ";"@")
ExecuteSQL还支持UNION 和 UNION ALL(具体用法请百度之),举个例子:
ExecuteSQL("Select F.id, F.data from First1 AS F UNION SelectS.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 ASS";" ";"@")
ExecuteSQL不支持RIGHT OUTER JOIN,FULL OUTERJOIN,INTERSECT(在两个集合中都存在的数据)和 EXCEPT(第一个集合中存在,但是不存在于第二个集合中的数据),所以以下表达式将会得出问号结果:
ExecuteSQL("
SelectF.id, F.data, S.fk, S.nn from First1 AS F RIGHT OUTER JOIN Second2AS S ON F.id=S.fk";" ";"@")
ExecuteSQL("
SelectF.id, F.data, S.fk, S.nn from First1 AS F FULL OUTER JOIN Second2AS S ON F.id=S.fk";" ";"@")
ExecuteSQL("
SelectF.data from First1 AS F INTERSECT Select S.nn from Second2 ASS";"";"@")
ExecuteSQL("
SelectF.data from First1 AS F EXCEPT Select S.nn from Second2 ASS";"";"@")
ExecuteSQL还支持SQL的逻辑分支功能,比如CASE:
ExecuteSQL("Select id, CASE WHENid='aa' THEN 1 WHEN id='ab' THEN 2 ELSE 0 END from First1 wheredata<?";"";"@";"36")
以上基本把ExecuteSQL所有的简单用法包括了,如果是初学者建议按我的例子表和数据,把每一条ExecuteSQL语句执行一遍,看看结果,体会用法。