MongoDB & MySQL

gistfile1.txt

MySQL:  SELECT * FROM user WHERE name = "foobar"
Mongo:  db.user.find({"name" : "foobar"})
MySQL:  INSERT INOT user (`name`, `age`) values ("foobar", 25)
Mongo:  db.user.insert({"name" : "foobar", "age" : 25})
MySQL:  DELETE * FROM user
Mongo:  db.user.remove({})
MySQL:  DELETE FROM user WHERE age < 30
Mongo:  db.user.remove({"age" : {$lt : 30}})  $gt : > ; $gte : >= ; $lt : < ; $lte : <= ; $ne : !=
MySQL:  UPDATE user SET `age` = 36 WHERE `name` = "foobar"
Mongo:  db.user.update({"name" : "foobar"}, {$set : {"age" : 36}})
MySQL:  UPDATE user SET `age` = `age` + 3 WHERE `name` = "foobar"
Mongo:  db.user.update({"name" : "foobar"}, {$inc : {"age" : 3}})
MySQL:  SELECT COUNT(*) FROM user WHERE `name` = "foobar"
Mongo:  db.user.find({"name" : "foobar"}).count()  db.user.count({"name" : "foobar"})
MySQL:  SELECT * FROM user limit 10, 20
Mongo:  db.user.find().skip(10).limit(20)
MySQL:  SELECT * FROM user WHERE `age` IN (25, 35,45)
Mongo:  db.user.find({"age" : {$in : [25, 35, 45]}})
MySQL:  SELECT * FROM user ORDER BY age DESC
Mongo:  db.user.find().sort({"age" : -1})
MySQL:  SELECT DISTINCT(name) FROM user WHERE age > 20
Mongo:  db.user.distinct("name", {"age": {$lt : 20}})
MySQL:  SELECT name, sum(marks) FROM user GROUP BY name
Mongo:
    db.user.group({
        key : {"name" : true},
        cond: {"name" : "foo"},
        reduce: function(obj,prev) { prev.msum += obj.marks; },
        initial: {msum : 0}
    });
MySQL:  SELECT name FROM user WHERE age < 20
Mongo:  db.user.find("this.age < 20", {name : 1})


create table users (a number, b number)
insert into users values(1,1)
db.users.insert({a:1,b:1})

select a,b from users
db.users.find({}, {a:1,b:1})

select a,b from users where age=33
db.users.find({age:33}, {a:1,b:1})


select * from users where age=33 order by name
db.users.find({age:33}).sort({name:1})

select * from users where age>33 and age<=40 order by name DESC
db.users.find({"age":{$gt:33,$lte:40}})}).sort({name:-1})

create index myindexname on users(name)
db.users.ensureIndex({name:1})


select * from users where a=1 or b=2
db.users.find({ $or : [ { a : 1 } , { b : 2 } ] })


select * from users limit 1
db.users.findOne()

explain select * from users where z=3
db.users.find({z:3}).explain()

select distinct last_name from users
db.users.distinct("last_name")

select * from user where `age` in (25, 35, 45)
db.user.find({"age" : {"$in" : [25, 35, 45]}})

select * from users where a = 1 or b = 2
db.users.find({"$or" : [{ "a" : 1 }, { "b" : 2 }]})

select count(*) from users where age > 30
db.users.find({"age" : {"$gt" : 30}}).count()

select count(*) from user where `name` = "foobar"
db.user.find({"name" : "foobar"}).count()

select count(age) from users
db.users.find({"age": {"$exists" : true}}).count()

update users set a = 1 where b = "q"
db.users.update({"b" : "q"}, {"$set" : {"a" : 1}}, false, true)

update user set `age` = 36 where `name` = "foobar"
db.user.update({"name" : "foobar"}, {"$set" : {"age" : 36}})

update user set `age` = `age` + 3 where `name` = "foobar"
db.user.update({"name" : "foobar"}, {"$inc" : {"age" : 3}})