i数据库的连接与断开
用SQL访问数据库,首先要连接到指定的数据库。连接数据库通常可以选择两种方式:在数据源设定中直接连接,或者在网格中调用函数连接。
A | |
1 | =connect("EsProc") |
在使用时,直接连接的数据库,直接使用连接名调用,在数据库未断开时有效;而通过函数连接的,使用单元格名调用,在调用函数关闭之前有效。
A | B | |
1 | =connect("EsProc") | |
2 | =EsProc.query("select * from states") | |
3 | =A1.query("select * from states") |
同样,数据库连接的断开,也有两种方式:
A | |
1 | =connect("EsProc") |
在使用时,直接连接的数据库,直接使用连接名调用,在数据库未断开时有效;而通过函数连接的,使用单元格名调用,在调用函数关闭之前有效。
4 | =A1.close() |
ii简单SQL的使用
使用db.query()函数即可在指定数据集中执行sql命令。在sql语句中,可以包含各种查询子句以及数据库函数。
A | B | |
1 | =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中,也是可以使用参数的:
6 | CAA | CA |
7 | >A1.execute("update states set ShortName=? where ShortName=?",A6,B6) |
iv利用SQL的查询结果
在esProc中,可以利用SQL的查询结果,进行过滤、排序、组合等等操作,以提高查询效率,或者解决一些比较复杂的问题。
下面的例子都是基于A2格中的查询结果做的:
A | B | |
1 | =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. distinct7 | =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中,其实并没有真正的“组”的概念,只能在查询中,根据分组直接聚集计算,结果如下: