[SQL] 오라클 sql 정리6 - DDL, DML, 제약조건, Sequence

BACK END/SQL

[SQL] 오라클 sql 정리6 - DDL, DML, 제약조건, Sequence

circle kim 2020. 12. 28. 01:09

[목차]

10. DDL

11. DML

12. 제약조건

13. Sequence

[내용]

10. DDL(Date Definition Language) : 테이블 생성, 수정, 삭제, 자동 commit

1) 테이블 생성

create table 테이블명(
     칼럼명 데이터 타입(크기)
      ....            );

create table board( 
no number, 
id varchar2(20), 
title varchar2(20), 
content varchar2(2), 
hit number, 
regdate date);

create table myemp(
empno char(5), ename varchar2(10), hiredate date, sal number(10), memo varchar2(10));

2) 테이블 변경 - 추가

alter table 테이블명
add (칼럼명 데이터타입(크기));

alter table board
add (email varchar2(20));

3) 테이블 변경 - 수정

alter table 테이블명
modify (칼럼명 데이터타입(크기));

alter table board
modify (content varchar2(20));

4) 테이블 삭제

drop table 테이블명;

drop table emp2;

11. DML(Data Manipulation Language) : 데이터 조작어

1) insert into : data 추가

insert into 테이블명
values(값,...);

insert into board values(1,'jang','test','test1',0,sysdate,null);

insert into board values(2,'kim','title','ccocnocnco',0,sysdate,'kwdd@naver.com');

insert into board values(6,'choi','oracle','안녕하세요?????',0,sysdate, null);

insert into myemp values('00001', 'scott', sysdate, 3000,'신입');

- 선택 칼럼의 데이터만 입력하기

insert into emp (empno, ename, hiredate) values(1111,'이이이',sysdate);
insert into emp (empno, ename, hiredate, deptno) values(7777,'율곡', sysdate, 100);

2) update

update 테이블
set
where

update board
set title ='oracle'
where no=1;

update board
set title='oracle', hit=hit+1
where no=4;

- set절 서브쿼리 이용하여 조건에 맞는 값 update

update myemp
set sal=(select sal
         from emp
         where empno=7934)
where empno=7369;

update myemp
set sal=(select avg(sal)
		from emp
		where mgr=7698)
where ename is null;

update myemp
set sal =sal+300
where sal<(select avg(sal)
           from emp
           where mgr=7698);
-> where절 서브쿼리 이용 조건에 맞는 값 update

3) delete

delete (into) from 테이블명
where

delete from board
where no=5;

delete board
where id is null;

- where절 서브쿼리 이용 조건에 맞는 값 delete

delete from myemp
where sal>(select avg(sal)
           from myemp);

4) 삽입된 데이터 저장하기

commit;

12. 제약조건

- 테이블 생성시 제약조건 추가

create table 테이블명(
칼럼명 데이터타입(크기) 제약조건,
  ....                 );

create table emp2(id varchar2(10) primary key,
                  name varchar2(10) not null,
                  pass varchar2(10));

create table emp2(
    empno number(5),
    ename varchar2(10) not null,
    deptno varchar2(10),
    sal number,
    tel varchar2(10),
    job varchar2(10),
    constraints emp2_empno_pk primary key(empno));

- 테이블에 적용된 제약 조건 확인

desc user_contraints

select OWNER,CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
from user_constraints;

OWNER
---------------------------------------------------------------------------------------------------------------------
CONSTRAINT_NAME                                              CO TABLE_NAME
------------------------------------------------------------ -- -----------------------------------------------------
SCOTT
SYS_C006997                                                  P  EMP

SCOTT
SYS_C007004                                                  C  EMP2

SCOTT
BOARD_ID_PK                                                  P  BOARD

SCOTT
EMP2_ID_FK                                                   R  EMP2

SCOTT
EMP2_SAL_CK                                                  C  EMP2

SCOTT
SYS_C007013                                                  P  DEPT2

SCOTT
EMP2_JOB_CK                                                  C  EMP2

- 제약 조건 변경 (primary key)

alter table board
add constraint board_no_pk primary key(no);

- 제약 조건 변경 (unique key)

alter table emp2
add constraint emp2_tel_uk unique(tel);

- 제약 조건 변경 (check)

alter table 테이블
add constraint 제약조건명 check(조건);

alter table emp2
add constraint emp2_sal_ck check(sal between 5000 and 7000);

alter table emp2
add constraint emp2_job_ck check(job in('developer', 'siger', 'teacher'));

- 제약 조건 변경 (foreign key)

alter table 테이블명
add constraiont 제약조건명 foreign key(칼럼) references 테이블명2(칼럼2);
                        primary key(칼럼);

alter table emp2
add constraint emp2_deptno_fk foreign key(deptno) references dept2(decode);

alter table emp2
add constraint emp2_id_fk foreign key(id) references board(id);

- 제약조건 삭제

alter table emp2
drop constraint emp2_empno_pk;

13. Sequence
- 시퀀스 생성

create sequence 시퀀스명;

create sequence board_seq;

- 시퀀스 시작값 설정

create sequence tb_order_seq
start with 2020111100;

- 현재 시퀀스 확인.

select 시퀀스명.currval form dual;

select tb_order_seq.currval from dual;
->현재 sequence 확인.

- 다음 시퀀스

select board_seq.nextval from dual;

insert into board(no, title,regdate,hit)
            values(board_seq.nextval, 'text', sysdate,0);

14. on delete cascade : 미사용 권장

- on delete cascade추가하여 foreign 제약조건 추가.

alter table tb_order
add constraint order_fk foreign key(id) references tb_customer(id) on delete cascade;

- on delete cascade foreign키 삭제 시 error 발생

drop table tb_customer;

- on delete cascade constraint 삭제

drop table tb_customer cascade constraints;

alter table tb_order
add constraint id_notnull not null(id);
-> error

alter table tb_order
modify id varchar2(10) not null;
-> not null 조건 추가

create table myemp
as
select empno, ename,sal, mgr, hiredate
from emp
where 1=1;
-> 테이블 생성
-> 데이터 들어감

create table myemp2
as
select empno, ename,sal, mgr, hiredate
from emp
where 1=2;
-> 테이블 생성
-> 데이터 안들어감

insert all
     into myemp values(7777,'aaaaa',1000,7475,sysdate)
     into myemp2 values(7777,'aaaaa',1000,7475,sysdate)
select * from dual;
-> myemp, myemp2에 date insert

insert all
     into myemp2 values(1111,'bbbb',1000,7475,sysdate)
     into myemp2 values(2222,'ccccc',1000,7475,sysdate)
select * from dual;
-> myemp2에 2개 date insert

- Data Dictionary : 읽기전용
- rollback;

- 모든 테이블 삭제

SELECT 'DROP TABLE "' || TABLE_NAME || '" CASCADE CONSTRAINTS;' FROM user_tables;