Example description of MongoDB query statements

​

If we have a database in MongoDB server, there is a data set (table) UserModel in the data, and the stored data and fields are as follows:

​

1. Query all data in the set (table) UserModel:

   //select * from UserModel
   db.UserModel.find()
   Copy the code

   

   ​

2. Query all data in the set (table) UserModel in ascending order of UserAge:

   //select * from UserModel order by UserAge ASc db.usermodel.find ().sort({UserAge:1}Copy the code

   

   ​

3. Query all data in the set (table) UserModel, in descending order of UserAge:

   //select * from UserModel order by UserAge desc db.usermodel.find ().sort({UserAge:-1}Copy the code

   

   ​

4. Query the first 5 items in the set (table) UserModel:

   //select top 5 * from UserModel
   db.UserModel.find().limit(5)
   Copy the code

   

   ​

5. Select * from UserModel; select * from UserModel;

   Select * from UserModel where UserId not in(select top 5 UserId from UserModel order by UserId ASC) order by UserId, Db.usermodel.find ().skip(5).sort({UserId:1}) db.usermodel.find ().skip(5).sort({UserId:1})Copy the code

   

   ​

6. Select * from UserModel; select * from UserModel; select * from UserModel;

   Db.usermodel.find ().limit(10).skip((2-1)*10).sort({UserAge:1})Copy the code

   

   ​

7. Select * from UserModel where UserId=6;

   //select * from UserModel where UserId=6
   db.UserModel.find({UserId:6})
   Copy the code

   

   ​

8. Select * from UserModel where Status=1 and UserAge>29;

   //select * from UserModel where Status=1 and UserAge>29
   db.UserModel.find({Status:1,UserAge:{$gt:29}})
   Copy the code

   

   ​

9. Select * from UserModel; select * from UserModel;

   //select * from UseModel where UserName like'%ac%'
   db.UserModel.find({UserName:/ac/})
   Copy the code

   

   ​

10. Query set UserModel UserName prefix contains Qu data:

    //select * from UserModel where UserName like’Qu%’ db.UserModel.find({UserName:/^Qu/})

! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")! [](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/ad6980bc8eac4418bebdba2dc50eb8ca~tplv-k3u1fbpfcp-zoom-1.image)! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")Copy the code

11. Select * from UserModel where UserAge>13;

    //select * from UserModel where UserAge>13 db.UserModel.find({UserAge:{$gt:13}})

! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")! [](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/31d7478b6b474337885408dadcbde834~tplv-k3u1fbpfcp-zoom-1.image)! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")Copy the code

12. Select * from UserModel where UserAge>=13;

    //select * from UserModel where UserAge>=13 db.UserModel.find({UserAge:{$gte:13}})

! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")! [](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/9bd7bb5393a34c59bf2b60adbf9f73c4~tplv-k3u1fbpfcp-zoom-1.image)! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")Copy the code

13. Select * from UserModel where UserAge<13;

    //select * from UserModel where UserAge<13 db.UserModel.find({UserAge:{$lt:13}})

! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")! [](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/381e001dd7594938a0abec74c210deaf~tplv-k3u1fbpfcp-zoom-1.image)! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")Copy the code

14. Select * from UserModel where UserAge<=13;

    //select * from UserModel where UserAge<=13 db.UserModel.find({UserAge:{$lte:13}})

! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")! [](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/b1b683ccea114d75bef3f176f0c24db7~tplv-k3u1fbpfcp-zoom-1.image)! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")Copy the code

15. Select * from UserModel where UserAge>=10 and UserAge<=20;

    //select * from UserModel where UserAge<=10 and UserAge<=20 db.UserModel.find({UserAge:{
    $gte:10,$lte:20}})

! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")! [](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/6c0698742d3f4924b13821dcc7fda11a~tplv-k3u1fbpfcp-zoom-1.image)! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")Copy the code

16. Select * from UserModel where UserAge>45 or UserAge<10; select * from UserModel where UserAge<10;

    //select * from UserModel where UserAge>45 or UserAge<10 order by UserAge asc db.UserModel.find({or:[{UserAge:{gt:45}},{UserAge:{$lt:10}}]}).sort({UserAge:1})

! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")! [](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/a9bf79b470e7430898ab75dcbf4edcc4~tplv-k3u1fbpfcp-zoom-1.image)! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")Copy the code

17. Select * from UserModel where UserName = UserId; select * from UserModel where UserName = UserAge;

    //select UserId,UserName,UserAge from UserModel db.UserModel.find({},{UserId:1,UserName:1,UserAge:1})

! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")! [](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/cb524ae1c6574bd59113e056719f0dc2~tplv-k3u1fbpfcp-zoom-1.image)! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")Copy the code

18. Query all data in the set (table) UserModel and only query all fields except UserId:

    / / select the UserName, UserAge IsNormal, CreateTime from UserModel, query out all the columns except UserId db UserModel. Find ({}, {UserId: 0})

! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")! [](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/107480255e07488fb176791b7c73d2fe~tplv-k3u1fbpfcp-zoom-1.image)! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")Copy the code

19. Select * from UserModel where UserAge>=10 and UserAge<=20; select * from UserModel where UserAge>=10 and UserAge<=20;

    //select UserId,UserName,UserAge from UserModel where UserAge<=10 and UserAge<=20 db.UserModel.find({UserAge:{
    $gte:10,$lte:20}},{UserId:1,UserName:1,UserAge:1})

! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")! [](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/98731b53db5f4957a35708b41e0aae95~tplv-k3u1fbpfcp-zoom-1.image)! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")Copy the code

20. Query a single piece of data in the set (table) UserModel:

    Select top 1 * from UserModel db.usermodel.findone () where db.usermodel.find ().limit(1)

! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")! [](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/12774050729e484bb9b58a067b1d5975~tplv-k3u1fbpfcp-zoom-1.image)! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")Copy the code

21. The total number of entries in the query set (table) UserModel that meet the query criteria:

    // Return the total number of sets that meet the query condition: select count(*) from UserModel where UserAge<=10 and UserAge<=20 db.UserModel.find({UserAge:{ gte:10,gte:10,gte:10,lte:20}}).count()

! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")! [](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/0438ceb276e24598a9295a6c8ed59714~tplv-k3u1fbpfcp-zoom-1.image)! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")Copy the code

22. Query set (table) Total number of entries in UserModel that meet the query criteria (paging condition) :

    If count(1) or count(true) is set, the total number of results returned is the total number of results of the current page query. Rather than the total number of article of db query condition. UserModel. Find ({UserAge: {gte: 10, gte: 10, gte: 10, lte: 20}}), sort ({1} UserAge:). The limit (2). Skip (5 * 2) count (1)

! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")! [](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/439e099eb47f450f82cf75b152c62bca~tplv-k3u1fbpfcp-zoom-1.image)! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")Copy the code

23. Group query on set (table) UserModel by Status field:

    Select count(*) from UserModel group by Status db.userModel. Aggregate ([{group: {_id:’Status’, total :{$sum:1}}}])

! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")! [](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/c59a15d7d4d04b6286e44ca4d6e8984a~tplv-k3u1fbpfcp-zoom-1.image)! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")Copy the code

24. Select * from UserModel; select * from UserModel;

    //select distinct Status from UserModel db.UserModel.distinct(‘Status’)

! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")! [](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/7edac12f74e6488fb1f53a488ecf7214~tplv-k3u1fbpfcp-zoom-1.image)! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")Copy the code

25. Example of a comprehensive query against the collection (table) UserModel:

    SQL > select * from UserModel; SQL > select * from UserModel; Where Status=1 and (UserName like’%Qu%’ or UserName like’Ja%’) and (UserAge>=40 or UserAge<=20) where Status=1 and (UserName like’%Qu%’ or UserName like’Ja%’) UserId,UserName,UserAge,IsNormal,Dtl */ db.UserModel.find( { Status:1, or:[UserName:/Qu/,UserName:/Ja/],or:[{UserName:/Qu/},{UserName:/^Ja/}], or:[UserName:/Qu/,UserName:/Ja/],or:[{UserAge:{gte:40}},{UserAge:{lte:20}}] }, {UserAge UserId: 1, the UserName: 1:1, IsNormal: 1, Dtl: 1}), sort ({1} UserAge:) limit (5). Skip ((2-1) * 5) / /. The count () / / returns the total number of article

! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")! [](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/a9b9242a68144ccb9235a718a61f6ce3~tplv-k3u1fbpfcp-zoom-1.image)! [](data:image/gif; Base64, R0lGODlhAQABAPABAP / / / wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw = = "click and drag to move")Copy the code

The complete code is as follows:

//select * from UserModel db.UserModel.find() //select * from UserModel order by UserAge asc db.UserModel.find().sort({UserAge:1}) //select * from UserModel order by UserAge desc db.UserModel.find().sort({UserAge:-1}) //select top 5 * from UserModel db.UserModel.find().limit(5) //select * from UserModel WHERE UserId not in(select top 5 UserId from UserModel order by UserId ASC) order by UserId, Db.usermodel.find ().skip(5).sort({UserId:1}) // paging query db.usermodel.find ().skip(5).sort({UserId:1}) Db.usermodel.find ().limit(10).skip((2-1)*10).sort({UserAge:1}) //select * from UserModel where UserId=6 db.UserModel.find({UserId:6}) //select * from UserModel where Status=1 and UserAge>29 db.UserModel.find({Status:1,UserAge:{$gt:29}}) //select * from UseModel where UserName like'%ac%' db.UserModel.find({UserName:/ac/}) //select * from UserModel where UserName like'Qu%' db.UserModel.find({UserName:/^Qu/}) //select * from UserModel where UserAge>13 db.UserModel.find({UserAge:{$gt:13}}) //select * from UserModel where UserAge>=13 db.UserModel.find({UserAge:{$gte:13}}) //select * from UserModel where UserAge<13 db.UserModel.find({UserAge:{$lt:13}}) //select * from UserModel where UserAge<=13 db.UserModel.find({UserAge:{$lte:13}}) //select * from UserModel where UserAge<=10 and UserAge<=20 db.UserModel.find({UserAge:{$gte:10,$lte:20}}) //select * from UserModel where UserAge>45 or UserAge<10 order by UserAge  asc db.UserModel.find({$or:[{UserAge:{$gt:45}},{UserAge:{$lt:10}}]}).sort({UserAge:1}) //select UserId,UserName,UserAge  from UserModel db.UserModel.find({},{UserId:1,UserName:1,UserAge:1}) //select UserName,UserAge,IsNormal,CreateTime from UserModel. Db.usermodel. find({},{UserId:0}) //select UserId,UserName,UserAge from UserModel where UserAge<=10 and UserAge<=20 db.UserModel.find({UserAge:{$gte:10,$lte:20}},{UserId:1,UserName:1,UserAge:1}) //select top 1 * from UserModel db.usermodel.findone () = db.usermodel.find ().limit(1) select count(*) from UserModel where UserAge<=10 and UserAge<=20 db.UserModel.find({UserAge:{$gte:10,$lte:20}}).count() If count(1) or count(true) is set, the total number of results returned is the total number of results of the current page query. Db.usermodel. find({UserAge:{$gte:10,$lte:20}}).sort({UserAge:1}).limit(2).skip(5*2).count(1) select Status,count(*) from UserModel group by Status db.UserModel.aggregate([ { $group: { _id:'$Status', {$sum:1}}}]) //select distinct Status from UserModel db.userModel. distinct('Status') // Select distinct Status from UserModel db.userModel Query the data on the second page of UserModel, 5 data on each page: Where Status=1 and (UserName like'%Qu%' or UserName like'Ja%') and (UserAge>=40 or UserAge<=20) where Status=1 and (UserName like'%Qu%' or UserName like'Ja%') UserId,UserName,UserAge,IsNormal,Dtl */ db.UserModel.find( { Status:1, $or:[{UserName:/Qu/},{UserName:/^Ja/}], $or:[{UserAge:{$gte:40}},{UserAge:{$lte:20}}] }, {UserAge UserId: 1, the UserName: 1:1, IsNormal: 1, Dtl: 1}), sort ({1} UserAge:) limit (5). Skip ((2-1) * 5) / /. The count () / / returns the total number of articleCopy the code

​