자주 쓰이는 SQL 연산자 & 함수 정리

이번 포스팅에선 기본적이면서 자주 쓰이는 SQL 함수에 대해 정리한다.

어떤 함수가 있는지 정리하고, 예제로 바로 적용해보자.

 

✔Table of Contents

 

1. 자주 쓰이는 연산자 정리

연산자	설명	비고
AND	앞, 뒤 조건 모두 만족
OR	앞, 뒤 조건 중 하나라도 만족
NOT	뒤에 오는 조건과 반대	밑의 BETWEEN, LIKE 등의 특수 연산자와 같이 사용
BETWEEN a AND b	a와 b의 값 사이	반대인 경우 NOT BETWEEN a AND b
LIKE '비교문자열'	비교문자열에 해당하는 경우만 필터 - 'E%' : E로 시작하는 문자만 필터 - '%E%' : E를 포함하는 문자만 필터 - '%E' : E로 끝나는 문자만 필터	반대인 경우 NOT LIKE '비교문자열'
IN (리스트)	리스트 내의 값	반대인 경우  NOT IN (리스트) ※ 참고로, 어떤 '값' 인지로 조건을 건다면 '=' 연산자를 사용할 것. 어떤 '리스트 내의 값' 으로 조건을 걸 때는 IN 연산자를 쓴다.
IS NULL	NULL 값	반대인 경우 IS NOT NULL
=, >, <, >=, <=, <>	같음, 보다 큼, 보다 작음,  크거나 같음, 작거나 같음, 같지 않음	비교 연산자
+, -, *, /	덧셈, 뺄셈, 곱셈, 나눗셈	산술 연산자(사칙연산)

 

이 연산자들이 실전에서는 어떻게 사용되는지 예제로 바로 알아보자.

보통 이러한 연산자들은 조건으로 필터링하는 역할로 사용되므로, WHERE 절에 위치한다.

 

Q1. [MEMBER] 테이블의 [addr] 컬럼이 'SEOUL'이 아닌 값만 조회하라.

SELECT *
FROM [MEMBER]
WHERE addr <> 'seoul'

 

Q2-1. [MEMBER] 테이블의 [gender]가 'man'이고 [ageband]가 20인 값만 조회하라.

SELECT *
FROM [MEMBER]
WHERE gender = 'man' AND ageband = 20

 

Q2-2. [MEMBER] 테이블의 [gender]가 'man'이고 [ageband]가 20인 값과, 또는 [addr]이 'seoul'인 값만 조회하라.

(참고로, 이 경우 연산자 구분이 명확하게 되도록 괄호를 씌워주어야 한다.)

SELECT *
FROM [MEMBER]
WHERE (gender = 'man' AND ageband = 20) OR addr = 'seoul'

 

Q3. [MEMBER] 테이블에서 [ageband]가 20~40인 값만 조회하라.

SELECT *
FROM [MEMBER] 
WHERE ageband BETWEEN 20 AND 40

 

Q4. [MEMBER] 테이블에서 [addr]이 'ae'를 포함하지 않는 값만 조회하라.

SELECT *
FROM [MEMBER]
WHERE addr NOT LIKE '%ae%'

 

Q5-1.  [ORDER] 테이블에서 [sales_amt] 열을 0.1로 곱한 열 'fees'를 만들어라.

(참고로, sales_amt*0.1엔 괄호를 씌워 주어도 되고, 안 씌워줘도 된다. 나는 복잡한 조건문을 쓸 때를 대비하여 평소에도 안전하게 괄호를 씌워주는 편이다.)

SELECT *, (sales_amt*0.1) AS fees
FROM [ORDER]

 

Q5-2. [ORDER] 테이블에서 [sales_amt]에 [sales_amt]를 0.1로 곱셈한 값을 뺄셈한 열 'Excluding_fees'를 만들어라.

(여기서 sales_amt - sales_amt*0.1 전체에 괄호를 씌우지 않을 거라면, 의미를 명확하게 해줘야 하므로 (sales_amt*0.1) 에라도 꼭 괄호를 씌워야 한다.)

SELECT *, (sales_amt - sales_amt*0.1) AS Excluding_fees
FROM [ORDER]

---

2. 자주 쓰이는 숫자형 함수 정리

이번에는 특정 열이 숫자형일 때 사용할 수 있는 숫자형 함수를 알아본다.

함수	설명
ABS()	절댓값
ROUND(숫자, m)	m의 자리까지 반올림
CEILING()	올림하여 정수로 표현
FLOOR()	내림하여 정수로 표현
POWER(m, n)	m^n (m의 n승)
SQRT(m)	루트 m (m의 제곱근)

 

바로 예제를 풀어보자. 문제에 따라 쿼리를 짜고, 그 결과가 내가 생각하는 답이 맞는지 알아보자.

 

Q1. 3.1419를 2의 자리에서 반올림하여라. (답: 3.14)

