sql 사용 완전 공략 [mysql]

1) SQL 쿼리 순서

# 적는 순서
SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY - LIMIT

# 실행 순서
FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY - LIMIT

위와 같은 순서로 실행 되기에 select의 alias를 where 절에서 사용하는 등의 행동은 불가하다. (where이 select보다 먼저 수행된다.)

 

2) IN

IN(a,b,c): 어떠한 컬럼 값이 a,b,c 중 하나라면 select된다.

SELECT CATEGORY,PRICE as MAX_PRICE,PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE (PRICE) IN (SELECT MAX(PRICE) FROM FOOD_PRODUCT GROUP BY CATEGORY)
AND CATEGORY IN ('과자', '국', '김치', '식용유')
ORDER BY PRICE DESC

 

아래와 같이, (컬럼 1, 컬럼 2) in  (select 컬럼 1와 비교할 값, 컬럼 2와 비교할 값 .... ) 으로 다수의 컬럼의 비교하는 것 역시 가능하다. 단,   (A,B,C) in  (select X,Y,Z)에서, 순서대로 A<>X 내역, B<>Y 내역, C<>Z 내역 안에서 찾게된다.

SELECT CATEGORY, PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE (CATEGORY, PRICE) IN (
    SELECT CATEGORY, MAX(PRICE)
    FROM FOOD_PRODUCT
    GROUP BY CATEGORY
    HAVING CATEGORY IN ('과자', '국', '김치', '식용유')
)

 

 

 

3) 문자열 

3-1) 문자열 비교

 

같은가?

'파닥%' : '파닥'으로 시작하는 문자열
'%파닥' : '파닥'으로 끝나는 문자열
'%파닥%' : '파닥'이 들어간 문자열
'_파닥' : 첫 글자 하나 있고 그 뒤에 '파닥'이 있는 문자열 (3글자)
'파닥_' : 처음에 '파닥'이 있고 맨 끝에 글자 하나 있는 문자열 (3글자)
'_파닥_' : 처음과 끝에 '파닥'이 있고, 그 가운데 '파닥'이 있는 문자열 (4글자)

ex. WHERE STRING LIKE '파닥%' (LIKE를 꼭 써주자)

like: AntPatmatcher 등 패턴 매치와 똑같다. 

 

대소비교

...........
where
"1234" < str

 

 추가로 "1234" < 특정 컬럼 과 같이 문자열간에 사전순으로 대소비교가 가능하기에, 날짜가 주어질 때, 비교 연산자로 대소 비교를 하는 것도 가능하다.

3-2) 문자열 길이

문자열 길이 -> CHAR LENGTH
Byte 길이 -> LENGTH 

LENGTH('PADAKMON') => 8
LENGTH('포켓몬') => 8

CHAR_LENGTH('PADAKMON') => 8
CHAR_LENGTH('포켓몬') => 3

 

3-3) 소문자와 대문자 변환

LOWER(STR) ↔️ UPPER(STR)

3-4) 문자열 가져오기

// STR=HELLO
LEFT(STR, 2) => HE    #왼쪽 2개
RIGHT(STR, 2) => LO	#오른쪽 2개
SUBSTR(STR, 2,3) => EL (2번째 ~ 3번째)

주의: index가 0이 아닌 1 부터 시작한다.
그리고 substr는 (문자열,시작, 끝) 으로 구성하며 시작, 끝을 포함한다

3-5) 공백 제거, 특정 문자 제거

// LEFT
LTRIM(STR)//왼쪽 공백 제거
TRIM(LEADING 특정문자 FROM STR)//STR의 가장 왼쪽부분의 특정 문자를 제거
// RIGHT
RTRIM(STR)//오른쪽 공백 제거
TRIM(TRAILING 특정문자 FROM STR)//STR의 가장 오른쪽부분의 특정 문자를 제거
// BOTH
TRIM(STR)//좌우 공백 제거
TRIM(BOTH 특정문자 FROM STR) // STR의 양쪽 끝부분의 특정 문자를 제거

 

 4) GROUP_BY

