[KH정보교육원] 22일차_DML(INSERT, UPDATE, DELETE), DDL(ALTER, DROP)

2021.11.10(수)

🌿 DML

DATA MANIPULATION LANGUAGE, 데이터 조작 언어
테이블에 새로운 데이터를 삽입(INSERT) + 기존의 데이터를 수정(UPDATE) + 삭제(DELETE)하는 구문

🌱 INSERT

1. INSERT: 테이블에 행 추가하는 구문; 해당 테이블의 모든 컬럼에 추가하고자 하는 값을 내가 직접 제시해서 한 행을 INSERT하고자 할 때 사용

주의할 점: 컬럼 순번을 지켜서 VALUES 괄호 안에 나열해야 함
표현법
- INSERT INTO 테이블명 VALUES(값, 값, 값..);
- INSERT INTO 테이블명(컬럼명1, 컬럼명2, 컬럼명3, ..) VALUES(값1, 값2, 값3, ..);
- INSERT INTO 테이블명 (서브쿼리); : VALUES로 값을 기입하는 것 대신에 서브쿼리로 조회한 결과값을 INSERT하는 구문 → 여러 행 INSERT 가능

CREATE TABLE EMP_01(
    EMP_ID NUMBER,
    EMP_NAME VARCHAR2(30),
    DEPT_TITLE VARCHAR2(20)
);

-- 전체 사원들의 사번, 이름, 부서명을 조회한 결과
INSERT INTO EMP_01
(SELECT EMP_ID, EMP_NAME, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID(+) -- JOIN의 기준이 되는 테이블의 반대쪽 연결고리에 (+) 표시
);

2. INSERT ALL: 2개 이상의 테이블에 각각 INSERT할 때 + 그 때 사용되는 서브쿼리가 동일한 경우 사용
- 표현법1:

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

INSERT ALL
INTO EMP_JOB VALUES(EMP_ID, EMP_NAME, JOB_NAME) -- 아래 서브쿼리의 RESULT SET 9개의 행 추가
INTO EMP_DEPT VALUES(EMP_ID, EMP_NAME, DEPT_TITLE) -- 아래 서브쿼리의 RESULT SET 9개의 행 추가
    SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME
    FROM EMPLOYEE E
    LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
    LEFT JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
    WHERE SALARY >= 3000000; -- 서브쿼리의 RESULT SET = 9행

- 표현법2:

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

INSERT ALL
WHEN HIRE_DATE < '2010-01-01' THEN
    INTO EMP_OLD VALUES(EMP_ID, EMP_NAME, HIRE_DATE, SALARY) -- 서브쿼리의 RESULT SET 중 9개의 행 추가
WHEN HIRE_DATE >= '2010-01-01' THEN
    INTO EMP_NEW VALUES(EMP_ID, EMP_NAME, HIRE_DATE, SALARY) -- 서브쿼리의 RESULT SET 중 16개의 행 추가
SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY FROM EMPLOYEE; -- 서브쿼리의 RESULT SET = 25행

🌱 UPDATE

테이블에 기록된 기존의 데이터를 수정하는 구문
제약조건 = 생성 및 수정 시 → UPDATE 사용하여 수정 시에도 제약조건에 위배되면 안 됨

표현법:

UPDATE 테이블명
SET 컬럼명 = 바꿀값
  , 컬럼명 = 바꿀값
  , 컬럼명 = 바꿀값 -- 여러 개의 컬럼 동시 변경 가능(",(쉼표)", ".(온점)")
WHERE 조건; -- 생략 가능 -> 생략 시 전체 행의 데이터가 모두 변경됨

-- EMP_SALARY 테이블의 방명수 사원의 급여와 보너스를 유재식 사원의 급여와 보너스 값으로 변경
UPDATE EMP_SALARY
SET (SALARY, BONUS) = (SELECT SALARY, BONUS
                       FROM EMP_SALARY
                       WHERE EMP_NAME = '유재식')
WHERE EMP_NAME = '방명수';

🌱 DELETE

테이블에 기록된 데이터(o) 테이블 자체(x)를 삭제하는 구문

표현법:

DELETE FROM 테이블명
WHERE 조건; -- WHERE절은 생략 가능 -> 생략 시 해당 테이블의 전체 행 삭제

-- 무민, 스노크메이든 사원의 데이터 삭제/지우기
DELETE FROM EMPLOYEE
WHERE EMP_NAME IN ('무민', '스노크메이든'); -- 2개 행이 삭제됨

DELETE FROM DEPARTMENT
WHERE DEPT_ID = 'D1'; -- D1을 가져다쓰고 있는 자식데이터가 있기 때문에 삭제 안 됨; ORA-02292: integrity constraint (KH.SYS_C007160) violated - child record found

-- DEPARTMENT 테이블로부터 DEPT_ID가 D3인 부서 삭제
DELETE FROM DEPARTMENT
WHERE DEPT_ID = 'D3'; -- D3을 가져다쓰고 있는 자식데이터가 없기 때문에 삭제됨

🌱 TRUNCATE

