MongoDB and MySQL are both powerful database management systems hence, it is important to know the difference between MongoDB vs MySQL. Here, I will explain the differences by exploring the CRUD operation on MongoDB and MySQL. CRUD is to create, read, update, and delete.
Related: MongoDB Beginners Tutorial with Examples
In simple words, MongoDB is a NO-SQL document-oriented database that is flexible for the querying of documents, including support for embedded documents and arrays, and MySQL is a SQL database that is powerful and expressive and supports a wide range of features including advanced querying, and joins.
Both MongoDB & MySQL differ in the data model, query language, and others.
- Data Model: MongoDB is a document-oriented NoSQL database, while MySQL is a relational database management system (RDBMS). MongoDB stores data in JSON-like documents, while MySQL stores data in table with rows and columns
- Query Language: MongoDB uses a query language called MongoDB Query Language (MQL), which is designed to work with documents, while MySQL uses Structured Query Language (SQL), which is designed to work with tables.
MongoDB | MySQL |
---|---|
Database | Database |
Collection | Table |
Document | Row or Record |
CRUD Operations in MongoDB vs MySQL
In both these databases, we can perform CRUD (Create, Read, Update, Delete) operations but the syntax differs.
CRUD | MongoDB | MySQL |
---|---|---|
Create | db.createCollection() | CREATE TABLE <TABLE> |
Insert | db.collection.insertMany() | INSERT INTO <TABLE> |
Select | db.collection.find() | SELECT * FROM <TABLE> |
Update | db.collection.updateOne() | UPDATE <TABLE> |
Delete | db.collection.deleteOne() | DELETE FROM <TABLE> |
1. Create in MongoDB Vs MySQL
In MongoDB and MySQL, new database objects like collections or tables are created using the CREATE
command. We can create the collection in MongoDB using the following command.
# Create the collection in MongoDB
db.createCollection("STUDENT");
The above command creates a new collection named STUDENT
in MongoDB. A collection holds multiple documents, it is similar to a table in RDBMS.
On the other hand, use the create table SQL statement to create a new MySQL table.
# Create a new MySQL table
CREATE TABLE STUDENT(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL
);
Thus, it creates a new table with the specified columns and data types shown in the output.
2. Insert in MongoDB Vs MySQL
Next, insert the documents or rows in MongoDB and MySQL using the insert command. The INSERT
command in MongoDB is used to insert documents into a collection, while in MySQL it is used to insert rows into a table. However, the syntax and usage of the command differ between the two databases. Consider the syntax for both databases.
In MongoDB, the insertOne()
or insertMany()
method is used to insert the documents according to the requirement. Here, we use the insertMany()
method to add four documents to MongoDB at once.
# insertMany() to add documents to MongoDB
db.STUDENT.insertMany([
{
_id: 1,
name: "Emily",
age: 22
},
{
_id: 2,
name: "Ellen",
age: 25
},
{
_id: 3,
name: "Tyler",
age: 21
},
{
_id: 4,
name: "Ben",
age: 23
}
]
)
The output shows that the command inserts a new document into the specified collection with the given key-value pair.
In case, you want to insert a document only when it does not exist refer to MongoDB insert if not exists
Now in MySQL, we insert multiple rows into a table using the INSERT INTO
command with a comma-separated list of value sets, like shown here.
# Insert into mySQL table
INSERT INTO STUDENT (ID,NAME,AGE) VALUES (1, 'Emily', 22),
(2, 'Ellen', 25), (3, 'Tyler', 21), (4, 'Ben', 23);
This will insert four rows into the table as displayed in the output.
3. Retrieve data in MongoDB Vs MySQL
We can retrieve data from a collection or table using the find()
and SELECT
commands respectively. However, the syntax and usage of these commands can differ significantly between the two databases due to their different data models and query languages.
The MongoDB find() command is used for the retrieval of data in MongoDB which is provided below.
# find() command in MongoDB
db.STUDENT.find()
As we can see, it returns all the documents from the collection. Moreover, we can also use various query operators to filter the results.
MySQL uses the SELECT
command to retrieve the data from the tables. Here is the command of SELECT
which returns rows from the table.
# Select command in MySQL
SELECT * from STUDENT;
The output displayed the rows of the specified table.
4. Update in MongoDB Vs MySQL
Both MongoDB and MySQL provide the ability to update data in their databases but with some differences. In MongoDB, the documents are updated via updateOne()
and updateMany()
commands. We have given an update command below which is performed using the $set
operator.
# updateOne() method
db.STUDENT.updateOne({'name':'Anna'},
{$set:{'name':'Emily'}})
Here, the outcome of the document modification is seen.
On the other hand, updates are performed using the UPDATE
statement, which can be used to update one or many rows in a table in MySQL. The SET
clause with the UPDATE
command is used to set the new values of the columns being updated.
# Update command in MySQL
UPDATE STUDENT SET NAME = 'Bella' WHERE ID = 2;
Hence, the output generated the results of the update statement below.
5. Delete in MongoDB Vs MySQL
In MongoDB, deletions are performed using the deleteOne()
to delete single documents or deleteMany()
to delete one or many documents. Although, we can use the remove()
method to do this. The delete operation is performed below.
# deleteOne() in MongoDB
db.STUDENT.deleteOne({ "_id": 3 })
Hence, the specified document is deleted below.
On the contrary, MySQL uses the delete statement to perform deletion operations. The statement of delete is provided as follows.
# Delete Statement in MySQL
DELETE FROM STUDENT WHERE ID = 3;
The specified row is now deleted from the table.
6. Conclusion
In conclusion, Both the MongoDB and MySQL system has their strength and weakness, the choice between the two depends on the specific needs of our application and the type of data we are working with. If your application requires complex queries and transactions, MySQL may be a better choice. If you need a flexible and scalable database that can handle large volumes of unstructured data, MongoDB may be a better fit.
I hope you now understand the difference between MySQL vs MongoDB and how to perform CRUD operations on each database.
More details about this topic can be found here.
Related Articles
- MongoDB Create User
- Most Useful Basic MongoDB Commands
- Using MongoDB Index Explained
- List Databases in MongoDB
- MongoDB Interview Questions
- Check MongoDB Version
- Node.js Using Express.js with MongoDB
- MongoDB Create Database & Collection
- MongoDB updateOne() method Explained
- Using Mongoose Module with MongoDB
- MongoDB Client Tools (Interact with MongoDB)
- Using MongoDB Index Explained
- MongoDB Schema