node配置mysql模块
步骤
- 安装第三方mysql模块
- 建立连接到mysql数据库
- 执行SQL语句操作数据库
在终端安装mysql模块
建立连接
1 2 3 4 5 6 7 8 9 10
| const mysql = require("mysql");
const ab = mysql.createPool({ host: "localhost", user: "root", password: "Q123123123", database: "demo", })
|
测试是否正常工作
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
| const mysql = require("mysql");
const ab = mysql.createPool({ host: "localhost", user: "root", password: "Q123123123", database: "demo", })
ab.query("select 1 ",(err,results)=>{ if(err){ return console.log(err.message); } console.log(results); })
|
查询数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
| const mysql = require("mysql");
const ab = mysql.createPool({ host: "localhost", user: "root", password: "Q123123123", database: "demo", })
const all ="select * from demo.demo3" ; ab.query(all,(err,results)=>{ if(err){ return console.log(err.message); } console.log(results); })
|
插入数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
| const mysql = require("mysql");
const ab = mysql.createPool({ host: "localhost", user: "root", password: "Q123123123", database: "demo", })
const demo = { 目标:"经济学", 行动:"学习和实践", }
const sqlstr = "insert into demo.demo3 (目标,行动) values (?,?)";
ab.query(sqlstr,[demo.目标,demo.行动],(err,results)=>{ if(err){ return console.log(err.message); } if(results.affectedRows > 0 ){ console.log("插入成功"); } })
|
便捷的方式
向表中新增数据时,如果数据对象的每个属性和数据表的字段一一对应,则可以通过如下方式快速插入数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
| const mysql = require("mysql");
const ab = mysql.createPool({ host: "localhost", user: "root", password: "Q123123123", database: "demo", })
const demo = { 目标:"经济学", 行动:"学习和实践", }
const sqlstr = "insert into demo.demo3 set ?";
ab.query(sqlstr,demo,(err,results)=>{ if(err){ return console.log(err.message); } if(results.affectedRows > 0 ){ console.log("插入成功"); } })
|
更新数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
| const mysql = require("mysql");
const ab = mysql.createPool({ host: "localhost", user: "root", password: "Q123123123", database: "demo", })
const demo = { id:"8", 目标:"经济学", 行动:"学习和实践", }
const sqlstr = "update demo.demo3 set 目标=? , 行动=? where id=?";
ab.query(sqlstr,[demo.目标,demo.行动,demo.id],(err,results)=>{ if(err){ return console.log(err.message); } if(results.affectedRows > 0 ){ console.log("更新成功"); }
|
便捷的方式
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
| const mysql = require("mysql");
const ab = mysql.createPool({ host: "localhost", user: "root", password: "Q123123123", database: "demo", })
const demo = { id:"8", 目标:"经济学", 行动:"学习和实践", }
const sqlstr = "update demo.demo3 set ? where id=?";
ab.query(sqlstr,[demo,demo.id],(err,results)=>{ if(err){ return console.log(err.message); } if(results.affectedRows > 0 ){ console.log("更新成功"); } })
|
删除数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
| const mysql = require("mysql");
const ab = mysql.createPool({ host: "localhost", user: "root", password: "Q123123123", database: "demo", })
const sqlstr = "delete from demo.demo3 where id=?";
ab.query(sqlstr,8,(err,results)=>{ if(err){ return console.log(err.message); } if(results.affectedRows > 0 ){ console.log("删除成功"); } })
|
使用 delete 语句会真正删除数据,保险起见,使用标记删除的形式,模拟删除的动作。即在表中设置状态字段,标记当前的数据是否被删除
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
| const mysql = require("mysql");
const ab = mysql.createPool({ host: "localhost", user: "root", password: "Q123123123", database: "demo", })
const sqlstr = "update demo.demo3 set status=1 where id=?";
ab.query(sqlstr,8,(err,results)=>{ if(err){ return console.log(err.message); } if(results.affectedRows > 0 ){ console.log("删除成功"); } })
|