[패스트캠퍼스] 2. SQL 기초1

SQL 기초

조건에 맞는 데이터 가져오기

WHERE

• WHERE [조건식] 형식으로 사용
• WHERE 절이 TRUE 가 되는 행만 선택

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

• MySQL에서는 TRUE 를 1로 표현, FALSE 는 0으로 표현

조건식에는 연산자가 사용된다.

대표적인 연산자

• 비교 연산자
  • =, >, <, !=
• 논리 연산자
  • NOT, AND, OR
• 기타 연산자
  • BETWEEN, IN ,IS

SELECT pokemon FROM mypokemon 
WHERE number >= 10
    AND speed <= 100
    AND NOT(type = 'bug') -- type != 'bug' 와 동일하다.
;

BETWEEN

• 특정 범위 내의 데이터를 선택할 때 사용하는 연산자
• [컬럼명] BETWEEN A AND B
• [컬럼명] BETWEEN A AND B 는 A <= [컬럼명] AND [컬럼명] <= B와 동일
  • 해당 컬럼 값이 A와 B사이에 포함되는 값을 가진 로우만 선택합니다.

SELECT pokemon FROM mypokemon 
WHERE speed BETWEEN 50 AND 100
;

SELECT pokemon FROM mypokemon 
WHERE speed >= 50 AND speed <= 100
;

-- 둘의 결과는 같습니다.

IN

• 목록 내 포함되는 데이터를 선택할 때 사용하는 연산자
• [컬럼명] IN (A, B, C ….. Z) 형식으로 사용
• 해당 컬럼의 값이 ‘()’ 내의 값에 포함되는 값을 가진 로우만 선택
• [컬럼명] IN (A, B) 는 [컬럼명] = A OR [] = B 와 동일
  • 목록에 넣을 값이 여러 개일 때, OR 연산자보다 가독성이 좋다.

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

SELECT pokemon 
FROM mypokemon 
WHERE type IN('bug', 'electric')
;

LIKE

• 특정 문자열이 포함된 데이터를 선택하는 연산자
• [컬럼명] LIKE [검색할 문자열] 형식으로 사용
• [검색할 문자열] 내에 와일드카드를 사용하여 검색 조건을 구체적으로 표현

SELECT [컬럼명]
FROM [테이블명]
WHERE [컬럼명] LIKE []
;

• 와일드카드
  • % : 0개 이상의 문자
  • _ : 1개의 문자

"_%" : 1개 이상의 문자
"__" : 2개의 문자

"e%" : e로 시작하는 문자열
"%e" : e로 끝나는 문자열
"%e%" : e를 포함하는 문자열

"_e" : e로 끝나고 e앞에 1개의 문자가 있는 문자열
"%_e" : e로 끝나고 e앞에 1개의 문자가 있는 문자열
"%_e_% : e를 포함하고 e앞, 뒤로 1개 이상의 문자가 있는 문자열 //e.g) aespa, eevee

-- chu로 끝나는 포켓몬 이름
SELECT name
FROM pokemon
WHERE name LIKE '%chu'
;
-- a가 포함되지만 a로 끝나지 않는 포켓몬 이름
SELECT name
FROM pokemon
WHERE name LIKE '%a_%'
;
/* %a%_도 가능
    (%와 _는 순서를 바꿔도 상관이 없다.) */

NULL

• 데이터 값이 존재하지 않는다라는 표현
• 0이나 공백이 아님(알 수 없는 값)
  • 공백도 하나의 데이터임
• [컬럼명] IS NULL, [컬럼명] IS NOT NULL 같은 형식으로 사용

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

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

실습

당신은 포켓몬고에 근무하는 신입 DBA 입니다.
다음과 같은 데이터가 필요하다고 개발자에게 요청이 왔습니다.
SQL을 작성해서 메일로 보내주세요…!

다음과 같은 SQL이 필요합니다.

