[Oracle] SELECT문

1. 기본검색 

테이블에 있는 데이터를 검색하기 위한 명령어가 SELECT이며, 형식은 다음과 같습니다.

SELECT [DISTNCT] column-names

FROM table-names

[WHERE predicate]

[GROUP BY column-names [HAVING predicate]]

[ORDER BY column-names[asc|desc]]

 

Predicate는 조건을 뜻하고, '[ ]'는 생략할 수 있습니다.

 

1.1 특정 열 검색

테이블 내의 특정 열에 대한 검색은 필요로 하는 특정 열의 이름을 SELECT 다음에 나열하면 됩니다. 이 때 테이블 생성할 때 정의할 열의 순서와는 관계없이 나타내기 원하는 열 순서대로 기술하면 됩니다.

SELECT empo, ename

FROM emp;

 

1.2 열에 별칭(ALIAS) 사용

SELECT ename as name, comm as sale_bonus

from emp;

 

1.3 연결 연산자

SELECT ename || job as 성명직무

FROM emp;

 

2. 조건 검색

테이블 데이터 중 특정 조건에 해당하는 행을 검색하고자 할 때 WHERE 절을 이용합니다.

 

2.1 WHERE 절을 이용한 조건검색

다음은 EMP 테이블의 데이터 중 직무가 "SALESMAN"인 사원의 정보만을 검색하는 질의문입니다.

SELECT ename, job, sal, deptno

FROM emp

WHERE job = "SALESMAN";

 

2.2 논리 연산자

WHERE절에서 논리 연산자 NOT, AND, OR를 사용하여 여러 개의 조건을 결합하여 표현할 수 있습니다. 

SELECT ename, job, sal, deptno

FROM  emp 

WHERE sal>=1000 or job="SALESMAN";

 

2.3 범위 조건

데이터의 조건 검색 중 특정 데이터의 범위를 검색하고자 한다면, 비교 연산자와 관계 연산자를 사용할 수 있으나, WHERE절에 BETWEEN ~ AND을 사용하여 검색할 수도 있습니다. 다음은 급여가 1000부터 1500사이의 데이터를 검색하는 질의문입니다.

SELECT *

FROM emp

Where sal between 1000 and 1500;

 

2.4 LIKE를 이용한 검색

사용자가 어떤 열의 데이터 값 중 일부만 알고 있을 경우 그 내용을 이용하여 검색하는 방법으로 LIKE가 사용되며, 와일드카드 문자와 함께 사용됩니다.

 

사원 이름 중 J자로 시작하는 사원의 정보를 검색하는 질의문

SELECT empno, ename, job

FROM emp

WHERE ename like 'J%';

 

오라클에서 LIKE와 같이 사용하는 와일드카드 문자는 다음과 같습니다.

 

기 호	의 미
%	0개 이상의 문자
_	1개의 문자

 

 

2.5 널(NULL) 값 처리

관계형 데이터베이스는 테이블 내에 널값이 존재할 수 있으며, 이는 때때로 자료처리에 있어서 우리를 곤경에 빠지게 합니다.

SELECT ename, job, sal, comm

FROM  emp;

 

검색 결과에서 커미션열에 데이터가 보이지 않는 것은 널값을 의미합니다. 일반적으로 테이블에 열의 추가 하거나, 한 테이블에서 일부 열의 값만을 정의하게 되면 테이블 내에는 널값이 존재하게 되며, 이때 널값은 비교 연산자와 비교될 수 없으며, 그 연산의 결과는 참이 될 수 없습니다. 

 

(ex) NULL 값을 가지는 연산의 예

SELECT ename, sal, comm, sal * 12, sal * 12 + comm

FROM emp

 

다음은 널값의 존재 여부를 미리 확인할 수 있는 질의문입니다.

SELECT empno, ename, comm

FROM emp

WHERE comm is null;

SELECT empno, ename, comm

FROM emp

WHERE comm is not null;

 

2.6 IN

여러 개 조건 값 중 하나만 만족하는 행을 처리할 경우 IN을 사용합니다. 부서번호가 10, 30인 부서에 근무하는 사원의 이름과 부서번호를 검색하는 질의문 입니다. 

