文章目录
MongoDB数据库
基本语法
1. 创建和查询数据库
use DATABASE_name
:使用数据库(如果该数据库不存在,则创建新的数据库)
// 例子
> use test
switched to test
(如果没有写入数据,该数据库不算真正意义上的创建)
// 查看当前数据库位置
>db
test
2. 查看数据库
> show dbs
admin 0.000GB
config 0.000GB
local 0.000GB
test1 0.000GB
//查看当前数据库的所有集合
> show tables
3. 插入数据
// db.DATABASE_name.insert(JSON_DATA)
> db.test.insert({"name":"xiaoming", "age":20})
WriteResult({ "nInserted" : 1 })
4. 删除数据库
// db.dropDatabase() 删除当前数据库
> db.dropDatabase()
{ "dropped" : "test", "ok" : 1 }
5. 删除集合(collection)
// 先创建一个名为test_collection的集合
// db.createCollection("COLLECTION_NAME")
> db.createCollection("test2_collection")
{ "ok" : 1 }
// 删除集合
// db.COLLECTION_NAME.drop()
> db.test2_collection.drop()
true
6. 创建集合
db.createCollection(name,* options)
- name:集合名称
- option:可选参数
字段 | 类型 | 描述 |
---|---|---|
capped | 布尔 | (可选)如果为 true,则创建固定集合。固定集合是指有着固定大小的集合,当达到最大值时,它会自动覆盖最早的文档。 当该值为 true 时,必须指定 size 参数。 |
autoIndexId | 布尔 | (可选)如为 true,自动在 _id 字段创建索引。默认为 false。 |
size | 数值 | (可选)为固定集合指定一个最大值,以千字节计(KB)。 如果 capped 为 true,也需要指定该字段。 |
max | 数值 | (可选)指定固定集合中包含文档的最大数量。 |
插入文档时, MongoDB会首先检查固定集合的size字段,再检查max字段
// 创建名为test_collection的集合
> db.createCollection("test_collection")
{ "ok" : 1 }
// 创建固定集合 mycol,整个集合空间大小 6142800 KB, 文档最大个数为 10000 个。
> > db.createCollection("mycol", { capped : true, autoIndexId : true, size :
6142800, max : 10000 } )
{ "ok" : 1 }
通过插入文档,直接创建集合
> db.test2_collection.insert({name:"小明", age:20})})
WriteResult({ "nInserted" : 1 })
> show tables
mycol
test2_collection
test_collection
7. 插入文档
基本语法:db.COLLECTION_NAME.insert(document)
示例:
db.test_collection.insert({
name:"xiaoming",
age:20,
friends:["lihua", "xiaohong"]
})
// ----------------------------------------
// 插入多条文档,用数组的形式插入,文档与文档间用逗号隔开
> db.test_collection.insert([{
... name:"xiaoming",
... age:20,
... friends:["lihua", "xiaohong"]
... }, {
... name: "xiaoliu",
... age:20,
... birthday:"2019-2-02"
... }])
BulkWriteResult({
"writeErrors" : [ ],
"writeConcernErrors" : [ ],
"nInserted" : 2,
"nUpserted" : 0,
"nMatched" : 0,
"nModified" : 0,
"nRemoved" : 0,
"upserted" : [ ]
})
// ---------------------------------------
// 查看集合中的文档
// db.COLLECTION_NAME.find()
>db.test_collection.find()
{ "_id" : ObjectId("5d84c7fdaefd17d6837ba4f8"), "name" : "xiaoming", "age" : 20, "friends" : [ "lihua", "xiaohong" ] }
在插入文档时,如果不指定_id参数,MongoDB会自动为文档分配一个唯一的objectId
保存:如果文档的_id已经存在则修改,不存在则添加
db.COLLECTION_NAME.save(document)
// 例如:
> db.test_collection.find()
{ "_id" : ObjectId("5d84c7fdaefd17d6837ba4f8"), "name" : "xiaoming", "age" : 20, "friends" : [ "lihua", "xiaohong" ] }
{ "_id" : ObjectId("5d84c872aefd17d6837ba4f9"), "name" : "xiaoming", "age" : 20, "friends" : [ "lihua", "xiaohong" ] }
{ "_id" : ObjectId("5d84c9b6aefd17d6837ba4fa"), "name" : "xiaoming", "age" : 20, "friends" : [ "lihua", "xiaohong" ] }
{ "_id" : ObjectId("5d84c9b6aefd17d6837ba4fb"), "name" : "xiaoliu", "age" : 20, "birthday" : "2019-2-02" }
{ "_id" : 10010, "name" : "xiaoming", "age" : 20, "friends" : [ "lihua", "xiaohong" ] }
{ "_id" : ObjectId("5d84ce87aefd17d6837ba4fc"), "name" : "xiaoliu", "age" : 20, "birthday" : "2019-2-02" }
// ----------------
// 程序会报错,因为_id:10010的文档已经存在,无法插入
> db.test_collection.insert({_id:10010,"name":"小明", "age":22 })
WriteResult({
"nInserted" : 0,
"writeError" : {
"code" : 11000,
"errmsg" : "E11000 duplicate key error collection: test.test_collection index: _id_ dup key: { _id: 10010.0 }"
}
})
// ---------------------
// 因为_id:10010已经存在,所以会显示nModified
> db.test_collection.save({_id:10010,"name":"小明", "age":22 })))
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
// ------------------------
// 如果_id不存在则显示nUpserted
> db.test_collection.save({_id:100101,"name":"小明", "age":22 })
WriteResult({ "nMatched" : 0, "nUpserted" : 1, "nModified" : 0, "_id" : 100101 })
8.更新
基本语法
db.COLLECTION_NAME.update(<query>, <update>*, {multi: true\false}
- query:查询条件
- update:更新操作符
- mulit:可选参数,默认为false(只更新一条)。true则更新多条。
> db.test_collection.find()
{ "_id" : ObjectId("5d84c7fdaefd17d6837ba4f8"), "name" : "xiaoming", "age" : 20, "friends" : [ "lihua", "xiaohong" ] }
{ "_id" : ObjectId("5d84c872aefd17d6837ba4f9"), "name" : "xiaoming", "age" : 20, "friends" : [ "lihua", "xiaohong" ] }
{ "_id" : ObjectId("5d84c9b6aefd17d6837ba4fa"), "name" : "xiaoming", "age" : 20, "friends" : [ "lihua", "xiaohong" ] }
{ "_id" : ObjectId("5d84c9b6aefd17d6837ba4fb"), "name" : "xiaoliu", "age" : 20, "birthday" : "2019-2-02" }
{ "_id" : 10010, "name" : "小明", "age" : 22 }
{ "_id" : ObjectId("5d84ce87aefd17d6837ba4fc"), "name" : "xiaoliu", "age" : 20, "birthday" : "2019-2-02" }
{ "_id" : 100101, "name" : "小明", "age" : 22 }
> db.test_collection.update({"name":"xiaoming"}, {"name":"小明"})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
// 注意update({"name":"xiaoming"}, {"name":"小明"}) 会把整个文档***重写**
> db.test_collection.find() })
{ "_id" : ObjectId("5d84c7fdaefd17d6837ba4f8"), "name" : "小明" }
{ "_id" : ObjectId("5d84c872aefd17d6837ba4f9"), "name" : "xiaoming", "age" : 20, "friends" : [ "lihua", "xiaohong" ] }
{ "_id" : ObjectId("5d84c9b6aefd17d6837ba4fa"), "name" : "xiaoming", "age" : 20, "friends" : [ "lihua", "xiaohong" ] }
{ "_id" : ObjectId("5d84c9b6aefd17d6837ba4fb"), "name" : "xiaoliu", "age" : 20, "birthday" : "2019-2-02" }
{ "_id" : 10010, "name" : "小明", "age" : 22 }
{ "_id" : ObjectId("5d84ce87aefd17d6837ba4fc"), "name" : "xiaoliu", "age" : 20, "birthday" : "2019-2-02" }
{ "_id" : 100101, "name" : "小明", "age" : 22 }
>
// 如果只想更新文档中的某一字段,而不是重写文档要用{$set:{}}
> db.test_collection.update({"name":"xiaoming"}, {$set:{"name":"小明"}})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> db.test_collection.find()
{ "_id" : ObjectId("5d84c7fdaefd17d6837ba4f8"), "name" : "小明" }
{ "_id" : ObjectId("5d84c872aefd17d6837ba4f9"), "name" : "小明", "age" : 20, "friends" : [ "lihua", "xiaohong" ] }
...
> db.test_collection.find()
{ "_id" : ObjectId("5d84c7fdaefd17d6837ba4f8"), "name" : "小明" }
{ "_id" : ObjectId("5d84c872aefd17d6837ba4f9"), "name" : "小明", "age" : 20, "friends" : [ "lihua", "xiaohong" ] }
{ "_id" : ObjectId("5d84c9b6aefd17d6837ba4fa"), "name" : "小明", "age" : 20, "friends" : [ "lihua", "xiaohong" ] }
{ "_id" : ObjectId("5d84c9b6aefd17d6837ba4fb"), "name" : "xiaoliu", "age" : 20, "birthday" : "2019-2-02" }
{ "_id" : 10010, "name" : "小明", "age" : 22 }
{ "_id" : ObjectId("5d84ce87aefd17d6837ba4fc"), "name" : "xiaoliu", "age" : 20, "birthday" : "2019-2-02" }
{ "_id" : 100101, "name" : "小明", "age" : 22 }
> db.test_collection.update({"name":"小明"}, {$set:{"name":"小红"}}, {"multi":true})))))
WriteResult({ "nMatched" : 5, "nUpserted" : 0, "nModified" : 5 })
> db.test_collection.find()
{ "_id" : ObjectId("5d84c7fdaefd17d6837ba4f8"), "name" : "小红" }
{ "_id" : ObjectId("5d84c872aefd17d6837ba4f9"), "name" : "小红", "age" : 20, "friends" : [ "lihua", "xiaohong" ] }
{ "_id" : ObjectId("5d84c9b6aefd17d6837ba4fa"), "name" : "小红", "age" : 20, "friends" : [ "lihua", "xiaohong" ] }
{ "_id" : ObjectId("5d84c9b6aefd17d6837ba4fb"), "name" : "xiaoliu", "age" : 20, "birthday" : "2019-2-02" }
{ "_id" : 10010, "name" : "小红", "age" : 22 }
{ "_id" : ObjectId("5d84ce87aefd17d6837ba4fc"), "name" : "xiaoliu", "age" : 20, "birthday" : "2019-2-02" }
{ "_id" : 100101, "name" : "小红", "age" : 22 }
>
9.删除
db.remove(<query>, {justOne: true\false})
- query:删除字段所满足的条件
- justOne:只删除一条,默认false(默认删除多条)
> db.test_collection.remove({"name":"小红"},{"justOne":true})
WriteResult({ "nRemoved" : 1 })
> db.test_collection.remove({"name":"小红"}) })
WriteResult({ "nRemoved" : 3 })
>
高级查询
- find()
db.COLLECTION_NAME.find(*<query>)
- query:加入查询条件(不写则查询全部)
> db.stu.find()
{ "_id" : ObjectId("5d856a6eaefd17d6837ba4fd"), "name" : "小明", "hometown" : "蒙古", "age" : 20, "gender" : true }
{ "_id" : ObjectId("5d856ab1aefd17d6837ba4fe"), "name" : "黄蓉", "hometown" : "桃花岛", "age" : 18, "gender" : false }
{ "_id" : ObjectId("5d856ac9aefd17d6837ba4ff"), "name" : "华筝", "hometown" : "蒙古", "age" : 18, "gender" : false }
{ "_id" : ObjectId("5d856addaefd17d6837ba500"), "name" : "黄药师", "hometown" : "桃花岛", "age" : 18, "gender" : true }
{ "_id" : ObjectId("5d856aebaefd17d6837ba501"), "name" : "段誉", "hometown" : "大理", "age" : 18, "gender" : true }
{ "_id" : ObjectId("5d856afaaefd17d6837ba502"), "name" : "段王爷", "hometown" : "大理", "age" : 18, "gender" : true }
{ "_id" : ObjectId("5d856b08aefd17d6837ba503"), "name" : "洪七公", "hometown" : "华山", "age" : 18, "gender" : true }
> db.stu.find({age:20})
{ "_id" : ObjectId("5d856a6eaefd17d6837ba4fd"), "name" : "小明", "hometown" : "蒙古", "age" : 20, "gender" : true }
>
- findOne() 查询一条数据
1.比较运算符
- 等于:默认是等于判断,没有运算符
- 小于:$lt (less than)
- 小于等于: $lte (less than equal)
- 大于:$gt (greater than)
- 大于等于:$gte (greater than equal)
- 不等于: $ne (not equal)
基本用法:db.COLLECTION_NAME.find( {"field":{$lt:xx} })
> db.stu.find({age:{$gte:20}})
{ "_id" : ObjectId("5d856a6eaefd17d6837ba4fd"), "name" : "小明", "hometown" : "蒙古", "age" : 20, "gender" : true }
{ "_id" : ObjectId("5d856addaefd17d6837ba500"), "name" : "黄药师", "hometown" : "桃花岛", "age" : 35, "gender" : true }
{ "_id" : ObjectId("5d856afaaefd17d6837ba502"), "name" : "段王爷", "hometown" : "大理", "age" : 30, "gender" : true }
{ "_id" : ObjectId("5d856b08aefd17d6837ba503"), "name" : "洪七公", "hometown" : "华山", "age" : 40, "gender" : true }
>
2.范围运算符
- $in
- KaTeX parse error: Expected '}', got 'EOF' at end of input: …nd( {"field": {in:[xx, xxx, xxx]} } )`
> db.stu.find({age:{$in:[18, 20]}})
{ "_id" : ObjectId("5d856a6eaefd17d6837ba4fd"), "name" : "小明", "hometown" : "蒙古", "age" : 20, "gender" : true }
{ "_id" : ObjectId("5d856ab1aefd17d6837ba4fe"), "name" : "黄蓉", "hometown" : "桃花岛", "age" : 18, "gender" : false }
{ "_id" : ObjectId("5d856ac9aefd17d6837ba4ff"), "name" : "华筝", "hometown" : "蒙古", "age" : 18, "gender" : false }
{ "_id" : ObjectId("5d856aebaefd17d6837ba501"), "name" : "段誉", "hometown" : "大理", "age" : 18, "gender" : true }
>
3.and or
- and:逗号隔开即为and
> db.stu.find({age:{$gt:20}, hometown:"大理"})
{ "_id" : ObjectId("5d856afaaefd17d6837ba502"), "name" : "段王爷", "hometown" : "大理", "age" : 30, "gender" : true }
>
- or:{$or: [] }
> db.stu.find({$or: [{age:18}, {hometown:"大理"}] })
{ "_id" : ObjectId("5d856ab1aefd17d6837ba4fe"), "name" : "黄蓉", "hometown" : "桃花岛", "age" : 18, "gender" : false }
{ "_id" : ObjectId("5d856ac9aefd17d6837ba4ff"), "name" : "华筝", "hometown" : "蒙古", "age" : 18, "gender" : false }
{ "_id" : ObjectId("5d856aebaefd17d6837ba501"), "name" : "段誉", "hometown" : "大理", "age" : 18, "gender" : true }
{ "_id" : ObjectId("5d856afaaefd17d6837ba502"), "name" : "段王爷", "hometown" : "大理", "age" : 30, "gender" : true }
>
4.正则表达式
> db.stu.find({hometown:/^大/})
{ "_id" : ObjectId("5d856aebaefd17d6837ba501"), "name" : "段誉", "hometown" : "大理", "age" : 18, "gender" : true }
{ "_id" : ObjectId("5d856afaaefd17d6837ba502"), "name" : "段王爷", "hometown" : "大理", "age" : 30, "gender" : true }
>
5.limit 和 skip
- limit() : 限制多个条文档
- skip() : 跳过多少信息
> db.stu.find().limit(2)
{ "_id" : ObjectId("5d856a6eaefd17d6837ba4fd"), "name" : "小明", "hometown" : "蒙古", "age" : 20, "gender" : true }
{ "_id" : ObjectId("5d856ab1aefd17d6837ba4fe"), "name" : "黄蓉", "hometown" : "桃花岛", "age" : 18, "gender" : false }
> db.stu.find().skip(2)
{ "_id" : ObjectId("5d856ac9aefd17d6837ba4ff"), "name" : "华筝", "hometown" : "蒙古", "age" : 18, "gender" : false }
{ "_id" : ObjectId("5d856addaefd17d6837ba500"), "name" : "黄药师", "hometown" : "桃花岛", "age" : 35, "gender" : true }
{ "_id" : ObjectId("5d856aebaefd17d6837ba501"), "name" : "段誉", "hometown" : "大理", "age" : 18, "gender" : true }
{ "_id" : ObjectId("5d856afaaefd17d6837ba502"), "name" : "段王爷", "hometown" : "大理", "age" : 30, "gender" : true }
{ "_id" : ObjectId("5d856b08aefd17d6837ba503"), "name" : "洪七公", "hometown" : "华山", "age" : 40, "gender" : true }
>
- limit().skip() 配合使用, 可以实现换页
db.COLLECTION_NAME.find().limt().skip()
db.COLLECTION_NAME.find().skip().limt()
效率会更高
> db.stu.find().limit(2).skip(2)
{ "_id" : ObjectId("5d856ac9aefd17d6837ba4ff"), "name" : "华筝", "hometown" : "蒙古", "age" : 18, "gender" : false }
{ "_id" : ObjectId("5d856addaefd17d6837ba500"), "name" : "黄药师", "hometown" : "桃花岛", "age" : 35, "gender" : true }
> db.stu.find()
{ "_id" : ObjectId("5d856a6eaefd17d6837ba4fd"), "name" : "小明", "hometown" : "蒙古", "age" : 20, "gender" : true }
{ "_id" : ObjectId("5d856ab1aefd17d6837ba4fe"), "name" : "黄蓉", "hometown" : "桃花岛", "age" : 18, "gender" : false }
{ "_id" : ObjectId("5d856ac9aefd17d6837ba4ff"), "name" : "华筝", "hometown" : "蒙古", "age" : 18, "gender" : false }
{ "_id" : ObjectId("5d856addaefd17d6837ba500"), "name" : "黄药师", "hometown" : "桃花岛", "age" : 35, "gender" : true }
{ "_id" : ObjectId("5d856aebaefd17d6837ba501"), "name" : "段誉", "hometown" : "大理", "age" : 18, "gender" : true }
{ "_id" : ObjectId("5d856afaaefd17d6837ba502"), "name" : "段王爷", "hometown" : "大理", "age" : 30, "gender" : true }
{ "_id" : ObjectId("5d856b08aefd17d6837ba503"), "name" : "洪七公", "hometown" : "华山", "age" : 40, "gender" : true }
> db.stu.find().skip(2).limit(2)
{ "_id" : ObjectId("5d856ac9aefd17d6837ba4ff"), "name" : "华筝", "hometown" : "蒙古", "age" : 18, "gender" : false }
{ "_id" : ObjectId("5d856addaefd17d6837ba500"), "name" : "黄药师", "hometown" : "桃花岛", "age" : 35, "gender" : true }
>
6.自定义查询
$where
> db.stu.find({$where: function() {return this.age>20}})
{ "_id" : ObjectId("5d856addaefd17d6837ba500"), "name" : "黄药师", "hometown" : "桃花岛", "age" : 35, "gender" : true }
{ "_id" : ObjectId("5d856afaaefd17d6837ba502"), "name" : "段王爷", "hometown" : "大理", "age" : 30, "gender" : true }
{ "_id" : ObjectId("5d856b08aefd17d6837ba503"), "name" : "洪七公", "hometown" : "华山", "age" : 40, "gender" : true }
>
7.投影:选择返回结果的字段
基本语法:db.COLLECTION_NAME.find({}, {字段A:1})
只显示字段A (_id默认为1,可以设为0)
> db.stu.find({age:{$gte:20}})
{ "_id" : ObjectId("5d856a6eaefd17d6837ba4fd"), "name" : "小明", "hometown" : "蒙古", "age" : 20, "gender" : true }
{ "_id" : ObjectId("5d856addaefd17d6837ba500"), "name" : "黄药师", "hometown" : "桃花岛", "age" : 35, "gender" : true }
{ "_id" : ObjectId("5d856afaaefd17d6837ba502"), "name" : "段王爷", "hometown" : "大理", "age" : 30, "gender" : true }
{ "_id" : ObjectId("5d856b08aefd17d6837ba503"), "name" : "洪七公", "hometown" : "华山", "age" : 40, "gender" : true }
> db.stu.find({age:{$gte:20}}, {name:1})
{ "_id" : ObjectId("5d856a6eaefd17d6837ba4fd"), "name" : "小明" }
{ "_id" : ObjectId("5d856addaefd17d6837ba500"), "name" : "黄药师" }
{ "_id" : ObjectId("5d856afaaefd17d6837ba502"), "name" : "段王爷" }
{ "_id" : ObjectId("5d856b08aefd17d6837ba503"), "name" : "洪七公" }
> db.stu.find({age:{$gte:20}}, {name:1, _id:0})
{ "name" : "小明" }
{ "name" : "黄药师" }
{ "name" : "段王爷" }
{ "name" : "洪七公" }
8.排序sort()
基本语法:db.COLLECTION_NAME.find().sort( {<query>} )
升序为1,降序为 -1
// 根据年龄从高到底排序
> db.stu.find()
{ "_id" : ObjectId("5d856a6eaefd17d6837ba4fd"), "name" : "小明", "hometown" : "蒙古", "age" : 20, "gender" : true }
{ "_id" : ObjectId("5d856ab1aefd17d6837ba4fe"), "name" : "黄蓉", "hometown" : "桃花岛", "age" : 18, "gender" : false }
{ "_id" : ObjectId("5d856ac9aefd17d6837ba4ff"), "name" : "华筝", "hometown" : "蒙古", "age" : 18, "gender" : false }
{ "_id" : ObjectId("5d856addaefd17d6837ba500"), "name" : "黄药师", "hometown" : "桃花岛", "age" : 35, "gender" : true }
{ "_id" : ObjectId("5d856aebaefd17d6837ba501"), "name" : "段誉", "hometown" : "大理", "age" : 18, "gender" : true }
{ "_id" : ObjectId("5d856afaaefd17d6837ba502"), "name" : "段王爷", "hometown" : "大理", "age" : 30, "gender" : true }
{ "_id" : ObjectId("5d856b08aefd17d6837ba503"), "name" : "洪七公", "hometown" : "华山", "age" : 40, "gender" : true }
> db.stu.find().sort({age:1})
{ "_id" : ObjectId("5d856ab1aefd17d6837ba4fe"), "name" : "黄蓉", "hometown" : "桃花岛", "age" : 18, "gender" : false }
{ "_id" : ObjectId("5d856ac9aefd17d6837ba4ff"), "name" : "华筝", "hometown" : "蒙古", "age" : 18, "gender" : false }
{ "_id" : ObjectId("5d856aebaefd17d6837ba501"), "name" : "段誉", "hometown" : "大理", "age" : 18, "gender" : true }
{ "_id" : ObjectId("5d856a6eaefd17d6837ba4fd"), "name" : "小明", "hometown" : "蒙古", "age" : 20, "gender" : true }
{ "_id" : ObjectId("5d856afaaefd17d6837ba502"), "name" : "段王爷", "hometown" : "大理", "age" : 30, "gender" : true }
{ "_id" : ObjectId("5d856addaefd17d6837ba500"), "name" : "黄药师", "hometown" : "桃花岛", "age" : 35, "gender" : true }
{ "_id" : ObjectId("5d856b08aefd17d6837ba503"), "name" : "洪七公", "hometown" : "华山", "age" : 40, "gender" : true }
>
9.统计个数count()
基本语法:db.COLLECTION_NAME.find(<query>).count()
,
db.COLLECTION_NAME.count(<query>)
> db.stu.count()
7
> db.stu.count({age:{$gte:20}})
4
> db.stu.find({age:{$gte:20}}).count()
4
10.去重distinct()
基本语法:db.COLLECTION_NAME.distinct("field", *<query>)
> db.stu.distinct("hometown" )
[ "蒙古", "桃花岛", "大理", "华山" ]
> db.stu.distinct("hometown",{age:{$gte:30}} )
[ "桃花岛", "大理", "华山" ]
聚合(aggregate)
1.什么是聚合?
聚合(aggregate)是基于数据处理的聚合管道,每个文档通过一个由多个阶段(stage)组成的管道,可以对每个阶段的管道进行分组、过滤等功能,然后经过一系列的处理,输出相应的结果。(简单来说就是把前一次的结果返回个下一个管道。)
基本语法:db.COLLOCTION_NAME.aggregate([ {pipe1:{expression}, pipe2:{express} } ])
2.常用表达式
处理输⼊⽂档并输出
语法:表达式:’$列名’
常⽤表达式:
$sum: 计算总和, $sum:1 表示以⼀倍计数
$avg: 计算平均值
$min: 获取最⼩值
$max: 获取最⼤值
$push: 在结果⽂档中插⼊值到⼀个数组中
$first: 根据资源⽂档的排序获取第⼀个⽂档数据
$last: 根据资源⽂档的排序获取最后⼀个⽂档数据
3.常用的管道命令
在mongodb中,⽂档处理完毕后, 通过管道进⾏下⼀次处理
常用管道如下:
$group: 将集合中的⽂档分组, 可⽤于统计结果
$match: 过滤数据, 只输出符合条件的⽂档
$project: 修改输⼊⽂档的结构, 如重命名、 增加、 删除字段、 创建计算结果
$sort: 将输⼊⽂档排序后输出
$limit: 限制聚合管道返回的⽂档数
$skip: 跳过指定数量的⽂档, 并返回余下的⽂档
$unwind: 将数组类型的字段进⾏拆分
示例:(stu.table)
> db.stu.find()
{ "_id" : ObjectId("5d856a6eaefd17d6837ba4fd"), "name" : "小明", "hometown" : "蒙古", "age" : 20, "gender" : true }
{ "_id" : ObjectId("5d856ab1aefd17d6837ba4fe"), "name" : "黄蓉", "hometown" : "桃花岛", "age" : 18, "gender" : false }
{ "_id" : ObjectId("5d856ac9aefd17d6837ba4ff"), "name" : "华筝", "hometown" : "蒙古", "age" : 18, "gender" : false }
{ "_id" : ObjectId("5d856addaefd17d6837ba500"), "name" : "黄药师", "hometown" : "桃花岛", "age" : 35, "gender" : true }
{ "_id" : ObjectId("5d856aebaefd17d6837ba501"), "name" : "段誉", "hometown" : "大理", "age" : 18, "gender" : true }
{ "_id" : ObjectId("5d856afaaefd17d6837ba502"), "name" : "段王爷", "hometown" : "大理", "age" : 30, "gender" : true }
{ "_id" : ObjectId("5d856b08aefd17d6837ba503"), "name" : "洪七公", "hometown" : "华山", "age" : 40, "gender" : true }
>
$group:{_id:"$xx", key:"$xxx"}
:分组
_id
:表示进行分组的依据,必须传入
key
:表示输出时的键
注意:
$group:{key1:value, key2:value2}
:里面有几个键,输出结果就有几个键- 分组依据是
_id
的value
- 取不同字段的值需要用
$
进行取值。如:{_id:"$xxx"}
示例1:
//对性别进行分组
> db.stu.aggregate(
... {
... $group:{_id:"$gender"} //对性别进行分组
... }
... )
{ "_id" : true }
{ "_id" : false }
// 对性别进行分组,并统计人数
> db.stu.aggregate(
... {
... $group:{_id:"$gender", count:{$sum:1}} // $sum:1表示以1为倍数,对其进行统计
... }
... )
{ "_id" : false, "count" : 2 }
{ "_id" : true, "count" : 5 }
// 对性别进行分组,并统计人数,同时统计平均年龄
> db.stu.aggregate(
... {
... $group:{_id:"$gender", count:{$sum:1}, mean_age:{$avg:"$age"}}
... }
... )
{ "_id" : true, "count" : 5, "mean_age" : 28.6 }
{ "_id" : false, "count" : 2, "mean_age" : 18 }
>
示例2:
// 按照hometo> db.stu.aggregate(
... {
... $group:{_id:"$hometown", mean_age:{$avg:"$age"}, count:{$sum:1}}
... }
... )
{ "_id" : "蒙古", "mean_age" : 19, "count" : 2 }
{ "_id" : "桃花岛", "mean_age" : 26.5, "count" : 2 }
{ "_id" : "大理", "mean_age" : 24, "count" : 2 }
{ "_id" : "华山", "mean_age" : 40, "count" : 1 }
>wn进行分组,获取不同组的平均年龄
示例3:(group by null:即把所有的文本分为一组)
// group by null
// 求学生总人数,平均年龄
> db.stu.aggregate(
... {
... $group:{_id:null, count:{$sum:1}, mean_age:{$avg:"$age"}}
... }
... )
{ "_id" : null, "count" : 7, "mean_age" : 25.571428571428573 }
>
$project : { key1 : ”$xxx“, key2 : 1 }
:修改输入输出文档的结果,如重命名,增加,删除字段,创建计算结果
key1
:新命名一个key
"$xxx"
:获取上一个管道xxx的value
key2:1
:显示key2:value (相当于key2:"$key2")
注意:
- 上一管道有的key,在project里面没有写入,则不显示
示例1:
> db.stu.aggregate(
... {
... $group:{_id:"$gender", count:{$sum:1}, mean_age:{$avg:"$age"}}
... }, // 管道之间用逗号隔开
... {
... $project:{gender:"$_id", count:"$count", mean_age:"$mean_age"} //”$_id“ 获取上一个管道_id的值
... }
... )
{ "_id" : true, "gender" : true, "count" : 5, "mean_age" : 28.6 }
{ "_id" : false, "gender" : false, "count" : 2, "mean_age" : 18 }
>
示例2:
> db.stu.aggregate(
... {
... $group:{_id:"$gender", count:{$sum:1}, mean_age:{$avg:"$age"}}
... },
... {
... $project:{_id:0, gender:"$_id", count:1}
... }
... )
{ "count" : 5, "gender" : true }
{ "count" : 2, "gender" : false }
>
$match:{}
:用于过滤数据,输出符合条件的文档、
示例1:
// 选择年龄大于18的学生,并统计男性和女性的人数
> db.stu.aggregate(
... {
... $match:> db.stu.aggregate(
... {
... $match:{age:{$gt:18}}
... },
... {
... $group:{_id:"$gender", count:{$sum:1}}
... },
... {
... $project:{_id:0, gender:"$_id", count:1}
... }
... )
{ "count" : 4, "gender" : true }
{ "count" : 1, "gender" : false }
>
示例2:
// 选择(年龄大于18或者来自桃花岛)的学生,并统计男性和女性的人数
> db.stu.aggregate(
... > db.stu.aggregate(
... {
... $match:{ $or:[ {age:{$gt:18}}, {hometown:"桃花岛"} ] }
... },
... {
... $group:{_id:"$gender", count:{$sum:1}}
... },
... {
... $project:{_id:0, gender:"$_id", count:1}
... }
... )
{ "count" : 4, "gender" : true }
{ "count" : 2, "gender" : false }
>
示例:
{ "country" : "china", "province" : "sh", "userid" : "a" }
{ "country" : "china", "province" : "sh", "userid" : "b" }
{ "country" : "china", "province" : "sh", "userid" : "a" }
{ "country" : "china", "province" : "sh", "userid" : "c" }
{ "country" : "china", "province" : "bj", "userid" : "da" }
{ "country" : "china", "province" : "bj", "userid" : "fa" }
{ "country" : "usa", "province" : "la", "userid" : "jack" }
需求:统计出每个country/province下的userid的数量(同一个userid只统计一次)
第一步:去重
> db.demo.aggregate(
... {
// _id的value要传入一个字典,这样才能把所有的信息的显示处理
... $group:{_id:{country:"$country", province:"$province", userid:"$userid"}}
... }
... )
{ "_id" : { "country" : "china", "province" : "bj", "userid" : "da" } }
{ "_id" : { "country" : "usa", "province" : "la", "userid" : "jack" } }
{ "_id" : { "country" : "china", "province" : "sh", "userid" : "ab" } }
{ "_id" : { "country" : "china", "province" : "bj", "userid" : "fa" } }
{ "_id" : { "country" : "china", "province" : "sh", "userid" : "a" } }
{ "_id" : { "country" : "china", "province" : "sh", "userid" : "c" } }
{ "_id" : { "country" : "china", "province" : "sh", "userid" : "b" } }
第二步:再分组
> db.demo.aggregate( { $group:{_id:{country:"$country", province:"$province", userid:"$userid"}} }, { $group:{_id:{country:"$_id.country", province:"$_id.province"}, count:{$sum:1}} }, { $project:{_id:0, country:"$_id.country", province:"$_id.province", count:1} } )
{ "count" : 2, "country" : "china", "province" : "bj" }
{ "count" : 1, "country" : "usa", "province" : "la" }
{ "count" : 4, "country" : "china", "province" : "sh" }
>
$sort:{}
:排序
示例:
// 查询男生、女生人数,被按人数降序
> db.stu.aggregate(
... {
... $group:{_id:"$gender", count:{$sum:1}}
... },
... {
... $sort:{count:-1}
... }
... )
{ "_id" : true, "count" : 5 }
{ "_id" : false, "count" : 3 }
$limit:{}
:限制$skip:{}
:跳过
示例:
> db.stu.aggregate({$limit:2})
{ "_id" : ObjectId("5d856a6eaefd17d6837ba4fd"), "name" : "小明", "hometown" : "蒙古", "age" : 20, "gender" : true }
{ "_id" : ObjectId("5d856ab1aefd17d6837ba4fe"), "name" : "黄蓉", "hometown" : "桃花岛", "age" : 18, "gender" : false }
> db.stu.aggregate({$skip:2})
{ "_id" : ObjectId("5d856ac9aefd17d6837ba4ff"), "name" : "华筝", "hometown" : "蒙古", "age" : 18, "gender" : false }
{ "_id" : ObjectId("5d856addaefd17d6837ba500"), "name" : "黄药师", "hometown" : "桃花岛", "age" : 35, "gender" : true }
{ "_id" : ObjectId("5d856aebaefd17d6837ba501"), "name" : "段誉", "hometown" : "大理", "age" : 18, "gender" : true }
{ "_id" : ObjectId("5d856afaaefd17d6837ba502"), "name" : "段王爷", "hometown" : "大理", "age" : 30, "gender" : true }
{ "_id" : ObjectId("5d856b08aefd17d6837ba503"), "name" : "洪七公", "hometown" : "华山", "age" : 40, "gender" : true }
{ "_id" : ObjectId("5d85bc7db6f03421ea742757"), "name" : "小红", "hometown" : "桃花岛", "age" : 25, "gender" : false }
> db.stu.aggregate({$skip:2}, {$limit:2})
{ "_id" : ObjectId("5d856ac9aefd17d6837ba4ff"), "name" : "华筝", "hometown" : "蒙古", "age" : 18, "gender" : false }
{ "_id" : ObjectId("5d856addaefd17d6837ba500"), "name" : "黄药师", "hometown" : "桃花岛", "age" : 35, "gender" : true }
>
> db.stu.aggregate({$group:{_id:"$hometown", count:{$sum:1}}}, {$limit:2})
{ "_id" : "桃花岛", "count" : 3 }
{ "_id" : "大理", "count" : 2 }
$unwind:{}
:根据某一数组类型字段拆分成多条,每条包含数组中的一个值
示例1:
// 数据准备
> db.t2.insert({item:'t-shirt', size:['S','M','L']})
WriteResult({ "nInserted" : 1 })
> db.t2.find()
{ "_id" : ObjectId("5d870818641d8fc7d9ff25af"), "item" : "t-shirt", "size" : [ "S", "M", "L" ] }
>
> db.t2.find()
{ "_id" : ObjectId("5d870818641d8fc7d9ff25af"), "item" : "t-shirt", "size" : [ "S", "M", "L" ] }
> db.t2.aggregate({$unwind:"$size"})
{ "_id" : ObjectId("5d870818641d8fc7d9ff25af"), "item" : "t-shirt", "size" : "S" }
{ "_id" : ObjectId("5d870818641d8fc7d9ff25af"), "item" : "t-shirt", "size" : "M" }
{ "_id" : ObjectId("5d870818641d8fc7d9ff25af"), "item" : "t-shirt", "size" : "L" }
>
示例2:
// 数据库中有一条数据:{"username":"Alex","tags": ['C#','Java','C++']},如何获取该tag列表的长度?
> db.t2.find()
{ "_id" : ObjectId("5d870818641d8fc7d9ff25af"), "item" : "t-shirt", "size" : [ "S", "M", "L" ] }
{ "_id" : ObjectId("5d87094a641d8fc7d9ff25b0"), "usename" : "Alex", "tags" : [ "c#", "python", "c++" ] }
> db.t2.aggregate( { $match:{"usename":"Alex"} }, { $unwind:"$tags" }, { $group:{_id:null, count:{$sum:1}} } )
{ "_id" : null, "count" : 3 }
>
属性 值为false表示丢弃属性值为空的⽂档
属性preserveNullAndEmptyArrays值为true表示保留属性值为空的⽂档
用法:
db.COLLECTION_NAME.aggregate(
{
KaTeX parse error: Expected '}', got 'EOF' at end of input: unwind:{path:"FIELD_NAME", preserveNullAndEmptyArray:true/false} // 这样可以防止数据丢失
}
)
索引
索引:以提升查询速度
测试:插入10万条数据到数据库中
for(i=0;i<100000;i++){db.t12.insert({name:'test'+i,age:i})}
db.t1.find({name:'test10000'})
db.t1.find({name:'test10000'}).explain('executionStats')
建立索引之后对比:
语法:db.集合.ensureIndex({属性:1}),1表示升序, -1表示降序
具体操作:db.t1.ensureIndex({name:1})
db.t1.find({name:‘test10000’}).explain(‘executionStats’)
- 在默认情况下创建的索引均不是唯一索引。
- 创建唯一索引:
db.t1.ensureIndex({"name":1},{"unique":true})
- 创建唯一索引并消除重复:
db.t1.ensureIndex({"name":1},{"unique":true,"dropDups":true})
- 建立联合索引(什么时候需要联合索引):
db.t1.ensureIndex({name:1,age:1})
- 查看当前集合的所有索引:
db.t1.getIndexes()
- 删除索引:
db.t1.dropIndex('索引名称')
数据备份与恢复
windows系统下,先切换到mongodb数据库的bin目录下,再进行如下操作:
备份
mongodump -h <dbhost> -d <dbname> -o <dbdirectory>
-h: 服务器地址,也可以指定端口号(可以不写 -h 的内容)
-d: 需要备份的数据库
-o: 备份数据要存放的位置
示例:将test数据库备份到D:\mongodb_test目录下
D:\mongodb\bin>mongodump -d test -o D:\mongodb_test
D:\mongodb\bin>mongodump -h 127.0.0.1:27017 -d test -o D:\mongodb_test
2019-09-21T09:37:36.881+0800 writing test.stu to
2019-09-21T09:37:36.907+0800 writing test.test_collection to
2019-09-21T09:37:36.910+0800 writing test.test2_collection to
2019-09-21T09:37:36.913+0800 writing test.mycol to
2019-09-21T09:37:36.914+0800 done dumping test.stu (7 documents)
2019-09-21T09:37:36.918+0800 done dumping test.test_collection (2 documents)
2019-09-21T09:37:36.924+0800 done dumping test.test2_collection (1 document)
2019-09-21T09:37:36.929+0800 done dumping test.mycol (0 documents)
恢复
mongorestore -h <dbhost> -d <dbname> --dir <dbdirectory>
-h: 服务器地址(可以不填)
-d: 需要备份到哪个数据库
–dir: 备份数据来源
D:\mongodb\bin>mongorestore -d test2 --dir D:\mongodb_test\test
2019-09-21T09:43:42.046+0800 the --db and --collection args should only be used when restoring from a BSON file. Other uses are deprecated and will not exist in the future; use --nsInclude instead
2019-09-21T09:43:42.084+0800 building a list of collections to restore from D:\mongodb_test\test dir
2019-09-21T09:43:42.092+0800 reading metadata for test2.stu from D:\mongodb_test\test\stu.metadata.json
...