.NET Magazine國際中文電子雜誌
作 者：許薰尹
審 稿：張智凱
文章編號：N160617203
出刊日期：2016/6/29
資料庫：MongoDB

本篇文章將延續前一篇《MongoDB入門 (1)》文章的內容，介紹MongoDB互動式介面中的常用資料查詢指令，以及管理資料庫的指令。

 

使用and查詢

若篩選條件有多個，你可以使用and查詢指定要滿足兩項以上條件的文件才要查詢出來。例如以下範例設定查詢條件：「borough」的值為「Bronx」；且「'cuisine'」是「'Bakery'」，以及「name」為「Morris Park Bake Shop」三項條件同時成立的資料：

 

使用or查詢

使用$or運算子，需要使用一個陣列，來指定多個篩選條件，只要滿足任一篩選條件的文件，都會被回傳。以下範例程式碼第一個句子是And查詢，回傳的資料筆數為1：

> db.restaurants.find({$or: [{'borough':'Bronx', 'cuisine':'Bakery','name':'Morris Park Bake Shop' }]} ).count()

1

以下範例使用$or運算子找出「borough」欄位的值為「Bronx」；或「'cuisine'」欄位的值是「'Bakery'」，或「name」欄位的值為「Morris Park Bake Shop」，任一條件成立的文件筆數：

> db.restaurants.find({$or: [{'borough':'Bronx'},{ 'cuisine':'Bakery'},{'name':'Morris Park Bake Shop' }]} ).count()

2958

我們也可以搭配Projection物件，指定要印出的資料欄位，以下指令設定只要印出「borough」、「cuisine」與「name」欄位的內容：

> db.restaurants.find({$or: [{'borough':'Bronx'},{ 'cuisine':'Bakery'},{'name':'Morris Park Bake Shop' }]} ,{"_id":0,'borough':1,'cuisine':1, 'name':1})

{ "borough" : "Bronx", "cuisine" : "Bakery", "name" : "Morris Park Bake Shop" }

{ "borough" : "Bronx", "cuisine" : "American ", "name" : "Wild Asia" }

{ "borough" : "Bronx", "cuisine" : "Ice Cream, Gelato, Yogurt, Ices", "name" : "Carvel Ice Cream" }

{ "borough" : "Manhattan", "cuisine" : "Bakery", "name" : "Olive'S" }

{ "borough" : "Bronx", "cuisine" : "Chinese", "name" : "Happy Garden" }

{ "borough" : "Bronx", "cuisine" : "Chinese", "name" : "Happy Garden" }

{ "borough" : "Bronx", "cuisine" : "American ", "name" : "Manhem Club" }

{ "borough" : "Bronx", "cuisine" : "American ", "name" : "The New Starling Athletic Club Of The Bronx" }

{ "borough" : "Bronx", "cuisine" : "American ", "name" : "Yankee Tavern" }

{ "borough" : "Bronx", "cuisine" : "Irish", "name" : "Mcdwyers Pub" }

…略

 

指定排序

叫用sort()方法可以將文件排序之後再回傳，以下範例設定「{name:1}」，將根據name欄位，由小到大排序：

> db.restaurants.find({$or: [{'borough':'Bronx'},{ 'cuisine':'Bakery'},{'name':'Morris Park Bake Shop' }]} ,{"_id":0,'borough':1,'cuisine':1, 'name':1}).sort({name:1})

{ "borough" : "Bronx", "cuisine" : "Other", "name" : "" }

{ "borough" : "Bronx", "cuisine" : "Other", "name" : "" }

{ "borough" : "Bronx", "cuisine" : "Other", "name" : "" }

{ "borough" : "Bronx", "cuisine" : "Other", "name" : "" }

{ "borough" : "Bronx", "cuisine" : "Other", "name" : "" }

{ "borough" : "Bronx", "cuisine" : "Other", "name" : "" }

{ "borough" : "Bronx", "cuisine" : "Other", "name" : "" }

{ "borough" : "Bronx", "cuisine" : "Other", "name" : "" }

