Akashic Records

MongoDB, mogo Shell 본문

오래된글/DataBase

MongoDB, mogo Shell

Andrew's Akashic Records 2018. 4. 17. 15:28
728x90

MongoDB Shell 사용법


mongo Shell 실행

yunchang-lee@HSK01-INTER999:~$ mongo mydb

MongoDB shell version: 2.0.6

connecting to: mydb



Help 사용법

MongoDB에서 Help을 보고 싶으면 해당 명령어에 help()을 입력 하면된다.

‘db’ 관련 Help가 보고 싶다면 'db.help()’

‘db.collection’관련 Help가 보고 싶다면 'db.collection.help()’


조회하기

RDBMS에서 Table의 개념을 mongoDB에서 굳이 찾자면 'collection’이다. 하지만 개념이 완전 다르기 때문에 유사하다 생각하면 안되고 Data을 조회하는 대상이라고만 생각하면 되겠다.


현재 사용 중인 ‘collection’들을 보고 싶다면 ‘db.collection.help()’을 쳐보면 collection level에서 사용할수 있는 함수 리스트가 나온다. 이중 'db.getCollectionNames()’는 사용중인 Collection들을 보여준다.

> db.getCollectionNames()

[ "system.indexes", "things" ]


“things”의 내부 Data을 보는 기본 Query는 "db.things.find()” 이지만 아래 3가지는 모두 동일하게 동작한다.

> db.things.find()

> db["things"].find()

> db.getCollection("things").find()


기본적으로 리스트 개수가 '20'이기 때문에 "things"안의 Data가 20개씩 보이고 다음 20개를 보고 싶으면 'it’을 입력해야 한다. 기본 값은 'DBQuery.shellBatchSize’로 변경할 수 있다.


> DBQuery.shellBatchSize=30


Cursor 사용 하기

Collection 조회 결과를 변수로 지정하고 이를 while을 이용해서 출력 할 수도 있다.


> var c = db.things.find() → Java의 Resultset처럼 사용하기 위해 "c”변수에 커서를 저장한다.

> while(c.hasNext()) printjson(c.next()) → Java 문법과 유사하다.

{ "_id" : ObjectId("5111e871abd47e5e659ee261"), "name" : "mongo" }

{ "_id" : ObjectId("5111e8f1abd47e5e659ee262"), "x" : 3 }

{ "_id" : ObjectId("5111e97babd47e5e659ee263"), "x" : 4, "j" : 1 }

{ "_id" : ObjectId("5111e97babd47e5e659ee264"), "x" : 4, "j" : 2 }

{ "_id" : ObjectId("5111e97babd47e5e659ee265"), "x" : 4, "j" : 3 }

{ "_id" : ObjectId("5111e97babd47e5e659ee266"), "x" : 4, "j" : 4 }

{ "_id" : ObjectId("5111e97babd47e5e659ee267"), "x" : 4, "j" : 5 }

{ "_id" : ObjectId("5111e97babd47e5e659ee268"), "x" : 4, "j" : 6 }

{ "_id" : ObjectId("5111e97babd47e5e659ee269"), "x" : 4, "j" : 7 }

{ "_id" : ObjectId("5111e97babd47e5e659ee26a"), "x" : 4, "j" : 8 }

{ "_id" : ObjectId("5111e97babd47e5e659ee26b"), "x" : 4, "j" : 9 }

{ "_id" : ObjectId("5111e97babd47e5e659ee26c"), "x" : 4, "j" : 10 }

{ "_id" : ObjectId("5111e97babd47e5e659ee26d"), "x" : 4, "j" : 11 }

{ "_id" : ObjectId("5111e97babd47e5e659ee26e"), "x" : 4, "j" : 12 }

{ "_id" : ObjectId("5111e97babd47e5e659ee26f"), "x" : 4, "j" : 13 }

{ "_id" : ObjectId("5111e97babd47e5e659ee270"), "x" : 4, "j" : 14 }

{ "_id" : ObjectId("5111e97babd47e5e659ee271"), "x" : 4, "j" : 15 }

{ "_id" : ObjectId("5111e97babd47e5e659ee272"), "x" : 4, "j" : 16 }

{ "_id" : ObjectId("5111e97babd47e5e659ee273"), "x" : 4, "j" : 17 }

{ "_id" : ObjectId("5111e97babd47e5e659ee274"), "x" : 4, "j" : 18 }

{ "_id" : ObjectId("5111e97babd47e5e659ee275"), "x" : 4, "j" : 19 }

{ "_id" : ObjectId("5111e97babd47e5e659ee276"), "x" : 4, "j" : 20 }


변수 "c”에 저장된 커서는 인덱스를 지정하여 배열이나 List처럼 사용할 수 있는데 ‘0’ 부터 시작한다.

> var c = db.things.find()

> printjson( c [ 4 ] )

{ "_id" : ObjectId("5111e97babd47e5e659ee265"), "x" : 4, "j" : 3 }



