TIL 정리_116(Oracle 문법)

https://ranprog.tistory.com/136

SQL/Oracle 문법의 내용을 보충 및 추가 

 

---

 

SELECT 구문의 모든 절을 실행 순서대로 나열

from 테이블명
where 조건식
group by 컬럼명
having 그룹함수 연산자 비교값
select * | [distinct] 컬럼, 계산식) [as] 별칭
order by 별칭 | 컬럼 | 컬럼순서 |[asc]|[desc]

 

 

정규표현식

특정한 규칙을 가진 문자열의 집합을 표현하는 데 사용하는 형식언어이다

 

 

정규식 표현

 

사용 기호	의미	사용 예시
^(캐럿)	해당 문자로 '시작하는' 라인을 출력한다	'^pattern'
$(달러)	해당 문자로 '끝나는' 라인을 출력한다	'pattern$'
.	$로 시작하여 E로 끝나는 라인을 설정한다	'S. . . . E'
*	모든이라는 의미로 글자수가 0일 수 있다	'[a-z]*'
[ ]	입력한 문자에 해당하는 한 문자를 출력한다	'[Pp]attern'
[ ^ ]	입력한 문자에 해당되지 않는 한 문자를 출력한다	'[^a-m]attern'

 

 

*오라클 10g 이상부터는 REGEXP_로 함수를 시작한다

 

정규식	의미
REGEXP_LIKE(컬럼 이름, 조건)	정규 표현식을 사용하여 조건 검색을 실행한다
REGEXP_REPLACE (문자열 또는 컬럼 이름, 조건, 대체할 문자열)	지정한 정규 표현식에 일치하는 부분을 다른 문자열로 바꾼다
REGEXP_INSTR (문자열 또는 컬럼 이름, 조건)	지정한 조건에 일치하는 부분의 최초 위치를 반환한다
REGEXP_SUBSTR (문자열 또는 컬럼 이름, 조건)	지정한 정규 표현식에 일치하는 부분 문자열을 찾아 결과를 반환한다

 

 

---

 

단일행 함수 

SQL 단일 행 함수 하나의 행에 있는 것만 가져와서 사용한다

-> 문자, 숫자, 날짜, 변환(묵시적, 데이터 형변환), 일반함수 등이 해당된다 

**오라클의 함수는 1부터 시작한다

 

INITCAP() 함수 INITCAP(문자열 또는 칼럼명)

>  select ename, INITCAP(ename) "INITCAP" from emp where deptno = 10;

 

 

LOWER() UPPER() - 대소문자 변환 

select ename, lower(ename) "LOWER", UPPER(ename) "UPPER" from emp where deptno = 10;

 

 

LENGTH / LENGTHB 함수 - 길이를 출력한다 

select ename, LENGTH(ename) "LENGTH", lengthb(ename) "LENGTHB" from emp where deptno = 20;

 

 

CONCAT()  - 연산자와 동일한 기능을 한다 

CONCAT('문자열 1', '문자열 2') select concat(ename, job) from emp where deptno = 10;

 

 

**SUBSTR('문자열' 또는 칼럼명, 기준위치, 출력개수)

select SUBSTR('문자열', 1, 4)

-> 1번째를(n번째) 기준으로 문자 4개를 출력한다

 

 

**INSTR() - 위치를 출력한다 

INSTR('문자열' or 컬럼, 찾는 글자, 시작위치(1부터 시작), 몇번째인지(기본 1))

select 'A-B-C-D', INSTR('A-B-C-D', '-', 3, 1) "INSTR" from dual;

결과 : 4

 

 

LPAD() 함수

LPAD('문자열' 또는 컬럼명, 자리수, '채울문자')

자리수 할당 후 왼쪽부터 빈자리를 채우는 함수이다

SELECT name, id, LPAD(id, 10, '*')

-> id를 10자리수 로 출력하고 빈 자리는 *로 채운다

 

RPAD() 함수 - 자리수 할당 후 오른쪽부터 빈자리를 채우는 함수이다

 

LTRIM() 함수

왼쪽 공백 혹은 문자를 제거한다

LTRIM('문자열' 또는 컬럼명, '제거할 문자')

 

RTRIM()

오른쪽 공백 혹은 문자를 제거한다(맨 끝만 제거)

SELECT RTRIM(ename, 'R') "RTRIM" from emp where deptno = 10;

 

 

 

숫자 관련 함수

ROUND 

ROUND(숫자, 출력을 원하는 자리 수) 소수점을 기준으로 n번째까지 반올림한다 

 

TRUNC 

TRUNC(숫자, 원하는 자리수) 소수점을 기준으로 n번째까지 값을 버린다 

 

• MOD 나머지를 출력한다 
• CEIL 주어진 숫자와 가장 근접한 큰 정수를 출력한다
• CEIL(12,345) -> 13
• FLOOR 주어진 숫자와 가장 근접한 작은 정수를 출력한다
• FLOOR(12.345) -> 12

 

 

날짜 관련 함수 

+, - 연산이 가능하다,  SYSDATE : 현재 날짜와 시간

**서버의 시간 변경은 조심할 것

 

MONTHS_BETWEEN

• 두 날짜 사이의 개월 수를 구한다. 큰 날짜를 먼저 써야 양수가 나온다
• 날짜가 같은 달에 속해있으면 특정 규칙으로 계산된 값이 나온다

 

