Node.js之mysql增删改查

1.安装库

1
npm install mysql

2.编写db.js(用作公共模块)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
//连接MySQL数据库
var mysql = require("mysql");

var pool = mysql.createPool({
host:"127.0.0.1",
user:"root",
password:"123456",
database:"wordpress",
port:3306
});


function query(sql,callback){
pool.getConnection(function(err,connection){
connection.query(sql, function (err,rows) {
callback(err,rows);
connection.release();
});
});
}

exports.query = query;

3.编写user.js(与数据库交互并对外开放接口)

顺便说下req.params、req.body、req.query的应用

req.body通常用于解析post请求数据

req.query通常用于解析get请求数据,如http://wwww.youcongtech.com/blog/user?username=youcong

req.params通常用于解析rest请求方式,如http://www.youcongtech.com/blog/user/youcong

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
var express = require('express');
var router = express.Router();
var URL = require('url');
var db = require('./db');
var jsonData = require('./jsonData');
var bodyParser = require('body-parser');

//定义一个post输出接口
router.post('/post', function(req, res) {

var obj = {
a: 1,
b: 2
};

res.json(obj); //以json格式输出
});



//增删改查

//查询所有信息
router.get('/queryAll', function(req, res, next) {

var userLogin = req.userLogin

var querySql = 'SELECT * FROM wp_users';

db.query(querySql,function (err, rows) {


if(err){
console.log('[SELECT ERROR] - ',err.message);
return;
}

//把搜索值输出
res.send(rows);
console.log('The solution is: ', rows[0].ID);

});


});

//添加用户信息
router.post("/add",function(req,res){
var params = URL.parse(req.url, true).query;

var addSql = "INSERT INTO wp_users (user_login) VALUES('"+params.userLogin+"')";


var addSqlParams = [params.userLogin];

db.query(addSql,function(err,rows){
if(err){
res.send("添加失败 " + err);
}else {
res.send(jsonData.addInfo);
}
});

});

//更新用户信息
router.put("/update",function(req,res,next){

var params = URL.parse(req.url, true).query;

var displayName = params.displayName;

var id = params.id;

var sql = "update wp_users set display_name = '"+ displayName +"' where ID = " + id;

db.query(sql,function(err,rows){
if(err){
res.send("修改失败 " + err);
}else {
res.send(jsonData.updateInfo);
}
});

});

//根据ID获取用户信息
router.get("/getById/:id",function(req,res){

var id = req.params.id;

var sql = 'select * from wp_users where ID = '+id;


db.query(sql,function (err, result) {

if(err){
console.log('[SELECT ERROR] - ',err.message);
return;
}

console.log(result);
res.send(result);

});

});


//删除用户信息
router.delete('/delete/:id', function(req, res, next) {


var id = req.params.id;

var delSql = 'delete from wp_users where id = '+id;

db.query(delSql,function (err, result) {

if(err){
console.log('[INSERT ERROR] - ',err.message);
return;
}
res.send(jsonData.deleteInfo);

});
});






module.exports = router;

4.不要忘记在app.js配置路由

1
2
3
var usersRouter = require('./routes/users');

app.use('/users', usersRouter);

参考资料如下:
node.js取参四种方法req.body,req.params,req.param,req.body:https://www.cnblogs.com/jkingdom/p/8065202.html

文章目录
  1. 1.安装库
  2. 2.编写db.js(用作公共模块)
  3. 3.编写user.js(与数据库交互并对外开放接口)
  4. 4.不要忘记在app.js配置路由