mysql - 자주 사용하는 대표 숫자 관련 함수 정리

함수 요약 테이블

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

실습 테이블

CREATE DATABASE pokemon;

SHOW DATABASES;

USE pokemon;

DROP TABLE mypokemon;
CREATE TABLE pokemon.mypokemon (
	number INT NOT NULL,
	name VARCHAR(1048) NOT NULL,
	height FLOAT NOT NULL,
	weight FLOAT NOT NULL,
	friendship FLOAT,
	PRIMARY KEY(number)
);

INSERT INTO mypokemon (number, name, height, weight, friendship) 
	VALUES (10, 'caterpie', 0.3, 2.9, -1.455);
INSERT INTO mypokemon (number, name, height, weight, friendship) 
	VALUES (25, 'pikachu', 0.4, 6, 124.78);
INSERT INTO mypokemon (number, name, height, weight, friendship) 
	VALUES (26, 'raichu', 0.8, 30 ,30.289);
INSERT INTO mypokemon (number, name, height, weight, friendship) 
	VALUES (125, 'electabuzz', 1.1, 30, -10.67);
INSERT INTO mypokemon (number, name, height, weight, friendship) 
	VALUES (133, 'eevee', 0.3, 6.5, 15.988);
INSERT INTO mypokemon (number, name, height, weight, friendship) 
	VALUES (137, 'porygo', 0.8 ,36.5, -0.245);
INSERT INTO mypokemon (number, name, height, weight, friendship) 
	VALUES (152, 'chikorita', 0.9, 6.4, 67.164);
INSERT INTO mypokemon (number, name, height, weight, friendship) 
	VALUES (153, 'bayleef', 1.2, 15.8 ,9.756);
INSERT INTO mypokemon (number, name, height, weight, friendship) 
	VALUES (172, 'pichu', 0.3, 2, 872.1);
INSERT INTO mypokemon (number, name, height, weight, friendship) 
	VALUES (470, 'leafeon', 1, 25.5, 3.42);

SELECT * FROM pokemon.mypokemon;

mypokemon 테이블

 

ABS

SELECT name, friendship, ABS(friendship)
FROM pokemon.mypokemon;

FLOAT 데이터 타입은 입력값의 근사치를 저장하기 때문에 위와 같이 출력된다.

 

CEILING, FLOOR

SELECT name, friendship, CEILING(friendship), FLOOR(friendship)
FROM pokemon.mypokemon;

ROUND, TRUNCATE

SELECT name, friendship, ROUND(friendship, 1), TRUNCATE(friendship, 1)
FROM pokemon.mypokemon;

TRUNCATE는 테이블 값을 지울때도 사용되는 함수임!-> 관련링크

POWER, MOD

SELECT name, number, POWER(number, 2), MOD(number, 2)
FROM pokemon.mypokemon;