DML DDL summary

DML 

 

데이터베이스의 테이블에 새로운 데이터를 저장하거나 삭제 또는 수정 및 병합할 때 사용하는

1. 단일행 추가는 새로운 행 삽입
   insert  into  테이블명 [(컬럼명, 컬럼명2...)]
   values (값, 값2);
   -> 
   특정 컬럼을 넣고 싶으면 컬럼명을 적기 
   값과 컬럼명을 순서대로 매치
   컬럼명을 생각하면 테이블 생성시 정의한 컬럼순서로 들어감.
   생략할거면 모든 컬럼값을 values 에 지정.
    데이터의 크기가 지정된 컬럼 값보다 작아야
   기본키 지정컬럼이나 unique 컬럼은 동일 값 저장 X
   
   실제 데이커 베이스 에 들어가진 않음
   커밋을 날리지 않으면 나한테만 보임
   
   insert into dept (deptno, dname, loc)
   values (50, 'aa'.'bb');
   -> 50번이라는 똑같은 조건을 추가시 unique constraint 라는 에러 발생
   
   insert into dept
   values (60, '인사', '경기');
   -> 모든 컬럼 insert 시 컬럼명 필요 없음 , 단 values 부분 컬럼 순서대로 적을것
   
   
   
2. 트랜잭션
   
   1) dml 사용시 논리적인 작업단위 (추가, 수정, 삭제, 병합)
   2) dml 명령어 사용시 자동으로 tx 시작
   3) tx를 종료하기 위하여 commit, rollback 을 명시적 실행해야
   
   insert into dept values(50, 'aa','aa');  --tx 포함
   insert into dept values (60, 'aa','bb'); -- tx 에 포함
   insert into dept values (70, 'cc','cc'); -- tx 에 포함
   rollback;
   -> 커밋이나 롤백을 하기 전까지
   
   insert into dept values (50, 'aa','aa'); -- tx  포함
   commit; -- tx 포함
   
   insert into dept values(60, 'aa, 'bb'); -- tx 새로시작
   
   insert into dept values (70, 'cc','cc'); -- tx 포함
   rollback;
   
   50번만 커밋돼서 db에 저장됨
   60 70 은 롤백되어 지워짐.
   
   

1.1 1) 널값 저장

• 묵시적/ 명시적
  
  
• into 절에서 생략하기
  insert into dept (deptno, dname) values (70, '인사');
  
  
