Wamp and MongoDB - II

In our previous article, we have seen installation of MongoDB on our C: drive (Windows Environment) at path "c:\mongodb\" and installed it as a Win32 Service. Now let's try to work with a bit. Let's do some basic Database stuffs.

Open command prompt and set path to the "c:\mongodb\bin" directory as shown below :

path=%path%;c:\mongodb\bin\

%path% refers to "path" environment variable; We are just appending new path to it. 

Next, we type "mongo" to start the Mongo Shell where we can run certain commands to do specific database tasks. See below what happens when we start the Mongo shell : 

MongoDB uses "Collections" which is similar to "Tables" in any R/DBMS. It also uses JSON like name/field value pair and such pairs are called "Documents". Here, JSON field value pairs are in BSON (Binary JSON) format which have additional type information. 

Next, we would try some commands at the prompt. 
   
1. db :: Will show you current Database
2. show dbs :: will show list of Databases
3. use mytestdb :: (without quote), Switch to a Database "mytestdb" even though it may not exist.

Now, we'll try CRUD (Create, Read, Update, Delete) operations. Let's insert some data inside a table called 'students' in 'myDatabase' Database.

1. use myDatabase    
2. db.myDatabase.find()    
3. db.myDatabase.insert({'fname':'Atal','lname':'Singh','roll':1})
4. db.myDatabase.insert({'fname':'Arin','lname':'Sen','roll':2})
5. db.myDatabase.insert({'fname':'Avik','lname':'Roy','roll':3})
6. db.myDatabase.insert({'fname':'Anik','lname':'Paul','roll':4})
7. db.myDatabase.insert({'fname':'Amal','lname':'Sen','roll':5})
8. db.myDatabase.insert({'fname':'Ajay','lname':'Dev','roll':6})
9. db.myDatabase.find()  
10. show collections  

The first command selects our database myDatabase.
The second command issues a SELECT SQL command with no parameter specified. So it returns all the record in the collection 'myDatabase'.
The 3 to 8 commands inserts individual records into that collection.
The 9th command again returns/shows all the records in that collection. 
The 10th command shows all the tables/collections within current selected database

Now we will try to filter the return records, i.e add "WHERE" clause to "SELECT" command. 

Say, we require names of all the students whose last name is 'Sen'. So we need issue a command like this :
db.myDatabase.find({ 'lname':'Sen' })

Here we wanted an exact match of lastname 'Sen'. However for a LIKE type query, we need issue following command.
db.myDatabase.find({ 'lname': /Sen/ })

We just used a REGEXP to specify a LIKE Query.

The find() accepts 2 parameters, first parameter is for filtering (WHERE clause) and second parameter is for selected/returned fields/columns.

Some more SELECT Query examples :: 

1) Select * from myDatabase where roll < 3 ORDER by roll DESC LIMIT 2
   db.myDatabase.find({roll:{$lt:3}}).limit(2).sort({'roll':-1})

  See that,  after the field roll, we used a sub-document { $lt : 3 } where $lt is an operator and it takes 3 as argument. 

   If we need to look for those records where roll < 10 and roll > 5 then we need to write this way: 
   db.myDatabase.find({roll:{$lt:10, $gt:5}})

2) Select fname from myDatabase where lname like '%Sen%' and status = 1
   db.myDatabase.find({'status':1, 'lname':/Sen/}, {'fname':1})
   
3) Select fname, lname from myDatabase where ( status = 0 OR status = 1) AND lname like "%Sen%"
   db.myDatabase.find( 
      { 'lname':/Sen/, 
 $or:[{'status':1}, {'status' : 0}] 
  }, {'fname':1, 'lname':1})

4) Select * from myDatabase where ( roll > 5 and roll <> 8 ) OR lname like "%Sen%"
       db.myDatabase.find(
     {  $or:[ {'lname':/Sen/}, 
      {$and   : [{'roll':{$ne:8}}, {'roll':{$gt:5}}]}
    ]
     },{'_id':0,'roll':1})
   
   db.myDatabase.find(
     { 
     $or:[ {'lname':/Sen/}, {'roll':{$ne:8, $gt:5}}] 
  },{'_id':0,'roll':1})
   
   Any of the above solution would work. 
   Note that '$ne' in MongoDB means '<>' in SQL, '$gt' in MongoDB means '>' in SQL.
   Also '$gte' in MongoDB means '>=' in SQL.

5) Select * from myDatabase order by fname, lname DESC limit 10
   db.myDatabase.find().sort({'fname':1, 'lname':-1}).limit(10);
   
6) Select count(*) from myDatabase
        db.myDatabase.find().count()
  db.myDatabase.count()

Any of the above commands will work.

7) Select distinct(fname) from myDatabase
    db.myDatabase.distinct('fname')

Some UPDATE Query examples :: 
   
1) Update myDatabase SET status = 5 where age > 16
   db.myDatabase.update(
       {age:{$gt:16}}, 
       {$set : {'status':5}}, {multi:true})

2) UPDATE myDatabase set roll = roll + 3 where age < 15   
   db.myDatabase.update(
       {age:{$lt:15}}, 
       {$inc : {'roll':3}}, {multi:true})

Some DELETE Query examples ::    

1) DELETE from myDatabase 
   db.myDatabase.remove()

2) DELETE from myDatabase where roll > 100
   db.myDatabase.remove({roll:{$gt:100}})   
   
3) DELETE from myDatabase where roll > 200 and status = 1
   db.myDatabase.remove({$and:[{roll:{$gt:200}}, {status:1} ]})      
   
In our next article, we'll write PHP code to do the above CRUD operations using MongoDB classes.