Category Archives: MongoDB

Create index on MongoDB

Create index on MongoDB

#Create index on employees table with column employee_id in ascending order

db.employees.ensureIndex( { emp_id: 1 } )

In SQL:
create index on idx_emp on employees (emp_id);

# create index on employees table with column emp_id and age(desc order)

db.employees.ensureIndex( { emp_id: 1, age: -1 } )

In SQL:
create index on idx_emp1 on employees(emp_id,age desc);

Advertisements

Create/alter/drop table(collection) in MongoDB

Create/alter/drop table(collection) in MongoDB

For Create Table in SQL:

CREATE TABLE employees( id MEDIUMINT NOT NULL  AUTO_INCREMENT,
    emp_id Varchar(30),
    age Number,
    status char(1),
    PRIMARY KEY (id) )
   
For Create table in MongoDB:
—————————-
db.employees.insert( {
    emp_id: “RAM”,
    age: 50,
    status: “A”
 } )

 Or

 db.createCollection(“employees”);

Add column in SQL:
———————
ALTER TABLE employees ADD join_date DATETIME

Add column in collection of MongoDB:
————————————
db.employees.update(
    { },
    { $set: { join_date: “” } },
    { multi: true }
)

Remove column in SQL:
———————-
alter table employees drop column join_date

Remove column in MongoDB:
—————————
db.employees.update(
    { },
    { $unset: { join_date: “” } },
    { multi: true }
)

Drop table SQL:
—————-
drop table users:

Drop collection in Mongdb:
—————————
In MongoDB:
db.users.drop();

Delete in MongoDB

Delete in MongoDB

Delete specific data from tables(collection)
———————————————
DELETE FROM employees WHERE status = “D”

db.employees.remove( { status: “D” } )

example:
> db.employees.remove( { status: “N” } )
WriteResult({ “nRemoved” : 1 })

Delete all data from table(collection)
—————-
DELETE FROM employees

db.employees.remove({})

Update query on MongoDB

Update query on MongoDB

UPDATE employees SET status = “C” WHERE age > 25

MongoDB:
———–
db.employees.update(  { age: { $gt: 25 } }, { $set: { status: “C” } },    { multi: true } )

> db.employees.update(  { age: { $gt: 25 } }, { $set: { status: “C” } },    { multi: true } )
WriteResult({ “nMatched” : 2, “nUpserted” : 0, “nModified” : 2 })

UPDATE employees SET age = age + 3 WHERE status = “A”;

db.employees.update(    { status: “A” } ,    { $inc: { age: 3 } } ,{ multi: true })