Based on node JS connection database (mysql, mongodb)


Introduction to MongoDB

MongoDB is a database based on distributed file storage.

MongoDB is a product between relational databases and non relational databases. It is the most functional and relational database among non relational databases.

Three concepts of MongoDB

  1. Database: a database is a warehouse in which collections can be stored
  2. Collection: a collection is like an array in which documents can be stored. A collection is a group of texts
  3. Document: a document is the smallest unit in the database, and all documents that can be stored and operated are documents; Every record in MongoDB is a document

Note: in MongoDB, there is no need to manually create databases and collections when they do not exist. MongoDB will automatically create databases and collections

Three relationships: database {collection [document ()]}

MongoDB installation

  1. Download on the official website:
  2. After downloading and installing, find the MongoDB installation path, find the bin directory, and copy the path D:\JS\MongoDB\bin
  3. Configure the system environment variable, find the Path variable, click Edit to add the Path just copied D:\JS\MongoDB\bin
  4. Test press and hold the win key + r key, enter cmd, enter mongod, and the installation is successful
  5. Add a data file to the root directory of drive c, and then add a DB file under the data file (this data file is the one that stores the Mongodb directory) D:\JS\MongoDB\data\db
  6. Press and hold the win key + r key, enter cmd, enter {mongod --dbpath D:\JS\MongoDB\data\db} to add the database directory to the corresponding path according to the specified path

1, MOngoDB basic operation

1. basic instructions

  1. Query all current databases: show dbs / show databases
  2. Use the specified database: use database name
  3. Display the current database: db
  4. Display the collection where the database is located: show collections
  5. Create new collection: db createCollection()
  6. Delete collection: db collection. drop()

Add document

  1. insert(): add document
  2. insertOne(): add a document
  3. insertMany(): add multiple documents
//Add multiple arrays

Query data

  1. db.gather.find(): query all and return data

  2. ad.gather.findOne(): query the first document in the collection that meets the conditions, and return the document object

  3. db.gather.find().count(): how many documents are there in the query collection

    db.gather.find({age:"16"});//Query the document with age of 16 and return it as an array
    db.gather.find({_id:"1"})[1];//Query all documents with id 1 and return an array, which is the second array
    ad.gather.findOne();  //Return document object

Modify document

  1. db.gather.update(): modify and replace the data of the document
  2. db.gather.updateOne(): modify and replace the data of a document
  3. db.gather.updateMany(): modify and replace the data of multiple documents
  4. db.gather.replaceOne(): replace a document
//db. gather. Update (query object, replace object)

remove document

  1. db.gather.remove(): delete one or more documents
  2. db.gather.deleteOne(): delete a document
  3. db.gather.deleteMany(): delete multiple documents
  4. db.gather.remove({}): to delete a collection, parameters must be passed
  5. db.gather.drop(): delete a collection
  6. db.dropDatabase(): delete a database

Sorting, displaying

sort(): you can specify ascending and descending order of documents. 1 is in ascending order, -1 is in descending order

db.gather.find({}).sort({id:1,age:-1}) //The sorting rules need to be specified in the transfer parameter. name is the first sorting, and age is the second sorting.

db.gather.find({},{_id: 0,name:1,age:1})//You can set the required query results by yourself. If it is 0, it will not be displayed, and if it is 1, it will be displayed


