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

翻页查询

翻页查询需要使用两个函数，读取指定行数，实现翻页功能
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
  按地理位置获取文档

最后编辑于：2017.12.09 00:43:36

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 217,657评论 6赞 505
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 92,889评论 3赞 394
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 164,057评论 0赞 354
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 58,509评论 1赞 293
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 67,562评论 6赞 392
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 51,443评论 1赞 302
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 40,251评论 3赞 418
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 39,129评论 0赞 276
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 45,561评论 1赞 314
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,779评论 3赞 335
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,902评论 1赞 348
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,621评论 5赞 345
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 41,220评论 3赞 328
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,838评论 0赞 22
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,971评论 1赞 269
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 48,025评论 2赞 370
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,843评论 2赞 354

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

翻页查询

排序

聚合

推荐阅读更多精彩内容