230414 서른 아홉 번째 수업_SQL

DML(Data Manipulation Language):

INSERT:

테이블에 새로운 행을 추가하는 구문

[표현법]

1) INSERT INTO 테이블명 VALUES(값1, 값2, 값3 ...);

-테이블에 모든 컬럼에 대한 값을 직접 제시하여 한 행을 넣고자 할 때 사용
-컬럼 순번을 지켜 VALUES에 값을 나열해야함
-데이터 값이 컬럼의 개수보다 적거나 많으면 오류

INSERT 
    INTO EMPLOYEE 
    VALUES
        (
            230
           ,'홍길동'
           ,'123456-1234567'
           ,'hong@google.com'
           ,'01012345678'
           ,'D3'
           ,'J2'
           ,3500000
           ,0.2
           , NULL
           , SYSDATE
           , NULL
           , DEFAULT
           );

2) INSERT INTO 테이블명(컬럼명1, 컬럼명2, 컬럼명3 ...) VALUES(값1, 값2, 값3 ...);

테이블에 내가 선택한 컬럼에 대한 값만 삽입할 때
-(행단위로 추가되기 때문에 넣지 않은 값은 NULL이나 DEFAULT값이 들어감)
-컬럼명을 기재한 순서에 맞춰 데이터 입력
=> NOT NULL 제약조건이 있는 컬럼은 반드시 값을 넣어줘야됨

--컬럼 중 몇 개만 데이터를 입력할 때 컬럼명 반드시 기재           
INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, EMP_NO, JOB_CODE, HIRE_DATE) 
VALUES(240,'아무개','987654-9876543','J5','11/10/21');

INSERT INTO EMPLOYEE(EMP_NO, JOB_CODE, EMP_ID, EMP_NAME, DEPT_CODE)
VALUES('781206-2947589','J7',241,'이길동','D3');

INSERT INTO 테이블명 (서브쿼리);

-VALUES로 값을 직접 명시하는 대신 서브쿼리로 조회된 결과값을 모두 INSERT가능(여러행도 가능)

--사번, 사원명, 부서명
--오라클 전용구문
SELECT EMP_ID, EMP_NAME, DEPT_TITLE
    FROM EMPLOYEE, DEPARTMENT
    WHERE DEPT_CODE = DEPT_ID(+); --NULL값까지 가져오려면(+)
--ANSI구문
SELECT EMP_ID, EMP_NAME, DEPT_TITLE
    FROM EMPLOYEE
    LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID); --NULL값까지 가져오려면 JOIN기준써주기
    
INSERT INTO EMP_02
    (SELECT EMP_ID, EMP_NAME, DEPT_TITLE
    FROM EMPLOYEE
    LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID));

INSERT ALL:

두개 이상의 테이블에 각각 INSERT할 때
이 때 사용되는 서브쿼리가 동일한 경우
[표현법]

INSERT ALL
INTO 테이블명1 VALUES(컬럼명, 컬럼명, ...)
INTO 테이블명2 VALUES(컬럼명, 컬럼명, ...)
서브쿼리;

-- 테이블 2개 생성
CREATE TABLE EMP_DEPT
AS SELECT EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE
    FROM EMPLOYEE
    WHERE 1=0; --데이터는 가져오지 않고 테이블의 구조만 가져오기
    
CREATE TABLE EMP_MANAGER
AS SELECT EMP_ID, EMP_NAME, MANAGER_ID
    FROM EMPLOYEE
    WHERE 1=0;
    
INSERT ALL --테이블 두 개 이상일 때 ALL
    INTO EMP_DEPT VALUES(EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE)
    --원하는 값만 골라 가져오기 때문에 ()만 쓰는게 아니라 VALUES넣어서 가져옴
    INTO EMP_MANAGER VALUES(EMP_ID, EMP_NAME, MANAGER_ID)
        SELECT EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE, MANAGER_ID
            FROM EMPLOYEE
            WHERE DEPT_CODE = 'D1';

* 조건을 사용하여 각 테이블에 값을 삽입:

[표현식]

   INSERT ALL
    WHEN 조건1 THEN 
        INTO 테이블명1 VALUES(컬럼명, 컬럼명, ...)
    WHEN 조건2 THEN
        INTO 테이블명2 VALUSE(컬럼명, 컬럼명, ...)
    서브쿼리;

--2000년도 이전 입사한 사원들의 대한 정보
INSERT ALL
WHEN HIRE_DATE < '2000/01/01' THEN 
    INTO EMP_OLD VALUES (EMP_ID, EMP_NAME, HIRE_DATE, SALARY)
