PostgreSQL

내가 설치한 버전

postgreSQL - 16.1
pgAdmin4 - 8.0

SQL 구문

• 대소문자,세미콜론 구별이 없지만, 대문자, 세미콜론 사용을 추천!

SELECT

SELECT column_name FROM table_name

DISTINCT

• 중복 제거

SELECT DISTINCT column FROM table

COUNT

• 개수를 세는

SELECT COUNT(name) FROM table;

SELECT COUNT(DISTINCT(amount)) FROM payment;

SELECT WHERE

• where 로 열에 조건을 지정

SELECT * FROM customer WHERE first_name = 'Jared';

SELECT * FROM film WHERE rental_rate > 4 AND replacement_cost >=19.99;

ORDER BY

• 오름차순,내림차순

SELECT store_id,first_name FROM customer ORDER BY store_id DESC, first_name ASC;

LIMIT

• 쿼리에 반환되는 행의 개수를 제한

• ORDER BY 와 자주 같이 사용.

SELECT * FROM payment ORDER BY payment_date DESC LIMIT 5;

BETWEEN

• 값 범위의 사잇값 검색

• BETWEEN 8 AND 9 => 8과 9 포함!

• NOT BETWEEN 8 AND 9 => 8과 9 포함 X!

IN

• WHERE 절 안에 특정 값을 선택하는 연산자

SELECT color FROM table WHERE color IN ('red','blue','green')

SELECT * FROM customer WHERE first_name NOT IN ('John','Jake','Julie');

LIKE and ILIKE

• 패턴 매칭.

LIKE

• 대소문자를 구분한다

ILIKE

• 대소문자 구분 X

% : 글자나 문자의 순서와 관계없이

WHERE name LIKE 'A%'

SELECT * FROM customer WHERE first_name LIKE 'J%'

밑줄 '_'

WHERE value LIKE 'Version__'

집계함수

MAX & MIN

• 집계함수는 하나의 열에서만 사용 가능하다.
• 다른 열을 호출하려면 GROUP BY 를 사용해야함!

SELECT MAX(replacement_cost) FROM film;

AVG

• 부동 소수점을 표시한다.
• ROUND 로 소수점 몇째 자리에서 반올림할지 지정 가능!

SELECT ROUND(AVG(replacement_cost),2) FROM film;

SUM

SELECT SUM(replacement_cost) FROM film;

GROUP BY

• 일정 카테고리 별로 열을 집계한다.

• 순서는 중요하지 않다.

• WHERE 이나 FROM 문 바로 뒤에 위치한다.

• GROUP BY 문을 사용하고 특정 열만을 선택하는 경우에는 GROUP BY 문에 반드시 그 열을 포함시켜야한다.

WHERE 문과 GROUP BY 문을 함께 사용하는 경우 WHERE 문에는 집계 대상을 적어서는 안된다.
=> HAVING 이 하는 것.

• AGG 에 집계함수

SELECT category_col , AGG(data_col) FROM table GROUP BY category_col;

// 회사별 부서별 판매액의 총 합
SELECT company, division, SUM(sales) FROM finance_table GROUP BY company, division

HAVING

• GROUP BY 문이 실행된 이후 집계된 결과를 바탕으로 where 을 하고 싶을때

SELECT company, SUM(sales) FROM finance_table GROUP BY company HAVING SUM(sales) > 1000;

AS

• 열이나 결과에 별칭을 부여

• 쿼리의 맨 마지막에 실행된다. => where, group by 에는 사용 불가.

• SELECT 에만 사용이 가능!

SELECT COUNT(amount) AS num_transactions FROM payment

SELECT customer_id, SUM(amount) AS total_spent FROM payment GROUP BY customer_id HAVING total_spent >100

=> 별칭 에러!

SELECT customer_id, SUM(amount) AS total_spent FROM payment GROUP BY customer_id HAVING SUM(amount) >100

=> O

---

JOIN

INNER JOIN

• 두 테이블을 매칭되는 지점으로 합치기

SELECT * FROM TableA INNER JOIN TableB ON TableA.col_match = TableB.col_match

