[패캠] SQL: 조건에 맞는 데이터 가져오기

회사에서의 대부분의 데이터 요청

 

"10번 이상 구매한 VIP 고객 리스트를 뽑아주세요."

"매출 5천만원 이상의 상품 리스트를 뽑아주세요."

 

와 같은 조건이 붙는다.

 

WHERE

 

가져올 데이터의 조건을 지정하는 키워드

WHERE 뒤의 조건이 참인 row(행)만 선택

 

SELECT 컬럼명
FROM 테이블명
WHERE 조건식

 

 

원하는 데이터의 조건이 True(참)이 되도록 조건식을 만든다.

 

MySQL에서는 True(참) 값을 1로 표현하고,

False(거짓) 값은 0으로 표현한다.

 

 

연산자 종류

비교 연산자

=, >, <

 

예제

요청: '피카츄'의 number를 찾아주세요.

 

SELECT number
FROM mypokemon
WHERE name = 'pikachu';

 

 

요청: 속도가 50보다 큰 포켓몬의 name을 찾아주세요.

 

SELECT name
FROM mypokemon
WHERE speed > 50;

 

 

요청: 전기 타입이 아닌 포켓몬의 name을 찾아주세요.

 

SELECT name
FROM mypokemon
WHERE type != 'electric';

 

논리 연산자

AND: 둘 다 참일 때 True

OR: 둘 중에 하나라도 참이면 True

NOT A: A가 아니면 True

 

예제

 

요청: 속도가 100 이하인 전기 타입 포켓몬의 name을 찾아주세요.

 

SELECT name
FROM mypokemon
WHERE speed <= 100
AND type = 'electric';

 

 

요청: bug 타입이거나 normal 타입인 포켓몬의 name을 찾아주세요.

 

SELECT name
FROM mypokemon
WHERE type = 'bug'
OR type = 'normal';

 

 

요청: 속도가 100 이하이고 bug 타입이 아닌 포켓몬의 name을 찾아주세요.

 

SELECT name
FROM mypokemon
WHERE speed <= 100
AND NOT(type = 'bug'); // AND type != 'bug' 와 동일

 

 

기타 연산자

BETWEEN

특정 범위의 데이터를 선택할 때

WHERE 절에서 사용

 

컬럼명 BETWEEN A AND B

A <= 컬럼값 <= B

 

SELECT 컬럼명
FROM 테이블명
WHERE 컬럼명 BETWEEN 조건1 AND 조건2;

 

 

예제

 

요청: 속도가 50과 100 사이인 포켓몬의 이름을 찾아주세요.

 

SELECT name
FROM mypokemon
WHERE speed BETWEEN 50 AND 100;

 

 

IN

목록에 포함되는 데이터를 선택할 때

WHERE 절에서 사용

 

컬럼명 IN (A, B, ..., C)

컬럼명 IN ('A', 'B')는 컬럼명 = 'A' OR 컬럼명 = 'B'와 동일

 

SELECT 컬럼명
FROM 테이블명
WHERE 컬럼명 IN (조건1, 조건2, ...);

 

예제

요청: bug 타입거나 normal 타입인 포켓몬의 이름을 찾아주세요.

(OR 예제와 동일)

SELECT name
FROM mypokemon
WHERE type IN ('bug', 'normal'); // WHERE type = 'bug' OR type = 'normal'과 동일

 

 

LIKE

 

특정 문자열이 포함된 데이터를 선택

WHRER절에서 사용

 

컬럼명 LIKE 검색할문자열

검색할문자열에 와일드카드를 사용하여 검색 조건을 구체적으로 지정 가능

 

와일드카드

%: 0개 이상의 문자 (~뻬고 여러 개)

_: 1개의 문자 (문자열 1자리)

 

'_e' = 'e'로 끝나고 앞에 1개의 문자가 있는 문자열

ae, ee, ce

 

 

SELECT 컬럼명
FROM 테이블명
WHERE 컬럼명 LIKE 검색할문자열;

 

 

예제

요청: 이름이 'chu'로 끝나는 포켓몬의 name을 찾아주세요.

 

SELECT name
FROM mypokemon
WHERE name LIKE '%chu';

 

 

요청: 이름에 'a'가 포함되는 포켓몬의 name을 찾아주세요.

 

SELECT name
FROM mypokemon
WHERE name LIKE '%a%';

 

 

NULL

 

데이터 값이 존재하지 않음을 표현

알수 없는 값을 의미

