260820

Data Manipulation Language (DML)

• INSERT
• DELETE
• UPDATE

< 기본 문법 >

INSERT

INSERT

INSERT INTO (TABLE)

(COLUMN1, COLUMN2, ...)

VALUES

(TUPLE1, TUPLE2, ...)

 

 

DELETE

DELETE

DELETE (TABLE)

WHERE (CONDITION)

[조건이 없을 경우 전부]

UPDATE

UPDATE

UPDATE (TABLE)

SET (CHANGE: COLUMN = VALUE, COLUMN = VALUE, ..)

WHERE (CONDITION)

 

SUBQUERY는 CONDITION과 VALUE에 들어갈 수 있다. -- SUBQUERY가 들어갈 수 있는 공간이 생각보다 많아

---

<예제> SubQuery

UPDATE EMP
SET SAL = SAL *1.1
WHERE DEPTNO IN (
    SELECT DEPTNO
    FROM DEPT
    WHERE DNAME IN ('SALES', 'ACCOUNTING')
    ); --'SALES', 'ACCOUNTING' 부서원의 월급을 10프로 인상

where에 나타나는 Column 이름과 SubQuery에서 뽑혀 나오는 Column의 이름이 동일한지 확인.

조건을 DEPTNO으로 선정했으면, IN도 똑같은 DEPTNO로,,,!

SELECT ENAME, EMPNO, JOB, DEPTNO
FROM EMP
WHERE DEPTNO IN (
    SELECT DEPTNO
    FROM DEPT
    WHERE DNAME IN ('SALES', 'ACCOUNTING')
    );

여기는 그냥 출력을 해 내는것.

UPDATE EMP
SET SAL = SAL*1.1
WHERE SAL BETWEEN (
    SELECT LOSAL
    FROM SALGRADE
    WHERE GRADE = 1
    ) AND (
    SELECT HISAL
    FROM SALGRADE
    WHERE GRADE = 2
    ); -- LOSAL이랑 HISAL에 뭐가 들어있고 SALGRADE를 어떻게 확인하는거야??? 부분 드래그 하고 CTRL ENT 하면 값이 나오긴 하네

 

salgrade 테이블 가보면 각 grade 별로 losal과 hisal이 정해져있어,

 

여기서 그럼 의미하는 바는, grade 1 의 losal ~ grade 2 의 hisal까지 범위에 속해있는 직원들의 월급을 10프로 인상하는 것

 

UPDATE EMP
SET SAL = SAL * 1.1
WHERE SAL BETWEEN (
    SELECT LOSAL
    FROM SALGRADE
    WHERE GRADE = 1
    ) AND ( 
    SELECT HISAL
    FROM SALGRADE
    WHERE GRADE = 1
    ) OR 
SAL BETWEEN(  
    SELECT LOSAL
    FROM SALGRADE
    WHERE GRADE = 4
    ) AND (
    SELECT HISAL
    FROM SALGRADE
    WHERE GRADE = 4
    );

 

여기선 1과 4에 속한 사람들의 임금 인상을 하는 건데,

 

앞선 예제와의 차이점은 단절된 두 grade 라는 점이야

 

그래서 grade 1의 losal ~ hisal 따로,

 

그 다음 grade 4의 losal ~ hisal 따로 입력해서 조건으로 넣은거야

 

UPDATE EMP
SET SAL = ROUND(SAL*1.1)
WHERE SAL IN (
    SELECT SAL
    FROM EMP JOIN SALGRADE
    ON SAL >=LOSAL AND SAL <= HISAL --2개를 조인하는 조건. 아주 칭찬해. 저번처럼 직접적인 공통점이 아니라 '숫자'비교를 통해 조건 부여
    WHERE GRADE IN (1,4)
    );

 

이건 위의 예제와 결과값은 다르지만, 조금 더 간단하게 입력한 거야

 

앞에는 join을 이용하지 않아서 Between을 통해서 그 범위를 하나하나 입력해줬어야 했지만,

 

salgrade 테이블을 emp 테이블로 가지고 들어와서

emp 테이블의 값과 salgrade의 값들을 비교하면서 (연결고리 만들고) 단순화 시킨거야.

 

여기서 값의 비교는 같은 데이터 타입이여야 하는 건 기본이고.

 

---

한글은 3바이트

한글은 한 글자

PAD계산에선 한글 2개로 계산

 

변환함수 P.62~63 **

<변환 함수>

TO_CHAR : 날짜형과 숫자형 > 문자형

----------사용되는 형식 요소------------

9 : 일반적인 숫자

0 : 앞의 빈자리를 0으로 채운다.

$ : 달러 표시를 나타낸다.

L : 지역 통화 단위를 나타낸다.

. : 소수점을 표시한다.