{ "borough" : "Bronx", "cuisine" : "Other", "name" : "" }

{ "borough" : "Bronx", "cuisine" : "Other", "name" : "" }

{ "borough" : "Bronx", "cuisine" : "Other", "name" : "" }

{ "borough" : "Bronx", "cuisine" : "Other", "name" : "" }

{ "borough" : "Bronx", "cuisine" : "Other", "name" : "" }

{ "borough" : "Bronx", "cuisine" : "Other", "name" : "" }

{ "borough" : "Bronx", "cuisine" : "Other", "name" : "" }

{ "borough" : "Bronx", "cuisine" : "Other", "name" : "" }

{ "borough" : "Bronx", "cuisine" : "Pizza/Italian", "name" : "#1 Me. Nick'S" }

{ "borough" : "Bronx", "cuisine" : "Latin (Cuban, Dominican, Puerto Rican, South & Central American)", "name" : "#1 Sabor Latino Restaurant" }

{ "borough" : "Bronx", "cuisine" : "Pizza", "name" : "$1.25 Pizza" }

{ "borough" : "Bronx", "cuisine" : "American ", "name" : "1 Banana Queen" }

以下略

以下範例指定「{name:1,borough:1}」，表示排序的第一優先順序欄位為name，第二排序優先順序欄為borough：

> db.restaurants.find({$or: [{ 'cuisine':'Bakery'},{'name':'Morris Park Bake Shop' }]} ,{"_id":0,'borough':1,'cuisine':1, 'name':1}).sort({name:1,borough:1})

{ "borough" : "Manhattan", "cuisine" : "Bakery", "name" : "1 Stop Patty Shop" }

{ "borough" : "Bronx", "cuisine" : "Bakery", "name" : "1617-A National Bakery" }

{ "borough" : "Manhattan", "cuisine" : "Bakery", "name" : "162 Eb Corp Bakery" }

{ "borough" : "Brooklyn", "cuisine" : "Bakery", "name" : "18 Bakery" }

{ "borough" : "Brooklyn", "cuisine" : "Bakery", "name" : "4618 Bakery" }

{ "borough" : "Manhattan", "cuisine" : "Bakery", "name" : "5 Estrella Bakery" }

{ "borough" : "Queens", "cuisine" : "Bakery", "name" : "51St Bakery And Cafe" }

{ "borough" : "Manhattan", "cuisine" : "Bakery", "name" : "80 Riverside Cafe" }

{ "borough" : "Brooklyn", "cuisine" : "Bakery", "name" : "86 Best Bakery" }

{ "borough" : "Queens", "cuisine" : "Bakery", "name" : "88 Canteen" }

{ "borough" : "Brooklyn", "cuisine" : "Bakery", "name" : "A & A Bake Shop" }

{ "borough" : "Queens", "cuisine" : "Bakery", "name" : "A Love For Cakes" }

{ "borough" : "Brooklyn", "cuisine" : "Bakery", "name" : "Abc Bakery" }

{ "borough" : "Queens", "cuisine" : "Bakery", "name" : "Abou Bakery" }

{ "borough" : "Brooklyn", "cuisine" : "Bakery", "name" : "Abu'S Homestyle Bakery" }

{ "borough" : "Staten Island", "cuisine" : "Bakery", "name" : "Alfonso'S Pastry Shoppe" }

{ "borough" : "Staten Island", "cuisine" : "Bakery", "name" : "Alfonso'S Pastry Shoppe" }

{ "borough" : "Staten Island", "cuisine" : "Bakery", "name" : "Alfonso'S Pastry Shoppe" }

{ "borough" : "Brooklyn", "cuisine" : "Bakery", "name" : "Aliotta Bake Shop" }

{ "borough" : "Brooklyn", "cuisine" : "Bakery", "name" : "Allan'S Bakery" }

以下略

若設定「-1」代表降冪排序，由大排到小：