0(숫자)이나 공백(문자)과는 다름

 

 

IS NULL

 

데이터가 NULL인지 여부 확인

WHERE 절에 사용

 

컬럼명 IS NULL

 

NULL이 아닌 데이터 검색 시:

컬럼명 IS NOT NULL

 

컬럼명 = NULL 이나 컬럼명 != NULL 이란 표현은 사용하지 않음

 

SELECT 컬럼명
FROM 테이블명
WHERE 컬럼명 IS NULL;

 

 

예제

 

요청: number가 null인 포켓몬의 name을 찾아주세요.

 

SELECT name 
FROM mypokemon
WHERE number IS NULL;

 

 

요청: type이 null이 아닌 포켓몬의 name을 찾아주세요.

 

SELECT name
FROM mypokemon
WHERE type IS NOT NULL;

 

 

실습

 

미션1) 이브이의 타입을 가져와 주세요.

 

SELECT type
FROM mypokemon
WHERE name = 'eevee';

 

 

미션2) 캐터피의 공격력과 방어력을 가져와 주세요.

 

SELECT attack, defense
FROM mypokemon
WHERE name = 'caterpie';

 

 

미션3) 몸무게가 6kg보다 큰 포켓몬들의 모든 데이터를 가져와 주세요.

 

SELECT *
FROM mypokemon
WHERE weight > 6;

 

 

미션4) 키가 0.5m보다 크고, 몸무게가 6kg보다 크거나 같은 포켓몬들의 name을 가져와 주세요.

 

SELECT name
FROM mypokemon
WHERE height > 0.5
AND weight >= 6;

 

 

 

미션5) 포켓몬 테이블에서 공격력이 50 미만이거나, 방어력이 50 미만인 포켓몬들의 name을 'weak_pokemon'이라는 별명으로 가져와 주세요.

 

SELECT name AS weak_pokemon
FROM mypokemon
WHERE attack < 50
OR defense < 50;

 

 

미션6) 'normal' 타입이 아닌 포켓몬들의 데이터를 전부 가져와 주세요.

 

SELECT * 
FROM mypokemon
WHERE type != 'normal'; // 또는 NOT(type = 'normal')

 

 

 

미션7) 타입이 (normal, fire, water, grass) 중에 하나인 포켓몬들의 name과 type을 가져와 주세요.

 

SELECT name, type
FROM mypokemon
WHERE type IN ('normal', 'fire', 'water', 'grass');

 

 

 

미션8) 공격력이 40과 60 사이인 포켓몬들의 name과 attack을 가져와 주세요.

 

SELECT name, attack
FROM mypokemon
WHERE attack BETWEEN 40 AND 60;

 

 

 

미션9) 이름에 'e'가 포함되는 포켓몬들의 name을 가져와 주세요.

 

SELECT name
FROM mypokemon
WHERE name LIKE '%e%';

 

 

 

미션10) 이름에 'i'가 포함되고, 속도가 50 이하인 포켓몬 데이터를 전부 가져와 주세요.

 

SELECT *
FROM mypokemon
WHERE name LIKE '%i%'
AND speed <= 50;

 

 

 

미션11) 이름이 'chu'로 끝나는 포켓몬들의 name, height, weight를 가져와 주세요.

 

SELECT name, height, weight
FROM mypokemon
WHERE name LIKE '%chu';

 

 

미션12) 이름이 'e'로 끝나고, 방어력이 50 미만인 포켓몬들의 name, defense를 가져와 주세요.

 

SELECT name, defense
FROM mypokemon
WHERE name LIKE '%e'
AND defense < 50;

 

 

 

미션13) 공격력과 방어력의 차이가 10 이상인 포켓몬들의 name, attack, defence를 가져와 주세요.

 

SELECT name, attack, defense
FROM mypokemon
WHERE attack - defense >= 10
OR defense - attack >= 10; // 공격력과 방어력 중 어느 게 더 높은지 모르기 때문에 둘 다 써주기

 

 

미션14) 능력치의 합이 150 이상인 포켓몬들의 name과 능력치의 합(attack + defense + speed)을 가져와 주세요.

이때, 능력치의 합은 'total'이라는 별명으로 가져와 주세요.

 

SELECT name, attack + defense + speed AS total
FROM mypokemon
WHERE total >= 150;

 

 

 

ORDER BY

가져온 데이터를 정렬

 

ORDER BY 컬럼명

기본 정렬은 오름차순(ASC - 생), 내림차순은 뒤에 DESC 붙여줘야

 

