임시테이블 DUAL
SELECT 'HELLO' FROM DUAL;
DUAL은 임시테이블로 값을 확인하는 용도로 쓴다. 새로운 테이블이나 데이터를 만들지 않아도 값을 확인하기에 편리하다. 이 경우에는 HELLO를 확인할 수 있다.
SQL 함수
문자열 대소문자 변경하는 함수
INITCAP : 단어별 첫글자가 알파벳일 때 대문자로 변경
SELECT INITCAP('hello hello') FROM DUAL; --결과값 : Hello Hello
INITCAP을 이용하여 영어 단어 첫글자를 대문자로 변경했다. Hello Hello가 출력된 테이블을 확인할 수 있다.
LOWER : 알파벳을 전부 소문자로 변경
UPPER : 알파벳을 전부 대문자로 변경
SELECT LOWER('Hello World') FROM DUAL; --결과값 : hello world
SELECT UPPER('Hello World') FROM DUAL; --결과값 : HELLO WORLD
LOWER을 한 결과는 hello world, UPPER을 한 경우는 HELLO WORLD가 된 것을 확인할 수 있다.
글자 개수 및 바이트 수를 구하는 함수
LENGTH : 글자 개수 구하는 함수
SELECT LENGTH('HELLO'), LENGTH('안녕하세요') FROM DUAL; --결과값 : 5,5
HELLO, 안녕하세요 모두 5글자이다.
LENGTHB : 글자 바이트 수 구하는 함수
SELECT LENGTHB('HELLO'), LENGTHB('안녕하세요') FROM DUAL; --결과값 : 5, 15
HELLO는 5바이트, 안녕하세요는 15바이트를 확인할 수 있다. 한글은 한글자에 3바이트이다.
문자열 합치기
CONCAT : 두 문자열을 하나의 문자열로 합치기
SELECT CONCAT('HELLO','WORLD') FROM DUAL; --결과값 : HELLOWORLD
HELLOWORLD로 합쳐진 결과를 확인할 수 있다.
EX. 전화번호 데이터 '010' '1111' '1234' 를 하나의 문자열로 합치기
SELECT CONCAT(CONCAT('010','1111'),'1234') FROM DUAL; --결과값 : 01011111234
CONCAT은 두개의 문자열을 합치는 함수임을 주의해야 한다. CONCAT(010,1111,1234) 이런식으로 문자를 3개이상 한번에 이용할 수 없다. 하나씩 합쳐주는 방식으로 여러 문자열을 합칠 수 있다.
연산자 ||
SELECT '010'||'1111'||'1234' FROM DUAL; --결과값 : 01011111234
함수 외에도 연산자 || 를 이용하면 문자열을 한번에 붙일 수 있다.
이 연산자 ||는 문자열 외에 숫자나 다른 데이터도 합칠 수 있다.
SELECT MAJOR_NO || MAJOR_NAME FROM MAJOR;
두 컬럼의 데이터를 한번에 모아서 확인할 수 있다.
문자열 추출
SUBSTR : 문자열 부분 추출 (문자 기준으로 추출)
SELECT SUBSTR('123456789',5,4) FROM DUAL; --결과값 : 5678
'문자열'에서 ,몇번째 문자부터, 몇글자를 추출할 것인지 입력하고 실행하면 된다. 여기서는 5번째인 5부터 네글자인 5678이 출력된다.
SELECT SUBSTR('안녕하세요',2,1) FROM DUAL; --결과값 : 녕
글자단위기 때문에 한글도 '안녕하세요'의 두번째 글자 1개 '녕'이 확인된다.
EX. 주민등록번호 '888888-1231231' 에서 뒷자리 중 첫글자를 추출
SELECT SUBSTR('888888-1231231',8,1); --결과값 :1
8번째 나오는 글자부터 1개를 추출
EX. 주민번호 뒷자리의 첫자리를 제외하고 *으로 마스킹 처리
SELECT SUBSTR('888888-1231231',1,8)||'******' FROM DUAL;
첫번째 글자부터 뒷자리의 첫글자까지 추출한뒤 || 으로 '******'문자열과 이어주면 된다.
SELECT SUBSTRB('안녕하세요',1,3) FROM DUAL; --결과값 : 안
SUBSTRB는 바이트단위로 문자를 추출할 수 있다. 알파벳을 추출할 때는 유용하지만 한글을 추출할 때는 한글 한글자가 3바이트임을 주의해야 한다. 첫번째 바이트부터 3개의 바이트를 추출했기 때문에 '안'이 출력된다.
3바이트 단위로 추출할 수 있고, 시작바이트도 1,4,7... 으로 3바이트 간격임을 주의해야 함수 이용이 가능하다.
INSTR 문자열 검색
SELECT INSTR('ABCDEFG','CD') FROM DUAL; --결과값 : 3
'ABCDEFG'에서 'CD'를 찾는다. 문자열중에 'CD'가 3번째글자에서 시작되기 때문에 위치값 3을 확인할 수 있다.
SELECT INSTR('ABCDEFG','AC') FROM DUAL; --결과값 : 0
'AC'는 해당 문자열에서 찾을 수 없기 때문에 위치값이 존재하지 않는다. 0으로 출력된다.
EX. HELLO WORLD에 공백이 있는지 체크
SELECT INSTR('HELLO WORLD',' ') FROM DUAL; --결과값 : 6
6번째글자 자리에 공백이 있기때문에 6으로 결과값이 뜬다. 만약 공백이 없다면 0으로 출력된다.
CHECK(INSTR(NAME,' ') = 0) 으로 이름에 공백이 들어가지 않도록 제약조건을 설정할 수있다.
문자열 바꾸기
REPLACE
SELECT REPLACE('ABCD','B','F') FROM DUAL; --결과값 : AFCD
'ABCD'문자열중에 'B'를 'F'로 바꾸어 주었다. AFCD가 결과값이 된다.
EX. 학생 테이블에서 이름 컬럼에 공백이 있는 학생의 이름을 공백을 지워서 업데이트
UPDATE STUDENT SET NAME = REPLACE(NAME, ' ', '') WHERE INSTR(NAME,' ') != 0 ;
컬럼 내 데이터를 수정하기 위해 UPDATE, SET을 이용했다. REPLACE 함수(), ISTR() 내에 처리할 문자열을 넣어주는 첫번째 자리에는 문자열이 직접올 수 있지만, 위와 같이 컬럼이 올 수도 있다.
PAD : 문자열의 자리수 맞추기 - RPAD, LPAD
원하는 문자열 개수만큼 남은 부분에 지정한 문자열로 채워주는 함수
SELECT RPAD('888888-1',14,'*') FROM DUAL; --결과값 : 888888-1******
원하는 글자수에 맞게끔 남는 자리수를 오른쪽에 채운다. 888888-1****** 이 된다.
SELECT LPAD('888888-1',14,'*') FROM DUAL; --결과값 : ******888888-1
원하는 글자수에 맞게끔 남는 자리수를 왼쪽에 채운다. ******888888-1 이 된다.
SELECT RPAD('ABC'10,'1234') FROM DUAL; --결과값 : ABC1234123
1234를 패턴처럼 남는 자리수만큼 찍어낸다.
RTRIM, LTRIM : 지정한 문자열을 제거
SELECT LTRIM('ABABABA','A') FROM DUAL; --결과값 : BABABA
A가 양쪽에 있지만 LTRIM으로 왼쪽의 A만 제거되었다.
SELECT LTRIM('AAABBBCCCDDDAAA','A') FROM DUAL; --결과값 : BBBCCCDDDAAA
왼쪽에서부터 A가 아닌 문자가 나올 때 까지의 연속된 A가 모두 제거되었다.
SELECT RTRIM('AAABBBCCCDDD','A') FROM DUAL; --결과값 : AAABBBCCCDDD
오른쪽 AAA만 제거 되었다.
TRIM : 좌우 공백을 제거
SELECT TRIM(' A A B ') FROM DUAL; --결과값 : A A B
LENGTH(TRIM(' A A B ')) 으로 글자수를 확인하면 5가 확인된다. 공백이 잘 제거되었음을 알 수 있다.
수학함수
ROUND : 원하는 자리수에서 반올림
SELECT ROUND(123.456,-2) FROM DUAL; -- 결과값 : 100
SELECT ROUND(123.456,-1) FROM DUAL; -- 결과값 : 120
SELECT ROUND(123.456,0) FROM DUAL; -- 결과값 : 123
SELECT ROUND(123.456,1) FROM DUAL; -- 결과값 : 123.5
SELECT ROUND(123.456,2) FROM DUAL; -- 결과값 : 123.46
자리수를 판단하기 위해 여러 명령을 실행했다.
자리수 | -3 | -2 | -1 | 0 | 1 | 2 | |
값 | 1 | 2 | 3 | . | 4 | 5 | 6 |
소수점 첫번째 자리수가 0이고, 이처럼 자리수가 표현된다.
ROUND(123.456,-2)는 십의자리(-2)에서 반올림,
ROUND(123.456,0)은 소수점 첫번째자리(0)에서 반올림,
ROUND(123.456,2)은 소수점 셋째자리(2)에서 반올림 한것이다.
TRUNC : 원하는 자리수에서 데이터 자름, 소수점 잘라낼 때 사용
SELECT TRUNC(123.456,-2) FROM DUAL; -- 결과값 : 100
SELECT TRUNC(123.456,-1) FROM DUAL; -- 결과값 : 120
SELECT TRUNC(123.456,0) FROM DUAL; -- 결과값 : 123
SELECT TRUNC(123.456,1) FROM DUAL; -- 결과값 : 123.4
SELECT TRUNC(123.456,2) FROM DUAL; -- 결과값 : 123.45
CEIL 올림 , FLOOR 내림
SELECT CEIL(123.456), FLOOR(123.456) FROM DUAL; -- 결과값 : 124, 123
CEIL, FLOOR는 소수점을 모두 없애고 올림과 내림의 차이만 있다.
MOD 나머지를 구하는 함수
SELECT MOD(7,3) FROM DUAL; --결과값 : 1
7나누기 3의 나머지인 1을 확인할 수 있다.
POWER(N,M) : N의 M승
SELECT POWER(2,10) FROM DUAL; --결과값 : 1024
TO_NUMBER('문자열') : 문자열을 숫자로 바꿔주는 함수
SELECT TO_NUMBER('123') FROM DUAL;
문자열 123이 숫자로 변환된다. 문자열에는 숫자만 들어가야 한다.
SELECT '123' + 456 FROM DUAL; --결과값 : 579
'123'은 TO_NUMBER으로 변환해주지 않았다. 하지만 결과는 123과 456을 더한 숫자값이 나온다. 데이터베이스에서는 문자열이라도 타입을 적절하게 자동으로 바꿔주기 때문이다.
RANK : 순위
RANK() OVER (정렬 기준)
SELECT S.*, RANK() OVER (ORDER BY SCORE DESC) FROM STUDENT S;
ORDER BY 기준으로 정렬한 다음 RANK 순위를 매겨준다.
날짜
SYSDATE : 오늘 날짜
SELECT SYSDATE FROM DUAL;
SYSDATE는 오늘 날짜를 확인해주는 함수이다. 기본 형식 22/05/13 으로 나타난다.
TO_DATE('문자열') : 문자열을 날짜로 바꿔주는 함수
SELECT TO_DATE('22/05/31') FROM DUAL; --결과값 : 22/05/31
날짜 형식 바꾸기
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_DATE_FORMAT 을 이용해서 오라클에서 지정된 현재 날짜 시간의 출력 포맷을 변경하였다.
다시 SYSDATE를 출력하면 2022-05-13 10:00:00 의 형식으로 나타난다.
MONTHS_BETWEEN
SELECT ABS(MONTHS_BETWEEN
(SYSDATE,'22/12/31')) FROM DUAL; --결과값 : 7.5619....
MONTHS_BETWEEN을 사용해서 지정날짜까지의 남은 월 계산을 할 수 있다.
오늘날짜부터 22/12/31 까지 남은 개월 수를 계산했다. 앞의 날짜에서 뒤의 날짜를 빼주는 것으로 -7.5619... 값이 나오기 때문에 항상 절대값이 나오도록 하는 ABS 함수도 추가하였다.
ADD_MONTHS
SELECT ADD_MONTHS(SYSDATE,2) FROM DUAL; --결과값 : 22/07/13
지정 날짜로부터 몇개월후의 날짜 /오늘날짜(SYSDATE)로부터 2개월 후의 날짜가 출력된다.
NEXT_DAY
주어진 날짜 기준으로 원하는 요일이 돌아오는 날짜
SELECT NEXT_DAY(SYSDATE, '화') FROM DUAL; --결과값 : 22-05-17
이번주 화요일은 지났기 때문에 다음주 화요일인 날짜가 나온다.
LAST_DAY
주어진 날짜 기준으로 날짜가 속한 달의 마지막 날
SELECT LAST_DAY(SYSDATE) FROM DUAL; --결과값 : 22/05/31
오늘날짜가 속한 5월의 마지막 날이 나온다.
내일 날짜 출력
SELECT SYSDATE +1 FROM DUAL;
날짜는 정수로 관리하기 때문에 하루는 1이다. +1을 하면 내일 날짜가 된다.
EX. 연말까지 남은 D-DAY 계산
SELECT TO_DATE('22/12/31') - SYSDATE FROM DUAL;
TO_CHAR(데이터,'형식') : 원하는 형식의 문자열로 변환
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM DUAL; --결과값 : 2022-05-13
SELECT TO_CHAR(SYSDATE,'MON MONTH DY DAY') FROM DUAL; --결과값 5월 5월 금 금요일
NVL(NULL,'문자열') : NULL 대신 문자열이 나오도록 대체
SELECT NVL(NULL,'널입니다') FROM DUAL;
DECODE : 하나 이상의 동등(=)문 조건과 조작
DECODE(컬럼, 조건1, TRUE 결과1, FALSE 결과1, 조건2, TRUE 결과2, FALSE 결과2, 조건3, 결과3..........)
NVL함수, DECODE 함수는 오라클에서만 존재하는 함수이니 주의해야 한다.
그룹함수 : SUM, AVG, MAX, MIN, COUNT, STDDEV(표준편차), VARIANCE(분산)
그룹함수는 함수명(컬럼명) 으로 사용하면 그룹을 묶은 기준으로 해당 컬럼을 가지고 계산한다.
그룹함수명(계산하고싶은 컬럼명) FROM 테이블명 GROUP BY 그룹 기준 컬럼
2022.05.20 - [DBMS/Oracle] - 그룹화(GROUP BY) , 그룹 함수(SUM, AVG, MAX, MIN, COUNT, STDDEV, VARIANCE)
그룹화(GROUP BY) , 그룹 함수(SUM, AVG, MAX, MIN, COUNT, STDDEV, VARIANCE)
그룹화 : group by 컬럼 그룹화 SELECT 컬럼 FROM 테이블 GROUP BY 그룹화할 컬럼; 조건 처리 후에 컬럼 그룹화 SELECT 컬럼 FROM 테이블 WHERE 조건식 GROUP BY 그룹화할 컬럼; 컬럼 그룹화 후에 조건 처..
jinaon.tistory.com
'DBMS > Oracle' 카테고리의 다른 글
절차형 SQL / 프로시저, 사용자 정의 함수, 트리거 (0) | 2022.05.25 |
---|---|
서브쿼리 (SUBQUERY) / 뷰(VIEW) (0) | 2022.05.23 |
제약조건 CONSTRAINT 종류, 생성, 수정, 조회, 삭제, 활성/비활성 (0) | 2022.05.20 |
시퀀스 SEQUENCE - 생성, 기본설정값, 조회 (0) | 2022.05.20 |
외래키 (Foreign Key) - 부모테이블, 자식테이블 / CASCADE (0) | 2022.05.20 |