mongoDB and mysql query comparison

Connect with

MongoDB

This post is about query comparison between mysql and mongodb.

In this post, if you have used mysql and not used mongodb, then you can corelate one from another. This post made simple for you to correlate any query in mongodb and its counter part in mysql. Based on the demand , I have written this post , many of our reader requested to write one post for the novice mongodb user, who already know the basic of mysql. its very easy for you to find , what is the query in mongodb for the counter part of mysql. You can run these query to mongodb terminal or any client like Robomongo, MongoChef ..

1. Creation of table

In mysql you have to create the table but in mongoDB if you have permission on user then collection can be build by mongodb.

MySQLmongoDBdescription
CREATE TABLE user (name VARCHAR(50), age NUMBER)db.createCollection("user")to create table/collection.

2. Select queries in mongodb/mysql

MySQLmongoDBdescription
SELECT * FROM userdb.user.find({})to select all
SELECT name, age FROM userdb.user.find({}, {name: 1, age: 1, _id:0}) to project column/fields
SELECT name, age FROM user WHERE age = 30db.user.find({age: 30}, {name: 1, age: 1, _id:0})select with project
SELECT * FROM user WHERE age > 30,db.user.find({age: {$gt: 30}})range based selection
SELECT * FROM user WHERE age <= 30, db.user.find({age: {$lte: 30}}) range based selection on numeric value
SELECT * FROM user WHERE age > 30 AND age < 40, db.user.find({age: {$gt: 30, $lt: 40}}),range based selection on numeric value
SELECT * FROM user WHERE age = 32 AND name = 'ranjeet', db.user.find({age: 32, name: 'ranjeet'});equality and range both
SELECT * FROM user WHERE age = 30 OR name = 'ranjeet', db.user.find({$or:[{age:30}, {name: 'ranjeet'}]});equality and range both
SELECT * FROM user WHERE age = 30 ORDER BY name ASC;db.user.find({age: 30}).sort({name: 1});equality and sorting on name with ascending
SELECT * FROM user ORDER BY name DESC ;db.user.find().sort({name: -1});equality and sorting on name with descending
SELECT * FROM user WHERE name LIKE '%ran%' ;db.user.find({name: /ran/});contains selection
SELECT * FROM user WHERE name LIKE 'ran%' ;db.user.find({name: /^ran/}); regex start with provided word
SELECT * FROM user LIMIT 10 SKIP 15;db.user.find().skip(15).limit(10); for pagination with offset and length
SELECT * FROM user LIMIT 1; db.user.findOne();select first record.
SELECT DISTINCT name FROM user;db.user.distinct("name");with distinct
SELECT COUNT(1) FROM user; db.user.count();count records/document
SELECT COUNT(*) FROM user WHERE AGE > 20db.user.find({age: {$gt: 20}}).count()provided condition and then count records/document
SELECT COUNT(AGE) FROM user;db.user.find({age: {$exists: true}}).count();existence check

3. Insert records/documents in mongodb/mysql

MySQLmongoDBdescription
INSERT INTO user(name\,age) VALUES ('ranjeet'\, 30);db.user.insert({name: "ranjeet"\, age: 30});to insert record/document

4. update queries in mongodb/mysql

MySQLmongoDBdescription
UPDATE user SET age = 30 WHERE name = 'anushka'; db.user.update({name: "anushka"}\, {$set: {age: 30}}\, {multi: true});update multiple matching
UPDATE user SET age = age + 2 WHERE name = 'ranjeet' ;db.user.update({name: "ranjeet"}\, {$inc: {age: 2}}\, {multi: true});update multiple matching
DELETE FROM user WHERE name = 'ranjeet' , db.user.remove({name: "ranjeet"});to delete matching condition

5. Index creation in in mongodb/mysql

MySQLmongoDBdescription
CREATE INDEX ON user (name ASC);db.user.createIndex({name: 1});to create index with asc order
CREATE INDEX ON user (name ASC, age DESC); db.user.ensureIndex({name: 1, age: -1});to create multi key index with asc and esc

6. explain query plan mongodb and mysql

MySQLmongoDBdescription
EXPLAIN SELECT * FROM user WHERE age = 32;db.user.find({age: 32}).explain(); for explain query

7. query with matching criteria in mongodb

mongoDBdescription
{key1: {$nin: [5, 'ranjeet']}};returns doc with anything but not contains 5 or 'ranjeet'.
{key1: {$size: 3}};return docs where array size of key1 exactly 3 elements.
{key1: {$exists: true}} return docs containing a key1 field.
{key1: {$not: {$type: 2}}};returns where key is not a string. $not negates any of the other query operators.

Your suggestions or comment are welcome to improve this post. Happy Learning 🙂


Connect with

Leave a Reply

Your email address will not be published. Required fields are marked *