• 명시적 방법
  values 절의 컬럼값에 null값을 지정하거나 ' ' 작은 따옴표를 적음.
  
  insert into dept (deptno, dname, loc) values (11, '인사');
  -> 에러. 위 3개의 컬럼에 대응하지 않은.. 개수가 일치하지 않아서
  
  insert into dept values (12, '인사);
  -> 에러
  
  insert into dept dept ( deptno, dname, loc) values ('개발', 13, '인사');
  -> 개발이 숫자가 아니어서 오류
  
  insert into dept(deptno, dname, loc )values (100, 'aa','aa');
  -> 숫자가 두 글자 최대인데 3글자 넣어서 오류 발생
  
  ★★★★ insert 의 특수한 형태
  1. 특수값입력
  
  참조부 결성 -> 외래키
  insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
  values (9000, user, '연구원', 7839, sysdate, 5000, null, 90);
  
  -> deptno 90 번이 존재하지않음
  90번을 만들던가 있는 번호로 사용하면 생김
  
  2. 특정 데이터 타입으로 입력(RR/MM/DD 형식, TO_DATE 형
  
  
• insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
  values (9001, '홍길동', 'MANAGER',7839, '2000/01/01', 2000, NULL, 30);
  
• insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
  values (9002, '임꺽정', 'MANAGER',7839, to_date('199-12-13','YYYY/MM/DD'),
  2000, NULL, 30);
  

1.2 복수행 insert문

 

하나의 인서트문을 사용하여 여러행을 저장하는 복수행 인서트

insert into 테이블명 [(컬럼명, 컬럼명2,...)]
subquery;

존재하는 테이블을 사용하여 새로운 테이블을 생성하는 방법:
create table 테이블명 [(컬럼명, 컬럼명2, ..)]
as subquery;
-> 테이블의 구조만 복사하기 위하여 where 절의 조건식이 false 가 되도록 1=2 같이 설정 
where 1=2;

insert into mydept
select * from mydept 
select deptno, dname, loc 
from dept;

select * from mydept;
-> dept 테이블 복사해서 mydept 에 붙임
확인하니 잘 붙여짐

 

• 1.3 다중 테이블 다중행 insert 문 외우기중요!!!!!★

한번의 insert 시 여러 테이블에 복수 데이터를 저장 insert all 이라 부름

 

create table myemp_hire as select empno, ename, hiredate, sal from emp where 1=2;

→ emp 테이블에서 empno, ename, hiredate, sal만 추출해서 새로운테이블에 붙임.

 

insert all into myemp_hire values(empno, ename, hiredate, sal)

into myemp_mgr values(empno, ename, mgr) select empno, ename, hiredate, sal, mgr from emp;

 

select * from myemp_hire;

select * from myemp_mgr;

 

create table myemp_hire2 as select empno, ename, hiredate, sal from emp where 1=2;

create table myemp_mgr2 as select empno, ename, mgr from emp where 1=2;

 

insert all when sal > 3000 then into myemp_hire2 values (empno, ename, hiredate, sal) when mgr = 7698 then into myemp_mgr2 VALUES (empno, ename, mgr) SELECT empno, ename, hiredate, sal, mgr from emp;

→ 3000 이상의 월급을 가진 사람만 복붙 매니저번호가 7698 인 사람만 복붙.

 

select empno, ename, hiredate, sal, mgr from emp;

select * from myemp_hire2; select * from myemp_mgr2;

 

insert first when sal = 900 then into myemp_hire3 values

(empno, ename, hiredate,sal) when sal<2500 then into myemp_mgr3

values (empno, ename, mgr) select empno, ename, hiredate, sal, mgr from emp;

→ 위 when 만 반영 첫번째 꺼.

 

1. 3 UPDATE 문

update 테이블명 set 컬럼명 = 변경할 값 [, 컬럼명1 = 변경할값]
[where 조건식];

update dept set loc ='aa', dname = 'aa';
→ loc 이 aa 이고 부서이름이 aa로 됨.

update dept set dname='영업부서', loc = '경기' where deptno = 50;
행 하나를 지정

update mydept set dname = ( select dname from dept where deptno=10) ,
loc =( select loc from dept where deptno = 20) where dept = 60;
→mydept 부서번호가 60인 사원의 부서명을 dept테이블의 부서번호가 10인 부서의 부서명으로 수정하고 부서위치는 dept테이블의 부서번호가 20인 부서위치로 수정

1.4 DELETE 문

delete from emp;
delete from emp where rmpno = 9000;

delete from dept where deptno = 10;
-> emp 테이블에서 참조하는 데이터 삭제가 안됨

delete from dept where deptno = 70;
-> 0개의 행 삭제

delete from mydept where loc =
(select loc from dept where deptno= 20);
-> dept의 deptno 가 20번인 loc과 mydept 의 loc 이 일치하면 삭제

1.5 MERGE

1.

merge into P_toal total 
using pt_02 p02
on (total.판매번호 = p.02 판매번호)
when matched then 
update set total.제품번호 = p02. 제품번호
WHEN NOT MATCHED THEN INSERT VALUES
( p02.판매번호,p02.제품번호,p02.수량, p02.금액);

 

 

2. 트랜잭션

분리될 수 없는 한개이상의 데이터베이스 조작
하나의 트랜잭션에는 하나 이상의 sql 문장이 포함될 수 있으며 대상이되는 sql문은 dml문

커밋롤백할 필요없음.

1) 트랜잭션 내의 모든 데이터 변경 사항은 트랜잭션이 종료되기 전에는 모두 임시적.
그래서 데이커를 변경 전 상태로 복구할 수 있다.
2) 변경된 행은 내부적으로 잠금이 걸리게 되어 해당 사용자를 제외한 나머지 사용자는
해당 행을 변경할 수 없다, 커밋또는 롤백으로 종료시켜야 잠금해제.
3) 데이터를 변경한 scott  변경된 데이터를 확인 할 수 있으나
sys는 변경된 데이터 결과를 확인할 수 없다.

 

 

DDL

 

데이터베이스의 구조를 수정 또는 삭제할 때 사용
자동으로 commit 돼서 데이터베이스에 즉각영향을 미침.

 

 

create 생성

alter 변경

drop 제거

RENAME 객체이름변경

