1. select查询
mongodb使用find或者findOne来查询:find批量查询。
findOne是查询一条记录。
find有两个参数： 第一个查询条件， 第二个查询返回的字段。下面是mongodb与sql查询的对应关系表：
| SQL SELECT Statements |
MongoDB find() Statements |
| SELECT * FROM users |
db.users.find() |
| SELECT id, user_id, status FROM users |
db.users.find( { }, { user_id:1,status:1 } ) |
| SELECT user_id,status FROM users |
db.users.find( { }, { user_id:1,status:1,_id:0 } ) |
| SELECT * FROM users WHERE status="A" |
db.users.find( { status:"A" } ) |
| SELECT user_id,status FROM users WHERE status="A" |
db.users.find( { status:"A" }, { user_id:1,status:1,_id:0 } ) |
| SELECT * FROM users WHERE status!="A" |
db.users.find( { status: {$ne:"A" } } ) |
| SELECT * FROM users WHERE status="A" AND age=50 |
db.users.find( { status:"A", age:50 } ) |
| SELECT * FROM users WHERE status="A" OR age=50 |
db.users.find( { $or: [ {status:"A" } , { age:50 } ] } ) |
| SELECT * FROM users WHERE age>25 |
db.users.find( { age: {$gt:25 } } ) |
| SELECT * FROM users WHERE age<25 |
db.users.find( { age: {$lt:25 } } ) |
| SELECT * FROM users WHERE age>25 AND age<=50 |
db.users.find( { age: {$gt:25,$lte:50 } } ) |
| SELECT * FROM users WHERE user_id like "%bc%" |
db.users.find( {user_id:/bc/ } ) |
| SELECT * FROM users WHERE user_id like "bc%" |
db.users.find( {user_id:/^bc/ } ) |
| SELECT * FROM users WHERE status="A" ORDER BY user_id ASC |
db.users.find( {status:"A" } ).sort( {user_id:1 } ) |
| SELECT * FROM users WHERE status="A" ORDER BY user_id DESC |
db.users.find( {status:"A" } ).sort( {user_id:-1 } ) |
| SELECT COUNT(*) FROM users |
db.users.count() or db.users.find().count() |
| SELECT COUNT(user_id) FROM users |
db.users.count( {user_id: {$exists:true } } ) or db.users.find( {user_id: {$exists:true } } ).count() |
| SELECT COUNT(*) FROM users WHERE age>30 |
db.users.count( {age: {$gt:30 } } ) or db.users.find( {age: {$gt:30 } } ).count() |
| SELECT DISTINCT(status) FROM users |
db.users.distinct("status" ) |
| SELECT * FROM users LIMIT 1 |
db.users.findOne() or db.users.find().limit(1) |
| SELECT * FROM users LIMIT 5 SKIP10 |
db.users.find().limit(5).skip(10) |
| EXPLAIN SELECT * FROM users WHERE status="A" |
db.users.find( {status:"A" } ).explain() |
2. insert插入?
| SQL INSERT Statements |
MongoDB insert() Statements |
| INSERT INTO users(user_id, age, status) VALUES ("bcd001", 45, "A") |
db.users.insert( {user_id:"bcd001",age:45,status:"A"} ) |
3. update更新
| SQL Update Statements |
MongoDB update() Statements |
| UPDATE users SET status="C" WHERE age>25 |
db.users.update( { age: { $gt:25 } }, { $set: { status:"C" } }, { multi:true } ) |
| UPDATE users SET age=age+3 WHERE status="A" |
db.users.update( { status:"A" } , { $inc: { age:3 } }, { multi:true } ) |
4. delete 删除
| SQL Delete Statements |
MongoDB remove() Statements |
| DELETE FROM users WHERE status="D" |
db.users.remove( { status:"D" } ) |
| DELETE FROM users |
db.users.remove({}) |