Oracle 공부 기초 05 : DML insert(추가),update(수정),delete(삭제)

Oracle 공부 기초 04 : DML insert(추가), update(수정), delete(삭제)

- 이번 시간엔 DML 즉 데이터를 조작하는 명령어를 다루어 본다. 데이터를 저장하는 일련의 과정을 트랜잭션(Transaction)이라고 하는데 이 DML 명령들이 대표적인 트랜잭션을 다루는 명령어입니다. 여기서 앞서 배운 DDL과 차이를 고민 중이시라면 DML언어의 insert, update, delete, select문들은 테이블의 행(row)을 기준으로 삽입, 갱신, 삭제, 조회를 수행한다는 사실입니다.

 

- INSERT(추가)

--예제를 통해 행단위의 조작을 하는 DML 명령들을 익혀봅니다.
 
--[1] 샘플 테이블 생성
create table exam01(
deptno  number(2),   -- 부서번호
dname  varchar2(14),  -- 부서명
loc  varchar2(14)   -- 위치
);
 
select * from exam01;
 
--[2] 레코드 추가
-- * 레코드 추가 명령 사용1
-- insert into 테이블 이름( 필드명1, 필드명2, ....) values(값1, 값2, ...)
-- 값은 문자와 숫자를 구분하여 입력합니다.
-- 첫번째 방식은 필드명과 입력되어야 하는 값을 1:1 로 매핑하여 입력합니다.
-- 널값이 있어도 되는 필드는 필드명과 값을 생략하고 입력가능합니다
 
-- * 레코드 추가 명령 사용2
--insert into 테이블 이름 values (전체 column(필드, 열)에  넣을 VALUE 들);
-- 두번째 방식은 모든 필드에 해당하는 데이터를 모두 입력하는 경우로서 필드명들을 명령어 속에
-- 나열하지 않아도 되지만, 필드의 순서대로 빠짐없이 데이터가 나열되어야 하는 불편함도 있습니다.
insert into exam01(deptno, dname, loc) values(10, 'ACCOUNT', 'NEW YORK');
 
--[3] 데이터 입력 행 생략
insert into exam01 values(30, 'SALES', 'CHICHAGO');
 
--[4] null 값 입력
insert into exam01 values(40, 'OPERATION', null);
insert into exam01(deptno, dname) values(20, 'MARKETING');
select * from exam01;
 
select * from booklist;
insert into booklist(booknum, subject, makeyear, inprice, outprice, grade )
values('A001', '좀비아이',  2020, 12000, 2500, '전체');
insert into booklist values('A002', '일곱해의 마지막',  2020, 12150, 2000, '전체');
insert into booklist(booknum, subject, makeyear, inprice, outprice )
values('A003', '봉제인형 살인사건',  2020, 12000, 2500);
 
alter table booklist drop constraint check_grade;
 
insert into booklist values('A004', '쇼코의 미소',  2019, 10800, 2500, '성인전용');
insert into booklist values('A005', '가면산장 살인사건',  2018, 13320, 1500, '청소년');
insert into booklist values('A006', '나미야 잡화점의 기적',  2017, 13320, 2000, '성인전용');
insert into booklist values('A007', '유튜브영상편집',  2020, 20700, 2500, '전체');
insert into booklist values('A008', '이것이자바다',  2017, 30000, 3000, '성인전용');
insert into booklist values('A009', 'JSP웹프로그래밍',  2016, 25000, 2500, '청소년');
insert into booklist values('A010', '오라클데이터베이스',  2020, 30000, 3000, '전체');
 
select * from booklist;
select * from person;
INSERT INTO PERSON(PERSONNUM, PERSONNAME, PHONE, BIRTH, BPOINT,AGE,GENDER)
VALUES('가01', '홍길동', '010-1234-1234', '80/06/05', 240, 30, 'M');
INSERT INTO PERSON VALUES('가10', '박지성', '010-9876-1234', '81/04/04', 140, 29, 'F');
INSERT INTO PERSON VALUES('가02', '구자철', '010-5555-1234', '82/05/05', 230, 25, 'M');
INSERT INTO PERSON VALUES('가03', '지동원', '010-8787-1234', '83/06/06', 150, 35, 'F');
INSERT INTO PERSON VALUES('가04', '추신수', '010-5656-1234', '84/07/07', 240, 28, 'M');
INSERT INTO PERSON VALUES('가05', '류현진', '010-3333-1234', '83/08/08', 142, 27, 'F');
INSERT INTO PERSON VALUES('가06', '손흥민', '010-4444-1234', '82/09/23', 220, 23, 'M');
INSERT INTO PERSON VALUES('가07', '이청용', '010-6666-1234', '81/06/14', 440, 36, 'F');
INSERT INTO PERSON VALUES('가08', '이영표', '010-2580-1234', '82/03/16', 140, 31, 'M');
INSERT INTO PERSON VALUES('가09', '최지만', '010-7777-1234', '83/04/14', 340, 29, 'F');
alter table person drop column enterdate;
 
