모방은 창조의 어머니

서브 쿼리를 이용한 데이터 추가

서브쿼리를 사용하면 SELECT문으로 한번에 여러 행의 데이터를 추가할 수 있습니다.

예를들어 사원정보(EMP) 테이블에서 사원급여정보(SALGRADE) 테이블을 참고하여 급여등급(GRADE)이 1인 사원만을 EMP_TEMP 테이블에 넣고 싶다면 서브쿼리를 포함한 INSERT문을 사용하시면 됩니다.

INSERT INTO EMP_BAK (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM, E.DEPTNO
    FROM EMP E, SALGRADE S
    WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL 
        AND S.GRADE = 1;

위의 예제에서 보는 것과 같이 INSERT문에서 서브쿼리를 사용할때는

• VALUES절을 사용하지 않아야 하고, 
• 데이터를 추가할 테이블과 서브쿼리의 열의 갯수가 일치해야 하며,
• 데이터를 추가할 테이블과 서브쿼리의 자료형이 일치해야 합니다.

데이터 삭제 DELETE

DELETE문은 테이블에 있는 데이터를 삭제한 때 사용 됩니다.

기본형식

DELETE FROM 테이블명
[WHERE 삭제할_대상_행을_선별하는_ 조건식];

이때 WHERE 절을 생략하면 모든 데이터가 삭제 됩니다.

데이터 일부만 삭제하기

DELETE문을 사용하여 EMP_TEST2 테이블에서 직책이 MANAGER인 사원만 삭제해 봅시다.

DELETE FROM EMP_TEST2
WHERE JOB = 'MANAGER';

WHERE절의 조건을 이용하여 특정 행을 삭제 하였습니다.

서브쿼리를 이용한 데이터 삭제

서브쿼리를 이용하여 EMP_TEST2테이블에서 급여가 3등급이고, 부서 번호가 30인 직원들을 삭제해 보자.

DELETE FROM EMP_TEST2
WHERE EMPNO IN (SELECT E.EMPNO
        FROM EMP_TEST2 E, SALGRADE S
        WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
            AND GRADE = 3
            AND DEPTNO = 30); 

데이터 전체 삭제하기

특별한 경우를 제외하고 흔하지는 않지만, 데이터 전체를 삭제하려면 WHERE절을 생략 하면 됩니다.

DELETE FROM EMP_TEST2;

테이블에 날짜 데이터 입력하기

1. 특정일 입력하기

 EMP 테이블의 형식을 가져와서 만든 EMP_BAK 테이블의 HIREDATE 컬럼에 날짜 데이터를 입력해 봅시다.

INSERT INTO EMP_BAK
    VALUES (1111, 'ANN', 'FREE', NULL, '01/12/25', 2500, 1000, 10);

날짜의 형식을 '01/12/25' 대신 '2002-12-25'의 형식으로 입력해도 자료는 정상적으로 입력 됩니다.

INSERT INTO EMP_BAK
    VALUES (1111, 'ANN', 'FREE', NULL, '2002-12-25', 2500, 1000, 10);

2. 오늘 날짜 입력하기

날짜를 입력할 때 특정일이 아닌 오늘의 날짜를 입력해 봅시다. 

INSERT INTO EMP_BAK
    VALUES (1111, 'ANN', 'FREE', NULL, SYSDATE, 2500, 1000, 10);

컬럼에 NULL 데이터 추가

INSERT문으로 새로운 데이터를 추가할 때, 특정 열에 들어갈 데이터가 확정되지 않았거나, 굳이 넣을 필요가 없을 때에는 NULL을 사용합니다. 

1. NULL의 명시적 입력

NULL을 지정하여 직접 입력하실 수 있습니다.

 INSERT INTO DEPT_BAK (DEPTNO, DNAME, LOC)
    VALUES (70, 'SOFTWARE', NULL);

NULL을 지정하여 직접 입력하는 것 이외에,

해당 자료형이 문자열이거나 날짜형인 경우 빈 공백문자를 이용하여 입력하실 수도 있습니다.

INSERT INTO DEPT_BAK (DEPTNO, DNAME, LOC)
    VALUES (80, 'MODEL', '');

2. NULL의 암시적 입력

NULL의 암시적 입력 방법은 INSERT 문에서 NULL이 들어갈 컬럼의 이름을 입력하지 않는 것입니다. 

사용 예는 아래와 같습니다.

INSERT INTO DEPT_BAK (DEPTNO, LOC)
    VALUES (90, 'INCHEON');

데이터 수정(변경) UPDATE

데이터베이스에 저장된 데이터를 수정(변경)하려면 UPDATE문을 사용하시면 됩니다. 

기본형식 

UPDATE 변경할_테이블_명
SET 변경할_컬럼1 = 데이터, 변경할_컬럼2 = 데이터, ..., 변경할_컬럼n = 데이터
[ WHERE 데이터를_변경할_대상_행_선별하는_조건; ]

UPDATE문은 변경할 테이블을 지정한뒤 해당 테이블에서 변경할 컬럼과, 그 컬럼에 들어갈 값을 '=' 기호를 통해 매핑합니다. 이때 변경할 컬럼과 값은 여러개가 올 수 있습니다. 또한 WHERE절 생략할 수 있으며, 이를 이용하여 변경할 데이터를 선별하는 조건식을 지정할 수 있습니다.

컬럼 전체 데이터 수정

UPDATE DEPT_TEST
    SET LOC = 'SEOUL';

해당 명령어는 DEPT_TEST 테이블의 LOC 컬럼의 값을 모두 'SEOUL'로 바꾸라는 명령어 입니다. 

컬럼 일부 데이터 수정

WHERE절의 조건식을 이용하여 특정조건에 맞는 컬럼만 수정 할 수 있습니다.

UPDATE DEPT_TEST
    SET DNAME = 'DATABASE', LOC = 'SEOUL'
WHERE DEPTNO = 40;

해당 예제는 DEPTNO가 40인 행의 DNAME 컬럼과, LOC 컬럼의 값을 각각 DATABASE와 SEOUL로 바꾸는 예제 입니다.

서브쿼리를 이용한 데이터 수정

UPDATE DEPT_TEST
    SET DNAME   = (SELECT DNAME
                   FROM DEPT
                   WHERE DEPTNO = 40),
        LOC     = (SELECT LOC
                   FROM DEPT
                   WHERE DEPTNO = 40)
WHERE DEPTNO = 40; 

이번 예제는 DEPT 테이블에서 DEPTNO가 40번인 열의 DNAME, LOC를 가져다가 DEPT_TEST 테이블에 DEPTNO가 40번인 열의 DNAME, LOC에 업데이트(수정)하는 예제 입니다.

SELECT 문을 통해서 DEPTNO 가 40인 열의 DNAME와 LOC를 한꺼번에 조회해 볼 수 있습니다. 이를 이용해서, 업데이트를 조금 간단하게 만들어 볼까요?

UPDATE DEPT_TEST
    SET(DNAME, LOC) = (SELECT DNAME, LOC
                        FROM DEPT
                        WHERE DEPTNO = 40)
WHERE DEPTNO = 40;  

WHERE절에 서브 쿼리를 이용하여 데이터를 수정 할 수도 있습니다.

테이블에 데이터 추가 INSERT 문 

테이블에 데이터를 추가할 때는 INSERT문을 사용하시면 됩니다.

기본형식

INSERT INTO 테이블명 ( 컬럼1, 컬럼2, ... , 컬럼n )
    VALUE ( 컬럼1에 들어갈 데이터, 컬럼2에 들어갈 데이터, ... , 컬럼n에 들어갈 데이터

INSERT INTO 절 뒤에 데이터를 추가할 테이블 이름을 쓰고,

해당 테이블의 컬럼을 소활호로 묶어 지정한 후

VALUE절 에서는 지정한 열에 입력한 데이터를 작성해 주시면 됩니다.

사용 예 1. 기본형식

실습을 위해 만들어 놓았던 DEPT_BAK 테이블에 정보를 추가해 봅시다.

INSERT INTO DEPT_BAK (DEPTNO, DNAME, LOC)
    VALUES (50, 'DATABASE', 'SEOUL');

DEPT_BAK 테이블을 조회해 보면, 해당 값이 정상적으로 추가된 것을 확인하실 수 있습니다.

사용 예 2. 컬럼 지정 생략

INSERT문에서 컬럼명을 생략할 수 있습니다. 컬럼명을 생략하면 해당 테이블에 자료를 추가할때 기존 컬럼의 순서대로 데이터가 입력 됩니다. 이때 테이블을 구성하는 열의 갯수나 자료형, 길이 등은 반드시 일치해야 합니다.

실습을 위해 만들어 놓았던 DEPT_BAK 테이블에 정보를 추가해 봅시다.

INSERT INTO DEPT_BAK
    VALUES (60, 'NETWORK', 'BUSAN');

SELECT *
FROM DEPT_BAK;

연산자 우선순의(Priority)

쿼리에서 사용하는 연산자의 우선순위는 수학의 우선순위와 같습니다. 

※ 모든 연산식에 대하여, ( )안에 있는 연산식을 먼저 수행 합니다.

테이블을 생성하는 CREATE 문

기존에 있는 테이블을 복사하여 새로운 테이블 만들기

새로운 자료의 추가, 삭제, 변경 등의 작업을 안전하게 진행하기 위해, 작업한 데이터에 대한 백업 또는 작업을 테스트해보기 위해 기존에 있는 테이블과 같은 테이블을 복사해야 할 때가 있습니다.  

DEPT 테이블에 작업하기 전에 DEPT 테이블을 백업해 봅시다.

CREATE TABLE DEPT_BAK 
AS SELECT * FROM DEPT;

DEPT 테이블과 DEPT_BAK  테이블을 비교해 보면 동일한 테이블이 하나 더 만들어 졌음을 확인하실 수 있습니다.

컬럼 구조만 복사하여 테이블 만들기

데이터 없이 컬럼 구조만 복사하여 테이블을 만들어 봅시다. 

CREATE TABLE EMP_BAK
    AS SELECT * FROM EMP WHERE 1 <> 1;

WHERE 절의 조건 '1 <> 1' 는 결과값이 항상 '거짓;이 되어 행을 출력하지 않습니다. 급히 같은 형태의 테이블을 만들어야 할 때 사용할 수 있는 방법입니다.

오라클(Oracle) 함수 정리

오라클 데이터베이스에서 사용되는 함수를 한곳에서 보고 싶어 보아 보았습니다.
오라클 함수는 크게 문자열 함수, 숫자 함수, 날짜 함수, 변환 함수, 집계 함수, 분석 함수로 구분 지어 정리.

1. 문자열 함수(String Functions)

• UPPER(문자열) : 문자열을 대문자로 변환하여 반환 합니다.
• LOWER(문자열) : 문자열을 소문자로 변환하여 반환 합니다.
• INITCAP(문자열) : 각 단어의 첫 글자를 대문자로 변환하여 반환 합니다.
• LENGTH(문자열) : 문자열의 길이를 반환합니다.
• LENGTHB(문자열) : 문자열의 길이를 바이트 단위로 구해서 반환합니다.
• SUBSTR(문자열, 시작 위치, 길이) : 지정한 위치에서 부분 문자열을 추출합니다.
• INSTR(문자열, 검색 문자열, 시작 위치, 발생 횟수) : 특정 문자열이 나타나는 위치를 반환합니다.
• REPLACE(문자열, 검색 문자열, 대체 문자열) : 특정 문자열을 다른 문자열로 대체합니다.
• LPAD(문자열, 검색 문자열, 길이, 대체 문자열) : 문자열에서 길이만큼 대체 문자열로 문자열 왼쪽을 대체 합니다.
• RPAD(문자열, 검색 문자열, 길이, 대체 문자열) : 문자열에서 길이만큼 대체 문자열로 문자열 오쪽을 대체 합니다.
• CONCAT(문자열1, 문자열2 ) : 두 개의 문자열을 연결합니다.
• TRIM(문자열) : 문자열의 앞뒤 공백을 제거합니다.

2. 숫자 함수(Numeric Functions)

• ROUND(숫자, 소수점 자리) : 지정한 자리에서 반올림합니다.
• TRUNC(숫자, 소수점 자리) : 지정한 자리에서 절삭합니다.
• MOD(숫자1, 숫자2) : 나머지 연산을 수행합니다.
• CEIL(숫자) : 올림 값을 반환합니다.
• FLOOR(숫자) : 내림 값을 반환합니다.
• ABS(숫자) : 절댓값을 반환합니다.

3. 날짜 함수(Date Functions)

• SYSDATE : 현재 날짜 및 시간을 반환합니다.
• CURRENT_DATE : 세션의 현재 날짜를 반환합니다.
• ADD_MONTHS(날짜, 개월 수) : 날짜에 지정한 개월 수를 추가합니다.
• MONTHS_BETWEEN(날짜1, 날짜2) : 두 날짜 간 개월 수를 반환합니다.
• NEXT_DAY(날짜, 요일) : 지정한 날짜 이후의 가장 가까운 해당 요일을 반환합니다.
• LAST_DAY(날짜) : 해당 월의 마지막 날을 반환합니다.
• EXTRACT(YEAR | MONTH | DAY FROM 날짜) : 날짜에서 연, 월, 일을 추출합니다.

4. 변환 함수(Conversion Functions)

• TO_CHAR(날짜 또는 숫자, 형식) : 날짜 또는 숫자를 문자열로 변환합니다.
• TO_DATE(문자열, 형식) : 문자열을 날짜로 변환합니다.
• TO_NUMBER(문자열, 형식) : 문자열을 숫자로 변환합니다.

5. 집계 함수(Aggregate Functions)

• COUNT(컬럼) : 행 개수를 반환합니다.
• SUM(컬럼) : 합계를 반환합니다.
  AVG(컬럼) : 평균 값을 반환합니다.
• MAX(컬럼) : 최대값을 반환합니다.
• MIN(컬럼) : 최소값을 반환합니다.
• GROUP BY 컬럼 : 그룹별 집계를 수행합니다.

6. 분석 함수(Analytic Functions)

• RANK() OVER (PARTITION BY 컬럼 ORDER BY 컬럼) : 순위를 반환하며, 동일 값에 동일 순위를 부여합니다.
• DENSE_RANK() OVER (PARTITION BY 컬럼 ORDER BY 컬럼) : 중복 없이 연속된 순위를 반환합니다.
• ROW_NUMBER() OVER (PARTITION BY 컬럼 ORDER BY 컬럼) : 행 번호를 반환합니다.
• LEAD(컬럼, 이동 행 수, 기본값) OVER (ORDER BY 컬럼) : 다음 행의 값을 반환합니다.
• LAG(컬럼, 이동 행 수, 기본값) OVER (ORDER BY 컬럼) : 이전 행의 값을 반환합니다.
• NTILE(N) OVER (ORDER BY 컬럼) : 전체 데이터를 N개의 그룹으로 나눕니다.

7. 기타 함수

• NVL(컬럼, 기본값) : NULL 값을 기본값으로 대체합니다.
• NVL2(컬럼, 값1, 값2) : 컬럼이 NULL이 아닐 경우 값1, NULL이면 값2를 반환합니다.
• DECODE(컬럼, 조건1, 결과1, 조건2, 결과2, ...) : IF-ELSE와 유사한 조건 처리를 수행합니다.
• CASE WHEN 조건 THEN 결과 ELSE 결과 END : 조건문을 처리합니다.

NULL에 관하여

Null 또는 NULL은 구조적 질의언어(SQL)에서 데이터베이스 내의 데이터 값이 존재하지 않는다는 것을 지시하는 데 사용되는 특별한 표시어(special marker)이다' _ 위키 

데이터베이스를 처음 공부하시는 분들이 의외로 많이 혼동스러워 하시는 개념 입니다. '값이 존재하지 않는다'라는 말은 NULL은 비어 있는 공백이나, 숫자에서 없음을 뜻하는 0이 아닙니다. 말 그대로 값이 없는 상태입니다. 

비어 있기 때문에 연산을 수행 할 수도 없습니다.

• NULL + 숫자 = NULL
• NULL > 숫자 = NULL
•  

아직 부여되지 않은 값을 의미 합니다. 예를들어 아직 태어나지 않은 아이의 생일,

직원 중 수당(SAL)이 없는(NULL) 직원을 조회하고 싶은 경우 아래와 같이 조회 하시면 됩니다. 예제로 사용된 TABLE의 정보는 'SCOTT 계정 예제 Table(emp, dept, salgrade)'을 참고하시면 됩니다.

SELECT *
FROM EMP
WHERE COMM IS NULL;

예제에서 NULL인 컬럼을 조회하기 위해서 'IS NULL' 연산자를 사용하였습니다. 혹시 'COMM = NULL'이라고 하면 NULL인 컬럼을 검색할 수 있지 않을까 생각하시는 분도 계실 것 같아 직접 쿼리를 수행해 보았습니다.

보시는 바와 같이 아무런 결과 값을 출력하지 않습니다. 쿼리를 통한 연산에서 값이 'TRUE'인 경우에 결과를 출력하는데, NULL과의 연산 결과는 NULL로 결과 값이 'TRUE'도 'FALSE'도 아니기 때문에 결과값으로 출력되지 않습니다

논리 연산자와 NULL의 관계

AND 연산자

OR 연산자