配置
安装
cnpm install mysql
链接
const mysql = require('mysql')
const db = mysql.createPool({
host: '127.0.0.1',
user: 'root',
password: 'root',
database: 'books'
})
查询
const sqlStr = 'select * from book'
db.query(sqlStr, (err, results) => {
if (err) return console.log(err.message);
console.log(results);
})
插入
// 新增一条数据
const book = {
id: 353,
bookname: 'hhhhh',
readname: 'slx',
date: '2002.6.7',
price: '63.4'
}
// 待执行的sql语句
const sqlStr = 'insert into book(id,bookname,readname,date,price) values (?,?,?,?,?)'
//问号是 占位符 []
// 执行sql
db.query(sqlStr, [book.id, book.bookname, book.readname, book.date, book.price], (err, results) => {
if (err) return console.log(ree.message);// fail
if (results.affectedRows == 1) { // 返回对象 包含affectedRows 来判断插入是否成功
console.log(results);
}
})
便捷方式
const book = {
id: 753,
bookname: 'tttt',
readname: 'srr',
date: '2002.6.7',
price: '63.4'
}
const sqlStr = 'insert into book set ?'// !!!!!
db.query(sqlStr, book, (err, results) => {// !!!!!
if (err) return console.log(ree.message);// fail
if (results.affectedRows == 1) {
console.log(results);
}
})
更新
const book2 = {
id: 753,
bookname: 'tttt',
readname: 'pol',
}
const sqlStr = 'update book set bookname=?,readname=? where id=?'// !!!!!
db.query(sqlStr, [book2.bookname, book2.readname, book2.id], (err, results) => {// !!!!!
if (err) return console.log(ree.message);// fail
if (results.affectedRows == 1) {
console.log(results);
}
})
便捷方式
const book3 = {
id: 222,
bookname: 'cao',
readname: 'md',
date: '1999.6.3',
price: '796.33'
}
const sqlStr = 'update book set ? where id=?'// !!!!!
db.query(sqlStr, [book3, book3.id], (err, results) => {// !!!!!
if (err) return console.log(ree.message);// fail
if (results.affectedRows == 1) {
console.log(results);
}
})
删除
彻底删除(有些危险)
const sqlStr = 'delete from book where id=?'// !!!!!
db.query(sqlStr, 353, (err, results) => {// !!!!!
if (err) return console.log(ree.message);// fail
if (results.affectedRows == 1) {
console.log(results);
}
})
标记删除
数据库中设置status
const sqlStr = 'update book set status=? where id=?'// !!!!!
db.query(sqlStr, [1, 753], (err, results) => {// !!!!!
if (err) return console.log(ree.message);// fail
if (results.affectedRows == 1) {
console.log(results);
}
})