[Oracle] DML

-data management language
-테이블에 레코드를 조작(추가, 수정, 삭제, 조회)하기 위한 명령어들
-insert(추가), update(수정), delete(삭제), select(조회 및 선택)

 

1. 샘플 테이블 생성

create table exam01(
	deptno number(2), --부서번호
	dname varchar2(14), --부서명
	LOC VARCHAR2(14) --위치
);

 

2. 레코드 추가(Insert)

-값은 문자('123')와 숫자(123)를 구분하여 입력함

-- 방법 첫번째
-- 필드명과 입력되어야 하는 값을 1:1로 매핑하여 입력함
-- 널값이 있어도 되는 필드는 필드명, 
-- 또는 기본값이 있는 필드명은 필드명과 값을 생략하고 입력가능함
insert into 테이블이름(필드명1, 필드명2,...) values(값1, 값2,...)

-- 방법 두번째
-- 모든 필드에 해당하는 데이터를 모두 입력하는 경우로서 
-- 필드명들을 명령어 속에 나열하지 않아도 되지만
-- 필드의 순서대로 빠짐없이 데이터가 나열되어야 하는 불편함도 있음
insert into 테이블이름 valuse(전체 column(필드, 열)에 넣을 값들);

-- 첫번째 방식의 레코드 추가
insert into exam01(deptno, dname, loc) 
values(10, 'ACCOUNT', 'NEW YORK');

-- 두번째 방식의 레코드 추가
insert into exam01 values(30,'SALES','CHICHAGO');


-- 두가지 방법 모두 null 값을 입력할 수 있음
insert into exam01(deptno, dname) 
values(20, 'MARKETING'); --첫번째 방법
insert into exam01 values(40,'OPERATION',null); --두번째 방법

 

 

-- booklist 테이블에 10개의 레코드를 추가
-- num은 시퀀스를 이용함 book_seq

insert into booklist( num, subject, makeyear, inprice, rentprice, grade )
values(book_seq.nextVal , '좀비아이',  2020, 12000, 2500, 'all');

insert into booklist values( book_seq.nextVal , '일곱해의 마지막',  2020, 12150, 2000, 'all');
insert into booklist values( book_seq.nextVal , '봉제인형 살인사건',  2020, 12000, 2500, '13');
insert into booklist values( book_seq.nextVal , '쇼코의 미소',  2019, 10800, 2500, '18');
insert into booklist values( book_seq.nextVal , '가면산장 살인사건',  2018, 13320, 1500, '13');
insert into booklist values( book_seq.nextVal , '나미야 잡화점의 기적',  2017, 13320, 2000, '18');
insert into booklist values( book_seq.nextVal , '유튜브영상편집',  2020, 20700, 2500, 'all');
insert into booklist values( book_seq.nextVal , '이것이자바다',  2017, 30000, 3000, '18');
insert into booklist values( book_seq.nextVal , 'JSP웹프로그래밍',  2016, 25000, 2500, '13');
insert into booklist values( book_seq.nextVal , '오라클데이터베이스',  2020, 30000, 3000, 'all');

 

-- 같은 방식으로 memberlist에 7명 데이터를 추가. 
-- member_seq를 이용

insert into memberlist
values(member_seq.nextVal,'추신수','010-5656-1234','84/07/07',240,'20/10/01','M',28);
insert into  memberlist
values(member_seq.nextVal, '류현진','010-3333-1234','83/08/08',142,'20/10/01','F', 27 );
insert into  memberlist
values(member_seq.nextVal, '손흥민','010-4444-1234','82/09/23',220,'20/10/01','M',23 );
insert into  memberlist
values(member_seq.nextVal, '이청용','010-6666-1234','81/06/14',440,'20/10/01','F', 36);
insert into  memberlist
values(member_seq.nextVal,'이영표','010-2580-1234','82/03/16',140,'20/10/01','M', 31);
insert into  memberlist
values(member_seq.nextVal,'최지만','010-7777-1234','83/04/14',340,'20/10/01','F', 29);
insert into  memberlist
values(member_seq.nextVal,'김하성','010-8888-1234','80/05/14',340,'20/10/01','F', 29);

 

-- rentlist 테이블도 rent_seq를 이용해서 10개의 데이터를 추가
-- 유의할점: bnum값과 mnum값은 booklist의 num값과 memberlist의 num값 
            중에 있는 것만 입력 가능함
-- insert into rentlist values( '2021/12/01', rent_seq.nextVal, 15, 15, 100);  
-- 실패
-- 오류내용 : integrity constraint (SCOTT.R_FK2) violated - parent key not found