SQL Statement와 Mongo Statement 비교

CREATE TABLE USERS (a Number, b Number)

implicit; can also be done explicitly with

db.createCollection("mycoll")

ALTER TABLE users ADD ...

implicit

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:{$ne: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

db.users.find().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 order_id FROM orders o, order_line_items li WHERE li.order_id=o.order_id AND li.sku=12345

db.orders.find({"items.sku":12345},{_id:1})

SELECT customer.name FROM customers,orders WHERE orders.id="q179" AND orders.custid=customer.id

var o = db.orders.findOne({_id:"q179"});
var name = db.customers.findOne({_id:o.custid})

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'});


복잡해지는 매칭 Query

SQL Example

MongoDB Example

Description

SELECT COUNT(*) AS count
FROM orders

db.orders.aggregate( [
   { $group: { _id: null,
               count: { $sum: 1 } } }
] )

Count all records fromorders

SELECT SUM(price) AS total
FROM orders

db.orders.aggregate( [
   { $group: { _id: null,
               total: { $sum: "$price" } } }
] )

Sum the price field from orders

SELECT cust_id,
       SUM(price) AS total
FROM orders
GROUP BY cust_id

db.orders.aggregate( [
   { $group: { _id: "$cust_id",
               total: { $sum: "$price" } } }
] )

For each uniquecust_id, sum theprice field.

SELECT cust_id,
       SUM(price) AS total
FROM orders
GROUP BY cust_id
ORDER BY total

db.orders.aggregate( [
   { $group: { _id: "$cust_id",
               total: { $sum: "$price" } } },
   { $sort: { total: 1 } }
] )

For each uniquecust_id, sum theprice field, results sorted by sum.

SELECT cust_id,
       ord_date,
       SUM(price) AS total
FROM orders
GROUP BY cust_id, ord_date

db.orders.aggregate( [
   { $group: { _id: { cust_id: "$cust_id",
                      ord_date: "$ord_date" },
               total: { $sum: "$price" } } }
] )

For each uniquecust_id, ord_dategrouping, sum theprice field.

SELECT cust_id, count(*)
FROM orders
GROUP BY cust_id
HAVING count(*) > 1

db.orders.aggregate( [
   { $group: { _id: "$cust_id",
               count: { $sum: 1 } } },
   { $match: { count: { $gt: 1 } } }
] )

For cust_id with multiple records, return the cust_id and the corresponding record count.

SELECT cust_id,
       ord_date,
       SUM(price) AS total
FROM orders
GROUP BY cust_id, ord_date
HAVING total > 250

db.orders.aggregate( [
   { $group: { _id: { cust_id: "$cust_id",
                      ord_date: "$ord_date" },
               total: { $sum: "$price" } } },
   { $match: { total: { $gt: 250 } } }
] )

For each uniquecust_id, ord_dategrouping, sum theprice field and return only where the sum is greater than 250.

SELECT cust_id,
       SUM(price) as total
FROM orders
WHERE status = 'A'
GROUP BY cust_id

db.orders.aggregate( [
   { $match: { status: 'A' } },
   { $group: { _id: "$cust_id",
               total: { $sum: "$price" } } }
] )

For each uniquecust_id with status A, sum the price field.

SELECT cust_id,
       SUM(price) as total
FROM orders
WHERE status = 'A'
GROUP BY cust_id
HAVING total > 250

db.orders.aggregate( [
   { $match: { status: 'A' } },
   { $group: { _id: "$cust_id",
               total: { $sum: "$price" } } },
   { $match: { total: { $gt: 250 } } }
] )

For each uniquecust_id with status A, sum the price field and return only where the sum is greater than 250.

SELECT cust_id,
       SUM(li.qty) as qty
FROM orders o,
     order_lineitem li
WHERE li.order_id = o.id
GROUP BY cust_id

db.orders.aggregate( [
   { $unwind: "$items" },
   { $group: { _id: "$cust_id",
               qty: { $sum: "$items.qty" } } }
] )

For each uniquecust_id, sum the corresponding line itemqty fields associated with the orders.

SELECT COUNT(*)
FROM (SELECT cust_id, ord_date
      FROM orders
      GROUP BY cust_id, ord_date) as DerivedTable

db.orders.aggregate( [
   { $group: { _id: { cust_id: "$cust_id",
                      ord_date: "$ord_date" } } },
   { $group: { _id: null, count: { $sum: 1 } } }
] )

Count the number of distinct cust_id,ord_date groupings.


728x90
저작자표시 비영리 변경금지

'오래된글 > DataBase' 카테고리의 다른 글

Oracle User Defined Object을 이용한 Table Return Function  (0) 2018.04.17
Oracle 중요 Hint  (0) 2018.04.17
MongoDB, 설치와 간단 사용법  (0) 2018.04.17
MongoDB 제한자  (0) 2018.04.17
Index가 옵티마이저에 의해 수행되는 경우  (0) 2018.04.15
'오래된글/DataBase' Related Articles more
Comments