SQLD - 과목2. (2) DDL

1. 데이터 유형

▷ 데이터 유형

    : 데이터베이스의 테이블에 특정 자료를 입력할 때, 그 자료를 받아들일 공간을 자료의 유형별로 나누는 기준이다.

      선언한 유형이 아닌, 다른 종류의 데이터가 들어오려고 하면 데이터베이스는 에러를 발생시킨다.

 

선언 당시에 지정한 데이터의 크기(SIZE)도 중요하다. 크기를 넘어선 자료가 입력되면 에러를 발생시킨다.

 

 

 

 

 

※ 숫자타입

 

ANSI / ISO 기준에서 NUMERIC Type 의 하위 개념으로,

NUMERIC, DECIMAL, DEC, SMALLINT, INTEGER, INT, BIGINT, FLOAT, REAL, DOUBLE PRECISION 이 있다.

 

SQL Server, Sybase 는,

작은 정수형, 정수형, 큰 정수형, 실수형 등 여러 숫자 타입을 제공하고,

MONEY, SMALLMONEY 등의 숫자 타입도 제공한다.

 

Oracle 은,

숫자형 타입에 대해서 NUMBER 한 가지 숫자 타입의 데이터 유형만 지원한다.

 

 

 

 

 

 

※ 자주 쓰는 데이터 유형

데이터 유형	설명
CHARACTER(s)	- 고정 길이 문자열 정보 (Oracle, SQL Server 모두 CHAR로 표현)  - s는 기본길이 1 바이트    최대 길이 : Oracle (2,000 바이트) , SQL Server (8,000 바이트)  - s 만큼 최대 길이를 갖고 고정 길이를 가지고 있으므로 할당된 변수 값    의 길이가 s 보다 작을 경우에는 그 차이 길이 만큼 공간으로 채워진다.
VARCHAR(s)	- Character Varying 의 약자로 가변 길이 문자열 정보.  - s는 최소 길이 1 바이트    최대 길이 : Oracle (4,000 바이트) , SQL Server (8,000 바이트)  - s 만큼 최대 길이를 갖지만, 가변 길이로 조정이 되기 때문에 할당된    변수 값의 바이트만 적용된다. ( Limit 개념 )
NUMERIC	- 정수, 실수 등 숫자 정보.    ( Oracle 은 NUMBER  /  SQL Server 는 10가지 이상의 숫자 타입 )  - Oracle ; 처음에 전체 자리 수를 지정하고 소수 부분의 자리 수를 지정.     ( ex : NUMBER(8,2) ; 정수 6자리 소수점 2자리 )
DATETIME	- 날짜와 시각 정보.    ( Oracle 은 DATE  /  SQL Server 는 DATETIME )  - Oracle ; 1초 단위  - SQL Server ; 3.33ms (millisecond) 단위 관리

 

 

 

※ CHAR vs VARCHAR 비교

 

1) 저장 영역과 문자열의 비교 방법

 

VARCHAR : 길이가 다양한 칼럼, 정의된 길이와 실제 데이터 길이에 차이가 있는 칼럼에 적합하다.

                저장 측면에서도 CHAR 유형보다 작은 영역에 저장할 수 있다.

                VARCHAR(40) 으로 40 바이트가 지정되어도, 'ABC' 는 3 바이트의 공간만 차지한다.

                고정된 길이의 문자열이 아니라면 VARCHAR 유형을 적용하는 것이 좋다.

 

 

2) 비교 방법의 차이

 

CHAR : 문자열을 비교할 때 공백(BLANK) 을 채워서 비교한다.

          공백 채우기 비교이므로, 끝의 공백만 다른 문자열은 같다고 판단된다.

 

VARCHAR : 맨 처음부터 한 문자씩 비교하여 공백도 하나의 문자로 취급한다.

                끝의 공백이 다르면 다른 문자로 판단한다.

 

CHAR : 'AA' = ' AA' = 'AA ' = ' AA '

