node增刪改查

  1. 安裝 express
    npm install express --save
  2. 安裝 mysql
    npm install mysql --save
    要引入mysql的同時(shí)還需要req.body對(duì)表單數(shù)據(jù)進(jìn)行解析 所以還需引入body-parser，所以需要安裝
  3. 安裝body-parser
    npm install body-parser --save
  4. 開啟本地mysql

我使用的是XAMPP集成，在網(wǎng)上可以直接下載
這個(gè)是下載地址： XAMPP

下載完成之后開啟mysql

image.png

現(xiàn)在開啟了mysql還不能管理
需要下載navicat for mysql來管理我們的數(shù)據(jù)表


image.png

navicat for mysql下載地址
沒有注冊(cè)過的有免費(fèi)試用期

下載好了navicat之后，進(jìn)行本地連接


image.png

下面新建一個(gè)表，我新建的表名 叫 test
在test表里面有四個(gè)字段，自己可以寫上數(shù)據(jù)，方便一會(huì)兒寫查詢接口

再新建一個(gè)表叫 discribe ，新建這個(gè)表是為了多表查詢


image.png

下面就可以寫接口了

新建一個(gè)文件叫做app.js

寫入下面內(nèi)容

const express = require('express');
const app = express();
const bodyParser = require('body-parser');
const mysql = require('mysql');
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'test',
    multipleStatements: true, //  允許執(zhí)行多條語句
})


connection.connect(function() {
    console.log('鏈接成功')
});


app.use(bodyParser.urlencoded({
    extends: true
}));

//設(shè)置跨域訪問
app.all('*', function(req, res, next) {
    res.header("Access-Control-Allow-Origin", "*");
    res.header("Access-Control-Allow-Headers", "X-Requested-With");
    res.header("Access-Control-Allow-Methods", "PUT,POST,GET,DELETE,OPTIONS");
    res.header("X-Powered-By", ' 3.2.1');
    res.header("Content-Type", "application/json;charset=utf-8");
    next();
});

//配置服務(wù)端口 
var server = app.listen(3000, function() {
    const hostname = 'localhost';
    const port = 3000;
    console.log(`Server running at http://${hostname}:${port}/`);
})

執(zhí)行文件

打開當(dāng)前文件夾的控制臺(tái)，輸入 node app.js，可以看到連接成功的字樣就代表可以連接啦~~~


image.png

第一個(gè)查詢接口

使用的是mysql原生查詢


//  查詢
app.get('/getdata', (req, res) => {
    let sql = ` select t.id,t.name,t.age,t.school,d.discribe
                from test as t
                join discribe as d
                on t.id = d.id
    `;
    connection.query(sql, (err, results) => {
        if (err) return res.json({
            code: 100,
            data: '沒有內(nèi)容'
        });
        res.json({
            code: 200,
            data: results
        });
    })
});

第一個(gè)插入接口

//  插入
app.post('/insertdata', (req, res) => {
    let params = [req.body.name, req.body.age, req.body.school] //  傳來的參數(shù)
    let params_t = [req.body.discribe] //  傳來的參數(shù)  
    let addsql = `insert into test(name,age,school) value (?,?,?)` //  插入語句，?代表插入的值，要插入到test表內(nèi)容
    let addsql_t = `insert into discribe(discribe) value (?)` //  傳插入語句，?代表插入的值，要插入到discribe表中

    let test = new Promise((resolve, reject) => {
        connection.query(addsql, params, (err, result) => {
            err ? reject(`插入失敗`) : resolve(result)
        })
    })
    let discribe = new Promise((resolve, reject) => {
        connection.query(addsql_t, params_t, (err, result) => {
            err ? reject(`插入失敗`) : resolve(result)
        })
    })

    Promise.all([test, discribe]).then(result => {
        //  last_insert_id是獲取表中最后一條數(shù)據(jù)
        connection.query('select last_insert_id()', (err, results) => {
            res.json({
                code: 200,
                data: {
                    id: results[0]['last_insert_id()'],
                    name: req.body.name,
                    age: parseInt(req.body.age),
                    school: req.body.name,
                    discribe: req.body.discribe
                }
            });
        })
    }).catch(err => {
        res.json({
            code: 100,
            data: `插入數(shù)據(jù)有誤`
        })
    })
});

第一個(gè)刪除接口