GROUP_BY를 사용하면 SELECT 할 수 있는 것이 제한된다.

집계 함수 - SUM, COUNT, MIN/MAX, AVG만 가능.

만약 A,B,C라는 컬럼(attribute)이 있고 A로 grouping시
SELECT A, COUNT(B) 처럼 grouping한 컬럼만 추출할 수 있고, 나머지 컬럼은 통계 수치(ex. COUNT,SUM,AVG)만 추출 가능하다. 

MIN, COUNT, SUM 등 집계함수는 "select" 절, order by 등등에서 사용 가능하다. 또한, count()는 null은 개수에 포함 시키지 않는다.

집계함수는 group by 와 함께 사용 시 각 그룹을 집계하는데 사용할 수 있지만, group by 없이도 사용 가능하다.

# A테이블의 튜플 수 선택
SELECT count(*) 
from A

 

예제

https://school.programmers.co.kr/learn/courses/30/lessons/133027

프로그래머스

 

order by 에서 역시 집계함수로 사용가능.

select FLAVOR
from
(SELECT * FROM FIRST_HALF
UNION
SELECT * FROM JULY) as total
group by flavor
order by SUM(TOTAL_ORDER) DESC
limit 3

 

* 주의  group by 사용 시, select 절에는 집계합수, group by의 대상이 된 column 만 올 수 있다.

 

https://school.programmers.co.kr/learn/courses/30/lessons/293261

ex) NAME으로 group  by 하면, 각 name 간에 서로 다른 ID 가 너무 많다. 따라서 ANY_VALUE 등으로 표기해줘야함

프로그래머스

하지만 아래와 같이 ANY_VALUE를 한다면, ID 에는 MAX(LENGTH) 에 맞는 ID 값이 아닌, Random ID 값이 들어와서 의도와 다른 답이 나온다..

 

정답

SELECT ID, FISHNAME, LENGTH
FROM FISHINFO JOIN FISHNAMEINFO USING(FISHTYPE)
WHERE (FISHTYPE, LENGTH) IN (
SELECT FISHTYPE, MAX(LENGTH) AS LENGTH
FROM FISHINFO
GROUP BY FISH_TYPE
)
ORDER BY ID ASC;

 

having VS where

위에서 적은 쿼리 실행 순서에서 알 수 있겠지만, where 절과 다르게 group by 이후의 결과에 조건을 걸어 필터링된다.

where : group by로 grouping 하기전에 조건을 통해 필터링한다.

having: group by로 grouping 완료 후에 조건을 통해 필터링 한다.

 

즉, where은, from에서 선택한 테이블 전체에 대한 조건을 거는 것이고, having은 group by로 그룹화하여, 각 그룹당 조건을 거는 것이다. 

 

ex) 부서내 직원 중 최고 연봉이 1억 이상인 부서만 고르기 -> having에서 각 그룹변 제약조건을 사용해야한다.

 

또한 having은 mysql에 특수하게 실행 순서와 관계없이  select의 alias를 사용하는 것도 가능하다.

#HAVING 사용 시, select 절의 alias 사용 가능.
SELECT ROUND(A.DAILY_FEE * 30 * (1 - B.DISCOUNT_RATE/100)) AS FEE
FROM xxx
HAVING FEE <500

#where 사용 시, select 절의 alias 사용 불가.
SELECT ROUND(A.DAILY_FEE * 30 * (1 - B.DISCOUNT_RATE/100)) AS FEE
FROM xxx
where ROUND(A.DAILY_FEE * 30 * (1 - B.DISCOUNT_RATE/100)) <500 #FEE 사용 불가하기에, 중복 발생!
#위와 같이 할바에는, SET @ 를 통해 변수에 값을 할당하는 것도 방법이다.

 

아래 문제를 풀어보자.

https://school.programmers.co.kr/learn/courses/30/lessons/157340\

프로그래머스

 

정답코드