insert into  rentlist values('2021/12/01', rent_seq.nextVal,  3 , 1 , 100);  -- 성공
insert into  rentlist values('2021/12/02', rent_seq.nextVal,  6 , 1 , 100);
insert into  rentlist values('2021/12/03', rent_seq.nextVal,  7 , 2 , 200);
insert into  rentlist values('2021/12/03', rent_seq.nextVal,  8 , 3 , 300);
insert into  rentlist values('2021/12/04', rent_seq.nextVal,  9 , 7 , 100);
insert into  rentlist values('2021/12/04', rent_seq.nextVal,  10 , 8 , 300);
insert into  rentlist values('2021/12/01', rent_seq.nextVal,  4 , 4 , 100);
insert into  rentlist values('2021/12/02', rent_seq.nextVal,  5 , 5 , 200);
insert into  rentlist values('2021/12/05', rent_seq.nextVal,  1 , 9 , 100);
insert into  rentlist values('2021/12/05', rent_seq.nextVal,  2 , 6 , 200);

 

3. 데이터 수정(Update)

-명령문에 where 이후 구문은 생략이 가능함

-다만 이부분을 생략하면 모든 레코드를 대상으로해서 update 명령이 실행되어 모든 레코드가 수정도미

-검색조건: 필드명(비교-관계연산자) 조건값으로 이루어진 조건연산이며 흔히 자바에서 if() 괄호 안에 사용했던 연산을 그대로 사용하는게 보통(논리연산 &&, ||: and or)

update 테이블명 set 변경내용 where 검색조건

update memberlist set age=30, gender='F' where num=10; 
-- 회원번호가 10인 회원의 나이를 30으로 수정

 

-- 데이터 수정

-- 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이 'MARKETING'인 레코드의 deptno를 40으로 수정
update exam01 set deptno=40 where dname='MARKETING';

-- exam01 테이블에서 dname이 'OPERATION'인 레코드의 deptno를 30으로 수정
update exam01 set deptno=30 where dname='OPERATION';

-- exam01 테이블에서 deptno 40인 레코드의 loc를 'LA'로 수정
update exam01 set loc='LA' where deptno=40;

 

-- 연습하기
-- 테이블 생성
CREATE TABLE DEPT (
	DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY, 
	DNAME VARCHAR2(14) ,
	LOC VARCHAR2(13) 
) ;
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

CREATE TABLE EMP(
	EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
	ENAME VARCHAR2(10),
	JOB VARCHAR2(9),
	MGR NUMBER(4),
	HIREDATE DATE,
	SAL NUMBER(7,2),
	COMM NUMBER(7,2),
	DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
 );
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),
800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),
1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),
1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),
2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),
1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),
2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),
2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-7-1987','dd-mm-yyyy'),
3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),
5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),
1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-7-1987','dd-mm-yyyy'),
1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),
950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),
3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),
1300,NULL,10);

CREATE TABLE BONUS (
	 ENAME VARCHAR2(10) ,
	 JOB VARCHAR2(9)  ,
	 SAL NUMBER,
	 COMM NUMBER
 ) ;

CREATE TABLE SALGRADE( 
	 GRADE NUMBER,
	 LOSAL NUMBER,
	 HISAL NUMBER 
 );
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);

COMMIT

-- emp 테이블의 모든 사원의 sal 값을 10% 씩 인상
update emp set sal = sal * 1.1;
update emp set sal = sal + (sal* 0.1);

-- emp 테이블에서 sal 값이 3000 이상인 사원의 급여 10% 삭감
update emp set sal = sal * 0.9 where sal>=3000;
update emp set sal = sal - (sal*0.1) where sal>=3000;

-- emp 테이블의 hiredate 가 2002년 이전인 사원의 급여를  + 2000
update emp set sal = sal + 2000 where hiredate < '2002-01-01';
update emp set sal = sal + 2000 where hiredate <= '2001-12-31';

-- emp 테이블의 ename 이 j로 시작하는 사원의 job을  manager 로 변경
update emp set job='MANAGER' where ename like 'J%';
update emp set job='MANAGER' where ename like '%J';  
                                        -- j로 끝나는 이름 검색
update emp set job='MANAGER' where ename like '%J%' ; 
                                        -- j를 포함하는 이름 검색

-- memberlist 테이블에서 bpoint가
-- 200이 넘는 사람만 bpoint*100 으로 변경
update memberlist set bpoint=bpoint*100 where bpoint>=200;

-- rentlist 테이블에서  할인금액이 100원이 넘으면 
-- 모두 할인 금액을 90으로 변경
update rentlist set discount = 90 where discount>=100;

