《MongoDB高手课》学习记录（第十一天）

第十一天

今天继续CRUD部分的复习。

查询数据

昨天还遗留3个内容没复习，今天补上

查询 null 值

生成几条测试数据

db.inventory.insertMany([
   { _id: 1, item: null },
   { _id: 2 }
])

如果下面这样写，除返回值是 null 的文档之外，还返回不包含 item 字段的文档。

> db.inventory.find( { item: null } )
{ "_id" : 1, "item" : null }
{ "_id" : 2 }

那么，如果我只想返回值是 null 的数据呢？

> db.inventory.find( { item : { $type: 10 } } )
{ "_id" : 1, "item" : null }

$type 为 10 表示的是MongoDB内置类型为 null，更多的类型如下

类型	数字	别名
Double	1	“double”
String	2	“string”
Object	3	“object”
Array	4	“array”
Binary data	5	“binData”
ObjectId	7	“objectId”
Boolean	8	“bool”
Date	9	“date”
Null	10	“null”
Regular Expression	11	“regex”
JavaScript	13	“javascript”
JavaScript (with scope)	15	“javascriptWithScope”
32-bit integer	16	“int”
Timestamp	17	“timestamp”
64-bit integer	18	“long”
Decimal128	19	“decimal”
Min key	-1	“minKey”
Max key	127	“maxKey”

查询不包含某个字段的文档

> db.inventory.find( { item : { $exists: false } } )
{ "_id" : 2 }

控制返回的字段列表

现在数据是这样的