SELECT  car_id, case when  #select 절에서 값에 조건을 걸기위해 case 사용
SUM(#해당 그룹 내에서, 특정 조건 만족하는 튜플 수 구하기
    CASE WHEN(start_date<="2022-10-16" AND "2022-10-16"<=end_date) 
    THEN 1  #조건 만족(해당 기간에 대여 불가) 시 1
    ELSE 0  # 조건 만족 x 시 0
    END) >0  #해당 car의 모든 대여 기록 중, 한번이라도 지정한 날짜에 대여가 예정되있으면 대여불가
then "대여중"else "대여 가능" end as AVAILABILITY
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
group by car_id
order by car_id desc

우선, history 테이블에는 다수의 car_id가 있다.( 1개의 차량이 여러번 대여 될 수 있다.) 따라서, 해당 자동차의 모든 대여기록 중, 한번이라도 2022-10-16일에 걸리는 날이 있다면, '대여 중' 으로 표시해야한다. 

이를 위해 group by를 사용하고, 집계함수를 통해, 각 그룹별 연산을 수행할 수 있는 점을 이용하여 sum( 조건문) 으로 해당 날짜에 대여일정이 존재하는 튜플의 개수를 찾는다. 하나라도 해당 날짜에 대여가 예정되어있으면 '대여중' 으로 표기하고, 그렇지 않다면 '대여 가능' 으로 표기한다.

 

+@

새로운 컬럼을 추가하여 정답에 리턴하고 싶다면, select절에 해당 값을 추가하면 된다!

 

 

팁: 여러 테이블의 데이터를 전부 활용해야한다면, 각 테이블을 일단 전부 join한 뒤에, 조건절로 선별하자.

ex) A와 B가 FK로 연결 되어있고, B와 C가 FK로 연결되어있을 때,

A,B join하고 필요한 필드만 추출,  B,C만 join하고 필요한 필드만 추출한 뒤, 이 두 테이블을 join하는 등 멍청한 짓 말고,

그냥 A,B,C를 FK를 기준으로 join해 통합 테이블을 만들고 처리하자!

 

5) JOIN

두 테이블의 조인을 위해서는 기본키(PRIMARY KEY, PK)와외래키(FOREIGN KEY, FK)관계로 맺어져야 하고, 이를 일대다 관계라고 한다. 종류는 INNER JOIN, OUTER JOIN, CROSS JOIN, SELF JOIN가 있다. 그냥 join 만 사용시 'inner join' 이다.

조인을 쓰면 두 개의 테이블을 엮어서 원하는 데이터를 추출할 수 있다.

양쪽 테이블의 각 튜플에서 각각 원하는 값을 select 해서, 하나의 튜플로 만든다. 

 

아래의 2개 테이블의 이름이 A,B이고 각 컬럼의 이름이 왼쪽 부터 1,2,3 라고 해보자.

 

아래의 쿼리가 위와 같은 join을 표현한 것이다.

select AB.1, AB.2,AB.3
from A join B as AB
on [join 조건]    #A,B 테이블을 합치는 기준

만약 on~, 와 같은 조건이 없다면, a와 b의 모든 

Join의 조건문

join(이너 조인) 은 조건문이 없이도 사용 가능하다. 조건문이 없는 join은 어떤식으로 동작할까?

예를 들어 car 테이블의 PK car_id를 FK 로 가지는 car_history가 있고, car은 30개 의 튜플, car_history는 160개의 튜플을 가지고 있다 가정해보자.

select count(*) from car; #30개

select count(*) from car_history # 160개

 

 join 조건이 없을 경우

SELECT * 
from CAR_RENTAL_COMPANY_RENTAL_HISTORY as a join CAR_RENTAL_COMPANY_CAR as b
# join 조건 없음

join의 제약 조건이 없기에, 가능한 모든 튜플 조합이 나오게 되며 마치 카티션 곱처럼 160*30 개의 튜플이 결과로 나온다.

 

join 조건이 있을 경우

SELECT * 
from CAR_HISTORY as a join CAR as b
on a.car_id = b.car_id

