mongoDB学习笔记三

第四章 查询

 

一 find

 

#查询所有记录

> db.users.find()

{ "_id" :ObjectId("586b40507dfc144b68de4071"), "relationships" : {"enemies" : 2, "friend" : 32 }, "username" :"retacn yue", "age" : 33, "sex" : "ma

le", "location" :"ZiBo", "emails" : [ "[e-mail]","[e-mail]", "[e-mail]","[e-mail]" ] }

 

#查询一条记录

> db.users.findOne()

{

       "_id" : ObjectId("586b40507dfc144b68de4071"),

       "relationships" : {

               "enemies" : 2,

                "friend" : 32

       },

       "username" : "retacn yue",

       "age" : 33,

       "sex" : "male",

       "location" : "ZiBo",

       "emails" : [

               "[e-mail]",

                "[e-mail]",

               "[e-mail]",

                "[e-mail]"

       ]

}

 

#查询指定条件的记录

> db.users.find({"age":33})

{ "_id" :ObjectId("586b40507dfc144b68de4071"), "relationships" : {"enemies" : 2, "friend" : 32 }, "username" :"retacn yue", "age" : 33, "sex" : "ma

le", "location" :"ZiBo", "emails" : [ "[e-mail]","[e-mail]", "[e-mail]","[e-mail]" ] }

 

#按组合条件查询

>db.users.find({"age":33,"username":"retacn yue"})

{ "_id" :ObjectId("586b40507dfc144b68de4071"), "relationships" : {"enemies" : 2, "friend" : 32 }, "username" :"retacn yue", "age" : 33, "sex" : "ma

le", "location" :"ZiBo", "emails" : [ "[e-mail]","[e-mail]", "[e-mail]", "[e-mail]"] }

> 

 

 

指定返回的键

 

#使用第二个参数指定返回键

>db.users.find({},{"username":1,"emails":1})

{ "_id" :ObjectId("586b40507dfc144b68de4071"), "username" :"retacn yue", "emails" : ["[e-mail]", "[e-mail]","zhenhuayue@so

ho.com", "[e-mail]"] }

> 

 

#指定不需要返回的键

>db.users.find({},{"age":0,"_id":0})

{ "relationships" : {"enemies" : 2, "friend" : 32 }, "username" :"retacn yue", "sex" : "male","location" : "ZiBo", "emails" : ["zhenhuayue@hotmail

.com", "[e-mail]","[e-mail]", "[e-mail]" ] }

> 

 

 

限制

查询文档的值必须是常量

 

二查询条件

$lt  <

$lte <=

$gt  >

$gte >=

 

#添加用户

>db.users.insert({"username":'ann',"age":30,"sex":"femail","location":"BoShan","relationships":{"enemies":1,"friend":20},"emails":["[e-mail]","ann

@sina.com"]})

 

>db.users.insert({"username":'andy',"age":18,"sex":"femail","location":"ZhangDian","relationships":{"enemies":2,"friend":15},"emails":["[e-mail]"

,"[e-mail]"]})

 

>db.users.insert({"username":'mike',"age":15,"sex":"mail","location":"HuanTai","relationships":{"enemies":3,"friend":18},"emails":["[e-mail]","mi

[e-mail]"]})

 

#查询18到30岁的用户

>db.users.find({"age":{"$gte":18,"$lte":30}})

{ "_id" :ObjectId("58708a439193775a5f6b5f14"), "username" :"ann", "age" : 30, "sex" : "femail","location" : "BoShan", "relationships" : {"enemies"

 : 1,"friend" : 20 }, "emails" : [ "[e-mail]","[e-mail]" ] }

{ "_id" :ObjectId("58708ab49193775a5f6b5f15"), "username" :"andy", "age" : 18, "sex" : "femail","location" : "ZhangDian", "relationships" : {"enem

ies" : 2, "friend" : 15 },"emails" : [ "[e-mail]", "[e-mail]" ] }

 

 

Or查询

$in  查询一个键的多个值

$or  多个键值的任意给定值

 

 

#in条件查询

>db.users.find({"location":{"$in":["BoShan","ZhangDian"]}})

{ "_id" :ObjectId("58708a439193775a5f6b5f14"), "username" :"ann", "age" : 30, "sex" : "femail","location" : "BoShan", "relationships" : {"enemies"

 : 1,"friend" : 20 }, "emails" : [ "[e-mail]","[e-mail]" ] }

{ "_id" :ObjectId("58708ab49193775a5f6b5f15"), "username" :"andy", "age" : 18, "sex" : "femail","location" : "ZhangDian", "relationships" : {"enem

ies" : 2, "friend" : 15 },"emails" : [ "[e-mail]", "[e-mail]" ] }

> 

 

#nin条件查询

>db.users.find({"location":{"$nin":["BoShan","ZhangDian"]}})

{ "_id" : ObjectId("586b40507dfc144b68de4071"),"relationships" : { "enemies" : 2, "friend" : 32}, "username" : "retacn yue", "age" : 33,"sex" : "ma

le", "location" :"ZiBo", "emails" : [ "[e-mail]","[e-mail]", "[e-mail]","[e-mail]" ] }

{ "_id" :ObjectId("58708aeb9193775a5f6b5f16"), "username" :"mike", "age" : 15, "sex" : "mail","location" : "HuanTai", "relationships" : {"enemies"

 : 3,"friend" : 18 }, "emails" : [ "[e-mail]","[e-mail]" ] }

 

 

#or条件查询

> db.users.find({"$or":[{"sex":"male"},{"age":18}]})

{ "_id" :ObjectId("586b40507dfc144b68de4071"), "relationships" : {"enemies" : 2, "friend" : 32 }, "username" :"retacn yue", "age" : 33, "sex" : "ma

le", "location" :"ZiBo", "emails" : [ "[e-mail]","[e-mail]", "[e-mail]","[e-mail]" ] }

{ "_id" :ObjectId("58708aeb9193775a5f6b5f16"), "username" :"mike", "age" : 15, "sex" : "male","location" : "HuanTai", "relationships" : {"enemies"

 : 3,"friend" : 18 }, "emails" : [ "[e-mail]","[e-mail]" ] }

{ "_id" :ObjectId("58708e5e9193775a5f6b5f18"), "username" :"andy", "age" : 18, "sex" : "female","location" : "ZhangDian", "relationships" : {"enem

ies" : 2, "friend" : 15 },"emails" : [ "[e-mail]", "[e-mail]" ] }

> 

 

#not条件查询

>db.users.find({"age":{"$not":{"$mod":[5,0]}}})

{ "_id" :ObjectId("586b40507dfc144b68de4071"), "relationships" : {"enemies" : 2, "friend" : 32 }, "username" :"retacn yue", "age" : 33, "sex" : "ma

le", "location" :"ZiBo", "emails" : [ "[e-mail]","[e-mail]", "[e-mail]","[e-mail]" ] }

{ "_id" :ObjectId("58708e5e9193775a5f6b5f18"), "username" :"andy", "age" : 18, "sex" : "female","location" : "ZhangDian", "relationships" : {"enem

ies" : 2, "friend" : 15 },"emails" : [ "[e-mail]", "[e-mail]" ] }

> 

 

条件语句的规则

一个键可以有多个条件,不能有多个修改器

 

三特定类型的查询条件

 

#null查询条件

#查询键值为null

>db.users.find({"state":null})

{ "_id" :ObjectId("586b40507dfc144b68de4071"), "relationships" : {"enemies" : 2, "friend" : 32 }, "username" :"retacn yue", "age" : 33, "sex" : "ma

le", "location" :"ZiBo", "emails" : [ "[e-mail]","[e-mail]", "[e-mail]","[e-mail]" ], "state" : null }

 

#查询键值不存的且值为 null

> db.users.find({"states":null})

{ "_id" :ObjectId("586b40507dfc144b68de4071"), "relationships" : {"enemies" : 2, "friend" : 32 }, "username" :"retacn yue", "age" : 33, "sex" : "ma

le", "location" :"ZiBo", "emails" : [ "[e-mail]","[e-mail]", "[e-mail]","[e-mail]" ], "state" : null }

 

{ "_id" :ObjectId("58708a439193775a5f6b5f14"), "username" :"ann", "age" : 30, "sex" : "female","location" : "BoShan", "relationships" : {"enemies"

 : 1,"friend" : 20 }, "emails" : [ "[e-mail]","[e-mail]" ], "state" : "T" }

{ "_id" :ObjectId("58708aeb9193775a5f6b5f16"), "username" :"mike", "age" : 15, "sex" : "male","location" : "HuanTai", "relationships" : {"enemies"

 : 3,"friend" : 18 }, "emails" : [ "[e-mail]","[e-mail]" ], "state" : "F" }

{ "_id" :ObjectId("58708e5e9193775a5f6b5f18"), "username" :"andy", "age" : 18, "sex" : "female","location" : "ZhangDian", "relationships" : {"enem

ies" : 2, "friend" : 15 },"emails" : [ "[e-mail]", "[e-mail]" ],"state" : "T" }

 

#查询键值是否为null,还在检查键值是否存在

>db.users.find({"states":{"$in":[null],"$exists":true}})

> 

 

 

正则表达式

>db.users.find({"username":/^an/})

{ "_id" :ObjectId("58708a439193775a5f6b5f14"), "username" :"ann", "age" : 30, "sex" : "female","location" : "BoShan", "relationships" : {"enemies"

 : 1,"friend" : 20 }, "emails" : [ "[e-mail]","[e-mail]" ], "state" : "T" }

{ "_id" :ObjectId("58708e5e9193775a5f6b5f18"), "username" :"andy", "age" : 18, "sex" : "female","location" : "ZhangDian", "relationships" : {"enem

ies" : 2, "friend" : 15 },"emails" : [ "[e-mail]", "[e-mail]" ],"state" : "T" }

 

 

 

查询数组

>db.users.find({"emails":"[e-mail]"})

{ "_id" :ObjectId("586b40507dfc144b68de4071"), "relationships" : {"enemies" : 2, "friend" : 32 }, "username" :"retacn yue", "age" : 33, "sex" : "ma

le", "location" :"ZiBo", "emails" : [ "[e-mail]","[e-mail]", "[e-mail]","[e-mail]" ], "state" : null }

 

 

#all

#查询用户信息

> db.users.find()

{ "_id" :ObjectId("586b40507dfc144b68de4071"), "relationships" : {"enemies" : 2, "friend" : 32 }, "username" :"retacn yue", "age" : 33, "sex" : "ma

le", "location" :"ZiBo", "emails" : [ "[e-mail]","[e-mail]", "[e-mail]","[e-mail]" ], "state" : null,

"languages" : [ "java","python", "c", "c++" ] }

{ "_id" :ObjectId("58708a439193775a5f6b5f14"), "username" :"ann", "age" : 30, "sex" : "female","location" : "BoShan", "relationships" : {"enemies"

 : 1,"friend" : 20 }, "emails" : [ "[e-mail]","[e-mail]" ], "state" : "T","languages" : [ "java", "python", "c++"] }

{ "_id" :ObjectId("58708aeb9193775a5f6b5f16"), "username" :"mike", "age" : 15, "sex" : "male","location" : "HuanTai", "relationships" : {"enemies"

 : 3,"friend" : 18 }, "emails" : [ "[e-mail]","[e-mail]" ], "state" : "F","languages" : [ "java", "c++" ] }

{ "_id" :ObjectId("58708e5e9193775a5f6b5f18"), "username" :"andy", "age" : 18, "sex" : "female","location" : "ZhangDian", "relationships" : {"enem

ies" : 2, "friend" : 15 },"emails" : [ "[e-mail]", "[e-mail]" ],"state" : "T", "languages" : [ "java","c", "c++" ] }

> 

 

#通过多个元素来匹配数组

>db.users.find({"languages":{"$all":["java","python"]}})

{ "_id" : ObjectId("586b40507dfc144b68de4071"),"relationships" : { "enemies" : 2, "friend" : 32}, "username" : "retacn yue", "age" : 33,"sex" : "ma

le", "location" :"ZiBo", "emails" : [ "[e-mail]","[e-mail]", "[e-mail]", "[e-mail]"], "state" : null,

"languages" : [ "java","python", "c", "c++" ] }

{ "_id" :ObjectId("58708a439193775a5f6b5f14"), "username" :"ann", "age" : 30, "sex" : "female","location" : "BoShan", "relationships" : {"enemies"

 : 1,"friend" : 20 }, "emails" : [ "[e-mail]","[e-mail]" ], "state" : "T","languages" : [ "java", "python", "c++"] }

> 

 

 

 

#查询指定长度的数组$size

>db.users.find({"languages":{"$size":4}})

{ "_id" :ObjectId("586b40507dfc144b68de4071"), "relationships" : {"enemies" : 2, "friend" : 32 }, "username" :"retacn yue", "age" : 33, "sex" : "ma

le", "location" :"ZiBo", "emails" : [ "[e-mail]","[e-mail]", "[e-mail]","[e-mail]" ], "state" : null,

"languages" : [ "java","python", "c", "c++" ] }

> 

 

 

#find的第二个元素指定返回哪些键,$slice返回数组的子集合

>db.users.find({"username":"retacnyue"},{"languages":{"$slice":2}})

{ "_id" :ObjectId("586b40507dfc144b68de4071"), "relationships" : {"enemies" : 2, "friend" : 32 }, "username" :"retacn yue", "age" : 33, "sex" : "ma

le", "location" :"ZiBo", "emails" : [ "[e-mail]","[e-mail]", "[e-mail]","[e-mail]" ], "state" : null,

"languages" : [ "java","python" ] }

 

 

#指定偏移值和元素数量

>db.users.find({"username":"retacn yue"},{"languages":{"$slice":[1,4]}})

{ "_id" :ObjectId("586b40507dfc144b68de4071"), "relationships" : {"enemies" : 2, "friend" : 32 }, "username" :"retacn yue", "age" : 33, "sex" : "ma

le", "location" :"ZiBo", "emails" : [ "[e-mail]","[e-mail]", "[e-mail]","[e-mail]" ], "state" : null,

"languages" : ["python", "c", "c++" ] }

 

查询内嵌文档

#查询方法一,不推荐

>db.users.find({"relationships":{"enemies":2,"friend":32}})

{ "_id" :ObjectId("586b40507dfc144b68de4071"), "relationships" : {"enemies" : 2, "friend" : 32 }, "username" :"retacn yue", "age" : 33, "sex" : "ma

le", "location" :"ZiBo", "emails" : [ "[e-mail]","[e-mail]", "[e-mail]","[e-mail]" ], "state" : null,

"languages" : [ "java","python", "c", "c++" ] }

 

 

 

#方法二推荐

>db.users.find({"relationships.enemies":2,"relationships.friend":32})

{ "_id" :ObjectId("586b40507dfc144b68de4071"), "relationships" : {"enemies" : 2, "friend" : 32 }, "username" :"retacn yue", "age" : 33, "sex" : "ma

le", "location" :"ZiBo", "emails" : [ "[e-mail]","[e-mail]", "[e-mail]","[e-mail]" ], "state" : null,

"languages" : [ "java","python", "c", "c++" ] }

 

 

四 where查询条件

#添加记录

> db.foo.insert({"apple":1,"banana":6,"peach":3})

WriteResult({ "nInserted" : 1 })

>db.foo.insert({"apple":8,"spinach":4,"watermelon":4})

WriteResult({ "nInserted" : 1 })

 

#查询记录

> db.foo.find()

{ "_id" :ObjectId("58709e809193775a5f6b5f19"), "apple" : 1,"banana" : 6, "peach" : 3 }

{ "_id" :ObjectId("58709e999193775a5f6b5f1a"), "apple" : 8,"spinach" : 4, "watermelon" : 4 }

> 

 

 

#比较文档中的两个键值是否相等

>db.foo.find({"$where":function(){

... for (var current in this){

...   for(var other in this){

...      if(current !=other && this[current]==this[other]){

...            return true;

...       }

...    }

... }

... return false;

... }});

{ "_id" :ObjectId("58709e999193775a5f6b5f1a"), "apple" : 8,"spinach" : 4, "watermelon" : 4 }

 

由于查询速度比较慢,所以尽量不要使用

 

五游标

> for (i=0;i<100;i++){db.c.insert({x:i}); }

> var cursor=db.c.find();

>while(cursor.hasNext()){obj=cursor.next(); print(obj.x); }

#输出结果:

0

1

2

3

4

5

6

7

8

9

10

11

12

13

...

 

 

Limit 限制查询数量

> db.c.find().limit(3)

{ "_id" :ObjectId("5870a4e79193775a5f6b5f1b"), "x" : 0 }

{ "_id" :ObjectId("5870a4e79193775a5f6b5f1c"), "x" : 1 }

{ "_id" :ObjectId("5870a4e79193775a5f6b5f1d"), "x" : 2 }

 

 

Skip 跳过指定数量文档

> db.c.find().skip(3)

{ "_id" :ObjectId("5870a4e79193775a5f6b5f1e"), "x" : 3 }

{ "_id" :ObjectId("5870a4e79193775a5f6b5f1f"), "x" : 4 }

{ "_id" :ObjectId("5870a4e79193775a5f6b5f20"), "x" : 5 }

{ "_id" :ObjectId("5870a4e79193775a5f6b5f21"), "x" : 6 }

{ "_id" :ObjectId("5870a4e79193775a5f6b5f22"), "x" : 7 }

{ "_id" :ObjectId("5870a4e79193775a5f6b5f23"), "x" : 8 }

{ "_id" :ObjectId("5870a4e79193775a5f6b5f24"), "x" : 9 }

{ "_id" :ObjectId("5870a4e79193775a5f6b5f25"), "x" : 10 }

{ "_id" :ObjectId("5870a4e79193775a5f6b5f26"), "x" : 11 }

{ "_id" :ObjectId("5870a4e79193775a5f6b5f27"), "x" : 12 }

{ "_id" : ObjectId("5870a4e79193775a5f6b5f28"),"x" : 13 }

{ "_id" :ObjectId("5870a4e79193775a5f6b5f29"), "x" : 14 }

{ "_id" :ObjectId("5870a4e79193775a5f6b5f2a"), "x" : 15 }

{ "_id" :ObjectId("5870a4e79193775a5f6b5f2b"), "x" : 16 }

{ "_id" :ObjectId("5870a4e79193775a5f6b5f2c"), "x" : 17 }

{ "_id" :ObjectId("5870a4e79193775a5f6b5f2d"), "x" : 18 }

{ "_id" :ObjectId("5870a4e79193775a5f6b5f2e"), "x" : 19 }

{ "_id" :ObjectId("5870a4e79193775a5f6b5f2f"), "x" : 20 }

{ "_id" :ObjectId("5870a4e79193775a5f6b5f30"), "x" : 21 }

{ "_id" :ObjectId("5870a4e79193775a5f6b5f31"), "x" : 22 }

Type "it" for more

 

 

Sort 排序 ,1为升序,-1为降序

>db.users.find().sort({"age":1})

{ "_id" :ObjectId("58708aeb9193775a5f6b5f16"), "username" :"mike", "age" : 15, "sex" : "male","location" : "HuanTai", "relationships" : {"enemies"

 : 3,"friend" : 18 }, "emails" : [ "[e-mail]","[e-mail]" ], "state" : "F","languages" : [ "java", "c++" ] }

{ "_id" :ObjectId("58708e5e9193775a5f6b5f18"), "username" :"andy", "age" : 18, "sex" : "female","location" : "ZhangDian", "relationships" : {"enem

ies" : 2, "friend" : 15 },"emails" : [ "[e-mail]", "[e-mail]" ],"state" : "T", "languages" : [ "java","c", "c++" ] }

{ "_id" :ObjectId("58708a439193775a5f6b5f14"), "username" :"ann", "age" : 30, "sex" : "female","location" : "BoShan", "relationships" : {"enemies"

 : 1,"friend" : 20 }, "emails" : [ "[e-mail]","[e-mail]" ], "state" : "T","languages" : [ "java", "python", "c++"] }

{ "_id" :ObjectId("586b40507dfc144b68de4071"), "relationships" : {"enemies" : 2, "friend" : 32 }, "username" :"retacn yue", "age" : 33, "sex" : "ma

le", "location" :"ZiBo", "emails" : [ "[e-mail]","[e-mail]", "[e-mail]","[e-mail]" ], "state" : null,

"languages" : [ "java","python", "c", "c++" ] }

 

不要使用skip进行分页

 

高级查询选项

$maxscan:int   最多扫描的文档数量

$min 查询开始条件

$max 查询结束条件

$hint 指定服务器从哪个索引开始查询

$explain 取得查询的执行细节

$snapshot 确保查询结果是在查询执行那时的一致快照