[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 기본 #2

<목차>

• 데이터 유형
• CREATE TABLE
  • 테이블과 칼럼 정의
  • CREATE TABLE
  • 제약조건
  • 생성된 테이블 구조 확인
  • SELECT 문장을 통한 테이블 생성 사례
• ALTER TABLE
  • ADD COLUMN
  • DROP COLUMN
  • MODIFY COLUMN
  • RENAME COLUMN
  • DROP CONSTRAINT
  • ADD CONSTRAINT
• RENAME TABLE
• DROP TABLE
• TRUNCATE TABLE

<< DDL (Data Definition Language) >>

 

1. 데이터 유형

1. 유형

   * DB의 테이블에 특정 자료를 입력할 때, 자료를 받아들일 공간을 자료의 유형별로 나누는 기준.

   * 특정 칼럼을 정의할 때, 선언한 데이터 유형은 그 칼럼이 받아들일 수 있는 자료의 유형을 규정하게 됨.

   * 따라서 선언한 데이터 유형이 아닌 다른 유형의 데이터가 들어오는 경우 에러를 발생시킴.

   * 예시 : '체중' 칼럼에 숫자가 아닌 다른 데이터 유형이 들어오면 에러가 발생함.

2. 크기

   * 특정 칼럼을 정의할 때, 선언한 데이터의 크기를 넘어서는 자료가 들어오면 에러를 발생시킴.

3. 특징

   * 벤더별로 SQL 문장의 차이는 적지만, 데이터 유형/내장형 함수 부분에서는 차이가 많은 편.

4. 예시

   * 숫자 타입의 경우 : NUMERIC의 하위 개념 --> NUMERIC, DECIMAL, DEC, SMALLINT, INTEGER, INT, BIGINT, FLOAT, DOUBLE, REAL, PRECISION

   * SQL Server/Sybase : 작은 정수형, 정수형, 큰 정수형, 실수형 등 여러 숫자 타입 제공과 동시에 MONEY, SMALLMONEY 등의 숫자 타입도 가짐.

   * Oracle : NUMBER 한 가지의 숫자 타입만을 지원함.

자주 쓰이는 데이터 유형 4가지

• VARCHAR와 CHAR는 같은 문자열 유형이지만, VARCHAR는 가변 길이고 CHAR는 고정 길이를 가짐.

• 문자열 비교에서 VARCHAR는 공백도 하나의 문자로 취급하기 때문에 'AA' != 'AA ' 로 취급함.

• 문자열 비교에서 CHAR는 공백을 채워서 비교하기 때문에 'AA' == 'AA ' 로 취급함.

• 사번이나 주민등록번호 같이 고정된 길이의 데이터라면 CHAR를 아니라면 VARCHAR를 쓰는 것이 효율적.

• VARCHAR(40)로 지정된 경우, 문자열의 최대 길이가 40바이트라는 뜻.

 

2. CREATE TABLE

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

 

2-1. 테이블과 칼럼 정의

1. 기본키(Primary Key) 지정
   * 테이블에 존재하는 모든 데이터를 고유하게 식별(UNIQUE KEY)할 수 있으면서 반드시 값이 존재(NOT NULL)하는 단일 칼럼이나 칼럼의 조합들(후보키) 중에 하나를 선정하여 기본키 칼럼으로 지정함. (ex. 주민등록번호, 사원번호, 선수ID, 제품 일련번호)
   * 기본키는 단일 칼럼이 아닌 여러 개의 칼럼으로도 만들어질 수 있음.
   * 기본키와 다른 테이블의 외부키(Foreign Key)를 활용해 테이블 간의 관계를 설정함.

K-리그의 테이블과 칼럼 정보

• <선수 정보> 테이블과 <팀 정보> 테이블이 합쳐져 한 테이블(선수+팀)로 존재한다고 가정했을 때, 한 선수가 A팀에서 B팀으로 이적하는 경우, <선수+팀> 테이블에서 팀과 관련된 정보들을 일일히 변경시켜줘야 함.

• 팀이 해체되는 경우도 마찬가지.

• 수정/삭제 이상(Anormaly) 현상이 발생할 수 있기 때문에 팀 정보와 선수 정보를 따로 나누고 <팀 정보> 테이블에서 팀 ID를 기본키로 설정하고 <선수 정보> 테이블에서는 팀 ID를 외부키로 참조하게 한다.

• 정규화의 내용은 이후에 자세하게 다룰 예정

 

2-2. CREATE TABLE

1. 구문 형식

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

2. 테이블 생성 규칙

   * 테이블 명은 객체를 의미할 수 있는 적절한 이름 사용. 가능한 단수형.

   * 테이블 명은 다른 테이블의 이름과 중복되지 않아야 함.

   * 한 테이블 내에서는 칼럼명이 중복될 수 없음.

   * 테이블 명 지정 후 "( )" 처리.

   * 각 칼럼은 "," 로 구분됨.

   * 테이블 생성문 끝은 항상 세미콜론 ";".

   * 칼럼은 다른 테이블까지 고려하여 일관성 있게 사용. (데이터 표준화 관점)

   * 칼럼 뒤에 데이터 유형은 반드시 지정되어야 함.

   * 테이블 명/칼럼명은 반드시 문자로 시작해야 함. (a-z, A-Z, 0-9, _, $, #)

   * 사전 예약어는 사용할 수 없음.

   * 대/소문자 구분하지 않음. 기본적으로 대문자로 생성됨.

   * 문자 데이터 유형은 최대 길이 명시해야 함.

   * 칼럼에 대한 제약조건은 CONSTRAINT를 이용해서 추가할 수 있음.

3. 예시 (Oracle)

   CREATE TABLE PLAYER ( 
       PLAYER_ID CHAR(7) NOT NULL, 
       PLAYER_NAME VARCHAR2(20) NOT NULL, 
       TEAM_ID CHAR(3) NOT NULL, 
       E_PLAYER_NAME VARCHAR2(40), 
       NICKNAME VARCHAR2(30), 
       JOIN_YYYY CHAR(4), 
       POSITION VARCHAR2(10), 
       BACK_NO NUMBER(2), 
       NATION VARCHAR2(20), 
       BIRTH_DATE DATE, 
       SOLAR CHAR(1), 
       HEIGHT NUMBER(3), 
       WEIGHT NUMBER(3), 
       /* CONSTRAINT을 통한 제약조건 */
       CONSTRAINT PLAYER_PK PRIMARY KEY (PLAYER_ID), 
       CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID) 
   );

4. 예시 (SQL Server)

   CREATE TABLE PLAYER ( 
       PLAYER_ID CHAR(7) NOT NULL, 
       PLAYER_NAME VARCHAR(20) NOT NULL, 
       TEAM_ID CHAR(3) NOT NULL, 
       E_PLAYER_NAME VARCHAR(40), 
       NICKNAME VARCHAR(30), 
       JOIN_YYYY CHAR(4), 
       POSITION VARCHAR(10), 
       BACK_NO TINYINT, 
       NATION VARCHAR(20), 
       BIRTH_DATE DATE, 
       SOLAR CHAR(1), 
       HEIGHT SMALLINT, 
       WEIGHT SMALLINT, 
       CONSTRAINT PLAYER_PK PRIMARY KEY (PLAYER_ID), 
       CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID) 
   );

 

