테이블에 내용을 추가INSERT, 수정UPDATE, 삭제DELETE하기 위한 DML

고객코드	고정 문자열 7자리, 공백 허용하지 않음, 기본키 적용
고객명	가변 문자열 15자리(한글은 5글자), 동백 허용하지 않음
성별	고정 문자열 1자리, 공백허용하지 않음.M(남성), W(여성)
생일	고정 문자열 8자리, 공백 허용하지 않음
전화번호	가변 문자열 15자리, 공백 허용
이메일	가변 문자열 50자리, 공백 허용
누적포인트	숫자 10자리, 소수점 이하 없음

PRIMARY KEY는 무조건 NOT NULL

고객 테이블 구조

테이블명: TB_CUSTOMER

기본키 설정방법: 컬럼명 자료형 PRIMARY KEY

TABLE 생성시 

컬럼명 자료형 NULL/NOT NULL PRIMARY KEY,

컬럼명 자료형 NULL/NOT NULL

 

nVARCHAR2(n)

없으면 고정길이, 있으면 가변길이

없으면 200BYTE, 있으면 400BYTE

크기

없으면 영문 1BYTE 한글 3BYTE 크기를 BYTE 수 단위로 받음, 있으면 유니코드 문자형 모든 문자 2BYTE 크기를 글자의 단위로 받음

문자니까 CHAR필수

 

---

1) 테이블에 새로운 행을 추가하는 INSERT문

특정한 컬럼에만 DATA를 입력	모든 컬럼에 DATA를 입력
INSERT INTO table name(column_value, ...) VALUES(column_value,...)	INSERT INTO table_name VALUES(column_value,...)

새로운 행을 추가하기 위해 insert문을 사용하면 한 번에 하나의 행만 삽입된다. 작성한 칼럼 목록 순서대로 VALUES에 지정된 값이 삽입된다.

 

날짜->' '

숫자-> 그대로

문자-> 자바 CHAR(' ')/String(" ")

           오라클 CHAR/VARCHAR2->' ', 별칭->" "

 

작업단위를 짧게 잡아 COMMIT

 

INSERT 구문에서 오류 발생의 예 

• 칼럼명에 기술된 목록의 수보다 VALUES 다음에 나오는 괄호 안에 기술한 값의 개수가 적으면 에러가 발생.
• 칼럼명에 기술된 목록의 수보다 VALUES 다음에 나오는 괄호에 기술한 값의 개수가 많으면 에러가 발생.
• 칼럼명이 잘못 입력되었을 때
• 칼럼과 입력할 값의 데이터 타입이 서로 맞지 않은 경우에도 에러가 발생한다.

1>칼럼명을 생략한 INSERT 구문

모든 칼럼에 자료를 입력한 경우에는 굳이 칼럼 목록을 기술하지 않아도 됨. 

단, 디버그 시 용이하지 않으므로 칼럼명을 써 주는것이 좋다.

INSERT INTO DEPT
VALUES(20, 'RESEARCH', 'DALLAS');

NULL은 암시적으로 들어가게 하거나 컬럼 생략시 명시적으로 입력해 줘야 한다.

NULL 값을 갖는 칼럼을 추가하기 위해 NULL 대신 ''를 사용 가능=> NOT NULL일 때 '' 불가능(가장 많이 만나는 오류)

 

--암시적으로 NULL값 삽입
INSERT INTO DEPT(DEPTNO, DNAME)
VALUES(30, 'SALES');

--명시적
INSERT INTO DEPT
VALUES(40, 'OPERATIONS',NULL);

 

--DEPT 테이블의 전체 레코드 삭제
DELETE FROM DEPT;

--DEPT 테이블 삭제
DROP 사용

1. 레코드는 가져오지 않고 기존 테이블의 컬럼만 가져오게 구조 복사

CREATE TABLE 테이블

AS

SELECT 컬럼명, 컬럼명 FROM 테이블 WHERE 1=0;

 

2. 기존 테이블에 존재하는 데이터를 다른 테이블에 입력할 때 

INSERT INTO table_name(column1, column2,...)

SELECT column1, column2,...FROM table_name WHERE 조건;

 

2>테이블의 내용을 수정하기 위한 UPDATE 문

UPDATE table_name

SET column_name1 = value1, column_name2 = value2,...

WHERE conditions; //where절을 빼먹으면 다 업데이트 된다. 주의

1. 테이블의 모든 행 변경

--입사일을 오늘로 수정
UPDATE EMP
SET HIRE_DATE=SYSDATE;

--SYSDATE: 현재 날짜

2. 테이블의 특정 행만 변경

SUBSTR(SUBSTRING): 잘라와라