여러 개 컬럼 정렬

ORDER BY 컬럼명1, 컬럼명2, ... (생략 또는 DESC);

 

 

예제

 

포켓몬을 number 기준으로 내림차순 정렬해서 포켓몬의 number와 name을 가져와 주세요.

 

SELECT number, name
FROM mypokemon
ORDER BY number DESC;

 

 

포켓몬을 attack 기준으로 내림차순, defense 기준으로 오름차순 정렬해서 포켓몬의 number, name, attack, defense를 가져와 주세요.

 

SELECT number, name, attack, defense
FROM mypokemon
ORDER BY attack DESC, defense;

 

 

 

RANK

 

메서드여서 뒤에 소괄호를 붙여준다.

 

RANK() OVER (ORDER BY 컬럼명)

 

항상 SELECT절에서 ORDER BY와 함께 사용

정렬된 순서에 순위를 붙여서 새로운 컬럼을 보여준다.

 

SELECT 컬럼명, ..., RANK() OVER (ORDER BY 컬럼명)
FROM 테이블명
WHERE 조건식;

 

 

예제

총 3가지 출력

• name과 attack 출력
• attack 기준으로 내림차순 정렬한 순위를 attack_rank라는 별명으로 함께 출력

SELECT name, attack, RANK() OVER (ORDER BY attack DESC) AS attack_rank
FROM pokemon.mypokemon;

 

 

RANK()는 동일한 순위가 여러 개일 경우 다음 순위를 그 갯수만큼 건너뛰고 출력

예: 1, 2, 2, 4, 5

 

DENSE_RANK()는 동일한 순위가 여러 개여도 그 갯수를 건너뛰지 않고 출력

예: 1, 2, 2, 3, 4

 

ROW_NUMBER()는 동일한 순위가 있어도 모두 고유한 순위로 출력

예: 1, 2, 3, 4, 5

 

 

문자형 데이터 함수

LOCATE(찾을문자, 문자열)

문자열에서 문자가 위치한 인덱스 반환

예: LOCATE('A', 'ABC') = 1

 

찾을 문자가 여러 개라면 가장 먼저 찾은 문자의 위치를 가져옴

찾는 문자가 없을 경우 0을 가져옴

 

예제: part(숫자 번호), lyric에서 'i'가 등장하는 인덱스 번호 출력

SELECT part, LOCATE('i', lyric)
FROM bts_music_butter;

 

'i'가 없는 행의 경우 인덱스 번호 대신 0을 출력

 

SUBSTRING(문자열, 시작위치)

문자열에서 시작위치부터 반환

예: SUBSTRING('ABC', 2) = BC

 

시작 위치가 문자열 길이보다 크면 아무 것도 가져오지 않음

 

RIGHT(문자열, 갯수)

문자열 오른쪽에서 갯수만큼 반환

예: RIGHT('ABC', 1) = C

 

LEFT(문자열, 갯수)

문자열 왼쪽에서 갯수만큼 반환

예: LEFT('ABC', 1) = A

 

REPLACE(문자열, 기존문자열, 바꿀문자열)

문자열에서 기존 문자열을 새 문자열로 대체

예: REPLACE('ABC', 'A', 'Z') = ZBC

 

 

숫자형 데이터 함수

ABS(숫자): 숫자의 절댓값

예: ABS(-1.255) = 1.255

 

CEILING(숫자): 숫자를 정수로 올림

예: CEILING(4.2) = 5

 

FLOOR(숫자): 숫자를 정수로 내림

예: FLOOR(4.8) = 4

 

 

ROUND(숫자, 자릿수): 숫자를 자릿수까지 반올림

예: ROUND(3.426, 2) = 3.47

 

 

TRUNCATE(숫자, 자릿수): 숫자를 자릿수까지 절삭

예: TRUNCATE(3.426, 2) = 3.42

 

 

POWER(숫자1, 숫자2): 숫자1를 숫자2만큼 제곱

예: POWER(4, 2) = 4의 2제곱 = 16

 

 

MOD(숫자1, 숫자2): 숫자1을 숫자2로 나눈 나머지

예: MOD(10, 3) = 1

 

 

날짜형 데이터 함수

NOW()

현재 날짜와 시간 반환

 

CURRENT_DATE()

현재 날짜 반환

 

CURRENT_TIME()

현재 시간 반환

 

YEAR(날짜)

날짜의 연도 반환

 

MONTH(날짜)

날짜의 월 반환

 

