MySQL to MongoDB – An Admin Cheat Sheet

The database and tables are created explicitly through the PHP admin panel or defined within a script i.e

Creating a Database

CREATE DATABASE database_name

Creating a table

CREATE TABLE users (
    id MEDIUMINT NOT NULL
        AUTO_INCREMENT,
    UserId Varchar(30),
    Age Number,
    Gender char(1),
    Name VarChar(222),
    PRIMARY KEY (id)
)

The database can be created implicitly or explicitly. Implicitly during the first document insert the database and collection are created as well as an automatic _id field being added to this document.

db.users.insert( {
    UserId: "user1",
    Age: 55,
    Name: "Berry Hellington",
    Gender: "F",
 } )

You can also create the database explicitly by running this comment in the Mongo Shell

db.createCollection("users")

In MySQL, you have to specify the columns in the table you are creating as well as setting some validation rules like in this example the type of data and length that goes to a specific column. In the case of MongoDB, it is not a must to define neither the fields each document should hold nor the validation rules the specified fields should hold.

However, in MongoDB for data integrity and consistency you can set the validation rules using the JSON SCHEMA VALIDATOR

Dropping a table

DROP TABLE users

db.users.drop()

This are statements for deleting a table for MySQL and collection in the case of MongoDB.

Adding a new column called join_date

ALTER TABLE users ADD join_date DATETIME

Removing the join_date column if already defined

ALTER TABLE users DROP COLUMN join_date DATETIME

Adding a new field called join_date

db.users.updateMany({},{$set:{‘join_date’: new Date()})

This will update all documents in the collection to have the join date as the current date.

Removing the join_date field if already defined

db.users.updateMany({},{$unset:{‘join_date’: “”})

This will remove the join_date field from all the collection documents.

Altering the structure of the schema by either adding or dropping a column/field.

Since the MongoDB architecture does not strictly enforce on the document structure, documents may have fields different from each other.

Creating an index with the UserId column ascending and Age descending

CREATE INDEX idx_UserId_asc_Age_desc
ON users(UserId)

Creating an index involving the UserId and Age fields.

db.users.ensureIndex( { UserId: 1, Age: -1 } )

Indices are generally created to facilitate the querying process.

INSERT INTO users(UserId,
                  Age,
                  Gender)
VALUES ("user1",
        25,
        "M")

db.users.insert( {
       UserId: "bcd001",
       Age: 25,
       Gender: "M",
     Name: "Berry Hellington",
} )

Inserting new records.

DELETE FROM users
WHERE Age = 25

db.users.deleteMany( { Age = 25 } )

Deleting records from the table/collection whose age is equal to 25.

DELETE FROM users

db.users.deleteMany({})

Deleting all records from the table/collection.

SELECT * FROM users

db.users.find()

Returns all records from the users table/collection with all columns/fields.

SELECT id, Age, Gender FROM users

db.users.find(
   { },
   { Age: 1, Gender: 1 }
)

Returns all records from the users table/collection with Age, Gender and primary key columns/fields.

SELECT  Age, Gender FROM users

db.users.find(
   { },
 { Age: 1, Gender: 1,_id: 0}
)

Returns all records from the users table/collection with Age and Gender columns/fields. The primary key is omitted.

SELECT * FROM users WHERE Gender = “M”

db.users.find({ Gender: "M"})

Returns all records from the users table/collection whose Gender value is set to M.

SELECT Gender FROM users WHERE Age = 25

db.users.find({ Age: 25}, { _id: 0, Gender: 1})

Returns all records from the users table/collection with only the Gender value but whose Age value is equal to 25.

SELECT * FROM users WHERE Age = 25 AND Gender = ‘F’

db.users.find({ Age: 25, Gender: "F"})

Returns all records from the users table/collection whose Gender value is set to F and Age is 25.

SELECT * FROM users WHERE  Age != 25

db.users.find({ Age:{$ne: 25}})

Returns all records from the users table/collection whose Age value is not equal to 25.

SELECT * FROM users WHERE Age = 25 OR Gender = ‘F’

db.users.find({$or:[{Age: 25, Gender: "F"}]})

Returns all records from the users table/collection whose Gender value is set to F or Age is 25.

SELECT * FROM users WHERE Age > 25

db.users.find({ Age:{$gt: 25}})

Returns all records from the users table/collection whose Age value is greater than 25.

SELECT * FROM users WHERE Age <= 25

db.users.find({ Age:{$lte: 25}})

Returns all records from the users table/collection whose Age value is less than or equal to 25.

SELECT Name FROM users WHERE Name like "He%"

db.users.find(
  { Name: /He/ }
)

Returns all records from the users table/collection whose Name value happens to have He letters.

SELECT * FROM users WHERE Gender = ‘F’ ORDER BY id ASC

db.users.find( { Gender: "F" } ).sort( { $natural: 1 } )

Returns all records from the users table/collection whose Gender value is set to F and sorts this result in the ascending order of the id column in case of MySQL and time inserted in the case of MongoDB.

SELECT * FROM users WHERE Gender = ‘F’ ORDER BY id DESC

db.users.find( { Gender: "F" } ).sort( { $natural: -1 } )

Returns all records from the users table/collection whose Gender value is set to F and sorts this result in the descending order of the id column in case of MySQL and time inserted in the case of MongoDB.

SELECT COUNT(*) FROM users

db.users.count()

or

db.users.find().count()

Counts all records in the users table/collection.

SELECT COUNT(Name) FROM users

db.users.count({Name:{ $exists: true }})

or

db.users.find({Name:{ $exists: true }}).count()

Counts all records in the users table/collection who happen to have a value for the Name property.

SELECT * FROM users LIMIT 1

db.users.findOne()

or

db.users.find().limit(1)

Returns the first record in the users table/collection.

SELECT * FROM users WHERE Gender = ‘F’ LIMIT 1

db.users.find( { Gender: "F" } ).limit(1)

Returns the first record in the users table/collection that happens to have Gender value equal to F.

SELECT * FROM users LIMIT 5 SKIP 10

db.users.find().limit(5).skip(10)

Returns the five records in the users table/collection after skipping the first five records.

UPDATE users SET Age = 26 WHERE age > 25

db.users.updateMany(
  { age: { $gt: 25 } },
  { $set: { Age: 26 } }
)

This sets the age of all records in the users table/collection who have the age greater than 25 to 26.

UPDATE users SET age = age + 1

db.users.updateMany(
  {} ,
  { $inc: { age: 1 } }
)

This increases the age of all records in the users table/collection by 1.

UPDATE users SET age = age - 1
WHERE id = 1

db.users.updateMany(
  {} ,
  { $inc: { age: -1 } }
)

This decrements the age of the first record in the users table/collection by 1.