SQL与MongoDB的详细对比

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

可执行文件对比

MySQL	Oracle	MongoDB
mysqld	oracle	mongod
mysql	sqlplus	mongo

 

术语对比

 

MySQL	MongoDB
database	database
table	collection
index	index
row	BSON document
column	BSON field
join	Embedding and linking
primary key	_id field
group by	aggregation

 

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为数据库操作提供了大量灵活的方式。当然，在增加灵活性的同时，也增加了程序员记忆的难度。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15498/viewspace-1984777/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15498/viewspace-1984777/