尚學堂MongoDB語法

小劉新鮮事兒發表於2020-12-06

插入資料

> db.dev.insertMany([
{"title":"北京尚學堂","description":"程式設計師的搖籃","url":"www.bjsxt.com","tags":["Java","大資料","Python"]},
{"title":"百戰程式設計師","description":"身經百戰，高薪相伴","url":"www.itbaizhan.cn","tags":["JavaWeb實戰","資料庫實戰","微服務實戰"]},
{"title":"尚學堂大資料","description":"培養大資料人才的搖籃","url":"www.bjsxt.com","tags":["Hadoop","Spark","Hbase"]},
{"title":"Java","description":"程式設計師的搖籃","url":"www.bjsxt.com","tags":["Java","大資料","Python"]},
{"title":"Java","tags":["JavaSE","JavaEE","JavaME"]},
{"title":"ORM","tags":["MyBatis","Hibernate"]},
{"title":"Spring","tags":["SpringMVC","Spring Data","Spring Cloud"]},
{"title":"Web","tags":["Jsp","Servlet"]},
{"title":"RPC","tags":["RMI","Dubbo"]},
{"title":"DataBase","tags":["Oracle","MySQL"]}
])

查詢dev集合中的所有記錄

> db.dev.find()
{ "_id" : ObjectId("5fc4e460c07669d59be84edc"), "title" : "北京尚學堂", "description" : "程式設計師的搖籃", "url" : "www.bjsxt.com", "tags" : [ "Java", "大資料", "Python" ] }
{ "_id" : ObjectId("5fc4e460c07669d59be84edd"), "title" : "百戰程式設計師", "description" : "身經百戰，高薪相伴", "url" : "www.itbaizhan.cn", "tags" : [ "JavaWeb實戰", "資料庫實戰", "微服務實戰" ] }
{ "_id" : ObjectId("5fc4e460c07669d59be84ede"), "title" : "尚學堂大資料", "description" : "培養大資料人才的搖籃", "url" : "www.bjsxt.com", "tags" : [ "Hadoop", "Spark", "Hbase" ] }
{ "_id" : ObjectId("5fc4e460c07669d59be84edf"), "title" : "Java", "description" : "程式設計師的搖籃", "url" : "www.bjsxt.com", "tags" : [ "Java", "大資料", "Python" ] }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee0"), "title" : "Java", "tags" : [ "JavaSE", "JavaEE", "JavaME" ] }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee1"), "title" : "ORM", "tags" : [ "MyBatis", "Hibernate" ] }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee2"), "title" : "Spring", "tags" : [ "SpringMVC", "Spring Data", "Spring Cloud" ] }
{ "_id" : ObjectId("5fc4e664c07669d59be84ee3"), "title" : "Web", "tags" : [ "Jsp", "Servlet" ] }
{ "_id" : ObjectId("5fc4e664c07669d59be84ee4"), "title" : "RPC", "tags" : [ "RMI", "Dubbo" ] }
{ "_id" : ObjectId("5fc4e664c07669d59be84ee5"), "title" : "DataBase", "tags" : [ "Oracle", "MySQL" ] }
> db.dev.find().pretty() #pretty()函式以格式化的方式來顯示文件
{
	"_id" : ObjectId("5fc4e460c07669d59be84edc"),
	"title" : "北京尚學堂",
	"description" : "程式設計師的搖籃",
	"url" : "www.bjsxt.com",
	"tags" : [
		"Java",
		"大資料",
		"Python"
	]
}
{
	"_id" : ObjectId("5fc4e460c07669d59be84edd"),
	"title" : "百戰程式設計師",
	"description" : "身經百戰，高薪相伴",
	"url" : "www.itbaizhan.cn",
	"tags" : [
		"JavaWeb實戰",
		"資料庫實戰",
		"微服務實戰"
	]
}
{
	"_id" : ObjectId("5fc4e460c07669d59be84ede"),
	"title" : "尚學堂大資料",
	"description" : "培養大資料人才的搖籃",
	"url" : "www.bjsxt.com",
	"tags" : [
		"Hadoop",
		"Spark",
		"Hbase"
	]
}
{
	"_id" : ObjectId("5fc4e460c07669d59be84edf"),
	"title" : "Java",
	"description" : "程式設計師的搖籃",
	"url" : "www.bjsxt.com",
	"tags" : [
		"Java",
		"大資料",
		"Python"
	]
}
{
	"_id" : ObjectId("5fc4e460c07669d59be84ee0"),
	"title" : "Java",
	"tags" : [
		"JavaSE",
		"JavaEE",
		"JavaME"
	]
}
{
	"_id" : ObjectId("5fc4e460c07669d59be84ee1"),
	"title" : "ORM",
	"tags" : [
		"MyBatis",
		"Hibernate"
	]
}
{
	"_id" : ObjectId("5fc4e460c07669d59be84ee2"),
	"title" : "Spring",
	"tags" : [
		"SpringMVC",
		"Spring Data",
		"Spring Cloud"
	]
}
{
	"_id" : ObjectId("5fc4e664c07669d59be84ee3"),
	"title" : "Web",
	"tags" : [
		"Jsp",
		"Servlet"
	]
}
{
	"_id" : ObjectId("5fc4e664c07669d59be84ee4"),
	"title" : "RPC",
	"tags" : [
		"RMI",
		"Dubbo"
	]
}
{
	"_id" : ObjectId("5fc4e664c07669d59be84ee5"),
	"title" : "DataBase",
	"tags" : [
		"Oracle",
		"MySQL"
	]
}

