How to use MySQL database query statements in Node.js

With the continuous development of technology, the requirements of Web applications are becoming more and more complex, and the processing of data is also becoming more and more complex. The database is the most important part of a Web application and is the core for data storage, retrieval, and management. In Node.js, it is very common to use the MySQL database. In this article, we will discuss how to use MySQL database query statements in Node.js.

In Node.js, you can use the mysql package of Node.js to create a database connection and execute Query statements. This package can be installed via npm, or you can download and install it manually. Here are the commands to install the mysql package:

npm install mysql

Once you have installed the mysql package, you can start using it to create database connections. In this article, we will use the following code to create a database connection:

var mysql = require('mysql');

var connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'database_name'
});

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

    console.log('Connected to database as ID ' + connection.threadId);
});

In the above code, we use the createConnection method of the node.js mysql module to connect to the mysql database. You need to create this connection with the correct host, username, password, and database name. Once the connection is successful, you can use the connection object to execute Query statements.

In Node.js, MySQL query statements are very common. These query statements allow you to retrieve data from the database, filter data, sort data, and perform aggregation operations. The following are some sample query statements.

SELECT statement

The SELECT statement is used to retrieve data from the database. Following is the sample code to retrieve data using SELECT:

var sql = "SELECT * FROM customers";

connection.query(sql, function(err, result) {
    if (err) throw err;
    console.log(result);
});

In the above code, we are using SELECT * to retrieve all the data from the customers table of the database. If the query is successful, we will print out the results on the console.

WHERE statement

The WHERE clause allows you to specify conditions to filter the data that needs to be retrieved. The following is the sample code using WHERE clause:

var sql = "SELECT * FROM customers WHERE address = 'Park Lane 38'";

connection.query(sql, function(err, result) {
    if (err) throw err;
    console.log(result);
});

In the above code, we use WHERE clause to specify the conditions for the data that need to be retrieved from the database. In this case, we only retrieve customer data for the address "Park Lane 38".

ORDER BY statement

The ORDER BY statement is used to sort data by specified columns. Following is the sample code using ORDER BY:

var sql = "SELECT * FROM customers ORDER BY name DESC";

connection.query(sql, function(err, result) {
    if (err) throw err;
    console.log(result);
});

In the above code, we are using ORDER BY clause to sort the results by name in descending order. Descending order is achieved by adding the DESC keyword after the column name.

LIMIT statement

The LIMIT statement is used to limit the number of records returned. The following is a sample code using LIMIT:

var sql = "SELECT * FROM customers LIMIT 5";

connection.query(sql, function(err, result) {
    if (err) throw err;
    console.log(result);
});

In the above code, we use the LIMIT clause to limit the number of rows returned in the result set to 5.

UPDATE statement

The UPDATE statement is used to update data in the database. The following is a sample code using UPDATE:

var sql = "UPDATE customers SET address = 'Canyon 123' WHERE address = 'Valley 345'";

connection.query(sql, function(err, result) {
    if (err) throw err;
    console.log(result.affectedRows + " record(s) updated");
});

In the above code, we use the UPDATE statement to update the address of the customer data with the address "Valley 345" to "Canyon 123".

The above are some commonly used MySQL query statements. In your Node.js application, you can use these queries to retrieve, filter, sort, aggregate, and update data. Of course, this is just the beginning, you can use more query statements to complete more operations, and use the mysql package of Node.js to complete the task easily.

The above is the detailed content of How to use MySQL database query statements in Node.js. For more information, please follow other related articles on the PHP Chinese website!