简单使用Node.js连接MySql数据库进行增删改查

连接数据库

//引入mysql（既然要使用它，当然要引入啦）
const mysql = require('mysql'); 
 
//连接mysql,option可很具情况自行配置
const option = {
  host: "主机地址",
  user: "Mysql用户名",
  password: "Mysql用户密码",
  database: "数据库名称",
  port: '端口号',
  connectTimeout: 5000, // 连接超时
};
 
const connection = mysql.createConnection(option);
 
//连接开始
connection.connect(function(err){
    if(err){
        console.log("连接失败");
    }
    else{
        console.log('连接成功');
    }
});

增

var userAddSql = 'INSERT INTO userinfo(Id,UserName,UserPass) VALUES(0,?,?)';
var userAddSql_Params = ['Wilson','abcd'];


//增
 connection.query(userAddSql,userAddSql_Params,function(err,result){
     if(err){
         console.log('[INSERT ERROR] - ',err.message);
        return;
    }
    
     console.log('--------------------------INSERT----------------------------');
     console.log('INSERT  ID:',result);
     console.log('-----------------------------------------------------------------\n\n');
 });

改

 var userModSql = 'UPDATE userinfo SET UserName =?,UserPass = ?  WHERE Id = ?';
 var userModSql_Params = ['钟馗','5678',1];
 
 //改
 
 connection.query(userModSql,userModSql_Params,function(err,result){
    if(err){
        console.log('[UPDATE ERROR] - ',err.message);
        return;
    } 
      console.log('--------------------------UPDATE----------------------------');
      console.log('UPDATE affectedRows',result);
      console.log('-----------------------------------------------------------------\n\n');
 });

查

var userGetSql = 'SELECT * FROM userinfo';
 
 //查
 connection.query(userGetSql,function(err,result){
    if(err){
        console.log('[SELECT ERROR] - ',err.message);
     return;
    } 
    
    console.log('--------------------------SELECT----------------------------');
    console.log(result);        
    console.log('-----------------------------------------------------------------\n\n');  
 });

删

 var userDelSql = 'DELETE FROM userinfo';
 
 //删
 connection.query(userDelSql,function(err,result){
    if (err) {
        console.log('[DELETE ERROR] - ',err.message);
        return;
    } 
    
    console.log('--------------------------DELETE----------------------------');
    console.log('DELETE affectedRows',result);
    console.log('-----------------------------------------------------------------\n\n');  
 });

结束连接

 connection.end()

完整代码

//引入mysql（既然要使用它，当然要引入啦）
const mysql = require('mysql'); 
 
//连接mysql,option可很具情况自行配置
const option = {
  host: "101.201.145.134",
  user: "TestDB",
  password: "Wa8emaaN7ShSkcyR",
  database: "TestDB",
  port: '3306',
  connectTimeout: 5000, // 连接超时
};
 
const connection = mysql.createConnection(option);
 
//连接开始
connection.connect(function(err){
    if(err){
        console.log("连接失败");
    }
    else{
        console.log('连接成功');
    }
});
 
 
//这里就是增删改查的地方啦
 
var userAddSql = 'INSERT INTO userinfo(Id,UserName,UserPass) VALUES(0,?,?)';
var userAddSql_Params = ['Wilson','abcd'];


//增
connection.query(userAddSql,userAddSql_Params,function(err,result){
    if(err){
        console.log('[INSERT ERROR] - ',err.message);
        return;
    }
    
    console.log('--------------------------INSERT----------------------------');
    console.log('INSERT  ID:',result);
    console.log('-----------------------------------------------------------------\n\n');
});
 
 
 
 
 var userModSql = 'UPDATE userinfo SET UserName =?,UserPass = ?  WHERE Id = ?';
 var userModSql_Params = ['钟馗','5678',1];
 
 //改
 
 connection.query(userModSql,userModSql_Params,function(err,result){
    if(err){
        console.log('[UPDATE ERROR] - ',err.message);
        return;
    } 
      console.log('--------------------------UPDATE----------------------------');
      console.log('UPDATE affectedRows',result);
      console.log('-----------------------------------------------------------------\n\n');
 });
 
 
 var userGetSql = 'SELECT * FROM userinfo';
 
 //查
 connection.query(userGetSql,function(err,result){
    if(err){
        console.log('[SELECT ERROR] - ',err.message);
     return;
    } 
    
    console.log('--------------------------SELECT----------------------------');
    console.log(result);        
    console.log('-----------------------------------------------------------------\n\n');  
 });
 
 
 var userDelSql = 'DELETE FROM userinfo';
 
 //删
 connection.query(userDelSql,function(err,result){
    if (err) {
        console.log('[DELETE ERROR] - ',err.message);
        return;
    } 
    
    console.log('--------------------------DELETE----------------------------');
    console.log('DELETE affectedRows',result);
    console.log('-----------------------------------------------------------------\n\n');  
 });
 
 
 //结束连接
 connection.end()

学自：https://www.cnblogs.com/zhongweiv/p/nodejs_mysql.html