[SQL] 산술 연산자, 함수

📁 산술 연산자

• 수학에서 사용하는 사칙연산의 기능을 가진 연산자

연산자	의 미	우선순위
()	괄호로 우선순위 조정	1
*	곱하기	2
/	나누기(0으로 나눌 경우 NULL)	2
+	더하기	3
-	빼기	3
%	나머지(0으로 나눌 경우 NULL)	3

• ex)

SELECT 10+5, 10-5, 10/5, 10%5, 10/0, 10%0;
SELECT 10+5 AS A, 10-5, 10/5, 10%5, 10/0, 10%0;  -- AS 로 컬럼명 정하기
SELECT 1+2*3 AS R1, (1+2)*3 AS R2; -- 연산 우선 순위 정하기

• ex) 상품 별 오프라인 매출 구하기

SELECT P.PRODUCT_CODE, SUM(S.SALES_AMOUNT*P.PRICE) FROM PRODUCT P
JOIN OFFLINE_SALE S ON P.PRODUCT_ID=S.PRODUCT_ID
GROUP BY P.PRODUCT_CODE
ORDER BY SUM(S.SALES_AMOUNT*P.PRICE) DESC, P.PRODUCT_CODE;

📁 함수

<문자 함수>

• ASCII(문자열)
  • 문자열의 가장 왼쪽 문자의 아스키 코드 값을 반환하는 함수
  • ASCII코드는 총 128개의 문자를 숫자로 표현할 수 있도록 정의해 놓은 코드
    

SELECT ASCII("A"); --> 65
# 반대로 ASCII코드를 문자열로 반하는 함수는 char()
SELECT char(65); --> A

• LOWER(문자열)
  • 문자열을 소문자로 변환해주는 함수

SELECT LOWER('ABC'); 

• UPPER(문자열)
  • 문자열을 대문자로 변환해주는 함수

SELECT UPPER('abc');

• LTRIM(문자열)
  • 문자열의 왼쪽 공백 제거

SELECT LTRIM("  ABC  ");

• RTRIM(문자열)
  • 문자열의 왼쪽과 오른쪽 공백제거

SELECT TRIM("  ABC  ");

• SUBSTR(문자열, 시작점 [,길이]) *[]는 옵션
  • 문자열의 원하는 부분만 잘라서 반환. 길이를 명시하지 않았을 경우 문자열의 시작점부터 끝까지 반환
  • 그 외 SUBSTRING_INDEX(), LEFT(), RIGHT()도 있음

SELECT SUBSTR("ABCDEF", 2,3);
SELECT SUBSTR("ABCDEF", 2);

• LENGTH(문자열)
  • 문자열의 길이 반환

SELECT LENGTH ("ABCDEF");

• REPLACE(문자열, 변경 전 문자열, 변경 후 문자열)
  • 문자열에서 변경 전 문자열을 찾아 변경 후 문자열로 바꿔주는 함수

SELECT REPLACE ("안녕하세요", "하세요", "ㅎㅎ"); --> 안녕ㅎㅎ

• LPAD(문자열, 길이, 문자)
  • 문자열이 설정한 길이가 될 때까지 왼쪽을 특정 문자로 채우는 함수

SELECT LPAD ("123", 5, "0"); --> 00123

• CONCAT(문자열1, 문자열2 [, 문자열3, …])
  • 둘 이상의 문자열을 하나로 합쳐서 반환해주는 함수

SELECT CONCAT("A", "b", "C");	 --> AbC

• CONCAT_WS(구분값, 문자열1, 문자열2 [, 문자열3])
  • 둘 이상의 문자열을 하나로 합쳐서 반환해주는 함수. 문자 사이에 구분 값을 넣어서 반환 해줌

SELECT CONCAT_WS(",", "A", "b", "C");	 --> A,b,C

# 각 컬럼의 값을 ","을 기준으로 합쳐서 하나의 컬럼의 값 처럼 사용하고 싶을 때 사용 

<숫자 함수>

• ABS(수)
  • 수의 절댓값을 반환해주는 함수

SELECT ABS(-1)

