nodejs mysql 驱动_nodejs MySQL操作

一  wamp创建数据库

选择phpMyAdmin

选择用户，添加用户

填写数据库详细资料，填写完毕选择右下角的“执行”

用户添加成功

2. nodejs 安装mysql驱动

npm install mysql

3 数据库操作CURD

连接数据库

index.js：

var mysql = require('mysql');

var connection = mysql.createConnection({

host : 'localhost',

user : 'test',

password : '123456',

database : 'test'

});

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);

});

运行后，输出结果：

查询

新建表account用于测试：

index.js：

var mysql = require('mysql');

var connection = mysql.createConnection({

host : 'localhost',

user : 'test',

password : '123456',

database : 'test'

});

connection.connect();

var sql = 'SELECT * FROM account';

connection.query(sql,function (err, result) {

if(err){

console.log('[SELECT ERROR] - ',err.message);

return;

}

console.log('--------------------------SELECT----------------------------');

console.log(result);

console.log(result[0].id, result[0].name, result[0].age);

console.log('------------------------------------------------------------\n\n');

});

connection.end();

输出结果：

插入数据

index.js：

var mysql = require('mysql');

var connection = mysql.createConnection({

host : 'localhost',

user : 'test',

password : '123456',

database : 'test'

});

connection.connect();

var addSql = 'INSERT INTO account(id,name,age) VALUES(3,?,?)'; //插入数据

var addSqlParams = ['baby',23]; //填写问号的数据

//增

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 : 'test',

password : '123456',

database : 'test'

});

connection.connect();

var modSql = 'UPDATE account SET name = ?,age = ? WHERE id = ?';

var modSqlParams = ['Lee', 31, 1];

//改

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 : 'test',

password : '123456',

database : 'test'

});

connection.connect();

var delSql = 'DELETE FROM account where id=3';

//删

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

运行结果，id=3的数据被删除：