查詢title為DataBase的文件並格式化顯示

> db.dev.find({title:"DataBase"}).pretty()
{
	"_id" : ObjectId("5fc4e664c07669d59be84ee5"),
	"title" : "DataBase",
	"tags" : [
		"Oracle",
		"MySQL"
	]
}

findOne()函式只返回滿足條件的第一條資料。如果未作投影操作，該方法則自帶格式化功能

> db.dev.findOne()
{
	"_id" : ObjectId("5fc4e460c07669d59be84edc"),
	"title" : "北京尚學堂",
	"description" : "程式設計師的搖籃",
	"url" : "www.bjsxt.com",
	"tags" : [
		"Java",
		"大資料",
		"Python"
	]
}

> db.dev.insertMany([
... {title:"dev",desc:"test1"},
... {title:"dev",desc:"test2"},
... {title:"dev",desc:"test3"}
... ])
> db.dev.findOne({title:"dev"})
{
	"_id" : ObjectId("5fc4e9edc07669d59be84ee6"),
	"title" : "dev",
	"desc" : "test1"
}

1. 模糊查詢

查詢文件中title的值含有a的所有記錄

> db.dev.find({title:/a/})
{ "_id" : ObjectId("5fc4e460c07669d59be84edf"), "title" : "Java", "description" : "程式設計師的搖籃", "url" : "www.bjsxt.com", "tags" : [ "Java", "大資料", "Python" ] }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee0"), "title" : "Java", "tags" : [ "JavaSE", "JavaEE", "JavaME" ] }
{ "_id" : ObjectId("5fc4e664c07669d59be84ee5"), "title" : "DataBase", "tags" : [ "Oracle", "MySQL" ] }

使用^表示查詢內容的起始位置
查詢文件中title的值以S開頭的所有內容

> db.dev.find({title:/^S/})
{ "_id" : ObjectId("5fc4e460c07669d59be84ee2"), "title" : "Spring", "tags" : [ "SpringMVC", "Spring Data", "Spring Cloud" ] }

使用$表示結尾
查詢文件title的值以g結尾的所有內容

> db.dev.find({title:/g$/})
{ "_id" : ObjectId("5fc4e460c07669d59be84ee2"), "title" : "Spring", "tags" : [ "SpringMVC", "Spring Data", "Spring Cloud" ] }

2. 投影操作

查詢dev集合中所有記錄的title

