【学习笔记】nodejs操作mysql增删改查

最新推荐文章于 2023-12-29 15:23:08 发布

转载最新推荐文章于 2023-12-29 15:23:08 发布 · 854 阅读

文章标签：

#mysql #nodejs #测试

后台专栏收录该内容

6 篇文章

订阅专栏

本文档详细介绍了如何使用Node.js连接MySQL数据库，并演示了基本的CRUD操作，包括安装必要的模块、配置连接参数及执行SQL命令等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

准备工作

npm install mysql  //安装第三方库

测试连接


var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',  //your hostname
  user     : 'root',  //your master name
  password : '123456',  //your password
  database : 'test'  //your database name
});

connection.connect();

connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

保存文件，回到终端，执行以下命令，如果你没有创建test数据库，这里会报错，请回到mysql命令行穿件数据库。

$ node test.js
The solution is: 2

数据库连接参数说明

更多参数请参考：mysql github

数据库操作CRUD
增加(Create)、读取查询(Retrieve)、更新(Update)和删除(Delete)

node <your module name>.js  //执行增删改查程序

//查询数据
var mysql  = require('mysql');  

var connection = mysql.createConnection({     
  host     : 'localhost',       
  user     : 'root',              
  password : '123456',       
  port: '3306',                   
  database: 'test', 
}); 

connection.connect();

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

       console.log('--------------------------SELECT----------------------------');
       console.log(result);
       console.log('------------------------------------------------------------\n\n');  
});

connection.end();

//插入数据
var mysql  = require('mysql');  

var connection = mysql.createConnection({     
  host     : 'localhost',       
  user     : 'root',              
  password : '123456',       
  port: '3306',                   
  database: 'test', 
}); 

connection.connect();

var  addSql = 'INSERT INTO websites(Id,name,url,alexa,country) VALUES(0,?,?,?,?)';
var  addSqlParams = ['菜鸟工具', 'https://c.runoob.com','23453', 'CN'];
//增
connection.query(addSql,addSqlParams,function (err, result) {
        if(err){
         console.log('[INSERT ERROR] - ',err.message);
         return;
        }        

       console.log('--------------------------INSERT----------------------------');
       //console.log('INSERT ID:',result.insertId);        
       console.log('INSERT ID:',result);        
       console.log('-----------------------------------------------------------------\n\n');  
});

connection.end();

//更新数据
var mysql  = require('mysql');  

var connection = mysql.createConnection({     
  host     : 'localhost',       
  user     : 'root',              
  password : '123456',       
  port: '3306',                   
  database: 'test', 
}); 

connection.connect();

var modSql = 'UPDATE websites SET name = ?,url = ? WHERE Id = ?';
var modSqlParams = ['菜鸟移动站', 'https://m.runoob.com',6];
//改
connection.query(modSql,modSqlParams,function (err, result) {
   if(err){
         console.log('[UPDATE ERROR] - ',err.message);
         return;
   }        
  console.log('--------------------------UPDATE----------------------------');
  console.log('UPDATE affectedRows',result.affectedRows);
  console.log('-----------------------------------------------------------------\n\n');
});

connection.end();

//删除数据
var mysql  = require('mysql');  

var connection = mysql.createConnection({     
  host     : 'localhost',       
  user     : 'root',              
  password : '123456',       
  port: '3306',                   
  database: 'test', 
}); 

connection.connect();

var delSql = 'DELETE FROM websites where id=6';
//删
connection.query(delSql,function (err, result) {
        if(err){
          console.log('[DELETE ERROR] - ',err.message);
          return;
        }        

       console.log('--------------------------DELETE----------------------------');
       console.log('DELETE affectedRows',result.affectedRows);
       console.log('-----------------------------------------------------------------\n\n');  
});

connection.end();