Skip to content

SQL使用说明

1.注意

1.select请带入where条件
2.历史必须带入时间段和测点信息
3.update更新表仅更新现有字段,例如更新realtime表,指定AV字段,则仅更新AV

2.表操作支持

1)V4.0.23及之前和V5.0.3及之前版本:表支持情况

描述SELECTINSERTUPDATEDELETE过滤条件分页选项排序选项
DATABASE数据库表
NODE节点表
POINT点表
REALTIME实时数据表×
ARCHIVE历史数据表
STAT历史统计表×××
ALARM实时报警表×××
AALARM历史报警表×××

1)V4.0.24和V5.0.4及之后版本:表支持情况

描述SELECTINSERTUPDATEDELETE过滤条件分页选项排序选项
DATABASE数据库表
NODE点表
POINT节点表
REALTIME实时数据表×
ARCHIVE历史数据表
STAT历史统计表×××
ALARM实时报警表×××
AALARM历史报警表×××
User用户表
Groups安全组表
Access权限表

3.常用参数解释

字段类型说明
PNchar(32)名称,支持数字、字母和特殊字符#😡-_
MODEhidden textraw:原始值(加开始结束时间)、arch:原始值、span:等间距、plot:绘图值、 flow:流量值、 max:最大值、min:最小值、 avg:面积平均值、 mean:算术平均值、 stdev:标准方差、sum:算术和
INTERVALhidden text时间间隔:h,m,s
QTYPEhidden tinyint质量过滤:0-不过滤,1-去除坏点, 2-去除超时,3-去除1/2

注:raw:原始值(加开始结束时间):如果开始和结束时间无原始值保存,数据库会还原出一个结果。arch:原始值,查询结果根据真实原始值返回。

4.常用语法示例

语句语句示例应用场景功能点备注
selectselect PN from Point where ID in (1024,1025,1028)常见字段查询in
selectselect PN from Point where ID NOT in (1024,1025,1028)常见字段查询Not in
selectselect PN from Point where ID < 1100常见字段查询比较操作符[=, !=, >, <, >=, <=]
selectselect ID from Point where PN regexp "POINT\d*"常见字段查询正则匹配操作匹配点名形如"POINT123"的点
selectselect ID from Point where PN like "_INT%"常见字段查询like匹配,通配符_, %匹配形为"XINT001"的点
selectselect ID from Point where PN NOT like "%POINT%"常见字段查询NOT like匹配PN中不含有POINT的点
selectselect PN from Point where ID between 1024 and 1099常见字段查询between andbetween and 只用于数值类型和date
selectselect (DS & 0x0f << 8) AS XX from Realtime where ID = 1024移位操作获取某值位操作符[与,或,非, <<, >>]
selectselect (AV + 100) AS value from Realtime where ID = 1025算术表达式操作算术表达式[+,-,*,/]
selectselect AV as XX from Realtime where ID = 1024查询结果取别名as别名
selectselect PN from Point where ID in (1025, 1024, 1026) order by ID结果排序order by
selectselect PN from Point where ID > 1030 limit 0,20查询结果分页limit
selectselect count(ID) from Point where ID > 1099查询记录数函数count()
selectselect GN,AV,TM from Archive where mode="max" and interval="5h" and GN like "%00%" and TM between "2017-5-26 13:00:00.000" and "2017-06-27 14:00:00.000";查询历史查询历史的mode和interval的设置方法mode的取值:raw, span, plot, flow, max, min, avg, mean, stdev, sum;interval的取值:interval不支持小数写法,单位有:y年,d日,h时,m分,s秒,ms毫秒,w星期,q季度;其中,TM的设置时分秒可以缺省,缺省值:00:00:00.000
selectselect PN,AV,DS from Realtime where ID in (1024,1025,1028) and ds=0虚拟状态字段查询模拟状态字段清单
selectselect PN,AV,TV,ED from Alarm where ID in (1024,1025,1028)静态字段虚拟字段非静态数据表内,可以查看静态测点字段
selectselect ID,GN,AV from Realtime where TM=strftime("%s","2017-06-27 10:49:03","utc")查询指定时刻实时值
selectSELECT strftime("%Y/%m/%d %H时%M分%f秒", TM, "unixepoch", "localtime"),GN,TM,DS,ED,AV from Archive where id=51023 and TM BETWEEN "2024-03-28" AND "2024-04-30"查询历史数据并转换格式

