day32-DB: sql

1. DML

-- Insert(행 삽입)
DESC departments;

--lst
INSERT INTO departments VALUES(280,'개발자',null,1700);

--2nd
INSERT INTO departments(department_id , department_name , location_id ) VALUES (290,'DBA',1700);

SELECT * FROM departments;

rollback; -- 되돌리기

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);

-- 실습을 위한 가짜 테이블
create table emps as(select * from employees WHERE 1=2); --구조만 복사

SELECT * FROM emps;

--3nd(서브쿼리절) , values를 서브쿼리절로 대체
INSERT INTO emps (SELECT * FROM employees WHERE job_id='IT_PROG');

DESC emps;

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;

INSERT INTO emps (employee_id , first_name, last_name , email, hire_date, job_id)
VALUES ((SELECT MAX(employee_id) FROM emps) + 100 ,'test' ,'test' ,'test' ,sysdate , 'test');

INSERT INTO emps (employee_id , first_name, last_name , email, hire_date, job_id)
VALUES ((SELECT MAX(employee_id) + 100 from emps ), 'test' ,'test' ,'test' ,sysdate , 'test');

--------------------------------------------------------------------------------
commit;
select * from emps;

-- update
update emps set salary = 10000; -- 전체가 변경됨 rollback 
rollback;

update emps set salary = 10000 where employee_id = 103; -- 값, employee_id가 103번인 사람 salary update
update emps set salary = salary * 1.1 where employee_id = 103; -- 연산된 결과
update emps set phone_number = '515.123.4566', manager_id = 102 where employee_id = 103; -- 여러 컬럼일 경우 ,로 나열

-- update 서브쿼리
update emps set salary = (select salary from emps where employee_id = 104) where employee_id = 103; -- 104번의 salary 값 = 103번의 salary 값

-- 103번의 salary, phone_number = 104번과 일치
update emps set (salary, phone_number) = (select salary, phone_number 
                                            from emps
                                           where employee_id = 104)
where employee_id = 103; 

-- first_name이 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');

--------------------------------------------------------------------------------
select * from emps;
rollback;
-- delete
delete from emps where employee_id = 208; -- 키를 이용한 삭제가 더 좋음
delete from emps where job_id = 'FI_MGR';

-- delete 서브쿼리
delete from emps where department_id = (select department_id from emps where first_name = 'Diana');

select * from departments; -- department_id가 pk
select * from employees;
delete from department where department_id = 50; -- pk를 참조하는 employees 내용이 붕 떠서 지워지지 않음

--------------------------------------------------------------------------------
rollback;
select * from emps;
delete from emps where employee_id in (104, 105, 106, 107, 108, 208);
-- merge (있으면 update, 없으면 insert)
select * from employees where job_id = 'IT_PROG';

-- 동일한 형식의 구조를 가진 테이블로 부터 merge
MERGE INTO emps a -- (타켓 테이블)
    using (select * from employees where job_id = 'IT_PROG') b -- 조인 구문(적용할 데이터), where 조건이 빠지면 employees 전체 
    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); -- 조회된 값대로 들어감
         
select * from emps;
-- 다른 테이블에서 데이터를 비교하여 가져오는 것이 아니라, 직접 값을 넣고자 한다면 dual을 사용할 수 있습니다.
-- (단, on절은 키를 통한 연결이 들어가야 합니다.)
merge into emps a
    using dual
    on (a.employee_id = 1000) -- 얘가 있으면 matched then, 없으면 not matched then
when matched then
    update set first_name = 'test1',
               last_name = 'test2'
when not matched then
    insert(employee_id,
         first_name,
         last_name,
         email,
         hire_date,
         job_id)
    values(1000, 'admin1', 'admin2', 'admin3', sysdate, 'admin');
        rollback;
        
-- when matched then on절에 쓰인 키가 매치 됐을 때 어떤 작업을 수행할건지
-- when not mathched then 매치 되는게 없을 땐 insert 구문 사용

 

2. 예제

--문제 1.
--DEPTS테이블의 다음을 추가하세요

create table depts as select * from departments;
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);

rollback;
--문제 2.
--DEPTS테이블의 데이터를 수정합니다