> db.dev.find({},{title:1})
{ "_id" : ObjectId("5fc4e460c07669d59be84edc"), "title" : "北京尚學堂" }
{ "_id" : ObjectId("5fc4e460c07669d59be84edd"), "title" : "百戰程式設計師" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ede"), "title" : "尚學堂大資料" }
{ "_id" : ObjectId("5fc4e460c07669d59be84edf"), "title" : "Java" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee0"), "title" : "Java" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee1"), "title" : "ORM" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee2"), "title" : "Spring" }
{ "_id" : ObjectId("5fc4e664c07669d59be84ee3"), "title" : "Web" }
{ "_id" : ObjectId("5fc4e664c07669d59be84ee4"), "title" : "RPC" }
{ "_id" : ObjectId("5fc4e664c07669d59be84ee5"), "title" : "DataBase" }
{ "_id" : ObjectId("5fc4e9edc07669d59be84ee6"), "title" : "dev" }
{ "_id" : ObjectId("5fc4e9edc07669d59be84ee7"), "title" : "dev" }
{ "_id" : ObjectId("5fc4e9edc07669d59be84ee8"), "title" : "dev" }

查詢dev集合中所有記錄的title，不顯示_id

> db.dev.find({},{title:1,_id:0})
{ "title" : "北京尚學堂" }
{ "title" : "百戰程式設計師" }
{ "title" : "尚學堂大資料" }
{ "title" : "Java" }
{ "title" : "Java" }
{ "title" : "ORM" }
{ "title" : "Spring" }
{ "title" : "Web" }
{ "title" : "RPC" }
{ "title" : "DataBase" }
{ "title" : "dev" }
{ "title" : "dev" }
{ "title" : "dev" }

查詢title的值為dev的第一條記錄，這條記錄只顯示desc欄位，不顯示_id
可以看到，findOne()做投影以後，就不再格式化了

> db.dev.findOne({title:"dev"},{desc:1,_id:0})
{ "desc" : "test1" }

3. 條件操作符

語法格式：
find({鍵:{操作符:條件}})
或者
findOne({鍵:{操作符:條件}})

大於操作符$gt，該操作符可以對數字或日期進行判斷

> db.dev.insertMany([
... {title:"test1",size:100},
... {title:"test2",size:200},
... {title:"test3",size:300},
... {title:"test4",size:400},
... {title:"test5",size:500}
... ])

查詢size大於300的文章的所有記錄

> db.dev.find({size:{$gt:300}})
{ "_id" : ObjectId("5fc4f11ec07669d59be84eec"), "title" : "test4", "size" : 400 }
{ "_id" : ObjectId("5fc4f11ec07669d59be84eed"), "title" : "test5", "size" : 500 }

小於操作符$lt
查詢size小於300的文章的所有記錄

> db.dev.find({size:{$lt:300}})
{ "_id" : ObjectId("5fc4f11ec07669d59be84ee9"), "title" : "test1", "size" : 100 }
{ "_id" : ObjectId("5fc4f11ec07669d59be84eea"), "title" : "test2", "size" : 200 }

等於操作符$eq

不等於操作符$ne

查詢size等於300的文件記錄

> db.dev.find({size:{$eq:300}})
{ "_id" : ObjectId("5fc4f11ec07669d59be84eeb"), "title" : "test3", "size" : 300 }

$and操作符表示多條件間的並且關係

語法格式：find({$and:[{條件1},{條件2},…]})

# 再插入一條資料
> db.dev.insert({title:"test6",size:500})

如果在查詢中給定了多個查詢條件，條件之間的關係預設是and關係

查詢size大於100並且小於300的記錄

> db.dev.find({size:{$gt:100,$lt:300}})
{ "_id" : ObjectId("5fc4f11ec07669d59be84eea"), "title" : "test2", "size" : 200 }

查詢size大於100並且小於300的記錄，使用$and指定多條件關係

> db.dev.find({$and:[{size:{$gt:100}},{size:{$lt:300}}]})
{ "_id" : ObjectId("5fc4f11ec07669d59be84eea"), "title" : "test2", "size" : 200 }

$or表示多條件間的或者關係

語法格式：find({$or:[{條件1},{條件2},…]})

查詢title的值為test2或者size的值大於300的文件

> db.dev.find({$or:[{title:{$eq:"test2"}},{size:{$gt:300}}]})
{ "_id" : ObjectId("5fc4f11ec07669d59be84eea"), "title" : "test2", "size" : 200 }
{ "_id" : ObjectId("5fc4f11ec07669d59be84eec"), "title" : "test4", "size" : 400 }
{ "_id" : ObjectId("5fc4f11ec07669d59be84eed"), "title" : "test5", "size" : 500 }
{ "_id" : ObjectId("5fc4f40fc07669d59be84eee"), "title" : "test6", "size" : 500 }

條件操作 $and 與 $or 聯合使用

查詢title為test5並且size等於500，或者size小於400的文件

> db.dev.find({$or:[{$and:[{title:{$eq:"test5"}},{size:500}]},{size:{$lt:400}}]})
{ "_id" : ObjectId("5fc4f11ec07669d59be84ee9"), "title" : "test1", "size" : 100 }
{ "_id" : ObjectId("5fc4f11ec07669d59be84eea"), "title" : "test2", "size" : 200 }
{ "_id" : ObjectId("5fc4f11ec07669d59be84eeb"), "title" : "test3", "size" : 300 }
{ "_id" : ObjectId("5fc4f11ec07669d59be84eed"), "title" : "test5", "size" : 500 }

$type 操作符是基於BSON型別來檢索集合中匹配的資料型別，並返回結果

# 插入一條記錄
> db.dev.insert({title:200,size:400})

> typeof 123
number
> typeof "xiaoliu"
string

查詢title的值的型別為number的記錄

> db.dev.find({title:{$type:"number"}})
{ "_id" : ObjectId("5fc77a105a5617876fcd23c8"), "title" : 200, "size" : 400 }

limit()函式讀取指定數量的資料記錄

查詢前兩條記錄的title

> db.dev.find({},{title:1}).limit(2)
{ "_id" : ObjectId("5fc4e460c07669d59be84edc"), "title" : "北京尚學堂" }
{ "_id" : ObjectId("5fc4e460c07669d59be84edd"), "title" : "百戰程式設計師" }

跳過第1條，取5條

> db.dev.find({},{title:1}).skip(1).limit(5)
{ "_id" : ObjectId("5fc4e460c07669d59be84edd"), "title" : "百戰程式設計師" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ede"), "title" : "尚學堂大資料" }
{ "_id" : ObjectId("5fc4e460c07669d59be84edf"), "title" : "Java" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee0"), "title" : "Java" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee1"), "title" : "ORM" }

4. MongoDB排序

sort()函式對查詢到的文件進行排序，可以通過引數指定排序的欄位。並使用1和-1來指定排序的方式
語法格式：db.集合名.find().sort({排序鍵:1})

查詢size的值為number型別的文件，顯示title，size的內容，並對size做升序排序

> db.dev.find({size:{$type:"number"}},{title:1,size:1,_id:0}).sort({size:1})
{ "title" : "test1", "size" : 100 }
{ "title" : "test2", "size" : 200 }
{ "title" : "test3", "size" : 300 }
{ "title" : "test4", "size" : 400 }
{ "title" : 200, "size" : 400 }
{ "title" : "test5", "size" : 500 }
{ "title" : "test6", "size" : 500 }

查詢size的值為number型別的文件，顯示title，size的內容，並對size做降序排序

> db.dev.find({size:{$type:"number"}},{title:1,size:1,_id:0}).sort({size:-1})
{ "title" : "test5", "size" : 500 }
{ "title" : "test6", "size" : 500 }
{ "title" : "test4", "size" : 400 }
{ "title" : 200, "size" : 400 }
{ "title" : "test3", "size" : 300 }
{ "title" : "test2", "size" : 200 }
{ "title" : "test1", "size" : 100 }

5. 索引

在MongoDB中會自動為文件中的_id（文件的主鍵）鍵建立索引，與關係型資料的主鍵索引類似

可以使用createIndex()函式來為其他的鍵建立索引。在建立索引時需要指定排序規則

語法格式：db.集合名.createIndex({建立索引的鍵:排序規則,…},{建立索引的引數(可選引數)})

為dev集合中的title鍵建立索引，並讓建立工作在後臺執行

> db.dev.createIndex({title:1},{background:true})
{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1
}

檢視索引

可以通過getIndexes()或者getIndexSpecs()函式檢視集合中的所有索引資訊

語法格式：db.集合名.getIndexes()
語法格式：db.集合名.getIndexSpecs()

使用getIndexes()函式檢視當前dev集合中的索引

> db.dev.getIndexes()
[
	{
		"v" : 2,
		"key" : {
			"_id" : 1
		},
		"name" : "_id_",
		"ns" : "bjsxt.dev"
	},
	{
		"v" : 2,
		"key" : {
			"title" : 1
		},
		"name" : "title_1",
		"ns" : "bjsxt.dev",
		"background" : true
	}
]
> db.dev.getIndexSpecs()
[
	{
		"v" : 2,
		"key" : {
			"_id" : 1
		},
		"name" : "_id_",
		"ns" : "bjsxt.dev"
	},
	{
		"v" : 2,
		"key" : {
			"title" : 1
		},
		"name" : "title_1",
		"ns" : "bjsxt.dev",
		"background" : true
	}
]

我們只為title建立了索引，為什麼返回了兩條資訊呢？
因為第一條是_id，MongoDB會自動為_id鍵建立索引在這裡插入圖片描述

可以通過使用getIndexKeys()函式檢視集合的索引鍵

語法格式：db.集合名.getIndexKeys()

檢視dev集合中的索引鍵

> db.dev.getIndexKeys()
[ { "_id" : 1 }, { "title" : 1 } ]

可以通過totalIndexSize()函式來檢視當前集合中索引的大小，單位為位元組

語法格式：db.集合名.totalIndexSize( [detail] (可選引數))

引數解釋：detail可選引數，如果傳入除0或false外的任意資料，那麼會顯示該集合中每個索引的大小及集合中索引的總大小。如果傳入0或false則只顯示該集合中所有索引的總大小。預設值false。

檢視dev集合中所有索引的總大小

> db.dev.totalIndexSize()
53248

> db.dev.totalIndexSize([1])
_id_	36864
title_1	16384
53248

刪除索引

可以通過dropIndex()函式來刪除指定索引
語法格式db.集合名.dropIndex(“索引名稱”)

刪除title鍵的索引

> db.dev.dropIndex("title_1")
{ "nIndexesWas" : 2, "ok" : 1 }

> db.dev.getIndexes()
[
	{
		"v" : 2,
		"key" : {
			"_id" : 1
		},
		"name" : "_id_",
		"ns" : "bjsxt.dev"
	}
]

可以使用dropIndexes()函式刪除集合中的全部索引，_id鍵的索引除外

語法格式：db.集合名.dropIndexes()

> db.dev.dropIndexes()
{
	"nIndexesWas" : 1,
	"msg" : "non-_id indexes dropped for collection",
	"ok" : 1
}

> db.dev.getIndexes()
[
	{
		"v" : 2,
		"key" : {
			"_id" : 1
		},
		"name" : "_id_",
		"ns" : "bjsxt.dev"
	}
]

5.1 單欄位索引

單欄位索引是指在索引中只包含了一個鍵。查詢時，可加速對該欄位的各種查詢請求，是最常見的索引形式。MongoDB預設建立的_id索引也是這種型別。我們可以使用createIndexes({索引鍵:排序規則})函式來建立單欄位索引。

語法格式：db.集合名.createIndexes({索引鍵名:排序規則})

為dev集合中的title鍵建立單欄位索引

> db.dev.createIndex({title:1},{background:true})
{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1
}

5.2 交叉索引

交叉索引就是為一個集合的多個欄位分別建立索引，在查詢的時候通過多個欄位作為查詢條件，這種情況稱為交叉索引

在查詢文件時，在查詢條件中包含一個交叉索引鍵或者在一次查詢中使用多個交叉索引鍵作為查詢條件都會觸發交叉索引

為dev集合中的size鍵建立交叉索引

> db.dev.createIndex({size:1})
{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 2,
	"numIndexesAfter" : 3,
	"ok" : 1
}

> db.dev.getIndexes()
[
	{
		"v" : 2,
		"key" : {
			"_id" : 1
		},
		"name" : "_id_",
		"ns" : "bjsxt.dev"
	},
	{
		"v" : 2,
		"key" : {
			"title" : 1
		},
		"name" : "title_1",
		"ns" : "bjsxt.dev",
		"background" : true
	},
	{
		"v" : 2,
		"key" : {
			"size" : 1
		},
		"name" : "size_1",
		"ns" : "bjsxt.dev"
	}
]

title索引和size索引就是一個典型的交叉索引

5.3 複合索引

複合索引針對多個欄位聯合建立索引，先按第一個欄位排序，第一個欄位相同的文件按第二個欄位排序，以此類推

語法格式：db.集合名.createIndex({索引鍵名:排序規則,索引鍵名:排序規則,…})

刪除dev中的交叉索引

> db.dev.dropIndexes()
{
	"nIndexesWas" : 3,
	"msg" : "non-_id indexes dropped for collection",
	"ok" : 1
}

建立title與size的複合索引

> db.dev.createIndex({title:1,size:1})
{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1
}

5.4 多key索引

當索引的欄位為陣列時，建立處的索引為多key索引，多key索引會為陣列的每個元素建立一條索引。

語法格式：db.集合名.createIndex({陣列鍵名:排序規則})

為dev集合中tags鍵建立多key索引

> db.dev.createIndex({tags:1})
{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 2,
	"numIndexesAfter" : 3,
	"ok" : 1
}

> db.dev.getIndexes()
[
	{
		"v" : 2,
		"key" : {
			"_id" : 1
		},
		"name" : "_id_",
		"ns" : "bjsxt.dev"
	},
	{
		"v" : 2,
		"key" : {
			"title" : 1,
			"size" : 1
		},
		"name" : "title_1_size_1",
		"ns" : "bjsxt.dev"
	},
	{
		"v" : 2,
		"key" : {
			"tags" : 1
		},
		"name" : "tags_1",
		"ns" : "bjsxt.dev"
	}
]

5.5 唯一索引

唯一索引會保證索引對應的鍵不會出現相同的值，_id索引就是唯一索引

語法格式：db.集合名.createIndex({索引鍵名:排序規則},{unique:true})

如果唯一索引所在欄位有重複資料寫入時，丟擲異常

刪除dev集合中的索引，為dev集合中的title鍵建立唯一索引

#刪除索引
> db.dev.dropIndexes()
{
	"nIndexesWas" : 3,
	"msg" : "non-_id indexes dropped for collection",
	"ok" : 1
}

#刪除title欄位的重複值的記錄
> db.dev.deleteMany({$or:[{title:"dev"},{title:"Java"}]})

# 為dev集合中的title鍵建立唯一索引
> db.dev.createIndex({title:1},{background:true,unique:true})
{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1
}

#因為title欄位中已經有test1了，再插入test1會丟擲異常
> db.dev.insert({title:"test1"})
WriteResult({
	"nInserted" : 0,
	"writeError" : {
		"code" : 11000,
		"errmsg" : "E11000 duplicate key error collection: bjsxt.dev index: title_1 dup key: { : \"test1\" }"
	}
})

5.6 部分索引

部分索引是隻針對符合某個特定條件的文件建立索引

語法格式：db.集合名.createIndex({索引鍵名:排序規則},{partialFilterExpression:{鍵名:{匹配條件:條件值}}})

為dev集合中的size鍵建立部分索引，條件為大於300

> db.dev.createIndex({size:1},{partialFilterExpression:{size:{$gt:300}}})
{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 2,
	"numIndexesAfter" : 3,
	"ok" : 1
}
> db.dev.getIndexes()
[
	{
		"v" : 2,
		"key" : {
			"_id" : 1
		},
		"name" : "_id_",
		"ns" : "bjsxt.dev"
	},
	{
		"v" : 2,
		"unique" : true,
		"key" : {
			"title" : 1
		},
		"name" : "title_1",
		"ns" : "bjsxt.dev",
		"background" : true
	},
	{
		"v" : 2,
		"key" : {
			"size" : 1
		},
		"name" : "size_1",
		"ns" : "bjsxt.dev",
		"partialFilterExpression" : {
			"size" : {
				"$gt" : 300
			}
		}
	}
]

建立一個索引，這個索引既指定了部分索引，同時也給定了唯一約束索引。
對於一個這樣的索引而言，唯一索引只對什麼樣的索引生效呢?
只對滿足了查詢條件的內容生效。也就是說，在滿足了查詢條件的資料當中是不允許有重複的，但是對於那些不滿足這些條件的資料，唯一性索引是不生效的。也就是說，小於300的資料是允許有重複的，大於300的資料是不允許有重複的

6. 聚合查詢

在MongoDB中可以通過aggregate()函式來完成一些聚合查詢，aggregate()函式主要用於處理諸如統計，平均值，求和等，並返回計算後的資料結果。

語法格式：db.集合名.aggregate([{$group:{_id:"$分組鍵名","$分組鍵名",…,別名:{聚合運算:"$運算列"}}},{條件篩選:{鍵名:{運算條件:運算類}}}])

常見的mongo的聚合操作與mysql的查詢做類比

SQL操作	mongodb聚合操作
where	$match
group by	$group
having	$match
select	$project
order by	$sort
limit	$limit
sum()	$sum
count()	$sum
join	$lookup

6.1 $sum

查詢dev集合中一共有多少個文件

> db.dev.aggregate([{$group:{_id:null,count:{$sum:1}}}])
{ "_id" : null, "count" : 15 }

sql：select count(*) as count from dev
mongodb: db.dev.aggregate([{$group:{_id:null,count:{$sum:1}}}])

$group：分組，代表聚合的分組條件
_id：分組的欄位。相當於SQL分組語法group by column_name中的column_name部分。如果根據某欄位的值分組，則定義為_id:"$欄位名"

查詢dev集合中的所有size鍵中的值的總和

> db.dev.aggregate([{$group:{_id:null,totalSize:{$sum:"$size"}}}])
{ "_id" : null, "totalSize" : 2400 }

對每一個title進行分組並計算每組中的size的總和

> db.dev.aggregate([{$group:{_id:"$title",countNum:{$sum:"$size"}}}])
{ "_id" : "test6", "countNum" : 500 }
{ "_id" : "test5", "countNum" : 500 }
{ "_id" : 200, "countNum" : 400 }
{ "_id" : "test4", "countNum" : 400 }
{ "_id" : "test3", "countNum" : 300 }
{ "_id" : "ORM", "countNum" : 0 }
{ "_id" : "尚學堂大資料", "countNum" : 0 }
{ "_id" : "Spring", "countNum" : 0 }
{ "_id" : "百戰程式設計師", "countNum" : 0 }
{ "_id" : "北京尚學堂", "countNum" : 0 }
{ "_id" : "Web", "countNum" : 0 }
{ "_id" : "RPC", "countNum" : 0 }
{ "_id" : "test1", "countNum" : 100 }
{ "_id" : "DataBase", "countNum" : 0 }
{ "_id" : "test2", "countNum" : 200 }

6.2 條件篩選 - $match

查詢dev集合有多少文件的size大於200

> db.dev.aggregate([{$match:{size:{$gt:200}}},{$group:{_id:null,totalSize:{$sum:1}}}])
{ "_id" : null, "totalSize" : 5 }

查詢dev集合，根據title分組計算出每組的size的總和，並過濾掉總和小於等於200的文件

> db.dev.aggregate([{$group:{_id:"$title",totalSize:{$sum:"$size"}}},{$match:{totalSize:{$gt:200}}}])
{ "_id" : "test6", "totalSize" : 500 }
{ "_id" : "test5", "totalSize" : 500 }
{ "_id" : 200, "totalSize" : 400 }
{ "_id" : "test4", "totalSize" : 400 }
{ "_id" : "test3", "totalSize" : 300 }

6.3 $max

查詢dev集合中的最大size的值

> db.dev.aggregate([{$group:{_id:null,maxSize:{$max:"$size"}}}])
{ "_id" : null, "maxSize" : 500 }

6.4 $min

查詢dev集合中的最小size的值

> db.dev.aggregate([{$group:{_id:null,minSize:{$min:"$size"}}}])
{ "_id" : null, "minSize" : 100 }

6.5 $avg

查詢dev集合中size的平均值

> db.dev.aggregate([{$group:{_id:null,avgSize:{$avg:"$size"}}}])
{ "_id" : null, "avgSize" : 342.85714285714283 }

6.6 統計結果返回陣列 $push

查詢dev集合，按照size分組並返回他們的title，如果size相同則使用陣列返回他們的title

> db.dev.aggregate([{$group:{_id:"$size",titleArray:{$push:"$title"}}}])
{ "_id" : 500, "titleArray" : [ "test5", "test6" ] }
{ "_id" : 400, "titleArray" : [ "test4", 200 ] }
{ "_id" : null, "titleArray" : [ "北京尚學堂", "百戰程式設計師", "尚學堂大資料", "ORM", "Spring", "Web", "RPC", "DataBase" ] }
{ "_id" : 100, "titleArray" : [ "test1" ] }
{ "_id" : 200, "titleArray" : [ "test2" ] }
{ "_id" : 300, "titleArray" : [ "test3" ] }

6.7 陣列欄位拆分 $unwind

查詢dev集合，將陣列中的內容拆分顯示

> db.dev.aggregate([{$unwind:"$tags"}])
{ "_id" : ObjectId("5fc4e460c07669d59be84edc"), "title" : "北京尚學堂", "description" : "程式設計師的搖籃", "url" : "www.bjsxt.com", "tags" : "Java" }
{ "_id" : ObjectId("5fc4e460c07669d59be84edc"), "title" : "北京尚學堂", "description" : "程式設計師的搖籃", "url" : "www.bjsxt.com", "tags" : "大資料" }
{ "_id" : ObjectId("5fc4e460c07669d59be84edc"), "title" : "北京尚學堂", "description" : "程式設計師的搖籃", "url" : "www.bjsxt.com", "tags" : "Python" }
{ "_id" : ObjectId("5fc4e460c07669d59be84edd"), "title" : "百戰程式設計師", "description" : "身經百戰，高薪相伴", "url" : "www.itbaizhan.cn", "tags" : "JavaWeb實戰" }
{ "_id" : ObjectId("5fc4e460c07669d59be84edd"), "title" : "百戰程式設計師", "description" : "身經百戰，高薪相伴", "url" : "www.itbaizhan.cn", "tags" : "資料庫實戰" }
{ "_id" : ObjectId("5fc4e460c07669d59be84edd"), "title" : "百戰程式設計師", "description" : "身經百戰，高薪相伴", "url" : "www.itbaizhan.cn", "tags" : "微服務實戰" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ede"), "title" : "尚學堂大資料", "description" : "培養大資料人才的搖籃", "url" : "www.bjsxt.com", "tags" : "Hadoop" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ede"), "title" : "尚學堂大資料", "description" : "培養大資料人才的搖籃", "url" : "www.bjsxt.com", "tags" : "Spark" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ede"), "title" : "尚學堂大資料", "description" : "培養大資料人才的搖籃", "url" : "www.bjsxt.com", "tags" : "Hbase" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee1"), "title" : "ORM", "tags" : "MyBatis" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee1"), "title" : "ORM", "tags" : "Hibernate" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee2"), "title" : "Spring", "tags" : "SpringMVC" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee2"), "title" : "Spring", "tags" : "Spring Data" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee2"), "title" : "Spring", "tags" : "Spring Cloud" }
{ "_id" : ObjectId("5fc4e664c07669d59be84ee3"), "title" : "Web", "tags" : "Jsp" }
{ "_id" : ObjectId("5fc4e664c07669d59be84ee3"), "title" : "Web", "tags" : "Servlet" }
{ "_id" : ObjectId("5fc4e664c07669d59be84ee4"), "title" : "RPC", "tags" : "RMI" }
{ "_id" : ObjectId("5fc4e664c07669d59be84ee4"), "title" : "RPC", "tags" : "Dubbo" }
{ "_id" : ObjectId("5fc4e664c07669d59be84ee5"), "title" : "DataBase", "tags" : "Oracle" }
{ "_id" : ObjectId("5fc4e664c07669d59be84ee5"), "title" : "DataBase", "tags" : "MySQL" }

