Node-RED中连接sqlserver数据库并实现增删改查的操作

KK20230320

已于 2023-10-24 11:49:12 修改

阅读量1.5k

点赞数

文章标签：数据库 node.js sqlserver 1024程序员节

于 2023-10-18 11:56:01 首次发布

本文链接：https://blog.csdn.net/qq_39745055/article/details/133902100

版权

Node-RED中连接sqlserver数据库并实现增删改查的操作

1、打开node-red 网页界面

2、安装NodeRED节点

节点名称：node-red-contrib-mssql

3 创建表

设计流程图

①inject 节点，只需要拖拽进页面即可，不需要做内容修改

② MSSQL 节点，输入信息，编辑数据库连接；输出创表语句

创表语句：

CREATE TABLE [user2] (id VARCHAR(128) NOT NULL,name VARCHAR(128) DEFAULT NULL,pass VARCHAR(128) DEFAULT NULL,create_time DATETIME DEFAULT GETDATE(),update_time DATETIME DEFAULT GETDATE(),PRIMARY KEY (id));

③ debug 节点，只需要拖拽进页面即可，不需要做内容修改

部署后，点击按钮执行创表操作

4 插入数据

设计流程图

inject 节点和debug节点操作，MSSQL 节点编辑连接操作和创表流程步骤相同，就不重复说明了

②MSSQL 节点，输入插入数据语句

创表语句：

INSERT INTO [user2]  (id, name, pass)
VALUES ('2', 'luvk', '30');

5 更新语句

更新语句：

UPDATE [user2]
SET name = 'cc'
WHERE id = 1;

6 查看语句

查看语句：

select * from [user];

7 查看语句

删除语句：

DELETE FROM [user2]
WHERE id = 2;

总结：全流程json数据