join의 조건에 car_Id의 일치를 걸었기에, 같은 아이디를 가진 튜플만 합쳐져서 출력된다.( 디폴트 이너 조인 ) 

또한 중복되는 car_id는 어차피 같은 값을 가지고 있지만, 각각 다른 컬럼으로 나온다. (각각 CAR 테이블의 car_id, CAR_HISTORY 테이블의 car_id)

 

 

주의- join에서 alias

#에러x
(SELECT * a
from CAR_RENTAL_COMPANY_RENTAL_HISTORY as a join CAR_RENTAL_COMPANY_CAR as b) as c

#에러
SELECT *
from (CAR_RENTAL_COMPANY_RENTAL_HISTORY as a join CAR_RENTAL_COMPANY_CAR as b) as c

 

on VS where

 join~~ on : join 하기전에 조건을 통해 필터링한다.

join~~ where: join완료 후에 조건을 통해 필터링 한다.

 

JOIN 의 종류

• INNER JOIN(내부 조인)은 두 테이블을 조인할 때, 두 테이블에 모두 지정(select)한 열의 데이터가 있어야 한다.
• OUTER JOIN(외부 조인)은 두 테이블을 조인할 때, 1개의 테이블에만 데이터가 있어도 결과가 나온다. (한쪽 테이블이 select할 컬럼 값이 NULL이라도, 합쳐진다.) 주의: , mysql에서는 full outer join이 존재하지 않는다!
• CROSS JOIN(상호 조인)은 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인하는 기능이다.
• SELF JOIN(자체 조인)은 자신이 자신과 조인한다는 의미로, 1개의 테이블을 사용한다.

INNER JOIN(내부 조인)

두 테이블을 연결할 때 가장 많이 사용하는  것이 inner join 이다. 그냥 join == inner join 이다

SELECT <열 목록>
FROM <첫 번째 테이블>
    INNER JOIN <두 번째 테이블>
    ON <조인 조건>
[WHERE 검색 조건]

#INNER JOIN을 JOIN이라고만 써도 INNER JOIN으로 인식합니다.

OUTER JOIN(외부 조인)

내부 조인은 두 테이블에 모두 지정(select)한 열의 데이터가 있어야만 결과가 나오지만, 외부 조인은 한쪽에만 지정(select)한 열의 데이터가 있어도 결과가 나온다.

또한, join 조건 (on) 이 필수로 들어가야한다( 아니면 문법 오류 발생 )

 

OUTER JOIN의 종류
LEFT OUTER JOIN: 왼쪽 테이블의 모든 값이 출력되는 조인
RIGHT OUTER JOIN: 오른쪽 테이블의 모든 값이 출력되는 조인
FULL OUTER JOIN: 왼쪽 또는 오른쪽 테이블의 모든 값이 출력되는 조인 ( mysql은 지원 x)

my sql은 left, right join 만 지원한다. 따라서 outer join은 left or right join 1가지만 기억하면 된다.

보통 테이블 간 join을 수행할 때, 어떤 테이블의 튜플은 모두 살리고 싶을 때 사용한다.

따라서 보통의 inner join 결과에, left 부분은 있지만, right는 없는 튜플도 추가된다.

 

예시로 left join의 예시를 살펴보자.

LEFT JOIN

첫 번째 테이블을 기준으로 두 번째 테이블을 조합한다.
ON절의 조건을 만족하지 못하는 경우 첫 번째 테이블의 필드값은 모두 가져오지만 해당 튜플의 두 번째 테이블 필드값은 모두 NULL이다.

 

아래의 예시는 DEPT는 DEPTNO라는 PK를 가지고, EMP는 DEPTNO를 FK로 가지고 있을 때, LEFT JOIN의 예시이다.

전제 조건으로, DEPT는 당연히 모든 DEPTNO를 가지고 있지만, EMP는 모든 DEPTNO를 가지고 있지 않다고 하겠다.( 아래 예시에선 DEPTNO 40이 EMP에 존재하지 않음을 가정)

 

