DAY05. SQL Basic DML (INSERT, UPDATE, DELETE), Sequence

데이터 조작어 (DML)

- INSERT, UPDATE, DELETE : commit 대상 (구조변경)

- SELECT : commit 대상 아님 (단순 검색. 구조 변경 없음)

- 단일 query / sub query 이용

INSERT/UPDATE/DELETE - 메인쿼리

SELECT절 - 1차 서브쿼리

​

테이블 삭제

DROP TABLE dept01 PURGE;

​

테이블 생성

CREATE TABLE DEPT01(
DEPTNO NUMBER(4),
DNAME VARCHAR2(30),
LOC VARCHAR2(20)
);

​

1. 레코드 삽입(insert)

1) 기본 qeury 이용

INSERT INTO dept01(deptno, dname, loc)
VALUES(10, 'ACCOUNTING', 'NEW YORK');

​

모든 칼럼에 자료 입력 : 칼럼명 생략

INSERT INTO DEPT01
VALUES (20, 'RESEARCH', 'DALLAS');
SELECT * FROM dept01;

​

​

1. EMP 테이블의 4개 칼럼의 구조를 이용하여 SAM01 테이블을 생성하시오.

CREATE TABLE sam01
AS
SELECT empno, ename, job,sal FROM emp
WHERE 1=0;

​

2. SAM01 테이블에 다음과 같은 데이터를 추가하시오.

INSERT INTO SAM01 VALUES(1000, 'APPLE', 'POLICE', 10000);
INSERT INTO SAM01 VALUES(1001, 'BANANA', 'NURSE', 15000);
INSERT INTO SAM01 VALUES(1002, 'ORANGE', 'DOCTOR', 25000);

SELECT * FROM sam01;
DESC sam01;

​

암시적(묵시적) NULL 입력

INSERT INTO dept01 (deptno, dname)
VALUES (30, 'SALES'); -- loc칼럼 생략, null값이 자동으로 삽입

​

​명시적 NULL 입력

INSERT INTO dept01
VALUES (40, 'OPERATIONS', NULL);

INSERT INTO dept01
VALUES (50, '', 'CHICAGO'); -- ''안의 공백도 문자취급되므로 공백도 없어야함

SELECT*FROM dept01;

​

​

3. 문제 1에서 생성한 SAM01 테이블의 JOB 칼럼이 NULL값을 갖는 2개의 행을 추가하시오.

SELECT * FROM sam01;
INSERT INTO sam01 VALUES(1030, 'VERY', '', 25000);
INSERT INTO sam01 VALUES(1040, 'CAT', '', 2000);

​

​

2) sub query를 이용해 테이블 삽입

1. 서브 쿼리로 데이터를 삽입하기 위해서 우선 테이블을 생성하되 데이터는 복사하지 않고 빈 테이블만 생성

CREATE TABLE DEPT02
AS
SELECT * FROM DEPT WHERE 1=0;

​

레코드 추가 (AS키워드 없음. DDL과 DML의 차이)

INSERT INTO dept02
SELECT*FROM dept;

​

4. 문제 1에서 생성한 SAM01 테이블에 서브 쿼리문을 사용하여 EMP 에 저장된 사원 중 10번 부서 소속사원의 정보를 추가하시오.

SELECT*FROM sam01;

INSERT INTO sam01
SELECT empno, ename, job, sal
FROM emp
WHERE deptno=10;

​

 

​

​2. 다중 테이블에 다중 행 입력하기

1) 다중 테이블 생성

CREATE TABLE emp_hir
AS
SELECT empno, ename, hiredate
FROM emp
WHERE 1=0;

CREATE TABLE emp_mgr
AS
SELECT empno, ename, mgr
FROM emp
WHERE 1=0;

​

​2) 다중 레코드 삽입

INSERT ALL
INTO emp_hir VALUES(empno, ename, hiredate) -- 2. 각각 테이블에 뿌려서 삽입할 수 있음
INTO emp_mgr VALUES(empno, ename, mgr)
SELECT empno, ename, hiredate, mgr -- 1. 서브쿼리에서 출력한 칼럼을 각각 테이블에 뿌려서 삽입할 수 있음
FROM EMP
WHERE DEPTNO=20;

