Comparison Sql Server and MongoDB for a database developer
This morning just visited mongodb.com found mongo announced mongo 3.2. After going through their master manual for mongo 3.2 which is published on dated 11 March 2016, Friday. I just compared stuff which will help you to understand mongoDB operation quickly. MongoDB is not difficult for a relation database developer.
The following tables presents the various SQL terminology and concepts and the corresponding MongoDB terminology and concepts.
Terms Comparison
|
SQL Terms
|
Mongo Terms
|
Brief.
|
|
Database
|
Database
|
Organized collection
of information
|
|
Table
|
Collection
|
Representation of row
columns
|
|
Row
|
Document
|
|
|
Columns
|
Fields
|
|
|
Index
|
Index
|
Created to speed up
the execution
|
|
Table joins
|
Embedded document
& linking
|
Interlinked
information
|
|
Primary key
|
Primary key
|
Quickly identify a
record set/query result
|
|
Sql Terms, functions
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT
SUM()
COUNT()
join
|
Aggregation operators
$match
$group
$match
$project
$sort
$limit
$sum
$sum
$lookup
|
Matching ,grouping
,choosing, aggregate information to provide require result set/query result
|
Schema Comparison
|
SQL Schema
|
Mongo Schema
|
Brief.
|
|
CREATE TABLE
table1(
id int not null Identity(1,1) Primary key,
Name varchar(50),
Shift char(1),
Experience int
)
|
db.createCollection("table1")
db.table1.insert( {
Name:
"Sunit Kanyan",
Shift:
“G”
Experience: 5
} )
|
db.createCollection will create empty
collection. Insert implicity create the collection.
The primary key _id will automated added in
mongo.
|
|
Alter Table
table1 add join_date DATETIME
|
db.table1.update(
{ }
{ $set: { join_date: new Date() }
{ multi: true }
)
|
Collection does not enforce structure of
documents so there is no structure alteration at collection level. Update can
add new fields in existing documents.
|
|
Alter Table
table1 DROP COLUMN join_date
|
db.table1.update(
{ }
{ $unset: { join_date: “” }
{ multi: true }
)
|
Collection does not enforce structure of
documents.
Update can remove a field by unset operator.
|
|
CREATE INDEX
idx_table1_experience
ON table1(Experience)
|
db.table1.createIndex(
{ Experience: 1
} )
|
|
|
DROP Table
table1
|
db.table1.drop()
|
|
Insert Statement Comparison
|
SQL Insert Statements
|
Mongo Insert Statements
|
Brief.
|
|
Single Row Insert:-
INSERT INTO
table1
(Name
,Shift
,Experience)
VALUES
('SunitKanyan' ,'G'
,5)
INSERT INTO table1
VALUES
('Sunit Kanyan' ,'G'
,5)
|
db.table1.insert(
{ Name:
“SunitKanyan”,
Shift: “G”, Experience:
5 }
)
db.table1.insertOne(
{ Name:
“Sunit Kanyan”,
Shift: “G”, Experience:
5 }
)
|
Inserting
document/record
|
|
Multiple Row Insert:-
INSERT INTO table1
(Name
,Shift
,Experience)
VALUES
('Sunit Kanyan' ,'G'
,5),
('Sumit Ror' ,'G'
,4),
('Sunit Ror' ,'G'
,6),
|
db.table1.insertMany([
{ Name:
“Sunit Kanyan”,
Shift: “G”, Experience:
5 },
{ Name:
“Sumit Ror”,
Shift: “G”, Experience:
4 },
{ Name:
“Sunit Ror”,
Shift: “G”, Experience:
6 }
])
|
Inserting documents/records
|
Update Statement Comparison
|
SQL Update Statements
|
Mongo Update Statements
|
Brief.
|
|
Single Row Update:-
Update table1
Set
Shift='E'
Where
Experience =4
|
db.users.Update(
{ Experience: { $eq: 4} },
{ $set: { Shift: “E”, }}
)
db.users.UpdateOne(
{ Experience: { $gt: 4} },
{ $set: { Shift: “E”, }}
)
|
updating
document/record
|
|
Multiple Row Update:-
Update table1
Set
Shift='E'
Where
Experience > 4
|
db.users.UpdateMany(
{ Experience: { $gt: 4} },
{ $set: { Shift: “E”, }}
)
|
Updating documents/records
|
Select/Querying Statement Comparison
|
SQL Querying Statements
|
Mongo Querying Statements
|
Brief.
|
|
SELECT * FROM
table1
|
db.table1.find()
|
Retrieve all record
|
|
SELECT id,Name,Shift,Experience FROM table1
|
db.table1.find(
{ },
{ Name:1, Shift:1,
Experience:1)
|
Specified fields/columns
|
|
SELECT Name,Shift,Experience FROM
table1
|
db.table1.find(
{ },
{ Name: 1, Shift: 1, Experience: 1, _id: 0 })
|
Specified fields/columns
|
|
SELECT * FROM table1
WHERE Shift = 'E'
|
db.table1.find(
{ Shift: "E" })
|
Where clause
|
|
SELECT Name,Experience FROM table1
WHERE Shift = 'E'
|
db.table1.find(
{ Shift: "E" },
{ Name: 1, Experience: 1 })
|
Where clause
|
|
SELECT * FROM table1
WHERE Shift = 'E'
|
db.table1.find(
{ Shift: {
$qe: "E" } } )
|
equal
|
|
SELECT * FROM table1
WHERE Shift != 'E'
And Experience = 5
|
db.table1.find(
{ Shift: {
$ne: "E" } , Experience: 5} )
|
Not equal
|
|
SELECT * FROM table1
WHERE Shift = 'E'
And Experience = 5
|
db.table1.find(
{ Shift: {
$eq: "E" } , Experience: 5} )
|
And condition
|
|
SELECT * FROM table1
WHERE Shift = 'E'
OR Experience = 5
|
db.table1.find(
{ $or:
[Shift: {
$qe: "E" } ,
Experience:
5}
] }
)
|
Or condition
|
|
SELECT * FROM table1
WHERE Experience > 4
|
db.table1.find(
{ Experience: { $gt: 4}} )
|
Greater than
|
|
SELECT * FROM table1
WHERE Experience < 4
|
db.table1.find(
{ Experience: { $lt: 4}} )
|
Less than
|
|
SELECT * FROM table1
WHERE Experience > 4
And Experience <= 10
|
db.table1.find(
{ Experience: { $gt: 4, $lte: 10 } } )
|
And less/greater than
|
|
SELECT * FROM table1
WHERE Name LIKE '%Sunit%'
|
db.table1.find(
{ Name: /Sunit/} )
|
Like
|
|
SELECT * FROM table1
WHERE Experience in (3,4,5,6)
|
db. table1.find( { Experience: { $in: [ 3,4,5,6 ] } } )
|
In
|
|
SELECT TOP
1 * FROM table1
|
Db.table1.findOne()
|
Top
|
|
SELECT * FROM table1
Order by Name
|
Db.table1.find().sort(
{ Name: 1})
|
Sort
|
|
SELECT DISTINCT Name FROM table1
|
db.table1.distinct(: "Name")
|
Distinct
|
Delete Statement Comparison
|
SQL Delete Statements
|
Mongo Delete Statements
|
Brief
|
|
Delete FROM table1
|
db.table1.remove( { } )
|
All delete
|
|
Delete FROM table1
WHERE Experience = 4
|
db.table1.remove( { Experience:
4 } )
|
Conditional remove
|
|
Delete TOP (1) from table1 WHERE Experience = 4
|
db.table1.remove( { Experience:
4 } , true)
|
Top one delete
|
--
Sunit
No comments:
Post a Comment