1. 이브이 타입을 조회
2. 캐터피의 공격력 방어력 조회
3. 몸무게 6kg 보다 큰 포켓몬 조회
4. 키가 0.5m보다 크고, 몸무게가 6kg보다 크거나 같은 포켓몬들의 이름 조회
5. 공격력이 50 미만이거나, 방어력이 50 미만인 포케몬들의 이름은 weak_pokemon 이라는 별명으로 조회
6. 노말타입이 아닌 포켓몬 조회
7. 타임이 normal, fire, water, grass 중에 하나인 포켓몬 이름과 타입 조회
8. 공격력이 40과 60사이인 포켓몬들의 이름 조회
9. 이름에 e를 포함되는 포켓몬들의 이름 조회
10. 이름에 i포함되고, 속도가 50 이하인 포켓몬 조회
11. 이름이 chu로 끝나는 포켓몬들의 이름, 키, 몸무게 조회
12. 이름이 e로 끝나고, 방어력이 50 미만인 포켓몬들의 이름, 방어력 조회
13. 공격력과 방어력의 차이가 10 이상인 포켓몬들의 이름, 공격력, 방어력 조회
14. 능력치의 합이 150이상인 포켓몬의 이름과 능력치의 함을 조회
      능력치의 합은 total이라는 별명으로
    능력치의 합은 공격력, 방어력, 속도의 합을 의미

TABLE

SQL

/*
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보다 크거나 같은 포켓몬들의 이름 조회
*/
SELECT name
FROM mypokemon
WHERE height > 0.5 
 AND weight > 6
;

/*
5. 공격력이 50 미만이거나, 방어력이 50 미만인 포케몬들의 이름은 weak_pokemon 이라는 별명으로 조회
*/
SELECT name as weak_pokemon
FROM mypokemon
WHERE attack < 50
 OR defense < 50
;

/*
6. 노말타입이 아닌 포켓몬 조회
*/
SELECT *
FROM mypokemon
WHERE type != 'normal'
;

SELECT *
FROM mypokemon
WHERE NOT(type = 'normal')
;

/*
7. 타임이 normal, fire, water, grass 중에 하나인 포켓몬 이름과 타입 조회
*/
SELECT name, type
FROM mypokemon
WHERE type IN('normal', 'fire', 'water', 'grass')
;

/*
8. 공격력이 40과 60사이인 포켓몬들의 이름 조회
*/
SELECT name
FROM mypokemon
WHERE attack >= 40
 AND attack <= 60
;

SELECT name
FROM mypokemon
WHERE attack BETWEEN 40 AND 60
;

/*
9. 이름에 e를 포함되는 포켓몬들의 이름 조회
*/
SELECT name
FROM mypokemon
WHERE name LIKE '%e%'
;

/*
10. 이름에 i포함되고, 속도가 50 이하인 포켓몬 조회
*/
SELECT *
FROM mypokemon
WHERE name LIKE '%i%'
 AND speed <= 50
;

/*
11. 이름이 chu로 끝나는 포켓몬들의 이름, 키, 몸무게 조회
*/
SELECT name, height, weight
FROM mypokemon
WHERE name LIKE '%chu'
;

/*
12. 이름이 e로 끝나고, 방어력이 50 미만인 포켓몬들의 이름, 방어력 조회
*/
SELECT name, defense
FROM mypokemon
WHERE name LIKE '%e'
 AND defense < 50
;

/*
13. 공격력과 방어력의 차이가 10 이상인 포켓몬들의 이름, 공격력, 방어력 조회
*/
SELECT name, attack, defense
FROM mypokemon
WHERE attack
 AND abs(attack - defense) >= 10
;

/*
14. 능력치의 합이 150이상인 포켓몬의 이름과 능력치의 합을 조회
      능력치의 합은 total이라는 별명으로
    능력치의 합은 공격력, 방어력, 속도의 합을 의미
*/
SELECT name, attack + defense + speed as total
FROM mypokemon
WHERE attack + defense + speed >= 150
;

SELECT name, attack + defense + speed AS total 
FROM mypokemon
having total >= 100
;

원하는 데이터 만들기

ORDER BY

