MongoDB official documentation: SQL to MongoDB Mapping Chart

Translator: CS

preface

Many developers are first introduced to the concept of a database (usually in a college class), or their first database, usually SQL, and now that NoSQL databases are catching up, many users of original SQL data will inevitably switch to NoSQL. As a representative of NoSQL database, MongoDB is becoming more and more popular in the community and widely used in production environment.

For SQL to NoSQL developers, the most difficult step is to directly reuse the original SQL concepts and knowledge, maximizing the cost of learning.

In fact, this step MongoDB official has been considered for everyone, that is, in: MongoDB CRUD Operations > MongoDB CRUD Operations > SQL to MongoDB Mapping Chart, this document is a good summary of SQL corresponding MongoDB terms and concepts. Executables, SQL statements /MongoDB statements, etc.

It can be said that for SQL database developers, if they understand the correspondence between them, then one foot in the door of MongoDB.

Terminology and Concepts

The following table describes the various SQL terms and concepts and the corresponding MongoDB terms and concepts.

SQL terms/concepts MongoDB terminology/concepts
database database
table collection
row document 或 BSON document
column field
index index
Table joins $lookup.Embedded Documents
The primary key specifies any unique column or column group as the primary key primary keyIn MongoDB, the primary key is automatically set to_idfield
Aggregation = group by Aggregation pipelineReference:SQL to Aggregation Mapping Chart
SELECT INTO NEW_TABLE $outReference:SQL to Aggregation Mapping Chart
MERGE INTO TABLE $merge(available as of MongoDB 4.2) Reference:SQL to Aggregation Mapping Chart
transactions transactions

TIP

In many cases, the de-normalized data model (embedded Documents and Arrays) denormalized Data Model (Embedded Documents and Arrays) will continue to be your best choice for data and use cases over multi-document transactions. That is, for many scenarios, properly modeling the data will minimize the need for multi-document transactions.

Executables

The following table shows some database executables and the corresponding MongoDB executables. This table is not exhaustive.

MongoDB MySQL Oracle Informix DB2
Database Server mongod mysqld oracle IDS DB2 Server
Database Client mongo mysql sqlplus DB-Access DB2 Client

Examples

The following table shows the various SQL statements and the corresponding MongoDB statements. The examples in the table assume the following conditions:

  • The Sql example assumes a table named people.
  • The MongoDB example assumes that a collection named People contains documents for the following archetypes:
{
  _id: ObjectId("509a8fb2f3f4948bd2f983a0"),
  user_id: "abc123",
  age: 55,
  status: 'A'
}
Copy the code

Create and Alter

CREATE TABLE

  • SQL mode statements:
CREATE TABLE people (
    id MEDIUMINT NOT NULL
        AUTO_INCREMENT,
    user_id Varchar(30),
    age Number.status char(1),
    PRIMARY KEY (id))Copy the code
  • MongoDB mode statement:
db.people.insertOne( {
    user_id: "abc123".age: 55.status: "A"})Copy the code

Implicitly created on the first insertOne() or insertMany() operation. If the _id field is not specified, the primary key _ID is automatically added.

However, you can also explicitly create a collection:

db.createCollection("people")
Copy the code

ALTER TABLE / ADD

  • SQL mode statements:
ALTER TABLE people
ADD join_date DATETIME
Copy the code
  • MongoDB mode statement:
db.people.updateMany(
    { },
    { $set: { join_date: new Date()}})Copy the code

Collections do not describe or enforce the structure of their documents; That is, there are no structural changes at the collection level.

At the document level, however, the updateMany() operation can add fields to an existing document using the $set operator.

ALTER TABLE / DROP COLUMN

  • SQL mode statements:
ALTER TABLE people
DROP COLUMN join_date
Copy the code
  • MongoDB mode statement:
db.people.updateMany(
    { },
    { $unset: { "join_date": ""}})Copy the code

Collections do not describe or enforce the structure of their documents; That is, there are no structural changes at the collection level.

However, at the document level, the updateMany() operation can remove fields from the document using the $unset operator.

CREATE INDEX

  • SQL mode statements:
CREATE INDEX idx_user_id_asc
ON people(user_id)
Copy the code
  • MongoDB mode statement:
db.people.createIndex( { user_id: 1})Copy the code

CREATE INDEX / Multi

  • SQL mode statements:
CREATE INDEX
       idx_user_id_asc_age_desc
ON people(user_id, age DESC)
Copy the code
  • MongoDB mode statement:
db.people.createIndex( { user_id: 1.age: - 1})Copy the code

DROP TABLE

  • SQL mode statements:
DROP TABLE people
Copy the code
  • MongoDB mode statement:
db.people.drop()
Copy the code

For more details on the methods and operators used, see:

  • db.collection.insertOne()
  • db.collection.insertMany()
  • db.createCollection()
  • db.collection.updateMany()
  • db.collection.createIndex()
  • db.collection.drop()
  • $set
  • $unset

See also:

  • Databases and Collections
  • Documents
  • Indexes
  • Data Modeling Concepts

Insert

The following table shows the various SQL statements associated with inserting records into a table and the corresponding MongoDB statements.

  • SQL INSERT statements
INSERT INTO people(user_id,
                  age,
                  status)
VALUES ("bcd001".45."A")
Copy the code
  • Mongo insertOne () statement
db.people.insertOne(
   { user_id: "bcd001".age: 45.status: "A"})Copy the code

For more information, see db.collection.insertone ().

See also:

  • Insert Documents
  • db.collection.insertMany()
  • Databases and Collections
  • Documents

Select

The following table shows the various SQL statements associated with reading records from the table and the corresponding MongoDB statements.

NOTE:

