[패스트캠퍼스] SQL 강의 : 원하는 데이터 가져오기

 

데이터 줄 세워서 가져오기(ODER BY)

• ODER BY [컬럼 이름] 형식으로 사용
• 입력한 [컬럼이름] 을 기준으로 모든 row를 정렬한다
• 기본 규칙은 오름차순이다 : ODER BY [컬럼이름] =ODER BY [컬럼이름] ASC
• 내림차순 정렬을 원할 시 : ODER BY [컬럼이름] DESC
• 여러 컬럼 정렬도 가능 : ODER BY [컬럼1], [컬럼2] 
• [컬럼1] 기준으로 정렬 → [컬럼1] 값이 동일한 로우 간에 [컬럼2] 기준으로 정렬
• 컬럼 번호로도 정렬이 가능하다. 이때, 컬럼 번호는 SELECT 절의 컬럼 이름 순서를 의미 

 

 

 

1. ODER BY 기본 문법

SELECT [컬럼이름] FROM [테이블이름]

WHERE 조건식 ODER BY [컬럼이름] ASC;

 

2.컬럼 번호로도 정렬이 가능 예시

SELECT number, name, attack, defense FROM mypokemon

ODER BY attack DESC, defense;

 

아래와 같음

 

SELECT number, name, attack, defense FROM mypokemon

ODER BY 3 DESC, 4;

 

순위를 정해 데이터 가져오기 (RANK)

• RANK() OVER (ODER BY [컬럼 이름]) 형식으로 사용
• 항상 ODER BY와 사용
• SELECT 절에 사용하며 정렬된 순서에 순위를 붙인 새로운 컬럼을 생성

 

1. RANK 문법 내림차순

SELECT [컬럼이름],..., RANK() OVER (ODER BY [컬럼이름]DESC)

FROM [테이블이름]

WHERE 조건식;

 

2. 공격력 순위 매기기

SELECT name, attack RANK() OVER(ODER BY attack DESC) AS attack_rank

FROM pokemon.mypokemon;

 

*RANK/ DENSE_RANK/ ROW_NUMBER 비교하기

• RANK : 공동순서가 있으면 다음 순서로 건너뜀
• DENSE_RANK : 공동순서가 있으도 다음 순서를 뛰어 넘지 않음
• ROW_NUMBER : 공동 순위를 무시함

 

---

문자형 데이터

 

함수이름(함수를 적용할 값 또는 컬럼이름) 형식으로 사용

결과 값을 새로운 컬럼으로 반환

 

<문자형 데이터 함수>

 

1. LOCATE

• LOCATE("A","ABC")
• "ABC"에서 "A"는 몇 번째에 위치해 있는지를 검색해 위치 반환 
• 문자가 여러 개라면 가장 먼저 찾은 문자의 위치를 가져온다
  만약 찾는 문자가 없다면 0을 가져온다

*괄호 내에는 함수를 적용할 값 또는 컬럼이름 가져온다

<예제>

SELECT part LOCATE('i', lyric) 

FROM bts_music.butter;

 

2. SUBSTRING

• SUBSTRING("ABC",2)
• "ABC"에서 2번째 문자부터 반환
• 만약 입력한 숫자가 문자열의 길이보다 크다면 아무것도 가져오지 않는다

<예제>

SELECT part, SUBSTRING(lyric,3)

FROM bts_music.butter;

 

3. RIGHT

• RIGHT("ABC",1) 
• "ABC"의 오른쪽에서 1번째 문자까지 반환

4. LEFT

• LEFT("ABC",1)
• "ABC"의 왼쪽에서 1번째 문자까지 반환

 

<예제>

SELECT part, RIGHT(lyric,3), LEFT(lyric,3)

FROM bts_music.butter;

 

5. UPPER

• UPPER("abc")
• 문자를 대문자로 바꿔서 반환

6. LOWER

• LOWER("ABC")
• 문자를 소문자로 바꿔서 반환

<예제>

SLECT part, UPPER(lyric), LOWER(lyric)

FROM bts_music.butter;

10. LENGTH

• LENGTH("ABC")
• "ABC"글자 수를 변환 

 

<예제>