• SIGN(수)
  • 수의 부호를 반환해주는 함수
  • 양수면 1, 음수면 -1, 0이면 0을 반환

SELECT SIGN(-10);

• ROUND(수 [, 자릿수])

# 수를 지정된 소수점 자릿수까지 반올림하여 반환해 주는 함수
SELECT ROUND(156.123, 2); #출력 : 156.1`2`

# 자릿수를 명시하지 않았을 경우 기본값은 0
SELECT ROUND(156.123); #출력 : 15`6`

#  자릿수가 음수일 경우 지정된 정수부를 반올림하여 반환
SELECT ROUND(156123, -2); #출력 : 156`1`00

• TRUNCATE(수, 자릿수)

# 수를 지정된 소수점 자릿수까지 버림하여 반환해주는 함수
SELECT TRUNCATE(156.987, 2); #출력 : 156.9`8`

# 자릿수를 명시하지 않았을 경우 문법 오류가 남
SELECT TRUNCATE(156.987);

# 자릿수가 음수일 경우 지정된 정수부에서 버림하여 반환
SELECT TRUNCATE(156987, -2) #출력 : 156900

• CEIL(수)
  • 소수점 이하의 수를 올림한 정수를 반환해주는 함수
  • 자릿수를 지정할 필요 없음

SELECT CEIL(156.12) #출력 : 157
SELECT CEIL(-156.12) #출력 : -156

• FLOOR(수)
  • 소수점 이하의 수를 버림한 정수를 반환해 주는 함수
  • 자릿수를 지정할 필요 없음

SELECT FLOOR(156.12) #출력 : 156
SELECT FLOOR(-156.12) #출력 : 157

• MOD(수1, 수2)

# 수1을 수2로 나눈 ‘나머지’를 반환해주는 함수
SELECT MOD(33, 10) #출력 : 3

# 단, 수2가 0일 경우 NULL 반환
SELECT MOD(33, 0) #출력 : NULL

<날짜 함수>

• SYSDATE()

# 현재의 연, 월, 일, 시, 분, 초를 반환해주는 함수
SELECT sysdate();

  - SELECT NOW()도 사용 가능
  - now()는 쿼리가 실행되는 시간반환, sysdate는 해당 함수가 호출되는 시점을 반환
  - 예를 들어 sleep함수를 이용해 임의로 5초를 지연시켰을 때 now()는 쿼리가 실행된 시간을 반환하지만, sysdate()는 쿼리 실행 시점에서 +5초가 된 시간을 반환함

• EXTRACE(특정단위 FROM 날짜데이터)
  • 대표적으로 사용할 수 있는 단위: YEAR, MONTH, DAY, HOUR, MINUTE, SECOUND

SELECT extract(YEAR from sysdate()); 

• DATE_ADD(기준날짜, INTERVAL)
  • 기준 날짜에서 간격만큼을 더함.

# 현재 시간에 한 시간 더하기
SELECT DATE_ADD(SYSDATE(), INTERVAL 1 HOUR);

• DATE_SUB(기준날짜, INTERVAL)
  • 기준 날짜에 간격만큼을 뺌

# 현재 시간에 한 시간 빼기
SELECT DATE_sub(SYSDATE(), INTERVAL 1 HOUR);

SELECT DATE_ADD(SYSDATE(), INTERVAL -1 HOUR); 와 동일

<NULL 관련 함수>

• IFNULL(인수1, 인수2)
  • 첫번째 인수가 NULL이 아니면 첫번째 인수를 반환하고, 첫번재 인수가 NULL이면 두번째 인수를 반환

SELECT IFNULL("1", "2"); --> 1
SELECT IFNULL(NULL, "2"); --> 2

• NULLIF(인수1, 인수2)
  • 인수1과 인수2가 같으면 NULL을 반환하고, 다르면 인수1 반환

SELECT NULLIF("A", "A"); --> NULL
SELECT NULLIF("A", "B"); --> "A"

• COALESCE(인수1, 인수2, …)
  • NULL이 아닌 최초의 인수를 반환

SELECT COALESCE(NULL, NULL, 1, 100); --> 1