如圖所示：
在這裡插入圖片描述

7.1 聚合投影約束

查詢dev集合，將陣列中的內容拆分顯示，並只顯示title鍵與tags鍵的值

> db.dev.aggregate([{$unwind:"$tags"},{$project:{_id:0,tags:"$tags",title:"$title"}}])
{ "tags" : "Java", "title" : "北京尚學堂" }
{ "tags" : "大資料", "title" : "北京尚學堂" }
{ "tags" : "Python", "title" : "北京尚學堂" }
{ "tags" : "JavaWeb實戰", "title" : "百戰程式設計師" }
{ "tags" : "資料庫實戰", "title" : "百戰程式設計師" }
{ "tags" : "微服務實戰", "title" : "百戰程式設計師" }
{ "tags" : "Hadoop", "title" : "尚學堂大資料" }
{ "tags" : "Spark", "title" : "尚學堂大資料" }
{ "tags" : "Hbase", "title" : "尚學堂大資料" }
{ "tags" : "MyBatis", "title" : "ORM" }
{ "tags" : "Hibernate", "title" : "ORM" }
{ "tags" : "SpringMVC", "title" : "Spring" }
{ "tags" : "Spring Data", "title" : "Spring" }
{ "tags" : "Spring Cloud", "title" : "Spring" }
{ "tags" : "Jsp", "title" : "Web" }
{ "tags" : "Servlet", "title" : "Web" }
{ "tags" : "RMI", "title" : "RPC" }
{ "tags" : "Dubbo", "title" : "RPC" }
{ "tags" : "Oracle", "title" : "DataBase" }
{ "tags" : "MySQL", "title" : "DataBase" }

