MongoDB与SQL常用语法对应表

CREATE TABLE people (

    id MEDIUMINT NOT NULL

        AUTO_INCREMENT,

    user_id Varchar(30),

    age Number,

    status char(1),

    PRIMARY KEY (id)

)

db.createCollection("people")

db.people.insertOne( {

    user_id: "abc123",

    age: 55,

    status: "A"

 } )

ADD join_date DATETIME

db.people.updateMany(

    { },

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

)

DROP COLUMN join_date

db.people.updateMany(

    { },

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

)

CREATE INDEX idx_user_id_asc

ON people(user_id)

       idx_user_id_asc_age_desc

ON people(user_id, age DESC)

INSERT INTO people(user_id,

                  age,status)

VALUES ("bcd001",45,"A")

db.people.insertOne(

   { user_id: "bcd001", age: 45, status: "A" }

)

SELECT id,

       user_id,

       status

FROM people

db.people.find(

    { },

    { user_id: 1, status: 1 }

)

FROM people

db.people.find(

    { },

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

)

FROM people

WHERE status = "A"

    { status: "A" },

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

)

SELECT *

FROM people

WHERE status != "A"

db.people.find(

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

)

FROM people

WHERE status = "A"

AND age = 50

db.people.find(

    { status: "A",

      age: 50 }

)

SELECT *

FROM people

WHERE status = "A"

OR age = 5

db.people.find(

    { $or: [ { status: "A" } , { age: 50 } ] }

)

SELECT *

FROM people

WHERE age > 25

AND   age <= 50

db.people.find(

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

)

SELECT *

FROM people

WHERE user_id like "%bc%

db.people.find( { user_id: /bc/ } )

-or-

db.people.find( { user_id: { $regex: /bc/ } } )

FROM people

WHERE user_id like "bc%"

db.people.find( { user_id: /^bc/ } )

-or-

db.people.find( { user_id: { $regex: /^bc/ } } )

SELECT *

FROM people

WHERE status = "A"

ORDER BY user_id AS

SELECT COUNT(*) FROM people

db.people.count()

or

db.people.find().count()

FROM people

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

or

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

SELECT COUNT(*)

FROM people

WHERE age > 30

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

or

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

SELECT DISTINCT(status)

FROM people

db.people.aggregate( [ { $group : { _id : "$status" } } ] )

or

db.people.distinct( "status" )

EXPLAIN SELECT *

FROM people

WHERE status = "A"

SET age = age + 3

WHERE status = "A"

db.people.updateMany(

   { status: "A" } ,

   { $inc: { age: 3 } }

)