, : 천 단위를 표시한다.

 

SCC : 세기를 표시, S는 BC

YEAR : 연도를 알파벳(영어)로 표시한다.

YYYY 

YY

MONTH

MON

MM

DAY

DY

DDD

DD

D

HH, HH24

MI

SS

AM, PM

-------------------------------

TO_DATE : 문자형 > 날짜형

TO_NUMBER : 문자형 > 숫자형

INSERT INTO EMP
(EMPNO, ENAME, HIREDATE) --ENAME, EMPNO, HIREDATE
VALUES
(9995, 'EJKIM', SYSDATE)
;
DELETE FROM EMP
WHERE EMPNO=9999
;
DELETE FROM EMP
WHERE EMPNO=9995
;
INSERT INTO EMP -- 또 다른 INSERT 방법
    VALUES
    (9999, 'EJKIM', NULL, NULL, SYSDATE, NULL, NULL, NULL)
    ; --EMPNO는 NULL이 불가능하다. WHY? -EMP에 제약조건 들어가면 PRIMARY KEY로 들어가 있어서 그런거같아

UPDATE EMP
    SET HIREDATE = SYSDATE
    WHERE EMPNO IN ( --WHERE 다음에 써야하는 거에 대한 지식이 거의 없네. 기준이 뭐지? WHERE이랑 SELECT랑 똑같아야 하네 일단.
        SELECT EMPNO
        FROM EMP
        WHERE ENAME = 'SMITH'
        );

SELECT E.*, TO_CHAR(HIREDATE, 'YYYY-MM-DD'), TO_CHAR(HIREDATE, 'YY-MON-DD, DY HH24')
--CHARACTER 형태로 뿌려줘. 라는 말 ; 문자로 뿌리기 때문에 ' '를 붙이는 듯 하다.
FROM EMP E
;
SELECT *
FROM EMP
;

INSERT INTO EMP
    VALUES
    (9998, 'EJKIM', NULL, NULL, TO_DATE('20년 8월 3일 13시', 'YYYY년 MM월 DD일 HH24시'), NULL, NULL, NULL) -- TO_DATE포멧을 인식할 수 없대
;
INSERT INTO EMP
    (EMPNO, ENAME, HIREDATE)
    VALUES
    (9997, 'EJKIM', TO_DATE('2020-08-03 13', 'YYYY-MM-DD HH24'))
; --이렇게 하는게 오류가 없다.

TO_DATE ( 변수명, 'YYYY-MM-DD HH24') 형태를 유지.

HTML > JSP > JAVA > DB와 연결 > SQL TO_DATE/TO_CHAR > 결과 테이블 > JAVA > JSP > HTML 

이 과정 속에서 FORMAT을 계속 맞추는 과정이 'INTERFACE'이다.

JAVA

문자열 > 숫자 : Integer.passInt(문자열);

숫자 > 문자열 : String.valueOf(숫자);

 

---

SELECT EMPNO, ENAME, NVL(COMM, 0) COMM
FROM EMP
;
SELECT EMPNO, ENAME, NVL2(COMM, SAL+COMM, SAL) PAY, SAL, COMM
FROM EMP
;
SELECT NULLIF(10, 10), NULLIF('A', 'B')
FROM DUAL
;

기타 함수로,

NVL ( A, B) : A column의 value가 null이라면, B로 치환

 

NVL2 ( A, B, C) :  A column의 value가 null이 아니라면 B로 치환, null 이라면 A로 치환

 

NULLIF ( A, B) : A와 B를 비교하여 두 값이 같다면  null, 다르다면 A를 출력

---

SELECT DECODE(100, 100, 'EQUAL', 'UNEQUAL')
FROM DUAL
;
SELECT DEPTNO, ENAME, DEPTNO, DECODE(DEPTNO, 10, '회계팀', 20, '개발팀', 30, '영업팀', 40, '운영팀')DEPTNAME
FROM EMP
; --DECODE가 어떻게 활용되는 지 정확히 파악하자

 

DECODE 기본과 DECODE 활용

 

DECODE ( A, B, C, D)  : A와 B 값을 비교해서, 같으면 C를, 다르면 D를 출력한다.

 

한 발 더 활용을 하면, FALSE 부분의 연장이 가능해서, else if문 처럼 여러 선택지를 넣을 수 있어

 

하지만 보기 깔끔하지 않은 단점이 있지.

 

그래서 활용할 수 있는 것이 'CASE'

 

SELECT DEPTNO, ENAME, DEPTNO,
    CASE 
        WHEN DEPTNO = 10
            THEN '회계팀'
        WHEN DEPTNO = 20
            THEN '개발팀'
        WHEN DEPTNO = 30
            THEN '영업팀'
        WHEN DEPTNO = 40
            THEN '운영팀'
          ELSE 'UNKNOWN'
        END DEPTNAME--여기다가 nickname
