MongoDB高级查询

袁袁袁袁满
281 阅读7分钟

开启掘金成长之旅！这是我参与「掘金日新计划 · 2 月更文挑战」的第 15 天，点击查看活动详情

数据准备（三国争霸）：

db.test002.insertMany([
    {name: "张飞", hometown: "蜀国", age: 30, sex: "男"},
    {name: "关羽", hometown: "蜀国", age: 40, sex: "男"},
    {name: "刘备", hometown: "蜀国", age: 50, sex: "男"},
    {name: "曹操", hometown: "魏国", age: 45, sex: "男"},
    {name: "司马懿", hometown: "魏国", age: 45, sex: "男"},
    {name: "孙权", hometown: "吴国", age: 50, sex: "男"}
]);

1、数据查询

  • 条件查询：db.集合名称.find({条件⽂档})

  • 查询只返回第⼀个：db.集合名称.findOne({条件⽂档})

  • 将结果格式化：db.集合名称.find({条件⽂档}).pretty()

案例1：

// 查询年龄为50
> db.test002.find({age:50})
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e7"), "name" : "刘备", "hometown" : "蜀国", "age" : 50, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3ea"), "name" : "孙权", "hometown" : "吴国", "age" : 50, "sex" : "男" }

// 查询年龄为45
> db.test002.find({age:45})
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e8"), "name" : "曹操", "hometown" : "魏国", "age" : 45, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e9"), "name" : "司马懿", "hometown" : "魏国", "age" : 45, "sex" : "男" }

案例2：

//查询一个年龄为50
> db.test002.findOne({age:50})
{
        "_id" : ObjectId("6219a246d1ca96a61ceca3e7"),
        "name" : "刘备",
        "hometown" : "蜀国",
        "age" : 50,
        "sex" : "男"
}

//查询一个年龄为45
> db.test002.findOne({age:45})
{
        "_id" : ObjectId("6219a246d1ca96a61ceca3e8"),
        "name" : "曹操",
        "hometown" : "魏国",
        "age" : 45,
        "sex" : "男"
}

案例3：

//美化输出年龄为50
> db.test002.find({age:50}).pretty()
{
        "_id" : ObjectId("6219a246d1ca96a61ceca3e7"),
        "name" : "刘备",
        "hometown" : "蜀国",
        "age" : 50,
        "sex" : "男"
}
{
        "_id" : ObjectId("6219a246d1ca96a61ceca3ea"),
        "name" : "孙权",
        "hometown" : "吴国",
        "age" : 50,
        "sex" : "男"
}

2、比较运算符

等于： 默认是等于判断， 没有运算符

⼩于：$lt （less than）

⼩于等于：$lte （less than equal）

⼤于：$gt （greater than）

⼤于等于：$gte

不等于：$ne

格式：db.集合名称.find({age:{$gte:18}})

案例：

//年龄小于45
> db.test002.find({age:{$lt:45}})
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e5"), "name" : "张飞", "hometown" : "蜀国", "age" : 30, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e6"), "name" : "关羽", "hometown" : "蜀国", "age" : 40, "sex" : "男" }

//年龄小于等于45
> db.test002.find({age:{$lte:45}})
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e5"), "name" : "张飞", "hometown" : "蜀国", "age" : 30, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e6"), "name" : "关羽", "hometown" : "蜀国", "age" : 40, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e8"), "name" : "曹操", "hometown" : "魏国", "age" : 45, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e9"), "name" : "司马懿", "hometown" : "魏国", "age" : 45, "sex" : "男" }

//年龄大于45
> db.test002.find({age:{$gt:45}})
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e7"), "name" : "刘备", "hometown" : "蜀国", "age" : 50, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3ea"), "name" : "孙权", "hometown" : "吴国", "age" : 50, "sex" : "男" }

//年龄大于等于45
> db.test002.find({age:{$gte:45}})
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e7"), "name" : "刘备", "hometown" : "蜀国", "age" : 50, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e8"), "name" : "曹操", "hometown" : "魏国", "age" : 45, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e9"), "name" : "司马懿", "hometown" : "魏国", "age" : 45, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3ea"), "name" : "孙权", "hometown" : "吴国", "age" : 50, "sex" : "男" }

