1.表與表的三種關(guān)系
一對一、一對多、多對多
| 管道操作符 | 介紹 |
|---|---|
$project |
增加、刪除、重命名 |
$match |
條件匹配，滿足條件進入下一階段 |
$limit |
限制結(jié)果的數(shù)量 |
$skip |
跳過文檔的數(shù)量 |
$sort |
條件排序 |
$group |
條件組合結(jié)果 |
$lookup |
引入其他集合數(shù)據(jù) |
2. aggregate聚合管道
聚合管道可以對集合中的問那個進行變換和組合
以下例子的表全部如下：
> db.order.find()
{ "_id" : ObjectId("5f81817f2501fd0ea0437220"), "order_id" : "1", "uid" : 10, "trade_no" : "111", "all_price" : 100, "all_num" : 2 }
{ "_id" : ObjectId("5f81818a2501fd0ea0437221"), "order_id" : "2", "uid" : 7, "trade_no" : "222", "all_price" : 90, "all_num" : 2 }
{ "_id" : ObjectId("5f8181932501fd0ea0437222"), "order_id" : "3", "uid" : 9, "trade_no" : "333", "all_price" : 20, "all_num" : 6 }
> db.order_item.find()
{ "_id" : ObjectId("5f81829c2501fd0ea0437226"), "order_id" : "1", "title" : "商品鼠標(biāo) 1", "price" : 50, "num" : 1 }
{ "_id" : ObjectId("5f81829c2501fd0ea0437227"), "order_id" : "1", "title" : "商品鍵盤 2", "price" : 50, "num" : 1 }
{ "_id" : ObjectId("5f81829e2501fd0ea0437228"), "order_id" : "1", "title" : "商品鍵盤 3", "price" : 0, "num" : 1 }
{ "_id" : ObjectId("5f8182cf2501fd0ea0437229"), "order_id" : "2", "title" : "牛奶", "price" : 50, "num" : 1 }
{ "_id" : ObjectId("5f8182d02501fd0ea043722a"), "order_id" : "2", "title" : "酸奶", "price" : 40, "num" : 1 }
{ "_id" : ObjectId("5f8182e42501fd0ea043722b"), "order_id" : "3", "title" : "礦泉水", "price" : 2, "num" : 5 }
{ "_id" : ObjectId("5f8182e52501fd0ea043722c"), "order_id" : "3", "title" : "毛巾", "price" : 10, "num" : 1 }
- $project
修改文檔的結(jié)構(gòu)，可以用來重命名、增加或刪除文檔中的字段
//只顯示order_id、trade_no、all_price
db.order.aggregate([
{
$project: { order_id: 1, trade_no: 1, all_price: 1 }
}
])
//輸出
{ "_id" : ObjectId("5f8182692501fd0ea0437223"), "order_id" : "1", "trade_no" : "111", "all_price" : 100 }
{ "_id" : ObjectId("5f81826f2501fd0ea0437224"), "order_id" : "2", "trade_no" : "222", "all_price" : 90 }
{ "_id" : ObjectId("5f8182732501fd0ea0437225"), "order_id" : "3", "trade_no" : "333", "all_price" : 20 }
- $match
用法類似于 find() 方法中的參數(shù)。
//只顯示trade_no、all_price，且>=90的數(shù)據(jù)
db.order.aggregate([
{
$project: { trade_no: 1, all_price: 1 }
},
{
$match: { "all_price": { $gte: 90 } }
}
])
//輸出
{ "_id" : ObjectId("5f8182692501fd0ea0437223"), "trade_no" : "111", "all_price" : 100 }
{ "_id" : ObjectId("5f81826f2501fd0ea0437224"), "trade_no" : "222", "all_price" : 90 }
- $group
將集合中的文檔進行分組，可用于統(tǒng)計結(jié)果
//假設(shè)id相同統(tǒng)計他的價格
db.order_item.aggregate(
[{
$group: { _id: "$order_id", total: { $sum: "$price" } }
}]
)
//輸出
{ "_id" : "2", "total" : 90 }
{ "_id" : "3", "total" : 12 }
{ "_id" : "1", "total" : 100 }
- $sort
//只顯示trade_no、all_price，逆序排列，
db.order.aggregate([
{
$project: { trade_no: 1, all_price: 1 }
},
{
$sort: { "all_price": -1 }//正序參數(shù)填1
}
])
//輸出
{ "_id" : ObjectId("5f8182692501fd0ea0437223"), "trade_no" : "111", "all_price" : 100 }
{ "_id" : ObjectId("5f81826f2501fd0ea0437224"), "trade_no" : "222", "all_price" : 90 }
{ "_id" : ObjectId("5f8182732501fd0ea0437225"), "trade_no" : "333", "all_price" : 20 }
- $limit
//只顯示一個搜索結(jié)果
db.order.aggregate([
{
$project: { trade_no: 1, all_price: 1 }
},
{
$sort: { "all_price": -1 }
},
{
$limit:1
}
])
//輸出
{ "_id" : ObjectId("5f8182692501fd0ea0437223"), "trade_no" : "111", "all_price" : 100 }
- $skip
//跳過第一個結(jié)果
db.order.aggregate([
{
$project: { trade_no: 1, all_price: 1 }
},
{
$sort: { "all_price": -1 }
},
{
$skip: 1
}
])
//輸出
{ "_id" : ObjectId("5f81826f2501fd0ea0437224"), "trade_no" : "222", "all_price" : 90 }
{ "_id" : ObjectId("5f8182732501fd0ea0437225"), "trade_no" : "333", "all_price" : 20 }
- $lookup
db.order.aggregate([
{
$lookup:
{
from: "order_item",
localField: "order_id",//目前表order索引
foreignField: "order_id",//目的表order_item索引
as: "items"http://新生的鍵名
}
}
])
//輸出
{
"_id": ObjectId("5f8182692501fd0ea0437223"),
"order_id": "1",
"uid": 10,
"trade_no": "111",
"all_price": 100,
"all_num": 2,
"items": [{
"_id": ObjectId("5f81829c2501fd0ea0437226"),
"order_id": "1",
"title": "商品鼠標(biāo) 1",
"price": 50,
"num": 1
}, {
"_id": ObjectId("5f81829c2501fd0ea0437227"),
"order_id": "1",
"title": "商品鍵盤 2",
"price": 50,
"num": 1
}, {
"_id": ObjectId("5f81829e2501fd0ea0437228"),
"order_id": "1",
"title": "商品鍵盤 3",
"price": 0,
"num": 1
}]
} {
"_id": ObjectId("5f81826f2501fd0ea0437224"),
"order_id": "2",
"uid": 7,
"trade_no": "222",
"all_price": 90,
"all_num": 2,
"items": [{
"_id": ObjectId("5f8182cf2501fd0ea0437229"),
"order_id": "2",
"title": "牛奶",
"price": 50,
"num": 1
}, {
"_id": ObjectId("5f8182d02501fd0ea043722a"),
"order_id": "2",
"title": "酸奶",
"price": 40,
"num": 1
}]
} {
"_id": ObjectId("5f8182732501fd0ea0437225"),
"order_id": "3",
"uid": 9,
"trade_no": "333",
"all_price": 20,
"all_num": 6,
"items": [{
"_id": ObjectId("5f8182e42501fd0ea043722b"),
"order_id": "3",
"title": "礦泉水",
"price": 2,
"num": 5
}, {
"_id": ObjectId("5f8182e52501fd0ea043722c"),
"order_id": "3",
"title": "毛巾",
"price": 10,
"num": 1
}]
}
3.SQL 和 和 NOSQL 對比
| SQL | NOSQL |
|---|---|
| WHERE | $match |
| GROUP | $group |
| HAVING | $match |
| SELECT | $project |
| ORDER BY | $sort |
| LIMIT | $limit |
| SUM() | $sum |
| COUNT() | $sum |
| join | $lookup |
4.數(shù)據(jù)庫備份及還原
以下操作不需要啟動mongodb服務(wù)
數(shù)據(jù)庫導(dǎo)出備份：
mongodump -h dbhost -d dbname -o dbdirectory
-h：一般為127.0.0.1，也可以指定端口號
-d： 數(shù)據(jù)庫名
-o： 要提取到的目錄
數(shù)據(jù)庫恢復(fù)及導(dǎo)入：
mongorestore -h dbhost -d dbname dbdirectory
--drop：恢復(fù)時候先刪除當(dāng)前數(shù)據(jù)，然后恢復(fù)備份數(shù)據(jù)，恢復(fù)后備份后添加修改的數(shù)據(jù)都會被刪除，慎用
如果導(dǎo)入的數(shù)據(jù)庫是有密碼的
mongodump -h dbhost -d dbname -u username -p password -o dbdirectory
mongorestore -h dbhost -d dbname dbdirectory -u username -p password
windows10下需要先下載mongodb-database-tools-windows-x86_64-100.1.1這個包
cmd打開進入C:\Software-Download\mongodb\mongodb-database-tools-windows-x86_64-100.1.1\bin，假設(shè)安裝在其他地方填寫mongodb-database-tools安裝的路徑找到bin文件，cd進入
mongodump -h 127.0.0.1 -d wenera -o C:\222-test\node\bf 導(dǎo)出
mongorestore -h 127.0.0.1 -d test C:\222-test\node\bf導(dǎo)入
