首先需要下载控件：node-red-contrib-mssql

首先要先了解MSSQL是Microsoft SQL Server的简称，他们其实是一样的，所以大家在node-red中下载控件的时候搜sqlserver是找不出来一样名字的节点的。

mssql节点的官方说明里可以说是什么都没说，所以我来记录一下我使用中用到的增、删、改、查，以及使用传参的形式实现数据的处理。

1、数据库配置的第一步

首先我们使用node-red与SQL Server建立通信是属于远程连接的，需要使用到账号、密码、1433端口的通信、SQLServer服务的启动，如果有报错可以参考我的文章【SQL server】数据库远程配置

2、SQLServer建立表

以我如上图实现的功能为例，在sutdent数据库中建立了stuTable，表内容参考如下
ID为键值，且自增.

3、MSSQL节点连接数据库

• Server：一般是电脑名称或者127.0.0.1
• Username：sa
• Password：在安装SQL server时设置的密码
• Database：设置的数据库名称，如果想用自己的数据库信息的话，这里是需要修改的哦

4、MSSQL语句使用

1、直接使用MSSQL语句

我们可以直接在MSSQL节点中输入SQL语句进行增、删、改、查

2、使用msg传参变量

其实我们也都知道，在右侧栏中可以查看节点的帮助说明，但这个节点他就坑人了啊，官方链接里没有说明就算了，侧边栏的帮助还是个错的。

当我们按照他提供的这个写法传参是，MSSQL节点就会报错，并给出"RequestError: Invalid column name 'z'."这种类型的错误提示。
真正传参的时候写法如下，是需要对参数引号的，将如下的语句写在MSSQL中即可。

但是举一反三之后发现只要是msg的参数，不管是payload的还是topic的或者其他自定义，都可以使用

3、使用function节点传SQL语句

将SQL语句封装在msg.payload中直接传递给MSSQL，而在MSSQL中不用编写任何语句。这样写的好处就是不仅msg变量可以使用，我们设定的全局变量也可以直接封装到MSSQL语句中

还可以通过判断函数来实现SQL语句的设定。

5、案例介绍：增

在form节点中将数据库中涉及到的需要添加的学生信息列举上，如下示例

想要调用输入的姓名，只需要调用msg.payload.stuname，所以我们在mssql节点中使用如下内容对学生数据进行新增

INSERT INTO 
stuTable(stuname,age, gender,brithdate,grade,class,enrollmentdate,state ) 
VALUES('{{{payload.stuname}}}','{{{payload.age}}}','{{{payload.gender}}}','{{{payload.brithdate}}}','{{{payload.grade}}}','{{{payload.class}}}','{{{payload.enrollmentdate}}}','{{{payload.state}}}')

6、案例介绍：查

输入学生姓名可查询所有同姓名的学生信息
使用text input节点设置为姓名的输入节点，并使用change节点将msg.payload信息转移到flow全局变量中（功能等同于 flow.set(“updatename”,msg.payload)）

• button（查询并修改学生信息）：按钮作为触点发送查询指令
• function（SQL语句查询学生信息）：因为mssql节点中不能使用全局变量，因此需要将全局变量写到msg.payload.stuname中；或者如下所示，将SQL语句和变量直接整合成一句进行查询，这种方式在MSSQL中是不需要编辑的。

msg.payload = "select * from stuTable where stuname='" + flow.get("updatename") +"' "
return msg;

• MSSQL：选择数据库的连接信息
• function（学生信息表格化）：etable表格的详细使用教程参考我的【Node-Red】一款可以自定义的table节点，在这里，我们需要将获取到的学生信息放在msg.payload中，并在columns中对参数做好一一对应，但同样的，在function中做好了设置，在etable中只需要选择显示的Tab界面即可。

msg = {payload:msg.payload,config:{options: { "movableColumns": true, "resizableColumns": true, "selectable": 5, "responsiveLayout": "collapse","autoResize": true, "layout": "fitColumns","pagination": "local", "height": "800px","groupBy": "system","groupStartOpen": true },columns: [{title: "序号", field: "ID", width: 70},{title: "姓名", field: "stuname", editor: "input" , width: 70},{title: "年龄", field: "age",  editor: "input", width: 70},{ title: "性别", field: "gender", width: 75, editor: "select", editorParams: { values: ["女", "男"] } },{ title: "出生日期", field: "brithdate", width: 120, sorter: "date", align: "center" },{ title: "年级", field: "grade", editor: "input" , width: 70},{ title: "班级", field: "class", editor: "input" , width: 70},{ title: "入学日期", field: "enrollmentdate", width: 120, sorter: "date", align: "center" },{ title: "就读状态", field: "state", width: 120, align: "center", formatter: "tickCross", sorter: "boolean", editor: true }]} 
}
return msg;

7、案例介绍：改

