Mac installation mongo

Connect to the database

// Start mongod --dbpath database directory /data/db mongoCopy the code
/ / clear screen CLSCopy the code
// Query the database list show DBSCopy the code

Create, view, and delete a database

1. Use a database and create a database

use itying
Copy the code

If you really want the database to succeed, you must insert a piece of data. You cannot insert data directly into a database. You can only insert data into collections. Insert data into the user table of the ITying database.

Db. User. Insert ({" name ":" xiaoming "});Copy the code

2. View the database

show dbs
Copy the code

Mysql > select * from ‘table’;

show collections
Copy the code

4. Delete the collection and delete the specified collection delete table

Db.collection_name drop() db.user.drop()Copy the code

5. Delete the database. Delete the current database

db.dropDatabase();
Copy the code

Insert (add) data

Data is inserted, and with the data being inserted, the database is created successfully, and the collection is created successfully.

The name of the table. The insert ({"name":"zhangsan"."age":20});
Copy the code

Fourth, find data

1. Query all records

db.user.find();

// equivalent to: select* from user;
Copy the code

2. Query the duplicate data of a column in the current collection

db.user.distinct("name");

// Will filter out the same data in name
Select distict name from user;
Copy the code

3. Query the records whose age is 22

db.user.find({"age": 22});

Select * from user where age = 22;
Copy the code

4. Query the records whose age is greater than 22

db.user.find({age: {$gt: 22}});

Select * from user where age >22;
Copy the code

5. Query the records whose age is less than 22

db.user.find({age: {$lt: 22}});

Select * from user where age <22;
Copy the code

6. Query the records whose age >= 25

db.user.find({age: {$gte: 25}});

Select * from user where age >= 25;
Copy the code

7. Query the records whose age is less than = 25

db.user.find({age: {$lte: 25}});
Copy the code

Age >= 23 and age <= 26

db.user.find({age: {$gte: 23.$lte: 26}});
Copy the code

9. Data fuzzy query containing Mongo in query name is used for search

db.user.find({name: /mongo/});

// Equivalent to %%
// select * from user where name like ‘%mongo%’;
Copy the code

10. Select * from name where name starts with mongo

db.user.find({name: /^mongo/});

Select * from user where name like 'mongo%';
Copy the code

11. Query the specified columns name and age

db.user.find({}, {name: 1.age: 1});

// equivalent to: select name, age from user;
// If true is used, the value of name:1 is the same. If false is used, the value of name is excluded.
Copy the code

Select * from age where age > 25

db.user.find({age: {$gt: 25}}, {name: 1.age: 1});

Select name, age from user where age >25;
Copy the code

13. Sort by age from 1 up to 1 down

Ascending: db. The user. The find (). Sort ({age: 1}); Descending order: db. The user. The find (). Sort ({age: -1});
Copy the code

Select * from name = zhangsan, age = 22 where name = zhangsan, age = 22

db.user.find({name: 'zhangsan'.age: 22});

// equivalent to: select * from user where name = 'zhangsan' and age = '22';
Copy the code

Query the first 5 items of data

db.user.find().limit(5);

// equivalent to: selectTop 5 * from user;
Copy the code

16, query the next 10 data

db.user.find().skip(10);
Copy the code

17, query data between 5-10

db.user.find().limit(10).skip(5);

// Limit is pageSize, skip is (page-1)*pageSize
Copy the code

18, or and query

db.user.find({$or: [{age: 22}, {age: 25}]});

Select * from user where age = 22 or age = 25;
Copy the code

19. FindOne Query the first data

db.user.findOne();

// equivalent to: selectTop 1 * from user;
// db.user.find().limit(1);
Copy the code

20. Query the number of entries in a result set

db.user.find({age: {$gte: 25}}).count();

Select count(*) from user where age >= 20;
// If you want to return the number of records after the limit, use count(true) or count(non-0)
// db.users.find().skip(10).limit(5).count(true);
Copy the code

4. Modify data

Modify there are query conditions. Who do you want to change? Tell Mongo.

  • Select * from xiao Ming where age = 16;
