[Oracle] SQL BASICS (Phrase, 날짜, 변환 함수)

1) Select + column - From + table as 별칭 (Oracle)

: 기본적으로 전체 레코드를 조사함 

중복된 데이터를 한번만 보겠다 --> distinct 활용

 

2) Select + column - From + table - Where (행 선택 조건식)

:  비교연산자(=,<,>,!=,<>,^=), 범위 (between A and B), 동등연산자 (in + 값)

  like + wild연산자, escape sth, And /or / not, is null

 

**SQL에서는 LIKE %로 인해서 %가 나머지가 아니라, WILD카드로 인식됨.  따라서 나머지는 MOD함수를 이용할 것

 

example.

  WHERE loc like '%@%%' ESCAPE '@'

예시 출처: 그루비 강좌

 

3) (정렬) Slect + column - From + table - Where + condition - Order by + column + ASC/DESC

: 다중 정렬의 경우는 ORDER BY A1 DESC, B1 이런식으로 다중 조건의 정렬이 가능함

 

4) Fuction 함수

4.1 단일행 함수 (Single-row)

: 함수가 행단위로 적용됨

 

4.2 다중행 함수 (Aggregate)

단일행 그룹핑은 문자/숫자/날짜 등 데이터 타입 관련이며 행 단위 적용됨

명시적으로 묶이는 것은 ~별 (성별, 학년별, 부서별 등)

Group By + column

---

[Function Summary]

INITCAP( ) : Capitalize the first letter of the word (apple -> Apple)

example) SELECT INITCAP('Sth') FROM dual

#dual is dummy table 

 

Upper( ) : Capitalize every letter of the word, opposite function is LOWER()

** WHERE UPPER(NAME) == 'ACE'

 

SYSDATE : Function that brings current date

# SYSDATE + 1 처럼 날짜 연산 가능

# TO_CHAR(SYSDATE,'yyyy-mm') 처럼 형태변환 가능

 

CONCAT(1,2) : Function that connects two string into one string

CONCAT('A','B') -> 'AB'

or 연결 연산자로 'A' || 'B' 로 표현 가능

 

LENGTH( ) : Returns number value of length of the character

 

INSTR(표현식,검색값,[M,N]) : Returns index of the certain letter

(M은 시작 위치값, N은 몇 번째로 나오는 문자를 검색할지 정해줌)

문자가 없을 시 0이 반환됨 (일반적으로 다른 언어는 -1을 반환하지만 SQL은 0을 반환함)

** SQL에서 인덱스는 1부터 시작함

 

SUBSTR(표현식,M [,N]) : Extract partial string

(M은 시작 위치값, N은 반환 개수) N이 없으면 전체를 반환함

SUBSTR('1234',2) - > '234'

 

REPLACE(표현식, 이전 문자열, 새로운 문자열) : Replace original string into other string

REPLACE("JACK","J","A") -> AACK

 

LPAD(표현식,N,'STR') : Fill in the empty location within N letters with string from left side

N은 자릿수를 의미함, STR은 삽입할 문자 의미 (오른쪽부터 채우려면 RPAD)

SELECT LPAD('ABC',5,'*') -> **ABC

 

LTRIM(표현식, STR) : Delete the particular string

LTRIM('MILLER','M') -> ILLER

*연속된 중복값은 삭제됨, 특정 문자열을 계속 없애다가 막히면 스탑함

LTRIM('MMMILLER','M') -> ILLER

LTRIM('MILMER','M') -> ILMER

 

TRIM(LEADING 'STR' FROM COLUMN) == LTRIM

TRIM(TRAILING 'STR' FROM COLUMN) == RTRIM

TRIM(BOTH 'STR' FROM COLUMN) 

 

SELECT TRIM(LEADING '0' FROM '0012300') -> 123000

SELECT TRIM(TRAILING '0' FROM '0012300') -> 000123

SELECT TRIM(BOTH '0' FROM '0012300') -> 123

 

TRUNC(숫자, 자리수) : Truncate the number. 숫자를 절사한다!

TRUNC(412.65,-1) -> 410

 

MOD(나눌값, 나누는 값) : 나머지를 구함

단, MOD(10,0) -> 타언어는 에러가 나지만 SQL은 오히려 나누는 값 10 을 반환함

 

