Shulou(Shulou.com)05/31 Report--

Today, I would like to talk to you about how to understand the relationship between SQL and MongoDB. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something from this article.

Preface

Many developers first come into contact with the concept of database (usually in college classes), or the first database, usually SQL database, but now that NoSQL database comes from behind, many users of original SQL data inevitably need to turn to NoSQL. As the representative of NoSQL database, MongoDB is becoming more and more popular in the community, and the production environment is used more and more widely.

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

In fact, this step has been taken into account by MongoDB officials, that is: MongoDB CRUD Operations > MongoDB CRUD Operations > SQL to MongoDB Mapping Chart. This document summarizes the terms and concepts of SQL corresponding to MongoDB, as well as executable files, SQL statements / MongoDB statements, and so on.

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

Terminology and Concepts

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

TIP

In many cases, the non-normalized data model (embedded documents and arrays) denormalized data model (embedded documents and arrays) will continue to be the best choice for your data and use cases, rather than multiple document transactions. That is, for many scenarios, proper modeling of the data minimizes the need for multiple document transactions (multi-document transactions).

Executables

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

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 example of MongoDB assumes that a collection named people contains documents for the following prototypes:

{_ id: ObjectId ("509a8fb2f3f4948bd2f983a0"), user_id: "abc123", age: 55, status:'A'} Create and AlterCREATE TABLE

SQL mode statement:

CREATE TABLE people (id MEDIUMINT NOT NULL AUTO_INCREMENT, user_id Varchar (30), age Number, status char (1), PRIMARY KEY (id))

MongoDB mode statement:

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

Created implicitly 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") ALTER TABLE / ADD

SQL mode statement:

ALTER TABLE people ADD join_date DATETIME

MongoDB mode statement:

Db.people.updateMany ({}, {$set: {join_date: new Date ()}})

The collection does not describe or enforce the structure of its document; that is, there is no structural change at the collection level.

However, at the document level, the updateMany () operation can use the $set operator to add fields to an existing document.

ALTER TABLE / DROP COLUMN

SQL mode statement:

ALTER TABLE people DROP COLUMN join_date

MongoDB mode statement:

Db.people.updateMany ({}, {$unset: {"join_date": ""}})

The collection does not describe or enforce the structure of its document; that is, there is no structural change at the collection level.

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

CREATE INDEX

SQL mode statement:

CREATE INDEX idx_user_id_asc ON people (user_id)

MongoDB mode statement:

Db.people.createIndex ({user_id: 1}) CREATE INDEX / Multi

SQL mode statement:

CREATE INDEX idx_user_id_asc_age_descON people (user_id, age DESC)

MongoDB mode statement:

Db.people.createIndex ({user_id: 1, age:-1}) DROP TABLE

SQL mode statement:

DROP TABLE people

MongoDB mode statement:

Db.people.drop () Insert

The following table shows the various SQL statements related to inserting records into the table and the corresponding MongoDB statements.

SQL INSERT statement

INSERT INTO people (user_id, age, status) VALUES ("bcd001", 45, "A")

Mongodb insertOne () statement

Db.people.insertOne ({user_id: "bcd001", age: 45, status: "A"}) Select

The following table shows the various SQL statements related to reading records from the table and the corresponding MongoDB statements.

NOTE:

The find () method always contains the _ id field that returns the document, unless specifically excluded by projection. Some of the following SQL queries may include a _ id field to reflect this, even if it is not included in the corresponding find () query.

SELECT... WHERE

SQL statement

SELECT user_id, status FROM people WHERE status = "A"

Mongodb statement

Db.people.find ({status: "A"}, {user_id: 1, status: 1, _ id: 0}) SELECT. AND

SQL statement

SELECT * FROM people WHERE age > 25 AND age 25

Mongodb statement

Db.people.updateMany ({age: {$gt: 25}}, {$set: {status: "C"}}) UPDATE... INC

SQL statement

UPDATE people SET age = age + 3 WHERE status = "A"

Mongodb statement

Db.people.updateMany ({status: "A"}, {$inc: {age: 3}}) Delete Records

The various SQL statements related to deleting records from the table and the corresponding MongoDB statements are shown below.

DELETE WHERE

SQL statement

DELETE FROM peopleWHERE status = "D"

Mongodb statement

Db.people.deleteMany ({status: "D"}) DELETE

SQL statement

DELETE FROM people

Mongodb statement

Db.people.deleteMany ({})

Seeing here, you must have corresponded the knowledge related to SQL and MongoDB one by one in your mind, so the rest needs a lot of practice and in-depth mining.

After reading the above, do you have any further understanding of how to understand the relationship between SQL and MongoDB? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.