SQL 기초(4) 함수들

sql에서 쿼리문을 작성하면서 사용했던 함수를 정리해 보려고 한다.

 

나는 SQL을 하면서 생각보다 다른 것을 할 때 보다 좀 더 편하게 느껴졌다.

이미 있는 데이터를 다루는 것도 있고, 논리를 생각하면서 작성하면 생각보다 편하게 쿼리문을 작성할 수 있었다.

 

-- 2005년도에 회원이 주문한 데이터에 대하여, 전체 거래처별 매출금액의 총합을 조회하려고 합니다. 

-- 조회컬럼 : 거래처코드, 거래처명, 총매출금액

-- 정렬 : 총매출금액 기준 내림차순

SELECT buyer_id , buyer_name, SUM(cart_qty * prod_sale)AS total
FROM buyer 
	LEFT OUTER JOIN prod 
	ON(buyer_id = prod_buyer)
	LEFT OUTER JOIN cart 
	on(cart_prod = prod_id
	AND cart_no LIKE '2005%')
GROUP BY buyer_id, buyer_name
ORDER BY total DESC;

위의 코드는 조건에 따라 컬럼을 조회하는 쿼리문이다.

2005년에 회원이 주문했던 데이터이며, 전체 거래처별 매출금액의 총합을 조회한다.

함수 사용을 보도록 하자 우선 select절에 사용한 sum()함수이다.

 

다른 곳과 비슷하게 총합을 구해준다.

위에 쿼리문에서는 물건을 구매 시 카트에 이력이 남는다.

cart_qty는 수량이다.

prod_sale은 물품의 판매가격이다.

 

총매출금액은 각 물품의 가격과 수량을 곱하면 총금액이 도출된다.

그리고 이것의 별칭은 total로 했다.

 

아래에서는 like를 볼 수 있는데,

이것은 문자열 내에 포함을 가리킨다.

cart_no은 주문번호로 처음부터 8자리가 날짜를 담고 있다.

 

이것으로 2005년도에 구매한 물품을 알 수 있다.

 

like는 '' 안에 %를 가지고 사용한다.

cart_no like '2005%'
이것은 2005로 시작되는 문자열을 지칭한다.

cart_no like '%2005'
이건 2005로 끝나는 문자열을 지칭한다.

cart_no like '%2005%'
이건 문자열 내에 2005가 포함되어있는것을 지칭한다.

 

비슷하게 사용할 수 있는 함수는 substring이 있다.

사용법은 아래와 같다.

substring(cart_no, 1, 4) = 2005

이건 cart_no의 첫 번째 자리부터 4자리가  2005인 cart_no의 데이터를 지칭한다.

 

나는 like에 손이 더 많이 갔다.

길기도 하고 자릿수도 확인해야 해서 정확한 자리에 문자열을 찾고 싶을 때만 사용했다.

 

 

최소와 최대

MIN()과 MAX()는 이름만 보아도 사용법이 보인다.

min()은 최솟값을 조회하고, max()는 최댓값을 조회한다.

 

포함

IN 이 친구도 이름만 봐도 알 수 있을 것이다.

SELECT prod_name , prod_sale
from prod 
WHERE prod_sale in(150000, 170000, 330000);

위 와 같은 형태로 사용하는데,

prod_name과 prod_sale 컬럼을 조회하는데 조건이 prod_sale이 15만 ,17만, 33만에 포함되는 것만을 조회한다.

셋 중에 하나라도 포함되면 조건이 참이 되어 조회한다,

 

문자열 변환

LOWER()와 UPPER() 도 이름만 보아도 알 것 같은 함수들이다.

lower는 소문자로 변환해 주고, upper는 대문자로 변환해 준다.

 

공백제거

TRIM() 함수는 공백을 제거해 준다

SELECT '    안녕', LTRIM('     왼쪽공백제거'),
		'오른쪽 공백     ' ,RTRIM('오른쪽 공백 제거       '),
		'    좌우 공백       ', TRIM('      좌우 공백 제거        ') ;

앞에 L이 붙으면 좌측의 공백을 제거해 주고, R이 붙으면 오른쪽의 공백을 제거해 준다.

그냥 TRIM으로 사용하면 좌우 공백을 모두 제거해 준다.

 

 

컬럼 데이터 병합

CONCAT(mem_regno1,'-',mem_regno2)

mem_regno1은 주민번호 앞자리 , 2는 뒷자리이다.

따로 컬럼이 되어있어 병합하기 위해 concat함수를 사용했다.

 

컬럼의 사이에 문자열도 넣을 수 있다.

중간에 '-'은 두 컬럼을 합쳤을때도 가독성이 좋게하기 위해 사용했다.

 

 

특정 자릿수까지 문자 추출

-- mysql , mariadb 용
SELECT LEFT(prod_id, 4), RIGHT(prod_id, 6)
FROM prod ;

left와 right를 사용하여 원하는 자릿수만큼 조회할 수 있다.

prod_id를 왼쪽에서부터 4자리 , 오른쪽에서부터 6자리 를 각각  조회한다.

 

다른 언어들과 비슷하게 사용하다 보니 다 아는 얼굴들이었다.

 

부정을 사용하고 싶을 때는 함수 앞에 NOT을 붙여 사용한다.

like가 포함되는 문자열을 지칭했다면 not like는 포함되지 않는 것을 지칭한다.

이처럼 함수 앞에 not을 붙여 사용하면 부정의 의미가 된다.

 

 

생각보다 익숙하던 함수들 이어서 잠깐 생각해 보면 쉽게 사용할 수 있다.