目录
官方文档:https://docs.mongodb.com/manual/crud/
SQL语法和mongodb语法的映射:https://docs.mongodb.com/manual/reference/sql-comparison/
Insert
db.collection.insertOne() | 插入一个文档 |
db.collection.insertMany() | 插入多个文档 |
db.collection.insert() | 插入一个或多个文档 |
db.inventory.insertOne(
{ item: "canvas", qty: 100, tags: ["cotton"], size: { h: 28, w: 35.5, uom: "cm" } }
)
db.inventory.insertMany([
{ item: "journal", qty: 25, tags: ["blank", "red"], size: { h: 14, w: 21, uom: "cm" } },
{ item: "mat", qty: 85, tags: ["gray"], size: { h: 27.9, w: 35.5, uom: "cm" } },
{ item: "mousepad", qty: 25, tags: ["gel", "blue"], size: { h: 19, w: 22.85, uom: "cm" } }
])
db.products.insert( { item: "card", qty: 15 } )
db.products.insert(
[
{ _id: 11, item: "pencil", qty: 50, type: "no.2" },
{ item: "pen", qty: 20 },
{ item: "eraser", qty: 25 }
]
)
以下方法也可以将新文档添加到集合中:
db.collection.update()
与该 选项一起使用时。upsert: true
db.collection.updateOne()
与该选项一起使用时。upsert: true
db.collection.updateMany()
与该选项一起使用时。upsert: true
db.collection.findAndModify()
与该选项一起使用时。upsert: true
db.collection.findOneAndUpdate()
与该选项一起使用时 。upsert: true
db.collection.findOneAndReplace()
与该选项一起使用时 。upsert: true
db.collection.save()
。db.collection.bulkWrite()
。
Query
普通查询
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" }
]);
# 查找全部
db.inventory.find( {} )
# 通过相等条件查询
{ <field1>: <value1>, ... }
db.inventory.find( { status: "D" } )
# 使用操作符查询
{ <field1>: { <operator1>: <value1> }, ... }
db.inventory.find( { status: { $in: [ "A", "D" ] } } )
# 复合查询 AND条件
db.inventory.find( { status: "A", qty: { $lt: 30 } } )
# 复合查询 OR条件
db.inventory.find( { $or: [ { status: "A" }, { qty: { $lt: 30 } } ] } )
# 复合查询
example: where the status equals "A" and either qty is less than ($lt) 30 or item starts with the character p.
db.inventory.find( {
status: "A",
$or: [ { qty: { $lt: 30 } }, { item: /^p/ } ]
} )
内嵌查询
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" }
]);
# 内嵌查询
使用.
db.inventory.find( { "size.uom": "in" } )
db.inventory.find( { "size.h": { $lt: 15 } } )
db.inventory.find( { "size.h": { $lt: 15 }, "size.uom": "in", status: "D" } )
数组查询、数组内嵌文档查询
# 数组查询
db.inventory.insertMany([
{ item: "journal", qty: 25, tags: ["blank", "red"], dim_cm: [ 14, 21 ] },
{ item: "notebook", qty: 50, tags: ["red", "blank"], dim_cm: [ 14, 21 ] },
{ item: "paper", qty: 100, tags: ["red", "blank", "plain"], dim_cm: [ 14, 21 ] },
{ item: "planner", qty: 75, tags: ["blank", "red"], dim_cm: [ 22.85, 30 ] },
{ item: "postcard", qty: 45, tags: ["blue"], dim_cm: [ 10, 15.25 ] }
]);
# 同时包含。tags数组同时包含"red"和"blank"
db.inventory.find( { tags: { $all: ["red", "blank"] } } )
# 包含一个。tags数组至少包含一个"red"
db.inventory.find( { tags: "red" } )
# 加入操作符。查询数组dim_cm包含至少一个值大于25的元素的所有文档
db.inventory.find( { dim_cm: { $gt: 25 } } )
# 复合查询
# 一个元素可以满足大于15的条件,而另一个元素可以满足小于20的条件,或者一个元素可以满足以下两个条件
db.inventory.find( { dim_cm: { $gt: 15, $lt: 20 } } )
# 查询dim_cm数组包含至少一个同时大于($gt) 22和小于($lt)的元素30
db.inventory.find( { dim_cm: { $elemMatch: { $gt: 22, $lt: 30 } } } )
# 查询取值
# 暗战索引下标取值。查询数组中dim_cm大于25的第二个元素
db.inventory.find( { "dim_cm.1": { $gt: 25 } } )
# 按照数组长度取值。选择数组tags具有3个元素的文档。
db.inventory.find( { "tags": { $size: 3 } } )
# 查询数组中的内嵌文档。
db.inventory.insertMany( [
{ item: "journal", instock: [ { warehouse: "A", qty: 5 }, { warehouse: "C", qty: 15 } ] },
{ item: "notebook", instock: [ { warehouse: "C", qty: 5 } ] },
{ item: "paper", instock: [ { warehouse: "A", qty: 60 }, { warehouse: "B", qty: 15 } ] },
{ item: "planner", instock: [ { warehouse: "A", qty: 40 }, { warehouse: "B", qty: 5 } ] },
{ item: "postcard", instock: [ { warehouse: "B", qty: 15 }, { warehouse: "C", qty: 35 } ] }
]);
# 查询instock数组包含至少一个嵌入式文档,qty值小于或等于20
db.inventory.find( { 'instock.qty': { $lte: 20 } } )
# 查询instock数组包含至少一个嵌入式文档,qty值为5且warehouse为 "A"。
db.inventory.find( { "instock": { $elemMatch: { qty: 5, warehouse: "A" } } } )
db.inventory.find( { "instock.qty": 5, "instock.warehouse": "A" } )
# 查询instock数组包含至少一个嵌入式文档,qty值大于10小于等于20
db.inventory.find( { "instock": { $elemMatch: { qty: { $gt: 10, $lte: 20 } } } } )
db.inventory.find( { "instock.qty": { $gt: 10, $lte: 20 } } )
限定查询返回的字段
db.inventory.insertMany( [
{ item: "journal", status: "A", size: { h: 14, w: 21, uom: "cm" }, instock: [ { warehouse: "A", qty: 5 } ] },
{ item: "notebook", status: "A", size: { h: 8.5, w: 11, uom: "in" }, instock: [ { warehouse: "C", qty: 5 } ] },
{ item: "paper", status: "D", size: { h: 8.5, w: 11, uom: "in" }, instock: [ { warehouse: "A", qty: 60 } ] },
{ item: "planner", status: "D", size: { h: 22.85, w: 30, uom: "cm" }, instock: [ { warehouse: "A", qty: 40 } ] },
{ item: "postcard", status: "A", size: { h: 10, w: 15.25, uom: "cm" }, instock: [ { warehouse: "B", qty: 15 }, { warehouse: "C", qty: 35 } ] }
]);
# 所有字段
db.inventory.find( { status: "A" } )
SELECT * from inventory WHERE status = "A"
# 指定字段
db.inventory.find( { status: "A" }, { item: 1, status: 1 } )
SELECT _id, item, status from inventory WHERE status = "A"
# 指定字段、排除字段
db.inventory.find( { status: "A" }, { item: 1, status: 1, _id: 0 } )
SELECT item, status from inventory WHERE status = "A"
# 排除字段
db.inventory.find( { status: "A" }, { status: 0, instock: 0 } )
# 内嵌字段
# 指定字段。结果:返回_id, status, item, size文档中的uom字段
db.inventory.find(
{ status: "A" },
{ item: 1, status: 1, "size.uom": 1 }
)
# 排除字段。结果:除了内嵌size文档中的uom字段,全部返回
db.inventory.find(
{ status: "A" },
{ "size.uom": 0 }
)
# 数组中的内嵌文档
# 指定字段。结果:返回_id, status, item, instock数组中的内嵌文档的qty字段
db.inventory.find( { status: "A" }, { item: 1, status: 1, "instock.qty": 1 } )
# 使用操作符。结果:返回_id, status, item, instock数组中的最后一个元素
db.inventory.find( { status: "A" }, { item: 1, status: 1, instock: { $slice: -1 } } )
查询Null字段
db.inventory.insertMany([
{ _id: 1, item: null },
{ _id: 2 }
])
# 等值查询。查询item为null或item字段不存在的文档
db.inventory.find( { item: null } )
# 类型检查。查询item为null且BSON类型为10的文档。https://docs.mongodb.com/manual/reference/bson-types/
db.inventor.find( { item : { $type: 10 } } )
# 存在检查。查询item字段不存在的文档
db.inventory.find( { item : { $exists: false } } )
游标迭代器
var myCursor = db.users.find( { type: 2 } );
myCursor
---
var myCursor = db.users.find( { type: 2 } );
while (myCursor.hasNext()) {
print(tojson(myCursor.next()));
}
---
var myCursor = db.users.find( { type: 2 } );
while (myCursor.hasNext()) {
printjson(myCursor.next());
}
---
var myCursor = db.users.find( { type: 2 } );
myCursor.forEach(printjson);
---
var myCursor = db.inventory.find( { type: 2 } );
var documentArray = myCursor.toArray();
var myDocument = documentArray[3];
---
var myCursor = db.users.find( { type: 2 } );
var myDocument = myCursor[1];
myCursor.toArray() [1];
---
Update
db.collection.updateOne(<filter>, <update>, <options>)
db.collection.updateMany(<filter>, <update>, <options>)
db.collection.replaceOne(<filter>, <update>, <options>)
db.collection.updateOne() | 更新至多一个符合条件的文档 |
db.collection.updateMany() | 更新所有符合条件的文档 |
db.collection.replaceOne() | 替换至多一个符合条件的文档 |
db.collection.update() | 更新或替换一个或所有符合条件的文档,默认更新一个。 |
以下方法也可以更新集合中的文档
db.collection.findOneAndReplace()
.db.collection.findOneAndUpdate()
.db.collection.findAndModify()
.db.collection.save()
.db.collection.bulkWrite()
.
db.inventory.insertMany( [
{ item: "canvas", qty: 100, size: { h: 28, w: 35.5, uom: "cm" }, status: "A" },
{ item: "journal", qty: 25, size: { h: 14, w: 21, uom: "cm" }, status: "A" },
{ item: "mat", qty: 85, size: { h: 27.9, w: 35.5, uom: "cm" }, status: "A" },
{ item: "mousepad", qty: 25, size: { h: 19, w: 22.85, uom: "cm" }, status: "P" },
{ item: "notebook", qty: 50, size: { h: 8.5, w: 11, uom: "in" }, status: "P" },
{ 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" },
{ item: "sketchbook", qty: 80, size: { h: 14, w: 21, uom: "cm" }, status: "A" },
{ item: "sketch pad", qty: 95, size: { h: 22.85, w: 30.5, uom: "cm" }, status: "A" }
] );
# 更新item为"paper"的数据,size字段uom字段为"cm","status"字段为"P",lastModified字段为当前日期
db.inventory.updateOne(
{ item: "paper" },
{
$set: { "size.uom": "cm", status: "P" },
$currentDate: { lastModified: true }
}
)
# updateMany
db.inventory.updateMany(
{ "qty": { $lt: 50 } },
{
$set: { "size.uom": "in", status: "P" },
$currentDate: { lastModified: true }
}
)
# 替换操作无法使用更新操作符,_id字段无法修改
# 替换一个
db.inventory.replaceOne(
{ item: "paper" },
{ item: "paper", instock: [ { warehouse: "A", qty: 60 }, { warehouse: "B", qty: 40 } ] }
)
# upsert : true
# 第三个参数添加upsert: true参数,如果没有匹配的文档,就插入一个文档
Delete
db.collection.deleteOne() | Delete at most a single document that match a specified filter even though multiple documents may match the specified filter. New in version 3.2. |
db.collection.deleteMany() | Delete all documents that match a specified filter. New in version 3.2. |
db.collection.remove() | Delete a single document or all documents that match a specified filter. |
以下方法也可以从集合中删除文档:
-
db.collection.findOneAndDelete()
。findOneAndDelete() 提供了一个排序选项。该选项允许删除按指定顺序排序的第一个文档。
-
db.collection.findAndModify()
。db.collection.findAndModify()
提供排序选项。该选项允许删除按指定顺序排序的第一个文档。
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: "P" },
{ 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" },
] );
# 删除所有数据
db.inventory.deleteMany({})
# 删除所有符合条件的数据
db.inventory.deleteMany({ status : "A" })
# 删除一条符合条件的数据
db.inventory.deleteOne( { status: "D" } )