帮助中心Appearance
SQL使用说明
1.注意
1.select请带入where条件
2.历史必须带入时间段和测点信息
3.update更新表仅更新现有字段，例如更新realtime表，指定AV字段，则仅更新AV
2.表操作支持
1）V4.0.23及之前和V5.0.3及之前版本：表支持情况
| 表 | 描述 | SELECT | INSERT | UPDATE | DELETE | 过滤条件 | 分页选项 | 排序选项 |
|---|---|---|---|---|---|---|---|---|
| DATABASE | 数据库表 | √ | √ | √ | √ | √ | √ | √ |
| NODE | 节点表 | √ | √ | √ | √ | √ | √ | √ |
| POINT | 点表 | √ | √ | √ | √ | √ | √ | √ |
| REALTIME | 实时数据表 | √ | √ | √ | × | √ | √ | √ |
| ARCHIVE | 历史数据表 | √ | √ | √ | √ | √ | √ | √ |
| STAT | 历史统计表 | √ | × | × | × | √ | √ | √ |
| ALARM | 实时报警表 | √ | × | × | × | √ | √ | √ |
| AALARM | 历史报警表 | √ | × | × | × | √ | √ | √ |
1）V4.0.24和V5.0.4及之后版本：表支持情况
| 表 | 描述 | SELECT | INSERT | UPDATE | DELETE | 过滤条件 | 分页选项 | 排序选项 |
|---|---|---|---|---|---|---|---|---|
| DATABASE | 数据库表 | √ | √ | √ | √ | √ | √ | √ |
| NODE | 点表 | √ | √ | √ | √ | √ | √ | √ |
| POINT | 节点表 | √ | √ | √ | √ | √ | √ | √ |
| REALTIME | 实时数据表 | √ | √ | √ | × | √ | √ | √ |
| ARCHIVE | 历史数据表 | √ | √ | √ | √ | √ | √ | √ |
| STAT | 历史统计表 | √ | × | × | × | √ | √ | √ |
| ALARM | 实时报警表 | √ | × | × | × | √ | √ | √ |
| AALARM | 历史报警表 | √ | × | × | × | √ | √ | √ |
| User | 用户表 | √ | √ | √ | √ | √ | √ | √ |
| Groups | 安全组表 | √ | √ | √ | √ | √ | √ | √ |
| Access | 权限表 | √ | √ | √ | √ | √ | √ | √ |
3.常用参数解释
| 字段 | 类型 | 说明 |
|---|---|---|
| PN | char(32) | 名称，支持数字、字母和特殊字符:#@-_ |
| MODE | hidden text | raw：原始值(加开始结束时间)、arch:原始值、span：等间距、plot：绘图值、 flow：流量值、 max：最大值、min：最小值、 avg：面积平均值、 mean：算术平均值、 stdev：标准方差、sum：算术和 |
| INTERVAL | hidden text | 时间间隔：h,m,s |
| QTYPE | hidden tinyint | 质量过滤：0-不过滤，1-去除坏点, 2-去除超时，3-去除1/2 |
注：raw：原始值(加开始结束时间)：如果开始和结束时间无原始值保存，数据库会还原出一个结果。arch:原始值，查询结果根据真实原始值返回。
4.常用语法示例
| 语句 | 语句示例 | 应用场景 | 功能点 | 备注 |
|---|---|---|---|---|
| select | select PN from Point where ID in (1024,1025,1028) | 常见字段查询 | in | |
| select | select PN from Point where ID NOT in (1024,1025,1028) | 常见字段查询 | Not in | |
| select | select PN from Point where ID < 1100 | 常见字段查询 | 比较操作符[=, !=, >, <, >=, <=] | |
| select | select ID from Point where PN regexp "POINT\d*" | 常见字段查询 | 正则匹配操作 | 匹配点名形如"POINT123"的点 |
| select | select ID from Point where PN like "_INT%" | 常见字段查询 | like匹配，通配符_, % | 匹配形为"XINT001"的点 |
| select | select ID from Point where PN NOT like "%POINT%" | 常见字段查询 | NOT like | 匹配PN中不含有POINT的点 |
| select | select PN from Point where ID between 1024 and 1099 | 常见字段查询 | between and | between and 只用于数值类型和date |
| select | select (DS & 0x0f << 8) AS XX from Realtime where ID = 1024 | 移位操作获取某值 | 位操作符[与,或,非, <<, >>] | |
| select | select （AV + 100） AS value from Realtime where ID = 1025 | 算术表达式操作 | 算术表达式[+,-,*,/] | |
| select | select AV as XX from Realtime where ID = 1024 | 查询结果取别名 | as别名 | |
| select | select PN from Point where ID in (1025, 1024, 1026) order by ID | 结果排序 | order by | |
| select | select PN from Point where ID > 1030 limit 0,20 | 查询结果分页 | limit | |
| select | select count(ID) from Point where ID > 1099 | 查询记录数 | 函数count() | |
| select | select 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 |
| select | select PN,AV,DS from Realtime where ID in (1024,1025,1028) and ds=0 | 虚拟状态字段查询 | 模拟状态字段清单 | |
| select | select PN,AV,TV,ED from Alarm where ID in (1024,1025,1028) | 静态字段虚拟字段 | 非静态数据表内，可以查看静态测点字段 | |
| select | select ID,GN,AV from Realtime where TM=strftime("%s","2017-06-27 10:49:03","utc") | 查询指定时刻实时值 | ||
| select | 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" | 查询历史数据并转换格式 |
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 ""; | 查询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的安全组测点 |