> db.restaurants.find({$or: [{ 'cuisine':'Bakery'},{'name':'Morris Park Bake Shop' }]} ,{"_id":0,'borough':1,'cuisine':1, 'name':1}).sort({name:-1,borough:1})

{ "borough" : "Bronx", "cuisine" : "Bakery", "name" : "Zaro'S Bread Basket" }

{ "borough" : "Manhattan", "cuisine" : "Bakery", "name" : "Zaro'S Bread Basket" }

{ "borough" : "Manhattan", "cuisine" : "Bakery", "name" : "Zaro'S Bread Basket" }

{ "borough" : "Manhattan", "cuisine" : "Bakery", "name" : "Zaro'S Bread Basket" }

{ "borough" : "Manhattan", "cuisine" : "Bakery", "name" : "Zaro'S Bread Basket" }

{ "borough" : "Manhattan", "cuisine" : "Bakery", "name" : "Zaro'S Bread Basket" }

{ "borough" : "Manhattan", "cuisine" : "Bakery", "name" : "Zaro'S Bakery" }

{ "borough" : "Brooklyn", "cuisine" : "Bakery", "name" : "Yummy Bakery" }

{ "borough" : "Brooklyn", "cuisine" : "Bakery", "name" : "Yu King Bakery" }

{ "borough" : "Brooklyn", "cuisine" : "Bakery", "name" : "Your Bakery" }

{ "borough" : "Queens", "cuisine" : "Bakery", "name" : "Yeh'S Bakery" }

{ "borough" : "Queens", "cuisine" : "Bakery", "name" : "Yee Mei Fong Taiwan Bakery" }

 

使用比較運算子

MongoDB提供多種比較運算子：

• $gt：大於。
• $gte：大於等於。
• $lt：小於。
• $lte：小於等於。
• $ne：不等於。

要特別注意，在命令提示字元之中，運算子（Operators）的大小寫視為不同。

 

使用$gt運算子

$gt運算子用於大於的比較。以下範例找出「grades.score」大於「90」的文件個數：

> db.restaurants.find({'grades.score':{$gt:90}}).count()

4

以下範例找出「grades.score」大於「100」的文件個數：

> db.restaurants.find({'grades.score':{$gt:100}}).count()

1

 

使用$lt運算子

以下範例找出「grades.score」小於「100」的文件個數：

> db.restaurants.find({'grades.score':{$lt:0}},{'grades.score':1}).count()

13

以下範例找出「grades.score」小於「5」的文件個數：

> db.restaurants.find({'grades.score':{$lt:5}},{'grades.score':1}).count()

8038

 

使用$ne運算子

$ne運算子用來排除具有特定值的document。以下範例找尋「'restaurant_id」欄位不等於「30112340」的document個數：

> db.restaurants.find({'restaurant_id':'30112340'}).count()

1

> db.restaurants.find({'restaurant_id':{$ne:'30112340'}}).count()

25358

> db.restaurants.find().count()

25359

 

常用的管理資料庫命令

MongoDB也提供一些資料庫管理的命令，例如想要建立資料庫，但當下myTestDb資料庫不存在時，可以使用use語法，後接想新建立的資料庫名稱：

> use myTestDb

switched to db myTestDb

此時若使用show dbs指令，並不會看到myTestDb資料庫，這是因為資料庫尚未被建立：

> show dbs

local 0.000GB

test 0.005GB

若此時，我們叫用insert()方法，新增一筆資料到集合，指定集合名稱為「employees」，資料包含name與age兩個欄位：

> db.employees.insert( { name: "mary", age: 15 } )

WriteResult({ "nInserted" : 1 })

就會自動建立資料庫，以及集合，此時再使用show指令查詢資料庫已建立：

> show dbs

local 0.000GB

myTestDb 0.000GB

test 0.005GB

查詢建立的集合：

> show collections

employees

查詢新建立的文件，每一份文件會自動加上Object Id：

> db.employees.find()

{ "_id" : ObjectId("573ae34df7913cee2e20142b"), "name" : "mary", "age" : 15 }