VARCHAR : 'AA' ≠ ' AA'

 

 

 

 

 

 

 

 

 

---

2. CREATE TABLE

① 테이블과 칼럼 정의

 

테이블 생성을 위해서, 해당 테이블에 입력될 테이터를 정의하고, 정의한 데이터를 어떠한 데이터 유형으로 선언할 것인지 결정해야 한다.

 

모든 데이터를 고유하게 식별할 수 있으면서 반드시 값이 존재하는 단일 칼럼,

또는 칼럼의 조합들 (후보키) 중에 하나를 선정하여 기본키 칼럼으로 지정한다.

기본키는 단일 칼럼이 아닌 여러 개의 칼럼으로도 만들수 있다.

 

테이블 - 테이블 간의 정의된 관계는 기본키( PRIMARY KEY) 와 외부키( FOREIGN KEY) 를 활용한다.

이상 현상 ( 수정/삭제 이상 현상 등 ) 이 발생하지 않도록 테이블 분리, 외부키 참조를 잘 해야 한다.

데이터 모델링 및 정규화에 대한 내용은 업무를 개선시킬 수 있는 고급 SQL 작성에 필요한 내용이다.

 

 

 

---

② CREATE TABLE

 

CREATE TABLE 테이블이름 ( 칼럼명1 DATATYPE [DEFAULT 형식],
	 		  칼럼명2 DATATYPE [DEFAULT 형식],
                          칼럼명3 DATATYPE [DEFAULT 형식],
                        );

 

(예시)

CREATE TABLE PLAYER ( PLAYER_ID CHAR(7) NOT NULL,
		    PLAYTER_NAME VARCHAR(20) NOT NULL,
      	   	    TEAM_ID CHAR(3) NOT NULL,
       	  	    BIRTH DATE,
        	    HEIGHT NUMBER(3),
        	    CONSTRAINT PLAYER_PK PRIMARY KEY (PLAYER_ID)
         	   );

 

 

※ 테이블 생성 시 규칙

 

(1) 테이블명은 객체의 의미할 수 있는 적절한 이름을 사용한다. ( 가능한 단수형 )

(2) 테이블명은 다른 테이블 이름과 중복되지 않아야 한다.

(3) 한 테이블 내에서는 칼럼명이 중복되게 지정될 수 없다.

(4) 각 칼럼들은 괄호 "( )" 로 묶어 지정한다.

(5) 각 칼럼들은 콤마 "," 로 구분되고, 테이블 생성문의 끝은 세미콜론 ";" 으로 끝난다.

(6) 칼럼 뒤에 데이터 유형은 꼭 지정되어야 한다.

(7) 문자는 A-Z, a-z, 0-9, _, $, # 만 허용된다.

 

(8) 테이블 생성시 대/소문자 구분은 하지 않는다.

(9) DATETIME 데이터 유형에는 별도로 크기를 지정하지 않는다.

(10) 문자 데이터 유형은 반드시 가질 수 있는 최대 길이를 표시해야 한다.

(11) 칼럼에 대한 제약조건이 있으면 CONSTRAINT 를 이용하여 추가한다.

 

한 테이블 안에서 칼럼명은 달라야 하지만, 다른 테이블의 칼럼 이름과는 같을 수 있다.

 

 

 

 

---

③ 제약조건 ( CONSTRAINT )

 

▷ 제약조건 : 사용자가 원하는 조건의 데이터만 유지하기 위한,

                  즉, 데이터의 무결성을 유지하기 위한 데이터베이스의 보편적인 방법으로 테이블의 특정 칼럼에 설정하는 제약.

 

 

 

※ 제약조건의 종류

제약조건의 종류

 

 

* NULL 의미

  : ' 아직 정의되지 않은 미지의 값 ' 또는 ' 현재 데이터를 입력하지 못하는 경우 '

 