• MSSQL（修改个人信息）
  在UI界面选中行或修改行内容后会有选中行的数据输出，避免学生有重名，我们根据输出的ID和学生姓名修改即可。

UPDATE stuTable SET 
age='{{{payload.age}}}',
gender='{{{payload.gender}}}' ,
brithdate='{{{payload.brithdate}}}' ,
grade='{{{payload.grade}}}' ,
class='{{{payload.class}}}' ,
enrollmentdate='{{{payload.enrollmentdate}}}' ,
state='{{{payload.state}}}'   
WHERE stuname = '{{{payload.stuname}}}' AND ID='{{{payload.ID}}}'

8、案例介绍：删

输入学生姓名和学生的唯一ID可删除学生信息
使用text input节点设置为姓名和ID的输入节点，并使用change节点将msg.payload信息转移到flow全局变量中

• button（删除学生信息）：节点作为触点发送删除指令
• function（SQL语句删除学生信息）：将姓名和ID的全局变量与SQL语句进行整合

msg.payload = "DELETE FROM stutable WHERE stuname = '"+flow.get("updatename")+"' AND ID='"+flow.get("updateID")+"'"
return msg;

• MSSQL：选择数据库的连接信息

9、参考代码

[{"id":"c7aab3e635c61389","type":"tab","label":"流程 8","disabled":false,"info":"","env":[]},{"id":"d367d7d0b87b3461","type":"ui_etable","z":"c7aab3e635c61389","payload":"{}","payloadType":"json","options":"{}","group":"ca791f6a0ddafded","name":"","order":9,"width":"15","height":"4","columns":[],"outputs":1,"cts":false,"x":1030,"y":480,"wires":[["b8c760d4008c1dc1"]]},{"id":"65aa633d11484515","type":"function","z":"c7aab3e635c61389","name":"学生信息表格化","func":"msg = {\n    payload:msg.payload,\n    config:\n    {\n        options: { \n            \"movableColumns\": true, \n            \"resizableColumns\": true, \n            \"selectable\": 5, \n            \"responsiveLayout\": \"collapse\",\n            \"autoResize\": true, \n            \"layout\": \"fitColumns\",\n            \"pagination\": \"local\", \n            \"height\": \"800px\",\n            \"groupBy\": \"system\",\n            \"groupStartOpen\": true \n     },\n         columns: [\n            {title: \"序号\", field: \"ID\", width: 70},\n            {title: \"姓名\", field: \"stuname\", editor: \"input\" , width: 70},\n            {title: \"年龄\", field: \"age\",  editor: \"input\", width: 70},\n            { title: \"性别\", field: \"gender\", width: 75, editor: \"select\", editorParams: { values: [\"女\", \"男\"] } },\n            { title: \"出生日期\", field: \"brithdate\", width: 120, sorter: \"date\", align: \"center\" },\n            { title: \"年级\", field: \"grade\", editor: \"input\" , width: 70},\n            { title: \"班级\", field: \"class\", editor: \"input\" , width: 70},\n            { title: \"入学日期\", field: \"enrollmentdate\", width: 120, sorter: \"date\", align: \"center\" },\n            { title: \"就读状态\", field: \"state\", width: 120, align: \"center\", formatter: \"tickCross\", sorter: \"boolean\", editor: true }\n \n        ]\n    } \n}\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":880,"y":480,"wires":[["d367d7d0b87b3461"]]},{"id":"3aa4e0691ee10230","type":"MSSQL","z":"c7aab3e635c61389","mssqlCN":"20f8d3d0cad2a879","name":"","query":"","outField":"payload","x":720,"y":480,"wires":[["65aa633d11484515"]]},{"id":"b8c760d4008c1dc1","type":"MSSQL","z":"c7aab3e635c61389","mssqlCN":"20f8d3d0cad2a879","name":"修改个人信息","query":"UPDATE stuTable SET \nage='{{{payload.age}}}',\ngender='{{{payload.gender}}}' ,\nbrithdate='{{{payload.brithdate}}}' ,\ngrade='{{{payload.grade}}}' ,\nclass='{{{payload.class}}}' ,\nenrollmentdate='{{{payload.enrollmentdate}}}' ,\nstate='{{{payload.state}}}'   \nWHERE stuname = '{{{payload.stuname}}}' AND ID='{{{payload.ID}}}'","outField":"payload","x":1180,"y":480,"wires":[[]]},{"id":"137ac717d2a3595e","type":"ui_button","z":"c7aab3e635c61389","name":"","group":"ca791f6a0ddafded","order":5,"width":0,"height":0,"passthru":false,"label":"查询并修改学生信息","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":320,"y":480,"wires":[["c1e2aa6f4d383564"]]},{"id":"37a204197bcfebb5","type":"ui_button","z":"c7aab3e635c61389","name":"","group":"ca791f6a0ddafded","order":7,"width":0,"height":0,"passthru":false,"label":"删除学生信息","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":460,"y":660,"wires":[["d34afc2f92e64c11"]]},{"id":"0eceaf108f4790e8","type":"ui_text_input","z":"c7aab3e635c61389","name":"","label":"姓名：","tooltip":"","group":"ca791f6a0ddafded","order":2,"width":0,"height":0,"passthru":true,"mode":"text","delay":300,"topic":"topic","sendOnBlur":true,"className":"","topicType":"msg","x":430,"y":620,"wires":[["1521cdb031c5ea28"]]},{"id":"1521cdb031c5ea28","type":"change","z":"c7aab3e635c61389","name":"","rules":[{"t":"move","p":"payload","pt":"msg","to":"updatename","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":610,"y":620,"wires":[[]]},{"id":"131a69e05dd3eecd","type":"MSSQL","z":"c7aab3e635c61389","mssqlCN":"20f8d3d0cad2a879","name":"","query":"","outField":"payload","x":860,"y":660,"wires":[[]]},{"id":"80c1a0cc7818d9e8","type":"ui_text_input","z":"c7aab3e635c61389","name":"","label":"学号：","tooltip":"","group":"ca791f6a0ddafded","order":1,"width":0,"height":0,"passthru":true,"mode":"text","delay":300,"topic":"topic","sendOnBlur":true,"className":"","topicType":"msg","x":430,"y":580,"wires":[["43b0afed19409a8d"]]},{"id":"43b0afed19409a8d","type":"change","z":"c7aab3e635c61389","name":"","rules":[{"t":"move","p":"payload","pt":"msg","to":"updateID","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":610,"y":580,"wires":[[]]},{"id":"d34afc2f92e64c11","type":"function","z":"c7aab3e635c61389","name":"SQL语句删除学生信息","func":"msg.payload = \"DELETE FROM stutable WHERE stuname = '\"+flow.get(\"updatename\")+\"' AND ID='\"+flow.get(\"updateID\")+\"'\"\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":680,"y":660,"wires":[["131a69e05dd3eecd"]]},{"id":"c1e2aa6f4d383564","type":"function","z":"c7aab3e635c61389","name":"SQL语句查询学生信息","func":"msg.payload = \"select * from stuTable where stuname='\" + flow.get(\"updatename\") +\"' \"\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":540,"y":480,"wires":[["3aa4e0691ee10230"]]},{"id":"a661c42cc481b9aa","type":"MSSQL","z":"c7aab3e635c61389","mssqlCN":"20f8d3d0cad2a879","name":"","query":"INSERT INTO \nstuTable(stuname,age, gender,brithdate,grade,class,enrollmentdate,state ) \nVALUES('{{{payload.stuname}}}','{{{payload.age}}}','{{{payload.gender}}}','{{{payload.brithdate}}}','{{{payload.grade}}}','{{{payload.class}}}','{{{payload.enrollmentdate}}}','{{{payload.state}}}')\n\n","outField":"payload","x":520,"y":400,"wires":[[]]},{"id":"f3010ba39de064ad","type":"ui_form","z":"c7aab3e635c61389","name":"添加学生信息","label":"","group":"5c1beac1ae5c7436","order":2,"width":0,"height":0,"options":[{"label":"姓名","value":"stuname","type":"text","required":true,"rows":null},{"label":"年龄","value":"age","type":"text","required":false,"rows":null},{"label":"性别","value":"gender","type":"text","required":false,"rows":null},{"label":"出生日期","value":"brithdate","type":"date","required":false,"rows":null},{"label":"年级","value":"grade","type":"text","required":false,"rows":null},{"label":"班级","value":"class","type":"text","required":false,"rows":null},{"label":"入学时间","value":"enrollmentdate","type":"date","required":false,"rows":null},{"label":"是否在读","value":"state","type":"checkbox","required":false,"rows":null}],"formValue":{"stuname":"","age":"","gender":"","brithdate":"","grade":"","class":"","enrollmentdate":"","state":false},"payload":"","submit":"录入信息","cancel":"","topic":"topic","topicType":"msg","splitLayout":"","className":"","x":320,"y":400,"wires":[["a661c42cc481b9aa"]]},{"id":"ca791f6a0ddafded","type":"ui_group","name":"修改学生信息界面","tab":"f7382c37c20c9e08","order":7,"disp":true,"width":"15","collapse":false,"className":""},{"id":"20f8d3d0cad2a879","type":"MSSQL-CN","name":"127.0.0.1","server":"127.0.0.1","encyption":false,"database":"student"},{"id":"5c1beac1ae5c7436","type":"ui_group","name":"录入学生信息","tab":"f7382c37c20c9e08","order":6,"disp":true,"width":"6","collapse":false,"className":""},{"id":"f7382c37c20c9e08","type":"ui_tab","name":"学校登录系统","icon":"dashboard","order":1,"disabled":false,"hidden":false}]