[SQLD] 2과목: SQL 기본 및 활용 - Chapter1. SQL 기본 - (2) DDL (Data Definition Language)

• DDL
  • 스키마, 도메인, 테이블, 뷰, 인덱스를 정의/변경/제거할 때 사용
  • auto commit이 되기 때문에 DDL이 수행되면 ROLLBACK해도 데이터 COMMIT이 된다.

 

1. CREATE

• 테이블을 생성하기 위한 명령어

CREATE TABLE 테이블명(
컬럼명1 데이터 타입 (DEFAULT/NULL 여부),
컬럼명2 데이터 타입 (DEFAULT/NULL 여부),
...
);

 

• 테이블 생성 시 반드시 지켜야 할 규칙
  • 테이블명은 고유해야 한다
  • 한 테이블 내에서 칼럼명은 고유해야 한다
  • 칼럼명 뒤에 데이터 유형과 데이터 크기가 명시되어야 한다
  • 칼럼에 대한 정의는 괄호() 안에 기술한다
  • 각 컬럼들은 콤마(,)로 구분된다
  • 테이블명과 컬럼명은 숫자로 시작될 수 없다
  • 마지막은 세미콜론(;)으로 끝난다

 

• 에러를 발생시키지는 않지만 지키지 않으면 피곤해지는 항목들...
  • 테이블은 각각의 정체성은 나타내는 이름을 가져야 한다
  • 칼럼명을 정의할 때는 다른 테이블과 통일성이 있어야 한다

 

• CTAS문
  • 기존에 존재하던 테이블을 복사해서 생성하고 싶은 경우, CTAS(CREATE TABLE ~ AS SELECT~)문을 활용할 수 있다.
  • 칼럼별로 데이터 유형을 다시 명시해주지 않아도 된다.
  • 다만 NOT NULL 제약 조건만 복사가 되며 PK, UNIQUE KEY, CHECK 등의 제약조건은 초기화 되므로 필요할 경우 ALTER 명령을 써서 별도로 정의해주어야 한다.

CREATE TABLE 테이블명 AS SELECT * FROM 복사할 테이블명;

 

• CTAS문 응용: 테이블 구조만 복사하여 생성

CREATE TABLE EMP3 AS
SELECT * 
FROM EMP
WHERE 1=2;

• WHERE에 FALSE조건을 입력하여 만든 CTAS문. 테이블의 구조만 복사하고 로우는 복사하지 않는 방식.

 

• 제약조건 (CONSTRAINT)
  • 데이터의 무결성을 유지하기 위한 방법, 사용자가 원하는 조건의 데이터만 유지하기 위한 방법
  • Alter Table에서도 설정 가능하다

 

• 제약조건의 종류

제약조건 종류	설명
Primary Key (기본키)	- 한 테이블에 하나만 지정 가능 - 자동으로 UNIQUE한 인덱스 생성 - NULL값 입력 불가
UNIQUE Key (고유키)	- 테이블에 저장된 각각의 Row에 대한 고유성 보장 (Primary key와 유사) - NULL값 허용
NOT NULL	- 해당 칼럼에 NULL값이 입력되는 것을 명시적으로 허용하지 않는 제약조건
CHECK	- 칼럼에 저장될 수 있는 값의 범위 제한 - 데이터 무결성을 유지하기 위해 특정 칼럼에 설정
Foreign key (외래키)	- 참조 무결성 옵션 선택 가능 - 여러 개 가능

 

• PK제약조건 생성하는 DDL
  • CREATE문 안에 생성하는 방법

