국비지원학원 3, 4일 차 정리 / ORACLE

SELECT 문의 실행 순서

SELECT DEPTNO, AVG(SAL)
FROM EMP
WHERE SAL > 1000
GROUP BY DEPTNO
HAVING AVG(SAL) > 1700
ORDER BY DEPTNO; 의 실행 순서

FROM EMP -- 첫 번째, EMP 테이블을 불러옴
WHERE SAL > 1000 -- 두 번째, SAL column이 조건에 맞는 지 확인
GROUP BY  DEPTNO -- 세 번째, DEPTNO 기준으로 그룹화
HAVING AVG(SAL) > 2000 -- 네 번째, 완료된 그룹화의 SAL column 을 기준으로 조건 확인
SELECT DEPTNO, AVG(SAL) -- 다섯 번째, 지정한 column만 출력
ORDER BY -- 마지막 실행, 지정한 조건을 기준으로 정렬

 

ROLLUP 함수 : GROUP BY 명령어 이후 사용, 지정한 그룹 별 소계와 총계를 보여줌

SELECT DEPTNO, JOB, COUNT( * ), MAX(SAL), SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB); 
-- DEPTNO 를 기준으로 그룹화 하여 DEPTNO 별 소계와 총계를 보여줌

 

SELECT DEPTNO, JOB, COUNT( * ), MAX(SAL), SUM(SAL)
FROM EMP
GROUP BY DEPTNO, ROLLUP(JOB); 
-- DEPTNO 기준으로 그룹화, 각 DEPTNO 별 소계만 출력

 

SELECT DEPTNO, JOB, COUNT( * ), MAX(SAL), SUM(SAL) 
FROM EMP
GROUP BY JOB, ROLLUP(DEPTNO); 
-- JOB 별 그룹화, 각 JOB 별 소계만 출력

 

CUBE 함수 : ROLLUP 과 같이 GROUP BY 함수와 같이 사용, 그룹화 이후 각 column 별 소계와 총계 모두 출력

SELECT DEPTNO, JOB, COUNT( * ), MAX(SAL), SUM(SAL) 
FROM EMP
GROUP BY CUBE(DEPTNO, JOB); 
-- DEPTNO 를 기준으로 그룹화, 총계가 가장 위, 이후 JOB 별 소계, DEPTNO 별 소계 출력

 

TABLE 여러 개 호출하기 / JOIN

SELECT * FROM EMP, DEPT; 
-- EMP 와 DEPT 를 한 테이블에 출력. EMP 의 행 갯수와 DEPT 행 갯수만큼 곱해져서 나옴

 

SELECT * 
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO; 
-- EMP 의 DEPTNO 와 DEPT 의 DEPTNO 가 같은 행만 출력

 

SELECT E.EMPNO, D.DNAME 
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO; 
-- 테이블 이름도 별칭 설정 가능,이름이 겹치는 column 이 있을 경우 출력하고 싶은 테이블을 지정해줘야 함. 
-- 테이블은 AS 명령어를 사용하지 않음 (Oracle 기준)

 

테이블을 호출할 때에는 * 처럼 모든 column을 출력하는 것도 좋지만, 혹시 모를 오류 감지를 위해 원하는 column 이름을 지정해 주는 것이 좋음

 

SELECT * 
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL; 
-- EMP 의 SAL 값이 SALGRADE 의 LOSAL 과 HISAL 사이에 포함된 행만 출력

 

CREATE TABLE [생성할 테이블 이름] 
AS SELECT * FROM [복사할 테이블 이름]; 
-- FROM 이후 테이블을 복사하여 새로운 테이블로 생성

 

SELECT M.EMPNO, M.ENAME, E.ENAME, E.EMPNO
FROM EMP E, EMP M
WHERE E.EMPNO = M.MGR; 
-- EMP 테이블의 EMPNO 값과 MGR 값을 비교하여 해당하는 행을 출력
-- 같은 테이블을 여러 번 호출하여 비교 가능

 

오른쪽 외부 조인

SELECT E.EMPNO, E.ENAME, E.MGR, S.EMPNO, S.ENAME 
FROM EMP E, EMP S
WHERE E.MGR(+) = S.EMPNO; 
-- MGR 값과 EMPNO 값이 같은 행만 출력하였으나 호출되지 않은 EMP 테이블의 모든 EMPNO 값에 NULL 값을 넣어 출력
-- 잘 사용하진 않음

 

왼쪽 외부 조인