db.student.update({"name":"Xiao Ming"}, {$set: {"age":16}});
Copy the code
  • Select * from math where age = 33;
db.student.update({"score.shuxue":70}, {$set: {"age":33}});
Copy the code
  • Change all matching items:”

By default, the update() method updates a single document. To update multiple documents, use the multi option in the update() method.

By default, the update () method updates a single document. To update multiple documents, use the multi option in the update () method.

db.student.update({"sex":"Male"}, {$set: {"age":33}}, {multi: true});
Copy the code
  • The $set keyword is not present: note
db.student.update({"name":"Xiao Ming"}, {"name":"Ming"."age":16});
Copy the code
db.users.update({name: 'Lisi'}, {$inc: {age: 50}}, false.true);

Update users set age = age + 50 WHERE name = 'Lisi';

db.users.update({name: 'Lisi'}, {$inc: {age: 50}, $set: {name: 'hoho'}}, false.true);

Update users set age = age + 50, name = 'hoho' where name = 'Lisi';
Copy the code

Delete data

db.collectionsNames.remove( { "borough": "Manhattan"})// db.users.remove({age: 132});
Copy the code

By default, the remove() method removes all documents that match the remove condition. Use the justOne option to limit the remove operation to only one of the matching documents.

By default, the remove () method removes all documents that qualify for removal. Use the justOne option to limit the delete operation to only one matching document.

db.restaurants.remove( { "borough": "Queens" }, { justOne: true})Copy the code

Six, index,

1. Index basis

An index is a structure that sorts the values of one or more columns of a database table, making it faster to query a database. MongoDB indexes are almost identical to traditional relational databases, including some basic query optimization techniques.

  • Here are the commands to create the index, 1(ascending), -1(descending) :
db.user.ensureIndex({"userame":1})
Copy the code
  • Get the index of the current collection:
db.user.getIndexes()
Copy the code
  • The command to drop an index is:
db.user.dropIndex({"username":1})
Copy the code

In MongoDB, we can also create composite indexes, such as:

  • The number 1 indicates that the indexes of the username key are stored in ascending order, and -1 indicates that the indexes of the age key are stored in descending order.
db.user.ensureIndex({"username":1."age": -1})
Copy the code

After the index is created, queries based on username and age will use the index, or queries based on username will use the index, but queries based only on age will not use the composite index. Therefore, if you want to use a composite index, you must include the first N index columns in the composite index in the query criteria. However, if the order of the key values in the query criteria is inconsistent with the order in which the composite index is created, MongoDB can intelligently help us adjust that order so that the composite index can be used for the query. Such as:

db.user.find({"age": 30."username": "stephen"})
Copy the code

For the query criteria in the example above, MongoDB dynamically adjusts the order of the query criteria documents prior to retrieval so that the query can use the compound index just created.

For the index created above, MongoDB automatically assigns an index name to the newly created index based on its keyname and index direction. You can use the following command to specify an index name during index creation, for example:

db.user.ensureIndex({"username":1}, {"name":"userindex"})
Copy the code

As the collection grows, you need to index the large number of sorts in the query. If you call sort on a key without an index, MongoDB needs to extract all the data into memory and sort it. Therefore, when performing indexless sorting, MongoDB will report an error if the amount of data is too large to be sorted in memory.

2. Enforce indexes

The hint command forces the use of an index.

db.t5.find({age: {$lt:30}}).hint({name:1.age:1})
Copy the code

3. Unique index

  • By default, indexes created are not unique. The following example creates a unique index, such as:
db.user.ensureIndex({"userid":1}, {"unique":true})
Copy the code
  • If a document with a duplicate userID is inserted again, MongoDB will report an error indicating that the duplicate key is inserted, for example:
db.user.insert({"userid":5})
db.user.insert({"userid":5})
> E11000 duplicate key error index: user.user.$userid_1 dup key: { : 5.0 }
Copy the code
  • If the inserted document does not contain the userID key, then the value of the userID key is null. If similar documents are inserted multiple times, MongoDB will report the same error, such as:
