Ex3. 아래 정보를 professor 테이블에 입력하세요. (날짜 데이터 입력) 교수번호(profno): 5001 교수이름(name): 이광희 ID: Gwang POSITION: 정교수 PAY: 1000 입사일: 2023년 12월 21일
> INSERT INTO professor (profno, name, id, position, pay, hiredate) VALUES (5001, '이광희', 'Gwang', '정교수', 1000, '2023-12-21')
* 날짜 형식
유닉스(리눅스)용 오라클: DD-MON-YY
윈도우용 오라클: YYYY-MM-DD
* 유닉스용(리눅스) 오라클(DD-MON-YY) 과 윈도우용 오라클(YYYY-MM-DD)이 다르기 때문에 유닉스용 오라클에서는 ① 날짜 형식을 미리 변경한 후 '2023-10-02' 형식으로 입력하든지 ② 날짜 부분에 TO_DATE 함수를 사용하여 TO_DATE('2023-10-23', 'YYYY-MM-DD') 형식으로 입력해야 한다.
날짜 형식을 변경하는 명령어
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS';
# 사용 예1: 날짜 데이터 입력하기
앞에서 몇 차례 언급이 된 부분인데, 윈도우와 유닉스 계열은 날짜 형태가 다르므로 날짜 데이터를 입력할 때 형태를 잘 맞춰야 합니다. 아래의 예는 유닉스 계열의 오라클에서 윈도우용 오라클에서 사용하는 날짜 형태로 입력하는 예제입니다. 아래 정보를 professor 테이블에 입력하세요.
교수번호(profno): 5001 교수이름(name): James Bond ID(id): Love_me POSITION(position): a full professor PAY(pay): 500 입사일(hiredate): 2014년 10월 23일
> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS'; > INSERT INTO professor (profno, name, id, position, pay, hiredate) VALUES (5001, 'James Bond', 'Love_me', 'a full professor', 500, '2014-10-23');
Null 값 입력하기
자동 Null 값 입력하기: 데이터를 입력할 때 컬럼에 값을 안 주면 자동으로 Null이 삽입된다.
데이터를 조회하면 no1과 no3는 동일하게 정수와 소수 및 음수까지 이상없이 출력되지만 no2의 경우 소수점 이하가 표시되지 않는다는 것을 알 수 있다.
INSERT와 서브 쿼리를 사용하여 여러 행 입력하기
professor 테이블을 복사하여 professor2 테이블 생성하기 > CREATE TABLE professor2 SELECT * FROM professor WHERE 1 = 2; // 테이블은 복사하되, 데이터는 입력되지 않도록 하기 위함
> SELECT * FROM professor2 no row selected
서브쿼리를 사용하여 여러 건의 데이터를 한 번에 입력할 수 있습니다.
> CREATE TABLE pofessor2 SELECT * FROM professor;
> SELECT * FROM professor2; 17 row selected
이처럼 서브 쿼리를 사용하여 여러 데이터를 가져와서 입력하는 방법을 ITAS라고 한다.
서브쿼리를 사용하여 테이블을 생성할 때, 복사한 테이블(professor)과 복사할 테이블(professor2)은 컬럼의 개수와 데이터 형이 동일해야 한다.
INSERT ALL을 이용한 여러 테이블에 여러 행 입력하기
1. prof_1, prof_2 테이블 생성 > CREATE TABLE prof_1 ( profno NUMBER, name VARCHAR(25));
> CREATE TABLE prof_2 ( profno NUMBER, name VARCHAR(25));
2. 다중 테이블에 다중 행 입력하기 INSERT ALL 명령문은 서브 쿼리의 결과 집합을 조건 없이 여러 테이블에 동시에 입력하기 위한 명령문
> INSERT ALL INSERT EMP_HIR VALUES(EMPNO, ENAME, HIREDATE) INSERT EMP_MGR VALUES(EMPNO, ENAME, MGR) SELECT EMPNO, ENAME, HIREDATE, MGR FROM EMP WHERE DEPTNO=20;
사용 예1: 다른 테이블에 한 번에 데이터 입력하기
> INSERT ALL INTO p_01 (no, name) VALUES (1, 'AAA') INTO p_02 (no, name) VALUES (2, 'BBB')
> SELECT * FROM dual;
사용 예2: 다른 테이블의 데이터를 가져와서 입력하기
professor 테이블에서 교수 번호가 1000번에서 1999번까지인 교수의 번호와 교수 이름은 prof_1 테이블에 입력하고 교수 번호가 2000번에서 2999번까지인 교수의 번호와 이름은 prof_2 테이블에 입력하세요.
> INSERT ALL WHEN profno BETWEEN 1000 AND 1999 THEN INTO prof_1 VALUES (profno, name) WHEN profno BETWEEN 2000 AND 2999 THEN INTO prof_2 VALUES (profno, name) SELECT profno, name FROM professor;
6 row selected
사용 예3: 다른 테이블에 동시에 같은 데이터 입력하기
prof_3과 prof_4 테이블의 데이터를 TRUNCATE로 삭제한 후 professor 테이블에서 교수 번호가 3000번에서 3999번인 교수들의 교수 번호와 이름을 prof_3 테이블과 prof_4 테이블에 동시에 입력하세요.
> TRUNCATE TABLE prof_3; > TRUNCATE TABLE prof_4;
> INSERT ALL INTO prof_3 VALUES (profno, name) INTO prof_4 VALUES (profno, name) SELECT profno, name FROM professor WHERE profno BETWEEN 2000 AND 3999;
UPDATE : 데이터 변경
UPDATE는 기존의 데이터를 다른 데이터로 변경할 때 사용하는 방법이다.
UPDATE 문장에서 WHERE절을 누락시키지 않도록 주의해야 한다.
문법
UPDATE table SET column = value WHERE 조건;
# 사용 예1
Professor 테이블에서 직급이 조교수(assistant professor)인 교수들의 BONUS를 100만원으로 인상하세요.
> UPDATE professor SET BONUS = 100 WHERE position = 'assistant professor';
# 사용 예2
professor 테이블에서 'Sharon Stone' 교수의 직급과 동일한 직급을 가진 교수들 중 현재 급여가 250만원이 안 되는 교수들의 급여를 15% 인상하세요.
> UPDATE professor SET pay = pay * 1.15 WHERE position = ( SELECT position FROM professor WHERE name = 'Sharon Stone') AND pay < 250;
# 사용 예3
-- 테이블의 특정 행만 변경 실습 -- Q. 1983년에 입사한 사원의 입사일을 오늘로 수정 --*HINT : SUBSTR
> UPDATE EMP01 SET HIREDATE = SYSDATE WHERE SUBSTR(HIREDATE, 1, 2) = ‘83’;
해석: SUBSTR은 SQL에서 문자열의 일부분을 추출하는 함수입니다. SUBSTR(문자열, 시작 위치, 길이)와 같은 형태로 사용하며, 여기서 "시작 위치"는 1부터 시작합니다.
SUBSTR(HIREDATE, 1, 2) = '83'는 HIREDATE 필드의 값에서 첫 번째 위치부터 두 글자를 추출하여 그 값이 '83'인지 비교하는 조건입니다.
HIREDATE 필드가 날짜 형식(DATE type)인 경우 SUBSTR 함수를 직접 사용할 수 없습니다. 문자열 함수를 날짜에 바로 적용하려고 하면 오류 발생
HIREDATE 값을 문자열로 변환한 후에 SUBSTR 함수를 적용
> UPDATE EMP01 SET HIREDATE = SYSDATE WHERE SUBSTR(TO_CHAR(HIREDATE, 'YYYY'), 3, 2) = '83';
해석: TO_CHAR 함수를 사용하여 HIREDATE 값을 'YYYY' 형식의 문자열로 변환한 후, 그 결과에서 세 번째 위치부터 두 글자('83')를 추출하여 비교하는 방식으로 작동
> DELETE FROM dept2 WHERE dcode >= 9000 AND dcode =< 9100;
또는
> DELETE FROM dept2 WHERE dcode BETWEEN 9000 AND 9100;
* DELETE문은 데이터를 삭제한다고 알고 있지만, 사실 데이터는 삭제되지 않고 해당 블록에 그대로 남아 있다.
1) DELETE문은 해당 데이터가 사용하고 있던 파일의 저장 공간은 반납하지 않고 데이터만 삭제하는 구문
2) 데이터는 삭제되지 않고 해당 블록에 그대로 남아있으며 BBED 등의 툴을 이용하면 DELETE된 데이터도 복구 가능
3) DELETE를 한 후에 테이블의 크기를 확인해보면 크기가 줄어들지 않고 그대로인 것을 확인 가능 (저장공간을 반납하지 않기 때문에 용량이 줄어들지 않는다는 의미)
4) 만약 모든 데이터가 지워졌는데 용량까지 줄이고 싶다면 REORG 작업을 별도로 해줘야 한다.
실습1: 테이블 생성
> CONN / AS sysdba;
> CREATE TABLE scott.test01 (no NUMBER, name VARCHAR(20), addr VARCHAR(20));
실습2: 위에서 생성했던 scott.test01 테이블에 데이터 추가
> BEGIN FOR i IN 1..1000 LOOP INSERT INTO scott.test01 VALUES (i, DBMS_RANDOM.STRING('A', 19), DBMS_RANDOM.STRING('B', 19)); END LOOP; COMMIT; END; /
실습3: 데이터 건수와 테이블 용량 측정
> SELECT COUNT(*) FROM scott.test01;
1000 건 입력 용량은 여전히 28MB
MERGE : 데이터 병합
MERGE는 여러 테이블의 데이터를 병합하는 명령어이다.
MERGE의 경우 문법이 다른 명령어와 비교하면 복잡한 편이므로 잘 공부하도록 하자.
문법 : table1과 table2의 내용을 합쳐 table1에 모음
MERGE INTO table1 USING table2 ON (병합 조건절) WHEN MATCHED THEN // 병합 조건절이 만족한다면 UPDATE 또는 DELETE 실행 UPDATE SET 업데이트 내용 DELETE WHERE 조건 WHEN NOT MATCHED THEN // 병합 조건절이 만족하지 않다면 INSERT 실행 INSERT VALUES(컬럼 이름);
* MERGE 구문이 수행될 때 집계 테이블(table1)의 데이터와 신규 테이블(table2)의 데이터의 내용을 비교해서 확인하기 때문에 집계 테이블에 데이터가 많아질수록 MERGE 작업의 수행속도가 늦어진다는 특징이 있다.
Ex1. 일별 판매 테이블인 pt_01과 pt_02 테이블이 있고 판매 집계 테이블인 p_total 테이블이 있다. 매일 새벽에 일별 판매 테이블과 판매 집계 테이블을 MERGE 한다고 가정하고 MERGE하는 SQL을 작성하세요.
1. MERGE 작업 전 테이블 조회 >SELECT * FROM pt_01;
>SELECT * FROM pt_02;
>SELECT * FROM p_total; no row selected
2. pt_01 테이블과 p_total 테이블 병합 > MERGE INTO p_total "total" USING pt_01 p01 ON (total.판매번호 = p01.판매번호) WHEN MATCHED THEN UPDATE SET total.제품번호 = p01.제품번호 WHEN NOT MATCHED THEN INSERT VALUES (p01.판매번호, p01.제품번호, p01.수량, p01.금액);
3, pt_02 테이블과 p_total 테이블 병합 > MERGE pt_02 "p02" ON (total.판매번호 = p02.판매번호) WHEN MATCHED THEN UPDATE SET total.제품번호 = p02.제품번호 WHEN NOT MATCHED THEN INSERT VALUES (p02.판매번호, p02.제품번호, p02.수량, p02.금액);
4. MERGE 후 작업 테이블 조회 > SELECT * FROM p_total;
TRANSACTION
논리의 작업단위이며, 여러가지 DML 작업들을 하나의 단위로 묶어둔 것을 말한다.
-> 데이터베이스에서는 데이터의 일관성과 무결성을 보장하기 위해서는 반드시 트랜잭션 관리가 필요
-> COMMIT, ROLLBACK, SAVEPOINT가 있음
COMMIT
: 하나의 트랜잭션인 여러 DML 명령어를 정상적으로 데이터베이스에 반영하겠다는 의미
=> 즉, 하나의 트랜잭션 과정이 정상적으로 종료하게 된다.
=> 이때 COMMIT 명령을 사용하지 않아도 자동으로 COMMIT이 되는 경우가 있다. (DDL(CREATE, DROP 등) 및 DCL 명령문을 수행하면 자동 COMMIT이 되고 데이터베이스를 정상적으로 종료하였을 경우 자동 COMMIT이 된다.)
ROLLBACK
: 잘못된 명령이나 잘못된 데이터를 입력하는 등 문제가 발생했을 때 하나의 트랜잭션을 취소하는 명령어
=> 이때 데이터베이스가 비정상적으로 종료된다면 자동으로 ROLLBACK이 된다.
SAVEPOINT
: 하나의 트랜잭션을 작게 분할하여 저장하는 기능을 한다.
=> SAVEPOINT는 [SAVEPOINT SAVEPOINT이름]을 지정하고 저장된 SAVEPOINT에 [ROLLBACK TO SAVEPOINT이름]문을 이용하여 해당 SAVEPOINT까지 ROLLBACK 할 수 있다.
=> SAVEPOINT는 하나의 트랜잭션에서 여러 개 지정할 수 있다.
실습
1. 실습용 테이블 생성 > CREATE TABLE test ( NO NUMBER(3), NAME VARCHAR(10), AGE NUMBER(3), GENDER VARCHAR(6));
2. 데이터 추가, SAVEPOINT, ROLLBACK 3. ROLLBACK 테스트
UPDATE 조인 : 다른 테이블과 조인을 하는UPDATE
일반적인 UPDATE 구문이 아닌 다른 테이블과 JOIN을 하는 UPDATE
UPDATE 문에 조인이 발생하는 경우는 ① WHERE 절에만 다른 테이블과 조인을 하는 경우와 ② WHERE 절과 SET 절 모두 다른 테이블과 조인을 하는 경우이다.
실습
1. employees 테이블과 departments 테이블이 있다고 가정합니다. departments 테이블에서 departments_name을 업데이트 하세요. -> employee.id가 1인 직원의 departments_name을 '새 부서'로 업데이트 employees 테이블과 departments 테이블은 departments_id로 JOIN됨
2. orders 테이블과 customers 테이블이 있습니다. customers 테이블의 customer_name을 업데이트 하세요. -> order_id가 101인 주문의 customer_name을 '새 고객 이름'으로 업데이트함.
3. ptoducts 테이블과 suppliers 테이블이 있다고 가정합니다. products 테이블과 supplier_name을 업데이트 하세요. -> product_id가 2023인 제품의 supplier_name을 '새 공급 업체 이름'으로 업데이트 함
실습1
1. INSERT, TRANSACTION - 학생 명단에 새 학생 추가 아래 조건에 맞춰 새로운 학생을 추가하세요. 학생의 이름은 나형진, 나이는 20세 반은 3반입니다. TRANSACTION을 사용하여 수행한 뒤 COMMIT 해주세요. HINT: 시퀀스 (일련번호 자동생성 객체) - SEQ_STUDENTS 해당 시퀀스의 다음 값을 가져오기 - .NEXTVAL
1-1. 아래 조건에 맞춰 새로운 학생을 추가해주세요. 학생의 숫자는 ‘9410’, ID는 ‘asdas’ 학생의 이름은 ‘나형진’, 학년은 3학년, 주민등록번호는 ‘7510231901812’ 입니다. 아래 조건에 맞춰 새로운 학생을 추가
1-2. 이전 문제에서 '나형진’ 학생의 GRADE가 3인 경우에만 ID를 ‘updatedID123’로 변경하고, JUMIN의 뒷자리 6자리를 ‘000000’으로 바꾸세요. * HINT : CONCAT, SUBSTR
1-3. STUDENT 테이블에서 GRADE가 3이고 주민등록번호 뒷자리가 3으로 시작하는 학생을 지워주세요. * HINT : SUBSTR
실습2
2. UPDATE JOIN, DELETE - 주문 정보 수정 및 취소 아래 조건에 맞춰 BLACKLIST가 YES로 설정된 모든 고객의 주문을 취소하세요. 그리고 주문 정보와 고객 정보를 JOIN 하여 '나형진'이 주문한 모든 제품의 이름을 '반품'으로 업데이트 하세요. HINT: 고객 ID 조회 -> 고객 주문 삭제 -> TABLE JOIN 후 '나형진'이 주문한 제품 찾기 -> '반품' UPDATE
실습3
3. MERGE - 재고 정보 갱신 아래 조건에 맞춰 새로 도착한 제품 정보 (NEW_ARRIVALS)를 기존의 재고 정보(STOCK)에 병합하세요. HINT: 제품 ID로 일치하는 데이터 찾기 -> 일치하는 데이터가 있으면 STOCK 테이블 QUANTITY 갱신 -> 일치하는 데이터가 없으면 STOCK 테이블에 추가
실습4
<< 도서 관리 시스템 만들기 >> # 테이블 생성 *PRIMARY KEY : DB 테이블에서 테이블 각 행을 고유하게 식별할 수 있는 컬럼을 지정하는 제약 조건
1. 데이터 입력 새로운 도서 3권을 도서 테이블에 추가 한 권의 도서를 대출 테이블에 추가
2. 데이터 조회 재고량이 6 이하인 도서의 제목과 저자를 조회 아직 반납되지 않은 도서의 제목과 대출일을 조회
3. 데이터 수정 특정 도서의 재고량을 1 감소(도서 ID = 1) 아직 반납되지 않은 도서 중 하나의 반납일을 현재 날짜로 설정
4. 데이터 삭제 재고량이 7인 도서를 도서 테이블에서 삭제
5. 트랜잭션 새로운 도서를 추가하고, 바로 그 도서를 대출하는 작업을 한 트랜잭션 처리 (도서 추가와 대출 작업이 모두 성공하거나 실패해야 함)
*AUTOCOMMIT OFF : SQL 명령을 실행할 때마다 자동으로 트랜잭션이 COMMIT됨
