const mySql = require('mysql');
let connection ;
let insert, update, deletes,select;
insert= update = deletes = select = (condition, callback) =>{//insert into 表名(`name`,`age`) values('张三',12),('李四',13)//select * from 表名 where id=1 and name='张三'//update 表名 set name='张三' where id=1//delete from 表名 where id=1
connection.query(condition, (err, data) =>{if (err) throwerr;
callback&&callback(data);
});
};//或使用占位符的方式
insert= (sql, values, callback) =>{//单条数据插入//sql:'insert into 表名 set ?'//values:{name: '张三', age:14}
connection.query(sql, values, (err, data) =>{if (err) throwerr;
callback&&callback(data);
});//多条数据插入//sql:'insert into 表名(`name`,`age`) values ?'//values:[['张三', 18],['李四', 19],['王五', 20]]
connection.query(sql, [values], (err, data) =>{if (err) throwerr;
callback&&callback(data);
});
}
update= (sql, values, callback) =>{//注意:mysql 没有批量 update 的方法,得使用循环来单个修改//更新单条数据//sql:'update 表名 set name=?,age=? where id=?'//values:['张三', 14, 1]
connection.query(sql, values, (err, data) =>{if (err) throwerr;
callback&&callback(data);
});//更新全部数据//sql:'update 表名 set name=?'//values:['张三']
connection.query(sql, values, (err, data) =>{if(err) throwerr;
callback&&callback(data);
})
};select = (sql, values, callback) =>{//多条件查询//sql:'select * from 表名 where name = ? and age = ?'//或 sql:'select * from 表名 where name = ? or age = ?'//values:['张三', 12]
connection.query(sql, values, (err, data) =>{if (err) throwerr;
callback&&callback(data);
});//同条件限定查询//sql:'select * from 表名 where id in (?)'//values:[1,2,3,4]
connection.query(sql, [values], (err, data)=>{if(err) throwerr;
callback&&callback(data)
})
};
deletes= (sql, values, callback) =>{//删除一条//sql:'delete from 表名 where id=?'//values:[1]
connnection.query(sql, values, (err, data)=>{if(err) throwerr;
callback&&callback(data);
})//删除多条//sql:'delete from 表名 where id in (?)'//values:[1,2,3]
connection.query(sql, [values], (err, data)=>{if(err) throwerr;
callback&&callback(data)
});
};const connectionStart = callback =>{
connection=mySql.createConnection({
host:'localhost',
user:'root',
password:'',
database:'bms-test-back'});
connection.connect(err=>{if (err) return console.log('connectSql fail:' +err);
callback&&callback();
});
};const connectionEnd = () =>connection.end();
module.exports={
insert,
update,
deletes,select,
connectionStart,
connectionEnd
}