테이블의 전체 행을 삭제할 때 사용하는 구문, '초기화'로 이해해도 됨
DELETE보다 수행속도가 더 빠름
별도의 조건 제시 불가
ROLLBACK 불가능 = 한 번 실행하면 돌이킬 수 없음
TRUNCATE TABLE 테이블명;

🌿 DDL

DATA DEFINITION LANGUAGE, 데이터 정의 언어
객체들을 새로이 생성(CREATE), 수정(ALTER), 삭제(DROP)하는 구문

🌱 ALTER

객체 구조를 수정하는 구문
테이블 수정: ALTER TABLE 테이블명 수정할내용;
수정할내용
- 컬럼 추가/수정/삭제
  - 컬럼 추가(ADD): ADD 추가할컬럼명 데이터타입 (DEFAULT 기본값)
  - 컬럼 수정(MODIFY)
    - 데이터타입 수정: MODIFY 수정할컬럼명 바꾸고자하는 데이터타입
    - DEFAULT값 수정: MODIFY 수정할컬럼명 DEFAULT 바꾸고자하는 기본값
  - 컬럼 삭제: DROP COLUMN 삭제하고자하는 컬럼명
- 제약조건 추가/삭제 (제약조건 수정은 불가(수정하고자 하면 삭제 후 새롭게 추가))
  - 제약조건 추가
```
 ADD PRIMARY KEY(컬럼명);
 ADD FOREIGN KEY(컬럼명) REFERENCES 참조할테이블명(참조할컬럼명)
 ADD UNIQUE(컬럼명);
 ADD CHECK(컬럼명);
 MODIFY 컬럼명 NOT NULL;
```
  - 제약조건 삭제
    - PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK: DROP CONSTRAINT 제약조건명
    - NOT NULL: MODIFY 컬럼명 NULL
- 테이블명, 컬럼명, 제약조건명 변경
  - 컬럼명 변경: RENAME COLUMN 기존컬럼명 TO 바꿀컬럼명;
  - 제약조건명 변경: RENAME CONSTRAINT 기존제약조건명 TO 바꿀제약조건명;
  - 테이블명 변경: RENAME (기존테이블명) TO 바꿀테이블명;

🌱 DROP

객체를 삭제하는 구문
DDL인 바 ROLLBACK 불가능
어딘가에서 참조되고 있는 부모테이블은 삭제 불가능
- 만약 삭제하고 싶으면
  - 방법1) 자식테이블을 먼저 삭제한 후, 부모테이블 삭제
  - DROP TABLE 자식테이블; DROP TABLE 부모테이블;
  - 방법2) 부모테이블만 삭제하되, 맞물려있는 제약조건도 함께 삭제
  - DROP TABLE 부모테이블 CASCADE CONSTRAINT;

📗 소감

데이터베이스 객체를 기본적으로 생성/변경/삭제하고, 데이터 조작하는 문법을 다 배웠다. 필요할 때 잘 쓸 수 있게 어떤 것들이 가능한지 대략적으로나마 기억해두자

📗 homework:

-- 2021.11.10(수) 16H20~17H30 문제 풀기

-- DDL 계정에 기 존재하는 MEMBER 테이블 삭제하고 시작
-- DROP TABLE MEMBER;
ALTER TABLE MEMBER RENAME TO MEMBER_TEST;

-- 실습 1. (DDL 계정)
-- MEMBER 테이블을 생성하여 사용자 정보를 받을 수 있는 테이블 객체를 만들되,
-- 1) 회원 번호는 NUMBER형태로 기본키 설정하고,
-- 2) 회원 아이디는 중복 불가에 필수 입력 사항으로,
-- 3) 회원 비밀번호는 필수 입력 사항,
-- 4) 회원 이름,
-- 5) 성별('M', 'F'),
-- 6) 회원 연락처,
-- 7) 회원 생년월일 정보를
-- 받을 수 있는 컬럼을 가진 테이블을 생성하시오.
-- 단, 각 컬럼의 길이는 직접 판단하고,
-- 위에 생성된 테이블 기준으로
-- 회원 정보를 최소 5개 이상 삽입하여 확인하시오.
CREATE TABLE MEMBER(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) UNIQUE NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20),
    MEM_GENDER CHAR(3) CHECK (MEM_GENDER IN ('M', 'F')),
    MEM_PHONE VARCHAR2(15),
    MEM_BIRTHDAY DATE
);

SELECT * FROM MEMBER;

INSERT INTO MEMBER VALUES(1, 'user01', 'pass01', '강토미', 'F', '010-1234-5678', '1998-06-07');
INSERT INTO MEMBER VALUES(2, 'user02', 'pass01', '강판다', 'M', '010-2222-2222', NULL);
INSERT INTO MEMBER VALUES(3, 'user03', 'pass01', '강해피', NULL, NULL, NULL);
INSERT INTO MEMBER VALUES(4, 'user04', 'pass01', NULL, NULL, NULL, NULL);
INSERT INTO MEMBER VALUES(5, 'USER04', 'pass01', '강무민', 'M', NULL, NULL); -- UNIQUE 제약조건 검사 시 대/소문자 구별 -> 대/소문자가 다르면 다른 ID로 인식됨
INSERT INTO MEMBER VALUES(6, 'user06', 'pass01', '강스노크메든', 'F', NULL, TO_DATE(160409, 'YYMMDD')); -- 날짜 형식 RR로 바꾸고 싶음..