查詢dev集合，將陣列中的內容拆分顯示，並只顯示title鍵與tags鍵的值，並將title鍵修改為Title

> db.dev.aggregate([{$unwind:"$tags"},{$project:{_id:0,Title:"$title",tags:"$tags"}}])
{ "Title" : "北京尚學堂", "tags" : "Java" }
{ "Title" : "北京尚學堂", "tags" : "大資料" }
{ "Title" : "北京尚學堂", "tags" : "Python" }
{ "Title" : "百戰程式設計師", "tags" : "JavaWeb實戰" }
{ "Title" : "百戰程式設計師", "tags" : "資料庫實戰" }
{ "Title" : "百戰程式設計師", "tags" : "微服務實戰" }
{ "Title" : "尚學堂大資料", "tags" : "Hadoop" }
{ "Title" : "尚學堂大資料", "tags" : "Spark" }
{ "Title" : "尚學堂大資料", "tags" : "Hbase" }
{ "Title" : "ORM", "tags" : "MyBatis" }
{ "Title" : "ORM", "tags" : "Hibernate" }
{ "Title" : "Spring", "tags" : "SpringMVC" }
{ "Title" : "Spring", "tags" : "Spring Data" }
{ "Title" : "Spring", "tags" : "Spring Cloud" }
{ "Title" : "Web", "tags" : "Jsp" }
{ "Title" : "Web", "tags" : "Servlet" }
{ "Title" : "RPC", "tags" : "RMI" }
{ "Title" : "RPC", "tags" : "Dubbo" }
{ "Title" : "DataBase", "tags" : "Oracle" }
{ "Title" : "DataBase", "tags" : "MySQL" }