SELECT DISTINCT E.DEPTNO, D.DEPTNO  #left = DEPT
  FROM EMP E
  LEFT JOIN DEPT D
    ON E.DEPTNO = D.DEPTNO;

# DEPT에는 40번이 DEPTNO가 존재하지만 EMP에는 존재하지 않아 NULL이 발생한다.
SELECT DISTINCT E.DEPTNO, D.DEPTNO #left = EMP
  FROM DEPT D
  LEFT JOIN EMP E
    ON E.DEPTNO = D.DEPTNO;

 

EMP가 left일 때, left join 시 결과

emp는 40번이란 DEPTNO를 가지고 있지 않다. 따라서 DEPT테이블에서 DEPTNO가 40인 튜플은 INNER JOIN에서는 select 되지 않는다. 하지만 EMP가 LEFT인 LEFT OUTER JOIN이기에,결과 값은 EMP의 모든 튜플을 포함해야한다. 

따라서, 해당 필드(DEPTNO) 필드만 NULL로 가져온다. 위의 예시는 DEPTNO만 가져와서 전부 NULL 로 가지고 온 것 처럼 보이지만, 여러 필드를 가져온다면 아래와 같을 것이다.

 

EMP_ID  DEPT_ID  DEPTNO(EMP)   DEPTNO(DEPT)

 1              1                 1                        1

 2              2                 NULL                 2

 

 OUTER JOIN 자세히 알아보기

 

CROSS JOIN(상호 조인)

한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능이다. 상호 조인 결과의 전체 행 개수는 두 테이블의 각 행의 개수를 곱한  값이다.
카티션 곱(CARTESIAN PRODUCT)라고도 한다.

SELECT *
FROM <첫 번째 테이블>
    CROSS JOIN <두 번째 테이블>

SELF JOIN(자체 조인)

자체 조인은 자기 자신과 조인하므로 1개의 테이블을 사용한다. 다만, 같은 테이블을 다수 사용하기에, 각 테이블에 별칭(Alias)를 붙여줘야 한다.

SELECT <열 목록>
FROM <테이블> 별칭A
    INNER JOIN <테이블> 별칭B
[WHERE 검색 조건]

 

6) 조건문

조건에 따라서 출력을 다르게 하고 싶을 때, 조건문을 써야한다.

CASE

switch문과 유사하게 사용한다.

CASE
...WHEN과 THEN의 반복
ELSE
END AS 칼럼명

select, where, order by 에서 사용 가능하고, 보통 select에서 많이 사용한다. 

ELSE 부분을 생략하면 결과값이 NULL이 나오니 주의하자.

 

예시

 

SELECT ORDER_ID, PRODUCT_ID,OUT_DATE,
    CASE 
    WHEN OUT_DATE IS NULL 
    THEN '출고미정' 
    WHEN OUT_DATE<='2022-05-01' 
    THEN '출고완료' 
    ELSE '출고대기'
    END AS 출고여부
FROM FOOD_ORDER
ORDER BY ORDER_ID

IF

SELECT IF(조건, 참 값, 거짓 값)

IFNULL

// 해당 column의 값 중 NULL이 있으면, 대체값 입력
SELECT IFNULL(COLUMN, 대체값)

BETWEEN

column BETWEEN  최소 AND 최대 형식으로 사용하면 된다.

SELECT ...
FROM ...
WHERE ID BETWEEN 10 AND 40

 

 

+@: null 처리

sql에서, 어떤 필드가 null인지 검증하는 것에서  ( A = NULL),  (A!=NULL) 와 같은 접근법은 불가하다.

아래와 같이, IS NULL, IS NOT NULL 문을 사용해야한다.

컬럼명 IS NULL

컬럼명 IS NOT NULL

 

추가적으로, 집계함수는 NULL을 제외한다. 이를 응용하여 아래 문제를 해결할 수 있다.

 

Q) 각 팀별로, 점수의 평균을 구하되, 0점은 제외한 평균 구하기

