MongoDB University笔记总结-M001_Chapter 4 Advanced CRUD Operations

Source: MongoDB University   https://university.mongodb.com/

Course: M001 MongoDB bascis 

12/13/2020 Review

【Chapter4】: Advanced CRUD Operations

Key points:

1.Query Operators - Comparision

2.Query Operators - Logic

3.Expressive Query Operator

4.Array Operators

5.Array Operators and Projection

6.Array Operators and Sub-Documents

 

 

#--首先连接数据库

连接MongoDB

1.在mongo shell中连接数据库

1）cmd界面输入下面这行连接数据库，输入账号密码

mongo "mongodb+srv://sandbox.*****.mongodb.net/<dbname>" --username m001-student

2）连接成功，即可在命令行输入语句操作数据。

#-----

 

1.Query Operators - Comparision

Comparison Operators:

$eq   = Equal to

$neq  = Not EQual to

$gt  > Greater Than

$lt < Less Than

$gte >= Greater Than or Equal to

$lte <= Less Than or Equal to

语法如下：

{<filed> :{ <operator> :<value>} }

 

例：

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

在trips表中寻找，tripduration小于70，并且 usertype 不等于 Subscriber的数据。

 

2.Query Operators - Logic

Logic operators:

$and  匹配所有的 Match all the specified query clauses

$or  至少有一个  at least one of the query clauses is matched

$nor  fail to math both given clauses

$not 取反  Negates the query requirement

 

语法如下：

1）$and  / $or / $nor语法

{<operator>:[{statement1},{statement2},....]}

2）$not 语法

{$not:{statement}}

 

Note:

$and 是默认的operator,

例1： 查找sector为Mobile Food Vendor - 881,result是Warning的数据

{sector: "Mobile Food Vendor - 881",result : "Warning"}

等同于：

{"$and":[{sector: "Mobile Food Vendor - 881",result : "Warning"}]}

 

例2：查找学生id大于25且小于100的数据

{"$and":[{"student_id":{"$gt":25}},{"student_id":{"$lt":100}}]}

等同于：

{"student_id":{"$gt":25}},{"student_id":{"$lt":100}}

等同于：

{"student_id":{"$gt":25,"$lt":100}}  //这种形式更为简便

 

例3：查找ariplanes是CR2或者A81，并在KNZ机场起飞或者离开的数据：

db.routes.find({ "$and":

                                     [ { "$or" :[ { "dst_airport": "KZN" }, { "src_airport": "KZN" } ] },

                                       { "$or" :[ { "airplane": "CR2" }, { "airplane": "A81" } ] }

                                      ]

                        }).pretty()

 

3.Expressive Query Operator

$expr的用法，允许在query中使用聚合表达式

$expr allows the use of aggregation expressions within the query language, $expr allows us to use variables and conditional statements.

语法如下：

{$expr:{<expression>}}

 

$符号的作用：

$denote the use of an operator, 用来表示一个operator

$addressing the field value.可以用来访问数据名称

 

例：查找trips表格中，end station id和 start station id 相同的数据的行数

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

 

例：查找trips表格中，end station id和 start station id 相同的，并且tripduration 列大于1200的数行数

db.trips.find({ "$expr": {

                                "$and": [

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

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

                                             ]

                                       }

                        }).count()

 

例: 查找所有的雇员比去年多的公司 Find all the companies that have more employees than the year in which they were founded?

db.companies.find({"$expr":{"$lt":["$founded_year","$number_of_employees"]}}).count()

db.companies.find({"$expr":{"$gt":["$number_of_employees","$founded_year"]}}).count()

以上两种都正确,分别用到了$lt (less than）和 $gt (greater than)的比较。

 

例:在sample_trainiong.companies这个表的数据中，有多少数据的permalink与twitter_username相同。How many companies in the sample_training.companies collection have the same permalink as their twitter_username?

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

注意：

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

1.两个变量 $permalink与 $twitter_username之间用逗号（,）连接，而非分号(:)

2.分号后面，$permalink与 $twitter_username 前后用方括号[ ]包围，而非{ }

 

4. Array Operators

对数组进行操作的符号。

{<array filed>:{"$size":<number>}}

Returns a cursor with all documents where the specified array filed is exactly the given length.

$size用于返回指定array大小的数据。

 

{<array field>：{"$all":<array>}}

Returns a cursor with all documents in which the specified array field contains all the given elements regardless of their order in the array.

$all返回包含指定array中全部元素的数据。

 

例：返回listingAndReviews表格中，amenities列（amentites是个array)中，有20个元素，且都必须包含指定的元素 [ "Internet", "Wifi", "Kitchen", "Heating", "Family/kid friendly", "Washer", "Dryer", "Essentials", "Shampoo", "Hangers", "Hair dryer", "Iron", "Laptop friendly workspace" ] 的数据。

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()

 

