SQL to MongoDB Mapping ChartSQL与MongoDB映射的对照表

On this page本页内容

In addition to the charts that follow, you might want to consider the Frequently Asked Questions section for a selection of common questions about MongoDB.除了下面的图表之外，您还可能需要考虑常见问题部分来选择关于MunGDB的常见问题。

Terminology and Concepts术语和概念

The following table presents the various SQL terminology and concepts and the corresponding MongoDB terminology and concepts.下表列出了各种SQL术语和概念以及相应的MongoDB术语和概念。

SQL Terms/ConceptsSQL术语/概念MongoDB Terms/ConceptsMongoDB术语/概念
database数据库 database数据库
table桌子 collection集合
row行 document文档 or 或BSON document文档
column列 field字段
index索引 index索引
table joins表联接 $lookup, embedded documents，嵌入文档

primary key主键

Specify any unique column or column combination as primary key.指定任何唯一的列或列组合作为主键。

primary key主键

In MongoDB, the primary key is automatically set to the _id field.在MongoDB中，主键自动设置为_id字段。

aggregation (e.g. group by)聚合（例如group by）

aggregation pipeline聚合管道

See the SQL to Aggregation Mapping Chart.请参阅SQL与聚合映射的对照表。

SELECT INTO NEW_TABLE

$out

See the SQL to Aggregation Mapping Chart.请参阅SQL与聚合映射的对照表。

MERGE INTO TABLE

$merge (Available starting in MongoDB 4.2)（从MongoDB 4.2开始提供）

See the SQL to Aggregation Mapping Chart.请参阅SQL与聚合映射的对照表。

UNION ALL $unionWith (Available starting in MongoDB 4.4)（从MongoDB 4.4开始提供）
transactions事务

transactions事务

Tip

For many scenarios, the denormalized data model (embedded documents and arrays) will continue to be optimal for your data and use cases instead of multi-document transactions. 对于许多场景，非规范化数据模型（嵌入式文档和数组）将继续适合您的数据和用例，而不是多文档事务。That is, for many scenarios, modeling your data appropriately will minimize the need for multi-document transactions.也就是说，对于许多场景，适当地建模数据将最大限度地减少对多文档事务的需要。

Executables可执行程序

The following table presents some database executables and the corresponding MongoDB executables. 下表显示了一些数据库可执行文件和相应的MongoDB可执行文件。This table is not meant to be exhaustive.本表并非详尽无遗。

 MongoDBMySQLOracleInformixDB2
Database Server数据库服务器 mongod mysqld oracle IDS DB2 Server
Database Client数据库客户端 mongo mysql sqlplus DB-Access DB2 Client

Examples示例

The following table presents the various SQL statements and the corresponding MongoDB statements. 下表显示了各种SQL语句和相应的MongoDB语句。The examples in the table assume the following conditions:表中的示例假设以下条件：

Create and AlterCREATE和ALTER

The following table presents the various SQL statements related to table-level actions and the corresponding MongoDB statements.下表显示了与表级操作相关的各种SQL语句以及相应的MongoDB语句。

SQL Schema StatementsSQL架构语句MongoDB Schema StatementsMongoDB架构语句
 
CREATE TABLE people (
    id MEDIUMINT NOT NULL
        AUTO_INCREMENT,
    user_id Varchar(30),
    age Number,
    status char(1),
    PRIMARY KEY (id)
)

Implicitly created on first insertOne() or insertMany() operation. 在第一次insertOne()或insertMany()操作中隐式创建。The primary key _id is automatically added if _id field is not specified.如果未指定_id字段，则会自动添加主键_id。

db.people.insertOne( {    user_id: "abc123",
    age: 55,
    status: "A"
 } )

However, you can also explicitly create a collection:但是，也可以显式创建集合：

db.createCollection("people")
ALTER TABLE people
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, updateMany() operations can add fields to existing documents using the $set operator.但是，在文档级别，updateMany()操作可以使用$set运算符向现有文档添加字段。

db.people.updateMany(
    { },
{ $set: { join_date: new Date() } })
ALTER TABLE people
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, updateMany() operations can remove fields from documents using the $unset operator.但是，在文档级别，updateMany()操作可以使用$unset运算符从文档中删除字段。

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()