-> Case 문으로 score가 0일시, null로 대체하면 집계함수에서 제외된다!

SELECT AVG(SCORE) SCORE_AVG1--전체 평균 집계
	,AVG(CASE WHEN SCORE = 0 THEN NULL ELSE SCORE END) SCORE_AVG2--0을 제외한 평균 집계
FROM TEMP_TABLE

 

7) 숫자 연산

7-1) ABS (절대값)

숫자에 절대값을 가져오는 함수

ABS(숫자)

 

7-2) CEILING (소수점 올림)

값보다 큰 정수 중 가장 작은 수를 가져오는 함수

CEILING(숫자)

 

7-3) FLOOR (소수점 버림)

값보다 작은 정수 중 가장 큰 수를 가져오는 함수 (소수점 버림)

FLOOR(숫자)

 

7-4) ROUND (소수점 반올림)

숫자를 소수점 이하 자릿수를 남기고 반올림하는 함수

ROUND(숫자, 자릿수)

• 자릿수를 생략하면 소숫점이 5이상일 때 반올림
• 자릿수가 0으로 지정하면 소숫점에서 반올림
• 자릿수를 양수로 지정하면 해당하는 자릿수에서 반올림
• 자릿수가 음수인 경우 소숫점 이하를 버리고 정수에 뒤에서부터 지정된 자릿수까지 반올림

 

7-5) TRUNCATE (소수점 자릿수 버림)

숫자를 소수점 이하 자릿수를 남기고 버림하는 함수

TRUNCATE(숫자, 자릿수)

• 자릿수가 양수인 경우 해당 자릿수에서 소수점 버림.
• 자릿수가 음수인 경우 소숫점 이하를 버리고 정수에 뒤에서부터 지정된 자릿수까지 0으로 처리

 

7-6) POW (제곱)

숫자의 n승을 가져오는 함수

POW(숫자, n)

 

7-7) MOD (나머지)

분자를 분모로 나눈 나머지를 가져오는 함수 (% 연산자와 동일)

MOD(분자, 분모)

 

7-8) GREATEST, LEAST

주어진 수 중 제일 큰 수, 작은 수를 가져오는 함수

GREATEST(숫자1, 숫자2, 숫자3 ...)

LEAST(숫자1, 숫자2, 숫자3 ...)

 

8) 변수

SET @변수명 = ...; 형태로 변수를 선언하고 아래 query 문에서 @변수명으로 쓸 수 있다.

SET @MX = (SELECT MAX(PRICE) FROM FOOD_PRODUCT);

SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE
FROM FOOD_PRODUCT
WHERE PRICE=@MX

어떠한 특정 값을 변수에 할당할 수 있다. 만약 max 값을 통해 무엇인가 추가적으로 수행해야한다면, 굳이 subquery로 지저분하게 하지 않고, 변수를 지정해서 사용하는 것도 좋은 방법이다.

 

주의
sql에서 '=' 은 비교연산자이다. 따라서 대입 연산자를 수행하기 위해선 ':=' 을 사용하자!

 

 

9) DATE

Date 단위
초-분-시-일-주-월-분기-연
SECOND-MINUTE-HOUR-DAY-WEEK-MONTH-QUARTER-YEAR

# 각 연/월/일을 가져오기
YEAR(날짜), MONTH(날짜), DAY(날짜), DATE(날짜)

# 형 변환
1) DATE_FORMAT(날짜, 원하는 format) ex. %Y-%m-%d
2) TO_DATE(날짜, 원하는 format)

ex) select date_format(start_date,'%Y-%m')// date 타입의 start_date 컬럼에서 년, 월만 추출
from xxx.....
//결과 예시: 2022-08

ex) select date_format(start_date,'%YXX%m')// date 타입의 start_date 컬럼에서 년, 월만 추출
from xxx.....
//결과 예시: 2022XX08

# format
%Y = 2022, %y=22, %M=October, %m=10 ...... # %y: 해당 년도의 뒤 2자리만

