[SQLD] SQL 기본 - DDL(Data Definition Language)

< 테이블 생성  : CREATE>

1. 기본적인 테이블 생성

Create Table EMP // EMP 테이블 생성
(
	empno number(10) primary key, // empno 칼럼 기본키 지정
    ename varchar2(20), // varchar2 : 가변길이문자
    sal number(6) // number : 숫자
);

 

DESC EMP; 로 테이블 구조 확인 가능

 

2. 제약조건 사용

 

- constraint 기본키 지정

Create Table EMP (
    empno number(10),
    ename varchar2(20),
    sal number(10,2) default 0, // 기본값 0으로 지정 
    				// number(10,2) -> 소수점 둘째자리까지 저장
    deptno varchar2(4) not null,
    createdate date default sysdate, // 기본값을 sysdate(오늘 날짜/시/분/초)로 지정
    constraint emppk primary key(empno)
    // costraint를 이용해 기본키 이름(emppk)과 기본키(empno) 지정
);

constraint 기본키 이름 primary key (칼럼 이름)

 

 

- constraint 외래키 지정

// 부서 테이블

Create Table deptA(
 deptno varchar2(4) primary key, 
 deptname varchar2(20)
);

// 사원 테이블

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 emppk primary key (empno),
 constraint deptfk foreign key (deptno) references deptA (deptno)
 // deptno 칼럼에 대해 외래키를 생성 -> deptA 테이블의 deptno 기본키를 참조
);

constraint 외래키 이름 foreign key (칼럼 이름) references 참조 테이블명 (참조 칼럼 이름)

 

 

3. CASCADE 사용

- 테이블 생성시 CASCADE 옵션 사용가능 

- 참조 관계 (기본키 & 외래키 관계)가 있을 경우 참조되는 데이터를 자동으로 반영

// 마스터 테이블 DEPT 생성

Create Table DEPT
(
    deptno varchar2(4) primary key,
    deptno varchar2(20)
);

INSERT INTO DEPT VALUES ('1000', '인사팀');
INSERT INTO DEPT VALUES ('1001', '총무팀');

 

// 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 CASCADE
);
INSERT INTO EMP VALUES(100, '임베스트', 1000, '1000', sysdate);
INSERT INTO EMP VALUES(101, '을지문덕', 2000, '1001', sysdate);

 

DELETE FROM DEPT WHERE DEPTNO = '1000';
SELECT FROM EMP;

* ON DELETE CASCADE 옵션은 자신이 참조하는 테이블의 데이터가 삭제되면 자동으로 자신도 삭제되는 옵션

* ON DELETE CASCADE 옵션은 참조 무결성을 준수할 수 있음

 

 

< 테이블 수정 : ALTER >

1. 테이블명 변경

ALTER TABLE EMP // EMP 테이블을
	RENAME TO NEW_EMP; // NEW_EMP 테이블로 변경

ALTER TABLE 기존 테이블명 RENAME TO 새 테이블명;

 

 

2. 칼럼 추가

ALTER TABLE NEW_EMP
    ADD (age number(2) default 1); // NEW_EMP 테이블에 age 칼럼 추가

ALTER TABLE 테이블명 ADD (추가할 칼럼);

 

 

3.  칼럼 변경

ALTER TABLE NEW_EMP
    MODIFY(ename varchar2(40) Not Null); // ename 칼럼의 길이를 40으로 변경하고 not null 조건 설정

ALTER TABLE 테이블명 MODIFY (변경할 칼럼);

* 기존 데이터가 존재할 경우 에러가 발생함

 

 

4.  칼럼 삭제

ALTER TABLE NEW_EMP
	DROP COLUMN age; // age 칼럼 삭제

ALTER TABLE 테이블명 DROP COLUMN 삭제할 칼럼;

 

 

5. 칼럼명 변경

ALTER TABLE NEW_EMP
	RENAME COLUMN ename to new_ename; //enme 칼럼 new_ename으로 변경

ALTER TABLE 테이블명 RENAME COLUMN 이름 변경할 칼럼 TO 변경할 이름;

 

 

< 테이블 삭제 : DELETE >

DROP TABLE NEW_EMP;

DROP TABLE 삭제할 테이블명;

* 테이블의 구조와 데이터를 모두 삭제

 

DROP TABLE NEW_EMP CASCADE CONSTRAINT

DROP TABLE 삭제할 테이블명 CASCADE CONSTRAINT;

* 해당 테이블의 데이터를 외래키로 참조한 슬레이브 테이블과 관련된 제약사항도 삭제

 

< View >

1. View란 ?

테이블로부터 유도된 가상의 테이블

실제 데이터를 가지고 있지않고, 테이블을 참조해서 원하는 칼럼만을 조회할 수 있게함.

데이터 딕셔너리에 SQL문 형태로 저장하되 실행시에 참조됨

 

2. View의 특징

- 참조된 테이블이 변경되면 뷰도 변경

- 뷰의 검색은 참조된 테이블과 동일하게 가능하지만 뷰에 대한 입력, 수정, 삭제에는 제약이 있음

- 특정 칼럼만 조회해 보안성 향상

- 한번 생성된 뷰는 변경 X (변경을 원하면 삭제 후 재생성, ALTER문 사용 불가)

 

3. View 생성

CREATE VIEW T_EMP AS
	SELECT * FROM EMP; // EMP 테이블을 조회해 T_EMP라는 뷰를 생성

CREATE VIEW 뷰 이름 AS SLECET 참조할 칼럼 FROM 참조할 테이블

 

 

4. View 조회

SELECT * FROM T_EMP; 

SELECT 조회할 뷰 칼럼 FROM 조회할 뷰;

* 일반 테이블 조회와 동일

 

 

4. View 삭제

DROP VIEW T_EMP;

DROP VIEW 삭제할 뷰 이름;

* 참조했던 테이블이 삭제되는것은 아님