2-3. 제약조건 (CONSTRAINT)

1. 정의
   * 데이터의 무결성을 유지하기 위한 DB의 보편적인 방법으로 테이블의 특정 칼럼에 설정하는 제약.
   * 반드시 설정할 필요는 없음. 이후 ALTER TABLE을 통해 추가 가능
   * 이미 데이터가 존재하는 테이블의 경우라면 처리 과정이 쉽지 않으므로 초기 테이블 생성 시점부터 적합한 제약 조건에 대해 충분히 검토해야함.

2. 종류

   

   제약조건의 종류

   * NULL
     : 공백이나 숫자 0, 공집합과는 전혀 다른 값. 아직 정의되지 않은 미지의 값이거나 현재 데이터를 입력하지 못하는 경우를 의미.
   * DEFAULT
     : 데이터의 기본값을 사전에 설정할 수 있음. DEFAULT로 특정 값을 정의했다면 NULL이 아니라 정의한 특정 값이 자동으로 입력된다.
     : 하지만 해당 칼럼에 대해 NULL인 데이터가 존재하는 상태에서 DEFAULT 값을 설정하면 기존의 NULL 값은 그대로 NULL로 유지된다. 변경 이후의 ROW에 대해서만 DEFAULT가 적용된다.

3. 예시 (Oracle)

   CREATE TABLE TEAM ( 
       TEAM_ID CHAR(3) DEFAULT '001' NOT NULL, 
       REGION_NAME VARCHAR2(8) NOT NULL, 
       TEAM_NAME VARCHAR2(40) NOT NULL, 
       E_TEAM_NAME VARCHAR2(50), 
       ORIG_YYYY CHAR(4), 
       STADIUM_ID CHAR(3) NOT NULL, 
       ZIP_CODE1 CHAR(3), 
       ZIP_CODE2 CHAR(3), 
       ADDRESS VARCHAR2(80), 
       DDD VARCHAR2(3), 
       TEL VARCHAR2(10), 
       FAX VARCHAR2(10), 
       HOMEPAGE VARCHAR2(50), 
       OWNER VARCHAR2(10), 
       CONSTRAINT TEAM_PK PRIMARY KEY (TEAM_ID), 
       CONSTRAINT TEAM_FK FOREIGN KEY (STADIUM_ID) REFERENCES STADIUM(STADIUM_ID) 
   );

