mongodb操作

插入操作

在collection中创建documnets, 如果collection不存在,则会自动创建

单条插入: db.collection.insertOne()

批量插入: db.collection.insertMany()

单条数据插入

db.inventory.insertOne(
   { item: "canvas", qty: 100, tags: ["cotton"], size: { h: 28, w: 35.5, uom: "cm" } }
)

多条数据插入

db.inventory.insertMany([
   { item: "journal", qty: 25, tags: ["blank", "red"], size: { h: 14, w: 21, uom: "cm" } },
   { item: "mat", qty: 85, tags: ["gray"], size: { h: 27.9, w: 35.5, uom: "cm" } },
   { item: "mousepad", qty: 25, tags: ["gel", "blue"], size: { h: 19, w: 22.85, uom: "cm" } }
])

读取操作

简单查询

db.inventory.find( {} )
VS
SELECT * FROM inventory

条件查询

db.inventory.find( { status: "D" } )
VS
SELECT * FROM inventory WHERE status = "D"

多条件查询

  • and
db.inventory.find( { status: "A", qty: { $lt: 30 } } )
db.inventory.find( { $and: [ { status: "A" }, { qty: { $lt: 30 } } ] } )
VS
SELECT * FROM inventory WHERE status = "A" AND qty < 30
  • or
db.inventory.find( { $or: [ { status: "A" }, { qty: { $lt: 30 } } ] } )
VS
SELECT * FROM inventory WHERE status = "A" OR qty < 30
  • and - or
db.inventory.find( {
     status: "A",
     $or: [ { qty: { $lt: 30 } }, { item: /^p/ } ]
} )
VS
SELECT * FROM inventory WHERE status = "A" AND ( qty < 30 OR item LIKE "p%")
  • 备注:

    db.collection.findone() 方法是在 db.collection.find()方法中加入了 limit 1 的条件;

    常用条件表达式:

    mongo表达式 常见表示 表达式描述
    $gt > 大于
    $gte >= 大于等于
    $lt < 小于
    $lte <= 小于等于

范围查询

db.inventory.find( { status: { $in: [ "A", "D" ] } } )
VS
SELECT * FROM inventory WHERE status in ("A", "D")

模糊查询

db.products.find( { sku: { $regex: /789$/ } } )
VS
SELECT * FROM products WHERE sku like "%789";

联合查询

  • union all
db.suppliers.aggregate([   // 当前表
   { $project: { state: 1, _id: 0 } },
   { $unionWith: { 
   		coll: "warehouses",  // 需要union的表
   		pipeline: [          // 需要union的表查询条件
   			{ $project: { state: 1, _id: 0 } } ]} }
   		] 
   	)
  • left join
db.orders.aggregate([  // 左表
   {
     $lookup:
       {
         from: "inventory",  // 右表
         localField: "item",  // 左表关键字
         foreignField: "sku", // 右表关键字
         as: "inventory_docs" // 右表关联数据别名
       }
  }
])

分组查询

db.sales.aggregate( [
  {
    $group: {
       _id: null,
       count: { $sum: 1 }
    }
  }
] )
SELECT COUNT(*) AS count FROM sales
db.sales.aggregate(
  [
    // First Stage
    {
      $group :
        {
          _id : "$item",
          totalSaleAmount: { $sum: { $multiply: [ "$price", "$quantity" ] } }
        }
     },
     // Second Stage
     {
       $match: { "totalSaleAmount": { $gte: 100 } }
     }
   ]
 )
SELECT item,
   Sum(( price * quantity )) AS totalSaleAmount
FROM   sales
GROUP  BY item
HAVING totalSaleAmount >= 100
db.sales.aggregate([
  // First Stage
  {
    $match : { "date": { $gte: new ISODate("2014-01-01"), $lt: new ISODate("2015-01-01") } }
  },
  // Second Stage
  {
    $group : {
       _id : { $dateToString: { format: "%Y-%m-%d", date: "$date" } },
       totalSaleAmount: { $sum: { $multiply: [ "$price", "$quantity" ] } },
       averageQuantity: { $avg: "$quantity" },
       count: { $sum: 1 }
    }
  },
  // Third Stage
  {
    $sort : { totalSaleAmount: -1 }
  }
 ])
