alex9ufo 聰明人求知心切: SQLITE Database範例

SQLITE Database範例

DB Browser for SQLite 下載位置 https://sqlitebrowser.org/dl/

DB Browser 建立資料庫的方法

資料位置 : C:\Users\User\.node-red\LED_STATUS.db 若是路徑C:\Users\User\.node-red不對請修正

利用Node-Red 建立資料庫

CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id));

Node-Red 程式暨畫面

資料位置 : C:\Users\User\.node-red\LED_STATUS.db 若是路徑C:\Users\User\.node-red不對請修正

命令	SQLite語法
CREATE	CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id));
INSERT	INSERT INTO LEDSTATUS (STATUS , Date , Time )values("on", "11/01" , "21:05")
SELECT	SELECT * FROM LEDSTATUS
DELETE	DELETE from LEDSTATUS
DROP TABLE	DROP TABLE LEDSTATUS
Function
CREATE　DATABASE	//CREATE TABLE LEDSTATUS ( //id INTEGER, //STATUS TEXT, //Date DATE, //Time TIME, //PRIMARY KEY (id) //); //CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id)); msg.topic = "CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id))"; return msg;
INSERT	var Today = new Date(); var yyyy = Today.getFullYear(); //年 var MM = Today.getMonth()+1; //月 var dd = Today.getDate(); //日 var h = Today.getHours(); //時 var m = Today.getMinutes(); //分 var s = Today.getSeconds(); //秒 if(MM<10) { MM = '0'+MM; } if(dd<10) { dd = '0'+dd; } if(h<10) { h = '0'+h; } if(m<10) { m = '0' + m; } if(s<10) { s = '0' + s; } var var_date = yyyy+'/'+MM+'/'+dd; var var_time = h+':'+m+':'+s; var myLED = msg.payload; msg.topic = "INSERT INTO LEDSTATUS ( STATUS , Date , Time ) VALUES ($myLED, $var_date , $var_time ) " ; msg.payload = [myLED, var_date , var_time ] return msg; //INSERT INTO LEDSTATUS ( //id INTEGER, //STATUS TEXT, //Date DATE, //Time TIME, //PRIMARY KEY (id) //);
檢視資料	//INSERT INTO LEDSTATUS ( //id INTEGER, //STATUS TEXT, //Date DATE, //Time TIME, //PRIMARY KEY (id) //); //SELECT * FROM LEDSTATUS ORDER BY id DESC LIMIT 50; msg.topic = "SELECT * FROM LEDSTATUS ORDER BY id DESC LIMIT 50"; return msg;
DELETE ALL DATA	//CREATE TABLE LEDSTATUS ( //id INTEGER, //STATUS TEXT, //Date DATE, //Time TIME, //PRIMARY KEY (id) //); //CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id)); msg.topic = "DELETE from LEDSTATUS"; return msg;
function 84 function 85	var topic=msg.payload; if (topic==""){ return [msg,null]; } if (topic=="Cancel"){ return [null,msg]; } return msg;
DROP　DATABASE	//CREATE TABLE LEDSTATUS ( //id INTEGER, //STATUS TEXT, //Date DATE, //Time TIME, //PRIMARY KEY (id) //); //CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id)); msg.topic = "DROP TABLE LEDSTATUS"; return msg;
查詢一筆資料	// var id = msg.payload.id; msg.topic=""; let temp ="SELECT * FROM LEDSTATUS"; temp=temp+" WHERE id LIKE '"+ id +"'"; msg.topic=temp; return msg; //SELECT * FROM COMPANY WHERE AGE LIKE 'XXX%'; //INSERT INTO LEDSTATUS ( //id INTEGER, //STATUS TEXT, //Date DATE, //Time TIME, //PRIMARY KEY (id) //); //SELECT * FROM LEDSTATUS ORDER BY id DESC LIMIT 50;
刪除一筆資料	// var id = msg.payload.id; msg.topic=""; let temp ="DELETE FROM LEDSTATUS"; temp=temp+" WHERE id LIKE '"+ id +"'"; msg.topic=temp; return msg; //SELECT * FROM COMPANY WHERE AGE LIKE 'XXX%'; //INSERT INTO LEDSTATUS ( //id INTEGER, //STATUS TEXT, //Date DATE, //Time TIME, //PRIMARY KEY (id) //); //DELETE FROM COMPANY WHERE ID = 7;

資料位置 : C:\Users\User\.node-red\LED_STATUS.db

alex9ufo 聰明人求知心切

2023年11月3日星期五

SQLITE Database範例

沒有留言:

張貼留言

2024_09 作業3 以Node-Red 為主

2023年11月3日 星期五

SQLITE Database範例

沒有留言:

張貼留言

2024_09 作業3 以Node-Red 為主

2023年11月3日星期五