SQL vs NoSQL
1 SYSTEM........................................................................................................................................... 1
2 CONCEPTS....................................................................................................................................... 2
3 Schema Statements......................................................................................................................... 3
|
|
SQL Databases |
NoSQL Databases |
|
Data Storage Model |
Tables with fixed rows and columns |
Document: JSON documents, Key-value: key-value pairs, Wide-column: tables with rows and dynamic columns, Graph: nodes and edges |
|
Development History |
Developed in the 1970s with a focus on reducing data duplication |
Developed in the late 2000s with a focus on scaling and allowing for rapid application change driven by agile and DevOps practices. |
|
Examples |
Oracle, MySQL, Microsoft SQL Server, and PostgreSQL |
Document: MongoDB and CouchDB, Key-value: Redis and DynamoDB, Wide-column: Cassandra and HBase, Graph: Neo4j and Amazon Neptune |
|
Primary Purpose |
General purpose |
Document: general purpose, Key-value: large amounts of data with simple lookup queries, Wide-column: large amounts of data with predictable query patterns, Graph: analyzing and traversing relationships between connected data |
|
Schemas |
Rigid |
Flexible |
|
Scaling |
Vertical (scale-up with a larger server) |
Horizontal (scale-out across commodity servers) |
|
Multi-Record ACID Transactions |
Supported |
Most do not support multi-record ACID transactions. However, some—like MongoDB—do. |
|
Joins |
Typically required |
Typically not required |
|
Data to Object Mapping |
Requires ORM (object-relational mapping) |
Many do not require ORMs. MongoDB documents map directly to data structures in most popular programming languages. |
|
MySQL Terms |
MongoDB Terms |
Explanation |
|
Table |
Collection |
This is the storage container for data that tends to be similar in the contained objects. |
|
Row |
Document |
Defines the single object entity in the table for MySQL and collection in the case of MongoDB. |
|
Column |
Field |
For every stored item, it has properties which are defined by different values and data types. In MongoDB, documents in the same collection, may have different fields from each other. In MySQL, every row must be defined with the same columns from the existing ones. |
|
Primary key |
Primary key |
Every stored object is identified with a unique field value in the case of MongoDB we have _id field set automatically whereas in MySQL you can define your own primary key which is incremental as you create new rows. |
|
Table Joins |
Embedding and linking documents |
Connection associated with an object in a different collection/table to data in another collection/table. |
|
where |
$match |
Selecting data that matches criteria. |
|
group |
$group |
Grouping data according to some criteria. |
|
drop |
$unset |
Removing a column/field from a row/document/ |
|
set |
$set |
Setting the value of an existing column/field to a new value. |
|
Statement |
SQL |
NoSQL |
|
Create a Database |
CREATE DATABASE database_name |
db.users.insert( { UserId: "user1", Age: 55, Name: "Berry Hellington", Gender: "F", } ) |
|
Create Table |
CREATE TABLE users ( id MEDIUMINT NOT NULL AUTO_INCREMENT, UserId Varchar(30), Age Number, Gender char(1), Name VarChar(222), PRIMARY KEY (id) )
|
db.createCollection("users") |
|
Drop a Table |
DROP TABLE users |
db.users.drop() |
|
Add new column |
ALTER TABLE users ADD join_date DATETIME |
db.users.updateMany({},{$set:{‘join_date’: new Date()}) |
|
Removing a column |
ALTER TABLE users DROP COLUMN join_date DATETIME |
db.users.updateMany({},{$unset:{‘join_date’: “”}) |
|
Create an Index |
CREATE INDEX idx_UserId_asc_Age_desc ON users(UserId)
|
db.users.ensureIndex( { UserId: 1, Age: -1 } ) |
|
Insert new record |
INSERT INTO users(UserId, Age, Gender) VALUES ("user1", 25, "M")
|
db.users.insert( { UserId: "bcd001", Age: 25, Gender: "M", Name: "Berry Hellington", } )
|
|
Delete a records |
DELETE FROM users WHERE Age = 25
|
db.users.deleteMany( { Age = 25 } ) |
|
Return all record |
SELECT * FROM users |
db.users.find() |
|
Select |
SELECT id, Age, Gender FROM users |
db.users.find( { }, { Age: 1, Gender: 1 } ) |
|
Select Male |
SELECT * FROM users WHERE Gender = “M” |
db.users.find({ Gender: "M"}) |
|
Select Gender = F age = 25 |
SELECT * FROM users WHERE Age = 25 AND Gender = ‘F’ |
db.users.find({ Age: 25, Gender: "F"}) |
|
Select Gender = F, sort by id |
SELECT * FROM users WHERE Gender = ‘F’ ORDER BY id ASC |
db.users.find( { Gender: "F" } ).sort( { $natural: 1 } ) |
|
Count record in table |
SELECT COUNT(*) FROM users |
db.users.count() |
|
Return the first record |
SELECT * FROM users LIMIT 1 |
db.users.findOne() |
|
Update by to 26 |
UPDATE users SET Age = 26 WHERE age > 25 |
db.users.updateMany( { age: { $gt: 25 } }, { $set: { Age: 26 } } ) |
|
Increase age by 1 |
UPDATE users SET age = age + 1 |
db.users.updateMany( {} , { $inc: { age: 1 } } ) |