//年龄不等于45
> db.test002.find({age:{$ne:45}})
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e5"), "name" : "张飞", "hometown" : "蜀国", "age" : 30, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e6"), "name" : "关羽", "hometown" : "蜀国", "age" : 40, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e7"), "name" : "刘备", "hometown" : "蜀国", "age" : 50, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3ea"), "name" : "孙权", "hometown" : "吴国", "age" : 50, "sex" : "男" }

3、范围运算符

判断是否在某个范围内：使⽤$in， $nin

格式：db.集合名字.find({age:{$in:[18,28]}})

案例：

//年龄为30,40,50
> db.test002.find({age:{$in:[30,40,50]}})
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e5"), "name" : "张飞", "hometown" : "蜀国", "age" : 30, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e6"), "name" : "关羽", "hometown" : "蜀国", "age" : 40, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e7"), "name" : "刘备", "hometown" : "蜀国", "age" : 50, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3ea"), "name" : "孙权", "hometown" : "吴国", "age" : 50, "sex" : "男" }

//年龄不为30,40,50
> db.test002.find({age:{$nin:[30,40,50]}})
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e8"), "name" : "曹操", "hometown" : "魏国", "age" : 45, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e9"), "name" : "司马懿", "hometown" : "魏国", "age" : 45, "sex" : "男" }

4、逻辑运算符

and：在json中写多个条件即可

格式：db.集合名称.find({条件1, 条件2})

or：使⽤$or， 值为数组， 数组中每个元素为json

格式：db.集合名词.find({$or:[{条件1}, {条件2}]})

案例：

//年龄为40的蜀国人
> db.test002.find({age: 40, hometown: "蜀国"})
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e6"), "name" : "关羽", "hometown" : "蜀国", "age" : 40, "sex" : "男" }

//年龄小于50或者为吴国人
> db.test002.find({$or:[{age:{$lt:50}}, {hometown: "吴国"}]})
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e5"), "name" : "张飞", "hometown" : "蜀国", "age" : 30, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e6"), "name" : "关羽", "hometown" : "蜀国", "age" : 40, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e8"), "name" : "曹操", "hometown" : "魏国", "age" : 45, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e9"), "name" : "司马懿", "hometown" : "魏国", "age" : 45, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3ea"), "name" : "孙权", "hometown" : "吴国", "age" : 50, "sex" : "男" }

5、正则表达式

使⽤//或$regex编写正则表达式

格式1：db.集合名称.find({name:/^张/})

格式2：db.集合名词.find({name:{$regex:'^张'}})

案例：

//查询姓名以张开头
> db.test002.find({name:/^张/})
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e5"), "name" : "张飞", "hometown" : "蜀国", "age" : 30, "sex" : "男" }
> db.test002.find({name:{$regex:'^张'}})
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e5"), "name" : "张飞", "hometown" : "蜀国", "age" : 30, "sex" : "男" }

6、limit和skip

⽤于读取指定数量的⽂档：db.集合名称.find().limit(number)

⽤于跳过指定数量的⽂档：db.集合名称.find().skip(number)

同时使用：

db.集合名称.find().limit(number).skip(number)
或
db.集合名称.find().skip(number).limit(number) //推荐使用效率会更高

案例：

//查询前两条数据
> db.test002.find().limit(2)
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e5"), "name" : "张飞", "hometown" : "蜀国", "age" : 30, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e6"), "name" : "关羽", "hometown" : "蜀国", "age" : 40, "sex" : "男" }

//查询除前两条以外的数据
> db.test002.find().skip(2)
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e7"), "name" : "刘备", "hometown" : "蜀国", "age" : 50, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e8"), "name" : "曹操", "hometown" : "魏国", "age" : 45, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e9"), "name" : "司马懿", "hometown" : "魏国", "age" : 45, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3ea"), "name" : "孙权", "hometown" : "吴国", "age" : 50, "sex" : "男" }

//查询前五条数据中后两条
> db.test002.find().limit(5).skip(3)
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e8"), "name" : "曹操", "hometown" : "魏国", "age" : 45, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e9"), "name" : "司马懿", "hometown" : "魏国", "age" : 45, "sex" : "男" }

//查询后三条数据中的前两条
> db.test002.find().skip(3).limit(2)
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e8"), "name" : "曹操", "hometown" : "魏国", "age" : 45, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e9"), "name" : "司马懿", "hometown" : "魏国", "age" : 45, "sex" : "男" }

7、自定义查询

使⽤$where后⾯写⼀个函数， 返回满⾜条件的数据

格式：

db.集合名称.find({
    $where:function() {
        return this.条件;}
})