7.2 字串處理

查詢dev集合，將陣列中的內容拆分顯示。將title中的值轉換為小寫並命名為New_Title，將tags的值轉換為大寫並命名為New_Tags

> db.dev.aggregate([{$unwind:"$tags"},{$project:{_id:0,New_Title:{$toLower:"$title"},New_Tags:{$toUpper:"$tags"}}}])
{ "New_Title" : "北京尚學堂", "New_Tags" : "JAVA" }
{ "New_Title" : "北京尚學堂", "New_Tags" : "大資料" }
{ "New_Title" : "北京尚學堂", "New_Tags" : "PYTHON" }
{ "New_Title" : "百戰程式設計師", "New_Tags" : "JAVAWEB實戰" }
{ "New_Title" : "百戰程式設計師", "New_Tags" : "資料庫實戰" }
{ "New_Title" : "百戰程式設計師", "New_Tags" : "微服務實戰" }
{ "New_Title" : "尚學堂大資料", "New_Tags" : "HADOOP" }
{ "New_Title" : "尚學堂大資料", "New_Tags" : "SPARK" }
{ "New_Title" : "尚學堂大資料", "New_Tags" : "HBASE" }
{ "New_Title" : "orm", "New_Tags" : "MYBATIS" }
{ "New_Title" : "orm", "New_Tags" : "HIBERNATE" }
{ "New_Title" : "spring", "New_Tags" : "SPRINGMVC" }
{ "New_Title" : "spring", "New_Tags" : "SPRING DATA" }
{ "New_Title" : "spring", "New_Tags" : "SPRING CLOUD" }
{ "New_Title" : "web", "New_Tags" : "JSP" }
{ "New_Title" : "web", "New_Tags" : "SERVLET" }
{ "New_Title" : "rpc", "New_Tags" : "RMI" }
{ "New_Title" : "rpc", "New_Tags" : "DUBBO" }
{ "New_Title" : "database", "New_Tags" : "ORACLE" }
{ "New_Title" : "database", "New_Tags" : "MYSQL" }