db.user.insert({"abc":5})
db.user.insert({"abc":5})
> E11000 duplicate key error index: user.user.$userid_1 dup key: { : null }
Copy the code

If duplicates already exist when creating a unique index, we can use the following command to help us eliminate duplicates when creating a unique index and keep only the first document found

  • Delete the unique index you just created.
db.user.dropIndex({"userid":1})
Copy the code
  • Insert test data to ensure that duplicate keys exist in the collection.
db.user.remove()
db.user.insert({"userid":5})
db.user.insert({"userid":5})
Copy the code
  • Recreate the unique index
db.user.ensureIndex({"userid":1}, {"unique":true.dropDups:true })
Copy the code
  • We can also create composite unique indexes by ensuring that the composite key values are unique. Such as:
db.user.ensureIndex({"userid":1."age":1}, {"unique":true.dropDups:true})
Copy the code

4. Parameters of the index

parameter type Description
background Boolean Creating an index blocks other database operations. Background specifies how to create an index in the background. Default: false
unique Boolean Whether the created index is unique. Default value: false
name string The index name. If not specified, MongoDB generates an index name by concatenating index field names and sorting order
DropDups (3.0 +) Boolean Whether to delete duplicate records when creating unique indexes, specifying true to create unique indexes. Default value: false

If you are creating an index for a document that already has data, you can execute the following command so that MongoDB creates the index behind the scenes without blocking other operations. By contrast, creating an index in blocking mode makes the whole creation process more efficient, but MongoDB will not be able to receive other operations at creation time.

db.user.ensureIndex({"username":1}, {"background":true})
Copy the code

5. Use explain

Explain is a very useful tool to help you get a lot of useful information about your query. You can get query details simply by calling this method on the cursor. Explain returns a document, not the cursor itself. Such as:Explain returns statistics on indexes used by the query, time spent, and number of documents scanned.

6. Explain executionStats queries specific execution times

db.tablename.find().explain( "executionStats") on the output of the following values: explain. ExecutionStats. ExecutionTimeMillisCopy the code

7. Account permission configuration

1. Create a super administrator

use admin
db.createUser({
user:'admin'.pwd:'123456'.roles: [{role:'root'.db:'admin'}]})Copy the code

2. Modify the Mongodb database configuration file

Path: C: Program Files\MongoDB\Server\4.0\ bin \ mongod CFG configuration:security:
authorization: enabled
Copy the code

3. Restart the mongodb service

Select Services in Task Manager and find them in the service listMongoDB ServerRight click to restartUse can be found at this pointmongoNo information is obtained from the database

4. Connect to the database as the super administrator

Mongo admin -u User name -p Password mongo192.1681.200.:27017/test -u user -p password
Copy the code

5. Create a user that can only access the user database and cannot access other databases

use user
db.createUser(
  {
  user: "useradmin".pwd: "123456".roles: [{role: "dbOwner".db: "user"}]})// dbOwner database management role
Copy the code

6. Common commands used in permission configuration of Mongodb accounts

  • View the users under the current library
show users; 
Copy the code
  • Delete user
db.dropUser("eggadmin")
Copy the code
  • Changing a User password
db.updateUser( "admin", {pwd:"password"});
Copy the code
  • Password authentication
db.auth("admin"."password");
Copy the code

7. Mongodb database roles

  1. Database user roles:read,readWrite
  2. Database management roles:dbAdmin,dbOwner,userAdmin
  3. Cluster management Roles:clusterAdmin,clusterManager,clusterMonitor,hostManager
  4. Backup and restore roles:backup,restore
  5. All database roles:readAnyDatabase,readWriteAnyDatabase,userAdminAnyDatabase,dbAdminAnyDatabase
  6. Super user role:root

MongoDB built-in role reference

8. Set the account password when connecting to the database

const url = 'mongodb://admin:123456@localhost:27017/';
Copy the code

Table to table relationship

1. A one-on-one relationship

One person for a unique ID number, one ID card for one person, that is, a one-to-one relationship.

