M001: MongoDB Basics ——MongoDB sql语句练习（1）

M001: MongoDB Basics ——MongoDB sql语句练习（1）
Free MongoDB Official Courses | MongoDB University

Navigate to Atlas.

Your Chapter 3 IDE space - MongoDB University
- mongo "mongodb+srv://*********/myFirstDatabase" --username m*****t
- show dbs
- use sample_training
- show collections
- db.zips.find({"state":"NY"})
- It
- db.zips.find({"state":"NY","city":"ALBANY"})
- db.zips.find({"state":"NY"}).count()
- db.zips.find({"state":"NY","city":"ALBANY"}).count()
- db.zips.find({"state":"NY","city":"ALBANY"}).pretty()
- db.inspections.findOne()
db.inspections.insert({

...         "_id" : ObjectId("56d61033a378eccde8a8354f"),

...         "id" : "10021-2015-ENFO",

...         "certificate_number" : 9278806,

...         "business_name" : "ATLIXCO DELI GROCERY INC.",

...         "date" : "Feb 20 2015",

...         "result" : "No Violation Issued",

...        "sector" : "Cigarette Retail Dealer - 127",

...         "address" : {

...                 "city" : "RIDGEWOOD",

...                 "zip" : 11385,

...                 "street" : "MENAHAN ST",

...                 "number" : 1712

...         }

... })

db.inspections.insert([{"test":1},{"test":2},{"test":3}])

db.inspections.insert([{"_id":1,"test":1},{"_id":1,"test":2},{"_id":3,"test":3}],{"ordered":false})

db.inspections.find({

...         "id" : "10021-2015-ENFO",

...         "certificate_number" : 9278806

... }).pretty()

db.zips.updateMany({"city":"HUDSON"},{"$inc":{"pop":10}})

db.zips.update({"zip":"12534"},{"$set":{"pop":17630}})

db.grades.updateOne({"class_id":339,"student_id":250},{"$push":{"score":{"type":"extra certification","score":99}}})

