1.表与表的三种关系
一对一、一对多、多对多
| 管道操作符 | 介绍 |
|---|---|
$project |
增加、删除、重命名 |
$match |
条件匹配，满足条件进入下一阶段 |
$limit |
限制结果的数量 |
$skip |
跳过文档的数量 |
$sort |
条件排序 |
$group |
条件组合结果 |
$lookup |
引入其他集合数据 |
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" : "商品鼠标 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
修改文档的结构，可以用来重命名、增加或删除文档中的字段
//只显示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() 方法中的参数。
//只显示trade_no、all_price，且>=90的数据
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
将集合中的文档进行分组，可用于统计结果
//假设id相同统计他的价格
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 }//正序参数填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
//只显示一个搜索结果
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
//跳过第一个结果
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"//新生的键名
}
}
])
//输出
{
"_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": "商品鼠标 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.数据库备份及还原
以下操作不需要启动mongodb服务
数据库导出备份：
mongodump -h dbhost -d dbname -o dbdirectory
-h：一般为127.0.0.1，也可以指定端口号
-d： 数据库名
-o： 要提取到的目录
数据库恢复及导入：
mongorestore -h dbhost -d dbname dbdirectory
--drop：恢复时候先删除当前数据，然后恢复备份数据，恢复后备份后添加修改的数据都会被删除，慎用
如果导入的数据库是有密码的
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，假设安装在其他地方填写mongodb-database-tools安装的路径找到bin文件，cd进入
mongodump -h 127.0.0.1 -d wenera -o C:\222-test\node\bf 导出
mongorestore -h 127.0.0.1 -d test C:\222-test\node\bf导入
网友评论