• ORDER BY [컬럼명] 형식으로 사용
• 입력한 [컬럼명] 기준으로 모든 행을 정렬
• 기본 정렬은 오름차순(ASC)
  • 오름차순(ASC)
    • 1, 2, 3, 4, 5, ,,,, 8, 9, 10
  • 내림차순(DESC)
    • 10, 9, 8, 7, ,,,, 3, 2, 1
• 다중 컬럼으로 정렬 가능
  • ORDER BY [컬럼명1], [컬럼명2]
    1. [컬럼명1] 기준으로 정렬
    2. [컬럼명1] 기준 동일한 조건 발생
    3. [컬럼명2] 기준으로 정렬
• 컬럼 번호로 정렬 가능
  • SELECT 절 뒤의 [컬럼명] 오름차순으로 번호가 된다.
    • SELECT name, age, team_name 일 경우
    • name = 1, age = 2, team_name = 3 입니다.

SELECT [컬럼명]
FROM [테이블명]
WHERE [조건식]
ORDER BY [컬럼명] DESC
;

SELECT *
FROM mypokemon
WHERE name LIKE '%a%'
ORDER BY attack DESC, defense
;

-- 컬럼 번호로 정렬
SELECT name, age, team_name
FROM mypokemon
ORDER BY 2 DESEC, 1
;

728x90

RANK

• RANK() OVER (ORDER BY [컬럼명]) 형식으로 사용
• 항상 ORDER BY 와 함께 사용
• SELECT 절에 사용하며, 정렬된 순서에 순위를 붙인 새로운 컬럼을 보여줍니다.
  • 실제 테이블의 데이터에는 영향이 없습니다.

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

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

RANK, DENSE_RANK, ROW_NUMBER

SELECT name, attack, 
    RANK() OVER (ORDER BY attack DESC) AS rank_rank,
    DENSE_RANK() OVER (ORDER BY attack DESC) AS rank_dense_rank,
    ROW_NUMBER() OVER (ORDER BY attack DESC) AS rank_row_number
FROM mypokemon
;

• RANK
  • 공동 순위가 있으면 다음 순위로 건너 뜀
    • e.g) 2등이 2명이면 다음 순위는 4등
• DENSE_RANK
  • 공동 순위가 있으면 다음 순위를 뛰어 넘지 않음
    • e.g) 2등이 2명이면 다음 순위는 3등
• ROW_NUMBER
  • 공동 순위를 무시
    • e.g) 2등이 2명이 될수 없음

문자형 데이터 함수

현업에서 자주 사용하는 함수들

• LOCATE
  • LOCATE('A', 'ABC')
  • ABC에서 A를 가장 먼저 찾은 위치 반환
  • 문자가 없으면 0 반환
    • e.g) SELECT id, LOCATE('a', name) FROM MEMBER;
• SUBSTRING
  • SUBSTRING('ABC', 2)
  • ABC에서 2번째 문자부터 반환(1부터 시작)
  • 만약 입력한 숫자가 문자열의 길이보다 크다면? 반환값은 없음
    • e.g) SELECT id, SUBSTRING(name, 3) FROM MEMBER;
• RIGHT
  • RIGHT('ABC', 1)
  • ABC에서 오른쪽 1번째 문자까지 반환
    • e.g) SELECT id, RIGHT(name, 1) FROM MEMBER;
• LEFT
  • LEFT('ABC', 1)
  • ABC에서 왼쪽에서 1번째 문자까지 반환
    • e.g) SELECT id, LEFT(name, 1) FROM MEMBER;
• UPPER
  • UPPER('abc')
  • 대문자로 변환
    • e.g) SELECT id, UPPER(name) FROM MEMBER;
• LOWER
  • LOWER('ABC')
  • 소문자로 변환
    • e.g) SELECT id, LOWER(name) FROM MEMBER;
• LENGTH
  • LENGTH('ABC')
  • 글자수 반환
    • e.g) SELECT id, LENGTH(name) FROM MEMBER;
