This article explains how Node.js connects MySQL and MongoDB, and realizes the basic function of add, delete, change and check.
Installation of software tools such as MySQL and MongoDB is not covered here.
A, MySQL,
1, design table
First, design the table using a visual tool, and then add a few test data:
2. Install node. js to connect to MySQL
npm i mysql -d
Copy the code3, connect to MySQL
MySQL.js
Const mysql = require(const mysql = require('mysql'); // Mysql connection informationlet connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'root',
database: 'test', port: 3306 }); // Start connection connection.connect(); // Query info_test table connection.query('SELECT * FROM info_test', (error, results, fields) => {
if(error) throw error; console.log(results); }); Connection.end ();Copy the codeRun node mysql.js, the connection is successful:
Next, it’s time to implement the function of adding, deleting, changing, and checking.
4. Database operation: add, delete, change and check
4.1,
add.js
const mysql = require('mysql');
let connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'root',
database: 'test', port: 3306 }); connection.connect(); // Set the SQL insert statementlet addSql = 'INSERT INTO info_test(id,name,age) VALUES(0,? ,?) '; // Insert datalet addSqlParams = ['zhao'.'18']; Connection.query (addSql, addSqlParams, (error, response) => {if (error) {
console.log("New failure!");
console.log(error);
return;
} else {
console.log("Add success!");
console.log(response);
};
});
connection.end();
Copy the codePerform the node add. Js
Refresh Navicat and you will see that a new piece of data has been added.
4.2, delete
delete.js
const mysql = require('mysql');
let connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'root',
database: 'test', port: 3306 }); connection.connect(); // Set the SQL delete statementlet delSql = 'DELETE FROM info_test where id=0';
connection.query(delSql, (error, response) => {
if (error) {
console.log("Delete failed!");
console.log(error);
return;
} else {
console.log("Delete successful!");
console.log(response);
};
});
connection.end();
Copy the codePerform the node delete. Js
Refresh Navicat and you will see that the data with ID 0 has been deleted.
4.3 instead,
update.js
const mysql = require('mysql');
let connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'root',
database: 'test', port: 3306 }); connection.connect(); // Set the SQL change statementlet updateSql = 'UPDATE info_test SET name = ? ,age = ? WHERE ID = ? '; // The data to be modifiedlet updateSqlParams = ['Wang'.'18'1]; connection.query(updateSql, updateSqlParams, (error, response) => {if (error) {
console.log("Delete failed!");
console.log(error);
return;
} else {
console.log("Delete successful!");
console.log(response);
};
});
connection.end();
Copy the codePerform the node update. Js
Refresh Navicat and you will see that the data with ID 1 has been modified.
4.4,
read.js
const mysql = require('mysql');
let connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'root',
database: 'test', port: 3306 }); connection.connect(); // Set the SQL change statementlet readSql = 'SELECT * FROM info_test';
connection.query(readSql, (error, response) => {
if (error) {
console.log("Query failed!");
console.log(error);
return;
} else {
console.log("Query successful!");
console.log(response);
};
});
connection.end();
Copy the codePerform the node read. Js
Second, the mongo
1. Install mongodb
npm install mongodb –save
2. Create a database
To create a database in MongoDB, we first need to create a MongoClient object and then configure the specified URL and port number.
If the database does not exist, MongoDB will create the database and establish a connection. For example, we will create a test database:
MongoDB.js
var MongoClient = require('mongodb').MongoClient;
var url = "mongodb://localhost:27017/test";
MongoClient.connect(url, function(err, db) {
if (err) throw err;
console.log("Database created!");
db.close();
});
Copy the code
3. Create a collection
We can create collections using the createCollection() method:
Modify the mongo. Js
var MongoClient = require('mongodb').MongoClient;
var url = "mongodb://localhost:27017/test";
MongoClient.connect(url, function (err, db) {
if (err) throw err;
console.log('Database created');
var dbase = db.db("test");
dbase.createCollection('info_test'.function (err, res) {
if (err) throw err;
console.log("Create collection info_test!");
db.close();
});
});
Copy the code
If you have a visualization tool for MongoDB, such as Studio 3T, you can see the database test and collection (table) Info_test that you just created.
4. Database operation: add, delete, change and check
Unlike MySQL, MongoDB creates databases and collections automatically, so you don’t need to manually create them before using them.
4.1,
- Insert a data: insertOne();
- Insert multiple pieces of data: insertMany().
In the following example, we connect to the info_test table of the database test and insert a single entry, using insertOne() :
add.js
var MongoClient = require('mongodb').MongoClient;
var url = "mongodb://localhost:27017/test";
MongoClient.connect(url, function(err, db) {
if (err) throw err;
var dbo = db.db("test");
var user = {
name: "Liu",
age: "24"
};
dbo.collection("info_test").insertOne(user, function(err, res) {
if (err) throw err;
console.log("Data inserted successfully!");
db.close();
});
});
Copy the code
If you do not have a visual tool installed, you can also open the MongoDB client to view the data, for example:
Next we use insertMany() to insert multiple pieces of data.
add.js
var MongoClient = require('mongodb').MongoClient;
var url = "mongodb://localhost:27017/test";
MongoClient.connect(url, function(err, db) {
if (err) throw err;
var dbo = db.db("test");
var user = [{
name: "Zhao",
age: "25"
}, {
name: "Sun",
age: "18"
}, {
name: "Du",
age: "23"
}];
dbo.collection("info_test").insertMany(user, function(err, res) {
if (err) throw err;
console.log("Insert" + res.insertedCount + "Data success!");
db.close();
});
});
Copy the code
4.2, delete
- Delete a data: deleteOne();
- Delete multiple data: deleteMany().
Delete data where name = “Sun” :
delete.js
var MongoClient = require('mongodb').MongoClient;
var url = "mongodb://localhost:27017/test";
MongoClient.connect(url, function(err, db) {
if (err) throw err;
var dbo = db.db("test"); // Query the condition varwhereStr = {
"name": "Sun"
};
dbo.collection("info_test").deleteOne(whereStr, function(err, res) {
if (err) throw err;
console.log("Data deleted successfully!");
db.close();
});
});
Copy the code
Select * from ‘age’ where age = 23;
var MongoClient = require('mongodb').MongoClient;
var url = "mongodb://localhost:27017/test";
MongoClient.connect(url, function(err, db) {
if (err) throw err;
var dbo = db.db("test"); // Query the condition varwhereStr = {
age: "23"
};
dbo.collection("info_test").deleteMany(whereStr, function(err, res) {
if (err) throw err;
console.log(res.result.n + "Bar document deleted");
db.close();
});
});
Copy the code
4.3 instead,
- Modify one data: updateOne();
- Run the updateMany() command to modify multiple data.
Change the age value from Liu to 18:
update.js
var MongoClient = require('mongodb').MongoClient;
var url = "mongodb://localhost:27017/test";
MongoClient.connect(url, function(err, db) {
if (err) throw err;
var dbo = db.db("test"); // Query the condition varwhereStr = {
"name": "Liu"}; Var updateStr = {$set: {
"age": "18"}}; dbo.collection("info_test").updateOne(whereStr, updateStr, function(err, res) {
if (err) throw err;
console.log("Data modified successfully!");
db.close();
});
});
Copy the code
Alter table age = 18 alter table name = Wang;
update.js
var MongoClient = require('mongodb').MongoClient;
var url = "mongodb://localhost:27017/test";
MongoClient.connect(url, function(err, db) {
if (err) throw err;
var dbo = db.db("test"); // Query the condition varwhereStr = {
"age": "18"}; Var updateStr = {$set: {
"name": "Wang"}}; dbo.collection("info_test").updateMany(whereStr, updateStr, function(err, res) {
if (err) throw err;
console.log(res.result.nModified + "Data modified successfully!");
db.close();
});
});
Copy the code
4.4,
You can use find() to find data, find() returns all data matching a condition, and if no condition is specified, find() returns all data in the collection.
read.js
var MongoClient = require('mongodb').MongoClient;
var url = "mongodb://localhost:27017/test";
MongoClient.connect(url, function(err, db) {
if (err) throw err;
var dbo = db.db("test"); Dbo.collection ()"info_test").find({}).toArray(function(err, result) {
if (err) throw err;
console.log(result);
db.close();
});
});
Copy the code
Select * from age where age = 18;
update.js
var MongoClient = require('mongodb').MongoClient;
var url = "mongodb://localhost:27017/test";
MongoClient.connect(url, function(err, db) {
if (err) throw err;
var dbo = db.db("test"); // Query the condition varwhereStr = {
"age": "18"
}
dbo.collection("info_test").find(whereStr).toArray(function(err, result) {
if (err) throw err;
console.log(result);
db.close();
});
});
Copy the code
4.5, sorting,
Use the sort() method, which takes an argument specifying whether it is in ascending (1) or descending (-1) order.
For example, sort by age from smallest to largest:
sort.js
var MongoClient = require('mongodb').MongoClient;
var url = "mongodb://localhost:27017/test";
MongoClient.connect(url, function(err, db) {
if (err) throw err;
var dbo = db.db("test"); Var mySort = {"age": 1
}
dbo.collection("info_test").find().sort(mySort).toArray(function(err, result) {
if (err) throw err;
console.log(result);
db.close();
});
});
Copy the code
4.6. Query paging
If you want to set a specified number of returns, you can use the limit() method, which takes a single argument specifying the number of returns.
Let’s take a look at the info_test table:
limit.js
var MongoClient = require('mongodb').MongoClient;
var url = "mongodb://localhost:27017/test";
MongoClient.connect(url, function(err, db) {
if (err) throw err;
var dbo = db.db("test");
dbo.collection("info_test").find().limit(2).toArray(function(err, result) {
if (err) throw err;
console.log(result);
db.close();
});
});
Copy the code
If you want to specify the number of bars to skip, use the skip() method.
skip.js
var MongoClient = require('mongodb').MongoClient;
var url = "mongodb://localhost:27017/test";
MongoClient.connect(url, function(err, db) {
if (err) throw err;
var dbo = db.db("test");
dbo.collection("info_test").find().skip(2).limit(2).toArray(function(err, result) {
if (err) throw err;
console.log(result);
db.close();
});
});
Copy the code
4.7 Connection operation
MongoDB is not a relational database, but we can use $lookup to implement a left join.
For example, we have two sets of data, respectively:
Set 1: Orders
[
{ _id: 1, product_id: 154, status: 1 }
]
Copy the codeSet 2: Products
[
{ _id: 154, name: 'Laptop' },
{ _id: 155, name: 'headphone' },
{ _id: 156, name: 'Desktop'}]Copy the code$lookup implements the left join
var MongoClient = require('mongodb').MongoClient;
var url = "mongodb://localhost:27017/";
MongoClient.connect(url, function(err, db) {
if (err) throw err;
var dbo = db.db("test");
dbo.collection('orders').aggregate([{
$lookup: {
from: 'products'// select * from *localField: 'product_id', // Left set join field foreignField:'_id'; // select join as:'orderdetails'// New generated field (type array)}}]).toarray (function(err, res) {
if (err) throw err;
console.log(JSON.stringify(res));
db.close();
});
});
Copy the code4.8. Delete a collection
We can drop collections using the drop() method:
drop.js
var MongoClient = require('mongodb').MongoClient;
var url = "mongodb://localhost:27017/";
MongoClient.connect(url, function(err, db) {
if (err) throw err;
var dbo = db.db("test");
dbo.collection("info_test").drop(function(err, delOK) {
if (err) throw err;
if (delOK) console.log("Collection deleted successfully!");
db.close();
});
});
Copy the code