MySQL 함수와 @변수의 사용

제어문 관련

select if (1 > 2, '참', '거짓') as '판단';

select ifnull(null, '널이다');
select ifnull(1, '널이 아니다');

-- select~case~when~end
select case 10
	when 1 then '1이다'
    when 10 then '10이다'
    else '모르겠다'
end as 'case연습'

 

문자열 관련

-- ascii
select ascii('a'), char(65); -- ascii

-- 다양한 length 함수들
-- utf-8 사용하므로 1글자 당 영어는 1byte, 한글의 경우 3byte
-- char_length는 글자 갯수 (가장 많이 사용됨)
-- length는 byte 갯수
select bit_length('abc'), char_length('abc'), length('abc'); -- 24, 3, 3
select bit_length('가나다'), char_length('가나다'), length('가나다'); -- 72, 3, 9

-- concat
select concat('good', 'by', 'cruel', 'world') as 'output'; -- goodbycruelworld
select concat_ws('|', 'I', 'My', 'Me', 'Mine') as 'output'; -- I|My|Me|Mine

-- left, right
SELECT LEFT("my name is da and", 5); // 왼쪽 5 문자열 출력
SELECT RIGHT("and you are my sunsine", 3); // 오른쪽부터 3 문자열 출력

-- lower, upper
SELECT
  LOWER ("ABC"), // 소문자화
  UPPER ("asdf") // 대문자화

 

문자열 찾기. 잘 사용하면 편리함

-- 0이 아니라 1부터 시작함.

select elt(1, '하나', '둘', '셋'); -- 1을 찾는다.
select field('하나', '하나', '둘', '셋'); -- '하나'를 찾는다
select find_in_set('둘', '하나,둘,셋'); -- ,로 구분된 문자열 중 '둘' 찾음
select instr('하나둘셋', '둘'); -- 기준 문자열 중 부분 문자열을 찾는다
select locate('둘', '하나둘셋') -- instr와 동일하나 파라미터 순서만 바뀐 것;

 

그 외의 문자열 관련 내부 함수들

-- pad
select lpad('이것이', 5, '*'), rpad('패드다', 5, '###');

-- ltrim, rtrim, trim
select ltrim('    이것이'), rtrim('트림이다    '), trim(' 이것  이 트림이  다  ');

-- repeat
select repeat('반복', 5);


-- replace
SELECT REPLACE("MSSQL", "MS", "MY") // 첫 인자의 두 인자(MS)에서 세번 째 인자(MY)로 교체. => MYSQL이 됨

-- reverse
select reverse('토마토맛토');

-- substring, substr, mid 같은 함수임 차이없음
select substring('대한민국만세', 3, 2); -- 민국

-- split 함수와 동일함. 아래 함수는 '.'을 기준으로 2번째 값까지 출력하고 그 이후는 버림. 
select substring_index('cafe.naver.com', '.', 2)
-- 음수도 가능함. 
select substring_index('cafe.naver.com', '.', -2)

 

숫자, 수학 관련

삼각함수 등 복잡한 내용은 필요할 때 직접 찾아보자... acos, asin, atan, sin, cos 등등

select format(1234.1234, 4); -- 소숫점 4자리 까지 + 3자리 수마다 , 찍어줌

-- 2, 16, 8 진법
select bin(16), hex(16), oct(16); 

-- 말 그대로 내림, 올림, 반올림
SELECT FLOOR(10.99),
CEIL(10.1),
ROUND(10.5);

-- 제곱근, 지수, 팩토리얼, 로그(자연로그)
SELECT SQRT(4), POW(2, 3), EXP(3), LOG(10)

-- 삼각함수와 pi
SELECT SIN(PI()/2), COS(PI()), TAN(PI() / 4)

-- 절대값과 랜덤값(0~1사이)
SELECT ABS(-5), RAND(), ROUND(RAND() * 100, 0)

 

날짜, 시간관련

사실 수학보다 날짜, 시간 관련 함수를 좀 더 자주 사용하는 편입니다.

SELECT NOW(), CURDATE(), CURTIME()

SELECT 
NOW(),
DATE(NOW()),
MONTH(NOW()),
DAY(NOW()),
HOUR(NOW()),
MINUTE(NOW()),
SECond(NOW())

SELECT 
NOW(),
MONTHNAME(NOW()),
dayname(NOW())

SELECT 
NOW(),
DAYOFMONTH(NOW()), // 일요일 1 ~ 토요일 7 중 몇번 째 날?
DAYOFWEEK(NOW()), // 0, 31 중 며칠?
DAYOFYEAR(NOW()) // 1, 366 중 어느날?

 

포맷을 설정할 수도 있다.

SELECT date_format(NOW(), '%D %y %a %d %m %n %j')

 

시간을 더하거나 빼야할 때는 직접 형변환을 통해 계산하기 보다

adddate, subdate, addtime, subtime 메서드를 사용합니다.

select adddate('2025-01-01', interval 31 day);
select adddate('2025-01-01', interval 1 month);
select subdate('2025-01-01', interval 31 day);
select subdate('2025-01-01', interval 31 day);
select addtime('2025-01-01', '1:1:1');
select subtime('2025-01-01', '1:1:1');

 

시스템 관련 함수

-- found_rows는 바로 윗 줄에 실행된 select문에서 조회된 행의 갯수를 구한다.
select * from usertbl;
select found_rows();

-- row_count는 바로 윗 줄에 실행된 insert, update, delete 문에 영향을 받은 행의 갯수를 구한다.
update buytbl set price=price*2;
select row_count();

select user(); -- 사용 중인 유저
select database(); -- 현재 사용 중인 DB
select version(); -- 현재 사용 중인 version
select sleep(4); -- 슬립

 

 

변수 설정 및 활용

set @const = 'Parto';
set @limit = 3;

-- select로 출력 가능
select @limit;

-- prepare로 쿼리를 설정 후 using을 사용해 활용 가능
prepare myQuery from 'select * from employees.employees where first_name = ? limit ?';
execute myQuery using @const, @limit;