2. One-to-many relationships

A class corresponds to multiple students, and a student can belong to only one class, that is, one-to-many relationship

3. Many-to-many relationships

Aggregation Pipeline

1. Pipeline concept

The POSIX Portable Operating System Interface of UNIX (POSIX) one of the most important ways to use multithreading is —– pipeline (also known as “pipeline”), The “data element” stream is executed serially by a set of threads in sequence.

To understand the idea of object-oriented, the whole pipeline, can be understood as a data transmission pipeline; Each working thread in the pipeline can be understood as a working stage of the whole assembly line, and the cooperation between these working threads is linked one by one. The worker thread closer to the input port is stage, which is the earlier stage in the sequence. Its work results will affect the work results of the next worker thread stage, that is, the next stage depends on the output of the previous stage, and the output of the previous stage becomes the input of the current stage. This is also a common feature of pipelines!

Aggregation pipes allow you to transform and combine documents in a collection.

Actual projects: table associated query, data statistics.

Directing the use ofdb.COLLECTION_NAME.aggregate([{<stage>},...] )Method to build and use aggregation pipes. Take a look at the examples on the official website to get a feel for the use of aggregation pipes. Explanation:

  • The first stage matches (filters) all in the Orders tablestatus:'A'The data.
  • The second stage is based on the data of the previous stagecust_idGroup the data fields within the groupamountI take the sum, and I assign it tototal.

2. Pipe operators

The operator The paper
$project The projection operator, used to reconstruct the fields of each document, can extract fields, rename fields, and even add new fields after operating on existing fields
$match Match operator, used to filter a collection of documents
$group Grouping operator used to group collections of documents
$unwind The split operator is used to split each value in an array into separate documents
$sort Sort operator, used to sort documents by one or more fields
$limit Limit operator, used to limit the number of documents returned
$skip Skip operator, used to skip a specified number of documents
$lookup The populate operator, used to connect to another collection in the same database and retrieve the specified document, is similar to populate
$count Statistics operator that counts the number of documents

Db.collection. aggregate([]) is a method used for aggregate pipe query. The parameter is array, and each array element is a stage. In stage, operators are used to process data and then hand it over to the next stage, until there is no next stage and the final result is output

3. Pipe expressions

Pipe operators act as “keys” and the corresponding “values” are called pipe expressions.

For example, {$match:{status:”A”}}, $match is called the pipe operator, and status:”A” is called the pipe Operand. Each pipe expression is a document structure consisting of field names, field values, and expression operators.

Common expression operators Description
$addToSet Deduplicates the value of a specified field in the document
$max The document specifies the maximum value for a field
$min The document specifies the minimum value for a field
$sum The document specifies field summation
$avg Average the fields specified by the document
$gt Greater than a given value
$lt Less than a given value
$eq Equal to a given value

4. Basic usage examples

The demo uses the following data

1. $project

Requirement: Finding order returns only the trade_NO and all_price fields in the document

db.order.aggregate([
  {
  	$project: {trade_no:1.all_price:1}}]) --> {"_id" : ObjectId("5fc1f01319245fa314447af7"), "trade_no" : "111"."all_price" : 100 }
{ "_id" : ObjectId("5fc1f05319245fa314447af8"), "trade_no" : "222"."all_price" : 90 }
{ "_id" : ObjectId("5fc1f05d19245fa314447af9"), "trade_no" : "333"."all_price" : 20 }
Copy the code
2. $match

Used to filter documents. The usage is similar to the arguments in the find() method.

db.order.aggregate([
  {
    $project: {trade_no:1.all_price:1}}, {$match: {"all_price": {$gte:90}}}]) --> {"_id" : ObjectId("5fc1f01319245fa314447af7"), "trade_no" : "111"."all_price" : 100 }
{ "_id" : ObjectId("5fc1f05319245fa314447af8"), "trade_no" : "222"."all_price" : 90 }
Copy the code
3. $group

Group documents in a collection that can be used for statistical results.

Count the order quantity of each order and group them according to the order number

