MongoDB查询更新操作语句命令大全
查询所有数据
SQL:SELECT * FROM users
db.users.find()
条件查询
SQL:SELECT * FROM users WHERE username= 'Lucy'
db.users.find({"username" : "Lucy"})
条件查询(AND)
SQL:SELECT * FROM users WHERE username = "Lucy" AND age = 18
db.users.find({"username" : "Lucy", "age" : 18})
查询特定列
SQL:SELECT username, email FROM users
db.users.find({}, {"username" : 1, "email" : 1})
查询特定列(阻止_id
返回)
SQL:SELECT username FROM users
db.users.find({}, {"username" : 1, "_id" : 0})
区间范围查询
SQL:SELECT * FROM users WHERE age >=18 AND age <= 30
db.users.find({"age" : {"$gte" : 18, "$lte" : 20}})
查找username
的值不等于Lucy
的这个条件的所有字段
SQL:SELECT * FROM users WHERE username <> "Lucy"
db.users.find({"username" : {"$ne" : "Lucy"}})
IN
SQL:SELECT * FROM users WHERE age IN(18, 20, 22)
db.users.find({"age" : {"$in" : [18, 20, 22]}})
NOT IN
SQL:SELECT * FROM users WHERE age NOT IN(18, 20, 22)
db.users.find({"age" : {"$nin" : [18, 20, 22]}})
OR
SQL:SELECT * FROM users WHERE age = 18 OR adopt = true
db.users.find({"$or" : [{"age" : 18}, {"adopt" : true}]})
NOT
SQL:SELECT * FROM users WHERE NOT(age = 18)
db.users.find({"$not": {"age" : 18}})
如果直接通过find({"username" : null})
进行查询,那么连带"没有username"
的纪录一并筛选出来
SQL:SELECT * FROM users WHERE username IS NULL
db.users.find({"username" : {"$in" : [null], "$exists" : true}})
LIKE
模糊查询
SQL:SELECT * FROM users WHERE username LIKE "%A%"
db.users.find({"username" : /A/})
LIKE
模糊查询(以x开头)
SQL:SELECT * FROM users WHERE username LIKE "A%"
db.users.find({"username" : /^A/})
查询结果使用ORDER BY
字段ASC
排序
SQL:SELECT * FROM users WHERE username = "Lucy" ORDER BY _id ASC
db.users.find({"username ": "Lucy"}).sort({_id : 1})
查询结果使用ORDER BY
字段DESC
排序
SQL:SELECT * FROM users WHERE userName ="Lucy" ORDER BY _id DESC
db.users.find({"username ": "Lucy"}).sort({_id : -1})
mongoDB SQL
db.users.find({"name" : /joey?/i})
db.users.find({fruit : {$all : ["apple", "banana"]}})
db.users.find({"fruit.2" : "peach"})
db.users.find({"fruit" : {"$size" : 3}})
db.users.findOne(criteria, {"comments" : {"$slice" : 10}})
db.users.find({"name.first" : "Joe", "name.last" : "Schmoe"})
db.users.find({"comments" : {"$elemMatch" : {"author" : "joe", "score" : {"$gte" : 5}}}})
db.users.find({"$where" : "this.x + this.y == 10"})
db.users.find({"$where" : "function() { return this.x + this.y == 10; }"})
db.users.find().sort({"x" : 1}).limit(1).skip(10)
批量操作
更新
db.form_product.find({"xisShelf": true}).forEach(function(item){
db.form_product.update({"id": item.id},{$set:{"xisShelf": false}});
})