[Basic] SQL 데이터 조작어(DML)

1 데이터 조작어(DML: Data Manpulation Language)

: 테이블에 데이터 입력, 수정, 삭제, 병합하기 위한 명령어

2 입력

2-1 단일 행

• 한 번에 하나의 행 입력

insert into student
values (10110, '홍길동', 'hong', '1', '851011143098', '85/01/01', 
'041)630-3114', 170, 70, 101, 9903);

• Null 입력하기
  • 묵시적: 해당 칼럼값 생략
  • 명시적: 직접 Null, '' 입력
• 날짜 입력하기: 해당 시스템이 요구하는 형식으로. 오라클은 YY/MM/DD.(YYYY/MM/DD도 가능)
  • TO-DATE('2006/01/01', 'YYYY/MM/DD') '2006/01/01'이라는 글자를 날짜로 입력.
  • SYSDATE: 현재 날짜 데이터 반환

2-2 다중 행

• Values절 대신 서브쿼리의 검색 결과 집합을 한 번에 여러 행 동시 입력. => 기본 키, 고유 키, 제약조건 위반하지 않도록 주의.

INSERT INTO t-student(입력할 테이블 혹은 칼럼) 
SELECT * FROM student(들고올 테이블 혹은 칼럼)

2-2-1 INSERT ALL

• 2개 이상 테이블에 한거번에 데이터를 입력하는 방법. ALL은 서브쿼리의 컬럼 이름과 데이터가 입력되는 컬럼 이름이 동일해야 한다.
• ALL: 컬럼 이름 일치하면 그냥 무조건(unconditional) 다 입력.

INSERT ALL
INTO height_info VALUES (studno, name, height) (입력할 테이블 혹은 칼럼)
INTO weight_info VALUES (studno, name, weight)
SELECT studno, name, height, weight (들고올 칼럼)
FROM student
WHERE grade >= '2';

2-2-2 WHEN, THEN => 조건 부여

INSERT ALL
WHEN height > 170 THEN (조건 TRUE인 행들만)
	INTO height_info VALUES (studno, name, height) (입력할 테이블 혹은 칼럼)
WHEN weight > 70 THEN (조건 TRUE인 행들만)
	INTO weight_info VALUES (studno, name, weight)
SELECT studno, name, height, weight (들고올 칼럼)
FROM student
WHERE grade >= '2';

2-2-3 INSERT FIRST

• 위의 조건에 맞아서 위의 것에 들어간 행은 제외된 채로 아래로 넘어감.

INSERT FIRST
WHEN height > 170 THEN (170 넘는 사람 다 들어감)
	INTO height_info VALUES (studno, name, height) (입력할 테이블 혹은 칼럼)
WHEN weight > 70 THEN (170 넘는 사람 빼고, 70kg 넘는 사람 다 들어감)
	INTO weight_info VALUES (studno, name, weight)
SELECT studno, name, height, weight (들고올 칼럼)
FROM student
WHERE grade >= '2';

2-2-4 PIVOTING INSERT

• 여러 개의 칼럼을 하나로 통합하는 등의 변환 작업에 쓰임.
• 예를 들면, 요일별 판매 실적 데이터를 5개 칼럼으로 저장됐는데, 하나의 칼럼으로 통합. => 구분 위한 요일 칼럼을 추가해서 1, 2, 3, 4, 5 로 표기.

INSERT ALL
sales_data VALUES(sales_no, week_no, '1', sales_mon)  (1이 월요일 표기. 기존의 sales_mon이란 칼럼의 값을 가져옴)
sales_data VALUES(sales_no, week_no, '2', sales_tue) (2이 화요일 표기. 기존의 sales_tue이란 칼럼의 값을 가져옴)
sales_data VALUES(sales_no, week_no, '3', sales_wed)
sales_data VALUES(sales_no, week_no, '4', sales_thu)
sales_data VALUES(sales_no, week_no, '5', sales_fri)
SELECT sales_no, week_no, sales_mon, sales_tue, sales_wed, 		sales_thu, sales_fri
FROM sales;

3 수정

UPDATE student (업데이트할 테이블, 칼럼)
SET (grade, deptno) = (SELECT grade, deptno
						FROM student
                       	WHERE studno = 10103) (SET 뒤에 바꿀 값. 이 경우 subquery로 여러 개 들고 옴)
where position = '조교수' (조건. 칼럼 이름, 표현식, 상수, 서브쿼리, 비교연산자 등)

