Node.js 简单数据库操作(mysql)

1、先完成MySql的安装、配置:

下载 XAMPP,安装并运行

使用 Navicat 连接并创建数据库 test 和表 think_user

2. 命令行进入d:\Documents\Nodejs\exTest,执行命令:

npm install mysql

3. 编辑 主程序文件 index.js

第一部分:引入mysql模块和添加数据库连接字符串

var express = require('express'); 
var bodyParser = require('body-parser');
var app = express();
const mysql = require('mysql');
const conn = mysql.createConnection({
    host:'localhost',
    user:'root',
    password:'mysqlpwd',
    database:'test'
});

//添加get,返回 所有数据
app.get('/getall', (req,res) => {    
    const sqlStr = 'select * from think_user'
    conn.query(sqlStr,(err,results) => {
         console.log(results)
         if(err)  return res.json({err_code:1,message:'获取失败',affectedRows:0})
         res.json({
            err_code:0,message:results,affectedRows:results.length
        });
    });
});

//添加get,返回指定数据
app.get('/get',(req,res) => {
const sqlStr = 'select * from think_user where id = ?' ;
    conn.query(sqlStr, req.query.id, (err,results) => {
        if(err) return res.json({err_code:1,message:'获取失败',affectedRows:0})
        if(results.length !== 1) 
		return res.json({err_code:1,message:'数据不存在',affectedRows:0})
        res.json({
            err_code:0, message:results[0], affectedRows:results.length
        }); 
    });
});

//添加post,添加、删除数据
var urlencodedParser = bodyParser.urlencoded({ extended: false });
app.post('/post/insert_del', urlencodedParser, (req,res) => {
   const data = req.body;
   const sqlStr = 'insert into think_user set ?'; //添加数据
   //const sqlStr = 'delete from think_user where ?'; /删除数据
   conn.query(sqlStr,data,(err,results) => {
         if(err)  return res.json({err_code:1,message:err.message, affectedRows:0});
         if(results.affectedRows !== 1) 
            return res.json({err_code:1,message:'操作失败', affectedRows:0});
        res.json({err_code:0,message:'操作成功', affectedRows:results.affectedRows})
   }); 
});

//添加post,更新数据,第三部分与上节一致
var urlencodedParser = bodyParser.urlencoded({ extended: false })
app.post('/post/update', urlencodedParser, (req,res) => {
   const data = req.body
   const sqlStr = 'update think_user set ? where id = ?'
   conn.query(sqlStr,[req.body, req.body.id], (err,results) => {
         if(err) return res.json({err_code:1,message:err.message, affectedRows:0})
         if(results.affectedRows !== 1) 
            return res.json({err_code:1,message:'操作失败', affectedRows:0})
        res.json({err_code:0,message:'操作成功', affectedRows:results.affectedRows})
 }); 
});

//web服务设置代码. 服务的端口为 8080
var server = app.listen(8080, function(){
	var host = server.address().address;
       var port = server.address().port;
       console.log("address: %s, port: %d", host, port);
});

4. API 访问测试:

获取全部数据 http://127.0.0.1:8080/getall
获取部分数据 http://127.0.0.1:8080/get
添加、删除数据 http://127.0.0.1:8080/post/insert_del
更新数据 http://127.0.0.1:8080/post/update


Comments are closed.