• CONCAT
  • CONCAT('AB', 'C')
  • AB와 C 문자열을 합쳐서 반환
    • e.g) SELECT id, CONCAT(LEFT(name, 3), RIGHT(name, 1)) AS nickname FROM MEMBER;
• REPLACE
  • REPLACE('ABC', 'A', 'Z')
  • ABC의 A를 Z로 바꿔서 반환
    • e.g) SELECT id, REPLACE(name, ' ', '_') FROM MEMBER;

숫자형 데이터 함수

현업에서 자주 사용하는 함수들

• ABS
  • ABS(숫자)
  • 절대값 반환
    • e.g) SELECT name, ABS(point) FROM MEMBER;
• CEILING
  • CEILING(숫자)
  • 올림해서 반환
    • e.g) SELECT name, CELING(point) FROM MEMBER;
• FLOOR
  • FLOOR(숫자)
  • 내림해서 반환
    • e.g) SELECT name, FLOOR(point) FROM MEMBER;
• ROUND
  • ROUND(숫자, **자릿수**)
  • 소수점 자릿수 까지 반올림해서 반환
    • e.g) SELECT name, ROUND(point, 2) FROM MEMBER;
• TRUNCATE
  • TRUNCATE(숫자, **자릿수**)
  • 소수점 자릿수 까지 버림해서 반환
    • e.g) SELECT name, TRUNCATE(point, 3) FROM MEMBER;
• POWER
  • POWER(숫자A, 숫자B)
  • 숫자A의 숫자B 제곱 반환
    • e.g) SELECT name, POWER(point, bonus) FROM MEMBER;
• MOD
  • MOD(숫자A, 숫자B)
  • 숫자A를 숫자B로 나눈 나머지 반환
    • e.g) SELECT name, ABS(point, age) FROM MEMBER;

날짜형 데이터 함수

현업에서 자주 사용하는 함수들

• NOW
  • NOW()
  • 현재 날짜와 시간 반환
• CURRENT_DATE
  • CURRENT_DATE()
  • 현재 날짜 반환
• CURRENT_TIME
  • CURRENT_TIME()
  • 현재 시간 반환

SELECT NOW(), CURRENT_DATE(), CURRENT_TIME();
-- yyyy-MM-dd hh:mm:ss, yyyy-MM-dd, hh:mm:ss
-- 2022-12-17 20:18:10, 2022-12-17, 20:18:10

• YEAR
  • YEAR(날짜)
  • 날짜의 연도 반환
• MONTH
  • MONTH(날짜)
  • 날짜의 월 반환
• MONTHNAME
  • MONTHNAME(날짜)
  • 날짜의 월을 영어(문자열)로 반환

SELECT YEAR(NOW()),      -- 2022
    MONTH(NOW()),          -- 12
    MONTHNAME(NOW())      -- December
    ;

• DAYNAME
  • DAYNAME(날짜)
  • 날짜의 요일을 영어(문자열)로 반환
• DAYOFMONTH
  • DAYOFMONTH(날짜)
  • 날짜의 일을 숫자로 반환
• DAYOFWEEK
  • DAYOFWEEK(날짜)
  • 날짜의 요일을 숫자로 반환
  • 일(1), 월(2), 화(3), 수(4), 목(5), 금(6), 토(7)
• WEEK
  • WEEK(날짜)
  • 날짜가 해당 연도에 몇 번째 주(숫자)인지 반환

SELECT DAYNAME(NOW()),     -- Saturday
    DAYOFMONTH(NOW()),     -- 17
    DAYOFWEEK(NOW()),          -- 7
    WEEK(NOW())                -- 50
    ;

• HOUR
  • HOUR(시간)
  • 시간의 시 반환
• MINUTE
  • MINUTE(시간)
  • 시간의 분 반환
• SECOND
  • SECOND(시간)
  • 시간의 초 반환

SELECT HOUR(now()),    -- 20
    MINUTE(now()),         -- 27
    SECOND(now())        -- 31
    ;