> db.inventory.find()
{ "_id" : 1, "item" : null }
{ "_id" : 2 }
{ "_id" : ObjectId("5e089a29667bbe281bb9a88b"), "item" : "journal", "status" : "A", "size" : { "h" : 14, "w" : 21, "uom" : "cm" }, "instock" : [ { "warehouse" : "A", "qty" : 5 } ] }
{ "_id" : ObjectId("5e089a29667bbe281bb9a88c"), "item" : "notebook", "status" : "A", "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "instock" : [ { "warehouse" : "C", "qty" : 5 } ] }
{ "_id" : ObjectId("5e089a29667bbe281bb9a88d"), "item" : "paper", "status" : "D", "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "instock" : [ { "warehouse" : "A", "qty" : 60 } ] }
{ "_id" : ObjectId("5e089a29667bbe281bb9a88e"), "item" : "planner", "status" : "D", "size" : { "h" : 22.85, "w" : 30, "uom" : "cm" }, "instock" : [ { "warehouse" : "A", "qty" : 40 } ] }
{ "_id" : ObjectId("5e089a29667bbe281bb9a88f"), "item" : "postcard", "status" : "A", "size" : { "h" : 10, "w" : 15.25, "uom" : "cm" }, "instock" : [ { "warehouse" : "B", "qty" : 15 }, { "warehouse" : "C", "qty" : 35 } ] }
>

如果我要item与status其他不想要，怎么办。传入第二个对象。

> db.inventory.find({}, {item: 1, status: 1, _id: 0})
{ "item" : null }
{  }
{ "item" : "journal", "status" : "A" }
{ "item" : "notebook", "status" : "A" }
{ "item" : "paper", "status" : "D" }
{ "item" : "planner", "status" : "D" }
{ "item" : "postcard", "status" : "A" }

_id 字段比较特殊，一定要显示的指定，否则默认一直显示，而其他这段，如果不写，就不显示。
反过来，如果我只排除item与status不显示，而其他都显示呢？

> db.inventory.find( { }, { item: 0, status: 0 } )
{ "_id" : 1 }
{ "_id" : 2 }
{ "_id" : ObjectId("5e089a29667bbe281bb9a88b"), "size" : { "h" : 14, "w" : 21, "uom" : "cm" }, "instock" : [ { "warehouse" : "A", "qty" : 5 } ] }
{ "_id" : ObjectId("5e089a29667bbe281bb9a88c"), "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "instock" : [ { "warehouse" : "C", "qty" : 5 } ] }
{ "_id" : ObjectId("5e089a29667bbe281bb9a88d"), "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "instock" : [ { "warehouse" : "A", "qty" : 60 } ] }
{ "_id" : ObjectId("5e089a29667bbe281bb9a88e"), "size" : { "h" : 22.85, "w" : 30, "uom" : "cm" }, "instock" : [ { "warehouse" : "A", "qty" : 40 } ] }
{ "_id" : ObjectId("5e089a29667bbe281bb9a88f"), "size" : { "h" : 10, "w" : 15.25, "uom" : "cm" }, "instock" : [ { "warehouse" : "B", "qty" : 15 }, { "warehouse" : "C", "qty" : 35 } ] }

但是，如果使用排除模式，就不能单独控制 _id 的显示了，否则报错,也就是不能混着用，要么排除，要么显示

> db.inventory.find( { }, { item: 0, status: 0, _id: 1 } )
Error: error: {
        "ok" : 0,
        "errmsg" : "Projection cannot have a mix of inclusion and exclusion.",
        "code" : 2,
        "codeName" : "BadValue"
}

如果是返回内嵌文档，比如只返回size.uom

> db.inventory.find({},{"size.uom": 1, _id: 0})
{  }
{  }
{ "size" : { "uom" : "cm" } }
{ "size" : { "uom" : "in" } }
{ "size" : { "uom" : "in" } }
{ "size" : { "uom" : "cm" } }
{ "size" : { "uom" : "cm" } }

如果控制返回数组内容,比如instock数组中的qty

> db.inventory.find({},{"instock.qty": 1,_id: 0})
{  }
{  }
{ "instock" : [ { "qty" : 5 } ] }
{ "instock" : [ { "qty" : 5 } ] }
{ "instock" : [ { "qty" : 60 } ] }
{ "instock" : [ { "qty" : 40 } ] }
{ "instock" : [ { "qty" : 15 }, { "qty" : 35 } ] }

那么，如果只想返回最后一组呢，比如上面qty:35那组有2个，我只要最后一个

> db.inventory.find({},{instock: { $slice: -1 },"instock.qty": 1,_id: 0})
{  }
{  }
{ "instock" : [ { "qty" : 5 } ] }
{ "instock" : [ { "qty" : 5 } ] }
{ "instock" : [ { "qty" : 60 } ] }
{ "instock" : [ { "qty" : 40 } ] }
{ "instock" : [ { "qty" : 35 } ] }

字段别名

插入数据

这块的内容，我们前面已经用过几次了，insert插入一个文档，insertMany插入多个文档。每个新插入的文档都会有一个_id做为主键，类型是ObjectId()，由系统自动生成。

插入1个

db.inventory.insertOne({ item: "journal", status: "A", size: { h: 14, w: 21, uom: "cm" }, instock: [ { warehouse: "A", qty: 5 } ] })

插入多个

> db.inventory.insertMany( [
  { item: "journal", status: "A", size: { h: 14, w: 21, uom: "cm" }, instock: [ { warehouse: "A", qty: 5 } ] },
  { item: "notebook", status: "A",  size: { h: 8.5, w: 11, uom: "in" }, instock: [ { warehouse: "C", qty: 5 } ] },
  { item: "paper", status: "D", size: { h: 8.5, w: 11, uom: "in" }, instock: [ { warehouse: "A", qty: 60 } ] },
  { item: "planner", status: "D", size: { h: 22.85, w: 30, uom: "cm" }, instock: [ { warehouse: "A", qty: 40 } ] },
  { item: "postcard", status: "A", size: { h: 10, w: 15.25, uom: "cm" }, instock: [ { warehouse: "B", qty: 15 }, { warehouse: "C", qty: 35 } ] }
]);

更新数据

更新1个文档

如果要更新数据，也要传入2个对象，第一个是条件，第二是要更新字段及值。

> db.inventory.updateOne(
    { item: "paper" },
    {
      $set: { "size.uom": "cm", status: "P" },
      $currentDate: { lastModified: true }
    }
 )
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.inventory.findOne(    { item: "paper" } )
{
        "_id" : ObjectId("5e089a29667bbe281bb9a88d"),
        "item" : "paper",
        "status" : "P",
        "size" : {
                "h" : 8.5,
                "w" : 11,
                "uom" : "cm"
        },
        "instock" : [
                {
                        "warehouse" : "A",
                        "qty" : 60
                }
        ],
        "lastModified" : ISODate("2019-12-29T12:58:35.907Z")
}

$currentDate: { lastModified: true }，是指定如果存在lastModified字段就是用currentDate，也就是当前时间赋值，如果不存在，刚创建一个。

> db.inventory.updateOne(
  { item: "planner" },
  {      
    $set: { 
      "size.uom": "cm", 
      status: "P"
    }
  } 
)
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.inventory.findOne(    { item: "planner" } )
{
        "_id" : ObjectId("5e089a29667bbe281bb9a88e"),
        "item" : "planner",
        "status" : "P",
        "size" : {
                "h" : 22.85,
                "w" : 30,
                "uom" : "cm"
        },
        "instock" : [
                {
                        "warehouse" : "A",
                        "qty" : 40
                }
        ]
}

更新多个文档

> db.inventory.updateMany(    { "instock.qty": { $lt: 50 } },    {      $set: { "size.uom": "in", status: "P" },      $currentDate: { lastModified: true }    } )
{ "acknowledged" : true, "matchedCount" : 9, "modifiedCount" : 9 }
> db.inventory.find({ "instock.qty": { $lt: 50 } })
{ "_id" : ObjectId("5e089a29667bbe281bb9a88b"), "item" : "journal", "status" : "P", "size" : { "h" : 14, "w" : 21, "uom" : "in" }, "instock" : [ { "warehouse" : "A", "qty" : 5 } ], "lastModified" : ISODate("2019-12-29T13:09:08.872Z") }
{ "_id" : ObjectId("5e089a29667bbe281bb9a88c"), "item" : "notebook", "status" : "P", "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "instock" : [ { "warehouse" : "C", "qty" : 5 } ], "lastModified" : ISODate("2019-12-29T13:09:08.872Z") }
{ "_id" : ObjectId("5e089a29667bbe281bb9a88e"), "item" : "planner", "status" : "P", "size" : { "h" : 22.85, "w" : 30, "uom" : "in" }, "instock" : [ { "warehouse" : "A", "qty" : 40 } ], "lastModified" : ISODate("2019-12-29T13:09:08.872Z") }
{ "_id" : ObjectId("5e089a29667bbe281bb9a88f"), "item" : "postcard", "status" : "P", "size" : { "h" : 10, "w" : 15.25, "uom" : "in" }, "instock" : [ { "warehouse" : "B", "qty" : 15 }, { "warehouse" : "C", "qty" : 35 } ], "lastModified" : ISODate("2019-12-29T13:09:08.872Z") }
{ "_id" : ObjectId("5e08a1a1667bbe281bb9a890"), "item" : "journal", "status" : "P", "size" : { "h" : 14, "w" : 21, "uom" : "in" }, "instock" : [ { "warehouse" : "A", "qty" : 5 } ], "lastModified" : ISODate("2019-12-29T13:09:08.872Z") }
{ "_id" : ObjectId("5e08a1a1667bbe281bb9a891"), "item" : "notebook", "status" : "P", "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "instock" : [ { "warehouse" : "C", "qty" : 5 } ], "lastModified" : ISODate("2019-12-29T13:09:08.872Z") }
{ "_id" : ObjectId("5e08a1a1667bbe281bb9a893"), "item" : "planner", "status" : "P", "size" : { "h" : 22.85, "w" : 30, "uom" : "in" }, "instock" : [ { "warehouse" : "A", "qty" : 40 } ], "lastModified" : ISODate("2019-12-29T13:09:08.872Z") }
{ "_id" : ObjectId("5e08a1a1667bbe281bb9a894"), "item" : "postcard", "status" : "P", "size" : { "h" : 10, "w" : 15.25, "uom" : "in" }, "instock" : [ { "warehouse" : "B", "qty" : 15 }, { "warehouse" : "C", "qty" : 35 } ], "lastModified" : ISODate("2019-12-29T13:09:08.872Z") }
{ "_id" : ObjectId("5e08a1f1667bbe281bb9a895"), "item" : "journal", "status" : "P", "size" : { "h" : 14, "w" : 21, "uom" : "in" }, "instock" : [ { "warehouse" : "A", "qty" : 5 } ], "lastModified" : ISODate("2019-12-29T13:09:08.872Z") }
>

替换文档

注意，_id字段无法替换

> db.inventory.find({ item: "paper"})
{ "_id" : ObjectId("5e089a29667bbe281bb9a88d"), "item" : "paper", "status" : "P", "size" : { "h" : 8.5, "w" : 11, "uom" : "cm" }, "instock" : [ { "warehouse" : "A", "qty" : 60 } ], "lastModified" : ISODate("2019-12-29T12:58:35.907Z") }
{ "_id" : ObjectId("5e08a1a1667bbe281bb9a892"), "item" : "paper", "status" : "D", "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "instock" : [ { "warehouse" : "A", "qty" : 60 } ] }
> db.inventory.replaceOne(
   { item: "paper" },
   { item: "paper", instock: [ { warehouse: "A", qty: 60 }, { warehouse: "B", qty: 40 } ] }
)
> db.inventory.find({ item: "paper"})
{ "_id" : ObjectId("5e089a29667bbe281bb9a88d"), "item" : "paper", "instock" : [ { "warehouse" : "A", "qty" : 60 }, { "warehouse" : "B", "qty" : 40 } ] }
{ "_id" : ObjectId("5e08a1a1667bbe281bb9a892"), "item" : "paper", "status" : "D", "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "instock" : [ { "warehouse" : "A", "qty" : 60 } ] }
>

删除数据

多个删除

> db.inventory.deleteMany( { status: "P" } )
{ "acknowledged" : true, "deletedCount" : 9 }
> db.inventory.find( {} )
{ "_id" : 1, "item" : null }
{ "_id" : 2 }
{ "_id" : ObjectId("5e089a29667bbe281bb9a88d"), "item" : "paper", "instock" : [ { "warehouse" : "A", "qty" : 60 }, { "warehouse" : "B", "qty" : 40 } ] }
>

注意，注意，如果你不写条件，下面这个操作将删除所有文档

db.inventory.deleteMany({})

单个删除

> db.inventory.deleteOne( { status: "D" } )
{ "acknowledged" : true, "deletedCount" : 1 }
> db.inventory.find( { status: "D" } )
>

删除表

只在mongoshell下可用

> db.inventory.drop()
true

删除数据库

只在mongoshell下可用，删库跑路，删除当前数据库

> show dbs
admin   0.000GB
config  0.000GB
foo     0.000GB
local   0.000GB
mock    0.047GB
> use foo
switched to db foo
> db.dropDatabase()
{ "dropped" : "foo", "ok" : 1 }
> show dbs
admin   0.000GB
config  0.000GB
local   0.000GB
mock    0.047GB
>

总结

今天完成CRUD所有基础操作的复习，当然还有很多高级的内容，先不纠结，先完成课程。明天继续复习聚合查询部分。