使用Express+Node.js對mysql進行增改查操作 

// 引入 express 框架
const express = require('express')
const mysql = require("mysql");
const bodyParser = require('body-parser');

// 創建實例
const app = express();
// 解析 application/json
app.use(bodyParser.json());
// // 解析 application/x-www-form-urlencoded
// app.use(bodyParser.urlencoded());

app.all('*', function(req, res, next) {
res.header('Access-Control-Allow-Origin',
'*'); //*表示可以跨域任何域名都行（包括直接存在本地的html文件）出於安全考慮最好隻設置 你信任的來源也可以填域名表示隻接受某個域名
res.header('Access-Control-Allow-Headers', 'X-Requested-With,Content-Type'); //可以支持的消息首部列表
res.header('Access-Control-Allow-Methods', 'PUT,POST,GET,DELETE,OPTIONS'); //可以支持的提交方式
res.header('Content-Type', 'application/json;charset=utf-8'); //響應頭中定義的類型
next();
});

const connection = mysql.createConnection({ //建立一個連接參數方法
host: 'localhost', //數據庫地址
port: '3306', //端口號
user: 'root', //用戶名
password: 'root', //密碼
database: 'test' //數據庫名稱
});
//用參數與數據庫進行連接
// connection.connect();
let str = '';
connection.connect(); //打開連接
// connection.end()//關閉連接 如果關閉連接第二次請求數據庫連接會失敗
const getBanner = (table) => { //查詢數據
return new Promise(function(resolve, reject) {
let sql = `SELECT * FROM ${table}`;
try {
connection.query(sql, (err, data) => {
if (err) {
reject(err)
} else {
resolve(data)
}
})
} catch (e) {
console.log(e);
}
})
}

const addBanner = (list) => { //增加數據
// console.log(Object.values(list));
list = Object.values(list) //將對象轉成數組
return new Promise(function(resolve, reject) {
// const sql = 'UPDATE `user` SET ? WHERE id = ?'
// //若sql語句中包含多個?占位符,第二個實參必須傳遞一個數組,並一一對應
// conn.query(sql,[user,user.id],(err,result) => {}
let addSql = `INSERT INTO banner(name,url) VALUES (?,?)`;
try {
connection.query(addSql, list, (err, result) => {
if (err) {
reject(err)
} else {
result = {
code: 200,
msg: '增加成功'
};
resolve(result)
}
})
} catch (e) {
console.log(e);
}
})
}
const deleteBanner = (list) => { //刪除數據
return new Promise(function(resolve, reject) {
let deleteSql = `delete from banner where name='${list.name}'`;
try {
connection.query(deleteSql, (err, result) => {
if (err) {
reject(err)
} else {
result = {
code: 200,
msg: '刪除成功'
};
// console.log(result);
resolve(result)
}
})
} catch (e) {
console.log(e);
}
})
}
const updateBanner = (list) => { //修改數據
return new Promise(function(resolve, reject) {
let updateSql = `update banner set ? where id=${list.id}`;
try {
connection.query(updateSql,list.data, (err, result) => {
if (err) {
reject(err)
} else {
result = {
code: 200,
msg: '修改成功'
};
// console.log(result);
resolve(result)
}
})
} catch (e) {
console.log(e);
}
})
}
//2.發送請求(查詢)
// var insert = `INSERT INTO table_name (name,url )VALUES(${name},${url});`

app.get('/', (req, res) => {
res.send('express啟動成功端口號3011');
})
app.post('/addbanner', (req, res) => { //增加
addBanner(req.body).then(re => {
console.log(re);
res.send(re)
})
})
app.post('/deletebanner', (req, res) => { //刪除
deleteBanner(req.body).then(re => {
// console.log(re);
res.send(re)
})
})
app.post('/updatebanner', (req, res) => { //修改
updateBanner(req.body).then(re => {
console.log(re);
res.send(re)
})
})
app.post('/select', (req, res) => { //查詢某表數據
getBanner(req.body.table).then(re => {
res.send(re);
})
})
// 監聽端口
app.listen(3011, () => {
console.log("服務已經啟動，3011 端口監聽中...");
})