MongoDB系列--03、mongoDB常用命令
数据库操作命令
创建数据库
use DATABASE_NAME # 如果数据库不存在,则创建数据库,否则切换到指定数据库
> use christy
switched to db christy
> db
christy
查看数据库
> show dbs
admin 0.000GB
config 0.000GB
local 0.000GB
可以看到上面没有我们刚才使用的数据库christy
,如果要显示它需要向数据库中插入一些数据
> db.first_collection.insert({"name":"christy","age":18})
{"t":{"$date":"2020-11-02T20:22:35.364+08:00"},"s":"I", "c":"STORAGE", "id":20320, "ctx":"conn1","msg":"createCollection","attr":{"namespace":"christy.first_collection","uuidDisposition":"generated","uuid":{"uuid":{"$uuid":"0a3a0f06-6d16-4546-8428-155c884262d9"}},"options":{}}}
{"t":{"$date":"2020-11-02T20:22:35.400+08:00"},"s":"I", "c":"INDEX", "id":20345, "ctx":"conn1","msg":"Index build: done building","attr":{"buildUUID":null,"namespace":"christy.first_collection","index":"_id_","commitTimestamp":{"$timestamp":{"t":0,"i":0}}}}
WriteResult({ "nInserted" : 1 })
> show dbs
admin 0.000GB
christy 0.000GB
config 0.000GB
local 0.000GB
我们向数据库christy
中的first_collection
中插入了一条数据{"name":"christy","age":18}
,再次执行show dbs
就可以看到我们的数据库了
删除数据库
db.dropDatabase()
这个命令是删除当前数据库,所以再删除数据库之前你需要使用use
命令切换到你要删除的数据库。
db
是查看当前数据库命令
> use christy
switched to db christy
> db.dropDatabase()
{"t":{"$date":"2020-11-02T20:29:59.612+08:00"},"s":"I", "c":"COMMAND", "id":20337, "ctx":"conn2","msg":"dropDatabase - starting","attr":{"db":"christy"}}
{"t":{"$date":"2020-11-02T20:29:59.612+08:00"},"s":"I", "c":"COMMAND", "id":20338, "ctx":"conn2","msg":"dropDatabase - dropping collection","attr":{"db":"christy","namespace":"christy.first_collection"}}
{"t":{"$date":"2020-11-02T20:29:59.613+08:00"},"s":"I", "c":"COMMAND", "id":20336, "ctx":"conn2","msg":"dropDatabase","attr":{"db":"christy","numCollectionsDropped":1}}
{ "dropped" : "christy", "ok" : 1 }
上述操作执行完毕后数据库christy
已经被删除,再执行show dbs
时已经看不到christy
数据库了
> show dbs
admin 0.000GB
config 0.000GB
local 0.000GB
集合操作命令
mongoDB中的集合相当于MySQL中的table
创建集合
mongoDB 中使用 createCollection() 方法来创建集合
db.createCollection(name,options)
# name:要创建的集合(mysql中对应table)名称
# options:可选参数,指定有关内存大小及索引的选项
options可以是以下参数
字段 | 类型 | 描述 |
---|---|---|
capped | 布尔 | (可选)如果为 true,则创建固定集合。固定集合是指有着固定大小的集合,当达到最大值时,它会自动覆盖最早的文档。 当该值为 true 时,必须指定 size 参数 |
autoIndexId | 布尔 | (可选)如为 true,自动在 _id 字段创建索引。默认为 false |
size | 数值 | (可选)为固定集合指定一个最大值(以字节计)。 如果 capped 为 true,也需要指定该字段 |
max | 数值 | (可选)指定固定集合中包含文档的最大数量 |
在插入文档时,mongoDB 首先检查固定集合的 size 字段,然后检查 max 字段。
use christy
switched to db christy
> db.createCollection("first_collection")
{"t":{"$date":"2020-11-02T20:40:05.920+08:00"},"s":"I", "c":"STORAGE", "id":20320, "ctx":"conn3","msg":"createCollection","attr":{"namespace":"christy.first_collection","uuidDisposition":"generated","uuid":{"uuid":{"$uuid":"5a74c819-fc93-4950-9182-7d2d67e59e27"}},"options":{}}}
{"t":{"$date":"2020-11-02T20:40:05.926+08:00"},"s":"I", "c":"INDEX", "id":20345, "ctx":"conn3","msg":"Index build: done building","attr":{"buildUUID":null,"namespace":"christy.first_collection","index":"_id_","commitTimestamp":{"$timestamp":{"t":0,"i":0}}}}
{ "ok" : 1 }
显示集合
执行show collections
可以看到上面我们创建的集合
> show collections
first_collection
删除集合
集合的删除语法也很简单
db.collection.drop() #还是一个函数,collection是要删除的集合的名称
> show collections
first_collection
> db.first_collection.drop()
{"t":{"$date":"2020-11-02T20:45:47.498+08:00"},"s":"I", "c":"COMMAND", "id":518070, "ctx":"conn3","msg":"CMD: drop","attr":{"namespace":"christy.first_collection"}}
{"t":{"$date":"2020-11-02T20:45:47.498+08:00"},"s":"I", "c":"STORAGE", "id":23879, "ctx":"conn3","msg":"About to abort all index builders","attr":{"namespace":"christy.first_collection","uuid":{"uuid":{"$uuid":"5a74c819-fc93-4950-9182-7d2d67e59e27"}},"reason":"Collection christy.first_collection(5a74c819-fc93-4950-9182-7d2d67e59e27) is being dropped"}}
{"t":{"$date":"2020-11-02T20:45:47.499+08:00"},"s":"I", "c":"STORAGE", "id":20318, "ctx":"conn3","msg":"Finishing collection drop","attr":{"namespace":"christy.first_collection","uuid":{"uuid":{"$uuid":"5a74c819-fc93-4950-9182-7d2d67e59e27"}}}}
true
最后返回true
说明删除成功,这时候再去show collections
,发现已经为空
> show collections
>
文档的操作命令
mongoDB中的文档相当于MySQL中的行或者说记录
插入文档
mongoDB中使用insert或者save方法向集合中插入文档
db.collection_name.insert(document);
> var document = {"name":"christy","age":18}
> db.first_collection.insert(document)
{"t":{"$date":"2020-11-02T20:54:19.158+08:00"},"s":"I", "c":"STORAGE", "id":20320, "ctx":"conn3","msg":"createCollection","attr":{"namespace":"christy.first_collection","uuidDisposition":"generated","uuid":{"uuid":{"$uuid":"358a36e2-0731-410c-8930-81d321702ebb"}},"options":{}}}
{"t":{"$date":"2020-11-02T20:54:19.184+08:00"},"s":"I", "c":"INDEX", "id":20345, "ctx":"conn3","msg":"Index build: done building","attr":{"buildUUID":null,"namespace":"christy.first_collection","index":"_id_","commitTimestamp":{"$timestamp":{"t":0,"i":0}}}}
WriteResult({ "nInserted" : 1 })
上述方法先是定义了一个document,然后执行的插入操作。
查询文档
db.collection.find(query, projection)
query :可选,使用查询操作符指定查询条件
projection :可选,使用投影操作符指定返回的键。查询时返回文档中所有键值, 只需省略该参数即可(默认省略)
如果你需要以易读的方式来读取数据,可以使用 pretty() 方法,语法格式如下:
db.mycollection.find().pretty()
> db.first_collection.find().pretty()
{
"_id" : ObjectId("5fa0017b1453bd91e72a6101"),
"name" : "christy",
"age" : 18
}
{
"_id" : ObjectId("5fa003741453bd91e72a6102"),
"name" : "Tide",
"age" : 19
}
删除文档
MongoDB的remove()函数是用来移除集合中的数据。在执行remove()函数前先执行find()命令来判断执行的条件是否正确,这是一个比较好的习惯
db.collection.remove(
<query>,
{
justOne: <boolean>,
writeConcern: <document>
}
)
query :(可选)删除的文档的条件
justOne : (可选)如果设为 true 或 1,则只删除一个文档,如果不设置该参数,或使用默认值 false,则删除所有匹配条件的文档
writeConcern :(可选)抛出异常的级别
我们现在移除数据库christy
的集合first_collection
中name为Tide的数据
> db.first_collection.remove({"name":"Tide"})
WriteResult({ "nRemoved" : 1 })
返回结果先是移除一条数据
如果你想一次性删除集合中的所有数据,只需执行
> db.collection_name.remove({})
# 这个就类似于MySQL中的truncate
文档排序
在 MongoDB 中使用 sort() 方法对数据(文档)进行排序,sort() 方法可以通过参数指定排序的字段,并使用 1 和 -1来指定排序的方式,其中 1 为升序排列,而 -1 是用于降序排列。
> db.collection_name.find().sort({KEY:1})
比如我们对数据库christy
的集合first_collection
中的文档按照age字段升序
> db.first_collection.find().sort({"age":1})
{ "_id" : ObjectId("5fa0072e1453bd91e72a6104"), "name" : "baby", "age" : 4 }
{ "_id" : ObjectId("5fa0017b1453bd91e72a6101"), "name" : "christy", "age" : 18 }
{ "_id" : ObjectId("5fa0071b1453bd91e72a6103"), "name" : "Tide", "age" : 19 }
limit()
如果你需要在MongoDB中读取指定数量的数据记录,可以使用MongoDB的Limit方法,limit()方法接受一个数字参数,该参数指定从MongoDB中读取的记录条数
> db.collection_name.find(<query>).limit(number)
目前我们数据库中有以下数据
> db.first_collection.find().pretty()
{
"_id" : ObjectId("5fa0017b1453bd91e72a6101"),
"name" : "christy",
"age" : 18
}
{
"_id" : ObjectId("5fa0071b1453bd91e72a6103"),
"name" : "Tide",
"age" : 19
}
{
"_id" : ObjectId("5fa0072e1453bd91e72a6104"),
"name" : "baby",
"age" : 4
}
目前我们的需求是查找文档中的两条记录,那么我们可以这么写
> db.first_collection.find({},{"_id":0,"name":1,"age":1}).limit(2)
{ "name" : "christy", "age" : 18 }
{ "name" : "Tide", "age" : 19 }
find函数这里说明一下
{}:第一个花括号是查询条件,为空则说明查询所有的记录
{“_id”:0,“name”:1,“age”:1}:指的是查询结果id不显示,name和age显示
skip()
我们除了可以使用limit()方法来读取指定数量的数据外,还可以使用skip()方法来跳过指定数量的数据,skip方法同样接受一个数字参数作为跳过的记录条数
> db.collection_name.find().limit(NUMBER).skip(NUMBER)
> db.first_collection.find({},{"_id":0,"name":1,"age":1}).limit(1).skip(1)
{ "name" : "Tide", "age" : 19 }
skip()方法默认参数为0
mongoDB 索引
创建索引
MongoDB使用 createIndex() 方法来创建索引
> db.collection_name.createIndex(keys, options)
语法中 Key 值为你要创建的索引字段,1 为指定按升序创建索引,如果你想按降序来创建索引指定为 -1 即可
createIndex() 方法中你也可以设置使用多个字段创建索引(关系型数据库中称作复合索引)
> db.col.createIndex({"title":1,"description":-1})
createIndex() 接收可选参数,可选参数列表如下:
Parameter | Type | Description |
---|---|---|
background | Boolean | 建索引过程会阻塞其它数据库操作,background可指定以后台方式创建索引,即增加 “background” 可选参数。 “background” 默认值为false。 |
unique | Boolean | 建立的索引是否唯一。指定为true创建唯一索引。默认值为false. |
name | string | 索引的名称。如果未指定,MongoDB的通过连接索引的字段名和排序顺序生成一个索引名称。 |
dropDups | Boolean | **3.0+版本已废弃。**在建立唯一索引时是否删除重复记录,指定 true 创建唯一索引。默认值为 false. |
sparse | Boolean | 对文档中不存在的字段数据不启用索引;这个参数需要特别注意,如果设置为true的话,在索引字段中不会查询出不包含对应字段的文档.。默认值为 false. |
expireAfterSeconds | integer | 指定一个以秒为单位的数值,完成 TTL设定,设定集合的生存时间。 |
v | index version | 索引的版本号。默认的索引版本取决于mongod创建索引时运行的版本。 |
weights | document | 索引权重值,数值在 1 到 99,999 之间,表示该索引相对于其他索引字段的得分权重。 |
default_language | string | 对于文本索引,该参数决定了停用词及词干和词器的规则的列表。 默认为英语 |
language_override | string | 对于文本索引,该参数指定了包含在文档中的字段名,语言覆盖默认的language,默认值为 language. |
例如,我们对数据库christy
的集合first_collection
中的文档字段name创建一个升序的索引
> db.first_collection.createIndex({name:1})
{"t":{"$date":"2020-11-02T21:58:42.747+08:00"},"s":"I", "c":"INDEX", "id":20438, "ctx":"conn4","msg":"Index build: registering","attr":{"buildUUID":{"uuid":{"$uuid":"25cb9cb7-a2c6-4e91-8352-02de9c600e61"}},"namespace":"christy.first_collection","collectionUUID":{"uuid":{"$uuid":"358a36e2-0731-410c-8930-81d321702ebb"}},"indexes":1,"firstIndex":{"name":"name_1"}}}
{"t":{"$date":"2020-11-02T21:58:42.774+08:00"},"s":"I", "c":"INDEX", "id":20384, "ctx":"IndexBuildsCoordinatorMongod-0","msg":"Index build: starting","attr":{"namespace":"christy.first_collection","buildUUID":null,"properties":{"v":2,"key":{"name":1.0},"name":"name_1"},"method":"Hybrid","maxTemporaryMemoryUsageMB":200}}
{"t":{"$date":"2020-11-02T21:58:42.774+08:00"},"s":"I", "c":"INDEX", "id":20440, "ctx":"conn4","msg":"Index build: waiting for index build to complete","attr":{"buildUUID":{"uuid":{"$uuid":"25cb9cb7-a2c6-4e91-8352-02de9c600e61"}},"deadline":{"$date":{"$numberLong":"9223372036854775807"}}}}
{"t":{"$date":"2020-11-02T21:58:42.774+08:00"},"s":"I", "c":"INDEX", "id":20391, "ctx":"IndexBuildsCoordinatorMongod-0","msg":"Index build: collection scan done","attr":{"buildUUID":null,"totalRecords":3,"durationMillis":0}}
{"t":{"$date":"2020-11-02T21:58:42.775+08:00"},"s":"I", "c":"INDEX", "id":20685, "ctx":"IndexBuildsCoordinatorMongod-0","msg":"Index build: inserted keys from external sorter into index","attr":{"namespace":"christy.first_collection","index":"name_1","keysInserted":3,"durationMillis":0}}
{"t":{"$date":"2020-11-02T21:58:42.776+08:00"},"s":"I", "c":"STORAGE", "id":3856203, "ctx":"IndexBuildsCoordinatorMongod-0","msg":"Index build: waiting for next action before completing final phase","attr":{"buildUUID":{"uuid":{"$uuid":"25cb9cb7-a2c6-4e91-8352-02de9c600e61"}}}}
{"t":{"$date":"2020-11-02T21:58:42.776+08:00"},"s":"I", "c":"STORAGE", "id":3856204, "ctx":"IndexBuildsCoordinatorMongod-0","msg":"Index build: received signal","attr":{"buildUUID":{"uuid":{"$uuid":"25cb9cb7-a2c6-4e91-8352-02de9c600e61"}},"action":"Single-phase commit"}}
{"t":{"$date":"2020-11-02T21:58:42.776+08:00"},"s":"I", "c":"INDEX", "id":20345, "ctx":"IndexBuildsCoordinatorMongod-0","msg":"Index build: done building","attr":{"buildUUID":null,"namespace":"christy.first_collection","index":"name_1","commitTimestamp":{"$timestamp":{"t":0,"i":0}}}}
{"t":{"$date":"2020-11-02T21:58:42.776+08:00"},"s":"I", "c":"STORAGE", "id":20663, "ctx":"IndexBuildsCoordinatorMongod-0","msg":"Index build: completed successfully","attr":{"buildUUID":{"uuid":{"$uuid":"25cb9cb7-a2c6-4e91-8352-02de9c600e61"}},"namespace":"christy.first_collection","uuid":{"uuid":{"$uuid":"358a36e2-0731-410c-8930-81d321702ebb"}},"indexesBuilt":1,"numIndexesBefore":1,"numIndexesAfter":2}}
{"t":{"$date":"2020-11-02T21:58:42.777+08:00"},"s":"I", "c":"INDEX", "id":20447, "ctx":"conn4","msg":"Index build: completed","attr":{"buildUUID":{"uuid":{"$uuid":"25cb9cb7-a2c6-4e91-8352-02de9c600e61"}}}}
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
结果蹦出来那么一大坨东西,我们的索引到底有没有创建成功呢?我们来看下查询索引的命令
查看集合索引
> db.first_collection.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_"
},
{
"v" : 2,
"key" : {
"name" : 1
},
"name" : "name_1"
}
]
可以看到集合first_collection
中有两个索引:_id是自动生成的,这个不管;另外一个就是我们刚才自己新增的name
索引
查看集合索引大小
db.collection_name.totalIndexSize()
删除集合所有索引
db.collection_name.dropIndexes()
删除集合指定索引
db.collection_name.dropIndex("索引名称")
聚合查询
MongoDB中聚合(aggregate)主要用于处理数据(诸如统计平均值,求和等),并返回计算后的数据结果
> db.collection_name.aggregate(AGGREGATE_OPERATION)
表达式 | SQL操作 | 描述 | 实例 |
---|---|---|---|
$group | group by | 分组 | |
$sum | count(),sum() | 计算总和 | db.mycol.aggregate([{KaTeX parse error: Expected '}', got 'EOF' at end of input: …roup : {_id : "by_user", num_tutorial : { s u m : " sum : " sum:"likes"}}}]) |
$avg | avg() | 计算平均值 | db.mycol.aggregate([{KaTeX parse error: Expected '}', got 'EOF' at end of input: …roup : {_id : "by_user", num_tutorial : { a v g : " avg : " avg:"likes"}}}]) |
$min | min() | 获取集合中所有文档对应值得最小值。 | db.mycol.aggregate([{KaTeX parse error: Expected '}', got 'EOF' at end of input: …roup : {_id : "by_user", num_tutorial : { m i n : " min : " min:"likes"}}}]) |
$max | max() | 获取集合中所有文档对应值得最大值。 | db.mycol.aggregate([{KaTeX parse error: Expected '}', got 'EOF' at end of input: …roup : {_id : "by_user", num_tutorial : { m a x : " max : " max:"likes"}}}]) |
$match | where,having | 查询条件 | |
$sort | order by | 排序 | |
$limit | limit | 取条数 | |
$project | select | 选择 | |
$lookup | join | 连接 | |
$push | 在结果文档中插入值到一个数组中 | db.mycol.aggregate([{KaTeX parse error: Expected '}', got 'EOF' at end of input: …roup : {_id : "by_user", url : { p u s h : " push: " push:"url"}}}]) | |
$addToSet | 在结果文档中插入值到一个数组中,但不创建副本 | db.mycol.aggregate([{KaTeX parse error: Expected '}', got 'EOF' at end of input: …roup : {_id : "by_user", url : { a d d T o S e t : " addToSet : " addToSet:"url"}}}]) | |
$first | 根据资源文档的排序获取第一个文档数据 | db.mycol.aggregate([{KaTeX parse error: Expected '}', got 'EOF' at end of input: …roup : {_id : "by_user", first_url : { f i r s t : " first : " first:"url"}}}]) | |
$last | 根据资源文档的排序获取最后一个文档数据 | db.mycol.aggregate([{KaTeX parse error: Expected '}', got 'EOF' at end of input: …roup : {_id : "by_user", last_url : { l a s t : " last : " last:"url"}}}]) |
本表参考文章:
https://www.cnblogs.com/zhoujie/p/mongo1.html
https://www.runoob.com/mongodb/mongodb-aggregate.html
开始聚合查询之前我们先准备一组数据
> var document = [{"name":"zhangsan","age":12,"addr":"HF"},{"name":"lisi","age":13,"addr":"HF"},{"name":"wangwu","age":14,"addr":"WH"},{"name":"zhaoliu","age":15,"addr":"WH"},{"name":"qianqi","age":16,"addr":"BB"},{"name":"zhangba","age":17,"addr":"BB"},{"name":"Christy","age":18,"addr":"AH"},{"name":"Tide","age":19,"addr":"AH"}]
> db.second_collection.insert(document)
插入完成后我们可以查看一下完整的数据如下
> db.second_collection.find().pretty()
{
"_id" : ObjectId("5fa018db41c583a81e394185"),
"name" : "zhangsan",
"age" : 12,
"addr" : "HF"
}
{
"_id" : ObjectId("5fa018db41c583a81e394186"),
"name" : "lisi",
"age" : 13,
"addr" : "HF"
}
{
"_id" : ObjectId("5fa018db41c583a81e394187"),
"name" : "wangwu",
"age" : 14,
"addr" : "WH"
}
{
"_id" : ObjectId("5fa018db41c583a81e394188"),
"name" : "zhaoliu",
"age" : 15,
"addr" : "WH"
}
{
"_id" : ObjectId("5fa018db41c583a81e394189"),
"name" : "qianqi",
"age" : 16,
"addr" : "BB"
}
{
"_id" : ObjectId("5fa018db41c583a81e39418a"),
"name" : "zhangba",
"age" : 17,
"addr" : "BB"
}
{
"_id" : ObjectId("5fa018db41c583a81e39418b"),
"name" : "Christy",
"age" : 18,
"addr" : "AH"
}
{
"_id" : ObjectId("5fa018db41c583a81e39418c"),
"name" : "Tide",
"age" : 19,
"addr" : "AH"
}
aggregate
> db.second_collection.aggregate([])
{ "_id" : ObjectId("5fa018db41c583a81e394185"), "name" : "zhangsan", "age" : 12, "addr" : "HF" }
{ "_id" : ObjectId("5fa018db41c583a81e394186"), "name" : "lisi", "age" : 13, "addr" : "HF" }
{ "_id" : ObjectId("5fa018db41c583a81e394187"), "name" : "wangwu", "age" : 14, "addr" : "WH" }
{ "_id" : ObjectId("5fa018db41c583a81e394188"), "name" : "zhaoliu", "age" : 15, "addr" : "WH" }
{ "_id" : ObjectId("5fa018db41c583a81e394189"), "name" : "qianqi", "age" : 16, "addr" : "BB" }
{ "_id" : ObjectId("5fa018db41c583a81e39418a"), "name" : "zhangba", "age" : 17, "addr" : "BB" }
{ "_id" : ObjectId("5fa018db41c583a81e39418b"), "name" : "Christy", "age" : 18, "addr" : "AH" }
{ "_id" : ObjectId("5fa018db41c583a81e39418c"), "name" : "Tide", "age" : 19, "addr" : "AH" }
$ group与$sum
> db.second_collection.aggregate([{$group:{_id:"$addr",snum:{$sum:"$age"}}}])
{ "_id" : "WH", "snum" : 29 }
{ "_id" : "HF", "snum" : 25 }
{ "_id" : "BB", "snum" : 33 }
{ "_id" : "AH", "snum" : 37 }
上面的意思是根据籍贯分组并计算每个地方人数的年龄之和,类似于mysql中的
select addr,sum(age) from second_collection group by addr
再比如:根据籍贯求人员个数
> db.second_collection.aggregate([{$group:{_id:"$addr",scount:{$sum:1}}}])
{ "_id" : "WH", "scount" : 2 }
{ "_id" : "HF", "scount" : 2 }
{ "_id" : "BB", "scount" : 2 }
{ "_id" : "AH", "scount" : 2 }
$avg
需求:根据籍贯分组对年龄求平均
> db.second_collection.aggregate([{$group:{_id:"$addr",avg:{$avg:"$age"}}}])
{ "_id" : "WH", "avg" : 14.5 }
{ "_id" : "HF", "avg" : 12.5 }
{ "_id" : "BB", "avg" : 16.5 }
{ "_id" : "AH", "avg" : 18.5 }
$ min与$max
需求:按照籍贯分组求每个籍贯的最小与最大年龄
> db.second_collection.aggregate([{$group:{_id:"$addr",min:{$min:"$age"}}}])
{ "_id" : "WH", "min" : 14 }
{ "_id" : "HF", "min" : 12 }
{ "_id" : "BB", "min" : 16 }
{ "_id" : "AH", "min" : 18 }
> db.second_collection.aggregate([{$group:{_id:"$addr",max:{$max:"$age"}}}])
{ "_id" : "WH", "max" : 15 }
{ "_id" : "HF", "max" : 13 }
{ "_id" : "BB", "max" : 17 }
{ "_id" : "AH", "max" : 19 }
$match
需求:取年龄大于15且小于18的记录
> db.second_collection.aggregate([{$match:{age:{$gt:15,$lte:18}}}])
{ "_id" : ObjectId("5fa018db41c583a81e394189"), "name" : "qianqi", "age" : 16, "addr" : "BB" }
{ "_id" : ObjectId("5fa018db41c583a81e39418a"), "name" : "zhangba", "age" : 17, "addr" : "BB" }
{ "_id" : ObjectId("5fa018db41c583a81e39418b"), "name" : "Christy", "age" : 18, "addr" : "AH" }
再进一步,将上述结果按照城市分组并求个数
> db.second_collection.aggregate([{$match:{age:{$gt:15,$lte:18}}},{$group:{_id:"$addr",scount:{$sum:1}}}])
{ "_id" : "AH", "scount" : 1 }
{ "_id" : "BB", "scount" : 2 }
$sort
需求:按照年龄倒序排序
> db.second_collection.aggregate([{$sort:{age:-1}}])
{ "_id" : ObjectId("5fa018db41c583a81e39418c"), "name" : "Tide", "age" : 19, "addr" : "AH" }
{ "_id" : ObjectId("5fa018db41c583a81e39418b"), "name" : "Christy", "age" : 18, "addr" : "AH" }
{ "_id" : ObjectId("5fa018db41c583a81e39418a"), "name" : "zhangba", "age" : 17, "addr" : "BB" }
{ "_id" : ObjectId("5fa018db41c583a81e394189"), "name" : "qianqi", "age" : 16, "addr" : "BB" }
{ "_id" : ObjectId("5fa018db41c583a81e394188"), "name" : "zhaoliu", "age" : 15, "addr" : "WH" }
{ "_id" : ObjectId("5fa018db41c583a81e394187"), "name" : "wangwu", "age" : 14, "addr" : "WH" }
{ "_id" : ObjectId("5fa018db41c583a81e394186"), "name" : "lisi", "age" : 13, "addr" : "HF" }
{ "_id" : ObjectId("5fa018db41c583a81e394185"), "name" : "zhangsan", "age" : 12, "addr" : "HF" }
$limit
需求:取上述倒序的结果得前3条数据
> db.second_collection.aggregate([{$sort:{age:-1}},{$limit:3}])
{ "_id" : ObjectId("5fa018db41c583a81e39418c"), "name" : "Tide", "age" : 19, "addr" : "AH" }
{ "_id" : ObjectId("5fa018db41c583a81e39418b"), "name" : "Christy", "age" : 18, "addr" : "AH" }
{ "_id" : ObjectId("5fa018db41c583a81e39418a"), "name" : "zhangba", "age" : 17, "addr" : "BB" }
$project
需求:将上面三条数据不显示“_id”字段
> db.second_collection.aggregate([{$project:{_id:0,name:1,age:1,addr:1}},{$sort:{age:-1}},{$limit:3}])
{ "name" : "Tide", "age" : 19, "addr" : "AH" }
{ "name" : "Christy", "age" : 18, "addr" : "AH" }
{ "name" : "zhangba", "age" : 17, "addr" : "BB" }
$lookup
这个命令是用于多个文档关联的,使用这个命令之前需要特别处理一下数据
> var document = [{"_id":1,"name":"male"},{"_id":2,"name":"female"}]
> db.code_collection.insert(document)
{"t":{"$date":"2020-11-03T19:14:24.471+08:00"},"s":"I", "c":"STORAGE", "id":20320, "ctx":"conn3","msg":"createCollection","attr":{"namespace":"christy.code_collection","uuidDisposition":"generated","uuid":{"uuid":{"$uuid":"4daa6ed6-2b40-4755-945b-221c4eecb124"}},"options":{}}}
{"t":{"$date":"2020-11-03T19:14:24.477+08:00"},"s":"I", "c":"INDEX", "id":20345, "ctx":"conn3","msg":"Index build: done building","attr":{"buildUUID":null,"namespace":"christy.code_collection","index":"_id_","commitTimestamp":{"$timestamp":{"t":0,"i":0}}}}
BulkWriteResult({
"writeErrors" : [ ],
"writeConcernErrors" : [ ],
"nInserted" : 2,
"nUpserted" : 0,
"nMatched" : 0,
"nModified" : 0,
"nRemoved" : 0,
"upserted" : [ ]
})
> db.code_collection.find()
{ "_id" : 1, "name" : "male" }
{ "_id" : 2, "name" : "female" }
新建一个代码集的集合,里面有两个类别male
与female
。另外,给我们的second_collection
中的文档新增一个sex
字段
> db.second_collection.update({},{$set:{sex:1}},{multi:1})
WriteResult({ "nMatched" : 8, "nUpserted" : 0, "nModified" : 8 })
> db.second_collection.find()
{ "_id" : ObjectId("5fa018db41c583a81e394185"), "name" : "zhangsan", "age" : 12, "addr" : "HF", "sex" : 1 }
{ "_id" : ObjectId("5fa018db41c583a81e394186"), "name" : "lisi", "age" : 13, "addr" : "HF", "sex" : 1 }
{ "_id" : ObjectId("5fa018db41c583a81e394187"), "name" : "wangwu", "age" : 14, "addr" : "WH", "sex" : 1 }
{ "_id" : ObjectId("5fa018db41c583a81e394188"), "name" : "zhaoliu", "age" : 15, "addr" : "WH", "sex" : 1 }
{ "_id" : ObjectId("5fa018db41c583a81e394189"), "name" : "qianqi", "age" : 16, "addr" : "BB", "sex" : 1 }
{ "_id" : ObjectId("5fa018db41c583a81e39418a"), "name" : "zhangba", "age" : 17, "addr" : "BB", "sex" : 1 }
{ "_id" : ObjectId("5fa018db41c583a81e39418b"), "name" : "Christy", "age" : 18, "addr" : "AH", "sex" : 1 }
{ "_id" : ObjectId("5fa018db41c583a81e39418c"), "name" : "Tide", "age" : 19, "addr" : "AH", "sex" : 1 }
需求:关联second_collection a
与code_collection b
,将a的”sex”与b的“_id”相关联,在a中显示sex对应的name
> db.second_collection.aggregate([{$lookup:{from:"code_collection",localField:"sex",foreignField:"_id",as:"code_name"}},{$project:{"_id":0}}])
{ "name" : "zhangsan", "age" : 12, "addr" : "HF", "sex" : 1, "code_name" : [ { "_id" : 1, "name" : "male" } ] }
{ "name" : "lisi", "age" : 13, "addr" : "HF", "sex" : 1, "code_name" : [ { "_id" : 1, "name" : "male" } ] }
{ "name" : "wangwu", "age" : 14, "addr" : "WH", "sex" : 1, "code_name" : [ { "_id" : 1, "name" : "male" } ] }
{ "name" : "zhaoliu", "age" : 15, "addr" : "WH", "sex" : 1, "code_name" : [ { "_id" : 1, "name" : "male" } ] }
{ "name" : "qianqi", "age" : 16, "addr" : "BB", "sex" : 1, "code_name" : [ { "_id" : 1, "name" : "male" } ] }
{ "name" : "zhangba", "age" : 17, "addr" : "BB", "sex" : 1, "code_name" : [ { "_id" : 1, "name" : "male" } ] }
{ "name" : "Christy", "age" : 18, "addr" : "AH", "sex" : 1, "code_name" : [ { "_id" : 1, "name" : "male" } ] }
{ "name" : "Tide", "age" : 19, "addr" : "AH", "sex" : 1, "code_name" : [ { "_id" : 1, "name" : "male" } ] }
from:需要关联的表【code_collection】
localField: 【second_collection】表需要关联的键。
foreignField:【code_collection】的matching key 主键
as:对应的外键集合的数据,【因为是一对多的】