4. 예시 (SQL Server)

   CREATE TABLE TEAM ( 
       TEAM_ID CHAR(3) DEFAULT '001' NOT NULL, 
       REGION_NAME VARCHAR(8) NOT NULL, 
       TEAM_NAME VARCHAR(40) NOT NULL, 
       E_TEAM_NAME VARCHAR(50), 
       ORIG_YYYY CHAR(4), 
       STADIUM_ID CHAR(3) NOT NULL, 
       ZIP_CODE1 CHAR(3), 
       ZIP_CODE2 CHAR(3), 
       ADDRESS VARCHAR(80), 
       DDD VARCHAR(3), 
       TEL VARCHAR(10), 
       FAX VARCHAR(10), 
       HOMEPAGE VARCHAR(50), 
       OWNER VARCHAR(10), 
       CONSTRAINT TEAM_PK PRIMARY KEY (TEAM_ID), 
       CONSTRAINT TEAM_FK FOREIGN KEY (STADIUM_ID) REFERENCES STADIUM(STADIUM_ID) 
   );

 

2-4. 생성된 테이블 구조 확인

1. 예시 (Oracle)

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

   DESCRIBE PLAYER; DESC PLAYER;   /* 실행 결과 칼럼                NULL 가능   데이터 유형 ------------------ ----------- --------------  PLAYER_ID           NOT NULL   CHAR(7) PLAYER_NAME         NOT NULL   VARCHAR2(20)     TEAM_ID             NOT NULL   CHAR(3)     E_PLAYER_NAME                  VARCHAR2(40)  NICKNAME                       VARCHAR2(30)  JOIN_YYYY                      CHAR(4)  POSITION                       VARCHAR2(10)  BACK_NO                        NUMBER(2)  NATION                         VARCHAR2(20)  BIRTH_DATE                     DATE  SOLAR                          CHAR(1)  HEIGHT                         NUMBER(3)  WEIGHT                         NUMBER(3) */

2. 예시 (SQL Server)

   exec sp_help 'dbo.PLAYER'
   go
    
   /* 실행 결과
   칼럼이름         데이터 유형   길이   NULL 가능 
   --------------- ------------ ------ -------- 
   PLAYER_ID        CHAR(7)      7      NO 
   PLAYER_NAME      VARCHAR(20)  20     NO 
   TEAM_ID          CHAR(3)      3      NO 
   E_PLAYER_NAME    VARCHAR(40)  40     YES 
   NICKNAME         VARCHAR(30)  30     YES 
   JOIN_YYYY        CHAR(4)      4      YES 
   POSITION         VARCHAR(10)  10     YES 
   BACK_NO          TINYINT      1      YES 
   NATION           VARCHAR(20)  20     YES 
   BIRTH_DATE       DATE         3      YES 
   SOLAR            CHAR(1)      1      YES 
   HEIGHT           SMALLINT     2      YES 
   WEIGHT           SMALLINT     2      YES */

 

2-5. SELECT 문장을 통한 테이블 생성 사례

•  CREATE TABLE ~ AS SELECT ~ 를 통해 기존 테이블의 칼럼별로 데이터 유형을 재정의하지 않고도 테이블을 생성할 수 있음.

• 주의할 점은 NOT NULL을 제외한 제약 조건은 사라지기 때문에 ALTER TABLE을 이용해 제약 조건을 추가해야 함.

Ex) TEAM 테이블의 칼럼들을 TEAM_TEMP 테이블에 그대로 정의하는 작업

1. 예시 (Oracle)

   CREATE TABLE TEAM_TEMP AS SELECT * FROM TEAM;

2. 예시 (SQL Server)

   SELECT * INTO TEAM_TEMP FROM TEAM;

 

