[SQL] DML / DDL

▣ DML

▶ INSERT 문 : 행 추가 구문

INSERT INTO 테이블명 (컬럼 [, 컬럼]) VALUES (값 [, 값[])

• 모든 컬럼에 값을 넣을 경우 컬럼 지정구문은 생략 할 수 있다. 
• 조회결과를 INSERT 하기 (subquery 이용) 
  - INSERT INTO 테이블명 (컬럼 [, 컬럼])  SELECT 구문 
  - INSERT할 컬럼과 조회한(subquery) 컬럼의 개수와 타입이 맞아야 한다. 
  - 모든 컬럼에 다 넣을 경우 컬럼 설정은 생략할 수 있다.

EX)

insert into dept (dept_id, dept_name, loc) values(500, '기확부', 'seoul');
insert into dept values(501,'구매부','인천');
insert into dept (dept_id, dept_name) values(502,'구매부');

 

 

▶ UPDATE : 테이블의 컬럼의 값을 수정

UPDATE 테이블명 
SET    변경할 컬럼 = 변경할 값  [, 변경할 컬럼 = 변경할 값] 
[WHERE 제약조건] 

 

• UPDATE: 변경할 테이블 지정
• SET: 변경할 컬럼과 값을 지정
• WHERE: 변경할 행을 선택. 
• rollback : 지금까지 수정했던 것들을 롤백(commit 진행하기 전까지 롤백)
• commit :지금까지한 작업을 DB에 적용

EX)

update emp
set salary = salary * 1.1
where emp_id = 200;

 

 

▶ DELETE : 테이블의 행을 삭제

DELETE FROM 테이블명 
[WHERE 제약조건] 

 

• WHERE: 삭제할 행을 선택

EX)

delete from emp
where job_id = 'SA_MAN'
and salary < 12000;

 

 

▣ DDL

 

▶ DDL(Data Definition Language) - 데이터베이스에서 사용되는 객체(테이블,사용자,시퀀스)를 관리하는 언어
- create - drop - alter

create table 테이블 이름(   
     컬럼 설정 - 컬럼이름, 데이터타입[, default값, 제약조건] 
     ) 

▶ 데이터타입

• 문자열 : char/nchar-고정길이, varchar2/nvarchar2/clob - 가변길이
• 숫자 : number, number(전체자리수, 소수부자리수)
• 날짜 : date, timestamp
• 파일 : blob

▶ 제약조건

• primary key(PK) : 기본키, 행식별자 컬럼
• unique key(UK) : 중복값을 못가지는 컬럼. null은 가질 수 있음.
• not null(NN) : null을 못가지는 컬럼
• check key(CK) : 컬럼에 넣을 값의 조건을 직접 지정.
• foreign key(FK) : 참조 칼럼. 부모테이블의 primary key만 값으로 가지는 컬럼. = 테이블 참조(조인)할 때 사용.

▶ 제약조건 설정 

• 컬럼 레벨 설정 -> 컬럼 설정에 같이 설정
• 테이블 레벨 설정 -> 컬럼 설정뒤에 따로 설정
• 기본 문법

: constraint 제약조건이름 제약조건타입 

•  테이블 제약 조건 조회 : USER_CONSTRAINTS 딕셔너리 뷰에서 조회 

▶ 테이블 삭제

DROP TABLE 테이블이름 [CASCADE CONSTRAINTS] -나를 참조하고 있는 제약조건들을 다 없애고 테이블 삭제

 

EX)

