How to operate mysql with nodejs

Node.js is a very powerful JavaScript runtime environment that can be used to develop various types of applications, including web applications, command line tools, and various back-end services. Its power lies in its rich modular ecosystem, which includes many modules for database connection and management. Among them, the mysql module is used to connect to the MySQL database.

In this article, we will introduce how to use Node.js to connect and manage MySQL database. The steps are roughly as follows:

1. Install Node.js and MySQL database.
2. Install the mysql module.
3. Connect to MySQL database.
4. Execute SQL query.
5. Insert, update or delete data.

Next, we will introduce it step by step.

Install Node.js and MySQL database

First, you need to make sure that Node.js and MySQL database are installed on your computer. If you have not installed these two software, you can visit the following link to download:

• Node.js: https://nodejs.org/
• MySQL: https://www .mysql.com/downloads/

Node.js installation is very simple. Just download the installation package of the corresponding version and follow the prompts to install it.

The installation of MySQL is also very simple. You just need to follow the prompts to download and install. When the installation is complete, you will need to set a root password. This password will be used to authenticate you when connecting to the MySQL database in Node.js. In this article, we assume that the root password is yourPassword.

Install the mysql module

Once you have installed Node.js, you can use npm (Node.js package manager) to install the mysql module. Open a terminal (or command line) and run the following command:

npm install mysql

This command will install the mysql module and add it to your project dependencies.

Connecting to a MySQL database

Next, we need to create a MySQL database and write code in Node.js to connect to the database. We will use the following code in Node.js to connect to the database:

const mysql = require('mysql');

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

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

In this code, we first import the mysql module. Next, we use the createConnection() method to create a connection object that contains some information needed to connect to the database, such as hostname, username, password, and database name. Here, we use localhost as the hostname, root as the username, yourPassword as the password, and mydatabase as the database name.

Once we have established the connection object, we will use the connect() method to connect to the database. This method will issue a connection request and wait for a response. If the request is successful, a "Connected!" message is printed; otherwise, an error is thrown and an error message is printed.

Execute SQL Query

Now, we have successfully connected to the MySQL database. We will use the mysql module in Node.js to execute SQL queries. First, we'll cover how to execute a SELECT query. The following is a sample code:

connection.query('SELECT * FROM users', (err, results) => {
  if (err) throw err;
  console.log(results);
});

In this code, we use the query() method of the connection object to perform a SELECT query. This method accepts two parameters: the SQL query to be executed and a callback function. When the query is complete, the callback function will be called and the query results will be returned as the second parameter. If an error occurs, an error is thrown and an error message is printed.

Insert, update or delete data

In addition to querying data, we can also use Node.js and the mysql module to insert, update or delete data. The following is a sample code:

// 插入数据
const user = { name: 'John', email: 'john@example.com' };
connection.query('INSERT INTO users SET ?', user, (err, results) => {
  if (err) throw err;
  console.log('Inserted!');
});

// 更新数据
const newEmail = 'newEmail@example.com';
connection.query('UPDATE users SET email = ? WHERE name = ?', [newEmail, 'John'], (err, results) => {
  if (err) throw err;
  console.log('Updated!');
});

// 删除数据
connection.query('DELETE FROM users WHERE name = ?', 'John', (err, results) => {
  if (err) throw err;
  console.log('Deleted!');
});

In this code, we first define an object named user that contains the data we want to insert. We use INSERT INTO statement to insert data. Here we use SET clause to set the data. When the insertion is successful, we print the "Inserted!" message.

We use the UPDATE statement to update data. Here, we use the WHERE clause to specify the rows to be updated. When the update is successful, we will print the "Updated!" message.

Finally, we use the DELETE statement to delete data. Here we use WHERE clause to specify the rows to be deleted. When the deletion is successful, we will print the "Deleted!" message.

Summary

In this article, we introduced how to use Node.js and the mysql module to connect and manage a MySQL database. We discussed how to install Node.js and a MySQL database and connect to the database using the mysql module. We also demonstrated how to perform SQL queries and insert, update, or delete data.

The sample code in this article will be useful if you are developing a Node.js application and need to connect to a MySQL database. Don’t forget to refer to the official documentation and community resources for more information and guidance on Node.js and the mysql module.

The above is the detailed content of How to operate mysql with nodejs. For more information, please follow other related articles on the PHP Chinese website!