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"
});