MongoDB的查询语句示例说明

Quber
114 阅读3分钟

​

假如我们在MongoDB服务器中有一个数据库，该数据中有一个数据集合（表）UserModel，存储的数据以及字段如下图所示：
​

  1. 查询集合（表）UserModel中的所有数据：

    //select * from UserModel
db.UserModel.find()

    ​

  2. 查询集合（表）UserModel中的所有数据，按照UserAge升序排列：

    //select * from UserModel order by UserAge asc
db.UserModel.find().sort({UserAge:1})//其中的1代表升序

    ​

  3. 查询集合（表）UserModel中的所有数据，按照UserAge降序排列：

    //select * from UserModel order by UserAge desc
db.UserModel.find().sort({UserAge:-1})//其中的-1代表降序

    ​

  4. 查询集合（表）UserModel中的前5条数据：

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

    ​

  5. 查询集合（表）UserModel按照UserId升序查询前5条数据以后的所有数据：

    //select * from UserModel where UserId not in(select top 5 UserId from UserModel order by UserId asc) order by UserId，按照UserId升序查询前5条数据以后的所有数据
db.UserModel.find().skip(5).sort({UserId:1})

    ​

  6. 查询集合（表）UserModel按照UserAge升序、第二页、每页10条的数据：

    //分页查询，按照UserAge升序，查询第二页的数据，每页10条数据（下面语句中的2*10代表第二页*10）
db.UserModel.find().limit(10).skip((2-1)*10).sort({UserAge:1})

    ​

  7. 查询集合（表）UserModel中UserId=6的数据：

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

    ​

  8. 查询集合（表）UserModel中Status=1并且UserAge>29的数据：

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

    ​

  9. 查询集合（表）UserModel中UserName包含ac的数据：

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

    ​

  10. 查询集合（表）UserModel中UserName的前缀包含Qu的数据：

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