{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

db.grades.update({"class_id":339,"student_id":250},{"$push":{"score":{"type":"extra certification","score":88}}})

# 注意：=>删除之前需确认（select，log），否则永久失去了书数据

# delete document: deleteMany(),deleteOne()

db.inspections.deleteMany({"test":1})

db.inspections.deleteOne({ "test": 3 })

# delete colllection: drop

db.inspection.drop()

# operator

# {"tripduration":{"$lte":70},"usertype":{"$ne":"Subscriber"}}

db.trips.find({"tripduration":{"$lte":70},"usertype":{"$eq":"Customer"}}).pretty()

# {"usertype":{"$eq":"Customer"}} 和{"usertype":"Customer"} 结果相同

db.trips.find({"tripduration":{"$lte":70},"usertype":"Customer"}).pretty()

{"$nor":[

{"result":"No Violation Issued"},

{"result":"Violation Issued"},

{"result":"Pass"},

{"result":"Fail"}

]}

db.inspections.find({"$nor":[{"result":"No Violation Issued"},{"result":"Violation Issued"},{"result":"Pass"},{"result":"Fail"}]}).pretty()

{"$and":[

{"$or":[

{"src_airport":"KZN"},

{"dst_airport":"KZN"}

]},

{"$or":[

{"airplane":"CR2"},

{"airplane":"A81"}

]}

]}

db.routes.find({"$and":[{"$or":[{"src_airport":"KZN"},{"dst_airport":"KZN"}]},{"$or":[{"airplane":"CR2"},{"airplane":"A81"}]}]}).pretty()

The same operator:"$or" more than once

No same operator

{"$and":[

{"result":"Out of Business"},

{"sector":"Home Improvement Contractor - 100"}

]}

$and — MongoDB Manual

# 注意：没有{}，没有[] ;与 and同义

{"result":"Out of Business","sector":"Home Improvement Contractor - 100"}

db.collection.find() — MongoDB Manual

db.inspections.find({"result":"Out of Business","sector":"Home Improvement Contractor - 100"}).count()

# 正确

# properties: "pop"的value ：是对应的数据类型：值；或者 operator 对象

db.zips.find({"pop":{"$lt":1000000},"pop":{"$gt":5000}}).count()

# operator：$and 的value 可以是数组

db.zips.find({"$and":[{"pop":{"$lt":1000000}},{"pop":{"$gt":5000}}]}).count()

# 错误

# "unknown operator: $bt"

# greater than；not bigger than

db.zips.find({"pop":{"$lt":1000000},"pop":{"$bt":5000}}).pretty()

# 错误：指定属性

db.zips.find({"pop":{"$lt":1000000},{"$gt":5000}}).pretty()

# 错误

# properties: "pop"的value ：是对应的数据类型：值；或者 operator 对象

# operator：$and 的value 可以是数组

db.zips.find({"pop":[{"$gt":5000},{"$ls":1000000}]})

# 问题描述方式 —— 值得借鉴

{"$or":[

{"$and":[

{"founded_year":2004},

{"$or":[

{"category_code":"web"},

{"category_code":"social"}

]}

]},

{"$and":[

{"founded_month":10},

{"$or":[

{"category_code":"web"},

{"category_code":"social"}

]}

]}

]}

db.companies.find({"$or":[{"$and":[{"founded_year":2004},{"$or":[{"category_code":"web"},{"category_code":"social"}]}]},{"$and":[{"founded_month":10},{"$or":[{"category_code":"web"},{"category_code":"social"}]}]}]}).count()

# 错误写法

{"$expr":{"$eq":["start station id","end station id"]}}

# 正确写法：

# 注意[]. {"$eq":[…]}, 而非{"$eq":{…}}

# 注意$. {"$eq":["$...","$..."]}, 而非 {"$eq":["...","..."]}{"$expr":{"$eq":["$start station id","$end station id"]}}

# 错误：{"$and":[{},{}]}

{"$expr":{

{"$and":[

"$eq":["$start station id","$end station id"],

"$gt":["$tripduration",1000]

]}

}}

# 错误：{"$expr":{}}

{"$expr":{

{"$and":[

{"$eq":["$start station id","$end station id"]},

{"$gt":["$tripduration",1000]}

]}

}}

# 正确

{"$expr":{

"$and":[

{"$eq":["$start station id","$end station id"]},

{"$gt":["$tripduration",1000]}

]

}}

db.trips.find({"$expr":{"$and":[{"$eq":["$start station id","$end station id"]},{"$gt":["$tripduration",1000]}]}}).count()

# 错误。 $expr, 而非$exp

{"$exp":{"$eq":["$permalink","$twitter_username"]}}

# 正确

{"$expr":{"$eq":["$permalink","$twitter_username"]}}

db.companies.find({"$expr":{"$eq":["$permalink","$twitter_username"]}}).count()

# 错误："$Shampoo"表示属性名"Shampoo"的值

{"amenities":"$Shampoo"}

# 错误：表示属性名"amenities"的值为["Shampoo"]（等于关系：array中只有一个值Shampoo）

{"amenities":["Shampoo"]}

# 正确：针对 array类型的查询，array中含有 "Shampoo"（包含关系）

{"amenities":"Shampoo"}

# 等价关系

db.listingsAndReviews.find({"amenities":"Shampoo"}).count()

db.listingsAndReviews.find({"amenities":{"$all":["Shampoo"]}}).count()

# 正确：array 全值匹配，顺序不可调换

{"amenities":["Internet","Wifi","Air conditioning","Free parking on premises","Smoking allowed","Heating","Family/kid friendly","Suitable for events","Washer","Dryer","Fire extinguisher","Essentials","Shampoo","Hangers","Hair dryer","Iron","Laptop friendly workspace","Self check-in","Building staff"]}

# 错误：array 全值匹配，顺序不可调换

{"amenities":["Wifi","Internet","Air conditioning","Free parking on premises","Smoking allowed","Heating","Family/kid friendly","Suitable for events","Washer","Dryer","Fire extinguisher","Essentials","Shampoo","Hangers","Hair dryer","Iron","Laptop friendly workspace","Self check-in","Building staff"]}

# 正确："$all"：array 中属性amenities 包含 "Gym","Internet"两个value值的document

# 顺序无关

{"amenities":{"$all":["Gym","Internet"]}}

db.listingsAndReviews.find({"amenities":{"$all":["Gym","Internet"]}}).count()

# 正确：$size 限制array的length

{"amenities":{"$size":20,"$all":["Gym","Internet"]}}

db.listingsAndReviews.find({"amenities":{"$size":20,"$all":["Gym","Internet"]}}).count()

{"amenities":{"$size":20}}

db.listingsAndReviews.find({"amenities":{"$size":20}}).count()

db.listingsAndReviews.find({"amenities":{"$size":20}}).count()

db.listingsAndReviews.find({"amenities":{"$size":20,"$all":["Internet","Wifi","Kitchen","Heating","Family/kid friendly","Washer","Dryer","Essentials","Shampoo","Hangers","Hair dryer","Iron","Laptop friendly workspace"]}}).pretty()

db.listingsAndReviews.find({"accommodates":{"$gt":6},"reviews":{"$size":50}}).count()

$exists — MongoDB Manual

{"amenities":"Changing table","property_type":{"$exists":true}}

{"amenities":"Changing table","property_type":"House"}

db.listingsAndReviews.find({"amenities":"Changing table","property_type":"House"}).count()

# project: 字段映射

db.listingsAndReviews.find({"amenities":{"$size":20,"$all":["Internet","Gym"]}},{"price":1,"address":1}).pretty()

# 映射正确：only _id 可被排除，when 其他属性皆包含

db.listingsAndReviews.find({"amenities":{"$size":20,"$all":["Internet","Gym"]}},{"price":1,"_id":0}).pretty()

# 映射正确：全部属性为包含

db.listingsAndReviews.find({"amenities":{"$size":20,"$all":["Internet","Gym"]}},{"price":1,"room_type":1}).pretty()

# 映射错误：属性混合：包含、排除 & 被排除的属性并非仅限制为 _id

db.listingsAndReviews.find({"amenities":{"$size":20,"$all":["Internet","Gym"]}},{"price":1,"room_type":0}).pretty()

# elemMatch in filter

# 输出：全部的属性

db.grades.find({"class_id":431,"scores":{"$elemMatch":{"score":{"$gt":85}}}}).count()

# elemMatch in projection

# 输出：部分属性

#1. _id( 默认projection behavior)；

# 2. scores(取决是否符合elemMatch) ：

# if符合elemMatch

# then 全部scores属性（score & type，不止score）

# else 不显示scores属性

# 3. 其他属性不显示

db.grades.find({"class_id":431},{"scores":{"$elemMatch":{"score":{"$gt":85}}}}).pretty()

# array start position is 0

{"relationships.0.person.last_name":"Tanne"}

db.trips.findOne({"start station location.type":"Point"})

# 正确：project

db.companies.find({"relationships.0.person.last_name":"Tanne"},{"name":1}).pretty()

# 错误：project

db.companies.find({{"relationships.0.person.last_name":"Tanne"},{"name":1}}).pretty()

# array 查询：按位置查询

db.companies.find({"relationships.0.person.first_name":"Mark","relationships.0.title":{"$regex":"CEO"}},{"name":1})

# array 查询：按属性查询

{"relationships":{"$elemMatch":{"is_past":true,"person.first_name":"Mark"}}}

db.companies.find({"relationships":{"$elemMatch":{"is_past":true,"person.first_name":"Mark"}}},{"name":1})

Query an Array — MongoDB Manual

$regex — MongoDB Manual

db.listingsAndReviews.find({"amenities":"Wifi"},{"price":1,"address":1,"_id":0}).pretty()

# 错误

db.listingsAndReviews.aggregate("$match":{"amenities":"Wifi"},"$project":{"price":1,"address":1,"_id":0}).pretty()

# 正确

db.listingsAndReviews.aggregate([{$match:{"amenities":"Wifi"}},{$project:{"price":1,"address":1,"_id":0}}]).pretty()

db.listingsAndReviews.aggregate([{"$match":{"amenities":"Wifi"}},{"$project":{"price":1,"address":1,"_id":0}}]).pretty()

db.listingsAndReviews.findOne({},{"address":1,"_id":0})

# aggregate语法错误。需指明操作指令：group，project

db.listingsAndReviews.aggregate({"address":1,"_id":0},{$group:{"_id":"$address.country"}})

# pipleline 顺序问题。pipleline :group 处理之后，输出只有id

db.listingsAndReviews.aggregate([{$group:{"_id":"$address.country"}},{"$project":{"address":1,"_id":0}}])

# 纠正

# $address表示位置

# address表示值

db.listingsAndReviews.aggregate([{$group:{"_id":"$address.country"}},{"$project":{"_id":1}}])

# project错误。 pipeline：group 处理后，输出属性_id不存在

db.listingsAndReviews.aggregate([{$group:{"_id":"$address.country"}},{"$project":{"address":1,"_id":0}}}])

