SQL 기본 및 활용 (1) - DDL

DB 설치 없이 SQL 실습을 위해 오라클의 Live SQL을 사용하였다.

 

1. 테이블 생성

CREATE TABLE TEAM (   
    TEAM_ID         char(3), 
    REGION_NAME     varchar(8), 
    TEAM_NAME       char(40), 
    E_TEAM_NAME     varchar2(50), 
    ORIGIN_YYYY     char(4), 
    STADIUM_ID      char(3), 
    ZIP_CODE1       char(3), 
    ZIP_CODE2       char(3), 
    ADDRESS         varchar2(80), 
    DDD             varchar(3), 
    TEL             varchar(10), 
    FAX             varchar(10), 
    HOMEPAGE        varchar2(50), 
    OWNER           varchar(10));

 

2. 제약조건 추가

- Primary Key 추가

ALTER TABLE TEAM ADD CONSTRAINT TEAM_PK PRIMARY KEY (TEAM_ID)

- Foreign Key 추가

ALTER TABLE TEAM 
    ADD CONSTRAINT TEAM_FK FOREIGN KEY (STADIUM_ID) REFERENCES STADIUM(STADIUM_ID);

 

3. 칼럼 속성 변경

- 제약조건 삭제 : DROP CONSTRAINT

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

ex) PK  삭제

ALTER TABLE STADIUM DROP CONSTRAINT STADIUM_PK

ex) FK  삭제

ALTER TABLE TEAM DROP CONSTRAINT TEAM_FK;

 

- 제약조건 추가 : ADD CONSTRAINT

ex) PK  추가

ALTER TABLE STADIUM ADD CONSTRAINT STADIUM_PK PRIMARY KEY (STADIUM_ID)

ex) FK  추가

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

외부키 참조를 함으로써 본 테이블이나 본 테이블의 데이터 삭제를 방지할 수 있다. (참조무결성)

 

 

- 칼럼 삭제 : DROP_COLUMN

한 번에 하나의 칼럼만 삭제 가능하다

ALTER TABLE STADIUM DROP COLUMN STADIUM_ID

ALTER TABLE PLAYER DROP COLUMN ADDRESS;

 

- 칼럼 추가 : ADD_COLUMN

새롭게 추가된 칼럼은 테이블의 마지막 컬럼이 되며, 이 위치를 변경할 수는 없다.

(SQL Server)

ALTER TABLE STADIUM ADD STADIUM_ID CHAR(3)

(Oracle)

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

 

 

- 칼럼 업데이트 : MODIFY COLUMN

칼럼의 크기를 늘릴 수는 있지만 줄이지는 못한다. (기존 데이터 훼손방지)

러나, 해당 컬럼의 NULL만 가지고 있거나 아무행도 없으면 가능

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

(1) NULL -> NOT NULL

ALTER TABLE TEAM 
    MODIFY (REGION_NAME VARCHAR2(8) NOT NULL);

(2) 데이터 타입 변경

 

ex) TEAM 테이블의 ORIGIN_YYYY의 칼럼의 데이터 유형을 VARCHAR2(8)로 바꾸고, 향후 입력되는 데이터 값의 DEFAULT 값을 '20221104'를 적용하고, 제약조건을 NULL -> NOT NULL로 변경한다.

ALTER TABLE TEAM 
    MODIFY (ORIGIN_YYYY VARCHAR2(8) DEFAULT '20221104' NOT NULL);

- 칼럼 이름만 변경 : RENAME COLUMN

테이블을 생성하면서 만들었던 컬럼명을 불가피하게 변경해야 할 경우 사용한다.

(Oracle)
ALTER TABLE [테이블명] RENAME COLUMN 변경해야 할 컬러명 TO 새로운 칼럼명;
(SQL Server)
sp_rename 변경해야 할 칼럼명, 새로운 칼럼명, 'COLUMN';

ALTER TABLE PLAYER RENAME COLUMN TEAM_ID TO PLAYER_ID;

 

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

DESCRIBE TEAM;

TEAM table 조회 결과

5. SELECT 를 이용한 테이블 생성

칼럼별로 데이터 유형을 재정의 하지 않고 테이블 생성할 수 있다는 장점이 있는 반면, 제약 조건 중 NOT NULL만 새로운 복제 테이블에 적용되고, 기본키, 고유키, 외래키, CHECK 등의 다른 제약 조건들은 ALTER 문을 사용해야 한다.

 

ex) 위의 TEAM 테이블과 같은 내용으로  PLAYER라는 테이블 만들기

(Oracle)

CREATE TABLE PLAYER AS SELECT * FROM TEAM;

(SQL Server)

SELECT * INTO PLAYER FROM TEAM;

NOT NULL 속성만 가져왔온 것을 알 수 있다.

 

6. 테이블 이름 변경 : RENAME TABLE

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

 

7. 테이블 삭제 : DROP TABLE

DROP TABLE 테이블명 [CASCADE CONSTRAINT];

* CASCADE CONSTRAINT 옵션 

: 해당 테이블을 참조하는 조건도 함께 삭제한다.

(SQL에는 이 옵션이 없다. 따라서 참조하는 테이블을 먼저 삭제해줘야 본 테이블을 삭제할 수 있다.)

 

8. TRUNCATE TABLE

테이블에 있던 행, 데이터만 제거 되고 저장공간은 그대로 남아있어 재사용이 가능하다.

TRUNCATE TABLE 테이블명;

truncate 후 player table schema