3 Jan 2014

MongoDB - Basic DB operations / A CRUD Introduction

1/03/2014

Lets come a step closer to start using and understanding mongodb. We all have been quite in a habit in understanding database via some of the very conventional databases that we have been using such as Oracle, MySQL, SQL etc. Although MongoDB is not a substitute to these relational databases (as mostly mistaken), even then lets try to drive a analogy between MongoDB and some of the conventiaonal databases we have been using.  

Terminology


Sql term "database" remains the same in MongoDB as well.

Sql term/concept "table" translates to "collection" in MongoDB.

Sql term/concept "column" translates to "field" in MongoDB.

Sql term/concept "row" translates to "document / BSON document" in MongoDB.

Sql term/concept "index" remains same in MongoDB.

Sql term/concept "primary key" also remains same in MongoDB, the only difference is that in MongoDB all collection has a primary key _id which is a auto generated value in MongoDB.

Examples

Lets take now some examples along some very CRUD operations differ from how we have been doing in the conventional databases.

Note - To execute all the commands in MongoDB, simply go to the command prompt and type in command mongo at MDB_HOME location on the command prompt.

To switch to the database and or create a database, simply use the command 'use <db_name>', e.g. use nimblegeek

Figure 1: Screen shot showing switch / create database in MongoDB

CREATE

Lets take an example to create a table or collection

SQL
CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    username Varchar(30),
    first_name Varchar(50),
    last_name Varchar(50),
    age Number,
    status char(1),
    PRIMARY KEY (id)
)

MONGODB
db.users.insert( {
    username: "anup_gaurav",
    first_name: "Anup",
    last_name: "Gaurav"
    age: 55,
    status: "A"

 } )

ALTER

Lets take an example to alter data in MongoDB and see how its different from the conventional way

SQL
ALTER TABLE users ADD create_date DATETIME;

MONGODB
db.users.update( 
{},
{$set:{create_date: new Date()}},
{multi:true}
)

Note - The mongoDB update method has three parts, the first one is for the condition that you want to impose, the second part is to set and unset things and the last part is to signify if multiple records can be updated or not.

Lets take another example where we want to drop a field from a MongoDB document

SQL
ALTER TABLE users DROP create_date DATETIME;

MONGODB
db.users.update( 
{},
{$unset:{create_date: ""}},
{multi:true}
)

INDEX

Lets now see an example as to how we can create indexes in a MongoDB collection.

SQL
CREATE INDEX user_name_age_index ON users (username, age DESC);

MONGODB
db.users.ensureIndex({username:1,age:-1})

Where in the above statement 1 signifies ASC and -1 signifies DESC.

INSERT

Lets now try to drive an analogy as how to insert record in SQL and MongoDB

SQL
Insert into users(username,first_name,last_name,age,status) values ('anup_gaurav','Anup','Gaurav',32,'A')

MONGODB
db.users.insert({username:"anup_gaurav",first_name:"Anup",last_name="Gaurav",age:32,status="A"});

SELECT

Well lets try to cater to a variety of select statements that we can think of

SQL 
SELECT * FROM USERS;

MONGODB
db.users.find({});

SQL/MangoDB Select Query with where clause 
SQL 
SELECT * FROM USERS WHERE age=20;

MONGODB
db.users.find({age:20});

SQL/MangoDB Select Query with where clause having greater than operator
SQL 
SELECT username,first_name,last_name from users WHERE age > 20;

MONGODB 
db.users.find({age:{$gt:20},{username:1,first_name:1,last_name:1,_id:0}}

SQL/MangoDB query with not equal to and AND operator
SQL
SELECT * FROM USERS WHERE age != 20 AND status='A';

MONGODB 
db.users.find({age:{$ne:20},status:'A'});

SQL/MangoDB query with less that equal to and AND operator
SQL
SELECT * FROM USERS WHERE age > 20 AND age <= 60;

MONGODB 
db.users.find({age:{$gt:20,$lte:60}});

SQL/MangoDB Select query with OR operator
SQL
SELECT * FROM USERS WHERE age > 20 OR status = 'A';

MONGODB
db.users.find({$or:[{age:{$gt:20}},{status:'A'}]});

SQL/MangoDB Select query with LIKE operator
SQL
SELECT * FROM USERS WHERE first_name like '%Ag%';

MONGODB
db.users.find({first_name:/bc/});

SQL
SELECT * FROM USERS WHERE first_name like 'Ag%';

MONGODB
db.users.find({first_name:/^bc/});

SQL/MangoDB Select query with ORDER_BY operator
SQL
SELECT * FROM USERS WHERE status = 'A' ORDER BY username DESC;

MONGODB
db.users.find({status:'A'}).sort({username:-1});

We use 1 for ASC and -1 for DESC while sorting the result in a MongoDB query.

SQL/MangoDB Select query with COUNT operator
SQL
SELECT count(*) FROM USERS;

MONGODB 
db.users.find({}).count();

SQL
SELECT count(username) FROM USERS;

MONGODB
db.users.count({username:{$exists:true}});

SQL/MangoDB Select query with DISTINCT operator
SQL
SELECT DISTINCT(username) FROM USERS;

MONGODB 
db.users.distinct("username");

SQL/MangoDB Select query with LIMIT operator
SQL
SELECT * FROM USERS LIMIT 1;

MONGODB
db.users.findOne(); or db.users.find().limit(1);

UPDATE

SQL
UPDATE USERS SET status='I' where age => 58;

MONGODB
db.users.update( 
{age:{$gte:58}},
{$set:{status:'I'}},
{multi:true}
);


DELETE

SQL
DELETE FROM USERS WHERE age => 58;

MONGODB
db.users.remove({age:{$gte:58}});

I guess that enough for you now to get started and doing DB. 

Written by

We are Creative Blogger Theme Wavers which provides user friendly, effective and easy to use themes. Each support has free and providing HD support screen casting.

0 comments:

Post a Comment

 

© 2013 NimbleGeek. All rights resevered. Designed by Templateism

Back To Top