案例：

//查询年龄大于40
> db.test002.find({
     $where:function() {
         return this.age>40;}
  })
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e7"), "name" : "刘备", "hometown" : "蜀国", "age" : 50, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e8"), "name" : "曹操", "hometown" : "魏国", "age" : 45, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e9"), "name" : "司马懿", "hometown" : "魏国", "age" : 45, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3ea"), "name" : "孙权", "hometown" : "吴国", "age" : 50, "sex" : "男" }

8、投影

在查询到的返回结果中， 只选择必要的字段：db.集合名称.find({条件（可省略）},{字段名称:1,...})

  • 参数为字段与值， 值为1表示显示， 不显示则不用写

  • 特殊： 对于_id列默认是显示的， 如果不显示需要明确设置为0

案例1：

//显示name和age字段
> db.test002.find({},{name:1, age:1})
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e5"), "name" : "张飞", "age" : 30 }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e6"), "name" : "关羽", "age" : 40 }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e7"), "name" : "刘备", "age" : 50 }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e8"), "name" : "曹操", "age" : 45 }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e9"), "name" : "司马懿", "age" : 45 }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3ea"), "name" : "孙权", "age" : 50 }
  • 可以看出_id列默认是显示的

案例2：

//仅显示name字段
> db.test002.find({},{name:1, _id:0})
{ "name" : "张飞" }
{ "name" : "关羽" }
{ "name" : "刘备" }
{ "name" : "曹操" }
{ "name" : "司马懿" }
{ "name" : "孙权" }

9、排序

对集合进⾏排序：db.集合名称.find().sort({字段:1,...})

  • 参数1为升序排列

  • 参数-1为降序排列

案例：

//根据年龄升序排序
> db.test002.find().sort({age:1})
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e5"), "name" : "张飞", "hometown" : "蜀国", "age" : 30, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e6"), "name" : "关羽", "hometown" : "蜀国", "age" : 40, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e8"), "name" : "曹操", "hometown" : "魏国", "age" : 45, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e9"), "name" : "司马懿", "hometown" : "魏国", "age" : 45, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e7"), "name" : "刘备", "hometown" : "蜀国", "age" : 50, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3ea"), "name" : "孙权", "hometown" : "吴国", "age" : 50, "sex" : "男" }

//根据年龄降序排序
> db.test002.find().sort({age:-1})
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e7"), "name" : "刘备", "hometown" : "蜀国", "age" : 50, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3ea"), "name" : "孙权", "hometown" : "吴国", "age" : 50, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e8"), "name" : "曹操", "hometown" : "魏国", "age" : 45, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e9"), "name" : "司马懿", "hometown" : "魏国", "age" : 45, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e6"), "name" : "关羽", "hometown" : "蜀国", "age" : 40, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e5"), "name" : "张飞", "hometown" : "蜀国", "age" : 30, "sex" : "男" }

//根据name降序，再根据age降序
> db.test002.find().sort({name:-1, age:-1})
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e8"), "name" : "曹操", "hometown" : "魏国", "age" : 45, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e5"), "name" : "张飞", "hometown" : "蜀国", "age" : 30, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3ea"), "name" : "孙权", "hometown" : "吴国", "age" : 50, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e9"), "name" : "司马懿", "hometown" : "魏国", "age" : 45, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e7"), "name" : "刘备", "hometown" : "蜀国", "age" : 50, "sex" : "男" }
{ "_id" : ObjectId("6219a246d1ca96a61ceca3e6"), "name" : "关羽", "hometown" : "蜀国", "age" : 40, "sex" : "男" }

10、统计个数

统计结果集中⽂档条数：

  • db.集合名称.find({条件}).count()
  • db.集合名称.count({条件})

案例：

//统计蜀国人个数
> db.test002.find({hometown: "蜀国"}).count()
3

> db.test002.count({hometown: "蜀国"})
3

注意：如果有find()，需要在find()中写条件

11、去除重复

数据进⾏去重：db.集合名称.distinct('去重字段',{条件})

案例：

//对hometown去重
> db.test002.distinct('hometown')
[ "吴国", "蜀国", "魏国" ]
//对age去重
> db.test002.distinct('age')
[ 30, 40, 45, 50 ]

//对age去重，并且为蜀国人
> db.test002.distinct('age',{hometown:"蜀国"})
[ 30, 40, 50 ]
avatar
文章
阅读
粉丝