INFLUXDB-influxDB一些查询语法你必须知道
卫逸春
首先我们向一个表中插入数据，具体格式如下，
插入
insert <measurement>[,<tag-key>=<tag-value>...] <field-key>=<field-value>[,<field2-key>=<field2-value>...] [unix-nano-timestamp]
顺序为：表名（不存在则直接新建）+tag的key和value，field的key和value。注意中间的空格。
> insert test,host=127.0.0.1,monitor_name=test1,app=ios count=2,num=3
> select * from test
name: test
time app count host monitor_name num
---- --- ----- ---- ------------ ---
1585897703920290000 1 127.0.0.1 test
1585897983909417000 ios 2 127.0.0.1 test1 3
1585898383503216000 ios 2 127.0.0.1 test1 3
1585901694441000000 ios 2 127.0.0.1 app1 3
1585901704179677000 ios 2 127.0.0.1 ios1 3
展示tag
> show tag keys from test
name: test
tagKey
------
app
host
monitor_name
从上面可以看到app host monitor_name为tag 而num/count为field
distinct的字段仅是field 不能是tag
##distinct
> select distinct(count) from test
name: test
time distinct
---- --------
0 1
0 2
##distinct 仅仅对field生效，app是tag，不生效，所以没有结果
> select distinct(app) from test
>
在工作中，一个字段经常用来做查询，于是我设置成了tag类型，但是有一个需求是仅仅展示这个tag的value，并且有一些where查询条件，于是我增加了一个冗余的field类型的value1。就是为了满足这个distinct只能是field类型
group by仅是tag 不能是field
## 此时app是tag类型
> select * from test group by app
name: test
tags: app=
time count host monitor_name num
---- ----- ---- ------------ ---
1585897703920290000 1 127.0.0.1 test
name: test
tags: app=ios
time count host monitor_name num
---- ----- ---- ------------ ---
1585897983909417000 2 127.0.0.1 test1 3
1585898383503216000 2 127.0.0.1 test1 3
>
查询单个tag的value值
##查询所以tag为app的value的值
> show tag values from test with key="app"
name: test
key value
--- -----
app ios
##用下面这种方式，是查询不到app的value值的，app为tag的情况
> select app from test
##但是，当查询的包括tag+field的时候，却可以查询出来
> select app,count from test
name: test
time app count
---- --- -----
1585897703920290000 1
1585897983909417000 ios 2
1585898383503216000 ios 2
模糊查询
> select * from test
name: test
time app count host monitor_name num
---- --- ----- ---- ------------ ---
1585897703920290000 1 127.0.0.1 test
1585897983909417000 ios 2 127.0.0.1 test1 3
1585898383503216000 ios 2 127.0.0.1 test1 3
1585901694441000000 ios 2 127.0.0.1 app1 3
1585901704179677000 ios 2 127.0.0.1 ios1 3
## =~/给定字段/ 包含指定字段的
> select * from test where monitor_name =~/app/
name: test
time app count host monitor_name num
---- --- ----- ---- ------------ ---
1585901694441000000 ios 2 127.0.0.1 app1 3
##=~/^给定字段/ 以指定字段开始的
> select * from test where monitor_name =~/^app/
name: test
time app count host monitor_name num
---- --- ----- ---- ------------ ---
1585901694441000000 ios 2 127.0.0.1 app1 3
##=~/给定字段$/ 以指定字段结尾的
> select * from test where monitor_name =~/p1$/
name: test
time app count host monitor_name num
---- --- ----- ---- ------------ ---
1585901694441000000 ios 2 127.0.0.1 app1 3
分页 limit offset
limit pageSize offset (pageIndex - 1) * pageSize
比如：limit 10 offset 15，就是从第15行开始之后的10条数据
> select * from test
name: test
time app count host monitor_name num
---- --- ----- ---- ------------ ---
1585897703920290000 1 127.0.0.1 test
1585897983909417000 ios 2 127.0.0.1 test1 3
1585898383503216000 ios 2 127.0.0.1 test1 3
1585901694441000000 ios 2 127.0.0.1 app1 3
1585901704179677000 ios 2 127.0.0.1 ios1 3
> select * from test limit 2 offset 2
name: test
time app count host monitor_name num
---- --- ----- ---- ------------ ---
1585898383503216000 ios 2 127.0.0.1 test1 3
1585901694441000000 ios 2 127.0.0.1 app1 3
>
没有IN操作，但是有 OR
influxDB中没有in的操作，但是有or。对于习惯了mysql的in来说，用or就需要在代码中循环了。
> select * from test;
name: test
time app count host monitor_name num
---- --- ----- ---- ------------ ---
1585897703920290000 1 127.0.0.1 test
1585897983909417000 ios 2 127.0.0.1 test1 3
1585898383503216000 ios 2 127.0.0.1 test1 3
1585901694441000000 ios 2 127.0.0.1 app1 3
1585901704179677000 ios 2 127.0.0.1 ios1 3
> select * from test where monitor_name = 'test' or monitor_name ='app1'
name: test
time app count host monitor_name num
---- --- ----- ---- ------------ ---
1585897703920290000 1 127.0.0.1 test
1585901694441000000 ios 2 127.0.0.1 app1 3
更多：
类似资料：