SELECT part, LENGTH(lyric)

FROM bts_music.butter;

11. CONCAT

• CONCAT("ABC","DEF")
• "ABC"와 "DEF" 문자열을 합쳐서 반환

<예제>

SELECT part, CONCAT(LEFT(lyric,1),RIGHT(lyric,1)) AS first_last

FROM bts_music.butter;

12. REPLACE

• REPLACE("ABC","A","Z")
• "ABC"의 "A"를 "Z"로 바꿔 반환

 

SELECT part, REPLACE(lyric, ' ', '_')

FROM bts_music.butter;

 

---

숫자형 데이터

1. ABS

• ABS(숫자)
• 숫자의 절대값 반환

 

SELECT name, fridendship, ABS(friendship)

FROM pokemon. mypokemon;

2. CEILING

• CEILING(숫자)
• 숫자를 정수로 올림해서 반환 

 

3. FLOOR

• FLOOR(숫자)
• 숫자를 정수로 내림해서 반환

 

SELECT name, CEILING(friendship), FLOOR(friendship)

FROM pokemon.mypokemon;

4. ROUND

• ROUND(숫자, 자릿수)
• 숫자를 소수점 자릿수까지 반올림해서 반환
• 자릿수에 0 입력 시 소수점 없이 정수만 반환

 

5. TRUNCATE

• TRANCATE(숫자, 자릿수)
• 숫자를 소수점 자릿수까지 버림해서 반환
• 자릿수에 0 입력 시 소수점 없이 정수만 반환

 

SELECT name, ROUND(friendship,1), TRUNGATE(friendship,1)

FROM pokemon.mypokemon;

6. POWER

• POWER(숫자A, 숫자B)
• 숫자A의 숫자 B 제곱 반환

 

SELECT name, number, POWER(number,2)

FROM pokemon.mypokemon;

7. MOD

• MOD(숫자A, 숫자B)
• 숫자A를 숫자B로 나눈 나머지 반환 
• 짝수인지 홀수인지 알 수 있음

 

SELECT name, number, MOD(number,2)

FROM pokemon.mypokemon;

---

날짜형 데이형

 

함수	활용	설명
NOW	NOW()	현재 날짜와 시간 반환
CURRENT_DATE	CURRENT_DATE()	현재 날짜 반환
CURRENT_TIME	CURRENT_TIME()	현재 시간 반환
쿼리 : SELECT NOW(), CURRENT_DATE(), CURRENT_TIME();

*이 함수들은 X값(입력값)이 필요 없는 함수

 

함수	활용	설명
YEAR	YEAR(날짜)	날짜의 연도 반환
MONTH	MONTH(날짜)	날짜의 월 반환
MONTHNAME	MONTHNAME(날짜)	날짜의 월을 영어로 반환
SELECT NOW(), YEAR(NOW()), MONTH(NOW()), MONTHNAME(NOW());

함수	활용	설명
DAYNAME	DAYNAME(날짜)	날짜의 요일을 영어로 반환
DAYOFMONTH	DAYOFMONTH(날짜)	날짜의 일 반환
DAYOFWEEK	DAYOFWEEK(날짜)	날짜의 요일을 숫자로 반환
WEEK	WEEK(날짜)	날짜가 해당 연도의 몇 번째 주인지 반환
SELECT NOW(), DAYNAME(NOW()), DAYOFMONTH(NOW()), DAYOFWEEK(NOW()), WEEK(NOW());

함수	활용	설명
HOUR	HOUR(시간)	시간의 시 반환
MINUTE	MINUTE(시간)	시간의 분 반환
SECOND	SECOND(시간)	시간의 초 반환
SELECT NOW(), HOUR(NOW()), MINUTE(NOW()), SECOND(NOW());

함수	활용	설명
DATE_FORMAT	DATEFORMAT(날짜/시간,형식)	날짜/시간의 형식을 형식으로 바꿔 변환
SELECT DATE_FORMAT('1996-11-16 17:34:58', '%Y년 %m월 %d일 %H시 %i분 %s초') AS formatted_date;

 

DATE_FORMAT 함수

 

