문자형 데이터 실습

숫자형 데이터 ABS, CEILING, ROUND, POWER, MOD

ABS(숫자)=숫자의 절댓값 반환

CEILING=정수로 올림해서 반환
FLOOR=정수로 내림해서 반환

ROUND=소숫점 자릿수까지 반올림해서 반환
TRUNCATE=소수점 자릿수까지 버림해서 반환

POWER(10, 2)=10의 2제곱 반환

MOD(10,5)=10을 5로 나눈 나머지 반환
따라서 2로 나누면 짝수인지 홀수인지 확인가능

 

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

 

mission2)
SELECT name, RANK() OVER (ORDER BY defense DESC) AS defense_rank
FROM mypokemon

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

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

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

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

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

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

mission 6) 틀림- 정수로 올림 ROUND
SELECT ROUND(AVG(attack)) AS avg_of_attack
FROM mypokemon;

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

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

mission 9) 틀림
SELECT name, MOD(attack, 2) AS div2
FROM mypokemon;

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

mission 11) 틀림
SELECT CURRENT_DATE() AS now_date, CURRENT_TIME() AS now_time;

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

mission 13) 틀림
SELECT DAYOFWEEK(capture_date) AS day_num, DAYNAME(capture_date) AS day_eng
FROM mypokemon;

mission 14)
SELECT YEAR(capture_date) AS year, MONTH(capture_date) AS month, DAY(capture_date) AS day
FROM mypokemon;

 

 

테이블 생성하기

DROP DATABASE IF EXISTS pokemon;
CREATE DATABASE pokemon;
USE pokemon;

CREATE TABLE mypokemon(
name VARCHAR(20),
number INT
capture_date DATE
);
INSERT INTO mypokemon(name, number)
VALUES ('pikachu',1),
VALUES ('raichu', 2);