SELECT empno, deptno

FROM emp

WHERE deptno in (10,30);

 

3번 라인은 "where deptno=10 or deptno=30"으로 처리됩니다.

 

2.7 NOT IN

여러 개 조건 값 중 하나만 만족하는 행을 처리할 경우 NOT IN을 사용합니다. 부서번호가 10, 30이 아닌 부서에 근무하는 사원의 이름과 부서번호를 검색하는 질의문 입니다. 

SELECT empno, deptno
FROM emp
WHERE deptno not in (10,30);

 

* in과 not in 다음에 오는 괄호 안에는 서브쿼리가 들어갈 수 있습니다.

3. 검색 결과의 순서화(정렬)

정렬은 데이터를 어떤 기준에 의해 나열하는 것을 의미합니다. 이때 나열 방법에는 오름차순과 내림차순 2가지 방법이 있습니다. SQL에서 질의의 결과를 특정 열을 기준으로 정렬하고자 할 경우 ORDER BY절을 사용합니다. 다음은 사원들의 정보를 입사일자 기준으로 정렬하는 질의문입니다.

SELECT empno, job, comm, deptno, hiredate

FROM emp

ORDER BY hiredate;

 

ORDER BY는 SELECT문의 마지막에 위치하며, 오름차순으로 정렬할 경우 생략가능하고, 내림차순으로 정렬할 경우에는 반드시 DESCENDING 또는 DESC를 명시해야합니다. 또한 열의 위치, 별칭, SELECT문에 명시되지 않은 열 및 산술식을 이용하여 정렬할 수도 있습니다. 다음은 최근에 입사한 사원부터 검색하는 질의문입니다.

SELECT empno, job, comm, deptno, hiredate

FROM emp

ORDER BY hiredate desc;

 

다음은 별칭이 붙어 있는 열을 기준으로 정렬하는 질의문 입니다.

SELECT empno, ename, deptno, sal*12 as annualsal

FROM emp

ORDER BY annualsal;

 

열의 순서번호를 이용하여 정렬하는 질의문입니다. 

SELECT empno, ename, deptno, sal * 12 as annualsal

FROM emp

ORDER BY 4;

 

산술식의 열의 이름을 이용하여 정렬하는 질의문의 예입니다.

SELECT empno, ename, deptno, sal*12 as annualsal

FROM emp

ORDER BY sal*12;

 

여러 개의 열을 기준으로 정렬하는 질의문의 예입니다.

SELECT empno, ename, deptno, sal*12 as annualsal

FROM emp

ORDER BY deptno, annualsal;

 

오름차순과 내림차순이 혼용된 질의문의 예입니다.

SELECT empno, ename, deptno, sal*12 as annualsal

FROM emp

ORDER BY deptno, annualsal desc;

 

SELECT 절에 포함되지 않은 열을 이용하여 정렬하는 질의문의 예입니다.

SELECT empno, ename, deptno, sal

FROM emp

ORDER BY hiredate;

 

4.함수

4.1 함수란

함수란 C언어에서처럼 하나 이상의 인수를 전달받아 처리한 한 개의 결과 값을 함수의 이름을 이용하여 반환해 주는 프로그램 모듈입니다. 이러한 함수에는 각 행에 대한 처리된 결과를 반환하는 함수와 데이터 그룹에 대한 처리 결과를 반환하는 그룹함수로 구분할 수 있습니다. 각 행에 대해 처리된 결과를 반환하는 단일행 함수는 함수가 정의된 SQL문장이 실행될 때 각각의 행에 대해 수행되며 각 행 당 하나의 결과 값을 반환하며, 그룹함수는 데이터를 그룹화하고 각 그룹에 대한 함수의 결과를 반환하며, GROUP BY절을 사용합니다. 

 

4.2 단일행 함수

오라클은 데이터 처리를 위해 다양한 함수를 제공하고 있으며, 인수의 데이터 타입에 따라 문자함수, 숫자함수, 날짜함수로 구분할 수 있습니다. 이외에 형 변환 함수, 일반함수가 있습니다. 또한 함수의 활용에서 함수는 인수의 수 및 각 인수의 역할이 중요함으로 인수를 바꾸어가며 충분히 실습하고 숙지해야합니다.

 

