SQL 구문정리
5. 데이터 타입과 제약조건
1. 데이터 타입
Boolean -- 참과 거짓에 대한 값을 저장하는 데이터 타입
True = t, 1, y, yes
False = 0, no
## 참 값 추출
select *
from
stock_availability
where available = 'yes' ; -- where available ;
or
where available = 'no' ; -- where not available ;
char/varchar/text -- 3개의 차이 구분
## 실습
create table character_tests
(
id serial primary key,
x char(3),
y varchar(10),
z text
) ;
insert into character_tests
values
(1, 'Y', 'YES', 'YESYESYES') ;
commit ;
## 결과
id = 1
x = Y**
y = YES
z = YESYESYES
insert into charater_tests
values
(2, 'Y', 'Y', 'YESYESYES') ;
commit ;
## 결과
id = 2
x = Y**
y = Y
z = YESEYSEYS
but, X=Y
numeric -- 정수부터 실수형까지의 숫자를 표현, 각각의 자릿수 지정
create table products
(
id serial primary key,
name varchar not null,
price numeric(5, 2)
) ;
insert into products (name, price)
values
('Phone', 500.215),
('Tablet', 500.214)
;
commit;
## 결과
(5, 2)의 범위를 가지므로, 소수점 3째자리에서 반올림 된다.
500.22
500.21
## 에러
insert into products (name, price)
values
('공기청정기', 123456.21) ; -- (5,2)의 범위를 초과
integer
SMALLINT: 2byte (-32,768 ~ 32,768)
INTEGER: 4byte (-2,147,483,648 ~ 2,147, 483,647)
BIGINT: 8byte (-9,223,372,854,775,80 ~ 9,223,372,854,775,80)
## 실습
create table books
(
book_id seiral primary key,
title varchar(255) not null,
pages smallint not null check (pages > 0)
);
create table cities
(
city_id serial primary key,
city_name varchar(255) not null,
population int not null check(population >= 0)
)
date 일자
SELECT NOW()::date; -- 현재 일자를 가져온다
SELECT CURRENT_DATE; -- 현재 일자를 가져온다
SELECT TO_CHAR(NOW() :: DATE, 'dd/mm/yyyy'); -- 다른 형태의 포맷으로
SELECT TO_CHAR(NOW() :: DATE, 'Mon dd, yyyy');
## 응용
select
first_name,
last_name,
age(create_Date) as diff -- create date - 현재 시점으로 나이 구함
from customer ;
select
first_name,
last_name,
now() - create_date as diff
from customer ;
## 다양한 일자 추출
select
first_name,
last_name,
extract (year from create_date) as year,
extract(month from create_date) as month,
extract(day from create_date) as day
from customer ;
time 시간
select current_time ;
select localtime ;
## 다양한 시간 추출 -- 전자와 같음
## 시간 계산
select time '10:00' - time '02:00' as diff ;
select
localtime,
localtime + interval '2hours' as plus_2hours,
localtime - interval '2hours' as minus_2hours ;
timestamp 일자 및 시간
select now() ; -- 2021-08-03 14:30:59
select current_timestamp ; -- 2021-08-03 14:31:16
select timeofday() ; -- Tue Aug 03 14:31:39.194035 2021 KST
## 다양한 형태의 시간 포맷을 문자열로 출력
select
to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS.US') ;
-- 2021-08-03 14:31:58.002093
기본키 -- 테이블 내의 유일한 값, not null, 실체 무결성에 대한 제약
## 기본키 생성
create table tb_product_pk_test
(
product_no integer,
description text,
product_cost numeric
);
alter table tb_product_pk_test
add primary key (product_no) ; -- unique & not null
## 기본키 auto increment
create table tb_product__pk_test_2
(
name varchar(255)
);
insert into tb_product_pk_test_2 (name)
values
()
;
commit;
alter table tb_product_pk_test_2
add column id serial primary key ;
## 기본키 제거
alter table tb_product_pk_test
drop constraint tb_product_pk_test_pkey ;
alter table tb_product_pk_test_2
drop constraint tb_product_pk_test_2_pkey ;
외래키 -- 자식 테이블의 특정 컬럼이 부모 테이블의 특정 컬럼의 값을 참조
-- 참조 무결성
## 실습
alter table so_items
add constraint fk_so_headers_id
foreign key (so_id) references so_headers(id) ;
insert into so_headers ()
values
()
;
insert into so_items () -- so_id의 값은 so_headers의 id 컬럼에 대응한다
values
()
;
## 삭제
alter table so_itmes
drop constraint fk_so_headers_id ;
## 테이블 생성과 동시에 외래키 생성
create table so_items
(
item_id integer not null,
so_id integer references so_headers(ID), -- OK
product_id integer,
QTY integer,
net_price numeric,
primary key (item_ID, so_id)
);
## 복합 외래키도 생성 가능
create table child_table
(
c1 integer primary key,
c2 integer,
c3 integer,
foriegn key (c2, c3) references parent_table (p1, p2)
) ;
체크제약 조건 -- 업무상 절대 들어갈 수 없는 값을 물리적으로 막음
## 실습
create table tb_emp_check_test
(
id serial primary key,
first_name varchar(50),
last_name varchar(50),
birth_date date check (birth_date > '1900-01-01'),
joind_Date date check (joined_date > birth_date),
salary numeric check (salary > 0)
) ;
insert into tb_emp_check_test
(
first_name,
last_name,
birth_date,
joined_date,
salary
)
values
(
'John',
'doe',
'1972-01-01',
'2015-07-01',
-100000 -- SQL ERROR
) ;
## 테이블 생성 후 체크 제약 조건 추가
alter table tb_emp_check_test
add constraint salary_range_check
check (salary > 0 and salary <= 100000000) ;
alter table tb_emp_check_test
add constraint name_check
check (length (first_name) > 0 and length (last_name) > 0) ;
Unique 제약조건
## 실습
create table person
(
id serial primary key,
first_name varchar (50),
last_name varchar(50),
email varchar (50),
unique (email) -- email 주소는 유일값이어야 한다
) ;
insert into person(first_name, last_name, email)
vlaues
(
'john',
'doe',
'j.doe@postgresqltutorial.com'
) ; -- OK
but, 상단의 SQL 구문을 반복하면 제약 조건으로 ERROR
## Unique index 생성 -- unique가 더 빨리 구동된다
create unique index
ix_person_unique_index_test_o1
on person_unique_index_test (email) ;
not null 제약 조건 실습
## update
create table invoice_update_test
(
id serial primary key,
product_id int not null,
QTY numeric not numm check(QTY >0),
net_price numeric check (net_price > 0)
) ;
insert into
valeus
()
;
update
invoice_update_test
set
product_id = null
where
product_id = 1 ; -- SQL ERROR
Quiz 1
영화 예매 시스템을 구축하고자 한다. 아래의 요구조건을 부합하는 물리 테이블을 생성하고 임의의 테스트 데이터를 입력하시오.
1. 고객(TB_MOVIE_CUST) 테이블을 생성한다.
고객 테이블의 테이블 구성은 아래와 같다.
고객ID(문자열10자리) 고객ID는 고객 테이블의 기본키이다.
, 고객명(문자열최대50) 고객명은 널값을 가질수 없다.
, 성별(문자열최대6) 성별은 남자 혹은 여자의 값만 가진다.
, 생년월일(DATE) 생년월일은 널값을 가질수 없다.
, 주소(문자열최대200) 주소는 넒값을 가질수 있다.
, 핸드폰번호(문자열최대13자리) 핸드폰번호는 널값을 가질 수 있다.
, 고객등급(문자열1자리) 고객등급은 널값을 가질수 없다. 고객등급은 S, A, B, C, D 값만 허용한다.
, 회원가입일자(DATE) 회원가입일자는 널값을 가질수 없다.
, 회원탈퇴일자(DATE) 회원탈퇴일자는 널값을 가질수 없고 기본값은 9999년 12월 31일로 한다. 회원가입일자는 회원탈퇴일자보다 작아야 한다.
## 결과
create table tb_movie_cust
(
cust_id char(10) primary key,
cust_nm varchar(50) not null,
sex varcchar(6) not null check(sex in ('남자', '여자')),
birth_date date not nul,
address varchar (200),
phone_number varcahr(13),
cust_grade char(1) check (cust_grade in ('S', 'A', 'B', 'C', 'D') ),
join_dt date not null check(join_dt < = expire_dt),
expire_dt date not null default to_date ('9999-12-31', 'YYYY-MM-DD')
) ;
INSERT INTO TB_MOVIE_CUST(CUST_ID, CUST_NM, SEX, BIRTH_DATE, ADDRESS, PHONE_NUMBER, CUST_GRADE,JOIN_DT)
values
('0000000001', '서진희', '여자', TO_DATE('1996-08-23', 'YYYY-MM-DD'), '경기도 구리시 갈매순환로 1길', '010-1234-1234', 'S', TO_DATE('2017-01-01', 'YYYY-MM-DD')) ;
2. 예매(TB_MOVIE_RESV) 테이블을 생성한다.
예매 테이블의 테이블 구성은 아래와 같다.
예매번호(문자열10자리) 예매번호는 예매테이블의 기본키이다.
,영화ID(문자열6자리) 영화ID는 널값을 가질수 없다.
,영화관번호(문자열6자리) 영화관번호는 넒갑을 가질수 없다.
,고객ID(문자열10자리) 고객ID는 널값을 가질수 없고 고객테이블의 고객ID를 참조한다.
,영화시작시간(TIMESTAMP) 영화시작시간은 널값을 가질수 없다. 영화시작시간은 영화종료시작보다 반드시 작아야 한다.
,영화종료시간(TIMESTAMP) 영화종료시간은 넒값을 가질수 없다.
,좌석번호(문자열4자리) 좌석번호는 널값을 가질수 없다.
## 결과
CREATE TABLE TB_MOVIE_RESV
(
RESV_NO CHAR(10) PRIMARY KEY
, MOVIE_ID CHAR(6) NOT NULL
, MOVIE_Theater_ID CHAR(6) NOT NULL
, CUST_ID CHAR(10) REFERENCES TB_MOVIE_CUST(CUST_ID) NOT NULL
, MOVIE_START_TIME TIMESTAMP NOT NULL CHECK (MOVIE_START_TIME < MOVIE_END_TIME)
, MOVIE_END_TIME TIMESTAMP NOT NULL
, SEAT_NO CHAR(4) NOT NULL
)
;
INSERT INTO TB_MOVIE_RESV
VALUES
('9000000001', '000001', '000010', '0000000001', to_timestamp('2019-05-01 14:00:00', 'YYYY-MM-DD HH24:MI:SS'), to_timestamp('2019-05-01 17:30:00', 'YYYY-MM-DD HH24:MI:SS'), 'A-01')
;
Quiz 2
영화 예매 시스템의 고객 통계를 보고 싶다.
아래의 조건을 만족하는 SQL을 작성하시오.
## 목표 출력값
|전체고객수|등급의개수|등급별평균고객수|등급별최대고객수|등급별최소고객수|최소고객수의등급|최대고객수
|-------|--------|------------|--------|--------|--------|--------
| 9| 5| 1.80| 3| 1|D |A
## 1 고객등급 별 카운트 구하기
select count(*) CNT
from tb_movie_cust
group by cust_grade ;
## 2 1번을 기준으로 평균, 최대, 최소 구하기
select
avg (cnt) avg_by_grade,
max (cnt) max_by_grade,
min(cnt) min_by_grade
from
(
select count(*) CNT
from tb_movie_cust
group by cust_grade
) A ;
#3 고객 등급별로 최소 고객수의 등급 구하기
select
cust_grade as grade)by_min_emp_count
from
(
select cust_grade,
count(*) CNT
from tb_movie_cust
group by cust_grade
order by CNT
)A
limit 1 ;
#4 고객 등급별로 최대 고객수의 등급 구하기
select
cust_grade as grade)by_max_emp_count
from
(
select cust_grade,
count(*) CNT
from tb_movie_cust
group by cust_grade
order by CNT DESC
)A
limit 1 ;
#5 인라인뷰로 네 집합 조인하고 목표값 구하기
select
count (*) "전체고객수",
count(distinct cust_grade) "등급의개수",
round(max(avg_by_grade), 2) "등급별평균고객수",
MAX(MAX_BY_GRADE) "등급별최대고객수",
MAX(MIN_BY_GRADE) "등급별최소고객수",
MAX(GRADE_BY_MIN_EMP_COUNT) "최소고객수의등급",
MAX(GRADE_BY_MAX_EMP_COUNT) "최대고객수의등급"
from
tb_movie_cust,
(SELECT AVG(CNT) AVG_BY_GRADE, MAX(CNT) MAX_BY_GRADE, MIN(CNT) MIN_BY_GRADE
from A) B, C, D
