node.js操作mysql相關(guān)詳細(xì)API：https://github.com/mysqljs/mysql
步驟一：準(zhǔn)備好node環(huán)境
初始化環(huán)境 npm init嗓违，安裝express并引入express
步驟二：安裝mysql
npm install mysql --save
//app.js
const express=require('express');
const mysql=require('mysql');
const app=express();
//創(chuàng)建連接
const connection = mysql.createConnection({
? host? ? : 'localhost',
? user? ? : 'me',
? password : 'secret',
? database : 'my_db'
});
connection.connect((err)=>{
if(err)? throw err;
console.log('mysql is conected');
});
// 創(chuàng)建數(shù)據(jù)庫
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...');
? });
});
// 創(chuàng)建表
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表已經(jīng)建立...');
? });
});
// 插入內(nèi)容
app.get('/addpost1', (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('post 1 added....');
? });
});
// 查詢內(nèi)容
app.get('/getposts', (req, res) => {
? let sql = 'SELECT * FROM posts';
? db.query(sql, (err, result) => {
? ? if (err) throw err;
? ? console.log(result);
? ? res.json(result);
? });
});
// 查詢單條內(nèi)容
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.json(result);
? });
});
// 更新內(nèi)容
app.get('/updatepost/:id', (req, res) => {
? let newTitle = 'update 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.json('更新成功...');
? });
});
// 刪除內(nèi)容
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.json('刪除成功....');
? });
});
app.listen(3000,()=>{
????console.log('server started on 3000');
});
