본문 바로가기
LG 헬로비전 DX DATA SCHOOL/DATA BASE

Oracle

by 황밤 2023. 7. 21.
728x90
반응형

2023- 07 -21

SMALL

oracle

 

1. 계층형 조회

1.1) 개요

  • oracle 에서 제공하는 기능
  • self join 과 함께 하나의 테이블에 동일한 의미를 갖는 컬럼이 2개 이상 존재하는 경우 사용 가능
  • 인사 테이블에서 사원의 아이디와 관리자의 아이디를 하나의 테이블로 관리하는 경우나 
  • Social Network에서 회원 아이디와 Follow Id를 하나의 테이블로 관리하는 경우 또는 구매 테이블이나 장바구니 같은 곳에서 상품 정보를 각각의 컬럼에 나열한 경우에 사용가능

 

하나의 컬럼에서 연관된 컬럼을 쫓아가면서 데이터를 조회하는 것

MILLER의 상관이 JAMES

JAMES의 상관이 TOM

TOM의 상관이 ADAM인 경우

 

MILLER에서 ADAM까지 조회하고자 하는 경우 또는 반대의 경우

 

 

1.2) 기본 형식

 

  • WHERE : 조건
  • START WITH : 계층 구조를 지정
  • CONNECT BY : 연결 관계를 설정

 

1.3) 연습

 

EMP 테이블에는 EMPNO라는 사원번호와 MGR이라고 하는 관리자 사원 번호 컬럼이 존재

MGR이 NULL인 데이터가 가장 높은 레벨의 데이터

 

-- EMP 테이블의 레벨이 얼마가 최대인지 확인

SELECT MAX(LEVEL)

FROM EMP

START WITH MGR IS NULL

CONNECT BY PRIOR EMPNO = MGR;

 

  • CONNECT BY 가 추가되면 LEVEL이라는 컬럼이 자동으로 생성됩니다.

 

  • EMP 테이블에서 JOB이 PRESIDENT 인 데이터로부터 아래 방향으로 LEVEL과 ENAME, EMPNO, MGR을 조회 SELECT LEVEL, ENAME, EMPNO, MGR

FROM EMP

START WITH UPPER(JOB) = 'PRESIDENT'

CONNECT BY PRIOR EMPNO = 'MGR'

ORDER BY 나 CONNECT BY 에 조건을 추가하여 조회하는 것이 가능

 

TERMINAL(LEAF NODE - 자식이 없는 노드)여부 판단은 CONNECT_BY_ISLEAF 컬럼으로 조회

 

2.Pseudo Column

실제 테이블에는 존재하지만 인위적으로 추가한 가짜 컬럼

Oracle에서는 ROWID와 ROWNUM이라는 컬럼을 제공

 

 

2.1) ROWID

  • 데이터의 실제적인 참조 값
  • 오라클에서는 인스턴스 번호, 파일 번호, 블록 번호, 블록 내의 행 번호로 구성
  • 이 값을 이용해서 조회를 하면 가장 빠르게 조회 가능
  • SELECT로 조회 가능
--ROW ID 조회

SELECT ROWID, ENAME

FROM EMP;

 

  • 중복 제거에 유용

여러 컬럼을 조회해야 하는데 그 중 하나의 컬러의 중복을 제거해서 출력하고자 하는 경우

EMP 테이블에서 DEPTNO 별로 한 명의 DEPTNO와 ENAME을 조회

 

-- 다른 컬럼을 사용하지 않고 그룹화 한 후 ROWID가 가장 큰 데이터를 추출
SELECT DEPTNO, ENAME
FROM EMP
WHERE ROWID IN (SELECT MAX(ROWID) FROM EMP GROUP BY DEPTNO);
--ROWNUM을 이용한 조회 조건을 만들 때 주의
SELECT ROWNUM, ENAME
FROM EMP e WHERE ROWNUM < 3;

SELECT ROWNUM, ENMAE
FROM EMP
WHERE ROWNUM > 3;

 

 

3. 행 개수 제한

 

  • ORACLE 에서는 OFFSET과 FETCH를 SELECT 구문의 마지막에 추가해서 행의 개수를 제한할 수 있다.
  • SELECT 구문의 마지막 절에 추가하는데 OFFSET은 시작 위치이고 FETCH는 행의 개수이다.
  • ORACLE 12c 버전에서부터 지원

 