SELECT date,
       Sum(( price * quantity )) AS totalSaleAmount,
       Avg(quantity)             AS averageQuantity,
       Count(*)                  AS Count
FROM   sales
GROUP  BY Date(date)
ORDER  BY totalSaleAmount DESC

更新操作

  1. 更新单个文档

db.collection.updateOne(, , )

$set $set修饰符来更新字段

$currentDate 将字段更新为当前时间

db.inventory.updateOne(
   { item: "paper" },
   {
     $set: { "size.uom": "cm", status: "P" },
     $currentDate: { lastModified: true }
   }
)
  1. 更新多个文档
db.inventory.updateMany(
   { "qty": { $lt: 50 } },
   {
     $set: { "size.uom": "in", status: "P" },
     $currentDate: { lastModified: true }
   }
)
  1. 替换文档

替换文档时,只写键值对就可以了,不要更新运算符

db.inventory.replaceOne(
   { item: "paper" },
   { item: "paper", instock: [ { warehouse: "A", qty: 60 }, { warehouse: "B", qty: 40 } ] }
)
  • mongodb中的所有写操作都是单个文档级别的原子操作
  • _id 字段设置后,将无法更新 _ id 字段的值, 也无法使用具有不同 _id字段值的替换文档替换现有文档
  • mongodb会在执行写操作后保留文档字段的顺序。 _id 字段始终时第一个字段

删除操作

  1. 全量删除
db.inventory.deleteMany({})
  1. 根据条件删除
db.inventory.deleteMany({ status : "A" })
  1. 只删除一个
db.inventory.deleteOne( { status: "D" } )

删除方法

db.collection.deleteOne() 即使多个文档可能与指定过滤器匹配,也最多删除一个与指定过滤器匹配的文档。3.2版中的新功能。
db.collection.deleteMany() 删除所有与指定过滤器匹配的文档。3.2版中的新功能。
db.collection.remove() 删除单个文档或与指定过滤器匹配的所有文档。
db.collection.findOneAndDelete() 提供了一个排序选项。该选项允许删除按指定顺序排序的第一个文档。
db.collection.findAndModify() 提供排序选项。该选项允许删除按指定顺序排序的第一个文档。
db.collection.bulkWrite()

进阶

查询进阶

插入数据源

db.inventory.insertMany( [
   { item: "journal", qty: 25, size: { h: 14, w: 21, uom: "cm" }, status: "A" },
   { item: "notebook", qty: 50, size: { h: 8.5, w: 11, uom: "in" }, status: "A" },
   { item: "paper", qty: 100, size: { h: 8.5, w: 11, uom: "in" }, status: "D" },
   { item: "planner", qty: 75, size: { h: 22.85, w: 30, uom: "cm" }, status: "D" },
   { item: "postcard", qty: 45, size: { h: 10, w: 15.25, uom: "cm" }, status: "A" }
]);

嵌套文档查询

在文档的单个字段上进行查询

查询 size={ h: 14, w: 21, uom: "cm" } 的数据

db.inventory.find( { size: { h: 14, w: 21, uom: "cm" } } )

在文档内的嵌套字段上进行查询

查询size.uom=in (size下的内嵌文档中的uom)的数据

db.inventory.find( { "size.uom": "in" } )

数组查询

  1. 匹配一个数组

完全匹配模式,普通查询都是完全匹配模式,value必须完全一致

db.inventory.find( { tags: ["red", "blank"] } )

自由匹配模式

$all ,查询出数组中同时包含"red"和 "blank"元素的数据,不管顺序

db.inventory.find( { tags: { $all: ["red", "blank"] } } )
  1. 根据数组中的元素进行查找(满足一个条件即可)

根据数组中的某一个元素,查询包含该元素的数据

数组元素中还包含 “red” 的数据

db.inventory.find( { tags: "red" } )

数组中至少有一个元素>25 的数据

db.inventory.find( { dim_cm: { $gt: 25 } } )

数组元素中包含 >15或<20 或 15<x<20 的数据

db.inventory.find( { dim_cm: { $gt: 15, $lt: 20 } } )
  1. 指定数组元素多重条件(满足所有条件)

