MongoDB与SQL常用语法对应表

时间:2020-09-10 15:24:00 来源:互联网 作者: 神秘的大神 字体:

    对于经常写SQL的同学,一开始写mongoDB的语法会十分不习惯,下表整理了常见的SQL语法,函数以及相应的MongoDB对应语法,仅供参考。

 

术语及概念

SQL
MongoDB
database database
table collection
row document
column field
index index
table joins $lookup
primary key primary key
SELECT INTO NEW_TABLE $out
MERGE INTO TABLE $merge(mongodb > 4.2)
UNION ALL $unionWith (mongodb > 4.4)
transactions transactions

 

语法对应表

SQL MongoDB

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"

 } )

ALTER TABLE people

ADD join_date DATETIME

db.people.updateMany(

    { },

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

)

ALTER TABLE people

DROP COLUMN join_date

db.people.updateMany(

    { },

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

)

CREATE INDEX idx_user_id_asc

ON people(user_id)

db.people.createIndex( { user_id: 1 } )
CREATE INDEX

       idx_user_id_asc_age_desc

ON people(user_id, age DESC)

db.people.createIndex( { user_id: 1, age: -1 } )
DROP TABLE people db.people.drop()

INSERT INTO people(user_id,

                  age,status)

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

db.people.insertOne(

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

)

SELECT * FROM people db.people.find()

SELECT id,

       user_id,

       status

FROM people

db.people.find(

    { },

    { user_id: 1, status: 1 }

)

SELECT user_id, status

FROM people

db.people.find(

    { },

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

)

SELECT user_id, status

FROM people

WHERE status = "A"

db.people.find(

    { status: "A" },

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

)

SELECT *

FROM people

WHERE status != "A"

db.people.find(

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

)

SELECT *

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/ } } )

SELECT *

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

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

SELECT COUNT(*) FROM people

db.people.count()

or

db.people.find().count()

SELECT COUNT(user_id)

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"

db.people.find( { status: "A" } ).explain()
UPDATE people

SET age = age + 3

WHERE status = "A"

db.people.updateMany(

   { status: "A" } ,

   { $inc: { age: 3 } }

)