-- 급여의 내림차순으로 정렬해서 조회
SELECT *
FROM EMP
ORDER BY SAL DESC;


-- 급여의 내림차순으로 정렬해서 5개의 데이터만 조회
SELECT *
FROM EMP
ORDER BY SAL DESC
OFFSET 0
ROWS FETCH NEXT 5 ROWS ONLY;

 

 

4. Synonym

  • 별명, 동의어
  • 기존 객체에 별명을 붙이는 것
  • 응용 프로그램과 데이터베이스를 연결할 때 이용하면 유지보수가 편리해집니다.

응용 프로그램에게는 동의어를 사용하도록 하고 데이터베이스 내에서 이름을 변경할 때 동의어만 변경해주면 응용 프로그램을 변경할 필요가 없어지기 때문이다.

출처 : 알유 프린쓰 쏭? 예아

TDD Agile : 작은 기능들의 모음집.

 

 

5. SEQUENCE

일련 번호를 생성해주는 객체

MySQL은 AUTO_INCREMENT를 하나의 테이블에서 하나만 사용해야 하고 반드시 PRIMARY KEY 나 UNIQUE 제약 조건을 설정해야만 한다.

ORACLE의 SEQUENCE는 이러한 제약이 없다.

 

5.1) 생성

CREATE SEQUENCE 이름
            [START WITH 초기값]
            [INCREMENT BY 증감값]
            [MAXVALUE(최대값) | NOMAXVALUE]
            [MINVALUE(최소값) | NOMINVALUE]
            [CYCLE | NOCYCLE]
            [CACHE 개수 |NOCACHE];

 

 

5.2) 사용

이름.currval : 현재 값, nextval을 한 번은 호출해야 사용 가능

이름.nextval : 다음 값

 

5.3) 삭제

DROP SEQUENCE 이름;

 

5.4) 변경

ALTER SEQUENCE 이름

             START WITH를 제외한 모든 옵션 수정 가능

            

-- SEQUENCE 생성
-- 초기값은 50, 10씩 증가
CREATE SEQUENCE DEPT_SEQ
	START WITH 50
	INCREMENT BY 10;

-- 값 확인
SELECT DEPT_SEQ.NEXTVAL
FROM DUAL;

-- 시퀀스를 이용한 데이터 삽입
INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES(DEPT_SEQ.NEXTVAL, '기획', '목동')

-- 확인
SELECT *
FROM DEPT;

 

6. 분석 및 통계 함수

 

6.1) ROLLUP

  • 그룹 별로 집계를 하는데 중간 집계를 조회할 때 사용
  • ROLLUP에 설정하는 컬럼은 숫자 데이터는 안됩니다.

숫자 컬럼의 경우는 DECODE 함수로 감싸서 조회

 

-- DEPTNO 별로 SAL의 합계를 조회
-- 숫자 컬럼은 조회시 DECODE로 감싸야 함.
-- DECODE 값이 NULL이면 전체, 그렇지 않으면 DEPTNO를 변환해서 조회
SELECT DECODE(DEPTNO, NULL, '전체', DEPTNO) DEPTNO, SUM(SAL) 급여합계
FROM EMP
GROUP BY ROLLUP(DEPTNO);

SELECT DEPTNO, JOB, SUM(SAL) 급여합계
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB)
ORDER BY DEPTNO;


-- 전체 합계는 제외
SELECT DEPTNO, NVL(JOB, '합계'), SUM(SAL) 급여합계
FROM EMP
GROUP BY DEPTNO, ROLLUP(JOB)
ORDER BY DEPTNO;

 

 

6.2) CUBE

  • 가능한 모든 중간 집계를 조회
-- CUBE는 모든 중간 집계를 조회 (JOB별, 번호별)
SELECT DEPTNO, JOB, SUM(SAL) 급여합계
FROM EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY DEPTNO;

 

6.3) GROUPING

 

중간 집계이면 1을, 그렇지 않으면 0을 리턴해주는 함

-- GROUPING : 중간 집계이면 1 그렇지 않으면 0을 리턴
SELECT DEPTNO, DECODE(GROUPING(DEPTNO), 1,'전체 합계') AS ALLTOT, 
JOB, DECODE(GROUPING(JOB), 1, '부서 합계') AS DEPTTOT, SUM(SAL) 급여합계
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB)
ORDER BY DEPTNO;

 

6.4) GROUPING SETS

 