Lab1:

What is the name of the listing in the sample_airbnb.listingsAndReviews dataset that accommodates more than 6 people and has exactly 50 reviews?

结果是：Sunset Beach Lodge Retreat

db.listingsAndReviews.find(

                                         {"$and":[

                                                       {"reviews": {"$size":50}},

                                                       {"accommodates": {"$gt":6}}

                                                        ]

                                           }).pretty()

或者：

db.listingsAndReviews.find({"reviews": {"$size":50},

                                              "accommodates": {"$gt":6}}).pretty()   

第二种方法更为简便

 

Lab2:

Using the sample_airbnb.listingsAndReviews collection find out how many documents have the "property_type" "House", and include "Changing table" as one of the "amenities"?

db.listingsAndReviews.find({"$and":[{"property_type":"House"},{"amenities":{ "$all": ["Changing table"]}}]}).count()  //$and可省略

 

quiz:

Which of the following queries will return all listings that have "Free parking on premises", "Air conditioning", and "Wifi" as part of their amenities, and have at least 2 bedrooms in the sample_airbnb.listingsAndReviews collection?

db.listingsAndReviews.find(
                                            { "amenities":

                                                                    { "$all": [ "Free parking on premises", "Wifi", "Air conditioning" ] },

                                                "bedrooms":

                                                                      { "$gte":  2 } }

                                             ).pretty()

 

5.Array Operators and Projection

用来显示指定数据列，这样使得查询的结果更加明确。

语法如下:

db.<collection>.find({<query>,{<projection>}})

1- include the field

0-exclude the field

Use only 1s or only 0s,

只可以全部使用1或者全部0，不可以在一个query语句中混合使用。混合使用只有一种特例，就是"_id" =0