select * from in_out;
INSERT INTO IN_OUT VALUES('2020/06/25', 1, 'A002', '가03', 100);
INSERT INTO IN_OUT VALUES('2020/06/25', 2, 'A004', '가03', 200);
INSERT INTO IN_OUT VALUES('2020/06/25', 3, 'A005', '가05', 200);
INSERT INTO IN_OUT VALUES('2020/06/25', 4, 'A006', '가07', 300);
INSERT INTO IN_OUT VALUES('2020/07/01', 1, 'A006', '가02', 100);
INSERT INTO IN_OUT VALUES('2020/07/01', 2, 'A004', '가08', 200);
INSERT INTO IN_OUT VALUES('2020/07/01', 3, 'A003', '가09', 300);
INSERT INTO IN_OUT VALUES('2020/07/10', 1, 'A002', '가10', 200);
INSERT INTO IN_OUT VALUES('2020/07/10', 2, 'A003', '가02', 100);
INSERT INTO IN_OUT VALUES('2020/07/10', 3, 'A001', '가01', 50);--TIP: 하나의 문장이 끝나는 부분에만 ; 찍기

- COMMIT?

- 일반적으로 DBMS에서의 데이터 운영은 실시간으로 데이터 원본으로 작업하고 변경사항이 저장되는 것이 아니라 가상의 사본을 대상으로 작업하게 됩니다. 원래 이를 원본에 적용하기 위해서는 commit이라는 명령으로 원본에 적용해주어야  다른 접근에서 변경이 다만 이클립스에서의 데이터베이스 운영은 Auto Commit 이 적용되어 실행 즉시 적용되고 있습니다. 이런 부분들을 참고하여 다른 상황에선 반드시 마지막에 COMMIT 해야 함을 인지 해야 합니다.

이클립스의 오토 커밋 기능

- update(수정)

--[5] 데이터 변경- 수정(UPDATE)
--UPDATE 테이블명 SET 변경내용 WHERE 검색 조건
-- 명령문에 WHERE 이후 구문은 생략이 가능합니다.
-- 다만 이부분을 생략하면 모든 레코드를 대상으로해서 UPDATE 명령이 실행되어 모든 레코드가 수정됩니다.
-- 검색조건 : 필드명 (비교-관계연산자) 조건값   으로 이루어진 조건연산이며, 흔히 자바에서 if() 괄호안에
-- 사용했던 연산을 그대로 사용하는게 보통입니다.
-- ex) 나이가 26세 이상 -> WHERE AGE>=26
--  예제 로 익히자
-- exam01  테이블에서 deptno 값을 모두 30으로 수정
update exam01 set deptno = 30;
-- exam01 테이블에서 dname이 'ACCOUNT' 인 레코드의 deptno 를 10으로 수정
update exam01 set deptno = 10 WHERE dname='ACCOUNT';
-- exam01 테이블에서 dname이 'SALES' 인 레코드의 deptno 를 20으로 수정
update exam01 set deptno = 20 WHERE dname='SALES';
-- exam01 테이블에서 dname이 'OPERATION' 인 레코드의 deptno 를 30으로 수정
update exam01 set deptno = 30 WHERE dname='OPERATION';
-- exam01 테이블에서 dname이 'MARKETING' 인 레코드의 deptno 를 40으로 수정
update exam01 set deptno = 40 WHERE dname='MARKETING';
-- exam01 테이블에서 deptno이 30 인 레코드의 loc 를 'BOSTON' 으로 수정
update exam01 set loc = 'BOSTON' WHERE deptno=30;
-- exam01 테이블에서 deptno이 40 인 레코드의 loc 를 'LA' 으로 수정
update exam01 set loc = 'LA' WHERE deptno=40;
select * from exam01;
 
select * from booklist;
select * from person;
select * from in_out;
select * from emp;
 
