简单查询

标签：city name ObjectId country db NumberDecimal 查询简单

计划

今天要学习《06 MongoDB基本操作》

Mongo的连接地址格式

mongodb://username:password@host[:port]/database?<options>

基本操作

以下都在shell环境下

查看当前数据库

> db
mock

切换数据库

> use test

查看当前表

> show collections
> show tables

插入一条记录

> db.fruit.insertOne({name: "apple"})
{
        "acknowledged" : true,
        "insertedId" : ObjectId("5dfcae3eebbe93035d7c6b55")
}

插入多条记录

> db.fruit.insertMany([
... {name: "apple"},
... {name: "pear"},
... {name: "orage"}
... ])
{
        "acknowledged" : true,
        "insertedIds" : [
                ObjectId("5dfcb07debbe93035d7c6b56"),
                ObjectId("5dfcb07debbe93035d7c6b57"),
                ObjectId("5dfcb07debbe93035d7c6b58")
        ]
}

检索记录

相当于 select 返回的是游标

> db.fruit.find()
{ "_id" : ObjectId("5dfcae3eebbe93035d7c6b55"), "name" : "apple" }
{ "_id" : ObjectId("5dfcb07debbe93035d7c6b56"), "name" : "apple" }
{ "_id" : ObjectId("5dfcb07debbe93035d7c6b57"), "name" : "pear" }
{ "_id" : ObjectId("5dfcb07debbe93035d7c6b58"), "name" : "orage" }

单条件查询（一个条件）

> db.orders.find({"name":"Destinee Schneider"})
{
    "_id": ObjectId("5dbe7a545368f69de2b4d36e"),
    "street": "493 Hilll Curve",
    "city": "Champlinberg",
    "state": "Texas",
    "country": "Malaysia",
    "zip": "24344-1715",
    "phone": "425.956.7743 x4621",
    "name": "Destinee Schneider",
    "userId": 3573,
    "orderDate": ISODate("2019-03-26T03:20:08.805Z"),
    "status": "created",
    "shippingFee": NumberDecimal("8.00"),
    "orderLines": [{
        "product": "Refined Fresh Tuna",
        "sku": "2057",
        "qty": 25,
        "price": NumberDecimal("56.00"),
        "cost": NumberDecimal("46.48")
    }, {
        "product": "Refined Concrete Ball",
        "sku": "1738",
        "qty": 61,
        "price": NumberDecimal("47.00"),
        "cost": NumberDecimal("47")
    }, {
        "product": "Rustic Granite Towels",
        "sku": "500",
        "qty": 62,
        "price": NumberDecimal("74.00"),
        "cost": NumberDecimal("62.16")
    }, {
        "product": "Refined Rubber Salad",
        "sku": "1400",
        "qty": 73,
        "price": NumberDecimal("93.00"),
        "cost": NumberDecimal("87.42")
    }, {
        "product": "Intelligent Wooden Towels",
        "sku": "5674",
        "qty": 72,
        "price": NumberDecimal("84.00"),
        "cost": NumberDecimal("68.88")
    }, {
        "product": "Refined Steel Bacon",
        "sku": "5009",
        "qty": 8,
        "price": NumberDecimal("53.00"),
        "cost": NumberDecimal("50.35")
    }],
    "total": NumberDecimal("407")
}

多条件 and 查询(1)