db.<collection>.find({<query>,{<field1>:1,<field2>:1}) // filed1和filed2在结果中显示，其余均不显示

or

db.<collection>.find({<query>,{<field1>:0,<field2>:0}) // filed1和filed2在结果中不显示，其余均显示

exception:

db.<collection>.find({<query>,{<field1>:1,"_id":0}) // 1和0混合使用的唯一特例，"_id"=0,其余全部1或全部0 

 

例：查找sample_airbnb库中listingsAndReviews表中，amenties列包含wifi的价格数据。

db.listingsAndReviews.find({ "amenities": "Wifi" }, { "price": 1, "_id": 0 }).pretty()
"price"=1 表示只显示price，这个例子中 0 和1 混合使用，该情况下，"_id" = 0允许出现

MongoDB Enterprise atlas-mgr67a-shard-0:PRIMARY> db.listingsAndReviews.find({ "amenities": "Wifi" }, { "price": 1, "_id": 0 }).pretty()
{ "price" : NumberDecimal("80.00") }
{ "price" : NumberDecimal("317.00") }
{ "price" : NumberDecimal("115.00") }
{ "price" : NumberDecimal("40.00") }
{ "price" : NumberDecimal("701.00") }
{ "price" : NumberDecimal("135.00") }
{ "price" : NumberDecimal("119.00") }
{ "price" : NumberDecimal("527.00") }
{ "price" : NumberDecimal("250.00") }
{ "price" : NumberDecimal("50.00") }
{ "price" : NumberDecimal("205.00") }
{ "price" : NumberDecimal("43.00") }
{ "price" : NumberDecimal("140.00") }
{ "price" : NumberDecimal("858.00") }
{ "price" : NumberDecimal("361.00") }
{ "price" : NumberDecimal("50.00") }
{ "price" : NumberDecimal("30.00") }
{ "price" : NumberDecimal("181.00") }
{ "price" : NumberDecimal("181.00") }
{ "price" : NumberDecimal("58.00") }
Type "it" for more

 

如何在一个array中查找指定的数据

$elemMatch  //必须在array中才能使用

语法如下：

db.<collection>.find({<query>,{<projection>})

{<field>:{"$elemMatch":{<field>:<value>}}}  Matches documents that contain an array filed with at least one element that matches the specified query criteria

 

例子：查找grades表中，班级id是431，并且某项目分数大于85的的成绩。

grades表的结构如下：

数据由student_id，scores和class_id组成的，且scores是一个array，包含着多个类型，exam，quiz，homework等。

MongoDB Enterprise atlas-mgr67a-shard-0:PRIMARY> db.grades.findOne()
{
        "_id" : ObjectId("56d5f7eb604eb380b0d8d8ce"),
        "student_id" : 0,
        "scores" : [
                {
                        "type" : "exam",
                        "score" : 78.40446309504266
                },
                {
                        "type" : "quiz",
                        "score" : 73.36224783231339
                },
                {
                        "type" : "homework",
                        "score" : 46.980982486720535
                },
                {
                        "type" : "homework",
                        "score" : 76.67556138656222
                }
        ],
        "class_id" : 339
}

查找语句如下：

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

$elemMatch用来在array中进行条件匹配，这样我们可以查到符合条件的成绩，如果某一条数据中，没有符合的数据，则只返回_id.

MongoDB Enterprise atlas-mgr67a-shard-0:PRIMARY>  db.grades.find({"class_id":431},{"scores":{"$elemMatch":{"score":{"$gt":85}}}}).pretty()
{ "_id" : ObjectId("56d5f7eb604eb380b0d8d8fb") }
{ "_id" : ObjectId("56d5f7eb604eb380b0d8dbf2") }
{
        "_id" : ObjectId("56d5f7eb604eb380b0d8dca5"),
        "scores" : [
                {
                        "type" : "homework",
                        "score" : 96.91641379652361
                }
        ]
}
{
        "_id" : ObjectId("56d5f7eb604eb380b0d8de16"),
        "scores" : [
                {
                        "type" : "exam",
                        "score" : 86.41243160598542
                }
        ]
}
{
        "_id" : ObjectId("56d5f7eb604eb380b0d8e640"),
        "scores" : [
                {
                        "type" : "exam",
                        "score" : 86.58727609342327
                }
        ]
}
{
        "_id" : ObjectId("56d5f7eb604eb380b0d8e789"),
        "scores" : [
                {
                        "type" : "quiz",
                        "score" : 88.34543956073009
                }
        ]
}
{ "_id" : ObjectId("56d5f7eb604eb380b0d8e7f6") }
{ "_id" : ObjectId("56d5f7eb604eb380b0d8e95c") }
{
        "_id" : ObjectId("56d5f7eb604eb380b0d8eb0b"),
        "scores" : [
                {
                        "type" : "exam",
                        "score" : 88.38390090500901
                }
        ]
}
{ "_id" : ObjectId("56d5f7eb604eb380b0d8ebbe") }
{
        "_id" : ObjectId("56d5f7ec604eb380b0d8ed51"),
        "scores" : [
                {
                        "type" : "quiz",
                        "score" : 96.39610778554403
                }
        ]
}
{
        "_id" : ObjectId("56d5f7ec604eb380b0d8ef7d"),
        "scores" : [
                {
                        "type" : "quiz",
                        "score" : 91.36344285996199
                }
        ]
}
{
        "_id" : ObjectId("56d5f7ec604eb380b0d8f042"),
        "scores" : [
                {
                        "type" : "exam",
                        "score" : 99.20557464658
                }
        ]
}
{
        "_id" : ObjectId("56d5f7ec604eb380b0d8f26d"),
        "scores" : [
                {
                        "type" : "exam",
                        "score" : 91.97457411016345
                }
        ]
}
{
        "_id" : ObjectId("56d5f7ec604eb380b0d8f43c"),
        "scores" : [
                {
                        "type" : "exam",
                        "score" : 93.95060569368262
                }
        ]
}
{
        "_id" : ObjectId("56d5f7ec604eb380b0d8f76c"),
        "scores" : [
                {
                        "type" : "homework",
                        "score" : 93.31790089774611
                }
        ]
}
{ "_id" : ObjectId("56d5f7ec604eb380b0d8f8ea") }
{ "_id" : ObjectId("56d5f7ec604eb380b0d8fb98") }
{ "_id" : ObjectId("56d5f7ec604eb380b0d8fc0c") }
{ "_id" : ObjectId("56d5f7ec604eb380b0d8fd69") }
Type "it" for more

 

例：

查找grades表中，scores中存在extra credit元素的数据

db.grades.find({ "scores": { "$elemMatch": { "type": "extra credit" } } }).pretty()

查找companies表中，offices中存在Seattle的数据

db.companies.find({"offices":{"$elemMatch":{"city":"Seatle"}}}).count()

 

 

6.Array Operators and Sub-Documents

1) $regex   正则表达式，用来匹配字符串

详细参考:https://docs.mongodb.com/manual/reference/operator/query/regex/index.html

 

2) dot-notation to specify the address of nested elements in a document

To use dot-notation in arrays specify the position of the element in the array 用点操作符来指定array中元素的位置

 

例：在companies表中，relationships的类型是array，里面包含着很多个元素，由is_past, title,和person组成，查找relationships的第一个object中last name是zuckerbery并且title是CEO的数据。

companies表结构如下：

 

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

//relationships.0.person.first_name访问访问relationships的第一个object下的person元素下的first_name的内容用到了dot-notation, 0 为一个元素。

//relationships.0.title 访问relationships的第一个object下的title元素

//"$regex": "CEO"  字符串匹配，用来查找title中含有CEO字符的数据

// "name": 1  代表只显示name这一列元素

MongoDB Enterprise atlas-mgr67a-shard-0:PRIMARY> db.companies.find({ "relationships.0.person.first_name": "Mark", "relationships.0.title": { "$regex": "CEO" } }, { "name": 1 }).pretty()
{ "_id" : ObjectId("52cdef7c4bab8bd675297d8e"), "name" : "Facebook" }
{ "_id" : ObjectId("52cdef7c4bab8bd675297dd3"), "name" : "iSkoot" }
{ "_id" : ObjectId("52cdef7c4bab8bd675297efc"), "name" : "Helium" }
{ "_id" : ObjectId("52cdef7c4bab8bd675297fb5"), "name" : "Feedjit" }
{ "_id" : ObjectId("52cdef7c4bab8bd675297fbf"), "name" : "Avvo" }
{ "_id" : ObjectId("52cdef7c4bab8bd675298077"), "name" : "eXelate" }
{ "_id" : ObjectId("52cdef7c4bab8bd6752980ed"), "name" : "Bloglines" }
{ "_id" : ObjectId("52cdef7c4bab8bd67529838c"), "name" : "HipLogic" }
{ "_id" : ObjectId("52cdef7c4bab8bd6752983f1"), "name" : "Surf Canyon" }
{ "_id" : ObjectId("52cdef7c4bab8bd675298543"), "name" : "SignalDemand" }
{ "_id" : ObjectId("52cdef7c4bab8bd675298655"), "name" : "Wiredset" }
{ "_id" : ObjectId("52cdef7d4bab8bd675298c18"), "name" : "Market Sentinel" }
{ "_id" : ObjectId("52cdef7d4bab8bd675298e2b"), "name" : "Courtland Brooks" }
{
        "_id" : ObjectId("52cdef7d4bab8bd675298ec2"),
        "name" : "Are You Watching This?!"
}
{ "_id" : ObjectId("52cdef7d4bab8bd675298ff0"), "name" : "Gay Ad Network" }
{ "_id" : ObjectId("52cdef7d4bab8bd67529906d"), "name" : "SNASM" }
{ "_id" : ObjectId("52cdef7d4bab8bd67529914b"), "name" : "Bitly" }
{ "_id" : ObjectId("52cdef7d4bab8bd6752991fa"), "name" : "N-Play" }
{ "_id" : ObjectId("52cdef7d4bab8bd6752992c2"), "name" : "SAVO" }
{ "_id" : ObjectId("52cdef7d4bab8bd6752993d7"), "name" : "AirMe" }
Type "it" for more