create table child_tb(
    no          number,--PK
    jumin_num   char(14), --UK
    age         number not null, --CK(10~90)
    p_no        number, --FK(parent_tb)
    constraint pk_child_tb primary key(no),
    constraint uk_child_tb_jumin_num unique(jumin_num),
    constraint ck_child_tb_age check(age between 10 and 90),
    --constraint FK_child_tb_parent_tb foreign key(p_no) references parent_tb,
    -- 부모테이블에서 참조하는 행이 삭제되면 자식의 행도 같이 삭제하겠다.
    --constraint FK_child_tb_parent_tb foreign key(p_no) references parent_tb on delete cascade,
    -- 부모테이블에서 참조하는 행이 삭제되면 참조컬럼의 값을 null로 update.
    constraint FK_child_tb_parent_tb foreign key(p_no) references parent_tb on delete set null
);

 

※ ALTER : 테이블 수정

▶ 컬럼 추가

ALTER TABLE 테이블이름 ADD (추가할 컬럼설정 [, 추가할 컬럼설정])

 

-> 하나의 컬럼만 추가할 경우 ( ) 는 생략가능

▶ 컬럼 수정

 ALTER TABLE 테이블이름 MODIFY (수정할컬럼명  변경설정 [, 수정할컬럼명  변경설정]) 

 

• 하나의 컬럼만 수정할 경우 ( )는 생략 가능
• 숫자/문자열 컬럼은 크기를 늘릴 수 있다.
• 크기를 줄일 수 있는 경우 : 열에 값이 없거나 모든 값이 줄이려는 크기보다 작은 경우
• 데이터가 모두 NULL이면 데이터타입을 변경할 수 있다. (단 CHAR<->VARCHAR2 는 가능.)

▶ 컬럼 삭제

 ALTER TABLE 테이블이름 DROP COLUMN 컬럼이름 [CASCADE CONSTRAINTS] 

• CASCADE CONSTRAINTS : 삭제하는 컬럼이 Primary Key인 경우 그 컬럼을 참조하는 다른 테이블의 Foreign key 설정을 모두 삭제한다.
• 한번에 하나의 컬럼만 삭제 가능.

  ALTER TABLE 테이블이름 SET UNUSED (컬럼명 [, ..])   
  ALTER TABLE 테이블이름 DROP UNUSED COLUMNS 

• SET UNUSED 설정시 컬럼을 바로 삭제하지 않고 삭제 표시를 한다. 
• 설정된 컬럼은 사용할 수 없으나 실제 디스크에는 저장되 있다. 그래서 속도가 빠르다.
• DROP UNUSED COLUMNS 로 SET UNUSED된 컬럼을 디스크에서 삭제한다. 

▶ 컬럼 이름 바꾸기

ALTER TABLE 테이블이름 RENAME COLUMN 원래이름 TO 바꿀이름; 

 

※ 제약 조건 관련 수정

▶ 제약조건 추가

  ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건 설정 

▶ 제약조건 삭제
  

ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건이름 

•  PRIMARY KEY 제거: ALTER TABLE 테이블명 DROP PRIMARY KEY [CASCADE]

ALTER TABLE 테이블명 DROP PRIMARY KEY [CASCADE] 

• CASECADE : 제거하는 Primary Key를 Foreign key 가진 다른 테이블의 Foreign key 설정을 모두 삭제한다.
  

▶ NOT NULL <-> NULL 변환은 컬럼 수정을 통해 한다.   

ALTER TABLE 테이블명 MODIFY (컬럼명 NOT NULL)
ALTER TABLE 테이블명 MODIFY (컬럼명 NULL)   

 

- 컬럼, 데이터복사, not null을 제외한 제약조건은 복사되지 않는다. (FK,PK등은 복사되지 않는다.)

EX)

-- PK제약조건 추가
alter table cust add constraint pk_cust primary key(cust_id);
alter table ord add constraint fk_ord_cust foreign key(cust_id) references cust;

-- 컬럼추가
alter table cust add (age number(2) default 0);
alter table cust add (age2 number(2) not null);

