숫자형 데이터 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);
'SQL' 카테고리의 다른 글
숫자형데이터 실습 (0) | 2022.06.27 |
---|---|
쿼리실행순서 (0) | 2022.06.27 |
문자열데이터(LENGTH, CONCAT, Locate, SUBSTR, LEFT, UPPER, Replace (0) | 2022.06.27 |
비교연산자, LIKE, BETWEEN, IN 예제 (0) | 2022.06.27 |
Where, 비교 연산자, (0) | 2022.06.27 |