[SQLD] 2과목 SQL 기본및 활용 - 1장 2절 DDL

[SQLD] 2과목 SQL 기본및 활용 - 1장 2절 DDL

1. 데이터 유형

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

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

 

* VARCHAR 유형은 가변 길이이므로 필요한 영역은 실제 데이터 크기뿐이다.

* CHAR에서는 문자열을 비교할 때 공백을 채워서 비교하는 방법을 사용한다.

- 공백 채우기 비교에서는 우선 짧은쪽의 끝에 공백을 추가하여 2개의 데이터가 같은 길이가 되도록 한 후 앞에서부터 비교한다.(공백만 다른 문자열은 같다고 판단함.)

* VARCHAR

- 맨 처음부터 한 문자씩 비교하고 공백도 하나의 문자로 취급하므로 끝의 공백이 다르면 다른 문자로 판단한다.

ex) 

CHAR 유형:  'AA' = 'AA    '

VARCHAR 유형 'AA' != 'AA     '

2. CREATE TABLE

가. 테이블과 칼럼 정의

- 테이블에 존재하는 모든 데이터를 고유하게 식별할 수 있으면서 반드시 값이 존재하는 단일 칼럼이나 칼럼의 조합들(후보키) 중에 하나를 선정하여 기본칼럼으로 지정한다.

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

- 테이블과 테이블간에 정의된 관계는 기본키와 외부키를 활용해서 설정하도록 한다.

나. CREATE TABLE

- 테이블을 생성하는 구문.

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

 

- 테이블 생성 시 주의할 규칙.

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

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

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

4) 테이블 이름을 지정하고 각 칼럼들은 괄호로 묶어서 지정한다.

5) 각 칼럼들은 콤마로 구분되고, 테이블 생성명의 끝은 ;로 끝난다.

6) 칼럼에 대해서는 다른 테이블까지 고려하여 데이터베이스 내에서는 일관성 있게 사용하는 것이 좋다.

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

8) 테이블명과 칼럼명은 반드시 문자로 시작해야 하고 벤더별로 길이에 대한 한계가 있다.

9) 벤더에서 사전에 지정한 예약어는 사용할 수 없다.

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

ex) 테이블명이 잘못 정의된 사례

10_player

star-player

- 규칙에 맞게 선수 테이블 생성

1) 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), POSITION VARCHAR2(10), BACK_NO NUMBER(2), NATION VARCHAR2(20), BIRTH_DATE DATE, SOLAR CHAR(1), HEIGHT NUMBER(3), WHEIGHT NUMBER(3), CONSTARAINT PLAYER_PK PRIMARY KEY(PLAYER_ID), CONSTARAINT PLAYER_FK (TEAM_ID) REFERENCES TEAM(TEAM_ID) );

 

 

2) 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), POSITION VARCHAR(10), BACK_NO TYNINT, NATION VARCHAR(20), BIRTH_DATE DATE, SOLAR CHAR(1), HEIGHT SMALLINT, WHEIGHT SMALLINT, CONSTARAINT PLAYER_PK PRIMARY KEY(PLAYER_ID), CONSTARAINT PLAYER_FK (TEAM_ID) REFERENCES TEAM(TEAM_ID) );

- 테이블 생성시 대소문자 구분하지 않는다.

- 기본적으로 테이블이나 칼럼명은 대문자로 만들어진다.

- DATETIME 데이터 유형에는 별도의 크기를 지정하지 않는다.

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

- 칼럼과 칼럼의 구분은 콤마로 하되, 마지막 칼럼은 콤마를 찍지 않는다.

- 칼럼에 대한 제약조건이 있으면 CONSTRAINT를 이용하여 추가할 수 있다.

* 제약조건은 PLAYER_NAME, TEAM_ID 컬럼의 데이터 유형 뒤에 NOT NULL을 정의한 사례와 같은 칼럼레벨 정의방식과, PLAYER_PK PRIMARY KEY, PLAYER FK FOREIGN KEY 사례처럼 테이블 마지막에 모든 제약조건을 기술하는 테이블레벨 방식이 있다.

다. 제약조건 (CONSTRAINT)

- 제약조건이란 사용자가 원하는 조건의 데이터만 유지하기 위한 즉, 데이터 무결성을 유지하기 위한 데이터베이스의 보편적 방법으로 테이블의 특정 칼럼에 설정하는 제약이다.

- 제약조건의 종류

