문자열 관련 함수들
-
LENGTH(string): 문자열 길이
-
LENGTHB(string): 문자열 바이트 수
-
CONCAT(string1, string2): 문자열 합쳐준다.
GROUP_CONCAT(합칠 컬럼, '구분자')
CONCAT()처럼 문자열을 합치는 함수이긴한데 특정 컬럼으로GROUP BY로 했을 때 지정하여 합칠 컬럼에서 각 row들의 값들을 구분자를 기준으로 합쳐준다.
ex) fruit라는 열에서 사과, 바나나, 참외, 사과가 있었을 때 id를 기준으로 group by 했을 때GROUP_CONCAT(fruit, '/')라고 하면사과/바나나/참외/사과이렇게 되고GROUP_CONCAT(DISTINCT(fruit, '/)라고 하면사과/바나나/참외가 된다. -
UPPER(string): 대문자로 치환
-
LOWER(string): 소문자로 치환
-
SUBSTRING(string, start_position, [length])
: 하위 문자열 추출. 이때 position은 index가 아니라 1부터 계산 -
Left('string or column', number_of_characters)
: 문자열의 끝(왼쪽)에서 지정된 개수의 문자를 추출 -
RIGHT('string or column', number_of_characters)
: 문자열의 끝(오른쪽)에서 지정된 개수의 문자를 추출 -
INSTR(original_string, search_string, [start_position], [occurrence])
: 문자열 내 하위 문자열의 시작 위치(position, not index)를 찾는 데 사용. start_position은 몇번째 문자부터 조회를 시작하는지이며 occurence는 search_string 중에서도 몇 번째의 position을 반환하는지 -
LPAD(추출할 문자열 또는 컬럼, 생성할 전체 자릿수, 'characters to fill left')
: 지정된 문자로 문자열을 왼쪽에서 채우는 함수 -
RPAD(추출할 문자열 또는 컬럼, 생성할 전체 자릿수, 'characters to fill right')
: 지정된 문자로 문자열을 오른쪽에서 채우는 함수LPAD, RPAD에서 전체 자릿수가 생성할 전체 자릿수보다 클 경우 생성할 전체 자릿수까지 자른다.
-
LTRIM([추출할 문자열 또는 컬럼])
: 공백을 왼쪽에서 제거하는 함수 -
RTRIM([추출할 문자열 또는 컬럼])
: 공백을 오른쪽에서 제거하는 함수 -
TRIM(추출할 문자열 또는 컬럼)
: 문자열 양 끝의 공백을 제거하는 함수 -
REPLACE('string or column', 'string1', 'string2')
: 특정 string또는 column에서 string1을 string2로 대체한다
ex) REPLACE('NAME','H', '')를 해서 이름에 'H'(특정 문자)를 제거할 수도 있음. -
TRANSLATE('string or column', '찾을 char들', '바꿀 char들')
: 문자열이나 열에서 찾을 char과 바꿀 char이 1대1 대응 되면서 각 대응되는 것들로 대체한다
ex)-- animal_ins 테이블의 name 컬럼에서 'D'를 'X'로, 'C'를 'Y'로 변환한 결과를 출력합니다. SELECT TRANSLATE(name, 'DC', 'XY') AS translated_name FROM animal_ins;이렇게 되면 'Cat'은 Yat', 'Dog'는 'Xog'가 된다.
날짜 관련 함수들
- DATE_FORMAT(timestamp, '%Y-%m-%d %H:%i:%s')
: 지정된 형식에 따라 날짜 값의 형식을 지정 - DATEDIFF(date1, date2)
: 두 기간 사이의 일수 계산. 이때 date1에서 date2를 빼서 date1이 더 큰 날짜여야함 - TIMEDIFF(time1, time2)
: 두 기간 사이의 시간 계산 - PERIOD_DIFF(date1, date2)
: 두 기간 사이의 개월 수 계산 - TIMESTAMPDIFF(unit, datetime1, datetime2)
: 두 기간 사이의 시간 계산. 단위 지정 가능 - NOW(): 현재 날짜와 시간을 반환
- CURDATE(): 현재 날짜를 반환
- CURTIME(): 현재 시간을 반환
숫자 관련 함수들
-
CEIL(num): 올림하여 정수로 출력
-
FLOOR(num): 내림하여 정수로 출력
-
ROUND(n,[m])
: n을 m자리까지 반올림. 없으면 정수로(정수만들때 사용)
ex) ROUND(123.4567, 2)는 123.46을 반환하고, ROUND(123.4567, -1)은 120을 반환 -
TRUNCATE(n,m)
: 숫자 값을 지정된 소수 자릿수로 자르는 데 사용(소수점 반올림이 아니라 자르기임)
ex)TRUNCATE(123.4567, 2)는 123.45, TRUNCATE(123.4567, -1)은 120을 반환 -
POWER(n,m): n의 m 제곱근
-
SQRT(num): num의 제곱근
-
ABS(num): num의 절대값
-
PI(): 3.14(상수)
-
EXP(num): e의 num 거듭제곱 값을 반환
데이터 형 변환 관련 함수들
- TO_CHAR(original, '형태')
- TO_DATE(original, '형태')
- TO_NUMBER(original, '형태')
NULL 관련 함수들
- COALESCE(column_to_inspect, value_to_replace_if_null)
: 주어진 column에서 NULL이 있는 경우 주어진 값으로 대체함 - IFNULL(column_to_inspect, value_to_replace_if_null)
: 주어진 column에서 NULL이 있는 경우 주어진 값으로 대체함 - IS NULL
: 특정 컬럼이 NULL인지 체크 - IS NOT NULL
:특정 컬럼이 NULL이 아닌지 체크
CASE WHEN THEN ELSE END
예시)
-- CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블
-- 대여 시작일이 2022년 9월에 속하는 대여 기록에 대해서
-- 대여 기간이 30일 이상이면 '장기 대여' 그렇지 않으면 '단기 대여' 로 표시하는 컬럼(컬럼명: RENT_TYPE)을 추가하여 대여기록을 출력하는 SQL문을 작성해주세요.
-- 결과는 대여 기록 ID를 기준으로 내림차순 정렬해주세요.
-- 이 문제에서 중요한 것은 날짜의 차이가 아니라 기간이라는 것을 생각해야한다
-- 9월 28일부터 9월 28일의 DATEDIFF()를 하면 0이지만 실제로는 하루 대여한 것이다.
-- 즉, DATEDIFF()+1해주는 것이 중요한 문제였다.
SELECT HISTORY_ID, CAR_ID, DATE_FORMAT(START_DATE,'%Y-%m-%d') AS START_DATE, DATE_FORMAT(END_DATE,'%Y-%m-%d') AS END_DATE,DATEDIFF(START_DATE, END_DATE),
CASE
WHEN DATEDIFF(END_DATE, START_DATE)+1 >= 30 THEN '장기 대여'
ELSE '단기 대여'
END AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE LIKE '2022-09%'
ORDER BY 1 DESC;
DATEDIFF(day1,day2)에서 day1이 day2보다 이른 날짜라면 결과는 음수가 되고DATEDIFF(day2,day1)을 해야지 양수가 된다.
WITH RECURSIVE문 (재귀 쿼리)
with recursive rc as (
select 1 as h -- 재귀 초깃값
union all
select h + 1 -- 재귀
from rc
where h < 5 -- 재귀 정지 조건
)
select * from rc