SQL 구문 정리

4. 데이터 조작 및 테이블 관리

1. 데이터 조작

insert -- 빈 테이브에 데이터를 삽입

## 기본

insert into link
(url, name)
values
(),
(),
;

commit ;

## 테이블을 테이블에 입력 " 테이블 구조 + 데이터 복제 "

create table link_tmp as -- link테이블 구조 복제
select *
from link
where 0=1 ;

insert into link_tmp -- link테이블의 데이터 삽입
select *
from link
;

update -- 데이터 수정 작업 " 동시성 유의 "

              --  update는 해당 행에 락을 건다, 작업 후 빠른 commit 필요

** alter + add/alter

     update + set +where

## 준비 -- 컬럼 추가 및 설정

alter table link
        add column last_update (as)date ;
alter table link
        alter column last_update
            set default (as)current_date;

-- last_update is null

## update 

update link
        set last_update = default
where
             last_update is null ;

commit ;

## 전체 테이블 수정 -- where절이 없으므로 테이블의 전체 행을 대상으로 한다

update link
        set rel = 'NO DATA' ;

commit ;

## 전체 테이블 수정 + 특정 컬럼으로 대체 --name 테이블 값 복제

update link
    set description = name ;

commit ;

update join -- 다른 테이블의 내용을 참조하여 업데이트 한다.

" 복잡한 업무 시, excel <<< update join "

## product : product_segment = M : 1

update product p
       set net_price = p. price - (p. price * ps. discount)
        from product_segment ps
where p. segment_id = ps. segment_id ;

-- product 테이블의 net_price 컬럼을 구하기 위해 product_segment 테이블 참조

delete -- 특정 조건의 행을 삭제

## 특정 조건 삭제

delete
    from link
where id = 5 ;

commit ;

## delete join -- 다른 테이블과 겹치는 데이터 삭제

delete from
        link_tmp a
using link b
where a. id = b. id ; -- 두 테이블의 id 컬럼이 겹치는 데이터 삭제

commit ;

## 전체 행 삭제 " 테이블 내 모든 데이터 삭제 "

delete from link ;

commit ;

-- select count(*)
    from link ; -- 0

upsert -- 조건(상황)에 따라 update

           -- 복잡한 업무 처리에 자주 사용

## do nothing

insert into
            customers (name, email)
values
('Microsoft', 'hotline@microsoft.com')
on conflict (name) -- 데이터 삽입시, name 컬럼(unique)이 충돌하면 아무 것도 안함
do nothing ;

commit ;

## do update

insert into
            customers (name, email)
values
('Microsoft', 'hotline@microsoft.com')
on conflict (name)
do update
        set email = excluded.email || ' ; ' || customers.email ; -- 같이 정렬

commit ;

export -- 테이블의 데이터를 다른 형태의 데이터로 추출

## 엑셀형식으로 추출

copy category (category_id, name, last_update)
to 'c:\tmp\db_category.csv'
delimiter ','
csv header ;

## 텍스트 파일로 출력

copy category (category_id, name, last_update)
to 'c:\tmp\db_category.txt'
delimiter '|'
csv header ;

## 컬럼명없이 출력

copy category (category_id, name, last_update)
to 'c:\tmp\db_category_2.csv'
delimiter ','
csv ;

import -- 다른 형식의 데이터를 테이블에 넣는 작업

            -- 데이터 구축 시 자주 사용

## 엑셀파일을 적재

create table category_import
(
    category_id serial not null,
     "name" varchar(25) not null,
    last_update timestamp not null default now(),
    constraint category_import_pkey primary key (category_id)
);

copy category_import (category_id, "name", last_update)
from 'c:\tmp\db_category.csv'
delimiter ','
csv header ;

## 텍스트파일을 적재

copy category_import (category_id, "name", last_update)
from 'c:\tmp\db_category.txt'
delimiter '|'
csv header ;

## 컬렴명이 없는 엑셀파일 적재

copy category_import (category_id, "name", last_update)
from 'c:\tmp\db_category_2.csv'
delimiter ','
csv ; -- header 제거 안할 경우, 첫 행을 헤어로 인식하여 한 건 누락됨

2. 테이블 관리

데이터 타입

Boolean
- Boolean 참과 거짓의 값을 저장

Character
- char 고정형 길이의 문자열로 저장 + 공백 O
- varchar 가변형 길이의 문자열로 저장 + 공백 X
- text 대용량의 문자데이터 저장

