학원/강의

*40일차 (DML)

pringspring 2022. 3. 24. 21:11

@DML

  • Data Mainpulation Language : 데이터 조작어
  • 테이블의 데이터를 조작하는 명령어 모음
  • insert / update / delete / select
  • 처리된 행의 개수를 리턴
  • 명령어 실행으로는 db에 실제 반영되지 않으므로 TCL commit 명령어를 실행
  • rollback처리하면 마지막 커밋시점으로 돌아감

 

 

*INSERT

  • 특정테이블에 새로운 행(row/record)을 추가하는 명령어
  • 정상실행시 테이블에 행이 추가됨

*문법1 : 컬럼명을 지정하지 않는 경우 (모든 컬럼을 테이블에 정의된 순서대로 작성)

insert into 테이블명

values (컬럼1값 컬럼2값 . . .);

 

*문법2 : 컬럼명을 지정하는 경우 (지정한 컬럼만 데이터를 작성. not null컬럼은 생략 불가)

insert into 테이블 명(컬럼명1,컬럼명2,,,)

values (컬럼1값 컬럼2값  . . .);

create table sample (
    code number,
    name varchar2(100) not null,
    nickname varchar2(100) default '홍길동',
    email varchar2(100) default 'honggd@gmail.com' not null,
    enroll_date date default sysdate
);

--데이터 추가

insert into
    sample 
values(
    123, '고길동', '미스터고고', 'go@gmail.com', default
);

insert into
    sample 
values(
    123, '고길동', null, 'go@gmail.com', default
);
 

-- 자료형 일치하지 않을때 ORA-01722: 수치가 부적합합니다
-- 컬럼수가 맞지 않는 경우 ORA-00947: 값의 수가 충분하지 않습니다
-- not null컬럼에 null값 대입 ORA-01400: NULL을 ("KH"."SAMPLE"."NAME") 안에 삽입할 수 없습니다
-- 지정한 자료형 크기보다 큰값을 추가할 때 ORA-12899: "KH"."SAMPLE"."NICKNAME" 열에 대한 값이 너무 큼(실제: 360, 최대값: 100)

-- 생략한 값은 null이 대입되거나, default값이 지정된 경우 default값으로 처리

 

insert into 
    sample (code, name, email)
values(
    345, '신사', 'sinsa@gmail.com'
);

 


-- not null컬럼은 생략할 수 없다. ORA-01400: NULL을 ("KH"."SAMPLE"."NAME") 안에 삽입할 수 없습니다.
-- not null이어도 default값이 지정된 경우는 생략할 수 있다.
insert into 
    sample (code, name)
values(
    555, '세종'
);

insert into 
    sample (name, code )
values(
    '전봉준', 1234
);

select * from sample;

commit;


-- ex_employee 생성!
-- subquery를 이용해 table을 생성하면, not null을 제외한 제약조건, 기본값은 모두 제거된다.

create table ex_employee
as
select
    *
from
    employee;

select * from ex_employee;

--not null 확인

desc employee;

--기본값 확인

select
    *
from
    user_tab_cols
where
    table_name = 'EX_EMPLOYEE';

 

--기본값,제약조건 추가

alter table ex_employee
add constraint pk_ex_employee primary key(emp_id)   -- emp_id 기본키지정(식별자컬럼)
modify quit_yn default 'N'                          -- 기본값 지정
modify hire_date default sysdate;                    -- 기본값 지정
-- 데이터 추가
-- 301 함지민 - 모든 컬럼에 데이터추가 (문법1)
-- 302 김톄리 - not null 컬럼만 데이터 추가 (문법2)
insert into
    ex_employee 
values(
    '301', '함지민', '990909-2345678' , 'ham@gmail.com', '01012341234', 'D9', 'J2', 'S2', 5000000, 0.1, '200', default, null, default
);

insert into
    ex_employee (emp_id, emp_name, emp_no, job_code, sal_level)
values(
    '302', '김톄리', '770707-2345432', 'J3', 'S3'
);

select * from ex_employee;

--서브쿼리를 이용한 insert

create table ex_employee_info (
    emp_id char(3),
    emp_name varchar2(30),
    email varchar2(100)
);

 

--values절 없음

insert into ex_employee_info (
    select
        emp_id, emp_name, email
    from
        ex_employee
);

select * from ex_employee_info;

--ex_employee_manager 테이블 생성

--사번 사원명 매니저사번 매니저명

desc ex_employee;

create table ex_employee_manager (
    emp_id char(3),
    emp_name varchar2(20),
    manager_id char(3),
    manager_name varchar2(20)
);

insert into ex_employee_manager(
    select
        emp_id,
        emp_name,
        manager_id,
        (select emp_name from employee where emp_id = e.manager_id) manager_name
    from
        employee e
);

select * from ex_employee_manager;

 

--특정테이블의 데이터를 여러테이블에 동시에 insert 하기

create table ex_employee_hire_date (
    emp_id char(3),
    emp_name varchar2(20),
    hire_date date
);

create table ex_employee_salary (
    emp_id char(3),
    emp_name varchar2(20),
    salary number
);

select * from ex_employee_hire_date;
select * from ex_employee_salary;

--insert all

insert all
into ex_employee_salary values(emp_id, emp_name, salary)
into ex_employee_hire_date values(emp_id, emp_name, hire_date)
select
    emp_id, emp_name, salary, hire_date
from
    ex_employee;

 

 

*UPDATE

  • 특정행을 찾고, 해당 행의 컬럼값을 변경
  • 처리이후 행의 수 변화는 없음
  • 요청 후 수정된 행의 수 반환

 

-- 205번 사원의 급여를 100000원인상, 직급은 J2로 변경

select
    *
from
    ex_employee
where
    emp_id = '205';
    
    
update 
    ex_employee
set
    job_code = 'J4',
    salary = salary + 100000
where
    emp_id = '205';
    
commit;
rollback;

-- where절에 행이 여러개 조회되면 동시에 여러행을 수정가능
-- D5부서원의 급여를 10%로 인상

select * from ex_employee where dept_code = 'D5';

update
    ex_employee
set
    salary = salary + (salary * 0.1)
where
    dept_code = 'D5';

--임시환 차장의 직급을 과장으로 변경

select * from ex_employee where emp_name = '임시환';

update
    ex_employee
set
    job_code = (select job_code from job where job_name = '과장')
where
    emp_name = '임시환';

 

--where절에 조건컬럼은 식별자 컬럼을 사용하는 것이 좋음

 

 

*DELETE

  • 특정행을 삭제하는 구문
  • 처리결과 행의 수가 줄어듦
--delete from
--    ex_employee
--where
--    emp_id = '302';

select * from ex_employee;

rollback;
commit;

--where절을 사용하지않으면 전체 행이 삭제

--delete from
--    ex_employee;

 

 

*TRUNCATE

  • DDL로써 전체행 삭제
  • auto-commit되므로 롤백으로 복구 안됨
  • delete와 달리 before-image를 생성하지 않으므로 처리속도가 매우 빠름
truncate table ex_employee;

select * from ex_employee;
rollback;

insert into ex_employee (
    select * from employee
);
commit;

 

--DDL/DML을 혼용할 때 주의점

create table tb_test (
    id varchar2(20)
);

insert into tb_test values('honngd');
insert into tb_test values('sinsa');

-- 중간 끼어든 ddl작업 
-- commit실행시, 이전 작업내용 포함
create table tb_test2 (
    id varchar2(20)
);

rollback;

select * from tb_test;