22.05.11-12

오혜원·2022년 5월 11일

SQL

목록 보기

2/7

Oracle 기본 함수

: 문자, 수, 날짜

▶ 문자
: UPPER - 모두 대문자
: LOWER - 모두 소문자
: INITCAP - 첫 문자만 대문자
: LENGTH - 문자열의 길이를 정수
: LENGTHB - 문자열의 길이를 byte 단위 (비영어권 문자 : LENGTH * 3)
→ 비영어권 문자는 oracle의 기본 세팅이 3byte
: SUBSTR('STR', A, B) - 문자열을 A부터 B개 자르기 (INDEX는 1부터)
: SUBSTRB - 문자열을 byte 단위로 A부터 B개 자르기
→ 비영어권 문자는 시작 INDEX부터 3의 배수 단위로 잘라야 문자 출력
→ 시작 INDEX가 비영어권 문자의 중간이라면 공백 출력
: INSTR - 문자열 위치 찾기
: INSTR('STR', 'SUB', A, B) - STR에서 SUB부분을 INDEX A부터 B번째로 나오는 지점 찾기
: INSTRB - 문자열을 byte 단위로 위치 찾기
: LPAD('STR', A, 'B') - A만큼의 공간에 STR의 왼쪽을 지정한 문자로 채우기
: RPAD('STR', A, 'B') - A만큼의 공간에 STR의 오른쪽을 지정한 문자로 채우기
: TO_CHAR(SYSDATE, 'FORMAT') - 날짜를 문자 FORMAT으로 변경
→ FORMAT : YYYY/YY(년) MM(월) DD(일) DAY(요일) DY(요일 첫글자) D(요일숫자) AM/PM(오전, 오후) HH(0~12시간) HH24(0~24시간) MI(분) SS(초)
→ D(요일 숫자) : 1-일 2-월 3-화 4-수 5-목 6-금 7-토
: TO_CHAR(A, 'FORMAT') - 숫자 A를 문자 FORMAT으로 변경
→ FORMAT : 0과 9로 , 혹은 소수점 이하 자리를 표현 (0은 맨 첫자리의 0 또는 소수점 끝의 0까지 표현, 9는 앞선 0 표현 X)
→ FORMAT : L을 붙일 경우 SQL이 실행되는 국가(Local)의 화폐 단위

SELECT UPPER('hello') FROM DUAL;
SELECT LOWER('WORLD') FROM DUAL;
SELECT INITCAP('nice TO meet YOU') FROM DUAL;

SELECT LENGTH('오라클') FROM DUAL;
SELECT LENGTHB('안녕하세요') FROM DUAL;

SELECT SUBSTR('NICE TO MEET YOU', 1, 10) FROM DUAL;
SELECT SUBSTRB('NICE TO MEET YOU', 1, 4) FROM DUAL;
SELECT SUBSTR('안녕하세요', 1, 3) FROM DUAL;
SELECT SUBSTRB('안녕하세요', 4, 6) FROM DUAL;
SELECT SUBSTRB('안녕하세요', 3, 7) FROM DUAL;

SELECT INSTR('NICE TO MEET YOU', 'TO') FROM DUAL;
SELECT INSTRB('NICE TO MEET YOU', 'TO') FROM DUAL;
SELECT INSTR('NICE TO MEET YOU', 'E', 1, 3) FROM DUAL;
SELECT INSTR('안녕하세요', '하') FROM DUAL;
SELECT INSTRB('안녕하세요', '하') FROM DUAL;

SELECT LPAD('HI', 10, '#') FROM DUAL;
SELECT RPAD('HI', 10, '-') FROM DUAL;

SELECT SYSDATE FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'DAY PM HH:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'DY AM HH24:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'D AM HH:MI:SS') FROM DUAL;

SELECT 0123456789 FROM DUAL;
SELECT TO_CHAR(0123456789, '0,000,000,000L') FROM DUAL;
SELECT TO_CHAR(0123456789, '9,999,999,999L') FROM DUAL;
SELECT 0.123000 FROM DUAL;
SELECT TO_CHAR(0.123000, '0.000000') FROM DUAL;
SELECT TO_CHAR(0.123000, '9.999999') FROM DUAL;

출력 결과

UPPER
-----
HELLO

LOWER
-----
world

INITCAP('NICETOMEETYOU')
-----------------------
Nice To Meet You

