查询 · 数据库

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 } ```