The find() method always contains the _id field in the returned document, unless specifically excluded by projection. Some of the following SQL queries may contain an _ID field to reflect this, even if it is not included in the corresponding find() query.

SELECT … WHERE

  • The SQL statement

SELECT user_id, status
FROM people
WHERE status = "A"
Copy the code
  • Directing a statement
db.people.find(
    { status: "A" },
    { user_id: 1.status: 1._id: 0})Copy the code

SELECT … AND

  • The SQL statement
SELECT *
FROM people
WHERE age > 25
AND   age <= 50
Copy the code
  • Directing a statement
db.people.find(
   { age: { $gt: 25.$lte: 50}})Copy the code

SELECT … OR

  • The SQL statement
SELECT *
FROM people
WHERE status = "A"
OR age = 50
Copy the code
  • Directing a statement
db.people.find(
    { $or: [{status: "A"}, {age: 50}]})Copy the code

SELECT … LIKE

  • The SQL statement
FROM people
WHERE user_id like "%bc%"
Copy the code
  • Directing a statement
db.people.find( { user_id: /bc/ } )

-or-

db.people.find( { user_id: { $regex: /bc/}})Copy the code

SELECT … OEDER BY

  • The SQL statement
SELECT *
FROM people
WHERE status = "A"
ORDER BY user_id ASC
Copy the code
  • Directing a statement
db.people.find( { status: "A" } ).sort( { user_id: 1})Copy the code

SELECT … COUNT

  • The SQL statement
SELECT COUNT(user_id)
FROM people
Copy the code
  • Directing a statement
db.people.count( { user_id: { $exists: true } } )

or

db.people.find( { user_id: { $exists: true } } ).count()
Copy the code

SELECT DISTINCT

  • The SQL statement
SELECT DISTINCT(status)
FROM people
Copy the code
  • Directing a statement

db.people.aggregate( [ { $group : { _id : "$status"}}]) or, for different sizes up to [BSON size limit](HTTPS:/ / docs.mongodb.com/manual/reference/limits/#limit-bson-document-size) set of values

db.people.distinct( "status" )
Copy the code

SELECT … LIMIT SKIP

  • The SQL statement
SELECT *
FROM people
LIMIT 5
SKIP 10
Copy the code
  • Directing a statement
db.people.find().limit(5).skip(10)
Copy the code

EXPLAIN SELECT

  • The SQL statement
EXPLAIN SELECT *
FROM people
WHERE status = "A"
Copy the code
  • Directing a statement
db.people.find( { status: "A" } ).explain()
Copy the code

For more information about the methods used, see:

  • db.collection.find()
  • db.collection.distinct()
  • db.collection.findOne()
  • limit()
  • skip()
  • explain()
  • sort()
  • count()

Operators:

  • $ne
  • $and
  • $or
  • $gt
  • $lt
  • $exists
  • $lte
  • $regex

See also:

  • Query Documents
  • Query and Projection Operators
  • mongo Shell Methods

Update Records

The various SQL statements associated with updating existing records in the table and the corresponding MongoDB statements are shown below.

UPDATE … SET

  • The SQL statement
UPDATE people
SET status = "C"
WHERE age > 25
Copy the code
  • Directing a statement
db.people.updateMany(
   { age: { $gt: 25}}, {$set: { status: "C"}})Copy the code

UPDATE … INC

  • The SQL statement
UPDATE people
SET age = age + 3
WHERE status = "A"
Copy the code
  • Directing a statement
db.people.updateMany(
   { status: "A"}, {$inc: { age: 3}})Copy the code

For more information about the methods and operators used in the examples, see:

  • db.collection.updateMany()
  • $gt
  • $set
  • $inc

See also:

  • Update Documents
  • Update Operators
  • db.collection.updateOne()
  • db.collection.replaceOne()

Delete Records

The various SQL statements associated with deleting records from a table and the corresponding MongoDB statements are shown below.

DELETE WHERE

  • The SQL statement
DELETE FROM people
WHERE status = "D"
Copy the code
  • Directing a statement
db.people.deleteMany( { status: "D"})Copy the code

DELETE

  • The SQL statement
DELETE FROM people
Copy the code
  • Directing a statement
db.people.deleteMany({})
Copy the code

For more information, see db.collection.deletemany ().

  • Delete Documents
  • db.collection.deleteOne()

See here, presumably you should have SQL related knowledge in mind and MongoDB one-to-one correspondence, then the rest of the need for more practice, in-depth mining.

But at all times, keep in mind that official MongoDB documentation is by far the most authoritative and comprehensive source you can find.

The last

Small assistant here also offers a team recruitment, interested children’s shoes are welcome to vote ~ ~

Jingdong Shenzhen – Bump Laboratory (JINGdong Retail – User Experience Design Department – Multi-terminal R&D Department) Spring campus recruitment has begun!

We are a team that loves to create and constantly try new technologies, new experiences and new products.

Internship vacancies are open for 2020, so come and join!

The basic requirements

  • A full-time bachelor degree or above with a minimum of 1 October 2020 to 30 September 2021;
  • Solid foundation, logical, good communication skills and team work spirit;
  • Deep understanding of Web standards, practical knowledge of usability, accessibility, etc.
  • Proficient in using HTML, CSS, JavaScript to build high-performance Web applications;
  • Proficient in using at least one mainstream JS framework (Taro/React/Nerv), with good code style, interface design and program architecture ability;
  • Love computer programming, pay attention to new things, new technology, creativity and strong learning ability.

Give priority to

  • Experience in Node.js/Java server development;
  • Have wechat small program/mobile terminal development experience;
  • Have a personal open source project or technical blog with frequent updates.

contact

aotu[AT]jd.com

Please specify the source [gold digging]