mongo db 기초

SQL

mongo db 기초

J.H_DA 2022. 3. 17. 15:14

오늘은 몽고 db를 활용하여 sql 문을 파이썬을 통해 실행해 보았다.

mongo --version

C:\WINDOWS\system32>mongo
시작시키기

---
> show dbs
admin   0.000GB
config  0.000GB
local   0.000GB
>
> use ubion
switched to db ubion
> show dbs
admin   0.000GB
config  0.000GB
local   0.000GB

확인하기

> db.createCollection("test", {capped: true, size:6142800, max:10000})

true할때 소문자로 해줘야함.

insert문
> db.test.insert({"name":"test","phone":"01057862039"})
WriteResult({ "nInserted" : 1 })

제대로 들어갔는지 확인해보기
> db.test.find()
{ "_id" : ObjectId("62328e768a00dfee784c7f37"), "name" : "test", "phone" : "01057862039" }

test2를 만들때에는 create안해줘도 됐었음.어쩌면 간편한 것임
> db.test2.insert({"name":"test2","phone":"01092162039"})
WriteResult({ "nInserted" : 1 })

> db.test.insert([{"loc":"Seoul"},{"gender":"male","age":20,"hobby": "soccer"}])
BulkWriteResult({
        "writeErrors" : [ ],
        "writeConcernErrors" : [ ],
        "nInserted" : 2,
        "nUpserted" : 0,
        "nMatched" : 0,
        "nModified" : 0,
        "nRemoved" : 0,
        "upserted" : [ ]

딕셔너리 하나랑 세개를 같이 넣어줬기 때문에 []필요하다.

> db.test.find()
{ "_id" : ObjectId("62328e768a00dfee784c7f37"), "name" : "test", "phone" : "01057862039" }
{ "_id" : ObjectId("623290568a00dfee784c7f39"), "loc" : "Seoul" }
{ "_id" : ObjectId("623290568a00dfee784c7f3a"), "gender" : "male", "age" : 20, "hobby" : "soccer" }
>여기서 보면 _id 저장되어있는 주소 자동생서 겹치지 않음. 프라이머리키 넣는 것은 다들어감

> db.test.find({"name":"test"})
{ "_id" : ObjectId("62328e768a00dfee784c7f37"), "name" : "test", "phone" : "01057862039" }
이렇게 컬럼하나만 쳐줘도 나올 수 있음.

insert해주기
> db.test.insert({"name":"test2","age":30})
WriteResult({ "nInserted" : 1 })

name이라는 것만 출력해주기 앞에{}를 넣어줘야 name이라는 값을 나올 수 있게함.
> db.test.find({},{"name":1})
{ "_id" : ObjectId("62328e768a00dfee784c7f37"), "name" : "test" }
{ "_id" : ObjectId("623290568a00dfee784c7f39") }
{ "_id" : ObjectId("623290568a00dfee784c7f3a") }
{ "_id" : ObjectId("623291a98a00dfee784c7f3b"), "name" : "test2" }
>

0과 1은 true,false임.
> db.test.find({},{"name":1,"_id":0})
{ "name" : "test" }
{  }
{  }
{ "name" : "test2" }
>

test2 삭제해주기
> db.test2.drop()
true

삭제후 확인하기-여기서보면 test밖에 없음
> show collections
test

cannot remove from a capped collection: ubion.test
collection은 remove는 안되고 drop은 가능

주피터에서 몽고실행하기
mongo라는 폴더 만들고, 220317.ipynb파일 만들기
cmd에서 pip install pymongo설치 후 시작

from pymongo import MongoClient

client에 커넥션해주기
host = 'localhost'
    port=27017
몽고디비 포트번호 27017

데이터베이스 확인해주는 것
client.list_database_names()

from pymongo import MongoClient

In [6]:

client=MongoClient(
    host = 'localhost',
    port=27017
)

In [7]:

client.list_database_names()
#데이터베이스 확인해주기

Out[7]:

['admin', 'config', 'local', 'ubion']

In [8]:

db=client["ubion"]
#db라고 하는 변수를 쓰면 ubion이라고 하는 데이터베이스를 쓰는것

In [9]:

db.test.find()
#test라는 거 불러주기
#데이터 형태때문에 출력이 안되는 것

Out[9]:

<pymongo.cursor.Cursor at 0x1edefe5d100>

In [10]:

list(db.test.find())
#딕셔너리로 저장되어있던 것을 리스트형태로 해놓음 그래야 출력해서 나옴.
#클래스와 함수를 불러오는 것 .의 형태

Out[10]:

[{'_id': ObjectId('62328e816b824df43fef8783'),
  'name': 'test',
  'phone': '01012345678'},
 {'_id': ObjectId('623290556b824df43fef8785'), 'loc': 'Seoul'},
 {'_id': ObjectId('623290556b824df43fef8786'),
  'gender': 'male',
  'age': 20.0,
  'hobby': 'soccer'}]

In [11]:

cb=client["ubion"]
list(db["test"].find())
#키값의 형태를 불러오는 ["test"]형태

Out[11]:

[{'_id': ObjectId('62328e816b824df43fef8783'),
  'name': 'test',
  'phone': '01012345678'},
 {'_id': ObjectId('623290556b824df43fef8785'), 'loc': 'Seoul'},
 {'_id': ObjectId('623290556b824df43fef8786'),
  'gender': 'male',
  'age': 20.0,
  'hobby': 'soccer'}]

In [12]:

data={
    "1": "1",
    "2":"2",
    "3":"3"
}
db.test.insert_one(data)
#.insert_one,.insert_many 차이가 쉽게 보임

Out[12]:

<pymongo.results.InsertOneResult at 0x1edf2903900>

In [13]:

#
data_2=[{
    "team_1":"Chelsea",
     "team_2":"Manchester",
     "team_3": "Arsenal"},
    {
    "food_1": "ramen",
    "food_2":"chicken",
    "food_3":"shrimp"

    }]
db.test.insert_many(data_2)

Out[13]:

<pymongo.results.InsertManyResult at 0x1edf29012c0>

In [14]:

list(db["test"].find())

Out[14]:

[{'_id': ObjectId('62328e816b824df43fef8783'),
  'name': 'test',
  'phone': '01012345678'},
 {'_id': ObjectId('623290556b824df43fef8785'), 'loc': 'Seoul'},
 {'_id': ObjectId('623290556b824df43fef8786'),
  'gender': 'male',
  'age': 20.0,
  'hobby': 'soccer'},
 {'_id': ObjectId('6232b45ba1fe1113784d5008'), '1': '1', '2': '2', '3': '3'},
 {'_id': ObjectId('6232b45da1fe1113784d5009'),
  'team_1': 'Chelsea',
  'team_2': 'Manchester',
  'team_3': 'Arsenal'},
 {'_id': ObjectId('6232b45da1fe1113784d500a'),
  'food_1': 'ramen',
  'food_2': 'chicken',
  'food_3': 'shrimp'}]

In [15]:

list(db.test.find({},{"food_1" :1,"food_2" :1}))
#true와 false인 값을 1,0으로 구분해서 (모든 데이터에서 일부 key값을 가져오기: db.[컬렉션명].find({},{key:1,key:1..}))

Out[15]:

[{'_id': ObjectId('62328e816b824df43fef8783')},
 {'_id': ObjectId('623290556b824df43fef8785')},
 {'_id': ObjectId('623290556b824df43fef8786')},
 {'_id': ObjectId('6232b45ba1fe1113784d5008')},
 {'_id': ObjectId('6232b45da1fe1113784d5009')},
 {'_id': ObjectId('6232b45da1fe1113784d500a'),
  'food_1': 'ramen',
  'food_2': 'chicken'}]

In [20]:

import mod_sql
import pandas as pd
import pprint

In [17]:

_db = mod_sql.Database()

In [24]:

sql =  """ 
       SELECT * FROM emp
       """
result = _db.executeAll(sql)
pd.DataFrame(result).to_csv("emp.csv")

In [25]:

db.emp.find_one

Out[25]:

<bound method Collection.find_one of Collection(Database(MongoClient(host=['localhost:27017'], document_class=dict, tz_aware=False, connect=True), 'ubion'), 'emp')>

In [27]:

list(db.emp.find(
     {"ENAME" : "SMITH"
     }
))
# 이거는 select * from where ename = "smith"

Out[27]:

[{'_id': ObjectId('6232b7a691d4755e775ae5b8'),
  '': '0',
  'ENAME': 'SMITH',
  'JOB': 'CLERK',
  'MGR': '7902',
  'HIREDATE': '1980-12-17',
  'SAL': '800.00',
  'DEPTNO': '20'}]

In [28]:

result = db.emp.find()

for list in result:
    print(list)

{'_id': ObjectId('6232b9a991d4755e775ae5c8'), 'EMPNO': '7369', 'ENAME': 'SMITH', 'JOB': 'CLERK', 'MGR': '7902', 'HIREDATE': '1980-12-17', 'SAL': '800.00', 'DEPTNO': '20'}
{'_id': ObjectId('6232b9a991d4755e775ae5c9'), 'EMPNO': '7499', 'ENAME': 'ALLEN', 'JOB': 'SALESMAN', 'MGR': '7698', 'HIREDATE': '1981-02-20', 'SAL': '1600.00', 'COMM': '300.00', 'DEPTNO': '30'}
{'_id': ObjectId('6232b9a991d4755e775ae5ca'), 'EMPNO': '7521', 'ENAME': 'WARD', 'JOB': 'SALESMAN', 'MGR': '7698', 'HIREDATE': '1981-02-22', 'SAL': '1250.00', 'COMM': '500.00', 'DEPTNO': '30'}
{'_id': ObjectId('6232b9a991d4755e775ae5cb'), 'EMPNO': '7566', 'ENAME': 'JONES', 'JOB': 'MANAGER', 'MGR': '7839', 'HIREDATE': '1981-04-02', 'SAL': '2975.00', 'DEPTNO': '20'}
{'_id': ObjectId('6232b9a991d4755e775ae5cc'), 'EMPNO': '7654', 'ENAME': 'MARTIN', 'JOB': 'SALESMAN', 'MGR': '7698', 'HIREDATE': '1981-09-28', 'SAL': '1250.00', 'COMM': '1400.00', 'DEPTNO': '30'}
{'_id': ObjectId('6232b9a991d4755e775ae5cd'), 'EMPNO': '7698', 'ENAME': 'BLAKE', 'JOB': 'MANAGER', 'MGR': '7839', 'HIREDATE': '1981-05-01', 'SAL': '2850.00', 'DEPTNO': '30'}
{'_id': ObjectId('6232b9a991d4755e775ae5ce'), 'EMPNO': '7782', 'ENAME': 'CLARK', 'JOB': 'MANAGER', 'MGR': '7839', 'HIREDATE': '1981-06-09', 'SAL': '2450.00', 'DEPTNO': '10'}
{'_id': ObjectId('6232b9a991d4755e775ae5cf'), 'EMPNO': '7788', 'ENAME': 'SCOTT', 'JOB': 'ANALYST', 'MGR': '7566', 'HIREDATE': '1987-06-28', 'SAL': '3000.00', 'DEPTNO': '20'}
{'_id': ObjectId('6232b9a991d4755e775ae5d0'), 'EMPNO': '7839', 'ENAME': 'KING', 'JOB': 'PRESIDENT', 'HIREDATE': '1981-11-17', 'SAL': '5000.00', 'DEPTNO': '10'}
{'_id': ObjectId('6232b9a991d4755e775ae5d1'), 'EMPNO': '7844', 'ENAME': 'TURNER', 'JOB': 'SALESMAN', 'MGR': '7698', 'HIREDATE': '1981-09-08', 'SAL': '1500.00', 'COMM': '0.00', 'DEPTNO': '30'}
{'_id': ObjectId('6232b9a991d4755e775ae5d2'), 'EMPNO': '7876', 'ENAME': 'ADAMS', 'JOB': 'CLERK', 'MGR': '7788', 'HIREDATE': '1987-07-13', 'SAL': '1100.00', 'DEPTNO': '20'}
{'_id': ObjectId('6232b9a991d4755e775ae5d3'), 'EMPNO': '7900', 'ENAME': 'JAMES', 'JOB': 'CLERK', 'MGR': '7698', 'HIREDATE': '1981-12-03', 'SAL': '950.00', 'DEPTNO': '30'}
{'_id': ObjectId('6232b9a991d4755e775ae5d4'), 'EMPNO': '7902', 'ENAME': 'FORD', 'JOB': 'ANALYST', 'MGR': '7566', 'HIREDATE': '1981-12-03', 'SAL': '3000.00', 'DEPTNO': '20'}
{'_id': ObjectId('6232b9a991d4755e775ae5d5'), 'EMPNO': '7934', 'ENAME': 'MILLER', 'JOB': 'CLERK', 'MGR': '7782', 'HIREDATE': '1982-01-23', 'SAL': '1300.00', 'DEPTNO': '10'}

In [30]:

## emp collection
## key 값이 empno, ename, deptno 만 출력이 되도록 코드를 작성

result = db.emp.find(
    {},               # where 조건을 쓸때 사용하는 공간
    {"EMPNO": 1, 
     "ENAME":1,
     "DEPTNO": 1} #WHERE 절
) # select empno, ename, deptno from emp
for list in result:
    print(list)

{'_id': ObjectId('6232b9a991d4755e775ae5c8'), 'EMPNO': '7369', 'ENAME': 'SMITH', 'DEPTNO': '20'}
{'_id': ObjectId('6232b9a991d4755e775ae5c9'), 'EMPNO': '7499', 'ENAME': 'ALLEN', 'DEPTNO': '30'}
{'_id': ObjectId('6232b9a991d4755e775ae5ca'), 'EMPNO': '7521', 'ENAME': 'WARD', 'DEPTNO': '30'}
{'_id': ObjectId('6232b9a991d4755e775ae5cb'), 'EMPNO': '7566', 'ENAME': 'JONES', 'DEPTNO': '20'}
{'_id': ObjectId('6232b9a991d4755e775ae5cc'), 'EMPNO': '7654', 'ENAME': 'MARTIN', 'DEPTNO': '30'}
{'_id': ObjectId('6232b9a991d4755e775ae5cd'), 'EMPNO': '7698', 'ENAME': 'BLAKE', 'DEPTNO': '30'}
{'_id': ObjectId('6232b9a991d4755e775ae5ce'), 'EMPNO': '7782', 'ENAME': 'CLARK', 'DEPTNO': '10'}
{'_id': ObjectId('6232b9a991d4755e775ae5cf'), 'EMPNO': '7788', 'ENAME': 'SCOTT', 'DEPTNO': '20'}
{'_id': ObjectId('6232b9a991d4755e775ae5d0'), 'EMPNO': '7839', 'ENAME': 'KING', 'DEPTNO': '10'}
{'_id': ObjectId('6232b9a991d4755e775ae5d1'), 'EMPNO': '7844', 'ENAME': 'TURNER', 'DEPTNO': '30'}
{'_id': ObjectId('6232b9a991d4755e775ae5d2'), 'EMPNO': '7876', 'ENAME': 'ADAMS', 'DEPTNO': '20'}
{'_id': ObjectId('6232b9a991d4755e775ae5d3'), 'EMPNO': '7900', 'ENAME': 'JAMES', 'DEPTNO': '30'}
{'_id': ObjectId('6232b9a991d4755e775ae5d4'), 'EMPNO': '7902', 'ENAME': 'FORD', 'DEPTNO': '20'}
{'_id': ObjectId('6232b9a991d4755e775ae5d5'), 'EMPNO': '7934', 'ENAME': 'MILLER', 'DEPTNO': '10'}

In [32]:

def find(a, b=3):
    return a+b
find("", "5")

Out[32]:

'5'

In [33]:

result = db.emp.find().sort("ENAME")
for list in result:
    print(list)
# select * from emp order by ename

{'_id': ObjectId('6232b9a991d4755e775ae5d2'), 'EMPNO': '7876', 'ENAME': 'ADAMS', 'JOB': 'CLERK', 'MGR': '7788', 'HIREDATE': '1987-07-13', 'SAL': '1100.00', 'DEPTNO': '20'}
{'_id': ObjectId('6232b9a991d4755e775ae5c9'), 'EMPNO': '7499', 'ENAME': 'ALLEN', 'JOB': 'SALESMAN', 'MGR': '7698', 'HIREDATE': '1981-02-20', 'SAL': '1600.00', 'COMM': '300.00', 'DEPTNO': '30'}
{'_id': ObjectId('6232b9a991d4755e775ae5cd'), 'EMPNO': '7698', 'ENAME': 'BLAKE', 'JOB': 'MANAGER', 'MGR': '7839', 'HIREDATE': '1981-05-01', 'SAL': '2850.00', 'DEPTNO': '30'}
{'_id': ObjectId('6232b9a991d4755e775ae5ce'), 'EMPNO': '7782', 'ENAME': 'CLARK', 'JOB': 'MANAGER', 'MGR': '7839', 'HIREDATE': '1981-06-09', 'SAL': '2450.00', 'DEPTNO': '10'}
{'_id': ObjectId('6232b9a991d4755e775ae5d4'), 'EMPNO': '7902', 'ENAME': 'FORD', 'JOB': 'ANALYST', 'MGR': '7566', 'HIREDATE': '1981-12-03', 'SAL': '3000.00', 'DEPTNO': '20'}
{'_id': ObjectId('6232b9a991d4755e775ae5d3'), 'EMPNO': '7900', 'ENAME': 'JAMES', 'JOB': 'CLERK', 'MGR': '7698', 'HIREDATE': '1981-12-03', 'SAL': '950.00', 'DEPTNO': '30'}
{'_id': ObjectId('6232b9a991d4755e775ae5cb'), 'EMPNO': '7566', 'ENAME': 'JONES', 'JOB': 'MANAGER', 'MGR': '7839', 'HIREDATE': '1981-04-02', 'SAL': '2975.00', 'DEPTNO': '20'}
{'_id': ObjectId('6232b9a991d4755e775ae5d0'), 'EMPNO': '7839', 'ENAME': 'KING', 'JOB': 'PRESIDENT', 'HIREDATE': '1981-11-17', 'SAL': '5000.00', 'DEPTNO': '10'}
{'_id': ObjectId('6232b9a991d4755e775ae5cc'), 'EMPNO': '7654', 'ENAME': 'MARTIN', 'JOB': 'SALESMAN', 'MGR': '7698', 'HIREDATE': '1981-09-28', 'SAL': '1250.00', 'COMM': '1400.00', 'DEPTNO': '30'}
{'_id': ObjectId('6232b9a991d4755e775ae5d5'), 'EMPNO': '7934', 'ENAME': 'MILLER', 'JOB': 'CLERK', 'MGR': '7782', 'HIREDATE': '1982-01-23', 'SAL': '1300.00', 'DEPTNO': '10'}
{'_id': ObjectId('6232b9a991d4755e775ae5cf'), 'EMPNO': '7788', 'ENAME': 'SCOTT', 'JOB': 'ANALYST', 'MGR': '7566', 'HIREDATE': '1987-06-28', 'SAL': '3000.00', 'DEPTNO': '20'}
{'_id': ObjectId('6232b9a991d4755e775ae5c8'), 'EMPNO': '7369', 'ENAME': 'SMITH', 'JOB': 'CLERK', 'MGR': '7902', 'HIREDATE': '1980-12-17', 'SAL': '800.00', 'DEPTNO': '20'}
{'_id': ObjectId('6232b9a991d4755e775ae5d1'), 'EMPNO': '7844', 'ENAME': 'TURNER', 'JOB': 'SALESMAN', 'MGR': '7698', 'HIREDATE': '1981-09-08', 'SAL': '1500.00', 'COMM': '0.00', 'DEPTNO': '30'}
{'_id': ObjectId('6232b9a991d4755e775ae5ca'), 'EMPNO': '7521', 'ENAME': 'WARD', 'JOB': 'SALESMAN', 'MGR': '7698', 'HIREDATE': '1981-02-22', 'SAL': '1250.00', 'COMM': '500.00', 'DEPTNO': '30'}

In [36]:

# 급여가 2000보다 큰 데이터만 출력
result=db.emp.find(
       { 
           "SAL" : {
               "$gt": 2000 # 2000 보다 큰
           }

       }
)
for list in result:
    print(list)
    # select * from emp where sal>2000;

{'_id': ObjectId('6232c6a891d4755e775ae5d9'), 'EMPNO': 7566, 'ENAME': 'JONES', 'JOB': 'MANAGER', 'MGR': 7839, 'HIREDATE': '1981-04-02', 'SAL': 2975.0, 'DEPTNO': 20}
{'_id': ObjectId('6232c6a891d4755e775ae5db'), 'EMPNO': 7698, 'ENAME': 'BLAKE', 'JOB': 'MANAGER', 'MGR': 7839, 'HIREDATE': '1981-05-01', 'SAL': 2850.0, 'DEPTNO': 30}
{'_id': ObjectId('6232c6a891d4755e775ae5dc'), 'EMPNO': 7782, 'ENAME': 'CLARK', 'JOB': 'MANAGER', 'MGR': 7839, 'HIREDATE': '1981-06-09', 'SAL': 2450.0, 'DEPTNO': 10}
{'_id': ObjectId('6232c6a891d4755e775ae5dd'), 'EMPNO': 7788, 'ENAME': 'SCOTT', 'JOB': 'ANALYST', 'MGR': 7566, 'HIREDATE': '1987-06-28', 'SAL': 3000.0, 'DEPTNO': 20}
{'_id': ObjectId('6232c6a891d4755e775ae5de'), 'EMPNO': 7839, 'ENAME': 'KING', 'JOB': 'PRESIDENT', 'HIREDATE': '1981-11-17', 'SAL': 5000.0, 'DEPTNO': 10}
{'_id': ObjectId('6232c6a891d4755e775ae5e2'), 'EMPNO': 7902, 'ENAME': 'FORD', 'JOB': 'ANALYST', 'MGR': 7566, 'HIREDATE': '1981-12-03', 'SAL': 3000.0, 'DEPTNO': 20}

In [37]:

result=db.emp.find(
       { 
           "SAL" : {
               "$gte": 2000 # 2000 보다 크고 같은
           }

       }
)
for list in result:
    print(list)
    # select * from emp where sal>=2000;

{'_id': ObjectId('6232c6a891d4755e775ae5d9'), 'EMPNO': 7566, 'ENAME': 'JONES', 'JOB': 'MANAGER', 'MGR': 7839, 'HIREDATE': '1981-04-02', 'SAL': 2975.0, 'DEPTNO': 20}
{'_id': ObjectId('6232c6a891d4755e775ae5db'), 'EMPNO': 7698, 'ENAME': 'BLAKE', 'JOB': 'MANAGER', 'MGR': 7839, 'HIREDATE': '1981-05-01', 'SAL': 2850.0, 'DEPTNO': 30}
{'_id': ObjectId('6232c6a891d4755e775ae5dc'), 'EMPNO': 7782, 'ENAME': 'CLARK', 'JOB': 'MANAGER', 'MGR': 7839, 'HIREDATE': '1981-06-09', 'SAL': 2450.0, 'DEPTNO': 10}
{'_id': ObjectId('6232c6a891d4755e775ae5dd'), 'EMPNO': 7788, 'ENAME': 'SCOTT', 'JOB': 'ANALYST', 'MGR': 7566, 'HIREDATE': '1987-06-28', 'SAL': 3000.0, 'DEPTNO': 20}
{'_id': ObjectId('6232c6a891d4755e775ae5de'), 'EMPNO': 7839, 'ENAME': 'KING', 'JOB': 'PRESIDENT', 'HIREDATE': '1981-11-17', 'SAL': 5000.0, 'DEPTNO': 10}
{'_id': ObjectId('6232c6a891d4755e775ae5e2'), 'EMPNO': 7902, 'ENAME': 'FORD', 'JOB': 'ANALYST', 'MGR': 7566, 'HIREDATE': '1981-12-03', 'SAL': 3000.0, 'DEPTNO': 20}

In [38]:

result=db.emp.find(
       { 
           "SAL" : {
               "$lt": 2450 # 2450 보다 작은
           }

       }
)
for list in result:
    print(list)
    # select * from emp where sal<2000;

{'_id': ObjectId('6232c6a891d4755e775ae5d6'), 'EMPNO': 7369, 'ENAME': 'SMITH', 'JOB': 'CLERK', 'MGR': 7902, 'HIREDATE': '1980-12-17', 'SAL': 800.0, 'DEPTNO': 20}
{'_id': ObjectId('6232c6a891d4755e775ae5d7'), 'EMPNO': 7499, 'ENAME': 'ALLEN', 'JOB': 'SALESMAN', 'MGR': 7698, 'HIREDATE': '1981-02-20', 'SAL': 1600.0, 'COMM': 300.0, 'DEPTNO': 30}
{'_id': ObjectId('6232c6a891d4755e775ae5d8'), 'EMPNO': 7521, 'ENAME': 'WARD', 'JOB': 'SALESMAN', 'MGR': 7698, 'HIREDATE': '1981-02-22', 'SAL': 1250.0, 'COMM': 500.0, 'DEPTNO': 30}
{'_id': ObjectId('6232c6a891d4755e775ae5da'), 'EMPNO': 7654, 'ENAME': 'MARTIN', 'JOB': 'SALESMAN', 'MGR': 7698, 'HIREDATE': '1981-09-28', 'SAL': 1250.0, 'COMM': 1400.0, 'DEPTNO': 30}
{'_id': ObjectId('6232c6a891d4755e775ae5df'), 'EMPNO': 7844, 'ENAME': 'TURNER', 'JOB': 'SALESMAN', 'MGR': 7698, 'HIREDATE': '1981-09-08', 'SAL': 1500.0, 'COMM': 0.0, 'DEPTNO': 30}
{'_id': ObjectId('6232c6a891d4755e775ae5e0'), 'EMPNO': 7876, 'ENAME': 'ADAMS', 'JOB': 'CLERK', 'MGR': 7788, 'HIREDATE': '1987-07-13', 'SAL': 1100.0, 'DEPTNO': 20}
{'_id': ObjectId('6232c6a891d4755e775ae5e1'), 'EMPNO': 7900, 'ENAME': 'JAMES', 'JOB': 'CLERK', 'MGR': 7698, 'HIREDATE': '1981-12-03', 'SAL': 950.0, 'DEPTNO': 30}
{'_id': ObjectId('6232c6a891d4755e775ae5e3'), 'EMPNO': 7934, 'ENAME': 'MILLER', 'JOB': 'CLERK', 'MGR': 7782, 'HIREDATE': '1982-01-23', 'SAL': 1300.0, 'DEPTNO': 10}

In [39]:

result=db.emp.find(
       { 
           "SAL" : {
               "$lte": 2450 # 2450 보다 같고 작은
           }

       }
)
for list in result:
    print(list)
    # select * from emp where sal<=2000;

{'_id': ObjectId('6232c6a891d4755e775ae5d6'), 'EMPNO': 7369, 'ENAME': 'SMITH', 'JOB': 'CLERK', 'MGR': 7902, 'HIREDATE': '1980-12-17', 'SAL': 800.0, 'DEPTNO': 20}
{'_id': ObjectId('6232c6a891d4755e775ae5d7'), 'EMPNO': 7499, 'ENAME': 'ALLEN', 'JOB': 'SALESMAN', 'MGR': 7698, 'HIREDATE': '1981-02-20', 'SAL': 1600.0, 'COMM': 300.0, 'DEPTNO': 30}
{'_id': ObjectId('6232c6a891d4755e775ae5d8'), 'EMPNO': 7521, 'ENAME': 'WARD', 'JOB': 'SALESMAN', 'MGR': 7698, 'HIREDATE': '1981-02-22', 'SAL': 1250.0, 'COMM': 500.0, 'DEPTNO': 30}
{'_id': ObjectId('6232c6a891d4755e775ae5da'), 'EMPNO': 7654, 'ENAME': 'MARTIN', 'JOB': 'SALESMAN', 'MGR': 7698, 'HIREDATE': '1981-09-28', 'SAL': 1250.0, 'COMM': 1400.0, 'DEPTNO': 30}
{'_id': ObjectId('6232c6a891d4755e775ae5dc'), 'EMPNO': 7782, 'ENAME': 'CLARK', 'JOB': 'MANAGER', 'MGR': 7839, 'HIREDATE': '1981-06-09', 'SAL': 2450.0, 'DEPTNO': 10}
{'_id': ObjectId('6232c6a891d4755e775ae5df'), 'EMPNO': 7844, 'ENAME': 'TURNER', 'JOB': 'SALESMAN', 'MGR': 7698, 'HIREDATE': '1981-09-08', 'SAL': 1500.0, 'COMM': 0.0, 'DEPTNO': 30}
{'_id': ObjectId('6232c6a891d4755e775ae5e0'), 'EMPNO': 7876, 'ENAME': 'ADAMS', 'JOB': 'CLERK', 'MGR': 7788, 'HIREDATE': '1987-07-13', 'SAL': 1100.0, 'DEPTNO': 20}
{'_id': ObjectId('6232c6a891d4755e775ae5e1'), 'EMPNO': 7900, 'ENAME': 'JAMES', 'JOB': 'CLERK', 'MGR': 7698, 'HIREDATE': '1981-12-03', 'SAL': 950.0, 'DEPTNO': 30}
{'_id': ObjectId('6232c6a891d4755e775ae5e3'), 'EMPNO': 7934, 'ENAME': 'MILLER', 'JOB': 'CLERK', 'MGR': 7782, 'HIREDATE': '1982-01-23', 'SAL': 1300.0, 'DEPTNO': 10}

In [41]:

# select * from emp where ENAME = SMITH OR ENAME = ALLEN
result=db.emp.find({
    "$or" : [
        {"ENAME" : "SMITH"},
        {"ENAME" : "ALLEN"}
    ]}
)
for list in result: 
    print(list)

{'_id': ObjectId('6232b9a991d4755e775ae5c8'), 'EMPNO': '7369', 'ENAME': 'SMITH', 'JOB': 'CLERK', 'MGR': '7902', 'HIREDATE': '1980-12-17', 'SAL': '800.00', 'DEPTNO': '20'}
{'_id': ObjectId('6232b9a991d4755e775ae5c9'), 'EMPNO': '7499', 'ENAME': 'ALLEN', 'JOB': 'SALESMAN', 'MGR': '7698', 'HIREDATE': '1981-02-20', 'SAL': '1600.00', 'COMM': '300.00', 'DEPTNO': '30'}
{'_id': ObjectId('6232c6a891d4755e775ae5d6'), 'EMPNO': 7369, 'ENAME': 'SMITH', 'JOB': 'CLERK', 'MGR': 7902, 'HIREDATE': '1980-12-17', 'SAL': 800.0, 'DEPTNO': 20}
{'_id': ObjectId('6232c6a891d4755e775ae5d7'), 'EMPNO': 7499, 'ENAME': 'ALLEN', 'JOB': 'SALESMAN', 'MGR': 7698, 'HIREDATE': '1981-02-20', 'SAL': 1600.0, 'COMM': 300.0, 'DEPTNO': 30}

In [42]:

# seect * from emp where ENAME = SMITH AND JOB = CLERK
result=db.emp.find({
    "ENAME":"SMITH",
    "JOB": "CLERK"
        
    }
)
for list in result: 
    print(list)

{'_id': ObjectId('6232b9a991d4755e775ae5c8'), 'EMPNO': '7369', 'ENAME': 'SMITH', 'JOB': 'CLERK', 'MGR': '7902', 'HIREDATE': '1980-12-17', 'SAL': '800.00', 'DEPTNO': '20'}
{'_id': ObjectId('6232c6a891d4755e775ae5d6'), 'EMPNO': 7369, 'ENAME': 'SMITH', 'JOB': 'CLERK', 'MGR': 7902, 'HIREDATE': '1980-12-17', 'SAL': 800.0, 'DEPTNO': 20}

In [51]:

# SELECT * FROM emp WHERE SAL >=2000 AND SAL < 4000

result = db.emp.find(
    {"SAL":{"$gte": 2000,"$lt": 4000}},
     {
         "_id": 0 
      })
pd.DataFrame(result)

Out[51]:

EMPNOENAMEJOBMGRHIREDATESALDEPTNO01234

7566	JONES	MANAGER	7839	1981-04-02	2975.0	20
7698	BLAKE	MANAGER	7839	1981-05-01	2850.0	30
7782	CLARK	MANAGER	7839	1981-06-09	2450.0	10
7788	SCOTT	ANALYST	7566	1987-06-28	3000.0	20
7902	FORD	ANALYST	7566	1981-12-03	3000.0	20

In [55]:

# SELECT * FROM emp where ENAME LIKE = "%S"
result = db.emp.find(
    {
        "ENAME" : {
            "$regex" : "^S"
        }
            
    }
)
pd.DataFrame(result)

Out[55]:

_idEMPNOENAMEJOBMGRHIREDATESALDEPTNO0123

6232b9a991d4755e775ae5c8	7369	SMITH	CLERK	7902	1980-12-17	800.00	20
6232b9a991d4755e775ae5cf	7788	SCOTT	ANALYST	7566	1987-06-28	3000.00	20
6232c6a891d4755e775ae5d6	7369	SMITH	CLERK	7902	1980-12-17	800.0	20
6232c6a891d4755e775ae5dd	7788	SCOTT	ANALYST	7566	1987-06-28	3000.0	20

In [56]:

# S포함하는 것들 출력
result = db.emp.find(
    {
        "ENAME" : {
            "$regex" : "S"
    }}
)
pd.DataFrame(result)

Out[56]:

_idEMPNOENAMEJOBMGRHIREDATESALDEPTNO0123456789

6232b9a991d4755e775ae5c8	7369	SMITH	CLERK	7902	1980-12-17	800.00	20
6232b9a991d4755e775ae5cb	7566	JONES	MANAGER	7839	1981-04-02	2975.00	20
6232b9a991d4755e775ae5cf	7788	SCOTT	ANALYST	7566	1987-06-28	3000.00	20
6232b9a991d4755e775ae5d2	7876	ADAMS	CLERK	7788	1987-07-13	1100.00	20
6232b9a991d4755e775ae5d3	7900	JAMES	CLERK	7698	1981-12-03	950.00	30
6232c6a891d4755e775ae5d6	7369	SMITH	CLERK	7902	1980-12-17	800.0	20
6232c6a891d4755e775ae5d9	7566	JONES	MANAGER	7839	1981-04-02	2975.0	20
6232c6a891d4755e775ae5dd	7788	SCOTT	ANALYST	7566	1987-06-28	3000.0	20
6232c6a891d4755e775ae5e0	7876	ADAMS	CLERK	7788	1987-07-13	1100.0	20
6232c6a891d4755e775ae5e1	7900	JAMES	CLERK	7698	1981-12-03	950.0	30

In [68]:

# SELECT ENAME, JOB, SAL, DEPTNO FROM emp 
# WHERE SAL > 1500 and SAL <=3000 AND JOB = SALESMAN
# ORDER BY ENAME
result = db.emp.find(
    {
    "$and" : [
    {"SAL":{"$gt": 1250}},{"SAL":{"$lte": 3000}},{"JOB" : "SALESMAN"}]},{ "ENAME":1,
         "JOB":1,
         "SAL":1,
         "DEPTNO":1,
         "_id":0}
).sort("ENAME", 1) # 1일경우 오름차순 -1일경우 내림차순이다.
pd.DataFrame(result)

Out[68]:

ENAMEJOBSALDEPTNO01

ALLEN	SALESMAN	1600.0	30
TURNER	SALESMAN	1500.0	30

In [63]:

result = db.emp.find(
    {
        "SAL":{"$gt": 1250,"$lte": 3000},
        "JOB" : "SALESMAN"
        },
     {
         "ENAME":1,
         "JOB":1,
         "SAL":1,
         "DEPTNO":1,
         "_id":0 
      }
).sort("ENAME")


pd.DataFrame(result)

Out[63]:

ENAMEJOBSALDEPTNO01

ALLEN	SALESMAN	1600.0	30
TURNER	SALESMAN	1500.0	30

728x90

'SQL' 카테고리의 다른 글

IMPALA(임팔라) (0)	2022.08.27
파이썬을 이용한 SQL문 연습 (0)	2022.03.16
SQL- 실습 (파이썬에서 연동) (0)	2022.03.10

현재글mongo db 기초

250x250

J.H의 Data Analyst 되기

안녕하세요 데이터 분석가가 되기 위한 공부를 기록하였습니다.

Today :
Yesterday :

J.H의 Data Analyst 되기

mongo db 기초

'SQL' 카테고리의 다른 글

'SQL'의 다른글

티스토리툴바

« 2025/07 »
일	월	화	수	목	금	토
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31

mongo db 기초

'SQL' 카테고리의 다른 글

'SQL'의 다른글

관련글

티스토리툴바