MySQL语法 |
MongoDB语法 |
select * from t1; |
db.t1.find({},{_id:0}) |
select id,name from t1; |
db.t1.find({},{_id:0,id:1,name:1}) |
select id,name from t1 limit 1; |
db.t2.findOne({},{_id:0,id:1,name:1}) |
select id,name from t1 limit 10; |
db.t1.find({},{_id:0,id:1,name:1}).limit(10) |
select id,name from t1 limit 10,4; |
db.t1.find({},{_id:0,id:1,name:1}).skip(10).limit(4) |
select id,name from t1 order by id desc limit 10; |
db.t1.find({},{_id:0,id:1,name:1}).limit(10).sort({id:-1}) |
select id,name from t1 where id=2; |
db.t1.find({id:2},{_id:0,cid:1,name:1}) |
select id,name from t1 where i d>=2; |
db.t1.find({id:{$gte:2}},{_id:0,cid:1,name:1}) |
select id,name from t1 where i d>=2 AND id <=10; |
db.t1.find({id:{$gte:2,$lte:10}},{_id:0,cid:1,name:1}) |
select id,name from t1 where id in (1,3,5); |
db.t1.find({id:{$in:[1,3,5]}},{_id:0,cid:1,name:1}) |
select id,name from t1 where i d=1 AND name =’a’; |
db.t1.find({$and:[{id:1},{name:"a"}]},{_id:0,cid:1,name:1}) |
select id,name from t1 where i d=1 OR name =’a’; |
db.t1.find({$or:[{id:1},{name:"a"}]},{_id:0,cid:1,name:1}) |
select count(*) from t1; |
db.t1.find().count() |
select distinct(name) from t1; |
db.t1.distinct('name').count() |
select count(*) from t1; |
db.t1.aggregate({$group:{_id:null,count:{$sum:1}}}) |
select sum(cid) from t1; |
db.t1.aggregate({$group:{_id:null,sum:{$sum:'$cid'}}}) |
select sum(cid) from t1 group by name; |
db.t1.aggregate({$group:{_id:'$name',sum:{$sum:'$cid'}}}) |
select count(*) from t1 where name=’ABC’ group by class_id ; |
db.t2.aggregate({$match:{name:"ABC"}},{$group:{_id:’class_id’,count:{$sum:1}}}) |
select count(*) from t1 group by name having count(*) >2; |
db.t1.aggregate({$group:{_id:'name',count:{$sum:1}}},{$match:{count:{$gt:2}}}) |
select id,name,count(*) from t1 group by id,name having count(*) >=1; |
db.t1.aggregate({$project:{id:1,"name":1}},{$match:{name:"bb"}},{$group:{"_id":{id:"$id",name:"$name"},count:{$sum:1}}}, {$match:{count:{$gte:1}}}) |
insert into t1(cid,name) values(5,’B’),(6,’C’); |
db.t1.insertMany([{cid:5,name:"B"},{cid:6,name:"C"}]) |
replace into t1(cid,name) values(7,’D’); |
db.t1.update({},{cid:7,name:"D"},{upsert:1}) |
update t1 set name=’A’ where name=’a’; |
db.t1.update({name:"a"},{$set:{name:"A"}},{multi:1}) |
update t1 set cid=cid+10 where cid=’6’; |
db.t1.update({cid:6},{$inc:{cid:10}}) |
delete from t1 where name = ‘D’; |
db.t1.remove({name:"D"}) db.t1.deleteMany({name:"D"}) |
alter table t1 drop column name; |
db.t1.update({},{$unset:{name:1}},{multi:1}) |