SQL 변수와 내장 함수

1. 변수의 선언 및 값 대입

SET @변수이름 = 변수의 값 ;

SELECT @변수이름 ; -- 변수의 값 출력

 

set @var1 = 2 ;

set @var2 = 5 ;

set @var3 = 7.85 ;

set @var4 = '도레미' ;

 

select @var1 ;  # 5

select @var2 + var3 ; # 12.85000000000000000000000000000000

select @var4, name from userTable ;

 

# limit 에는 원칙적으로 변수 사용 불가능하지만 prepare과 execute문을 활용해서 변수 활용 가능

set @var1 = 5 ;

prepare myQuery

     from 'select name, height from userTbl order by height limit ?' ;

execute myQuery using @var1 ;

# limit @var1 은 오류 => using @var1 이용해서 쿼리문의 '?' 부분에 대입

 

2. 제어 흐름 함수

-- 프로그램의 흐름을 제어

 

-- if (수식, 참, 거짓)

select if(100>200, '참이다', '거짓이다') ; -- 거짓이다

 

-- ifnull (수식1, 수식2) : 수식1이 null이 아니면 수식1 반환, null이면 수식2 반환

select ifnull(null, '널입니다'), ifnull(300, '널입니다') ; -- 널입니다, 100

 

-- nullif (수식1, 수식2) : 수식1과 수식2가 같으면 null 반환, 다르면 수식1 반환

select nullif(100, 100), ifnull(500, 1000) ; -- null, 200

 

3. 문자열 함수

-- 문자열을 조작

 

-- ASCII (아스키코드), char(숫자)

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

 

# 문자열 길이

-- bit_length (문자열) : 할당된 bit 크기 또는 문자 크기 반환

-- char_length (문자열) : 문자의 개수 반환

-- length (문자열) : 할당된 byte 수 반환

 

# 문자열 연결

-- concat (문자열1, 문자열2, ... ) : 문자열 연결 

-- ex. concat(column1, '[', column2, ']') => 이런식으로 다른 문자열 사이사이에 추가 가능

-- concat_ws (구분자, 문자열1, 문자열2, ... ) : 구분자와 함께 문자열을 연결

-- ex. 문자열 연결에 특정 패턴 이용하기!

--      concat_ws ('#', column1, column2, column3) => column1#column2#column3

 

# 문자열 찾기

-- find_in_set (찾을 문자열, 문자열 리스트) : 찾을 문자열을 문자열 리스트에서 찾아서 위치 반환 (문자열 리스트는 콤마 구분, 공백 없어야 함) 

--     ex. find_in_set ('male', sex) : sex라는 칼럼에 'male'이 들어간 경우 찾기 (포함 => 1로 표시, 포함X => 0으로 표시)

--     find_in_set은 where절과 유사, 단 찾고자하는 'male'이 단독인 경우만 가능. 문자열 사이에 있는 단어면 X

-- field (찾을 문자열, 문자열1, 문자열2, ... ) : 찾는 문자열의 위치를 반환

-- elt (위치, 문자열1, 문자열2, ... ) : 위치 번째에 해당하는 문자열 반환

-- instr (기준 문자열, 부분 문자열) : 기준 문자열에서 부분 문자열을 찾아서 그 시작 위치를 반환

-- locate (부분 문자열, 기준 문자열) : istr( )과 동일, 파라미터 순서 반대  = position( )

 

# 문자열 표시 형식

-- format (숫자, 소수점 자릿수) : 숫자를 소수점 아래 자릿수까지 표현 + 1000 단위마다 콤마 표시

-- bin (숫자) / hex (숫자) / oct (숫자) : 2진수, 16진수, 8 진수 반환

-- ucase (문자열) / lcase (문자열) : 소문자를 대문자로 / 대문자를 소문자로 변경  = upper( ) / lower( )

 

# 문자열 변경

-- insert (기준 문자열, 위치, 길이, 삽입할 문자열) : 기준 문자열의 위치부터 길이만큼 지우고 삽입할 문자열을 끼워 넣음

-- left (문자열, 길이) / right (문자열, 길이) : 왼쪽/오른쪽에서 문자열의 길이만큼 반환

-- lpad (문자열, 길이, 채울 문자열) / rpad (문자열, 길이, 특정문자) : 문자열 길이만큼 늘린 후 왼쪽 / 오른쪽의 빈 곳을 특정문자로 채움

-- ltrim (문자열) / rtrim (문자열) : 문자열의 왼쪽 / 오른쪽 공백을 제거. 중간 공백은 제거X

-- trim (문자열) : 문자열의 앞뒤 공백 모두 제거

-- trim(방향 특정문자열 from 문자열) : 특정 방향의 특정문자열을 문자열에서 제거

-- * 방향 : leading(앞), both(양쪽), trailing(뒤)

-- repeat (문자열, 횟수) : 문자열을 횟수만큼 반복

-- replace (문자열, 원래 문자열, 바꿀 문자열) : 문자열에서 원래 문자열을 찾아 바꿀 문자열로 바꿈

-- reverse (문자열) : 문자열의 순서를 거꾸로

 

# 공백

