SQL문법과 MongoDB 문법 비교 분석

 

1. Create & Alter 문

 

 

SQL 문장	MongoDB 스키마 문장
CREATE TABLE members(    mem_no  varchar(30),    age number,    type char(1),    PRIMARY KEY (mem_no) );	db.members.insert({    mem_no  :  "T2013001",    age        :  49,    type       :  "ACE"}) 또는 db.createCollection("members")
ALTER TABLE members ADD regist_date DATE;
ALTER TABLE members DROP COLUM regist_date;
CREATE INDEX i_members_type ON members(type);	db.members.ensureIndex({ type:1})
CREATE INDEX i_members_type_no ON members( type, mem_no DESC);	db.members.ensureInde( { type:1, mem_no: -1})
DROP TABLE members;	db.member.drop()

 

 

2. Insert 문

 

SQL문	MongoDB
INSERT INTO members( mem_no, age, type) VALUES ("T20130102",35,"GOLD");	db.members.insert( { mem_no: "T20130102", age:35, type:"GOLD" })

 

 

 

3. SELECT 문

 

SQL문	MongoDB문
SELECT * FROM members;	db.members.find()
SELECT rowid,mem_no,type FROM members;	db.members.find( {}, {mem_no:1, type:1} )
SELECT mem_no, type FROM members;	db.members.find( {}, { mem_no;1, type:1, _id:0} )
SELECT * FROM members WHERE type = "ACE";	db.members.find( {type:"ACE"} )
SELECT mem_no, type FROM members WHERE type = "ACE";	db.members.find( {type:"ACE"}, { mem_no;1, type:1, _id:0} )
SELECT * FROM members WHERE type != "ACE";	db.members.find( {type: { $ne: "ACE"} } )
SELECT * FROM members WHERE type = "ACE";  AND age = 49;	db.members.find( {type:"ACE", age:49} )
SELECT * FROM members WHERE type = "ACE";  OR age = 49;	db.members.find( {$or: [ {type:"ACE"}, {age:49} ] } )
SELECT * FROM members WHERE age > 45;	db.members.find( {age: { $gt: 45} } )
SELECT * FROM members WHERE age < 55;	db.members.find( {age: { $lt: 55} } )
SELECT * FROM members WHERE age > 45 AND age <= 55;	db.members.find( {age: { $gt: 45 , $lte:55}} )
SELECT * FROM members WHERE mem_no like "%2013%";	db.members.find(  { mem_no: /2013/} )
SELECT * FROM members WHERE mem_no like "T%";	db.members.find(  { mem_no: /^T/} )
SELECT * FROM members WHERE type = "ACE" ORDER BY mem_no ASC;	db.members.find( { type: "ACE" } ).sort( { mem_no:1})
SELECT * FROM members WHERE type = "ACE" ORDER BY mem_no DES;	db.members.find( { type: "ACE" } ).sort( { mem_no:-1})
SELECT COUNT FROM members;	db.members.count()   db.memers.find().count()
SELECT COUNT(mem_no) FROM members;	db.members.count( { mem_no: {$exists:true} })   db.members.find( { mem_no: {$exists:true} }). count()
SELECT COUNT(*) FROM members WHERE age > 45;	db.members.count( { age: {$gt:45}})   db.members.find( { age: {$gt:45}}).count()
SELECT DISTINCT type FROM members;	db.members.distinct("type")
SELECT * FROM members WHERE rownum =1;	db.members.findOne()   db.members.find().limit(1)
EXPLAIN PLAN SELECT * FROM members WHERE type = "ACE";	db.members.find( { type:"ACE"}).explain()

 

 

4. Update Records 문

 

SQL	MongoDB
UPDATE members SET type = "GOLD" WHERE age > 45	db.members.update( { age: { $gt:45 } }, { $set: { type: "GOLD" } }, { multi: true} )
UPDATE members SET age = age + 3 WHERE type = "ACE"	db.members.update( { type: "ACE" }, { $inc: { age:3 } }, { multi: true} )

 

 

 

5. Delete Records 문

 

SQL	MongoDB
DELETE FROM members WHERE type = "ACE"	db.members.remove( {type: "ACE"})
DELETE FROM members	db.members.remove()

 

 

 

http://creatorw.tistory.com/entry/MongoDB%EC%99%80-SQL-%EC%B0%A8%EC%9D%B4

https://docs.mongodb.org/manual/reference/sql-aggregation-comparison/