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同义
{"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()
{"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
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
}
]
}