db.order_item.aggregate(
  [
    {
    	$group: {_id: "$order_id".total: {$sum: "$num"}}}]) --> {"_id" : "2"."total" : 2 }
{ "_id" : "1"."total" : 3 }
{ "_id" : "3"."total" : 6 }
Copy the code
4. $sort

Sort the documents in the collection.

db.order.aggregate([
  {
  	$project: {trade_no:1.all_price:1}}, {$match: {"all_price": {$gte:90}}}, {$sort: {"all_price": -1}}]) --> {"_id" : ObjectId("5fc1f01319245fa314447af7"), "trade_no" : "111"."all_price" : 100 }
{ "_id" : ObjectId("5fc1f05319245fa314447af8"), "trade_no" : "222"."all_price" : 90 }
Copy the code
4. $limit

Only take one

db.order.aggregate([
  {
  	$project: {trade_no:1.all_price:1}}, {$match: {"all_price": {$gte:90}}}, {$sort: {"all_price": -1}}, {$limit:1-- > {}])"_id" : ObjectId("5fc1f01319245fa314447af7"), "trade_no" : "111"."all_price" : 100 }
Copy the code
5. $skip

Start with number one

db.order.aggregate([
  {
  	$project: {trade_no:1.all_price:1}}, {$match: {"all_price": {$gte:90}}}, {$sort: {"all_price": -1}}, {$skip:1-- > {}])"_id" : ObjectId("5fc1f05319245fa314447af8"), "trade_no" : "222"."all_price" : 90 }
Copy the code
6. $lookup table association
Grammar value explain
from A collection waiting to be joined in the same database.
localField If a document does not contain a localField Key (Field), it will contain a localField: NULL Key pair by default.
foreignField Match value of the collection to be joined. If the document to be joined does not have a foreignField value, the document will contain a foreignField: NULL key-value pair by default during processing.
as Name the new value for the output document. If the value already exists in the input set, it will be overwritten 
db.order.aggregate([
  {
  	$lookup: {
          from: "order_item".localField: "order_id".foreignField: "order_id".as: "items"}}]) --> {"_id": ObjectId("5fc1f01319245fa314447af7"),
	"order_id": "1"."uid": 10."trade_no": "111"."all_price": 100."all_num": 2."items": [{
		"_id": ObjectId("5fc1f06e19245fa314447afa"),
		"order_id": "1"."title": "Commercial Mouse 1"."price": 50."num": 1
	}, {
		"_id": ObjectId("5fc1f06e19245fa314447afb"),
		"order_id": "1"."title": "Commodity Keyboard 2"."price": 50."num": 1
	}, {
		"_id": ObjectId("5fc1f08219245fa314447afc"),
		"order_id": "1"."title": "Commodity Keyboard 3"."price": 0."num": 1} {}]"_id": ObjectId("5fc1f05319245fa314447af8"),
	"order_id": "2"."uid": 7."trade_no": "222"."all_price": 90."all_num": 2."items": [{
		"_id": ObjectId("5fc1f08619245fa314447afd"),
		"order_id": "2"."title": "Milk"."price": 50."num": 1
	}, {
		"_id": ObjectId("5fc1f08619245fa314447afe"),
		"order_id": "2"."title": "Yogurt"."price": 40."num": 1} {}]"_id": ObjectId("5fc1f05d19245fa314447af9"),
	"order_id": "3"."uid": 9."trade_no": "333"."all_price": 20."all_num": 6."items": [{
		"_id": ObjectId("5fc1f08619245fa314447aff"),
		"order_id": "3"."title": "Mineral water"."price": 2."num": 5
	}, {
		"_id": ObjectId("5fc1f08819245fa314447b00"),
		"order_id": "3"."title": "Towel"."price": 10."num": 1}}]Copy the code

MongoDB MongoDB backup (mongodump) and restore (mongorestore)

Database migration requires the use of mongodump and Mongorestore syntax. If permission validation is not enabled on the database, use the command directly

$ mongodump -h dbhost -d dbname -o dbdirectory
$ mongorestore -h <hostname><:port> -d dbname <path>
Copy the code