SELECT E.EMPNO, E.ENAME, E.MGR, S.EMPNO, S.ENAME 
FROM EMP E, EMP S
WHERE E.MGR = S.EMPNO(+); 
-- 모든 MGR 값을 호출하여 NULL 값을 넣어 출력

 

서브 쿼리 : 쿼리 문 안의 쿼리 문, 쿼리 문의 결과 값을 해당 식의 인자 값으로 사용할 수 있음

SELECT ENAME, SAL
FROM EMP
WHERE SAL > (SELECT SAL FROM EMP
            WHERE ENAME = 'JONES'); 
-- JONES 의 SAL 값 보다 큰 SAL 값을 가진 EMP 테이블의 ENAME, SAL 값 출력

 

SELECT ENAME
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM DEPT
                WHERE LOC = 'DALLAS'); 
-- DEPT 테이블의 LOC 값이 DALLAS 인 DEPTNO 값과 EMP 테이블의 DEPTNO 값이 같은 ENAME 출력

 

조인과 서브 쿼리는 같은 결과 값을 출력할 수 있으나 조인은 큰 테이블을 먼저 만들고 이후 조건에 따라 값을 출력하는 반면

서브 쿼리는 각 쿼리 별 결과를 확인 후 상위 쿼리로 올라가는 방식, 데이터가 많을 경우 서브 쿼리가 더 유용한 경우가 있음

SELECT ENAME 
FROM EMP
WHERE SAL > (SELECT AVG(SAL) FROM EMP) AND DEPTNO = 20; 
-- EMP 테이블의 평균 SAL 값보다 큰 값을 가지고 DEPTNO 값이 20을 가지고 있는 행의 ENAME 출력

 

결과 값이 여러 개인 서브 쿼리를 이용한 명령문

SELECT ENAME 
FROM EMP
WHERE SAL IN (SELECT MAX(SAL) 
    FROM EMP
    GROUP BY DEPTNO); 
-- IN 함수를 이용하여 DEPTNO 로 GROUP BY 한 값을 인자로 받는 조건식
-- DEPTNO 별 가장 큰 SAL 값을 가진 ENAME 출력, IN 함수를 이용하여 결과 값이 여러 개 인 경우도 사용 가능

 

 ANY 함수

SELECT * 
FROM EMP
WHERE SAL < ANY (SELECT SAL FROM EMP
                 WHERE DEPTNO = 30); 
-- DEPTNO 가 30 인 SAL 값들 중 어느 것보다 하나라도 작으면 true를 반환하여 값 출력
-- 모든 값 중 하나라도 작으면 값을 출력하기 때문에 SAL 값 중 최댓값보다 작은 값 출력하는 조건식과 같음
SOME 도 ANY 와 같은 역할을 함

 

ALL 함수

SELECT * 
FROM EMP
WHERE SAL < ALL (SELECT SAL FROM EMP
                 WHERE DEPTNO = 30); 
-- DEPTNO 가 30 인 SAL 값 들 모두보다 작아야 true를 반환하기 때문에 출력되는 SAL 값 중 최솟값 보다 작은 SAL 을 가진 행을 출력
-- 비교 연산자의 방향에 따라 최솟값보다 작은, 최댓값보다 큰 조건을 걸 수 있음

 

EXISTS 함수

SELECT * 
FROM EMP
WHERE EXISTS (SELECT DNAME FROM DEPT
              WHERE DEPTNO = 10); 
-- 서브 쿼리문의 출력 값이 하나라도 있으면 true를 반환하여 SELECT 문 실행
-- 출력 값이 하나도 없으면 false를 반환하여 쿼리 실행 X

 

다중 열 서브 쿼리

SELECT * 
FROM EMP
WHERE (DEPTNO, SAL) 
   IN (SELECT DEPTNO, MAX(SAL) FROM EMP
       GROUP BY DEPTNO); 
-- 서브 쿼리로 DEPTNO, MAX(SAL) 값을 인자로 받는 조건식
-- EMP 테이블 중 조건에 맞는 행만 출력, IN 연산자 사용

 

FROM 절 서브 쿼리

SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME, D.LOC
 FROM (SELECT * FROM EMP WHERE DEPTNO=10) E,
      (SELECT * FROM DEPT) D
WHERE E.DEPTNO = D.DEPTNO; 
-- FROM 에도 서브 쿼리를 사용하여 원하는 값을 가진 테이블만 사용할 수 있음
-- SELECT 와 WHERE 에 들어가는 값은 서브 쿼리 안에 들어가있는 값이어야만 함

 

