MongoDB与SQL语法对应表

学习笔记 2020/09/09 MongoDB, MYSQL

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


术语及概念

SQL
MongoDB
databasedatabase
tablecollection
rowdocument
columnfield
indexindex
table joins$lookup
primary keyprimary key
SELECT INTO NEW_TABLE$out
MERGE INTO TABLE$merge(mongodb > 4.2)
UNION ALL$unionWith (mongodb > 4.4)
transactionstransactions


语法对应表

SQLMongoDB

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 peopledb.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 peopledb.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 } }

)







本文地址：https://www.stayed.cn/item/270

转载请注明出处。

本站部分内容来源于网络,如侵犯到您的权益,请 联系我

我的博客

人生若只如初见，何事秋风悲画扇。

我的标签

随笔档案