[3주차] SQL: ORDER BY, RANK, 함수(문자열, 날짜, 숫자)

/* RANK - SELECT절에 사용
SELECT 컬럼이름, ..., RANK() OVER(ORDER BY 컬럼 ASC/DESC)
FROM 테이블이름
WHERE 조건;
*/  

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

 
 
** RANK, DENSE_RANK, ROW_NUMBER

/* 
DENSE RANK() OVER (ORDER BY 컬럼이름)
ROW_NUMBER() OVER (ORDER BY 컬럼이름)
*/

# 순위 함수 비교
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: 공동 순위가 있으면 다음 순서로
DENSE_RANK: 공동 순위가 있어도 다음 순서로 건너뛰지 않음
ROW_NUMBER: 공동 순위 무시

 
 

# 데이터베이스 생성
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');

 
 
 
 

1. 문자열 함수

 

LOCATE(찾을 값, 찾을 곳): 위치 반환
SUBSTR(문자, 반환시작위치) : 입력한 숫자가 문자열의 길이보다 크면 반환X
RIGHT(문자, 오른쪽에서 몇 번째 문자까지 반환?)
LEFT(문자, 왼쪽에서 몇 번째 문자까지 반환?)
UPPER(문자): 대문자로
LOWER(문자): 소문자로
LENGTH(문자): 문자 길이 반환
CONCAT(문자, 문자): 문자열 합치기
REPLACE(전체문자, 바꿀 대상, 뭐로 바꿀거야)

 

# 전화번호에서 괄호안에 있는 지역번호 추출

SELECT SUBSTR(Phone, INSTR(Phone, '(')+1, (INSTR(Phone, ')') - INSTR(Phone, '(')-1)) AS areaCd 
FROM Suppliers
WHERE SUBSTR(Phone, INSTR(Phone, '(')+1, (INSTR(Phone, ')') - INSTR(Phone, '(')-1)) IS NOT NULL;

# 강사님이 마지막인자에 그냥 -2만 넣어도 된다고 하셨긴 했는데
# '('가 전부 첫번째라서 가능할 듯
# '('앞에 다른 문자가 있다면 적용안되니 그냥 위에 방식으로 기억하기
SELECT SUBSTR(Phone, INSTR(Phone, '(')+1, (INSTR(Phone, ')'-2)) AS areaCd 
FROM Suppliers

 

2. 숫자 함수

ABS(숫자): 숫자의 절댓값 반환
FLOOR(숫자): 숫자를 정수로 내림해서 반환
ROUND(숫자, 자릿수): 숫자를 소수점 자릿수까지 반올림해서 반환
TRUNCATE(숫자, 자릿수): 숫자를 소수점 자릿수까지 버림해서 반환
POWER(숫자A, 숫자B): 숫자A의 숫자B 제곱 반환
MOD(숫자A, 숫자B): 숫자A를 숫자B로 나눈 나머지 반환

 
 
 

3. 날짜 함수

NOW(): 현재 날짜와 시간 반환
CURRENT_DATE(): 현재 날짜 반환
CURRENT_TIME(): 현재 시간 반환
YEAR(날짜): 날짜의 연도 반환
MONTH(날짜): 날짜의 월 반환
MOTHNAME(날짜): 날짜의 월을 영어로 반환
DAYNAME(날짜): 날짜의 요일을 영어로 반환
DAYOFMONTH(날짜): 날짜의 일 반환
DAYOFWEEK(날짜): 날짜의 요일을 숫자로 반환
WEEK(날짜): 날짜가 해당 연도에 몇 번째 주인지 반환
HOUR(시간): 시간의 시 반환
MINUTE(시간): 시간의 분 반환
SECOND(시간): 시간의 초 반환
DATEFORMAT(날짜/시간, 형식): 날짜/시간의 형식을 형식으로 바꿔 반환
DATEDIFF(날짜1, 날짜2): 날짜1과 날짜2의 차이 반환 (날짜1 ‒ 날짜2)
TIMEDIFF(시간,1 시간2): 시간1과 시간2의 차이 반환 (시간1 ‒ 시간2)

 
 

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

 
 

# 1 포켓몬의 이름과 이름의 글자수를 글자수로 정렬
SELECT name, LENGTH(name)
FROM mypokemon
ORDER BY 2;

# 2 이름과, 방어력 순위(큰 순서) 컬럼, 공동순위가 있다면 건너 뛰기
SELECT name,
	RANK() OVER (ORDER BY defense DESC) AS defense_rank
FROM mypokemon;

# 3 이름과 포켓몬 포획이 기준날짜(2022-02-14)로 부터 며칠 지났는지
SELECT name,
	DATEDIFF('2022-02-14', capture_date) AS days
FROM mypokemon;

 

# 1 포켓몬의 이름 마지막 3글자
SELECT RIGHT(name, 3) AS last_char
FROM mypokemon;

# 포켓몬 이름 왼쪽에서 2글자
SELECT LEFT(name, 2) AS left2
FROM mypokemon;

#3 포켓몬 이름에 o포함된 포켓몬만 모든 소문자 o를 대문자 o로 바꿔서 가져오기
SELECT REPLACE(name, 'o', 'O') AS bigO
FROM mypokemon
WHERE name LIKE '%o%';

# 4 이름과 포켓몬 타입을 첫번째 글자, 마지막 글자 병합 후, 대문자로
SELECT name, 
UPPER(CONCAT(LEFT(type, 1), RIGHT(type, 1))) AS type_code
FROM mypokemon;    

# 5 이름의 글자수가 8보다 큰 포켓몬들의 모든 데이터
SELECT *
FROM mypokemon
WHERE LENGTH(name) > 8;

# 6 모든 포켓몬들의 공격력 평균을 정수로 반올림
SELECT ROUND(AVG(attack), 0) AS avg_of_attack
FROM mypokemon;

# 7 모든 포켓몬들의 방어력 평균을 정수로 내림
SELECT FLOOR(AVG(defense)) AS avg_of_defense
FROM mypokemon;

# 8 이름과 이름 길이가 8미만인 포켓몬의 공격력 2제곱
SELECT name, 
	POWER(attack, 2) AS attack2
FROM mypokemon
WHERE LENGTH(name) < 8;   

# 9 이름과 공격력을 2로 나눈 나머지
SELECT name,
	MOD(attack, 2) AS div2
FROM mypokemon;

# 10 공격력이 50이하인 포켓몬의 공격력을 방어력으로 뺀 값의 절대값    
SELECT name,
	ABS(attack - defense) AS diff
FROM mypokemon
WHERE attack <= 50;

# 11 현재 날짜와 시간
SELECT NOW() AS now, CURRENT_DATE() AS now_date, CURRENT_TIME() AS now_time;

# 12 포획한 달을 숫자와 영어로
SELECT MONTH(capture_date) AS month_num,
	MONTHNAME(capture_date) AS month_eng
FROM mypokemon;

# 13 포획한 날의 요일을 숫자와 영어로
SELECT DAY(capture_date) AS capture_day,
	DAYOFWEEK(capture_date) AS day_num,
    DAYNAME(capture_date) AS day_eng
FROM mypokemon;

# 14 포획한 날의 연도, 월, 일을 각각 숫자로
SELECT YEAR(capture_date) AS year,
	MONTH(capture_date) AS month,
    DAY(capture_date) AS day
FROM mypokemon;