3. ALTER TABLE

 한 번 생성된 테이블은 사용자가 구조를 변경하기 전까지 당시의 생성 구조를 유지한다. 업무 요구 사항이나 테이블을 변경할 일이 발생할 때 사용하는 명령. (칼럼의 추가/수정/삭제, 제약조건 추가/수정/삭제)

 테이블에 ALTER 작업이 올바르게 되었는지는 DESC(Oracle), exec sp_help ... go(SQL Server) 명령어를 통해 확인한다.

 

3-1. ADD COLUMN (ADD)

 다음은 기존의 테이블에 필요한 칼럼을 추가하는 명령이다.

추가된 칼럼은 테이블의 마지막 칼럼이 되며 칼럼의 위치를 지정할 수는 없다.

Ex) PLAYER 테이블에서 데이터 유형은 최대 길이가 80인 문자열이고, 이름은 ADDRESS인 칼럼을 추가하는 작업

1. 예시 (Oracle)

   1 2

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

2. 예시 (SQL Server)

   1 2

   ALTER TABLE PLAYER  ADD ADDRESS VARCHAR(80);

 

3-2. DROP COLUMN (DROP COLUMN)

 테이블에서 필요 없는 칼럼을 삭제하는 명령이다.

테이터가 있거나 없거나 모두 삭제가 가능하며 한 번에 하나의 칼럼만 삭제 가능하다.삭제를 진행한 후 테이블에 최소 1개 이상의 칼럼은 남아있어야 하며, 삭제된 칼럼은 복구가 불가능하다.

Ex) PLAYER 테이블에서 ADDRESS 칼럼을 삭제하는 작업

1. 예시 (Oracle, SQL Server 동일)

   1 2

   ALTER TABLE PLAYER  DROP COLUMN ADDRESS;

 

3-3. MODIFY COLUMN (MODIFY)

테이블에 존재하는 칼럼에 대한 정의를 변경하는 명령이다.

1. 주의사항
   * 칼럼의 크기를 늘릴 수는 있지만 줄일 수는 없음 ---> 데이터의 훼손이 우려되기 때문에
   * 해당 칼럼이 NULL 값만 갖고 있거나 테이블의 ROW가 존재하지 않으면 칼럼을 줄일 수 있음.
   * 해당 칼럼이 NULL 값만 갖고 있으면 데이터 유형 변경이 가능.
   * 해당 칼럼의 DEFAULT 값을 바꾸면 기존 ROW에는 적용이 안되며 변경 작업 이후에 발생하는 ROW에만 영향을 미침.
   * 해당 칼럼에 NULL 값이 없을 경우에만 NOT NULL 제약조건을 추가할 수 있음.

2. 예시 (Oracle)
   Ex) TEAM_TEMP 테이블의 ORIG_YYYY 칼럼의 데이터 유형을 CHAR(4)에서 VARCHAR2(8)로 변경, 향후 입력되는 데이터의 DEFAULT 값으로 '20020129'을 적용하고, ORIG_YYYY 칼럼에 NULL 이 없는 상태이므로 제약조건을 NOT NULL로 변경

   1 2

   ALTER TABLE TEAM_TEMP  MODIFY (ORIG_YYYY VARCHAR2(8) DEFAULT '20020129' NOT NULL);

   cs

3. 예시 (SQL Server)

   /* 1. 타겟 칼럼 데이터 유형 변경 + NOT NULL로 변경 */
   ALTER TABLE TEAM_TEMP 
   ALTER COLUMN ORIG_YYYY VARCAHR(8) NOT NULL; 
   /* 명령 완료 */
    
   /* 2. DEFAULT값을 타겟 칼럼에 적용한 제약조건  */
   ALTER TABLE TEAM_TEMP 
   ADD CONSTRAINT DF_ORIG_YYYY DEFAULT '20020129' FOR ORIG_YYYY;
   /* 명령 완료 */

 

3-4. RENAME COLUMN (RENAME COLUMN)

칼럼명을 변경해야 하는 경우에 사용하는 명령.

1. 특징
   * 해당 칼럼과 관계된 제약조건에 대해서도 자동으로 변경되는 장점이 있지만, ANSI/ISO에 명시된 기능이 아니기 때문에 Oracle 등 일부 DBMS에서만 지원하는 기능

2. 예시 (Oracle)
   Ex) PLAYER 테이블의 PLAYER_ID를 TEMP_ID로 칼럼명을 변경

   /* RENAME COLUMN [col1] to [col2] : col1을 col2로 컬럼명 변경 */
   ALTER TABLE PLAYER 
   RENAME COLUMN PLAYER_ID TO TEMP_ID;
    
   /* RENAME TO [new_name] : new_name으로 테이블명 변경 */
   ALTER TABLE PLAYER
   RENAME TO PLAYER_TEMP;