• DATE_FORMAT
  • DATE_FORMAT(날짜/시간, 형식)
  • 날짜/시간의 형식을 형식으로 바꿔 반환

SELECT DATE_FORMAT('1994-02-16 11:11:11', '%Y년 %m월 %d일 %H시 %i분 %s초');
-- 1994년 02월 16일 11시 11분 11초

날짜/시간 형식

• DATEDIFF
  • DATEDIFF(날짜1, 날짜2)
  • 날짜1과 날짜2의 차이 반환
• TIMEDIFF
  • TIMEDIFF(시간1, 시간2)
  • 시간1과 시간2의 차이 반환

-- 두 함수 모두 음수를 반환 할 수도 있습니다.
SELECT DATEDIFF(now(), '1994-02-16'); -- 10531
SELECT TIMEDIFF('21:00:00', '22:00:00'); -- -1:00:00

실습

여전히 포켓몬고 DBA로 일하는 당신!
기획자가 다음과 같은 데이터가 필요하다고 합니다.

안녕하세요.
기획담당 김기획입니다.
저번 미팅 때 말씀드린 내용 메일로 드립니다.

1. 포켓몬 테이블에서 포켓몬의 이름과 이름의 글자 수를 이름의 글자 수로 정렬한 데이터
    정렬 순서는 글자 수가 적은 것에서 많은 순으로 해주세요.

2. 포켓몬 테이블에서 순위를 보여주는 컬럼을 새로 만들어서 defense_rank라는 별명으로 된 데이터와
    포켓몬 이름도 필요합니다.
    조건1: 방어력 순위란 방어력이 큰 순서대로 나열
    조건2: 공동 순위가 있으면 다음 순서로 건너뛰기

3. 포켓몬 테이블에서 포케몬을 포획한 지 기준 날짜까지 며칠이 지났는 지를 days라는 별명으로 된 데이터
    포켓몬 이름도 함께 가져와 주세요.
    조건: 기준날짜 2022-02-14

감사합니다.^^

TABLE

POKEMON

SQL

/*
포켓몬 테이블에서 포켓몬의 이름과 이름의 글자 수를 이름의 글자 수로 정렬한 데이터
    정렬 순서는 글자 수가 적은 것에서 많은 순으로 해주세요.
*/
SELECT name, 
    LENGTH(name) 
FROM POKEMON
ORDER BY LENGTH(name) ASC -- default가 ASC라서 없어도 됩니다.
;

/*
포켓몬 테이블에서 순위를 보여주는 컬럼을 새로 만들어서 defense_rank라는 별명으로 된 데이터와
    포켓몬 이름도 필요합니다.
    조건1: 방어력 순위란 방어력이 큰 순서대로 나열
    조건2: 공동 순위가 있으면 다음 순서로 건너뛰기
*/
SELECT name, 
    RANK() OVER (ORDER BY defense DESC) AS defense_rank
FROM POKEMON
;

/*
포켓몬 테이블에서 포케몬을 포획한 지 기준 날짜까지 며칠이 지났는 지를 days라는 별명으로 된 데이터
    포켓몬 이름도 함께 가져와 주세요.
    조건: 기준날짜 2022-02-14
*/
SELECT name, 
    DATEDIFF('2022-02-14', capture_date) AS days
FROM POKEMON
;

업무를 완료한 당신!에게 또 메일이 옵니다.!

안녕하세요.
기획담당 김기획입니다.
저번에 주신 SQL은 개발자분이 잘 받았다고 합니다.
그런데 이번 업체와의 미팅 결과로 새로운 데이터가 필요하다 합니다...
그래서 다시 한번 요청드립니다.

1. 포켓몬의 이름을 마지막 3개 문자만, last_char 라는 별명으로

2. 포켓몬 이름을 왼쪽에서 2개 문자를 left2라는 별명으로

3. 포멧몬 이름에서 이름에 o가 포함된 포멧몬만 모든 소문자를 o를 대문자 O로 바꿔서 bigO라는 별명으로

