node.js使用mysql数据库——koa2.x

1、安装mysql和Navicat for MySQL

2、mysql的增删改查语句：

  INSERT INTO user (username,password) value ('zhangsan','123456')
  DELETE from user WHERE id=3
  update user set username="zhansan",password="123456" where id=1
  SELECT  *  from 'user' where username='admin'

3、koa操作mysql

1）、npm install mysql
2）、引入mysql、建立连接、获取连接对象、操作mysql

var mysql      = require('mysql');
  var connection = mysql.createConnection({
    host     : 'localhost',
    user     : 'me',
    password : 'secret',
    database : 'my_db'
  });

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

  connection.end();

4、封装mysql数据库
1）、新建mysqldb.js

var mysql =  require('mysql');
function __connection(){
  var connection = mysql.createConnection({
    host     : 'localhost',
    user     : 'me',
    password : 'secret',
    database : 'my_db'
  });

  connection.connect();
  return connection;
}

exports.query = function(sql,params=null){  //params如果没有传入值就默认为空
  //获取数据库连接对象
  var connection = __connection();
  return new Promise((resolve,reject) => {

      connection.query(sql,params, function (error, results, fields) {
        if (error) throw error;
        resolve(results);
       });

      connection.end();
  })

}

2)、外部引用的方法

var db = require(’./module/mysqldb.js’);

 var username = 'slj';
 var password = '123456';

 var sql = 'select * from user'
 var sql = 'insert into user (username,password) value (?,?) '   //？为占位符
 var sql = 'update user set username=?,password=? where id=3'
 var sql = 'delete from user where id=4'

 var params = [username,password];
 var result = await db.query(sql,params); //没有params就只传sql