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

准备工作

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.jsThe 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();

参考文献：
菜鸟教程 Node.js 连接 MySQL

菜鸟教程 nodejs教程