維新生活日記

http://www.mongodb.org/display/DOCS/SQL+to+Mongo+Mapping+Chart

MySQL executable     Oracle executable     Mongo executable
mysqld
oracle
mongod
mysql
sqlplus
mongo

MongoDB queries are expressed as JSON (BSON) objects.  This quick reference chart shows examples as both SQL and in Mongo Query Language syntax.

The query expression in MongoDB (and other things, such as index key patterns) is represented as JSON.  However, the actual verb (e.g. "find") is done in one's regular programming language.  The exact forms of these verbs vary by language.  The examples below are Javascript and can be executed from the mongo shell.

SQL Statement
Mongo Query Language Statement
CREATE TABLE USERS (a Number, b Number)
implicit; can also be done [explicitly] with
db.createCollection("mycoll")
     
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 * FROM users
db.users.find()
SELECT * FROM users WHERE age=33
db.users.find({age:33})
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
db.users.find({'age':{$gt:33}})})
SELECT * FROM users WHERE age<33
db.users.find({'age':{$lt:33}})})
SELECT * FROM users WHERE name LIKE "%Joe%"
db.users.find({name:/Joe/})
SELECT * FROM users WHERE name LIKE "Joe%"
db.users.find({name:/^Joe/})
SELECT * FROM users WHERE age>33 AND age<=40
db.users.find({'age':{$gt:33,$lte:40}})})
SELECT * FROM users ORDER BY name DESC
db.users.find().sort({name:-1})
SELECT * FROM users WHERE a=1 and b='q'
db.users.find({a:1,b:'q'})
SELECT * FROM users LIMIT 10 SKIP 20
db.users.find().limit(10).skip(20)
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()
SELECT DISTINCT last_name FROM users
db.users.distinct('last_name')
SELECT COUNT(*y)
FROM users
db.users.count()
SELECT COUNT(*y)
FROM users where AGE > 30
db.users.find({age: {'$gt': 30}}).count()
SELECT COUNT(AGE) from users
db.users.find({age: {'$exists': true}}).count()
     
CREATE INDEX myindexname ON users(name)
db.users.ensureIndex({name:1})
CREATE INDEX myindexname ON users(name,ts DESC)
db.users.ensureIndex({name:1,ts:-1})
     
EXPLAIN SELECT * FROM users WHERE z=3
db.users.find({z:3}).explain()
     
UPDATE users SET a=1 WHERE b='q'
db.users.update({b:'q'}, {$set:{a:1}}, false, true)
UPDATE users SET a=a+2 WHERE b='q'
db.users.update({b:'q'}, {$inc:{a:2}}, false, true)
     
DELETE FROM users WHERE z="abc"
db.users.remove({z:'abc'});