MongoDB 基础（三）：MongoDB 条件查询

---

1. 查询所有记录

> db.singer.find()
{ "_id" : ObjectId("622a15791181c7388318c06f"), "name" : "张三", "age" : 25 }
{ "_id" : ObjectId("622a158d1181c7388318c071"), "name" : "zhangsan", "age" : 25, "sex" : true }

# 相当于：
select * from singer

2. 查询去重后数据

> db.singer.distinct("name")
[ "zhangsan", "小李", "小王", "张三", "李四", "老李" ]
# 相当于：
select distict name from singer

3. 查询age = 22的记录

> db.singer.find({ age: 20 })
{ "_id" : ObjectId("622a18c1e53b1497cfd6d1a3"), "name" : "小王", "age" : 20 }
{ "_id" : ObjectId("622a18c1e53b1497cfd6d1a4"), "name" : "小李", "age" : 20 }
# 相当于： 
select * from singer where age = 20;

4. 查询age > 22的记录

> db.singer.find({ age: {$gt: 22} })
{ "_id" : ObjectId("622a158d1181c7388318c071"), "name" : "张三", "age" : 25, "sex" : "男" }
{ "_id" : ObjectId("622a17481181c7388318c076"), "name" : "李四", "age" : 26, "sex" : "男" }
# 相当于：
select * from singer where age > 22;

5. 查询age < 22的记录

> db.singer.find({ age: { $lt: 22 } })
{ "_id" : ObjectId("622a18c1e53b1497cfd6d1a3"), "name" : "小王", "age" : 20 }
{ "_id" : ObjectId("622a18c1e53b1497cfd6d1a4"), "name" : "小李", "age" : 20 }
# 相当于：
select * from singer where age < 22;

6. 查询age >= 25的记录

> db.singer.find({ age: {$gte: 25} })
{ "_id" : ObjectId("622a17481181c7388318c076"), "name" : "张三", "age" : 25, "sex" : "男" }
{ "_id" : ObjectId("622a17d61181c7388318c078"), "name" : "老李", "age" : 30, "sex" : "男" }
# 相当于：
select * from singer where age >= 25;

7. 查询age <= 25的记录

> db.singer.find({ age: {$lte: 25} })
{ "_id" : ObjectId("622a17481181c7388318c076"), "name" : "zhangsan", "age" : 25, "sex" : "男" }
{ "_id" : ObjectId("622a18c1e53b1497cfd6d1a3"), "name" : "小王", "age" : 20 }
{ "_id" : ObjectId("622a18c1e53b1497cfd6d1a4"), "name" : "小李", "age" : 20 }

8. 查询age >= 23 并且 age <= 26

> db.singer.find({age: {$gte: 23, $lte: 26}});
{ "_id" : ObjectId("622a15791181c7388318c06f"), "name" : "张三", "age" : 25 }
{ "_id" : ObjectId("622a17481181c7388318c076"), "name" : "zhangsan", "age" : 25, "sex" : "男" }

9. 查询name中包含 小的数据

> db.singer.find({name: /小/});
{ "_id" : ObjectId("622a18c1e53b1497cfd6d1a3"), "name" : "王二小", "age" : 20 }
{ "_id" : ObjectId("622a18c1e53b1497cfd6d1a4"), "name" : "小李", "age" : 20 }
# 相当于%%
select * from singer where name like '%小%';

10. 查询name中以小开头的

> db.singer.find({name: /^小/})
{ "_id" : ObjectId("622a18c1e53b1497cfd6d1a4"), "name" : "小李", "age" : 20 }
# 相当于：
select * from singer where name like '小%';

11. 查询指定列name、age数据

> db.singer.find();
{ "_id" : ObjectId("622a17481181c7388318c076"), "name" : "张三", "age" : 25, "sex" : "男" }
{ "_id" : ObjectId("622a17481181c7388318c076"), "name" : "李四", "age" : 26, "sex" : "男" }
> db.singer.find({}, {name: 1, age: 1});
{ "_id" : ObjectId("622a17481181c7388318c076"), "name" : "张三", "age" : 25 }
{ "_id" : ObjectId("622a17481181c7388318c076"), "name" : "李四", "age" : 26 }
# 相当于：
select name, age from singer;

12. 查询指定列name、age数据, age > 25

> db.singer.find();
{ "_id" : ObjectId("622a17d61181c7388318c078"), "name" : "老李", "age" : 22, "sex" : "男" }
{ "_id" : ObjectId("622a18611181c7388318c07a"), "name" : "老王", "age" : 30, "sex" : "男" }
> db.singer.find({age: {$gt: 25}}, {name: 1, age: 1});
{ "_id" : ObjectId("622a18611181c7388318c07a"), "name" : "老李", "age" : 30 }
# 相当于：
select name, age from singer where age >25;

13. 按照年龄排序

# 升序：
db.singer.find().sort({age: 1});
# 降序：
db.singer.find().sort({age: -1});

14. 查询name = zhangsan, age = 22的数据

db.singer.find({name: 'zhangsan', age: 22});
# 相当于：
select * from singer where name = 'zhangsan' and age = ’22';

15. 查询前5条数据

db.singer.find().limit(5);
# 相当于：
select top 5 * from singer;

16. 查询10条以后的数据

db.singer.find().skip(10);
# 相当于：
select * from singer where id not in (select top 10 * from userInfo);

17. 查询在5-10之间的数据

db.singer.find().limit(10).skip(5);

18. or 与 查询

查询singer集合中name包含 小 并且age 为 20 或 25 的数据

> db.singer.find({name: /小/, $or: [{age: 20}, {age: 25}]});
{ "_id" : ObjectId("622a18c1e53b1497cfd6d1a3"), "name" : "小王", "age" : 20 }
{ "_id" : ObjectId("622a18c1e53b1497cfd6d1a4"), "name" : "小李", "age" : 20 }

19. 查询第一条数据

db.singer.findOne();
db.singer.find().limit(1);
# 相当于：
select top 1 * from singer;

20. 查询某个结果集的记录条数

> db.singer.find({age: {$gte: 20}}).count();
9
# 相当于：
select count(*) from singer where age >= 20;