함 수	설 명
LOWER(인수)	인수를 모두 소문자로 변환하여 반환
UPPER(인수)	인수를 모두 대문자로 변환하여 반환
INITCAT(인수)	인수 단어의 첫 번째 문자를 대문자로 변환하여 반환
SUBSTR(인수1,인수2,인수3,인수4)	문자열인수1의 일부분을 추출하여 반환 (ex) SUBSTR(ename, 2, 3) : 사원 이름의 2번째부터 3자를 반환
LENGTH(인수)	문자인수의 길이를 반환
INSTR(인수1, 인수2, 인수3, 인수4)	문자인수 중 특정 문자의 절대 위치를 반환.  INSTR(비교할대상, 비교하고자하는값, 비교를 시작할 위치, 검색된 결과의 순번)    (ex) INSTR(ename, 'A', 2, 2) : 사원 이름 중 2번째 문자부터 'A'가 2번 연속 나타나는 절대위치를 반환
LPAD(인수1, 인수2, 인수3)	자릿수를 지정하고 빈 공간을 특정 문자로 왼쪽부터 채워서 문자열을 반환 (ex) LPAD(ename, 10, '&')
RPAD(인수1, 인수2, 인수3)	자릿수를 지정하고 빈 공간을 특정 문자로 오른쪽부터 채워서 문자열을 반환 (ex) RPAD(ename, 10, '+')

 

예제를 통해서 알아봅시다.

SELECT LOWER('KOREA')

FROM dual;

SELECT UPPER('korea')

FROM dual;

 

이름의 뒤에서 2자를 추출하고자 한다면 다음과 같이 표현할 수 있습니다. 먼저 이름의 길이를 알아야 하므로 LENGTH함수를 이용하고, 뒤에서 3자를 추출하여야 하므로 SUBSTR함수를 이용합니다. 

SELECT ename, substr(ename, length(ename)-1, 2)

FROM emp;

 

4.3 숫자 함수

숫자 인수를 사용하는 숫자함수에 대해 알아봅시다.

함 수	설 명
ROUND(인수1, 인수2)	인수1의 값을 인수2의 자리로 반올림하여 반환
TRUNC(인수1, 인수2)	인수1의 값을 인수2자리까지 유지하고, 나머지는 절사하여 반환
MOD(인수1, 인수2)	인수1의 값을 인수2 값으로 나눈 나머지를 반환
ABS(인수)	인수의 절대값을 반환
FLOOR(인수)	소수점 이하 자리를 절사하여 반환

 

오라클은 위의 표에 있는 함수 외에도 SIN, COS, TAN 등 수학에서 사용되는 함수를 제공하고 있습니다. 

 

SELECT round(345.678), round(345.678, 0), round(345.678, 1), round(345.678, -1)

FROM dual;

 

결과에서 보듯이 인수2를 사용하지 않았을 경우 0으로 간주하여 소수 첫 자리에서 반올림하여 결과를 반환하며, 4번째 열처럼 인수2를 -1로 하였을 경우 일의 자리에서 반올림하여 십의 자리의 결과를 반환합니다.

 

4.4 날짜 함수

날짜를 인수로 하여 실행되는 날짜 함수에 대해 알아봅시다. 기본 날짜 형식은 'DD-MON-RR' 형식이며, 이 때 RR형식을 사용하면 21세기와 20세기 데이터의 처리가 가능합니다.

 

함 수	설 명
SYSDATE	시스템의 오늘 날짜를 반환
날짜	연산날짜에 +,- 연산을 함
MONTHS_BETWEEN(인수1, 인수2)	인수1, 2의 날수 차이를 반환
NEXT_DAY(인수1, 인수2)	인수 1에서 가장 가까운 인수2의 요일을 반환
ADD_MONTH(인수1, 인수2)	인수1에 인수2의 달을 더하여 반환
LAST_DAY(인수1)	인수1이 속한 달의 마지막 날을 반환
ROUND(인수1, 인수2)	인수1의 값을 인수2를 기준으로 반올림하여 반환
TRUNC(인수1)	인수1의 값을 인수2를 기준으로 절사하여 반환 (ex) TRUNC(SYSDATE, 'MON')

 

