SQL to Mongo Mapping Chart

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

MySQL term	Mongo term
database	database
table	collection
index	index
row	BSON document
column	BSON field
join	embedding and linking
primary key	_id field primary key名字不可变 ,可以explicitly 指定 _id :值

MongoDB queries are expressed as JSON (BSON) objects.

The query expression in MongoDB (and other things, such as index key patterns) is represented as JSON (BSON). However, the actual verb (e.g. "find") is done in one's regular programming language; 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 schema free

`INSERT INTO USERS VALUES(3,5)`	`db.users.insert({a:3,b:5})` `tuple 以document 形式插入`

`SELECT a,b FROM users`	`db.users.find({}, {a:1,b:1} a,b field,1表示true,)` `条件为不设限投影a,b field,1表示true`
`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}}) //{$gt:33} 是一个对象，查询虽以document 的形式，但是是经解释器处理了的，并不是JS天生就具有这种方便查询的功能;$只是一个字符而已，没有特殊含义，用于定义标识符`
`SELECT * FROM users WHERE age!=33`	`db.users.find({age:{$ne:33}})`
`SELECT * FROM users WHERE name LIKE "%Joe%" ,%号表示任意多个字符`	`db.users.find({name:/Joe/}) /Joe/ 正则表达式，解释器认为要寻找在name值中的符号串中能grep 到此正则表达式的document`
`SELECT * FROM users WHERE name LIKE "Joe%"`	`db.users.find({name:/^Joe/}) ^表示以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 (结果集中的偏移量）` `返回21-30共10个`	`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` `o 是 orders的别名` `li 是 order_line_item的别名`	`db.orders.find({"items.sku":12345},{_id:1})` `这个解释有问题，"items.sku"是orders 某条记录的 field,没有人能知道 "items.sku":12345这个字段是如何自己冒出来的。关于连接还是看下一条解释吧。` `***********************************************` `猜测：可能是collection的模式设计比较特殊，通过人力可以转为上述语句`
`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})` `Mongo没有连接查询？因为Collection中Schema Free,大多数连接没有意义？`

`SELECT DISTINCT last_name FROM users`	`db.users.distinct('last_name');` `去重，只有select出来投影后 row完全一样的才被去掉。`
`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` `统计:AGE属性不空的个数？`	`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();` `如果在SELECT语句前放上关键词EXPLAIN，MySQL将解释它如何处理SELECT，提供有关表如何联接和联接的次序。借助于EXPLAIN，可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT。`

`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)` `false 指定条件，true 指定update ？ $指定更新的操作？也可以直接写上{a:指定值}?`

`DELETE FROM users WHERE z="abc"`	`db.users.remove({z:'abc'});`