-- 컬럼 수정
desc cust;
alter table cust modify (cust_name nvarchar2(200));
alter table cust modify (address varchar2(10); -- 지금 저장할수 있을 만큼의 크기로 줄여야함
alter table cust modify (cust_name null, address null, postal_code null); -- null 허용

-- 컬럼명변경
alter table cust rename column cust_name to name; --cust이름을 name으로 바꾼다.

--컬럼삭제
alter table cust drop column age;
select * from cust;

-- 제약조건 삭제
alter table ord drop constraint fk_ord_cust;
--alter table cust drop constraint pk_cust
alter table cust drop primary key;

 

※ 시퀀스 : SEQUENCE

• 자동증가하는 숫자를 제공하는 오라클 객체
• 테이블 컬럼이 자동증가하는 고유번호를 가질때 사용한다.
• 하나의 시퀀스를 여러 테이블이 공유하면 중간이 빈 값들이 들어갈 수 있다.

▶ 생성 구문

CREATE SEQUENCE sequence이름
	[INCREMENT BY n]	
	[START WITH n]                		  
	[MAXVALUE n | NOMAXVALUE]   
	[MINVALUE n | NOMINVALUE]	
	[CYCLE | NOCYCLE(기본)]		
	[CACHE n | NOCACHE]	

 

 

• INCREMENT BY n: 증가치 설정. 생략시 1
• START WITH n: 시작 값 설정. 생략시 0
• 시작값 설정시
   - 증가: MINVALUE 보다 크커나 같은 값이어야 한다.
   - 감소: MAXVALUE 보다 작거나 같은 값이어야 한다.
• MAXVALUE n: 시퀀스가 생성할 수 있는 최대값을 지정
• NOMAXVALUE : 시퀀스가 생성할 수 있는 최대값을 오름차순의 경우 10^27 의 값. 내림차순의 경우 -1을 자동으로 설정. 
• MINVALUE n :최소 시퀀스 값을 지정
• NOMINVALUE :시퀀스가 생성하는 최소값을 오름차순의 경우 1, 내림차순의 경우 -(10^26)으로 설정
• CYCLE 또는 NOCYCLE : 최대/최소값까지 갔을때 순환할 지 여부. NOCYCLE이 기본값(순환반복하지 않는다.)
• CACHE|NOCACHE : 캐쉬 사용여부 지정.(오라클 서버가 시퀀스가 제공할 값을 미리 조회해 메모리에 저장) NOCACHE가 기본값(CACHE를 사용하지 않는다. )

▶ 시퀀스 자동증가값 조회

• sequence이름.nextval  : 다음 증감치 조회
  
• sequence이름.currval  : 현재 시퀀스값 조회
  
  
  

▶ 시퀀스 수정

ALTER SEQUENCE 수정할 시퀀스이름
	[INCREMENT BY n]	               		  
	[MAXVALUE n | NOMAXVALUE]   
	[MINVALUE n | NOMINVALUE]	
	[CYCLE | NOCYCLE(기본)]		
	[CACHE n | NOCACHE]	

 

- 수정후 생성되는 값들이 영향을 받는다. (그래서 start with 절은 수정대상이 아니다.)   


▶ 시퀀스 제거

DROP SEQUENCE sequence이름 

EX) 1부터 1씩 자동증가하는 시퀀스
create sequence dept_id_seq; -- dept_id : 이 시퀀스를 사용할 컬럼. _seq

 

EX.1)

-- 100 부터 -100까지 -100씩 자동 감소하는 시퀀스
-- 감소 : maxvalue 기본 : -1 start with 가 maxvalue보다 크면 안됨
-- 증가 : start with 가 minvalue 보다 작으면 안됨
create sequence ex6_seq
increment by -100
start with 100
maxvalue 100
minvalue -100;

 

EX.2)

select ex8_seq.nextval from dual;
-- 순환하는 시퀸스의 경우 제공하는 값의 개수가 cache개수보다 적어야 한다.
-- cathe의 기본값 : 6
create sequence ex9_seq
increment by 10
maxvalue 50
cycle
cache 3;
select ex9_seq.nextval from dual;