현재 날짜를 구하는 예제

SELECT sysdate

FROM dual;

 

다음은 날짜 형식을 연월일로 바꾸는 예입니다.

ALTER session set nls_date_format='yyyy-md-dd';

SELECT sysdate

FROM dual;

 

다음은 요일을 기준으로 날짜를 구하는 함수의 예입니다.

SELECT next_day(sysdate, '월')

FROM dual;

 

날짜도 숫자와 같이 반올림을 할 수 있습니다.

SELECT hiredate, round(hiredate, 'MON')

FROM emp;

 

4.5 변환 함수

C언어의 'int'나 'float'처럼 오라클에서도 데이터의 형을 변환할 수 있습니다.

함 수	내 용
TO_NUMBER	문자 데이터를 숫자 데이터로 변환
TO_DATE	문자 데이터를 날짜 데이터로 변환
TO_CHAR	숫자, 날짜 데이터를 문자 데이터로 변환

 

TO_CHAR 함수는 주로 출력에 형식을 지정하기 위해 사용되며, 날짜형, 숫자형 모든 데이터에 사용합니다

 

* 날짜를 인수로 가지는 TO_CHAR.

첫째 인수의 날짜를 두 번째 인수 형식으로 변환합니다. 사원의 입사일자를 'YYYY-MM' 형태로 검색하는 질의문입니다.

SELECT empno, ename, to_char(hiredate, 'yyyy-mm') as 입사년월

FROM emp;

 

* TO_NUMBER

TO_NUMBER 함수는 숫자 형태의 문자를 숫자로 변환할 때 사용합니다.

