INSERT INTO USERS

VALUES( 1, 1)

db.users.insert( { a: 1, b: 1 } )

// GENOID is optional. if not done by client,

// server will add an _id

c.insert("mydb.users",

  BSON(GENOID<<"a"<<1<<"b"<<1));

// then:

string err = c.getLastError();

SELECT a,b FROM users

db.users.find( {},

               {a: 1, b: 1 }

             )

auto_ptr<DBClientCursor> cursor =

  c.query("mydb.users", Query(),

  0, 0, BSON("a"<<1<<"b"<<1));

SELECT * FROM users

db.users.find()

auto_ptr<DBClientCursor> cursor =

  c.query("mydb.users", Query());

SELECT *

FROM users

WHERE age=33

db.users.find( { age: 33 } )

auto_ptr<DBClientCursor> cursor =

  c.query("mydb.users", QUERY("age"<<33))

// or:

auto_ptr<DBClientCursor> cursor =

  c.query("mydb.users", BSON("age"<<33))

SELECT *

FROM users

WHERE age=33

ORDER BY name

db.users.find( { age: 33 } ).sort( { name: 1 } )

auto_ptr<DBClientCursor> cursor =

  c.query("mydb.users",

    QUERY("age"<<33).sort("name"));

SELECT *

FROM users

WHERE age>33

AND age<=40

db.users.find( { ‘age‘: { $gt:33, $lte:40 } } )

auto_ptr<DBClientCursor> cursor =

  c.query("mydb.users",

  QUERY("age"<<GT<<33<<LTE<<40));

CREATE INDEX myindexname

ON users(name)

db.users.ensureIndex( {name: 1 } )

c.ensureIndex("mydb.users", BSON("name"<<1));

SELECT *

FROM users

LIMIT 10

SKIP 20

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

auto_ptr<DBClientCursor> cursor =

  c.query("mydb.users", Query(),

          10, 20);

SELECT * FROM users LIMIT 1

db.users.findOne()

bo obj = c.findOne("mydb.users", Query());

SELECT DISTINCT last_name

FROM users

WHERE x=1

db.users.distinct( ‘last_name‘, {x: 1} )

// no helper for distinct yet in c++ driver,

// so send command manually

bo cmdResult;

bool ok = c.runCommand(

  "mydb",

  BSON("distinct" << "users"

                  << "key" << "last_name"

                  << "query" << BSON("x"<<1)),

  cmdResult);

list<bo> results;

cmdResult["values"].Obj().Vals(results);

SELECT COUNT(*)

FROM users

where AGE > 30

db.users.find( { age: { $gt: 30 } } ).count()

unsigned long long n =

   c.count("mydb.users", BSON("age"<<GT<<30));

UPDATE users

SET a=a+2

WHERE b=‘q‘

db.users.update( { b: ‘q‘ },

                 { $inc: { a:2 } },

                 false, true)

c.update("mydb.users", QUERY("b"<<"q"),

         BSON("$inc"<<BSON("a"<<2)), false, true);

// then optionally:

string err = c.getLastError();

bool ok = err.empty();

DELETE

FROM users

WHERE z="abc"

db.users.remove( { z: ‘abc‘ } )

c.remove("mydb.users", QUERY("z"<<"abc"));

// then optionally:

string err = c.getLastError();

database

database

table

collection

row

document or BSON document

column

field

index

index

table joins

embedded documents and linking

primary key

Specify any unique column or column combination as primary key.

primary key

In MongoDB, the primary key is automatically set to the _idfield.

aggregation (e.g. group by)

aggregation pipeline

See the SQL to Aggregation Mapping Chart.

Database Server

mysqld/oracle

mongod

Database Client

mysql/sqlplus

mongo

CREATE TABLE users (

    id MEDIUMINT NOT NULL

        AUTO_INCREMENT,

    user_id Varchar(30),

    age Number,

    status char(1),

    PRIMARY KEY (id)

)

Implicitly created on first insert() operation. The primary key_id is automatically added if _id field is not specified.

db.users.insert( {

    user_id: "abc123",

    age: 55,

    status: "A"

 } )

However, you can also explicitly create a collection:

db.createCollection("users")

See insert() anddb.createCollection()for more information.

ALTER TABLE users

ADD join_date DATETIME

Collections do not describe or enforce the structure of its documents; i.e. there is no structural alteration at the collection level.

However, at the document level, update() operations can add fields to existing documents using the $set operator.