-- 실습 2. (KH계정)
-- 방명수 사원의 급여 인상 소식을 전해들은 '노옹철', '전형돈', '정중하', '하동운' 사원들이 자신들도 급여와 보너스를 인상해 달라며 파업을 하고 있다.
-- 노옹철, 전형돈, 정중하, 하동운 사원의 급여를 유재식 사원과 같은 급여, 보너스로 수정하는 UPDATE 구문을 작성하시오.
-- 단, 다중 열 서브쿼리로 구현하여 작성해 보시오.
SELECT * FROM EMP_SALARY; -- 데이터가 없음
DROP TABLE EMP_SALARY;

CREATE TABLE EMP_SALARY2
AS SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY, BONUS
   FROM EMPLOYEE;

SELECT * FROM EMP_SALARY2;
SELECT * FROM EMP_SALARY2 WHERE EMP_NAME IN ('유재식', '노옹철', '전형돈', '정중하', '하동운');

UPDATE EMP_SALARY2
SET (SALARY, BONUS) = (SELECT SALARY, BONUS
                       FROM EMP_SALARY2
                       WHERE EMP_NAME = '유재식') -- 서브쿼리의 RESULT SET = (3400000, 0.2)
WHERE EMP_NAME IN ('노옹철', '전형돈', '정중하', '하동운');

-- 실습 3. (KH계정)
-- 위와 같은 직원들의 급여 인상 소식이 메스컴으로 통해 퍼져나가 아시아 지역에 근무하는 전 직원들의 급여도 인상해 달라는 시위가 진행되고 있다.
-- 이에 난감한 운영 측은 급여는 인상이 불가하지만, 보너스는 0.25로 인상을 해주겠다고 시위 대표자인 선동일 사원과 합의를 보게 되었다.
-- EMP_SALARY 테이블에서 아시아 지역에 근무하는 모든 직원들의 보너스를 0.25로 인상/조정하는 UPDATE 구문을 작성하시오.
-- 해석1) 기존 보너스가 0.25를 초과한 아시아 지역에 근무하는 직원들의 보너스는 그대로 유지
CREATE TABLE EMP_SALARY3
AS SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY, BONUS
   FROM EMPLOYEE;

SELECT * FROM EMP_SALARY3;

UPDATE EMP_SALARY3
SET BONUS = 0.25
WHERE EMP_ID IN (SELECT EMP_ID
                 FROM EMPLOYEE
                 LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
                 LEFT JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
                 WHERE LOCAL_NAME LIKE ('ASIA%')) -- 서브쿼리의 RESULT SET = 16명
 AND NVL(BONUS, 0) < 0.25;

-- 기존 보너스가 0.25를 초과한 아시아 지역에 근무하는 직원들 조회 RESULT SET = 전지연, 선동일
SELECT EMP_ID, EMP_NAME
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
LEFT JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
WHERE LOCAL_NAME LIKE ('ASIA%')
 AND NVL(BONUS, 0) > 0.25;

-- 해석2) 아시아 지역에 근무하는 직원들의 보너스를 모두 일괄적으로 0.25로 조정/인상
CREATE TABLE EMP_SALARY4
AS SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY, BONUS
   FROM EMPLOYEE;

SELECT * FROM EMP_SALARY4;

UPDATE EMP_SALARY4
SET BONUS = 0.25
WHERE EMP_ID IN (SELECT EMP_ID
                 FROM EMPLOYEE
                 LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
                 LEFT JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
                 WHERE LOCAL_NAME LIKE ('ASIA%')); -- 서브쿼리의 RESULT SET = 18명

-- 아시아 외 지역에서 일하는 직원들의 이름 조회 -> 3명
SELECT EMP_NAME
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
LEFT JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
WHERE LOCAL_NAME NOT LIKE ('ASIA%');

-- 해석3) 기존 보너스가 없는, 아시아 지역 근무 직원들의 보너스는 0.25로 인상/조정하지 않음; 그 외 아시아 지역 근무 직원들 모두의 보너스는 0.25로 조정/인상
CREATE TABLE EMP_SALARY5
AS SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY, BONUS
   FROM EMPLOYEE;

SELECT * FROM EMP_SALARY5;

UPDATE (SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY, BONUS
        FROM EMP_SALARY5
        LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
        LEFT JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
        WHERE LOCAL_NAME LIKE ('ASIA%'))
SET BONUS = 0.25
WHERE BONUS IS NOT NULL;

저작자표시 비영리 변경금지

'back-end dev > database' 카테고리의 다른 글

[KH정보교육원] 24일차_SEQUENCE, DBMS 정리 (0)	2021.11.13
[KH정보교육원] 23일차_DCL, TCL, VIEW (0)	2021.11.11
[KH정보교육원] 21일차_DDL(CREATE) (0)	2021.11.09
[KH정보교육원] 20일차_SUBQUERY, RANK, CREATE (0)	2021.11.08
[KH정보교육원] 19일차_SUBQUERY (0)	2021.11.08