# 더하기/빼기
// (날짜, INTERVAL_더할/뺄 숫자_단위)
DATE_ADD(DATE, INTERVAL 1 HOUR)
DATE_SUB(DATE, INTERVAL 1 HOUR)

# 차이 구하기
DATEDIFF(날짜1, 날짜2) => '일(DAY)' 기준 //날짜2 -날짜1
TIMESTAMPDIFF(단위, 날짜1, 날짜2) => 단위 기준
#단위: HOUR, SECOND, MONTH, YEAR,...

 

실전 예시

#A테이블에 start_date라는 Date 타입을 통해, 10월인 것만 select
select *
from A
where date_format(start_date,'%m')= 10

 

 

Date 주의 사항

start_date, end_date가 Date 타입일 때, 아래는 에러가 나는 코드이다.

#start_date >= '2022-11-01' AND end_date <= '2022-11-30'
where start_date < '2022-11-31' and end_date> '2022-11-00')

날짜에 관한 타입이기에, 11월 31일, 11월 0일 과 같은 날짜는 "Date" 타입으로 형변환 되지 않는다. 따라서 타입 에러 발생한다. ( Date 타입 < Date가 아닌 타입이 되버리므로 )

 

+@

( <=, >= 와 같은 연산자는 '=' 가 오른쪽에 와야한다!

 

 

format 사용시 주의점

%Y	년도 - Year(4자리 표기)
%y	년도 (뒤에 2자리 표기)
%M	월 - 월 이름(January ~ December)
%m	월 - 월 숫자(00 ~ 12)
%d	일(00 ~ 31)

 

%H	시간 24시간(00 ~ 23)
%h	시간 12시간(00 ~ 12)
%i	분 (00 ~ 59)
%s	초 (00 ~ 59)

 

여기서 %h == 12시간까지밖에 표현 못한다. 따라서 24시간 인지, 12시간인지 따라서 올바르게 사용하지 않으면, 엄청난 문제가 생기게 된다..

 

10) 순위 매기기

코딩 테스트 SQL 문제에 자주 등장하는 문제이다.

SELECT A, B, C,RANK() OVER (ORDER BY salary DESC) "등수"
FROM TABLE;

RANK 관련 함수 3가지 (중복 순위를 처리하는게 다르다.)
1) RANK : 1-2-3-3-3-6    #중복 순위가 존재하고, 각 순위는 나보다 위의 인원 수
2) DENSE_RANK : 1-2-3-3-3-4    #중복 순위가 존재하지만, 실제 순위 = 숫자의 순서
3) ROW_NUMBER : 1-2-3-4-5-6    #중복 순위가 존재하지 않는다

랭크함수 OVER(ORDER BY [컬럼] [정렬기준]  ) 와 같이 사용 가능하다.

 

partition by

그룹 내 순서를 만들 때 사용한다.

 

  RANK() OVER(PARTITION BY product_id
                          ORDER BY price DESC)

 

 

랭크 함수 응용

https://school.programmers.co.kr/learn/courses/30/lessons/131124

프로그래머스

 

rank 함수의 특성 ( 같은 우선순위는, 공동 순위 표시 ) 를 활용 해서, 리뷰의 개수가 가장 많은 "모든 사용자" 를 찾았다.

 

 

rank over로 각 멤버의 id, 순위(리뷰 개수) 로 랭킹을 매기는데, 우리가 필요한건 "1위" 들이다.

따라서, (memberid,1) in (~~~) 를 통해, 모든 공동 1위의 id를 찾을 수 있다.

SELECT M.MEMBER_NAME, R.REVIEW_TEXT, DATE_FORMAT(R.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE M
JOIN REST_REVIEW R ON M.MEMBER_ID = R.MEMBER_ID
WHERE (R.MEMBER_ID,1) in (
    SELECT MEMBER_ID,rank() over(order by COUNT(MEMBER_ID) DESC)
                    FROM REST_REVIEW
                    GROUP BY MEMBER_ID
                     )
ORDER BY 3,2;

 

 

 

11) 중복 제거

