集算器中SQL的使用

i数据库的连接与断开

用SQL访问数据库，首先要连接到指定的数据库。连接数据库通常可以选择两种方式：在数据源设定中直接连接，或者在网格中调用函数连接。

 A1=connect("EsProc")

在使用时，直接连接的数据库，直接使用连接名调用，在数据库未断开时有效；而通过函数连接的，使用单元格名调用，在调用函数关闭之前有效。

 AB1=connect("EsProc") 2=EsProc.query("select * from states") 3=A1.query("select * from states") 

同样，数据库连接的断开，也有两种方式：

 A1=connect("EsProc")

在使用时，直接连接的数据库，直接使用连接名调用，在数据库未断开时有效；而通过函数连接的，使用单元格名调用，在调用函数关闭之前有效。

4=A1.close()

    ii简单SQL的使用     

使用db.query()函数即可在指定数据集中执行sql命令。在sql语句中，可以包含各种查询子句以及数据库函数。

 AB1=connect("EsProc") 2=A1.query("select * from states where ShortName like'N%' order by Population desc") 3=A1.close() 

A2中查询结果如下：

       

在sql语句中，也可以使用网格中的其它数据作为参数：    

3=["CA","ME","NM","SC","LA"] 4=A1.query("select * from states where ShortName in(?) order by Area",A3) 

 A4中查询结果如下：

   

iii无返回值的SQL

如果需要通过sql对数据库操作，而没有返回的结果集，如使用create、update、delete等sql语句。那么在执行时需要使用db.execute()函数，同时，由于不需要对单元格赋值，使用”>”代替”=”。例如：

5>A1.execute("update states set ShortName='CAA' where ShortName='CA'") 

在无返回值的sql中，也是可以使用参数的：

6CAACA7>A1.execute("update states set ShortName=? where ShortName=?",A6,B6) 

iv利用SQL的查询结果

在esProc中，可以利用SQL的查询结果，进行过滤、排序、组合等等操作，以提高查询效率，或者解决一些比较复杂的问题。

下面的例子都是基于A2格中的查询结果做的：

 AB1=connect("EsProc") 2=A1.query("select * from states order by Population desc") 

      

例如，对数据过滤：（检索指定缩写的州数据）

3=["CA","ME","NM","SC","LA"] 4=A2.select(A3.pos(shortName)>0) 

  

例如，对数据聚合计算：（缩写以C开头的州共有多少个）

5=A2.count(like(ShortName,"C*")) 

例如，对数据库数据按要求分组：（按照缩写的首字母分组）

6

=A2.group(left(ShortName,1)) 

         

其中选中成员的具体数据为：（双击可查看）

     
v常见的SQL语句与esProc语法的对照

1.       select * from

3=connect("EsProc") 4=A1.query("select * from PlayersHitting") 

查询结果如下：

2.       select … from

3=A2.new(Name,League,AVG,OBP,SLG)=A1.query("select Name,League,AVG,OBP,SLG from PlayersHitting")

从表中取出指定字段，查询结果是相同的，如下：

  

3.       as

4=A2.new(Name,round(OBP+SLG,3):OPS)=A1.query("select Name,round(OBP+SLG,3) as OPS from PlayersHitting")

根据上垒率(On-Base Percentage)和长打率(Slugging Percentage)计算出上垒加长打率(On-Base percentage Plus Slugging percentage保留三位小数)，查询结果是相同的，如下：

    

4.       where

5=A4.select(OPS>0.9)=A1.query("select Name,round(OBP+SLG,3) as OPS from PlayersHitting where OBP+SLG>0.9")

查询上垒加长打率大于0.9的球员，在esProc中，可以利用已有的结果计算，查询结果如下：

  

5.       count、sum、avg、max和min

6=A2.count(AVG>=0.3)=A1.query("select count(Name),round(OBP+SLG,3)  from PlayersHitting where AVG>0.9")

查询打击率(Batting Average)大于或等于0.3的球员数，结果如下：
      

sum、avg、max和min等sql函数的使用方法和count基本类似。在sql函数中需要使用条件时，也可以使用sumif、avgif，maxif，minif和countif这几个函数，如A2.countif(AVG;AVG>=0.3)

6.       distinct 7=A2.id(Team)=A1.query("select distinct Team  from PlayersHitting")

查询打击率(Batting Average)大于或等于0.3的球员，并按照打击率降序和姓名升序排序，查询结果如下：

    

7.       order by

8=A2.select(AVG>=0.3).new(Name,AVG).sort(AVG:-1,Name)=A1.query("select Name,AVG from PlayersHitting where AVG>=0.3 order by AVG desc,Name")

查询打击率(Batting Average)大于或等于0.3的球员，并按照打击率降序和姓名升序排序，查询结果如下：

 

 8.       and、or、not和<>

9=A2.select(SB>=CS&&SB>=20).new(Name,SB,CS)=A1.query("select Name,SB,CS from PlayersHitting where SB>=CS and SB>=20")

查询盗垒成功数(Stolen Bases)大于等于盗垒失败数(Caught Stealing)，且盗垒成功数不小于20的球员，结果如下：        

与之类似，or在esProc中使用操作符“||”，not在esProc中使用操作符“!”，<>在esProc中使用操作符“!=”
9.       like

10=A2.select(like(Name,"C*")).new(Name)=A1.query("select Name from PlayersHitting where Name like 'C%'")

查询姓名以C开头的球员，查询结果如下：

       

在使用like函数时，不同的数据库，对通配符的使用是不同的，如本例中，用通配符“%”来表示零个或多个任意字符，而在某些数据库中，要用通配符“*”；而用esProc来处理的话，对任何数据库，语法都是统一的。
 10.       group

11=A2.new(Name,WT).group(WT)=A1.query("select WT,count(Name) as Count  from PlayersHitting by WT")

根据球员体重分组，在esProc中，可以用group函数对记录分组，结果如下：
   

其中选中成员的具体数据如下：
      

在SQL中，其实并没有真正的“组”的概念，只能在查询中，根据分组直接聚集计算，结果如下：