[SQL] SQL 고급문법 - 내장함수

안녕하세요 과외선생J 입니다. 

 

앞으로 몇차례에 걸쳐서 SQL 고급문법에 대해서 공부해보고자 합니다. 

 

* 본 포스팅은 중부기술교육원 수업과 한빛미디어 데이터베이스 개론과 실습 책을 기본바탕으로 공부한 뒤 작성되었음을 알립니다. 

 

* 교재의 예제 위주로 이해를 돕기 위해 각 문법에 대한 해설을 달았습니다. 

 

[SQL 내장함수]

내장함수란 DBMS에서 제공하는 함수로 특정 값이나 열의 값을 입력 받아서 계산하여 결과값을 보여줍니다. 즉, 수학에서 배운 함수와 동일한 기능을 하는 녀석인데 DBMS에서 미리 함수를 만들어놔서 저희는 가져다 쓰기만 하면 된다는 이야기 입니다. 

앞 포스팅에서 공부하시던 중 나왔던 SUM, AVG 등이 내장함수에 속합니다. 

내장함수를 쓸 수 있는 위치는 SELECT, WHERE, UPDATE 등 모든 절에서 사용이 가능합니다. 

 

종류는 크게 3가지(단일행, 집계, 분석함수)로 나뉘게 되며 특히 단일행 함수에는 숫자, 문자, 날짜시간, 변환, 인코딩,디코딩, NULL 관련 함수들이 있습니다. 

 

그 중 사용 빈도가 높은 것들만 한번 다뤄보도록 하겠습니다. 

 

 

1. 숫자함수

  숫자 계산에 관련한 함수로 SQL문에서는 +, -, /, * (사칙연산)과 나머지 계산(%) 기호를 쓰면 됩니다. 그 외에 몇가지를 내장함수로 제공하고 있습니다. 

 

1) ABS함수 : 절대값을 계산

사용 예시 ) SELECT ABS(-78) FROM DUAL;

-> DUAL 테이블에서 -78의 절대값을 계산하여 나타내라는 의미입니다. 

-> DUAL 테이블은 가상의 테이블로 연산작업을 하기 위해 일시적으로 생성이 되는 테이블입니다. 

 

2) ROUND 함수 : 소수 계산 시 반올림

ROUND( 숫자, 반올림할 자리) 로 사용이 됩니다. 

사용 예시) SELECT ROUND(4.1234, 2) FROM DUAL;

-> 4.1234 라는 소수를 소수 둘째자리까지 반올림을 하라는 의미입니다.

-> 결과는 4.12 로 나오게 됩니다.  

 

3) 숫자함수의 연산

 숫자함수를 사용할 시 입력값을 숫자뿐 아니라 열이름도 사용이 가능합니다. 예를 들어 위에서 ABS를 쓸 때 

ABS(saleprice) 라고 하여도 계산이 된다는 의미입니다. 

 또한 함수를 여러개를 중첩하여 사용할 수도 있습니다. 

 계속 봐왔던 마당서점의 예시에서 saleprice의 평균금액을 1000원 단위로 반올림한다고 할 경우 다음과 같이 작성할 수 있습니다. 

ROUND ( AVG (SALEPRICE) , -3 )

사용 예시) SELECT ROUND ( AVG (SALEPRICE) , -3 ) FROM Orders ;

-> ROUND 함수 사용 시 반올림할 자리가 소수점 아래자리일 경우 첫째자리부터 1,2,3,4 로 증가하며 소수점 윗자리일 경우 1의 자리는 -0, 10의 자리는 -1, 100의 자리는 - 2으로 음수값으로 증가합니다. 

 

 

 

2. 문자함수

  CHAR 나 VARCHAR2 데이터 타입을 가공하는 함수 입니다. 그냥 문자와 관련된 함수라고 생각하시면 맘편합니다. 

매~우 많은 내장함수가 있으나 그 중 딱 3가지만 보겠습니다. 

 

  1) REPLACE 함수 

  REPLACE : 대체하다, 교환하다 등의 의미로 문자열을 치환하는 함수입니다. 

사용 방법은 REPLACE ( 문자열이 속해있을 속성이나 문자열, 변경 전 문자, 변경 후 문자열 ) 로 쓰면 됩니다. 