4. 포켓몬 타입을 가장 첫번째 글자 1자, 가장 마지막 글자 1자를 합친 후, 대문자로 변환해서 type_code라는 별명으로 가져와주시고 이름도 함께 가져와 주세요.
 e.g) 타입이 water이면, WR이 됩니다.

5. 포켓몬 이름의 글자 수가 8보다 큰 포켓몬 데이터 전부

6. 모든 포켓몬의 공격력 평균을 정수로 반올림해서 avg_of_attack 이라는 별명으로

7. 모든 포켓몬의 방어력 평균을 정수로 내림해서 avg_of_defense라는 별명으로

8. 이름의 길이가 8미만인 포멧몬의 공격력의 2 제곱을 attack2라는 별명으로, 
    이때 이름도 함께 가져와 주세요.

9. 모든 포켓몬의 공격력을 2로 나눈 나머지를 div2라는 별명으로, 
    이때 이름도 함께 가져와 주세요.

10. 공격력이 50 이하인 포켓몬의 공격력을 방어력으로 뺀 값의 절댓값을 diff라는 별명으로, 
    이름도 함께 가져와주세요.

11. 현재 날짜와 시간을 가져와 주세요. 
    각각 now_date, now_time 이라는 별명으로 가져와 주세요.

12. 포켓몬을 포획한 달(월, MONTH)을 숫자와 영어로 가져와 주세요. 
    숫자는 month_num, 영어는 month_eng라는 별명으로 가져와 주세요.

13. 포켓몬을 포획한 날의 요일을 숫자와 영어로 가져와 주세요.
    숫자는 day_num, 영어는 day_eng이라는 별명으로 가져와 주세요.

14. 포켓몬을 포획한 날의 연도, 월, 일을 각각 숫자로 가져와 주세요.
    연도는 year, 월은 month, 일은 day라는 별명으로


감사합니다.^^

SQL

/*
1. 포켓몬의 이름을 마지막 3개 문자만, last_char 라는 별명으로
*/
SELECT RIGHT(name, 3) AS last_char
FROM POKEMON
;

/*
2. 포켓몬 이름을 왼쪽에서 2개 문자를 left2라는 별명으로
*/
SELECT LEFT(name, 2) AS left2
FROM POKEMON
;

/*
3. 포켓몬 이름에서 이름에 o가 포함된 포켓몬만 모든 소문자를 o를 대문자 O로 바꿔서 bigO라는 별명으로
*/
SELECT REPLACE(name, 'o', 'O') AS bigO
FROM POKEMON
WHERE name LIKE %o%
;