# 正确

db.listingsAndReviews.aggregate([{$group:{"_id":"$address.country"}},{"$project":{"address":1}}])

# group 增加属性，每一条 +1

db.listingsAndReviews.aggregate([{"$project":{"address":1,"_id":0}},{"$group":{"_id":"$address.country","total":{"$sum":1}}}])

=》aggragation： compute， reshape，reorganize

=》data in aggragation pipeline：不会修改、更新数据，通过 cursor

# group 错误

db.listingsAndReviews.aggregate([{"$project":{"room_type":1,"_id":0}},{"$group":{"_id":"room_type","total":{"$sum":1}}}])

# 正确

db.listingsAndReviews.aggregate([{"$project":{"room_type":1,"_id":0}},{"$group":{"_id":"$room_type","total":{"$sum":1}}}])

db.listingsAndReviews.aggregate([ { "$group": { "_id": "$room_type" } }])

# 错误

db.zips.sort({"pop":1}).limit(1).pretty()

# 正确：升序

db.zips.find().sort({"pop":1}).limit(1).pretty()

# 正确：降序

db.zips.find().sort({"pop":1}).limit(1).pretty()

db.zips.find().sort({"pop":1,"city":-1}).limit(10).pretty()

# only limit，但是前面无 sort，则不会保证数据的顺序，可能是任何的部分数据

