node增删改查

作者: smallzip | 来源:发表于2019-06-13 23:35 被阅读0次

安装 express
npm install express --save
安装 mysql
npm install mysql --save
要引入mysql的同时还需要req.body对表单数据进行解析所以还需引入body-parser，所以需要安装
安装body-parser
npm install body-parser --save
开启本地mysql

我使用的是XAMPP集成，在网上可以直接下载
这个是下载地址： XAMPP

下载完成之后开启mysql

image.png

现在开启了mysql还不能管理
需要下载navicat for mysql来管理我们的数据表

image.png

navicat for mysql下载地址
没有注册过的有免费试用期

下载好了navicat之后，进行本地连接

image.png

下面新建一个表，我新建的表名叫 test
在test表里面有四个字段，自己可以写上数据，方便一会儿写查询接口

再新建一个表叫 discribe ，新建这个表是为了多表查询

image.png

下面就可以写接口了

新建一个文件叫做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, //  允许执行多条语句
})


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


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

//设置跨域访问
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();
});

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

执行文件

打开当前文件夹的控制台，输入 node app.js，可以看到连接成功的字样就代表可以连接啦~~~

image.png

第一个查询接口

使用的是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: '没有内容'
        });
        res.json({
            code: 200,
            data: results
        });
    })
});

第一个插入接口

//  插入
app.post('/insertdata', (req, res) => {
    let params = [req.body.name, req.body.age, req.body.school] //  传来的参数
    let params_t = [req.body.discribe] //  传来的参数  
    let addsql = `insert into test(name,age,school) value (?,?,?)` //  插入语句，?代表插入的值，要插入到test表内容
    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是获取表中最后一条数据
        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: `插入数据有误`
        })
    })
});

第一个删除接口

//  删除
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.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: '删除失败'
        });
    })
});

大功告成啦！

现在就需要去使用这些接口

我们可以直接在浏览器输入请求地址
查询接口

image.png

有数据出来就说明请求成功啦！！
接下来我们还要测试删改查三个接口，来写一个html页面测试

完整的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, //  允许执行多条语句
})


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

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

//设置跨域访问
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: '没有内容'
        });
        res.json({
            code: 200,
            data: results
        });
    })
});

//  插入
app.post('/insertdata', (req, res) => {
    let params = [req.body.name, req.body.age, req.body.school] //  传来的参数
    let params_t = [req.body.discribe] //  传来的参数  
    let addsql = `insert into test(name,age,school) value (?,?,?)` //  插入语句，?代表插入的值，要插入到test表内容
    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是获取表中最后一条数据
        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: `插入数据有误`
        })
    })
});

//  删除
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: '删除失败'
        });
    })
});


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

新建html页命名为 test.html
写入一下内容

<!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" href="https://unpkg.com/element-ui/lib/theme-chalk/index.css">
    <!-- 引入组件库 -->
    <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">
            添加数据
            </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="学校"
                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="请输入名字"></el-input>
                </el-form-item>
                <el-form-item label="年龄" label-width="200">
                    <el-input v-model="form.age" placeholder="请输入年龄" type="number" min="0"></el-input>
                </el-form-item>
                <el-form-item label="学校" label-width="200">
                    <el-input v-model="form.school" placeholder="请输入学校"></el-input>
                </el-form-item>
                <el-form-item label="介绍" label-width="200">
                    <el-input v-model="form.discribe" placeholder="请输入介绍"></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: {
                //  简单封装g请求 url:请求地址  params：参数   showNotify：是否显示错误弹出框 默认显示
                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'
                            })
                        })
                    })
                },
                //  获取数据
                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>

一个简单的node.js配合vue+element+axios的增删改查demo就写好了。
但这是远远不够，app.js里面如果还有很多接口，在一个页面中是难以维护的，那么就需要用到express中的router进行路由管理，不同的需求模块用不同的文件来管理路由请求。

那么可以看一看下一章，简单介绍express路由的使用
express路由router

node增删改查

下面就可以写接口了

新建一个文件叫做app.js

执行文件

第一个查询接口

第一个插入接口

第一个删除接口

第一个修改接口

大功告成啦！

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读