LENGTH('오라클')
-------------
            3

LENGTHB('안녕하세요')
----------------
              15

SUBSTR('NICETOMEETYOU',1,10)
---------------------------
NICE TO ME

SUBSTR('NICETOMEETYOU',1,4)
--------------------------
NICE

SUBSTR('안녕하세요',1,3)
--------------------
안녕하

SUBSTRB('안녕하세요',4,6)
---------------------
녕하

SUBSTRB('안녕하세요,3,7)
--------------------
 녕하

INSTR('NICETOMEETYOU','TO')
---------------------------
                          6

INSTRB('NICETOMEETYOU','TO')
----------------------------
                           6

INSTR('NICETOMEETYOU', 'E', 1, 3)
--------------------------------
                              11

INSTR('안녕하세요','하')
------------------
                 3

INSTRB('안녕하세요','하')
-------------------
                  7

LPAD('HI',10,'#')
----------------
########HI

RPAD('HI',10,'-')
----------------
HI--------

SYSDATE 
--------
22/05/12

TO_CHAR(SYSDATE,'YYYY-MM-DD')
----------------------------
2022-05-12

TO_CHAR(SYSDATE,'DAYPMHH:MI:SS')
-------------------------------
목요일 오전 10:26:08

TO_CHAR(SYSDATE,'DYAMHH24:MI:SS')
--------------------------------
목 오전 10:26:08

TO_CHAR(SYSDATE,'DAMHH:MI:SS')
-----------------------------
5 오전 10:26:08

0123456789
----------
 123456789

TO_CHAR(0123456789,'0,000,000,000L')
-----------------------------------
                    0,123,456,789￦

TO_CHAR(0123456789,'9,999,999,999L')
-----------------------------------
                     123,456,789￦

  0.123000
----------
      .123

TO_CHAR(0.123000, '0.000000')
----------------------------
                    0.123000

TO_CHAR(0.123000, '9.999999')
----------------------------
                     .123000

▶ 숫자
: CEIL - 올림
: FLOOR - 내림
: ROUND - 반올림
: MOD(A, B) - A % B
: TRUNC(A, B) - 소수점 B번째까지 A 출력 (B가 없을 시 정수 부분만 출력)
: TO_NUMBER('STR') - 문자를 숫자로 변경

SELECT CEIL(0.1) FROM DUAL;
SELECT FLOOR(0.9) FROM DUAL;
SELECT ROUND(0.4) FROM DUAL;
SELECT ROUND(0.5) FROM DUAL;
SELECT MOD(49, 5) FROM DUAL;
SELECT TRUNC(2.632) FROM DUAL;
SELECT TRUNC(1.123456789, 3) FROM DUAL;
SELECT TRUNC(1.123456789, 7) FROM DUAL;

출력 결과

 CEIL(0.1)
----------
         1

FLOOR(0.9)
----------
         0

ROUND(0.4)
----------
         0

ROUND(0.5)
----------
         1

  MOD(49,5)
----------
         4

TRUNC(2.632)
------------
           2

TRUNC(1.123456789,3)
--------------------
               1.123

TRUNC(1.123456789,7)
--------------------
           1.1234567

123+123
---------------------
                 246

'123'+'1234'
------------
        1357

TO_NUMBER('123')+TO_NUMBER('5432')
----------------------------------
                              5555

▶ 날짜
: SYSDATE - YY/MM/DD
: TO_DATE('STR') - 문자를 날짜로 변경
: MONTHS_BETWEEN(A, B) - 두 날짜 간의 기간을 월로 계산, A-B로 계산
: ADD_MONTHS(A, B) - A의 날짜에 B개월 수를 더한 날짜를 계산
: NEXT_DAY(A, B) - A 날짜를 기준으로 돌아오는 다음 B요일
: LAST_DAY - 기준이 되는 달의 마지막 날짜
: ROUND(A, B) - A 날짜를 B를 기준으로 반올림, 월 기준일 경우 1~15는 내림, 16~31은 올림 (각 달의 날짜와 무관)
: TRUNC(A, B) - A 날짜를 B를 기준으로 자르기

SELECT SYSDATE FROM DUAL;

SELECT '20220512' FROM DUAL;
SELECT TO_DATE('20220512') FROM DUAL;

SELECT MONTHS_BETWEEN(TO_DATE('20220927'), SYSDATE) FROM DUAL;
SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL;
SELECT NEXT_DAY(SYSDATE, 3) FROM DUAL;
SELECT LAST_DAY(SYSDATE) FROM DUAL;
SELECT LAST_DAY('20220203') FROM DUAL;

SELECT ROUND(TO_DATE('20220630'), 'YYYY') FROM DUAL;
SELECT ROUND(TO_DATE('20220701'), 'YYYY') FROM DUAL;
SELECT ROUND(TO_DATE('20220215'), 'MM') FROM DUAL;

SELECT TRUNC(SYSDATE, 'YYYY') FROM DUAL;
SELECT TRUNC(TO_DATE('20220531'), 'MM') FROM DUAL;

출력 결과

SYSDATE
--------
22/05/11

'20220512'
----------
20220512

TO_DATE('20220512')
------------------
22/05/12

MONTHS_BETWEEN(TO_DATE('20220927'),SYSDATE)
-------------------------------------------
                                 4.46932945

ADD_MONTH(SYSDATE, 3)
--------------------
22/08/12

NEXT_DAY(SYSDATE, 3)
-------------------
22/05/17

LAST_DAY(SYSDATE)
----------------
22/05/31

LAST_DAY('20220203')
-------------------
22/02/28

ROUND(TO_DATE('20220630'),'YYYY')
--------------------------------
22/01/01

ROUND(TO_DATE('20220701'),'YYYY')
--------------------------------
23/01/01

ROUND(TO_DATE('20220215'),'MM')
------------------------------
22/02/01

TRUNC(SYSDATE,'YYYY')
--------------------
22/01/01

TRUNC(TO_DATE('20220531'),'MM')
------------------------------
22/05/01

Oracle 제어문

▶ 테이블
: NVL(COLUMN, VALUE) - COLUMN이 NULL이면 대체값 VALUE를 적용, NULL이 아니면 입력값 적용
: NV2(COLUMN, VALUE1, VALUE2) - COLUMN이 NULL이면 대체값 VALUE2, NULL이 아니면 대체값 VALUE1 적용
→ ※ 주의! 대체값은 COLUMN의 data type과 동일
→ if ~ else 문과 유사

SELECT ENAME, COMM, NVL(COMM, 0) FROM EMP;
SELECT ENAME, COMM, NVL2(COMM, COMM * 12, 0) FROM EMP;
SELECT ENAME, JOB, NVL2(JOB, '직무있음', '직무없음') FROM EMP;
SELECT ENAME, HIREDATE, NVL2(HIREDATE, HIREDATE, SYSDATE) FROM EMP;

출력 결과

ENAME            COMM NVL(COMM,0)
---------- ---------- -----------
SMITH                           0
ALLEN             300         300
WARD              500         500
JONES                           0
MARTIN           1400        1400
BLAKE                           0
CLARK                           0
SCOTT                           0
KING                            0
TURNER                          0
ADAMS                           0
JAMES                           0
FORD                            0
MILLER                          0

ENAME            COMM NVL2(COMM,COMM*12,0)
---------- ---------- --------------------
SMITH                                    0
ALLEN             300                 3600
WARD              500                 6000
JONES                                    0
MARTIN           1400                16800
BLAKE                                    0
CLARK                                    0
SCOTT                                    0
KING                                     0
TURNER                                   0
ADAMS                                    0
JAMES                                    0
FORD                                     0
MILLER                                   0

ENAME      JOB       NVL2
---------- --------- ----
SMITH      CLERK     직무있음
ALLEN      SALESMAN  직무있음
WARD       SALESMAN  직무있음
JONES      MANAGER   직무있음
MARTIN     SALESMAN  직무있음
BLAKE      MANAGER   직무있음
CLARK      MANAGER   직무있음
SCOTT      ANALYST   직무있음
KING       PRESIDENT 직무있음
TURNER     SALESMAN  직무있음
ADAMS      CLERK     직무있음
JAMES      CLERK     직무있음
FORD       ANALYST   직무있음
MILLER     CLERK     직무있음

ENAME      HIREDATE NVL2(HIR
---------- -------- --------
SMITH      80/12/17 80/12/17
ALLEN      81/02/20 81/02/20
WARD       81/02/22 81/02/22
JONES      81/04/02 81/04/02
MARTIN     81/09/28 81/09/28
BLAKE      81/05/01 81/05/01
CLARK      81/06/09 81/06/09
SCOTT      82/12/09 82/12/09
KING       81/11/17 81/11/17
TURNER     81/09/08 81/09/08
ADAMS      83/01/12 83/01/12
JAMES      81/12/03 81/12/03
FORD       81/12/03 81/12/03
MILLER     82/01/23 82/01/23

: DECODE(COLUMN, VALUE 1, VALUE 2) : COLUMN의 값이 VALUE 1일 때, VALUE 2로 적용 / 나머지는 모두 NULL
: DECODE(COLUMN, VALUE 1, VALUE 2, VALUE 3) : COLUMN의 값이 VALUE 1일 때, VALUE 2로 적용 / 나머지는 모두 VALUE 3
: DECODE(COLUMN, VALUE 1, VALUE 2, VALUE 3, VALUE 4) : COLUMN의 값이 VALUE 1일 때, VALUE 2로 적용 / COLUMN의 값이 VALUE 3일 때, VALUE 4로 적용 / 나머지는 모두 NULL
→ 이러한 방식으로 변경 가능
→ if ~ else if ~ else 문과 유사

SELECT DEPTNO, DECODE(DEPTNO, 10, '회계') FROM EMP;
SELECT DEPTNO, DECODE(DEPTNO, 10, '회계', '--') FROM EMP;
SELECT DEPTNO, DECODE(DEPTNO, 10, '회계', 20, '기획') FROM EMP;
SELECT DEPTNO, DECODE(DEPTNO, 10, '회계', 20, '기획', '--') FROM EMP;
SELECT DEPTNO, DECODE(DEPTNO, 10, '회계', 20, '기획', 30, '영업') FROM EMP;

출력 결과

    DEPTNO DECODE
---------- ------
        20 NULL  
        30 NULL  
        30 NULL  
        20 NULL  
        30 NULL  
        30 NULL  
        10 회계
        20 NULL  
        10 회계
        30 NULL  
        20 NULL
        30 NULL  
        20 NULL  
        10 회계

    DEPTNO DECODE
---------- ------
        20 --
        30 --
        30 --
        20 --
        30 --
        30 --
        10 회계
        20 --
        10 회계
        30 --
        20 --
        30 --
        20 --
        10 회계

    DEPTNO DECODE
---------- ------
        20 기획
        30 NULL  
        30 NULL  
        20 기획
        30 NULL  
        30 NULL  
        10 회계
        20 기획
        10 회계
        30 NULL  
        20 기획
        30 NULL  
        20 기획
        10 회계

    DEPTNO DECODE
---------- ------
        20 기획
        30 --
        30 --
        20 기획
        30 --
        30 --
        10 회계
        20 기획
        10 회계
        30 --
        20 기획
        30 --
        20 기획
        10 회계

    DEPTNO DECODE
---------- ------
        20 기획
        30 영업
        30 영업
        20 기획
        30 영업
        30 영업
        10 회계
        20 기획
        10 회계
        30 영업
        20 기획
        30 영업
        20 기획
        10 회계

: CASE WHEN COLUMN = VALUE 1 THEN VALUE 2 ELSE VALUE 3 END
COLUMN의 값이 VALUE 1일 때, VALUE 2로 변경, 이외의 경우에는 VALUE 3으로 변경
→ switch case: default 문과 유사

SELECT DEPTNO, CASE WHEN DEPTNO = 10 THEN '회계'
                    WHEN DEPTNO = 20 THEN '기획'
                    WHEN DEPTNO = 30 THEN '영업'
                    ELSE '--' END FROM EMP;

출력 결과

    DEPTNO CASE
---------- ----
        20 기획
        30 영업
        30 영업
        20 기획
        30 영업
        30 영업
        10 회계
        20 기획
        10 회계
        30 영업
        20 기획
        30 영업
        20 기획
        10 회계

▷ QUIZ
EMP의 모든 속성을 출력하되 JOB이 'CLERK', 'ANALYST'일 때, SAL을 5%, JOB이 'SALESMAN', 'MANAGER'일 때, SAL을 10% 인상한 값으로 출력하라.

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO,
         CASE WHEN JOB = 'CLERK' THEN SAL * 1.05
              WHEN JOB = 'ANALYST' THEN SAL * 1.05
              WHEN JOB = 'SALESMAN' THEN SAL * 1.1
              WHEN JOB = 'MANAGER' THEN SAL * 1.1
              ELSE SAL END FROM EMP;
              
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO,
       DECODE(JOB, 'CLERK', SAL * 1.05, 'ANALYST', SAL * 1.05, 'SALESMAN', SAL * 1.1, 'MANAGER', SAL * 1.1, SAL) FROM EMP;

출력 결과

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO     연봉협상
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 80/12/17        800                    20        840
      7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30       1760
      7521 WARD       SALESMAN        7698 81/02/22       1250        500         30       1375
      7566 JONES      MANAGER         7839 81/04/02       2975                    20     3272.5
      7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30       1375
      7698 BLAKE      MANAGER         7839 81/05/01       2850                    30       3135
      7782 CLARK      MANAGER         7839 81/06/09       2450                    10       2695
      7788 SCOTT      ANALYST         7566 82/12/09       3000                    20       3150
      7839 KING       PRESIDENT            81/11/17       5000                    10       5000
      7844 TURNER     SALESMAN        7698 81/09/08       1500                    30       1650
      7876 ADAMS      CLERK           7788 83/01/12       1100                    20       1155
      7900 JAMES      CLERK           7698 81/12/03        950                    30      997.5
      7902 FORD       ANALYST         7566 81/12/03       3000                    20       3150
      7934 MILLER     CLERK           7782 82/01/23       1300                    10       1365

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO     연봉협상
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 80/12/17        800                    20        840
      7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30       1760
      7521 WARD       SALESMAN        7698 81/02/22       1250        500         30       1375
      7566 JONES      MANAGER         7839 81/04/02       2975                    20     3272.5
      7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30       1375
      7698 BLAKE      MANAGER         7839 81/05/01       2850                    30       3135
      7782 CLARK      MANAGER         7839 81/06/09       2450                    10       2695
      7788 SCOTT      ANALYST         7566 82/12/09       3000                    20       3150
      7839 KING       PRESIDENT            81/11/17       5000                    10       5000
      7844 TURNER     SALESMAN        7698 81/09/08       1500                    30       1650
      7876 ADAMS      CLERK           7788 83/01/12       1100                    20       1155
      7900 JAMES      CLERK           7698 81/12/03        950                    30      997.5
      7902 FORD       ANALYST         7566 81/12/03       3000                    20       3150
      7934 MILLER     CLERK           7782 82/01/23       1300                    10       1365

GROUP BY

: 그룹을 생성해 그룹 안에서 계산
→ GROUP BY 뒤에 오는 속성을 기준으로 그룹 생성
→ 속성은 ,(comma)를 통해 여러 개 적용 가능
→ ※ 주의! GROUP BY는 GROUP 함수와 함께 사용
→ ※ 주의! GROUP BY의 위치는 ORDER BY 앞
→ ※ 주의! GROUP BY의 SELECT 절에는 GROUP 함수와 GROUP BY 뒤에 오는 속성만 가능

▶ GROUP 함수
: MAX(최대값), MIN(최소값), SUM(합계), AVG(평균), COUNT(개수)

SELECT MAX(SAL) FROM EMP;
SELECT JOB, MIN(SAL) FROM EMP GROUP BY JOB;
SELECT SUM(SAL) FROM EMP;
SELECT TRUNC(AVG(SAL), 2) FROM EMP;
SELECT DEPTNO, COUNT(SAL) FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO;

출력 결과

MAX(SAL)
----------
      5000

JOB         MIN(SAL)
--------- ----------
CLERK            800
SALESMAN        1250
MANAGER         2450
ANALYST         3000
PRESIDENT       5000

  SUM(SAL)
----------
     29025

TRUNC(AVG(SAL),2)
-----------------
          2073.21

    DEPTNO COUNT(SAL)
---------- ----------
        10          3
        20          5
        30          6

▶ HAVING
: GROUP BY된 조회 결과에 대해 WHERE처럼 조건을 제한하는 역할
: HAVING (기준 속성) (조건 연산자) (기준 data)
→ (기준 속성)은 GROUP 함수와 GROUP BY의 기준 속성만 가능
→ AND, OR를 통해 조건 확장 가능

SELECT JOB, SUM(SAL) FROM EMP GROUP BY JOB;
SELECT JOB, SUM(SAL) FROM EMP GROUP BY JOB HAVING SUM(SAL) >= 6000;
SELECT JOB, SUM(SAL) FROM EMP GROUP BY JOB HAVING JOB != 'MANAGER';

출력 결과

JOB         SUM(SAL)
--------- ----------
CLERK           4150
SALESMAN        5600
MANAGER         8275
ANALYST         6000
PRESIDENT       5000

JOB         SUM(SAL)
--------- ----------
MANAGER         8275
ANALYST         6000

JOB         SUM(SAL)
--------- ----------
CLERK           4150
SALESMAN        5600
ANALYST         6000
PRESIDENT       5000

▷ QUIZ
1. 모든 사원의 급여 최고액, 최저액, 총액, 평균, 사원수를 구하시오.

SELECT MAX(SAL), MIN(SAL), SUM(SAL), TRUNC(AVG(SAL), 2) AS "AVG(SAL)", COUNT(SAL) FROM EMP;

출력 결과

  MAX(SAL)   MIN(SAL)   SUM(SAL)   AVG(SAL) COUNT(SAL)
---------- ---------- ---------- ---------- ----------
      5000        800      29025    2073.21         14

JOB 별로 급여 최고액, 최저액, 총액, 평균, 사원수를 구하시오.

SELECT MAX(SAL), MIN(SAL), SUM(SAL), TRUNC(AVG(SAL), 2) AS "AVG(SAL)", COUNT(SAL) FROM EMP GROUP BY JOB;

출력 결과

  MAX(SAL)   MIN(SAL)   SUM(SAL)   AVG(SAL) COUNT(SAL)
---------- ---------- ---------- ---------- ----------
      1300        800       4150     1037.5          4
      1600       1250       5600       1400          4
      2975       2450       8275    2758.33          3
      3000       3000       6000       3000          2
      5000       5000       5000       5000          1

모든 사원의 급여 최고액과 최저액의 차액을 구하시오.

SELECT MAX(SAL) - MIN(SAL) FROM EMP;

출력 결과

MAX(SAL)-MIN(SAL)
-----------------
             4200

DEPTNO 별로 급여 최고액, 최저액, 총액, 평균, 사원수를 구하시오.

SELECT MAX(SAL), MIN(SAL), SUM(SAL), TRUNC(AVG(SAL), 2) AS "AVG(SAL)", COUNT(SAL) FROM EMP GROUP BY DEPTNO;

출력 결과

  MAX(SAL)   MIN(SAL)   SUM(SAL)   AVG(SAL) COUNT(SAL)
---------- ---------- ---------- ---------- ----------
      3000        800      10875       2175          5
      2850        950       9400    1566.66          6
      5000       1300       8750    2916.66          3

TABLE

: SQL의 데이터 셋

▶ CREATE
: 테이블 생성 명령어
: 생성 시 COLUMN의 이름과 data type을 지정
: CREATE TABLE TABLE_NAME (COLUMN_NAME1 DATA_TYPE1, COLUMN_NAME2 DATA_TYPE2...);

▷ DATA_TYPE
: VARCHAR2 - 문자
: NUMBER - 숫자
: DATE - 날짜

CREATE TABLE FREEBOARD (NO NUMBER(5),
                        TITLE VARCHAR2(90),
                        WRITER VARCHAR2(90),
                        WDATE DATE,
                        WCNTS VARCHAR2(3000));

출력 결과
Table FREEBOARD이(가) 생성되었습니다.

▶ DESC
: TABLE의 구조를 보여주는 명령어
: DESC TABLE_NAME;

DESC EMP;

출력 결과

NAME     NULLABLE DATA_TYPE           
-------- -------- ------------ 
EMPNO    NOT NULL NUMBER(4)    
ENAME             VARCHAR2(10) 
JOB               VARCHAR2(9)  
MGR               NUMBER(4)    
HIREDATE          DATE         
SAL               NUMBER(7,2)  
COMM              NUMBER(7,2)  
DEPTNO            NUMBER(2)

▶ ALTER
: 테이블 수정 명령어
: ALTER TABLE TABLE_NAME ~

▷ ADD (COLUMN_NAME, DATA_TYPE);
: COLUMN 추가

▷ MODIFY COLUMN_NAME DATA_TYPE;
: COLUMN 속성 변경

▷ RENAME COLUMN (변경 전 속성 이름) TO (변경 후 속성 이름);
: COLUMN 이름 변경

▷ DROP COLUMN COLUMN_NAME;
: 속성 삭제

ALTER TABLE FREEBOARD ADD (CLICKCOUNT NUMBER(5));
DESC FREEBOARD;

ALTER TABLE FREEBOARD RENAME COLUMN CLICKCOUNT TO VIEWCNT;
DESC FREEBOARD;

ALTER TABLE FREEBOARD MODIFY TITLE VARCHAR2(150);
DESC FREEBOARD;

ALTER TABLE FREEBOARD DROP COLUMN VIEWCNT;
DESC FREEBOARD;

출력 결과

Table FREEBOARD이(가) 변경되었습니다.
이름        유형             
---------- -------------- 
NO         NUMBER(5)      
TITLE      VARCHAR2(90)   
WRITER     VARCHAR2(90)   
WDATE      DATE           
WCNTS      VARCHAR2(3000) 
CLICKCOUNT NUMBER(5)

Table FREEBOARD이(가) 변경되었습니다.
이름     유형             
------- -------------- 
NO      NUMBER(5)      
TITLE   VARCHAR2(90)   
WRITER  VARCHAR2(90)   
WDATE   DATE           
WCNTS   VARCHAR2(3000) 
VIEWCNT NUMBER(5)

Table FREEBOARD이(가) 변경되었습니다.
이름     유형             
------- -------------- 
NO      NUMBER(5)      
TITLE   VARCHAR2(150)  
WRITER  VARCHAR2(90)   
WDATE   DATE           
WCNTS   VARCHAR2(3000) 
VIEWCNT NUMBER(5)

Table FREEBOARD이(가) 변경되었습니다.
이름    유형             
------ -------------- 
NO     NUMBER(5)      
TITLE  VARCHAR2(150)  
WRITER VARCHAR2(90)   
WDATE  DATE           
WCNTS  VARCHAR2(3000)

▶ TRUNCATE
: 테이블 내용을 모두 삭제
: TRUNCATE TABLE TABLE_NAME;
: ROLLBACK 불가

TRUNCATE TABLE FREEBOARD;

출력 결과

Table FREEBOARD이(가) 잘렸습니다.

▶ DROP
: 테이블 구조 및 내용 삭제
: DROP TABLE TABLE_NAME;
: ROLLBACK 불가

DROP TABLE FREEBOARD;

출력 결과

Table FREEBOARD이(가) 삭제되었습니다.

INSERT

: TABLE에 DATA를 입력하는 명령어
: INSERT INTO TABLE_NAME(COLUMN_NAME1, COLUMN_NAME2...) VALUES(COLUMN_VALUE1, COLUMN_VALUE2...);
→ TABLE_NAME 뒤에는 입력할 속성들의 이름 작성
→ VALUES() 의 값은 TABLE_NAME 뒤에 작성한 COLUMN 순서대로 작성
→ 두 () 안의 이름과 값의 순서와 개수가 동일
: INSERT INTO TABLE_NAME VALUES(COLUMN_VALUE1, COLUMN_VALUE2...)
→ TABLE의 모든 COLUMN에 값을 입력할 시에는 TABLE_NAME 다음에 COLUMN_NAME을 명시할 필요 X
→ TABLE 생성시 작성한 COLUMN 순서대로 값을 작성

INSERT INTO FREEBOARD(NO, TITLE, WRITER, WDATE, WCNTS) 
               VALUES(1, 'IMSI', 'RO', TO_DATE('20220512'), '내용은 짧게');
INSERT INTO FREEBOARD VALUES(1, 'IMSI', 'RO', TO_DATE('20220512'), '내용은 짧게');

SELECT * FROM FREEBOARD;

출력 결과

1 행 이(가) 삽입되었습니다.
1 행 이(가) 삽입되었습니다.

        NO TITLE      WRITER     WDATE    WCNTS
---------- ---------- ---------- -------- -------------
         1 IMSI       RO         22/05/12 내용은 짧게 
         1 IMSI       RO         22/05/12 내용은 짧게

UPDATE

: TABLE에 입력된 DATA 수정 명령어
: 반드시 WHERE 사용해서 수정하려는 DATA 정확히 지정
→ WHERE 절을 작성하지 않을 경우 TABLE 전체에 수정 내용 적용
: UPDATE TABLE_NAME SET (COLUMN_NAME) = (UPDATE_VALUE) WHERE 조건절
→ 수정 속성과 수정값은 ,(comma)를 통해 다수 지정 가능
→ 수정 전, SELECT로 WHERE 절 검증

SELECT * FROM FREEBOARD;
SELECT * FROM FREEBOARD;
UPDATE FREEBOARD SET NO = 3, TITLE = 'UPDATE', WRITER = 'KANG', 
                     WDATE = TO_DATE('20220511'), WCNTS = '수정 업데이트' 
                     WHERE WDATE IS NULL;
SELECT * FROM FREEBOARD;

출력 결과

        NO TITLE      WRITER     WDATE    WCNTS
---------- ---------- ---------- -------- -------------
         1 IMSI       RO         22/05/12 내용은 짧게 
         1 IMSI       RO         22/05/12 내용은 짧게
         1 IMSI       RO         NULL     내용은 짧게

1 행 이(가) 업데이트되었습니다.

        NO TITLE      WRITER     WDATE    WCNTS
---------- ---------- ---------- -------- -------------
         1 IMSI       RO         22/05/12 내용은 짧게 
         1 IMSI       RO         22/05/12 내용은 짧게
         3 UPDATE     KANG       22/05/11 수정 업데이트

DELETE

: 테이블 내의 DATA 삭제 명령어
: DELETE FROM TABLE_NAME;
→ TABLE의 DATA 전체 삭제
: DELETE FROM TABLE_NAME WHERE 조건절;
→ 조건절에 해당하는 DATA를 삭제

SELECT * FROM FREEBOARD;
DELETE FROM FREEBOARD WHERE NO = 1;
SELECT * FROM FREEBOARD;

출력 결과

        NO TITLE      WRITER     WDATE    WCNTS
---------- ---------- ---------- -------- -------------
         1 IMSI       RO         22/05/12 내용은 짧게 
         1 IMSI       RO         22/05/12 내용은 짧게
         3 UPDATE     KANG       22/05/11 수정 업데이트

2개 행 이(가) 삭제되었습니다.

        NO TITLE      WRITER     WDATE    WCNTS
---------- ---------- ---------- -------- -------------
         3 UPDATE     KANG       22/05/11 수정 업데이트

TRANSACTION

▶ AUTOCOMMIT
: 자동 DB 반영 모드
: OFF - INSERT, UPDATE, DELETE 결과 저장 X
: ON - INSERT, UPDATE, DELETE 결과 자동 저장 (주의)
→ 작업 결과 취소는 AUTOCOMMIT OFF에서만 가능

▷ SHOW AUTOCOMMIT;
: AUTOCOMMIT의 상태(ON/OFF) 확인

▷ SET AUTOCOMMIT ON / OFF;
: AUTOCOMMIT ON/OFF 명령어

▷ COMMIT (F11)
: AUTOCOMMIT OFF에서 작업 결과를 DB에 반영하는 명령어
→ AUTOCOMMIT ON인 경우 작업을 실행하면 자동으로 COMMIT

▷ ROLLBACK (F12)
: AUTOCOMMIT OFF에서 작업 결과를 취소하는 명령어
: 마지막 COMMIT; 이후 작업 모두를 취소
→ TRUNCATE, DROP은 불가

INSERT INTO FREEBOARD VALUES (1, 'TT1', 'WRITER1', SYSDATE, 'HELLO');
ROLLBACK;

INSERT INTO FREEBOARD VALUES (1, 'TT1', 'WRITER1', SYSDATE, 'HELLO');
COMMIT;
ROLLBACK;

출력 결과
1 행 이(가) 삽입되었습니다.
롤백 완료.
△ 삽입된 DATA 작업 취소
1 행 이(가) 삽입되었습니다.
커밋 완료.
롤백 완료.
△ 커밋 되었기 때문에 롤백으로 작업을 취소 할 수 없음

오혜원

이전 포스트

22.05.10

다음 포스트

22.05.11-12

SQL

Oracle 기본 함수

Oracle 제어문

GROUP BY

TABLE

INSERT

UPDATE

DELETE

TRANSACTION

22.05.10

22.05.13

0개의 댓글