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