SQL to Mongo Mapping Chart
SQL to Mongo Mapping Chart
http://www.mongodb.org/display/DOCS/SQL+to+Mongo+Mapping+Chart
MySQL executable
Oracle executable
Mongo executable
mysqld
oracle
mongod
mysql
sqlplus
mongo
MySQL term
Mongo term/concept
database
database
table
collection
index
index
row
BSON
document
column
BSON field
join
embedding and linking
primary key
_id field
group by
aggregation
MongoDB queries are expressed as JSON (BSON
) objects. The following chart shows examples as both SQL and inMongo Query Language syntax.
The query expression in MongoDB (and other things, such as index key
patterns) is represented as JSON (BSON). However, theactual verb
(e.g. "find") is done in one's regular programminglanguage; thus 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 Statement
CREATE TABLE USERS (a Number
, b Number
)
implicit; can also be done explicitly
with
db.createCollection("mycoll"
)
ALTER TABLE users ADD ...
implicit
INSERT INTO USERS VALUES(3,5)
db.users.insert({a:3,b:5})
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:{$ne: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 order_id FROM orders o, order_line_items li WHERE li.order_id=o.order_id AND li.sku=12345
db.orders.find({"items.sku"
:12345},{_id:1})
SELECT customer.name FROM customers,orders WHERE orders.id="q179"
AND orders.custid=customer.id
var
o = db.orders.findOne({_id:"q179"
});
var
name = db.customers.findOne({_id:o.custid})
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'});
More examples, specifically aggregation examples, here
页:
[1]