$elemMatch 指定多个条件, 至少一个元素满足条件 >22且<30

db.inventory.find( { dim_cm: { $elemMatch: { $gt: 22, $lt: 30 } } } )

按照元素索引位置进行查找

查找数组中第二个元素>25 的数据

array.index 数组.元素下标

db.inventory.find( { "dim_cm.1": { $gt: 25 } } )

根据数组元素个数查询

$size

db.inventory.find( { "tags": { $size: 3 } } )
  1. 查询嵌入式文档数组

嵌入式文档查询上边已经介绍过了

数组查询也已经介绍过了

现在时将两者综合运用

在嵌入式文档数组中的字段上使用查询条件

db.inventory.find( { 'instock.qty': { $lte: 20 } } )

在嵌入式文档数组中的元素索引上使用查询条件

db.inventory.find( { 'instock.0.qty': { $lte: 20 } } )

返回查询的字段

db.inventory.find( { status: "A" }, { item: 1, status: 1 } )
VS
SELECT _id, item, status from inventory WHERE status = "A"

如果不想返回_id 字段,可以将_id:0

db.inventory.find( { status: "A" }, { item: 1, status: 1, _id: 0 } )

想要返回什么字段,字段的值就==1,不想显示什么字段就 ==0

如果全是 1,则全部返回;

有0有1的,返回为1 的

全都是0,返回非0 的其他全部字段

$slice 分片,根据数组下标范围进行数组截取, -1 表示取最后一个数据

db.inventory.find( { status: "A" }, { item: 1, status: 1, instock: { $slice: -1 } } )

查询为空或者字段缺失的数据

db.inventory.insertMany([
   { _id: 1, item: null },
   { _id: 2 }
])
  1. 平等过滤器,两条数据都会被查出来
db.inventory.find( { item: null } )
  1. 类型检查

只返回类型为null的数据

$type

db.inventory.find( { item : { $type: 10 } } )
  • 类型说明列表
Type Number Alias Notes
Double 1 “double”
String 2 “string”
Object 3 “object”
Array 4 “array”
Binary data 5 “binData”
Undefined 6 “undefined” Deprecated.
ObjectId 7 “objectId”
Boolean 8 “bool”
Date 9 “date”
Null 10 “null”
Regular Expression 11 “regex”
DBPointer 12 “dbPointer” Deprecated.
JavaScript 13 “javascript”
Symbol 14 “symbol” Deprecated.
JavaScript code with scope 15 “javascriptWithScope” Deprecated in MongoDB 4.4.
32-bit integer 16 “int”
Timestamp 17 “timestamp”
64-bit integer 18 “long”
Decimal128 19 “decimal” New in version 3.4.
Min key -1 “minKey”
Max key 127 “maxKey”
  1. 存在检查

查询不包含item字段的文档

$exists

db.inventory.find( { item : { $exists: false } } )

聚合管道更新

@since4.2

使用聚合管道可以实现更具表达力的更新语句

什么是管道?pipline ? ===> 就是 ==> [ ]

$addFields 新增字段 $set 更新字段
$project $unset
$replaceRoot $replaceWith

$set 更新字段

$trunc 将数字阶段为整数或指定的小数位

返回整数

db.samples.aggregate([
   { $project: { truncatedValue: { $trunc: [ "$value", 0 ] } } }
])

返回保留一位小数

db.samples.aggregate([
   { $project: { truncatedValue: { $trunc: [ "$value", 1 ] } } }
])

-1是什么意思?

The following aggregation returns value truncated to the first place

下面的聚合返回截断到第一个位置的值

{ _id: 1, value: 19.25 }
db.samples.aggregate([
   { $project: { truncatedValue: { $trunc: [ "$value", -1 ] } } }
])
{ "_id" : 1, "truncatedValue" : 10 }

$addFields 新增字段

$mergeObjects 有就更新,没有就填充

$concatArrays 数组拼接

$map 将表达式应用于数组中的每个项目,并返回具有应用结果的数组