DAYOFMONTH(날짜)

날짜의 일 반환

 

 

MONTHNAME(날짜)

날짜의 월 이름 반환 (영어) 

 

DAYNAME(날짜)

날짜의 요일 이름 반환 (영어)

DAYOFWEEK(날짜)

날짜의 요일 숫자 반환

 

WEEK(날짜)

연도 기준으로 날짜가 몇 번째 주인지 반환

 

HOUR(시간)

시간의 시 반환

MINUTE(시간)

시간의 분 반환

 

SECOND(시간)

시간의 초 반환

 

 

DATE_FORMAT(날짜/시간, 형식)

날짜 또는 시간을 특정 형식으로 바꿔서 반환

예: DATE_FORMAT('1996-11-06 17:34:58', '%Y년 %m월 %d일 %H시 %i분 %s초') AS formatted_date;

 

결과:

formatted_date

1996년 11월 06일 17시 34분 58초

 

 

DATEDIFF(기준날짜, 다른날짜)

기준 날짜와 다른 날짜 사이의 차이 반환

SELECT name, DATEDIFF('2022-02-14', capture_date)
FROM mypokemon;

 

 

예제

포켓몬 이름에서 이름에 'o'가 포함된 포켓만의 모든 소문자 'o'를 대문자 'O'로 바꿔서 'bigO'라는 별명으로 가져와 주세요.

 

힌트: 문자열의 일부분만 대체할 때는 REPLACE 사용

 

SELECT REPLACE(name, 'o', 'O') AS bigO
FROM mypokemon
WHERE name LIKE '%o%';

 

 

GROUP BY

 

그룹별 데이터 집계 시 사용

GROUP BY가 쓰인 쿼리의 SELECT 절에는 반드시 GROUP BY의 대상 컬럼과 그룹 함수만 사용 가능

(아니면 에러 발생)

 

 

숫자로도 GROUP BY 가능

이때 숫자는 SELECT 절의 컬럼명 순서

예: GROUP BY 1, 2

 

SELECT [GROUP BY 대상 컬럼명], ..., [그룹 함수]
FROM 테이블명
WHERE 조건식
GROUP BY 컬럼명;

 

 

예제

포켓몬 테이블에서 type별로 그룹화한 뒤 포켓몬의 type 출력

 

SELECT type
FROM pokemon.mypokemon
GROUP BY type;

 

HAVING

 

그룹화된 데이터에 WHERE를 쓸 수 없는 이유:

WHERE는 행 하나하나의 단위로 가져오기 때문

 

GROUP BY된 데이터를 조건으로 가져올 때는

HAVING을 사용해야 한다.

 

HAVING 절에서는 그룹 함수 사용

 

그룹 함수

COUNT, SUM, AVG, MIN, MAX

SELECT 절과 HAVING 절에서 사용

 

COUNT(1)은 하나의 값을 1로 세어주는 표현으로 COUNT 함수에 자주 사용

=> 그룹화된 행들의 각 그룹별 갯수 출력

 

예: HAVING COUNT(1) = 2; 

그룹화된 행의 갯수가 2개인 그룹만 출력

GROUP BY가 없는 쿼리에서도 사용 가능 

이때는 전체 행에 그룹 함수가 적용됨

 

쿼리 실행 순서

FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY

 

 

예제

 

미션1) name의 길이가 5보다 큰 포켓몬들을, type을 기준으로 그룹화하고, weight의 평균이 20 이상인 그룹의 타입과 ,몸무게의 평균을 가져와 주세요.

결과는 weight의 평균을 기준으로 내림차순 정렬해 주세요.

 

SELECT type, AVG(weight)
FROM mypokemon
WHERE LENGTH(name) > 5
GROUP BY type
HAVING AVG(weight) >= 20
ORDER BY 2 DESC;

 

 

미션2) number가 200보다 작은 포켓몬들을 type 기준으로 그룹화하고, weight의 최댓값이 10보다 크거나 같고 최솟값은 2보다 크거나 같은 그룹의 type과, height의 최솟값, 최댓값을 가져와 주세요.

결과는 height의 최솟값의 내림차순으로 정렬하고, 만약 height 최솟값이 같다면 height의 최댓값의 내림차순으로 정렬해 주세요.

 

SELECT type, MIN(height), MAX(height)
FROM mypokemon
WHERE number < 200
GROUP BY type
HAVING MAX(weight) >= 10 
AND MIN(weight) >= 2
ORDER BY 2 DESC, 3 DESC;