Explain nodejs implementation of database addition, deletion, modification and query

Node.js (Node) is a JavaScript runtime environment built on the Chrome V8 engine. It allows JavaScript to run on the server side and is efficient, lightweight, and cross-platform. Currently, Node has become a very important back-end development language.

This article aims to introduce how to use Node.js to implement additions, deletions, modifications and queries of the database, especially to operate relational databases. Here we take MySQL as an example to explain.

1. MySQL installation and importing data

First, we need to install the MySQL database and create some test data.

After installing MySQL, you need to log in to the MySQL server. We can use the MySQL command line tool or graphical tools such as phpMyAdmin, Navicat, etc. Use the following command on the command line to enter MySQL:

$ mysql -u root -p

It will prompt you to enter the password, enter it and press Enter.

Then execute the following commands in the MySQL command line to create a database named test and a table named user:

mysql> create database test;
mysql> use test;
mysql> create table user(id int not null auto_increment, name varchar(255), age int, primary key (id));
mysql> insert into user(name, age) values('Tom', 18);
mysql> insert into user(name, age) values('Jack', 25);
mysql> insert into user(name, age) values('Lucy', 27);

Here we create a database named test, and then A table named user is created in the database and three pieces of data are inserted into the table.

2. Install the MySQL module

Node.js officially provides the mysql module, which can easily operate the MySQL database. Before using the mysql module, you need to install it. Use the following command to install it:

$ npm install mysql --save

3. Connect to the database

Before connecting to the MySQL database and performing operations, you need to create a connection.

You can use the following method to connect to the MySQL database:

var mysql = require('mysql');
var connection = mysql.createConnection({
    host: 'localhost', // 数据库IP地址
    user: 'root',      // 数据库用户名
    password: '123456',// 数据库密码
    database: 'test'   // 数据库名称
});

connection.connect(function(err) {
    if (err) {
        console.error('error: ' + err.stack);
        return;
    }

    console.log('connected as id ' + connection.threadId);
});

//断开连接
connection.end();

Here we create a connection object and pass in the database connection parameters. By calling the connection.connect() function, we can try to connect to the database. If the connection cannot be made, an error message will be output, otherwise a successful connection message will be output. The last line connection.end() is used to disconnect from the database.

Next, let’s take a look at how to use Node.js to implement common data operations.

4. Insert data

The method of inserting data using MySQL is INSERT, and the method of inserting data using the mysql module in Node.js is:

var insertSql = 'insert into user(name, age) values (?,?)';
var insertParams = ['Tom', 20];
connection.query(insertSql, insertParams, function(err, result) {
    if (err) {
        console.log('[INSERT ERROR] - ', err.message);
        return;
    }

    console.log('-------INSERT----------');
    console.log('INSERT ID:', result.insertId);
    console.log('INSERT ID:', result.affectedRows);
    console.log('#######################');
});

Here? is the statement placeholder, and the following insertParams are the inserted parameters. In the callback function, if an error occurs, error information is output. If the insertion is successful, you can get the ID of the inserted data from result.resultId and the number of affected rows from result.affectedRows.

5. Query data

The method to query MySQL data is SELECT. The sample code for querying in Node.js is as follows:

var selectSql = 'SELECT * FROM user';
connection.query(selectSql, function(err, rows) {
    if (err) {
        console.log('[SELECT ERROR] - ', err.message);
        return;
    }

    console.log('-------SELECT----------');
    console.log(rows);
    console.log('#######################');
});

here * is a wildcard character, indicating that all columns are returned. rows is the query result returned by MySQL. It is an array that contains all query results. You can use a loop to iterate through the array to get the details of the query results.

6. Update data

The method to update data in MySQL is UPDATE. The code for using the mysql module in Node.js is as follows:

var updateSql = 'UPDATE user SET age = ? WHERE name = ?';
var updateParams = [22, 'Tom'];
connection.query(updateSql, updateParams, function(err, result) {
    if (err) {
        console.log('[UPDATE ERROR] - ', err.message);
        return;
    }

    console.log('-------UPDATE----------');
    console.log('UPDATE affectedRows', result.affectedRows);
    console.log('#######################');
});

The field names here need to be wrapped in backticks.

7. Delete data

The method to delete data in MySQL is DELETE. The sample code used in Node.js is as follows:

var deleteSql = 'DELETE FROM user WHERE name = ?';
var deleteParams = ['Tom'];
connection.query(deleteSql, deleteParams, function(err, result) {
    if (err) {
        console.log('[DELETE ERROR] - ', err.message);
        return;
    }

    console.log('-------DELETE----------');
    console.log('DELETE affectedRows', result.affectedRows);
    console.log('#######################');
});

Summary

Node.js provides very convenient functions for operating MySQL databases. I believe this article will be helpful to friends who want to learn Node.js. In actual application, development needs to be combined with your own business needs, and is for reference only.

The above is the detailed content of Explain nodejs implementation of database addition, deletion, modification and query. For more information, please follow other related articles on the PHP Chinese website!