CEIL(표현식) : 소수점을 가진 실수값을 같거나 큰 최대 정수값으로 반환 (<-> FLOOR)

CEIL(10.6) -> 11

CEIL(-10.6) -> -10 (음수값도 가능함, -11보다 -10이 크기 때문에 -10 반환)

 

SIGN( ) : Determines if the value is positive or negative (양수1, 음수 -1, 0은 0)

SIGN(100) -> 1

SING(-100) -> -1

SIGN(0) -> 0

 

 

---

• 날짜 함수

SYSDATE : Return the current date

**주의! 괄호가 없고 SYSDATE 자체가 함수임

SYSTIMESTAMP는 분/초 단위까지 보여주는 차이점 존재

오라클의 기본 날짜 형식은 RR/MM/DD이기 때문에 22/09/07 이런식으로 출력됨

30/09/07일 때, 오라클의 년도 타입 YY는 무조건 현재의 세기를 따라가서 2030년으로 인식함

RR은 지정날짜와 현재날짜 중 자동화함 

 

MONTHS_BETWEEN(DATE1,DATE2) : Automatically return how many months are between dates

 

ADD_MONTHS(DATE, N) : Add or substract months from date

ADD_MONTHS(SYSDATE,3)

ADD_MONTHS(SYSDATE,-2)

 

NEXT_DAY(DATE, 요일): 지정된 날짜로부터 앞으로 돌아올 지정 요일이 가장 가까운 날을 반환

NEXT_DAY(SYSDATE,'금')

'월요일', '화요일', '수요일' 등등 완전 단어로 표현 가능 

일요일은 1, 월요일은 2, 화요일은 3, 수요일은 4, 목요일은 5, 금요일은 6, 토요일은 7로 표현 가능

 

LAST_DAY(DATE) : 해당 날짜가 속한 달의 마지막 날짜를 반환

 

ROUND(DATE,'YEAR'/'MONTH') : 연도나 월을 반올림 하는 함수

*연도가 올라가면 월과 일은 01/01 새해 날짜로 바뀜

*월도 업데이트 시에 일 날짜가 1일로 초기화 됨

 

 

---

• 변환 함수

: DATA 종류가 변경되는 함수를 의미함

 

to_char(변화대상, 포맷) : 숫자/날짜를 문자열로 포맷팅

포맷 문자	의미
YYYY	4자리 연도
YY	2자리 연도
MM	월을 숫자로 표현
MON	월을 알파벳 표현
DAY	요일 표현
DY	요일을 약어로 표현
DD	일을 숫자로 표현
AM  or  PM	AM PM 포맷
A.M  or   P.M	A.M P.M 포맷
HH  or  HH12	12시간 단위 시간 표현
HH24	24시간 기준 시간 표현
MI	분으로 표현
SS	초 단위로 표현

**주의**

SELECT TO_CHAR(SYSDATE,'YYYY "년" MM "월" DD "일" ')

우리나라에서 일반적인 년월일 단위 표현 시, 쌍따옴표 " " 를 이용해서 문자열을 내부에 표기함

 

 

[숫자를 문자로 바꿀 때 포맷 바꾸는 법]

숫자형식	의미
9	한자리 숫자
0	앞부분을 0으로 표현
$	달러 기호 앞 표현
.	소수점
,	특정 위치에 , 표현
B	공백을 0으로 표현
L	지역통화 (우리나라는 원화)

TO_CHAR(PRICE,'$999,999')

 

to_number()  : 숫자 형태  문자열로 숫자로 변환 (연산 목적)

SELECT TO_NUMBER('123,456') -> ERROR 발생! 숫자형태의 문자열이 아니기 때문임

SELECT TO_NUMBER('123,456','999,999') ->123456

 

to_date() : 날짜 형태 문자열을 날짜 데이터로 변환

TO_DATE('20220907'.'YYYYMMDD')

 

 

 

<기본적인 파라미터의 설정값을 알아보기 위한 SQL문>

SELECT *

FROM NLS_SESSION_PARAMETERS

 

<날쩌 파라미터의 설정값을 RR형태에서 변경한S SQL문>

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MM:SS'

 

< Reference Website >

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Functions.html#GUID-D079EFD3-C683-441F-977E-2C9503089982

SQL Language Reference