.NET Magazine國際中文電子雜誌
作 者:許薰尹
審 稿:張智凱
文章編號:N160617203
出刊日期:2016/6/29
資料庫:MongoDB
本篇文章將延續前一篇《MongoDB入門 (1)》文章的內容,介紹MongoDB互動式介面中的常用資料查詢指令,以及管理資料庫的指令。
使用and查詢
若篩選條件有多個,你可以使用and查詢指定要滿足兩項以上條件的文件才要查詢出來。例如以下範例設定查詢條件:「borough」的值為「Bronx」;且「'cuisine'」是「'Bakery'」,以及「name」為「Morris Park Bake Shop」三項條件同時成立的資料:
> db.restaurants.find( {'borough':'Bronx', 'cuisine':'Bakery','name':'Morris Park Bake Shop' } ).count()
1
> db.restaurants.find( {'borough':'Bronx', 'cuisine':'Bakery','name':'Morris Park Bake Shop' } )[0]
{
"_id" : ObjectId("5705e0ae16dfa0ae82083766"),
"address" : {
"building" : "1007",
"coord" : [
-73.856077,
40.848447
],
"street" : "Morris Park Ave",
"zipcode" : "10462"
},
"borough" : "Bronx",
"cuisine" : "Bakery",
"grades" : [
{
"date" : ISODate("2014-03-03T00:00:00Z"),
"grade" : "A",
"score" : 2
},
{
"date" : ISODate("2013-09-11T00:00:00Z"),
"grade" : "A",
"score" : 6
},
{
"date" : ISODate("2013-01-24T00:00:00Z"),
"grade" : "A",
"score" : 10
},
{
"date" : ISODate("2011-11-23T00:00:00Z"),
"grade" : "A",
"score" : 9
},
{
"date" : ISODate("2011-03-10T00:00:00Z"),
"grade" : "B",
"score" : 14
}
],
"name" : "Morris Park Bake Shop",
"restaurant_id" : "30075445"
}
使用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 }