구분	설명
PRIMARY KEY(기본키)	테이블에 저장된 행 데이터를 고유하게 식별하기 위한 기본키를 정의한다. 하나의 테이블에는 하나의 기본키 제약만 정의할 수 있다. 기본키 제약을 정의하면 DBMS는 자동으로 UNIQUE INDEX를 생성하며 기본키를 구성하는 칼럼에는 NULL을 입력할 수 없다. 기본키 제약 = 고유키 제약 & NOT NULL 제약이다.
UNIQUE KEY (고유키)	테이블에 저장된 행 데이터를 고유하게 식별하기 위한  고유키를 정의한다. 단, NULL은 고유키 제약의 대상이 아니므로, NULL 값을 가진 행이 여러 개가 있더라도 고유키 제약 위반이 되지 않는다.
NOT NULL	NULL값의 입력을 금지한다. 이 제약을 정함으로써 해당 칼럼은 입력 필수가 된다. NOT NULL을 CHECK의 일부분으로 이해할 수 있다.
CHECK	입력할 수 있는 값의 범위 등을 제한한다. CHECK 제약으로는 TRUE or FALSE로 평가할 수 있는 논리식을 지정한다.
FOREIGN KEY(외래키)	관계형 데이터베이스에서 테이블 간의 관계를 정의하기 위해 기본키를 다른테이블의 외래키로 복사하는 경우 외래키가 생성된다. 외래키 지정 시 참조 무결성 제약 옵션을 선택할 수 있다.

 

- 조건에 따른 테이블 생성

1) ORACLE

CREATE TABLE TEAM( TEAM_ID CHAR(3) 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_ID_PK PRIMARY KEY (TEAMD_ID), CONSTRAINT TEAM_FK FROEIGN KEY(STADIUM_ID) REFERENCES(STADIUM_ID) );

 

2)SQL server

CREATE TABLE TEAM( TEAM_ID CHAR(3) 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_ID_PK PRIMARY KEY (TEAMD_ID), CONSTRAINT TEAM_FK FROEIGN KEY(STADIUM_ID) REFERENCES(STADIUM_ID) );

 

라. 생성된 테이블 구조 확인.

오라클 : DESC 테이블명; 또는 DESCRIBE 테이블명;

SQL SEVER :  exec sp_help 'dbo.테이블명'

                        go

마. SELECT 문장을 통한 테이블 생성.

- Create Table ~  As select ~

해당 방법을 이용할 경우 칼럼별로 데이터 유형을 다시 정의하지 않아도된다.

- CATS기법 사용시 기존테이블의 제약 조건중에 Not null만 새로운 테이블에 적용되며 기본키, 고유키, 외래키, Check 등의 다른 제약조건은 없어진다.

- SQL Server에서는 Select ~ into ~ 를 활용하여 같은 결과를 얻을 수 있으며 칼럼속성에 Identity를 사용했다면 Identity 속성까지 같이 적용된다.

* 오라클

CREATE TABLE TEMP_PLAYER

AS SELECT * FROM PLAYER;

* SQL SERVER

SELECT * INTO TEAM_TEMP FROM TEAM;

3. ALTER TABLE

- 한번 생성된 테이블의 구조를 변경하기위해 사용한다.

- 칼럼을 추가/삭제하거나 제약조건을 추가/삭제 하는 작업을 진행하게된다.

가. ADD COLUMN.

ALTER TABLE 테이블명

ADD 추가할 칼럼명 데이터 타입;

* 추가된 칼럼은 마지막에 위치하게되며 위치를 지정할 수 없다.

- 오라클

ALTER TABLE PLAYER

ADD (ADDRESS VARCHAR2(80));

* 아래 명령어도됨.

ALTER TABLE PLAYER

ADD ADDRESS VARCHAR2(80);

- SQL SERVER

ALTER TABLE PLAYER

ADD ADDRESS VARCHAR(80);

나. DROP COLUMN

- 테이블에서 필요없는 칼럼을 삭제할 수 있으며 데이터가 없거나 있거나 모두 삭제 가능하다.

- 한 번에 하나의 칼럼만 삭제 가능하며 칼럼 삭제 후 최소 하나 이상의 칼럼이 데이터에 존재해야한다.

- 한 번 삭제된 칼럼은 복구가 불가능하다.

ALTER TABLE 테이블명

DROP COLUMN 컬럼명;

다. MODIFY COLUMN 

- ALTER TABLE 명령어를 통해 칼럼의 데이터 유형, 디폴트 값, NOT NULL 제약조건에 대해 변경을 포함할 수 있다.

