Docs Home → MongoDB Manual

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.除了下面的图表之外，您可能还需要考虑常见问题部分，以选择有关MongoDB的常见问题。

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文档或BSON文档
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. This table is not meant to be exhaustive.下表显示了一些数据库可执行文件和相应的MongoDB可执行文件。本表并非详尽无遗。

MongoDBMySQLOracleInformixDB2
Database Server数据库服务器mongodmysqldoracleIDSDB2 Server
Database ClientmongoshmysqlsqlplusDB-AccessDB2 Client

Examples实例

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

  • The SQL examples assume a table named people.SQL示例假定一个名为people的表。
  • The MongoDB examples assume a collection named people that contain documents of the following prototype:MongoDB示例假设一个名为people的集合包含以下原型的文档：

    {
      _id: ObjectId("509a8fb2f3f4948bd2f983a0"),
      user_id: "abc123",
      age: 55,
      status: 'A'
    }

Create and Alter创建和更改

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

SQL Schema StatementsMongoDB Schema Statements
 
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:有关使用的方法和运算符的更多信息，请参阅：

Tip

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

Tip

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() StatementsMongoDB find()语句
 
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有关所用方法和运算符的更多信息，请参阅

Tip

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 StatementsSQL更新语句MongoDB updateMany() StatementsMongoDB updateMany()语句
 
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:有关示例中使用的方法和运算符的更多信息，请参阅：

Tip

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 StatementsSQL删除语句MongoDB deleteMany() StatementsMongoDB deleteMany()语句
 
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()。

Tip

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:下载包括以下资源：

  • Presentation on the methodology of data modeling with MongoDB介绍使用MongoDB进行数据建模的方法
  • White paper covering best practices and considerations for migrating to MongoDB from an RDBMS data model白皮书介绍了从RDBMS数据模型迁移到MongoDB的最佳实践和注意事项
  • Reference MongoDB schema with its RDBMS equivalent参考MongoDB模式及其RDBMS等价物
  • Application Modernization scorecard应用程序现代化记分卡