3. DDL(Data Definition Language)
1. 테이블(Table) 생성
- 데이터베이스를 사용하기 위해서는 테이블을 먼저 생성
- 테이블 생성은 Create Table문을 사용하고 테이블 변경은 Alter Table문을 사용
마지막으로 생성된 테이블을 삭제하고 싶을 때는 Drop Table문을 사용
테이블 관리 SQL문
Create Table
- 새로운 테이블을 생성
- 테이블 생성할 때 기본키, 외래키, 제약사항 등을 설정할 수 있음
Alter Table
- 생성된 테이블을 변경
- 칼럼을 추가하거나 변경, 삭제할 수 있음
- 기본키를 설정하거나, 외래키를 설정할 수 있음
Drop Table
- 해당 테이블을 삭제
- 테이블의 데이터 구조뿐만 아니라 저장된 데이터도 모두 삭제
1. 기본적인 테이블 생성
- 테이블을 생성하는 방법은 여러가지가 있음
먼저 아주 간단한 테이블 생성 방법을 확인
Create Table EMP
(
empno number(10) primary key,
ename varchar2(20),
sal number(6)
);
-
Create Table EMP : 생성되는 테이블명이고 테이블 명은 영문자로 시작
-
empno, ename, sal : 생성되는 EMP 테이블의 칼럼명
-
number(6), number(10), varchar2(20) : 데이터 타입을 지정
Number는 숫자, varchar2는 가변 길이 문자를 의미
Create Table문의 구조
- Create Table
- 'Create Table EMP'는 'EMP'테이블을 생성하라는 의미
- '( )'사이에 컬럼을 쓰고 마지막은 세미콜론으로 끝남
- 칼럼 정보
- 테이블에 생성되는 칼럼 이름과 데이터 타입을 입력
- 칼럼 이름은 영문, 한글, 숫자 모두 가능
- 데이터 타입
- number는 칼럼의 데이터 타입을 숫자형 타입으로, varchar2는 가변 길이 문자열로 지정할 때 사용
- char는 칼럼의 데이터 타입을 고정된 크기의 문자로 지정할 때, date는 날짜형 타입으로 지정할 떄 사용
- 기본키
- 기본키를 지정할 때 칼럼 옆에 primary key를 입력
TIP 테이블의 구조 확인
- SQL 중에 DESC문이 있음
- DESC문은 테이블의 구조를 확인할 때 사용
- 즉, Create Table로 생성된 테이블의 구조를 보고 싶을 때 사용
2. 제약 조건 사용
-
기본키, 외래키, 가본값, not null 등은 테이블을 생성할 때 지정할 수 있음
-
위의 예를 보면 'constraint'를 사용하여
기본키(empno)와 기본키의 이름(emppk)을 지정할 수 있음 -
만약 위의 예에서 두 개의 기본키를 지정하고자 한다면
'constraint emppk primarykey(empno, ename)'으로 지정하면 됨 -
sal 칼럼은 number(10, 2)로 지정
이것은 소수점 둘째 자리까지 저장 -
Oracle 데이터베이스에서 'sysdate'는 오늘의 날짜를 조회
이를 default 옵션을 사용해서 오늘 날짜를 기본값으로 지정할 수 있음
-
외래키(Foreign key)를 지정하려면, 먼저 마스터 테이블이 생성되어야 함
즉, 사원과 부서 테이블에서는 부서가 마스터 테이블이 됨 -
사원 테이블이 부서 테이블의 deptno를 참조해야 하는 것
-
그리고 EMP 테이블을 생성할 때 constraint를 사용하여
외래키 이름인 'deptfk'를 입력 후 외래키를 생성
constraint deptk foreign key(deptno) reference dept (deptno)
- constraint
- deptfk : 외래키 이름
- foreign key(deptno) : EMP 테이블의 deptno
- references dept(deptno) : DEPT 테이블의 deptno 칼럼
3.테이블 생성 시 CASCADE 사용
-
테이블을 생성할 때 CASCADE 옵션을 사용할 수 있음
CASCADE 옵션은 참조 관계(기본키와 외래키 관계)가 있을 경우
참조되는 데이터를 자동으로 반영할 수 있는 것 -
먼저 마스터 테이블을 생성
즉, DEPT 테이블을 생성하고 데이터를 입력
Create Table DEFT
(
deptno varchar2(4) primary key,
deptname varchar2(20)
);
INSERT INTO DEPT VALUES ('1000', '인사팀');
INSERT INTO DEPT VALUES ('1001', '총무팀');- EMP 테이블을 생성하고 데이터를 입력
EMP 테이블을 생성할 때 'On Delete Cascade' 옵션을 사용
Create Table EMP(
empno number(10),
ename varchar2(20),
sal number(10,2) default 0,
deptno varchar2(4) not null,
createdate date default sysdate,
constraint e_pk primary key(empno),
constraint d_fk foreign key (deptno)
references dept (deptno)
ON DELETE CASECADE
);
INSERT INTO EMP VALUES(100, '임베스트', 1000, '1000' sysdate);
INSERT INTO EMP VALUES(101, '을지문덕', 2000, '1001' sysdate);- EMP 테이블을 생성하고 데이터 두 개를 입력함
DELETE FROM DEPT WHERE DEPTNO = '1001';
SELECT * FROM EMP;- 위 예제를 보면 DEFT 테이블에서 DEPTNO가 '1000'번인 인사팀을 삭제함
- EMP 테이블의 데이터를 조회한 결과,
DEPTNO '1000'번이였던 '임베스트' 데이터도 자동으로 삭제된 것을 알 수 있음 - ON DELETE CASCADE 옵션은 자신이 참조하고 있는 테이블(DEPT)의 데이터가 삭제되면
자동으로 자신(EMP)도 삭제되는 옵션 - ON DELETE CASCADE 옵션을 사용하면 참조 무결성을 준수할 수 있음
- 참조 무결성이란?
마스터 테이블(DEPT)에는 해당 부서번호(DEPTNO)가 없는데,
슬레이브 테이블(EMP)에는 해당 부서번호가 있는 경우를 참조 무결성 위배로 볼 수 있음
2. 테이블(Table) 변경
- ALTER TABLE문을 통해 테이블 변경을 할 수 있으며, 테이블명 변경, 칼럼 추가, 변경, 삭제 등을 할 수 있음
1. 테이블명 변경
- 테이블명 변경은 ALTER TABLE ~ RENAME TO문을 사용하면 됨
ALTER TABLE EMP
RENAME TO NEW_EMP;2. 칼럼 추가
- 생성된 EMP 테이블에 ALTER TABLE ~ ADD문을 사용해서 칼럼을 추가
ALTER TABLE EMP
ADD (age number(2) default 1);3. 칼럼 변경
- 칼럼의 변경은 ALTER TABLE ~ MODIEFY문을 사용하면 됨
- 칼럼 변경을 통해 데이터 타입을 변경하거나 데이터의 길이를 변경할 수 있음
- 칼럼을 변경할 때 제약조건을 설정할 수도 있음
- 칼럼의 데이터 타입을 변경할 때 기존 데이터가 있는 경우 에러가 발생함
ex) 숫자 데이터가 저장되어 있는데 문자형 데이터 타입으로 변경하면 에러가 발생
ALTER TABLE EMP
MODIFY (ename varchar2(40) not null);4. 칼럼 삭제
- 칼럼에 대한 삭제는 ALTER TABLE ~ DROP COLUMN문으로 삭제
ALTER TABLE EMP
DROP COLUMN age;5. 칼럼명 변경
- 칼럼명 변경은 ALTER TABLE ~ RENAME COLUMN ~ To 문으로 변경
ALTER TABLE EMP
RENAME COLUMN ename to new_ename;3. 테이블(Table) 삭제
- 테이블 삭제는 DROP TABLE문을 사용해서 삭제할 수 있음
- DROP TABLE은 테이블의 구조와 데이터를 모두 삭제
DROP TABLE EMP;- DROP TABLE에서 'CASCADE CONSTRAINT'옵션을 사용할 수 있음
- 'CASCADE CONSTRAINT'옵션은 해당 테이블의 데이터를 외래키로 참조한 슬레이브 테이블과 관련된
제약 사항도 삭제할 때 사용
DROP TALBlE EMP CASCADE CONSTRAINT;4. 뷰(VIEW) 생성과 삭제
- 뷰란 테이블로부터 유도된 가상의 테이블
- 실제 데이터를 가지고 있지 않고 테이블을 참조해서 원하는 칼럼만을 조회할 수 있게 함
- 뷰는 데이터 딕셔너리(Data Dictionary)에 SQL문 형태로 저장하되 실행 시 참조
뷰의 특징
- 참조한 테이블이 변경되면 뷰도 변경됨
- 뷰의 검색은 참조한 테이블과 동일하게 할 수 있지만, 뷰에 대한 입력, 수정, 삭제에는 제약이 있음
- 특정 칼럼만 조회시켜서 보안성을 향상
- 한번 생성된 뷰는 변경할 수 없고 변경을 원하면 삭제 후 재생성해야 함
- ALTER문을 사용해서 뷰를 변경할 수 없음
CREATE VIEW T_EMP AS
SELECT * FROM EMP;- 뷰를 생성할 때 CREATE VIEW문을 사용하며 이때 참조할 테이블은 SELECT문으로 지정
SELECT * FROM T_EMP;- 뷰의 조회는 SELECT문을 사용해서 일반 테이블처럼 조회
DROP VIEW T_EMP;- 뷰의 삭제는 'DROP VIEW'를 사용함
- 뷰를 삭제했다고 해서 참조했던 테이블이 삭제되지 않음
뷰의 장점과 단점
장점
- 특정 컬럼만 조회할 수 있기 때문에 보안 기능이 있음
- 데이터 관리가 간단
- SELECT문이 간단
- 하나의 테이블에 여러 개의 뷰를 생성할 수 있음
단점
- 뷰는 독자적인 인덱스를 만들수 없음
- 삽입, 수정, 삭제 연산이 제약
- 데이터 구조를 변경할수 없음
learn !