SQL - DML 데이터 조작어 (SELECT, WHERE, ORDER BY, GROUP BY, HAVING, JOIN) 기초

 

SQL 분류

DDL(데이터 정의어) : CREATE, ALTER, DROP

DCL(데이터 제어어) : GRANT, REVOKE

 

*명령어를 대문자로 쓰는 것을 지향

 

SELECT

 

 SELECT col FROM TableA

TableA 에서 col 속성 출력

 

 SELECT * TableA

* 은 모든 속성

 

SELECT DISTINCT rental_rate FROM film

DISTINCT : 중복 제외

 

 

---

 

 

 

집계함수(COUNT, SUM, AVG, MAX, MIN) + ROUND , AS

SELECT COUNT(rental_rate), SUM(rental_rate), MIN(rental_rate), MAX(rental_rate), AVG(rental_rate)
FROM film

 

 

ROUND(col, 자리수)

SELECT COUNT(rental_rate), SUM(rental_rate), MIN(rental_rate), MAX(rental_rate), 
ROUND(AVG(rental_rate), 2) AS avg
FROM film

ROUND 로 소수점 자리수 맞출 수 있고

AS 를 통해 속성의 별칭을 설정할 수 있다.

 

 

---

 

 

WHERE

비교연산자

연산자	설명
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
<> | !=	Not equal to

 

 

논리연산자

AND, OR, NOT

 

 

IN 연산자

WHERE first_name (NOT) IN ('John', 'Jake', 'Julie')

SELECT * FROM actor
WHERE first_name IN ('John', 'Jake', 'Julie')

 

 

(NOT) LIKE / ILIKE
LIKE( 대소문자 구분 ) , ILIKE ( 대소문자 구분 안함 )

SELECT * FROM TableA
WHERE name LIKE '_her%'
Cheryl, Theresa, Sherri

_ : 한개의 Char 문자
% : 문자열

문서 : https://www.postgresql.org/docs/9.3/functions-matching.html

 

 

 

BETWEEN

사이값 지정

 

SELECT * FROM payment
WHERE payment_date BETWEEN '2007-02-01' AND '2007-02-15'

날짜 형식은 'ISO-8601'

'2007-02-15' (00:00) 부터
~ 23:59:59 까지

 

 

 

 

---

 

 

ORDER BY (DESC)

order by 기본은 오름차순 (ASC)

SELECT * FROM actor
WHERE first_name NOT IN ('John', 'Jake', 'Julie')
ORDER BY first_name

 

 

 

 

DESC : 내림차순

SELECT * FROM actor
WHERE first_name NOT IN ('John', 'Jake', 'Julie')
ORDER BY first_name DESC

 

 

LIMIT

row 개수 지정

LIMIT 3

SELECT * FROM actor
WHERE first_name NOT IN ('John', 'Jake', 'Julie')
ORDER BY first_name DESC
LIMIT 3

 

 

 

---

 

 

 

GROUP BY

데이터를 집계하고 함수를 적용하는 sql

SELECT category_col, AVG(col2)
FROM table
WHERE
GROUP BY category_col / (날짜인 경우) DATE(payment_date)

+ SUM, MIN, MAX ,AVG, COUNT

참고 : GROUP BY 1 의 '1'은 컬럼 순서 번호

 

 

ex)

SELECT rating, AVG(replacement_cost)
FROM film
GROUP BY rating

film 테이블에서 rating의 그룹별 rating, replacement_cost 의 평균 집계

 

 

HAVING

GROUP BY 문과 함께 사용하여 필터링을 수행

* 별칭으로 HAVING 조건을 수행할 수 없다.

SELECT customer_id, sum(amount) FROM payment
GROUP BY customer_id
HAVING sum(amount) > 200

 

 

 

SELECT customer_id, count(payment_id) FROM payment
GROUP BY customer_id
HAVING count(payment_id) >= 40

 

 

 

 

---

 

 

JOIN

 

- Subject : INNER JOIN
-> 교집합

 

SELECT payment_id, payment.customer_id, first_name FROM payment
INNER JOIN customer
ON payment.customer_id = customer.customer_id

* customer , payment 조인 순서 바꾸어도 대칭(semantic) 이여서 결과가 같다.

 

 

예제)

-- 캘리포니아에 살고 있는 고객의 이메일?

SELECT district, email FROM address
INNER JOIN customer ON address.address_id = customer.address_id
WHERE district = 'California'

 

 

USING

결합 조건으로 사용할 컬럼 지정

SELECT e.empno, deptno, d.dname
FROM emp e JOIN dept d USING(deptno);

 

---

 

 FULL OUTER JOIN

 

여기서 추가로

교집합 제외한 A, B 집합

ex) -- 우리에게 있는 고객 정보 중 결제한 적이 없거나 payment 테이블에만 고유한 것이 있다는 의미

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

WHERE customer.customer_id is null OR payment.payment_id is null

 

순서 바꾸어도 대칭(semantic) 해서 결과가 같다.

 

 

 

---

LEFT (OUTER) JOIN

왼쪽 테이블에 있거나 둘다 있거나

조인 순서에 따라 결과가 다르다.

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 (교집합 제외, 오른쪽 테이블 null 인 것 찾기)

 

 

RIGHT (OUTER) JOIN

오른쪽 테이블에서 왼쪽으로

SELECT * FROM tableA 
RIGHT OUTER JOIN tableB
(WHERE tableA.id is null)

 

 

 

테이블 3개 조인

ex) -- 닉 월버그가 출연한 영화목록

SELECT title, first_name, last_name FROM film
INNER JOIN film_actor ON film.film_id = film_actor.film_id
INNER JOIN actor ON film_actor.actor_id = actor.actor_id
WHERE first_name = 'Nick' and last_name = 'Wahlberg'

위 부터 select 한후

actor와 조인

 

 

 

---

 

 

---

 

 

UNION

concat 과 비슷

SELECT * FROM Salse2021_Q1
UNION
SELECT * FROM Salse2021_Q4
(ORDER BY name;)

 

 

 

---

비등가조인

컬럼과 컬럼 사이 값에 속하는 것

SELECT e.sal, s.losal, s.hisal
FROM emp e, salgrade s
WHERE e.sal BETWEEN losal AND hisal;

 

 

 

포인트에 따라 어떤 gift 에 속하는 지

SELECT *
FROM customer C, gift G
WHERE C.POINT BETWEEN g_start AND g_end;

SELECT *
FROM customer C
JOIN gift G ON C.POINT BETWEEN g_start AND g_end;

--또는
SELECT *
FROM customer C
JOIN gift G ON C.POINT >= g_start AND C.POINT <=g_end;