-- 등급이 '18'인 책은 rentprice을 10% 인상, 
-- 책 제목에 끝에 18+를 추가...
update booklist set rentprice=rentprice*1.1, subject = subject 
|| '18+' where grade='18';
--  오라클의 문자 이어붙이기 연산 : ||

-- 성별이 널인 것을 모두 M으로 수정
update memberlist set gender='M' where gender is null
update memberlist set gender='M' where gender = null

 

4. 레코드 삭제(Delete)

delete from 테이블명 where 조건식

-- rentlist 테이블에서 discount가 100이하인 레코드를 삭제
delete from rentlist where discount<100;
-- where 절이 없으면 테이블 내의 모든 레코드를 삭제함
select*from rentlist;

-- 수정과 삭제의 제한
-- '봉제인형 살인사건' 도서가 booklist에 있고 
-- 그 도서를 대여한 적이 있어서 
-- rentlist에 그 도서의 도서 번호가 존재할 때
-- booklist에서 해당도서를 삭제하거나, 관리상의 목적으로 
-- 도서번호를 수정하려고 한다면 오류남

-- 봉제인형 살인사건 도서가 rentlist에 대여목록으로 존재하므로
-- 외래키의 참조무결성에 위배됨
-- 따라서 이 삭제 또는 수정 명령은 에러를 발생함
delete from booklist where num=3;
update booklist set num=10003 where num=3;
-- intergrity constraint (SCOTT>FK1) violated - child record found

-- 해결방법 1.
-- rentlist 테이블에 해당 도서의 대여목록 레코드를 모두 삭제한 후
-- booklist 테이블에서 해당 도서를 삭제해야 함
delete from rentlist where bnum=3;
delete from booklist where num=3;

-- 해결방법 2.
-- 외래키 제약조건을 삭제한 후 다시 실행
-- 외래키 제약조건을 다시 생성하는데, 생성시에 옵션을 추가해서 
-- 참조되는 값이 삭제되면 참조하는 값도 같이 삭제되게 구성함

-- 외래키 삭제
alter table renlist drop constraint r_fk1;
-- 새로운 외래키 추가
alter table renlist add constraint r_fk1 fordign key(bnum)
references booklist(num) on delete cascade;
-- on delete cascade: booklist의 도서가 삭제되면 
-- rentlist의 해당 도서 대여 내역도 함께 삭제하는 옵션

-- 참조되는 값의 삭제가 아니라 수정은 적용되지 않음
-- booklist와 memberlist 테이블의 num은 대여기록이 있을 경우 
-- 수정이 아직 불가능함
-- 이를 해결하기 위해서 외래키 설정시 
-- on update cascade 옵션을 추가하면 될듯 하지만,
-- 이는 오라클에서 허용하지 않음

-- 연습하기
-- memberlist 테이블에서 회원 한명을 삭제하면 
-- rentlist 테이블에서도 해당회원이 대여한 기록을
-- 같이 삭제하도록 외래키 설정(외래키 제약조건 삭제 후 재생성)
alter table rentlist drop constraint r_fk2;
alter table rentlist add constraint r_fk2 foreign key(mnum)
references memberlist(num) on delete cascade;

 

---

5. 선택(Select)

1) scott 사용자가 관리하고 있는 테이블 목록

select*from tab; --단순 검색
select*from tabs; --상세 검색

2) 특정 테이블의 구조 조회(필드 리스트/데이터 형식)

desc dept; -- 커멘드창(sqlplust)에서 확인 요망
desc memberlist; -- 커멘드창(sqlplust)에서 확인 요망
desc rentlist; -- 커멘드창(sqlplust)에서 확인 요망

 

3) select 사용방법

-- 특정 테이블의 모든 필드 표시 
select * from 테이블명; 

-- 모든 컬럼(필드명)이 아닌, 필요한 필드만 조회
select * 필드명1, 필드명2, ... from 테이블명;

-- 필드명에 별칭을 부여해서 출력
select 필드명 as "부여할별칭" from 테이블명;

-- 중복 제거
select distinct 필드명 from 테이블명

-- 검색 조건의 추가
select 테이블명 from where 조건

-- 연습하기

-- 아래와 같이 연산식을 써서 연산된 결과를 필드로 조회하고자 할 때 
-- as와 함께 만들어진 필드명을 지어주기도 함
select empno || '-' || ename as empInfo from emp;

-- 필드명에 공백이 있거나 기술하기 어려운 필드명일때도 
-- as로 별칭을 붙이기도 함. 병칭에도 공백이 있다면 ""로 묶어서 써줌
select empno as 사원번호, ename as "사원 성명" from emp

-- 입고 가격이 20000원 이상인 book 목록
select*from booklist where inprice>=20000