SELECT 절 서브 쿼리

SELECT E.EMPNO, E.JOB, E.ENAME, D.DNAME
,(SELECT GRADE FROM SALGRADE 
  WHERE E.SAL BETWEEN LOSAL AND HISAL) AS SALGRADE
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND E.SAL > (SELECT AVG(SAL) FROM EMP);
-- SALGRADE 테이블의 GRADE 값을 EMP 테이블의 SAL 값에 따라 LOSAL 과 HISAL 사이 값에 있는 행 출력
-- 조건은 SAL 의 평균보다 큰 SAL 값만 출력

 

데이터 조작 및 정의

SQL, DML, DDL 명령어가 있음

 

데이터 추가 INSERT : INSERT INTO 명령어를 통해 테이블에 값 추가 가능

INSERT INTO DEPT_TEMP
VALUES (50, 'DATABASE', 'SEOUL'); 
-- DEPT_TEMP 테이블에 VALUES 값 추가, 테이블 이름 다음에 값을 넣을 column을 지정하여 원하는 값만 넣을 수 있음
-- 값을 넣고 싶지 않을 경우 NULL 값을 직접 작성하여 추가하는 편이 좋음

INSERT INTO DEPT_TEMP (DEPTNO, DNAME, LOC)
VALUES (70, 'WEB', NULL);

 

서브 쿼리를 사용하여 값을 넣을 수 있으나, VALUES 를 적지 않음

INSERT INTO DEPT_TEMP (DEPTNO)
  (SELECT DEPTNO
  FROM EMP
  WHERE ENAME = 'SCOTT');

 

데이터 수정 UPDATE : UPDATE 명령어를 통해 테이블 값 수정 가능

UPDATE DEPT_TEMP
SET DNAME = 'DATABASE',
LOC = 'SEOUL'
WHERE DEPTNO = 30; 
-- DEPT_TEMP 테이블의 DEPTNO 가 30인 행의 DNAME 과 LOC 값을 수정

 

UPDATE DEPT_TEMP
SET (DNAME, LOC) =
  (SELECT DNAME, LOC FROM DEPT_TEMP
  WHERE DEPTNO = 20); 
-- 서브 쿼리문의 출력값이 여러 개일 경우 SET 이후에 해당 갯수를 일치시켜 값 수정 가능

 

데이터 삭제 DELETE : DELETE 명령어를 통해 테이블 값 삭제 가능, 삭제 후 저장 시 복구가 어려워 신중해야 함

DELETE FROM EMP WHERE DEPTNO = 30; -- DEPTNO 가 30 인 EMP 테이블의 값 삭제

테이블 생성 CREATE : CREATE TABLE 명령어를 통해 테이블 생성 가능

CREATE TABLE DEPT_TEMP 
AS SELECT * FROM DEPT
WHERE 1=2;
-- DEPT 테이블의 형태는 유지하며 테이블의 내용은 가져오고 싶지 않을 때 사용
-- WHERE 조건이 아무것도 일치하지 않으면 됨
ex) 1=2, 1<>1, 1!=1 .. 등

 

각 column 별 타입 지정하여 테이블 생성하기

CREATE TABLE EMP_DDL(
EMPNO NUMBER(4)
, ENAME VARCHAR2(10)
, JOB VARCHAR2(9)
, MGR NUMBER(4)
, HIREDATE DATE
, SAL NUMBER(7,2)
, COMM NUMBER(7,2)
, DEPTNO NUMBER(2)
); -- CREATE 명령어를 사용하며 각 column 별 데이터 타입을 지정할 수 있음,

NUMBER(4) - 숫자 자료형으로 최대 4자리까지 작성 가능
NUMBER(7,2) - 숫자 자료형으로 소숫점 2자리를 포함한 최대 7자리까지 작성 가능 ex) 12345.67
VARCHAR2(10) - 가변형 문자형식, 최대 10글자까지 작성 가능 (영어 기준, 한글은 다를 수 있음)
DATE - 날짜 타입

 

테이블 변경 ALTER : ALTER 명령어를 먼저 입력하고 수정 할 테이블 지정

ALTER TABLE EMP_DDL
DROP COLUMN DEPTNO; -- DROP 명령어를 이용하여 DEPTNO column 삭제

 

MODIFY : column 의 데이터 타입 변경