SELECT ROUND(3.1419, 2)

 

Q2. 3.1419를 올림하여 정수로 표현하여라. (답: 4)

SELECT CEILING(3.1419)

 

Q3. 3.1419를 버림하여 정수로 표현해라. (답: 3)

SELECT FLOOR(3.1419)

 

Q4. 100의 제곱근은? (답: 10)

SELECT SQRT(100)

 

Q5. 6의 2승은? (답: 36)

SELECT POWER(6,2)

---

3. 자주 쓰이는 문자형 함수 정리

이번에는 특정 열이 문자형일 때 사용할 수 있는 문자형 함수를 알아본다.

※ 주의: 문자열을 쓸 때에는 반드시 따옴표를 붙여야 함! 

함수	설명
LOWER(), UPPER()	문자열을 모두 소문자로, 대문자로
LEN()	문자열의 문자 수 반환(공백 포함)
REPLACE(문자열, a, b)	문자열의 a 문자를 모두 b로 반환
CONCAT('문자1', '문자2', ..)	문자1, 문자2,... 를 공백없이 모두 붙여 하나의 문자로 반환
SUBSTRING(문자열, m, n)	문자열의 m번째 위치에서 n개의 길이만큼 문자를 반환
STUFF(문자열, m, n, a)	문자열의 m번째 위치에서 n개 길이만큼 삭제한 후, a를 넣음
LEFT(문자열, n), RIGHT(문자열, n)	왼쪽부터 지정한 수(n)만큼 / 오른쪽부터 지정한 수(n)만큼 문자 반환
LTRIM(), RTRIM(), TRIM()	공백을 제거하여 문자를 반환 왼쪽 공백만 제거 / 오른쪽 공백만 제거 / 모두 제거
CHARINDEX(a, 문자열, n)	문자열 중에 a라는 문자가 있으면 그 위치를 반환함(없으면 0을 반환) 단, n은 검색 시작 위치를 지정함
SPACE(n)	n만큼 공백 추가

 

Q1. 'eVERly'를 모두 대문자로, 'EverLY'를 모두 소문자로 나타내라.

SELECT UPPER('eVERly')
SELECT LOWER('EverLY')

 

Q2. 'I LOVE HER.' 의 문자 길이는? (답: 11)

SELECT LEN('I LOVE HER.')

 

Q3. 'I WISH YOU WERE HERE' 문장에서 마지막 E를 제거하라.

SELECT STUFF('I WISH YOU WERE HERE', 20, 1, ' ')

 

[Q3 번외] 위의 문제를 그대로 풀되, 모두 소문자로 바꿔라.

SELECT LOWER(STUFF('I WISH YOU WERE HERE', 20, 1, ' '))

 

Q4. '저는 강남구에 살아요' 문장을 '저는 서초구에 살아요'로 바꾸어라.

SELECT REPLACE('저는 강남구에 살아요', '강남', '서초')

 

Q5. '대한', '민국', '만세'를 붙여 '대한민국만세'로 만들어라.

SELECT CONCAT('대한', '민국', '만세')

 

[Q5 번외] 띄어쓰기도 같이 해서 '대한민국 만세'로 만들어라.

SELECT CONCAT('대한', '', '민국', ' ', '만세')

 

Q6. '  엘리자베스를 위하여   ' 라는 쓸모없는 공백이 많은 문장이 있다. 이 공백을 제거하라.

SELECT TRIM('  엘리자베스를 위하여   ')

 

Q7. '서울시 동작구 흑석로 184' 에서 '동작'만 뽑아라.

SELECT SUBSTRING('서울시 동작구 흑석로 184', 5, 2)

 

Q8. '서울시 동작구 흑석로 184'에서 '서울'만 뽑아라.

SELECT LEFT('서울시 동작구 흑석로 184', 2)

 

Q9. '서울시 동작구 흑석로 184'에서 '흑석' 이라는 단어가 몇 번째에 있는가? (답: 9)

SELECT CHARINDEX('흑석','서울시 동작구 흑석로 184')

 

Q10. '딸기', '수박바' 단어를 연결하되, 중간에 공백 5글자를 추가하라. 

SELECT '딸기' + SPACE(5) + '수박바'

---

4. 자주 쓰이는 날짜형 함수 정리

이번에는 특정 열이 날짜형일 때 사용할 수 있는 날짜형 함수를 알아본다.

※ 주의: 날짜열을 쓸 때에는 반드시 따옴표를 붙여야 함! 

함수	설명
GETDATE()	현재 날짜 및 시간 출력
DATEPART(기준, 날짜열)	날짜열로부터 기준에 해당하는 값만 뽑음 ※ 기준 종류 - YEAR, MONTH, DAY : 연, 월, 일 - HH, MI, SS: 시, 분, 초 - DW: 날짜(일요일을 1로 봄)
DATEADD(기준, n, 날짜열)	날짜열로부터 기준에 해당하는 값을 n 만큼 옮긴 날짜를 출력
DATEDIFF(기준, 날짜열a, 날짜열b)	날짜열a와 날짜열b 간 차이를 기준에 맞게 출력

 