db.users.update(

    { },

    { $set: { join_date: new Date() } },

    { multi: true }

)

See the Data Modeling Concepts, update(), and$set for more information on changing the structure of documents in a collection.

ALTER TABLE users

DROP COLUMN join_date

Collections do not describe or enforce the structure of its documents; i.e. there is no structural alteration at the collection level.

However, at the document level, update() operations can remove fields from documents using the $unset operator.

db.users.update(

    { },

    { $unset: { join_date: "" } },

    { multi: true }

)

See Data Modeling Concepts, update(), and$unset for more information on changing the structure of documents in a collection.

CREATE INDEX idx_user_id_asc

ON users(user_id)

db.users.ensureIndex( { user_id: 1 } )

See ensureIndex() andindexes for more information.

CREATE INDEX

       idx_user_id_asc_age_desc

ON users(user_id, age DESC)

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

See ensureIndex() andindexes for more information.

DROP TABLE users

db.users.drop()

See drop() for more information.

INSERT INTO users(user_id,

                  age,

                  status)

VALUES ("bcd001",

        45,

        "A")

db.users.insert( {

       user_id: "bcd001",

       age: 45,

       status: "A"

} )

See insert() for more information.

SELECT *

FROM users

db.users.find()

See find()for more information.

SELECT id, user_id, status

FROM users

db.users.find(

    { },

    { user_id: 1, status: 1 }

)

See find()for more information.

SELECT user_id, status

FROM users

db.users.find(

    { },

    { user_id: 1, status: 1, _id: 0 }

)

See find()for more information.

SELECT *

FROM users

WHERE status = "A"

db.users.find(

    { status: "A" }

)

See find()for more information.

SELECT user_id, status

FROM users

WHERE status = "A"

db.users.find(

    { status: "A" },

    { user_id: 1, status: 1, _id: 0 }

)

See find()for more information.

SELECT *

FROM users

WHERE status != "A"

db.users.find(

    { status: { $ne: "A" } }

)

See find()and $ne for more information.

SELECT *

FROM users

WHERE status = "A"

AND age = 50

db.users.find(

    { status: "A",

      age: 50 }

)

See find()and $andfor more information.

SELECT *

FROM users

WHERE status = "A"

OR age = 50

db.users.find(

    { $or: [ { status: "A" } ,

             { age: 50 } ] }

)

See find()and $or for more information.

SELECT *

FROM users

WHERE age > 25

db.users.find(

    { age: { $gt: 25 } }

)

See find()and $gt for more information.

SELECT *

FROM users

WHERE age < 25

db.users.find(

   { age: { $lt: 25 } }

)

See find()and $lt for more information.

SELECT *

FROM users

WHERE age > 25

AND   age <= 50

db.users.find(

   { age: { $gt: 25, $lte: 50 } }

)

Seefind(),$gt, and$lte for more information.

SELECT *

FROM users

WHERE user_id like "%bc%"

db.users.find(

   { user_id: /bc/ }

)

See find()and $regexfor more information.

SELECT *

FROM users

WHERE user_id like "bc%"

db.users.find(

   { user_id: /^bc/ }

)

See find()and $regexfor more information.

SELECT *

FROM users

WHERE status = "A"

ORDER BY user_id ASC

db.users.find( { status: "A" } ).sort( { user_id: 1 } )

See find()and sort()for more information.

SELECT *

FROM users

WHERE status = "A"

ORDER BY user_id DESC

db.users.find( { status: "A" } ).sort( { user_id: -1 } )

See find()and sort()for more information.

SELECT COUNT(*)

FROM users

db.users.count()

or

db.users.find().count()

See find()andcount() for more information.

SELECT COUNT(user_id)

FROM users

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

or

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

Seefind(),count(), and$exists for more information.

SELECT COUNT(*)

FROM users

WHERE age > 30

db.users.count( { age: { $gt: 30 } } )

or

db.users.find( { age: { $gt: 30 } } ).count()

Seefind(),count(), and $gt for more information.

SELECT DISTINCT(status)

FROM users

db.users.distinct( "status" )

See find()anddistinct()for more information.

SELECT *

FROM users

LIMIT 1

db.users.findOne()

or

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

Seefind(),findOne(), andlimit() for more information.

SELECT *

FROM users

LIMIT 5

SKIP 10

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

Seefind(),limit(), and skip()for more information.

EXPLAIN SELECT *

FROM users

WHERE status = "A"