5.常用语句示例

数据库表SQL示例语句说明
Root表select * from Root;查询所有字段信息
Root表select IP,PO from Root;查询IP和PO字段
Root表update Root set PO=8500;更新PO字段信息
Root表select IP,PO,MP from Root;查询IP、PO、MP字段信息
Product表select * from Product;查询所有字段信息
Product表select ET,HO from Product;查询指定字段ET,HO
Database库表select * from Database;查询所有字段信息
Database库表select ID,PN,ED from Database;查询指定字段 ID,PN,ED
Database库表select * from Database where id=10;根据指定库ID查询
Database库表insert into Database (PN,DD,ED) values ("magus","magus","magus"),("W6","W6","W6");插入多库,指定名称,数据文件夹,描述,适用于V4.0数据库
Database库表insert into Database (PN,ED) values ("magus","magus"),("W6","W6");插入多库,指定名称,描述,适用于V5.0数据库
Database库表update Database set ED="W3" where PN="W3";更新指定条件数据库的描述
Database库表update Database set PN="magus" where PN="test";修改数据库名称,此操作变更较大
Database库表delete from Database where PN like "W%";根据PN过滤条件删除指定数据库
Database库表delete from Database where ID in (2,9);根据ID过滤条件删除指定数据库
NODE节点表insert into Node (ID,PN) values (11,"SSS001"),(12,"SSS002"),(13,"SSS003");根据ID,PN插入NODE
NODE节点表select * from Node;查询所有字段信息
NODE节点表select ID,PN,ED,LC from Node where id in (1,10);根据ID过滤条件,查询指定字段
NODE节点表select ID,PN,ED,LC from Node where PN like "S%";根据PN过滤条件,查询指定字段
NODE节点表select ID,PN,ED,LC from Node where ID between 10 and 15;根据ID过滤条件,查询指定字段
NODE节点表delete from Node where ID between 11 and 13;根据ID条件删除NODE
NODE节点表insert into Node (GN) values ("W3.GN003"),("W3.GN001"),("W3.GN002");根据GN插入NODE
NODE节点表delete from Node where GN like "%GN%";根据GN like条件删除NODE
NODE节点表delete from Node where GN ="W3.GN003";根据GN条件删除NODE
NODE节点表insert into Node (PN) values ("SSS001"),("SSS002"),("SSS003");根据PN插入NODE
NODE节点表update Node set LC=1 where PN like "S%";更新指定条件的点表字段
NODE节点表"delete from Node where PN like "S%" ;根据PN条件删除NODE
NODE节点表insert into Database (ID,PN,DD) values (2,"magus","magus");插入指定ID,PN,DD的多库
NODE节点表insert into MAGUS.NODE (ID,GN) values (2,"MAGUS.TEST1"),(3,"MAGUS.GN001");插入多库点表,表名带库名
NODE节点表select ID,PN from MAGUS.Node ;查询多库点表
NODE节点表select ID,PN,ED,LC from Node where ID in (10,15);根据ID过滤条件,查询指定字段
Point点表insert into Node (ID,GN) values (10,'W3.GN01');插入点表
Point点表insert into Point (GN) values ("W3.GN01.Point001"),("W3.GN01.Point002"),("W3.GN01.Point003");插入测点,根据GN插入测点
Point点表insert into Point (PN,ND) values ("PNND001",10),("PNND002",10),("PNND003",10);插入测点,根据节点和测点名称插入
Point点表insert into Point (ID,PN,ND) values (6666,"IDNDPN_001",10),(6667,"IDNDPN_002",10),(6668,'IDNDPN_003",10);插入测点,指定ID和GN
Point点表select ID,PN,GN from Point ;查询所有字段信息
Point点表select GN,ID,PN,ED,EU from Point where id between 6667 and 7000;查询指定字段信息,根据ID过滤
Point点表select count(ID) from Point where PN like "%00%";统计符合条件的测点数目
Point点表select ID,PN,ED,LC from Point where GN like "%00%";查询指定字段信息,根据GN过滤
Point点表select ID,PN,ED from Point where GN like "%/_%" escape "&quot;;查询GN带下划线的测点信息,根据GN过滤
Point点表select ID,PN,ED,LC from Point where PN like "%00%";查询指定字段信息,根据PN过滤
Point点表update Point set ED="this is a test",EU="W" where PN="POINT002" and ND =10;更新符合条件的测点ED和EU字
Point点表update Point set ND=10 where PN like "Test%";更新符合条件的测点所属父点表
Point点表update Point set ND=10 where PN like "Test%" and ND=16;更新符合条件的测点所属父点表
Point点表update Point set L4=10,L3=20,ZL=30,LL=40,HL=50,ZH=60,H3=70,H4=80 where PN like "Point%";更新测点报警配置
Point点表update Point set PT=1,KT=1,EX="return 99" where PN like "Point001";更新测点计算点配置
Point点表update Point set PT=1 where GN="W3.GN01.POINT001';更新测点计算点配置
Point点表insert into Database (ID,PN,DD) values (2,"magus","magus");插入多库
Point点表insert into NODE (GN) values ("MAGUS.TEST"),("MAGUS.GN001");插入多库点表
Point点表insert into POINT (GN) values ("MAGUS.TEST.TEST001"),("MAGUS.TEST.TEST002"),("MAGUS.TEST.TEST003");插入多库测点,根据GN
Point点表update Point set PT=1,KT=1,EX="return 77" where PN like "%00%";更新多库测点计算点配置
Point点表select GN,PT,KT,EX from Point where PN like "%00%";查询多库测点计算点配置
Point点表delete from Point where ID between 1024 and 1026;删除测点,根据ID过滤
Point点表delete from Point where ND= 10 and PN ="POINT001';删除测点,根据点表ND和测点ID过滤
Point点表delete from Point where GN like '%00%';删除测点,根据GN过滤
Point点表delete from Point where GN ="W3.GN01.POINT001';删除测点,根据GN过滤
Point点表select * from Point where id <1280;删除测点,根据ID过滤
Realtime实时表select * from Realtime;查询实时所有字段信息
Realtime实时表select * from Realtime where GN like "%00%";查询实时数据,根据GN过滤
Realtime实时表select * from Realtime where GN like "%00%";查询多库实时信息,根据GN过滤
Realtime实时表select * from Realtime where GN like "%00%" and ds=-32768 and AV>10;查询所有信息,根据GN、DS和AV组合过滤
Realtime实时表insert into Realtime values (1024," W3.GN01.Point001" ," 2017-06-27 10:49:03",0,666);插入指定测点指定时间的实时数据
Realtime实时表select ID,GN,AV from Realtime where TM=strftime('%s','2017-06-27 10:49:03','utc');查询实时数据,根据TM过滤
Realtime实时表update Realtime set AV=1000 where GN like " %00%" ;更新实时值,仅更新实时值不改变时间
Realtime实时表select strftime("%Y/%m/%d %H:%M:%S", datetime(TM,"unixepoch")) as TIME ,AV from Realtime limit 10查询实时并转换时间格式
Archive历史表insert into Archive values (1024," W3.GN001.POINT001" ," 2017-05-26 15:49:03",0,666);插入指定ID测点指定时刻历史值,状态
Archive历史表select ID,GN,AV,TM,DS from Archive where GN="W3.GN001.POINT001" and TM between "2017-5-26 00:00:00" and "2017-05-27 00:00:00" ;查询历史数据,根据GN和TM过滤
Archive历史表insert into Archive values (2098176," MAGUS.GN001.TEST001" ," 2017-05-26 15:49:03",0,666);插入多库指定ID测点指定时刻历史值,状态
Archive历史表select * from Archive where GN="W4.GN001.TEST001" and TM between "2017-5-26 13:00:00.000" and "2017-05-27 00:00:00.000';查询多库历史数据,根据GN和TM过滤
Archive历史表update Archive set AV=999, DS=0 where GN="W3.GN001.POINT001" and TM="2017-05-26 15:49:03";更新指定GN测点指定时刻历史值,状态
Archive历史表update Archive set AV=999, DS=0 where GN="MAGUS.GN001.TEST001" and TM="2017-05-26 15:49:03";更新多库指定GN测点指定时刻历史值,状态
Archive历史表select ID,GN,AV,TM,DS from Archive where GN like "%00%" and TM ="2017-06-12 16:42:35"查询指定时刻历史数据,根据GN和TM过滤
Archive历史表select ID,GN,TM,DS,AV from Archive where GN like "%00%" and TM between "2017-05-26 09:30:00" and "2017-05-27 09:40:00" limit 0,20;查询历史数据,根据GN和TM过滤,like
Archive历史表select ID,GN,TM,DS,AV from MAGUS.Archive where GN like "%00%" and TM between "2017-05-26 09:30:00" and "2017-05-27 09:30:10";查询多库历史数据,根据GN和TM过滤,like
Archive历史表select * from Archive where mode="raw" and GN like "%00%" and TM between "2017-5-26 13:00:00.000" and "2017-05-27 14:00:00.000";查询历史数据,根据mode,GN和TM过滤
Archive历史表select ID,GN,AV,TM from MAGUS.Archive where mode="max" and interval ="1h" and GN like '%00%" and TM between "2017-5-26 13:00:00.000" and "2017-05-27 14:00:00.000";查询历史数据,根据mode,GN和TM过滤
Archive历史表SELECT strftime("%Y/%m/%d %H时%M分%f秒", TM, "unixepoch", "localtime"),GN,TM,DS,ED,AV from Archive where id=51023 and TM BETWEEN "2024-03-28" AND "2024-04-30";查询历史数据并转换时间格式,根据GN和TM过滤
Archive历史表delete from Archive where GN="W3.GN001.POINT001" and TM between "2017-05-26 15:49:03" and "2017-05-27 00:00:03';删除历史数据,指定GN和时间段
Alarm报警表select ID,GN,TA,AV,TM from Alarm where PN like '%01';查询实时报警数据,根据PN过滤
Alarm报警表select * from Alarm where GN like "%02";查询实时报警数据,根据GN过滤
Alarm报警表select * from Alarm where GN like "%W5%";查询多库实时报警数据,根据GN过滤
AAlarm历史报警表select ID,GN,TV,AV,TM from AAlarm where PN like "%00%"and TM between "2017-05-27 09:30:00" and "2017-05-31 15:30:10";查询历史报警数据,根据PN和TM过滤
AAlarm历史报警表select * from AAlarm where GN like "%00%" and TM between "2017-05-27 09:30:00" and "2017-05-31 15:30:10";查询历史报警数据,根据GN和TM过滤
STAT统计表select * from Stat where GN like "%00%" and TM between "2017-05-30 09:30:00" and "2017-05-31 15:30:10";查询统计数据,根据GN和TM过滤
STAT统计表select ID,GN,MAXV,MAXTIME from Stat where QTYPE=3 and ID>2000 and TM between "2017-05-30 09:30:00" and "2017-05-31 15:30:10";查询统计数据,根据ID,QTYPE和TM过滤
STAT统计表select ID,GN,MAXV,MAXTIME from Stat where GN like "%00%" and TM between "2017-05-30 09:30:00" and "2017-05-31 15:30:10";查询指定字段统计数据,根据PN和TM过滤
User用户表select * from user查询用户表信息
User用户表insert into User values("test1@1.1.1.2",password("Magus123123"))插入用户test1,指定IP:1.1.1.2,密码:Magus123123
User用户表insert into User values("test2@%",password("test2test2"))插入用户test2,所有IP均可使用,密码:test2test2
User用户表update user set PW=PASSWORD("123456") where us="test1@1.1.1.2"更改密码
User用户表delete from user where us="test1@1.1.1.2"删除用户
Access权限表select * from access查询权限信息
Access权限表insert into access values("test@%","W3","select;update;insert;delete;create;drop;alter;grant;read,write;")插入test用户权限信息,支持W3库执行select;update;insert;delete;create;drop;alter;grant;read,write;
Access权限表insert into access values("test@%","global","all")插入test用户权限信息,支持全局权限
Access权限表UPDATE access set PL="select;update" WHERE us="test@%" and gp="W3"更新test用户W3库权限
Access权限表delete from access where us="test@%"删除test用户的各类权限
Groups资源表select * from groups查询资源组信息
Groups资源表select GN from Point where bit(SG,0)=1查询ID=0的安全组信息,安全组ID可通过select查询
Groups资源表insert into groups values("安全组9",3)插入安全组
Groups资源表delete from groups删除安全组
Groups资源表UPDATE Groups set gp="安全组999" WHERE id=8修改安全组名称
Groups资源表update Point set SG=bitset(SG,0) where GN="W3.A.B";增加测点到ID=0的安全组
Groups资源表update Point set SG=bitclr(SG,0) where GN="W3.A.B";清除ID=0的安全组测点