SELECT to_char(to_number(1234.5678, '9999.999')

FROM dual;

 

소수 이하 자릿수는 반올림하여 표현합니다.

SELECT to_char(to_number(1234.5678), '999.999')

FROM dual;

 

위의 예문에서처럼 자릿수가 적을 경우 결과는 에러('#')로 표현됩니다.

SELECT empno, ename, to_char(to_number(sal), '$999') salary

FROM emp;

 

* TO_DATE

TO_DATE 함수는 날짜 형태의 문자를 날짜로 변환할 때 사용합니다.

SELECT empno, ename

FROM emp

WHERE hiredate = to_date('1980-12-17', 'yy-mm-dd');

 

4.6 일반 함수

다음의 함수들은 널값과 관계가 있는 함수로 실무에서 아주 중요하게 사용되는 함수입니다.

 

* NVL

NVL 함수는 인수 값이 널인지를 체크하여 연산이 가능한 값을 변환하여 반환하는 함수입니다.

 

NVL(인수1, 인수2). NVL 함수는 인수1이 널이면 인수2를, 널이 아니면 인수1을 반환하는 함수입니다.

 

다음의 구문은   MGR에 널값이 존재할 경우, 널값을 0으로 치환하여 반환하는 예입니다.

SELECT mgr, nvl(mgr, 0)

FROM emp;

 

* NVL2

 

NVL2(인수1, 인수2, 인수3) NVL 함수는 인수1이 널값을 가졌을 때 정의된 인수2의 값을 치환하여 널값을 표현하였다면, NVL2 함수는 인수1이 널값이 아니면 인수2의 값을 반환해 주고, 널값이면 인수3의 값을 반환해 주는 함수입니다.

 

SELECT ename, sal, comm, nvl2(comm, sal+comm, sal)

FROM emp;

 

위의 질문은 커미션열을 체크하여 커미션이 널값이면 급여값만을, 널값이 아니면 급여와 커미션을 더한 값을 반환하는 예입니다, 

 

SELECT ename, sal, comm, sal+comm

FROM emp;

 

* NULLIF

NULLIF 함수는 인수1과 인수2의 값을 비교하여 그 값이 같으면 널값을, 아니면 인수1의 값을 반환하는 함수입니다,

 

NULLIF(인수1, 인수2)

 

SELECT nvl(nullif('Z', 'Z'), 'NULL')

FROM dual;

 

* CASE

오라클에서도 C언어의 SWITCH문과 같이 CASE문을 이용하여 조건에 따른 처리가 가능합니다.

SELECT column-names,

CASE WHEN condition-1 THEN statement-1,

     WHEN condition-2 THEN statement-2,

     WHEN condition-n THEN statement-n,

             ELSE statement 

        END AS COL-NAME

FROM table-name;

 

다음은 CASE문을 이용하여 직무별 인금인상액을 계산하여 출력하는 질의문입니다.

SELECT empno, ename, sal,

case job when 'SALESMAN' then sal*1.1

when 'CLERK' then sal*1.15

when 'MANAGER' then sal*1.2

else sal

end as 급여인상

FROM emp;

 

 

* DECODE

DECODE( column-name, condition-1, statement-1,

       condition-2, statement-2,

       condition-n, statement-n,

statement)

 

다음은 CASE에서 사용했던 예문을 DECODE로 바꾸어 표현하였습니다.

SELECT empno, ename, job, sal,

Decode(job, 'SALESMAN', sal*1.1,

    'CLERK', sal*1.15,

    'MANAGER', sal*1.2,

Sal) as 인상된급여

FROM emp;

 

5.그룹함수

그룹함수는 단일행 함수와 달리 여러 행에 대한 연산 즉 열의 평균, 합 등의 결과값을 반환 하는 함수입니다. 특히 SELECT문에서  GROUP BY절에 정의된 열을 기준으로 행을 그룹화 시켜 그룹함수를 적용하면 각 그룹에 하나의 결과를 반환하며, 만약 GROUP BY 절을 사용하지 않으면 데이터 전체에 대해 하나의 결과값을 반환합니다.

 

함 수	기 능
COUNT()	조건을 만족하는 열의 데이터 값들의 개수를 반환
COUNT(*)	모든 행의 개수를 반환
SUM()	조건을 만족하는 열의 데이터 값들의 합을 반환
AVG()	조건을 만족하는 열의 데이터 값들의 평균을 반환
MAX()	조건을 만족하는 열의 데이터 값들 중 최대값을 반환
MIN()	조건을 만족하는 열의 데이터 값들 중 최소값을 반환
STDDEV()	조건을 만족하는 열의 데이터 값들의 표준편차를 반환
VARIANCE()	조건을 만족하는 열의 데이터 값들의 분산 값을 반환

 

그룹함수 중 COUNT(*)을 제외하고는 널값을 제외하고 처리합니다. 또한 중복 값을 제외하기 위해서는 DISTINCT를 사용합니다.

 

* COUNT 함수

SELECT count(*), count(COMM)

 

* MAX와 MIN 함수

 

SELECT MAX(sal), MIN(sal)
FROM   emp

 

SELECT min(hiredate), max(hiredate)

FROM emp

WHERE deptno = 10;

 

6. GROUP BY 절

6.1 단일행을 이용한 GROUP BY절

GROUP BY절은 그룹을 지어 그룹별 대표 값을 반환할 때 사용합니다. 다음은 부서별로 그룹화하여 부서별 급여의 합을 구하는 예입니다.

SELECT deptno, sum(sal)

FROM emp

GROUP BY deptno;

 

다음은 급여가 2000이상 사원들의 부서별 사원수를 구하는 예입니다.

SELECT deptno, count(*)

FROM emp

WHERE sal>=2000

GROUP BY deptno;

 

 

6.2 복수열 GROUP BY 절

GROUP BY 절에 두 개 이상의 열이 기술될 수 있으며, 이 경우 GROUP BY절에 나열된 열의 순으로 행을 그룹지어 처리합니다.

SELECT deptno, job, count(*)

FROM emp

GROUP BY deptno, job;

 

6.3 Having절 사용

그룹함수를 적용한 결과에 다시 조건을 부여할 때는 HAVING 절을 사용합니다.

 

다음은 직무가 "SALESMAN"이 아닌 사원에 대한 부서별 급여 합이 6500인 부서번호를 구하는 예입니다. 

SELECT deptno, sum(sal)

FROM emp

WHERE job != "SALESMAN"

GROUP BY deptno HAVING sum(sal)>=6500;