If login authentication is enabled, the database cannot be directly backed up and restored. The following error is reported:

Failed: koa.user: error reading database: command listCollections requires authentication
Copy the code

-u

-p= 

Failed: error connecting to db server: server returned error on SASL authentication step: Authentication failed
Copy the code

-u

-p= Add: –authenticationDatabase admin 

mongorestore -h 127.0. 01.:27017 -d koa -u admin -p=123456 -o C:\Users\yq612\Desktop\koa --authenticationDatabase admin
Copy the code

!!!!! Note that the password p is followed by =

  • The backup
mongodump -h 127.0. 01. -d newsDB -u admin -p=123456 -o D:\MongoDB\bf\newsDB --authenticationDatabase admin
Copy the code
  • reduction
mongorestore -h 127.0. 01. -d newsDB2 -u admin -p=123456 D:\MongoDB\bf\newsDB --authenticationDatabase admin
Copy the code

Use Mongodb in Nodejs

    1. Create a project folder

    1. The introduction ofmongodb
npm init --yes
cnpm install mongodb --save
Copy the code

“Document” https://github.com/mongodb/node-mongodb-native

    1. The connection

A new index. Js

//index.js
const MongoClient = require('mongodb').MongoClient;
const url = 'mongodb://localhost:27017';
// const url = 'mongodb://admin:123456@localhost:27017/'; // There is a password connection mode
const dbName = 'ybs';
const client = new MongoClient(url, { useUnifiedTopology: true });
client.connect(function (err) {
    if (err) {
        console.log(err);
        return;
    }
    console.log("Connection successful");
    /* Data operation... * / 
    // Close the connection to complete the database operation
    client.close();
});
Copy the code
/ / run
node index.js
Copy the code
    1. Look for the sample
const MongoClient = require('mongodb').MongoClient;
const url = 'mongodb://localhost:27017';
const dbName = 'itying';
const client = new MongoClient(url, { useUnifiedTopology: true });
client.connect(function (err) {
    if (err) {
        console.log(err);
        return;
    }
    const db = client.db(dbName); // Get the db object
    db.collection("user").find({}).toArray(function (err, data) { / / to find
        if (err) {
            console.log(err);
            return;
        }
        console.log(data); client.close(); })});Copy the code
    1. Increase the data
const MongoClient = require('mongodb').MongoClient;
const url = 'mongodb://localhost:27017';
const dbName = 'itying';
const client = new MongoClient(url, { useUnifiedTopology: true });
client.connect(function (err) {
    if (err) {
        console.log(err);
        return;
    }
    const db = client.db(dbName); // Get the db object
    // Add data
    db.collection("user").insertOne({ "name": "nodejs"."age": 10 }, (err, result) = > {
        if (err) {
            console.log(err);
            return;
        }
        console.log(result); client.close(); })});Copy the code
    1. Modify the data
const MongoClient = require('mongodb').MongoClient;
const url = 'mongodb://localhost:27017';
const dbName = 'itying';
const client = new MongoClient(url, { useUnifiedTopology: true });
client.connect(function (err) {
    if (err) {
        console.log(err);
        return;
    }
    const db = client.db(dbName); // Get the db object
    // Modify the data
    db.collection("user").updateOne({ "name": "zhangsan12" }, { $set: { "age": 50}},(err, result) = > {
        if (err) {
            console.log(err);
            return;
        }
        console.log(result); client.close(); })});Copy the code
    1. Delete the data
const MongoClient = require('mongodb').MongoClient;
const url = 'mongodb://localhost:27017';
const dbName = 'itying';
const client = new MongoClient(url, { useUnifiedTopology: true });
client.connect(function (err) {
    if (err) {
        console.log(err);
        return;
    }
    const db = client.db(dbName); // Get the db object
    // Delete data
    db.collection("user").deleteOne({ "name": "zhangsan12" }, function (err, result) {
        if (err) {
            console.log(err);
            return;
        }
        console.log(result);
        client.close();
    });
});
Copy the code