MongoDB and SQL Operations mapping

Example

The following mapping is an example of the following document:

{  _id:ObjectId("509a8fb2f3f4948bd2f983a0"),  user_id:"abc123",  age:55,  status:'A'}

Create or Altercreate table or collection

CREATETABLE people (                       db.people.insertOne( {    idNOTNULL                       "abc123",        AUTO_INCREMENT,                         55,    Varchar(30),                        "A"    Number,                                 } )    char(1),    PRIMARYKEY (id))

ADD column or key

ALTERTABLE people                          db.people.updateMany(ADD join_date DATETIME                          { },                                                newDate() } }                                                )

Delete column or key

ALTERTABLE people                          db.people.updateMany(DROPCOLUMN join_date                           { },                                                "join_date""" } }                                                )

Create Single Index

CREATEINDEX idx_user_id_asc                1 } )ON people(user_id)

Create Union and DESC Index

CREATEINDEX                                1, age: -1 } )       idx_user_id_asc_age_descONDESC)

Drop table or Collection

DROPTABLE people                           db.people.drop()

See also:

• db.collection.insertOne()
• db.collection.insertMany()
• db.createCollection()
• db.collection.updateMany()
• $set
• $unset
• db.collection.createIndex()
• Indexes
• db.collection.drop()
• Data Modeling Concepts.

Insertinsert One

INSERTINTO people(user_id,                 db.people.insertOne(                  age,                          "bcd001"45"A" }                  status)                   )VALUES ("bcd001",        45,        "A")

See also:

db.collection.insertOne()

Selectselect All

SELECT *                                    db.people.find()FROM people

Select specifically

SELECTid,                                  db.people.find(       user_id,                                 { },       status                                   11 }FROM people                                 )SELECT user_id, status                      db.people.find(FROM people                                     { },                                                110 }                                                )

SELCT logically

SELECTUSER_ID, Status Db.people.find ( fromPeople {Status:"A"},WHEREStatus ="A"{user_id:1, Status:1, _id:0}                                            )SELECT* Db.people.find ( fromPeople {status: {$ne:"A"} }WHEREStatus! ="A")SELECT* Db.people.find ( fromPeople {Status:"A",WHEREStatus ="A"Age -} andAge = -)SELECT* Db.people.find ( fromPeople {$or: [{status:"A"} ,WHEREStatus ="A"{Age: -} ] }ORAge = -)SELECT* Db.people.find ( fromPeople {age: {$gt: -} }WHEREAge > -)SELECT* Db.people.find ( fromPeople {age: {$lt: -} }WHEREAge < -)SELECT* Db.people.find ( fromPeople {age: {$gt: -, $lte: -} }WHEREAge > -) andAge <= -

Select fuzzily

SELECT *                                    db.people.find( { user_id: /bc/ } )FROM people                                 -or-WHERElike"%bc%"                   db.people.find( { user_id: { $regex: /bc/ } } )SELECT *                                    db.people.find( { user_id: /^bc/ } )FROM people                                 -or-WHERElike"bc%"                    db.people.find( { user_id: { $regex: /^bc/ } } )

Select and Sort

SELECT *                                    db.people.find(FROM people                                      "A"1 }WHERE"A"                          )ORDERBYASCSELECT *                                    db.people.find(FROM people                                     "A" } ).sort( { user_id: -1 }WHERE"A"                          )ORDERBYDESC

Select and Count

SELECT COUNT(*) Db.people.count () fromPeople-or-db.people.find (). Count ()SELECT COUNT(user_id) Db.people.count ({user_id: {$exists:true} } ) fromPeople-or-db.people.find ( {user_id: {$exists:true}}). Count ()SELECT COUNT(*) Db.people.count ({age: {$gt: -} } ) frompeople-or-WHEREAge > -Db.people.find ({age: {$gt: -}}). Count ()

Select and Distinct

SELECTDISTINCT(status)                     db.people.aggregate(FROM people                                     "$status" } } ]                                            )                                            -or-                                            "status" )

Select limitedly

SELECT *                                    db.people.findOne()FROM people                                 -or-LIMIT1                                     db.people.find().limit(1)SELECT *                                    db.people.find().limit(5).skip(10)FROM peopleLIMIT5SKIP10

Explain Select

EXPLAINSELECT *                            "A" } ).explain()FROM peopleWHERE"A"

See also:

• db.collection.find()
• db.collection.distinct()
• db.collection.findOne()
• Query operators:,,,,,, $ne $and , and $or $gt $lt $exists $lte $regex .
• limit()
• skip()
• explain()
• sort()
• count()

Update

UPDATE people                               db.people.updateMany(SET"C"                                25 } },WHERE25                                  "C" } }                                            )                                            UPDATE people                               db.people.updateMany(SET3                               "A" } ,WHERE"A"                              3 } }                                            )

See Also

db.collection.updateMany(),,, $set $inc and $gt .

Delete

DELETEFROM people                          "D" } )WHERE"D"DELETEFROM people                          db.people.deleteMany({})

See Also

db.collection.deleteMany().

MongoDB and SQL Operations mapping