Collection查詢資料操作 |
Select * from tab |
db.tab.find() db.tab.find({}) |
Select id from tab |
db.tab.find({},{"id":1}) #({條件},{欄位:0/1}) db.tab.find({},{"_id":0,"id":1}) db.tab.aggregate({ $project : {id : 1 }}); db.tab.find({id:{$exists:1}}); |
Select * from tab where id=1 |
db.tab.find( { id :1}) |
Select id from tab where id=1 |
db.tab.find({id :1},{"_id":0,"id":1}) |
Select * from tab where id<>1 |
db.tab.find({id:{$ne:1}}) |
Select * from tab where id>2 |
db.tab.find( { id: { $gt: 2} } ) |
Select * from tab where id<3 |
db.tab.find( { id: { $lt: 3} } ) |
Select * from tab where id>=2 |
db.tab.find( { id: { $gte: 2 } } ) |
Select * from tab where id<=3 |
db.tab.find( { id: { $lte: 3} } ) |
Select * from tab where id = 1 or id = 2 |
db.tab.find({$or:[{id:3},{id:2}]}) |
Select * from tab where id < 2 or id >4 |
db.tab.find({$or:[{id:{$gt:4}},{id:{$lt:2}}]}) |
Select * from tab where id in (1,2) |
db.tab.find( { id: { $in: [ 1, 2 ] } } ) |
Select * from tab where id not in (2,3) |
db.tab.find({id:{"$nin":[2,3]}}) |
Select * from tab where id between 2 and 3 Select * from tab where id >= 2 and id <= 3 |
db.tab.find({$and:[{id:{$gte:2}},{id:{$lte:5}}]}) |
Select * from tab where id = 1 and name = ‘kk’ |
db.tab.find({id:2, name:'kk'}) |
Select distinct id from tab |
db.tab.distinct("id"); db.runCommand ( { distinct: "tab", key: "id" } ) |
Select distinct id from tab where name = ‘A’ |
db.runCommand({distinct:'tab',key:'id',query:{name:'A'}}) |
Select * from tab where name like ‘%A%’ |
db.tab.find({ name:{$regex:"A"}}) db.tab.find({ name:/A/}) |
Select * from tab order by id asc |
db.tab.find().sort({id:1}) |
Select * from tab order by id desc |
db.tab.find().sort({id:-1}) |
Select top 5 * from tab |
db.tab.find().limit(5) |
跳過前m條記 |
db.tab.find().skip(2) db.tab.aggregate({ $skip : 2 }); |
跳過前m條記錄,從m+1開始取n條 |
db.tab.find().skip(2).limit(3) |
除了指定的列,其他列都顯示 |
db.tab.find({id:null}) db.tab.find({},{"_id":0}) |
尋找欄位id為string類型的行(參考下表格) |
db.tab.find({ id: {$type: 2}}); |
|
|
select name,sum(id) as sumid from tab where id >0 group by name |
db.tab.group({ key:{ "name":true} # group by name ,cond:{id:{ $gt:0}} # where id >0 ,reduce:function(obj,prev) #彙總函式 { prev.sumid += obj.id; } #函數邏輯,累加id , initial: {sumid: 0 }}) #初始化 |
Select sum(*) from tab |
db.tab.group({key:{},cond:{} ,reduce:function(obj,prev) { prev.sumid += obj.id; },initial: {sumid: 0 }}); |
Select sum(*) as newcol from tab |
db.tab.aggregate([{$group:{_id:"$by_user",newcol:{$sum:"$id"}}}]) |
Select count(*) from tab |
db.tab.count() 或者 db.tab.find().count() |
Select count(*) from tab |
db.tab.group({key:{},cond:{}, reduce:function(obj,prev) { prev.sumid += 1; },initial: {sumid: 0 }}); |
Select avg(*) from tab |
db.tab.aggregate([{$group:{_id:"$by_user",newcol:{$avg:"$id"}}}]) |
Select max(*) from tab |
db.tab.find().sort({id:-1}).limit(1) db.tab.aggregate([{$group:{_id:"$by_user",newcol:{$max:"$id"}}}]) |
Select min(*) from tab |
db.tab.find().sort({id:1}).limit(1) db.tab.aggregate([{$group:{_id:"$by_user",newcol:{$min:"$id"}}}]) |
|
|
#元素查詢 #db.tab.insert({ratings: [ 5, 8, 9 ] }) db.tab.find({ ratings: [ 5, 8, 9 ] } ) #尋找匹配的數組 db.tab.find({ ratings: 5 }) #尋找元素中含“5”的記錄 db.tab.find({ ratings:{$elemMatch:{$gt:8,$lt:10}}}) #元素匹配尋找 #內嵌文檔 #db.tab.insert({producer:{company: 'ABC',address: 'Street'}}) #db.tab.insert({producer:[{ company: 'ABC',address: 'Street'},{ company: 'KK',address: 'Street2'}] }) db.tab.find({producer:{company: 'ABC',address: 'Street'}}) db.tab.find({'producer.company': 'ABC'}) db.tab.find( { 'producer.0.address': 'Street'} ) #欄位'producer的第一個元素的address=’ Street’ |