• Table A, Table B 모두에 있는 행만 가져온다.

FULL OUTER JOIN

• 한 테이블에만 있는 경우든 모든 테이블에 있든 상관없이 모두 검색

• 겹치는게 없으면 null 자동 입력.

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name

• 테이블 A와 테이블 B에 고유한 레코드 집합을 생성하기 위해
  동일한 FULL OUTER JOIN을 수행한 다음 where 절을 통해 양쪽에서 원하지 않는 레코드를 제외합니다.

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableA.id IS null
OR TableB.id IS null


SELECT * FROM customer 
FULL OUTER JOIN payment 
ON customer.customer_id = payment.customer_id
WHERE customer.customer_id IS null
OR payment.payment_id IS null

LEFT (OUTER) JOIN

• 대칭이 아니기에 순서가 중요. 어떤것이 왼쪽 테이블이 될지..

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name


SELECT film.film_id, title, inventory_id, store_id
FROM film
LEFT JOIN inventory ON
inventory.film_id = film.film_id

// film 이 왼쪽이기 때문에 film 테이블에만 있거나 film과 inventory 모두에 있는 행만 확인할것

• where 문으로 조건을 추가해 왼쪽 테이블에만 고유한 행을 구하기

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableB.id IS null


SELECT film.film_id, title, inventory_id, store_id
FROM film
LEFT JOIN inventory ON
inventory.film_id = film.film_id
WHERE inventory.film_id IS null

// 관련있는 영화 정보는 나오지만 재고는 없습니다

RIGHT JOIN

• LEFT JOIN 과 같은것.

SELECT * FROM TableA
RIGHT JOIN TableB
ON TableA.name = TableB.name

SELECT * FROM TableA
RIGHT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableA.id IS null

UNION

• UNION 은 두 결과를 직접 붙인다는 차이.

• 바로 붙일 수 있도록 열이 일치해야한다!

• EX) 1분기, 2분기 테이블 합치기

SELECT column_name FROM table1 UNION SELECT column_name FROM table2;

셀프 조인

• 두개의 직원표를 하나의 직원표로 만드는 느낌

SELECT emp.name, report.name
FROM employees AS emp
JOIN employees AS report ON
emp.emp_id = report.report_id

겹치는 영화가 아니면서, 길이는 같은 영화를 찾을때

SELECT f1.title, f2.title, f1.length
FROM film AS f1
INNER JOIN film AS f2
ON f1.film_id != f2.film_id
AND f1.length = f2.length

---

멘토링 QnA

Q. 프론트 엔드와 백엔드가 명확히 구분된 팀 프로젝트를 진행해 본 경험이 없기에 다소 막연하게 질문을 드려봅니다 :) 프론트 엔드와 백엔드 개발자 간 흔히 발생하는 갈등 상황의 대표적인 케이스가 존재한다면 몇가지 소개를 부탁드립니다! 백엔드 개발자 관점에서, 프론트엔드 개발자와 원활히 협업하기 위해 가지면 좋을 마인드셋이나 배경지식이 있을까요?

• 백엔드는 CRUD 성 API 를 쪼개는게 편해.이슈도 적고 다양한 문제발생의 소지가 적어. 프론트엔드는 여러 API 를 나눠서 호출하기 또 싫거든. 그래서 서로 상충하게돼. => graphQL 로 해결

• 프론트는 디자이너, 기획자랑 한번 합의하고 백엔드랑 맞춰야해. 근데 그 합의 과정에서 백엔드 개발자는 보통 없어서 커뮤니케이션 미스가 자주 일어남.

• 백엔드를 하더라도 프론트를 해봐야해. 그래야 입장을 이해할 수 있어. 그 부분을 한번이라도 겪어보면, 아~ 이래서 그렇구나 라는걸 알 수 있음. 그러기 위해서 되게 가볍게라도 다른 티어를 만져보면 이해도가 확확 오를 수 있어.

• DBA 가 요즘 없어졌지만, DBA 를 이해하기 위해 스토어드 프로시저를 구성해본다거나 등등

---