SQL_Basic_04_Day 05. 원하는 데이터 만들기

Day 05. 원하는 데이터 만들기

---

1. 데이터 줄 세우기

---

원하는 데이터 가져오기

“10번 이상 구매한 VIP-고객, 구매 금액 순으로 리스트 뽑아주세요”
“매출 5천만원 이상의 상품, 판매량 순으로 리스트 뽑아주세요”

 

이번 클립에서 우리는 데이터를 줄세우는 방법을 배운다.

 

가져온 데이터를 정렬해주는 키워드

ORDER BY

ORDER BY 특징

• ORDER BY [컬럼이름] 형식으로 사용한다.
• 입력한 [컬럼이름]의 값을 기준으로 모든row를 정렬합니다.
• 기본정렬 규칙은 오름차순.
• ORDER BY [컬럼이름] = ORDER BY [컬럼이름] ASC
• 내림차순 정렬을 원할경우에는 마지막에 DESC 키워드를 추가.
• ORDER BY [컬럼이름] DESC
• 여러 컬럼으로 정렬도 가능하며, 키워드 뒤에 [컬럼이름]을 복수개 입력하면 된다.
  
  • 위치한 순서대로 정렬이 된다.
    (예시) ORDER BY [컬럼1], [컬럼2]
    • [컬럼1] 기준으로 정렬-> [컬럼1]값이 동일한 로우간에 [컬럼2] 기준으로 정렬
• 컬럼번호로도 정렬이 가능.
  • 이때, 컬럼번호는 SELECT 절의 컬럼 이름의 순서를 의미.

 

# 오름차순 정렬
SELECT [컬럼이름]
FROM[테이블이름]
WHERE조건식
ORDER BY [컬럼이름] ASC;

# 내림차순 정렬
SELECT [컬럼이름]
FROM[테이블이름]
WHERE조건식
ORDER BY [컬럼이름] DESC;

 

ORDER BY 사용예제(1)

 

SELECT number, name
FROM mypokemon --테이블사용(USE) 명시했다고가정
ORDER BY number DESC;

 

 

ORDER BY 사용예제(2)

 

SELECT number, name, attack, defense
FROM mypokemon --테이블사용(USE) 명시했다고가정
ORDER BY attack DESC, defense;

 

 

ORDER BY 사용예제(3)

 

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

 

 

2. 데이터 순위 만들기

---

순위를 정해 원하는 데이터 가져오기

“10번 이상 구매한 VIP 고객,구매 금액 순으로 BEST 100 리스트 뽑아주세요”
“매출 5천만원 이상의 상품,판매량 순으로 WORST 30 리스트 뽑아주세요”

데이터에 순위를 만드는 방법을 배워보자.

 

데이터를 정렬해 순위를 만들어주는 함수

RANK

 

RANK 특징

 

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

 

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

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

 

RANK 사용 예제 

 

SELECT name, attack,
RANK() OVER (ORDER BY attack DESC) AS attack_rank
FROM pokemon.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;

 

 

3. 문자형 데이터 정복하기

 

MySQL내의 다양한 타입의 데이터는 ‘함수’를 사용하여 변형 할 수 있다.

 

 

함수 특징

함수 이름 (함수를 적용할값 또는 컬럼 이름) 형식으로 사용.
결과 값을 새로운 컬럼으로 반환.

 

자주 사용하는 문자형 데이터 함수

 

LOCATE 함수

문자가 여러개 라면 가장 먼저 찾은 문자의 위치를 가져온다.
만약 찾는 문자가 없다면 0을 가져온다.

 

SELECT part, LOCATE( ‘i’, lyric)
FROM bts_music.butter;

 

 

 

SUBSTRING 함수

만약 입력한 숫자가 문자열의 길이보다 크다면 아무것도가져오지않습니다.

 

SELECT part, SUBSTRING(lyric, 3)
FROM bts_music.butter;

 

 

RIGHT, LEFT 함수

 

SELECT part, RIGHT(lyric, 3), LEFT(lyric, 3)
FROM bts_music.butter;

 

 

UPPER, LOWER 함수 

 

SELECT part, UPPER(lyric), LOWER(lyric)
FROM bts_music.butter;

 

 

LENGTH 함수

 

SELECT part, LENGTH(lyric)
FROM bts_music.butter;

 

 

CONCAT 함수

 

SELECT part,
CONCAT(LEFT(lyric, 1), RIGHT(lyric, 1)) AS first_last
FROM bts_music.butter;

 

 

REPLACE 함수

 

SELECT part, REPLACE(lyric, ‘ ‘, ‘_’)
FROM bts_music.butter;

 

 

4. 숫자형 데이터 정복하기

---

자주 사용하는 대표 숫자 관련 함수

 

 

ABS 함수

 

SELECT name, friendship, ABS(friendship)
FROM pokemon.mypokemon;

 

 

 

CEILING, FLOOR 함수

 

SELECT name, friendship, CEILING(friendship), FLOOR(friendship)
FROM pokemon.mypokemon;

 

 

ROUND, TRUNCATE 함수

※ 자릿수에 0 입력시 소수점 없이 정수만 반환

 

SELECT name, friendship, ROUND(friendship, 1), TRUNCATE(friendship, 1)
FROM pokemon.mypokemon;

 

 

 

POWER 함수

 

SELECT name, number, POWER(number, 2)
FROM pokemon.mypokemon;

 

 

MOD 함수

 

SELECT name, number, MOD(number, 2)
FROM pokemon.mypokemon;

 

5. 날짜형 데이터 정복하기

---

자주 사용하는 대표 날자형 데이터 함수

 

 

NOW, CURRENT_DATE, CURRENT_TIME 함수

 

