SQL 문법 정리 : 데이블관리(CTAS,테이블구조 &이름 변경,컬럼추가,컬럼제거,컬럼 테이터 타입변경,테이블제거,임시테이블,TRUNCATE)

SQL 문법 정리 : 데이블관리(CTAS,테이블 구조 변경,이름 변경,컬럼추가,컬럼제거,컬럼 테이터 타입변경,테이블제거,임시테이블,TRUNCATE)

 

CTAS

CTAS는 CREATE TABLE AS SELECT의 약어로써 SELECT문을 기반으로 CREATE TABLE을 할 수 있는 CREATE문이다.

 

CREATE TABLE NEW_TABLE

AS

SELECT문

 

CREATE TABLE NEW_TABLE(NEW_COLUMN_1, NEW_COLUMN2)

AS

SELECT문

 

CREATE TABLE IF NOT EXISTS  NEW_TABLE

AS

SELECT문

#기존에 테이블이 존재하지 않는 경우만 생성한다.

 

테이블 구조 변경

한번 만들어진 테이블이라고 하더라도 테이블 구조를 변경 할 수 있다. 이 기능으로 인해 업무변화에 유연하게 대처할 수 있다.

 

CREATE TABLE LINKS(

 LINK_ID SERIAL PRIMARY KEY

,TITLE VARCHAR (512) NOT NULL

,URL VARCHAR (1024) NOT NULL UNIQUE

);

 

ALTER TABLE LINKS ADD COLUMN ACTIVE BOOLENA;

#ACTIVE 컬럼을 추가

ALTER TABLE LINKS DROP COLUMN ACTIVE;

# ACTIVE 컬럼을 제거

ALTER TABLE LINKS RENAME COLUMN TITLE TO LINK_TITLE;

# TITLE 컬럼을 LINK_TITLE컬럼으로 변경

ALTER TABLE LINKS ADD COLUMN TARGET VARCHAR(10);

#TARGET컬럼을 추가

ALTER TABLE LINKS ALTER COLUMN TARGET SET DEFAULT '_BANK';

#TARGET컬럼의 DEFAULT 값을 '_BLANK'로 설정

 

 

 

테이블 이름 변경

한번 만들어진 테이블이라고 하더라도 테이블 이름을 변경 할 수 있다.

CREATE TABLE VENDORS

(

 ID SERIAL PRIMARY KEY

, NAME NARCHAR NOT NULL

);

 

ALTER TABLE VENDORS RENAME TO SUPPLIERS;

# VENDORS테이블을 SUPPLIERS 테이블로 변경

 

CREATE TABLE SUPPLIER_GROUPS

(

 ID SERIAL PRIMARY KEY

,NAME VARCHAR NOT NULL

);

 

ALTER TABLE SUPPLIERS ADD COLUMN GRUOP_ID INT NOT NULL;

ALTER TABLE SUPPLIERS ADD FOREIGN KEY(GROUP_ID)

REFERENCES SUPPLIER_GRUOPS(ID);

#SUPPLIERS테이블에 컬럼 추가 후 FK 생성

# 테이블 이름을 변경하면, 기존의 참조무결성 제약조건이나 뷰등이 자동으로 반영된다.

 

컬럼추가

기존에 존재하는 테이블에 컬럼을 추가 할 수 있다.

CREATE TABLE TB_CUST(

 CUST_ID SERIAL PRIMARY KEY

,CUST_NAME VARCHAR(50) NOT NULL

);

 

ALTER TABLE TB_CUST ADD COLUMN PHONE_NUMBER VARCHAR(13);

*한번에 두개추가

ALTER TABLE TB_CUST

 ADD COLUMN FAX_NUMBER VARCHAR(13),

 ADD COLUMN EMAIL_ADDR VARCHAR (50);

 

컬럼제거

기존에 존재하는 테이블에 컬럼을 삭제 할 수 있다

ALTER TABLE BOOKS DROP COLUMN CATEGORY_ID;