-- 이름이 '홍'으로 시작하는 회원의 모든 회원정보 출력
select*from memberlist where name like '홍%' 

-- 1983년도 이후로 태어난 회원의 모든 회원정보
select*from memberlist where birth>='1981-01-01'

-- 사은포인트(BPOINT)가 250점 이상이고 
-- 1982년 이후로 태어난 회원의 모든 회원정보(and, or 연산자 사용)
select*from memberlist where bpoint>=250 and birth>='1982-01-01'

-- 이름이 '용'으로 끝나는 회원의 정보
select* from memberlist where name like '%용'

-- 도서 제목에 '이'가 포함되는 도서 정보
select* from booklist where name like '%이%'

-- memberlist에서 성별이 NULL이 아닌 회원의 이름과 전화번호
select name, phone from memberlist where gender is not null;
select name, phone from memberlist where gender <> null;

-- booklist에서 도서 제목에 두번째 글자가 '것'인 도서 정보
select*from booklist where subject like '_것%'

 3.1) 조건식(any, some, all, in)

-- where 절에서 사용하는 그룹내 해당 요소 찾기 함수들

-- 1) any
select*from emp where deptno=any(10,20,40);
-- any() 괄호 안에 나열된 내용 중 
-- 어느 하나라도 해당하는 것이 있다면 검색 대상으로 함

-- 2) some조건식 any와 동일 in과도 동일
select*from emp where deptno=some(10,20,40);
select*from emp where deptno in(10,20,40);

-- 3) all
select*from emp where deptno=all(10,20,40);
-- 괄호 안의 모든 값이 동시 만족해야하는 조건이므로 
-- 해당하는 레코드가 없을 때가 대부분
select*from emp where deptno<>all(10,20,40);
select*from emp where deptno<>10 and deptno<>20 and deptno<>40) 
-- 두번째 사용예처럼 모두와 다를 때를 위해 사용되곤 함. 사용빈도수 낮음

-- 4) 논리조건식 not
select*from emp where deptno not in(10,20,40);
select*from emp where deptno <> all(10,20,40);
-- in() 안에 있는 것과 하나도 매칭되지 않은 값이 검색대상

 3.2) 정렬(sort)

-where 구문 뒤에, 또는 구문의 맨 끝에 oder by 필드명 [desc]라고 기술

-select 명령의 결과를 특정 필드값의 오름차순이나 내림차순으로 정렬하라는 명령

-asc: 오름차순 정렬, 쓰지 않으면 기본 오름차순 정렬로 실행함

-desc: 내림차순 정렬, 내림차순 정렬을 위해서는 반드시 필드명 내에 써야하는 키워드

-- emp 테이블에서 
-- sal이 1000 이상인 데이터를 ename의 오름차순으로 정렬하여 조회
select*from emp where sal>=1000 order by ename --asc는 생략 가능

-- sal이 1000 이상인 데이터를 ename의 내림차순으로 정렬하여 조회
select*from emp where sal>=1000 order by ename desc;

-- 검색조건 없이 모든 레코드를 job으로 내림차순 정렬
select*from emp order by job desc;

-- job으로 내림차순 정렬 후 같은 job 사이에서는 
-- 순서를 hiredate의 내림차순으로 정렬
select*from emp order by job desc, hiredate  desc;
-- 두개 이상의 정렬 기준이 필요하다면 
-- 위와 같이 (,)로 구분해서 두 가지 기준을 지정해주며
-- 위의 예제로 봤을 때 job으로 1차 내림차순 정렬하고, 
-- 같은 job 값들 사이에 hiredate로 내림차순 정렬함

 3.3) 기타

-- 부서번호가 10이 아닌 사원(아래 두 문장은 같은 의미의 명령)
select*from emp where not (deptno=10);
select*from emp where deptno<>10;

-- 급여가 1000달러 이상, 3000달러 이하
select*from emp where sal>=1000 and sal<=3000;
select*from emp where sal between 1000 and 3000;

-- 특정 필드값이 널인 레코드 또는 널이 아닌 레코드
select*from emp where comm is null 
                         --comm 필드가 null인 레코드
select*from emp where comm is not null 
                         --comm 필드가 null이 아닌 레코드

-- select와 from 사이에는 문자연산 및 산술연산도 쓸 수 있음
-- 사원의 연봉 출력
select deptno, ename, comm, sal*12 as 연봉 from emp;
-- sal 값이 null 경우 위의 계산에 오류 발생. 이를 해결하기 위한 방법
select deptno, ename, comm, nvl(sal,1000)*12 as 연봉 from emp;
-- nvl 함수는 null 값을 다른 값으로 바꿔주는 내장함수