ROLLUP 이나 CUBE는 여러 개의 컬럼으로 그룹화하면 여러 컬럼을 합쳐서 그룹화하지만 GROPING SETS는 여러 개의 컬럼을 사용하면 각 컬럼 별 집계를 수행한다.

 

-- GROUPING SETS 는 개별 그룹화를 수행합니다.
SELECT DEPTNO, JOB, SUM(SAL) 급여합계
FROM EMP
GROUP BY GROUPING SETS(DEPTNO, JOB);

 

 

7. WINDOW FUNCTION

  • 행과 행 간의 관계를 정의하기 위해 제공되는 함수
  • 순위, 합계, 평균, 또는 누적 합이나 비율 등을 위한 함수

 

7.1) 기본 형식

 

SELECT WINDOW_FUCTION(매개변수)

OVER(PARTITION BY 그룹화할 컬럼 이름 ORDER BY WINDOWING)

FROM 테이블 이름;

 

  • WINDOW_FUNCTION : 윈도우 함수를 설정하면 되는데 순위, 집계 및 행 순서 등
  • 매개변수는 함수에 따라 다르게 적용
  • PARTITION BY는 생략하면 데이터 전체
  • WINDOWING : 정렬 기준

 

7.2)실습

 

EMP 테이블에서 전체 SAL에서 자신의 SAL의 비율

-- EMP 테이블에서 전체 SAL에서 자신의 SAL의 비율
-- SUM(SAL)의 카디널리티가 1임.
-- 이 구문은 SAL과 SUM(SAL)의 행의 개수가 달라서 에러 -> OVER() 사용
-- SUM SAL을 전부 복사하여 14개의 행으로 만들어 조회!
SELECT ENAME, SAL, SAL* 100/SUM(SAL) OVER() AS "급여 비율"
FROM EMP;

 

  • 범위를 설정하기 위해서 시작 위치와 종료 위치를 설정하는 것이 가능

ROWS BETWEEN 시작위치 AND 종료위치

RANGE BETWEEN 시작위치 AND 종료위치

 

  • 위치 지정 방법

start

                 UNBOUNDED PRCEDING : 처음부터

                 CURRENT ROW : 현재 행 부터

                 n PRECEDING : n 번째 행 앞부터

 

end

                  UNBOUNDED FOLLOWING : 마지막 까지

                  CURRENT ROW : 현재 행 까지

                  n FOLLOWING : n 번째 행 뒤까지

 

 

 

  • EMP 테이블에서 EMPNO, ENAME, SAL, 현재행까지의 SAL 합계를 조회
-- EMP 테이블에서 EMPNO, ENAME, SAL, 현재행까지의 SAL 합계를 조회
SELECT EMPNO, ENAME, SAL, SUM(SAL) OVER(ORDER BY SAL 
	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "현재 행까지의 급여 합계"
FROM EMP;

 

 

 

  • EMP 테이블에서 EMPNO, ENAME, SAL, 현재행부터 마지막 행까지의 SAL 합계를 조회
-- 현재 행부터 마지막 행까지의 SAL 합계를 조회
SELECT EMPNO, ENAME, SAL, SUM(SAL) OVER(ORDER BY SAL 
	ROWS BETWEEN CURRENT ROW 
	AND UNBOUNDED FOLLOWING) "전체 급여 합계"
FROM EMP;

 

  • OVER 안에 PARTITION BY 컬럼이름을 기재하면 그룹화해서 집계를 수행
  • 부서별 급여 평균
-- 부서별 급여 평균
SELECT EMPNO, ENAME, SAL, ROUND(AVG(SAL) OVER(PARTITION BY DEPTNO),2) "부서별 급여 합계"
FROM EMP;

 

 

  • 순위 구해주는 함수

ROW_NUMBER() : 동일한 순위가 존재하지 않는 일련번호 형태의 순위

DENSE_RANK() : 동일한 순위는 동일한 순위로 출력하고 다음 순위를 건너뛰지 않고 연속해서 순위를 부여

RANK() : 동일한 순위는 동일한 순위로 출력하고 다음 순위를 건너뛰고 부여

-- 부서별 급여 순위
SELECT ENAME, SAL,
	RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) "급여 순위",
	DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) "급여  순위",
	ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) "급여   순위"
FROM EMP;

 

 

 

  • 행 순서 관련 함수

FIRST_VALUE(컬럼 이름) : 그룹의 첫번째 행

LAST_VALUE(컬럼 이름) : 그룹의 마지막 행

