SQLD - SQL 기본 [DDL]

1.   데이터 유형

-       숫자 타입

ANSI/ISO 기준 : NUMERIC, DEMICAL, DEC, SMALLINT, INTEGER,INT, BIGINT, FLOAT, REAL, DOUBLE PRECISION

SQL Server와 Sybase : 작은 정수형, 정수형, 큰 정수형, 실수형 등 + MONEY, SMALLMONEY

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

-       벤더에서 ANSI/ISO 표준을 사용할 떄는 기능을 중심으로 구현하므로, 일반적으로 표준과 다른 용어를 사용하는 것이 허용
Ex) NUMERIC -> NUMBER , WINDOW FUCTION ->ANALYTIC/RANK FUNCTION

-       테이블의 칼럼이 가지고 있는 대표적인 4가지 데이터 유형
기타 유형 : ANSI/ISO에서 Binary String Type, Binary Large Object String Type,
           National Character String Type, Boolean Type 등

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

 

-       문자열 유형 : CHAR 유형과 VARCHAR 유형의 차이

VARCHAR 유형 : 가변길이

CHAR 유형 : 고정길이

 

2.   CREATE TABLE

가.   테이블과 칼럼 정의
후보키 중에하나를 선정하여 기본키 칼럼으로 지정

 

나.  CREATE TABLE

-       CREATE TABLE 테이블명 (칼럼명1 DATETYPE DEFAULT 형식,
                        칼럼명2 DATETYPE DEFAULT 형식,
                        칼럼명3 DATETYPE DEFAULT 형식);

테이블 생성 시에 주의해야할 규칙들

n  테이블명 : 객체를 의미할 수 있는 적절한 이름을 사용

n  벤더에서 사전에 정의한 예약어(Reserved word)는 쓸 수 없다.
(A-Z, a-z, 0-9, _, $, # 문자만 허용된다.)

n  테이블 명이 잘못된 사례

10_PLAYER	반드시 숫자가 아닌 문자로 시작되어야 한다.
T-PLAYER	특수 문자 ‘-‘는 허용되지 않는다.

                     추가적인 주의 사항

u  테이블 생성시 대/소문자 구분은 하지 않는다. (기본적으로 테이블이나 칼럼명은 대문자로 작성한다.)

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

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

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

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

 

다.  제약조건(CONSTRAINT)

제약조건(CONSTRAINT) : 데이터 무결성을 유지하기 위한 데이터베이스의 보편적인 방법

-       제약조건의 종류

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

-       NULL 의미
‘NULL’은 ‘아직 정의되지 않은 미지의 값’이거나 ‘현재 데이터를 입력하지 못하는 경우’를 의미한다.

-       DEFAULT 의미

데이터 입력 시에 칼럼의 값이 지정되어 있지 않을 경우 기본값(DEFAULT)

 

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

-       Oracle
DESC 테이블명;

-       SQL Server
exec sp_hepl ‘테이블명’ go

 

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

-       SELECT 문장을 활용해서 테이블을 생성할 수 있는 방법(CTAS : Create Table ~ As Select ~)

-       CTAS 기법 사용시 주의할 점
기존테이블의 제약조건 중 NOT NULL만 새로운 복제 테이블에 적용되고,
기본키, 고유키,외래키 CHECK 등의 다른 제약 조건은 없어진다는 점이다.
제약 조건을 추가하기 위해서는 ALTER TABLE 기능을 사용해야 한다.
SQL Server에서는 Select ~ Into ~ 를 활용하여 위와 같은 결과를 얻을 수 있다.
          단, 칼럼 속성에 Identity를 사용했다면 Identity 속성까지 같이 적용된다.

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

-       SQL Server
SELECT * INTO 테이블명 FROM 테이블명;

 

3.   ALTER TABLE

가.  ADD COLUMN

-       ALTER TABLE 테이블명 ADD 추가할 칼럼명 데이터 유형;
* 새롭게 추가된 칼럼은 테이블의 마지막 칼럼이 되며 칼럼의 위치는 지정할 수 없다.

-       Oracle
ALTER TABLE 테이블명 ADD (추가할칼럼명 데이터유형);

-       SQL Server
ALTER TABLE 테이블명 ADD 추가할칼럼명 데이터유형;

 

나.  DROP COLUMN

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

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

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

 

다.  MODIFY COLUMN

-       ALTER TABLE 명령을 이용해 칼럼의 데이터유형, 디폴트(DEFAULT) 값, NOT NULL 제약조건에 대한 변경을 포함

-       Oracle
ALTER TABLE 테이블명 MODIFY (칼럼명1 데이터유형 DEFUALT 식 NOT NULL,
                              칼럼명2 데이터유형 …);

-       SQL Server
ALTER TABLE 테이블명 ALTER (칼럼명1 데이터유형 DEFUALT 식 NOT NULL,
                            칼럼명2 데이터유형 …);

-       고려사항

n  해당 칼럼의 크기를 늘릴 수는 있지만 줄이지는 못하낟.

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

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

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

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

-       RENAME COLUMN

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

-       RENAME COLUMN으로 칼럼명이 변경되면, 해당칼럼과 관계된 제약조건에 대해서도 자동으로 변경되는 장점이 있지만, ADD/DROP COLUMN 기능처럼 ANSI/ISO에 명시되어 있는 기능이 아니고 Oracle 등 일부 DBMS에서만 지원하는 기능이다.

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

-       SQL Server
sp_rename 변경해야할칼럼명, 새로운칼럼명, ‘COLUMN’;

 

라.  DROP CONSTRAINT

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

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

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

마.  ADD CONSTRAINT

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

-       Oracle
ALTER TABLE 테이블명 ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID);

-       SQL Server
ALTER TABLE 테이블명 ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID);

 

-       참조무결성 옵션에 따라 데이터 삭제하려 할 경우 외부에서 참조되고 있는 경우 삭제가 불가능하게 제약을 할 수 있다. 즉, 외부키(FK)를 설정함으로써 실수에 의한 테이블 삭제나 필요한 데이터의 의도하지 않은 삭제와 같은 불상사를 방지하는 효과를 볼 수 있다.

 

4.   RENAME TABLE

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

-       SQL Server에서는 sp_rename을 이용하여 테이블 이름을 변경할 수 있다.
sp_rename 변경전 테이블명, 변경후 테이블명;

 

5.   DROP TABLE

-       DROP TABLE 테이블명 [CASCADE CONSTRAINT];

-       CASCADA CONSTRAINT 옵션은 해당 테이블과 관계가 있었던 참조되는 제약조건에 대해서도 삭제한다는 것을 의미한다.

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

 

6.   TRUNCATE TABLE

-       TRUNCATE TABLE 테이블명;

-       TRUNCATE TABLE은 테이블 자체가 삭제되는 것이 아니고, 해당 테이블에 들어있던 모든 행들이 제거되고 저장 공간을 재사용 가능하도록 해제한다. 테이블 구조를 완전히 삭제하기 위해서는 DROP TABLE을 실행하면 된다.

-       TRUNCATE는 데이터 구조의 변경없이 테이블의 데이터를 일괄 삭제하는 명령어로 DML로 분류할 수도 있지만, 내부 처리 방식이나 Auto Commit 특성 등으로 인해 DDL로 분류하였다.