cursor.limit() — MongoDB Manual

# sort 错误

db.trips.find().sort("birth year":1).limit(1).pretty()

# sort 正确

db.trips.find().sort({"birth year":1}).limit(1).pretty()

db.routes.createIndex({ "src_airport": -1 })

# It doesn't really matter whether the index was created in increasing or decreasing order when it is a simple single-field index.

db.trips.createIndex({"start location id":1,"birth year":1})

# index 错误

db.trips.CreatIndex({"birth year":1})

# Pipeline

[{

    $match: {

        "amenities": "Wifi"

    }

}, {

    $project: {

        "address": 1,

        "price": 1,

        "_id": 0

    }

}, {

    $group: {

        _id: "$address.country",

        count: {

            "$sum": 1

        },

        price: {

            "$sum": "$price"

        }

    }

}, {

    $count: "num_country"

}]

# c#语言

new BsonArray

{

    new BsonDocument("$match",

    new BsonDocument("amenities", "Wifi")),

    new BsonDocument("$project",

    new BsonDocument

        {

            { "address", 1 },

            { "price", 1 },

            { "_id", 0 }

        }),

    new BsonDocument("$group",

    new BsonDocument

        {

            { "_id", "$address.country" },

            { "count",

    new BsonDocument("$sum", 1) },

            { "price",

    new BsonDocument("$sum", "$price") }

        }),

    new BsonDocument("$count", "num_country")

}

# heat map

{

"isFetching": false,

"isOpen": true,

"pipeline": [

    {

      "$match": {

        "price": {

          "$lt": 20000

        }

      }

    },

    {

      "$project": {

        "__alias_0": "$price",

        "__alias_1": "$address.location"

      }

    },

    {

      "$project": {

        "intensity": "$__alias_0",

        "geopoint": "$__alias_1",

        "_id": 0

      }

    },

    {

      "$match": {

        "geopoint.type": "Point",

        "geopoint.coordinates": {

          "$type": "array"

        },

        "geopoint.coordinates.0": {

          "$type": "number",

          "$ne": {

            "$numberDouble": "NaN"

          },

          "$gte": -180,

          "$lte": 180

        },

        "geopoint.coordinates.1": {

          "$type": "number",

          "$ne": {

            "$numberDouble": "NaN"

          },

          "$gte": -90,

          "$lte": 90

        }

      }

    },

    {

      "$limit": 50000

    }

]

}
相关阅读:
来自Jakob Jenkov的Jackson教程
 Linux查看某个端口的连接数
 Linux查看某个进程的线程
 Jackson反序列JSON为实体对象出现：no String-argument constructor/factory method to deserialize from String value的问题
 软路由OpenWrt教程收集（插件开发教程，opkg安装软件教程）
软路由系统收集
 Linux防止“rm -rf /”误删除
 Mac 10.12原生方法对NTFS分区进行读写的配置
 Linux下进行Web服务器压力（并发）测试工具http_load、webbench、ab、Siege、autobench简单使用教程（转）
微服务实施Spring Boot/Spring Cloud中踩过的坑（转）
原文地址：https://www.cnblogs.com/panpanwelcome/p/16316557.html