MongoDB与MySQL常用操作语句对照
一、MongoDB对MySQL常用的SQL语句对应的实现
——————————————
MySQL:
SELECT*FROMuser
Mongo:
db.user.find()
——————————————
MySQl:
SELECT*FROMuserWHEREname='foobar'
Mongo:
db.user.find({'name':'foobar'})
——————————————
MySql:
INSERTINTOuser('name','age')VALUES('foobar',25)
Mongo:
db.user.insert({'name':'foobar','age':25})
——————————————
如果想增加email这一列
MySql:
ALTERTABLEuser...
Mongo:
db.user.insert({'name':'foobar','age':25,'email':'foo@bar.com'})
——————————————
MySql:
DELETE*FROMuser
Mongo:
db.user.remove({})
——————————————
MySql:
DELETEFROMuserWHEREage<30
Mongo:
db.user.remove({'age':{$lt:30}})
$gt:>;$gte:>=;$lt:<;$lte:<=;$ne:!=
——————————————
MySql:
UPDATEuserSET`age`=36WHERE`name`='foobar'
Mongo:
db.user.update({'name':'foobar'},{$set:{'age':36}})
——————————————
MySQL:
UPDATEuserSET`age`='age'+3WHERE`name`='foobar'
Mongo:
db.user.update({'name':'foobar'},{$inc:{'age':3}})
——————————————
MySQl:
SELECTCOUNT(*)FROMuserWHERE`name`='foobar'
Mongo:
db.user.find({'name':'foobar'}).count()
——————————————
MySql:
SELECT*FROMuserlimit10,20
Mongo:
db.user.find().skip(10).limit(20)
——————————————
MySQl:
SELECT*FROMuserWHERE`age`IN(25,35,45)
Mongo:
db.user.find({'age':{$in:[25,35,45]}})
——————————————
MySql:
SELECT*FROMuserORDERBYageDESC
Mongo:
db.user.find().sort({'age':-1})
——————————————
MySQL:
SELECTDISTINCT(name)FROMuserWHEREage>20
Mongo:
db.user.distinct('name',{'age':{$lt:20}})
——————————————
MySQL:
SELECTname,sum(marks)FROM userGROUPBYname
Mongo:
db.user.group({
key:{'name':true},
cond:{'name':'foo'},
reduce:function(obj,prev){prev.msum+=obj.marks;},
initial:{msum:0}
})
——————————————
MySQL:
SELECTnameFROMuserWHEREage<20
Mongo:
db.user.find('this.age<20',{name:1})
二、MongoDB数据库与MySQL操作对照
左边是mongodb查询语句，右边是sql语句。对照着用，挺方便。
db.users.find()select*fromusers
db.users.find({"age":27})select*fromuserswhereage=27
db.users.find({"username":"joe","age":27})select*fromuserswhere"username"="joe"andage=27
db.users.find({},{"username":1,"email":1})selectusername,emailfromusers
db.users.find({},{"username":1,"_id":0})//nocase //即时加上了列筛选，_id也会返回；必须显式的阻止_id返回
db.users.find({"age":{"$gte":18,"$lte":30}})select*fromuserswhereage>=18andage<=30//$lt(<)$lte(<=)$gt(>)$gte(>=)
db.users.find({"username":{"$ne":"joe"}})select*fromuserswhereusername<>"joe"
db.users.find({"ticket_no":{"$in":[725,542,390]}})select*fromuserswhereticket_noin(725,542,390)
db.users.find({"ticket_no":{"$nin":[725,542,390]}})select*fromuserswhereticket_nonotin(725,542,390)
db.users.find({"$or":[{"ticket_no":725},{"winner":true}]})select*formuserswhereticket_no=725orwinner=true
db.users.find({"id_num":{"$mod":[5,1]}})select*fromuserswhere(id_nummod5)=1
db.users.find({"$not":{"age":27}})select*fromuserswherenot(age=27)
db.users.find({"username":{"$in":[null],"$exists":true}})select*fromuserswhereusernameisnull//如果直接通过find({"username":null})进行查询，那么连带"没有username"的纪录一并筛选出来
db.users.find({"name":/joey?/i})//正则查询，value是符合PCRE的表达式
db.food.find({fruit:{$all:["apple","banana"]}})//对数组的查询,字段fruit中，既包含"apple",又包含"banana"的纪录
db.food.find({"fruit.2":"peach"})//对数组的查询,字段fruit中，第3个(从0开始)元素是peach的纪录
db.food.find({"fruit":{"$size":3}})//对数组的查询,查询数组元素个数是3的记录，$size前面无法和其他的操作符复合使用
db.users.findOne(criteria,{"comments":{"$slice":10}})//对数组的查询，只返回数组comments中的前十条，还可以{"$slice":-10}，{"$slice":[23,10]};分别返回最后10条，和中间10条
db.people.find({"name.first":"Joe","name.last":"Schmoe"}) //嵌套查询
db.blog.find({"comments":{"$elemMatch":{"author":"joe","score":{"$gte":5}}}})//嵌套查询，仅当嵌套的元素是数组时使用,
db.foo.find({"$where":"this.x+this.y==10"})//复杂的查询，$where当然是非常方便的，但效率低下。对于复杂查询，考虑的顺序应当是正则->MapReduce->$where
db.foo.find({"$where":"function(){returnthis.x+this.y==10;}"})//$where可以支持javascript函数作为查询条件
db.foo.find().sort({"x":1}).limit(1).skip(10);//返回第(10,11]条，按"x"进行排序;三个limit的顺序是任意的，应该尽量避免skip中使用large-number