데이터 줄세우기 (ORDER BY)
ORDER BY
- 가져온 데이터를 정렬해주는 키워드
- OREDER BY[컬럼 이름]형식으로 사용
- 입력한 [컬럼 이름]의 값을 기준으로 모든 row를 정렬
- 기본 정렬 규칙은 오름차순
- ORDER BY[컬럼 이름] = ORDER BY[컬럼 이름]ASC - 내림차순 정렬을 원할 경우에는 마지막에 DESC 키워드를 추가
- ORDER BY[컬럼 이름]DESC - 여러 컬럼으로 정렬도 가능. 키워드 뒤에 [컬럼 이름]을 복수 개 입력하면 된다
- 위치한 순서대로 정렬 - 컬럼 번호로도 정렬이 가능
- 이 때, 컬럼 번호는 SELECT 절의 컬럼 이름의 순서를 의미
SELECT[컬럼 이름]
FROM[테이블 이름]
WHERE 조건식
ORDER BY[컬럼 이름]ASC;
데이터 순위 만들기 (RANK, ROW_NUMBER)
RANK
- 데이터를 정렬해 순위를 만들어주는 함수
- RANK() OVER (ORDER BY[컬럼 이름])형식으로 사용
- 항상 ORDER BY와 함께 사용
- SELECT절에 사용하며, 정렬된 순서에 순위를 붙인 새로운 칼럼을 보여줌
- 테이블의 실제 데이터에는 영향을 미치지 않는다.
SELECT[컬럼 이름],...,RANK() OVER(ORDER BY[컬럼 이름])
FROM[테이블 이름]
WHERE 조건식;
문자형 데이터 정복하기
- MySQL내의 다양한 타입의 데이터는 '함수'를 사용하여 변형이 가능
함수 특징
- 함수 이름(함수를 적용할 값 또는 컬럼 이름) 형식으로 사용
- 결과 값을 새로운 컬럼으로 반환
LOCATE
- LOCATE("A","ABC") : "ABC"에서 "A"는 몇 번째에 위치해 있는지 검색해 위치 반환
- 문자가 여러 개라면 가장 먼저 찾은 문자의 위치를 가져온다
- 만약 찾는 문자가 없다면 0을 가져온다
SELECT [컬럼 이름],LOCATE('i',lyric)
FROM [테이블 이름]
SUBSTRING
- SUBSTRING("ABC",2) : "ABC"에서 2번째 문자부터 반환
- 만약 입력한 숫자가 문자열의 길이보다 크다면 아무것도 가져오지 않는다
RIGHT
- RIGHT("ABC",1) : "ABC"에서 오른쪽에서 1번째 문자까지 반환
LEFT
- LEFT("ABC",1) : "ABC"에서 왼쪽에서 1번째 문자까지 반환
UPPER
- UPEER("abc") : "abc"를 대문자로 바꿔 반환
LOWER
- LOWER("ABC") : "ABC"를 소문자로 바꿔 반환
LENGTH
- LENGTH("ABC") : "ABC"의 글자 수를 반환
CONCAT
- CONCAT("ABC", "DEF") : "ABC"문자열과 "CDF"문자열을 합쳐 반환
REPLACE
- REPLACE("ABC","A","Z") : "ABC"의 "A"를 "Z"로 변경
숫자형 데이터 정복하기
함수
ABS
- ABS(숫자) : 숫자의 절댓값 반환
CEILING
- CEILING(숫자) : 숫자를 정수로 올림해서 반환
FLOOR
- FLOOR(숫자) : 숫자를 정수로 내림해서 반환
ROUND
- ROUND(숫자, 자릿수) : 숫자를 소수점 자릿수까지 반올림해서 반환
TRUNCATE
- TRUNCATE(숫자, 자릿수) : 숫자를 소수점 자릿수까지 버림해서 반환
POWER
- POWER(숫자A,숫자B) : 숫자A의 숫자B 제곱 반환
MOD
- MOD(숫자A,숫자B) : 숫자A를 숫자B로 나눈 나머지 반환
날짜형 데이터 정복하기
함수
NIW
- NOW() : 현재 날짜와 시간 반환
CURRENT_DATE
- CURRENT_DATE() : 현재 날짜 반환
CURRENT_TIME
- CURRENT_TIME() : 현재 시간 반환
YEAR
- YEAR(날짜) : 날짜의 연도 반환
MONTH
- MONTH(날짜) : 날짜의 월 반환
MONTHNAME
- MONTHNAME(날짜) : 날짜의 월을 영어로 반환
DAYNAME
- DATNAME(날짜) : 날짜의 요일을 영어로 반환
DAYOFMONTH
- DAYOFMONTH(날짜) : 날짜의 일 반환
DAYOFWEEK
- DAYOFWEEK(날짜) : 날짜의 요일을 숫자로 반환
WEEK
- WEEK(날짜) : 날짜가 해당 연도에 몇 번째 주인지 반환
HOUR
- HOUR(시간) : 시간의 시 반환
MINUTE
- MINUTE(시간) : 시간의 분 반환
SECOND
- SECOND(시간) : 시간의 초 반환
DATE_FORMAT
- DATEFORMAT(날짜/시간,형식) : 날짜/시간의 형식을 형식으로 바꿔 반환
DATEDIFF
- DATEDIFF(날짜1, 날짜2) : 날짜1과 날짜2의 차이 반환(날짜1-날짜2)
TIMEDIFF
- TIMEDIFF(시간1,시간2) : 시간1과 시간2의 차이 반환(시간1-시간2)
실습1
DROP DATABASE IF EXISTS pokemon; CREATE DATABASE pokemon; USE pokemon; CREATE TABLE mypokemon( number int, name varchar(20), type varchar(20), height float, weight float, attack float, defense float, speed float ); INSERT INTO mypokemon(number, name, type, height, weight, attack, defense, speed) VALUES (10,'caterpie', 'bug',0.3,2.9,.30,35,45), (25,'picachu','electric',0.4,6,55,40,90), (27,'raichu','electric',0.8,30,90,55,110), (133,'eevee','normal',0.3,6.5,55,50,55), (152,'chikoirita','grass',0.9,6.4,49,65,45);
- 포켓몬 테이블에서 포켓몬의 이름과 이름의 글자 수를 이름의 글자 수로 정렬해서 가져와 주세요(정렬 순서는 글자 수가 적은 것부터 많은 것 순으로 해주세요)
SELECT name, LENGTH(name) FROM mypokemon ORDER BY LENGTH(name);
- 포켓몬 테이블에서 방어력 순위를 보여주는 컬럼을 새로 만들어서 'defense_rank'라는 별명으로 가져와 주세요. 이 때, 포켓몬 이름 데이터도 함께 가져와 주세요
조건1. 방어력 순위란 방어력이 큰 순서대로 나열한 순위를 의미합니다.
조건2. 공동 순위가 있으면 다음 순서로 건너 뛰어 주세요.
SELECT name, RANK() OVER (ORDER BY defense DESC) AS defense_rank FROM mypokemon;
- 포켓몬 테이블에서 포켓몬을 포획한 지 기준 날짜까지 며칠이 지났는 지를 'days'라는 별명으로 가져와 주세요. 이 때, 포켓몬의 이름도 함께 가져와 주세요
조건. 기준 날짜는 2002년 2월 14일 입니다.
SELECT name, DATEDIFF('2022-02-14', capture_date) AS days FROM mypokemon;
실습2
DROP DATABASE IF EXISTS pokemon; CREATE DATABASE pokemon; USE pokemon; CREATE TABLE mypokemon( number int, name varchar(20), type varchar(20), height float, weight float, attack float, defense float, speed float ); INSERT INTO mypokemon(number, name, type, height, weight, attack, defense, speed) VALUES (10,'caterpie', 'bug',0.3,2.9,.30,35,45), (25,'picachu','electric',0.4,6,55,40,90), (27,'raichu','electric',0.8,30,90,55,110), (133,'eevee','normal',0.3,6.5,55,50,55), (152,'chikoirita','grass',0.9,6.4,49,65,45);
- 포켓몬의 이름을 마지막 3개 문자만,'last_char'이라는 별명으로 가져와주세요
SELECT RIGHT(name, 3) AS last_char FROM mypokemon;
- 포켓몬 이름을 왼쪽에서 2개 문자를 'left2'라는 별명으로 가져와 주세요
SELECT LEFT(name, 2) AS left2 FROM mypokemon;
- 포켓몬 이름에서 이름에 o가 포함된 포켓몬만 모든 소문자 o를 대문자 O로 바꿔서 'bigO'라는 별명으로 가져와 주세요
SELECT REPLACE(name, 'o', 'O') AS bigO FROM mypokemon WHERE name LIKE '%o%';
- 포켓몬 타입을 가장 첫번째 글자 1자, 가장 마지막 글자 1자를 합친 후, 대문자로 변환해서 'type_code'라는 별명으로 가져오 주세요. 이 때, 이름도 함께 가져와 주세요.
SELECT name, UPPER(CONCAT(LEFT(type, 1), RIGHT(type, 1))) AS type_code FROM mypokemon;
- 포켓몬 이름의 글자 수가 8보다 큰 포켓몬의 데이터를 전부 가져와 주세요
SELECT * FROM mypokemon WHERE LENGTH(name) > 8;
- 모든 포켓몬의 공격력 평균을 정수로 반올림해서 'avg_of_attack'이라는 별명으로 가져와 주세요
SELECT ROUND(AVG(attack)) AS avg_of_attack FROM mypokemon;
- 모든 포켓몬의 방어력 평균을 정수로 내림해서 'avg_of_defense'이라는 별명으로 가져와 주세요
SELECT FLOOR(AVG(defense)) AS avg_of_defense FROM mypokemon;
- 이름의 길이가 8미만인 포켓몬의 공격력의 2 제곱을 'attack2'라는 별명으로 가져와 주세요. 이 때, 이름도 함께 가져와 주세요
SELECT name, POWER(attack, 2) AS attack2 FROM mypokemon WHERE LENGT(name) < 8;
- 모든 포켓몬의 공격력을 2로 나눈 나머지를 'div2'라는 별명으로 가져와 주세요. 이 때, 이름도 함께 가져와 주세요
SELECT name, MOD(attack, 2) AS div2 FROM mypokemon;
- 공격력이 50이하인 포켓몬의 공격력을 방어력으로 뺀 값의 절댓값을 'diff'라는 별명으로 가져와 주세요. 이 때, 이름도 함께 가져와 주세요
SELECT name, ABS(attack - defense) AS diff FROM mypokemon WHERE attack <= 50;
- 현재 날짜와 시간을 가져와 주세요. 각각 now_date, now_time이라는 별명으로 가져와 주세요.
SELECT CURRENT_DATE() AS mew_date, CURRENT_TIME() AS row_time;
- 포켓몬을 포획한 달(월, MONTH)을 숫자와 영어로 가져와 주세요. 숫자는 month_num, 영어는 month_eng이라는 별명으로 가져와 주세요
SELECT MONTH(capture_date) AS month_num, MONTHNAME(capture_date) AS month_eng FROM mypokemon;
- 포켓몬을 포획한 날의 요일을 숫자와 영어로 가져와 주세요. 숫자는 day_num, 영어는 day_eng이라는 별명으로 가져와 주세요
SELECT DAYOFWEEK(capture_date) AS day_num, DAYNAME(capture_date) AS day_eng FROM mypokemon;
- 포켓몬을 포획한 날의 연도, 월, 일을 각각 숫자로 가져와 주세요. 연도는 year, 월은 month, 일은 day라는 별명으로 가져와 주세요
SELECT YEAR(capture_date) AS year, MONTH(capture_date) AS month, DAY(capture_date) AS day FROM mypokemon;
가보자가보자~