nodejs related query

Node.js is a JavaScript runtime environment for building high-performance, high-concurrency, scalable, and reliable network applications. It is a very common requirement to perform database operations in Node.js, and query is one of the most commonly used functions in database operations. In actual development, multi-table related queries need to be frequently performed. This article will introduce how to implement multi-table related queries in Node.js.

1. Database connection

Performing database operations in Node.js requires the use of a third-party library. This article uses MySQL as an example and uses the MySQL client library to perform database operations. , you need to use the following code to connect:

const mysql = require('mysql');

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

connection.connect();

The above code uses the createConnection method of the mysql module to create a MySQL connection object, and then calls connect Method to connect to the database. Among them, host is the host name where the database is located, user is the user name used to log in to the database, password is the password, and database is the username used to log in to the database. The name of the connected database.

2. Single table query

Before performing multi-table related queries, you first need to understand how to perform single table queries. Using the MySQL client library, you can use the query method to send SQL statements to the database, as follows:

connection.query('SELECT name, age FROM users', (error, results, fields) => {
    if (error) throw error;
    console.log(results);
});

connection.end();

The above code queries the name in the users table and age fields and output them to the console. query The first parameter of the method is the SQL statement to be executed, and the second parameter is a callback function used to process the query results. The first parameter of the callback function is the error that occurred during the query process, the second parameter is the query result, and the third parameter is the description information of the query field.

3. Multi-table related query

Before performing multi-table related query, you need to understand several JOIN statements:

• INNER JOIN: Returns the rows that meet the conditions in both tables;
• LEFT JOIN: Returns all the rows in the left table, the rows in the right table that meet the conditions, and returns NULL if there are no rows in the right table that meet the conditions;
• RIGHT JOIN: Returns all rows in the right table, rows in the left table that meet the conditions, and returns NULL if there are no rows in the left table that meet the conditions;
• FULL OUTER JOIN (MySQL does not support ): Returns all rows in the two tables, and any rows that do not match are NULL.

Using multi-table related queries requires the use of JOIN statements and ON clauses to specify the conditions to be related. The following is an example of a multi-table related query:

connection.query('SELECT users.name, orders.id FROM users INNER JOIN orders ON users.id = orders.user_id', (error, results, fields) => {
    if (error) throw error;
    console.log(results);
});

connection.end();

The above code queries the data in the users table and the orders table, and uses INNER JOIN to associate two tables. The association condition is users.id = orders.user_id, that is, the id field of the users table and the user_id# of the orders table ## Fields are associated. The query results include the values ​​of the two fields users.name and orders.id.

Multiple table related queries

connection.query('SELECT users.name, orders.id, order_items.product_id FROM users INNER JOIN orders ON users.id = orders.user_id INNER JOIN order_items ON orders.id = order_items.order_id', (error, results, fields) => {
    if (error) throw error;
    console.log(results);
});

connection.end();

users table, orders table and order_items table , and use INNER JOIN to join the three tables. The associated conditions are users.id = orders.user_id and orders.id = order_items.order_id. The query results include the values ​​of the three fields users.name, orders.id and order_items.product_id.

Summary

The above is the detailed content of nodejs related query. For more information, please follow other related articles on the PHP Chinese website!