ADD_MONTHS(SYSDATE, 1) : 1달을 더하여 출력

select SYSDATE, ADD_MONTHS(SYSDATE, 1) from dual;

 

NEXT_DAY() : 다음주 요일 출력

**윈도우는 날짜를 한글로 써야 한다 (리눅스는 'MON')

select sysdate, NEXT_DAY(SYSDATE, '월') FROM dual; 

 

LAST_DAY() : 마지막날 출력

 

 

 

형변환 함수 (11g 기준)

 

TO_CHAR(원래 날짜, '원하는 모양') -> 날짜를 원하는 format으로 출력할 수 있다

 

TO_NUMBER(함수 TO_NUMBER('숫자') 문자형 숫자를 숫자로 형변환한다

select TO_NUMBER('3') from dual;

select ASCII('A') from dual; //알파벳 -> 숫자

 

TO_DATE('문자') 문자 -> 날짜

select To_date('2014/05/31') from dual;

 

 

 

일반 함수

 

REPLACE('문자열' or 컬럼명, '문자1', '문자2') 문자1을 2로 바꾼다

SELECT ename, REPLACE(ename, SUBSTR(ename, 1, 2), '**')

-> ename의 첫번째를 기준으로 2개를 **로 바꾼다

-> 주민등록번호를 가릴 때 replace로 대체한다 

 

NVL() 함수 : NULL 값을 만나면 다른 값으로 치환해서 출력하는 함수이다(null처리)

문법 : NVL(컬럼, 치환할 값) NVL(sal, 0) NVL(sal, 100) null 대신 숫자로 치환

 

DECODE() : if문과 비슷하다. 오라클에서만 사용된다 조건문을 처리할 수 있다

 

CASE문

문법 : CASE 조건 WHEN 결과 THEN 출력 ELSE 나머지 END 칼럼이름

** , 를 사용하지 않는다

 

 

---

 

복수행 함수

입력되는 복수의 데이터들을 출력한다

-> 일종의그룹함수

 

COUNT() 함수 : null값이 있는 데이터도 count한다

select count(*), count(COMM) from emp;

 

 

SUM() 함수 : 총합을 구한다 

select count(comm), sum(comm) from emp;

 

 

AVG() 함수 : 평균을 구한다 

SELECT count(comm), sum(comm), avg(comm) from emp;

 

 

MAX(), MIN() : 최대값 / 최소값을 출력한다

 

STDDEV(), VARIANCE() : 표준편차/분산을 출력한다 

*표준편차 : sqrt(분산) = 5 같은 평균이어도 편차에 따라 다른 값을 출력한다 

*분산 : 표준편차를 제곱한 값 5*5 20*20

 

 

HAVING - group by절에서 where 대신 사용한다

**grouping 조건이 여러 개 존재할 떄 GROUPING SETS를 사용한다

 

 

ROLLUP() 함수 자동으로 소계를 만들어준다 

CUBE() 함수 : 소계와 전체 합계까지 출력하는 함수이다 

-> 부서별, 직급별, 전체 평균 월 급여와 사원수 출력에 사용

 

 

LAG 함수 : 하나씩 뒤로 미룬다

LAG(출력할 컬럼명, OFFSET, 기본 출력값) OVER(Query_partition 구문, ORDER BY 정렬할 컬럼)

 

 

LEAD 함수 : LAG 와 반대의 의미. 역으로 할당된다

select ename, hiredate, sal LEAD(sal, 2, 1) over (order by hiredate) "LEAD" from emp;

 

 

PIVOT 함수

• 행과 열을 뒤집는다.(변환한다) 
• PIVOT을 할 컬럼을 미리 정의해야 한다 

 

SELECT * FROM ( PIVOT 대상 쿼리문)
PIVOT (그룹합수 (집계컬럼) FOR PIVOT 컬럼 IN (PIVOT 컬럼값 AS 별칭 ... )

 

 

RANK 순위 출력 함수

rank(조건값) within group (order by 조건값 컬럼명 asc desc)

select RANK('SMITH') within group (order by ename) "RANK" from emp;

 

**테이블 전체 순위를 볼때는 within 대신 over를 사용한다

 

DENSE_RANK

순위 함수 -> rank와 달리 순위가 밀리지 않는다

 

ROW_NUMBER()

순위 함수 > 같은 순위가 있을 때 번호가 작은 것이 순위가 더 높게 나온다 (중복x)

 

SUM() OVER : 합계를 구한다

 

RATIO_TO_REPORT() :  판매 비율을 구한다 

 

 

---

 

서브쿼리 

• 하나의 SQL문에 포함되어 있는 또 다른 SQL문을 의미한다
• 사용할때는 괄호로 감싸서 사용하며, ORDER BY는 사용할 수 없다 

 

서브쿼리의 종류 

• 단일행 서브쿼리 : 서브쿼리의 조회 결과 값이 한 개인 경우
• 다중행 서브쿼리 : 서브쿼리의 조회 결과 하나의 컬럼에 행의 개수가 여러 개인 경우
• 다중열 서브쿼리 : 서브쿼리의 조회결과가 한 개의 행에 컬럼이 여러 개인 경우
• 다중열 다중행 서브쿼리 : 서브쿼리의 조회 결과가 여러 개의 칼럼과 여러 개의 행인 경우