SELECT NOW(), CURRENT_DATE(), CURRENT_TIME();

# 이함수들은 X값 (입력값)이 필요없는 함수입니다

 

 

YEAR, MONTH, MONTHNAME 함수

 

SELECT NOW(), YEAR(NOW()), MONTH(NOW()), MONTHNAME(NOW());

 

 

DAYNAME, DAYOFMONTH, DAYOFWEEK, WEEK 함수

 

SELECT NOW(), DAYNAME(NOW()), DAYOFMONTH(NOW()), DAYOFWEEK(NOW()), WEEK(NOW());

 

 

HOUR, MINUTE, SECOND 함수

 

SELECT NOW(), HOUR(NOW()), MINUTE(NOW()), SECOND(NOW());

 

 

 

DATE_FORMAT 함수

 

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

 

 

 

DATEDIFF, TIMEDIFF 함수

 

SELECT DATEDIFF(‘2022-01-01 00:00:00’, ‘2021-12-25 12:00:00’) AS DATE_DIFF,
	TIMEDIFF(‘2022-01-01 00:00:00’, ‘2021-12-25 12:00:00’) AS TIME_DIFF;

 

 

6.  [실습] 데이터를 요청대로 만들어 보자.

---

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

 

SELECT name , LENGTH(name) 
FROM mypokemon
ORDER BY( LENGTH(name))

 

 

MISSION (2)
포켓몬 테이블에서 방어력 순위를 보여주는 컬럼을 새로 만들어서 ‘defense_rank’라는별명으로 가져와주세요. 
이때, 포켓몬 이름 데이터도 함께 가져와주세요.
조건1: 방어력 순위란 방어력이 큰순서대로 나열한 순위를 의미합니다.
조건2: 공동 순위가 있으면 다음 순서로 건너 뛰어주세요. -> RANK() 함수 의미

 

SELECT defense , RANK() OVER(ORDER BY defense DESC )
FROM mypokemon;

 

 

MISSION (3)
포켓몬 테이블에서 포켓몬을 포획한지 기준날짜까지 며칠이 지났는지를 ‘days’라는 별명으로 가져와 주세요.
 이때, 포켓몬의 이름도 함께 가져와주세요.
조건: 기준날짜는 2022년2월14일 입니다.

 

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

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

 

 

 

 

7.  [실습]  다양한 함수를 사용해보자

---

MISSION (1)
포켓몬의 이름을 마지막3개 문자만, ‘last_char’이라는 별명으로 가져와주세요.

 

SELECT  RIGHT(name,3) AS last_char
FROM mypokemon;

 

 

MISSION (2)
포켓몬 이름을 왼쪽에서 2개 문자를 ‘left2’ 라는 별명으로 가져와주세요.

 

SELECT LEFT(name,2) AS left2
FROM mypokemon;

 

 

MISSION (3)
포켓몬 이름에서 이름에o가 포함된 포켓몬만 모든 소문자o를 대문자O로 바꿔서 ’bigO’라는 별명으로 가져와주세요.
Ex) 이름이 ‘pokemon’일 경우, ’bigO’ 값은 ’pOkemOn’이 됩니다.

 

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

 

 

MISSION (4)
포켓몬 타입을 가장 첫번째 글자1자, 가장 마지막 글자1자를 합친 후, 대문자로 변환해서
‘type_code’라는 별명으로 가져와 주세요. 이때, 이름도 함께 가져와주세요.
Ex) 타입이 ‘water’일 경우, ‘type_code’값은 ‘w’ 와‘r’를 대문자로 바꾼‘WR’이 됩니다.

 

SELECT CONCAT( UPPER (LEFT(type,1)), UPPER (RIGHT(type,1)) ) AS type_code
FROM mypokemon;

 

 

 MISSION (5)
포켓몬 이름의 글자 수가 8보다 큰 포켓몬의 데이터를 전부 가져와주세요

 

SELECT *
FROM mypokemon
WHERE LENGTH(name) > 8 ;

 

 

MISSION (6)
모든 포켓몬의 공격력 평균을 정수로 반올림해서 ‘avg_of_attack’ 이라는 별명으로 가져와주세요.

 

SELECT ROUND(AVG(attack) ,0) AS avg_of_attack
FROM mypokemon;

 

 

MISSION (7)
모든 포켓몬의 방어력 평균을 정수로 내림해서 ‘avg_of_defense’이라는 별명으로 가져와주세요.

 

SELECT FLOOR (AVG(defense)  ) AS  avg_of_defense
FROM mypokemon;

 

 

MISSION (8)
이름의 길이가 8미만인 포켓몬의 공격력의 2 제곱을 ‘attack2’라는 별명으로 가져와주세요. 

이때, 이름도 함께 가져와주세요.

 

SELECT name, POWER( attack, 2 ) AS attack2
FROM mypokemon
WHERE LENGTH(name) < 8 ;

 

 

 

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

 

SELECT name, MOD(attack , 2) AS div2
FROM mypokemon ;

 

 

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

 

SELECT name , ABS (defense - attack) AS diff
FROM mypokemon
WHERE attack <= 50;

 

 

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

 

SELECT CURRENT_DATE() AS now_date  , CURRENT_TIME() AS now_time ;

 

 

 

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

 

SELECT MONTH(capture_date) AS month_num , MONTHNAME(capture_date) AS month_eng
FROM mypokemon;

 

 

 

 

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

 

SELECT DAYOFMONTH(capture_date) AS day_num , DAYNAME(capture_date) AS day_eng
FROM mypokemon;

 

 

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

 

 

SELECT YEAR(capture_date) AS year , MONTH(capture_date) AS month, DAYOFMONTH(capture_date) AS day
FROM mypokemon;