SQL 쿼리와 비교하는 MongoDB 쿼리

개념 (SQL : MongoDB)

- Database : Database 

- Table : Collection

- Row : Document

- Column : Field

- index : index

- join : embedding and lingking

- transaction : transaction

 

 

 

몽고 DB 기본 명령어 사용법 (DB / Collection / Document 다루기)

: https://2mukee.tistory.com/516

MongoDB 기본 명령어 사용법 (DB / Collection / Document 다루기)

 

 

 

삽입

- INSERT INTO USERS VALUES (26,  'Mark Jonson Junior')

: db.users.insert({age: 26, name: 'Mark Jonson Junior'})

 

 

 

조회

- SELECT age, name FROM USERS

: db.users.find({}, {'age': 1, 'name': 1)

 

- SELECT * FROM USERS

: db.users.find()

 

- SELECT * FROM USERS WHERE age=26

: db.users.find({'age': 26})

 

- SELECT a,b FROM USERS WHERE age=26

: db.users.find({'age': 26}, {'age': 1, 'name': 1})

 

- SELECT * FROM USERS WHERE age > 20

: db.users.find({'age': {$gt: 20}})

 

- SELECT * FROM USERS WHERE age >= 20

: sb.users.find({'age': {$gte: 20}})

 

- SELECT * FROM USERS WHERE age < 30

: db.users.find({'age': {$lt: 30}})

 

- SELECT * FROM USERS WHERE age <= 30

: db.users.find({'age': {$lte: 30]})

 

- SELECT * FROM USERS WHERE age > 20 AND age <= 30

: db.users.find({'age': {$gt: 26, $lte: 30}})

 

- SELECT * FROM USERS WHERE name LIKE '%son%'

: db.users.find({'name': /son/})

 

- SELECT * FROM USERS WHERE name LIKE 'son%'

: db.users.find({'name': /^son/})

 

- SELECT * FROM USERS WHERE name LIKE '%son'

: db.users.find({'name': /son$/})

 

- SELECT * FROM USERS WHERE age=26 ORDER BY name

: db.users.find({ 'age': 26 }, sort({ 'name': 1 }))

 

- SELECT * FROM USERS ORDER BY name DESC

: db.users.find().sort({'name': -1})

 

- SELECT * FROM USERS LIMIT 10 SKIP 20

: db.users.find().limit(10).skip(20)

: db.users.findOne().skip(20)

 

- SELECT * FROM USERS WHERE age > 20 AND name = 'Mark Jonson Junior'

: db.users.find({ $and: [ { 'age': {$gt: 20}, 'name': 'Mark Jonson Junior' } ] })

 

- SELECT * FROM USERS WHERE age > 20 OR name = 'Mark Jonson Junior'

: db.users.find({ $or: [ { 'age': {$gt: 20}, 'name': 'Mark Jonson Junior' } ] })

 

- SELECT * FROM USERS WHERE NOT age > 20 

: db.users.find({ $not: [ { 'age': {$gt: 20}] })

 

- SELECT DISTINCT name FROM USERS

: db.users.distinct('name')

 

- SELECT COUNT * FROM USERS

: db.users.count();

 

- SELECT COUNT age FROM USERS

: db.users.find({'age': {$exists: true}}).count()

 

 

 

인덱스 생성

- CREATE INDEX myIndex ON USERS(name)

: db.users.ensureIndex({'name': 1})

 

 

 

익스플레인 (실행계획)

- EXPLAIN SELECT * FROM USERS WHERE age=26

: db.users.find({'age': 26}).ensureIndex()

 

 

갱신

- UPDATE USERS SET age=30, name: 'MIKE' WHERE age=26

: db.users.update({'age': 26}, {$set: {'age': 30, 'name': 'MIKE'}}, false, true)

 

 

삭제

- DLETE FROM USERS WHERE name='MIKE'

: db.users.remove({'name': 'MIKE'}) 

 

 

 

몽고 DB의 비교연산자

- = (equal)

: $eq

 

- > (greather than)

: $gt

 

- >= (greather than or equal)

: $gte

 

- < (less than)

: $lt

 

- <= (less than or equal)

: $lte

 

- != (not equal)

: $ne

 

- IN

: $in

 

- NOT IN

: $nin

 

 

 

몽고 DB의 논리연산자

- $or

- $and

- $not

- $nor

 

 

정규식

- { '필드': { $regex: /패턴/, $options: '옵션' } }

 

- 옵션

: i (대소문자 무시)

: m (정규식에서 anchor 사용 시 \n을 무시)

: x (정규식 안에 있는 white space 모두 무시)

: s (.(dot)을 사용할 때 \n을 포함하여 매치)

 

 

 

$where 연산자

- js expression 사용가능

: db.users.find({ $where: 'name.length > 0' })

 

 

 

기타 연산자

- $slice

: embedded Document 배열을 읽을 때 limit 설정

 

- $elemMatch

: 쿼리 연산자중 일부만 읽고 싶을 때 사용

 

- $push

: 배열에 값 추가

 

- $pull

: 배열의 값 제거

 

 

 

 

 

 

Refference

mongoDB 쿼리의 모든 것!

 

[MongoDB] 강좌 3편 Document Query(조회) – find() 메소드 | VELOPERT.LOG