2-1장 SQL 기본 - 2절 DDL(Data Definition Language)

1. 데이터 유형

CHARACTERS
고정길이 문자열 정보. CHAR로 표현
기본 길이 1바이트, 최대 길이 Oracle 2000 바이트, SQL는 8000 바이트
고정길이를 가지고 있으므로 할당된 변수 값의 길이가 s보다 작을 경우 그 차이 길이만큼 공간으로 채워진다.

 

VARCHAR
가변길이 문자열 정보 Oracle은 VARCHAR2로, SQL은 VARCHAR로 표현
최소 길이 1바이트, 최대 길이 Oracle 4000 바이트, SQL는 8000 바이트
가변 길이로 조정이 되기 때문에 할당된 변수의 바이트만 적용됨(Limit 있음)

 

NUMERIC
정수, 실수 등 숫자정보 Oracle은 Number로, SQL은 10가지 숫자 타입을 가지고 있음
Oracle은 처음에 전체 자리 수를 지정, 그 다음 소수 부분을 지정함 ex) (8,2): 전체 글자수는 8인데, 소수점 부분은 2자리(나머지 6자리는 정수 부분)

 

DATETIME
날짜와 시각정보, Oracle은 DATE, SQL은 DATETIME으로 표현
Oracle은 1초 단위, SQL은 3,33ms 단위 관리

CHAR와 VARCHAR 유형은 비교 방법의 차이
CHAR: 문자열을 비교할때 공백(BLANK)을 채워서 비교하는 방법을 사용한다.('AA' = 'AA ')
VARCHAR: 공백도 하나의 문자로 취급하므로 끝의 공백이 다르면 다른 문자로 판단한다. ('AA' != 'AA ')

 

2. CREATE TABLE

명령어 설명
테이블에 존재하는 모든 데이터를 고유하게 식별할 수 있으면서 반드시 존재하는 단일 칼럼이나 칼럼의 조합들 중 하나를 선정하여 기본키 칼럼으로 지정
테이블과 테이블 간의 정의된 관계는 기본키-외부키를 활용하여 외부키가 참조하여 수정/삭제의 이상현상을 방지

 

테이블 구문형식
CREATE TABLE 테이블 이름 (
칼럼명 1    데이터타입    [디폴트 형식],
칼럼명 2    데이터타입    [디폴트 형식],
칼럼명 3    데이터타입    [디폴트 형식])

 

테이블 생성 시 주의해야 할 규칙
A-Z, a-z, 0-9, _, $, # 문자만 허용 된다.(알파벳, 숫자, 언더바, 달러, 샵만 사용가능)
테이블 생성시 대/소문자 구분은 하지 않는다. 기본적으로 테이블이나 칼럼명은 대문자로 만들어진다
테이블명과 칼럼명은 숫자가 아닌 문자로 시작해야 하고, 길이가 한계가 있다.
예약어는 사용 불가
테이블 명은 적절한 이름을 사용하며, 단수형이 좋다.(다른 테이블의 이름과 중복 X)
한 테이블 내에서는 칼럼명이 중복 X, 다른 테이블 간에는 칼럼명이 중복 O
DATETIME 데이터 유형에는 별도로 크기를 지정하지 않는다
문자 데이터 유형은 반드시 가질 수 있는 최대길이를 지정
칼럼에 대한 제약조건이 있으면 CONSTRAINT를 이용하여 추가 가능

 

제약조건
사용자가 원하는 조건의 데이터만 유지하기 위해 사용. 데이터 무결성을 유지하기 위한 방법
복제 테이블에는 기존 테이블 제약조건 중 NOT NULL만 적용
PRIMARY KEY(기본키): 하나의 테이블에는 하나의 기본키(행 데이터를 고유하게 식별) 제약만 정의. 자동으로 UNIQUE 인덱스 생성, NULL 입력 X
FOREIGN KEY(외래키): 관계형 데이터베이스에서 테이블 간의 관계를 정의하기 위해 기본키를 다른 테이블의 오래키로 복사하는 경우 외래키가 생성. 무결성 제약 옵션을 선택
UNIQUE KEY(고유키): 행 데이터를 고유하게 식별하기 위한 고유키 정의 NULL 입력 가능
NOT NULL: NULL 값 입력을 금지. 해당 칼럼은 입력을 필수로 해야함
CHECK: 입력할 수 있는 값의 범위를 제한.
※ NULL: 아직 정의되지 않은 값 또는 현재 데이터를 입력하지 못하는 값, NULL과의 1) 수치연산은 NULL 2) 비교연산은 FALSE 출력
※ DEFAULT: 데이터 입력 시 칼럼의 값이 지정되어 있지 않은 경우 기본값을 사전에 설정.데이터 입력시 명시된 값을 지정하지 않은 경우에 NULL값이 입력되고, DEFAULT값을 정의 했다면 해당 칼럼에 NULL 대신 정의된 기본값이 자동으로 입력

 