//  刪除
app.post('/deletedata', (req, res) => {
    let [params, addsql, addsql_t] = [
        [req.body.id],
        'delete test from test where id = ? ',
        'delete discribe from discribe where id = ? '
    ]

    let test = new Promise((resolve, reject) => {
        connection.query(addsql, params, function(err, result) {
            err ? reject(`刪除失敗`) : resolve(result)
        })
    })
    let discribe = new Promise((resolve, reject) => {
        connection.query(addsql_t, params, function(err, result) {
            err ? reject(`刪除失敗`) : resolve(result)
        })
    })

    Promise.all([test, discribe]).then(result => {
        res.json({
            code: 200,
            data: []
        });
    }).catch(err => {
        res.json({
            code: 100,
            data: '刪除失敗'
        });
    })
});

第一個(gè)修改接口

//  修改
app.post('/updatedata', (req, res) => {
    let [params, params_t, addsql, addsql_t] = [
        [req.body.name, req.body.age, req.body.id, req.body.school],
        [req.body.discribe, req.body.id],
        'update test set name = ? , age = ? , school = ? where id = ? ',
        'update discribe set discribe = ? where id = ? '
    ]

    let selectSql = `select test.id,test.name,test.age,test.school,discribe.discribe 
                     from test,discribe 
                     where test.id = discribe.id = ?
    `

    let test = new Promise((resolve, reject) => {
        connection.query(addsql, params, (err, result) => {
            err ? reject(`插入失敗`) : resolve(result)
        })
    })
    let discribe = new Promise((resolve, reject) => {
        connection.query(addsql_t, params_t, (err, result) => {
            err ? reject(`插入失敗`) : resolve(result)
        })
    })

    Promise.all([test, discribe]).then(result => {
        connection.query(selectSql, [req.body.id], (err, results) => {
            if (err) res.json({
                code: 200,
                data: []
            });
            res.json({
                code: 200,
                data: results
            });
        })
    }).catch(err => {
        res.json({
            code: 100,
            data: '刪除失敗'
        });
    })
});

大功告成啦！

現(xiàn)在就需要去使用這些接口

我們可以直接在瀏覽器輸入 請(qǐng)求地址
查詢接口

image.png

有數(shù)據(jù)出來就說明請(qǐng)求成功啦??！
接下來我們還要測(cè)試 刪 改 查 三個(gè)接口，來寫一個(gè)html頁面測(cè)試

完整的app.js

const express = require('express');
const app = express();
const bodyParser = require('body-parser');
const mysql = require('mysql');
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'test',
    multipleStatements: true, //  允許執(zhí)行多條語句
})


connection.connect(function() {
    console.log('鏈接成功')
});

app.use(bodyParser.json());
app.use(bodyParser.urlencoded({
    extended: false
}))

//設(shè)置跨域訪問
app.all('*', function(req, res, next) {
    res.header("Access-Control-Allow-Origin", "*");
    res.header("Access-Control-Allow-Headers", "Content-Type, Content-Length, Authorization, Accept, X-Requested-With , yourHeaderFeild");
    res.header("Access-Control-Allow-Methods", "PUT,POST,GET,DELETE,OPTIONS");
    res.header("X-Powered-By", ' 3.2.1');
    res.header("Content-Type", "application/json;charset=utf-8");
    next();
});



//  查詢
app.get('/getdata', (req, res) => {
    let sql = ` select t.id,t.name,t.age,t.school,d.discribe
                from test as t
                join discribe as d
                on t.id = d.id
    `;
    connection.query(sql, (err, results) => {
        if (err) return res.json({
            code: 100,
            data: '沒有內(nèi)容'
        });
        res.json({
            code: 200,
            data: results
        });
    })
});

//  插入
app.post('/insertdata', (req, res) => {
    let params = [req.body.name, req.body.age, req.body.school] //  傳來的參數(shù)
    let params_t = [req.body.discribe] //  傳來的參數(shù)  
    let addsql = `insert into test(name,age,school) value (?,?,?)` //  插入語句，?代表插入的值，要插入到test表內(nèi)容
    let addsql_t = `insert into discribe(discribe) value (?)` //  傳插入語句，?代表插入的值，要插入到discribe表中

    let test = new Promise((resolve, reject) => {
        connection.query(addsql, params, (err, result) => {
            err ? reject(`插入失敗`) : resolve(result)
        })
    })
    let discribe = new Promise((resolve, reject) => {
        connection.query(addsql_t, params_t, (err, result) => {
            err ? reject(`插入失敗`) : resolve(result)
        })
    })

    Promise.all([test, discribe]).then(result => {
        //  last_insert_id是獲取表中最后一條數(shù)據(jù)
        connection.query('select last_insert_id()', (err, results) => {
            res.json({
                code: 200,
                data: {
                    id: results[0]['last_insert_id()'],
                    name: req.body.name,
                    age: parseInt(req.body.age),
                    school: req.body.name,
                    discribe: req.body.discribe
                }
            });
        })
    }).catch(err => {
        res.json({
            code: 100,
            data: `插入數(shù)據(jù)有誤`
        })
    })
});