3. 예시 (SQL Server)
   

   sp_rename 'dbo.TEAM_TEMP.TEAM_ID', 'TEAM_TEMP_ID', 'COLUMN';

 

3-5. DROP CONSTARINT

 부여했던 제약조건을 삭제하는 명령.

Ex) PLAYER_FK 이름을 가진 제약조건을 삭제하는 작업

1. 예시 (Oracle, SQL Server)

   ALTER TABLE PLAYER 
   DROP CONSTRAINT PLAYER_FK;
   /* PLAYER_FK 는 PLAYER_ID에 대한 기본키 제약조건의 제약조건명 */

 

3-6. ADD CONSTRAINT

 제약조건을 추가하는 명령.

* PLAYER 테이블에 TEAM 테이블과의 외부키 제약조건을 추가하고 제약조건명은 PLAYER_FK, PLAYER 테이블의 TEAM_ID 칼럼이 TEAM 테이블의 TEAM_ID를 참조하게끔 제약조건 추가

1. 예시 (Oracle, SQL Server)

   1 2

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

   cs

   
   * PLAYER 테이블이 참조하는 TEAM 테이블을 삭제해보자. (DROP TABLE TEAM; )

   a. Oracle, SQL Server

    TEAM을 삭제하면 외부키 제약조건에 위배되므로 삭제 불가능

   
   * PLAYER 테이블이 참조하는 TEAM 테이블의 데이터를 삭제해보자. (DELETE TEAM WHERE TEAM_ID='K01'; )

   a. Oracle, SQL Server

    참조 무결성 제약조건에 의해 삭제 불가능 (K01의 값을 참조하는 PLAYER 테이블의 데이터가 있기 때문에)

 

4. RENAME TABLE

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

Ex) TEAM 테이블의 이름을 TEAM_BACKUP으로 변경하는 작업

1. 예시 (Oracle)

   RENAME TEAM TO TEAM_BACKUP;

2. 예시 (SQL Server)

   sp_rename 'dbo.TEAM','TEAM_BACKUP';

 

5. DROP TABLE

 테이블을 삭제하는 명령

1. 예시 (Oracle, SQL Server)

   1 2

   DROP TABLE PLAYER; /* 테이블명 뒤에 CASCADE나 RESTRICT 제약조건이 올 수 있음. Oracle만 */

   cs

   * CASCADE : 참조 중인 다른 테이블도 모두 삭제 (연쇄적)
   * RESTRICT : 관계된 테이블 있으면 삭제 X (제한적)

 

6. TRUNCATE TABLE

 테이블 자체를 삭제하는 것이 아니고 테이블에 포함된 모든 ROW 들이 제거되고 저장 공간을 재사용 가능하도록 해제시키는 명령

1. 예시 (Oracle, SQL Server)

   1

   TRUNCATE TABLE TEAM;

   cs

   * DROP TABLE 후에 DESC는 에러가 발생하지만, TRUNCATE 후 DESC는 정상 작동

2. 특징

   * DML (Data Manipulation Language)의 DELETE와 비교할 수 있는데, DELETE의 경우 내부 처리 방식이나 Auto Commit 특성 등으로 인해 DML에 속하고 TRUNCATE는 DDL에 속함

   * 시스템 부하 : DELETE로 전체 ROW 삭제 > TRUNCATE로 전제 ROW 삭제

   주의사항 : TRUNCATE는 DDL이므로 ROLLBACK이 불가하므로 복구 역시 불가능.

 

* DROP VS TRUNCATE VS DELETE

DROP	TRUNCATE	DELETE
DDL	DDL	DML
ROLLBACK 불가능	ROLLBACK 불가능	ROLLBACK 가능
AUTO COMMIT	AUTO COMMIT	사용자 COMMIT
테이블이 사용했던 저장 공간을 모두 Release	테이블이 사용했던 저장공간 중 최초 테이블 생성시 할당된 공간만 남기고 모두 Release	데이터를 모두 DELETE해도 사용했던 저장공간은 Release 되지 않음
테이블의 정의 자체를 완전히 삭제	테이블을 최초 생성된 초기상태로 만듬	데이터만 삭제됨

 

 

 

 

 

 

 

출처

http://www.dbguide.net/db.db?cmd=view&boardUid=148190&boardConfigUid=9&categoryUid=216&boardIdx=134&boardStep=1

데이터 전문가 지식포털 DBGuide.net

질문은 댓글로 남겨주시면 되겠습니다. 감사합니다.