---

SQL vs. NoSQL

관계형 DB와 NoSQL 기술 요소

• 	관계형 DB	NoSQL
  기술 요소	AICD (Atomicity, Consistency, Isolation, Durability)
  특징	데이터 일관성을 위해 트랜잭션이 안전하게 수행	데이터 가용성과 데이터 처리 성능이 향상
  업무 범위	트랜잭션	시스템 전체 대상
  중점 사항	데이터 일관성	서비스 가용성

모델링 절차

관계형 DB

• 정규화를 통해 중복을 최소화 시킨 테이블을 만들고 외래키(FK)를 통해 관계매핑
  
  

NoSQL

• 성능 향상을 위해 의도적으로 데이터를 중복(반정규화(Denormalization))해서 저장
  • 한 번의 쿼리가 가능하게 됨으로써 네트워크 I/O 비용 감소
  • 쿼리 수행 복잡도 감소
• 엔티티간에 상/하위 관계를 가질 경우 하나의 엔티티를 다른 엔티티에 엔티티로 편입(임베디드)

Query 비교

MySQL	mongoDB	description
SELECT * FROM user;	db.user.find({})	to select all
SELECT name, age FROM user;	db.user.find({}, {name: 1, age: 1, _id:0})	to project column/fields
SELECT name, age FROM user WHERE age = 30;	db.user.find( {age: 30}, {name: 1, age: 1, _id:0})	select with project
SELECT * FROM user WHERE age > 30;	db.user.find({age: {$gt: 30}})	range based selection
SELECT * FROM user WHERE age <= 30;	db.user.find({age: {$lte: 30}})	range based selection on numeric value
SELECT * FROM user WHERE age > 30 AND age < 40;	db.user.find( {age: {$gt: 30, $lt: 40}})	range based selection on numeric value
SELECT * FROM user WHERE age = 32 AND name = 'ranjeet';	db.user.find( {age: 32, name: 'ranjeet'})	equality and range both
SELECT * FROM user WHERE age = 30 OR name = 'ranjeet';	db.user.find( {$or:[{age:30}, {name:'ranjeet'}]})	equality and range both
SELECT * FROM user WHERE age = 30 ORDER BY name ASC;	db.user.find( {age: 30}).sort({name: 1})	equality and sorting on name with ascending
SELECT * FROM user ORDER BY name DESC ;	db.user.find().sort({name: -1});	equality and sorting on name with descending
SELECT * FROM user WHERE name LIKE '%ran%' ;	db.user.find({name: /ran/});	contains selection
SELECT * FROM user WHERE name LIKE 'ran%' ;	db.user.find({name: /^ran/});	gex start with provided word
SELECT * FROM user LIMIT 10 SKIP 15;	db.user.find().skip(15).limit(10);	for pagination with offset and length
SELECT * FROM user LIMIT 1;	db.user.findOne();	select first record MySQL mongoDB description
SELECT DISTINCT name FROM user;	db.user.distinct("name");	with distinct
SELECT COUNT(*) FROM user;	db.user.count();	count records/document
SELECT COUNT(*) FROM user WHERE AGE > 20	db.user.find( {age: {$gt: 20}}).count()	provided condition then count records/document
SELECT COUNT(AGE) FROM user;	db.user.find( {age: {$exists: true}}).count();	existence check