-- space (길이) : 길이만큼의 공백 반환

-- replace( ) 문자열 치환 => 이것을 통해 공백(' ') 제거 가능

-- (' ') 이 공백은 space(1)로도 표현 가능

 

# substring

-- substring (문자열, 시작위치, 길이) = substring (문자열 from 시작위치 for 길이)

--     substring (column_name, 숫자) : 몇번째 자리부터 나머지 문자열을 출력

--     substring (column_name, 숫자, 숫자) : 몇번째 자리부터 몇개의 문자열을 출력

--     * substring( ) = substr( ) = mid( )

-- substring_index (문자열, 구분자, 횟수) : 문자열에서 지정된 구분자가 지정된 횟수만큼 등장할떄까지만 출력

 

4. 수학 함수

-- abs (숫자) : 절댓값

-- acos, asin, atan, atan2, sin, cos, tan (숫자) : 삼각 함수 

-- ceiling, floor, round (숫자) : 올림, 내림, 반올림  * ceiling( ) = ceil( )

-- conv (숫자, 원래 진수, 변환할 진수) : 숫자를 원래 진수에서 변환할 진수로 계산

-- degress, radians (숫자) : 라디안 값을 각도값으로, 각도값을 라디안 값으로 변환

-- pi ( ) : 파이값 반환

-- exp, ln, log, log2, log10 : 지수, 로그 관련 함수  * log(숫자) / log(밑수, 숫자)

-- mod (숫자1, 숫자2) = 숫자1 % 숫자2 = 숫자1 mod 숫자2 : 숫자1을 숫자2로 나눈 나머지 값

-- pow (숫자1, 숫자2), sqrt (숫자) : 거듭제곱값, 제곱근

-- rand ( ) : 0 이상 1 미만의 실수 랜덤 생성

-- * 'm <= 임의의 정수 < n'  =>  floor( m + (rand( ) * (n-m) )

-- sign (숫자) : 숫자가 양수, 0, 음수인지 => 결과는 1, 0, -1

-- truncate (숫자, 정수) : 숫자를 소수점 기준으로 정수 위치까지 구하고 나머지 버림

 

5. 날짜 및 시간 함수

-- adddate(날짜, 숫자) : 날짜를 기준으로 며칠을 더함 = date_add( )

-- subdate(날짜, 숫자) : 날짜를 기준으로 며칠을 뺌 = date_sub( )

-- addtime(날짜/시간, 숫자) : 날짜/시간을 기준으로 몇시간을 더함

-- subtime(날짜/시간, 숫자) : 날짜/시간을 기준으로 몇시간을 뺌

-- curdate( ) : 현재 날짜(연-월-일) = current_date( ), current_date

-- curtime( ) : 현재 시간(시:분:초) = current_time( ), current_time

-- now( ), sysdate( ) : 현재 날짜와 시간 = localtime, localtime( ), localtimestamp, localtimestamp( )

-- year(날짜), month(날짜), day(날짜), hour(시간), minute(시간), second(시간), microsecond(시간) : 연, 월, 일, 시, 분, 초, 밀리초를 구함

-- dayofmonth( ) = day( )

-- date(날짜), time(날짜) : datetime 형식에서 연-월-일 및 시:분:초만 추출

-- datediff(날짜1, 날짜2) : 날짜1 - 날짜2의 일수

-- timediff(시간1, 시간2) : 시간1 - 시간2의 결과 (날짜도 입력 가능)

-- dayofweek(날짜) : 요일(1:일 ~ 7:토)

-- monthname(날짜) : 월 이름

-- dayofyear(날짜) : 1년 중 며칠이 지났는지

-- last_day(날짜) : 해당 달의 마지막 날짜

-- makedate(연도, 숫자) : 연도에서 며칠이 지난 날짜

-- maketime(시, 분, 초) : '시:분:초'의 Time 형식 만듦

-- period_add(연월, 숫자) : 연월에거 몇 개월만큼 지난 연월을 구함 (연월은 YYYY 또는 YYYYMM 형식)

-- period_diff(연월1, 연월2) : 연월1 - 연월2의 개월수

-- quarter(날짜) : 날짜가 4분기 중 몇 분기인지

-- time_to_sec(시간) : 시간을 초 단위로

-- timestampdiff(단위, 날짜1, 날짜2) : 날짜2 - 날짜1을 단위 기준으로 표현

# 나이 구하기

-- select name, date_of birth, timestampdiff(YEAR, date_of_birth, '2019-10-01') as age from student_details ;  

 

6. 시스템 정보 함수

-- user( ) : 현재 사용자 = session_user( ), current_user( )

-- database( ) : 현재 선택된 데이터베이스 = schema( )

-- found_rows( ) : 바로 앞의 select문에서 조회된 행의 개수

-- row_count( ) : 바로 앞의 insert, update, delete문에서 입력, 수정, 삭제된 행의 개수

--     * (create, drop문은 0, select문은 -1 반환)

-- version( ) : 현재 MySQL의 버전

-- sleep(초) : 쿼리의 실행을 잠깐 정지

--   ex. select sleep(5) ;   select '5초 후에 이 문장이 나옵니다.' ;