> db.orders.find({"country":"Italy","city":"North Elliot"})
{
    "_id": ObjectId("5dbe7a5b50fc769de3e1a0f8"),
    "street": "5769 Von Trail",
    "city": "North Elliot",
    "state": "South Dakota",
    "country": "Italy",
    "zip": "93708-8445",
    "phone": "1-390-726-3544",
    "name": "Alexys Swift",
    "userId": 9628,
    "orderDate": ISODate("2019-07-25T07:21:14.661Z"),
    "status": "completed",
    "shippingFee": NumberDecimal("10.00"),
    "orderLines": [{
        "product": "Refined Plastic Computer",
        "sku": "5065",
        "qty": 50,
        "price": NumberDecimal("82.00"),
        "cost": NumberDecimal("71.34")
    }, {
        "product": "Generic Granite Gloves",
        "sku": "4352",
        "qty": 10,
        "price": NumberDecimal("37.00"),
        "cost": NumberDecimal("32.56")
    }, {
        "product": "Practical Rubber Chicken",
        "sku": "2035",
        "qty": 29,
        "price": NumberDecimal("84.00"),
        "cost": NumberDecimal("68.04")
    }, {
        "product": "Handcrafted Fresh Bike",
        "sku": "2276",
        "qty": 51,
        "price": NumberDecimal("77.00"),
        "cost": NumberDecimal("64.68")
    }, {
        "product": "Sleek Cotton Car",
        "sku": "5257",
        "qty": 6,
        "price": NumberDecimal("94.00"),
        "cost": NumberDecimal("79.9")
    }, {
        "product": "Practical Fresh Mouse",
        "sku": "9593",
        "qty": 67,
        "price": NumberDecimal("98.00"),
        "cost": NumberDecimal("96.04")
    }, {
        "product": "Incredible Plastic Ball",
        "sku": "7804",
        "qty": 22,
        "price": NumberDecimal("45.00"),
        "cost": NumberDecimal("39.15")
    }, {
        "product": "Refined Plastic Bike",
        "sku": "9804",
        "qty": 27,
        "price": NumberDecimal("22.00"),
        "cost": NumberDecimal("18.92")
    }, {
        "product": "Handcrafted Concrete Shoes",
        "sku": "1473",
        "qty": 1,
        "price": NumberDecimal("49.00"),
        "cost": NumberDecimal("44.1")
    }],
    "total": NumberDecimal("588")
}

多条件 and 查询(2)

> db.orders.find({$and: [{"country":"Italy"},{"city":"North Elliot"}]})

多条件 or 查询

> db.orders.find({$or:[{"country":"Italy"},{"city":"North Elliot"}]})

正则表达式查询

> db.orders.find({"country": /^I/})

查询条件

SQL	MQL
a=1	{a: 1}
a<>1	{a: {$ne: 1}}
a>1	{a: {$gt: 1}}
a<1	{a: {$lt: 1}}
a>=1	{a: {$gte: 1}}
a<=1	{a: {$lte: 1}}
a=1 and b=1	{a:1,b:1} 或 {$and: [{a:1},{b:1}]}
a=1 or b=1	{$or:[{a:1},{b:1}]}
a=null	{a: {$exists:false}}
a in (1,2,3)	{a: {$in:[1,2,3]}}
a not in(1,2,3)	{a: {$nin:[1,2,3]}}

搜索子文档

> db.fruit.insertOne({
    name: "apple",
    from:{
        country: "China",
        province: "Guangdon"
    }
})

要查询China

> db.fruit.find({"from.country":"China"})
{ 
    "_id" : ObjectId("5dfcc154d432688fd475d7a4"), 
    "name" : "apple", 
    "from" : {
        "country" : "China", 
        "province" : "Guangdon"
    }
}

查询数组中的值

> db.fruit.insert([
    {
        "name":"Apple",
        "color":["red","green"]
    },
    {
        "name":"Mango",
        "color":["yellow","green"]
    }
])

方式1

> db.fruit.find({"color":"red"})
{ "_id" : ObjectId("5dfcc543f598f9f1c332185f"), "name" : "Apple", "color" : [ "red", "green" ] }

方式2

> db.fruit.find({$or: [{"color":"red"},{"color":"yellow"}]})
{ 
    "_id" : ObjectId("5dfcc2eed432688fd475d7a6"), 
    "name" : "Mango", 
    "color" : [
        "yellow", 
        "green"
    ]
},
{ 
    "_id" : ObjectId("5dfcc543f598f9f1c332185f"), 
    "name" : "Apple", 
    "color" : [
        "red", 
        "green"
    ]
}

查询数组中的对象

> db.movies.insertOne({
    "title": "Raiders of the Lost Ark",
    "filming_locations": [
        {
            "city": "Los Angeles",
            "state":"CA",
            "country": "USA"
        },
        {
            "city": "Rome",
            "state": "Lazio",
            "country": "Italy"
        },
        {
            "city": "Florence",
            "state": "SC",
            "country": "USA"
        }
    ]
})

一个条件

