SQL vs NoSQL

 

1 SYSTEM........................................................................................................................................... 1

2 CONCEPTS....................................................................................................................................... 2

3 Schema Statements......................................................................................................................... 3

 

1 SYSTEM

 

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.

 

2 CONCEPTS

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.

 

3 Schema Statements

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 } }

)