[02. PostgreSQL] 007. DML – INSERT, SELECT, UPDATE, DELETE

가장 중요한 시간이다. 솔직히 SQL은 INSERT, SELECT, UPDATE, DELETE 4가지만 알면 90%는 끝난 것이다. DB전문가가 아닌 이상 현업에서는 이걸로 충분하다.

 

1) INSERT

이 명령어는 데이터를 입력하는데 사용된다. 테이블에서 uuid타입을 사용하고 있는 경우 이를 생성하기 위해 아래와 같이 uuid 생성 모듈을 설치한다.

 

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

 

현재의 타임과 컴퓨터의 맥어드레스를 이용한 uuid를 생성하기 위해서는 uuid_generate_v1()를 사용하고 단순히 완전 랜덤으로 생성하기 위해서는 uuid_generate_v4()를 사용하면 된다.

 

INSERT INTO postgresql.stock_order VALUES(uuid_generate_v1(), '2019-01-01', '005930 KS Equity', 50000, 100);

 

2) SELECT

이 명령어는 데이터를 조회하는데 사용된다.

 

SELECT * FROM postgresql.stock_trade;

 

위와 같이 조회를 하면 해당 테이블의 모든 데이터를 보여준다. * 대신에 특정 컬럼을 입력하면 입력된 컬럼의 데이터만 보여준다.

 

SELECT와 결합하여 데이터를 검색 및 가공을 하는 방법도 있다. 자세한 내용은 아래를 참고하자.

 

 

2-1) WHERE: 데이터 검색

SELECT * FROM postgresql.stock_trade WHERE ticker='005930 KS Equity';

SELECT * FROM postgresql.stock_trade WHERE ticker like '005930%';

SELECT * FROM postgresql.stock_trade WHERE ticker<>'005930 KS Equity';

SELECT * FROM postgresql.stock_trade WHERE ticker<>'005930 KS Equity' and filled_qty>100;

SELECT * FROM postgresql.stock_trade WHERE ticker in ('005930 KS Equity', '005380 KS Equity') or filled_qty>100;

 

2-2) GROUP BY: 데이터 그룹화

SELECT ticker, sum(filled_qty*filled_price) / sum(filled_qty) as avg_price FROM postgresql.stock_trade GROUP BY ticker;

SELECT ticker, count(*) || '건' as cnt FROM postgresql.stock_trade GROUP BY ticker;

 

2-3) HAVING: 조건이 있는 데이터 그룹화

SELECT ticker, sum(filled_qty*filled_price) / sum(filled_qty) as avg_price FROM postgresql.stock_trade GROUP BY ticker HAVING ticker = '005930 KS Equity';

 

2-4) ORDER BY: 데이터 정렬

SELECT * from postgresql.stock_trade ORDER BY ticker ASC;

SELECT * from postgresql.stock_trade ORDER BY ticker DESC;

 

2-5) 서브쿼리: 쿼리속의 쿼리

SELECT * from postgresql.stock_trade where order_id in (SELECT id FROM postgresql.stock_order);

 

2-6) 조건식

SELECT ticker, case when ticker='005930 KS Equity' then '삼성전자' when ticker='005380 KS Equity' then '현대차' else null end from postgresql.stock_trade where order_id in (SELECT id FROM postgresql.stock_order);

 

2-7) OVER PARTITION BY: 내부에서 그룹화(GROUP BY는 외부에서 그룹화)

SELECT ticker, sum(filled_qty*filled_price) OVER (PARTITION BY ticker) / sum(filled_qty) OVER (PARTITION BY ticker) as avg_price FROM postgresql.stock_trade where ticker='005930 KS Equity';

 

2-8) RANK: 순위 표시, DENSE_RANK: 중복 순위 건너뛰기 하지 않음

SELECT *, RANK() OVER(Order by trade_date ASC) FROM postgresql.stock_trade;

SELECT *, DENSE_RANK() OVER(Order by trade_date ASC) FROM postgresql.stock_trade;

 

2-9) DISTINCT: 중복 데이터 제거, DISTINCT ON: 중복 데이터 중 첫 번째 ROW데이터만 남김

SELECT DISTINCT trade_date, ticker, filled_qty FROM postgresql.stock_trade ORDER BY trade_date, ticker, filled_qty;

SELECT DISTINCT ON (trade_date) trade_date, ticker, filled_qty FROM postgresql.stock_trade ORDER BY trade_date, ticker, filled_qty;

 

2-10) LIMIT: 제한하여 조회하기, OFFSET: 몇 번째부터 제한하여 조회하기

SELECT * FROM postgresql.stock_trade LIMIT 2;

SELECT * FROM postgresql.stock_trade LIMIT 2 OFFSET 1;

 

2-11) JOIN: 테이블간 데이터 합치기

- INNER JOIN

SELECT * FROM postgresql.stock_order a INNER JOIN postgresql.stock_trade b ON a.id=b.order_id;

 

- FULL JOIN

SELECT * FROM postgresql.stock_order a FULL JOIN postgresql.stock_trade b ON a.id=b.order_id;

 

- LEFT JOIN

SELECT * FROM postgresql.stock_order a LEFT JOIN postgresql.stock_trade b ON a.id=b.order_id;

 

- RIGHT JOIN

SELECT * FROM postgresql.stock_order a RIGHT JOIN postgresql.stock_trade b ON a.id=b.order_id;

 

[그림 2.5] JOIN

 

3) UPDATE

이 명령어는 데이터의 값을 수정하기 위해 사용된다.

 

방법은 다음과 같다.

UPDATE postgresql.stock_trade SET filled_qty=100, … WHERE ticker='005930 KS Equity';

 

4) DELETE

이 명령어는 데이터를 삭제하는데 사용된다. Truncate와 차이점은 DELETE는 데이터만 삭제하기 때문에 Commit이전에는 Rollback이 가능하지만 TRUNCATE는 테이블을 최초 생성된 초기상태로 만들기 때문에 Rollback이 불가능하다.

 

방법은 다음과 같다.

DELETE FROM postgresql.stock_trade;

DELETE FROM postgresql.stock_trade WHERE ticker=’005930 KS Equity’;

 

JOIN 그림출처: POSTGRESQL TUTORIAL