node mysql的增删改查基础

学习koa时，不选择mongodb，而是MySQL，虽然node对mongodb更亲和，但是我感觉MySQL的键值对的储存结构更正规

1.首选确认你的数据库有个库。有个表,我的如下

在这里插入图片描述

2.配置

let mySqlConfig={host:'localhost',user:'root',password:'123456',database:'X',port:'3306'
}
module.exports=mySqlConfig

const mysql=require('mysql');
const mysqlConfig=require('../config/');//上面的mySqlConfig代码
var connection = mysql.createConnection(mysqlConfig);
connection.connect();

1.查

const c = 'SELECT * FROM user';
connection.query(c,function (err, result) {if(err){console.log('[SELECT ERROR] - ',err.message);return;}console.log('---------------SELECT----------------');console.log(result);console.log('-------------------------------------');
});

结果
在这里插入图片描述

2.改

const userModSql = 'UPDATE user SET name = ?,sex = ? ,age= ? where id= ?';
const userModSql_Params = ["王五","女","22","3"];
connection.query(userModSql,userModSql_Params,function (err, result) {if(err){console.log('[UPDATE ERROR] - ',err.message);return;}console.log('----------UPDATE-------------');console.log('UPDATE affectedRows',result.affectedRows);console.log('******************************');});

结果
在这里插入图片描述

3.删

const userDelSql = 'DELETE FROM user WHERE id = 3';
connection.query(userDelSql,function (err, result) {if(err){console.log('[DELETE ERROR] - ',err.message);return;}console.log('-------------DELETE--------------');console.log('DELETE affectedRows',result.affectedRows);console.log('*********************************');
});

结果
在这里插入图片描述

4.增

const  sql = 'INSERT INTO user(name,sex,age) VALUES(?,?,?)';
const  params = ['张四', '男','21'];
connection.query(sql, params, function (err, result) {if(err){console.log('[UPDATE ERROR] - ',err.message);return;}console.log('----------UPDATE-------------');console.log('UPDATE affectedRows',result.affectedRows);console.log('******************************');
});

结果
在这里插入图片描述

完整一套

const mysql=require('mysql');
const mysqlConfig=require('../config/');
var connection = mysql.createConnection(mysqlConfig);
connection.connect();//查 query
const c = 'SELECT * FROM user';
connection.query(c,function (err, result) {if(err){console.log('[SELECT ERROR] - ',err.message);return;}console.log('---------------SELECT----------------');console.log(result);console.log('-------------------------------------');
});// 增 insert
const  sql = 'INSERT INTO user(name,sex,age) VALUES(?,?,?)';
const  params = ['张四', '男','21'];
connection.query(sql, params, function (err, result) {if(err){console.log('[UPDATE ERROR] - ',err.message);return;}console.log('----------UPDATE-------------');console.log('UPDATE affectedRows',result.affectedRows);console.log('******************************');
});// //改 update
const userModSql = 'UPDATE user SET name = ?,sex = ? ,age= ? where id= ?';
const userModSql_Params = ["王五","女","22","3"];
connection.query(userModSql,userModSql_Params,function (err, result) {if(err){console.log('[UPDATE ERROR] - ',err.message);return;}console.log('----------UPDATE-------------');console.log('UPDATE affectedRows',result.affectedRows);console.log('******************************');});//删 delete
const userDelSql = 'DELETE FROM user WHERE id = 3';
connection.query(userDelSql,function (err, result) {if(err){console.log('[DELETE ERROR] - ',err.message);return;}console.log('-------------DELETE--------------');console.log('DELETE affectedRows',result.affectedRows);console.log('&&&&&&&&&&&&&&&&&');
});
connection.end();