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