- 칼럼에 대한 데이터 정의를 변경하는 명령어

ALTER TABLE 테이블명

MODIFY ( 칼럼명1 데이터유형 [DEFAULT] [NOTNULL] ,

                 칼럼명2 데이터유형 .... ) ;

* 칼럼 변경 시 고려할 사항.

- 해당 칼럼의 크기는 늘릴 수 있지만 줄일 수는 없다. (기존 데이터의 훼손 방지)

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

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

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

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

ex) TEAM 테이블에 ORIG_YYYY컬럼의 데이터 유형을 CHAR(4) -> VARCHAR2(8) 형식으로 변경하고 향후 입력되는 데이터의 DEFAULT 값으로 20090826 적용하고 모든 행의 ORIG_YYYY컬럼에 널이 없으므로 제약조건을 NOT NULL로 변경한다

- 오라클

ALTER TABLE TEAM

MODIFY (ORIG_YYYY VARCHAR2(8) DEFAULT '20150826' NOT NULL) ;

-SQL SERVER
ALTER TABLE TEAM

ALTER CLOUMN ORIG_YYYY VARCHAR(8) DEFAULT '20150826' NOT NULL;

ALTER TABLE TEAM

ADD CONSTRAINT DF_ORIG_YYYY DEFAULT '201508226' FOR ORIG_YYYY;

- RENAME COLUMN

칼럼명을 어떤 이유로 불가피하게 변경해야 하는 경우.

ALTER TABLE 테이블명

RENAME COLUMN 변경해야 할 컬럼명 TO 새로운 칼럼명;

ex) 

- 오라클

ALTER TABLE PLAYER

RENAME COLUMN PLAYER_ID TO TEMP_ID;

ALTER TABLE PLAYER 

RENAME COLUMN TEMP_ID TO PLAYER_ID;

- SQL server

sp_rename 프로시져를 이용하여 변경한다.

sp_rename 변경해야 할 컬럼명, 새로운 칼럼명, 'COLUMN';

ex) sp_rename 'dbo.TEAM_TEMP.TEAMID', 'TEAM_TEMP_ID', 'COLUMN';

라. DROP CONSTRAINT

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

ALTER TABLE 테이블명

DROP CONSTRAINT 제약조건명;

ex) 

- 오라클 / SQL server

ALTER TABLE PLAYER

DROP CONSTRAINT PLAYER_FK;

마. ADD CONSTRAINT

- 테이블 생성 이후에 필요에 의해서 제약조건을 추가할 수 있다.

ALTER TABLE 테이블

ADD CONSTRAINT 제약조건명 제약조건 (칼럼명) ;

ex)

ALTER TABLE PLAYER

ADD CONSTRAINT PLAYER_FK

FOREIGN KEY(TEAM_ID) REFERENCES TEAM(TEAM_ID);

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

=> FK를 설정함으로써 실수에 의한 테이블 삭제나 필요한 데이터의 의도하지 않은 삭제와 같은 불상사를 방지할 수있다.

4. RENAME TABLE

- RENAME 명령어를 사용하여 테이블 이름을 변경할 수 있다.

오라클: RENAME 변경전 테이블명 TO 변경후 테이블명;

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

ex)

오라클

RENAME TEAM TO TEMP_TEAM;

SQL server

sp_rename 'dbo.team','temp_team';

5. DROP TABLE

- 테이블을 잘못 만들거나 더이상 필요없을 경우 삭제하는 명령어

DROP TABLE 테이블명 [CASCADE CONSTRAINT];

* DROP 명령어를 사용하면 테이블의 데이터 및 구조를 삭제한다.

* CASCADE CONSTRAINT 옵션을 사용하면 테이블과 관계가 있었던 참조제약조건에 대해서도 삭제한다.

 (SQL server에서는 CASCADE옵션이 존재하지 않으며 테이블 삭제하기 전에는 FOREIGN KEY 제약조건 또는 참조하는 테이블을 먼저 삭제해야한다.)

ex) 

- 오라클 

DROP TABLE TEMP_EMP;

- SQL server

DROP TABLE TEMP_EMP;

6. TRUNCATE TABLE

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

- TRUNCATE 는 내부 처리방식이나 AUTO COMMIT 특성에 의해 DDL로 분류한다.

- TRUNCATE TABLE 테이블명

ex) 오라클/ SQL server

TRUNCATE TABLE TEAM;