[SQL] 원하는 데이터 만들기(ORDER BY, RANK, 문자 + 숫자 + 날짜형 데이터 함수)

ORDER BY

디폴트 값은 ASC(오름차순)

칼럼이 여러개인 경우 칼럼1 값이 동일한 로우 간에 칼럼2 기준으로 정렬 ~~~
-> 먼저 입력된 칼럼 기준

칼럼 번호로도 정렬 가능

---

Rank

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

• 항상 ORDER BY와 함께 사용
• SELECT 절에서 사용하며 정렬된 순서에 순위를 붙인 새로운 컬럼을 보여줌
  -> 실제 데이터 영향 X

DENSE_RANK(), ROW_NUMBER

---

문자형 데이터 함수

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

LOCATE

가장 먼저 위치한 것에 위치 가져옴, 찾는 문자 없으면 0 반환

SUBSTRING

RIGHT, LEFT

UPPER, LOWER

LENGTH

CONCAT

REPLACE

---

숫자형 데이터 함수

---

날짜형 데이터 함수

DateFormat 함수

DateDIFF

---

코드 예시

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');

USE pokemon;

select * from mypokemon;

select name, LENGTH(name) from mypokemon ORDER BY LENGTH(name);

select name, ROW_NUMBER() OVER(ORDER BY defense DESC) AS defense_rank from mypokemon;

select name, DATEDIFF('2022-02-14', capture_date) AS days from mypokemon;

select RIGHT(name, 3) AS last_char from mypokemon;

select LEFT(name, 2) AS left2 from mypokemon;

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;

select * from mypokemon where LENGTH(name) > 8;

select CEILING(AVG(attack)) AS avg_of_attack from mypokemon;

select FLOOR(AVG(defense)) AS avg_of_defense from mypokemon;

select name, POW(attack,2) as attack2 from mypokemon where LENGTH(name) < 8;

select name, MOD(attack,2) as div2 from mypokemon; 

select name, ABS(attack - defense) as diff from mypokemon where attack < 50;

select CURRENT_DATE as now_date, CURRENT_TIME as now_time from mypokemon limit 1;

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, DAY(capture_date) as day from mypokemon;