MongoDB学习

use my_study
;
// 查询
db.my_study.find();
// 插入一个
db.my_study.insertOne({
    SysNo: 2970,
    ProductName: "Dell",
    Weight: 460, ProductMode: "Set"
});
// 插入多个
db.my_study.insertMany(
    [
        {
            SysNo: 2971,
            ProductName: "DE-1300 Earbuds",
            Weight: 465, ProductMode: "Set"
        },
        {
            SysNo: 8622,
            ProductName: "(Rose Gold) 16GB",
            Weight: 143, ProductMode: ""
        }
    ]
    );
// 修改，如果没有查询结果，忽略
db.my_study.updateOne({
    _id: ObjectId("60b2edb8714c7b1741387e4c")
}, {$set: {ProductMode: "", Weight: 1000}});
// 修改或插入，如果没有查询结果，插入新纪录
db.my_study.updateOne({
    _id: ObjectId("60b2edb8714c7b1741387e4f")
}, {
    $set: {
        SysNo: 8624,
        ProductName: "Telphone",
        Weight: 200, ProductMode: ""
    }
}, {upsert: true});
// 更新多个
db.my_study.updateMany({
    Weight: {$lt: 500}
}, {
    $set: {
        ProductMode: "Set"
    }
});
// 删除一个字段
db.my_study.updateMany({}, {$unset: {weight: ""}});
// 更新匹配到的第一条记录(覆盖全文档，所有字段)
db.my_study.replaceOne({ProductMode: ""}, {ProductMode: "ABCD"});
// 删除文档
db.my_study.deleteOne({ProductMode: "ABCD"});
db.my_study.deleteMany({ProductMode: "ABCD"});
// 查询
db.my_study.find({}, {_id: 0});
db.my_study.findOne({ProductMode: "Set"}, {_id: 0, ProductMode: 1, ProductName: 1, SysNo: 1});
db.my_study.find({SysNo: {$in: [2971, 2970]}});
db.my_study.find({$and: [{Weight: {$gte: 140}, SysNo: {$eq: 8622}}]});

db.my_study.insertOne({SysNo: 678, field1: 2});
db.my_study.find({field1: {$exists: true}});

db.my_study.insertOne({SysNo: 999, field1: [11, 12, 13]});
db.my_study.insertOne({SysNo: 997, field1: [{f1: "11"}, {f1: "12"}, {f1: "13"}]});
db.my_study.find({field1: {$type: "array"}});
db.my_study.find();
// 比较两个字段
db.my_study.updateOne({SysNo: 2971}, {$set: {Weight: 2971}});
db.my_study.find({$expr: {$eq: ["$SysNo", "$Weight"]}});
db.my_study.find({$and: [{$expr: {$eq: ["$SysNo", "$Weight"]}}, {ProductName: {$exists: true}}]});
// 模糊查询
db.my_study.updateOne({SysNo: 678}, {$set: {ProductMode: "aet"}});
db.my_study.find({ProductMode: {$regex: "a", $options: "i"}});
// 内嵌文档
db.student.insertOne({
    name: 'hunter',
    courses: [{name: '英语', classRoom: '1001'}, {name: '高等数学', classRoom: '1001'}]
});
db.student.insertOne({
    name: 'zhangsan',
    courses: [{name: '英语', classRoom: 1001}, {name: '高等数学', classRoom: 1002}]
});
// 内嵌文档新增项
db.student.updateMany({name: 'hunter'}, {$addToSet: {courses: {name: '复变函数', classRoom: '1002'}}})
// 重复执行一下以上指令，发现并没有再新增一个课程，相同的数据只写入一次。
//  那么要插入重复元素怎么处理，用关键字$push替代$addToSet，其他的不变，执行完后，可以发现，数据里有两门一样的复变函数课程。
db.student.updateOne({name: 'hunter'}, {$push: {courses: {name: '复变函数', classRoom: '1002'}}})
// 删除内嵌文档的项
db.student.updateMany({name: 'zhangsan'}, {$pull: {courses: {name: '复变函数'}}});
// 修改内嵌文档, $[]子文档所有项, $子文档第一项, 下标, 从0开始
db.student.updateMany({'courses.name': '英语'}, {$set: {'courses.$[].classRoom': '100'}});
db.student.updateMany({'courses.name': '英语'}, {$set: {'courses.$.classRoom': '108'}});
// 下标, 从0开始
db.student.updateMany({'courses.name': '英语'}, {$set: {'courses.1.classRoom': '1080'}});
db.student.updateMany({'name': 'zhangsan', 'courses.name': '英语'}, {$set: {'courses.1.classRoom': '108'}});
// 内嵌文档严格匹配
db.student.find({'courses.name': '高等数学', 'courses.classRoom': '108'});
// 整行记录
db.student.find({'courses': {$elemMatch: {'name': '高等数学', 'classRoom': '108'}}});
// 显示内嵌值, 这个是匹配出正确的文档后，再筛选指定的数据，find的第二个参数是个project，虽然与查询参数是一样的，作用倒是不同
db.student.find({'courses': {$elemMatch: {'name': '高等数学', 'classRoom': '108'}}},
    {'courses': {$elemMatch: {'name': '高等数学', 'classRoom': '108'}}});
