node连接数据库实现简单接口

const express = require("express");

const bodyParser = require("body-parser");

const app = express();

app.use(bodyParser.json());

app.use(bodyParser.urlencoded({ extended: true }))

app.listen(3000, () => console.log("server on http://localhost:3000"))

var mysql = require('mysql')

// 连接数据库
var connection = mysql.createConnection({
    host: 'localhost',//端口号
    user: 'root',//数据库用户名
    password: '052618',//数据库密码
    database: "text"//数据库名
});
connection.connect();


//定义方法
//http://localhost:3000/getInstitutionalControl

app.post('/getInstitutionalControl', function (req, res) {
    var org_id = req.body.org_id
    console.log(org_id);
    // sql语句
    var sql = `SELECT id, user_id,monitor_count,each_download_count,org_media_count,label_count,monitor_rule_item_count FROM inews_billing WHERE org_id = ${org_id}`;
    var data = []
    connection.query(sql, function (err, rows, fields) {
        if (err)
            console.log(err);
        else {
            rows[0].number_recipients = 200  //接口有了以后去掉
            rows[0].monitor_number = 20
            res.json({ 'status': 1, 'msg': '请求成功', 'data': JSON.stringify(rows) })
        }
    });
});


app.put('/updateInstitutionalControl', function (req, res, next) {
    console.log(JSON.parse(req.body.row));
    let row = JSON.parse(req.body.row)
    let id = row.id
    let user_id = row.user_id
    let monitor_count = row.monitor_count
    let each_download_count = row.each_download_count
    let org_media_count = row.org_media_count
    let number_recipients = row.number_recipients
    let label_count = row.label_count
    let monitor_rule_item_count = row.monitor_rule_item_count
    let monitor_number = row.monitor_number

    var sql = `UPDATE inews_billing SET user_id = '${user_id}' ,monitor_count = ${monitor_count}, each_download_count = ${each_download_count}, org_media_count = ${org_media_count}, label_count = ${label_count}, monitor_rule_item_count = ${monitor_rule_item_count} WHERE id =${id} `
    console.log("sql", sql);

    var data = []
    connection.query(sql, function (err, rows, fields) {
        if (err)
            console.log(err);
        else {
            res.json({ 'status': 1, 'msg': '请求成功', 'data': JSON.stringify(data) })
        }
    });
    //记得关闭连接
    //connection.end();
    //res.send();
    console.log('data', data)
});

// 搜索
app.post('/search', function (req, res) {
    console.log(req.body);
    res.json(req.body)
});
// 添加
app.post('/add', function (req, res) {
    console.log('req.body', req.body)
    res.json(req.body)
});
// 删除
app.delete('/delete/:id', function (req, res) {
    console.log('req.params.id', req.params.id)
    res.json(req.params.id)
});

一般获取用get请求，修改用put请求，查询用post请求，删除用delete请求，注意上面代码中delete请求获取参数的方式有所不同，因为不是body传参所以用req.params获取。

使用时通过  node 文件名  的命令启动

接口地址为 http://localhost:3000拼接接口名称

例如：

http://localhost:3000/getInstitutionalControl