> db.movies.find({
    "filming_locations.city": "Rome"
})
{ 
    "_id" : ObjectId("5dfcc850d432688fd475d7a7"), 
    "title" : "Raiders of the Lost Ark", 
    "filming_locations" : [
        {
            "city" : "Los Angeles", 
            "state" : "CA", 
            "country" : "USA"
        }, 
        {
            "city" : "Rome", 
            "state" : "Lazio", 
            "country" : "Italy"
        }, 
        {
            "city" : "Florence", 
            "state" : "SC", 
            "country" : "USA"
        }
    ]
}

二个条件

这样是啥也查不到的

> db.movies.find({
    "filming_locations.city": "Rome",
    "filming_locaionts.country": "Italy"
})

应该这么写

> db.movies.find({
    "filming_locations": {
        $elemMatch: {
            "city":"Rome",
            "country": "Italy"
        }
    }
})

{ 
    "_id" : ObjectId("5dfcc850d432688fd475d7a7"), 
    "title" : "Raiders of the Lost Ark", 
    "filming_locations" : [
        {
            "city" : "Los Angeles", 
            "state" : "CA", 
            "country" : "USA"
        }, 
        {
            "city" : "Rome", 
            "state" : "Lazio", 
            "country" : "Italy"
        }, 
        {
            "city" : "Florence", 
            "state" : "SC", 
            "country" : "USA"
        }
    ]
}

控制 find 返回的字段

返回所有记录，但只要 title 字段，不要 _id 字段

> db.movies.find({},{"_id":0,"title":1})
{ 
    "title" : "Raiders of the Lost Ark"
}

对 find 返回的结果进行格式化

> db.movies.find().pretty()

要不然在SHELL下面是这样的

{ "_id" : ObjectId("5dfcc850d432688fd475d7a7"), "title" : "Raiders of the Lost Ark", "filming_locations" : [ { "city" : "Los Angeles", "state" : "CA", "country" : "USA" }, { "city" : "Rome", "state" : "Lazio", "country" : "Italy" }, { "city" : "Florence", "state" : "SC", "country" : "USA" } ] }

加完变这样了

{
    "_id" : ObjectId("5dfcc850d432688fd475d7a7"),
    "title" : "Raiders of the Lost Ark",
    "filming_locations" : [
        {
            "city" : "Los Angeles",
            "state" : "CA",
            "country" : "USA"
        },
        {
            "city" : "Rome",
            "state" : "Lazio",
            "country" : "Italy"
        },
        {
            "city" : "Florence",
            "state" : "SC",
            "country" : "USA"
        }
    ]
}

删除文档

删除指定 _id 的记录

> db.fruit.remove({"_id" : ObjectId("5dfcc543f598f9f1c3321860")})

删除指定条件的文档

> db.fruit.remove({"color":"red"})
WriteResult({ "nRemoved" : 2 })

删除所有

> db.movies.remove({})

不给参数会报错

> db.movies.remove()

更新文档

只作用于集合中存在的文档

更新值

将下面的 name 是 apple 的改成 Apple

{ 
    "_id" : ObjectId("5dfcc154d432688fd475d7a4"), 
    "name" : "apple", 
    "from" : {
        "country" : "China", 
        "province" : "Guangdon"
    }
}
{ 
    "_id" : ObjectId("5dfcc2eed432688fd475d7a5"), 
    "name" : "Apple", 
    "color" : [
        "read", 
        "green"
    ]
}
{ 
    "_id" : ObjectId("5dfcc2eed432688fd475d7a6"), 
    "name" : "Mango", 
    "color" : [
        "yellow", 
        "green"
    ]
}

> db.fruit.update({"name": "apple"}, {$set: {"name": "Apple"}})

加个字段

> db.fruit.updateOne({"name":"Mango"},{$set: {"from":"China"}})

更新条件

updateOne 与 updateMany

条件	作用
$push	添加一个对象到数组底部
$pushAll	添加多个对象到数组底部
$pop	从数组底部删除一个对象
$pull	如果满足指定条件，就从数组中删除对象
$pullAll	如果满足任意条件，就从数组中删除对象
$addToSet	如果不存在，就增加值到数组

删除集合 drop

删除了没办法恢复哈

> db.fruit.drop()

删除数据库 dropDatabase

> db.dropDatabase()

标签：city,name,ObjectId,country,db,NumberDecimal,查询,简单
From： https://www.cnblogs.com/lovezhr/p/17823400.html

计划