CREATE TABLE EMP
(EMP_NO VARCHAR2(10) PRIMARY KEY, 
EMP_NM VARCHAR2(300) NOT NULL

CREATE TABLE PRODUCT
(PROD_ID VARCHAR2(10) NOT NULL,
PROD_NM VARCHAR2(100) NOT NULL,
REG_DT DATE NOT NULL,
REGR_NO NUMBER(10),
CONSTRAINT PRODUCT_PK PRIMARY KEY(PROD_ID));

• CREATE문 밖에 생성하는 방법 (ALTER TABLE)

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (col_1, col_2,...)

 

• NULL
  • 존재하지 않음, 즉 값이 없음을 의미. 알 수 없는 값
  • 0 도 아니고 공백 ('  ')도 아님
  • NULL은 "IS NULL", "IS NOT NULL"로만 비교 가능 (<>NULL, !=NULL 이런거 안 됨)
  • SQL Server에서 a=''로 넣으면 NULL이 아닌 공백값이 들어감 -> IS NULL 시 FALSE 반환

 

• 외래키(FK) 참고사항
  • 테이블 생성 시 설정할 수 있다
  • 외래키는 NULL값 가질 수 있다
  • 한 테이블에 여러 개 존재 가능
  • 참조 무결성 제약을 받는다

2. ALTER

(1) ALTER TABLE + ADD COLUMN: 칼럼 추가

• 기존 테이블에 새롭게 칼럼을 추가하고 싶은 경우 사용
• 추가된 칼럼의 위치는 늘 맨 끝. 별도 위치 지정 불가능

ALTER TABLE 테이블명
ADD (칼럼이름 칼럼의유형 제약조건,
	칼럼이름2 칼럼의유형 제약조건,
    칼럼이름3 칼럼의유형 제약조건...);

 

(2) ALTER TABLE + DROP COLUMN: 칼럼 삭제

• 기존에 있던 칼럼이 필요 없어졌을 때 삭제하는 명령어
• 한번 삭제한 칼럼은 복구 불가
• 데이터가 있거나 없거나 모두 삭제 가능. 
• 한번에 하나의 칼럼만 삭제 가능.

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

 

(3) ALTER TABLE + MODIFY : 칼럼의 속성 변경

• 기존에 있던 칼럼을 변경하고 싶을 때 사용하는 명령어
• 데이터 유형, DEFAULT값, NOT NULL 제약조건에 대한 변경 가능
• 칼럼에 저장된 모든 데이터의 크기가 줄이고자 하는 칼럼의 크기보다 작을 경우에만 줄일 수 있다 (크기를 늘리는 것은 데이터와 상관없이 가능)
• 칼럼에 저장된 데이터가 없는 경우에만 데이터 유형을 변경할 수 있다

ALTER TABLE 테이블명 MODIFY (칼럼명1 데이터유형 [DEFAULT값] [NOT NULL],
			칼럼명2 데이터유형 ...);

 

(4) ALTER TABLE + RENAME COLUMN A TO B: 칼럼명 변경

• 기존에 있던 칼럼의 이름을 변경하고 싶을 때 쓰는 명령어

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

 

(5) RENAME A TO B: 테이블명 변경

• 기존에 존재하는 테이블명을 변경하고 싶을 때 쓰는 명령어

<오라클>
RENAME 기존테이블명 TO 새로운테이블명;

<SQL SERVER>
SP_NAME '기존테이블명', '새로운테이블명';

 

(6) ALTER TABLE + ADD CONSTRAINT : 제약조건 추가 (변경 희망 시 삭제 후 다시 ADD)

• CONSTRAINT는 수정하는 개념은 없기때문에 수정을 원할 경우 기존 CONSTRAINT를 삭제하고 새롭게 만들어야 한다.

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (칼럼명);

 

(7) ALTER TABLE + DROP CONSTRAINT: 제약조건 삭제

ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명 제약조건 (칼럼명);

 

• ALTER TABLE로 칼럼명 변경 시 주의사항
  • 칼럼의 길이 (크기)를 늘리는 것은 자유롭지만, 값이 존재할 경우 크기는 줄일 수 없다. 
    (NULL값만 가지거나 아무행도 존재하지 않으면 길이 줄이기 가능)
  • 해당 칼럼이 NULL값만 가지고 있으면 타입(숫자,문자) 변경 가능
  • NULL이 없는 경우에만 NOT NULL 제약조건 추가 가능
    (이미 NULL이 존재하는 경우 NOT NULL 제약조건 추가 불가)
  • DEFAULT값을 설정하는 경우, 변경 작업 이후 발생하는 행 삽입에 대해서만 DEFAULT값이 영향

 

(8) ALTER TABLE (SQL server)

• 칼럼 추가

ALTER TABLE 테이블명 ADD 추가칼럼명 Dtype;

• 칼럼 삭제

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

• 칼럼 속성 변경

ALTER TABLE 테이블명 ALTER COLUMN 칼럼명 Dtype;

 

 

3. DROP TABLE 

• 테이블을 삭제할 때 쓰는 명령어
• 테이블 구조 및 행까지 모두 영구적으로 완벽하게 삭제한다
• 해당 테이블을 참조하고 있는 다른 테이블이 존재하는 경우 CASCADE 옵션을 명시하지 않으면 삭제되지 않는다.

DROP TABLE 테이블명 CASCASE CONSTRAINT;

• 테이블을 삭제하되 참조 제약에 걸린 것까지 연쇄적으로 제거해라. (나는 너와의 관계를 끊어내고 사라지겠다)
• SQL Server에는 CASCADE 옵션이 없다.

 

4. TRUNCATE TABLE

• 테이블에 저장되어 있는 데이터를 모두 제거 (모든 행 제거) -> 테이블 비우기
• 테이블 삭제가 아닌, 해당 테이블의 모든 행만 제거 후 저장공간을 재사용하도록 해제해준다.
• DELETE 명령어와 유사하지만 저장 공간이 재사용되도록 초기화된다는 차이점이 있고 ROLLBACK이 불가능해 DDL로 분류

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

 

 

• DDL vs. DML 의 삭제

종류	AUTO COMMIT 여부	삭제 구문
DDL	o	- DROP: 완전히 삭제, 원상복구 불가 - TRUNCATE: 행만 완전히 삭제, 원상복구 불가
DML	x (사용자가 COMMIT해야 함)	- DELETE: 테이블 삭제하지만 ROLLBACK으로 복구 가능

 

• 참조 동작 (Referential Action)
  • INSERT Action

구문	설명
AUTOMATIC	Child 삽입 시 Parent 테이블에 PK가 없으면, Parent PK 생성 후 Child에 삽입
SET NULL	Child 삽입 시 Parent 테이블에 PK가 없으면,  Child 외부키를 NULL값으로 세팅
SET DEFAULT	Child 삽입 시 Parent 테이블에 PK가 없으면,  Child 외부키를 지정된 Default값으로 세팅
DEPENDENT	Child 삽입 시 Parent 테이블에 PK가 존재할 때만 Child 삽입 허용
NO ACTION	참조 무결성을 위반하는 삽입 액션은 취하지 않는다

 

• DELETE / MODIFY Action

구문	설명
CASCADE	Parent 삭제 시 Child 같이 삭제
SET NULL	Parent 삭제 시 Child의 해당 필드는 NULL로 세팅
SET DEFAULT	Parent 삭제 시 Child의 해당 필드를 Default값으로 세팅
RESTRICT	Parent 삭제 시 Child 테이블에 PK가 없는 경우에만 Parent 삭제 허용
NO ACTION	참조 무결성을 위반하는 삭제/수정 액션은 취하지 않는다