For more information on the methods and operators used, see:有关所用方法和运算符的更多信息，请参阅：

See also参阅

Insert

The following table presents the various SQL statements related to inserting records into tables and the corresponding MongoDB statements.下表显示了与向表中插入记录相关的各种SQL语句以及相应的MongoDB语句。

SQL INSERT StatementsMongoDB insertOne() Statements
 
INSERT INTO people(user_id,
                  age,
                  status)
VALUES ("bcd001",
        45,
        "A")
 
db.people.insertOne(
{ user_id: "bcd001", age: 45, status: "A" })

For more information, see db.collection.insertOne().有关更多信息，请参阅db.collection.insertOne()。

See also参阅

Select

The following table presents the various SQL statements related to reading records from tables and the corresponding MongoDB statements.下表显示了与从表中读取记录相关的各种SQL语句以及相应的MongoDB语句。

Note

The find() method always includes the _id field in the returned documents unless specifically excluded through projection. find()方法总是在返回的文档中包含_id字段，除非通过投影明确排除。Some of the SQL queries below may include an _id field to reflect this, even if the field is not included in the corresponding find() query.下面的一些SQL查询可能包含一个_id字段来反映这一点，即使该字段未包含在相应的find()查询中。

SQL SELECT StatementsMongoDB find() Statements
 
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 *
FROM people
WHERE status = "A"
 
db.people.find(
{ status: "A" })
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 = 50
 
db.people.find(
    { $or: [ { status: "A" } , { age: 50 } ] }
)
SELECT *
FROM people
WHERE age > 25
 
db.people.find(
    { age: { $gt: 25 } }
)
SELECT *
FROM people
WHERE age < 25
 
db.people.find(
   { age: { $lt: 25 } }
)
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 ASC
 
db.people.find( { status: "A" } ).sort( { user_id: 1 } )
SELECT *
FROM people
WHERE status = "A"
ORDER BY user_id DESC
 
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, for distinct value sets that do not exceed the BSON size limit或者，对于不超过BSON大小限制的各不相同值集，可以使用：

db.people.distinct( "status" )
SELECT *
FROM people
LIMIT 1
 
db.people.findOne()

or

db.people.find().limit(1)
SELECT *
FROM people
LIMIT 5
SKIP 10
 
db.people.find().limit(5).skip(10)
EXPLAIN SELECT *
FROM people
WHERE status = "A"
 
db.people.find( { status: "A" } ).explain()

For more information on the methods and operators used, see有关所用方法和运算符的更多信息，请参阅

See also参阅

Update Records更新记录

The following table presents the various SQL statements related to updating existing records in tables and the corresponding MongoDB statements.下表显示了与更新表中现有记录相关的各种SQL语句以及相应的MongoDB语句。

SQL Update StatementsMongoDB updateMany() Statements
 
UPDATE people
SET status = "C"
WHERE age > 25
 
db.people.updateMany(
   { age: { $gt: 25 } },
   { $set: { status: "C" } }
)
UPDATE people
SET age = age + 3
WHERE status = "A"
 
db.people.updateMany(
   { status: "A" } ,
   { $inc: { age: 3 } }
)

For more information on the method and operators used in the examples, see:有关示例中使用的方法和运算符的更多信息，请参阅：

See also参阅

Delete Records删除记录

The following table presents the various SQL statements related to deleting records from tables and the corresponding MongoDB statements.下表显示了与从表中删除记录相关的各种SQL语句以及相应的MongoDB语句。

SQL Delete StatementsMongoDB deleteMany() Statements
 
DELETE FROM people
WHERE status = "D"
 
db.people.deleteMany( { status: "D" } )
DELETE FROM people
 
db.people.deleteMany({})

For more information, see db.collection.deleteMany().有关更多信息，请参阅db.collection.deleteMany()。

See also参阅

Further Reading深入阅读

If you are considering migrating your SQL application to MongoDB, download the MongoDB Application Modernization Guide.如果您正在考虑将SQL应用程序迁移到MongoDB，请下载MongoDB应用程序现代化指南。

The download includes the following resources:下载内容包括以下资源：