查詢dev集合，將陣列中的內容拆分顯示。將title欄位和tags欄位的值拼接為一個完整字串並在Title_Tags欄位中顯示

> db.dev.aggregate([{$unwind:"$tags"},{$project:{_id:0,Title_Tags:{$concat:["$title","-","$tags"]}}}])
{ "Title_Tags" : "北京尚學堂-Java" }
{ "Title_Tags" : "北京尚學堂-大資料" }
{ "Title_Tags" : "北京尚學堂-Python" }
{ "Title_Tags" : "百戰程式設計師-JavaWeb實戰" }
{ "Title_Tags" : "百戰程式設計師-資料庫實戰" }
{ "Title_Tags" : "百戰程式設計師-微服務實戰" }
{ "Title_Tags" : "尚學堂大資料-Hadoop" }
{ "Title_Tags" : "尚學堂大資料-Spark" }
{ "Title_Tags" : "尚學堂大資料-Hbase" }
{ "Title_Tags" : "ORM-MyBatis" }
{ "Title_Tags" : "ORM-Hibernate" }
{ "Title_Tags" : "Spring-SpringMVC" }
{ "Title_Tags" : "Spring-Spring Data" }
{ "Title_Tags" : "Spring-Spring Cloud" }
{ "Title_Tags" : "Web-Jsp" }
{ "Title_Tags" : "Web-Servlet" }
{ "Title_Tags" : "RPC-RMI" }
{ "Title_Tags" : "RPC-Dubbo" }
{ "Title_Tags" : "DataBase-Oracle" }
{ "Title_Tags" : "DataBase-MySQL" }

