Node.js adds, deletes, modifies and checks the actual combat record of MySQL database

//Install the mysql third-party module npm i mysql
//Install the express third-party module npm i express

// Import express
const express = require('express');
// Create the server const app = express();
// Start the server app.listen(80, () => {
    console.log('http://127.0.0.1');
})

// 1. Import mysql
const mysql = require('mysql');
// 2. Establish a connection with the MySQL database var db = mysql.createPool({
    host: '127.0.0.1', // database IP address user: 'root', // database login account password: '123456', // database login password database: 'web67' // specify which database to operate });

// Test whether the mysql module can run normally, find all data and display it on the page db.query('select * from one', (err, data) => {
        if (err) return console.log(err.message);
        if (data.length === 0) return console.log('There is no data in the database');
        console.log(data) //data is the data found in the database})
    });

// Get data from the database app.get('/selectuser', (req, res) => {
    // Check if the database connection is successful db.query('select * from one', (err, data) => {
        //If err is not empty, it indicates an error if (err) return console.log(err.message);
        if (data.length === 0) return console.log('Failed to obtain');
        res.send(data)
    })
});

// Add data to the database app.post('/insertuser', (req, res) => {
    // Receive data requested by the client const body = req.body;
    // Build SQL statement const sql = 'insert into one set?'
        //Insert the data requested by the customer into the database db.query(sql, body, (err, data) => {
        if (err) return console.log(err.message);
        if (data.affectedRows !== 1) return console.log('Add failed');
        res.send({
            status: 0,
            msg: 'Add data successfully'
        })
    })
})

// Modify the data in the database app.post('/updateuser', (req, res) => {
    //Receive data requested by the client const body = req.body;
    //Build the modified sql statement const sql = 'update one set uname=? where id=?';
    db.query(sql, [body.uname, body.id], (err, data) => {
        if (err) return console.log(err.message);
        if (data.affectedRows !== 1) return console.log('Modification failed');
        res.send({
            status: 0,
            msg: 'Data modification successful'
        })
    })
})

// Delete data in the database app.get('/deleteuser/:id', (req, res) => {
    // Get the data submitted by the client, req.params matches the dynamic parameter through the colon: const id = req.params.id;
    // Build the SQL statement for deletion. Generally, in order to prevent data from being permanently deleted, we will modify the status code corresponding to the data to 0 through update
    const sql = 'update one set status=0 where id=?';
    // Execute sql
    db.query(sql, id, (err, data) => {
        if (err) return console.log(err.message);
        if (data.affectedRows !== 1) return console.log('deletion failed');
        res.send({
            status: 0,
            msg: 'Deleted successfully'
        })
    })
})

// Use express middleware to add, delete, modify and check the MySQL database const express = require('express');
// Create a server const app = express();
// Parse the data requested by the client app.use(express.urlencoded({ extended: false }));
//Introduce mysql operation database const mysql = require('mysql');
// Configure the database const db = mysql.createPool({
    host: '127.0.0.1', //database ip address user: 'root', //database account password: '123456', //database password database: 'web67' //database name });
 
// Get data from the database app.get('/selectuser', (req, res) => {
    // Check if the database connection is successful db.query('select * from one', (err, data) => {
        if (err) return console.log(err.message);
        if (data.length === 0) return console.log('Failed to obtain');
        res.send(data)
    })
});
 
// Add data to the database app.post('/insertuser', (req, res) => {
    // Receive data requested by the client const body = req.body;
    // Build SQL statement const sql = 'insert into one set?'
        //Insert the data requested by the customer into the database db.query(sql, body, (err, data) => {
        if (err) return console.log(err.message);
        if (data.affectedRows !== 1) return console.log('Add failed');
        res.send({
            status: 0,
            msg: 'Add data successfully'
        })
    })
})
 
// Modify the data in the database app.post('/updateuser', (req, res) => {
    const body = req.body;
    const sql = 'update one set uname=? where id=?';
    db.query(sql, [body.uname, body.id], (err, data) => {
        if (err) return console.log(err.message);
        if (data.affectedRows !== 1) return console.log('Modification failed');
        res.send({
            status: 0,
            msg: 'Data modification successful'
        })
    })
})
 
// Delete data in the database (specify id)
app.get('/deleteuser/:id', (req, res) => {
        const id = req.params.id; //id is a dynamic parameter, so it must be obtained through req.params const sql = 'update one set status=0 where id=?'
        db.query(sql, id, (err, data) => {
            if (err) return console.log(err.message);
            if (data.affectedRows !== 1) return console.log('Failed to delete data');
            res.send({
                status: 0,
                msg: 'Deleted successfully'
            })
        })
    })
    // Start the server app.listen(80, () => {
    console.log('http://127.0.0.1');
})