| 1 | 普通 | db.getCollection('collection').find({}) | select * from tablename | collection不存在时会自动创建，tablename不存在时会报错 |
| 2 | 条件查询and | db.getCollection('collection').find({"name":"梧桐","age":18}) | select * from tablenamewhere name = "梧桐" and age = 18 | |
| 3 | 条件查询or | db.getCollection('collection').find({$or:[{'name':'梧桐2'},{'name':'梧桐1'}]}) | select * from tablename where name = "梧桐1" or name= "梧桐2" | |
| 4 | 条件查询>,<, >=,<= | db.getCollection('collection').find("age":{$gt:18}) | select * from tablename where age > 18 | gt :>lt : <$gte : >=$$lte : <= |
| ISODate | db.getCollection('tablename').find({'gmtCreate':{gt:ISODate("2019−12−04T00:00:00.000Z"),lt:ISODate("2019-12-05T00:00:00.000Z")}}) | select * from tablename where gmtCreate > "2019-12-04T00:00:00.000"and "2019-12-05T00:00:00.000" | |
| 5 | 条件查询 and，or，< | db.getCollection('collection').find({or:[{and:[{"name":"梧桐"},{"age":18}]},{"age":{$lt:18}}]}) | select * from tablename where (name = '梧桐'and sex = '男' ）or age < 18 | $and must be an array |
| 6 | 条件查询!= | db.collection.find({"name":{$ne:"梧桐"}}) | select * from tablename where name != '梧桐' | db.getCollection('collection')和db.collection都可以 |
| 7 | 条件查询 in | db.collection.find({"name":{$in:["梧桐","梧桐雨"]}}) | select * from tablename where name in ("梧桐","梧桐雨") | |
| 8 | 条件查询not in | db.collection.find({"name":{$nin:["梧桐","梧桐雨"]}}) | select * from tablename where name not in ("梧桐","梧桐雨") | $nin 这个东西不推荐 |
| 9 | 条件查询limit | db.collection.find({}).limit(1) | select * from tablename limit 1 | |
| 10 | 条件查询返回指定字段 | db.collection.find( {},{ "name": 1, "age": 1 }) | select name,agefrom tablename | mongo第一个参数为查询条件，空代表查询所有 |
| 11 | 条件查询返回除指定字段外其他字段 | db.collection.find( {},{ "sex": 0 }) | 这个要写存储过程，略 | |
| 12 | 排序sort | db.getCollection('collecton').find({}).sort({age:1}).limit(1) | select * from tablename order by age limit 1 | Mongo 如果按照 id排序db.getCollection('t_holder').find().sort({"_id":1}) |
| 13 | 分页查询skip | db.getCollection('t_asset_event_collect').skip(2).limit(3) | select * from tablename limit 2,3 | |
| 14 | 分组 group | db.getCollection('t_event_collect').group({key:{ "gmtMirror":true},initial: {count:0},reduce:function(obj,prev){prev.count++;},condition:{"quantity":{$gt:0}}})返回 类和计数 | select *from tablename group by gmt_mirror having quantity > 0 | group参数选项：1.key： 这个就是分组的key2.initial： 每组都分享一个初始化函数，特别注意：是每一组initial函数。3.reduce： 这个函数的第一个参数是当前的文档对象，第二个参数是上一次function操作的累计对象。有多少个文档， $reduce就会调用多少次。4.condition： 这个就是过滤条件。5.finalize： 这是个函数，每一组文档执行完后，多会触发此方法。 |
| 15 | 分组aggerate | db.getCollection('t_event_collect').aggregate([//如果有条件 match{match :{"device":2,"holder":1}},{ group : {_id : {gmtMirror:"gmtMirror"},object: {first:"$$ROOT"}}},{ $sort :{ gmtMirror :1,quantity:1}}]) | select * from tablename gr | $$ROOT 返回对象first返回第一条group 分组 |
| 16 | 分组aggerate | db.getCollection('t_holder').aggregate([{group:{_id : {agentNo:"agentNo"},count: {sum: 1}}},{sort:{"count":-1}},{$limit:1}]) | select count(1) as a from t_detail_2 group by holder ORDER BY a desc limit 1; | |
| 分组 aggregate | db.getCollection('t_holder').aggregate([{group:{_id : {agentNo:"agentNo",type:"type",code:"code"},count: {sum: 1}}},{match:{count:{gt :1},"_id.agentNo":{ne:""}}}]) | select count(1),agentNo from t_holder ORDER BY agentNo having count(1) >1 and agentNo != "" | 查询表中的重复数据集 |
| 17 | 分组 | db.collection.aggregate({"group":{_id: 'sex',max:{"max":"$age"}}}); | select max(age) from tablename group by sex | addToSet](https://docs.mongodb.com/manual/reference/operator/aggregation/addToSet/#grp._S_addToSet) 将符合筛选的字段组合成集合并返回[avg 平均值first](https://docs.mongodb.com/manual/reference/operator/aggregation/first/#grp._S_first) 第一条记录[last 最后一条记录max](https://docs.mongodb.com/manual/reference/operator/aggregation/max/#grp._S_max) 最大值[min 最小值sum](https://docs.mongodb.com/manual/reference/operator/aggregation/sum/#grp._S_sum) 总计[mergeObjects 返回几条记录中 的所有字段，记录该字段最后一条记录的值push](https://docs.mongodb.com/manual/reference/operator/aggregation/push/#grp._S_push)[stdDevPop$stdDevSamp |
| 18 | 聚合函数sum | db.t_event_collect.aggregate([{group: {_id: null,count: { sum: "$quantity" } }}]) | select sum(quantity) from tablename | In MongoDB 3.2 and earlier, sum](https://docs.mongodb.com/manual/reference/operator/aggregation/sum/#grp._S_sum) is available in the [group stage only.sum](https://docs.mongodb.com/manual/reference/operator/aggregation/sum/#grp._S_sum) is available in the following stages:● [group● project](https://docs.mongodb.com/manual/reference/operator/aggregation/project/#pipe._S_project)● [addFields (Available starting in MongoDB 3.4)● set](https://docs.mongodb.com/manual/reference/operator/aggregation/set/#pipe._S_set) (Available starting in MongoDB 4.2)● [replaceRoot (Available starting in MongoDB 3.4)● replaceWith](https://docs.mongodb.com/manual/reference/operator/aggregation/replaceWith/#pipe._S_replaceWith) (Available starting in MongoDB 4.2)● [match stage that includes an $expr expression |
| 19 | 聚合函数sum 加总多个字段 | db.t_event_collect.aggregate([{group: { _id: null, count: { sum:{multiply:["quantity","$holder"]} } }}]) | select sum(english + yuwen) from tablename; | |
| 20 | 聚合函数count | 写法一：db.t_event_collect.aggregate([{ group: { _id: null, myCount: { sum: 1 } } }])写法二：db.t_event_collect.aggregate([{$count:"quantity"}]) | select count(1) from tablename | sum:1表示每次符合加1sum: 2 表示每次符合加2 |
| 21 | 函数max,min,avg | db.t_event_collect.aggregate([{group: { _id: null, count: { max: "$quantity" } }}]) | select max(quantity) from tablename | maxmin$avg |