unique한 값을 얻고 싶을 때 사용한다.

DISTINCT는 개별 column에만 적용되는 것이 아니라, DISTINCT 뒤에 나오는 모든 column에 적용되는 것이다.
따라서 SELECT A, DISTINCT(A)와 같은 사용은 문법 오류이다.

SELECT DISTINCT 컬럼명1, 컬럼명 2,...
FROM ......

 

12) 최대 개수 제한

LIMIT을 통해 최대 개수를 제한할 수 있다. 이를 Rank와 결합하여 상위 5명을 구하는 등의 응용으로 많이 나온다.

SELECT ... FROM ...
LIMIT 가져올 개수

13) 문자열 합치기

CONCAT을 통해 여러개의 Column을 1개로 합칠 수 있다. Concat은 "문자열 합치기" 이지만, '문자' 타입으로 타입 캐스팅이 가능한 int 등의 타입도 합칠 수 있다.

이를 통해 두개의 Column을 1개로 합치고, Disntinct를 걸어 순서쌍(A,B)가 중복되지 않게 하는 등의 응용을 할 수 있다, .

ex) (x,y) 좌표가 중복되지 않게 구하기, (A_id, B_id) 순서쌍 중복되지 않게 구하기

SELECT DISTINCT CONCAT(A, B) AS ONE

 

 

14) 쿼리문 합치기

UNION 연산자를 통해 같은 구조의 테이블 2개를 합집합 할 수 있다. 

Join과 헷갈릴 수 있지만, UNION은 튜플 기준 합집합이기에, UNION할 두 테이블의 컬럼 수, 컬럼의 종류가 완벽히 같아야한다.

 

TABLE1

ID	NAME
1	USER1
2	USER2

TABLE2

ID	NAME
2	USER1
3	USER3

다음과 같은 테이블이 존재 할 때 각각의 테이블을 합치기 위해 UNION ALL을 사용 할 수 있습니다.

 SELECT ID, NAME FROM TABLE1
 UNION ALL
 SELECT ID, NAME FROM TABLE2

ID	NAME
1	USER1
2	USER2
2	USER1
3	USER3

UNION ALL은 쿼리에서 나온 데이터를 하나로 합쳐주고, 중복되는 데이터도 모두 출력된다.

하지만 UNION은 중복되는 값은 빼고 출력된다.

SELECT ID, NAME FROM TABLE1 
UNION
SELECT ID, NAME FROM TABLE2

 

ID	NAME
1	USER1
2	USER2
3	USER3

 

15) 재귀

아래와 같이, sql 에서도 WITH RECURSIVE 테이블 AS ~를 통해 재귀를 사용할 수 있다.

 

예시

WITH RECURSIVE EMPTY_HOUR_TABLE AS (
	#====non-recursive 문장( 첫번쨰 루프에서만 수행 된다 )
    SELECT 0 AS HOUR, 0 AS COUNT # 초기값을 설정
    UNION # 위 쿼리와 아래 쿼리의 값을 합친다.
    
    #==== Recursive 문장( 읽어 올 떄마다, 튜플의 위치가 기억되어 다음번에도 반영된다 )
    SELECT HOUR + 1, COUNT #Hour 값을 1씩 증가 시킨다.
    FROM EMPTY_HOUR_TABLE 
    WHERE HOUR < 23 #반복을 멈추는 용도 ( Recursive 부분에는 반드시 where을 통한 종료 조건이 포함되야한다. )
)

 

with recursive rc as (
  select 1 as h -- 재귀 초깃값
  union all
  select h + 1 -- 재귀
  from rc 
  where h < 5 -- 재귀 정지 조건
)

select * from rc

결과: h=1,2,3,4,5

 

 

재귀 문제 모음

 

https://school.programmers.co.kr/learn/courses/30/lessons/59413

프로그래머스

 

 

https://school.programmers.co.kr/learn/courses/30/lessons/59413

프로그래머스