sqlite3操作

sqlite3的使用： 

1. 创建数据库
创建数据库 sqlite3 test.db
打开一个已经存在的数据库   sqlite3 test.db 
查询 db文件是否存在  .databases

2. 创建表
create table if not exists plcdata (OrderNum integer primary key autoincrement,Data text)

获取表的完整信息
.schema tablename

3. 删除表  drop table plcdata

4. 建立表

例子1 ：
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

例子2：

insert into plcdata (OrderNum,Data)values(1,'one');
insert into plcdata (Data)values('two');
insert into plcdata (Data)values('three');

查询表是否存在 .tables
查询表结构： .schema plcdata     

5. 查询数据

select * from plcdata ;
select value1 value2 from plcdata;
例子：
select OrderNum from plcdata;
select Data from plcdata;

select OrderNum,Data from plcdata;

6. 条件查询：
SELECT * FROM COMPANY WHERE SALARY > 50000;

SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000; 
SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );

SELECT * FROM COMPANY WHERE AGE  LIKE '2%';  模糊匹配

删除 ：
 DELETE FROM COMPANY WHERE ID = 7;

3.退出  .quit

sqlite服务器程序的编写，基于ubusd和invoke通讯机制，单一进程中处理所有的sqlite请求，ubus call或应用进程中发起请求：
create table if not exists plcdata (OrderNum integer primary key autoincrement,Data text);

ubus call sqlite execute '{"execute": "create table if not exists plcdata (OrderNum integer primary key autoincrement,Data text)"}'  #建表
ubus call sqlite execute '{"execute": "drop table plcdata"}'    #删表
ubus call sqlite execute '{"execute": "insert into plcdata (Data)values(\"one\")"}'    #增
ubus call sqlite commit '{"commit": "insert into plcdata (Data)values(\"one\");insert into plcdata (Data)values(\"two\");insert into plcdata (Data)values(\"three\");insert into plcdata (Data)values(\"four\");insert into plcdata (Data)values(\"five\");insert into plcdata (Data)values(\"six\")"}'    #事务提交
ubus call sqlite query '{"query": "select * from plcdata"}'    #查
ubus call sqlite execute '{"execute": "update plcdata set Data = \"Texas\" where OrderNum = 16"}'    #改
ubus call sqlite execute '{"execute": "delete from plcdata where OrderNum > 6"}'    #删

测试 ：
1.环境运行
 执行 ubusd &
 执行 ./sqlite_server &

1. 建表

ubus call sqlite execute '{"execute": "create table if not exists plcdata (OrderNum integer primary key autoincrement,Data text)"}'  #建表

ubus call sqlite commit '{"commit": "insert into plcdata (Data)values(\"one\");insert into plcdata (Data)values(\"two\");insert into plcdata (Data)values(\"three\");insert into plcdata (Data)values(\"four\");insert into plcdata (Data)values(\"five\");insert into plcdata (Data)values(\"six\")"}' 

ubus call sqlite query '{"query": "select * from plcdata"}'    #查

2.

sql语句查询 “OrderNum最大” 的数据记录
SELECT Identifier,DevProID,DevID,DataType,Addr,Data,Length FROM table_name WHERE OrderNum=(select max(OrderNum) from table_name);
sql语句查询 “OrderNum最大 并且 STlevel==1” 的数据记录
SELECT Identifier,DevProID,DevID,DataType,Addr,Data,Length FROM table_name WHERE OrderNum=(select max(OrderNum) from table_name) AND STlevel==1;
sql语句查询 “OrderNum最大 并且 STlevel==2” 的数据记录
SELECT Identifier,DevProID,DevID,DataType,Addr,Data,Length FROM table_name WHERE OrderNum=(select max(OrderNum) from table_name) AND STlevel==2;
sql语句查询 “OrderNum最大 并且 STlevel==3” 的数据记录
SELECT Identifier,DevProID,DevID,DataType,Addr,Data,Length FROM table_name WHERE OrderNum=(select max(OrderNum) from table_name) AND STlevel==3;
sql语句查询 “OrderNum最大 并且 STlevel==4” 的数据记录
SELECT Identifier,DevProID,DevID,DataType,Addr,Data,Length FROM table_name WHERE OrderNum=(select max(OrderNum) from table_name) AND STlevel==4;
sql语句查询 “OrderNum最大 并且 STlevel==5” 的数据记录
SELECT Identifier,DevProID,DevID,DataType,Addr,Data,Length FROM table_name WHERE OrderNum=(select max(OrderNum) from table_name) AND STlevel==5;

