SQL术语和MongoDB术语对比
集合
创建集合
MongoDB中创建集合有两种方式
显式创建集合
db.createCollection(name, options)
其中name是要创建集合的名称,option是一个文档,用于指定集合的配置
参数 | 类型 | 描述 |
---|---|---|
name | String | 要创建集合的名称 |
options | Document | (可选)指定有关内存大小和索引的选项 |
options参数是可选的,因此只需要指定集合的名称,下面是options的参数列表
参数 | 类型 | 描述 |
---|---|---|
capped | Boolean | (可选)如果为true,则启用封闭的集合。上限集合是固定大小的集合,它在达到其最大大小时自动覆盖其最旧的条目。 如果指定true,则还需要指定size参数。 |
autoIndexId | Boolean | (可选)如果为true,则在_id字段上自动创建索引。默认值为false。 |
size | int | (可选)指定上限集合的最大大小(以字节为单位)。 如果capped为true,那么还需要指定此字段的值。 |
max | int | (可选)指定上限集合中允许的最大文档数。 |
示例:
不使用options的createCollection方法
>use test
switched to db test
>db.createCollection("mycollection")
{ "ok" : 1 }
>show collections
mycollection
使用options的createCollection方法
> db.createCollection("mycol", {capped : true, autoIndexId : true, size : 6142800, max : 10000 })
{ "ok" : 1 }
>
隐式创建集合
-MongoDB中,可以不用显式创建集合,当创建一些文档时,MongoDB会自动创建集合。
>db.newcollection.insert({"name":"test"})
>show collections
newcollection
mycollection
>
删除集合
drop()方法:
db.collection_name.drop()
示例:
> show collections
newCollection
users
> show collections
newCollection
users
> db.newCollection.drop()
true
> show collections
users
>
文档
因为MongoDB中的文档就是一条数据,并且文档格式与json数据格式相同,因此我理解文档等同于一个json字符串(不知道是否正确)
插入文档
insert()方法
db.collection_name.insert(document)
示例
> db.users.insert({
... _id: 100,
... title: 'MongoDB Overview',
... description: 'MongoDB is no sql database',
... by: 'yiibai tutorials',
... url: 'http://www.yiibai.com',
... tags: ['mongodb', 'database', 'NoSQL'],
... likes: 100,
... })
WriteResult({ "nInserted" : 1 })
> db.users.find()
{ "_id" : ObjectId("597a16b69c7b90a3d9020b86"), "username" : "test" }
{ "_id" : ObjectId("597c6181b0842614c2f7184f"), "name" : "qy" }
{ "_id" : 100, "title" : "MongoDB Overview", "description" : "MongoDB is no sql database", "by" : "yiibai tutorials", "url" : "http://www.yiibai.com", "tags" : [ "mongodb", "database", "NoSQL" ], "likes" : 100 }
>
如果一次插入多条语句,则在insert方法参数中传递一个文档数组。
db.collection_name.insert([{...},{...},{...}])
save()方法
db.collection_name.save()
注意:如果插入文档时不指定 _id ,则save和insert作用相同,都会自动分配 _id 值,但如果指定了 _id 值,并且集合中有相同 _id 值的文档,使用insert方法时会报错duplicate key error,而save方法会将新文档替换掉旧文档
> db.users.find()
{ "_id" : ObjectId("597a16b69c7b90a3d9020b86"), "username" : "test" }
{ "_id" : ObjectId("597c6181b0842614c2f7184f"), "name" : "qy" }
{ "_id" : 100, "title" : "MongoDB Overview", "description" : "MongoDB is no sql database", "by" : "yiibai tutorials", "url" : "http://www.yiibai.com", "tags" : [ "mongodb", "database", "NoSQL" ], "likes" : 100 }
> db.users.insert({ "_id" : 100, "name" : "remove a document"})
WriteResult({
"nInserted" : 0,
"writeError" : {
"code" : 11000,
"errmsg" : "E11000 duplicate key error collection: my.users index: _id_ dup key: { : 100.0 }"
}
})
> db.users.save({ "_id" : 100, "name" : "remove a document"})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> db.users.find()
{ "_id" : ObjectId("597a16b69c7b90a3d9020b86"), "username" : "test" }
{ "_id" : ObjectId("597c6181b0842614c2f7184f"), "name" : "qy" }
{ "_id" : 100, "name" : "remove a document" }
>
insertOne()和insertMany()方法
这两个方法的使用与前面两个方法没有什么不同,只不过会返回包含新插入文档的 _id 字段值的返回结果
> db.users.insertMany([{"name":"mongodb"},{_id:123,value:20}])
{
"acknowledged" : true,
"insertedIds" : [
ObjectId("597ddb50b0842614c2f71852"),
123
]
}
>
查询文档
find()方法
db.collection_name.find(document)
pretty()方法:以格式化的方式显示结果
> db.users.insert({name:"test",method:"find",format:"pretty"})
WriteResult({ "nInserted" : 1 })
> db.users.find({method:"find"})
{ "_id" : ObjectId("597dfd0fb0842614c2f71853"), "name" : "test", "method" : "find", "format" : "pretty" }
> db.users.find({method:"find"}).pretty()
{
"_id" : ObjectId("597dfd0fb0842614c2f71853"),
"name" : "test",
"method" : "find",
"format" : "pretty"
}
>
findOne()方法
与find方法不同的是findOne方法只返回一个文档。
mongodb与RDBMS的等效where子句
操作 | 语法 | 示例 | RDMBMS等效语句 |
---|---|---|---|
相等 | {key:value} | db.users.find({method:”find”}) | where method=’find’ |
小于 | {key:{$lt:value}} | db.users.find({age:{$lt:20}}) | where age<20 |
小于等于 | {key:{$lte:value}} | db.users.find({age:{$lte:20}}) | where age<=20 |
大于 | {key:{$gt:value}} | db.users.find({age:{$gt:20}}) | where age>20 |
大于等于 | {key:{$gte:value}} | db.users.find({age:{$gte:20}}) | where age>=20 |
不等于 | {key:{$ne:value}} | db.users.find({age:{$ne:20}}) | where age!=20 |
逻辑操作AND和OR
语法:
在fin()方法中,如果用’,’来分开传递多个键,则视为AND条件,或者也可以显式使用AND
> db.users.find()
{ "_id" : ObjectId("597a16b69c7b90a3d9020b86"), "username" : "test" }
{ "_id" : 100, "name" : "remove a document" }
{ "_id" : ObjectId("597ddb50b0842614c2f71852"), "name" : "mongodb" }
{ "_id" : 123, "value" : 20 }
{ "_id" : ObjectId("597dfd0fb0842614c2f71853"), "name" : "test", "method" : "find", "format" : "pretty" }
{ "_id" : ObjectId("59843be7b0842614c2f71854"), "value" : 20, "name" : "zhangsan" }
> db.users.find({value:20})
{ "_id" : 123, "value" : 20 }
{ "_id" : ObjectId("59843be7b0842614c2f71854"), "value" : 20, "name" : "zhangsan" }
> db.users.find({value:20,name:"zhangsan"})
{ "_id" : ObjectId("59843be7b0842614c2f71854"), "value" : 20, "name" : "zhangsan" }
> db.users.find({$and:[{value:20},{name:"zhangsan"}]})
{ "_id" : ObjectId("59843be7b0842614c2f71854"), "value" : 20, "name" : "zhangsan" }
>
OR语句用法类似
> db.users.find({$or:[{name:"mongodb"},{value:20}]})
{ "_id" : ObjectId("597ddb50b0842614c2f71852"), "name" : "mongodb" }
{ "_id" : 123, "value" : 20 }
{ "_id" : ObjectId("59843be7b0842614c2f71854"), "value" : 20, "name" : "zhangsan" }
>
AND和OR一起使用,找出value>15,name为zhangsan或zhaoming的数据
> db.users.find()
{ "_id" : ObjectId("597a16b69c7b90a3d9020b86"), "username" : "test" }
{ "_id" : 100, "name" : "remove a document" }
{ "_id" : ObjectId("597ddb50b0842614c2f71852"), "name" : "mongodb" }
{ "_id" : 123, "value" : 20 }
{ "_id" : ObjectId("59843be7b0842614c2f71854"), "value" : 20, "name" : "zhangsan" }
{ "_id" : ObjectId("59843eb5b0842614c2f71855"), "value" : 15, "name" : "lisi" }
{ "_id" : ObjectId("59843f67b0842614c2f71856"), "value" : 20, "name" : "wangwu" }
{ "_id" : ObjectId("59843f83b0842614c2f71857"), "value" : 20, "name" : "zhaoming" }
> db.users.find({value:{$gt:15},$or:[
... {name:"zhangsan"},
... {name:"zhaoming"}
... ]
... }
... )
{ "_id" : ObjectId("59843be7b0842614c2f71854"), "value" : 20, "name" : "zhangsan" }
{ "_id" : ObjectId("59843f83b0842614c2f71857"), "value" : 20, "name" : "zhaoming" }
>
查询嵌入/嵌套文档
先创建一个集合inventory,并向集合中插入一些数据
> db.inventory.insertMany( [
... { item: "journal", qty: 25, size: { h: 14, w: 21, uom: "cm" }, status: "A" },
... { item: "notebook", qty: 50, size: { h: 8.5, w: 11, uom: "in" }, status: "A" },
... { item: "paper", qty: 100, size: { h: 8.5, w: 11, uom: "in" }, status: "D" },
... { item: "planner", qty: 75, size: { h: 22.85, w: 30, uom: "cm" }, status: "D" },
... { item: "postcard", qty: 45, size: { h: 10, w: 15.25, uom: "cm" }, status: "A" }
... ]);
{
"acknowledged" : true,
"insertedIds" : [
ObjectId("5984411bb0842614c2f71858"),
ObjectId("5984411bb0842614c2f71859"),
ObjectId("5984411bb0842614c2f7185a"),
ObjectId("5984411bb0842614c2f7185b"),
ObjectId("5984411bb0842614c2f7185c")
]
}
>
- 匹配嵌入文档
匹配嵌入文档时,整个嵌入文档需要被精准匹配,包括字段顺序。
> db.inventory.find({size:{h:14,w:21,uom:"cm"}})
{ "_id" : ObjectId("5984411bb0842614c2f71858"), "item" : "journal", "qty" : 25, "size" : { "h" : 14, "w" : 21, "uom" : "cm" }, "status" : "A" }
> db.inventory.find({size:{uom:"cm",h:14,w:21}})
> db.inventory.find({size:{h:14}})
>
可以看出,后面两条语句都没有查询到文档。
- 查询嵌套字段
如果只想通过部分嵌套字段来查询文档,可用这种语法
db.collection_name.find({parent_key.son_key:value})
示例:
> db.inventory.find({"size.h":14})
{ "_id" : ObjectId("5984411bb0842614c2f71858"), "item" : "journal", "qty" : 25, "size" : { "h" : 14, "w" : 21, "uom" : "cm" }, "status" : "A" }
>
注意这里的size.h要打引号。
更新文档
更新文档有update和save两个方法,update更新现有文档的字段值,而save直接将传递的文档替换现有的文档。
inventory集合现在有以下数据
> db.inventory.find()
{ "_id" : ObjectId("5984411bb0842614c2f71858"), "item" : "journal", "qty" : 25, "size" : { "h" : 14, "w" : 21, "uom" : "cm" }, "status" : "A" }
{ "_id" : ObjectId("5984411bb0842614c2f71859"), "item" : "notebook", "qty" : 50, "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "status" : "A" }
{ "_id" : ObjectId("5984411bb0842614c2f7185a"), "item" : "paper", "qty" : 100, "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "status" : "D" }
{ "_id" : ObjectId("5984411bb0842614c2f7185b"), "item" : "planner", "qty" : 75, "size" : { "h" : 22.85, "w" : 30, "uom" : "cm" }, "status" : "D" }
{ "_id" : ObjectId("5984411bb0842614c2f7185c"), "item" : "postcard", "qty" : 45, "size" : { "h" : 10, "w" : 15.25, "uom" : "cm" }, "status" : "A" }
>
可以格式化输出,只看指定的字段值:
> db.inventory.find({}, {'_id':1, 'item':1,'qty':1})
{ "_id" : ObjectId("5984411bb0842614c2f71858"), "item" : "journal", "qty" : 25 }
{ "_id" : ObjectId("5984411bb0842614c2f71859"), "item" : "notebook", "qty" : 50 }
{ "_id" : ObjectId("5984411bb0842614c2f7185a"), "item" : "paper", "qty" : 100 }
{ "_id" : ObjectId("5984411bb0842614c2f7185b"), "item" : "planner", "qty" : 75 }
{ "_id" : ObjectId("5984411bb0842614c2f7185c"), "item" : "postcard", "qty" : 45 }
>
用update将item:’journal’的文档的qty字段设置为200:
> db.inventory.update({item:'journal'},{$set:{qty:200}})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> db.inventory.find({item:'journal'},{_id:1,item:1,qty:1})
{ "_id" : ObjectId("5984411bb0842614c2f71858"), "item" : "journal", "qty" : 200 }
>
默认情况下,MongoDB只会更新一个文档。要更新多个文档,需要将参数’multi‘设置为true。
> db.inventory.update({item:'journal'},{$set:{qty:200}},{multi:true})
删除文档
删除文档用remove()方法,该方法接受两个参数,一个是删除条件,一个是删除标志。
- criteria - (可选)符合条件的文档将被删除
- justOne - (可选)如果设置为true或1,则只删除一个文档。
示例:删除qty:200的文档
> db.inventory.find({}, {'_id':1, 'item':1,qty:1})
{ "_id" : ObjectId("5984411bb0842614c2f71858"), "item" : "journal", "qty" : 200 }
{ "_id" : ObjectId("5984411bb0842614c2f71859"), "item" : "notebook", "qty" : 50 }
{ "_id" : ObjectId("5984411bb0842614c2f7185a"), "item" : "paper", "qty" : 100 }
{ "_id" : ObjectId("5984411bb0842614c2f7185b"), "item" : "planner", "qty" : 75 }
{ "_id" : ObjectId("5984411bb0842614c2f7185c"), "item" : "postcard", "qty" : 45 }
{ "_id" : ObjectId("59845a02040baf2cc2affb54"), "qty" : 200 }
> db.inventory.remove({qty:200})
WriteResult({ "nRemoved" : 2 })
> db.inventory.find({}, {'_id':1, 'item':1,qty:1})
{ "_id" : ObjectId("5984411bb0842614c2f71859"), "item" : "notebook", "qty" : 50 }
{ "_id" : ObjectId("5984411bb0842614c2f7185a"), "item" : "paper", "qty" : 100 }
{ "_id" : ObjectId("5984411bb0842614c2f7185b"), "item" : "planner", "qty" : 75 }
{ "_id" : ObjectId("5984411bb0842614c2f7185c"), "item" : "postcard", "qty" : 45 }
>
可以看出该条命令删除了两条记录,如果想只删除一条记录,则将第二个参数值赋为1或true。
如果不指定删除条件,则会删除该集合中的所有文档,相当于SQL中的truncate命令。
限制记录数
limit()方法和skip()方法都可以对符合条件的记录进行限制,limit限制显示记录数,skip用于跳过记录数量。
> db.inventory.find({}, {'_id':1, 'item':1,qty:1})
{ "_id" : ObjectId("5984411bb0842614c2f71859"), "item" : "notebook", "qty" : 50 }
{ "_id" : ObjectId("5984411bb0842614c2f7185a"), "item" : "paper", "qty" : 100 }
{ "_id" : ObjectId("5984411bb0842614c2f7185b"), "item" : "planner", "qty" : 75 }
{ "_id" : ObjectId("5984411bb0842614c2f7185c"), "item" : "postcard", "qty" : 45 }
> db.inventory.find({}, {'_id':1, 'item':1,qty:1}).limit(2)
{ "_id" : ObjectId("5984411bb0842614c2f71859"), "item" : "notebook", "qty" : 50 }
{ "_id" : ObjectId("5984411bb0842614c2f7185a"), "item" : "paper", "qty" : 100 }
> db.inventory.find({}, {'_id':1, 'item':1,qty:1}).skip(2)
{ "_id" : ObjectId("5984411bb0842614c2f7185b"), "item" : "planner", "qty" : 75 }
{ "_id" : ObjectId("5984411bb0842614c2f7185c"), "item" : "postcard", "qty" : 45 }
> db.inventory.find({}, {'_id':1, 'item':1,qty:1}).skip(2).limit(1)
{ "_id" : ObjectId("5984411bb0842614c2f7185b"), "item" : "planner", "qty" : 75 }
>
对记录排序
sort()方法,key:1或-1,1代表升序,-1代表降序。
> db.inventory.find({},{item:1,qty:1}).sort({qty:1})
{ "_id" : ObjectId("5984411bb0842614c2f7185c"), "item" : "postcard", "qty" : 45 }
{ "_id" : ObjectId("5984411bb0842614c2f71859"), "item" : "notebook", "qty" : 50 }
{ "_id" : ObjectId("59846461040baf2cc2affb55"), "item" : "apple", "qty" : 50 }
{ "_id" : ObjectId("5984646b040baf2cc2affb56"), "item" : "orange", "qty" : 50 }
{ "_id" : ObjectId("59846471040baf2cc2affb57"), "item" : "grass", "qty" : 50 }
{ "_id" : ObjectId("5984411bb0842614c2f7185b"), "item" : "planner", "qty" : 75 }
{ "_id" : ObjectId("5984411bb0842614c2f7185a"), "item" : "paper", "qty" : 100 }
> db.inventory.find({},{item:1,qty:1}).sort({qty:1,item:1})
{ "_id" : ObjectId("5984411bb0842614c2f7185c"), "item" : "postcard", "qty" : 45 }
{ "_id" : ObjectId("59846461040baf2cc2affb55"), "item" : "apple", "qty" : 50 }
{ "_id" : ObjectId("59846471040baf2cc2affb57"), "item" : "grass", "qty" : 50 }
{ "_id" : ObjectId("5984411bb0842614c2f71859"), "item" : "notebook", "qty" : 50 }
{ "_id" : ObjectId("5984646b040baf2cc2affb56"), "item" : "orange", "qty" : 50 }
{ "_id" : ObjectId("5984411bb0842614c2f7185b"), "item" : "planner", "qty" : 75 }
{ "_id" : ObjectId("5984411bb0842614c2f7185a"), "item" : "paper", "qty" : 100 }
> db.inventory.find({},{item:1,qty:1}).sort({qty:-1})
{ "_id" : ObjectId("5984411bb0842614c2f7185a"), "item" : "paper", "qty" : 100 }
{ "_id" : ObjectId("5984411bb0842614c2f7185b"), "item" : "planner", "qty" : 75 }
{ "_id" : ObjectId("5984411bb0842614c2f71859"), "item" : "notebook", "qty" : 50 }
{ "_id" : ObjectId("59846461040baf2cc2affb55"), "item" : "apple", "qty" : 50 }
{ "_id" : ObjectId("5984646b040baf2cc2affb56"), "item" : "orange", "qty" : 50 }
{ "_id" : ObjectId("59846471040baf2cc2affb57"), "item" : "grass", "qty" : 50 }
{ "_id" : ObjectId("5984411bb0842614c2f7185c"), "item" : "postcard", "qty" : 45 }
>
创建索引
ensureIndex()方法
> db.inventory.ensureIndex({"item":1,"qty":-1})
可以在多个字段上创建索引,1代表按照升序创建索引,-1代表降序。
ensureIndex也有可选参数列表,还没有细看,以后再仔细学习。