美文网首页
mongo学习（二）——常用查询技巧

mongo学习（二）——常用查询技巧

作者: 小明滚出去_ | 来源:发表于2017-07-19 11:46 被阅读0次

    翻页查询

    • 翻页查询需要使用两个函数，读取指定行数，实现翻页功能
    • db.col.find().skip(number)跳过指定行数
    • db.col.find().limit(number)读取指定条数数据
    > db.c_cons.find()
{ "_id" : 1574824688, "name" : "李大爷", "addr" : "狗蛋村", "mpId" : 10558463215, "status" : 0 }
{ "_id" : 1574824690, "name" : "邹老湿", "addr" : "丫蛋村", "mpId" : 10558463125, "status" : 0 }
{ "_id" : 1574824698, "name" : "李大爷", "addr" : "狗蛋村", "mpId" : 10558463211, "status" : 0 }
{ "_id" : 1574824699, "name" : "老王", "addr" : "隔壁", "mpId" : 10558463211, "status" : 0 }
> db.c_cons.find().skip(2).limit(2)
{ "_id" : 1574824698, "name" : "李大爷", "addr" : "狗蛋村", "mpId" : 10558463211, "status" : 0 }
{ "_id" : 1574824699, "name" : "老王", "addr" : "隔壁", "mpId" : 10558463211, "status" : 0 }

    即skip中传入起始行，limit中传入每页条数
    注意：skip、limit的调用顺序不影响查询结果，执行的时候按照sort - > skip -> limit顺序执行

    排序

    db.COLLECTION_NAME.find().sort({KEY:1})

    • 按指定字段排序 1升序 -1降序
    > db.c_cons.find().sort({"mpId":1})
{ "_id" : 1574824690, "name" : "邹老湿", "addr" : "丫蛋村", "mpId" : 10558463125, "status" : 0 }
{ "_id" : 1574824698, "name" : "李大爷", "addr" : "狗蛋村", "mpId" : 10558463211, "status" : 0 }
{ "_id" : 1574824699, "name" : "老王", "addr" : "隔壁", "mpId" : 10558463211, "status" : 0 }
{ "_id" : 1574824688, "name" : "李大爷", "addr" : "狗蛋村", "mpId" : 10558463215, "status" : 0 }
> db.c_cons.find().sort({"mpId":-1})
{ "_id" : 1574824688, "name" : "李大爷", "addr" : "狗蛋村", "mpId" : 10558463215, "status" : 0 }
{ "_id" : 1574824698, "name" : "李大爷", "addr" : "狗蛋村", "mpId" : 10558463211, "status" : 0 }
{ "_id" : 1574824699, "name" : "老王", "addr" : "隔壁", "mpId" : 10558463211, "status" : 0 }
{ "_id" : 1574824690, "name" : "邹老湿", "addr" : "丫蛋村", "mpId" : 10558463125, "status" : 0 }

    聚合

    db.COLLECTION_NAME..aggregate( [pipeline], <optional params> )

    • 数据准备
    > var score=[
    {_id:1,name:"甲子","subjectId":1,score:88},
    {_id:2,name:"甲子","subjectId":2,score:81},
    {_id:3,name:"甲子","subjectId":3,score:98},
    {_id:4,name:"乙丑","subjectId":1,score:78},
    {_id:5,name:"乙丑","subjectId":2,score:91},
    {_id:6,name:"乙丑","subjectId":3,score:95},
    {_id:7,name:"丙寅","subjectId":1,score:85},
    {_id:8,name:"丙寅","subjectId":2,score:71},
    {_id:9,name:"丙寅","subjectId":3,score:66},
    {_id:10,name:"丁卯","subjectId":1,score:89},
    {_id:11,name:"丁卯","subjectId":2,score:78},
    {_id:12,name:"丁卯","subjectId":3,score:77},
    {_id:13,name:"戊辰","subjectId":1,score:85},
    {_id:14,name:"戊辰","subjectId":2,score:98},
    {_id:15,name:"戊辰","subjectId":3,score:98},
    {_id:16,name:"己巳","subjectId":1,score:88},
    {_id:17,name:"己巳","subjectId":2,score:88},
    {_id:18,name:"己巳","subjectId":3,score:78},
    {_id:19,name:"庚午","subjectId":1,score:93},
    {_id:20,name:"庚午","subjectId":2,score:91},
    {_id:21,name:"庚午","subjectId":3,score:88},
    {_id:22,name:"辛未","subjectId":1,score:97},
    {_id:23,name:"辛未","subjectId":2,score:96},
    {_id:24,name:"辛未","subjectId":3,score:78},
    {_id:25,name:"壬申","subjectId":1,score:88},
    {_id:26,name:"壬申","subjectId":2,score:82},
    {_id:27,name:"壬申","subjectId":3,score:94},
    {_id:28,name:"癸酉","subjectId":1,score:88},
    {_id:29,name:"癸酉","subjectId":2,score:91},
    {_id:30,name:"癸酉","subjectId":3,score:86}
]
> db.score.insert(score)
BulkWriteResult({
        "writeErrors" : [ ],
        "writeConcernErrors" : [ ],
        "nInserted" : 30,
        "nUpserted" : 0,
        "nMatched" : 0,
        "nModified" : 0,
        "nRemoved" : 0,
        "upserted" : [ ]
})
    • 聚合表达式
      • 求和 $sum
        按学生分组求出学生的总分
      > db.score.aggregate([{$group:{
      _id:"$name",
      total:{$sum:"$score"}
     }}])
{ "_id" : "壬申", "total" : 264 }
{ "_id" : "癸酉", "total" : 265 }
{ "_id" : "辛未", "total" : 271 }
{ "_id" : "己巳", "total" : 254 }
{ "_id" : "戊辰", "total" : 281 }
{ "_id" : "乙丑", "total" : 264 }
{ "_id" : "丙寅", "total" : 222 }
{ "_id" : "甲子", "total" : 267 }
{ "_id" : "庚午", "total" : 272 }
{ "_id" : "丁卯", "total" : 244 }
      • 求平均值 $avg
        求出每个学生的平均分
      > db.score.aggregate([{$group:{_id:"$name",avg:{$avg:"$score"}}}])
      • 取最低分 $min
        求出学生科目最低分
      > db.score.aggregate([{$group:{_id:"$name",min:{$min:"$score"}}}])
      • 取最高分 $max
        求出学生科目最高分
      > db.score.aggregate([{$group:{_id:"$name",max:{$max:"$score"}}}])
      • 在结果文档中插入一个数组 $push
        学生名和对应的科目信息
      > db.score.aggregate([{$group:{_id:"$name",scores:{$push: {score:"$score",subjectId:"$subjectId"}}}}]).pretty()

    {
    "_id" : "壬申",
    "scores" : [
    {
    "score" : 88,
    "subjectId" : 1
    },
    {
    "score" : 82,
    "subjectId" : 2
    },
    {
    "score" : 94,
    "subjectId" : 3
    }
    ]
    }
    {
    "_id" : "癸酉",
    "scores" : [
    {
    "score" : 88,
    "subjectId" : 1
    },
    {
    "score" : 91,
    "subjectId" : 2
    },
    {
    "score" : 86,
    "subjectId" : 3
    }
    ]
    }
    <省略部分结果>

        - 使用$push可以任意拼装自己想要的结果集
    - $push时指定"$$ROOT"将会把当前document加入数组
    ```
    db.score.aggregate([
    {$group:{
        _id:"$name",
        doms:{$push:"$$ROOT"}
    }}
])
    ```

  - 往结果中插入一个数组 $addToSet
    - 功能为$push一样，唯一的不同是当数组原始相同时，$push会加入重复元素，而$addToSet不会
  学生的成绩数组

    db.score.aggregate([{$group:{_id:"$name",scores:{$push: "$score"}}}])
    { "_id" : "壬申", "scores" : [ 88, 88, 94 ] }
    { "_id" : "癸酉", "scores" : [ 88, 91, 86 ] }
    { "_id" : "辛未", "scores" : [ 97, 96, 78 ] }
    { "_id" : "己巳", "scores" : [ 88, 88, 78 ] }
    { "_id" : "戊辰", "scores" : [ 85, 98, 98 ] }
    db.score.aggregate([{$group:{_id:"$name",scores:{$addToSet: "$score"}}}])
    { "_id" : "壬申", "scores" : [ 94, 88 ] }
    { "_id" : "癸酉", "scores" : [ 91, 86, 88 ] }
    { "_id" : "辛未", "scores" : [ 78, 96, 97 ] }
    { "_id" : "己巳", "scores" : [ 78, 88 ] }
    { "_id" : "戊辰", "scores" : [ 98, 85 ] }

    - 获取第一个document $first

    db.score.aggregate([{$group:{_id:"$name",firstSubject:{$first: "$subjectId"}}}])

      - 获取最后一个document $last

    db.score.aggregate([{$group:{_id:"$name",lastSubject:{$last: "$subjectId"}}}])

    - pipeline 管道