• 서브쿼리를 이용해서 한거번에 여러 칼럼을 업데이트할 때는, 데이터 타입과 칼럼 수 일치. (칼럼 이름은 달라도 됨. 입력 순서 맞으면)
  
  

4 삭제

delete (from 생략 가능) student (테이블)
where studno = 20103; (조건)

• 조건 입력 안하면 테이블 전체 다 삭제

delete from student
where deptno = (select deptno
				from department
               	where dname = '컴퓨터공학과');

• 조건에 subquery 이용해서 한거번에 여러 행 내용 삭제 가능. 이 경우 where 절 칼럼과 subquery의 칼럼 이름 달라도, 데이터 타입과 칼럼 수 일치

5 MERGE

• 구조 같은 두 테이블 비교해서 하나의 테이블로 합치기 => whre 조건 절이 맞는 행은 새 값으로 update, 맞는 행이 없으면 insert로 입력

merge into professor p(합쳐질 결과 테이블)
using professor_temp f(위에 합칠 테이블)
on (p.profno = f.profno) (합칠 조건)
when matched then (조건이 맞을 땐)
	update set p.position = f.position (이 값 업데이트)
when not matched then (조건이 안맞을 땐)
	insert values(f.profno, f.name, f.userid, f.position, f.sal, f.hiredate 
    f.comm, f.deptno); (이 값 새로 입력)

6 트랜잭션(Transaction)

• 여러 개의 SQL 명령문을 하나의 논리적 작업 단위로 처리하는 개념.

begin; (트랜잭션 시작)

(데이터수정 ~~)

savepoint before_update; (세이브 포인트 지정)

(작업~~)

rollback; => savepoint로 롤백
commit; => 영구 저장

• commit: 정상 종료. 하나의 트랜잭션에서 실행된 모든 명령문의 처리 결과를 하드 디스크에 영구히 저장.
  • 해당 트랜잭션에 할당된 CPU, 메모리 같은 자원이 해제. commit 안하고 계속 사용하면 성능 저하된다. 왜냐하면 rollback했을 때 돌아갈 수 있게 내역 다 저장.
  • 여러 명이 동시 작업할 때, 자기가 한 작업 결과 다른 사람 접근 못함. => commit 하면 그때부터 공유.
• rollback: 실행된 sql 명령문 처리 결과 모두 취소하고 돌아감. 할당된 자원 해제, 트랜잭션 강제 종료.
• savepoint: 중간에 임시 저장.

7 시퀀스(Sequence)

• 유일한 식별자로, 기본 키 값을 자동으로 생성하기 위한 일련번호 생성 객체.
  예를 들면, 게시글 번호수, 주민번호 등처럼.
  => 여러 테이블에 공유 가능

CREATE SEQUENCE s_seq (시퀀스 객체 이름)
INCREMENT by 1 (증가치. default는 1)
START WITH 1 (시작값, default는 1)
MAXVALUE 100 (최댓값)
MINVALUE 1 (만약 cycle이면 최댓값 뒤에 다시 최솟값으로 돌아옴)
CYCLE/NOCYCLE (둘 중 하나 선택)
CACHE 20; (메모리 캐쉬하는 시퀀스 개수. default는 20)

7-1 NEXTVAL/CURRVAL

시퀀스이름.NEXTVAL
시퀀스이름.CURRVAL

• nextval은 시퀀스에서 다음 번호 생성. currval은 시퀀스에서 생성된 현재 번호 확인.

CREATE SEQUENCE my_sequence (1에서 1씩 증가하는 시퀀스 생성)
	START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

INSERT INTO my_table(id, name)
VALUES (my_sequence.NEXTVAL, 'john') (테이블에 시퀀스 다음값을 아이디로 넣음. 2가 들어감)

INSERT INTO my_table(id, name)
VALUES (my_sequence.NEXTVAL, 'tom') (테이블에 시퀀스 다음값을 아이디로 넣음. 3이 들어감)

SELECT my_sequence.CURRVAL (3이 나옴)
FROM dual;

7-2 시퀀스 정의 변경

ALTER SEQUENCE 시퀀스이름
INCREMENT ~~
START WITH ~~
MAXVALUE ~~
MINVALUE ~~
CYCLE/NOCYCLE 
CACHE ~~; 

로 똑같이 변경.

7-3 시퀀스 삭제

DROP SEQUENCE 시퀀스이름;