[DB] 8. DML (Data Manipulation Language)

DML

• DML은 테이블에 새로운 행을 추가 / 변경(수정) / 삭제 할 때 사용한다.
• INSERT / UPDATE / DELETE
• 데이터 조작 언어라고 생각하면된다.

 

INSERT

INSERT 기본 형식

INSERT의 기본 틀은 위와 같고 모든 컬럼에 데이터를 순서에 맞추어 넣는다면 컬럼은 생략할 수 있다.

INSERT에 data를 넣기 전에 데이터 타입을 봐야 한다. 데이터 타입은 desc table이름; 이렇게 실행하여 요약된 정보를 볼 수 있다.

-- insert 1
insert into departments values(280, '개발자', null, 1700);
-- insert 2
insert into departments(department_id, department_name, location_id) 
values(290, 'DBA', 1700);

위와 같이 컬럼명을 지정안하고 모든 데이터 넣기와 컬럼명을 지정하고 각 컬럼에 넣을 정보를 넣기 두가지 방법으로 insert를 사용 할 수 있다.

 

insert를 한다고 해서 실제 table에 commit된 것은 아니기 때문에 rollback으로 되돌릴 수 있다.

아래와 같이 insert를 해서 데이터를 넣어보았다.

insert into departments values(290, '디자이너', null, 1700);
insert into departments values(300, 'DB관리자', null, 1800);
insert into departments values(310, '데이터분석가', null, 1800);
insert into departments values(320, '퍼블리셔', 200, 1800);
insert into departments values(330, '서버관리자', 200, 1800);

🔸 ROLLBACK

rollback;

롤백 명령어를 실행하면 insert로 넣었던 모든 데이터가 제거된다. (원래 상태로 돌아감)

 

🔸 다른 테이블로부터 insert로 행을 복사해 올 수도 있다.

create table emps as (select * from employees where 1 = 2); --구조만 복사
-- where 1= 2 는 false결과로 emps를 만들때 data는 넣지 않고 구조만 복사되도록 하는 것.
select * from emps;

-- insert 3 (서브쿼리절) - 컬럼명 안적음
insert into emps (select * from employees where job_id = 'IT_PROG');
select * from emps; -- job_id가 it-prog인 사람들만 복사해서 정보가 들어간다.

-- insert 3 (서브쿼리절) - 컬럼명 적어줌
insert into emps(employee_id, first_name, last_name, email, hire_date, job_id)
                (select employee_id, first_name, last_name, email, hire_date, job_id from employees where job_id = 'FI_MGR');
select * from emps;

 

🔸 value구문안에 select절 넣어서 원하는 값을 넣어줄 수도 있다.

-- employee_id들 중 최대값에 100을 더해 insert하기
insert into emps(employee_id, first_name, last_name, email, hire_date, job_id)
values((select max(employee_id)+100 from employees), 'fname', 'lname', 'email', sysdate, 'test');

 

🔸 COMMIT

commit;

커밋 명령어를 실행해줘야 테이블에 적용되었다고 볼 수 있다.

 

UPDATE

UPDATE문의 기본 틀

❗ 주의 : UPDATE는 꼭 where절에 조건을 적어주자.

update emps set salary = 10000; -- 조건이 없으니 모든 salary의 정보가 10000으로 update되어 버렸다.

 

🔸 UPDATE 예시

-- 조건은 키를 이용하는 것이 좋다 (PK) pk는 중복이 안되므로 사용하기 좋다.
update emps set salary = 10000 where employee_id = 103; 
select * from emps;

-- 연산된 결과를 넣을 수도 있다.
update emps set salary = salary*1.1 where employee_id = 103; 
select * from emps;

-- 여러행을 update하는 경우
update emps set phone_number = '515.123.4566', manager_id = 102 where employee_id = 103; 
select * from emps;

 

🔸 UPDATE 응용 (서브쿼리) - 참고만

-- update의 서브쿼리 사용 (참고)
update emps 
set salary = (select salary from emps where employee_id = 104) 
where employee_id = 103;
select * from emps; -- 103번의 salary가 104번의 salary와 똑같아졌다

-- 여러행을 udpate
update emps 
set (salary, phone_number) 
= (select salary, phone_number from emps where employee_id = 104) 
where employee_id = 103;
select * from emps; -- 103번의 salary가 104번의 salary와 똑같아졌다