LAG(컬럼 이름) : 이전 행

LEAD(컬럼 이름, 인덱스) : 인덱스 만큼 건너뛴 행

 

 

  • 비율 관련 함수

CUME_DIST() : 누적 분포 상의 비율, 0~ 1사이 값

PERCENT_RANK() : 시작 위치를 0으로 종료 위치를 1로 했을 때의 누적 비율

N_TILE(분할할 개수) : 분할한 후 위치를 조회 (N 등분)

RATIO_TO_REPROT() : 합계에 대한 컬럼 값의 백분율

 

  • PIVOT

SLECT 필드목록 FROM 테이블

PIVOT(집계함수 FOR 컬럼이름 IN (컬럼 값 나열)) "별명"

SELECT * FROM EMP
PIVOT(MAX(SAL) FOR DEPTNO IN (10, 20, 30));

 

 

현재 ORACLE을 강사님의 도움에 받아 활용하였다.

집에서 접속하는 방법

HOST : 1.220.201.108

PORT : 1521

SID : xe

USER : user01 ~ user30

PASSWD : user01 ~ user30

 

디비버 만 깔면 집이든 어디든 사용이 가능하다.

 

 

** MongoDB

 

JavaScript : 브라우저 내부에서 컴파일되서 html을 동적으로 동작시킬 수 있는 언어

이 용도로 사용할 수 있는 언어는 JavaScript 뿐!!!!

 

1. Docker 에 Mongo DB 설치

docker run --name mango -v ~/data:/data/db -d -p 27017:27017 mongo

 

2. Mongo DB JSON Document - Binary JSON

Mongo DB의 데이터 표현법 - JavaScript 객체 표현 방식으로 표현

1) 장점

  • 경량
  • 각 필드는 데이터 타입과 길이가 먼저 저장되기 때문에 복잡한 처리 과정없이 빠르게 저장이 가능
  • 기본 데이터 타입으로 C 언어의 Primitive 타입(데이터의 참조가 아닌 실제 값)을 사용하기 때문에 빠르게 인코딩 과 디코딩 가능

2) 다양한 자료형을 지원

  • Objectid : 데이터를 저장할 때 데이터를 구별하기 위해서 Mongo DB가 삽입하는 데이터의 자료형

Oracle에서의 ROWID

 

3) 데이터 표현

객체

{"Key":"Value, "Key":"Value...}

         Key는 중복될 수 없음

         Key의 순서도 기억한다.

         {"name":"adam","album":"genesis"}와 {"album":"genesis","name":"adam"}는 다른 데이터 (Key의 순서가 다르기 때문)

         Key에 공백을 포함할 수 없습니다.

         Key에 . 과 $ 포함할 수 없음

         Key를 _로 시작하지 않는 것이 관례(예약어가 _로 시작하기 때문)

         Key는 대소문자 구분

 

  • 배열

[데이터 나열]

 

  • Mongo DB에서는 하나의 데이터를 Document 라고 합니다.

 

  • Mongo DB는 데이터의 모임을 collection으로 관리하는데 스키마가 없기 때문에 하나의 collection에 어떤 종류의 데이터라도 삽입이 가능하다.

 

4) 작업단위

database > collection > document

 

5) 데이터베이스 작업

  • 데이터베이스 목록 확인

show dbs

 

  • 데이터 베이스 설정

use 데이터베이스 이름

Mongo DB는 없는 이름을 사용하면 생성해줌

 

  • 현재 사용 중인 데이터베이스 확인

db

 

  • 현재 사용 중인 데이터베이스 삭제

db.dropDatabase()

 

6) 데이터베이스 접속 방법

Mongo DB는 기본적으로 계정과 비밀번호가 없습니다.

 

  • Mongo DB가 설치된 셀에 접속해서 사용

터미널을 열고, bash 셀에 접속

               docker exec - it 컨테이너이름 bash

프롬프트가 #으로 끝나는 것을 확인한 후 bin 디렉토리로 접

  • 접속 프로그램 이용

7) 명령어 실습

데이터베이스 확인

show dbs

 

=> adam 이라는 데이터베이스가 없으면 생성하고 연결

use adam

 

현재 사용중인 데이터베이스 확인

db

 

데이터 베이스 확인 : adam이 보이지 않음 -> 데이터가 없으면 용량이 할당되지 않아 그렇게 보임

 

데이터를 삽입하고 확인

db.mycollection.insertIne({name:1})

 

