SQL vs mongoDB (용어 및 예제)

용어 정리

SQL 용어	mongoDB 용어
Database	Database
table	collection
row	도큐먼트 or BSON document (필드: 값의 쌍으로 이루어짐)
column	field
index	index
table joins	embedded document (내장 도큐먼트)
primary key	primary key
하나의 열 또는 여러 열의 조합으로 기본 키를 생성	기본키를 지정할 필요 없이 _id 필드를 자동으로 기본키로 설정

---

예제

• Select

SQL SELECT Statement	MongoDB find() Statement
SELECT * FROM samples	db.samples.find()
SELECT id, user_id, status FROM samples	db.samples.find( {}, {user_id: 1, status: 1} )
SELECT user_id, status FROM samples	db.samples.find( {}, {user_id: 1, status: 1, _id: 0} )
SELECT * FROM samples WHERE status = "B"	db.samples.find( {status: "A"})
SELECT user_id, status FROM samples WHERE status = "A"	db.samples.find( {status: "A"}, {user_id: 1, status: 1, _id:0 } )
SELECT * FROM samples WHERE status != "A"	db.samples.find( {status: {$ne: "A"}})
SELECT * FROM samples WHERE status = "A" AND age = 50	db.samples.find( {status: "A", age: 50})
SELECT * FROM samples WHERE status = "A" OR age = 50	db.samples.find( { $or: [{status: "A"}, {age: 50} ] } )
SELECT * FROM samples WHERE age > 25	db.samples.find( { age: {$gt: 25} } )
SELECT * FROM samples WHERE age < 25	db.samples.find( {age: {$lt: 25} } )
SELECT * FROM samples WHERE age > 25 AND age <= 50	db.samples.find({age: { $gt: 25, Site: 50}})
SELECT * FROM samples WHERE user_id like "%bc%"	db.samples.find({user_id: /bc/}) 또는 db.samples.find({user_id: { $regex: /bc/}})
SELECT * FROM samples WHERE user_id like "bc%"	db.samples.find({user_id: /^bc/}) 또는 db.samples.find({user_id: { $regex: /^bc/}})
SELECT * FROM samples WHERE status = "A" ORDER BY user_id ASC	db.samples.find({ status: "A" }).sort( { user_id: 1})
SELECT * FROM samples WHERE status = "A" ORDER BY user_id DESC	db. samples.find({status: "A" }).sort( { user_id: -1}
SELECT COUNT(*) FROM samples	db.samples.count() or db. samples. find().count()
SELECT COUNT(user_id) FROM samples	db.samples.count( { user_id: { $exists: true } }) 또는 db.samples.find( { user_id: { $exists: true } } ).count()
SELECT COUNT(*) FROM samples WHERE age > 30	db.samples.count({age: { $gt: 30}}) 또는 db.samples.find({age: { $gt: 30}}).count()
SELECT * FROM samples LIMIT 1	db. samples.findOne() 또는 db. samples.find().limit(1)
EXPLAIN SELECT * FROM samples WHERE status = "A"	db. samples. find({ status: "A" }).explain()

• Update

SQL Update Statements	MongoDB updateMany() Statements
UPDATE samples SET status = "C" WHERE age > 25	db.samples.updateMany({ age: { $gt: 25 }}, {$set: { status: "C"}})
UPDATE samples SET age = age +3 WHERE status = "A"	db.samples.updateMany( { status: "A"}, {Sinc: {age: 3}})

• Delete

SQL Delete Statements	MongoDB deleteMany() Statements
DELETE FROM samples WHERE status = "D"	db.samples.deleteMany({status: "D" })
DELETE FROM samples	db.samples.deleteMany({})