/* (1) PostgreSQL과 DBeaver 설치
* Win10기준으로 Youtube를 참조하여 설치
*/
--자세한 내용은 Youtube를 참조함
/*
* (2) SQL 기본 구문 구조 이해
* 테스트 data는 (2)의 Youtube에 있음
* DBeaver에서 테스트(test_scripts.sql)
*/
-- 실행할 구문 선택 후 ^+Enter로 실행
select * from prod_cat_info;
select * from transactions;
select * from city;
select * from customer;
/* 테스트(필터링) 구문 */
select count(distinct a.transaction_id) as cnt_orders
, sum(a.total_amt) as sum_amt
, max(a.tran_date) as latest_tran_date
from transactions a
--where total_amt > 0
--group by customer_id
--having count(discount transaction_id) >= 2
--order by sum_amt desc
--limit 12;
-- c는 alias(별칭), distinct는 구별되는 유니크한 것만 추출
select count(c.customer_id)
, count(distinct c.transaction_id)
, sum(c.total_amt)
from transactions c;
select a.customer_id as 고객번호
, a.tran_date as 거래일자
, a.rate as 판매가
, a.qty as 주문수량
, a.total_amt as 주문금액
from transactions a;
select count(distinct b.transaction_id)
, count(distinct b.customer_id)
, sum(b.total_amt)
from transactions b;
-- 중복포함 모두 출력
select store_type
from transactions;
-- 중복된 것은 제거하고 서로 다른 것만 출력
select distinct store_type
from transactions ;
-- 문자 형식의 리터럴은 ''으로 감싸서 입력
-- 날짜 리터럴은 date키워드와 함께 날짜형식으로 입력
-- 날짜와 nation이 일치하는 데이터만 추출
select a.city_code
, a.city_name
, 'KOREA' as nation
, date '2021-09-26' as insert_dt
, 4923 as number_literal
from city a;
/* 새로운 테이블을 하나 만든다
* 기 생성된 DB Retail아래에 mytable이란 테이블 생김
* public -> Tables -> mytable이 생김
*/
create table mytable (
name varchar(80),
pet varchar(80)
);
insert into mytable values ('Mary', 'dog'), ('John', 'cat'), ('Robert', 'bitt');
-- 테이블 만드는 또 다른 예제
CREATE TABLE CUSTOMER
( CUSTOMER_ID VARCHAR(200) PRIMARY KEY,
DOB DATE NOT NULL,
GENDER VARCHAR(200),
CITY_CODE VARCHAR(200)
);
/* (3) Python 연결
* Python 코드로 어떤 테이블에 데이터 추가
* import psycopg2
* conn = psycopg2.connect("host='localhost' dbname='Retail' user='postgres' password='0577' port=5432")
* cur = conn.cursor()
* cur.execute("INSERT INTO mytable (name, pet) values ('Kdj', 'tan');")
* conn.commit() # 데이터 추가됨
*/
select * from mytable;
* cur.execute("SELECT * from mytable;")
* rows = cur.fetchall()
* rows # 출력
* [('Mary', 'dog'), ('John', 'cat'), ('Robert', 'bitt'), ('Kdj', 'tan')]
*/
-- 데이터 갱신
-- > cur.execute("update mytable set name='kdj' where pet='tan'")
-- > cur.execute("select * from mytable;")
-- > conn.commit()
-- > rows:
-- [('Mary', 'dog'), ('John', 'cat'), ('Robert', 'bitt'), ('lee', 'tan')]
-- 데이터 삭제
-- > cur.execute("delete from mytable where name='lee'")
-- > cur.execute("select * from mytable;")
-- > rows = cur.fetchall()
-- > rows: [('Mary', 'dog'), ('John', 'cat'), ('Robert', 'bitt')]
/* (4) SQL shell
* >>> PostgreSQL13 -> SQL shell(psql) 실행 <<<
* Table의 상위인 DATABASE의 조회, 생성, 삭제 가능
* command #로 들어가서
* \l or \l+ # 조회, 또는 상세 조회
* CREATE DATABASE hello; # DB생성
* DROP DATABASE hello; # DB제거
* 이미 만들어진 Retail 확인 가능
*/
/* (5) 이미지 파일의 저장(BYTEA)
* > 아래 파이썬 명령 참고
*/
-- > cur.execute("CREATE TABLE IF NOT EXISTS cartoon(cartoonID INTEGER, name TEXT, cartoonImg BYTEA)")
-- > conn.commit()
-- > cartoonID = 1
-- > name = "Casper"
-- > file_path = 'test.jpg'
-- > drawing = open(file_path, 'rb').read()
-- >
-- > cur.execute("INSERT INTO cartoon (cartoonID,name,cartoonImg) " + "VALUES(%s,%s,%s)", (cartoonID, name, psycopg2.Binary(drawing)))
-- > conn.commit()
-- image가 저장 되었는지 확인 가능
select *
from cartoon;