![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")

![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/ad6980bc8eac4418bebdba2dc50eb8ca~tplv-k3u1fbpfcp-zoom-1.image)![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")​

11. 查询集合（表）UserModel中UserAge>13的数据：

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

![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")

![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/31d7478b6b474337885408dadcbde834~tplv-k3u1fbpfcp-zoom-1.image)![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")​

12. 查询集合（表）UserModel中UserAge>=13的数据：

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

![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")

![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/9bd7bb5393a34c59bf2b60adbf9f73c4~tplv-k3u1fbpfcp-zoom-1.image)![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")​

13. 查询集合（表）UserModel中UserAge<13的数据：

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

![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")

![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/381e001dd7594938a0abec74c210deaf~tplv-k3u1fbpfcp-zoom-1.image)![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")​

14. 查询集合（表）UserModel中UserAge<=13的数据：

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

![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")

![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/b1b683ccea114d75bef3f176f0c24db7~tplv-k3u1fbpfcp-zoom-1.image)![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")​

15. 查询集合（表）UserModel中UserAge>=10并且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== "点击并拖拽以移动")

![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/6c0698742d3f4924b13821dcc7fda11a~tplv-k3u1fbpfcp-zoom-1.image)![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")​

16. 查询集合（表）UserModel中UserAge>45或UserAge<10的数据，并按照UserAge升序排列：

    //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== "点击并拖拽以移动")

![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/a9bf79b470e7430898ab75dcbf4edcc4~tplv-k3u1fbpfcp-zoom-1.image)![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")​

17. 查询集合（表）UserModel中的所有数据，并只查询UserId,UserName,UserAge这3个字段：

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

![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")

![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/cb524ae1c6574bd59113e056719f0dc2~tplv-k3u1fbpfcp-zoom-1.image)![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")​

18. 查询集合（表）UserModel中的所有数据，并只查询除UserId以外的所有字段：

    //select UserName,UserAge,IsNormal,CreateTime from UserModel，查询出除UserId以外的所有列
    db.UserModel.find({},{UserId:0})

![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")

![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/107480255e07488fb176791b7c73d2fe~tplv-k3u1fbpfcp-zoom-1.image)![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")​

19. 查询集合（表）UserModel中UserAge>=10并且UserAge<=20的数据，并只查询出UserId,UserName,UserAge这3个字段：

    //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== "点击并拖拽以移动")

![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/98731b53db5f4957a35708b41e0aae95~tplv-k3u1fbpfcp-zoom-1.image)![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")​

20. 查询集合（表）UserModel中的一条数据：

    //select top 1 * from UserModel
    db.UserModel.findOne() 等价于 db.UserModel.find().limit(1)

![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")

![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/12774050729e484bb9b58a067b1d5975~tplv-k3u1fbpfcp-zoom-1.image)![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")​

21. 查询集合（表）UserModel中满足查询条件的总条数：

    //返回满足查询条件的集合总条数：select count(*) from UserModel where UserAge<=10 and UserAge<=20
    db.UserModel.find({UserAge:{$gte:10,$lte:20}}).count()

![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")

![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/0438ceb276e24598a9295a6c8ed59714~tplv-k3u1fbpfcp-zoom-1.image)![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")​

22. 查询集合（表）UserModel中满足查询条件的总条数（分页情况）：

    //如果是分页的查询，如果设置count(1)或count(true)，则返回的条数就是当前页查询的结果总条数，而不是查询条件的总条数
    db.UserModel.find({UserAge:{$gte:10,$lte:20}}).sort({UserAge:1}).limit(2).skip(5*2).count(1)

![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")

![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/439e099eb47f450f82cf75b152c62bca~tplv-k3u1fbpfcp-zoom-1.image)![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")​

23. 对集合（表）UserModel进行分组查询，按Status字段进行分组：

    //分组查询，select Status,count(*) from UserModel group by Status
    db.UserModel.aggregate([
    	{
    		$group:
    		{
    			_id:'$Status',
    			总数:{$sum:1}
    		}
    	}
    ])

![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")

![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/c59a15d7d4d04b6286e44ca4d6e8984a~tplv-k3u1fbpfcp-zoom-1.image)![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")​

24. 对集合（表）UserModel中的Status字段去重查询：

    //select distinct Status from UserModel
    db.UserModel.distinct('Status')

![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")

![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/7edac12f74e6488fb1f53a488ecf7214~tplv-k3u1fbpfcp-zoom-1.image)![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")​

25. 对集合（表）UserModel进行综合查询示例：

    //综合查询示例语句（分页查询）
    /*
    	查询UserModel的第二页数据，每页5条数据
    	查询条件为：where Status=1 and (UserName like'%Qu%' or UserName like'Ja%') and (UserAge>=40 or UserAge<=20)
      查询的字段为：UserId,UserName,UserAge,IsNormal,Dtl
    */
    db.UserModel.find(
    	{
    	    Status:1,
    		$or:[{UserName:/Qu/},{UserName:/^Ja/}],
    		$or:[{UserAge:{$gte:40}},{UserAge:{$lte:20}}]
    	},
    	{UserId:1,UserName:1,UserAge:1,IsNormal:1,Dtl:1}
    )
    .sort({UserAge:1})
    .limit(5).skip((2-1)*5)
    //.count()  //返回总条数

![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")

![](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/a9b9242a68144ccb9235a718a61f6ce3~tplv-k3u1fbpfcp-zoom-1.image)![](data:image/gif;base64,R0lGODlhAQABAPABAP///wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw== "点击并拖拽以移动")​

完整代码如下：

//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，按照UserId升序查询前5条数据以后的所有数据
db.UserModel.find().skip(5).sort({UserId:1})

//分页查询，按照UserAge升序，查询第二页的数据，每页10条数据（下面语句中的2*10代表第二页*10）
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，查询出除UserId以外的所有列
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()

//如果是分页的查询，如果设置count(1)或count(true)，则返回的条数就是当前页查询的结果总条数，而不是查询条件的总条数
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')

//综合查询示例语句（分页查询）
/*
	查询UserModel的第二页数据，每页5条数据
	查询条件为：where Status=1 and (UserName like'%Qu%' or UserName like'Ja%') and (UserAge>=40 or UserAge<=20)
  查询的字段为：UserId,UserName,UserAge,IsNormal,Dtl
*/
db.UserModel.find(
	{
	    Status:1,
		$or:[{UserName:/Qu/},{UserName:/^Ja/}],
		$or:[{UserAge:{$gte:40}},{UserAge:{$lte:20}}]
	},
	{UserId:1,UserName:1,UserAge:1,IsNormal:1,Dtl:1}
)
.sort({UserAge:1})
.limit(5).skip((2-1)*5)
//.count()  //返回总条数

​

avatar
文章
阅读
粉丝