一、MongoDB 条件操作符
1、条件操作符
> | $gt |
>= | $gte |
< | $lt |
<= | $lte |
演示如下:
> db.test.find()
{ "_id" : ObjectId("5d3818f585ad3637d228a6ad"), "title" : "PHP", "tag" : "php", "likes" : 100 }
{ "_id" : ObjectId("5d38190585ad3637d228a6ae"), "title" : "JAVA", "tag" : "java", "likes" : 150 }
{ "_id" : ObjectId("5d38193585ad3637d228a6af"), "title" : "MangoDB", "tag" : "mongodb", "likes" : 200 }
> db.test.find({likes : {$gt :150}}).pretty() //相当于 SELECT * FROM test WHERE likes > 150;
{
"_id" : ObjectId("5d38193585ad3637d228a6af"),
"title" : "MangoDB",
"tag" : "mongodb",
"likes" : 200
}
> db.test.find({likes : {$lt:200,$gt:100}}).pretty() //相当于 SELECT * FROM test WHERE likes < 200 AND likes > 100;
{
"_id" : ObjectId("5d38190585ad3637d228a6ae"),
"title" : "JAVA",
"tag" : "java",
"likes" : 150
}
2、条件操作符 $type
$type操作符是基于BSON类型来检索集合中匹配的数据类型,并返回结果。
示例:获取 “test” 集合中 likes 为 String 的数据
> db.test.find()
{ "_id" : ObjectId("5d3820ef00308636f4d6a21d"), "title" : "PHP", "tag" : "php", "likes" : "100" }
{ "_id" : ObjectId("5d38210400308636f4d6a21e"), "title" : "JAVA", "tag" : "java", "likes" : 150 }
{ "_id" : ObjectId("5d38211900308636f4d6a21f"), "title" : "MangoDB", "tag" : "mangodb", "likes" : 200 }
> db.test.find({"likes" : {$type : 'string'}})
{ "_id" : ObjectId("5d3820ef00308636f4d6a21d"), "title" : "PHP", "tag" : "php", "likes" : "100" }
> db.test.find({"likes" : {$type : 2}}) //string类型对应数字即为2
{ "_id" : ObjectId("5d3820ef00308636f4d6a21d"), "title" : "PHP", "tag" : "php", "likes" : "100" }
二、MongoDB常用方法
1、limit方法
limit()方法接受一个数字参数,该参数指定从MongoDB中读取的记录条数。
> db.test.find()
{ "_id" : ObjectId("5d3820ef00308636f4d6a21d"), "title" : "PHP", "tag" : "php", "likes" : "100" }
{ "_id" : ObjectId("5d38210400308636f4d6a21e"), "title" : "JAVA", "tag" : "java", "likes" : 150 }
{ "_id" : ObjectId("5d38211900308636f4d6a21f"), "title" : "MangoDB", "tag" : "mangodb", "likes" : 200 }
> db.test.find().limit(2)
{ "_id" : ObjectId("5d3820ef00308636f4d6a21d"), "title" : "PHP", "tag" : "php", "likes" : "100" }
{ "_id" : ObjectId("5d38210400308636f4d6a21e"), "title" : "JAVA", "tag" : "java", "likes" : 150 }
2、skip()方法
我们除了可以使用limit()方法来读取指定数量的数据外,还可以使用skip()方法来跳过指定数量的数据,skip方法同样接受一个数字参数作为跳过的记录条数。
> db.test.find().limit(1).skip(1)
{ "_id" : ObjectId("5d38210400308636f4d6a21e"), "title" : "JAVA", "tag" : "java", "likes" : 150 }
3、sort() 方法
在 MongoDB 中使用 sort() 方法对数据进行排序,sort() 方法可以通过参数指定排序的字段,并使用 1 和 -1 来指定排序的方式,其中 1 为升序排列,而 -1 是用于降序排列。
> db.test.find().sort({"likes" : -1}) //降序
{ "_id" : ObjectId("5d3820ef00308636f4d6a21d"), "title" : "PHP", "tag" : "php", "likes" : "100" } //此行likes值为字符串类型
{ "_id" : ObjectId("5d38211900308636f4d6a21f"), "title" : "MangoDB", "tag" : "mangodb", "likes" : 200 }
{ "_id" : ObjectId("5d38210400308636f4d6a21e"), "title" : "JAVA", "tag" : "java", "likes" : 150 }
> db.test.find().sort({"likes" : 1}) //升序
{ "_id" : ObjectId("5d38210400308636f4d6a21e"), "title" : "JAVA", "tag" : "java", "likes" : 150 }
{ "_id" : ObjectId("5d38211900308636f4d6a21f"), "title" : "MangoDB", "tag" : "mangodb", "likes" : 200 }
{ "_id" : ObjectId("5d3820ef00308636f4d6a21d"), "title" : "PHP", "tag" : "php", "likes" : "100" }
skip(), limilt(), sort()三个放在一起执行的时候,执行的顺序是先 sort(), 然后是 skip(),最后是显示的 limit()。
> db.test.find().sort({"likes" : 1})
{ "_id" : ObjectId("5d38210400308636f4d6a21e"), "title" : "JAVA", "tag" : "java", "likes" : 150 }
{ "_id" : ObjectId("5d38211900308636f4d6a21f"), "title" : "MangoDB", "tag" : "mangodb", "likes" : 200 }
{ "_id" : ObjectId("5d3820ef00308636f4d6a21d"), "title" : "PHP", "tag" : "php", "likes" : "100" }
> db.test.find().limit(1).skip(1).sort({"likes" : 1})
{ "_id" : ObjectId("5d38211900308636f4d6a21f"), "title" : "MangoDB", "tag" : "mangodb", "likes" : 200 }
三、MongoDB索引
1、创建和删除索引基本语法介绍
MongoDB使用 createIndex() 方法来创建索引。
注:3.0.0 版本前创建索引方法为 db.collection.ensureIndex(),现在也能用,相当于别名
Key 值为你要创建的索引字段,1 为指定按升序创建索引,-1为指定按降序创建索引。
MongoDB 在创建集合期间在_id字段上 创建唯一索引。该索引可防止客户端插入具有相同字段值的两个文档。您不能在该字段上删除此索引
> db.record.getIndexes() //查看集合索引
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "test.record"
}
]
//删除集合指定索引
//删除集合所有索引
2、单健索引
(1)单个字段上创建索引
> db.record.find()
{ "_id" : ObjectId("5d3837d400308636f4d6a221"), "score" : 1034, "location" : { "state" : "NY", "city" : "New York" } }
{ "_id" : ObjectId("5d3838d200308636f4d6a222"), "score" : 777, "location" : { "state" : "LA", "city" : "Los Angeles" } }
> db.record.createIndex({score : 1}) //创建升序索引
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
> db.record.getIndexes()
[
... ... //为了更加直观,这里省略其他索引
{
"v" : 2,
"key" : {
"score" : 1
},
"name" : "score_1",
"ns" : "test.record"
}
]
> db.record.find({score :{$gt :800}})
{ "_id" : ObjectId("5d3837d400308636f4d6a221"), "score" : 1034, "location" : { "state" : "NY", "city" : "New York" } }
(2)在嵌入式字段中创建索引
> db.record.createIndex({"location.state" : 1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
> db.record.getIndexes()
[
... ...
{
"v" : 2,
"key" : {
"location.state" : 1
},
"name" : "location.state_1",
"ns" : "test.record"
}
]
> db.record.find({"location.state" : "NY"})
{ "_id" : ObjectId("5d3837d400308636f4d6a221"), "score" : 1034, "location" : { "state" : "NY", "city" : "New York" } }
(3)在嵌入式文档中创建索引
> db.record.createIndex({location : 1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" :1,
"numIndexesAfter" : 2,
"ok" : 1
}
> db.record.getIndexes()
[
... ...
{
"v" : 2,
"key" : {
"location" : 1
},
"name" : "location_1",
"ns" : "test.record"
}
]
> db.record.find({location : {"state" : "NY","city" : "New York"}})
{ "_id" : ObjectId("5d3837d400308636f4d6a221"), "score" : 1034, "location" : { "state" : "NY", "city" : "New York" } }
此外,createIndex() 接收可选参数,下面演示一些常见的:
> db.record.createIndex({score : 1,location : -1},{background : true}) //后台创建索引
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
> db.record.createIndex({score : 1},{name : "myIndex"}) //指定索引名称
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"ok" : 1
}
> db.record.getIndexes()
[
... ...
{
"v" : 2,
"key" : {
"score" : 1
},
"name" : "myIndex",
"ns" : "test.record"
}
]
3、复合索引
MongoDB还支持多个字段的用户定义索引,即 复合索引。语法格式为:
> db.product.find().pretty()
{
"_id" : ObjectId("5d3920e100308636f4d6a223"),
"item" : "Banana",
"category" : [
"food",
"produce",
"grocery"
],
"location" : "4th",
"stock" : 4,
"type" : "case"
}
> db.product.createIndex({"item" : 1,"stock" : 1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
除了支持在所有索引字段上匹配的查询之外,复合索引还可以支持与索引字段的前缀匹配的查询。也就是说,索引支持对item字段以及字段item和stock字段的查询。
> db.product.find({item : "Banana"})
{ "_id" : ObjectId("5d3920e100308636f4d6a223"), "item" : "Banana", "category" : [ "food", "produce", "grocery" ], "location" : "4th", "stock" : 4, "type" : "case" }
> db.product.find({item : "Banana","stock" : {$lt : 5}})
{ "_id" : ObjectId("5d3920e100308636f4d6a223"), "item" : "Banana", "category" : [ "food", "produce", "grocery" ], "location" : "4th", "stock" : 4, "type" : "case" }
复合索引中列出的字段顺序具有重要意义。例如,如果有复合索引{“item” : 1,“stock” : 1},则索引首先按照item升序排序,然后在每个score值内按照stock升序排序。对于复合索引,排序顺序可以决定索引是否可以支持排序操作。
例如,对于下面这个索引,
支持下面这两种排序操作
即,文档中所有键的指定排序方向必须与索引键模式匹配,或者与索引键模式的反转匹配
4、多健索引要索引包含数组值的字段,MongoDB会为数组中的每个元素创建索引键。这些多键索引支持针对数组字段的高效查询。
创建语法格式如下:
(1)多健索引演示
> db.inventory.find()
{ "_id" : ObjectId("5d394bb800308636f4d6a228"), "type" : "food", "item" : "aaa", "ratings" : [ 5, 8, 9 ] }
{ "_id" : ObjectId("5d394be000308636f4d6a229"), "type" : "food", "item" : "bbb", "ratings" : [ 5, 9 ] }
{ "_id" : ObjectId("5d394c1900308636f4d6a22a"), "type" : "food", "item" : "ccc", "ratings" : [ 9, 5, 8 ] }
{ "_id" : ObjectId("5d394c2100308636f4d6a22b"), "type" : "food", "item" : "ddd", "ratings" : [ 9, 5 ] }
{ "_id" : ObjectId("5d394c2900308636f4d6a22c"), "type" : "food", "item" : "eee", "ratings" : [ 5, 9, 5 ] }
> db.inventory.createIndex({ratings : 1}) //基于ratings字段建立一个多健索引
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
> db.inventory.find({ratings:[5,9]}) //查询ratings字段为数组[5,9]的文档
{ "_id" : ObjectId("5d394be000308636f4d6a229"), "type" : "food", "item" : "bbb", "ratings" : [ 5, 9 ] }
> db.inventory.find({ratings:[5,9]}).explain() //使用explain()看看执行计划
{
... ...
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"ratings" : 1
},
"indexName" : "ratings_1",
"isMultiKey" : true, //表明此次查询用到了多健索引
"multiKeyPaths" : {
"ratings" : [
"ratings"
]
},
... ...
"ok" : 1
}
(2)基本索引数组
假定存在包含下列文档的survey集合:
{ _id: 1, item: "ABC", ratings: [ 2, 5, 9 ] }
在ratings上创建索引,db.survey.createIndex( { ratings: 1 } ), 这个多键索引则包括2,5,9三个索引键,每一个分别指向相同的文档
(3)基于嵌入式文档的索引数组
> db.inventory.insertMany([ //先创建一个inventory集合
... {
... _id : 1,
... item : "abc",
... stock :[
... {size : "s",color : "red",quantity : 25},
... {size : "s",color : "blue",quantity : 10},
... {size : "m",color : "blue",quantity : 50},
... ]
... },
... {
... _id : 2,
... item : "def",
... stock : [
... {size : "s",color : "red",quantity : 20},
... {size : "m",color : "blue",quantity : 5},
... {size : "m",color : "black",quantity : 10},
... {size : "l",color : "red",quantity : 2},
... ]
... }])
> db.inventory.find({"stock.size" : "l"})
{ "_id" : 2, "item" : "def", "stock" : [ { "size" : "s", "color" : "red", "quantity" : 20 }, { "size" : "m", "color" : "blue", "quantity" : 5 }, { "size" : "m", "color" : "black", "quantity" : 10 }, { "size" : "l", "color" : "red", "quantity" : 2 } ] }
> db.inventory.find({"stock.size" : "l"}).explain() //看看内嵌文档stock.size为 l 的执行计划
{
... ...
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN", //基于索引的扫描
"keyPattern" : {
"stock.size" : 1,
"stock.quantity" : 1
},
"indexName" : "stock.size_1_stock.quantity_1",
"isMultiKey" : true,
"multiKeyPaths" : {
"stock.size" : [
"stock"
],
"stock.quantity" : [
"stock"
]
},
... ...
"ok" : 1
}
> db.inventory.find({"stock.size" : "m","stock.quantity" : {$gt: 20}}).pretty() // //基于内嵌文档2个键查询
{
"_id" : 1,
"item" : "abc",
"stock" : [
{
"size" : "s",
"color" : "red",
"quantity" : 25
},
{
"size" : "s",
"color" : "blue",
"quantity" : 10
},
{
"size" : "m",
"color" : "blue",
"quantity" : 50
}
]
}
5、复合多健索引
如果文档的多个要索引字段是数组,则无法创建复合多键索引。
> db.array.find() //创建了array集合用于演示
{ "_id" : ObjectId("5d3aa45b886cd3eea1085015"), "a" : [ 1, 2 ], "b" : [ 5, 9 ] }
> db.array.createIndex({a : 1,b : 1})
{
"ok" : 0,
"errmsg" : "cannot index parallel arrays [b] [a]",
"code" : 171,
"codeName" : "CannotIndexParallelArrays" //我们不能创建{a : 1,b : 1}索引,因为a、b字段都是数组
}
对于下面这种情况,复合多健索引是可以的:
> db.array1.find() //array1只有字段a是数组
{ "_id" : ObjectId("5d3aa698886cd3eea1085016"), "a" : [ 1, 2 ], "b" : 1 }
> db.array2.find() //array2只有字段b是数组
{ "_id" : ObjectId("5d3aa6d1886cd3eea1085018"), "a" : 1, "b" : [ 5, 9 ] }
> db.array1.createIndex({a : 1,b : 1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
> db.array2.createIndex({a : 1,b : 1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
如果已存在复合多键索引,则无法插入会违反此限制的文档:
> db.array1.find()
{ "_id" : ObjectId("5d3aa698886cd3eea1085016"), "a" : [ 1, 2 ], "b" : 1 }
> db.array1.insert({a : [1,3],b : [2,4]}) //array1存在复合多健索引,不能插入两个数组
WriteResult({
"nInserted" : 0,
"writeError" : {
"code" : 171,
"errmsg" : "cannot index parallel arrays [b] [a]"
}
})
> db.array1.dropIndexes() //删除array的索引
{
"nIndexesWas" : 2,
"msg" : "non-_id indexes dropped for collection",
"ok" : 1
}
> db.array1.insert({a : [1,3],b : [2,4]})
WriteResult({ "nInserted" : 1 })
> db.array1.find() //插入成功
{ "_id" : ObjectId("5d3aa698886cd3eea1085016"), "a" : [ 1, 2 ], "b" : 1 }
{ "_id" : ObjectId("5d3aaa02b3a8911f2c1a70d3"), "a" : [ 1, 3 ], "b" : [ 2, 4 ] }