node.js+mysql增删查改

时间:2022-07-25 11:26:40

数据库和表:

--
-- 数据库: `test`
-- -- -------------------------------------------------------- --
-- 表的结构 `mytable`
-- CREATE TABLE `mytable` (
`name` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8; --
-- 导出表中的数据 `mytable`
-- INSERT INTO `mytable` VALUES ('名称1');
INSERT INTO `mytable` VALUES ('名称2');

app.js

//连接数据库
var mysql = require('mysql');
var connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'xxx',
database:'test'
}); connection.connect(); //查询
var selectSQL='select * from `mytable`';
//添加
var insertSQL='insert into `mytable` (`name`)values("mary")';
//修改
var updateSQL='update `mytable` set `name`="caton" where name="mary"'
//删除
var deleteSQL='delete from `mytable` where `name` like "caton"'; //执行SQL
connection.query(updateSQL, function(err, rows) {
if (err) throw err;
}); //关闭连接
connection.end();

通过替换query里面的SQL语句就可以实现增删查改

整理下代码:

创建模块settings.js用于设置数据库连接信息

var settings={};
settings.db={
host: 'localhost',
user: 'root',
password: 'xxx',
database:'test'
}
module.exports=settings;

app.js

//连接数据库
var mysql = require('mysql');
//配置模块
var settings = require('./settings');
//连接数据库
var connection = mysql.createConnection(settings.db);
connection.connect(); //查询
var selectSQL = 'select * from `mytable`';
//添加
var insertSQL = 'insert into `mytable` (`name`)values("mary")';
//修改
var updateSQL = 'update `mytable` set `name`="caton" where name="mary"'
//删除
var deleteSQL = 'delete from `mytable` where `name` like "caton"'; //执行SQL,自行替换参数测试
connection.query(updateSQL, function(err, rows) {
if (err) throw err;
}); //关闭连接
connection.end();