문자열을 넣으실 때는 작은따옴표를 사용하셔서 입력해주셔야 합니다. 

 

사용 예시)

REPLACE ( bookname, '농구', '축구') 

-> bookname 에서 농구를 축구로 치환합니다. 

 

REPLACE ( '중부기술학원', '학', '교육' )

-> 중부기술학원이 중부기술교육원으로 치환이 됩니다. 

 

2) LENGTH 함수

 글자수를 세어 주는 함수입니다. ( 사실 DATABASE 에서 글자수를 셀 일이 많나 싶긴합니다.. 초보라그런가...)

주의하실 점은 세는 단위가 바이트가 아닌 문자단위입니다. 즉 결과를 바이트(BYTE) 단위가 아닌 문자단위로 반환해주며 글자수가 아닌 글자의 바이트를 확인하고 싶을 경우 LENGTHB 함수를 사용하시면 됩니다. 그리고 띄워쓰기된 부분도 하나의 문자로 인식합니다. 

사용 방법 : LENGTH(문자열 또는 속성이름)

사용 예시 : SELECT LEGNTH(bookname) FROM book;

-> book테이블에서 책이름의 글자수를 반환하게 됩니다. 

-> '축구의 역사' 인 경우 출력값이 5로 출력이 됩니다. 

 

 

3) SUBSTAR 함수 

 특정위치에서부터 원하는 길이만큼의 문자열을 반환하는 함수입니다. 

 

사용 방법 : SUBSTAR( 문자열 또는 속성이름, 시작위치, 길이 )

사용 예시 : SELECT SUBSTAR(name, 1, 1) from customer;

-> name 속성에서 1번째 글자부터 1개만 가져오게 됩니다. 

 

3. 날짜 시간 함수

날짜와 시간에 대한 함수이며 계산을 도와주기도 합니다. -,+ 를 이용하여 날짜 계산을 지원합니다. 

 

1) TO_DATE, TO_CHAR 함수

TO_DATE는 문자형을 날짜형으로, TO_CHAR은 날짜형을 문자형으로 바꿔주는 함수입니다. 

사용법: TO_DATE(속성, '형태')

예시 : SELECT to_date(orderdate, 'yyyy-mm-dd') from orders;

 

 

2) SYSDATE 함수, SYSTIMESTAMP 함수

 현재 시간과 날짜를 보여주는 함수입니다.

사용법 : SYSDATE 

사용예시 : SELECT SYSDATE, 'yyyy/mm/dd';

 

 

[NULL 값 처리]

 null : 미처리 값, 미지정값으로 0이나 없음, 공백, 빈문자 등과는 다릅니다. 

미지정값이므로 사칙연산도 안되고 비교연산자로 비교도 안됩니다. 

 

1.  NULL 값에 대한 연산과 집계함수 

    - null+숫자 = null

    - 집계 함수 사용 시 null은 제외

    - 집계 함수 사용 시 해당 행이 없을 경우 sum, avg은 null, count 값은 0

 

2. NULL 값을 확인하는 방법 - IS NULL, IS NOT NULL

 where절을 통해 조건 제시 시 null은 null이라고 명명해야 옳바른 값이 산출된다. 

 

3. NVL 함수 

 null 값을 임의의 다른 변경~! 

사용 방법 : NVL( 속성, 값)

사용 예시 : SELECT name, NVM(phone, '연락처 없음') FROM Customer;

-> phone 열 중에서 null값이 있다면 연락처 없음으로 변경.

 

 

[ROWNUM]

 - 내장함수는 아닙니다. 다만 자주쓰는 문법이기에 같이 포스팅합니다. 

 - 자료 일부분만 확인 시 유용한 문법

 - 조회 결과의 순번을 나타내는 함수

 - 일반적으로 생각하는 순서와 오라클의 처리순서가 다르기에 원하는 결과값이 나오지 않을 수 있으므로 주의해서 사용해야합니다. 

 

* 오라클 select 문 처리 순서

 - from절 -> where절(조건) -> select문 -> order by, group by 

 

 

정리하는데 생각보다 시간이 걸리네요 ㅠㅠ 다른 부분들도 빨리 정리해서 올려보도록 하겠습니다.

 

공부하신다고 고생하셨습니다~!