1. 테이블생성
   
   create table [스키마] .테이블명
   (컬럼멸 데이터타입[DEFAULT 값| 제약조건][..]);
   스키마 ex)scott
   
   테이블 명을 지정한 후에는 컬럼 이름과 데이터 타입을 지정해야 된다.
   컬럼 이름은 식별하기 쉬운 의미 있는 명사형으로 지정하고
   데이터 타입은 컬럼에 저장되는 데이터의 자료 형을 지정한다.
   
   1) char
   2) varchar 길이만큼만 기억공간이 할당 4000 까지 가능
   3) number (p,s) : p 자릿수 s 소숫점자릿수
   4) date
   5)rowid: select * from emp where rowid ='AAAE5eAABAAALC5AAB';
   6) LOB : 대용량 텍스트, 바이너리 (이미지 동영상 사운드)
   
   create table scott.employee (empno number (4),
   ename varchar(20), hiredate, date sal number (7,2));
   
   

• DEFAULT ★★★★
  
  insert 문으로 데이터를 저장할 때 특정 컬럼에 값을 지정X-> 자동 널값 저장.
  default 옵션을 사용하면 자동으로 기본값이 입력되어 null 값이 저장되는걸 방지가능.
  
  create table employee2
  (empno number(4),
  ename varchar2 (20),
  hiredate date default sysdate, sal number(7,2));
  
  
  
• 제약조건
  
  create table employee2
  (empno number(4) 제약조건 기술 --컬럼레벨제약조건 (1개만 기술가능),
  ename varchar2(20),
  hiredate date default sysdate, sal number (7,2)
  ename 제약조건기술);-- 테이블 레벨 제약조건(추가용)
  
  1)not null : null 불가능컬럼레벨방식만 지원
  2) unique: 중복불가 null 가능
  3) primary key : null 안되고 중복안됨
  4) forgin key: 참조되는 컬럼에 없는 값은 저장이 안됨.
  5) check : 특정 데이터 값의 범위나 사용자조건 지정.

1. PRIMARY KEY제약조건
   
   테이블당 하나의 기본키만 가질 수 있음.
   중복 X ,NOT NULL
   
   create table [스키마].테이블명
   (컬럼명 데이터타입 [ constraint 제약조건명] PRIMARY KEY,
   컬럼명 데이터타입, … );
   
   create table department (deptno NUMBER(2)
   constraint department_deptno_pk PRIMARY KEY,
   dname VARCHAR2(15), loc VARCHAR2(15));
   
   부서번호를 저장할 deptno 컬럼명과 데이터타입을 기술한 후에
   CONSTRAINT 키워드를 사용하고 제약조건명을 ‘테이블명_컬럼명_pk' 형식인
   department_deptno_pk로 지정한다. 이 후 PRIMARY KEY를 기술함으로써
   deptno컬럼에 기본 키 제약조건이 설정될 수 있다.
   테이블명이 중복되면 안되는 것처럼 department_deptno_pk 제약조건명도 중복되지 않도록
   지정해야 된다.
   
   select * from user_cons_columns where table_name = 'DEPARTMENT';
   → 어떤 칼럼에 제약조건이 정의되엇는지
   
   create table department2 (deptno NUMBER(2),
   dname VARCHAR2(15), loc varchar2(15),
   constraint department2_deptno_pk PRIMARY KEY(deptno));
   →부서번호, 부서명, 부서위치 컬럼을 모두 정의한 후에
   가장 마지막에서 PRIMARY KEY 제 약조건을 추가한다.
   이때 deptno 컬럼이 기본 키 컬럼인 것을 괄호() 안에 명시해야 된다.
   
   create table department3 (deptno NUMBER(2), dname VARCHAR2(15),
   loc varchar2(15), constraint department3_deptno_pk PRIMARY KEY(deptno, loc));
   → 두개를 쌍으로 비교하므로 동시에 두개가 같지않으면 ㄱㅊ 두개가 동시에 같으면 오류
   ex) 10 서울 10 서울
   
   create table department3 (deptno NUMBER(2), dname VARCHAR2(15), loc varchar2(15),
   constraint department3_deptno_pk PRIMARY KEY(deptno, loc));
   
   select * from department3;
   insert into department3 (deptno, loc, dname) values (10, '서우ㅜㄹ','aa');--ok
   insert into department3 (deptno, loc, dname) values (20, '제주','aa');--ok
   insert into department3 (deptno, loc, dname) values (20, '서우ㅜㄹ','aa'); --ok
   insert into department3 (deptno, loc, dname) values (10, '서우ㅜㄹ','aa'); --X