SQL与MongoDB的详细对比

为了更好的理解MongoDB与传统关系数据库的区别，我们将几款典型的关系数据库与MongoDB进行对比，如下图所示:

MySQL	MongoDB
database	database
table	collection
index	index
row	BSON document
column	BSON field
join	Embedding and linking
aggregation e.g. group by	aggregation pipeline
primary key Specify any unique column or column combination as primary key.	primary key.In MongoDB, the primary key is automatically set to the _id field |

MongoDB查询被表示成JSON(或BSON)对象，在MongoDB中查询语句（以及其他东西，比如索引健模式）被表示成JSON(BSON),然而实际动作（比如"find")是在某种常用编程语言中实现的。下图对比了在SQL和MongoDB中的查询语句:

SQL语句	MongoDB语句
create table users (a number,b number)	Implicit; can also be done explicitly with
	db.createCollection("users")
Insert into users values(1,2)	db.users.insert({a:1,b:2})
select a,b from users	db.users.find({ },{a:1,b:1})
select * from users	db.users.find()
select * from users where age=30	db.users.find({age:30})
select a,b from users where age=30	db.users.find({age:30},{a:1,b:1})
Select * from users where age=30 order by name	db.users.find({age:30}).sort()
select * from users where age>30	db.users.find({age:{$gt:30}})
select * from users where age>=30	db.users.find({age:{$gte:30}})
select * from users where age<30	db.users.find({age:{$lt:30}})
select * from users where age<=30	db.users.find({age:{$lte:30}})
select * from users where age!=30	db.users.find({age:{$ne:30}})
select * from users where age> 18 and age <25	db.users.find({age:{$gt:18,$lt:25}})
select * from users where a=18 or b=25	db.users.find({$or :[{a:18} , {b:25} ] } )
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 order by name desc	db.users.find().sort({name:-1}) 按降序排序
select * from users order by name	db.users.find().sort({name:1})  按升序排序
select * from users where a=2 and b='dsf'	db.users.find({a:2,b:'dsf'})
select * from users limit 10 skip 15	db.users.find().limit(10).skip(15)
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=123456	db.orders.find({"items.sku":123456},{_id:1})
select distinct first_name from users	db.users.distinct("first_name")
select count(*) from users	db.users.count()
select count(*) from users where age > 36	db.users.find({age:{'$gt':36}}).count()
select count(age) from users	db.users.find({age:{'$exists':true}}).count()
create index indexname on users(name)	db.users.ensureIndex({name:1})
create index indexname on users(name,add desc)	db.users.ensureIndex({name:1,add:-1})
explain select * from users where b=3	db.users.find({b:3}).explain()
update users set a=1 where b='dsf'	db.users.update({b:'dsf'},{$set:{a:1}})
update users set a=a+2 where b='dsf'	db.users.update({b:'dsf'},{$inc:{a:2}},false,true)
delete from users where z="abc"	db.users.remove({z:'abc'})

下面的例子表示的是JavaScript并且它们可以在mongo shell中执行,下面两条修改数据库文档的语句结果是相同的：

//查看原先的数据库内容

> db.chenfeng.find().forEach(printjson)

{ "_id" : ObjectId("56ad86df4486a08016f82bfa"), "a" : 1, "b" : 2 }

{ "_id" : ObjectId("56ad86f74486a08016f82bfb"), "x" : 3, "y" : 4 }

>

//将ｂ的值改为５

> var thing=db.chenfeng.findOne({a:1})

> thing

{ "_id" : ObjectId("56ad86df4486a08016f82bfa"), "a" : 1, "b" : 2 }

> thing.b=5

5

> db.chenfeng.save(thing)

WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })

> db.chenfeng.find().forEach(printjson)

{ "_id" : ObjectId("56ad86df4486a08016f82bfa"), "a" : 1, "b" : 5 }

{ "_id" : ObjectId("56ad86f74486a08016f82bfb"), "x" : 3, "y" : 4 }

>

//将ｂ的值改回成１

> db.chenfeng.update({a:1},{$set:{b:1}},false,true)

WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })

> db.chenfeng.find().forEach(printjson)

{ "_id" : ObjectId("56ad86df4486a08016f82bfa"), "a" : 1, "b" : 1 }

{ "_id" : ObjectId("56ad86f74486a08016f82bfb"), "x" : 3, "y" : 4 }

>

由这个例子可以看出，MongoDB为数据库操作提供了大量灵活的方式。当然，在增加灵活性的同时，也增加了程序员记忆的难度。