Numeric
- int 정수형 데이터를 저장. 크기는 4byte(32bit)
- smallint 정수형 데이터를 저장. 크기는 2byte(16bit)
- float 부동 소수점의 데이터를 저장. 크기는 8byte(64bit)
- numeric 전체 크기와 소수점의 자리 지정

Time
- date 일자 데이터를 저장
- time 시간 데이터를 저장
- timestamp 일자와 시간 데이터를 모두 저장

Arrays
- array 배열 형식의 데이터를 저장. 한개의 컬럼의 여러개의 데이터를 동시에 저장
- json json형식의 데이터를 입력해서 각 level의 데이터를 저장

테이블 생성

## 제약조건

not null 해당 제약 조건이 있는 컬럼은 null이 저장될 수 없다

unique 해당 제약 조건이 있는 컬럼의 값은 테이블 내에서 유일해야 한다(null가능)

primary key 해당 제약 조건이 있는 컬럼의 값은 테이블 내에서 유일해야 하고 반드시 not null이어야 한다.

check 해당 제약 조건이 있는 컬럼은 지정하는 조건에 맞는 값이 들어가야 한다

references 해당 제약 조건이 있는 컬럼의 값은 참조하는 테이블의 특정 컬럼에 값이 존재해야 한다.

CTAS -- select문을 기반으로 create table할 수 있는 create문이다.

## 액션영화의 정보만으로 신규 테이블을 생성

create table action_film as -- create table if not exists action_film
select   
        f. film_id,
        f. title,
        f. release_year,
        f. length,
        f. rating
from 
        film f, film_category fc
where f. film_id = fc. film_id
    and fc. category_id = 1 ;

테이블 구조 변경

create table links ;

## active 컬럼 추가

alter table links
        add column active boolean ;

## active 컬럼 제거

alter table links
        drop column active ;

## title 컬럼을 link_title 컬럼으로 변경

alter table links 
        rename column title to link_title ;

## target 컬럼 추가

alter table links
        add column target varchar(10) ;

## target 컬럼의 default 값을 "_blank"로 설정

alter table links
        alter column target 
            set default '_blank' ;

## target 컬럼은 null 대신 default 값 입력됨

insert into links (link_title, url)
values() ;

## target 컬럼에 대한 체크 제약 조건 추가

alter table links 
        add check (target in ('_self', '_blank', '_parent', '_top'));

insert into links(link_title, target)
values() ; -- 'whatever'은 error

테이블 이름 변경

## vendors 테이블 생성

create table vendors (
    id serial primary key,
    name varchar not null
);

## vendors 테이블을 suppliers 테이블로 변경

alter table vendors
        rename to suppliers ;

## supplier_groups 테이블 생성

create table supplier_groups (
    id serial primary key,
    name varchar not null
);

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

alter table suppliers
        add column group_id int not null ;

alter table suppliers
        add foreign key (group_id) references supplier_groups (id) ;

## view 생성

create view supplier_data as
select
            s. id,
            s. name,
            g. name "GROUP"
from
        suppliers s, supplier_group g
where s. group_id = g. id

select * from supplier_data ;

## supplier_groups 테이블 이름 변경

alter table supplier_groups
        rename to groups ; -- 참조하던 suppliers 테이블도 자동 반영

컬럼 추가 -- 기존에 존재하는 테이블에 컬럼 추기

## 테이블 생성

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) ;

## not null 제약 컬럼 추가하는 법

## 1 테이블에 데이터 삽입 완료 가정

insert into tb_cust
values
()
;

## 2 데이터가 있는 테이블에 컬럼(not null제약) 추가 시 에러발생

alter table tb_cust
        add column contact_nm varchar not null ;; -- sql error

## 3 임시로 null 제약으로 변경 후 컬럼 추가

alter table tb_cust
        add column contact_nm varchar null ;

## 4 null 값에 데이터 삽입

update tb_cust
        set contact_nm = '홍길동'
where cust_id = 1 ;

## 5 해당 컬럼에 not null 제약 조건 설정

alter table tb_cust
        alter column contact_nm 
         set not null ;

컬럼 제거

## FK 및 view 참조

create table publishers () ;

create table categories () ;

create table books () ; -- 이때 publishers(publisher_id), categories(category_id) 를 참조하는 컬럼이 존재

create view book_info as select
 from
        books, publishers
where books.publisher_id = publishers.publisher_id ;

## 컬럼 제거

alter table books
        drop column category_id ; -- 가능, cuz books tb is child not parent