-- Diana와 department_id가 같은 사람들의 commission pct를 0.2로 바꿔라
update emps set commission_pct = 0.2
where department_id 
= (select department_id from emps where first_name = 'Diana');

 

DELETE

DELET의 기본틀

DELETE는 table자체를 날려먹을 수 있기때문에 매우 위험성이 높다.

행 삭제를 하기 전에 반드시 확인하는 습관을 가지자. 또한 키를 조건으로 줘서 삭제하는 것으로 하고 키를 꼭! 주자...

 

🔸 DELETE의 사용

-- table자체를 다 날려버릴 수도 있으니 반드시 키를 주고 (delete시 주의를 기울이자~)
delete from emps where employee_id = 208;
--아래와 같이 삭제도 가능하지만 키를 시용한 삭제가 훨씬 좋다.
delete from emps where job_id = 'FI_MGR';

 

🔸 DELETE의 서브쿼리

-- Diana와 department_id가 같은 사람들 지우기
delete from emps where departmet_id = (select department_id from emps where first_name = 'Diana');

 

🔸 다른 테이블에서 FK로 사용되고 있는 PK의 DELETE에 대하여..

-- departments에서 department_id 50번을 지우면 employees에 50번 department_id를 가진 애들이 붕 뜨기 때문에 삭제가 안된다.
-- 즉, 다른 테이블에서 fk로 가지고 있는 pk는 삭제가 불가능하다.
select * from departments; -- department_id가 pk
select * from employees; --department_id가 fk

delete from departments where department_id = 50;

 

merge

만일 회원 정보를 관리하는 table이 있다고 할때 매번 수정, 탈퇴등 데이터가 변하는 테이블을 따로 만들어 놓고 일주일 단위로 회원정보를 가지고 있는 테이블에 업데이트한다고 가정한다면 merge를 통해서 일주일마다 기존회원은 update를 신규회원은 insert로 data를 추가해 줄 수 있겠다.

merge의 구조

 

🔸 동일한 table구조를 가지고 있는 table의 정보를 옮기는 merge구문

-- 있으면 update, 없으면 insert
select * from employees where job_id = 'IT_PROG'; -- 있으면 update, 없으면 insert

-- 동일한 형식의 구조를 가진 테이블로부터 merge (기존에 있는 테이블에서 정보를 가지고 오기)
merge into emps a -- 타겟 테이블
    using (select * from employees where job_id = 'IT_PROG') b -- 조인구문
    on(a.employee_id = b.employee_id) -- 조인조건
when matched then -- 조건에 일치할 경우 타겟테이블에 실행
    update set
        a.phone_number = b.phone_number,
        a.hire_date = b.hire_date,
        a.salary = b.salary,
        a.commission_pct = b.commission_pct,
        a.manager_id = b.manager_id,
        a.department_id = b.department_id
when not matched then --조건에 일치하지 않는 경우 타겟테이블에 실행
    insert values -- 컬럼만 지정도 가능
        (b.employee_id, b.first_name, b.last_name,
        b.email, b.phone_number, b.hire_date, b.job_id,
        b.salary, b.commission_pct, b.manager_id, b.department_id);

 

🔸 직접 값을 넣고자 한다면 dual을 사용하여 merge 해줄 수 있다.

-- 직접 값을 입력하여 merge하기.
-- 다른 테이블에서 데이터를 비교하여 가져오는 것이 아니라, 직접 값을 넣고자 한다면 dual을 사용할 수 있습니다.
-- 단 ON절 본 키를 통한 연결이 들어가야 합니다.
merge into emps a -- 타겟 테이블
    using dual -- 조인구문
    on(a.employee_id = 1000) -- 조인조건
when matched then -- 조건에 일치할 경우 타겟테이블에 실행
    update set first_name = 'fname',
                last_name = 'lname'
when not matched then --조건에 일치하지 않는 경우 타겟테이블에 실행
    insert (employee_id,
            first_name,
            last_name,
            email,
            hire_date,
            job_id)
    values(1000, 'admin1', 'admin2', 'admin', sysdate, 'demin');

 

연습문제

 

--문제 1.
--DEPTS테이블의 다음을 추가하세요
create table depts as (select * from departments); --departments table을 복사
select * from depts;

