node操作mysql数据基本增删改查功能笔记

安装mysql框架

npm install mysql

mysql第一个实例

数据库连接
*/
//加载数据库驱动
const db = require('mysql');
//创建数据库连接
const connecttion = db.createConnection({
	host:'localhost', // 数据库所在的服务器的域名或者IP地址
	user:'root', //登录数据库的账号
	password:'', //登录数据库的密码
	database:'storedb' //数据库名称
});
//执行数据库
connecttion.connect();
//操作数据库
connecttion.query('select count(*) as total from book',function(err,result,fields){
	if(err)throw err;
	console.log('表中一共有',result[0].total+'条数据');
});
//关闭数据库
connecttion.end();

插入数据

/*
 插入数据
 */ 

 const mysql = require('mysql');//加载数据库
 //创建数据库连接
 const connection = mysql.createConnection({
 	host:'localhost',
 	user:'root',
 	password:'',
 	database:'storedb'
 });
 connection.connect();//执行连接操作
let sql = 'insert into book set ?';
let data = {
	b_name:'海底五千米',
	b_price:99.60,
	t_desc:'发生在海底的故事'
};
//操作数据库
connection.query(sql,data,function(error,result,fields){
	if(error) throw error;
	console.log(result);
	if(result.affectedRows==1){
		console.log('数据插入成功！');
	}

});
// 关闭数据库
connection.end();

查询数据

/*
查询数据
*/

const mysql = require('mysql');
const connection = mysql.createConnection({
	host:'localhost',
	user:'root',
	passwors:'',
	database:'storedb'
});
connection.connect(); //执行数据库连接
let sql ='select * from book where b_id = ?';
let data = [3];
connection.query(sql,data,function(error,result,fields){
	if(error)throw error;
	console.log(result);
	if(result){
		for(var key in result[0]){
			console.log(result[0][key]);
		}
	}
});
connection.end();

删除数据

/*
删除数据库
*/

const mysql = require('mysql');
const connection = mysql.createConnection({
	host:'localhost',
	user:'root',
	password:'',
	database:'storedb'
});
connection.connect();
//sql 语句
let sql = 'delete from book where b_id = ?';
let data = [2];
connection.query(sql,data,function(error,result,fields){
	if(error) throw error;
	if(result.aftertedRows ==1){
		console.log('删除成功！');
	}
});
connection.end();//关闭数据库

跟新数据

/*
更新数据库
*/

const mysql = require('mysql');
const connection = mysql.createConnection({
	host:'localhost',
	user:'root',
	password:'',
	database:'storedb'
});
connection.connect();
//sql 语句
let sql = 'update  book  set b_name =?, b_price = ?, t_desc = ?  where b_id = ?';
let data = ['毛泽东语录',100.20,'伟人的诞生的时刻',1];
connection.query(sql,data,function(error,result,fields){
	if(error) throw error;
	console.log(result);
	if(result.affectedRows ==1){
		console.log('更新书成功！');
	}
});
connection.end();//关闭数据库

封装操作mysql的工具

/*
    封装操作数据库的通用api
*/
const mysql = require('mysql');
exports.base = (sql,data,callback)=>{
    //创建数据库链接
    const connection = mysql.createConnection({
        host:'localhost', //数据库所在的服务器的域名或者IP地址
        user:'root', //登录数据库的账号
        password:'', //登录数据库的密码
        database:'storedb' //数据库名
    });
    //执行连接操作
    connection.connect();
     // 操作数据库(数据库操作也是异步的)
     connection.query(sql,data,function(error,results,filds){
        if(error){
            throw error;
        }
        callback(results);
     });
     //关闭数据库
     connection.end();
}

测试mysql的工具封装

const db = require('./dbtools.js');

//插入数据操作
let sql = 'insert into book set ?';
let data = {
	'b_name':'的小平理论',
	'b_price':56.30,
	't_desc':'指导中国改革开放的'
};
db.base(sql,data,(result)=>{
	console.log(result);
})

测试删除

const db = require('./dbtools.js');
//更新数据操作
let sql = "update book set b_name = ?,b_price = ?,t_desc = ? where b_id=?";
let data = [
	"中国无产阶级",
	45.30,
	"伟大的工人阶级",
	1
];
db.base(sql,data,(result)=>{
	console.log(result);
})

测试 查询

const db = require('./dbtools.js');
//查询数据操作
let sql = "select * from book where b_id";
let data = [3];
db.base(sql,data,(result)=>{
	console.log(result[0].b_name);
})