db.collection.updateOne() 即使多个文档可能与指定的过滤器匹配,也最多更新一个与指定的过滤器匹配的文档。3.2版中的新功能。
db.collection.updateMany() 更新所有与指定过滤器匹配的文档。3.2版中的新功能。
db.collection.replaceOne() 即使多个文档可能与指定过滤器匹配,也最多替换一个与指定过滤器匹配的文档。3.2版中的新功能。
db.collection.update() 更新或替换与指定过滤器匹配的单个文档,或更新与指定过滤器匹配的所有文档。默认情况下,该db.collection.update()方法更新单个文档。要更新多个文档,请使用multi选项。
db.collection.findOneAndReplace()
db.collection.findOneAndUpdate()
db.collection.findAndModify()
db.collection.save()
db.collection.bulkWrite()

批量操作

try {
   db.characters.bulkWrite(
      [
         { insertOne :
            {
               "document" :
               {
                  "_id" : 4, "char" : "Dithras", "class" : "barbarian", "lvl" : 4
               }
            }
         },
         { insertOne :
            {
               "document" :
               {
                  "_id" : 5, "char" : "Taeln", "class" : "fighter", "lvl" : 3
               }
            }
         },
         { updateOne :
            {
               "filter" : { "char" : "Eldon" },
               "update" : { $set : { "status" : "Critical Injury" } }
            }
         },
         { deleteOne :
            { "filter" : { "char" : "Brisbane" } }
         },
         { replaceOne :
            {
               "filter" : { "char" : "Meldane" },
               "replacement" : { "char" : "Tanys", "class" : "oracle", "lvl" : 4 }
            }
         }
      ]
   );
}
catch (e) {
   print(e);
}

聚合函数

$lookup

table joins 表关联

什么意思?

联合查询,将关联表的数据以as的字段进行绑定