8)collection

=> 도큐먼트의 집합

  • = Mongo DB 에서는 스키마를 만들지 않고 JOIN을 하지 않는 것을 기본으로 하기 때문에 하나의 Collection에 모든 종류의 데이터를 삽입하는 것이 가능하다.
  • 실무에서는 많은 양의 데이터를 collection에 저장하면 실행 속도가 느려지기 때문에 데이터를 분할 저장하고 하나의 collection에 여러 종류의 데이터가 있으면 어떤 종류의 데이터를 저장하는 collection인지 알기 어렵기 때문에 대부분 동일한 종류의 데이터를 하나의 collection에 삽입합니다.

관계형 데이터베이스는 테이블을 먼저 생성 - 스키마를 생성

회원 - 아이디, 이름, 비밀번호를 저장

회원 테이블에는 위의 정보를 갖는 인스턴스만 저장이 가능

 

NoSQL에서는 테이블을 생성하지 않음 - 스키마를 생성하지 않는다.

회원 - [ ]

 [{id :"1", name:"park",pw:"!1234"}, {boardnum:1, title :"제목", content:"내용"}] -> 이렇게도 가능은하되 하진 않음.

 

 

3. collection 작업

3.1) collection 생성

db.createCollection("이름")

 

3.2) collection 이름을 조회

db.getCollectionNames()

 

show collections

 

3.3) collection 제거

db.이름.drop()

 

3.4) 이름변경

db.이름.renameCollection(새로운이름)

 

 

3.5) Capped collection

=> collection의 용량을 정해두고 용량 이상의 데이터가 삽입이 되면 가장 오래된 데이터를 삭제하면서 삽입하는 collection

 

db.createCollection(이름, {capped:true, size: 크기})

 

동작 확인

#컬렉션 생성

db.createCollection('cappedCollection', {capped : true, size: 10000})

 

#데이터 1개 삽입

db.cappedCollection.insertOne({x:1})

 

#데이터 확인

db.cappedCollection.find()

 

#많은 양의 데이터 삽입

for(i=0; i<1000; i++){

              db.cappedCollection.insertOne({x:1})

}

 

#데이터 확인

db.cappedCollection.find()

 

데이터를 1000개를 삽입하면 647번 부터 데이터가 존재한다.

-> 용량이 초과되서 이전에 저장된 데이터를 삭제하기 때문.

한정된 공간을 사용하는 시스템(IoT와 Embedded System)에서는 새로운 데이터를 저장하기 위해서 과거의 데이터를 지우면서 저장합니다.

 

CRUD(Create - 삽입, Read - 읽기, Update - 수정, Delete - 삭제)

 

4. 도큐먼트 생성

1) 특징

하나의 도큐먼트는 하나의 collection에 삽입

데이터를 삽입할 때 _id라는 속성에 값을 설정하지 않으면 Mongo DB가 _id 라는 속성에 값을 대입합니다.

이 값이 기본 키 값입니다.

 

2) 삽입 함수

db.컬렉션이름.insert(객체) : 동일한 _id를 사용하면 에러를 발생

db.컬렉션이름.save(객체) : 동일한 _id를 사용하면 수

 

insertOne(객체)

insertMany(객체)

 

  • 초창기에는 insert 함수를 이용해서 1개 또는 여러 개의 데이터를 삽입했는데 최근에는 insert 는 deprecated
  • =insertOne 과 insertMany를 사용하는 것을 권장합니다.
  • 최근의 데이터베이스들은 여러 개의 데이터를 삽입할 때 중간에 오류가 발생하더라도 다음 작업을 계속 진행할 수 있는 옵션을 제공합니다.

 

Batch Processing : 일괄처

 

여러분 드디어 학생들만 있는 단톡방을 만들었습니다!

앞으로 저희끼리 있을 의견 나눔이나 조율에 유용하게 쓰일거 같아 만들게 되었어용

직원분들이 없으니 눈치볼 필요도 없습니다

조만간 저희끼리 술자리도 주관해보겠습니다

반응형
LIST

'LG 헬로비전 DX DATA SCHOOL > DATA BASE' 카테고리의 다른 글

데이터 베이스  (0) 2023.07.25
Mongo DB  (0) 2023.07.24
파이썬 MySQL 연동  (0) 2023.07.20
DATA BASE 4  (0) 2023.07.20
Data Base 3  (0) 2023.07.19

관련글

티스토리툴바