ALTER TABLE EMP_DDL
MODIFY EMPNO NUMBER(5); 
-- 사원이 많아져 사원 번호가 5자리의 숫자가 필요할 경우 MODIFY 명령어 사용
기존 숫자 4자리에서 5자리까지 사용 가능하도록 수정

 

ADD : column 을 추가하는 명령어, 데이터 타입도 같이 지정

ALTER TABLE DEPT_TEMP
ADD HP VARCHAR2(20); 
-- ALTER 명령어를 이용하여 column 추가, DEPT_TEMP 테이블에 VARCHAR2(20) 형식을 갖는 HP column 추가
이미 데이터가 많이 들어있는 테이블에 열을 추가하는 행동은 신중해야 하는 행동
테이블을 만들 때부터 추가할 일 없게 만드는 것이 가장 좋음

 

RENAME : RENAME A TO B 명령어를 통해 A 이름을 B로 변경 가능

ALTER TABLE DEPT_TEMP
RENAME HP TO TEL;
-- ALTER 명령어를 통해 변경하려는 테이블을 선택하여 column HP 의 이름을 TEL 로 변경

 

테이블 이름 변경

RENAME DEPT_TEMP TO DEPT_RENAME;
-- RENAME 명령어를 통해 DEPT_TEMP 테이블의 이름을 DEPT_RENAME 으로 변경

TRUNCATE : TRUNCATE 명령어를 통해 테이블의 데이터를 삭제 가능

TRUNCATE TABLE [테이블 이름]
TRUNCATE TABLE DEPT_TEMP; -- DEPT_TEMP 테이블의 내용 전체 삭제

DROP : DROP TABLE 명령어를 통해 테이블 삭제 가능

DROP TABLE [테이블 이름]
DROP TABLE DEPT_TEMP; -- DEPT_TEMP 테이블 삭제

트랜잭션 제어 COMMIT ROLLBACK 

COMMIT : COMMIT 명령어를 통해 수정한 데이터베이스를 저장할 수 있음

ROLLBACK : ROLLBACK 명령어를 통해 이전 COMMIT 이후로 수정한 내역 되돌리기 가능, 전체 다 되돌리기되어 사용에 신중

COMMIT; -- 데이터베이스의 변경 사항 저장
ROLLBACK; -- 이전 COMMIT 이후 변경된 사항들 전체 복원

 

트랜잭션
더 이상 분할할 수 없는 최소 수행 단위, 명령어를 수행 후 마지막으로 COMMIT 혹은 오류 발생 시 ROLLBACK 명령어를 실행해야 하나의 트랜잭션이 종료되었다고 봄

 

세션
데이터베이스 접속을 시작으로 작업을 수행한 후 접속을 종료하기까지 전체 기간을 의미
서버에 저장되어 있는 정보를 세션, 로컬에 저장되어 있는 정보를 쿠키라고 함

 

CMD 화면과 Developer 화면을 동시에 같은 테이블을 수정하려고 하였을 때 한쪽에서 COMMIT 이 들어가야 다른 세션에서 수정이 가능 - 데이터의 일관성을 유지하는 기능

 

뷰
가상 테이블로 불리는 뷰는 하나 이상의 테이블을 조회하는 SELECT 문을 저장한 객체.
SELECT 문을 저장하기 때문에 물리적 데이터를 따로 저장하지는 않음.
주로 사용하는 column 만 모아서 뷰로 만들 수 있음. 함수처럼 명령어 압축이 가능

EMP_VIEW 에 EMP 테이블의 EMPNO, ENAME, DEPTNO 항목만 골라 저장할 수 있음
SELECT * FROM EMP_VIEW 명령어 실행 시 EMP 테이블의 지정되어 있는 column만 접근 가능
보안 유지 및 관리에 중요한 역할

 

뷰를 생성하기 위해서는 먼저 뷰 생성 권한이 필요, SYSTEM 계정에 접속하여 SCOTT 계정에 뷰 생성 권한 부여

GRANT CREATE VIEW TO SCOTT;

 

CREATE VIEW VW_EMP20
AS (SELECT EMPNO, ENAME, JOB, DEPTNO FROM EMP);
-- 명령어를 통하여 EMP 테이블의 EMPNO, ENAME, JOB, DEPTNO 가 들어있는 column만 VW_EMP20 이라는 이름의 뷰로 생성

 

생성된 뷰는 기본 테이블 보는것과 같은 명령어를 사용하여 출력할 수 있음

SELECT * FROM VW_EMP20;