查詢 · 數據庫

MongoDB只提供了`find`函數即可完成各種查詢，基本語法如下： ```sql db.collection_name.find(query, projection) query 查詢條件，可選 projection 投影操作符指定返回的鍵，即查詢的數據，可選 ``` 先插入測試文檔： ```js > for(var i=1; i<=30; i++) db.customer.insert({id: i, name: "xi"+i, age: 100+i}) ``` **1. 查詢全部** ```sql > db.customer.find() { "_id" : ObjectId("6050ca0444f167bbabdf6ab1"), "id" : 1, "name" : "xi1", "age" : 101 } { "_id" : ObjectId("6050ca0444f167bbabdf6ab2"), "id" : 2, "name" : "xi2", "age" : 102 } ... { "_id" : ObjectId("6050ca0444f167bbabdf6ac4"), "id" : 20, "name" : "xi20", "age" : 120 } Type "it" for more # 或者 > db.customer.find({}) ``` 按照插入的順序返回前 20 個文檔，如果記錄總數比 20 大，則我們可以通過命令`it`獲取更多文檔。 ```sql # 默認為顯示前20條文檔 > DBQuery.shellBatchSize 20 # 在當前會話中顯示可以查詢前50條文檔 > DBQuery.shellBatchSize=50 50 > db.customer.find() { "_id" : ObjectId("6050ca0444f167bbabdf6ab1"), "id" : 1, "name" : "xi1", "age" : 101 } { "_id" : ObjectId("6050ca0444f167bbabdf6ab2"), "id" : 2, "name" : "xi2", "age" : 102 } ... { "_id" : ObjectId("6050ca0444f167bbabdf6ace"), "id" : 30, "name" : "xi30", "age" : 130 } ``` **2. 以易讀方式查詢** ```sql > db.customer.find().pretty() { "_id" : ObjectId("6050ca0444f167bbabdf6ab1"), "id" : 1, "name" : "xi1", "age" : 101 } { "_id" : ObjectId("6050ca0444f167bbabdf6ab2"), "id" : 2, "name" : "xi2", "age" : 102 } ``` **3. 精確查詢** ```sql # 查詢id=9的文檔 > db.customer.find({id:9}) { "_id" : ObjectId("6050ca0444f167bbabdf6ab9"), "id" : 9, "name" : "xi9", "age" : 109 } # 查詢 id=9 && name="xi9" 的文檔 > db.customer.find({id:9, name:"xi9"}) { "_id" : ObjectId("6050ca0444f167bbabdf6ab9"), "id" : 9, "name" : "xi9", "age" : 109 } ``` **4. 比較運算** ```sql $lt 小于 $lte 小于等于 $gt 大于 $gte 大于等于 $ne 不等于。單獨使用$ne，它也不會利用索引的優勢，反而會進行全表掃描 ``` ```sql # 查詢id<5的文檔 > db.customer.find({id:{$lt:5}}) { "_id" : ObjectId("6050ca0444f167bbabdf6ab1"), "id" : 1, "name" : "xi1", "age" : 101 } { "_id" : ObjectId("6050ca0444f167bbabdf6ab2"), "id" : 2, "name" : "xi2", "age" : 102 } { "_id" : ObjectId("6050ca0444f167bbabdf6ab3"), "id" : 3, "name" : "xi3", "age" : 103 } { "_id" : ObjectId("6050ca0444f167bbabdf6ab4"), "id" : 4, "name" : "xi4", "age" : 104 } # 查詢 5<id<10 的文檔 > db.customer.find({id:{$gt:5, $lt:10}}) { "_id" : ObjectId("6050ca0444f167bbabdf6ab6"), "id" : 6, "name" : "xi6", "age" : 106 } { "_id" : ObjectId("6050ca0444f167bbabdf6ab7"), "id" : 7, "name" : "xi7", "age" : 107 } { "_id" : ObjectId("6050ca0444f167bbabdf6ab8"), "id" : 8, "name" : "xi8", "age" : 108 } { "_id" : ObjectId("6050ca0444f167bbabdf6ab9"), "id" : 9, "name" : "xi9", "age" : 109 } ``` **5. `$in`、`$nin`查詢** ```sql # 查詢 id=1、id=3、id=5 的文檔 > db.customer.find({id:{$in:[1, 5, 3]}}) { "_id" : ObjectId("6050ca0444f167bbabdf6ab1"), "id" : 1, "name" : "xi1", "age" : 101 } { "_id" : ObjectId("6050ca0444f167bbabdf6ab3"), "id" : 3, "name" : "xi3", "age" : 103 } { "_id" : ObjectId("6050ca0444f167bbabdf6ab5"), "id" : 5, "name" : "xi5", "age" : 105 } # 查詢 id!=1、id!=3、id!=5 的文檔 # $nin 是一種比較低效的査詢選擇器，它會進行全表掃描，因此最好不要單獨使用$nin > db.customer.find({id:{$nin:[1, 3, 5]}}) { "_id" : ObjectId("6050ca0444f167bbabdf6ab2"), "id" : 2, "name" : "xi2", "age" : 102 } { "_id" : ObjectId("6050ca0444f167bbabdf6ab4"), "id" : 4, "name" : "xi4", "age" : 104 } { "_id" : ObjectId("6050ca0444f167bbabdf6ab6"), "id" : 6, "name" : "xi6", "age" : 106 } { "_id" : ObjectId("6050ca0444f167bbabdf6ab7"), "id" : 7, "name" : "xi7", "age" : 107 } ``` **6. 邏輯運算** ```sql $or 或運算 $and 與運算 ``` ```sql # 查詢id=11，或者age=119 的文檔 > db.customer.find({$or:[{id:11}, {age:119}]}) { "_id" : ObjectId("6050ca0444f167bbabdf6abb"), "id" : 11, "name" : "xi11", "age" : 111 } { "_id" : ObjectId("6050ca0444f167bbabdf6ac3"), "id" : 19, "name" : "xi19", "age" : 119 } # 查詢 id=11 并且 age=111 的文檔 > db.customer.find({$and:[{id:11}, {age:111}]}) { "_id" : ObjectId("6050ca0444f167bbabdf6abb"), "id" : 11, "name" : "xi11", "age" : 111 } ``` **7. 嵌套查詢** ```sql { "_id" : Objectld("6050dae844f167bbabdf6acf"), "id" : 1, "name" : "xiaohong", "detail" : [ { "sex" : "femle", "age" : 105}, { "address" : "china", "post" : 5} ] } ``` ```sql # 查詢 post=5 的文檔 > db.customer.find({'detail.1.post': 5}).pretty() { "_id" : ObjectId("6050dae844f167bbabdf6acf"), "id" : 1, "name" : "xiaohong", "detail" : [ { "sex" : "femle", "age" : 105 }, { "address" : "china", "post" : 5 } ] } ``` **8. 投影查詢** 相當于select id, name from ... ，只查詢指定的字段。 ```sql # _id:0、id:1，0表示不查詢，1查詢 > db.customer.find({'detail.1.post':5}, {_id:0, id:1, name: 1}) { "id" : 1, "name" : "xiaohong" } ``` **8. 數組查詢** ```json { "_id" : 4, "AttributeName" : "material", "AttributeValue":["牛仔","織錦","雷紡","簾絲"], "IsOptional" : 1 }, { "_id" : 5, "AttributeName" : "version", "AttributeValue":["收腰型","修身型","直筒型","寬松型","其他"], "IsOptional":1 } ``` ```sql # 精確匹配數組 > db.customer.find({"AttributeValue": ["牛仔","織錦","雷紡","簾絲"]}).pretty() { "_id" : 4, "AttributeName" : "material", "AttributeValue" : [ "牛仔", "織錦", "雷紡", "簾絲" ], "IsOptional" : 1 } # 匹配數組中的一個值 > db.customer.find({"AttributeValue": "收腰型"}).pretty() { "_id" : 5, "AttributeName" : "version", "AttributeValue" : [ "收腰型", "修身型", "直筒型", "寬松型", "其他" ], "IsOptional" : 1 } # 匹配數組指定位置的值 # .0 就是數組的第0個元素的值 > db.customer.find({"AttributeValue.0": "收腰型"}).pretty() { "_id" : 5, "AttributeName" : "version", "AttributeValue" : [ "收腰型", "修身型", "直筒型", "寬松型", "其他" ], "IsOptional" : 1 } ```