一、术语和概念
下表显示了各种 SQL 术语和概念 以及相应的 MongoDB 术语和概念。
| SQL Terms/Concepts | MongoDB Terms/Concepts |
| 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. |
| aggregation (e.g. group by) | aggregation pipeline See the SQL to Aggregation Mapping Chart. |
| SELECT INTO NEW_TABLE | $out See the SQL to Aggregation Mapping Chart. |
| MERGE INTO TABLE | $merge (Available starting in MongoDB 4.2) See the SQL to Aggregation Mapping Chart. |
| UNION ALL | $unionWith (Available starting in MongoDB 4.4) |
| transactions | transactions |
二、可执行程序
下表显示了一些数据库可执行文件和相应的MongoDB可执行文件。本表并非详尽无遗。
三、示例
下表显示了各种SQL语句和相应的MongoDB语句。表中的示例假设以下条件：
- SQL示例假设一个名为people的表。
- MongoDB示例假设一个名为people的集合包含以下原型的文档：
{
_id: ObjectId("509a8fb2f3f4948bd2f983a0"),
user_id: "abc123",
age: 55,
status: 'A'
}
1、Create and Alter
下表显示了与表级操作相关的各种SQL语句以及相应的MongoDB语句。
| SQL Schema Statements | MongoDB Schema Statements |
|---|
CREATE TABLE people ( | id MEDIUMINT NOT NULL | AUTO_INCREMENT, | user_id Varchar(30), | age Number, | status char(1), | PRIMARY KEY (id) | ) |
|
在第一个insertOne（）或insertMany（）操作上隐式创建。如果未指定_id字段，则自动添加主键_id。
db.people.insertOne( { | user_id: "abc123", | age: 55, | status: "A" | } ) |
然而，您也可以显式创建集合：
db.createCollection("people") |
|
ALTER TABLE people | ADD join_date DATETIME |
| 托收不描述或强制执行其文件的结构；即，在收集级别没有结构变化。
然而，在文档级别，updateMany（）操作可以使用$set操作符将字段添加到现有文档中。
db.people.updateMany( | { }, | { $set: { join_date: new Date() } } | ) |
|
ALTER TABLE people | DROP COLUMN join_date |
| 托收不描述或强制执行其文件的结构；即，在收集级别没有结构变化。
然而，在文档级别，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 } ) |
|
|
|
|
2、Insert
下表显示了与将记录插入表中相关的各种SQL语句以及相应的MongoDB语句。
| SQL INSERT Statements | MongoDB insertOne() Statements |
|---|
INSERT INTO people(user_id, | age, | status) | VALUES ("bcd001", | 45, | "A") |
|
db.people.insertOne( | { user_id: "bcd001", age: 45, status: "A" } | ) |
|
3、select
下表显示了与从表中读取记录相关的各种SQL语句以及相应的MongoDB语句。
| SQL SELECT Statements | MongoDB find() Statements |
|---|
|
|
|
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 |
|
or
|
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" } } ] ) |
或者，对于不超过BSON大小限制的不同值集
db.people.distinct( "status" ) |
|
SELECT * | FROM people | LIMIT 1 |
|
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() |
|
4、update
下表显示了与更新表中现有记录相关的各种SQL语句以及相应的MongoDB语句。
| SQL Update Statements | MongoDB 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 } } | ) |
|
5、delete
下表显示了与从表中删除记录相关的各种SQL语句以及相应的MongoDB语句。
| SQL Delete Statements | MongoDB deleteMany() Statements |
|---|
DELETE FROM people | WHERE status = "D" |
|
db.people.deleteMany( { status: "D" } ) |
|
|
|
|