//1. enter my_test database
use my_test
//2. insert a document into the user collection of the database  
//3. query the documents in the user collection
//4. insert a document into the user collection of the database
//5. query the documents in the database user collection
//6. count the number of documents in the database user collection
//7. query the document whose username is sunwukong in the user collection of the database
//8. add an address attribute to the document whose username is sunwukong in the user collection of the database. The attribute value is huaguoshan
db.user.update({username:"sunwukong"}, {$set:{address:"huaguoshan"}});
//9. use {username: "tangsheng"} to replace the document whose username is zhubajie
//db.user.replaceOne({username:"zhubajie"}, {username:"tangseng"});
db.user.update({username:"zhubajie"}, {$set:{username:"tangseng"}});
//10. delete the address attribute of the document whose username is sunwukong
db.user.update({username:"sunwukong"}, {$unset:{address:1}});
//11. add a hobby:{cities:["Beijing", "Shanghai", "Shenzhen"], movies:["Sanguo", "hero"]} to the document whose username is sunwukong
//The attribute value in the MongoDB document can also be a document. When the attribute value of a document is a document, the file is called an embedded document
db.user.update({username:"sunwukong"}, {$set:{hobby:{cities:["beijing","shanghai","shenzhen"] , movies:["sanguo","hero"]}}});
//12. add a hobby:{movies:["A Chinese Odyssey","King of comedy"]}
db.user.update({username:"tangseng"}, {$set:{hobby:{movies:["A Chinese Odyssey","King of comedy"]}}});
//13. query the documents of hero
//MongoDB supports direct query through the attributes of embedded documents. If you query embedded documents, you can use the Form matching of
//If you want to query a document through an embedded document, you must use quotation marks for the attribute name (single quotation marks and double quotation marks are acceptable)
//14. add a new movie Interstellar to tangseng
//$push to add a new element to the array
//$addToSet adds a new element to the array. If this element already exists in the array, it will not be added again
//db.user.update({username:"tangseng"}, {$push:{"hobby.movies":"Interstellar"}});
db.user.update({username:"tangseng"}, {$addToSet:{"hobby.movies":"Interstellar"}});
//15. delete users who like beijing
//16. delete user set
show dbs;
//17. insert 20000 pieces of data into numbers
for(var i=1; i<=20000; i++) {
var arr = [];
for(var i=1; i<=20000; i++) {
//18. query documents with num of 500 in numbers
//19. query documents with num greater than 5000 in numbers
//20. query documents with num less than 30 in numbers
//21. query documents with num greater than 40 but less than 50 in numbers
db.numbers.find({num:{$gt:40, $lt:50}});
//22. query documents with num greater than 19996 in numbers
//23. view the top 10 data in the numbers set
//We will never query without conditions during development
//24. view the 11th to 20th data in the numbers set
//When skip(), family (), sort() are executed together, the order of execution is first sort(), then skip(), and finally the displayed limit(), which is independent of the order of command writing.
//25. view the 21st to 30th data in the numbers set
//26. import dept and emp sets into the database
//27. query employees whose salary is less than 2000
//28. query employees whose wages range from 1000 to 2000
db.emp.find({sal:{$gt:1000, $lt:2000}});
//29. query employees whose salary is less than 1000 or more than 2500
db.emp.find({$or:[{sal:{$lt:1000}}, {sal:{$gt:2500}}]});
//30. query all employees of the finance department
var depno = db.dept.find({dname:"Finance Department"})[0].deptno;
//31. query all employees in the Sales Department
var depno = db.dept.find({dname:"Sales Department"})[0].deptno;
//32. query all employees whose mgr is 7698
//33. increase the salary of 400 yuan for all employees whose salary is less than 1000 yuan
db.emp.update({sal:{$lte:1000}}, {$inc:{sal:400}}, {multi:true});


Mongoose is on node Object model tool for easy operation of mongodb in JS asynchronous environment - brief book

Full code

var mongoose = require("mongoose");
var Schema = mongoose.Schema;
var blogSchema = new Schema({
var StuModel = mongoose.model("student", blogSchema);
    name:"Xiao Zhan",
},function (err) {
    if (!err){
        console.log("It's plugged in")
  1. Create text/package JSON folder

      "name": "mongocd ", //Note: name cannot be mongoose, or an error is reported, which violates the npm rules
      "version": "0.0.1"
  2. Install mongoose to the specified cmd command d:\java\learning\nodejs\src\com\xiaozhan\text>
    Download through the command npm i mongoose --save

  3. Connect to MongoDB database

var mongoose = require('mongoose');
//mongoose.connect("mongodb:// database ip address: port number / database name");

//Database connection events 
mongoose.connection.once("open",function () {

//Database disconnect event
mongoose.connection.once("open",function () {
mongoose.disconnect();//Disconnect database

mongoose connection database

First: connect to the database

var mongoose = require("mongoose");
//Create a mongoose_ Textdatabase

Second: define a Schema

var Schema = mongoose.Schema;
var blogSchema = new Schema({
    name:String,//Define attributes
    age:Number//Define attributes

Third: create a Model

//Create a Model (representing a collection in the database) through a schema, and operate the database through a Model
//mongoose.model(modelName, schema) .  Modelname: collection name, indicating the mapped collection name
var StuModel = mongoose.model("student", blogSchema);

Fourth: insert document

StuModel.create({    //Insert document into database
    name:"Xiao Zhan",
},function (err) {
    if (!err){
        console.log("It's plugged in")


Create a Model to add, delete, modify and query the database


  1. Model.create(docs,[callback])


docs: to insert one or more documents, it can be spread pages or arrays

Callback: callback function


  1. Model. Find (conditions, [project], [options], [callback]) - query documents

  2. Model. Findbyid (id, [project], [options], [callback]) - query the document by id

  3. Model. Findone ([conditions], [project], [options], [callback]) - query the first qualified document

    conditions: query conditionsprojection: projection (custom obtained field)

    Options: Query options (skip, limit) callback: callback function. Query results are returned through callback

    Note: the query must have a callback function. If the callback function is not passed, the query will not be performed

// Do not fill in query criteria, and query all by default
StuModel.find({"query criteria"},function (err,docs) {
   if (!err){  console.log(docs); }
//Projection {name:1, age:0} means that only name / name -age is displayed. If you don't want it, you can write a - sign
StuModel.find({},{name: 1,age: 0},function (err,docs) {
   if (!err){  console.log(docs); }
//skip:2 skip the first two, limit:1 display only one
StuModel.find({},name -age,{skip:2,limit:1},function (err,docs) {
   if (!err){  console.log(docs); }


5. Model.remove(conditions , [callback])
6. Model.removeOne(conditions , [callback])
7. Model.removeMany(conditions , [callback])
StuModel.remove({name:"Xiao Zhan"},function (err) {
   if (!err){  console.log("Deletion succeeded");  }
  1. Model. Count (conditions, [callback]) statistics quantity


  1. update(update,[options],[callback]) - modify object
  2. remove([callback]) - delete object


//Hide objects convert document to normal objects
doc = doc.toObject();
delete doc.address;

node.js connecting to mysql

Prepare in advance. When you need to connect to a MySQL database, you need to quickly set up a MySQL environment locally and configure it to work normally

  1. Create database text group, node table

    CREATE TABLE node(    
    	title VARCHAR(50),
    	info VARCHAR(500)
  2. Next, install node JS mysql module npm install mysql to the specified file

  3. JavaScirpt operation mysql

var mysql = require("mysql");                       //Reference module
var client = mysql.createConnection({             //Create connection
    "host": "localhost",
    "port": "3306",
    "user": "root",
    "password": "root"
client.query("USE test", function (error, results) {
    if (error) {             //Exit on error
        console.log("ClientConnectionReady Error:" + error.message);
//insert data
InsertData = function () {
    var values = ["Hello! ", "node 2 mysql at:" + Math.random()];
    client.query("INSERT    INTO    node    SET    title=? , info=? ", values,
        function (error, results) {
            if (error) {                                     //Exit on error
                console.log("InsertData Error:" + error.message);
            console.log("Inserted: " + results.affectedRows + " row.");
            console.log("Id inserted: " + results.insertId);

//Query data
GetData = function (client) {
    client.query("SELECT * FROM node", function (error, results, fields) {
        if (error) {//Exit on error
            console.log("GetData Error:" + error.message);
        console.log(results);                    //Console output Recordset
        if (results.length > 0) {
            var rs = results[0];                //Get the first record
            console.log("Title:" + rs.title);   //Output specified fields
            console.log("info:" + rs["info"]);
    client.end();                                        //Close database connection
    console.log("Connection closed.");

Simple databases are successfully connected. Of course, some small partners may encounter bug s in the database version (8.0),

Example: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server;

The reason for this error is that the mysql module does not fully support the "caching_sha2_password" encryption method of MySQL 8.0, while "caching_sha2_password" is the default encryption method in MySQL 8.

terms of settlement:

Open the terminal and input commands in sequence

mysql -u root -p
 Enter your password
use mysql;
alter user 'root'@'localhost' identified with mysql_native_password by 'Your password';
flush privileges;

Modify the encryption rule to normal mode, and the encryption method supported by mysql module

node.js connection mongodb

MongoDB is a non relational database, relative to node JS application environment, the JSON data format it supports is very popular with developers, and it is also a NoSQL database with high voice.

The biggest difference between MongoDB and relational database (MySQL) operation data is that SQL syntax is not used

var mongoDB = require('mongoDB');                  //Reference module
var server = new mongoDB.Server('localhost', 27017, {auto_reconnect: true});
//Connect to database
var db = new mongoDB.Db('testnode', server, {safe: true});
//Open database (err, _db) {
    if (err) {                   //Prompt if an error is reported
    } else {
        //Open Recordset
        db.createCollection('nodemsg', function (err, collection) {
            if (err) {
            } else {
                var _data = {
                    "id": new Date().getSeconds(), "msg":
                        "node msg", "code": Math.random()
                //insert data
                collection.insert(_data, {safe: true}, function (err, result) {
                        //Update record with id=20
                        collection.update({id: 20}, {
                            $set: {
                                "msg": "updatemsg"
                        }, {safe: true}, function (err, result) {
                        //Query records with id>20
                        collection.find({"id": {"$gt": 20}}).toArray
                        (function (err, docs) {
                            console.log("find id>20");
                            db.close();           //Close connection

Tags: Javascript

Posted by lizard on Fri, 03 Jun 2022 04:24:01 +0530