[
{
"id": "0050d3b64520efc0",
"type": "tab",
"label": "sqlserver",
"disabled": false,
"info": "",
"env": []
},
{
"id": "50444b205365b7b1",
"type": "inject",
"z": "0050d3b64520efc0",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 430,
"y": 160,
"wires": [
[
"05980f64a43546e3"
]
]
},
{
"id": "205e1dc78be2b277",
"type": "debug",
"z": "0050d3b64520efc0",
"name": "debug 124",
"active": true,
"tosidebar": true,
"console": true,
"tostatus": true,
"complete": "payload",
"targetType": "msg",
"statusVal": "payload",
"statusType": "auto",
"x": 840,
"y": 160,
"wires": []
},
{
"id": "0623f8dda03d7cd7",
"type": "comment",
"z": "0050d3b64520efc0",
"name": "sqlserver 创建表",
"info": "",
"x": 160,
"y": 160,
"wires": []
},
{
"id": "05980f64a43546e3",
"type": "MSSQL",
"z": "0050d3b64520efc0",
"mssqlCN": "49a8339514344c57",
"name": "",
"query": "CREATE TABLE [user2] (id VARCHAR(128) NOT NULL,name VARCHAR(128) DEFAULT NULL,pass VARCHAR(128) DEFAULT NULL,create_time DATETIME DEFAULT GETDATE(),update_time DATETIME DEFAULT GETDATE(),PRIMARY KEY (id));",
"outField": "payload",
"x": 640,
"y": 160,
"wires": [
[
"205e1dc78be2b277"
]
]
},
{
"id": "90aef4487e33db0f",
"type": "comment",
"z": "0050d3b64520efc0",
"name": "sqlserver 插入值（固定值）",
"info": "",
"x": 200,
"y": 460,
"wires": []
},
{
"id": "a63859c171ea7853",
"type": "inject",
"z": "0050d3b64520efc0",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 430,
"y": 460,
"wires": [
[
"f2388da6cc16b183"
]
]
},
{
"id": "7bd4974d9b1b2336",
"type": "debug",
"z": "0050d3b64520efc0",
"name": "debug 125",
"active": true,
"tosidebar": true,
"console": true,
"tostatus": true,
"complete": "payload",
"targetType": "msg",
"statusVal": "payload",
"statusType": "auto",
"x": 840,
"y": 460,
"wires": []
},
{
"id": "f2388da6cc16b183",
"type": "MSSQL",
"z": "0050d3b64520efc0",
"mssqlCN": "49a8339514344c57",
"name": "",
"query": "INSERT INTO [user2] (id, name, pass)\nVALUES ('2', 'luvk', '30');",
"outField": "payload",
"x": 640,
"y": 460,
"wires": [
[
"7bd4974d9b1b2336"
]
]
},
{
"id": "82c0cecdbe6cb93d",
"type": "comment",
"z": "0050d3b64520efc0",
"name": "sqlserver 更新语句",
"info": "",
"x": 170,
"y": 580,
"wires": []
},
{
"id": "7ea630879fe10a7c",
"type": "inject",
"z": "0050d3b64520efc0",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 430,
"y": 580,
"wires": [
[
"721c3549f471cd91"
]
]
},
{
"id": "8549f456d149478e",
"type": "debug",
"z": "0050d3b64520efc0",
"name": "debug 126",
"active": true,
"tosidebar": true,
"console": true,
"tostatus": true,
"complete": "payload",
"targetType": "msg",
"statusVal": "payload",
"statusType": "auto",
"x": 840,
"y": 580,
"wires": []
},
{
"id": "721c3549f471cd91",
"type": "MSSQL",
"z": "0050d3b64520efc0",
"mssqlCN": "49a8339514344c57",
"name": "",
"query": "UPDATE [user2]\nSET name = 'cc'\nWHERE id = 1;",
"outField": "payload",
"x": 640,
"y": 580,
"wires": [
[
"8549f456d149478e"
]
]
},
{
"id": "247d024ec99b6548",
"type": "inject",
"z": "0050d3b64520efc0",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 430,
"y": 260,
"wires": [
[
"3d8078187ea32c21"
]
]
},
{
"id": "d93cc43727388ca4",
"type": "debug",
"z": "0050d3b64520efc0",
"name": "debug 127",
"active": true,
"tosidebar": true,
"console": true,
"tostatus": true,
"complete": "payload",
"targetType": "msg",
"statusVal": "payload",
"statusType": "auto",
"x": 840,
"y": 260,
"wires": []
},
{
"id": "93265186c022908e",
"type": "comment",
"z": "0050d3b64520efc0",
"name": "sqlserver 创建触发器create_time",
"info": "",
"x": 210,
"y": 260,
"wires": []
},
{
"id": "3d8078187ea32c21",
"type": "MSSQL",
"z": "0050d3b64520efc0",
"mssqlCN": "49a8339514344c57",
"name": "",
"query": "-- 创建触发器\nCREATE TRIGGER [user2_after_insert_trigger]\nON [user2]\nAFTER INSERT\nAS\nBEGIN\n UPDATE [user2]\n SET create_time = GETDATE()\n WHERE id IN (SELECT id FROM inserted);\nEND;\n\n",
"outField": "payload",
"x": 640,
"y": 260,
"wires": [
[
"d93cc43727388ca4"
]
]
},
{
"id": "afffde3e03143b47",
"type": "inject",
"z": "0050d3b64520efc0",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 430,
"y": 360,
"wires": [
[
"fd2b020e28f720fc"
]
]
},
{
"id": "432d297b7b56b66b",
"type": "debug",
"z": "0050d3b64520efc0",
"name": "debug 128",
"active": true,
"tosidebar": true,
"console": true,
"tostatus": true,
"complete": "payload",
"targetType": "msg",
"statusVal": "payload",
"statusType": "auto",
"x": 840,
"y": 360,
"wires": []
},
{
"id": "da86016c2ecaeabe",
"type": "comment",
"z": "0050d3b64520efc0",
"name": "sqlserver 创建触发器update_time",
"info": "",
"x": 210,
"y": 360,
"wires": []
},
{
"id": "fd2b020e28f720fc",
"type": "MSSQL",
"z": "0050d3b64520efc0",
"mssqlCN": "49a8339514344c57",
"name": "",
"query": "CREATE TRIGGER [user2_after_update_trigger]\nON [user2]\nAFTER UPDATE\nAS\nBEGIN\n UPDATE [user2]\n SET update_time = GETDATE()\n WHERE id IN (SELECT id FROM inserted);\nEND;\n\n",
"outField": "payload",
"x": 640,
"y": 360,
"wires": [
[
"432d297b7b56b66b"
]
]
},
{
"id": "b85037e9a7c7adeb",
"type": "comment",
"z": "0050d3b64520efc0",
"name": "sqlserver 查看语句",
"info": "",
"x": 170,
"y": 680,
"wires": []
},
{
"id": "e8137588ad614ddb",
"type": "inject",
"z": "0050d3b64520efc0",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 430,
"y": 680,
"wires": [
[
"5e12a9e7cbe29d1c"
]
]
},
{
"id": "8f81bce657ac3e85",
"type": "debug",
"z": "0050d3b64520efc0",
"name": "debug 129",
"active": true,
"tosidebar": true,
"console": true,
"tostatus": true,
"complete": "payload",
"targetType": "msg",
"statusVal": "payload",
"statusType": "auto",
"x": 830,
"y": 680,
"wires": []
},
{
"id": "5e12a9e7cbe29d1c",
"type": "MSSQL",
"z": "0050d3b64520efc0",
"mssqlCN": "49a8339514344c57",
"name": "",
"query": "select * from [user];",
"outField": "payload",
"x": 640,
"y": 680,
"wires": [
[
"8f81bce657ac3e85"
]
]
},
{
"id": "c45827576367c305",
"type": "comment",
"z": "0050d3b64520efc0",
"name": "sqlserver 删除语句",
"info": "",
"x": 170,
"y": 780,
"wires": []
},
{
"id": "e50bd5706ed4e109",
"type": "inject",
"z": "0050d3b64520efc0",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 430,
"y": 780,
"wires": [
[
"db6f27bb68271e21"
]
]
},
{
"id": "6ab8984efdf4fe38",
"type": "debug",
"z": "0050d3b64520efc0",
"name": "debug 130",
"active": true,
"tosidebar": true,
"console": true,
"tostatus": true,
"complete": "payload",
"targetType": "msg",
"statusVal": "payload",
"statusType": "auto",
"x": 830,
"y": 780,
"wires": []
},
{
"id": "db6f27bb68271e21",
"type": "MSSQL",
"z": "0050d3b64520efc0",
"mssqlCN": "49a8339514344c57",
"name": "",
"query": "DELETE FROM [user2]\nWHERE id = 2;\n",
"outField": "payload",
"x": 640,
"y": 780,
"wires": [
[
"6ab8984efdf4fe38"
]
]
},
{
"id": "49a8339514344c57",
"type": "MSSQL-CN",
"name": "",
"server": "127.0.0.1",
"encyption": true,
"database": "mssql_jk"
}
]