#BOOKS 테이블은 자식 테이블이므로 CATEGORY_ID컬럼은 제거가 가능하다.

#컬럼이 제거되면서 CATEGORY_ID의 FK도 함께 삭제 된다.

 

ALTER TABLE BOOKS DROP COLUMN PUBLISHER_ID;

#PUBLISHER_ID 컬럼을 제거하고자 하는 경우 아래와 같은 에러가 발생한다.

#해당 컬럼은 BOOK_INFO뷰에서 참조하고 있기 때문이다

->이런 경우에는 CASCADE옵션을 줘서 삭제한다

ALTER TABLE BOOKS DROP COLUMN PUBLISHER_ID CASCADE;

#컬럼 삭제에는 성공했지만 BOOK_INFO뷰도 같이 DROP 되었다

 

컬럼 테이터 타입변경

기존에 존재하는 테이블에 컬럼의 타입을 변경 할 수 있다.

CREATE TABLE ASSETS(

 ID SERIAL PRIMARY KEY

,NAME NTEXT NOT NULL

,ASSET_NO VARCHAR(10) NOT NULL

,DESCRIPTION TEXT

,LOCATION TEXT

,ACQUIRED_DATE DATE NOT NULL

);

 

INSERT INTO ASSETS(

 NAME

,ASSET_NO

,LOCATION

,ACQUIRED_DATE

)

VALUES

('SERVER', '0001','SERVER ROOM','2020-01-01'),

('UPS','0002','SERVER ROOM','2020-01-02');

 

ALTER TABLE ASSETS ALTER COLUMN NAME TYPE VARCHAR(20);

# NAME 컬럼의 데이터 타입을 변경

 

ALTER TABLE ASSETS

   ALTER COLUMN LOCATION TUPE VARCHAR(100);

   ALTER COLUMN DESCRIPTION TYPE VARHCAR(500);

#한번에 N개 컬럼의 데이터 타입을 변경

 

ALTER TABLE ASSETS ALTER COLUMN ASSET_NO TYPE INT;

# INT형으로 변경하려고 했으나 실패함

 

ALTER TABLE ASSETS

 ALTER COLUMN ASSET_NO TYPE INT USING ASSET_NO::INTEGER;

# INT형으로 데이터 타입 변경 성공

 

 

컬럼 이름 변경

기존에 존재하는 데이블에 이름을 변경할수 있다.

ALTER TABLE CUSTOMERS

   RENAME COLUMN EMAIL TO CONTACT_EMAIL;

 

테이블제거

존재하는 데이블을 제거할 수 있다. 테이블 제거 시는 항상 주의해야 하고 FK관계도 유의해야 한다.

DROP TABLE AUTHOR;

#FK 제약조건으로 인해 테이블 제거 실패

DROP TABLE AUTHOR CASCADE;

CASCADE옵션으로 삭제 성공

 

임시테이블

임시테이블은DB접속 세션의 활동 기간동안 존재하는 데이블이다. 세션이 종료되면 임시 테이블은 자동으로 소멸된다.

 

CRETE TEMP TABLE TB_CUST_TEMP_TEST(CUST_ID INT);

#임시 테이블 생성

 

TRUNCATE

대용량의 테이블을 빠르게 지우는 방법으로 TRUNCATE가 있다. 테이블의 세그먼트 자체를 바로 지우기 때문에 빠르게 데이터가 지원진다. TRUNCATE 명령어는 용량이 줄어 든다. 테이블을 삭제하지는 않고, 데이터만 삭제한다. 한꺼번에 다 지워야 한다. 삭제 후 되돌릴 수 없다. 속도가 빠르다.

(단, 오라클은 TRUNCATE는ROLLBACK이 불가능하지만, Postgresql은 가능하다!)

DELETE 명령어는 데이터는 지워지지만 테이블 용량은 줄어들지 않는다. 원하는 데이터만 지울 수 있다. 삭제 후 잘못 삭제한 것을 되돌릴 수 있다. 속도가 느리다.

 

TRUCATE TABLE BIG_TABLE;