SELECT * FROM EMP WHERE SUBSTR(HIRE_DATE, 1, 2)='08'; --첫번째부터 2개를 잘라와라, 0번째부터X

 

3.테이블에서 2개 이상의 칼럼 값 변경

테이블에서 하나의 컬럼이 아닌 복수 개의 칼럼 값을 변경하려면 기존 SET절에 콤마를 추가하고 칼럼=값을 추가 하면 된다.

--LAST_NAME 이 Russell인 사원의 급여를 17000로, 커미션 비율이 0.45로 인상된다.
SELECT*
FROM EMP
WHERE LAST_NAME='Russell';

UPDATE EMP
SET SALARY=17000, COMMISSION_PCT=0.45
WHERE LAST_NAME='Russell';

 

3>테이블에 불필요한 행(레코드)를 삭제하기 위한 DELETE문

테이블에 특정 로우(행)의 데이터를 삭제

--새로운 테이블 생성한 후 30번 부서를 삭제
DELETE FROM DEPT WHERE DEPTNO=30;

 

DML

INSERT INTO 테이블명(컬럼, 컬럼,...)

VALUES(값, 값,...)

--EMP01테이블에 EMPLOYEES 테이블에서 부서코드가 30인 직원의 
--사번, 이름, 소속부서, 입사일을 삽입. 
--EMP_MANAGER테이블에 EMPLOYEES 테이블의 부서코드가 30인 직원의 
--사번, 이름, 관리자 사번을 조회하여 삽입.
--두개 이상의 테이블에 INSERT ALL(마지막에 SELECT가 있어야함)을 이용하여 한번에 삽입 가능
--단, 각 서브쿼리의 조건절이 같아야함

INSERT ALL

INTO EMP01
VALUES(EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID, HIRE_DATE)

INTO EMP_MANAGER
VALUES(EMPLOYEE_ID, FIRST_NAME, MANAGER_ID)

SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID, HIRE_DATE, MANAGER_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID=30;

SELECT * FROM EMP01;
SELECT * FROM EMP_MANAGER;

-- EMPLOYEES 테이블의 구조를 복사하여 사번, 이름, 입사일, 급여를 저장할 수 있는
-- 테이블 EMP_OLD와 EMP_NEW 생성

CREATE TABLE EMP_OLD
AS
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY
FROM EMPLOYEES
WHERE 1=0;

CREATE TABLE EMP_NEW
AS
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY
FROM EMPLOYEES
WHERE 1=0;

-- EMPLOYEES 테이블의 입사일 기준으로 2006년 1월 1일 이전에 입사한 사원의 사번, 이름,
-- 입사일, 급여를 조회해서 EMP_OLD 테이블에 삽입하고 그 후에 입사한 사원의 정보는 EMP_NEW 테이블에 삽입

--INSERT ALL INTO EMP_OLD
--VALUES (EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY)
--SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY
--FROM EMPLOYEES WHERE HIRE_DATE <='06.01.01';

INSERT ALL
WHEN HIRE_DATE< '2006/01/01' THEN
    INTO EMP_OLD
    VALUES(EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY)
WHEN HIRE_DATE >= '2006/01/01' THEN
    INTO EMP_NEW
    VALUES(EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY)
    
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY
FROM EMPLOYEES;

4>MERGE문

조건을 비교해서 테이블에 해당 조건에 맞는 데이터가 없으면 INSERT문, 있으면 UPDATE를 수행하는 문장

MERGE INTO table_name

       USING(update나 insert 될 데이터 원천)

        ON(update될 조건)

WHEN MATCHED

 

MERGE INTO TB_CUSTOMER CU --CU별칭
    USING TB_ADD_CUSTOMER NC  --NC 별칭
    ON (CU.CUSTOMER_CD = NC.CUSTOMER_CD)
    WHEN MATCHED THEN 
        UPDATE SET  CU.CUSTOMER_NM = NC.CUSTOMER_NM,
                    CU.MW_FLG= NC.MW_FLG,
                    CU.BIRTH_DAY = NC.BIRTH_DAY,
                    CU.PHONE_NUMBER=NC.PHONE_NUMBER
    WHEN NOT MATCHED THEN
        INSERT (CU.CUSTOMER_CD, CU.CUSTOMER_NM, CU.MW_FLG, CU.BIRTH_DAY,
                CU.PHONE_NUMBER, CU.EMAIL, CU.TOTAL_POINT, CU.REG_DTTM)
        VALUES (NC.CUSTOMER_CD, NC.CUSTOMER_NM, NC.MW_FLG, NC.BIRTH_DAY,
                NC.PHONE_NUMBER, '', 0, TO_CHAR(SYSDATE, 'YYYYMMDDHHMISS'));

SELECT * FROM TB_CUSTOMER;