db.orders.insert([
   { "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 },
   { "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 },
   { "_id" : 3  }
])
db.inventory.insert([
   { "_id" : 1, "sku" : "almonds", "description": "product 1", "instock" : 120 },
   { "_id" : 2, "sku" : "bread", "description": "product 2", "instock" : 80 },
   { "_id" : 3, "sku" : "cashews", "description": "product 3", "instock" : 60 },
   { "_id" : 4, "sku" : "pecans", "description": "product 4", "instock" : 70 },
   { "_id" : 5, "sku": null, "description": "Incomplete" },
   { "_id" : 6 }
])
db.orders.aggregate([  // 左表
   {
     $lookup:
       {
         from: "inventory",  // 右表
         localField: "item",  // 左表关键字
         foreignField: "sku", // 右表关键字
         as: "inventory_docs" // 右表关联数据别名
       }
  }
])
{
   "_id" : 1,
   "item" : "almonds",
   "price" : 12,
   "quantity" : 2,
   "inventory_docs" : [
      { "_id" : 1, "sku" : "almonds", "description" : "product 1", "instock" : 120 }
   ]
}
{
   "_id" : 2,
   "item" : "pecans",
   "price" : 20,
   "quantity" : 1,
   "inventory_docs" : [
      { "_id" : 4, "sku" : "pecans", "description" : "product 4", "instock" : 70 }
   ]
}
{
   "_id" : 3,
   "inventory_docs" : [
      { "_id" : 5, "sku" : null, "description" : "Incomplete" },
      { "_id" : 6 }
   ]
}

$out

SELECT INTO NEW_TABLE

将聚合管道返回的文档,输出到指定的集合

就是将查询出来的结果插入到另外的集合中(可以是不同的数据库)

插入到同一个数据库中

  1. 查询test 数据库的 books 集合
  2. 将返回结果写入 authors 集合中
db.getSiblingDB("test").books.aggregate( [
    { $group : { _id : "$author", books: { $push: "$title" } } },
    { $out : "authors" }
] )

插入到不同的数据库中

  1. 查询test 数据库的 books 集合
  2. 将返回结果插入到 reporting 数据库的 authors 集合中
db.getSiblingDB("test").books.aggregate( [
    { $group : { _id : "$author", books: { $push: "$title" } } },
    { $out : { db: "reporting", coll: "authors" } }
] )

数据库操作相关常用方法介绍

https://blog.csdn.net/zhaoyong421127/article/details/51242691

$merge

简单的merge操作和out的效果是一样的,都是将上一步的结果插入到一个新的集合中,只能是当前数据库

db.sales.aggregate( [
   { $project: { _id: 0 } },
   { $merge : { into : "newCollection" } }
] )

on -指定条件

whenMatched : 当on的数据被匹配到(已存在)时,执行 ‘replace’ 更新操作

whenNotMatched :当on的数据没有被匹配到时(不存在),执行插入操作

db.getSiblingDB("zoo").salaries.aggregate( [
   { $group: { _id: { fiscal_year: "$fiscal_year", dept: "$dept" }, salaries: { $sum: "$salary" } } },
   { $merge : { into: { db: "reporting", coll: "budgets" }, on: "_id",  whenMatched: "replace", whenNotMatched: "insert" } }
] )

当数据被匹配到的时候,增加新的字段 thumpbsup , thumbsdown]

没被匹配上进行插入

db.votes.aggregate([
   { $match: { date: { $gte: new Date("2019-05-07"), $lt: new Date("2019-05-08") } } },
   { $project: { _id: { $dateToString: { format: "%Y-%m", date: "$date" } }, thumbsup: 1, thumbsdown: 1 } },
   { $merge: {
         into: "monthlytotals",
         on: "_id",
         whenMatched:  [
            { $addFields: {
                thumbsup: { $add:[ "$thumbsup", "$$new.thumbsup" ] },
                thumbsdown: { $add: [ "$thumbsdown", "$$new.thumbsdown" ] }
            } } ],
         whenNotMatched: "insert"
   } }
])
对日期格式进行转换
$dateToString: { format: "%Y-%m", date: "$date" }

如果输出集合具有文档:
{ _id: 1, a: 1, b: 1 }

并且聚合结果有文件:
{ _id: 1, b: 5, z: 1 }

然后,合并的文档为:
{ _id: 1, a: 1, b: 5, z: 1 }

$unionWith

联合查询

db.suppliers.aggregate([
   { $project: { state: 1, _id: 0 } },
   { $unionWith: { coll: "warehouses", pipeline: [ { $project: { state: 1, _id: 0 } } ]} }
])

$unwind

数据展开

db.inventory.insertOne({ "_id" : 1, "item" : "ABC1", sizes: [ "S", "M", "L"] })

数据展开

db.inventory.aggregate( [ { $unwind : "$sizes" } ] )

展开后的结果


{ "_id" : 1, "item" : "ABC1", "sizes" : "S" }
{ "_id" : 1, "item" : "ABC1", "sizes" : "M" }
{ "_id" : 1, "item" : "ABC1", "sizes" : "L" }

$unset

$unset是$project阶段的别名,用于删除/排除字段:

什么意思?

查询的时候,不显示某些字段

$facet

同一阶段。可以在一个文档上同时添加多个pipline,输出多个结果

返回的结果以文档数组形式存储

啥意思?

一个文档包含两个数字 1,2

可以同时计算 + - × ÷

返回结果 [3,-1,2,0.5]

文本搜索

文本索引,,支持对字符串内容的文本搜索查询

  1. 在文本集合上创建索引, 一个集合只能有一个文本搜索索引,该索引可涵盖多字段

创建索引

db.stores.createIndex( { name: "text", description: "text" } )

进行查询

多字符串查询查询, 分别包含 java ,coffee, shop 内容的数据

db.stores.find( { $text: { $search: "java coffee shop" } } )

单字符串查询

db.stores.find( { $text: { $search: "\"coffee shop\"" } } )

包含 java, shop 不包含 coffee

db.stores.find( { $text: { $search: "java shop -coffee" } } )

$accumulator (aggregation)

$addToSet (aggregation)

$allElementsTrue (aggregation)

$anyElementTrue (aggregation)

$binarySize (aggregation)

$bsonSize (aggregation)

$ceil (aggregation)

$cmp (aggregation)

$concat (aggregation)

$cond (aggregation)

$convert (aggregation)

$degreesToRadians (aggregation)

$filter (aggregation)

$first (aggregation accumulator)

$first (aggregation)

$floor (aggregation)

$function (aggregation)

$ifNull (aggregation)

$in (aggregation)

$last (aggregation accumulator)

$last (aggregation)

$let (aggregation)

$literal (aggregation)

$ln (aggregation)

$ltrim (aggregation)

$map (aggregation)

$mergeObjects (aggregation)

$meta$min (aggregation)

$not (aggregation)

$or (aggregation)

$push (aggregation)

$radiansToDegrees (aggregation)

$range (aggregation)

$reduce (aggregation)

$regexFind (aggregation)

$regexFindAll (aggregation)

$regexMatch (aggregation)

$replaceOne (aggregation)

$replaceAll (aggregation)

$reverseArray (aggregation)

$round (aggregation)

$rtrim (aggregation)

$second (aggregation)

$setDifference (aggregation)

$setEquals (aggregation)

$setIntersection (aggregation)

$setIsSubset (aggregation)

$setUnion (aggregation)

$size (aggregation)

$slice (aggregation)

$split (aggregation)

$stdDevPop (aggregation)

$stdDevSamp (aggregation)

$strcasecmp (aggregation)

$strLenBytes (aggregation)

$strLenCP (aggregation)

$substr (aggregation)

$substrBytes (aggregation)

$substrCP (aggregation)

$subtract (aggregation)

$switch (aggregation)

功能函数

$indexOfArray (aggregation)

$indexOfBytes (aggregation)

$indexOfCP (aggregation)

$isArray (aggregation)

$isNumber (aggregation)

数组处理

$arrayElemAt (aggregation)

$arrayToObject (aggregation)

$concatArrays (aggregation)

$objectToArray (aggregation)

字符串处理

数据类型处理

$toBool (aggregation)

$toDate (aggregation)

$toDecimal (aggregation)

$toDouble(aggregation)

$toInt (aggregation)

$toLong (aggregation)

$toObjectId (aggregation)

$toString (aggregation)

$trunc (aggregation)

字符串处理

$toLower (aggregation)

$toUpper (aggregation)

$trim (aggregation)

日期处理

$dateFromParts (aggregation)

$dateToParts (aggregation)

$dateFromString (aggregation)

$dateToString (aggregation)

$dayOfMonth (aggregation)

$dayOfWeek (aggregation)

$dayOfYear (aggregation)

$type (aggregation)

$week (aggregation)

$year (aggregation)

$hour (aggregation)

$month (aggregation)

$millisecond (aggregation)

$minute (aggregation)

$zip (aggregation)

$isoDayOfWeek (aggregation)

$isoWeek (aggregation)

$isoWeekYear (aggregation)

数学计算

$add (aggregation)

$divide (aggregation)

$abs (aggregation)

$multiply (aggregation)

$mod (aggregation)

$avg (aggregation)

$max (aggregation)

三角函数

$log (aggregation)

$log10 (aggregation)

$sin (aggregation)

$sqrt (aggregation)

$sum (aggregation)

$tan (aggregation)

$cos (aggregation)

$atanh (aggregation)

$atan2 (aggregation)

$asin (aggregation)

$asinh (aggregation)

$atan (aggregation)

$acos (aggregation)

$acosh (aggregation)

$exp (aggregation)

比较运算符

$lt (aggregation)

$lte (aggregation)

$ne (aggregation)

$pow (aggregation)

$gt (aggregation)

$gte (aggregation)

$eq (aggregation)

$ addFields(聚合)

$ bucket(汇总)

$ bucketAuto(聚合)

$ collStats(汇总)

$ count(汇总)

$ currentOp(汇总)

$ facet(汇总)

$ geoNear(汇总)

$ graphLookup(汇总)

$ group(汇总)

$ indexStats(汇总)

$ limit(汇总)

$ listLocalSessions

$ listSessions

$ lookup(汇总)

$ match(汇总)

$合并(汇总)

$ out(汇总)

$ planCacheStats

$ project(汇总)

$ redact(汇总)

$ replaceRoot(聚合)

$ replaceWith(汇总)

$ sample(汇总)

$ sampleRate(汇总)

$ set(汇总)

$ skip(汇总)

$ sort(汇总)

$ sortByCount(汇总)

$ unionWith(聚合)

$ unset(汇总)

$ unwind(汇总)

posted @ 2021-01-19 17:10  江湖一笑  阅读(162)  评论(0编辑  收藏  举报