# MongoDB 基础

MongoDB教程 https://www.yiibai.com/mongodb

# Node.js 操作 MongoDB

var mongodb = require('mongodb');
var MongoClient = mongodb.MongoClient;
var db;

MongoClient.connect("mongodb://localhost:27017/test1705candel", function(err, database) {
  if(err) throw err;

  db = database;
});

module.exports = {
	insert: function(_collection, _data, _callback){
		var i = db.collection(_collection).insert(_data).then(function(result){
			_callback(result);
		});
	},
	select: function(_collection, _condition, _callback){
		var i = db.collection(_collection).find(_condition || {}).toArray(function(error, dataset){
			_callback({status: true, data: dataset});
		})
	}
}

@前端进阶之旅: 代码已经复制到剪贴板

# MySql 基础

# node-mysql

npm install mysql

@前端进阶之旅: 代码已经复制到剪贴板

新建sql.js

var mysql = require('mysql');
var connection = mysql.createConnection({
	host: 'localhost',
	user: 'wscats',
	password: '123456789',
	database: 'asm'
});
connection.connect();//连接数据库
connection.query('select * from news', function(err, rows, fields) {
	if(err) throw err;
	console.log('The news is: ', rows[0]);
});//执行SQL语句
connection.end();//关闭连接。

@前端进阶之旅: 代码已经复制到剪贴板

连接基本参数

参数	用法
host	主机名，localhost代表本地
user Mysql用户
password	密码
database	连接的数据库

执行文件

node sql

@前端进阶之旅: 代码已经复制到剪贴板

注意如果我们要在每一次查询数据库后connection.end()关闭一次连接，那我们需要mysql.createConnection()创建一个新的connection，也就是每一次的开关都是用唯一一个connection来实现

var connection;
function createConnection() {
	connection = mysql.createConnection({
		host: 'localhost',
		user: 'laoxie',
		password: '12345678',
		database: 'asm'
	});
}

@前端进阶之旅: 代码已经复制到剪贴板

增删查改分页

注意sql语句不要写错语法

//增加记录
connection.query('insert into news (title ,text) values ("wscats" , "eno")');

@前端进阶之旅: 代码已经复制到剪贴板

//删除记录
connection.query('delete from news where title = "wscats"');

@前端进阶之旅: 代码已经复制到剪贴板

// 修改记录
connection.query('update news set text = "eno" where title = "wscats"');

@前端进阶之旅: 代码已经复制到剪贴板

//查找记录
connection.query('select * from news', function(err, rows, fields) {
	if(err) throw err;
	console.log('The news is: ', rows[0]);
});

@前端进阶之旅: 代码已经复制到剪贴板

//查询记录
var arr = [];
connection.query("select * from news", function selectTable(err, rows, fields) {
	if(err) {
		throw err;
	}
	if(rows) {
		for(var i = 0; i < rows.length; i++) {
			console.log("第" + i + "条", "id: " + rows[i].id, "title: " + rows[i].title, "text: " + rows[i].text);
			//把数据组装成数组对象
			var obj = {};
			obj.id = rows[i].id;
			obj.title = rows[i].title;
			obj.text = rows[i].text;
			arr.push(obj);
		}
	}
	console.log(arr);
});

@前端进阶之旅: 代码已经复制到剪贴板

//查询记录
connection.query('select * from news where id = 2', function(err, rows, fields) {
	if(err) throw err;
	console.log('The news is: ', rows[0]);
});

@前端进阶之旅: 代码已经复制到剪贴板

//分页
//取前5条数据
select * from table limit 0,5
//or
select * from table limit 5
//取第11条到第15条数据，共5条
select * from table limit 10,5
//格式
select * from table limit offset,rows
offset指定要返回的第一行的偏移量，rows第二个指定返回行的最大数目。初始行的偏移量是0(不是1)

@前端进阶之旅: 代码已经复制到剪贴板

封装成模块

最后我们可以把它封装成一个模块导出，在其他主模块中调用注意我在每个原型链的函数结尾处都会调用一个connection.end()方法，这个方法connection.connect()对应，一个开始，一个结束

配合await和async的封装

let mysql = require('mysql');
let config = require('./config.json');
let pool = mysql.createPool({
    connectionLimit: 10,
    host: config.host,
    user: config.user,
    port: config.port,
    password: config.password,
    database: config.database
});
let connect = () => {
    return new Promise((resolve, reject) => {
        pool.getConnection((err, connection) => {
            !err ? resolve(connection) : reject(err)
        });
    })
}

let find = (table, params) => {
    return new Promise(async (resolve, reject) => {
        let connection = await connect();
        connection.query(`SELECT * FROM ${table} ${params?'where ?':''}`, [{
            ...params
        }], (err, results, fields) => {
            !err ? resolve(results) : reject(err)
            connection.release();
        });
    })
}

let insert = (table, params) => {
    return new Promise(async (resolve, reject) => {
        let connection = await connect();
        connection.query(`INSERT INTO ${table} SET ?`, [{
            ...params
        }], (err, results, fields) => {
            !err ? resolve(results) : reject(err)
            connection.release();
        })
    })
}

let del = (table, params) => {
    return new Promise(async (resolve, reject) => {
        let connection = await connect();
        connection.query(`DELETE FROM ${table} WHERE ?`, [{
            ...params
        }], (err, results, fields) => {
            !err ? resolve(results) : reject(err)
            connection.release();
        })
    })
}

let update = (table, params1, params2) => {
    return new Promise(async (resolve, reject) => {
        let connection = await connect();
        connection.query(`UPDATE ${table} SET ? WHERE ?`, [{
            ...params1
        }, {
            ...params2
        }], (err, results, fields) => {
            !err ? resolve(results) : reject(err)
            connection.release();
        })
    })
}
module.exports = {
    connect,
    find,
    insert,
    del,
    update
}

@前端进阶之旅: 代码已经复制到剪贴板

断线重连

因为mysql连接时间长的话会自动断掉，可以封装一个断线重连的接口

自动断线

建议用下面这一段来实现mysql的自动连接和自动断开，那就不会出现too many connections的错误提醒了

# MongoDB 基础

# Node.js 操作 MongoDB

# MySql 基础

# node-mysql

# Node.js 操作 MySql

操作数据库|Nodejs专题系列

操作数据库|Nodejs专题系列