FROM EMP
;

 

CASE !!!

형태가 굉장히 JAVA의  SWITCH문과 닮아 있어서, 잘 정리되어 있는 느낌을 준다.

단어도 굉장히 직관적이여서,

CASE

WHEN (A)

THEN (B)

ELSE (C)

END (nickname) : 조건 A가 참이면, B를 출력하고, 조건들에 해당하는 게 없으면 C를 출력한다.

 

WHEN과 THEN을 무한정 반복할 수 있어.

 

 

 

< 그룹 함수 >

• COUNT
• SUM
• AVG
• MAX
• MIN

 

 

SELECT COUNT(*), COUNT(EMPNO), COUNT(MGR), COUNT(COMM)
FROM EMP
;

(NULL값은 제외된다.)

 

SELECT SUM(SAL)
FROM EMP
;

 

SELECT AVG(SAL)
FROM EMP
;
SELECT ROUND(AVG(SAL),2) -- 소수점 두 번째 자리에서 반올림한 결과를 RETURN
FROM EMP
;

활용

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

---

< 그룹 함수>

SELECT JOB, COUNT(JOB) -- COUNT(*) 여도 결과 똑같아. (순서가 있어서 그런 결과가 나타남.)
FROM EMP
GROUP BY JOB--JOB은 여러개의 TUPLE로 되어있는데, COUNT(JOB)은 하나의 TUPLE로 출력되니까 오류가 나는듯
;
-- QUERY를 보고 어떤 출력문이 나올지 예상할 수 있어야 한다.

 

뒤에서부터 올라가보면, 순서가 조금 명확해 지는 듯 해

 

먼저,

1. JOB을 그룹을 짓는다. (공통된 JOB들끼리 묶는다.)

2. EMP 테이블에 있는. (JOB들의 주소)

3. 거기서 JOB들과  각각의 JOB을 센 값을 출력한다.

SELECT JOB, COUNT(SAL)
FROM EMP
GROUP BY JOB
;

SELECT JOB, COUNT(SAL)
FROM EMP
GROUP BY JOB, SAL
; --JOB끼리 묶고, SAL끼리도 묶어서 나타낸다.

 

잘 알고있으라고 - 각각의 의미가 무엇이고, 출력될 때에 어떤 모습인지 예상할 수 있어야돼

Transaction 1 :

1. JOB을 그룹을 짓는다. (공통된 JOB들끼리 묶는다.)

2. EMP 테이블에 있는.

3. JOB들을 보여주고,  SAL을 센다. -- 여기선 아마 같은  JOB이지만, 서로 다른 SAL을 가진 사람들의 수를 보여줄 것 같아.

예제)

SALESMAN 4

 

Transaction 2 :

1. JOB과 SAL을 각각 그룹을 짓는다. (공통된 JOB과, 공통된 SAL들끼리 묶는다.)

2. EMP 테이블에 있는.

3. JOB들을 보여주고, SAL을 센다. -- 하지만, 1번에서 각각 그룹을 지었기 때문에, 같은 이름의  JOB이여도 SAL이 다르면 따로 그룹지어져 있다. 

예시)

SALESMAN 2

SALESMAN 1

SALESMAN 1

 

-- TABLE 복사할 땐, CONSTRAINT는 복사되지 않고 겉모습만 복사된다. (그냥 알고있으라고)

SELECT E.DEPTNO, DNAME, COUNT(E.EMPNO) --DNAME에는 D. 안붙여도 그냥 나오네? 어차피 D에 있는 값이여서? E.은 빼면 안돼.
FROM EMP E, DEPT D 
WHERE E.DEPTNO = D.DEPTNO --ON이 아니라 WHERE이여서 JOIN 못 쓰는듯?
GROUP BY E.DEPTNO, DNAME;
-- 부서번호, 부서이름에 따라 그룹핑한 후에 각 부서별 사원수를 리턴한다.

SELECT E.DEPTNO, DNAME, COUNT(E.EMPNO)
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
GROUP BY E.DEPTNO, DNAME
;

 

 

SELECT E.DEPTNO, DNAME, COUNT(E.EMPNO)
FROM EMP E, DEPT D
WHERE D.DEPTNO = E.DEPTNO
GROUP BY E.DEPTNO, DNAME
HAVING COUNT(E.EMPNO) > 5 -- 추가적인 조건까지 달았네
;

 

SELECT JOB, COUNT(*)
FROM EMP
GROUP BY JOB
HAVING JOB<>'PRESIDENT'
;
SELECT JOB, COUNT(*)
FROM EMP
WHERE mgr IS NOT NULL
GROUP BY JOB
;

HAVING문과 WHERE문 비교