Node.js连接Mysql实现增删改查操作

const express = require(‘express’)

const mysql =require(‘mysql’)

const app =express();

var db = mysql.createConnection({
host : ‘localhost’,
user : ‘root’,
password : ‘123456’,
database : ‘nodemysql’
});

db.connect(err=>{
if(err) throw err;
console.log(“连接成功！”)
});

//创建数据库
app.get(’/createdb’,(req,res)=>{
let sql =‘CREATE DATABASE nodemysql’
db.query(sql,(err,result)=>{
if(err)throw err;
console.log(result)
res.send(‘Database created…’)
});
});

//创建表
// app.get(’/createpoststable’,(req,res)=>{
// let sql=‘CREATE TABLE posts(id int AUTO_INCREMENT,title VARCHAR(255),body VARCHAR(255),PRIMARY KEY(id))’;
// db.query(sql,(err,result)=>{
// if(err) throw err;
// console.log(result);
// res.send(‘posts表已经建立’)
// });
// });

//插入内容
app.get(’/addpost’,(req,res)=>{
let post = {title:‘post one’,body:‘this is post one’};
let sql = ‘INSERT INTO posts SET ?’;
db.query(sql,post,(err,result)=>{
if(err) throw err;
console.log(result);
res.send(‘posts表已经建立’)
});
});
// 查询内容
app.get(’/getposts’,(req,res)=>{
let sql = ‘SELECT * FROM posts’;
db.query(sql,(err,result)=>{
if(err) throw err;
console.log(result);
res.send(result)
});
});

// 查询单个内容
app.get(’/getpost/:id’,(req,res)=>{
let sql = SELECT * FROM posts WHERE id=${req.params.id};
db.query(sql,(err,result)=>{
if(err) throw err;
console.log(result);
res.send(result)
});
});

// 更新内容
app.get(’/updatepost/:id’,(req,res)=>{
let newTitle =“new title”;
let sql = UPDATE posts SET title = '${newTitle}' WHERE id=${req.params.id};
db.query(sql,(err,result)=>{
if(err) throw err;
console.log(result);
res.send(result)
});
});
// 删除内容
app.get(’/deletepost/:id’,(req,res)=>{
let sql = DELETE FROM posts WHERE id=${req.params.id};
db.query(sql,(err,result)=>{
if(err) throw err;
console.log(result);
res.send(‘删除成功’)
});
});
app.listen(‘3000’,()=>{
console.log(‘Server is Start port 3000’)
})