MongoDB Basic Query and Index operation method Summary _mongodb

Query operations

1. Check all records

Db.userInfo.find ();

Equivalent:

select* from UserInfo;

2. Duplicate data for a column in the current clustered collection after the query has been removed

Db.userInfo.distinct ("name");

Will filter out the same data in name

Equivalent:

Select Disttince name from UserInfo;

3, Query age = 22 Records

Db.userInfo.find ({"Age": 22});

Equivalent:

SELECT * from userInfo where age = 22;

4. Check the records of age > 22

Db.userInfo.find ({age: {$gt: 22}});

Equivalent:

SELECT * from UserInfo where age >22;

5. Check the records of age < 22

Db.userInfo.find ({age: {$lt: 22}});

Equivalent:

SELECT * from UserInfo where age <22;

6. Check the records of age >= 25

Db.userInfo.find ({age: {$gte: 25}});

Equivalent:

SELECT * from UserInfo where age >= 25;

7. Check the records of age <= 25

Db.userInfo.find ({age: {$lte: 25}});

Equivalent:

SELECT * from UserInfo where age <= 25;

8. Query age >= 23 and age <= 26

Db.userInfo.find ({age: {$gte: $lte: 26}});

Equivalent:

SELECT * from UserInfo where age >=23 and age <= 26;

9, query name contains MONGO data

Db.userInfo.find ({name:/mongo/});

Equivalent:

SELECT * from UserInfo where name like '%mongo% ';

10, query name in the beginning of the MONGO

Db.userInfo.find ({name:/^mongo/});

Equivalent:

SELECT * from UserInfo where name like ' mongo% ';

11. Query Specify column name, age data

Db.userInfo.find ({}, {name:1, age:1});

Equivalent:

Select name, age from UserInfo;

Of course, name can also use True or false, as with ture in the case of river Name:1 effect, if False is excluding name, display column information other than name.

12. Query Specify column name, age data, age > 25

Db.userInfo.find ({age: {$gt:}}, {name:1, age:1});

Equivalent:

Select name, age from UserInfo where age >25;

13. Sort by age

Ascending:

Db.userInfo.find (). Sort ({age:1});

Descending:

Db.userInfo.find (). Sort ({Age:-1});

14, query the first 5 data

Db.userInfo.find (). Limit (5);

Equivalent:

SELECT * FROM (SELECT * to UserInfo) where RowNum < 6;//oracle

select * from UserInfo limit 5;//mysql

15, query 10 after the data

Db.userInfo.find (). Skip (10);

Equivalent:

select * from userInfo where id not in (select id from (select * from userInfo) where  and rownum < 11);

16, the query in the data between 5-10

Db.userInfo.find (). Limit (a). Skip (5);

Can be used for pagination, limit is Pagesize,skip is the first few pages *pagesize

17, OR and query

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

Equivalent:

SELECT * from userInfo where age = 25;

18, query the first piece of data

Db.userInfo.findOne ();

Db.userInfo.find (). limit (1);

Equivalent:

SELECT * FROM (SELECT * from UserInfo) where and RowNum < 2

19, query a result set of the number of records

Db.userInfo.find ({age: {$gte:}}). Count ();

Equivalent to: SELECT COUNT (*) from UserInfo where age >= 20;

Index

1. CREATE index

Db.userInfo.ensureIndex ({username:1});

In MongoDB, we can also create a composite index, as follows:

Db.userInfo.ensureIndex ({username:1, Age:-1});

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

Db.test.find ({"Age": "username": "Stephen"})

For the query criteria in the previous example, MongoDB will dynamically adjust the order of the query criteria document before it is retrieved so that the query can use the composite index that you just created.

2. Create a unique index

Indexes that are created by default are not unique indexes. The following example creates a unique index, such as:

Db.test.ensureIndex ({"userid": 1},{"unique": true})

If you insert a UserID duplicate document again, MongoDB will complain, prompting you to insert a duplicate key, such as:

Db.test.insert ({"userid": 5})

Db.test.insert ({"userid": 5})

E11000 duplicate key error index:test.test. $userid _1 dup key: {: 5.0}

If the inserted document does not contain the UserID key, the value of the key in the document is NULL, and if you insert a similar document multiple times, MongoDB will report the same error, such as:

Db.test.insert ({"Userid1": 5})

Db.test.insert ({"Userid1": 5})

E11000 duplicate key error index:test.test. $userid _1 dup key: {: null}

If duplicates are already present when creating a unique index, we can use the following command to help us eliminate duplicate documents when creating unique indexes, preserving only the first document found, such as:

--First delete the unique index you just created.

Db.test.dropIndex ({"userid": 1})

--Inserts test data to ensure that duplicate keys exist in the collection.

Db.test.remove ()

Db.test.insert ({"userid": 5})

Db.test.insert ({"userid": 5})

-Create a unique index and eliminate duplicate data.

Db.test.ensureIndex ({"userid": 1},{"unique": true, "dropdups": true})

--The query results confirm that the duplicate key was actually deleted when the index was created.

Db.test.find ()

{"_id": ObjectId ("4fe823c180144abd15acd52e"), "userid": 5}

We can also create a composite unique index, which guarantees that the composite key value is unique. Such as:

Db.test.ensureIndex ({"userid": 1, "Age": 1},{"unique": true})

3. Querying all indexes of the current aggregation collection

Db.userInfo.getIndexes ();

4. View the total index record size

Db.userInfo.totalIndexSize ();

5. Read all index information for the current collection

Db.users.reIndex ();

6, delete the specified index

Db.users.dropIndex ("username": 1);

7. Delete all index indexes

Db.users.dropIndexes ();