함수	활용	설명
DATEDIFF	DATEDIFF(날짜1, 날짜2)	날짜1과 날짜2의 차이 반환(날짜1-날짜2)
TIMEDIFF	TIMEDIFF(시간1, 시간2)	시간1과 시간2의 차이 변환(시간1-시간2)
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,

---

 

*예제 쿼리

 

DROP DATABASE IF EXISTS pokemon; CREATE DATABASE pokemon; USE pokemon; CREATE TABLE mypokemon ( number INT, name VARCHAR(20), type VARCHAR(10), attack INT, defense INT, capture_date DATE ); INSERT INTO mypokemon (number, name, type, attack, defense, capture_date) VALUES (10, 'caterpie', 'bug', 30, 35, '2019-10-14'), (25, 'pikachu', 'electric', 55, 40, '2018-11-04'), (26, 'raichu', 'electric', 90, 55, '2019-05-28'), (125, 'electabuzz', 'electric', 83, 57, '2020-12-29'), (133, 'eevee', 'normal', 55, 50, '2021-10-03'), (137, 'porygon', 'normal', 60, 70, '2021-01-16'), (152, 'chikoirita', 'grass', 49, 65, '2020-03-05'), (153, 'bayleef', 'grass', 62, 80, '2022-01-01');

 

예제 테이블

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

 

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

 

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



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

SELECT name, RANK() OVER (order by defense DESC) AS defense_rank FROM mypokemon;

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

 

---

DROP DATABASE IF EXISTS pokemon;
CREATE DATABASE pokemon;
USE pokemon;
CREATE TABLE mypokemon (
number INT,
name VARCHAR(20),
type VARCHAR(10),
attack INT,
defense INT,
capture_date DATE
);
INSERT INTO mypokemon (number, name, type, attack, defense, capture_date)
VALUES (10, 'caterpie', 'bug', 30, 35, '2019-10-14'),
(25, 'pikachu', 'electric', 55, 40, '2018-11-04'),
(26, 'raichu', 'electric', 90, 55, '2019-05-28'),
(125, 'electabuzz', 'electric', 83, 57, '2020-12-29'),
(133, 'eevee', 'normal', 55, 50, '2021-10-03'),
(137, 'porygon', 'normal', 60, 70, '2021-01-16'),
(152, 'chikoirita', 'grass', 49, 65, '2020-03-05'),
(153, 'bayleef', 'grass', 62, 80, '2022-01-01');

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

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

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



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

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

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


/*MISSION (5)
포켓몬 이름의 글자 수가 8보다 큰 포켓몬의 데이터를 전부 가져와 주세요.
MISSION (6)
모든 포켓몬의 공격력 평균을 정수로 반올림해서 ‘avg_of_attack’이라는 별명으로 가져와 주세요.
MISSION (7)
모든 포켓몬의 방어력 평균을 정수로 내림해서 ‘avg_of_defense’이라는 별명으로 가져와 주세요*/

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

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

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

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


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

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

/*MISSION (10)
공격력이 50 이하인 포켓몬의 공격력을 방어력으로 뺀 값의 절댓값을
‘diff’라는 별명으로 가져와 주세요. 이 때, 이름도 함께 가져와 주세요.
MISSION (11)
현재 날짜와 시간을 가져와 주세요. 각각 now_date, now_time이라
는 별명으로 가져와 주세요*/


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

SELECT current_date() AS now_date, current_time() AS now_time;




/*MISSION (12)
포켓몬을 포획한 달(월, MONTH)을 숫자와 영어로 가져와 주세요. 
숫자는 month_num, 영어는 month_eng이라는 별명으로 가져와
주세요.
MISSION (13)
포켓몬을 포획한 날의 요일을 숫자와 영어로 가져와 주세요. 숫자는
day_num, 영어는 day_eng이라는 별명으로 가져와 주세요.
MISSION (14)
포켓몬을 포획한 날의 연도, 월, 일을 각각 숫자로 가져와 주세요. 연
도는 year, 월은 month, 일은 day라는 별명으로 가져와 주세요*/


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

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

SELECT YEAR(capture_date)AS YEAR, MONTH(capture_date)AS MONTH, DAYOFMONTH(capture_date)AS DAY
FROM mypokemon;