SELECT * FROM emp_hir;
SELECT * FROM emp_mgr;

​

​

​3. 레코드 수정 (UPDATE)

CREATE TABLE emp01 -- emp테이블의 내용+구조를 모두 옮겨옴
AS
SELECT * FROM emp;

​

WHERE절 없음 -> 전체 레코드 수정

1. 모든 사원의 부서번호를 30번으로 수정

UPDATE emp01
SET deptno=30;

​

2. 모든 사원의 급여를 10% 인상시키는

UPDATE emp01
SET sal = sal*1.1;

​

3. 3. 모든 사원의 입사일을 오늘로 수정

UPDATE emp01
SET hiredate = sysdate;

​​

WHERE절 있음 -> 특정 레코드 수정

1. 부서번호가 10번인 사원의 부서번호를 30번으로 수정

UPDATE emp01
SET deptno=30
WHERE deptno=10;

​

2. 급여가 3000 이상인 사원만 급여를 10% 인상

UPDATE emp01
SET sal = sal * 1.1
WHERE sal >= 3000;

​

3. 1987년에 입사한 사원의 입사일을 오늘로 수정한다. 사원의 입사일을 오늘로 수정한 후에 테이블 내용 보기

UPDATE emp01
SET hiredate = SYSDATE
WHERE SUBSTR(hiredate, 1, 2)='87';

​

5. SAM01 테이블에 저장된 사원 중 급여가 10000 이상인 사원들의 급여만 5000원씩 삭감하시오.

UPDATE sam01
SET sal = sal-5000
WHERE sal>=10000;

​​

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

1. SCOTT 사원의 부서번호 20번, 직급은 MANAGER로 한꺼번에 수정

UPDATE emp01
SET deptno=20, JOB='MANAGER'
WHERE ename='SCOTT';

​

2. SCOTT 사원의 입사일자는 오늘, 급여를 50, 커미션을 4000 수정

UPDATE emp01
SET hiredate = SYSDATE, sal=50, comm=4000
WHERE ename='SCOTT'; -- 여러 개의 수정은 콤마로 구분

​

​

서브 쿼리를 이용한 레코드 수정하기

1. 20번 부서의 지역명을 10번 부서의 지역명으로 변경하기 위해서 서브 쿼리문을 사용

UPDATE dept01
SET loc=(SELECT loc FROM dept01 WHERE deptno=10) -- 서브쿼리 조건을
WHERE deptno=20; -- 메인쿼리에서 받아 20번 부서의 위치를 바꾼다

​

6. 서브 쿼리문을 사용하여 EMP 테이블에 저장된 데이터의 특정 컬럼만으로 구성된 SAM02 테이블을 생성하시오.

CREATE TABLE sam02
AS
SELECT ename, sal, hiredate, deptno FROM emp;

 

7. 생성 후 DALLAS 에 위치한 부서 소속 사원들의 급여를 1000 인상하시오.

SELECT * FROM sam02;

UPDATE sam02
SET sal=sal+1000
WHERE deptno=(SELECT deptno FROM dept WHERE loc='DALLAS'); -- DALLAS에 해당하는 부서 번호는 20.

​

​

서브 쿼리를 이용한 두 개 이상의 칼럼 값 변경

/* 형식 UPDATE table_name

SET (column_name1, column_name2, …) = (sub_query)

WHERE 조건

*/

UPDATE dept01
SET (dname, loc)=(SELECT DNAME, LOC FROM DEPT01 WHERE DEPTNO=10)
WHERE DEPTNO=50;
-- [해석] 10번 부서의 이름과 위치를 50번 부서의 이름과 위치값과 동일하게 수정하겠다.

​

8. 서브 쿼리문을 사용하여 SAM02 테이블의 모든 사원의 급여와 입사일을 이름이 KING인 사원의 급여와 입사일로 변경하시오.

