lr 操作mysql 增删改_Node.js 实现 MySQL 数据库增删改查

安装mysql

$ npm isntall mysql

连接数据库

需要根据实际配置修改数据库用户名、及密码及数据库名

let mysql = require('mysql');

let connection = mysql.createConnection({

host: 'localhost',

user: '***', // 用户名

password: '***', // 密码

port: '3306',

database: 'node_mysql_test' // 数据库名称

});

connection.connect((err, result) => {

if (err) {

console.log(err);

console.log("连接失败");

return;

}

console.log(result);

console.log("连接成功");

});

connection.end(); // 结束连接

在 MySQL 数据库中创建初始数据

终端输入：

mysql -u 【数据库名称】 -p

mysql> create database node_mysql_test default character set utf8 default collate utf8_general_ci;

mysql> use node_mysql_test;

mysql> source 『将users.sql文件直接拖拽至终端，自动补全其文件目录』

将users.sql文件如下：

SET NAMES utf8;

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

-- Table structure for `users`

-- ----------------------------

DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` char(20) NOT NULL DEFAULT '' COMMENT '姓名',

`email` varchar(255) NOT NULL DEFAULT '',

`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',

`city` char(10) NOT NULL DEFAULT '' COMMENT '城市',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of `users`

-- ----------------------------

BEGIN;

INSERT INTO `users` VALUES ('1', '张三', 'zhangsan@qq.com', '18', '上海'), ('2', '李四', '1234@163.com', '23', '重庆'), ('3', '王五', 'abc@sina.cn', '33', '青岛'), ('4', '马六', '666@qq.com', '19', '南京');

COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

查询数据

let mysql = require('mysql');

let connection = mysql.createConnection({

host: 'localhost',

user: '***',

password: '***',

port: '3306',

database: 'node_mysql_test'

});

connection.connect((err, result) => {

if (err) {

console.log(err);

console.log("连接失败");

return;

}

console.log(result);

console.log("连接成功");

});

/**

* -------------------

* 查询数据

* -------------------

*/

let selectSql = 'SELECT * FROM users';

connection.query(selectSql, (err, result) => {

if (err) {

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

return;

}

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

console.log('查询成功:',result);

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

});

connection.end();

增加数据

let mysql = require('mysql');

let connection = mysql.createConnection({

host: 'localhost',

user: '***',

password: '***',

port: '3306',

database: 'node_mysql_test'

});

connection.connect((err, result) => {

if (err) {

console.log(err);

console.log("连接失败");

return;

}

console.log(result);

console.log("连接成功");

});

/**

* -------------------

* 增加数据

* -------------------

*/

let addSql = 'INSERT INTO users(Id,name,email,age,city) VALUES(0,?,?,?,?)';

let addSqlParams = ['赵七', '777@qq.com', '22', '大连'];

connection.query(addSql, addSqlParams, (err, result) => {

if (err) {

console.log('[增加失败] - ', err.message);

return;

}

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

console.log('增加成功 ID:',result.insertId);

console.log('增加成功:', result);

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

});

connection.end();

更新数据

let mysql = require('mysql');

let connection = mysql.createConnection({

host: 'localhost',

user: '***',

password: '***',

port: '3306',

database: 'node_mysql_test'

});

connection.connect((err, result) => {

if (err) {

console.log(err);

console.log("连接失败");

return;

}

console.log(result);

console.log("连接成功");

});

/**

* -------------------

* 更新数据

* -------------------

*/

let modSql = 'UPDATE users SET name = ?,email = ? WHERE Id = ?';

let modSqlParams = ['Tom', 'tom@qq.com', 7];

connection.query(modSql, modSqlParams, (err, result) => {

if (err) {

console.log('[更新失败] - ', err.message);

return;

}

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

console.log('更新成功', result.affectedRows);

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

});

connection.end();

删除数据

let mysql = require('mysql');

let connection = mysql.createConnection({

host: 'localhost',

user: '***',

password: '***',

port: '3306',

database: 'node_mysql_test'

});

connection.connect((err, result) => {

if (err) {

console.log(err);

console.log("连接失败");

return;

}

console.log(result);

console.log("连接成功");

});

/**

* -------------------

* 删除数据

* -------------------

*/

let delSql = 'DELETE FROM users where id=4';

connection.query(delSql, (err, result) => {

if (err) {

console.log('[删除失败] - ', err.message);

return;

}

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

console.log('删除成功', result.affectedRows);

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

});

connection.end();