- 首先导入mysql模块 建立与自己的数据库的连接关系
const mysql = require('mysql')
const db = mysql.createPool({
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DBPWD,
database: process.env.DB_NAME,
})
- 测试mysql是否能正常工作
db.query('select 1', (err, results) => {
if(err) return console.log(err.message)
console.log(results)
})
- 查询
const sqlStr = 'select * from users'
db.query(sqlStr, (err, results) => {
if (err) return console.log(err.message)
console.log(results)
})
- 插入
const user = { username: 'Spider-Man', password: 'pcc123' }
const sqlStr = 'insert into users (username, password) values (?, ?)'
db.query(sqlStr, [user.username, user.password], (err, results) => {
if (err) return console.log(err.message)
if (results.affectedRows === 1) {
console.log('插入数据成功!')
}
})
const user = { username: 'Supperman', password: 'pcc4321' }
const sqlStr = 'insert into users set ?'
db.query(sqlStr, user, (err, results) => {
if (err) return console.log(err.message)
if (results.affectedRows === 1) {
console.log('插入数据成功')
}
})
- 更新
const user = { id: 6, username: 'aaa', password: '000' }
const sqlStr = 'update users set username=?, password=? where id=?'
db.query(sqlStr, [user.username, user.password, user.id], (err, results) => {
if (err) return console.log(err.message)
if (results.affectedRows === 1) {
console.log('更新成功')
}
})
const user = { id: 6, username: 'aaaa', password: '0000' }
const sqlStr = 'update users set ? where id=?'
db.query(sqlStr, [user, user.id], (err, results) => {
if (err) return console.log(err.message)
if (results.affectedRows === 1) {
console.log('更新数据成功')
}
})
- 删除
const sqlStr = 'delete from users where id=?'
db.query(sqlStr, 6, (err, results) => {
if (err) return console.log(err.message)
if (results.affectedRows === 1) {
console.log('删除数据成功')
}
})
const sqlStr = 'update users set status=? where id=?'
db.query(sqlStr, [1, 7], (err, results) => {
if (err) return console.log(err.message)
if (results.affectedRows === 1) {
console.log('标记删除成功')
}
})