* DEFAULT 의미

  : 칼럼의 값이 지정되어 있지 않을 경우 기본값 (DEFAULT) 을 사전에 설정한다.

    데이터 입력 시 명시된 값을 지정하지 않은 경우에 NULL 값이 입력된다.

    DEFAULT 값을 정의한 경우, 해당 칼럼에 NULL 값 대신 정의된 기본 값이 자동으로 입력된다.

 

 

 

 

---

④ 생성된 테이블 구조 확인

 

* Oracle - " DESCRIBE 테이블명; " 또는 " DESC 테이블명; " 으로 정보 확인 가능하다.

* SQL Server - " sp_help 'dbo.테이블명' " 으로 정보 확인 가능하다.

 

 

 

 

⑤ SELECT 문장을 통한 테이블 생성

 

DML 문장 중 SELECT 문장을 활용하여 CTAS ( Create Table ~ As Select ) 으로 테이블을 생성할 수 있다.

CTAS 방법의 장점은 칼럼 별로 데이터 유형을 다시 재정의 하지 않아도 되는 것이다.

CTAS 기법 사용 시 기존 테이블의 제약조건 중 NOT NULL 만 새로운 복제 테이블에 적용되고, 기본키, 고유키, 외래키 등의 다른 제약조건은 없어진다. 제약조건을 추가하기 위해 ALTER TABLE 기능을 사용한다.

SQL Server 에서는 Select ~ Into ~ 를 활용한다.

 

Oracle
CREATE TABLE 테이블명1 AS SELECT * FROM 테이블명2;

SQL Server
SELECT * INTO 테이블명1 FROM 테이블명2;

 

 

 

 

 

 

 

 

 

---

3. ALTER TABLE

테이블을 사용하는 도중에 변경해야 할 일이 발생할 수 있다. 

칼럼을 추가/삭제 또는 제약조건을 추가/삭제 하는 작업을 한다.

 

 

① ADD COLUMN

 

주의할 점 : 새롭게 추가된 칼럼은 테이블의 마지막 칼럼이 되며, 칼럼의 위치는 지정할 수 없다.

ALTER TABLE 테이블명 ADD <추가할 칼럼명> <데이터 유형> ;

 

(예시)

Oracle
ALTER TABLE PLAYER ADD (ADDRESS VARCHAR2(80));

SQL Server
ALTER TABLE PLAYER ADD ADDRESS VARCHAR(80);

 

 

---

② DROP COLUMN

 

테이블에서 필요 없는 칼럼을 삭제할 수 있는 기능이다.

데이터가 있든 없든 삭제 가능하며, 한 번에 하나의 칼럼만 삭제할 수 있다.

칼럼 삭제 후 최소 하나 이상의 칼럼은 데이블에 존재해야 하며, 한 번 삭제된 칼럼은 복구가 불가능하다.

 

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

 

(예시)

ALTER TABLE PLAYER DROP COLUMN ADDRESS;

 

 

---

③ MODIFY COLUMN

 

ALTER TABLE 명령을 이용해 칼럼의 데이터 유형, DEFAULT 값, NOT NULL 제약조건에 대한 변경을 한다.

 

Oracle
ALTER TABLE 테이블명 MODIFY (칼럼명1 데이터 유형 [DEFALUT 식][NOT NULL], ...);

SQL Server
ALTER TABLE 테이블명 ALTER (칼럼명1 데이터 유형 [DEFAULT 식][NOT NULL], ...);

 

 

※ 주의사항

(1) 해당 칼럼의 크기를 늘릴 수는 있지만 줄일 수는 없다. ( 기존의 데이터 훼손 가능성 )

(2) 해당 칼럼이 NULL 값만 가지고 있거나 테이블에 아무 행도 없으면 칼럼의 폭을 줄일 수 있다.

(3) 해당 칼럼이 NULL 값만 가지고 있으면 데이터 유형을 변경할 수 있다.

(4) 해당 칼럼의 DEFAULT 값을 바꾸면 변경 작업 이후 발생하는 행 삽입에만 영향을 미친다.

(5) 해당 칼럼에 NULL 값이 없을 경우에만 NOT NULL 제약조건을 추가할 수 있다.

 

 

 

 