mongoDB基本語法
2017-10-27
MongoDB
mongoDB的常用語法
2016-10-27
MongoDB
MongoDB 常用查詢語法
2019-10-09
MongoDB
【視訊分享】尚學堂---資料結構和演算法
2018-11-29
資料結構演算法
尚學堂的一個用了ExtJs的頁面程式碼
2013-12-12
JS
MongoDB入門簡介|MongoDB下載安裝|MongoDB語法
2011-11-10
MongoDB
德語語法快速學
2012-11-10
MongoDB語法與現有關係型資料庫SQL語法比較
2016-08-14
MongoDB資料庫SQL
HTML語法教學
2006-04-26
HTML
MarkDown語法學習
2024-04-12
Flutter Dart語法學習
2020-07-22
FlutterDart
tensorflow語法學習
2020-04-06
Scala基本語法學習
2018-07-21
Presto 與 Hive 語法學習
2022-04-16
RESTHive
SQL學習___03：DML語法
2020-12-11
SQL
學習Rust 基礎語法
2020-12-03
Rust
PHP基本語法學習 [常量]
2020-10-04
PHP
PHP基本語法學習常量
2020-10-04
PHP
redis學習(八) Lua語法
2018-03-08
Redis
Clojure語法學習-迴圈
2015-01-07
markdown學習（基礎語法）
2024-04-11
MongoDB常用語句
2017-06-02
MongoDB
課堂作業--黑客語解密
2020-10-30
黑客解密
小白學習Golang（三）Go語言基本語法
2020-09-19
Golang
易學堂線上教學系統
2019-05-11
小學課堂教學改革思路
2016-06-05
Flutter學習之Dart語法(二)
2020-03-07
FlutterDart
Flutter學習之Dart語法(一)
2020-02-29
FlutterDart
Flutter學習之Dart語法(三)
2020-03-29
FlutterDart
1分鐘學會Markdown語法
2020-04-07
Flutter學習之Dart語法特性
2019-03-13
FlutterDart
一文學完makefile語法
2021-05-24
一文學會Dockerfile語法
2021-02-22
Docker
學習ES6新語法
2018-08-11
ES模組語法學習小記
2018-08-08
Java學習之基礎語法
2020-12-30
Java
PHP基本語法學習 [變數]
2020-10-07
PHP變數
SASS&Compass語法學習
2016-08-23