ubus call sqlite query '{"query": "select * from plcdata"}'
ubus call sqlite query '{"query": "SELECT Identifier,DevProID,DevID,DataType,Addr,Data,Length FROM t20200311_004_ETH1_s7comm WHERE OrderNum=(select max(OrderNum) from t20200311_004_ETH1_s7comm)"}'
ubus call sqlite query '{"query": "SELECT Identifier,DevProID,DevID,DataType,Addr,Data,Length FROM t20200311_004_ETH1_s7comm WHERE OrderNum=(select max(OrderNum) from t20200311_004_ETH1_s7comm) AND Level==4"}'

主要应用：

SELECT * FROM table_name WHERE OrderNum=(select max(OrderNum) from table_name);

ubus call sqlite query '{"query": "select * from t20200311_004_ETH1_s7comm where OrderNum=(select max(OrderNum) from t20200311_004_ETH1_s7comm)"}';    ok

ubus call sqlite query '{"query": "select * from t20200312_010_ETH1_s7comm where OrderNum=(select max(OrderNum) from t20200312_010_ETH1_s7comm)"}';    ok

ubus call sqlite query '{"query": "select * from t1_ETH0_s7comm where OrderNum=(select max(OrderNum) from t1_ETH0_s7comm)"}';

ubus call sqlite query '{"query": "select * from  WELDER_Set_Parameters"}'

ubus call sqlite query '{"query": "select * from t20200311_001_ETH0_s7comm"}'; 

删除
ubus call sqlite execute '{"execute": "drop table weld_data"}'  

ubus call sqlite execute '{"execute": "drop table WELDER_Set_Parameters"}'  

查询焊机 保存的指令数据
ubus call sqlite query '{"query": "select * from WELDER_Set_Parameters where WelderOrderNum=(select max(WelderOrderNum) from WELDER_Set_Parameters)"}';

查询 
ubus call sqlite query '{"query": "select * from  weld_data"}'

ubus call sqlite query '{"query": "select * from  WELDER_Set_Parameters"}'

select?*?from?users?order?by?id?limit?10?offset?0

查询最近的两条数据

ubus call sqlite query '{"query": "select * from WELDER_Set_Parameters order by WelderOrderNum DESC limit 2"}';

            {
                "Identifier": "totle_boot_time",
                "Type": "int",
                "name": "开机总时间"
            },
            {
                "Identifier": "total_standy_time",
                "Type": "int",
                "name": "待机总时间"
            },

例子1
建表 ：
create table if not exists tewm_data (Num integer primary key autoincrement,WelderDataNum integer,Identifier text,DataType text,Data text,[CreatedTime] TimeStamp NOT NULL DEFAULT (datetime('now','localtime')));
      
插表 ：
insert into tewm_data (WelderDataNum,Identifier,DataType,Data)values(1,"node_name","string","ewm_001");
insert into tewm_data (WelderDataNum,Identifier,DataType,Data)values(2,"totle_boot_time",'int',1000);
insert into tewm_data (WelderDataNum,Identifier,DataType,Data)values(3,"total_standy_time",'int',2000);
    
查表
select WelderDataNum from tewm_data where WelderDataNum=(select max(WelderDataNum) from tewm_data) limit 0,1;

建表2 ：
create table if not exists %s (Num integer primary key autoincrement,\
      WelderDataNum integer,FrameNumber integer,Identifier text,DataType text,Data text,\
      [CreatedTime] TimeStamp NOT NULL DEFAULT (datetime('now','localtime')));