WHEN HIRE_DATE >= '2000/01/01' THEN
    INTO EMP_NEW VALUES (EMP_ID, EMP_NAME, HIRE_DATE, SALARY)
SELECT *
FROM EMPLOYEE;

UPDATE:

테이블에 기록되어있는 기존의 데이터를 수정하는 구문

[표현법]

UPDATE 테이블
SET 컬럼명 = 바꿀값,
컬럼명 = 바꿀값,
... -->여러개의 컬럼값을 동시 변경가능
[WHERE 조건]; -->주의: 조건을 생략하면 모든 행의 데이터가 변경

--EMP_02테이블의 DEPT_TITLE 값 변경
--모든 부서명이 다 바뀜
UPDATE EMP_02 
SET DEPT_TITLE = '전략기획팀';

ROLLBACK;

--부서명이 총무부인 것만 바뀜
UPDATE EMP_02 
    SET DEPT_TITLE = '전략기획팀'
WHERE DEPT_TITLE = '총무부';

--박정보 사원의 급여를 4000000원으로, 보너스를 0.2로 변경
UPDATE EMP_SALARY
    SET SALARY = 4000000, BONUS = 0.2
WHERE EMP_NAME = '박정보';

* UPDATE시 서브쿼리 사용가능:

[표현법]

UPDATE 테이블명
SET 컬럼명 = (서브쿼리)
WHERE 조건;

--아무개 사원의 급여와 보너스 값을 전정보 사원의 급여와 보너스로 변경
UPDATE EMP_SALARY
    SET SALARY = (SELECT SALARY
                    FROM EMP_SALARY
                    WHERE EMP_NAME = '전정보'),
        BONUS = (SELECT BONUS
                    FROM EMP_SALARY
                    WHERE EMP_NAME = '전정보')
WHERE EMP_NAME = '아무개';

--다중열 서브쿼리로도 가능
UPDATE EMP_SALARY
    SET (SALARY, BONUS) = (SELECT SALARY, BONUS
                                FROM EMP_SALARY
                            WHERE EMP_NAME = '문정보')
WHERE EMP_NAME = '이길동';

--EMPLOYEE_COPY테이블에서 ASIA 지역에서 근무하는 사원들의 보너스를 0.3으로 변경...조건문 쓰는 거 익히기
UPDATE EMPLOYEE_COPY
SET BONUS = 0.3
WHERE EMP_ID IN (SELECT EMP_ID
                    FROM EMPLOYEE_COPY
                JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
                JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
                WHERE LOCAL_NAME LIKE 'ASIA%');

--EMP_SALARY 테이블에서 이정하, 홍정보, 지정보, 정보 사원의 보너스를 오정보의 급여와 보너스가 같도록 변경
UPDATE EMP_SALARY
SET (BONUS, SALARY) = (SELECT BONUS, SALARY
                FROM EMP_SALARY
            WHERE EMP_NAME = '오정보')
WHERE EMP_NAME IN( '이정하' , '홍정보' , '최하보' , '지정보', '정보');

* UPDATE시에도 해당 컬럼에 대한 제약조건이 위배되면 안 됨:

-- EMPLOYEE 테이블에서 사번이 200번인 사원의 이름을 NULL로 변경
UPDATE EMPLOYEE
    SET EMP_NAME = NULL
WHERE EMP_ID = 200; --NULL로 업데이트 불가 오류 발생

UPDATE EMPLOYEE
    SET JOB_CODE = 'J9'
WHERE EMP_NAME = '김정보'; --부모 키가 없다는 FOREIGN KEY 위배 발생(JOB테이블에 J9가 없기 때문에)

DELETE:

테이블에 기록된 데이터를 삭제하는 구문(행단위로 삭제)

[표현법]

   DELETE FROM 테이블명
    [WHERE 조건]; -- 주의: 조건을 제시하지 않으면 모든 행 삭제

DELETE FROM EMPLOYEE_COPY3; -- 데이터 모두 삭제됨

ROLLBACK;

DELETE FROM EMPLOYEE_COPY
WHERE EMP_NAME = '지정보';

DELETE FROM EMPLOYEE_COPY
WHERE DEPT_CODE = 'D1';

ROLLBACK;

DELETE FROM DEPARTMENT
WHERE DEPT_ID = 'D1'; -- 자식 레코드 발견 FOREIGN KEY 오류 발생

TRUNCATE:

테이블의 전체행을 삭제할 때 사용되는 구문
DELETE보다 수행속도가 더 빠름
별도의 조건 제시 불가, ROLLBACK 불가

[표현법]

TRUNCATE TABLE 테이블명;

TRUNCATE TABLE EMP_01;
ROLLBACK; -- 롤백 기능 안 먹힘