1. 테이블 생성
| MySQL | MongoDB |
| CREATE TABLE table_name ( id NUMBER NOT NULL, name VARCHAR(30), age NUMBER, PRIMARY KEY(id) ) |
db.table_name.insertOne ({ id: 1, name: “user”, age: 27 }) db.createCollection("table_name") |
| table_name |
| id |
| name |
| age |
2. 데이터 삽입
| MySQL | MongoDB |
| INSERT INTO table_name VALUES (1, “user”, 27) | db.table_name.insertOne ({ id: 1, name: “user”, age: 27 }) |
3. 테이블 삭제
| MySQL | MongoDB |
| DROP TABLE table_name | db.table_name.drop() |
4. SELECT
| MySQL | MongoDB |
| SELECT * FROM table_name; | db.table_name.find() |
| SELECT id, name FROM table_name; | db.table_name.find( { }, { id: 1, name: 1, age: 0 } ) |
5. WHERE
| MySQL | MongoDB |
| SELECT * FROM table_name WHERE name=”A”; |
db.table_name.find( {name: ”A”} ) |
| SELECT id, name FROM table_name WHERE name= "A"; |
db.table_name.find( { name: "A"}, { id: 1, name: 1, age: 0 } ) |
| SELECT * FROM table_name WHERE name !="A"; |
db.table_name.find( { name: { $ne: "A"} } ) |
| SELECT id, name FROM table_name WHERE age > 25 AND age <= 50 |
db.table_name.find( { age: {.$gt:25, $lte: 50 } } ) |
| SELECT * FROM table_name WHERE name like "A%" |
db.table_name.find( {name: /^A/} ) |
| SELECT * FROM table_name WHERE name = "A" ORDER BY id ASC; |
db.table_name.find( {name: "A" } ).sort( {id: 1} ) |
| SELECT * FROM table_name WHERE name = "A" ORDER BY id DESC; |
db.table_name.find( {name: "A" } ).sort( {id: -1} ) |
| SELECT COUNT(*) FROM table_name |
db.table_name.find().count() |
| SELECT COUNT(*) FROM table_name WHERE age > 30 |
db.table_name.count( { age: {$gt: 30} } ) |
| SELECT DISTINCT(name) FROM table_name |
db.table_name.aggregate([{ $group : { _id : name }}]) |
7. 예제
(1) 사용자 테이블에서 나이순 정렬한 다음 상위 다섯명의 id와 이름 반환
| MySQL | MongoDB |
| SELECT id, name FROM user ORDER BY age LIMIT 5; |
db.table_name.find( { } {id:1, name:1, age:0} ) . sort({age: 1}) .limit(5) |
(2) 결제 내역 테이블에서 직원별 계산 횟수 반환
| MySQL | MongoDB |
| SELECT staff_id, COUNT(amount) FROM payment GROUP BY staff_id; |
db.payment.aggregate([ { $group: { _id: “$staff_id”, totalAmount: { $sum: 1 } } }, { $project: { staff_id: “$_id”, totalAmount: 1, _id: 0 } } ]) |
(3) 결제일별 매출액 합계를 매출액 오름차순 정렬해서 반환
| MySQL | MongoDB |
| SELECT DATE(payment_date), SUM(amount) FROM payment GROUP BY DATE(payment-date) ORDER BY SUM(amount) |
db.payment.aggregate([ { $group: { _id: {$dateToString: {format: “%Y-%m-%d”, date: “$payment_date”}}, totalAmount: {$sum: “$amount” } } }, { $sort: {totalAmount:1} } ]) |