MongoDB and MySQL common Operation statement Control _MONGODB

One, MongoDB to MySQL commonly used SQL statement corresponding realization

Copy Code code as follows:

——————————————
Mysql:
SELECT * from user
Mongo:
Db.user.find ()
——————————————
Mysql:
SELECT * FROM user WHERE name = ' Foobar '
Mongo:
Db.user.find ({' name ': ' Foobar '})
——————————————
Mysql:
INSERT into user (' name ', ' age ') VALUES (' Foobar ', 25)
Mongo:
Db.user.insert ({' name ': ' Foobar ', ' Age ': 25})
——————————————
If you want to add an email to this column
Mysql:
ALTER TABLE User ...
Mongo:
Db.user.insert ({' name ': ' Foobar ', ' age ':, ' email ': ' foo@bar.com '})
——————————————
Mysql:
DELETE * from user
Mongo:
Db.user.remove ({})
——————————————
Mysql:
DELETE from user WHERE age < 30
Mongo:
Db.user.remove ({' age ': {$lt: 30}})
$GT:>; $gte: >=; $lt:<; $lte: <=; $ne:!=
——————————————
Mysql:
UPDATE user SET ' age ' = + WHERE ' name ' = ' Foobar '
Mongo:
Db.user.update ({' name ': ' Foobar '}, {$set: {' age ': 36}}})
——————————————
Mysql:
UPDATE user SET ' age ' = ' age ' + 3 WHERE ' name ' = ' Foobar '
Mongo:
Db.user.update ({' name ': ' Foobar '}, {$inc: {' Age ': 3}}})
——————————————
Mysql:
SELECT COUNT (*) from user WHERE ' name ' = ' Foobar '
Mongo:
Db.user.find ({' name ': ' Foobar '}). Count ()
——————————————
Mysql:
SELECT * from user limit 10,20
Mongo:
Db.user.find (). Skip (limit) (20)
——————————————
Mysql:
SELECT * FROM user WHERE "age" in (25,35,45)
Mongo:
Db.user.find ({' age ': {$in: [25,35,45]}})
——————————————
Mysql:
SELECT * from User order by age DESC
Mongo:
Db.user.find (). Sort ({' Age ':-1})
——————————————
Mysql:
SELECT DISTINCT (name) from user WHERE age > 20
Mongo:
Db.user.distinct (' name ', {' age ': {$lt: 20}})
——————————————
Mysql:
SELECT name, sum (marks) from user GROUP by name
Mongo:
Db.user.group ({
key:{' name ': true},
cond:{' name ': ' foo '},
Reduce:function (Obj,prev) {prev.msum + = Obj.marks;},
initial:{msum:0}
})
——————————————
Mysql:
SELECT name from user WHERE age < 20
Mongo:
Db.user.find (' This.age < ', {name:1})

Second, MongoDB database and MySQL operation control

On the left is the MONGODB query statement, and the right side is the SQL statement. It is very convenient to use the control.

Copy Code code as follows:

Db.users.find () SELECT * from Users

Db.users.find ({"Age": in}) select * from users where age = 27

Db.users.find ({"username": "Joe", "Age":}) SELECT * from the users where "username" = "Joe" and age = 27

Db.users.find ({}, {"username": 1, "email": 1}) Select Username, email from users

Db.users.find ({}, {"username": 1, "_id": 0})//no case//Instant plus column filter, _id also return; You must explicitly block _id return

Db.users.find ({"Age": {"$gte": @, "$lte"}) select * from the users where age >=18 and <=//$lt (<) $l Te (<=) $gt (>) $gte (>=)

Db.users.find ({"username": {"$ne": "Joe"}}) select * from users where username <> "Joe"

Db.users.find ({"Ticket_no": {"$in": [725, 542, 390]}}) select * from Users where ticket_no in (725, 542, 390)

Db.users.find ({"Ticket_no": {"$nin": [725, 542, 390]}}) select * from users where ticket_no not in (725, 542, 390)

Db.users.find ({"$or": [{"Ticket_no": 725}, {"Winner": True}]}) select * Form users where Ticket_no = 725 or winner = TR Ue

Db.users.find ({"Id_num": {"$mod": [5, 1]}}) select * from Users where (id_num mod 5) = 1

Db.users.find ({"$not": {' Age ':}}) select * from users ' where not ' (age = 27)

Db.users.find ({"username": {"$in": [null], "$exists": True}}) select * from the users where username is null//if direct through find ( {"username": null}) For enquiries, then the "no username" record is screened together.

Db.users.find ({"Name":/joey?/i})//Regular query, value is an expression that conforms to Pcre

Db.food.find ({fruit: {$all: ["Apple", "Banana"]})//array of queries, field fruit, contains both "Apple" and "banana" records

Db.food.find ({"fruit.2": "Peach"})//array of queries, field fruit, 3rd (starting from 0) element is peach Record

Db.food.find ({"Fruit": {"$size": 3}})//array of queries, the number of query arrays is 3 records, $size cannot be combined with other operators before

Db.users.findOne (criteria, {"Comments": {"$slice": 10}})//array of queries, returns only the first 10 of arrays comments, can also {"$slice": -10}, {"$slice": [ 23, 10]}; Return the last 10, and the middle 10 respectively

Db.people.find ({"Name.first": "Joe", "Name.last": "Schmoe"})//nested query

Db.blog.find ({"Comments": {"$elemMatch": {"author": "Joe", "score": {"$gte": 5}}})//nested query, only used when nested elements are arrays.

Db.foo.find ({"$where": "this.x + This.y = 10"})//complex query, $where of course is very convenient, but inefficient. For complex queries, the order of consideration should be regular-> MapReduce-> $where

Db.foo.find ({"$where": "function () {return this.x + THIS.Y = n}"})//$where can support JavaScript functions as query criteria

Db.foo.find (). Sort ({"X": 1}). Limit (1). Skip (10); Return to article (10, 11), sorted by "X"; The order of the three limit is arbitrary and you should try to avoid using Large-number in Skip