//  刪除
app.post('/deletedata', (req, res) => {
    let [params, addsql, addsql_t] = [
        [req.body.id],
        'delete test from test where id = ? ',
        'delete discribe from discribe where id = ? '
    ]

    let test = new Promise((resolve, reject) => {
        connection.query(addsql, params, function(err, result) {
            err ? reject(`刪除失敗`) : resolve(result)
        })
    })
    let discribe = new Promise((resolve, reject) => {
        connection.query(addsql_t, params, function(err, result) {
            err ? reject(`刪除失敗`) : resolve(result)
        })
    })

    Promise.all([test, discribe]).then(result => {
        res.json({
            code: 200,
            data: `刪除成功`
        });
    }).catch(err => {
        res.json({
            code: 100,
            data: '刪除失敗'
        });
    })
});

//  修改
app.post('/updatedata', (req, res) => {
    let [params, params_t, addsql, addsql_t] = [
        [req.body.name, req.body.age, req.body.school, req.body.id],
        [req.body.discribe, req.body.id],
        'update test set name = ? , age = ? , school = ? where id = ? ',
        'update discribe set discribe = ? where id = ? '
    ]

    let selectSql = `select test.id,test.name,test.age,test.school,discribe.discribe 
                     from test,discribe 
                     where test.id = discribe.id = ?
    `

    let test = new Promise((resolve, reject) => {
        connection.query(addsql, params, (err, result) => {
            err ? reject(`插入失敗`) : resolve(result)
        })
    })
    let discribe = new Promise((resolve, reject) => {
        connection.query(addsql_t, params_t, (err, result) => {
            err ? reject(`插入失敗`) : resolve(result)
        })
    })

    Promise.all([test, discribe]).then(result => {
        connection.query(selectSql, [req.body.id], (err, results) => {
            if (err) res.json({
                code: 200,
                data: []
            });
            res.json({
                code: 200,
                data: results
            });
        })
    }).catch(err => {
        res.json({
            code: 100,
            data: '刪除失敗'
        });
    })
});


//配置服務(wù)端口 
var server = app.listen(3000, function() {
    const hostname = 'localhost';
    const port = 3000;
    console.log(`Server running at http://${hostname}:${port}/`);
})

新建html頁命名為 test.html
寫入一下內(nèi)容

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <script src="https://cdn.jsdelivr.net/npm/vue"></script>
    <script src="https://unpkg.com/axios/dist/axios.min.js"></script>
    <!-- 引入樣式 -->
    <link rel="stylesheet" >
    <!-- 引入組件庫 -->
    <script src="https://unpkg.com/element-ui/lib/index.js"></script>
    <title>Document</title>
</head>

<style>
    body {
        background-color: #f7f7f7;
        margin: 0;
    }
    
    #app {
        padding: 20px;
        background-color: #fff;
        box-shadow: 0 1px 4px #e7e7e7, 0 5px 40px #e9e9e9 inset;
    }
</style>