Q1. 현재 날짜를 출력하라.

SELECT GETDATE()

 

Q2. 2022-05-26 19:00:07 날짜의 연, 월, 일, 시, 분, 초, 요일을 뽑아라.

SELECT DATEPART(YEAR, '2022-05-26 19:00:07')
SELECT DATEPART(MONTH, '2022-05-26 19:00:07')
SELECT DATEPART(DAY, '2022-05-26 19:00:07')
SELECT DATEPART(HH, '2022-05-26 19:00:07')
SELECT DATEPART(MI, '2022-05-26 19:00:07')
SELECT DATEPART(SS, '2022-05-26 19:00:07')
SELECT DATEPART(DW, '2022-05-26 19:00:07')

 

참고로, 연, 월, 일은 DATEPART가 아니라 그냥 YEAR, MONTH, DAY 함수도 있으니 참고하자! (결과는 똑같다)

SELECT YEAR('2022-05-26 19:00:07')
SELECT MONTH('2022-05-26 19:00:07')
SELECT DAY('2022-05-26 19:00:07')

 

Q3. 특정 날짜 2개 '2022-04-19 05:22:11' 과 '2022-05-26 19:00:07' 날짜 사이의 간격을 알고자 한다.

날짜 간 차이가 얼마인지 연, 월, 일, 시, 분, 초로 알아보자.

SELECT DATEDIFF(YEAR, '2022-04-19 05:22:11', '2022-05-26 19:00:07')
SELECT DATEDIFF(MONTH, '2022-04-19 05:22:11', '2022-05-26 19:00:07')
SELECT DATEDIFF(DAY, '2022-04-19 05:22:11', '2022-05-26 19:00:07')

SELECT DATEDIFF(HH, '2022-04-19 05:22:11', '2022-05-26 19:00:07')
SELECT DATEDIFF(MI, '2022-04-19 05:22:11', '2022-05-26 19:00:07')
SELECT DATEDIFF(SS, '2022-04-19 05:22:11', '2022-05-26 19:00:07')

 

Q4-1. '2022-05-26'에 연인을 사귀었다. 100일 뒤 날짜는?

SELECT DATEADD(DAY, 100, '2022-05-26')

 

Q4-2. 그럼 8개월 전의 날짜는?

SELECT DATEADD(MONTH, -8, '2022-05-26')

---

5. 기타 함수(CAST, ISNULL, NULLIF)

함수	설명	비고
CAST(특정열 AS 기준)	특정열의 데이터 형식을 '기준'으로 변환	※ 기준: 데이터 형식을 의미 INT, REAL, DATE, VARCHAR 등
ISNULL(특정열, 지정값)	결측치 처리: 특정열에 NULL이 있는 경우 모두 0으로 변환하여 반환	만일 NULL이 없는경우, 수정없이 특정열을 그대로 반환함
NULLIF(특정열a, 특정열b)	특정열a와 특정열b가 같으면 NULL을, 다르면 특정열a만 반환

 

바로 예제로 알아보자!

 

Q1. 특정 컬럼이 문자로 되어있다. 이를 'INT'로 변경하고 싶다면?

SELECT CAST('0' AS INT)

 

위와 같이 숫자 0으로 바뀌었다. 

('0'도 숫자인 거 아닌가요? → 아니다. 숫자처럼 보이지만 따옴표가 붙어있으면 문자로 인식함)

 

 

Q2. 날짜가 DATETIME으로 되어있는데 DATE로 바꾸고 싶다면?

SELECT CAST('2022-05-26 14:59:38' AS DATE)

 

DATE는 연-월-일만 인식하는 반면, DATETIME은 연-월-일 시:분:초까지 인식한다. 

그래서 위처럼 DATETIME으로 된 시간을 DATE로 형변환할 경우, '2022-05-26' 으로 연-월-일만 출력된다.

 

 

Q3. NULL이 있으면 모두 0으로 변환하시오.(결측치 변환)

SELECT ISNULL(NULL, 0)

 

보통은 NULL 자리에 특정 열을 넣는다. 여기선 예시이므로 이 자리에 그냥 NULL을 넣었고, 이 값이 0으로 잘 바뀐 것을 볼 수 있다.

 

 

Q4. 두 개의 열이 같으면 NULL을 반환해라.

SELECT NULLIF('A', 'A')

 

NULLIF를 사용하면 된다. 두 열은 같으므로 NULL이 반환되었다.

 

---

참고로, 코드는 여기서 'chapter4_자주쓰는 SQL함수.sql'를 다운받으면 됩니다 :)

GitHub - suy379/SQL-basic