몽고디비 sql 정리

​SQL 문장

Mongo 쿼리 문장 

CREATE TALE USERS (a int, b int)

db.createCollection(“mycoll”) 

테이블 드랍하기 : db.mycoll.drop();

DB명 통째로 삭제하기: use mydb -> db.dropDatabase();  라고 쓰기

DB명 안의 테이블(=콜렉션) 전체 보기: show tables, 혹은 show collections

 INSERT INTO USERS VALUES (3,5)

db.users.insert({a:3, b:5}) 

 SELECT a, b FROM USERS

db.users.find({}, {a:1, b:1}) 

 SELECT * FROM users

 db.users.find()

 SELECT * FROM users WHERE age=33

 db.users.find({age:33})

 SELECT a,b FROM users WHERE age=33

 db.users.find({age:33}, {a:1,b:1})

 SELECT * FROM users WHERE age=33 ORDER BY name

 db.users.find({age:33}).sort({name:1})

 SELECT * FROM users WHERE age>33

 db.users.find({‘age’:{$gt:33}})

 SELECT * FROM users WHERE age<33

 db.users.find({‘age’:{$lt:33}})

 SELECT * FROM users WHERE name LIKE“%Joe%”

 db.users.find({name:/Joe/})

 SELECT * FROM users WHERE name LIKE “Joe%”

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

 SELECT * FROM users WHERE age>33 AND age<=40

 db.users.find({‘age’:{$gt:33,$lte:40}})

 SELECT * FROM users ORDER BY name DESC

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

 SELECT * FROM users WHERE a=1 and b=’q’

 db.users.find({a:1,b:’q’})

 SELECT * FROM users LIMIT 10 SKIP 20

 SELECT * FROM users LIMIT 10 SKIP 20

 SELECT * FROM users WHERE a=1 or b=2

 db.users.find( { $or : [ { a : 1 } , { b : 2 } ] } )

 SELECT * FROM users LIMIT 1

 db.users.findOne()

 SELECT DISTINCT last_name FROM users

 db.users.distinct(‘last_name’)

 SELECT COUNT(*y) FROM users

 db.users.count()

 SELECT COUNT(*y) FROM users where AGE > 30

 db.users.find({age: {‘$gt’: 30}}).count()

 SELECT COUNT(AGE) from users

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

 CREATE INDEX myindexname ON users(name)

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

 CREATE INDEX myindexname ON users(name,ts DESC)

 db.users.ensureIndex({name:1,ts:-1})

 EXPLAIN SELECT * FROM users WHERE z=3

 db.users.find({z:3}).explain()

 UPDATE users SET a=1 WHERE b=’q’

 db.users.update({b:’q’}, {$set:{a:1}}, false, true)

 UPDATE users SET a=a+2 WHERE b=’q’

 db.users.update({b:’q’}, {$inc:{a:2}}, false, true)

 DELETE FROM users WHERE z=“abc”

 db.users.remove({z:’abc’});