<body>
    <div id="app">
        <template>
            <el-button 
            type="primary" 
            @click="add" 
            style="margin-bottom:20px;" 
            size="medium">
            添加數(shù)據(jù)
            </el-button>
            <el-table
                :data="tableData"
                style="width: 100%">
                <el-table-column
                prop="name"
                label="名字"
                width="180">
                </el-table-column>
                <el-table-column
                prop="age"
                label="年齡"
                width="180">
                </el-table-column>
                <el-table-column
                prop="school"
                label="學(xué)校"
                width="180">
                </el-table-column>
                <el-table-column
                prop="discribe"
                label="介紹">
                </el-table-column>
                <el-table-column
                    fixed="right"
                    label="操作"
                    width="200">
                    <template slot-scope="scope">
                        <el-button @click="handleEdit(scope.row)" size="small">編輯</el-button>
                        <el-button type="danger" size="small" @click="handleDelete(scope.row)">刪除</el-button>
                    </template>
        </el-table-column>
        </el-table>

        <el-dialog title="填寫信息" :visible.sync="dialogFormVisible" :before-close="cancel">
            <el-form :model="form">
                <el-form-item label="名字" label-width="200">
                    <el-input v-model="form.name" placeholder="請(qǐng)輸入名字"></el-input>
                </el-form-item>
                <el-form-item label="年齡" label-width="200">
                    <el-input v-model="form.age" placeholder="請(qǐng)輸入年齡" type="number" min="0"></el-input>
                </el-form-item>
                <el-form-item label="學(xué)校" label-width="200">
                    <el-input v-model="form.school" placeholder="請(qǐng)輸入學(xué)校"></el-input>
                </el-form-item>
                <el-form-item label="介紹" label-width="200">
                    <el-input v-model="form.discribe" placeholder="請(qǐng)輸入介紹"></el-input>
                </el-form-item>
            </el-form>
            <div slot="footer" class="dialog-footer">
                <el-button @click="cancel">取 消</el-button>
                <el-button type="primary" @click="submit">確 定</el-button>
            </div>
        </el-dialog>
        </template>
    </div>

    <script>
        new Vue({
            el: '#app',
            data: {
                tableData: [],
                dialogFormVisible: false,
                form: {
                    name: '',
                    age: '',
                    school: '',
                    discribe: ''
                },
                submitState: 0,
            },
            mounted() {
                this.getData()
            },
            methods: {
                //  簡(jiǎn)單封裝g請(qǐng)求 url:請(qǐng)求地址  params：參數(shù)   showNotify：是否顯示錯(cuò)誤彈出框 默認(rèn)顯示
                getAxios(url, params, showNotify) {
                    return new Promise((resolve, reject) => {
                        axios({
                            method: params ? 'post' : 'get',
                            url: url,
                            data: params
                        }).then(res => {
                            if (res.data.code == 200) {
                                resolve(res)
                            } else {
                                reject(`${res.data.data}`)
                                this.$notify({
                                    title: '警告',
                                    message: res.data.data,
                                    type: 'warning'
                                })
                            }
                        }).catch(err => {
                            reject(err)
                            showNotify ? '' : this.$notify({
                                title: '警告',
                                message: err,
                                type: 'warning'
                            })
                        })
                    })
                },
                //  獲取數(shù)據(jù)
                getData() {
                    this.getAxios(`http://localhost:3000/getdata`).then(res => {
                        this.tableData = res.data.data
                    })
                },
                //  編輯
                handleEdit(i) {
                    this.submitState = 1
                    this.form = i
                    this.dialogFormVisible = true
                },
                //  刪除
                handleDelete(i) {
                    this.getAxios(`http://localhost:3000/deletedata`, {
                        id: i.id
                    }).then(res => {
                        this.tableData.splice(this.tableData.findIndex(e => e.id == i.id), 1)
                        this.dialogFormVisible = false
                    })
                },
                //  提交編輯
                submit() {
                    if (this.submitState == 0) {
                        this.getAxios(`http://localhost:3000/insertdata`, this.form).then(res => {
                            this.tableData.push(res.data.data)
                            this.dialogFormVisible = false
                        })
                    } else {
                        this.getAxios(`http://localhost:3000/updatedata`, this.form).then(res => {
                            this.tableData[this.tableData.findIndex(e => e.id == this.form.id)] = res.data.data
                            this.dialogFormVisible = false
                        })
                    }

                },
                //  添加
                add() {
                    this.submitState = 0
                    this.dialogFormVisible = true
                },
                cancel() {
                    this.form = {
                        name: '',
                        age: '',
                        school: ''
                    }
                    this.dialogFormVisible = false
                },
            }
        })
    </script>
</body>

</html>

一個(gè)簡(jiǎn)單的node.js配合vue+element+axios的增刪改查demo就寫好了。
但這是遠(yuǎn)遠(yuǎn)不夠，app.js里面如果還有很多接口，在一個(gè)頁面中是難以維護(hù)的，那么就需要用到express中的router進(jìn)行路由管理，不同的需求模塊用不同的文件來管理路由請(qǐng)求。

那么可以看一看下一章，簡(jiǎn)單介紹express路由的使用
express路由router

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容