생성된 테이블 구조 확인
DESCRIBE 테이블명, sp_help ‘dbo.테이블명’ 명령어를 통해 테이블 구조 확인

 

3. ALTER TABLE

명령어 설명
업무적인 요구사항이나 시스템 운영상 테이블을 사용하는 도중 변경해야 할 일들이 발생. 주로 칼럼을 추가/삭제, 제약조건을 추가/삭제하는 작업 진행

 

ADD COLUMN
기존 테이블에 칼럼을 추가. 마짐가 칼럼으로 추가됨(칼럼위치 지정불가)
명령어: ALTER TABLE 테이블명 ADD (칼럼명 데이터타입);

 

DROP COLUMN
테이블에서 필요 없는 칼럼 삭제 칼럼 삭제 후 최소 하나 이상의 칼럼이 테이블에 존재함 삭제 후 복구 불가
명령어: ALTER TABLE 테이블명 DROP COLUMN 칼럼명;

 

MODIFY COLUMN
칼럼의 데이터 유형, 디폴드 값, 제약조건에 대한변경
칼럼의 크기를 늘릴 수는 있지만 줄이지는 못한다.
칼럼이 NULL값만 가지고 있거나 테이블에 아무 행도 없으면 칼럼의 폭을 줄일 수 있음
해당 컬럼이 NULL 값만 가지고 있으면 데이터 유형 변경 가능
해당 칼럼의 DEFAULT 값을 바꾸면 변경작업이후 발생하는 행 삽입에만 영향을 미침
해당 칼럼에 NULL값이 없을 경우에만 NOT NULL 제약조건 추가 가능
명령어: ALTER TABLE 테이블명 MODIFY (칼럼명 데이터타입 제약조건);

 

RENAME COLUMN
테이블을 생성하면서 만들어졌던 칼럼명을 어떤 이유로 불가피하게 변경
명령어: ALTER TABLE 테이블명 RENAME COLUMN 변경해야 할 칼럼명 TO 새로운 칼럼명;

 

DROP CONSTRAINT
테이블 생성 시 부여했던 제약조건을 삭제
명령어: ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명

 

ADD CONSTRAINT
테이블 생성 시 제약조건을 적용하지 않았다면, 생성 이후에 필요에 의해서 제약조건을 추가
명령어: ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (칼럼명)

 

4. RENAME TABLE

테이블 이름을 변경할 때 사용
명령어: RENAME 테이블명 TO 테이블명; / ALTER TABLE 테이블명 RENAME TO 테이블명’으로도 가능함

 

5. DROP TABLE

테이블을 잘못 만들었거나, 더 이상 필요 없는 경우 테이블을 삭제. 테이블의 데이터와 구조 삭제, 복구 불가
CASCADE CONSTRAINT 옵션으로 관련 테이블의 참조 제약조건도 삭제하여 참조 무결성 유지(CREATE TABLE에서 ON DELETE CASCADE 옵션으로도 동일 기능 실현 가능)
SQL 서버에서는 CASCADE 옵션이 존재하지 않으며 테이블을 삭제하기 전 참조하는 FOREIGN KEY 제약조건 또는 참조하는 테이블 먼저 삭제
명령어: DROP TABLE 테이블명;

 

6. TRUNCATE TABLE

테이블의 전체 데이터 삭제. 모든 행이 제거되고, 저장공간 재사용가능(↔ DROP TABLE은 테이블 자체를 제거함)
로그를 기록하지 않기 때문에 ROLLBACK 불가
명령어: TRUNCATE TABLE 테이블명

 

7. DROP vs TRUNCATE vs DELETE 비교 

DROP	TRUNCATE	DELETE
DDL	DDL(일부 DML 성격)	DML
RollBack 불가	RollBack 불가	Commit 이전 RollBack 가능
Auto Commit	Auto Commit	사용자 Commit
테이블이 사용했던 Storage 모두 Release	테이블이 사용했던 Storage중 최초 테이블 생성시 할당된 Storage만 남기고 Release	데이터를 모두 Delete 해도 사용했던 Storage는 Release 되지 않음
테이블 정의 자체 완전 삭제	테이블 최초 생성된 초기상태 만듬	데이터만 삭제 (로그 남음)