db.student.find();
// 索引
db.my_study.find();
db.my_study.createIndex({'SysNo': 1}); // 创建索引
db.my_study.reIndex(); // 重建索引
db.my_study.getIndexes(); // 查询所有索引
db.my_study.totalIndexSize(); // 查询集合中所有索引的大小
db.my_study.totalSize();
db.my_study.dropIndex("SysNo_1");
db.my_study.createIndex({SysNo: 1, Weight: -1});

db.my_study.find({SysNo: {$lte: 1000}}).explain();
//聚合
// 分组求和
db.my_study.aggregate([{
    $group: {"_id": "$ProductMode", "sum": {$sum: '$SysNo'}}
}]);
db.my_study.aggregate([{
    $group: {"_id": "$ProductMode", "avg": {$avg: '$SysNo'}}
}]);
db.my_study.aggregate([{
    $group: {"_id": "$ProductMode", "max": {$max: '$SysNo'}}
}]);
// 显示指定字段
db.my_study.aggregate([{
    $project: {"_id": 0, "SysNo": 1, "ProductName": 1}
}]);
// 截取字符串
db.my_study.aggregate([{
    $project: {'ProductNameStr': {$substr: ['$ProductMode', 0, 2]}}
}]);
// 条件判断，必须有default分支
db.my_study.aggregate([{
    $project: {
        'SysNoSwitch': {
            $switch: {
                branches: [
                    {case: {$gt: ['$SysNo', 999]}, then: "Weight>999"},
                    {case: {$eq: ['$SysNo', 999]}, then: "Weight=999"}
                ], default: "Weight<999"
            }
        }
    }
}]);
// 查询字符串在文档中的位置
db.my_study.find();
db.my_study.aggregate([{
    $project: {
        '_id': 1,
        'ProductMode': 1,
        'ProductName': 1,
        'ProductModeStr': {$indexOfBytes: ['$ProductMode', 'e', 0, 9]},
        'ProductNameStr': {$substr: ['$ProductMode', 0, 2]},
        'SysNoSwitch': {
            $switch: {
                branches: [
                    {case: {$gt: ['$SysNo', 999]}, then: "Weight>999"},
                    {case: {$eq: ['$SysNo', 999]}, then: "Weight=999"}
                ], default: "Weight<999"
            }
        },
        'ProductModeLen': {$strLenBytes: "$ProductMode"}
    }
}]);
// 条件匹配，字符串长度(中文占3个字节)
db.my_study.updateOne({'_id': ObjectId('60b2ed20714c7b1741387e47')}, {$set: {'ProductMode': 'set你好'}});
db.my_study.aggregate([
    {$match: {ProductMode: {$exists: true}}},
    {
        $project: {
            'ProductMode': 1,
            'ProductModeLower': {$toLower: '$ProductMode'},
            'ProductModeUpper': {$toUpper: '$ProductMode'},
            'ProductConcat': {$concat: ['PM is ', '$ProductMode', '!']},
            'ProductModeLen': {$strLenBytes: "$ProductMode"},
            'ProductSplit': {$split: ['$ProductMode', 'e']}
        }
    }
]);
// 加减乘除
db.my_study.aggregate([
    {
        $match: {
            $and: [{'SysNo': {$exists: true}}, {'Weight': {$exists: true}}]
        }
    },
    {
        $project: {
            'SysNo': 1,
            'Weight': 1,
            'add': {$add: ['$SysNo', '$Weight']},
            'sub': {$subtract: ['$SysNo', '$Weight']},
            'multiply': {$multiply: ['$SysNo', 0.1]},
            'divide': {$divide: ['$SysNo', 10]},
            'mod': {$mod: ['$SysNo', 2]}
        }
    }
]);
// 时间
db.Carts.insertMany([{
    "Quantity": 8,
    "CustomerSysNO": 88070421,
    "CreateDate": ISODate("2016-09-28T01:37:39.018Z"),
    "product": {
        "ProductName": "Note2 2GB Memory + 16GB Mobile 4G Phone",
        "Weight": 400,
        "ProductMode": "",
        "Price": 9346
    }
}]);
db.Carts.find();
db.Carts.aggregate([
    {
        $project: {
            'CreateDate': 1,
            'year': {$year: "$CreateDate"},
            'month': {$month: "$CreateDate"},
            'week': {$week: "$CreateDate"},
            'hour': {$hour: "$CreateDate"},
            'minute': {$minute: "$CreateDate"},
            'second': {$second: "$CreateDate"},
            'millisecond': {$millisecond: "$CreateDate"},
            'dayOfYear': {$dayOfYear: "$CreateDate"},
            'dayOfMonth': {$dayOfMonth: "$CreateDate"},
            'dayOfWeek': {$dayOfWeek: "$CreateDate"}
        }
    }
]);
// $lookup
db.Product.insertOne({
    "SysNo": 2971,
    "ProductName": "DE - 1300 Earbuds",
    "Weight": 465,
    "ProductMode": "Set"
});
db.ProductContent.insertMany([{
    "ProductName": "DE - 1300 Earbuds",
    "Color": ["Red", "Blue"]
}, {
    "ProductName": "A05 Desktop Host GX3450 8G 500G Black",
    "Weight": 9500
}]);
db.Product.find();
db.ProductContent.find();
db.Product.aggregate([
    {
        $lookup: {
            from: "ProductContent",
            localField: "ProductName",
            foreignField: "ProductName",
            as: "All_Product"
        }
    },
    {
        $match: {
            $and: [
                {"ProductName": {$regex: "de", $options: "i"}},
                {"Weight": {$gt: 400}},
                {"All_Product": {$ne: []}}
            ]
        }
    },
    {
        $project: {
            "All_Product": 0
        }
    },
    {
        $count: "count_res"
    }
]);
// $count
db.Product.aggregate([
    {$match: {"Weight": {$gt: 400}}},
    {$count: "count_result"}
]);
// $unwind
db.ProductContent.aggregate([
    {
        $unwind: {
            path: "$Color",
            includeArrayIndex: "color_v_index",
            preserveNullAndEmptyArrays: true
        }
    }
]);
// $out
db.ProductContent.aggregate([
    {
        $unwind: {
            path: "$Color",
            includeArrayIndex: "color_v_index",
            preserveNullAndEmptyArrays: true
        }
    },
    {
        $project: {
            _id: 0
        }
    },
    {
        $out: "ProductContentOut"
    }
]);
db.ProductContentOut.find();
// 存储过程
db.Product.find();
db.Product.insertMany([{
    "SysNo": 8622,
    "ProductName": "(Rose Gold) 16GB",
    "Weight": 143,
    "ProductMode": ""
}]);
// 创建并加载存储过程
db.system.js.save({
    _id: "UpdateProductWeight",
    value: function (param) {
        return db.Product.updateOne({SysNo: param}, {$set: {Weight: 14300}});
    }
});
db.loadServerScripts();  // 加载后才能使用存储过程
db.system.js.find();    // 查询所有存储过程
UpdateProductWeight(8622); // 执行存储过程

// 映射与规约
var mapFunction=function () {
    emit(ProductMode, Weight);
};
var reduceFunction=function (key, values) {
    var total = 0;
    for (var i = 0; i < values.length; i++) {
        total += values[i];
    }
    return total;
};
db.Product.mapReduce(mapFunction, reduceFunction, {
    query: {SysNo: {$gt: 2000}},
    out: "Product_Mapreduce"
});