--1. department_name 이 IT Support 인 데이터의 department_name을 IT bank로 변경
update depts set department_name = 'IT BANK' where department_name = 'IT Support';
--2. department_id가 290인 데이터의 manager_id를 301로 변경
update depts set manager_id = '301' where department_id = 290;
--3. department_name이 IT Helpdesk인 데이터의 부서명을 IT Help로 , 매니저아이디를 303으로, 지역아이디를
--1800으로 변경하세요
update depts set department_name = 'IT Help' where department_name = 'IT Helpdesk';
update depts set manager_id = 303 where department_name = 'IT Helpdesk';
update depts set location_id = 1800 where department_name = 'IT Helpdesk';
select * from depts;
--4. 이사, 부장, 과장, 대리 의 매니저아이디를 301로 한번에 변경하세요.
update depts set manager_id = 301 where department_id = 290;
update depts set manager_id = 301 where department_id = 300;
update depts set manager_id = 301 where department_id = 310;
update depts set manager_id = 301 where department_id = 320;
-- update depts set manager_id = 301 where department_id in(290, 300, 310, 320);
--문제 3.
--삭제의 조건은 항상 primary key로 합니다, 여기서 primary key는 department_id라고 가정합니다.
--1. 부서명 영업부를 삭제 하세요
delete from depts where department_id = 320;
--2. 부서명 NOC를 삭제하세요
delete from depts where department_id = 220;
-- delete from depts where department_id = (select department_id from depts where ...);

--문제4
--1. Depts 사본테이블에서 department_id 가 200보다 큰 데이터를 삭제하세요.
delete from depts where department_id > 200;
select * from depts;
--2. Depts 사본테이블의 manager_id가 null이 아닌 데이터의 manager_id를 전부 100으로 변경하세요.
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 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보다 큰 데이터만 복사합니다)
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문을 작성하세요
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.job_title = b.job_title, -- 업데이트 되면 title도 변경될 수 있으니까
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);

 

3. 트랜잭션

-- 트랜잭션
-- commit(안하면 프로그램에서 조회하면 안나옴), rollback;
-- 오토커밋 여부(현재는 꺼져있음)
show autocommit;

-- 오토커밋 on/off
set autocommit on; -- delete하면 되돌릴 수 없음
set autocommit off;

rollback; -- 마지막 commmit 시점
commit; 

delete from depts where department_id = 10;

savepoint delete10;

delete from depts where department_id = 20;

savepoint delete20;

-- savepoint로 돌아가기
rollback to savepoint delete10; -- 10번행으로 돌아감
select * from depts;

-- commit을 하면 이전으로 돌아가지 못함
commit; 

-- 프로세스에서 트랜잭션으로 묶여야 하는 상황이 발생: 주문 → 결제

 

4. 테이블 생성(DDL)

 

테이블 - 편집 - DDL 누르면 확인 가능

-- DDL문, create문
create table dept2 (
    dept_no NUMBER(2), -- 컬럼명, 숫자 타입이고 자리수 정수 2자리 수까지
    dept_name varchar2(20), -- 20은 바이트의 크기(가변 문자, 영어는 1byte, 한글은 2byte)
    dept_YN char(1), -- 쓰던 쓰지 않던 1바이트 고정, 넓게 쓰면 공간이 낭비될 수 있음(고정형 문자)
    dept_date date, -- 날짜
    dept_bonus number(10, 3) -- 마지막에는 콤마 없고, 정수 10자리, 소수 3자리까지   
);

desc dept2;
insert into dept2 values(99, '영업', 'y', sysdate, 3.14);
insert into dept2 values(100, '회계', 'y', sysdate, 14.123); -- 100 때문에 안들어감(number의 자리수가 2자리까지라 안들어감)

commit;

select * from dept2;
--------------------------------------------------------------------------------
-- alter (테이블 구조 변경)
-- 컬럼 추가
ALTER TABLE DEPT2 ADD(DEPT_COUNT NUMBER(3)); -- 카운트 생성

DESC dept2; 

-- 컬럼명 변경
alter table dept2 rename column dept_count to emp_count; 

-- 컬럼 타입 변경(modify), 데이터가 있을 때, 손상되는 크기로 줄일 수는 없음
alter table dept2 modify (emp_count number(5) ); -- number니까 숫자(데이터가 들어있는데 날짜로 바꾸는거 불가
DESC dept2; 

-- 컬럼 삭제
alter table dept2 drop column emp_count;
DESC dept2;

-- 테이블 삭제
Drop table dept2;
drop table employees; -- 안지워짐, 제약조건을 삭제 불가 (casecade 제약조건을 지우면서 삭제는 가능)