/*
4. 포켓몬 타입을 가장 첫번째 글자 1자, 가장 마지막 글자 1자를 합친 후, 대문자로 변환해서 type_code라는 별명으로 가져와주시고 이름도 함께 가져와 주세요.
 e.g) 타입이 water이면, WR이 됩니다.
*/
SELECT name, 
    UPPER(CONCAT(LEFT(type, 1), RIGHT(type, 1)) AS type_code
FROM POKEMON
;

/*
5. 포켓몬 이름의 글자 수가 8보다 큰 포켓몬 데이터 전부
*/
SELECT *
FROM POKEMON
WHERE LENGHT(name) > 8
;

/*
6. 모든 포켓몬의 공격력 평균을 정수로 반올림해서 avg_of_attack 이라는 별명으로
*/
SELECT ROUND(AVG(attack)) AS avg_of_attack
FROM POKEMON
;

/*
7. 모든 포켓몬의 방어력 평균을 정수로 내림해서 avg_of_defense라는 별명으로
*/
SELECT FLOOR(AVG(defense)) AS avg_of_defense
FROM POKEMON
;

/*
8. 이름의 길이가 8미만인 포멧몬의 공격력의 2 제곱을 attack2라는 별명으로, 
    이때 이름도 함께 가져와 주세요.
*/
SELECT name, 
    POWER(attack) AS attack2
FROM POKEMON
WHERE LENGTH(name) < 8
;

/*
9. 모든 포켓몬의 공격력을 2로 나눈 나머지를 div2라는 별명으로, 
    이때 이름도 함께 가져와 주세요.
*/
SELECT name, 
    MOD(attack, 2) AS div2
FROM POKEMON
WHERE name LIKE %o%
;

/*
10. 공격력이 50 이하인 포켓몬의 공격력을 방어력으로 뺀 값의 절댓값을 diff라는 별명으로, 
    이름도 함께 가져와주세요.
*/
SELECT ABS(attak - defense) AS diff
FROM POKEMON
WHERE attack <= 50
;

/*
11. 현재 날짜와 시간을 가져와 주세요. 
    각각 now_date, now_time 이라는 별명으로 가져와 주세요.
*/
SELECT CURRNET_DATE() AS now_date, 
    CURRNET_TIME() AS now_time
FROM POKEMON
;

/*
12. 포켓몬을 포획한 달(월, MONTH)을 숫자와 영어로 가져와 주세요. 
    숫자는 month_num, 영어는 month_eng라는 별명으로 가져와 주세요.
*/
SELECT MONTH(capture_date) AS month_num, 
    MONTHNAME(capture_date) AS month_eng
FROM POKEMON
;

/*
13. 포켓몬을 포획한 날의 요일을 숫자와 영어로 가져와 주세요.
    숫자는 day_num, 영어는 day_eng이라는 별명으로 가져와 주세요.
*/
SELECT DAYOFWEEK (capture_date) AS day_num, 
    DAYNAME(capture_date) AS day_eng
FROM POKEMON
;
/*
14. 포켓몬을 포획한 날의 연도, 월, 일을 각각 숫자로 가져와 주세요.
    연도는 year, 월은 month, 일은 day라는 별명으로
*/
SELECT YEAR(capture_date) AS year, 
    MONTH(capture_date) AS month,
    DAY(capture_date) AS day
FROM POKEMON
;

반응형

데이터 그룹화 하기

GROUP BY

• GROUP BY [컬럼명] 형식으로 사용
• 그룹 별 데이터를 집계할 때 사용
  • 엑셀의 피벗 기능과 유사
• GROUP BY가 쓰인 쿼리의 SELECT 절에는 GROUP BY 대상 컬럼과 그룹 함수만 사용 가능
  • 만약, GROUP BY 대상 컬럼이 아닌 컬럼을 SELECT 하면, 에러가 발생
• 여러 컬럼으로 그룹화 가능, 키
  • 키워드 뒤에 [컬럼명]을 복수 개 입력
• 컬럼 번호로도 그룹화 가능
  • 컬럼 번호는 SELECT 절의 컬럼명의 순서를 의미

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

SELECT type
FROM mypokemon
GROUP BY type
;

HAVING

• 조회할 데이터 그룹에 조건을 지정해주는 키워드
• HAVING 조건식 형식으로 사용
• 조건식이 참(TRUE)이되는 그룹만 선택
• HAVING 절의 조건식에서는 그룹함수 활용

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

SELECT attack 
FROM mypokemon
GROUP BY attack
HAVING attack > 50
;

그룹 함수

COUNT

• 그룹의 값 수를 세는 함수
• COUNT([컬럼명]) 형식으로 SELECT, HAVING 절에서 사용
  • 집계할 컬럼명은 그룹의 기준이 되는 컬럼 이름과 같아도 되고, 같지 않아도 됨
  • COUNT(1)은 하나의 값을 1로 세어주는 표현으로 COUNT 함수에 자주 사용
    • COUNT(1)과 COUNT(*)은 동일
    • COUNT(*)과 COUNT([컬럼명])은 다름
      • COUNT(*): NULL 값에 관계없이 모든행 카운트 함
      • COUNT([컬럼명]): NULL 값은 카운트 하지 않음
• GROUP BY 없는 쿼리에서도 사용 가능
  • 이때는 전체 행에 함수가 적용

SELECT type, 
     COUNT(1) 
FROM mypokemon
GROUP BY `type` 
;

SUM, AVG, MIN, MAX

• 그룹의 합(SUM), 평균(AVG), 최소값(MIN), 최댓값(MAX)을 계산하는 함수
• 함수명([컬럼명]) 형식으로 SELECT, HAVING 절에서 사용
  • 집계할 컬럼명은 그룹의 기준이 되는 컬럼명과 같아도 되고, 같지 않아도 됨
• GROUP BY 가 없는 쿼리에서도 사용 가능
  • 전체 행에 함수 적용

SELECT type, 
    SUM(attack) AS sum,
    AVG(attack) AS avg,
    MAX(attack) AS max,
    MIN(attack) AS min
FROM mypokemon
GROUP BY `type` 
;

쿼리 순서

지금까지 SQL 키워드를 배웠습니다.
이 키워드들이 어떤 순서로 작성되는지 다시 한번 생각해보고,
키워드들이 어떤 순서로 실행되는지도 함께 알아봅시다.!

SELECT type,                -- 5
    COUNT(1),                -- 5
    MAX(weight)                -- 5
FROM mypokemon            -- 1
WHERE name LIKE '%a%'    -- 2
GROUP BY `type`            -- 3
HAVING MAX(height) > 1    -- 4
ORDER BY 3                -- 6     3번째 컬럼을 기준으로 정렬
;

실습

다음주에 휴가를 가는 당신!
휴가 가기전에 놓친 업무가 있는지 메일을 확인해 봅시다! 👀

안녕하세요.
기획담당 김기획입니다.
저번에 작성해주신 쿼리는 잘 받았습니다.!
다음주에 휴가계획이 있으시다고 들어서 긴급하게 회의하여 필요한 데이터 정립했습니다.
SQL 작성 부탁드리겠습니다.

1. 포켓몬 테이블에서 이름의 길이가 5보다 큰 포켓몬들을 타입을 기준으로 그룹화 하고,
몸무게의 평균이 20이상인 그룹의 타입과, 몸무게의 평균을 가져와 주세요.
이때 결과는 몸무게의 평균을 내림차순으로 정렬해 주세요.

2. 포켓몬 테이블에서 번호가 200보다 작은 포켓몬들을 타입을 기준으로 그룹화한 후에,
몸무게의 최댓값이 10보다 크거나 같고 최솟값은 2보다 크거나 같은 그룹의 타입, 키의 최솟값, 최댓값을 가져와 주세요.
이때의 결과는 키의 최솟값의 내림차순으로 정렬해주시고, 만약 키의 최솟값이 같다면 키의 최댓값의 내림차순으로 정렬 부탁드립니다.

감사합니다.

음… 휴가가기 전까지 일을 시키네요..! ㅠㅠ

/*
1. 포켓몬 테이블에서 이름의 길이가 5보다 큰 포켓몬들을 타입을 기준으로 그룹화 하고,
몸무게의 평균이 20이상인 그룹의 타입과, 몸무게의 평균을 가져와 주세요.
이때 결과는 몸무게의 평균을 내림차순으로 정렬해 주세요.
*/
SELECT type, 
    AVG(weight)
FROM POKEMON
WHERE LENGTH(name) > 5
GROUP BY type
HAVING AVG(weight) >= 20
ORDER BY 2 DESC
;

/*
2. 포켓몬 테이블에서 번호가 200보다 작은 포켓몬들을 타입을 기준으로 그룹화한 후에,
몸무게의 최댓값이 10보다 크거나 같고 최솟값은 2보다 크거나 같은 그룹의 타입, 키의 최솟값, 최댓값을 가져와 주세요.
이때의 결과는 키의 최솟값의 내림차순으로 정렬해주시고, 만약 키의 최솟값이 같다면 키의 최댓값의 내림차순으로 정렬 부탁드립니다.
*/
SELECT type,
    MIN(height),
    MAX(height)
FROM POKEMON
WHERE number < 200
GROUP BY type
HAVING MAX(weight) >= 10
    AND MIN(weight) >= 2
ORDER BY 2 DESC, 
    3 DESE
;