* RENAME COLUMN

 

▷ RENAME COLUMN : 칼럼명을 불가피하게 변경해야 하는 경우에 사용하는 문구.

 

 

Oracle
ALTER TABLE 테이블명 RENAME COLUMN 변경할 칼럼명 TO 새로운 칼럼명;

SQL Server
sp_rename 변경할 칼럼명, 새로운 칼럼명, 'COLUMN';

 

 

---

④ DROP CONSTRAINT

 

테이블 생성 시 부여했던 제약조건을 삭제하는 명령어이다.

 

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

 

(예시)

ALTER TABLE PLAYER DROP CONSTRAINT PLAYER_FK;

// PLAYER 테이블의 외래키 제약조건을 삭제

 

 

---

⑤ ADD CONSTRAINT

 

테이블 생성 시 제약조건을 적용하지 않았을 때, 생성 이후에 제약조건을 추가하는 명령어이다.

특정 칼럼에 제약조건을 추가하는 형태이다.

 

 

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

 

(예시)

ALTER TABLE PLAYER ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID);

// PLAYER 테이블에 TEAM 테이블과의 외래키 제약조건 추가
// PLAYER 테이블의 TEAM_ID 칼럼이 TEAM 테이블의 TEAM_ID 를 참조

 

※ 주의사항

참조 무결성 옵션에 따라서 테이블의 데이터를 삭제하려 할 경우 외부에서 참조되고 있다면, 삭제가 불가능하게 제약을 할 수 있다.

즉, 외부키(FK) 를 설정함으로써 실수에 의한 테이블 삭제나 필요한 데이터의 의도하지 않은 삭제와 같은 불상사를 방지한다.

 

 

DELETE TEAM WHERE TEAM ID = 'ABC';

ERROR: 무결성 제약조건이 위배되었다. 자식 레코드가 발견되었다.
       데이터는 삭제되지 않음

 

 

 

 

 

 

 

 

 

---

4. RENAME TABLE

테이블의 이름을 변경하는 명령어이다.

 

RENAME 변경전 테이블명 TO 변경후 테이블명;

SQL Server
sp_rename 변경전 테이블명, 변경후 테이블명;

 

 

 

 

---

5. DROP TABLE

테이블을 삭제할 때 사용하는 명령어이다.

 

DROP TABLE 테이블명 [CASCADE CONSTRAINT];

 

DROP 명령어 사용시 테이블의 모든 데이터 및 구조를 삭제한다.

CASCADE CONSTRAINT 옵션은 해당 테이블과 관계가 있었던 참조되는 제약조건에 대해서 삭제한다.

SQL Server 에서는 CASCADE 옵션이 없다.

테이블 삭제하기 전에, 참조하는 FOREIGN KEY 제약 조건과 참조 테이블을 먼저 삭제해야 한다.

 

 

 

 

---

6. TRUNCATE TABLE

테이블을 삭제하는 것이 아닌, 해당 테이블에 들어있던 모든 행들이 제거되고 저장 공간을 재사용 가능하도록 한다.

 

TRUNCATE TABLE 테이블명;

 

TRUNCATE TABLE 은 테이블 구조를 그대로 유지한 체 데이터만 전부 삭제하는 기능이다.

( DROP TABLE 은 테이블 자체가 없어지므로 테이블 구조 확인 불가능 )

 

 

TRUNCATE 는 데이터 구조의 변경 없이 테이블의 데이터를 일괄 삭제하므로 DML 로 분류할 수도 있다.

하지만, 내부 처리 방식이나 Auto Commit 특성으로 DDL 로 분류된다.

 

DELETE 와 TRUNCATE 의 처리 방식이 다르다.

시스템 활용 측면에서는 DELETE 보다 시스템 부하가 적은 TRUNCATE 가 좋다.

TRUNCATE 의 경우 정상적인 복구가 불가능하므로 주의해야 한다.

 

 

 

 

 

 

 

 

---