-- booklist 테이블의 제목 '봉제인형 살인사건' 도서의 grade 를 '성인전용' 으로 수정
update booklist set grade='성인전용' where subject='봉제인형 살인사건';
-- emp 테이블의 모든 사원의 sal 값을 10% 씩 인상
update emp set sal  = sal * 1.1;  --  sal  = sal + (sal * 0.1)
-- emp 테이블에서 sal 값이 3000 이상인 사원의 급여 10% 삭감
update emp set sal = sal * 0.9 where sal >= 3000;
-- hiredate 가 2002년 이전인 사원의 급여를  + 2000  -> (2001-12-31 보다 작거나 같은)
update emp set sal = sal + 2000 where hiredate<'2001-12-31';
-- ename 이 j로 시작하는 사원의 job을  manager 로 변경
update emp set job='manager' where ename like 'j%';
-- person 테이블에서 bpoint 가 200이 넘는 사람만 bpoint*100 으로 변경
update person set bpoint = bpoint*100 where bpoint>=200;
-- in_out 테이블에서  할인금액이 100원이 넘으면 모두 할인 금액을 90으로 변경
update in_out set discount = 90 where discount>=100;

- LIKE + 와일드카드

EX) ename 이 j로 시작하는 사원의 job을 manager로 변경

update emp set job ='manager' where ename like 'j%';

SOL) 바로 풀지 못했기에 따로 다루어봅니다. 여기서 LIKE 연산자는 검색하려는 값을 정확하게 모를 경우, 검색할 수 있도록 와일드카드와 함께 사용하여 원하는 결과를 검색해줍니다.
와일드카드  --> 문자의 자릿수 시작 끝 등의 섬세한 검색 가능! (% 문자가 없거나 하나이상의 문자가 어떤 값이 와도 상관없습니다.) 
ex) - j% : j로 시작하는 단어

    - %j : j로 끝나는 단어

    - %j% : j를 가지고 있는 단어

    - _j__ : 4글자인데 두 번째 알파벳이 j인 단어

    - select * from emp where not ename like '%j%';   : j를 포함하지 않은 이름!

- delete(삭제)

-- 레코드의 삭제
-- delete from 테이블명 where  조건식
-- in_out 테이블에서 discount가 100이하이 레코드를 삭제 
 
delete from in_out where disco<100; 
-- where 절이 없으면 테이블 내의 모든 레코드를 삭제합니다
 
-- 삭제의 제한
delete from booklist where subject = '봉제인형 살인사건';
-- 해당 도서가 대여내역 (in_out) 에 존재하고    대여내역의 도서번호가  booklist 의 도서번호를
-- 참조하는 외래키가 설정되어 있다면  booklist 의 해당 도서는 삭제 될수 없습니다
-- 도서가 지워지는 순간 in_out 의 외래키 규칙에 위배되므로 삭제되는것 자체가 불가능하게 됩니다.
-- 삭제 되려면  참조하고 있는 외래키의 옵션을  도서가 삭제 되면 대여내역도 함께 삭제되는 옵션으로 변경하여야 합니다
 
-- 외래키 삭제
alter table in_out drop constraint fk1;
-- 새로운 외래키 추가
alter table in_out add constraint fk1 FOREIGN KEY(booknum)
REFERENCES booklist(booknum) on delete cascade;
-- on delete cascade : booklist 의 도서가 삭제되면   in_out 의 해당 도서 대여내역도 함께 삭제하는 옵션
delete from booklist where subject = '봉제인형 살인사건';
select * from booklist;
select * from in_out;
 
-- person 테이블에서  회원 한명을 삭제하면, in_out 테이블에서도 해당회원이 대여한 기록을 같이 삭제하도록
-- 외래키 설정을 바꿔주세요(외래키 제약조건 삭제 후 재생성)
 
alter table in_out drop constraint fk2;
alter table in_out add constraint fk2 FOREIGN KEY(personnum)
REFERENCES person(personnum) on delete cascade;
delete from person where personname = '홍길동';
select * from person;
select * from in_out;

 

정리

- DML 4개 중 SELECT를 제외한 3가지 기능을 배워봤다.

- 삽입 : insert into exam01 values(30, 'SALES', 'CHICHAGO');

- 수정 : update exam01 set deptno = 40 WHERE dname='MARKETING';

- 삭제 : delete from in_out where disco <100;<100 

-COMMIT의 개념과 이클립스의 오토 커밋

- 와일드카드 :  % J, J%,%J% 등등.. 의 차이