Basic tutorial on operating MySQL database in Node.js

This article brings you a basic tutorial on operating the MySQL database in Node.js. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

This article is a basic tutorial on using the npm module mysql to operate the MySQL database. It does not involve the installation and configuration of MySQL. If MySQL is not installed on the computer, It is recommended to install integrated environments such as WAMP and XAMPP. This article also uses the lightweight Node.js framework Koa to build a web program in order to simulate project scenarios through front-end browser requests. You can easily read this article without mastering the syntax of the Koa framework.

Initialize the project

Create the project directory and use npm initAfter initializing the project, perform the following operations:

Install dependencies

npm install mysql koa koa-router

Create index.js

// index.js

const Koa = require('koa');
const Router = require('koa-router');
const mysql = require('mysql');

const app = new Koa();
const router = new Router();

const connection = mysql.createConnection({
  host: 'localhost', // 填写你的mysql host
  user: 'root', // 填写你的mysql用户名
  password: '123456' // 填写你的mysql密码
})

connection.connect(err => {
  if(err) throw err;
  console.log('mysql connncted success!');
})

router.get('/', ctx => {
  ctx.body = 'Visit index';
})
app.use(router.routes());

app.listen(3000);

Execute node index.js in the shell. When you see mysql connected success! printed in the shell, it indicates that the MySQL database connection is successful.

Open the browser and visit localhost:3000. When you see Visit index displayed on the screen, the table name Project initialization was successful.

Database operation

Create database

When accessing /createdb, create a mysqlkoa database, code As follows:

router.get('/createdb', ctx => {
  return new Promise(resolve => {
    const sql = `CREATE DATABASE mysqlkoa`;

    connection.query(sql, (err) => {
      if (err) throw err;
      ctx.body = {
        code: 200,
        msg: `create database mysqlkoa success!`
      }
      resolve();
    });
  })
})

Re-execute node index.js, and use the browser to access localhost:3000/createdb

<img src="/static/imghw/default1.png" data-src="https://img.php.cn//upload/image/332/781/973/1540792367149822.png" class="lazy" title="1540792367149822.png" alt="Basic tutorial on operating MySQL database in Node.js">

Create a data table

For convenience, we directly use the database we just created when connecting. We need to add the database:mysqlkoa configuration item in mysql.createConnection.

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '123456',
  database: 'mysqlkoa' // 添加该列
})

When accessing /createtable, we create a data table fe_frame, which is used to save the data of the front-end frame:

router.get('/createtable', ctx => {
  return new Promise(resolve => {
    const sql = `CREATE TABLE fe_frame(
      id INT(11) AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(255),
      author VARCHAR(255)
    )`;
    connection.query(sql, (err ,results, filelds) => {
      if (err) throw err;
      ctx.body = {
        code: 200,
        msg: `create table of fe_frame success!`
      }
      resolve();
    })
  })
})

Re-execute node index.js and use the browser to access localhost :3000/createtable

<img src="/static/imghw/default1.png" data-src="https://img.php.cn//upload/image/332/781/973/1540792367149822.png" class="lazy" title="1540792367149822.png" alt="Basic tutorial on operating MySQL database in Node.js">

Insert data

Insert a single piece of data

When accessing/insert, used to insert a single piece of data:

router.get('/insert', ctx => {
  return new Promise(resolve => {
    const sql = `INSERT INTO fe_frame(name, author)
    VALUES('vue', 'Evan')`;
    connection.query(sql, (err) => {
      if (err) throw err;
      ctx.body = {
        cde: 200,
        msg: `insert data to fe_frame success!`
      }
      resolve();
    })
  })
})

Re-execute node index.js, and use the browser to access localhost:3000/insert

Insert multiple pieces of data

When accessing /insertmulti, it is used to insert multiple pieces of data:

router.get('/insertmulti', ctx => {
  return new Promise(resolve => {
    const sql = `INSERT INTO fe_frame(name, author)
    VALUES ?`;
    const values = [
      ['React', 'Facebook'],
      ['Angular', 'Google'],
      ['jQuery', 'John Resig']
    ];
    connection.query(sql, [values], (err, result) => {
      if (err) throw err;
      ctx.body = {
        code: 200,
        msg: `insert ${result.affectedRows} data to fe_frame success!`        
      }
      resolve();
    })
  })
})

Re-execute node index.js and use Browser access localhost:3000/insertmulti

Use phpMyAdmin to access, you can see that the mysqlkoa table is as follows

Delete data

When accessing /delete, delete the corresponding row. We use the request parameter name to specify which frame to delete, and use ctx.query.name to obtain it on the server side. The code is as follows:

router.get('/delete', ctx => {
  return new Promise(resolve => {
    const name = ctx.query.name;
    const sql = `DELETE FROM fe_frame WHERE name = '${name}'`;
    connection.query(sql, (err, result) => {
      if (err) throw err;
      ctx.body = {
        code: 200,
        msg: `delete ${result.affectedRows} data from fe_frame success!`
      };
      resolve();
    })
  })
})

Re-execute node index.js, and use the browser to access http://localhost: 3000/delete?name=jQuery

Modify data

When accessing /update, the author of the updated vue framework is named Evan You, the code is as follows:

router.get('/update', ctx => {
  return new Promise(resolve => {
    const sql =  `UPDATE fe_frame SET author = 'Evan You' WHERE NAME = 'vue'`;
    connection.query(sql, (err, result) => {
      if (err) throw err;
      ctx.body = {
        code: 200,
        msg: `update ${result.affectedRows} data from fe_frame success!`
      };
      resolve();
    })
  })
})

Re-execute node index.js, and use the browser to access http://localhost:3000/update

Find data

When accessing /select, obtain items that meet the conditions of the frame name in the request parameters. The code is as follows:

router.get('/select', ctx => {
  return new Promise(resolve => {
    let name = ctx.query.name;
    const sql = `SELECT * FROM fe_frame WHERE name = '${name}'`;
    connection.query(sql, (err, result) => {
      if (err) throw err;
      ctx.body = {
        code: 200,
        data: result
      }
      resolve();
    })
  })
})

Re-execute node index.js, and Use a browser to access http://localhost:3000/select?name=vue

The above is the detailed content of Basic tutorial on operating MySQL database in Node.js. For more information, please follow other related articles on the PHP Chinese website!