- $project 修改输入文档的结构
类似于SQL中的as和子查询，可以定义结果集

    db.score.aggregate({$project:{_id:0}})
    { "name" : "甲子", "subjectId" : 1, "score" : 88 }
    { "name" : "甲子", "subjectId" : 2, "score" : 81 }
    { "name" : "甲子", "subjectId" : 3, "score" : 98 }
    db.score.find({},{_id:0})
    { "name" : "甲子", "subjectId" : 1, "score" : 88 }
    { "name" : "甲子", "subjectId" : 2, "score" : 81 }
    { "name" : "甲子", "subjectId" : 3, "score" : 98 }

    两个命令执行结果一致
  - $match 用于过滤数据
  放在group前相当于where使用,放在group后面相当于having使用
  例：统计各科大于90分的人数

    db.score.aggregate([
    ... {$match:{
    ... score:{$gt:90}
    ... }},
    ... {$group:{
    ... _id:"$subjectId",
    ... count:{$sum:1}
    ... }}
    ... ])
    { "_id" : 1, "count" : 2 }
    { "_id" : 2, "count" : 5 }
    { "_id" : 3, "count" : 4 }

      例：统计平均分大于90的学生

    db.score.aggregate([
    ... {$group:{

    ... _id:"$name",
    ... avg:{$avg:"$score"}
    ... }},
    ... {$match:{
    ... avg:{$gt:90}
    ... }}
    ... ])
    { "_id" : "辛未", "avg" : 90.33333333333333 }
    { "_id" : "戊辰", "avg" : 93.66666666666667 }
    { "_id" : "庚午", "avg" : 90.66666666666667 }

    - $limit $skip 取指定数据

    db.score.aggregate([{$skip:6},{$group:{_id:"$name",avg:{$avg:"$score"}}}])
    { "_id" : "壬申", "avg" : 90 }
    { "_id" : "癸酉", "avg" : 88.33333333333333 }
    { "_id" : "辛未", "avg" : 90.33333333333333 }
    { "_id" : "戊辰", "avg" : 93.66666666666667 }
    { "_id" : "己巳", "avg" : 84.66666666666667 }
    { "_id" : "庚午", "avg" : 90.66666666666667 }
    { "_id" : "丁卯", "avg" : 81.33333333333333 }
    { "_id" : "丙寅", "avg" : 74 }
    db.score.aggregate([{$group:{_id:"$name",avg:{$avg:"$score"}}},{$skip:5}])
    { "_id" : "乙丑", "avg" : 88 }
    { "_id" : "丙寅", "avg" : 74 }
    { "_id" : "甲子", "avg" : 89 }
    { "_id" : "庚午", "avg" : 90.66666666666667 }
    { "_id" : "丁卯", "avg" : 81.33333333333333 }
    db.score.aggregate([{$group:{_id:"$name",avg:{$avg:"$score"}}},{$skip:5},{$limit:2}])
    { "_id" : "乙丑", "avg" : 88 }
    { "_id" : "丙寅", "avg" : 74 }

      - $unwind 拆分文档中的数组形成多个文档
  例：将各科目前三名拆分成多条

    db.score.aggregate([
    ... {$sort:{
    ... score:-1
    ... }},
    ... {$group:{
    ... _id:"$subjectId",
    ... maxScores:{$push:{student:"$name",score:"$score"}}
    ... }},
    ... {$project:{
    ... maxScores : {
    ... $slice : ["$maxScores",0,3]
    ... }
    ... }},
    ... {$unwind:"$maxScores"}
    ... ])
    { "_id" : 1, "maxScores" : { "student" : "辛未", "score" : 97 } }
    { "_id" : 1, "maxScores" : { "student" : "庚午", "score" : 93 } }
    { "_id" : 1, "maxScores" : { "student" : "丁卯", "score" : 89 } }
    { "_id" : 2, "maxScores" : { "student" : "戊辰", "score" : 98 } }
    { "_id" : 2, "maxScores" : { "student" : "辛未", "score" : 96 } }
    { "_id" : 2, "maxScores" : { "student" : "乙丑", "score" : 91 } }
    { "_id" : 3, "maxScores" : { "student" : "甲子", "score" : 98 } }
    { "_id" : 3, "maxScores" : { "student" : "戊辰", "score" : 98 } }
    { "_id" : 3, "maxScores" : { "student" : "乙丑", "score" : 95 } }

      - $month $dayOfMonth $year
  取日期的年、月、日
  - $sort 排序
  例： 按学生平均分排序

    db.score.aggregate([
    ... {$group:{
    ... _id:"$name",
    ... avg:{$avg:"$score"}
    ... }},
    ... {$sort:{
    ... avg:-1
    ... }}
    ... ])
    { "_id" : "戊辰", "avg" : 93.66666666666667 }
    { "_id" : "庚午", "avg" : 90.66666666666667 }
    { "_id" : "辛未", "avg" : 90.33333333333333 }
    { "_id" : "壬申", "avg" : 90 }
    { "_id" : "甲子", "avg" : 89 }
    { "_id" : "癸酉", "avg" : 88.33333333333333 }
    { "_id" : "乙丑", "avg" : 88 }
    { "_id" : "己巳", "avg" : 84.66666666666667 }
    { "_id" : "丁卯", "avg" : 81.33333333333333 }
    { "_id" : "丙寅", "avg" : 74 }

      - geoNear
  按地理位置获取文档

    相关文章

      网友评论

          本文标题：mongo学习（二）——常用查询技巧

          本文链接：https://www.haomeiwen.com/subject/cuackxtx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|mongo学习（二）——常用查询技巧|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！