alter table books
        drop column publisher_id -- sql error, cascade 사용 시 view도 드랍

컬럼 데이터 타입 변경

## assets 테이블 생성

create table assets (
    id serial primary key,
    name text not null,
    asset_no varchar(10) not null,
    description text,
    location text,
    acqurired_date date not null
);

## insert into assets () values () ;

## name 컬럼의 데이터 타입 변경 -- OK

alter table assets
        alter column name type varchar(50) ;

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

alter table assets
        alter column location type varchar(100),
        alter column description type varchar(500) ;

## int형으로 변경

alter table assets
        alter column asset_no type int ; -- FAIL

then

alter table assets
        alter column asset_no type int using asset_no :: integer ; -- OK

테이블 제거

## FK 관계 두 테이블 생성

create table author ;
create table page ; -- foreign key(author_id) references author (author_id)

## 테이블 제거

drop table author ; -- FK 제약으로 실패

drop table author cascade ; -- page 테이블의 행은 존재하지만 FK제약조건 드랍

임시 테이블 -- 임시 테이블의 이름은 일반 테이블의 이름과 중복 가능하다

## 임시 테이블 생성 후 세션 재접속

create temp table tb_cust_temp_test () ;
insert into tb_cust_temp_test values () ; -- 세션 재접속 후 삭제된 것 확인

turncate -- 대용량의 테이블을 빠르게 지우는 방법

" delete 명령어는 데이터만 삭제, 테이블은 유지한다. 또한 특정 데이터만 삭제 가능 및 rollback 가능, 속도 느림 "

" turncate 명령어는 용량이 줄어든다. 테이블은 삭제하지 않고 데이터만 삭제, 복구 불가능 "

Quiz 1

dvd렌탈 시스템의 관리자는 고객별 매출 순위를 알고 싶다. 신규 테이블을 생성해서 고객의 매출순위를 관리하고 싶으며 신규 테이블의 이름은 customer_rank이고 테이블 구성은 customer_id, customer_rank로 정했다. CTAS 기법을 이용하여 신규 테이블을 생성하면서 데이터를 입력해라.

## 1 payment 테이블에서 customer_id별 매출합을 구한다.

select
        p. customer_id,
        sum(p. amount) as sum_amount
from payment p
group by p. customer_id

## 2 1번의 결과 집합을 인라인 뷰로 감싼 후 amount 합계 별 순위를 구한다.

select
        a. customer_id,
        row_number() over(order by sum_amount DESC) as customer_rank
from
(
select
        p. customer_id,
        sum(p. amount) as sum_amount
from payment p
group by p. customer_id
) a
order by customer_rank ;

## 3 최종으로 CTAS문을 사용하여 테이블 생성 및 데이터 입력

create table customer_rank as ~

Quiz 2

dvd렌탈 시스템의 관리자는 매달 마다 매출 순위 1위를 한 고객에게 특별한 선물을 주고자 한다.

이러한 업무를 달성하기 위해서 customer_rank_YYYYMM이라는 테이블을 CTAS 기법으로 생성하는 SQL문을 작성하라.

(단, 선물 제공 기준을 정하기 위해 sum_amount도 저장하라)

## 1 payment 테이블의 customer_id, YYYYMM 별 amount 합계를 구한다

select
        p. customer_id,
        to_char (p. payment_date, 'YYYYMM') as YYYYMM,
        sum(p. amount) as sum_amount
from payment p
group by p. customer_id, to_char(p. payment_date, 'YYYYMM') ;

## 2 1번의 결과집합을 인라인 뷰로 감싼 후 YYYYMM 별 amount 합계 순위 구한다

select
        a. customer_id,
        YYYYYMM,
        sum_amount,
        row_rank() over(partition by YYYYMM order by sum_amount DESC) as rank_YYYYMM
from
(
select
        p. customer_id,
        to_char (p. payment_date, 'YYYYMM') as YYYYMM,
        sum(p. amount) as sum_amount
from payment p
group by p. customer_id, to_char(p. payment_date, 'YYYYMM')
) a
order by YYYYMM, rank_yyyymm ;

## 3 최종으로 CTAS문을 사용하여 테이블 생성 및 데이터 입력

create table customer_rank_YYYYMM as

## 123등을 한 고객정보 추출

select *
from customer
where customer_id in
(
    select distinct
                    customer_id
    from
                    customer_rank_yyyymm
    where rank_yyyymm in(1,2,3)
);