UPDATE sam02
SET (sal, hiredate) = (SELECT sal,hiredate FROM sam02 WHERE ename='KING');

​

 

​​

4. 레코드 삭제 (DELETE)

1) 단일 query

DELETE FROM dept01
WHERE deptno = 30;

​

2) 전체 레코드 삭제

DELETE FROM dept01;

​

9. SAM01 테이블에서 직책이 정해지지 않은 사원을 삭제하시오.

DELETE FROM sam01
WHERE job IS NULL;

​

​

​

2) sub query 이용

사원 테이블에서 부서명이 SALES인 사원을 모두 삭제

DELETE FROM emp01
WHERE deptno=(SELECT deptno FROM dept WHERE dname='SALES'); -- 부서번호 30번
SELECT*FROM emp01;

​

DB반영

commit;

​

명령 이전으로 복원

ROLLBACK; -- 단, commit 이전 DML명령만 복원됨

 

 

 

 

 

Sequence (시퀀스)

CREATE SEQUENCE deptno_seq -- deptno_seq를 객체라고 표현

INCREMENT BY 1 -- 1씩 증가하는

START WITH 1; -- 1부터 시작하고

IN~과 ST~의 순서는 상관없음

​

1. 시퀀스 생성

CREATE SEQUENCE emp_seq
START WITH 1
INCREMENT BY 1
MAXVALUE 100000;
-- 시작 값이 1이고, 1씩 증가하고, 최댓값이 100000이 되는 시퀀스 emp_seq를 생성한다.

​

2. 테이블 생성

DROP TABLE emp01 PURGE;
CREATE TABLE emp01( -- 테이블 생성
empno NUMBER(4) PRIMARY KEY, --기본키
ename VARCHAR2(10),
hiredate DATE
);

​

3. 레코드 삽입 : 시퀀스 이용

INSERT INTO EMP01
VALUES(emp_seq.NEXTVAL, 'JULIA' , SYSDATE); -- (emp_seq.NEXTVAL) -> (객체.멤버)의 관계
-- [해석] 기본 키 설정이 된 테이블 empno의 칼럼 값을 시퀀스로 대체한다.
-- NEXTVAL에 시퀀스 값 1이 입력된다.

INSERT INTO EMP01
VALUES(emp_seq.NEXTVAL, 'HONG' , SYSDATE); -- empno의 값이 +1 되어 2로 설정되었음

​

4. 데이터 사전 뷰 : 시퀀스 정보 확인

SELECT * FROM USER_SEQUENCES; -- 시퀀스 확인
SELECT * FROM USER_TABLES; -- 테이블 확인

​​

5. 시퀀스 삭제 (PURGE 명령어 필요X)

DROP SEQUENCE emp_seq;

 

 

 

실습

CREATE SEQUENCE dept_deptno_seq -- 시퀀스 생성
START WITH 10
INCREMENT BY 10
MAXVALUE 30;

​

테이블 생성 (구조만)

CREATE TABLE dept_exam
AS
SELECT * FROM dept
WHERE 1=0;

​

레코드 추가

INSERT INTO dept_exam VALUES (dept_deptno_seq.NEXTVAL, '인사과', '서울시');
INSERT INTO dept_exam VALUES (dept_deptno_seq.NEXTVAL, '개발부', '대전시');
INSERT INTO dept_exam VALUES (dept_deptno_seq.NEXTVAL, '총무부', '서울시');

INSERT INTO dept_exam VALUES (dept_deptno_seq.NEXTVAL, '총무부', '서울시'); -- error 발생. WHY? 최대값이 30이므로

​​

6. 시퀀스 수정

ALTER SEQUENCE dept_deptno_seq MAXVALUE 1000; -- 최대값을 1000으로 수정
INSERT INTO dept_exam VALUES (dept_deptno_seq.NEXTVAL, '총무부', '서울시'); -- error 발생하지 않음

​

​ 7. 데이터 사전 뷰 : 시퀀스 구조 확인

SELECT*FROM USER_SEQUENCES;