insert into depts values(280, '개발', null, 1800);
insert into depts values(290, '회계부', null, 1800);
insert into depts values(300, '재정', 301, 1800);
insert into depts values(310, '인사', 302, 1800);
insert into depts values(320, '영업', 303, 1700);
select * from depts;

--문제 2.
--DEPTS테이블의 데이터를 수정합니다
--1. department_name 이 IT Support 인 데이터의 department_name을 IT bank로 변경
select * from depts where department_name = 'IT Support'; -- id 210

update depts 
set department_name = 'IT bank'
where department_name = 'IT Support';

select * from depts;

--2. department_id가 290인 데이터의 manager_id를 301로 변경
select * from depts where department_id = 290; -- null이었음

update depts
set manager_id = 301
where department_id =290;

--3. department_name이 IT Helpdesk인 데이터의 부서명을 IT Help로 , 매니저아이디를 303으로,
--지역아이디를 1800으로 변경하세요
select * from depts where department_name = 'IT Helpdesk'; -- 230/1700이었음

update depts
set department_name = 'IT Help', manager_id = 303, location_id = 1800
where department_name = 'IT Helpdesk';

select * from depts where department_name = 'IT Help'; 

--4. 이사, 부장, 과장, 대리 의 매니저아이디를 301로 한번에 변경하세요.
update depts 
set manager_id = 301
where department_id IN (290,300,310,320);

select * from depts;

--문제 3.
--삭제의 조건은 항상 primary key로 합니다, 여기서 primary key는 department_id라고 가정합니다.
--1. 부서명 영업부를 삭제 하세요
select * from depts where department_id =320;
delete from depts where department_id = 320;

--2. 부서명 NOC를 삭제하세요
select * from depts where department_id =220;
delete from depts where department_id = 220;

--문제4
--1. Depts 사본테이블에서 department_id 가 200보다 큰 데이터를 삭제하세요.
select * from depts where department_id > 200;

delete from depts 
where department_id = ANY(select department_id from depts where department_id > 200);

--2. Depts 사본테이블의 manager_id가 null이 아닌 데이터의 manager_id를 전부 100으로 변경하세요.
select * from depts;

update depts
set manager_id = 100
where manager_id is not null;

--3. Depts 테이블은 타겟 테이블 입니다.
--4. Departments테이블은 매번 수정이 일어나는 테이블이라고 가정하고 Depts와 비교하여
--일치하는 경우 Depts의 부서명, 매니저ID, 지역ID를 업데이트 하고
-- 새로유입된 데이터는 그대로 추가해주는 merge문을 작성하세요.

merge into depts a
    using (select * from departments) b
    on (a.department_id = b.department_id)
when matched then -- 기존데이터는 update
    update set
    a.department_name = b.department_name,
    a.manager_id = b.manager_id,
    a.location_id = b.location_id
when not matched then -- 새로유입된 데이터는 그대로 추가
    insert values
    (b.department_id, b.department_name, b.manager_id, b.location_id);

select * from depts;

--문제 5
--1. jobs_it 사본 테이블을 생성하세요 (조건은 min_salary가 6000보다 큰 데이터만 복사합니다)
select * from jobs where min_salary >= 6000;
create table jobs_it as (select * from jobs where min_salary >= 6000);
select * from jobs_it;

--2. jobs_it 테이블에 다음 데이터를 추가하세요
insert into jobs_it values('IT_DEV', '아이티개발팀', 6000, 20000);
insert into jobs_it values('NET_DEV', '네트워크개발팀', 5000, 20000);
insert into jobs_it values('SEC_DEV', '보안개발팀', 6000, 19000);

select * from jobs_it;

--3. jobs_it은 타겟 테이블 입니다
--4. jobs테이블은 매번 수정이 일어나는 테이블이라고 가정하고 jobs_it과 비교하여
-- min_salary컬럼이 0보다 큰 경우 기존의 데이터는 min_salary, max_salary를 업데이트 하고 새로 유입된
-- 데이터는 그대로 추가해주는 merge문을 작성하세요

select * from jobs_it;

merge into jobs_it a
    using(select * from jobs where min_salary > 0) b
    on(a.job_id = b.job_id)
when matched then
    update set
    a.min_salary = b.min_salary,
    a.max_salary = b.max_salary
when not matched then
    insert values (b.job_id, b.job_title, b.min_salary, b.max_salary);
    
select * from jobs_it;