為了更好的理解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為資料庫操作提供了大量靈活的方式。當然，在增加靈活性的同時，也增加了程式設計師記憶的難度。

SQL與MongoDB的詳細對比

相關文章